查询计划

概述

SQL的最佳功能（在全部的它的实现，而不仅仅是SQLite）这是一个陈述性的语言，而不是程序性的语言。当用SQL编程时，你告诉系统什么你想要计算，而不是怎样计算它。计算的任务这个怎样委托给查询计划程序内的子系统SQL数据库引擎。

对于任何给定的SQL语句，可能有数百或数千个或甚至有数百万种不同的算法来执行操作。全部这些算法中的一种会得到正确的答案，尽管有些会运行比其他人更快。查询计划器是一个人工智能那个尝试为每个SQL选择最快、最有效的算法声明。

大多数时候，SQLite中的查询规划器做得很好。然而，查询规划器需要索引来与合作。这些索引通常必须由程序员添加。查询规划器AI很少会生成次优算法选择。在这些情况下，程序员可能希望提供额外的提示可以帮助查询计划器做得更好。

本文档提供了有关SQLite查询规划器和查询引擎工作。程序员可以使用这些信息帮助创建更好的索引，并提供提示以帮助查询规划器需要。

更多信息见SQLite查询计划器和下一代查询规划器文件。

1搜索

1.1.无索引的表格

SQLite中的大多数表由零行或多行组成，行中有一个唯一的整数键（罗伊德或集成主键)然后是内容。（例外情况是不带ROWID表。）行按rowid增加的顺序进行逻辑存储。例如本文使用了一个名为“FruitsForSale”的表，该表关联了各种水果到州政府它们生长的地方和市场上的单价。模式如下：

为销售创建餐桌水果(水果文本，州文本，价格真实);

对于一些（任意）数据，这样的表可以逻辑存储在磁盘上如图1所示：

图1：“销售水果”表的逻辑布局

在本例中，rowid不是连续但有序。SQLite通常从开始创建行ID每增加一行，增加一行。但如果行是删除后，序列中可能会出现间隙。并且应用程序可以控制如果需要，可以指定rowid，这样就不必插入行在底部。但不管发生什么，赛艇运动员总是独特且严格按升序排列。

假设你想查询桃子的价格。该查询将具体如下：

从水果中选择价格，其中水果=“桃子”；

为了满足此查询，SQLite读取表中，检查“fruit”列是否具有“Peach”值，以及因此，从该行输出“price”列。流程如图所示通过图2如下所示。这个算法叫做全表扫描因为为了找到感兴趣的一行，必须阅读和检查表。对于只有7行的表，可以进行全表扫描，但如果表包含700万行，则完整的表扫描可能会读取MB的内容，以便找到一个8字节的数字。出于这个原因，通常会尝试避免全表扫描。

图2：全表扫描

1.2.按行ID查找

避免全表扫描的一种技术是通过rowid（或等效值集成主键). 要查找桃子的价格，人们会查询rowid为4的条目：

从水果销售中选择价格，其中rowid=4；

由于信息以rowid顺序存储在表中，SQLite可以使用二进制搜索找到正确的行。如果表包含N个元素，则查找所需行与logN成比例，而不是成比例在全表扫描中为N。如果表包含1000万个元素，这意味着查询将按N/logN或大约100万的顺序进行速度快了几倍。

图3：按行查找

1.3.按索引查找

按rowid查找信息的问题在于，您可能不管“第4项”的价格是多少，你想知道价格桃子。因此，rowid查找没有帮助。

为了提高原始查询的效率，我们可以在“fruitsforsale”表的“fruit”列如下：

在水果（水果）上创建索引Idx1；

索引是另一个类似于原始“fruitsforsale”表的表但内容物（在这种情况下是果柱）存储在rowid和内容顺序中的所有行。图4给出了Idx1索引的逻辑视图。“fruit”列是用于对表和“rowid”是用于在以下情况下打破平局的次键两行或多行具有相同的“结果”。在该示例中，rowid必须用作“橙色”行的平局。注意，由于rowid在原始表的所有元素中总是唯一的，即组合键在索引的所有元素中，“fruit”后面跟“rowid”是唯一的。

图4：水果柱上的索引

此新索引可用于为原来的“桃子价格”查询。

从水果中选择价格，其中水果=“桃子”；

查询首先在Idx1索引上对条目进行二进制搜索有水果的=“桃子”。SQLite可以在Idx1索引上执行此二进制搜索但不在原始FruitsForSale表中，因为Idx1中的行已排序在“水果”栏旁边。在Idx1索引中找到一行fruit='Peach'，数据库引擎可以提取该行的rowid。然后数据库引擎进行第二次二进制搜索在原始FruitsForSale表上查找包含水果的原始行=“桃子”。从FruitsForSale表中的行，然后SQLite可以提取price列的值。此过程如图所示图5.

图5：桃子价格索引查询

SQLite必须进行两次二进制搜索才能使用方法如上所示。但对于具有大量行的表仍然比进行全表扫描快得多。

1.4.多个结果行

在前一个查询中，fruit='Peach'约束缩小了结果范围降到一行。但即使是多重的，同样的技术也有效获得行。假设我们查橙子的价格而不是桃子：

从水果销售中选择价格WHERE fruit=“Orange”

图6：橙子价格索引查询

在这种情况下，SQLite仍然执行单个二进制搜索来查找第一个索引条目，其中水果=“橙色”。然后从中提取rowid索引并使用该行ID通过查找原始表条目二进制搜索并从原始表中输出价格。但是相反退出时，数据库引擎将前进到下一行索引重复下一个水果=“橙色”条目的过程。前进到索引（或表）的下一行比执行二进制操作的成本低得多由于下一行通常位于与当前行。事实上，前进到下一排的成本是如此与我们通常忽略的二进制搜索相比，它的成本很低。所以我们估计这个查询的总成本是3个二进制搜索。如果输出的行数为K，并且表中的行数为N，则通常执行查询的成本是成比例的至（K+1）*logN。

1.5.多个AND连接的鉴于条款条款

接下来，假设你想查询的不仅仅是任何桔子的价格，特别是加州的橘子。适当的查询将具体如下：

从水果销售中选择价格，其中水果=“橙色”，状态=“CA”

图7：加州橙子的索引查找

此查询的一种方法是使用WHERE的fruit='Orange'术语子句查找处理桔子的所有行，然后筛选这些行拒绝任何来自加利福尼亚州以外的州。这个流程如下所示图7以上。这是一个完美的在大多数情况下采用合理的方法。是的，数据库引擎确实有对佛罗里达州的橙色行进行额外的二进制搜索后来被拒绝了，所以它没有我们希望的那么有效对于许多应用程序来说，它足够高效。

假设除了“水果”索引之外，还有关于“状态”的索引。

创建水果销售指数Idx2（州）；

图8：状态列上的索引

“状态”索引的工作原理与“水果”索引类似，因为它是一个rowid前面有一个额外列并按排序的新表作为主键的额外列。唯一的区别是在Idx2中，第一列是“state”而不是“fruit”Idx1.在我们的示例数据集中，“state”中有更多冗余列，因此它们是更多重复条目。领带还在使用rowid解析。

在“state”上使用新的Idx2索引，SQLite有另一个选项查找加州橙子的价格：它可以查找每一行里面有加州的水果，过滤掉那些不是桔子。

图9：加州橙子索引查找

使用Idx2而不是Idx1会导致SQLite检查不同的行，但最终得到相同的答案（这非常重要-记住，索引永远不应该改变答案，只有帮助SQLite更快地得到答案），它做同样的工作量。因此，在这种情况下，Idx2索引对性能没有帮助。

在我们的示例中，最后两个查询花费的时间相同。那么SQLite将选择哪个索引Idx1或Idx2？如果分析命令已在数据库上运行，因此SQLite有机会收集有关可用指数的统计数据，那么SQLite就会知道Idx1索引通常会缩小搜索范围只有一个项目（我们的水果示例=“橙色”是例外而Idx2索引通常只会缩小向下搜索到两行。因此，如果其他条件都相同，SQLite将选择Idx1，希望将搜索范围缩小到最小尽可能多的行。这个选择之所以可能，是因为提供的统计数据分析.如果分析还没有然后，可以任意选择要使用的索引。

1.6.多列索引

要获得具有多个AND连接的查询的最大性能在WHERE子句中，您确实需要一个包含每个AND项的列。在这种情况下，我们创建一个新索引在FruitsForSale的“水果”和“状态”栏上：

创建待售水果索引Idx3（水果、州）；

图1：两列索引

多列索引遵循与单列索引相同的模式；索引列被添加到rowid前面。唯一的区别现在添加了多个列。最左边的列是用于对索引中的行进行排序的主键。第二列是用于打破最左边栏中的平局。如果有第三列，它将用于打破前两列的僵局。以此类推索引中的所有列。因为rowid是有保证的为了唯一，索引的每一行都是唯一的，即使两行的内容列是相同的。这种情况不会发生在我们的样本数据中，但有一种情况（水果=“橙色”）是第一列上的平局，必须被第二列打破。

考虑到新的多列Idx3索引，现在可以使用SQLite只需使用两个二进制搜索即可找到加州橙子的价格：

从水果销售中选择价格，其中水果=“橙色”，状态=“CA”

图11：使用两列索引进行查找

在受WHERE子句约束的两列上使用Idx3索引，SQLite可以对Idx3进行一次二进制搜索，以找到一个rowid对于加利福尼亚州的橙子，然后进行一次二进制搜索以找到价格对于原始表中的项目。没有死机也没有浪费了二进制搜索。这是一个更有效的查询。

请注意，Idx3包含与原始文件相同的所有信息标识x1.因此，如果我们有Idx3，我们就不需要Idx1再。使用Idx3可以满足“桃子价格”查询只需忽略Idx3的“state”列：

从水果销售中选择价格WHERE fruit=‘Peach’

图12：多列索引上的单列查找

因此，一个好的经验法则是，数据库模式永远不应该包含两个索引，其中一个索引是另一个索引的前缀。删除用更少的列编制索引。SQLite仍然能够高效地索引较长的查找。

1.7.覆盖索引

“加州橙子价格”查询通过使用两列索引。但是SQLite可以使用包含“价格”列的三列索引：

创建待售水果索引Idx4（水果、州、价格）；

图13：覆盖索引

这个新索引包含原始FruitsForSale表的所有列由查询使用-搜索词和输出。我们打电话这是一个“覆盖指数”。因为所有需要的信息都在覆盖索引，SQLite从不需要查阅原始表为了找到价格。

从水果销售中选择价格，其中水果=“橙色”，状态=“CA”；

图14：使用覆盖索引的查询

因此，通过在索引末尾添加额外的“输出”列可以避免引用原始表，从而将查询的二进制搜索次数减少一半。这是一个性能不断提高（大约是速度）。但另一方面，它也只是一种改进；两倍的性能提升远不及当表首次被索引时，增长了一百万倍。对于大多数查询，1微秒和2微秒不太可能被注意到。

1.8.WHERE子句中的OR连接词

仅当WHERE中的约束条件查询的子句由AND连接。因此，当搜索的项目是都是橘子，生长在加利福尼亚州，但这两种指数都不会如果我们想要所有不是桔子的东西，那有用吗或产于加利福尼亚州。

从出售水果中选择价格，其中水果=“橙色”或状态=“CA”；

当在WHERE子句中遇到OR连接项时，SQLite分别检查每个OR术语，并尝试使用索引查找与每个术语关联的rowid。然后，它需要结果rowid集的并集来查找最终结果。下图说明了此过程：

图15：带OR约束的查询

上图表明SQLite首先计算所有行ID然后在开始操作之前将其与联合操作组合rowid查找原始表。实际上，rowid查找中间穿插着rowid计算。SQLite在以下位置使用一个索引一个查找rowid的时间，同时记住它看到过哪些rowid以避免重复。这只是一个实现然而，细节。该图虽然不是100%准确，但提供了一个良好的对正在发生的事情的概述。

为了使上面显示的OR-by-UNION技术有用必须是有助于解析每个OR关联项的可用索引在WHERE子句中。如果连一个OR连接项都没有索引，然后必须进行全表扫描才能找到行ID由一个术语生成，如果SQLite必须进行完整的表扫描，它不妨在原始表上执行此操作，并在中获得所有结果无需干扰工会运作和后续工作的单程通行证二进制搜索。

我们可以看到OR-by-UNION技术也可以用于在where子句连接了术语的查询中使用多个索引通过AND，使用交集运算符代替并集。许多SQL数据库引擎就可以做到这一点。但使用后性能有所提高只有一个索引很小，所以SQLite没有实现该技术此时。然而，未来版本的SQLite可能会得到增强，以支持双向交叉口。

2排序

SQLite（与所有其他SQL数据库引擎一样）也可以使用索引除了加快速度之外，还要满足查询中的ORDER BY子句查找。换句话说，索引可以用于加快排序以及搜索。

当没有合适的索引可用时，使用ORDER BY的查询子句必须作为单独的步骤进行排序。考虑以下查询：

从水果中选择*订购水果；

SQLite通过收集查询的所有输出，然后通过分拣机运行输出。

图16：无索引排序

如果输出行数为K，则排序所需的时间为与KlogK成比例。如果K较小，排序时间通常为不是一个因素，但在像上面这样的查询中，其中K==N，时间排序所需的时间可能比执行全表扫描。此外，整个输出累加为临时存储（可能在主内存或磁盘上，取决于各种编译时和运行时设置）这意味着需要大量临时存储才能完成查询。

2.1.按行ID排序

由于排序可能很昂贵，SQLite很难转换ORDER BY子句转换为no-ops。如果SQLite确定输出将自然地以指定的顺序出现，然后不进行排序。因此，例如，如果以rowid顺序请求输出，则不进行排序将完成：

SELECT*FROM fruitsforsale ORDER BY rowid（从水果中选择*）；

图17：按行ID排序

您还可以请求这样的反向排序：

通过rowid DESC从水果订单中选择*；

SQLite仍将省略排序步骤。但为了输出到以正确的顺序出现，SQLite将从开始进行表扫描并朝着开始的方向努力，而不是从开始并接近结束，如所示图17.

2.2.按索引排序

当然，按rowid排序查询的输出很少有用。通常人们希望按其他列对输出进行排序。

如果ORDER BY列上有可用的索引，则可以使用该索引用于排序。考虑按“水果”排序的所有项目的请求：

从水果中选择*订购水果；

图18：使用索引排序

Idx1索引从上到下扫描（如果是，则从下到上扫描使用“ORDER BY fruit DESC”）以查找每个项目的行ID按水果排序。然后对每个rowid进行二进制搜索以进行查找并输出该行。通过这种方式，输出按请求的顺序出现无需收集整个输出并使用单独的步骤对其进行排序。

但这真的能节省时间吗？中的步骤数原始无索引排序与NlogN成比例，因为这就是对N行进行排序所需的时间。但当我们使用Idx1作为如图所示，我们必须执行N个rowid查找，每个查找花费logN时间，因此NlogN的总时间相同！

SQLite使用基于成本的查询计划器。当有两种或更多方式时在解决相同的查询时，SQLite尝试估计使用每个计划运行查询所需的时间，然后将该计划与最低估计成本。成本主要根据估计值计算时间，因此根据表大小和可用的WHERE子句约束等等。但总的来说如果没有其他排序，那么可能会选择索引排序原因，因为它不需要累加整个结果集在分类之前进行临时存储，因此使用的临时存储要少得多。

2.3.按覆盖索引排序

如果覆盖索引可以用于查询，则多个rowid查找可以避免，并且查询的成本大大降低。

图19：使用覆盖索引进行排序

使用覆盖索引，SQLite可以简单地将索引从一端移动到另一个，并按与N成比例的时间交付输出分配一个大缓冲区来保存结果集。

三。同时搜索和排序

前面的讨论将搜索和排序视为独立的话题。但在实践中，人们通常会想搜索同时进行排序。幸运的是，有可能做到这一点使用单个索引。

3.1.使用多列索引进行搜索和排序

假设我们想查一下分类的各种橙子的价格它们生长的州的顺序。查询如下：

从果品销售中选择价格WHERE fruit='Orange'按州订购

查询在WHERE子句中同时包含搜索限制和order BY子句中的排序顺序。搜索和排序可以使用两列索引Idx3同时完成。

图20：按多列索引搜索和排序

查询对索引进行二进制搜索以查找行的子集有水果的=“橙色”。（因为水果柱是最左边的柱索引的行是按排序的顺序排列的，所有这些行将相邻。）然后从上到下扫描匹配的索引行底部获取原始表的行ID，对于每个行ID对原始表进行二进制搜索以查找价格。

您会注意到，上图中没有“排序”框。查询的ORDER BY子句已成为no-op。不必进行排序在这里完成，因为输出顺序是由state列和state列也恰好是中水果列之后的第一列索引。因此，如果我们扫描索引中具有相同值的条目水果栏从上到下，这些索引条目保证按状态列排序。

3.2.使用覆盖索引进行搜索和排序

A类覆盖指数也可以同时用于搜索和排序。考虑以下内容：

选择*FROM fruitforsale WHERE fruit='Orange'ORDER BY state

图21：按覆盖索引搜索和排序

如前所述，SQLite执行单二进制搜索覆盖层中的行范围满足WHERE子句的索引，从顶部到底部以获得所需的结果。保证满足WHERE子句的行是相邻的因为WHERE子句是最左边的等式约束索引的列。并通过扫描匹配的索引行自上而下，输出保证按状态排序，因为状态列是水果列右侧的下一列。因此，结果查询非常有效。

SQLite可以使用类似的技巧来降低ORDER BY：

选择*FROM fruitforsale WHERE fruit='Orange'按州DESC订购

遵循相同的基本算法，除了这次匹配的行从下到上而不是从上到下扫描索引的，这样状态将按降序出现。

3.3.使用索引进行部分排序（也称为块排序）

有时，使用索引只能满足ORDER BY子句的一部分。例如，考虑以下查询：

从水果中选择*按水果、价格订购

如果覆盖索引用于扫描，则会出现“fruit”列自然以正确的顺序排列，但当有两行或多行同样的水果，价格可能不合理。发生这种情况时，SQLite有很多小种类的水果，每种水果有不同的价值而不是一个大的分类。下面的图22说明了这个概念。

图22：按索引部分排序

在这个例子中，不是7个元素的单一排序，而是是5种单元素，1种2元素水果箱==“橙色”。

进行许多较小排序而不是单个较大排序的优点是：

多个小排序比单个排序使用更少的CPU周期大型排序。
每个小排序都是独立运行的，这意味着信息少得多需要随时存放在临时仓库中。
ORDER BY中已经按正确顺序排列的那些列由于可以从排序键中省略索引，因此进一步减少了存储需求和CPU时间。
输出行可以作为每个小排序返回到应用程序完成，并且早在表扫描完成之前。
如果存在LIMIT子句，则可能会避免扫描整个桌子。

由于这些优点，SQLite总是尝试使用索引，即使不可能按索引进行完全排序。

4WITH OUT ROWID表格

上述基本原则适用于两个普通的rowid表和不带ROWID桌子。唯一的区别是作为键的rowid列表中最右边的项被替换为主键。