小巧。快速。可靠。
三选二。
三选二。
本文档概述了 SQLite 的查询规划器和优化器的工作原理。
给定单个 SQL 语句，可能有多种方法来实现该语句，具体取决于语句本身和底层数据库模式的复杂性。查询规划器的任务是选择能够最大程度地减少磁盘 I/O 和 CPU 开销的算法。
更多背景信息可在索引教程文档中找到。下一代查询规划器文档提供了有关如何选择连接顺序的更多详细信息。
在分析之前，将进行以下转换，以将所有连接约束移至 WHERE 子句
SQLite 不区分出现在 WHERE 子句中的连接约束和内连接的 ON 子句中的约束，因为这种区分不会影响结果。但是，外连接的 ON 子句约束和 WHERE 子句约束之间存在差异。因此，当 SQLite 将 ON 子句约束从外连接移动到 WHERE 子句时，它会在抽象语法树 (AST) 中添加特殊的标记，以指示该约束来自外连接以及它来自哪个外连接。在纯 SQL 文本中无法添加这些标记。因此，SQL 输入必须在外连接上使用 ON 子句。但在内部 AST 中，所有约束都是 WHERE 子句的一部分，因为将所有内容放在一个地方可以简化处理。
将所有约束移至 WHERE 子句后，WHERE 子句将被分解成连接（以下称为“项”）。换句话说，WHERE 子句被分解成由 AND 运算符分隔的部分。如果 WHERE 子句由 OR 运算符（析取）分隔的约束组成，则整个子句将被视为一个“项”，并对其应用OR 子句优化。
分析 WHERE 子句的所有项，以查看它们是否可以使用索引来满足。要被索引使用，项通常必须采用以下形式之一
column = expression column IS expression column > expression column >= expression column < expression column <= expression expression = column expression IS column expression > column expression >= column expression < column expression <= column column IN (expression-list) column IN (subquery) column IS NULL column LIKE pattern column GLOB pattern
如果使用以下语句创建索引
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语句中的表达式的副本），并且所有内容都将按相同的方式工作。
对于上面的索引和类似于此的 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 子句优化可能会适用。
如果 WHERE 子句的某个项采用以下形式
expr1 BETWEEN expr2 AND expr3
然后将添加两个“虚拟”项，如下所示
expr1 >= expr2 AND expr1 <= expr3
虚拟项仅用于分析，不会导致生成任何字节码。如果两个虚拟项最终都被用作索引上的约束，则将省略原始的 BETWEEN 项，并且不会对输入行执行相应的测试。因此，如果 BETWEEN 项最终被用作索引约束，则永远不会对该项执行测试。另一方面，虚拟项本身永远不会导致对输入行执行测试。因此，如果 BETWEEN 项不用作索引约束，而是必须用于测试输入行，则仅评估expr1表达式一次。
由 OR 而不是 AND 连接的 WHERE 子句约束可以通过两种不同的方式处理。
如果某个项包含多个包含公共列名并由 OR 分隔的子项，如下所示
column = expr1 OR column = expr2 OR column = expr3 OR ...
然后该项将重写如下
column IN (expr1,expr2,expr3,...)
然后，重写的项可能会继续使用IN运算符的正常规则来约束索引。请注意，column必须是每个 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前缀以获取所选查询策略的高级概述。
使用LIKE或GLOB运算符的 WHERE 子句项有时可以与索引一起执行范围搜索，几乎就像 LIKE 或 GLOB 是BETWEEN运算符的替代方案一样。此优化有很多条件
LIKE 运算符有两种模式，可以通过 pragma 设置。默认模式是 LIKE 比较对 latin1 字符的大小写差异不敏感。因此，默认情况下，以下表达式为真
'a' LIKE 'A'
如果 case_sensitive_like pragma 启用如下
PRAGMA case_sensitive_like=ON;
那么 LIKE 运算符会注意大小写，上面的示例将评估为假。请注意，大小写不敏感仅适用于 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。我们使用单个字符来表示此非通配符前缀，但读者应该理解前缀可以包含多个字符。令 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() 准备时，如果与右侧参数的绑定自上次运行以来已更改，则在每次运行的第一个 sqlite3_step() 调用时，语句将自动重新解析和重新编译。此重新解析和重新编译本质上与架构更改后发生的相同操作。重新编译是必要的，以便查询计划程序可以检查绑定到 LIKE 或 GLOB 运算符右侧的新值，并确定是否使用上述优化。
一般规则是，只有当索引最左侧列上有 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 表中包含大型组织中每个人的一个条目。根据“role”字段，每个人要么是“student”，要么是“teacher”。该表还记录了每个人的身高（以厘米为单位）。role 和 height 是已索引的。请注意，索引的最左侧列的选择性不是很高——它仅包含两个可能的值。
现在考虑一个查询，以查找组织中身高 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”值存储在我们将在此处称为“$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 个或更多时，跳过扫描才会变得有利（它才会比完全表扫描更快）。因此，永远不会在未经分析的数据库上使用跳过扫描。
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
相同的索引用于加速两种实现选项中的每个循环。这两种查询计划的唯一区别是循环嵌套的顺序。
那么哪种查询计划更好？事实证明，答案取决于在 node 和 edge 表中找到的数据类型。
假设 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命令来收集统计信息，如果统计信息表明备选方案可能运行得更快，则可能会做出不同的选择。
SQLite几乎总是自动选择最佳连接顺序。开发人员很少需要干预以向查询计划程序提供有关最佳连接顺序的提示。最佳策略是利用PRAGMA optimize来确保查询计划程序可以访问数据库中数据形状的最新统计信息。
本节描述开发人员可以用来控制SQLite中连接顺序的技术，以解决可能出现的任何性能问题。但是，不建议使用这些技术，除非作为最后的手段。
如果您确实遇到SQLite即使在运行PRAGMA optimize后仍然选择次优连接顺序的情况，请在SQLite社区论坛上报告您的情况，以便SQLite维护人员可以对查询计划程序进行新的改进，从而无需手动干预。
SQLite为高级程序员提供了控制优化器选择的查询计划的能力。一种方法是伪造ANALYZE结果到sqlite_stat1表中。
程序员可以通过使用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升级案例研究”。同一文档中稍后提供的查询计划程序检查清单提供了有关手动控制查询计划程序的进一步指导。
查询的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_statN表包含有关各个索引的选择性信息。例如，sqlite_stat1表可能指示对列x的等式约束平均将搜索空间减少到10行，而对列y的等式约束平均将搜索空间减少到3行。在这种情况下，SQLite将更倾向于使用索引ex2i2，因为该索引更具选择性。
注意：不建议以这种方式禁用WHERE子句项。这是一种变通方法。仅在作为最后手段以获得所需的性能时才执行此操作。如果您发现此变通方法是必要的，请在SQLite社区论坛上报告此情况，以便SQLite维护人员可以尝试改进查询计划程序，以便您的情况不再需要此变通方法。
可以通过在列名前面添加一元+运算符来手动禁用WHERE子句项以用于索引。一元+是一个无操作符，不会在准备好的语句中生成任何字节码。但是，一元+运算符将阻止该项约束索引。因此，在上面的示例中，如果查询被重写为
SELECT z FROM ex2 WHERE +x=5 AND y=6;
x列上的+运算符将阻止该项约束索引。这将强制使用ex2i2索引。
请注意，一元+运算符还会从表达式中删除类型亲和性，在某些情况下，这会导致表达式的含义发生细微变化。在上面的示例中，如果列x具有TEXT亲和性，则比较“x=5”将作为文本进行。+运算符删除了亲和性。因此，比较“+x=5”将比较列x中的文本与数值5，并且始终为假。
考虑一个略有不同的场景
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上的范围约束之间进行选择毫无用处。
在执行行的索引查找时，通常的过程是在索引上进行二分查找以查找索引条目，然后从索引中提取rowid，并使用该rowid在原始表上进行二分查找。因此，典型的索引查找涉及两次二分查找。但是，如果要从表中提取的所有列都已存在于索引本身中，则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 子句中，并重写外部查询中引用子查询结果集的表达式。例如
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
为了使查询扁平化发生，必须满足一系列的条件。一些约束条件用斜体文本标记为已过时。这些额外的约束保留在文档中以保留其他约束的编号。
非专业读者无需理解所有这些规则。这里的重点是扁平化规则微妙且复杂。多年来，由于过于激进的查询扁平化，导致了多个错误。另一方面，如果查询扁平化过于保守，则复杂查询和/或涉及视图的查询的性能往往会下降。
当视图用作每个视图的使用时，查询扁平化是一个重要的优化，因为它被转换为子查询。
SQLite 以三种方式之一实现 FROM 子句中的子查询
本节描述第三种技术：将子查询实现为协程。
协程类似于子程序，因为它在与调用者相同的线程中运行，并最终将控制权返回给调用者。区别在于，协程还可以在其完成之前返回，然后在下次调用时从中断的地方恢复。
当子查询作为协程实现时，会生成字节码以实现子查询，就好像它是一个独立的查询一样，只是协程在计算每一行后将控制权返回给调用者，而不是将结果行返回给应用程序。然后，调用者可以使用该计算的行作为其计算的一部分，然后在准备好下一行时再次调用协程。
协程优于将子查询的完整结果集存储在临时表中，因为协程使用更少的内存。使用协程，只需要记住结果的一行，而临时表必须存储结果的所有行。此外，由于协程不需要在外部查询开始工作之前完成运行，因此输出的第一行可以更快地出现，如果在整体查询完成之前放弃了整体查询，则总体完成的工作量更少。
另一方面，如果必须多次扫描子查询的结果（例如，它只是一个连接中的一个表），则最好使用临时表来记住子查询的整个结果，以避免多次计算子查询。
从 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 会按编写的方式实现每个查询。
包含单个 MIN() 或 MAX() 聚合函数的查询，其参数是索引的最左侧列，可以通过执行单个索引查找来满足，而不是扫描整个表。示例
SELECT MIN(x) FROM table; SELECT MAX(x)+1 FROM table;
当没有可用于辅助查询评估的索引时，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消息。应用程序开发人员可以并且应该使用这些警告来识别架构中新持久索引的需求。
不要将自动索引与内部索引（名称类似于“sqlite_autoindex_table_N”）混淆，后者有时用于实现PRIMARY KEY 约束或UNIQUE 约束。此处描述的自动索引仅在单个查询期间存在，绝不会持久保存到磁盘，并且仅对单个数据库连接可见。内部索引是 PRIMARY KEY 和 UNIQUE 约束实现的一部分，是持久的并保存到磁盘，并且对所有数据库连接可见。“autoindex”一词出现在内部索引的名称中是出于遗留原因，并不表示内部索引和自动索引之间存在关联。
自动索引几乎与哈希连接相同。唯一的区别在于使用 B 树而不是哈希表。如果您愿意说为自动索引构建的瞬态 B 树实际上只是一个花哨的哈希表，那么使用自动索引的查询就是一个哈希连接。
SQLite 在这种情况下构建瞬态索引而不是哈希表，因为它已经拥有一个健壮且高性能的 B 树实现，而哈希表则需要额外添加。添加单独的哈希表实现来处理这种情况会增加库的大小（该库设计用于低内存嵌入式设备），而性能提升却微乎其微。SQLite 可能会在将来增强哈希表实现，但目前看来，在客户端/服务器数据库引擎可能使用哈希连接的情况下继续使用自动索引更好。
如果子查询无法扁平化到外部查询中，仍然可以通过将外部查询中的 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;
WHERE 子句下推优化并非始终可用。例如，如果子查询包含 LIMIT，则将外部查询的任何 WHERE 子句部分下推可能会更改内部查询的结果。还有其他限制，在实现此优化的 pushDownWhereTerms() 例程的源代码中的注释中进行了说明。
不要将此优化与 MySQL 中同名优化混淆。MySQL 下推优化更改了 WHERE 子句约束的评估顺序，以便首先评估那些仅使用索引且无需查找对应表行即可评估的约束，从而避免在约束失败时不必要的表行查找。为了避免歧义，SQLite 将此称为“MySQL 下推优化”。除了 WHERE 子句下推优化之外，SQLite 也执行 MySQL 下推优化。但本节的重点是 WHERE 子句下推优化。
外部连接（左连接、右连接或全连接）有时可以简化。左连接或右连接可以转换为普通（内部）连接，或者全连接可以转换为左连接或右连接。如果 WHERE 子句中存在保证简化后结果相同的项，则可能会发生这种情况。例如，如果左连接右侧表中的任何列必须为非 NULL 才能使 WHERE 子句为真，则左连接将降级为普通连接。
确定连接是否可以简化的定理证明器并不完美。它有时会返回假阴性。换句话说，它有时无法证明降低外部连接的强度是安全的，而实际上它是安全的。例如，证明器不知道datetime() SQL 函数如果其第一个参数为 NULL，则始终返回 NULL，因此它不会识别以下查询中的左连接可以降低强度
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 函数。
有时可以完全省略查询中的左连接或右连接而不会更改结果。如果以下所有条件都为真，则可能会发生这种情况
当外部连接用于视图内部，然后以不引用左连接右侧表或右连接左侧表上任何列的方式使用该视图时，通常会出现外部连接消除。
以下是一个省略左连接的简单示例
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)
在撰写本文时，仅消除了左连接。此优化尚未推广到适用于右连接，因为右连接是 SQLite 的相对较新的添加。这种不对称性可能会在将来的版本中得到纠正。
当 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 快速查找所需的行。
此页面上次修改于 2024-07-24 12:16:13 UTC