The SQLite Query Optimizer Overview

本文档概述了SQLite的查询规划器和优化器的工作原理。

给定一条SQL语句，根据语句本身和底层数据库模式的复杂性，可能会有几十种，几百种甚至几千种方法来实现该语句。查询计划者的任务是从许多选择中选择一种算法，以最少的磁盘I/O和CPU开销提供答案。

索引教程文档中提供了其他背景信息。

在版本3.8.0中，SQLite查询规划器被重新实现为下一代查询规划器或“NGQP”。本文档中描述的所有功能，技术和算法均适用于3.8.0之前的旧版查询规划器和NGQP。有关NGQP与传统查询规划人员不同之处的进一步信息，请参阅NGQP的详细说明。

1.0 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等）以WHERE子句条款出现，则可以使用该索引。索引的初始列必须与=or IN或者IS运算符一起使用。最右边的列可以使用不等式。对于使用的索引的最右边一列，最多可能有两个不等式，这两个不等式必须将列的允许值夹在两个极值之间。

索引的每一列都不需要出现在WHERE子句中，以便使用该索引。但是在索引的列中不能有空位。因此，对于上面的示例索引，如果没有约束列c的WHERE子句项，那么约束列a和b的项可以与索引一起使用，但不能使用约束列d到z的项。同样，如果索引列位于仅受不等式限制的列右侧，通常不会用于索引列（用于索引目的）。（有关例外，请参阅下面的跳过扫描优化。）

在表达式索引的情况下，只要前面的文本中使用了“column”这个词，就可以用“索引表达式”（意思是出现在CREATE INDEX语句中的表达式的一个副本）替代，并且所有内容都可以工作。

1.1索引术语用法示例

对于上面的索引和WHERE子句，如下所示：

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

索引的前四列a，b，c和d将是可用的，因为这四列形成索引的前缀并且全部受到等式约束的约束。

对于上面的索引和WHERE子句，如下所示：

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

只有索引列a，b和c可用。d列不可用，因为它出现在c的右侧，并且c仅受不等式约束。

对于上面的索引和WHERE子句，如下所示：

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

只有索引的列a和b可用。d列将不可用，因为列c不受限制，并且索引可使用的列集中不存在空位。

对于上面的索引和WHERE子句，如下所示：

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

该索引根本不可用，因为索引的最左列（列“a”）不受限制。假设没有其他索引，上面的查询将导致全表扫描。

对于上面的索引和WHERE子句，如下所示：

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

该索引不可用，因为WHERE子句条款通过OR连接而不是AND连接。此查询将导致全表扫描。但是，如果添加了包含列b，c和d作为其最左列的三个附加索引，则可能会应用OR子句优化。

2.0 BETWEEN优化

如果WHERE子句的术语具有以下形式：

  expr1 BETWEEN expr2 AND expr3

然后添加两个“虚拟”术语，如下所示：

  expr1 >= expr2 AND expr1 <= expr3

虚拟术语仅用于分析，不会产生任何字节码。如果两个虚拟术语最终被用作索引的约束条件，则原始的BETWEEN术语将被省略，并且相应的测试不会在输入行上执行。因此，如果BETWEEN项最终被用作索引约束，那么在该项上不会执行任何测试。另一方面，虚拟术语本身从不会导致测试在输入行上执行。因此，如果BETWEEN项不用作索引约束，而是必须用于测试输入行，则expr1表达式只会计算一次。

3.0或优化

通过OR连接而不是AND的WHERE子句约束可以通过两种不同的方式来处理。如果一个术语由包含公共列名称的多个子条目组成，并由OR分隔，如下所示：

  column = expr1 OR column = expr2 OR column = expr3 OR ...

然后该术语被改写如下：

  column IN (expr1,expr2,expr3,...)

重写后的术语可能会继续使用IN运营商的常规规则来约束索引。请注意，列必须是每个OR连接的子项中的同一列，但列可以出现在=操作符的左侧或右侧。

当且仅当先前描述的OR转换为IN运算符不起作用时，才尝试第二个OR子句优化。假设OR子句由多个子项组成，如下所示：

  expr1 OR expr2 OR expr3

单独的子项可能是单个比较表达式a=5，x>y或者它们可以是LIKE或BETWEEN表达式，或者子项可以是带连接子子项的带括号的列表。对每个子项进行分析，就好像它本身是整个WHERE子句一样，以查看子项是否可以自行索引。如果OR子句的每个子项都可单独编制索引，则OR子句可能被编码，以便使用单独的索引来评估OR子句的每个项。一种思考SQLite如何为每个OR子句术语使用单独索引的方法是想象WHERE子句的重写如下：

  rowid IN (SELECT rowid FROM table WHERE expr1
            UNION SELECT rowid FROM table WHERE expr2
            UNION SELECT rowid FROM table WHERE expr3)

上面重写的表达是概念性的; 包含OR的WHERE子句并没有真正用这种方式重写。OR子句的实际实现使用更高效的机制，即使对于无法访问“rowid”的WITHOUT ROWID表或表也无效。但是实现的本质由上面的语句捕获：单独的索引用于从每个OR子句项中查找候选结果行，最终结果是这些行的联合。

请注意，在大多数情况下，SQLite将只对查询的FROM子句中的每个表使用单个索引。这里描述的第二个OR子句优化是该规则的例外。对于OR子句，OR子句中的每个子子句可以使用不同的索引。

对于任何给定的查询，可以使用此处描述的OR子句优化的事实并不保证它将被使用。SQLite使用基于成本的查询计划器来估计各种竞争查询计划的CPU和磁盘I / O成本，并选择它认为最快的计划。如果WHERE子句中有许多OR术语，或者单个OR-子句子项的某些索引不是非常有选择性的，那么SQLite可能会决定使用不同的查询算法甚至全表扫描更快。应用程序开发人员可以在语句中使用EXPLAIN QUERY PLAN前缀来获取所选查询策略的高级概述。

4.0 LIKE优化

使用LIKE或GLOB运算符的WHERE子句有时可以与索引一起使用来执行范围搜索，就好像LIKE或GLOB是BETWEEN运算符的替代物一样。这种优化有许多条件：

LIKE或GLOB的右侧必须是字符串文字或绑定到不以通配符开头的字符串文字的参数。
通过在左侧使用数值（而不是字符串或blob），不可能使LIKE或GLOB操作符为真。这意味着：
1. LIKE或GLOB运算符的左侧是具有TEXT关联性的索引列的名称，或
2. 右侧图案参数不以减号（“ - ”）或数字开头。

这种约束是由于数字不按字典顺序排列的事实而产生的。例如：9 <10但是'9'>'10'。

用于实现LIKE和GLOB的内置函数不能使用sqlite3_create_function（）API重载。
对于GLOB操作符，列必须使用内置的BINARY整理序列进行索引。
对于LIKE操作符，如果启用了case_sensitive_like模式，那么该列必须使用BINARY整理序列进行索引，或者如果case_sensitive_like模式被禁用，则该列必须使用内置的NOCASE整理序列进行索引。
如果使用ESCAPE选项，则ESCAPE字符必须是ASCII或UTF-8中的单字节字符。

LIKE运算符有两种可以通过编译指示设置的模式。默认模式是LIKE比较对latin1字符的大小写不敏感。因此，默认情况下，以下表达式为真：

  'a' LIKE 'A'

但是，如果启用case_sensitive_like编译指示，如下所示：

  PRAGMA case_sensitive_like=ON;

然后，LIKE操作员关注案例，上面的例子将评估为false。请注意，不区分大小写仅适用于latin1字符 - 基本上是ASCII的低位127字节代码中英文的大小写字母。国际字符集在SQLite中区分大小写，除非提供应用程序定义的整理序列和like（）SQL函数来考虑非ASCII字符。但是，如果提供了应用程序定义的整理序列和/或like（）SQL函数，那么此处描述的LIKE优化将不会被采用。

LIKE运算符在默认情况下不区分大小写，因为这是SQL标准所要求的。您可以在编译时使用编译器的SQLITE_CASE_SENSITIVE_LIKE命令行选项来更改缺省行为。

如果操作符左侧命名的列使用内置的BINARY整理序列进行索引，并且case_sensitive_like处于打开状态，则可能会发生LIKE优化。或者，如果使用内置的NOCASE整理序列对该列进行索引并且case_sensitive_like模式处于关闭状态，则可能会发生优化。这是LIKE运营商将优化的唯一两种组合。

GLOB运算符始终区分大小写。GLOB操作符左侧的列必须始终使用内置的BINARY整理序列，否则不会尝试使用索引优化该操作符。

只有在GLOB或LIKE运算符的右侧是文字字符串或已绑定到字符串文字的参数时，才会尝试LIKE优化。字符串文字不能以通配符开头; 如果右侧以通配符开始，则尝试进行该优化。如果右侧是一个绑定到字符串的参数，那么只有在包含表达式的预处理语句使用sqlite3_prepare_v2（）或sqlite3_prepare16_v2（）进行编译时才会尝试优化。如果右侧是参数，并且使用sqlite3_prepare（）或sqlite3_prepare16（）准备了语句，则不会尝试LIKE优化。

假设LIKE或GLOB运算符右侧的非通配符的初始序列是x。我们使用单个字符表示这个非通配符前缀，但读者应该明白，前缀可以包含多个1个字符。设y是与/ x /长度相同的最小字符串，但它比x大。例如，如果X是hello然后ý会hellp。LIKE和GLOB优化包括添加两个像这样的虚拟术语：

  column >= x AND column < y

在大多数情况下，即使虚拟术语用于约束索引，原始的LIKE或GLOB运算符仍然针对每个输入行进行测试。这是因为我们不知道字符对x前缀右侧可能施加的其他约束。但是，如果x的右侧只有一个全局通配符，则原始的LIKE或GLOB测试将被禁用。换句话说，如果模式是这样的：

  column LIKE x%
  column GLOB x*

那么当虚拟术语约束索引时，原始的LIKE或GLOB测试被禁用，因为在那种情况下，我们知道索引选择的所有行将通过LIKE或GLOB测试。

请注意，如果LIKE或GLOB运算符的右侧是参数，并且使用sqlite3_prepare_v2（）或sqlite3_prepare16_v2（）准备语句，则会在每次运行的第一个sqlite3_step（）调用时自动重新编译和重新编译语句自上一次运行以来，绑定到右侧的参数已经更改。重新编译和重新编译基本上是在模式更改后发生的相同操作。重新编译是必要的，以便查询规划器可以检查绑定到LIKE或GLOB运算符右侧的新值，并确定是否使用上述优化。

5.0跳过扫描优化

一般规则是索引只在索引最左边的列存在WHERE子句约束时才有用。但是，在某些情况下，即使索引的前几列从WHERE子句中省略，但后面的列也包含在内，SQLite仍可以使用索引。

考虑一下如下的表格：

  CREATE TABLE people(
    name TEXT PRIMARY KEY,
    role TEXT NOT NULL,
    height INT NOT NULL, -- in cm
    CHECK( role IN ('student','teacher') )
  );
  CREATE INDEX people_idx1 ON people(role, height);

人员表对于大型组织中的每个人都有一个条目。每个人都是“学生”或“教师”，由“角色”字段确定。我们记录每个人的厘米高度。角色和身高被编入索引。请注意，索引最左边的列不是很有选择性 - 它只包含两个可能的值。

现在考虑一个查询来查找组织中身高180cm或更高的每个人的姓名：

  SELECT name FROM people WHERE height>=180;

因为索引最左边的列没有出现在查询的WHERE子句中，所以有人试图断定索引在这里不可用。但SQLite能够使用索引。从概念上讲，SQLite使用索引就好像查询更像下面这样：

  SELECT name FROM people
   WHERE role IN (SELECT DISTINCT role FROM people)
     AND height>=180;

或这个：

  SELECT name FROM people WHERE role='teacher' AND height>=180
  UNION ALL
  SELECT name FROM people WHERE role='student' AND height>=180;

上面显示的替代查询公式只是概念。SQLite并没有真正转换查询。实际的查询计划是这样的：SQLite查找“角色”的第一个可能的值，它可以通过将“people_idx1”索引倒退到头并读取第一条记录来完成。SQLite将这个第一个“角色”值存储在一个内部变量中，我们将在这里称之为“$ role”。然后SQLite运行一个查询，如：“SELECT name FROM people WHERE role = $ role AND height> = 180”。此查询在索引最左边的列上有一个等式约束，因此索引可用于解析该查询。一旦查询完成，SQLite将使用“people_idx1”索引来查找“角色”列的下一个值，使用的代码与逻辑上类似于“

我们将这种索引用法称为“跳过扫描”，因为数据库引擎基本上是对索引进行全面扫描，但它会通过偶尔跳到下一个候选值来优化扫描（使其小于“全部”）。

如果SQLite知道第一个或多个列包含许多重复值，SQLite可能会对索引使用跳过扫描。如果索引最左边的列中的重复项太少，那么简单地前进到下一个值会更快，从而执行全表扫描，而不是在索引上执行二进制搜索以找到下一个左列值。

SQLite可以知道索引的最左边的列有多个重复的唯一方法是如果ANALYZE命令已在数据库上运行。如果没有ANALYZE的结果，SQLite必须猜测表中数据的“形状”，而默认猜测是索引最左列中每个值的平均值为10个重复项。但是，当重复次数大约为18或更多时，跳过扫描只会变得有利可图（它只会比全表扫描更快）。因此，在未分析的数据库上不会使用跳过扫描。

6.0加入

在上述1.0段落中的WHERE子句分析之前，内部连接的ON和USING子句被转换为WHERE子句的附加条款。因此，对于SQLite，使用较旧的SQL89逗号连接语法的新SQL92连接语法没有计算优势。他们最终在内部连接上完成了完全相同的事情。

对于左外连接来说，情况更复杂。以下两个查询是不相同的：

  SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.y;
  SELECT * FROM tab1 LEFT JOIN tab2 WHERE tab1.x=tab2.y;

对于内连接，上面的两个查询将是相同的。但是，特殊处理适用于OUTER连接的ON和USING子句：具体而言，如果连接的右表位于空行上，则ON或USING子句中的约束不适用，但约束确实应用于WHERE子句中。最终结果是，将ON或USING子句表达式置于WHERE子句中的LEFT JOIN有效地将查询转换为普通的INNER JOIN - 尽管是一个内部联接，运行速度更慢。

6.1连接中的表的顺序

SQLite的当前实现只使用循环连接。也就是说，连接被实现为嵌套循环。

连接中嵌套循环的默认顺序是FROM子句中最左边的表格构成外部循环，最右边的表格构成内部循环。但是，如果这样做会帮助它选择更好的索引，SQLite将以不同的顺序嵌套这些循环。

内部连接可以自由地重新排序。然而，左外连接既不交换也不关联，因此不会被重新排序。如果优化器认为有利但外连接总是按它们出现的顺序进行评估，则可以重新排序外连接左侧和右侧的内部连接。

SQLite专门处理CROSS JOIN运算符。理论上，CROSS JOIN算子是可交换的。但SQLite选择永不重新排列CROSS JOIN中的表。这提供了一种机制，程序员可以强制SQLite选择特定的循环嵌套顺序。

在连接中选择表的顺序时，SQLite使用高效的多项式时间算法。正因为如此，SQLite能够在几微秒内用50或60路连接来规划查询。

连接重新排序是自动的，通常工作得很好，程序员不必考虑它，尤其是如果使用ANALYZE来收集有关可用索引的统计信息。但偶尔需要程序员的一些提示。例如，考虑以下模式：

  CREATE TABLE node(
     id INTEGER PRIMARY KEY,
     name TEXT
  );
  CREATE INDEX node_idx ON node(name);
  CREATE TABLE edge(
     orig INTEGER REFERENCES node,
     dest INTEGER REFERENCES node,
     PRIMARY KEY(orig, dest)
  );
  CREATE INDEX edge_idx ON edge(dest,orig);

上面的模式定义了一个有向图，能够在每个节点上存储一个名称。现在考虑一下针对这个模式的查询：

  SELECT *
    FROM edge AS e,
         node AS n1,
         node AS n2
   WHERE n1.name = 'alice'
     AND n2.name = 'bob'
     AND e.orig = n1.id
     AND e.dest = n2.id;

此查询要求提供有关从标记为“alice”的节点到标记为“bob”的节点的所有信息。SQLite中的查询优化器在如何实现这个查询方面基本上有两种选择。（实际上有6种不同的选择，但我们只会在这里考虑其中的两种。）下面的伪代码展示了这两种选择。

Option 1:

  foreach n1 where n1.name='alice' do:
    foreach n2 where n2.name='bob' do:
      foreach e where e.orig=n1.id and e.dest=n2.id
        return n1.*, n2.*, e.*
      end
    end
  end

Option 2:

  foreach n1 where n1.name='alice' do:
    foreach e where e.orig=n1.id do:
      foreach n2 where n2.id=e.dest and n2.name='bob' do:
        return n1.*, n2.*, e.*
      end
    end
  end

在两个实现选项中，使用相同的索引来加速每个循环。这两个查询计划的唯一区别是嵌套循环的顺序。

那么哪个查询计划更好？事实证明，答案取决于在节点和边缘表中找到哪种数据。

设爱丽丝节点的数量为M，并且节点的数量为N.考虑两种情况。在第一种情况下，M和N都是2，但每个节点上有数千条边。在这种情况下，选项1是首选。使用选项1，内部循环检查一对节点之间是否存在边缘，如果找到则输出结果。但是因为每个节点只有2个爱丽丝和鲍勃节点，所以内部循环只需要运行4次，查询速度非常快。选项2在这里需要更长的时间。选项2的外部循环只执行两次，但由于离开每个alice节点的边数很多，所以中间循环必须重复数千次。它会慢很多。所以在第一种情况下，我们更愿意使用选项1。

现在考虑M和N都是3500的情况。Alice节点很丰富。但是，假设每个节点仅由一个或两个边连接。在这种情况下，选项2是首选。使用选项2时，外部循环仍然需要运行3500次，但中间循环只对每个外部循环运行一次或两次，而对于每个中间循环，内部循环只运行一次（如果有的话）。因此，内部循环的迭代总次数约为7000次。另一方面，选项1必须每次运行外部循环和中间循环3500次，导致中间循环的1200万次迭代。因此，在第二种情况下，选项2比选项1快近2000倍。

因此，您可以看到，根据数据在表中的结构，查询计划1或查询计划2可能会更好。SQLite默认选择哪个计划？从版本3.6.18开始，如果不运行ANALYZE，SQLite将选择选项2.但是，如果为了收集统计信息而运行ANALYZE命令，如果统计信息表明备选可能运行得更快，则可能会做出不同的选择。

6.2使用SQLITE_STAT表手动控制查询计划

SQLite为高级程序员提供了对优化器选择的查询计划的控制权。执行此操作的一种方法是在sqlite_stat1，sqlite_stat3和/或sqlite_stat4表中添加ANALYZE结果。除了下一段描述的情况外，不建议采用这种方法。

对于使用SQLite数据库作为其应用程序文件格式的程序，首次创建新数据库实例时，ANALYZE命令无效，因为数据库不包含收集统计信息的数据。在这种情况下，可以在开发过程中构建一个包含典型数据的大型原型数据库，并在此原型数据库上运行ANALYZE命令来收集统计信息，然后将原型统计信息保存为应用程序的一部分。部署完成后，当应用程序创建新的数据库文件时，它可以运行ANALYZE命令以创建统计表，然后将从原型数据库中获得的预先计算的统计数据复制到这些新的统计表中。这样，来自大型工作数据集的统计信息可以预先加载到新创建的应用程序文件中。

6.3使用CROSS JOIN手动控制查询计划

程序员可以通过使用CROSS JOIN运算符而不是JOIN，INNER JOIN，NATURAL JOIN或“，”连接来强制SQLite使用特定的循环嵌套顺序进行连接。尽管CROSS JOIN在理论上是可交换的，但SQLite选择从不对CROSS JOIN中的表进行重新排序。因此，CROSS JOIN的左表将始终在相对于右表的外部循环中。

在以下查询中，优化程序可以自由地对FROM子句的表进行重新排序，无论它认为合适：

  SELECT *
    FROM node AS n1,
         edge AS e,
         node AS n2
   WHERE n1.name = 'alice'
     AND n2.name = 'bob'
     AND e.orig = n1.id
     AND e.dest = n2.id;

但是，在以下相同查询的逻辑等效表述中，“”的“CROSS JOIN”的替换意味着表的顺序必须是N1，E，N2。

  SELECT *
    FROM node AS n1 CROSS JOIN
         edge AS e CROSS JOIN
         node AS n2
   WHERE n1.name = 'alice'
     AND n2.name = 'bob'
     AND e.orig = n1.id
     AND e.dest = n2.id;

在后一个查询中，查询计划必须是选项2.请注意，您必须使用关键字“CROSS”来禁用表格重新排序优化; INNER JOIN，NATURAL JOIN，JOIN和其他类似组合的工作方式就像逗号连接一样，优化程序可以自由地对表格进行适当的重新排序。（在外连接上也禁用表重排序，但这是因为外连接不是关联或可交换的。在OUTER JOIN中重新排序表会改变结果。）

有关使用CROSS JOIN手动控制连接的嵌套顺序的另一实际示例，请参阅“化石NGQP升级案例研究”。稍后在同一文档中找到的查询规划人员清单提供了有关查询规划人员手动控制的进一步指导。

7.0在多个索引之间进行选择

查询的FROM子句中的每个表最多可以使用一个索引（OR子句优化起作用时除外），并且SQLite会努力在每个表上至少使用一个索引。有时候，两个或更多的索引可能会在单个表上使用。例如：

  CREATE TABLE ex2(x,y,z);
  CREATE INDEX ex2i1 ON ex2(x);
  CREATE INDEX ex2i2 ON ex2(y);
  SELECT z FROM ex2 WHERE x=5 AND y=6;

对于上面的SELECT语句，优化器可以使用ex2i1索引查找包含x = 5的ex2行，然后针对y = 6项测试每行。或者，它可以使用ex2i2索引查找包含y = 6的ex2行，然后根据x = 5项测试每个行。

当面对两个或更多索引的选择时，SQLite会尝试使用每个选项来估计执行查询所需的总工作量。然后它选择提供最少估计工作的选项。

为了帮助优化程序更精确地估计使用各种索引所涉及的工作，用户可以选择运行ANALYZE命令。ANALYZE命令扫描数据库中所有可能在两个或更多索引之间进行选择的索引，并收集这些索引选择性的统计数据。通过此扫描收集的统计信息存储在特殊数据库表名称中，显示名称全部以“ sqlite_stat ” 开头。这些表格的内容不会随着数据库的更改而更新，因此在进行重大更改之后，重新运行ANALYZE可能会比较谨慎。ANALYZE命令的结果仅适用于在ANALYZE命令完成后打开的数据库连接。

各种sqlite_stat N表包含有关各种索引如何选择的信息。例如，sqlite_stat1表可能指示列x上的等式约束将搜索空间平均减少至10行，而列y上的相等约束将搜索空间平均减少至3行。在这种情况下，SQLite宁愿使用索引ex2i2，因为该索引更具选择性。

7.1取消资格WHERE子句条款使用一元 - “+”

通过将一个一元运算+符添加到列名称中，可以手动取消对WHERE子句中的术语进行索引使用。一元+是无操作，并且不会在预准备语句中生成任何字节码。但是一元运算+符会阻止该术语限制索引。所以，在上面的例子中，如果查询被重写为：

  SELECT z FROM ex2 WHERE +x=5 AND y=6;

在+对操作x栏将阻止来自任期限制的指数。这会强制使用ex2i2索引。

请注意，一元运算+符还会从表达式中移除类型关联，并且在某些情况下，这会导致表达式的含义发生微妙变化。在上面的例子中，如果列x有TEXT关联，那么比较“x = 5”将以文本形式完成。但+操作员删除亲和力。因此，比较“+ x = 5”将比较列中的文本x与数字值5并始终为假。

7.2范围查询

考虑一个稍微不同的场景：

  CREATE TABLE ex2(x,y,z);
  CREATE INDEX ex2i1 ON ex2(x);
  CREATE INDEX ex2i2 ON ex2(y);
  SELECT z FROM ex2 WHERE x BETWEEN 1 AND 100 AND y BETWEEN 1 AND 100;

进一步假设x列包含的值介于0和1,000,000之间，而列y包含的值介于0和1,000之间。在这种情况下，列x上的范围约束应该将搜索空间缩小10,000倍，而列y上的范围约束应该只将搜索空间缩小10倍。因此，ex2i1索引应该是首选。

SQLite会做出这个决定，但前提是它已经用SQLITE_ENABLE_STAT3或SQLITE_ENABLE_STAT4进行了编译。SQLITE_ENABLE_STAT3和SQLITE_ENABLE_STAT4选项使ANALYZE命令收集sqlite_stat3或sqlite_stat4表中列内容的直方图，并使用此直方图更好地猜测用于范围约束的最佳查询，如上所述。STAT3和STAT4的主要区别在于STAT3仅记录索引最左列的直方图数据，而STAT4记录索引所有列的直方图数据。对于单列索引，STAT3和STAT4的工作原理相同。

直方图数据仅在约束的右侧是简单的编译时常量或参数而不是表达式时才有用。

直方图数据的另一个限制是它只适用于索引最左边的列。考虑这种情况：

  CREATE TABLE ex3(w,x,y,z);
  CREATE INDEX ex3i1 ON ex2(w, x);
  CREATE INDEX ex3i2 ON ex2(w, y);
  SELECT z FROM ex3 WHERE w=5 AND x BETWEEN 1 AND 100 AND y BETWEEN 1 AND 100;

这里的不等式在列x和y上，它们不是最左边的索引列。因此，没有收集最左列指数的直方图数据在帮助在列x和y上的范围约束之间进行选择时是无用的。

8.0覆盖索引

在对一行进行索引查找时，通常的做法是在索引上执行二进制搜索以查找索引条目，然后从索引中提取rowid并使用该rowid在原始表上执行二进制搜索。因此，典型的索引查找包含两个二进制搜索。但是，如果从表中提取的所有列在索引中已经可用，则SQLite将使用索引中包含的值，并且永远不会查找原始表格行。这为每行节省了一次二进制搜索，并且可以使许多查询以两倍的速度运行。

当一个索引包含查询所需的所有数据，以及不需要查询原始表时，我们称该索引为“覆盖索引”。

9.0 ORDER BY优化

在可能的情况下，SQLite尝试使用索引来满足查询的ORDER BY子句。当面对使用索引来满足WHERE子句约束或满足ORDER BY子句的选择时，SQLite将执行上述相同的成本分析，并选择它认为会产生最快答案的索引。

SQLite还会尝试使用索引来帮助满足GROUP BY子句和DISTINCT关键字。如果可以排列连接的嵌套循环，使得对于GROUP BY或DISTINCT等同的行是连续的，则GROUP BY或DISTINCT逻辑可以确定当前行是否是同一组的一部分，或者当前行行通过将当前行与前一行进行比较而不同。这可以比将每一行与所有先前行进行比较的替代方法快得多。

9.1通过索引的部分ORDER BY

如果查询包含带有多个词的ORDER BY子句，则可能是SQLite可以使用索引来按照ORDER BY中某些词的前缀的顺序使行出来，但ORDER BY中后面的词不满足。在这种情况下，SQLite会阻止排序。假设ORDER BY子句有四个项，并且查询结果的自然顺序按照前两个项的顺序出现。由于每行由查询引擎输出并进入分拣机，因此当前行中与ORDER BY的前两项对应的输出将与前一行进行比较。如果它们已经改变，则当前排序完成并输出并开始新的排序。这会导致稍微更快的排序。但更大的优点是需要将更少的行存储在内存中，从而减少内存需求，

10.0子查询展平

在SELECT的FROM子句中发生子查询时，最简单的行为是将子查询计算为瞬时表，然后针对瞬态表运行外部SELECT。但是这样的计划可能并不理想，因为瞬态表不会有任何索引，并且外部查询（可能是连接）将被迫在瞬态表上执行全表扫描。

为了克服这个问题，SQLite试图压扁SELECT的FROM子句中的子查询。这涉及将子查询的FROM子句插入到外部查询的FROM子句中，并在外部查询中重写表达式来引用子查询的结果集。例如：

  SELECT t1.a, t2.b FROM t2, (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5

将使用查询展平重写为：

  SELECT t1.x+t1.y AS a, t2.b FROM t2, t1 WHERE z<100 AND a>5

为了使查询展平，必须满足一系列条件。一些约束被斜体文本标记为过时。这些额外的约束保留在文档中以保留其他约束的编号。

（已过时。不再尝试对聚合子查询进行查询展平。）
（已过时。不再尝试对聚合子查询进行查询展平。）
子查询不是LEFT JOIN的正确操作数，那么子查询可能不是联接，子查询的FROM子句可能不包含虚拟表，并且外部查询可能不是聚合。
子查询不是DISTINCT。
（归入约束4）
（已过时。不再尝试对聚合子查询进行查询展平。）
子查询有一个FROM子句。
子查询不使用LIMIT或外部查询不是联接。
子查询不使用LIMIT或外部查询不使用聚合。
（限制于2005年放宽）
子查询和外部查询都不具有ORDER BY子句。
（归入约束3）
子查询和外部查询都不使用LIMIT。
子查询不使用OFFSET。
外部查询是复合选择的一部分，那么子查询可能没有LIMIT子句。
外部查询是一个聚合，那么子查询可能不包含ORDER BY。
子查询是一个复合SELECT，然后
1. 所有复合运算符都必须是UNION ALL，并且
2. 子查询化合物的任何术语都不可能是聚合的或不同的，并且
3. 子查询中的每个术语都必须有一个FROM子句，并且
4. 外部查询可能不是聚合，DISTINCT查询或连接。

父母和子查询可能包含WHERE子句。根据规则（11），（12）和（13），它们也可能包含ORDER BY，LIMIT和OFFSET子句。

如果子查询是复合选择，则父级的ORDER by子句的所有项都必须是对子查询的列的简单引用。
如果子查询使用LIMIT，则外部查询可能没有WHERE子句。
（归入约束17d。）
子查询使用LIMIT，那么外部查询可能不是DISTINCT。
子查询可能不是递归CTE。
（归入约束17d。）
（已过时。不再尝试对聚合子查询进行查询展平。）

不期望随便读者理解或记住上面列表的任何部分。这个列表的要点是要证明是否要扁平查询的决定是复杂的。

使用视图时，查看展平是一个重要的优化，因为视图的每次使用都被转换为子查询。

11.0子查询联合例程

在较早版本的SQLite中，FROM子句中的子查询将被平铺到外部查询中，否则子查询将在外部查询开始之前运行到完成，子查询的结果集将存储在瞬时表中，然后在外部查询中使用瞬态表。较新版本的SQLite有第三种选择，即使用协同例程来实现子查询。

协程就像一个子程序，因为它与调用者在同一个线程中运行，并最终将控制权返回给调用者。不同之处在于一个协同例程在完成之前也有返回的能力，然后在下一次被调用时重新开始。

当子查询作为一个协程来实现时，会生成字节码来实现子查询，就好像它是一个独立查询一样，除了将结果行返回给应用程序之外，协程会将控制权返回给调用者计算每一行后。然后调用者可以使用该计算的一行作为其计算的一部分，然后在准备好下一行时再次调用该协程序。

联合例程比将子查询的完整结果集存储在临时表中更好，因为联合例程使用较少的内存。使用协同例程时，只需记住一行结果，而结果的所有行必须存储为一个瞬态表。另外，因为在外部查询开始工作之前，协程并不需要运行完成，所以输出的第一行可以更快地出现，并且如果整个查询被中止，则总体上完成的工作更少。

另一方面，如果子查询的结果必须被多次扫描（例如，因为它只是连接中的一个表），那么最好使用一个瞬态表来记住子查询的整个结果，在为了避免多次计算子查询。

11.1使用协同例程推迟排序后的工作

从SQLite版本3.21.0（2017-10-24）开始，查询规划器将始终使用协同例程来实现包含ORDER BY子句且不属于联接的FROM子句子查询。此功能使应用程序能够将分拣机之前的昂贵计算转移到分拣机之后，从而可以加快操作速度。例如，考虑这个查询：

  SELECT expensive_function(a) FROM tab ORDER BY date DESC LIMIT 5;

此查询的目标是为表中最近五个条目计算一些值。但是在上面的查询中，在排序之前调用“expensive_function（）”，因此在表的每一行上调用“expensive_function（）”，甚至由于LIMIT子句最终被省略的行也被调用。一个共同的例程可以用来解决这个问题：

  SELECT expensive_function(a) FROM (
    SELECT a FROM tab ORDER BY date DESC LIMIT 5
  );

在修改的查询中，由协同例程实现的子查询计算“a”的最近五个值。这五个值从协同例程传递到外部查询中，其中仅在应用程序关心的特定行上调用“expensive_function（）”。

未来SQLite版本中的查询规划器可能会变得非常聪明，可以自动地在两个方向上进行上述转换。也就是说，未来版本的SQLite可能会将第一种形式的查询转换为第二种，或者将第二种查询写入第一种。但是现在，SQLite实现了上面每个查询的写法。

12.0 The MIN/MAX optimization

通过执行单个索引查找而不是通过扫描整个表，可以满足包含单个MIN（）或MAX（）聚合函数（其参数是索引最左列）的查询。例子：

  SELECT MIN(x) FROM table;
  SELECT MAX(x)+1 FROM table;

13.0自动索引

当没有可用的索引来帮助评估查询时，SQLite可能会创建一个自动索引，该索引仅持续一个SQL语句的持续时间。由于构建自动索引的成本是O（NlogN）（其中N是表中的条目数），并且执行全表扫描的成本仅为O（N），所以只有在SQLite时才会创建自动索引期望在SQL语句的过程中查找运行次数超过logN次。考虑一个例子：

  CREATE TABLE t1(a,b);
  CREATE TABLE t2(c,d);
  -- Insert many rows into both t1 and t2
  SELECT * FROM t1, t2 WHERE a=c;

在上面的查询中，如果t1和t2都有大约N行，那么没有任何索引，查询将需要O（N * N）时间。另一方面，在表t2上创建索引需要O（NlogN）时间，然后使用该索引评估查询需要额外的O（NlogN）时间。在没有ANALYZE信息的情况下，SQLite推测N是100万，因此它认为构建自动索引将是更便宜的方法。

自动索引也可能用于子查询：

  CREATE TABLE t1(a,b);
  CREATE TABLE t2(c,d);
  -- Insert many rows into both t1 and t2
  SELECT a, (SELECT d FROM t2 WHERE c=b) FROM t1;

在此示例中，t2表用于子查询来转换t1.b列的值。如果每个表包含N行，SQLite预计子查询将运行N次，因此它会相信在t2上首先构造自动临时索引并使用该索引满足子查询的N个实例会更快。

自动索引功能可以在运行时使用automatic_index附注禁用。自动索引默认情况下处于打开状态，但可以更改，以便默认情况下使用SQLITE_DEFAULT_AUTOMATIC_INDEX编译时选项关闭自动索引。通过编译SQLITE_OMIT_AUTOMATIC_INDEX编译时选项，可以完全禁用创建自动索引的功能。

在SQLite 版本3.8.0（2013-08-26）及更高版本中，每次准备使用自动索引的语句时，都会向错误日志发送SQLITE_WARNING_AUTOINDEX消息。应用程序开发人员可以并应该使用这些警告来确定模式中对新持久索引的需求。

不要将自动索引与有时为实现PRIMARY KEY约束或UNIQUE约束而创建的内部索引（名称类似“sqlite_autoindex_ table _ N ”）混淆。这里描述的自动索引仅存在于单个查询期间，永远不会保存到磁盘，并且只对单个数据库连接可见。内部索引是PRIMARY KEY和UNIQUE约束实现的一部分，是持久的并且保存到磁盘，并且对所有数据库连接都可见。术语“autoindex”出于遗留原因出现在内部索引的名称中，并不表示内部索引和自动索引是相关的。

 SQLite is in the Public Domain.

本文档系腾讯云开发者社区成员共同维护，如有问题请联系 cloudcommunity@tencent.com

最后更新于：2019-12-27