SQLite 应该使用哪个索引？

Source: Dev.to

示例：在两个索引之间进行选择

考虑一个拥有多个索引的表：

-- Example table definition
CREATE TABLE table1 (
    id INTEGER PRIMARY KEY,
    x  INTEGER,
    y  INTEGER,
    z  TEXT
);

-- Indexes on separate columns
CREATE INDEX i1 ON table1(x);
CREATE INDEX i2 ON table1(y);

现在来看下面的查询：

SELECT z FROM table1 WHERE x = 5 AND y = 6;

SQLite 有两种选择：

1. 使用索引 i1 来查找 x = 5 的行，然后再过滤 y = 6。
2. 使用索引 i2 来查找 y = 6 的行，然后再过滤 x = 5。

这两种做法都是合法的，但它们的成本可能大相径庭。SQLite 会估算每个选项需要的工作量，并选择成本最低的那个。决策依据的启发式包括：

• 预计会匹配多少行
• 索引的选择性如何
• 查找后需要进行多少过滤

如果有统计数据可用，SQLite 能做出更好的决定。这时 sqlite_stat1 表就派上用场——它存储了每个列值通常对应多少行的信息，使 SQLite 能估算哪个索引能最大程度地缩小结果集。通常会倾向于返回行数更少的索引。

覆盖默认选择

有时你可能想让 SQLite 放弃使用某个特定索引。SQLite 提供了一种使用一元 + 运算符的微妙机制：

SELECT z FROM table1 WHERE +x = 5 AND y = 6;

+ 运算符在功能上不做任何事，但它会阻止 SQLite 使用列 x 上的索引。这样会迫使优化器考虑其他索引，例如列 y 上的 i2。这是一种不改变查询语义、轻量级地影响优化器的办法。

在 WHERE 与 ORDER BY 之间取得平衡

索引的选择不仅关乎行过滤；SQLite 还会考虑排序需求。

带 ORDER BY 的示例

SELECT * FROM table1 WHERE x = 5 ORDER BY y;

此时 SQLite 面临一个权衡：

• 使用 x 上的索引 来高效过滤，然后在 y 上对结果集进行排序。
• 使用 y 上的索引 直接满足 ORDER BY 子句，可能会以过滤效率稍低为代价。

SQLite 会评估这两种方案，并选择整体执行速度最快的那一个。有时 SQLite 会牺牲稍差的过滤效率，以避免代价高昂的排序操作。如果没有合适的索引能够满足 ORDER BY 子句，SQLite 必须使用临时排序器（一个瞬时的内存结构）手动排序。过程大致如下：

open sorter
where-begin
    extract required columns
    build a record
    generate sort key
    insert into sorter
where-end
sort
for each sorted entry
    extract data
    return result
close sorter

虽然这种方式可行，但会增加内存使用、额外的排序处理，并在大数据集上带来潜在的性能开销。因此，SQLite 总是尽可能使用索引来完成 ORDER BY。

决策之间的相互作用

• WHERE 子句 决定候选索引。
• 连接顺序 决定何时访问表。
• 索引选择 决定如何访问表。
• ORDER BY 可能影响首选的索引。

一次决策可能影响：

• 扫描的行数
• 是否需要排序
• 整体执行时间

了解 SQLite 如何做出这些决策，有助于你设计更合适的索引，并编写与优化器相匹配的查询。

下一部分将探讨 GROUP BY 与 MIN/MAX 的优化，SQLite 在这些场景下会采用额外的策略来高效地聚合和汇总数据。