SQLite是典型的嵌入式DBMS,它具有很多优点,它轻巧,编译后很小,其中一个原因就查询优化而言比较简单,它只是对索引机制进行优化使用,经过分析关于SQLite查询优化和源代码的研究,我将SQLite查询优化总结如下:
1.影响查询性能的因素:
1.检索表中的行数,越小越好
2.排序与否。
3.是否索引。
4.查询表单
\ Nbsp; 二,几种查询优化转换方法
1.对于单个表的单个列,如果存在TC = expr形式的子句,并且所有子句都由OR运算符连接,则形式为:x = expr1 OR expr2 = x OR x = expr3,不能为通过SQLite中的索引进行了优化,因此可以使用IN运算符将其转换为子句:x IN(expr1,expr2,expr3),以便可以使用索引进行优化,效果是显而易见的,但是如果全部没有索引, OR语句的效率将略高于IN语句的效率。
2.如果子句的运算符为BETWEEN,则不能在SQLite中使用索引进行优化,因此还必须执行相应的等效转换:例如:a BETWEEN b AND c可以转换为:(a BETWEEN b AND c )AND(a = a)和(a = c)。在上面的子句中,(a = = b)AND(a = c)将设置为dynamic并且是(a BETWEEN b AND c)的子句,则如果BETWEEN语句已被编码,该子句将被忽略无论如何,如果有一个可用的索引使该子句满足条件,则将忽略父句。
3.如果单元的操作员为LIKE,则将进行以下转换:x LIKE’abc%’,转换为:x \ gt; =’abc’AND x \\ ult26 lt; ‘abd’。由于无法通过索引优化SQLite中的LIKE,因此,如果有索引,则转换和非转换之间的差异非常大,因为它不适用于LIKE,但是如果没有索引,则LIKE仍然是术语效率。效率不如转换后。
\ Nbsp; 三,几种查询处理(复杂查询)的
语句
1.查询为:ORDER BY \ ORDER BY执行方法:是UNION ALL,UNION,EXCEPT或INTERSECT中的一种。该语句的执行过程是先执行selectA和selectB并对其进行排序,然后扫描两个结果,以上四个操作不同,执行过程分为七个子过程:
\ Nbsp; outA:将结果selectA的一行放入最终结果集中
\ Nbsp; outB:将结果selectA的一行插入到最终结果集中(仅运行UNION和UNION ALL操作,其他操作未放入最终结果集中)
AltB:当selectA的当前记录小于selectB的当前记录时
AeqB:当selectA的当前记录等于selectB的当前记录时
AgtB:当selectA的当前记录大于selectB的当前记录
EofA:遍历selectA的结果
\ Nbsp; EofB:遍历selectB的结果
\ Nbsp; 这是四个操作的执行:
代码:
”
UNION ALL
UNION
除
相交
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
停止
停止
EofB:
outA,nextA
outA,nextA
outA, nextA
停止
2.如果可能,可以将使用GROUP BY查询的语句转换为DISTINCT语句进行查询,因为有时索引可能用于GROUP BY,但索引不用于DISTINCT。 四个子查询拼合 示例:选择一个FROM(在x y上作为FROM t1,在z \\ ult26的地方)。 5 执行此SQL语句的一般默认方法是先执行内部查询,将结果放入临时表中,然后在此表上执行外部查询。这将需要两倍的数据处理。索引,因此,如果处理上述SQL,则无法优化外部查询。可以获取以下SQL语句:SELECT x + y AS a FROM t1 WHERE z \ lt; 100 AND \\ u>如图5所示,该结果显然与上述结果相同,但是此时仅需要查询一次数据,此外,如果在表t1上具有索引可以避免遍历整个表。 使用flatten方法优化SQL条件: \\ b 1.子查询和外部查询都不使用集合函数 2.子查询不使用set函数或外部查询不是表连接 3.子查询不是左外部联接右操作数 4.子查询不使用DISTINCT或外部查询不是表连接 5.子查询不使用DISTINCT或外部查询不使用集合函数 6.子查询不使用集合函数或外部查询不使用关键字DISTINCT 7.子查询具有FROM语句 8.子查询不使用LIMIT或外部查询不是表连接 9.子查询不使用LIMIT或外部查询不使用集合函数 10.子查询不使用集合函数或外部查询不使用LIMIT 11.子查询和外部查询都不都是ORDER BY子句 \\ ubsp; 12.子查询和外部查询均未使用LIMIT 13.子查询不使用OFFSET 14.外部查询不是复合查询的一部分,或者子查询未使用关键字ORDER BY和LIMIT 15.外部查询不使用set函数查询不包括ORDER BY \ nbsp; 16.复合子查询的拼合:子查询不是复合查询,或者它是UNION ALL复合查询,但是它由几个非集合函数查询组成,他的父查询不是复合查询的子查询,也不是它使用集合函数或DISTINCT查询,并且FROM语句中没有其他表或子查询。父查询和子查询可以包含Contains WHERE语句,但要遵守上面的11、12和13的条件。 代码: ”
选择a + 1 FROM(
选择x FROM选项卡
全部UNION
从选项卡选择y
全部UNIon
从tab2选择abs(z * 2)
)在哪! = 5 ORDER BY 1
转换为: 代码: ”
从选项卡中的x + 1中选择x + 1! = 5
全部联合
从选项卡中的y +1中选择y + 1! = 5
联合所有
选择abs(z * 2)+1从tab2那里abs(z * 2)+1! = 5
ORDER BY 1
复制代码 17.如果子查询是复合查询,则父查询的所有ORDER BY语句必须是对子查询列的简单引用 18.子查询不使用LIMIT或外部查询没有WHERE语句 子查询展平是通过特殊功能实现的,该功能是: 代码: ”
静态int flattenSubquery(
Parse * pParse,
选择* p,
int iFrom,
int isAgg,
int子查询IsAgg
)
它在Select.c文件中已实现。显然,对于更复杂的查询,如果通过拉平查询语句满足查询条件,则可以优化查询。如果有索引,效果会更好! 五,连接查询 返回查询结果之前,必须已连接相关表的每一行,在SQLite中,这是通过嵌套循环实现的。在早期版本中,最左边是最外层循环,最右边是最内层循环。连接两个或多个表时,如果有索引,请将Go放入内部循环,即,将其放在FROM的末尾,因为对于之前选择的每一行,如果找到与后面相对应的行,则有一个索引,它将很快,如果没有,它将遍历整个表,所以效率很低,但是在新版本中,已经实现了这种优化。 优化方法如下: 对于每个查询表,要计算该表上的索引信息,请首先将成本分配给SQLITE_BIG_DBL(系统定义的常量): 1.如果没有索引,请查找此表上是否存在针对rowid的查询条件: 如果存在Rowid = EXPR,如果存在,则返回此表的成本估算,成本为零,查询获得的记录数为1,并完成此表的估算成本。 如果没有Rowid = EXPR,但有rowid IN(…),并且IN是一个列表,则返回的记录数是IN列表中的元素数,估计成本为NlogN, 如果IN不是列表而是子查询结果,则特定子查询不能确定,因此只能估计一个值,返回的记录数为100,成本为200。 如果rowid是范围查询,则估计所有符合条件的记录占总记录的三分之一,总记录估计为1000000,并且估计成本也是记录数。 如果此查询也需要排序,则添加排序成本NlogN 如果此时获得的成本小于总成本,则更新总成本,否则不更新。 2,如果WHERE子句中有一个OR运算符,则应将这些OR所连接的所有子句分开并进行分析。 如果存在由AND连接器组成的子句,则分别分析由AND连接的子句。 如果connected子句的形式为X,则再次分析该子句。
下一步是计算整个”或”运算的总成本。
如果此查询需要排序,则将排序成本乘以NlogN
如果此时获得的成本小于总成本,则更新总成本,否则不更新。
\ Nbsp; 3,如果有索引,则为每个表,每个索引提供索引统计信息:
首先找到与该索引对应的列号,然后找到该索引的对应WHERE子句(如果可以使用的话)(运算符必须为=或IN(…))查找,然后为每个索引退出循环(如果找到) ,然后确定此子句的运算符是什么,如果为=,则没有附加成本,如果为IN(子选择),则在Multiplier中估算其附加成本对于25,如果为IN(列表),那么附加费用为N(N为列表中的列数)。
然后计算总成本以及查询结果记录和成本的总数。
nRow = pProbe-\ gt; aiRowEst * inMultiplier;
成本= nRow * estLog(inMultiplier);
如果找不到带有运算符=或IN(…)的子句,而是范围查询,则还必须将查询结果记录的数量估计为nRow/3,估计成本为cost/3。
同样,如果此查询需要排序,则将NlogN加到
以上的总费用中
如果此时获得的成本小于总成本,则更新总成本,否则不更新。
\ Nbsp; 4,通过优化上面的过程可以获得一个总考虑的
表查询
在第二个表上执行相同的操作,以便直到计算出FROM子句中的所有表各自的成本,并最终取最小值后,将其用作嵌入式。嵌套循环的最内层可以获取整个嵌套循环依次嵌套,这是目前最好的,达到了优化的目的。
如图5所示,因此循环的嵌套顺序不一定与FROM子句中的顺序相同,因为在执行过程中将使用索引优化来重新排列顺序。
\ Nbsp; 六,索引
在SQLite中,有以下索引:
1) 单列索引
2) 多列索引
3) 唯一性索引
4) 对于声明为:INTEGER PRIMARY KEY的主键,默认情况下将对该列进行排序,因此尽管它未在数据字典中建立索引,但其功能就像一个索引。因此,如果在此主键上单独创建索引,则将浪费空间并且没有好处。
使用索引的注意事项:
1) 对于小表,无需创建索引
2) 如果您经常在表上插入更新操作,则必须使用索引
3) 不要在表上创建太多索引,如果建立的索引太多,则SQLite可能不会选择在查询Query期间执行的最佳索引,一种解决方案是构建集群索引。
\ Nbsp; 使用机会指数:
1)运算符:=,\\,
2)BETWEEN,LIKE或OR运算符无法使用索引,
例如BETWEEN:SELECT * FROM mytable WHERE myfield位于10和20之间;
目前,应将其转换为:
SELECT * FROM mytable WHERE myfield \\ ugt26; = 10 AND myfield \\ ult26 lt; = 20;
如果此时myfield上存在索引,则可以使用它,从而大大提高了速度
另一个类似的示例:SELECT * FROM mytable WHERE myfield LIKE” sql%”;
此时应将其转换为:
SELECT * FROM mytable WHERE myfield \\ ugt26; =” sql” AND myfield \\ ult26; “平方米”;
如果此时myfield上存在索引,则可以使用它,从而大大提高了速度
另一个示例是OR:SELECT * FROM mytable WHERE myfield =” abc” OR myfield =” xyz”;
此时应将其转换为:
SELECT * FROM mytable WHERE myfield IN(” abc”,” xyz”);
如果此时myfield上存在索引,则可以使用它,从而大大提高了速度
\ Nbsp; 3)有时不使用索引,则应遍历整个表(表示程序)
代码:
“
SELECT *从mytable那里myfield%2 = 1;
SELECT * FROM mytable WHERE substr(myfield,0,1)=” w”;
SELECT * FROM mytable WHERE length(myfield)\ lt; 5;