针对索引上最左侧列的 MAX 查询进行 SQLITE 优化

Question

我注意到关于SQLite查询优化器的在线页面保证，如果有一个索引的最左边列是colA，则可以优化SELECT MAX(colA) FROM TABLE形式的查询。

但是，我不太清楚当使用索引根据WHERE相等子句缩小表时会发生什么，例如索引中的下一列是我要MAX的列。根据索引的结构，最大值应作为满足WHERE子句的索引子集中的最后一行快速访问。例如，给定一个关于colA和colB的索引，应该可以在不扫描与colA = 1相关的所有 6 行的情况下找到SELECT MAX(colB) FROM SillyTable WHERE colA = 1：

Index of SillyTable on colA, colB:
colA    colB    rowid
1       1       4
1       2       5
1       4       2
1       5       8
1       6       3     # This is the one
2       1       1
2       5       6
2       8       7

SQLite 是否真的优化了这样的查询，或者它会扫描满足WHERE子句的所有行？如果它执行扫描，如何更改查询以使其运行得更快？

我的具体用例类似于SillyTable示例。我创建了下表：

CREATE TABLE Product(
ProductTypeID      INTEGER NOT NULL,
ProductID          INTEGER NOT NULL,
PRIMARY KEY(ProductTypeID, ProductID),
FOREIGN KEY(ProductTypeID)
REFERENCES ProductType(ProductTypeID)
);

ProductTypeID对表格不是特别有选择性;我可能有很多行具有相同的ProductTypeID但不同的ProductID.EXPLAIN QUERY PLAN告诉我，我的查询使用为复合主键自动构建的索引，但无论是扫描还是二进制搜索使用索引找到的行子集，都是如此：

EXPLAIN QUERY PLAN SELECT MAX(ProductID) FROM Product
WHERE ProductTypeID = ?;
=>
SEARCH TABLE Product USING COVERING INDEX sqlite_autoindex_Product_1(ProductTypeID=?)

Answer 1

这显示在 EXPLAIN 输出中：

sqlite> 解释从产品中选择最大(产品 ID)，其中产品类型 ID = ？; 地址操作码 P1 P2 P3 P4 P5 注释      ----  -------------  ----  ----  ----  -------------  --  ------------- 0 初始化 0 17 0 00 从 17 开始  1 空 0 1 2 00 r[1..2]=空 2 OpenRead 1 3 0 k(2，，) 02 根=3 iDb=0;sqlite_autoindex_Product_13 变量 1 3 0 00 r[3]=参数(1，) 4 IsNull 3 13 0 00 if r[3]==NULL goto 13 5 亲和力 3 1 0 D 00 亲和力(r[3]) 6 SeekLE 1 13 3 1 00 key=r[3]     7 IdxLT 1 13 3 1 00 key=r[3]     8 列 1 1 4 00 r[4]=产品.产品ID 9 CollSeq 0 0 0 (二进制) 00               10 AggStep0 0 4 1 max(1) 01 accum=r[1] step(r[4]) 11 转到 0 13 0 00 max() 按索引 12 上一页 1 7 0 00               13 AggFinal 1 1 0 max(1) 00 accum=r[1] N=1 14 复制 1 5 0 00 r[5]=r[1]    15 结果行 5 1 0 00 输出=r[5] 16 停止 0 0 0 00               17 事务 0 0 1 0 01 使用StmtJournal=0 18 转到 0 1 0 00

为了使代码生成器更简单，SQLite 总是为聚合创建一个循环(第 6 行到第 12 行)。但是，对于max()，此循环在第一个成功步骤(第 11 行)后中止。