SQLite执行计划优化

本文地址：http://blog.csdn.net/mba16c35/article/details/60881716

翻译自：https://www.sqlite.org/optoverview.html#or_opt

本文档阐述了SQLite的执行计划和查询优化是如何工作的。

给定一个SQL语句，根据语句的复杂度和数据库schema，会有许多种甚至上千种实现该语句的方法。查询计划的作用就是，在这么多方法中选择一个IO和CPU耗费最少的实现方案。

1. WHERE子句分析

一个查询语句中的WHERE子句，其中由AND连接起来的子项会被拆开执行。如果WHERE子句是由OR操作符连接而成，那么整个子句会被当成单独一个子项，并应用OR-子句优化。

WHERE子句的所有子项都会被分析，是否可以满足使用索引的条件。一个子项必须满足以下的表达格式，才可用索引优化：

 column = expression
  column IS expression
  column > expression
  column >= expression
  column < expression
  column <= expression
  expression = column
  expression > column
  expression >= column
  expression < column
  expression <= column
  column IN (expression-list)
  column IN (subquery)
  column IS NULL

如果索引是这样建立的：

CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);

如果索引最开始的列(a,b,c,如此类推)是在WHERE子句的项中按序排列，那么这个索引就可以用于查询优化。而且WHERE子句中，索引最开始的列必须用  = 或者  IN 或者  IS 操作符。最右边的列才可以用不等式。对于要应用的索引中的最右一列，可以最多用两个不等式的上下限作为筛选条件。

WHERE子句的属于索引的列，不需要完全按序才能应用索引。不过，应用的索引的列之间不能有空缺。因此如果是上面的列子，如果WHERE子句中没有列c，列a和b可以应用索引，但是列d到z就不行了。类似的，位于被不等式限制的某列的右边索引列，都不能应用索引。（例外：skip-scan optimization）

在索引表达式的例子中，上述的索引列可以被替换成索引表达式（意味着CREATE INDEX语句的表达式的复制形式），其工作机制一样。

1.1 索引用法举例

对于上述的index和这样的WHERE子句：

... WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d='hello'

a,b,c,d 4列的index都是有用上的，因为这4列是index的前缀而且是相等约束(equality constraints).

而对于这样的WHERE子句：

... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello'

只有a,b,c的index是有用到的。d列的index没有用上，因为d位于c的右边，而且c是被不等式约束的。

对于：

... WHERE a=5 AND b IN (1,2,3) AND d='hello'

只有a和b的index是有用的。d列的index没有用到，因为c没有被约束，而索引用到的列必须是在声明中是连续的。

对于：

... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello'

这句完全没有用到index，因为最左边的列（列"a"）没有被约束。假设没有其他索引，上述的查询使用的是全表扫描。

对于：

... WHERE a=5 OR b IN (1,2,3) OR c NOT NULL OR d='hello'

上面的查询也是没有用到index的，因为WHERE子句由or相连。但是如果还有另外3个索引包含b,c和d作为最左列，就可以应用OR-子句优化策略。

2 BETWEEN 优化

如果WHERE子句是以下形式：

expr1 BETWEEN expr2 AND expr3

会像这样增加两个虚拟子项：

expr1 >= expr2 AND expr1 <= expr3

虚拟子项是用于分析的，不会产生任何VDBE代码。