一、简介

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

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

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

在 3.8.0 版 (2013-08-26) 中，SQLite 查询规划器被重新实现为下一代查询规划器或“NGQP”。本文档中描述的所有功能、技术和算法都适用于 3.8.0 之前的遗留查询规划器和 NGQP。有关 NGQP 与遗留查询规划器有何不同的更多信息，请参阅 NGQP 的详细描述。

2.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 子句项中，则可能会使用该索引。索引的初始列必须与=或IN或IS运算符一起使用。使用的最右边的列可以使用不等式。对于所使用的索引的最右边的列，最多可以有两个不等式，它们必须将列的允许值夹在两个极端之间。

索引的每一列都不必出现在 WHERE 子句术语中才能使用该索引。但是，所使用的索引的列中不能有间隙。因此，对于上面的示例索引，如果没有约束列 c 的 WHERE 子句术语，则约束列 a 和 b 的术语可以与索引一起使用，但不能用于约束列 d 到 z 的术语。类似地，如果索引列位于仅受不等式约束的列的右侧，则通常不会使用索引列（用于索引目的）。（有关例外情况，请参阅下面的跳过扫描优化。）

对于表达式上的索引，无论何时在前面的文本中使用“列”一词，都可以替换为“索引表达式”（意思是出现在CREATE INDEX 语句中的表达式的副本），一切都将相同。

2.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 子句优化。

3. BETWEEN优化

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


  expr1 BETWEEN expr2 AND expr3

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


  expr1 >= expr2 AND expr1 <= expr3

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

4.或优化

可以用两种不同的方式处理由 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 表达式，或者子项可以是 AND 连接的子子项的括号列表。每个子项都被分析，就好像它本身就是整个 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 子句的实际实现使用了一种更有效的机制，甚至适用于WITHOUT ROWID表或“rowid”不可访问的表。然而，上面的语句捕获了实现的本质：使用单独的索引从每个 OR 子句项中查找候选结果行，最终结果是这些行的并集。

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

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

5. LIKE优化

使用LIKE或GLOB运算符的 WHERE 子句术语有时可以与索引一起使用以进行范围搜索，几乎就好像 LIKE 或 GLOB 是BETWEEN 运算符的替代项一样。这个优化有很多条件：

LIKE 或 GLOB 的右侧必须是字符串文字或绑定到不以通配符开头的字符串文字的参数。
不能通过在左侧使用数值（而不是字符串或 blob）来使 LIKE 或 GLOB 运算符为真。这意味着：
1. LIKE 或 GLOB 运算符的左侧是具有TEXT affinity的索引列的名称，或者
2. 右侧模式参数不以减号（“-”）或数字开头。
此约束源于数字不按字典顺序排序的事实。例如：9<10 但 '9'>'10'。
不得使用sqlite3_create_function() API重载用于实现 LIKE 和 GLOB 的内置函数。
对于 GLOB 运算符，必须使用内置的 BINARY 整理序列对该列进行索引。
对于 LIKE 运算符，如果启用了case_sensitive_like模式，则该列必须使用 BINARY 整理序列进行索引，或者如果禁用case_sensitive_like模式，则该列必须使用内置的 NOCASE 整理序列进行索引。
如果使用 ESCAPE 选项，则 ESCAPE 字符必须是 ASCII，或 UTF-8 中的单字节字符。

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

'a' LIKE 'A'

如果启用 case_sensitive_like pragma，如下所示：

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()编译时才会尝试此优化。如果右侧是参数，则不会尝试 LIKE 优化并且该语句是使用 sqlite3_prepare()或sqlite3_prepare16()准备的。

假设 LIKE 或 GLOB 运算符右侧的非通配符的初始序列是x。我们使用单个字符来表示这个非通配符前缀，但读者应该理解前缀可以包含多个字符。设y为与 /x/ 长度相同但大于x的最小字符串。例如，如果x是 'hello'那么 y就是'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 ()准备语句时，如果自上次运行以来，与右侧参数的绑定发生了变化。这种重新分析和重新编译本质上与架构更改后发生的操作相同。重新编译是必要的，以便查询规划器可以检查绑定到 LIKE 或 GLOB 运算符右侧的新值，并确定是否采用上述优化。

6.跳过扫描优化

一般规则是索引只有在索引最左边的列上有 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);

people 表对大型组织中的每个人都有一个条目。每个人要么是“学生”，要么是“老师”，由“角色”字段决定。该表还记录了每个人的身高（以厘米为单位）。角色和高度被索引。请注意，索引最左边的列不是很有选择性——它只包含两个可能的值。

现在考虑查询以查找组织中身高 180 厘米或更高的每个人的姓名：

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 找到“role”的第一个可能值，它可以通过将“people_idx1”索引倒回到开头并读取第一条记录来完成。SQLite 将第一个“角色”值存储在一个内部变量中，我们将在此称为“$role”。然后 SQLite 运行如下查询：“SELECT name FROM people WHERE role=$role AND height>=180”。此查询在索引的最左边的列上有一个相等约束，因此索引可用于解析该查询。一旦该查询完成，SQLite 就会使用“people_idx1”索引来定位“role”列的下一个值，使用逻辑上类似于“SELECT role FROM people WHERE role>$role LIMIT 1”的代码。这个新的“role”值会覆盖 $role 变量，并且重复该过程，直到检查完“role”的所有可能值。

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

如果 SQLite 知道第一个或多个列包含许多重复值，它可能会对索引使用跳过扫描。如果索引最左边的列中的重复项太少，那么简单地前进到下一个值，从而进行全表扫描，比对索引进行二分查找来定位要快得多下一个左列值。

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

7.加入

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

对于 OUTER JOIN，情况更复杂。以下两个查询不等价：

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条款。最终效果是，将 LEFT JOIN 的 ON 或 USING 子句表达式放在 WHERE 子句中可以有效地将查询转换为普通的 INNER JOIN - 尽管内部联接运行得更慢。

7.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 中的查询优化器基本上有两种选择来实现这个查询。（实际上有六种不同的选择，但我们在这里只考虑其中两种。）下面的伪代码演示了这两种选择。

选项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

选项 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

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

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

设alice节点数为M，bob节点数为N。考虑两种场景。在第一种情况下，M 和 N 均为 2，但每个节点上有数千条边。在这种情况下，首选选项 1。对于选项 1，内部循环检查一对节点之间是否存在边，如果找到则输出结果。因为alice和bob节点各只有2个，所以内循环只需要跑四次，查询速度很快。选项 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命令以收集统计信息，如果统计信息表明备选方案可能运行得更快，则可能会做出不同的选择。

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

SQLite 为高级程序员提供了对优化器选择的查询计划进行控制的能力。一种方法是伪造 sqlite_stat1、 sqlite_stat3和/或sqlite_stat4表中的ANALYZE结果。大多数情况下不建议这样做。

7.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 手动控制连接嵌套顺序的另一个真实示例，请参阅“ Fossil NGQP 升级案例研究”。稍后在同一文档中找到的查询规划器清单提供了有关查询规划器手动控制的进一步指导。

8.在多个索引之间进行选择

查询的 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 索引来查找 ex2 中包含 x=5 的行，然后根据 y=6 项测试每一行。或者它可以使用 ex2i2 索引查找 ex2 中包含 y=6 的行，然后针对 x=5 项测试这些行中的每一行。

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

为了帮助优化器更准确地估计使用各种索引所涉及的工作，用户可以选择运行ANALYZE命令。ANALYZE命令扫描数据库的所有索引，其中可能有两个或多个索引之间的选择，并收集有关这些索引选择性的统计信息。此扫描收集的统计信息存储在特殊的数据库表中 names shows names all begin with " sqlite_stat ". 这些表的内容不会随着数据库的更改而更新，因此在进行重大更改后，重新运行ANALYZE可能是明智的。ANALYZE 命令的结果仅可用于在 ANALYZE 命令完成后打开的数据库连接。

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

8.1. 使用一元-“+”取消 WHERE 子句项的资格

通过在列名前加上一元+运算符，可以手动取消 WHERE 子句的条件以用于索引。一元+是空操作，不会在准备好的语句中生成任何字节代码。但是，一元+运算符将阻止该术语约束索引。因此，在上面的示例中，如果查询被重写为：

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

x列上的+运算符将防止该术语约束索引。这将强制使用 ex2i2 索引。

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

8.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 的范围约束之间进行选择是无用的。

9.覆盖索引

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

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

10. ORDER BY 优化

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

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

10.1. 通过索引的部分 ORDER BY

如果查询包含一个包含多个术语的 ORDER BY 子句，则可能是 SQLite 可以使用索引使行按照 ORDER BY 中术语的某些前缀的顺序出现，但不满足 ORDER BY 中后面的术语. 在这种情况下，SQLite 会进行块排序。假设 ORDER BY 子句有四个项，查询的自然顺序导致行按前两个项的顺序出现。当每一行由查询引擎输出并进入排序器时，当前行中对应于 ORDER BY 的前两项的输出将与前一行进行比较。如果它们发生变化，则当前排序结束并输出并开始新的排序。这导致排序稍微快一些。事件更大的优点是需要在内存中保存的行更少，

11.子查询展平

当 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 的右操作数，则
1. 子查询可能不是连接，并且
2. 子查询的 FROM 子句可能不包含虚拟表，并且
3. 外部查询可能不是聚合。
子查询不是 DISTINCT。
（包含在约束 4 中）
（已过时。不再尝试对聚合子查询进行查询展平。）
子查询有一个 FROM 子句。
子查询不使用 LIMIT 或外部查询不是连接。
子查询不使用 LIMIT 或外部查询不使用聚合。
（2005年放宽限制）
子查询和外部查询并不都具有 ORDER BY 子句。
（包含在约束 3 中）
子查询和外部查询并不都使用 LIMIT。
子查询不使用 OFFSET。
如果外部查询是复合选择的一部分，则子查询可能没有 LIMIT 子句。
如果外部查询是聚合，则子查询可能不包含 ORDER BY。
如果子查询是复合 SELECT，则
1. 所有复合运算符必须是 UNION ALL，并且
2. 子查询复合的任何术语都不能是聚合的或 DISTINCT 的，并且
3. 子查询中的每个术语都必须有一个 FROM 子句，并且
4. 外部查询可能不是聚合、DISTINCT 查询或连接。
父查询和子查询可以包含 WHERE 子句。根据规则 (11)、(12) 和 (13)，它们还可以包含 ORDER BY、LIMIT 和 OFFSET 子句。
如果子查询是复合选择，那么父查询的 ORDER by 子句的所有项都必须是对子查询列的简单引用。
如果子查询使用 LIMIT，则外部查询可能没有 WHERE 子句。
如果子查询是一个复合查询，那么它不能使用 ORDER BY 子句。
如果子查询使用 LIMIT，则外部查询可能不是 DISTINCT。
子查询可能不是递归 CTE。
（包含在约束 17d 中。）
（已过时。不再尝试对聚合子查询进行查询展平。）

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

12.子查询协程

在 SQLite 3.7.15 (2012-12-12) 之前，FROM 子句中的子查询将被展平到外部查询中，否则子查询将在外部查询开始之前运行完成，子查询的结果集将存储在临时表中，然后在外部查询中使用临时表。较新版本的 SQLite 有第三种选择，即使用协程实现子查询。

协同例程类似于子例程，因为它与调用者在同一线程中运行，并最终将控制权返回给调用者。不同之处在于协程还可以在完成之前返回，然后在下次调用时从中断处继续执行。

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

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

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

12.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()”在排序之前被调用，因此在表的每一行上被调用，甚至是由于 LIMIT 子句而最终被省略的行。可以使用协程来解决这个问题：

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

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

未来版本的 SQLite 中的查询规划器可能会变得足够聪明，可以在两个方向上自动进行上述转换。也就是说，SQLite 的未来版本可能会将第一种形式的查询转换为第二种形式，或者将以第二种方式编写的查询转换为第一种形式。从 SQLite 版本 3.22.0 (2018-01-22) 开始，如果外部查询未在其结果集中使用任何用户定义的函数或子查询，查询规划器将展平子查询。然而，对于上面显示的示例，SQLite 实现了每个编写的查询。

13. MIN/MAX 优化

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

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

14.自动索引

当没有索引可用于帮助评估查询时，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 是一百万，因此它认为构建自动索引将是更便宜的方法。

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

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 pragma在运行时禁用自动索引功能。自动索引在默认情况下是打开的，但是可以更改它，以便使用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”出现在内部索引的名称中出于遗留原因，并不表示内部索引和自动索引相关。

14.1. 哈希连接

自动索引与散列连接大致相同。唯一的区别是使用了 B-Tree 而不是哈希表。如果你愿意说为自动索引构建的瞬态 B-Tree 真的只是一个花哨的哈希表，那么使用自动索引的查询只是一个哈希连接。

在这个实例中，SQLite 构建了一个临时索引而不是哈希表，因为它手边已经有了一个健壮的高性能 B-Tree 实现，而需要添加一个哈希表。添加一个单独的哈希表实现来处理这种情况会增加库的大小（设计用于低内存嵌入式设备）以获得最小的性能增益。有朝一日，SQLite 可能会通过哈希表实现得到增强，但目前看来，在客户端/服务器数据库引擎可能使用哈希连接的情况下，继续使用自动索引似乎更好。

15.下推优化

如果无法将子查询展平到外部查询中，仍然可以通过将 WHERE 子句项从外部查询“下推”到子查询中来提高性能。考虑一个例子：

CREATE TABLE t1(a INT, b INT);
CREATE TABLE t2(x INT, y INT);
CREATE VIEW v1(a,b) AS SELECT DISTINCT a, b FROM t1;

SELECT x, y, b
  FROM t2 JOIN v1 ON (x=a)
 WHERE b BETWEEN 10 AND 20;

视图 v1 不能展平，因为它是 DISTINCT。它必须改为作为子查询运行，结果存储在临时表中，然后在 t2 和临时表之间执行连接。下推优化将“b BETWEEN 10 AND 20”项下推到视图中。这使得临时表更小，如果 t1.b 上有索引，则有助于子查询运行得更快。结果评估是这样的：

SELECT x, y, b
  FROM t2
  JOIN (SELECT DISTINCT a, b FROM t1 WHERE b BETWEEN 10 AND 20)
 WHERE b BETWEEN 10 AND 20;

不能总是使用下推优化。例如，如果子查询包含 LIMIT，则从外部查询中下推 WHERE 子句的任何部分都可能会更改内部查询的结果。还有其他限制，在实现此优化的 pushDownWhereTerms() 例程的源代码注释中进行了解释。

16. LEFT JOIN强度缩减优化

如果 WHERE 子句中有条款保证两个连接将产生相同的结果，则 LEFT JOIN 有时可以转换为普通 JOIN。特别是，如果 LEFT JOIN 右侧表中的任何列必须为非 NULL 才能使 WHERE 子句为真，则 LEFT JOIN 将降级为普通 JOIN。

在 WHERE 子句中确定 LEFT JOIN 右表的任何列是否必须为非 NULL 的证明者是不完善的。它有时会返回假阴性。换句话说，它有时无法降低 LEFT JOIN 的强度，而实际上这样做是可能的。例如，证明者不知道datetime() SQL 函数如果其第一个参数为 NULL 将始终返回 NULL，因此它不会认识到以下查询中的 LEFT JOIN 可以降低强度：

SELECT urls.url
  FROM urls
  LEFT JOIN
    (SELECT *
      FROM (SELECT url_id AS uid, max(retrieval_time) AS rtime
              FROM lookups GROUP BY 1 ORDER BY 1)
      WHERE uid IN (358341,358341,358341)
    ) recent
    ON u.source_seed_id = recent.xyz OR u.url_id = recent.xyz
 WHERE
     DATETIME(recent.rtime) > DATETIME('now', '-5 days');

证明者未来的增强可能会使其能够识别某些内置函数的 NULL 输入总是导致 NULL 答案。然而，并非所有内置函数都具有该属性（例如coalesce()），当然，证明者永远无法推理应用程序定义的 SQL 函数。

17. Omit LEFT JOIN优化

有时 LEFT JOIN 可以从查询中完全省略而不改变结果。如果满足以下所有条件，就会发生这种情况：

查询不是聚合
查询是 DISTINCT，或者 LEFT JOIN 上的 ON 或 USING 子句约束连接，使其仅匹配一行
LEFT JOIN 的右侧表不会在其自身的 USING 或 ON 子句之外的查询中的任何地方使用。

当在视图内部使用 LEFT JOIN 时，通常会出现 LEFT JOIN 消除，然后以不引用 LEFT JOIN 右侧表的任何列的方式使用视图。

下面是一个省略 LEFT JOIN 的简单示例：

CREATE TABLE t1(ipk INTEGER PRIMARY KEY, v1);
CREATE TABLE t2(ipk INTEGER PRIMARY KEY, v2);
CREATE TABLE t3(ipk INTEGER PRIMARY KEY, v3);

SELECT v1, v3 FROM t1 
  LEFT JOIN t2 ON (t1.ipk=t2.ipk)
  LEFT JOIN t3 ON (t1.ipk=t3.ipk)

t2 表在上面的查询中完全没有使用，因此查询规划器能够像这样编写查询一样实现查询：

SELECT v1, v3 FROM t1 
  LEFT JOIN t3 ON (t1.ipk=t3.ipk)

18. The Constant Propagation Optimization

当 WHERE 子句包含两个或多个由 AND 运算符连接的相等约束，使得各种约束的所有亲和力都相同时，SQLite 可能会使用相等的传递属性来构造新的“虚拟”约束，这些约束可用于简化表达式和/或提高性能。这称为“恒定传播优化”。

例如，考虑以下模式和查询：

CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT);
SELECT * FROM t1 WHERE a=b AND b=5;

SQLite 查看“a=b”和“b=5”约束并推断如果这两个约束为真，那么“a=5”也必须为真。这意味着可以使用 INTEGER PRIMARY KEY 的值 5 快速查找所需的行。