sqlite知识分享

数据结构模型

CREATE TABLE "parent"@H_502_5@ (
    "id"@H_502_5@ TEXT@H_502_5@ PRIMARY KEY@H_502_5@,"name"@H_502_5@ TEXT@H_502_5@,"remark"@H_502_5@ TEXT@H_502_5@,"dlt"@H_502_5@ INTEGER@H_502_5@,"createtime"@H_502_5@ TEXT@H_502_5@,"updatetime"@H_502_5@ TEXT@H_502_5@
);

CREATE TABLE "child"@H_502_5@ (
    "id"@H_502_5@ TEXT@H_502_5@ PRIMARY KEY@H_502_5@,"pid"@H_502_5@ TEXT@H_502_5@,"age"@H_502_5@ INTEGER@H_502_5@,"score"@H_502_5@ REAL,"updatetime"@H_502_5@ TEXT@H_502_5@
);

下文假定Parent为M个，每个parent有N个Child。Conn表示数据库连接。

优化二级查询

需求：查询所有Parent的所有Child组成一个Map

方案一

List@H_502_5@<Parent@H_502_5@> pList=Conn.get("select * from parent"@H_502_5@);
for@H_502_5@(Parent@H_502_5@ p:pList)
{
    List@H_502_5@<Child> cList=Conn.get("select * from child where pid=?"@H_502_5@,p.id);
    map.put(p,cList);
}

此方法通过先查所有的父项目，然后遍历父项目再查询子项目。此种方法简单粗暴,也最容易理解。查询次数:M+1次。

方案二

List@H_502_5@<@H_502_5@Parent@H_502_5@>@H_502_5@ pList=@H_502_5@Conn.@H_502_5@get("select * from parent order by pid"@H_502_5@);
List@H_502_5@<@H_502_5@Child>@H_502_5@ cList=@H_502_5@Conn.@H_502_5@get("select * from child order by pid"@H_502_5@);
int position=@H_502_5@0@H_502_5@;
String@H_502_5@ currentPid=@H_502_5@""@H_502_5@;
List@H_502_5@<@H_502_5@Child>@H_502_5@ temp;
for(Child c:cList)
{
    if@H_502_5@(currentPid.@H_502_5@equals@H_502_5@(c.@H_502_5@pid))
    {
        temp.@H_502_5@add(c)
    }else@H_502_5@{
        temp=@H_502_5@new@H_502_5@ ArrayList<>@H_502_5@();
        map@H_502_5@.@H_502_5@put(pList.@H_502_5@get(position++@H_502_5@),temp)
        currentPid=@H_502_5@c.@H_502_5@pid;
        temp.@H_502_5@add(c);
    }
}

此方法利用数据库对数据排序，形成有序队列，然后分段截取List组装成Map。显然只进行了两次查询，极大的缓解了查询次数。这里相对于方案一查询语句多了一个排序，sqlite使用B-Tree建立索引，主键百万级数据排序都是毫秒级的速度。方案二的耗时在Parent数量越大时查询速度越优于方案一。

但是方案二是有Bug的，如果一个Parent没有Child将会导致后序关系错乱。

方案二改进版

List@H_502_5@<@H_502_5@Parent@H_502_5@>@H_502_5@ pList=@H_502_5@Conn.@H_502_5@get("select * from parent"@H_502_5@);
List@H_502_5@<@H_502_5@Child>@H_502_5@ cList=@H_502_5@Conn.@H_502_5@get("select * from child order by pid"@H_502_5@);

HashMap<@H_502_5@String@H_502_5@,Parent@H_502_5@>@H_502_5@ indexParent=@H_502_5@new@H_502_5@ HashMap<>@H_502_5@();
//对Parent建立id<--->Parent@H_502_5@
for(Parent@H_502_5@ p:pList)
{
    indexParent.@H_502_5@put(p.@H_502_5@id,p);
}
String@H_502_5@ currentPid=@H_502_5@""@H_502_5@;
List@H_502_5@<@H_502_5@Child>@H_502_5@ temp;
for(Child c:cList)
{
    if@H_502_5@(currentPid.@H_502_5@equals@H_502_5@(c.@H_502_5@pid))
    {
        temp.@H_502_5@add(c);
    }else@H_502_5@{
        temp=@H_502_5@new@H_502_5@ ArrayList<>@H_502_5@();
        map@H_502_5@.@H_502_5@put(indexParent.@H_502_5@get(currentPid),temp);
        //遍历完一个就从现有集合中移除一个Parent@H_502_5@
        indexParent.@H_502_5@remove(currentPid);
        currentPid=@H_502_5@c.@H_502_5@pid;
        temp.@H_502_5@add(c);
    }
}
//把剩下没有Child的Parent添加到Map中@H_502_5@
Iterator iter =@H_502_5@ indexParent.@H_502_5@entrySet().@H_502_5@iterator();
while@H_502_5@ (iter.@H_502_5@hasNext()) {
　　Map@H_502_5@.@H_502_5@Entry entry =@H_502_5@ (Map@H_502_5@.@H_502_5@Entry) iter.@H_502_5@next();
　　Parent@H_502_5@ p =@H_502_5@ (Parent@H_502_5@)entry.@H_502_5@getValue();
　　map@H_502_5@.@H_502_5@put(p,null@H_502_5@);
}

改进版的方法List可以是无序的，因为用Parent.ID对其建立了一个HashMap。最后把没有Child的Parent给加到了结果集里，这段代码可按需添加。

测试:

测试机型：Mate7

无条件查询（指没有额外的Where条件）

数据量：Parent(513),Child(64058)
方案1耗时：48559ms
方案2耗时：15035ms

数据量：Parent(10),Child(10)
方案1耗时：210ms
方案2耗时：163ms

带复杂条件查询

数据量：Parent(513),Child(64058)
Where条件为：child.name like ‘%我%’
条件结果集：Parent(13),Child(13)
方案1耗时：32572ms
方案2耗时：294ms

总结

在小数据集里方案一和方案二区别不是很明显，大数据集的时候很明显速度快了三倍。在带复杂条件查询的时候，方案二的速度十分的快，因为方案二只进行了一次数据比对，而方案一比对了M次。

实时搜索优化

需求：对EditText中输入的信息实时检索。
EditText监听如下：

String currentKey=""@H_502_5@;
etSearch.addTextChangedListener(new@H_502_5@ TextWatcher() {
   @Override@H_502_5@
   public@H_502_5@ void@H_502_5@ beforeTextChanged@H_502_5@(CharSequence s,int@H_502_5@ start,int@H_502_5@ count,int@H_502_5@ after) {
   }
   @Override@H_502_5@
   public@H_502_5@ void@H_502_5@ onTextChanged@H_502_5@(CharSequence s,int@H_502_5@ before,int@H_502_5@ count) {
   }
   @Override@H_502_5@
   public@H_502_5@ void@H_502_5@ afterTextChanged@H_502_5@(Editable s) {
        currentKey=s.toString();
        search(currentKey);
   }
});

异步数据库 搜索

private@H_502_5@ void@H_502_5@ search@H_502_5@(final@H_502_5@ String key)
{
    mExcutorService.execute(new@H_502_5@ Runnable() {
        @Override@H_502_5@
        public@H_502_5@ void@H_502_5@ run@H_502_5@() {
            List rs=Conn.find(key);
            runOnUiThread(new@H_502_5@ Runnable() {
                @Override@H_502_5@
                public@H_502_5@ void@H_502_5@ run@H_502_5@() {
                    adapter.setData(rs);
                    adapter.notifyDataSetChange();
                }
            });
        }
    });
}

此方法利用线程池来完成搜索，然后将搜索结果post到主线程更新界面。优点很明显，在子线程搜索，搜索过程不会阻塞UI线程。但是实际上用户只关心最后停留文字的搜索结果。

改进版搜索

private@H_502_5@ void@H_502_5@ search@H_502_5@(final@H_502_5@ String key)
{
    mExcutorService.execute(new@H_502_5@ Runnable() {
        @Override@H_502_5@
        public@H_502_5@ void@H_502_5@ run@H_502_5@() {
            List rs=Conn.find(key);
            synchronized@H_502_5@ (currentKey)
            {
                 //只有当前输入的Key与本次搜索的Key一致才去刷新界面。否则放弃本次搜索结果。@H_502_5@
                if@H_502_5@(key.eqauls(currentKey))
                {
                    runOnUiThread(new@H_502_5@ Runnable() {
                        @Override@H_502_5@
                        public@H_502_5@ void@H_502_5@ run@H_502_5@() {
                            adapter.setData(rs);
                            adapter.notifyDataSetChange();
                        }
                    });
                }
            }

        }
    });
}

这里改进了结果展示的逻辑，虽然还是每个字段在默默的搜索，但是如果在搜索过程中，用户改变了搜索关键字，那么本次搜索结果就扔掉了。这样减少了界面刷新导致卡顿。

内存里的递归搜索

在实际搜索应用中，当用户输入一个a的时候得到的结果集为ResultA，当用户再输入一个b，则搜索ab得到的结果集为ResultAB。那么一定有关系ResultAB属于ResultA。这样就有了一种新的搜索方案，根据用户的输入行为进行递归搜索，在上一次结果上进行搜索。

List@H_502_5@ allData;
List@H_502_5@ currentData;
String@H_502_5@ currentKey;

public@H_502_5@ void@H_502_5@ search(String@H_502_5@ key)
{
    //搜索关键字没有改变@H_502_5@
    if@H_502_5@(key.@H_502_5@eqauls(currentKey))
        return@H_502_5@;
    if@H_502_5@(key.@H_502_5@contains(currentKey))
    {
        //符合递归搜索前提@H_502_5@
        currentData=@H_502_5@ search(currentData,key);
    }else@H_502_5@{
        currentData=@H_502_5@ search(allData,key);
    }
     currentKey=@H_502_5@key;
}

public@H_502_5@ List@H_502_5@ search(List@H_502_5@ list@H_502_5@,String@H_502_5@ key)
{
    //搜索比对代码@H_502_5@
}

总结

在实际情况中，大数据量使用sqlite搜索的速度绝对优于普通的遍历内存搜索速度，数据库对数据建立有索引并且搜索的算法比遍历式英明得多。

sqlite使用

Group By

该关键字主要用于对结果集分组。

需求：查询每个Parent下age字段最大的Child。

解决方案：利用group by关键字来筛选每个Parent下面age字段最大的条目

SELECT@H_502_5@ p.id,p.name,c.id,c.name,c.age FROM@H_502_5@ parent p LEFT@H_502_5@ JOIN@H_502_5@ (SELECT@H_502_5@ * FROM@H_502_5@ (SELECT@H_502_5@ * FROM@H_502_5@ child ORDER@H_502_5@ BY@H_502_5@ pid,age ASC@H_502_5@) GROUP@H_502_5@ BY@H_502_5@ pid) c ON@H_502_5@ p.id = c.pid@H_502_5@

先对Child表排序，pid优先排序，然后age升序排列。
对Child表进行Group By pid，Group by的特性是保留最后一条。因此结果集中就是每个Parent年龄最大的Child。
通过Left Join把Child和Parent连接起来即是想要的结果。

distinct

该关键字的意义是去除重复的行，该关键字可以和其他函数一起使用。例如，函数”count(distinct X)”返回字段X的不重复非空值的个数，而不是字段X的全部非空值。avg(distinct X)、sum(distinct X) 也有相同的效果。

group_concat(x[,y])

该函数返回一个字符串，该字符串将会连接所有非NULL的x值。该函数的y参数将作为每个x值之间的分隔符，如果在调用时忽略该参数，在连接时将使用缺省分隔符”,”。各个字符串之间的连接顺序是不确定的。当你想获得一段数据某一字段用逗号分隔开来的数据，那么你就应该用此函数。

ifnull(x,y)、coalesce(X,Y,…)

coalesce(X,…) 返回第一个非空参数的副本。若所有的参数均为NULL，返回NULL。至少2个参数。 ifnull(X,Y) 返回第一个非空参数的副本。若两个参数均为NULL，返回NULL。与 coalesce()类似。常用此函数来过滤掉空值。例如当前值和默认值的选择。

sqlite里面的NULL

x||null=NULL
null <>’123’=NULL
NULL !=NULL ISNULL=1
group by时NULL会被当成一类