《2022年SQLite的查询优化 .pdf》由会员分享，可在线阅读，更多相关《2022年SQLite的查询优化 .pdf（7页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、SQLite 是个典型的嵌入式DBMS，它有很多优点，它是轻量级的，在编译之后很小，其中一个原因就是在查询优化方面比较简单，它只是运用索引机制来进行优化的：一、影响查询性能的因素：1对表中行的检索数目，越小越好2排序与否。3是否要对一个索引。4查询语句的形式二、几个查询优化的转换1对于单个表的单个列而言，如果都有形如 T.C=expr 这样的子句，并且都是用 OR符连接起来，形如：x=expr1 OR expr2=x OR x=expr3 此时由于对于 OR，在 SQLite 中不能利用索引来优化，所以可以将它转换成带有IN 操作符的子句：x IN(expr1,expr2,expr3)这样就可
2、以用索引进行优化，效果很明显，但是如果在都没有索引的情况下OR语句执行效率会稍优于IN 语句的效率。2 如果一个子句的操作符是BETWEEN，在 SQLite 中同样不能用索引进行优化，所以也要进行相应的等价转换：如：a BETWEEN b AND c 可以转换成：(a BETWEEN b AND c)AND(a=b)AND(a=b)AND(a=abc AND xabd。因为在 SQLite 中的 LIKE 是不能用索引进行优化的，所以如果存在索引的话，则转换后和不转换相差很远，因为对 LIKE 不起作用，但如果不存在索引，那么 LIKE在效率方面也还是比不上转换后的效率的。三、几种查询语句的
3、处理（复合查询）1 查询语句为：ORDER BY ORDER BY 执行方法：is one of UNION ALL,UNION,EXCEPT,or INTERSECT.名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 7 页 -这个语句的执行过程是先将select A和 select B执行并且排序，再对两个结果扫描处理，对上面四种操作是不同的，将执行过程分成七个子过程：outA:将 selectA 的结果的一行放到最终结果集中outB:将 selectA 的结果的一行放到最终结果集中(只有 UNION 操作和 UNION ALL操作，其它操作都不放入最终结果集中)AltB:当
4、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 名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 7 页 -next
5、A 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 z5 对这个 SQL语句的执行一般默认的方
6、法就是先执行内查询，把结果放到一个临时表中，再对这个表进行外部查询，这就要对数据处理两次，另外这个临时表没有索引，所以对外部查询就不能进行优化了.名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 7 页 -如果对上面的 SQL进行处理后可以得到如下SQL语句：SELECT x+y AS a FROM t1 WHERE z5，这个结果显然和上面的一样，但此时只需要对数据进行查询一次就够了，另外如果在表t1 上有索引的话就避免了遍历整个表。运用 flatten方法优化 SQL的条件：1.子查询和外查询没有都用集函数2.子查询没有用集函数或者外查询不是个表的连接3.子查询不是一个左外连接
7、的右操作数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.外
8、查询没有用集函数子查询不包含ORDER BY 16.复合子查询的扁平化：子查询不是一个复合查询，或者他是一个UNION ALL复合查询，但他是都由若干个非集函数的查询构成，他的父查询不是一个复合查询的子查询，也没有用集函数或者是DISTINCT查询，并且在 FROM 语句中没有其它的表或者子查询，父查询和子查询可能会包含WHERE语句，这些都会受到上面 11、12、13 条件的限制。例：名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 7 页 -转换为：17.如果子查询是一个复合查询，那么父查询的所有的ORDER BY语句必须是对子查询的列的简单引用18.子查询没有用 LIMIT
9、或者外查询不具有 WHERE 语句子查询扁平化是由专门一个函数实现的，函数为：它是在 Select.c文件中实现的。显然对于一个比较复杂的查询，如果满足上面的条件时对这个查询语句进行扁平化处理后就可以实现对查询的优化。如果正好存在索引的话效果会更好！五、连接查询在返回查询结果之前，相关表的每行必须都已经连接起来，在SQLite 中，这是用嵌套循环实现的，在早期版本中，最左边的是最外层循环，最右边的是最内层循环，连接两个或者更多的表时，如果有索引则放到内层循环中，也就是放到 FROM 最后面，因为对于前面选中的每行，找后面与之对应的行时，如果有索引则会很快，如果没有则要遍历整个表，这样效率就很低
10、，但在新版本中，这个优化已经实现。优化的方法如下：对要查询的每个表，统计这个表上的索引信息，首先将代价赋值为SQLITE_BIG_DBL（一个系统已经定义的常量）：1)如果没有索引，则找有没有在这个表上对rowid 的查询条件：1 如果有 Rowid=EXPR，如果有的话则返回对这个表代价估计，代价计为零，查询得到的记录数为1，并完成对这个表的代价估计，2如果没有 Rowid=EXPR 但有 rowid IN(.)，而 IN 是一个列表，那么记录返回记录数为 IN 列表中元素的个数，估计代价为NlogN,3如果 IN 不是一个列表而是一个子查询结果，那么由于具体这个子查询不能确定，所以只能估计
11、一个值，返回记录数为100，代价为 200。4如果对 rowid 是范围的查询，那么就估计所有符合条件的记录是总记录的三分之一，总记录估计为1000000，并且估计代价也为记录数。5如果这个查询还要求排序，则再另外加上排序的代价NlogN 6如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。2)如果 WHERE 子句中存在 OR操作符，那么要把这些OR连接的所有子句分开再进行分析。名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 7 页 -1如果有子句是由 AND连接符构成，那么再把由AND连接的子句再分别分析。2如果连接的子句的形式是X，那么就再分析这个子句。3接下来就
12、是把整个对OR操作的总代价计算出来。4如果这个查询要求排序，则再在上面总代价上再乘上排序代价NlogN 5如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。3)如果有索引，则统计每个表的索引信息，对于每个索引：1先找到这个索引对应的列号，再找到对应的能用到（操作符必须为=或者是 IN（）这个索引的WHERE 子句，如果没有找到，则退出对每个索引的循环，如果找到，则判断这个子句的操作符是什么，如果是=，那么没有附加的代价，如果是 IN（sub-select），那么估计它附加代价inMultiplier为 25，如果是 IN（list），那么附加代价就是N（N为 list的列数）。2再计
13、算总的代价和总的查询结果记录数和代价。3nRow=pProbe-aiRowEsti*inMultiplier;/*计算行数*/4cost=nRow*estLog(inMultiplier);/*统计代价*/5如果找不到操作符为=或者是 IN（）的子句，而是范围的查询，那么同样只好估计查询结果记录数为nRow/3，估计代价为 cost/3。6同样，如果此查询要求排序的话，再在上面的总代价上加上NlogN 7如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。4)通过上面的优化过程，可以得到对一个表查询的总代价（就是上面各个代价的总和），再对第二个表进行同样的操作，这样如此直到把FROM
14、子句中所有的表都计算出各自的代价，最后取最小的，这将作为嵌套循环的最内层，依次可以得到整个嵌套循环的嵌套顺序，此时正是最优的，达到了优化的目的。5)所以循环的嵌套顺序不一定是与FROM 子句中的顺序一致，因为在执行过程中会用索引优化来重新排列顺序。六、索引在 SQLite 中，有以下几种索引：1)单列索引名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 7 页 -2)多列索引3)唯一性索引4)对于声明为：INTEGER PRIMARY KEY的主键来说，这列会按默认方式排序，所以虽然在数据字典中没有对它生成索引，但它的功能就像个索引。所以如果在这个主键上在单独建立索引的话，这样既浪
15、费空间也没有任何好处。运用索引的注意事项：1)对于一个很小的表来说没必要建立索引2)在一个表上如果经常做的是插入更新操作，那么就要节制使用索引3)也不要在一个表上建立太多的索引，如果建立太多的话那么在查询的时候 SQLite 可能不会选择最好的来执行查询，一个解决办法就是建立聚蔟索引索引的运用时机：1)操作符：=、IN 等2)操作符 BETWEEN、LIKE、OR不能用索引，如 BETWEEN：SELECT*FROM mytable WHERE myfield BETWEEN 10 and 20;这时就应该将其转换成：此时如果在 myfield上有索引的话就可以用了，大大提高速度再如 LIKE：SELECT*FROM mytable WHERE myfield LIKE sql%;此时应该将它转换成：此时如果在 myfield上有索引的话就可以用了，大大提高速度再如 OR：SELECT*FROM mytable WHERE myfield=abc OR myfield=xyz;此时应该将它转换成：此时如果在 myfield上有索引的话就可以用了，大大提高速度 3)有些时候索引都是不能用的，这时就应该遍历全表（程序演示）名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 7 页 -