深入 SQLite 前端：联接表排序

Source: Dev.to

概览

即使你的 WHERE 子句已经完美优化，糟糕的连接顺序仍然会导致查询变慢。SQLite 使用一种简单但有效的连接策略：它始终将连接执行为嵌套循环。FROM 子句中表的顺序决定了这些循环的嵌套方式。

连接执行模型

对于如下查询：

SELECT * FROM A JOIN B;

SQLite 大致按以下方式处理：

for each row in A:          # outer loop
    for each row in B:      # inner loop
        process the combination

• 第一张表 → 外层循环
• 最后一张表 → 内层循环

如果外层循环包含大量行，迭代次数会迅速增长。例如，每张表都有 100 万行时：

• A 为外层 → B 为内层 → 1 M × 1 M 次操作
• B 为外层 → A 为内层 → 也会很大，但成本可能因索引而不同。

目标是尽可能早地减少迭代次数。

自动重新排序

默认情况下 SQLite 按查询中写出的顺序执行，但当它发现更便宜的执行计划时可以重新排序表。SQLite 使用 贪心算法：

1. 首先挑选成本最低的表进行处理。
2. 反复加入下一个成本最低的表。
3. 直到所有表都被放置完毕。

如果两个选项成本相等，SQLite 会回退到原始查询顺序。

成本因素

SQLite 通过以下几个因素估算成本：

• 索引是否可用
• 索引的选择性
• 是否可以避免排序
• 估计需要扫描的行数

能够把 100 万行缩减到 1 行的索引价值极高，而把 100 万行缩减到 90 万行的索引几乎没有帮助。

运行 ANALYZE 很重要，因为它收集统计信息，提升 SQLite 成本估算的准确性。

索引嵌套循环连接

当表在连接列上有索引时，SQLite 通常会把该表放在 内层循环。这使得对外层循环的每一行都能高效地进行查找，这种技术称为 索引嵌套循环连接。

SQLite 不会扫描整个内层表，而是使用索引快速定位匹配的行。

不同连接类型的重新排序规则

• 内连接：SQLite 可以自由重新排序表，因为结果不依赖顺序。
• 外连接（LEFT OUTER JOIN、RIGHT OUTER JOIN）：顺序对正确性至关重要，SQLite 不会 重新排序外连接。不过，围绕外连接的内部连接表如果能提升性能，仍可能被重新排序。

当你使用 ON 或 USING 子句时，SQLite 会在优化之前把它们内部转换为额外的 WHERE 条件，因此所有 WHERE 子句的优化仍然适用。

强制特定顺序

如果需要显式控制连接顺序，可使用 CROSS JOIN。表会严格按照你写的顺序出现：

SELECT * 
FROM table1 
CROSS JOIN table2;

• table1 → 外层循环
• table2 → 内层循环

当自动重新排序不合适时，这种技巧可以强制所需的嵌套方式。

后续步骤

下一主题是 索引选择，即 SQLite 在存在多个候选索引时决定使用哪一个。理解连接排序和索引选择两者，有助于编写在 SQLite 上高效运行的查询。