微信sqlite本地全文索引搜索是怎么做的呢

这个我知道，微信使用的全文搜索FTS，分词器是自定义的。分词规则是数字，字母按照词来分，汉字按照单个汉字。因为微信的聊天内容很多是无意义的，用ICU的zh-cn来分效果没有单个汉字来的好。

SQLite是个典型的嵌入式DBMS，它有很多优点，它是轻量级的，在编译之后很小，其中一个原因就是在查询优化方面比较简单，它只是运用索引机制来进行优化的，经过对SQLite的查询优化的分析以及对源代码的研究，我将SQLite的查询优总结如下：

一、影响查询性能的因素：

1． 对表中行的检索数目，越小越好

2． 排序与否。

3． 是否要对一个索引。

4． 查询语句的形式

二、几个查询优化的转换

1． 对于单个表的单个列而言，如果都有形如TC=expr这样的子句，并且都是用OR *** 作符连接起来，形如： x = expr1 OR expr2 = x OR x = expr3 此时由于对于OR，在SQLite中不能利用索引来优化，所以可以将它转换成带有IN *** 作符的子句：x IN(expr1,expr2,expr3)这样就可以用索引进行优化，效果很明显，但是如果在都没有索引的情况下OR语句执行效率会稍优于IN语句的效率。

2． 如果一个子句的 *** 作符是BETWEEN，在SQLite中同样不能用索引进行优化，所以也要进行相应的等价转换： 如：a BETWEEN b AND c可以转换成：(a BETWEEN b AND c) AND (a>=b) AND (a<=c)。 在上面这个子句中， (a>=b) AND (a<=c)将被设为dynamic且是(a BETWEEN b AND c)的子句，那么如果BETWEEN语句已经编码，那么子句就忽略不计，如果存在可利用的index使得子句已经满足条件，那么父句则被忽略。

3． 如果一个单元的 *** 作符是LIKE，那么将做下面的转换：x LIKE ‘abc%’，转换成：x>=‘abc’ AND x<‘abd’。因为在SQLite中的LIKE是不能用索引进行优化的，所以如果存在索引的话，则转换后和不转换相差很远，因为对LIKE不起作用，但如果不存在索引，那么LIKE在效率方面也还是比不上转换后的效率的。

三、 几种查询语句的处理（复合查询）

1．查询语句为：<SelectA> <operator> <selectB> ORDER BY <orderbylist> ORDER BY

执行方法： is one of UNION ALL, UNION, EXCEPT, or INTERSECT 这个语句的执行过程是先将selectA和selectB执行并且排序，再对两个结果扫描处理，对上面四种 *** 作是不同的，将执行过程分成七个子过程：

outA: 将selectA的结果的一行放到最终结果集中

outB: 将selectA的结果的一行放到最终结果集中(只有UNION *** 作和UNION ALL *** 作，其它 *** 作都不放入最终结果集中)

AltB: 当selectA的当前记录小于selectB的当前记录

AeqB: 当selectA的当前记录等于selectB的当前记录

AgtB: 当selectA的当前记录大于selectB的当前记录

EofA: 当selectA的结果遍历完

EofB: 当selectB的结果遍历完

下面就是四种 *** 作的执行过程：

　执行顺序

UNION ALL

UNION

EXCEPT

INTERSECT

AltB:

outA, nextA

outA, nextA

outA,nextA

nextA

AeqB:

outA, nextA

nextA

nextA

outA, nextA

AgtB:

outB, nextB

outB, nextB

nextB

nextB

EofA:

outB, nextB

outB, nextB

halt

halt

EofB:

outA, nextA

outA, nextA

outA,nextA

halt

2． 如果可能的话，可以把一个用到GROUP BY查询的语句转换成DISTINCT语句来查询，因为GROUP BY有时候可能会用到index，而对于DISTINCT都不会用到索引的 。

四、子查询扁平化

例子：SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5

对这个SQL语句的执行一般默认的方法就是先执行内查询，把结果放到一个临时表中，再对这个表进行外部查询，这就要对数据处理两次，另外这个临时表没有索引，所以对外部查询就不能进行优化了，如果对上面的SQL进行处理后可以得到如下SQL语句：SELECT x+y AS a FROM t1 WHERE z<100 AND a>5，这个结果显然和上面的一样，但此时只需要对

数据进行查询一次就够了，另外如果在表t1上有索引的话就避免了遍历整个表。

运用flatten方法优化SQL的条件：

1子查询和外查询没有都用集函数

2子查询没有用集函数或者外查询不是个表的连接

3子查询不是一个左外连接的右 *** 作数

4子查询没有用DISTINCT或者外查询不是个表的连接

5子查询没有用DISTINCT或者外查询没有用集函数

6子查询没有用集函数或者外查询没有用关键字DISTINCT

7子查询有一个FROM语句

8子查询没有用LIMIT或者外查询不是表的连接

9子查询没有用LIMIT或者外查询没有用集函数

10子查询没有用集函数或者外查询没用LIMIT

11子查询和外查询不是同时是ORDER BY子句

12子查询和外查询没有都用LIMIT

13子查询没有用OFFSET

14外查询不是一个复合查询的一部分或者子查询没有同时用关键字ORDER BY和LIMIT

15外查询没有用集函数子查询不包含ORDER BY

16复合子查询的扁平化：子查询不是一个复合查询，或者他是一个UNION ALL复合查询，但他是都由若干个非集函数的查询构成，他的父查询不是一个复合查询的子查询，也没有用集函数或者是DISTINCT查询，并且在FROM语句中没有其它的表或者子查询，父查询和子查询可能会包含WHERE语句，这些都会受到上面11、12、13条件的限制。

例： SELECT a+1 FROM (

SELECT x FROM tab

UNION ALL

SELECT y FROM tab

UNION ALL

SELECT abs(z2) FROM tab2

) WHERE a!=5 ORDER BY 1

转换为：

SELECT x+1 FROM tab WHERE x+1!=5

UNION ALL

SELECT y+1 FROM tab WHERE y+1!=5

UNION ALL

SELECT abs(z2)+1 FROM tab2 WHERE abs(z2)+1!=5

ORDER BY 1

17如果子查询是一个复合查询，那么父查询的所有的ORDER BY语句必须是对子查询的列的简单引用

18子查询没有用LIMIT或者外查询不具有WHERE语句

子查询扁平化是由专门一个函数实现的，函数为：

static int flattenSubquery(

Parse pParse, / Parsing context /

Select p, / The parent or outer SELECT statement /

int iFrom, / Index in p->pSrc->a[] of the inner subquery /

int isAgg, / True if outer SELECT uses aggregate functions /

int subqueryIsAgg / True if the subquery uses aggregate functions /

)

它是在Selectc文件中实现的。显然对于一个比较复杂的查询，如果满足上面的条件时对这个查询语句进行扁平化处理后就可以实现对查询的优化。如果正好存在索引的话效果会更好！

五、连接查询

在返回查询结果之前，相关表的每行必须都已经连接起来，在SQLite中，这是用嵌套循环实现的，在早期版本中，最左边的是最外层循环，最右边的是最内层循环，连接两个或者更多的表时，如果有索引则放到内层循环中，也就是放到FROM最后面，因为对于前面选中的每行，找后面与之对应的行时，如果有索引则会很快，如果没有则要遍历整个表，这样效率就很低，但在新版本中，这个优化已经实现。

优化的方法如下：

对要查询的每个表，统计这个表上的索引信息，首先将代价赋值为SQLITE_BIG_DBL（一个系统已经定义的常量）：

1) 如果没有索引，则找有没有在这个表上对rowid的查询条件：

1．如果有Rowid=EXPR，如果有的话则返回对这个表代价估计，代价计为零，查询得到的记录数为1，并完成对这个表的代价估计，

2．如果没有Rowid=EXPR 但有rowid IN ()，而IN是一个列表，那么记录返回记录数为IN列表中元素的个数，估计代价为NlogN,

3．如果IN不是一个列表而是一个子查询结果，那么由于具体这个子查询不能确定，所以只能估计一个值，返回记录数为100，代价为200。

4．如果对rowid是范围的查询，那么就估计所有符合条件的记录是总记录的三分之一，总记录估计为1000000，并且估计代价也为记录数。

5．如果这个查询还要求排序，则再另外加上排序的代价NlogN

6．如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。

2) 如果WHERE子句中存在OR *** 作符，那么要把这些OR连接的所有子句分开再进行分析。

1． 如果有子句是由AND连接符构成，那么再把由AND连接的子句再分别分析。

2． 如果连接的子句的形式是X<op><expr>，那么就再分析这个子句。

3． 接下来就是把整个对OR *** 作的总代价计算出来。

4． 如果这个查询要求排序，则再在上面总代价上再乘上排序代价NlogN

5． 如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。

3) 如果有索引，则统计每个表的索引信息，对于每个索引：

1． 先找到这个索引对应的列号，再找到对应的能用到（ *** 作符必须为=或者是IN（…））这个索引的WHERE子句，如果没有找到，则退出对每个索引的循环，如果找到，则判断这个子句的 *** 作符是什么，如果是=，那么没有附加的代价，如果是IN（sub-select），那么估计它附加代价inMultiplier为25，如果是IN（list），那么附加代价就是N（N为list的列数）。

2． 再计算总的代价和总的查询结果记录数和代价。

3． nRow = pProbe->aiRowEst[i] inMultiplier;/计算行数/

4． cost = nRow estLog(inMultiplier);/统计代价/

5． 如果找不到 *** 作符为=或者是IN（…）的子句，而是范围的查询，那么同样只好估计查询结果记录数为nRow/3，估计代价为cost/3。

6． 同样，如果此查询要求排序的话，再在上面的总代价上加上NlogN

7． 如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。

4) 通过上面的优化过程，可以得到对一个表查询的总代价（就是上面各个代价的总和），再对第二个表进行同样的 *** 作，这样如此直到把FROM子句中所有的表都计算出各自的代价，最后取最小的，这将作为嵌套循环的最内层，依次可以得到整个嵌套循环的嵌套顺序，此时正是最优的，达到了优化的目的。

5) 所以循环的嵌套顺序不一定是与FROM子句中的顺序一致，因为在执行过程中会用索引优化来重新排列顺序。

六、索引

在SQLite中，有以下几种索引：

1) 单列索引

2) 多列索引

3) 唯一性索引

4) 对于声明为：INTEGER PRIMARY KEY的主键来说，这列会按默认方式排序，所以虽然在数据字典中没有对它生成索引，但它的功能就像个索引。所以如果在这个主键上在单独建立索引的话，这样既浪费空间也没有任何好处。

运用索引的注意事项：

1) 对于一个很小的表来说没必要建立索引

2) 在一个表上如果经常做的是插入更新 *** 作，那么就要节制使用索引

3) 也不要在一个表上建立太多的索引，如果建立太多的话那么在查询的时候SQLite可能不会选择最好的来执行查询，一个解决办法就是建立聚蔟索引

索引的运用时机：

1) *** 作符：=、>、<、IN等

2) *** 作符BETWEEN、LIKE、OR不能用索引，

如BETWEEN：SELECT FROM mytable WHERE myfield BETWEEN 10 and 20;

这时就应该将其转换成：

SELECT FROM mytable WHERE myfield >= 10 AND myfield <= 20;

此时如果在myfield上有索引的话就可以用了，大大提高速度

再如LIKE：SELECT FROM mytable WHERE myfield LIKE 'sql%';

此时应该将它转换成：

SELECT FROM mytable WHERE myfield >= 'sql' AND myfield < 'sqm';

此时如果在myfield上有索引的话就可以用了，大大提高速度

再如OR：SELECT FROM mytable WHERE myfield = 'abc' OR myfield = 'xyz';

此时应该将它转换成：

SELECT FROM mytable WHERE myfield IN ('abc', 'xyz');

此时如果在myfield上有索引的话就可以用了，大大提高速度

3) 有些时候索引都是不能用的，这时就应该遍历全表（程序演示）

SELECT FROM mytable WHERE myfield % 2 = 1;

SELECT FROM mytable WHERE substr(myfield, 0, 1) = 'w';

SELECT FROM mytable WHERE length(myfield) < 5;

SQLite3是目前最新的SQLite版本。可以从网站上下载SQLite3的源代码（本书使用的版本是sqlite-3612targz）。

解压缩后进入sqlite-3612的根目录，首先命令“/configure”生成Makefile文件，接着运行命令“make”对源代码进行编译，最后运行命令“make install”安装SQLite3。安装完毕后，可以运行命令sqlite3查看SQLite3是否能正常运行，如下所示：

[root@localhost ~]# sqlite3

SQLite version 3612

Enter "help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

可以看到，SQLite3启动后会停留在提示符sqlite>处，等待用户输入SQL语句。

在使用SQLite3前需要先了解下SQLite3支持的数据类型。SQLite3支持的基本数据类型主要有以下几类：

NULL

NUMERIC

INTEGER

REAL

TEXT

SQLite3会自动把其他数据类型转换成以上5类基本数据类型，转换规则如下所示：

char、clob、test、varchar—> TEXT

integer—>INTEGER

real、double、float—> REAL

blob—>NULL

其余数据类型都转变成NUMERIC

下面通过一个实例来演示SQLite3的使用方法。

新建一个数据库

新建数据库testdb（使用db后缀是为了标识数据库文件）。在testdb中新建一个表test_table，该表具有name,、sex、age三列。SQLite3的具体 *** 作如下所示：

[root@localhost home]# sqlite3 testdb

SQLite version 3612

Enter "help" for instructions

Enter SQL statements terminated with a ";"

sqlite> create table test_table(name, sex, age);

如果数据库testdb已经存在，则命令“sqlite3 testdb”会在当前目录下打开testdb。如果数据库testdb不存在，则命令“sqlite3 testdb”会在当前目录下新建数据库testdb。为了提高效率，SQLite3并不会马上创建testdb，而是等到第一个表创建完成后才会在物理上创建数据库。

由于SQLite3能根据插入数据的实际类型动态改变列的类型，所以在create语句中并不要求给出列的类型。

创建索引

为了加快表的查询速度，往往在主键上添加索引。如下所示的是在name列上添加索引的过程。

sqlite> create index test_index on test_table(name);

*** 作数据

如下所示的是在test_table中进行数据的插入、更新、删除 *** 作：

sqlite> insert into test_table values ('xiaoming', 'male', 20);

sqlite> insert into test_table values ('xiaohong', 'female', 18);

sqlite> select from test_table;

xiaoming|male|20

xiaohong|female|18

sqlite> update test_table set age=19 where name = 'xiaohong';

sqlite> select from test_table;

xiaoming|male|20

xiaohong|female|19

sqlite> delete from test_table where name = 'xiaoming';

sqlite> select from test_table;

xiaohong|female|19

批量 *** 作数据库

如下所示的是在test_table中连续插入两条记录：

sqlite> begin;

sqlite> insert into test_table values ('xiaoxue', 'female', 18);

sqlite> insert into test_table values ('xiaoliu', 'male', 20);

sqlite> commit;

sqlite> select from test_table;

xiaohong|female|19

xiaoxue|male|18

xiaoliu|male|20

运行命令commit后，才会把插入的数据写入数据库中。

数据库的导入导出

如下所示的是把testdb导出到sql文件中：

[root@localhost home]# sqlite3 testdb "dump" > testsql;

testsql文件的内容如下所示：

BEGIN TRANSACTION;

CREATE TABLE test_table(name, sex, age);

INSERT INTO "test_table" VALUES('xiaohong','female',19);

CREATE INDEX test_index on test_table(name);

COMMIT;

如下所示的是导入testsql文件（导入前删除原有的testdb）：

[root@localhost home]# sqlite3 testdb < testsql;

通过对testsql文件的导入导出，可以实现数据库文件的备份。

1122 SQLite3的C接口

以上介绍的是SQLite3数据库的命令 *** 作方式。在实际使用中，一般都是应用程序需要对数据库进行访问。为此，SQLite3提供了各种编程语言的使用接口（本书介绍C语言接口）。SQLite3具有几十个C接口，下面介绍一些常用的C接口。

sqlite_open

作用：打开SQLite3数据库

原型：int sqlite3_open(const char dbname, sqlite3 db)

参数：

dbname：数据库的名称；

db：数据库的句柄；

sqlite_colse

作用：关闭SQLite3数据库

原型：int sqlite_close(sqlite3 db)

例如：

testc：

#include <stdioh>

#include <sqlite3h>

static sqlite3 db=NULL;

int main()

{

int rc;

rc= sqlite3_open("testdb", &db);

if(rc)

{

printf("can't open database!\n");

}

else

{

printf("open database success!\n");

}

sqlite3_close(db);

return 0;

}

运行命令“gcc –o test testc –lsqlite3”进行编译，运行test的结果如下所示：

[root@localhost home]# open database success!

sqlite_exec

作用：执行SQL语句

原型：int sqlite3_exec(sqlite3 db, const char sql, int (callback)(void,int,char,char), void , char errmsg)

参数：

db：数据库；

sql：SQL语句；

callback：回滚；

errmsg：错误信息

例如：

testc：

#include <stdioh>

#include <sqlite3h>

static sqlite3 db=NULL;

static char errmsg=NULL;

int main()

{

int rc;

rc = sqlite3_open("testdb", &db);

rc = sqlite3_exec(db,"insert into test_table values('daobao', 'male', 24)", 0, 0, &errmsg);

if(rc)

{

printf("exec fail!\n");

}

else

{

printf("exec success!\n");

}

sqlite3_close(db);

return 0;

}

编译完成后，运行test的结果如下所示：

[root@localhost home]# /test

exec success!

[root@localhost home]# sqlite3 testdb

SQLite version 3611

Enter "help" for instructions

Enter SQL statements terminated with a ";"

sqlite> select from test_table;

daobao|male|24

sqlite3_get_table

作用：执行SQL查询

原型：int sqlite3_get_table(sqlite3 db, const char zSql, char pazResult, int pnRow, int pnColumn, char pzErrmsg)

参数：

db：数据库；

zSql：SQL语句；

pazResult：查询结果集；

pnRow：结果集的行数；

pnColumn：结果集的列数；

errmsg：错误信息；

sqlite3_free_table

作用：注销结果集

原型：void sqlite3_free_table(char result)

参数：

result：结果集；

例如：

testc：

#include <stdioh>

#include <sqlite3h>

static sqlite3 db=NULL;

static char Result=NULL;

static char errmsg=NULL;

int main()

{

int rc, i, j;

int nrow;

int ncolumn;

rc= sqlite3_open("testdb", &db);

rc= sqlite3_get_table(db, "select from test_table", &Result, &nrow, &ncolumn,

&errmsg);

if(rc)

{

printf("query fail!\n");

}

else

{

printf("query success!\n");

for(i = 1; i <= nrow; i++)

{

for(j = 0; j < ncolumn; j++)

{

printf("%s | ", Result[i ncolumn + j]);

}

printf("\n");

}

}

sqlite3_free_table(Result);

sqlite3_close(db);

return 0;

}

编译完成后，运行test的结果如下所示：

[root@localhost home]# /test

query success!

xiaohong | female | 19 |

xiaoxue | female | 18 |

xiaoliu | male | 20 |

daobao | male | 24 |

sqlite3_prepare

作用：把SQL语句编译成字节码，由后面的执行函数去执行

原型：int sqlite3_prepare(sqlite3 db, const char zSql, int nByte, sqlite3_stmt stmt, const char pTail)

参数：

db：数据库；

zSql：SQL语句；

nByte：SQL语句的最大字节数；

stmt：Statement句柄；

pTail：SQL语句无用部分的指针；

sqlite3_step

作用：步步执行SQL语句字节码

原型：int sqlite3_step (sqlite3_stmt )

例如：

testc：

#include <stdioh>

#include <sqlite3h>

static sqlite3 db=NULL;

static sqlite3_stmt stmt=NULL;

int main()

{

int rc, i, j;

int ncolumn;

rc= sqlite3_open("testdb", &db);

rc=sqlite3_prepare(db,"select from test_table",-1,&stmt,0);

if(rc)

{

printf("query fail!\n");

}

else

{

printf("query success!\n");

rc=sqlite3_step(stmt);

ncolumn=sqlite3_column_count(stmt);

while(rc==SQLITE_ROW)

{

for(i=0; i<2; i++)

{

printf("%s | ", sqlite3_column_text(stmt,i));

}

printf("\n");

rc=sqlite3_step(stmt);

}

}

sqlite3_finalize(stmt);

sqlite3_close(db);

return 0;

}

编译完成后，运行test的结果如下所示：

[root@localhost home]# /test

query success!

xiaohong | female | 19 |

xiaoxue | female | 18 |

xiaoliu | male | 20 |

daobao | male | 24 |

在程序中访问SQLite3数据库时，要注意C API的接口定义和数据类型是否正确，否则会得到错误的访问结果。

SELECT name FROM sqlite_master

WHERE type='table'

ORDER BY name;

如果你在sqlite行命令下，你可以直接使用 tables 或 schema 命令来得到完整的数据库集包括表s和索引s 这两个命令支持匹配符。

如果在其它宿主程序中例如 C/C++等，你可以从一个特殊的表 "SQLITE_MASTER" 得到类似的信息

以上就是关于微信sqlite本地全文索引搜索是怎么做的呢全部的内容，包括:微信sqlite本地全文索引搜索是怎么做的呢、如何优化sqlite的查询速度、如何使用SQLite等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！