Android SQLite数据库进行查询优化的方法
前言
数据库的性能优化行业里面普遍偏少，今天这篇希望给大家带来点帮助
SQLite是个典型的嵌入式DBMS，它有很多优点，它是轻量级的，在编译之后很小，其中一个原因就是在查询优化方面比较简单
我们在使用SQLite进行数据存储查询的时候，要进行查询优化，这里就会用到索引，C端的数据量大部分情况下面虽然不是很大，但良好的索引建立习惯往往会带来不错的查询性能提升，同时在未知的将来经得住更大数据的考验，那如何优化数据库查询呢，下面我们用例子一一演示下。
先建个测试表table1,包含了三个索引：
sqlite>.schem CREATETABLEtable1(idintegerprimarykeynotnulldefault0,ainteger,binteger,cinteger); CREATEINDEXa_iontable1(a); CREATEINDEXa_i2ontable1(a,b); CREATEINDEXa_i3ontable1(c);
在常见的数据库系统里面，进行SQL查询检验都是用explain关键字，比如：
sqlite>explainselect*fromtable1; addropcodep1p2p3p4p5comment --------------------------------------------------------- 0Init010000Startat10 1OpenRead020400root=2iDb=0;table1 2Rewind09000 3Rowid01000r[1]=rowid 4Column01200r[2]=table1.a 5Column02300r[3]=table1.b 6Column03400r[4]=table1.c 7ResultRow14000output=r[1..4] 8Next03001 9Halt00000 10Transaction004001usesStmtJournal=0 11Goto01000
立马就会得到输出，这些输出表示SQLite执行这条SQL用到的每句指令，这个其实不怎么直观，我们用到更多的是EXPLAINQUERYPLAN，如下：
sqlite>explainQUERYPLANselect*fromtable1; 0|0|0|SCANTABLEtable1
这条SQL语句是查询了整张表，所以结果关键字SCAN表示要完整遍历，这种效率是最低的，接下来我们试试加个查询条件：
sqlite>explainQUERYPLANselect*fromtable1wherea=1; 0|0|0|SEARCHTABLEtable1USINGINDEXa_i2(a=?)
加上wherea=1之后关键字变成了SEARCH，表示不再需要遍历了，而是使用了索引进行了部分检索，另外这条输出还有更多信息，比如使用了索引a_i2，而括号里面的a=?则表示是这个查询条件引起的
我们稍微修改下SQL：
sqlite>explainQUERYPLANselectafromtable1wherea=1; 0|0|0|SEARCHTABLEtable1USINGCOVERINGINDEXa_i(a=?)
把select变成了selecta，发现explain输出有细微变化，从INDEX变成了COVERINGINDEX，CONVERINGINDEX表示直接使用索引查询就可以得到结果，不需要再次回查数据表，这样效率更高。而之前的查询因为是使用，索引里面只有a记录，所以必须要查询原始记录才能得到b，c字段。我们再试下这条SQL：
sqlite>explainQUERYPLANselecta,bfromtable1wherea=1andb=1; 0|0|0|SEARCHTABLEtable1USINGCOVERINGINDEXa_i2(a=?ANDb=?)
同意因为索引a_i2已经包含a和b了，所以也是使用CONVERINGINDEX。那有同学可能会问了，那我们建索引的时候都把其他字段都加进去呗，虽然查询用不到，但不用二次查询原始记录效率高。理论上这样是可行的，但这里有个重要问题就是数据冗余太严重了，导致索引和原始数据一样大，在海量数据存储的数据库里面磁盘消耗是个问题，所以如何选择可能要做个平衡。
接下来我们把and换成or：
sqlite>explainQUERYPLANselecta,bfromtable1wherea=1orb=1; 0|0|0|SCANTABLEtable1USINGCOVERINGINDEXa_i2
发现又变回SCAN了，但仍然使用到了索引a_i2，对比下这条SQL：
sqlite>explainQUERYPLANselecta,bfromtable1wherea=1; 0|0|0|SEARCHTABLEtable1USINGCOVERINGINDEXa_i2(a=?)
多了个查询条件b=1之后效率变差了，这是为什么呢？这里要引出我们创建索引使用的最关键的原则：前缀索引。
索引一般是使用B树，前缀索引简单来讲，就是要想能使用这个索引，查询条件必须满足索引建立涉及到的字段，并且和查询使用的顺序一致。
我们回头看刚才那个or的例子，对于查询条件a=1，他能使用a_i2(a,b)这个索引，因为索引顺序也是a开头的。但or的例子里面还或上一个查询条件b=1，对于这个查询就没有索引可以用了，因为没有b开头的索引存在。a_i2(a,b)这个索引里面虽然有b，但b对于b=1这个查询条件来说不是在前面，不满足前缀索引原则。
而对于刚才那个and的例子，则能够完全使用索引，因为存在索引a_i2(a,b)，可以想象成先按索引a过滤数据，剩下数据再用索引b过滤数据。对于and条件来说，索引里面字段的顺序换一下也是没有关系的，数据库会自动优化选择，比如：
sqlite>.schem CREATEINDEXa_i22ontable2(b,a); sqlite>explainQUERYPLANselecta,bfromtable2wherea=1andb=1; 0|0|0|SEARCHTABLEtable2USINGCOVERINGINDEXa_i22(b=?ANDa=?)
如果or查询也要充分使用索引，聪明的读者一定想到了，那就是要建2个索引，如下：
CREATETABLEtable3(idintegerprimarykeynotnulldefault0,ainteger,binteger,cinteger); CREATEINDEXa_i222ontable3(a); CREATEINDEXa_i2222ontable3(b); sqlite>explainQUERYPLANselecta,bfromtable3wherea=1orb=1; 0|0|0|SEARCHTABLEtable3USINGINDEXa_i222(a=?) 0|0|0|SEARCHTABLEtable3USINGINDEXa_i2222(b=?)
我们再来看一个进阶的，加上一个排序：
CREATETABLEtable1(idintegerprimarykeynotnulldefault0,ainteger,binteger,cinteger); CREATEINDEXa_i2ontable1(a,b); sqlite>explainQUERYPLANselecta,bfromtable1wherea=1orderbyb; 0|0|0|SEARCHTABLEtable1USINGCOVERINGINDEXa_i2(a=?) CREATETABLEtable3(idintegerprimarykeynotnulldefault0,ainteger,binteger,cinteger); CREATEINDEXa_i222ontable3(a); CREATEINDEXa_i2222ontable3(b); sqlite>explainQUERYPLANselecta,bfromtable3wherea=1orderbyb; 0|0|0|SEARCHTABLEtable3USINGINDEXa_i222(a=?) 0|0|0|USETEMPB-TREEFORORDERBY
对比这2个查询，发现下面这个多了个USETEMPB-TREEFORORDERBY。对于第一个查询来说，我们可以看到排序也是同样满足前缀索引原则（先按索引a过滤数据，剩下数据用索引b排序）。对于第二个查询来说，因为不满足这个原则导致多了个临时表来做排序。看到这里大家应该理解前缀索引的意思了。
我们再看这个样子，把查询条件和排序换下：
sqlite>explainQUERYPLANselecta,bfromtable1whereb=1orderbya; 0|0|0|SCANTABLEtable1USINGCOVERINGINDEXa_i2
显然不满足前缀索引原则了，因为需要先按索引b过滤数据，但b不是第一个。
常规的查询语句大部分是and，or，order的组合使用，只需要掌握上面说的原则，一定能写出高性能的数据库查询语句来。
而对于更高级的一些连表可以继续翻阅官方文档：
https://www.sqlite.org/eqp.html
https://www.sqlite.org/lang_e...
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。