SQLite 查询优化器概述（九）_sqlite 优化器-优快云博客

本文链接：https://blog.youkuaiyun.com/lyfwwb/article/details/137225119

1. 引言

本文档概述了查询规划器和优化器如何用于 SQLite 工作。

给定一个 SQL 语句，可能有几十个、几百个甚至实现该语句的数千种方法，具体取决于复杂性语句本身和基础数据库架构。这查询规划器的任务是选择最小化的算法磁盘 I/O 和 CPU 开销。

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

从 3.8.0 版（2013-08-26）开始， SQLite查询规划器被重新实现为下一代查询规划器或“NGQP”。所有功能、技术、本文档中描述的算法适用于 3.8.0 之前的旧版查询规划器和 NGQP。欲了解更多信息 NGQP 与旧版查询规划器有何不同，请参阅 NGQP 的详细说明。

2. WHERE子句分析

查询的 WHERE 子句被分解为“terms”，其中每个术语通过 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
  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 子句术语，以便使用该索引。但是，所使用的索引列中不能有间隙。因此，对于上面的示例索引，如果没有 WHERE 子句术语约束 C 列，则约束 A 列和 B 列的项可以与索引一起使用，但不能与限制 d 到 z 列的术语一起使用。同样，通常不会使用索引列（用于索引目的）如果它们在右边仅受不等式约束的列。（有关例外情况，请参阅下面的跳过扫描优化。

对于表达式的索引，只要单词“column”是在上述文本中，可以用“索引表达式”代替（表示 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 子句约束可以以两种不同的方式处理。如果一个术语由多个包含公共列的子术语组成 name 并用 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)

上面重写的表达式是概念性的;包含 WHERE 子句或者不是真的以这种方式重写。 OR 子句的实际实现使用一种机制，即效率更高，即使不适用于没有 ROWID 表或无法访问“rowid”的表。不过语句捕获了实现的本质上图：使用单独的索引来查找候选结果行从每个 OR 子句项，最终结果是那些行。

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

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

5. LIKE优化

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

LIKE 或 GLOB 的右侧必须是字符串文本或绑定到字符串文本的参数这不是以通配符开头的。
不能通过以下方式使 LIKE 或 GLOB 运算符为 true 在左侧。这意味着：
1. LIKE 或 GLOB 运算符的左侧是名称具有 TEXT 相关性的索引列，或者
2. 右侧模式参数不以减号（“-”）或数字。
这种限制源于数字不排序的事实词典顺序。例如：9<10 但“9”>“10”。
用于实现 LIKE 和 GLOB 的内置函数不得已使用