SQLite查询优化器概述

本文档概述了SQLite的查询计划程序和优化程序的工作方式。

给定一个SQL语句，取决于语句本身和基础数据库架构的复杂性，可能有数十种，数百种甚至数千种方法来实现该语句。查询计划程序的任务是从众多选择中选择一种算法，以最小的磁盘I / O和CPU开销提供答案。

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

在版本3.8.0（2013-08-26）中，SQLite查询计划程序已重新实现为 下一代查询计划程序或“ NGQP”。本文档中描述的所有功能，技术和算法均适用于3.8.0之前的旧版查询计划程序和NGQP。有关NGQP与传统查询计划程序的不同之处的更多信息，请参见NGQP的 详细说明。

查询中的WHERE子句分为“条款”，其中每个条款都由AND运算符分隔。如果WHERE子句由由OR运算符分隔的约束组成，则将整个子句视为对其应用OR子句优化的单个“项” 。

分析WHERE子句的所有术语，以查看是否可以使用索引满足它们。为了能够被索引使用，术语必须具有以下形式之一：

  列=表达式列IS表达式列>表达式列>=表达式列<表达式列<=表达式表达式=列表达式>列表达式>=列表达式<列表达式<=列列IN（表达式列表）
  列IN（子查询）
  列一片空白

  
  
  
  
  
  
  
  
  
  
  

如果使用以下语句创建索引：

在ex1（a，b，c，d，e，...，y，z）上创建索引idx_ex1;

如果索引的初始列（a，b等列）出现在WHERE子句中，则可以使用该索引。索引的初始列必须与=或IN或IS运算符一起使用。使用的最右边的列可以使用不等式。对于使用的索引的最右边的列，最多可能有两个不等式，它们必须将列的允许值夹在两个极端之间。

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

对于表达式的索引，只要在前面的文本中使用单词“ column”，就可以替换“ indexed expression”（表示出现在CREATE INDEX 语句中的表达式的副本），并且所有内容都将相同。

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

...其中a=5并且b IN（1,2,3）和c为NULL并且d='hello'

索引的前四列a，b，c和d将可用，因为这四列构成索引的前缀，并且都由相等约束约束。

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

...其中a=5 AND b IN（1,2,3）AND c> 12 AND d='hello'

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

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

...其中a=5 AND b IN（1,2,3）AND d='hello'

仅索引的a和b列可用。d列将不可用，因为c列不受约束，并且索引中可用的列集合中不存在间隙。

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

...其中b IN（1,2,3）和c NOT NULL AND d='hello'

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

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

...在a=5或b IN（1,2,3）或c NOT NULL或d='hello'的地方

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

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

  表达式1 BETWEEN表达式2 AND表达式3

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

  expr1 >=expr2 AND expr1 <=expr3

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

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

  列=expr1或列=expr2或列=expr3或...

然后将该术语重写如下：

  列IN（expr1 ，expr2 ，expr3 ，...）

然后，重写的术语可能会继续使用IN运算符的常规规则来约束索引。注意，列必须是在每一个OR-连接subterm同一列，虽然柱可以在任一左侧或右侧发生=操作者。

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

  expr1或expr2或expr3

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

  rowid IN（从表WHERE expr1中
            选择SELECT rowid从表WHERE expr2中
            选择SELECT rowid从表WHERE expr3中选择UNION SELECT rowid ）

上面重写的表达是概念性的；包含OR的WHERE子句实际上不是用这种方式重写的。OR子句的实际实现使用一种效率更高的机制，甚至对于WITHOUT ROWID表或无法访问“ rowid”的表也可以使用。尽管如此，实现的实质还是由上面的语句捕获的：使用单独的索引从每个OR子句项中查找候选结果行，而最终结果是这些行的并集。

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

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

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

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

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

'a'像'A'

如果按以下方式启用了case_sensitive_like杂注：

PRAGMA case_sensitive_like=开;

然后，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优化包括添加两个虚拟术语，如下所示：

  列>=x AND列< y

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

  列LIKE x ％
  列GLOB x *

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

请注意，如果LIKE或GLOB运算符的右侧是参数，并且使用sqlite3_prepare_v2（） 或sqlite3_prepare16_v2（）准备该语句，则在每次运行的第一个sqlite3_step（）调用中会自动重新分析并重新编译该语句。自上次运行以来，与右侧参数的绑定已更改。这种重新解析和重新编译本质上是与架构更改后发生的相同操作。重新编译是必需的，以便查询计划者可以检查绑定到LIKE或GLOB运算符右侧的新值，并确定是否采用上述优化方法。

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

考虑如下表：

创建表人（
  名称TEXT PRIMARY KEY，
  角色TEXT NOT NULL，
  高度INT不为零，以厘米为单位
  CHECK（角色IN（'student'，'teacher'））
）;
CREATE INDEX people_idx1 ON人（角色，身高）；

人员表对于大型组织中的每个人都有一个条目。每个人都是由“角色”字段确定的“学生”或“老师”。该表还记录了每个人的身高（厘米）。角色和身高已编制索引。请注意，索引的最左列不是非常有选择性-它仅包含两个可能的值。

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

从身高>=180的人中选择姓名；

因为索引的最左列没有出现在查询的WHERE子句中，所以很容易得出结论，认为索引在这里不可用。但是，SQLite可以使用索引。从概念上讲，SQLite使用索引就像查询更像以下内容一样：

从人中选择姓名
 角色在何处（从人中选择不同的角色）
   AND高度>=180；

或这个：

从角色=“教师”且身高>=180的人中选择姓名
全联盟
在角色=“学生”且身高>=180的人员中选择姓名；

上面显示的替代查询公式仅是概念性的。SQLite并不会真正改变查询。实际的查询计划是这样的：SQLite定位“角色”的第一个可能值，这可以通过将“ people_idx1”索引倒退到开头并读取第一条记录来实现。SQLite将此第一个“ role”值存储在一个内部变量中，在这里我们将其称为“ $ role”。然后，SQLite运行一个查询，例如：“从人的角色中选择名称，角色=$角色且高度>=180”。该查询在索引的最左列具有相等约束，因此可以使用索引来解析该查询。查询完成后，SQLite然后使用“ people_idx1”索引查找“角色”列的下一个值，使用在逻辑上类似于“从人那里的选择角色> $ role LIMIT 1”的代码。这个新的“ role”值将覆盖$ role变量，然后重复该过程，直到检查了“ role”的所有可能值。

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

如果SQLite知道前一个或多个列包含许多重复值，则可以对索引使用跳过扫描。如果索引的最左列中的重复项太少，则比对索引进行二进制搜索来定位索引要快得多，因此直接前进到下一个值并进行全表扫描会更快。下一个左列值。

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

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

对于左外联接，情况更为复杂。以下两个查询不等效：

选择* FROM tab1左联接tab2 ON tab1.x=tab2.y;
选择* FROM tab1左联接tab2在哪里tab1.x=tab2.y;

对于内部联接，上面的两个查询将是相同的。但是，特殊处理适用于OUTER联接的ON和USING子句：特别是，如果联接的右表位于空行上，则ON或USING子句中的约束不适用，但是约束在WHERE中适用条款。最终结果是，将LEFT JOIN的ON或USING子句表达式放在WHERE子句中可有效地将查询转换为普通的INNER JOIN-尽管内部联接的运行速度较慢。

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

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

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

SQLite特别对待CROSS JOIN运算符。从理论上讲，CROSS JOIN运算符是可交换的。但是，SQLite选择从不对CROSS JOIN中的表进行重新排序。这提供了一种机制，程序员可以通过该机制强制SQLite选择特定的循环嵌套顺序。

选择联接中的表顺序时，SQLite使用高效的多项式时间算法。因此，SQLite能够在几微秒内计划使用50或60方式联接的查询

联接重新排序是自动的，通常效果很好，程序员无需考虑它，特别是如果使用ANALYZE 来收集有关可用索引的统计信息，尽管有时需要程序员一些提示。例如，考虑以下架构：

CREATE TABLE节点（
   id整数主键，
   名称TEXT
）;
CREATE INDEX node_idx ON节点（名称）；
创建表边缘（
   orig INTEGER REFERENCES节点，
   dest INTEGER REFERENCES节点，
   主键（orig，dest）
）;
创建索引edge_idx ON edge（dest，orig）;

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

选择 *
  从边缘AS e，
       节点AS n1，
       节点AS n2
 其中n1.name='alice'
   AND n2.name='bob'
   AND e.orig=n1.id
   AND e.dest=n2.id;

该查询询问有关从标记为“ alice”的节点到标记为“ bob”的节点的边的所有信息。对于如何实现此查询，SQLite中的查询优化器基本上有两种选择。（实际上有六个不同的选择，但是我们在这里只考虑其中两个。）下面的伪代码演示了这两个选择。

选项1：

foreach n1，其中n1.name='alice'执行以下操作：
  foreach n2，其中n2.name='bob'执行以下操作：
    foreach e其中e.orig=n1.id和e.dest=n2.id
      返回n1。*，n2。*，e。*
    结尾
  结尾
结尾

选项2：

foreach n1，其中n1.name='alice'执行以下操作：
  foreach e其中e.orig=n1.id执行以下操作：
    foreach n2，其中n2.id=e.dest和n2.name='bob'执行以下操作：
      返回n1。*，n2。*，e。*
    结尾
  结尾
结尾

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

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

令alice节点的数量为M，而bob节点的数量为N。考虑两种情况。在第一种情况下，M和N均为2，但每个节点上有数千条边。在这种情况下，首选选项1。使用选项1，内部循环检查一对节点之间是否存在边，如果找到，则输出结果。因为每个节点只有2个alice和bob节点，所以内部循环只需要运行四次，并且查询非常快。选项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命令以收集统计信息，则在统计信息表明替代方法可能运行得更快的情况下，可能会做出其他选择。

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

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

在以下查询中，优化器可以随意使用其认为合适的方式对FROM子句的表进行重新排序：

选择 *
  从节点AS n1，
       边缘AS e，
       节点AS n2
 其中n1.name='alice'
   AND n2.name='bob'
   AND e.orig=n1.id
   AND e.dest=n2.id;

在以下相同查询的逻辑等效表示中，将“ CROSS JOIN”替换为“，”表示表的顺序必须为N1，E，N2。

选择 *
  从节点AS n1交叉加入
       边缘作为交叉联接
       节点AS n2
 其中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升级案例研究”。稍后在同一文档中找到的查询计划者清单提供了有关手动控制查询计划者的进一步指导。

查询的FROM子句中的每个表最多可以使用一个索引（除非进行OR子句优化时除外），SQLite努力在每个表上至少使用一个索引。有时，两个或多个索引可能是在单个表上使用的候选对象。例如：

创建表ex2（x，y，z）;
在ex2（x）上创建索引ex2i1；
在ex2（y）上创建索引ex2i2;
在ex2的x=5和y=6的地方选择z；

对于上面的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，因为该索引更具选择性。

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

从ex2中选择z，其中+ x=5且y=6;

的+在操作者X列将防止该术语从约束的索引。这将强制使用ex2i2索引。

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

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

创建表ex2（x，y，z）;
在ex2（x）上创建索引ex2i1；
在ex2（y）上创建索引ex2i2;
从ex2中选择x，其中x在1和100之间，y在1和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的工作原理相同。

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

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

创建表ex3（w，x，y，z）;
在ex2（w，x）上创建索引ex3i1;
在ex2（w，y）上创建索引ex3i2;
从ex3中w=5且x在1和100之间以及y在1和100之间选择z；

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

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

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

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

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

如果查询包含带有多个术语的ORDER BY子句，则可能是SQLite可以使用索引使行以ORDER BY中术语的某些前缀的顺序出现，但ORDER BY中的后续术语不满足。在这种情况下，SQLite会进行块排序。假设ORDER BY子句具有四个术语，并且查询的自然顺序导致以前两个术语的顺序出现的行。当查询引擎输出每一行并进入排序器时，将当前行中与ORDER BY的前两项相对应的输出与前一行进行比较。如果它们已更改，则当前排序完成并输出，然后开始新的排序。这样会导致排序更快。事件的较大优势是，需要在内存中保留的行数少得多，

在SELECT的FROM子句中发生子查询时，最简单的行为是将子查询评估为临时表，然后对临时表运行外部SELECT。这样的计划可能不是最佳的，因为临时表将没有任何索引，并且外部查询（很可能是联接）将被迫对临时表进行全表扫描。

为了解决此问题，SQLite尝试在SELECT的FROM子句中平化子查询。这涉及将子查询的FROM子句插入外部查询的FROM子句中，并在外部查询中重写引用子查询结果集的表达式。例如：

从t2选择SELECT t1.a，t2.b，（在z <100的FROM t1中选择x + y作为a）> 5

将使用查询拼合重写为：

选择t1.x + t1.y作为a，从t2中选择t2.b，t1中z <100和a> 5

必须满足所有条件，才能使查询变平。某些约束已用斜体文本标记为过时。这些额外的约束保留在文档中，以保留其他约束的编号。

临时读者不应理解所有这些规则。本节的主要结论是，用于确定查询展平是安全还是不安全的规则是微妙而复杂的。多年来，由于过度激进的查询扁平化而导致了多个错误。另一方面，如果查询扁平化更为保守，则复杂查询和/或涉及视图的查询的性能往往会受到影响。

1. （已过时。不再尝试对聚合子查询进行平坦化查询。）
2. （已过时。不再尝试对聚合子查询进行平坦化查询。）
3. 如果子查询是LEFT JOIN的正确操作数，则
   1. 子查询可能不是联接，并且
   2. 子查询的FROM子句可能不包含虚拟表，并且
   3. 外部查询可能不是集合。
4. 子查询不是DISTINCT。
5. （归入约束4）
6. （已过时。不再尝试对聚合子查询进行平坦化查询。）
7. 子查询具有FROM子句。
8. 子查询不使用LIMIT或外部查询不是联接。
9. 子查询不使用LIMIT或外部查询不使用聚合。
10. （2005年放宽了限制）
11. 子查询和外部查询都没有ORDER BY子句。
12. （归纳为约束3）
13. 子查询和外部查询都不都使用LIMIT。
14. 子查询不使用OFFSET。
15. 如果外部查询是复合选择的一部分，则子查询可能没有LIMIT子句。
16. 如果外部查询是一个聚合，则子查询可能不包含ORDER BY。
17. 如果子查询是复合SELECT，则
    1. 所有复合运算符必须为UNION ALL，并且
    2. 子查询复合词的任何术语都不得为汇总或DISTINCT，并且
    3. 子查询中的每个术语都必须具有FROM子句，并且
    4. 外部查询可能不是聚合查询，DISTINCT查询或联接。
    父查询和子查询可能包含WHERE子句。根据规则（11），（12）和（13），它们还可以包含ORDER BY，LIMIT和OFFSET子句。
18. 如果子查询是复合选择，则父级的ORDER by子句的所有术语都必须是对子查询列的简单引用。
19. 如果子查询使用LIMIT，则外部查询可能没有WHERE子句。
20. 如果子查询是复合选择，则它不能使用ORDER BY子句。
21. 如果子查询使用LIMIT，则外部查询可能不是DISTINCT。
22. 子查询可能不是递归CTE。
23. （包含在约束17d中。）
24. （已过时。不再尝试对聚合子查询进行平坦化查询。）

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

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

协同例程就像子例程一样，它在与调用者相同的线程中运行，并最终将控制权返回给调用者。区别在于，协同例程还可以在完成之前返回，然后在下次调用时从中断的地方继续执行。

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

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

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

从SQLite版本3.21.0（2017-10-24）开始，查询计划者将始终喜欢使用协例程来实现包含子句的FROM子句子查询，并且该子查询在返回结果时不属于联接的一部分外部查询的集合是“复杂”。此功能使应用程序可以将昂贵的计算从分类器之前转移到分类器之后，从而可以加快操作速度。例如，考虑以下查询：

从选项卡ORDER BY日期DESC LIMIT 5中选择昂贵的功能（a）;

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

从（选择昂贵的功能（a）
  选择一个FROM选项卡ORDER BY日期DESC LIMIT 5
）;

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

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

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

SELECT MIN（x）FROM表；
SELECT MAX（x）+1 FROM表;

如果没有可用的索引来帮助评估查询，则SQLite可能会创建仅在单个SQL语句持续时间内自动存在的索引。由于构造自动索引的成本为O（NlogN）（其中N为表中条目的数量），而进行全表扫描的成本仅为O（N），因此只有在SQLite时才创建自动索引预计在SQL语句过程中，查找将运行超过logN次。考虑一个例子：

创建表t1（a，b）;
创建表t2（c，d）;
-在t1和t2中都插入许多行
选择*从t1，t2那里a=c;

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

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

创建表t1（a，b）;
创建表t2（c，d）;
-在t1和t2中都插入许多行
选择a，（从t2的d到c=b的SELECT d）从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消息发送到错误日志。应用程序开发人员可以并且应该使用这些警告来确定对架构中新的持久索引的需求。

不要混淆与自动索引内部指标（具有类似于“sqlite_autoindex_名称表_ ?这有时创建实行”）PRIMARY KEY约束或UNIQUE约束。此处描述的自动索引仅在单个查询期间存在，永不持久化到磁盘，并且仅对单个数据库连接可见。内部索引是PRIMARY KEY和UNIQUE约束的实现的一部分，具有持久性并且可以持久存储在磁盘上，并且对所有数据库连接都是可见的。术语“自动索引”出现在内部索引的名称中 出于传统原因，并不表示内部索引和自动索引相关。

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

创建表t1（a INT，b INT）;
创建表t2（x INT，y INT）;
创建视图v1（a，b）作为选择从t1开始的a，b;

选择x，y，b
  从t2加入v1开（x=a）
 b在10和20之间；

由于视图v1是DISTINCT，因此无法将其展平。相反，它必须作为子查询运行，并将结果存储在临时表中，然后在t2和临时表之间执行联接。下推式优化将“ 10和20之间的b”下推到视图中。这使瞬态表变小，并且如果t1.b上有索引，则有助于子查询更快地运行。结果评估如下：

选择x，y，b
  从t2开始
  联接（从t1的b到10和20之间选择t，a，b）
 b在10和20之间；

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

如果WHERE子句中有保证两个连接将给出相同结果的术语，则有时可以将LEFT JOIN转换为普通JOIN。特别是，如果LEFT JOIN右侧表中的任何列都必须为非NULL以便WHERE子句为true，则LEFT JOIN会降级为普通JOIN。

确定WHERE子句中LEFT JOIN右侧表的任何列是否必须为非NULL的证明者是不完善的。有时会返回假阴性。换句话说，有时可能无法降低左联接的强度。例如，证明者不知道如果datetime（）SQL函数的第一个参数为NULL，则它将始终返回NULL，因此它无法识别以下查询中的LEFT JOIN可以降低强度：

选择urls.url
  来自网址
  左联接
    （选择 *
      FROM（选择url_id AS uid，max（retrieval_time）AS rtime
              从查找中GROUP BY 1 ORDER BY 1）
      uid在（358341,358341,358341）
    ） 最近的
    开启u.source_seed_id=最近使用的xyz或u.url_id=最近使用的xyz
 在哪里
     DATETIME（recent.rtime）> DATETIME（'now'，'-5 days'）;

证明者将来的增强可能使它能够认识到某些内置函数的NULL输入始终会导致NULL答案。但是，并非所有内置函数都具有该属性（例如，coalesce（）），并且当然，证明者将永远无法推理 应用程序定义的SQL函数。

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

1. 查询不是汇总
2. 查询是DISTINCT还是LEFT JOIN上的ON或USING子句将联接限制为仅与单个行匹配
3. 在查询自身的USING或ON子句之外的任何地方，都不会使用LEFT JOIN的右侧表。

当在视图内部使用LEFT JOIN时，通常会出现LEFT JOIN消除的情况，然后使用该视图，这样就不会引用LEFT JOIN右侧表的任何列。

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

创建表t1（ipk整数主键，v1）;
创建表t2（ipk INTEGER PRIMARY KEY，v2）;
创建表t3（ipk整数主键，v3）;

从t1选择v1，v3 
  左联接t2 ON（t1.ipk=t2.ipk）
  左联接t3 ON（t1.ipk=t3.ipk）

t2表在上面的查询中是完全未使用的，因此查询计划者可以像编写该查询一样实现该查询：

从t1选择v1，v3 
  左联接t3 ON（t1.ipk=t3.ipk）

当WHERE子句包含由AND运算，使得所有连接的两个或多个等式约束亲和力的各种约束条件都是一样的，那么SQLite的可能会使用平等的传递特性来构建可用于新的“虚拟”的约束简化表达式和/或提高性能。这称为“常数传播优化”。

例如，考虑以下架构和查询：

创建表t1（a整数主键，b INT，c INT）;
选择*从t1那里a=b和b=5;

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