在开发CuteSqlite图形客户端的时候，需要用到SQL的语法解释，来对SQL语句进行优化。找了很多的SQL语法解释器，都不是十分满意，只有翻开Sqlite的源码，看看SQLite对SQL语句的解释过程，上一篇文章翻译了官方介绍SQLite查询优化器概述，本文翻译了官方介绍SQLite通过索引优化查询文章。
官方介绍SQLite通过索引优化查询：https://www.sqlite.org/queryplanner.html
开源项目CuteSqlite网址：https://github.com/shinehanx/CuteSqlite.git
目录
概述
SQL的最佳特性（在它的所有实现中，不仅仅是SQLite）是它是一种声明性语言，而不是一种过程语言。在SQL中编程时，你告诉系统你想计算什么，而不是如何计算，计算的任务被委托给SQL数据库引擎中的查询规划器子系统。
对于任何给定的SQL语句，可能有数百或数千甚至数百万种不同的算法来执行操作。所有这些算法都会得到正确的答案，尽管有些算法会比其他算法运行得更快。查询规划器是一个AI，它试图为每个SQL语句选择最快和最有效的算法。
大多数时候，SQLite中的查询规划器都做得很好。但是，查询规划器需要使用索引。这些索引通常必须由程序员添加。查询规划器AI很少会做出次优算法选择。在这些情况下，程序员可能希望提供额外的提示来帮助查询规划器更好地完成工作。
本文档提供有关SQLite查询规划器和查询引擎如何工作的背景信息。程序员可以使用这些信息来帮助创建更好的索引，并在需要时提供提示来帮助查询规划器。
SQLite查询规划器和下一代查询规划器文档中提供了其他信息。
1.搜索
1.1.没有索引的表格
SQLite中的大多数表由零个或多个行组成，这些行具有唯一的整数键（rowid或INTEGER PRIMARY KEY），后跟内容。(The例外是没有ROWID表。）这些行在逻辑上按照rowid递增的顺序存储。作为一个例子，本文使用了一个名为“FruitsForSale”的表，它将各种水果与它们生长的州和它们在市场上的单价联系起来。schema是这样的：
CREATE TABLE FruitsForSale( Fruit TEXT, State TEXT, Price REAL ); |
对于某些（任意）数据，这样的表可能逻辑上存储在磁盘上，如图1所示：
图1：表“FruitsForSale”的逻辑布局
在这个例子中，rowid不是连续的，但是它们是有序的。SQLite通常会创建以1开头的rowid，并且每增加一行就增加一行。但如果删除行，序列中可能会出现间隙。如果需要的话，应用程序可以控制分配的rowid，这样就不必在底部插入行。但无论发生什么，rowids总是唯一的，并且严格按照升序排列。
假设你想查询桃子的价格。查询将如下所示：
SELECT price FROM fruitsforsale WHERE fruit='Peach'; |
为了满足这个查询，SQLite读取表中的每一行，检查“fruit”列是否具有值“Peach”，如果是，则输出该行中的“price”列。下图2说明了这一过程。这种算法被称为全表扫描，因为为了找到感兴趣的一行，必须读取和检查表的整个内容。对于只有7行的表，全表扫描是可以接受的，但如果表包含7百万行，则全表扫描可能会读取兆字节的内容，以便找到一个8字节的数字。出于这个原因，通常会尝试避免全表扫描。
图2：全表扫描
1.2.通过Rowid查找
避免全表扫描的一种技术是通过rowid（或等效的INTEGER PRIMARY KEY）进行查找。要查找桃子的价格，可以查询rowid为4的条目：
SELECT price FROM fruitsforsale WHERE rowid=4; |
由于信息是按rowid顺序存储在表中的，因此SQLite可以使用二进制搜索找到正确的行。如果表包含N个元素，则查找所需行所需的时间与logN成正比，而不是像全表扫描那样与N成正比。如果表包含1000万个元素，这意味着查询将以N/logN的数量级或大约100万倍的速度进行。
图3：由Rowid创建
1.3.通过索引查找
通过rowid查找信息的问题是，您可能不关心“item 4”的价格-您想知道桃子的价格。所以rowid查找是没有帮助的。
为了使原始查询更高效，我们可以在“fruitsforsale”表的“fruit”列上添加一个索引，如下所示：
CREATE INDEX Idx1 ON fruitsforsale(fruit); |
索引是另一个类似于原始“fruitsforsale”表的表，但内容（在本例中是fruit列）存储在rowid之前，所有行都按内容顺序排列。图4给出了Idx1索引的逻辑视图。“fruit”列是用于对表的元素进行排序的主键，“rowid”是用于在两个或多个行具有相同的“fruit”时打破平局的次键。在本例中，rowid必须用作“橙子”行的决胜点。请注意，由于rowid在原始表的所有元素中始终是唯一的，因此“fruit”后跟“rowid”的组合键在索引的所有元素中都是唯一的。
图4：Fruit列上的索引
这个新的索引可以用来实现一个更快的算法，为原来的“价格桃子”查询。
SELECT price FROM fruitsforsale WHERE fruit='Peach'; |
查询首先在Idx1索引上对fruit ='Peach'的条目进行二分搜索。SQLite可以在Idx1索引上执行这种二分搜索，但不能在原始的FruitsForSale表上执行，因为Idx1中的行是按“fruit”列排序的。在Idx1索引中找到fruit ='Peach'的行后，数据库引擎可以提取该行的rowid。然后，数据库引擎对原始FruitsForSale表进行第二次二分搜索，以查找包含fruit ='Peach'的原始行。然后，SQLite可以从FruitsForSale表中的行提取price列的值。该过程如图5所示。
图5：桃子价格指数
SQLite必须使用上面显示的方法进行两次二进制搜索来找到桃子的价格。但是对于具有大量行的表，这仍然比执行全表扫描快得多。
1.4.多行结果
在前面的查询中，fruit ='Peach'约束将结果缩小到一行。但即使获得多行，同样的技术也有效。假设我们查的是橘子的价格而不是桃子的价格：
SELECT price FROM fruitsforsale WHERE fruit='Orange' |
图6：橙子价格的指数化价格
在这种情况下，SQLite仍然执行一个二进制搜索来查找索引的第一个条目，其中fruit ='Orange'。然后，它从索引中提取rowid，并使用该rowid通过二进制搜索查找原始表条目，并从原始表中输出价格。但是，数据库引擎并没有退出，而是前进到索引的下一行，对下一个fruit ='Orange'条目重复该过程。前进到索引（或表）的下一行比执行二分搜索成本低得多，因为下一行通常与当前行位于同一数据库页上。事实上，与二进制搜索相比，前进到下一行的成本是如此之低，以至于我们通常忽略它。所以我们估计这个查询的总成本是3个二进制搜索。如果输出的行数是K，表中的行数是N，那么一般来说，执行查询的成本与（K+1）*logN成正比。
1.5.多个AND连接的WHERE子句
接下来，假设您不仅要查找任何橙子的价格，而且要查找加利福尼亚产橙子的价格。适当的查询如下：
SELECT price FROM fruitsforsale WHERE fruit='Orange' AND state='CA' |
图7：加州橙子的索引
此查询的一种方法是使用WHERE子句的fruit='Orange'项来查找处理橙子的所有行，然后通过拒绝来自除加州以外的州的任何行来过滤这些行。这一过程如上文图7所示。在大多数情况下，这是一个完全合理的方法。是的，数据库引擎确实必须对后来被拒绝的佛罗里达橙子行进行额外的二进制搜索，所以它不如我们希望的那样高效，尽管对于许多应用程序来说它已经足够高效了。
假设除了关于“水果”的索引之外，还有关于“州”的索引。
CREATE INDEX Idx2 ON fruitsforsale(state); |
图8： state列上的索引
“state”索引的工作原理与“fruit”索引类似，因为它是一个新表，在rowid前面有一个额外的列，并按该额外列作为主键进行排序。唯一的区别是，在Idx2中，第一列是“state”而不是Idx1中的“fruit”。在我们的示例数据集中，“state”列中有更多的冗余，因此它们是更多的重复条目。仍然使用rowid来解析关系。
使用“state”上的新Idx 2索引，SQLite有另一个查找加州橙子价格的选项：它可以查找包含来自加州的水果的每一行，并过滤掉那些不是橙子的行。
图9：加州橙子的索引
使用Idx 2而不是Idx 1会导致SQLite检查一组不同的行，但最终得到相同的答案（这非常重要-记住索引永远不应该改变答案，只会帮助SQLite更快地获得答案），并且它做了相同的工作量。因此，Idx 2索引在这种情况下对性能没有帮助。
在我们的示例中，最后两个查询花费相同的时间。那么SQLite会选择Idx1还是Idx2呢？如果ANALYZE命令已经在数据库上运行，这样SQLite就有机会收集关于可用索引的统计信息，那么SQLite将知道Idx1索引通常会将搜索范围缩小到单个项目（我们的fruit='Orange'的示例是这个规则的例外），而Idx2索引通常只会将搜索范围缩小到两行。因此，如果其他条件都相同，SQLite将选择Idx1，希望将搜索范围缩小到尽可能少的行数。这种选择是唯一可能的，因为分析提供的统计数据。如果ANALYZE还没有运行，那么选择使用哪个索引是任意的。
1.6.多字段索引
为了使WHERE子句中包含多个AND连接项的查询获得最佳性能，您确实需要一个多列索引，其中每个AND项都有列。在本例中，我们在FruitsForSale的“fruit”和“state”列上创建一个新索引：
CREATE INDEX Idx3 ON FruitsForSale(fruit, state); |
图1：两列索引
多列索引遵循与单列索引相同的模式;索引列添加在rowid前面。唯一的区别是现在添加了多个列。最左边的列是用于对索引中的行进行排序的主键。第二列用于断开最左侧列中的连接。如果有第三列，它将被用来打破前两列的联系。对于索引中的所有列，依此类推。因为rowid保证是唯一的，所以索引的每一行都是唯一的，即使两行的所有内容列都相同。这种情况在我们的样本数据中不会发生，但有一种情况（fruit ='Orange'），第一列上有一个平局，必须由第二列打破。
考虑到新的多列Idx3索引，SQLite现在可以仅使用2个二进制搜索来查找加州橙子的价格：
SELECT price FROM fruitsforsale WHERE fruit='Orange' AND state='CA' |
图11：使用两列索引的SQL
通过在两个列上使用受WHERE子句约束的Idx3索引，SQLite可以对Idx3执行一次二分搜索，以找到加州橙子的一个rowid，然后执行一次二分搜索，以在原始表中找到该项目的价格。没有死胡同，也没有浪费的二进制搜索。这是一个更有效的查询。
注意，Idx3包含与原始Idx1相同的所有信息。所以如果我们有Idx3，我们就不再需要Idx1了。使用Idx3可以通过简单地忽略Idx3的“state”列来满足“price of peaches”查询：
SELECT price FROM fruitsforsale WHERE fruit='Peach' |
图12：多列索引上的单列索引
因此，一个很好的经验法则是，您的数据库模式永远不应该包含两个索引，其中一个索引是另一个索引的前缀。删除列数较少的索引。SQLite仍然能够使用更长的索引进行有效的查找。
1.7.覆盖索引
“加州橙子的价格”查询通过使用两列索引变得更加高效。但是SQLite可以做得更好，使用三列索引，其中还包括“价格”列：
CREATE INDEX Idx4 ON FruitsForSale(fruit, state, price); |
图13：覆盖索引
这个新索引包含查询所使用的原始FruitsForSale表的所有列-包括搜索项和输出。我们称之为“覆盖指数”。因为所有需要的信息都在覆盖索引中，SQLite永远不需要查询原始表来找到价格。
SELECT price FROM fruitsforsale WHERE fruit='Orange' AND state='CA'; |
图14：使用覆盖索引的查询
因此，通过在索引的末尾添加额外的“输出”列，可以避免引用原始表，从而将查询的二进制搜索次数减少一半。这是性能上的一个常数因子改进（大约是速度的两倍）。但另一方面，它也只是一种改进;两倍的性能提升远不如第一次索引表时看到的一百万倍的提升那么引人注目。对于大多数查询，1微秒和2微秒之间的差异不太可能被注意到。
1.8. WHERE子句中的OR连接项
只有当查询的WHERE子句中的约束条件通过AND连接时，多列索引才有效。因此，当搜索既是橙子又是生长在加州的项目时，Idx 3和Idx 4是有用的，但如果我们想要所有的项目都是橙子或生长在加州的项目，那么这两个索引都没有那么有用。
SELECT price FROM FruitsForSale WHERE fruit='Orange' OR state='CA'; |
当在WHERE子句中遇到OR连接的术语时，SQLite分别检查每个OR术语，并尝试使用索引来查找与每个术语关联的rowid。然后，它使用结果rowid集的并集来查找最终结果。下图说明了这一过程：
图15：带有OR约束的查询
上面的图表暗示SQLite首先计算所有rowid，然后在开始对原始表进行rowid查找之前将它们与联合操作组合。实际上，rowid查找是与rowid计算穿插的。SQLite每次使用一个索引来查找rowid，同时记住以前见过的rowid，以避免重复。不过，这只是一个实现细节。该图虽然不是100%准确，但提供了一个很好的概述。
为了使上面显示的OR-by-UNION技术有用，必须有一个可用的索引来帮助解析WHERE子句中的每个OR连接项。如果连一个OR连接的项都没有索引，那么就必须进行全表扫描，以便找到由一个项生成的rowids，如果SQLite必须进行全表扫描，它也可以在原始表上进行扫描，并在一次传递中获得所有结果，而不必混淆联合操作和后续的二进制搜索。
我们可以看到如何利用OR-by-UNION技术来对查询使用多个索引，其中WHERE子句中的项通过AND连接，通过使用交集运算符代替并集。许多SQL数据库引擎都可以做到这一点。但是，仅使用单个索引的性能增益很小，因此SQLite目前没有实现该技术。但是，未来版本的SQLite可能会增强以支持AND by INTERSECT。
2.排序
SQLite（与所有其他SQL数据库引擎一样）也可以使用索引来满足查询中的ORDERBY子句，除了加快查找。换句话说，索引可以用来加速排序和搜索。
当没有适当的索引可用时，必须将具有ORDER BY子句的查询作为单独的步骤进行排序。考虑这个查询：
SELECT * FROM fruitsforsale ORDER BY fruit; |
SQLite通过收集查询的所有输出，然后通过排序器运行该输出来处理此问题。
图16：没有索引的排序
如果输出行数为K，则排序所需的时间与KlogK成正比。如果K很小，排序时间通常不是一个因素，但是在上面的查询中，K==N，排序所需的时间可能比进行全表扫描所需的时间要长得多。此外，整个输出都累积在临时存储器中（可能在主存或磁盘上，取决于各种编译时和运行时设置），这可能意味着需要大量的临时存储来完成查询。
2.1.按Rowid排序
由于排序可能开销很大，SQLite努力将ORDER BY子句转换为无操作。如果SQLite确定输出将按照指定的顺序自然出现，则不进行排序。因此，例如，如果您以rowid顺序请求输出，则不会进行排序：
SELECT * FROM fruitsforsale ORDER BY rowid; |
图17：按Rowid排序
你也可以像这样请求一个逆序排序：
SELECT * FROM fruitsforsale ORDER BY rowid DESC; |
SQLite仍然会省略排序步骤。但是为了使输出以正确的顺序出现，SQLite将从末尾开始扫描表，而不是如图17所示从开头开始扫描表。
2.2.按索引排序
当然，按rowid对查询的输出进行排序很少有用。通常人们希望通过其他列来排序输出。
如果ORDERBY列上有可用的索引，则该索引可用于排序。考虑对所有按“水果”排序的项目的请求：
SELECT * FROM fruitsforsale ORDER BY fruit; |
图18：使用索引排序
Idx 1索引从上到下扫描（或者如果使用“ORDER BY fruit DESC”，则从下到上扫描），以便按水果的顺序找到每个项目的rowid。然后，对于每个rowid，执行二进制搜索以查找并输出该行。通过这种方式，输出以请求的顺序显示，而不需要收集整个输出并使用单独的步骤对其进行排序。
但这真的能保存时间吗？原始无索引排序的步骤数与NlogN成正比，因为这是排序N行所需的时间。但是当我们使用Idx 1时，我们必须执行N次rowid查找，每次需要logN时间，所以NlogN的总时间是相同的！
SQLite使用基于成本的查询规划器。当有两种或两种以上的方法来解决同一个查询时，SQLite会尝试估计使用每个计划运行查询所需的总时间，然后使用估计成本最低的计划。成本主要是根据估计的时间计算的，因此这种情况可以根据表的大小和可用的WHERE子句约束等情况进行选择。但一般来说，索引排序可能会被选择，如果没有其他原因，因为它不需要在排序之前将整个结果集累积在临时存储中，因此使用更少的临时存储。
2.3.按覆盖索引排序
如果覆盖索引可以用于查询，那么可以避免多个rowid查找，并且查询的成本显著下降。
图19：使用覆盖索引进行排序
使用覆盖索引，SQLite可以简单地将索引从一端走到另一端，并在与N成比例的时间内交付输出，而无需分配一个大的缓冲区来保存结果集。
3.搜索与排序同时进行
前面的讨论将搜索和排序作为单独的主题。但在实践中，经常会出现这样的情况，即人们希望同时进行搜索和排序。幸运的是，使用单个索引可以做到这一点。
3.1.使用多列索引进行搜索和排序
假设我们想找出各种橙子的价格，这些橙子是按照它们生长的州来排序的。查询是这样的：
SELECT price FROM fruitforsale WHERE fruit='Orange' ORDER BY state |
查询在WHERE子句中包含搜索限制，在ORDER BY子句中包含排序顺序。使用两列索引Idx3可以同时完成搜索和排序。
图20：按多列索引搜索和排序
查询对索引执行二分搜索，以查找具有fruit ='Orange'的行的子集。（因为fruit列是索引的最左边的列，并且索引的行是按排序顺序排列的，所以所有这样的行都是相邻的。然后，它从上到下扫描匹配的索引行，以获得原始表的rowid，并对每个rowid在原始表上进行二分搜索以查找价格。
你会注意到在上面的图中没有“排序”框。查询的ORDERBY子句已经变成了一个空操作。这里不需要进行排序，因为输出顺序是按照state列的，并且state列恰好是索引中fruit列之后的第一列。因此，如果我们从上到下扫描fruit列中具有相同值的索引条目，则可以保证这些索引条目按state列排序。
3.2.用覆盖索引进行搜索和排序
覆盖索引也可以同时用于搜索和排序。考虑以下情况：
SELECT * FROM fruitforsale WHERE fruit='Orange' ORDER BY state |
图21：按覆盖索引搜索和排序
和前面一样，SQLite对覆盖索引中满足WHERE子句的行范围进行单二分搜索，从上到下扫描以获得所需的结果。满足WHERE子句的行保证是相邻的，因为WHERE子句是索引最左边列的相等约束。通过从上到下扫描匹配的索引行，可以保证输出是按状态排序的，因为状态列是水果列右边的下一列。因此，结果查询是非常有效的。
SQLite可以对降序的ORDER BY使用类似的技巧：
SELECT * FROM fruitforsale WHERE fruit='Orange' ORDER BY state DESC |
遵循相同的基本算法，只是这次索引的匹配行是从下到上而不是从上到下扫描的，因此状态将按降序显示。
3.3.使用索引的部分排序（也称为区块排序）
有时候，使用索引只能满足ORDER BY子句的一部分。例如，考虑以下查询：
SELECT * FROM fruitforsale ORDER BY fruit, price |
如果覆盖索引用于扫描，则“fruit”列将以正确的顺序自然出现，但是当有两个或更多行具有相同的水果时，价格可能会乱序。当这种情况发生时，SQLite会进行许多小的排序，每个排序对应一个不同的fruit值，而不是一个大的排序。下图22说明了这一概念。
图22：部分按索引排序
在这个例子中，不是一个7个元素的排序，而是有5个每个元素一个元素的排序和1个2个元素的排序，对于fruit=='Orange'的情况。
做许多小排序而不是一个大排序的优点是：
- 多个小型排序共同使用的CPU周期比单个大型排序少。
- 每个小排序都是独立运行的，这意味着在任何时候都需要在临时存储器中保存更少的信息。
- ORDERBY中那些由于索引而已经处于正确顺序的列可以从排序键中省略，从而进一步减少存储需求和CPU时间。
- 在每个小排序完成时，可以将输出行返回到应用程序，并且在表扫描完成之前。
- 如果存在LIMIT子句，则可以避免扫描整个表。
由于这些优点，SQLite总是尝试使用索引进行部分排序，即使不可能通过索引进行完全排序。
4.无ROWID表
上述基本原则适用于普通rowid表和WITHOUT ROWID表。唯一的区别是，作为表的键并在索引中显示为最右侧项的rowid列被PRIMARY KEY替换。
此页面最后修改于2022-10-26 13：30：36 UTC
译者安利一下CuteSqlite开源项目，很方便的创建和管理SQLite数据表的索引：
CuteSqlite管理数据表索引
CuteSqlite分析SQL和给出优化建议
CuteSqlite下载： https://github.com/shinehanx/CuteSqlite/releases
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
