6.3 关键优化技术
6.3 关键优化技术：下推、扁平化、IN 化与排序消除
本节摘要：优化器每天都在自动做一批"改写题"：把谓词推到最内层、把子查询摊平成连接、把相关子查询转成 IN、把排序交给索引。本节盘点这四类技术的触发条件与失效场景，每项配一个"优化器吃到糖"与"没吃到糖"的对照计划，最后给一张查询改写自查清单。
谓词下推：把过滤送到最早的时刻
下推（push-down）的目标是让行在进入上层运算之前就被过滤掉。它在三个层面发生：
-- 下推生效：过滤条件只涉及单个表，能推进到该表的扫描层 SELECT o.id FROM orders o JOIN users u ON o.user_id = u.id WHERE u.region = 'CN' AND o.amount > 100; -- 计划：SEARCH u USING INDEX idx_region (region=?) 先筛出中国用户 -- 再 SEARCH o USING INDEX idx_orders_user (user_id=?) 逐用户取单 -- 下推失效：过滤条件用了聚合后的别名，只能等分组完成 SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id HAVING avg_sal > 20000; -- HAVING 无法下推，先全分组再过滤（SQLite 甚至要求聚合查询的过滤写 HAVING）
SQLite 的下推深度在 3.2x 系列版本后明显增强，能把 WHERE 条件推进子查询内部；PostgreSQL 的谓词下推在分区表上还有"推到分区裁剪"的额外收益（扫更少的分区文件），MySQL 的分区裁剪同样吃下推的饭。判断下推是否生效，看计划里 SCAN 或 SEARCH 行出现的位置：条件对应的表扫描是否已经只碰"该碰的行"。
子查询扁平化与 IN 化
两套改写都围绕"子查询"这个性能雷区，方向相反：
扁平化把不相关的标量与 IN 子查询摊平成连接，让它们进入连接顺序搜索的候选池：
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000); -- 扁平化后 ≈ SELECT DISTINCT name FROM users JOIN orders ... -- 计划从「外层逐行执行子查询」变成「连接顺序可搜索」
IN 化（且叫它反方向的救赎）处理扁平化救不了的相关子查询：WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000) 这类每行都要探测的形态，SQLite 会尝试把它转成对内层表的索引查找循环——判断吃没吃到糖，看内层出现的是 SEARCH 还是 SCAN：
-- 吃到糖：内层是 SEARCH（索引探测） -- SEARCH o USING INDEX idx_orders_user (user_id=?) -- 没吃到：内层是 SCAN（每行全扫内层表，复杂度乘以 10 万） -- SCAN o
⚠️ 内层 SCAN 的相关子查询是 SQLite 慢查询的第一大来源。改写方向优先级：先看能否扁平化成 JOIN；不能的话确保内层条件有索引；再不行考虑物化——先把内层结果存临时表再连接。
ORDER BY 消除与 LIMIT 的联合红利
第 5 章讲过排序消除的机制，这里补上它与 LIMIT 的化学反应。ORDER BY salary DESC LIMIT 10 在无索引时需要物化全部行再排序；有匹配排序的索引时，从索引尾端倒着读 10 行即完事，成本从"全表加排序"降到"10 次索引步进"。计划里的判别标志是是否出现 USE TEMP B-TREE FOR ORDER BY：
CREATE INDEX idx_emp_salary ON employees(salary DESC); EXPLAIN QUERY PLAN SELECT name FROM employees ORDER BY salary DESC LIMIT 10; -- 无 TEMP B-TREE：走索引倒序，LIMIT 直接截断
MySQL 与 PostgreSQL 同享这份红利，且 PG 的 Top-N heapsort 在无索引时也能只维护 10 个名额的堆——SQLite 的外部排序器没有这个特化，大数据量加 LIMIT 的查询在 SQLite 上尤其依赖索引消除排序。
改写自查清单
写完或改完一条查询，按顺序过五道闸：
- 计划里还有 SUBQUERY 加 SCAN 吗——有则优先扁平化或加内层索引。
- 每个表扫描是 SEARCH 吗——SCAN 出现在大表上就要问"为什么索引没用上"。
- ORDER BY 有 TEMP B-TREE 吗——大结果集排序考虑复合索引消除。
- 覆盖索引机会用了吗——查询列全进索引时计划出现 USING COVERING INDEX，回表归零。
- 改写后 ANALYZE 过吗——新索引的统计不喂给优化器，它可能继续视而不见。
对照三库的改写习惯差异：MySQL 用户依赖 hint 兜底的习惯在 SQLite 行不通（它没有等价体系），PostgreSQL 用户习惯的 CTE 物化（MATERIALIZED 关键字）在 SQLite 里行为不同——3.35 之后 SQLite 的 CTE 默认按引用展开，引用几次算几次，把可能重复计算的 CTE 当成物化视图用会翻车。跨库写查询时，这三条方言差异值得记在团队文档里。
常见问题速答
**给查询加 LIMIT 1 能加速吗？**取决于是否已找到可停的路径。点查（唯一索引等值）本就一行即停，LIMIT 1 没有增量收益；范围或排序查询里，LIMIT 让优化器优先考虑"读序即排序"的索引路径，一旦计划从"物化全部再排序"切换到"沿索引取前 N 行"，提速可达数量级。反例：LIMIT 出现在无序的分页查询里，只是截断输出，前面的扫描成本分文未省。判断标准看计划里有没有 TEMP B-TREE 与扫描行数的变化。
**IN 一万个值，会不会拖垮优化器？**会拖慢编译：SQLite 要为每个值做循环探测的计划，编译时间与值数量线性相关，万级 IN 列表的编译耗时会进入十毫秒级，且字节码体积膨胀挤占语句缓存。改写方向：值来自另一张表时改 JOIN 或 IN 子查询（可被扁平化）；值在应用内存里时考虑临时表加连接。MySQL 对超长 IN 有相似的处理历史（8.0 改进了范围优化），PostgreSQL 同样建议大集合走 JOIN——三个引擎的共识是"大集合属于表，不属于语句文本"。
本节要点回顾
- 下推让过滤尽早发生；聚合后的过滤（HAVING）永远无法下推。
- 扁平化救 IN 子查询，IN 化救相关子查询；判别标志是内层 SEARCH 还是 SCAN。
- 排序消除配 LIMIT 是 SQLite 上性价比最高的改写；无索引时它要物化全部行。
- 五道闸自查清单足以覆盖九成慢查询改写；SQLite 无 hint，计划控制靠改写与统计。
优化器的题眼到此盘点完毕。下一章转向运行时的另一半：内存与缓存。