4.3 主刀医生：查询优化器与SQL改写

4.3 主刀医生：查询优化器与 SQL 改写

本节摘要：优化器基于成本模型在候选执行路径里选一条，多数时候比人准，但统计失真与复杂语句会让它误诊。本节看它的判断逻辑、两个经典误诊病例，以及人们仍需手工改写的常用手法。

主刀医生怎么想

优化器给每条候选路径算成本：扫多少行、回表多少次、内存能不能装下排序。它的依据是索引统计信息（基数、区分度）：

SHOW INDEX FROM clinic_order;
-- Cardinalion 列即基数：索引列上不同值的个数
​

区分度（基数 / 总行数）低的列，比如 status 只有 5 个值，单独做索引几乎没用——优化器会算出回表成本高于全表扫，直接弃用。

误诊病例两则

病例一：统计过期选错索引。 表大量写入后统计偏旧，优化器放着区分度高的索引不用。处方是 ANALYZE TABLE clinic_order; 重算统计，再复测执行计划。

病例二：大偏移绕过索引。 某报表 SQL 用 OR 连接两个可走索引的条件后变成 index_merge，反而更慢。拆成两条 UNION 各走各的索引后快了一个量级：

-- 改写前：index_merge 交集回表，慢
SELECT * FROM clinic_order WHERE user_id = 1001 OR order_id = 88;
-- 改写后：两条各走主键/二级索引
SELECT * FROM clinic_order WHERE user_id = 1001
UNION ALL
SELECT * FROM clinic_order WHERE order_id = 88 AND user_id <> 1001;
​

值得记住的改写手法

-- 子查询延迟关联：先在索引里筛出主键，再回表取整行
SELECT o.* FROM clinic_order o
JOIN (
  SELECT order_id FROM clinic_order
  WHERE status = 2 ORDER BY created_at DESC LIMIT 100, 20
) t ON o.order_id = t.order_id;
​

-- 深聚合拆分：COUNT 与列表分两次查，别一条语句既排序又计数
SELECT COUNT(*) FROM clinic_order WHERE status = 2;
SELECT * FROM clinic_order WHERE status = 2 ORDER BY created_at DESC LIMIT 20;
​

图：优化器决策的输入与输出

💡 关键直觉：优化器不需要被"教会"，它需要被"喂饱"——准确的统计信息。手工强制索引提示是抗生素，能救急但别当饭吃，数据分布一变它就成了新的病灶。

直方图：给优化器补充常识

统计信息之外，8.0 引入直方图为优化器补充"数据分布长什么样"的常识。没有直方图时，优化器只知道 status 有几个不同值，不知道九成订单集中在状态 2；有了直方图，它就能算出 status=2 的查询走索引是亏本买卖：

ANALYZE TABLE clinic_order UPDATE HISTOGRAM ON status, user_id WITH 100 BUCKETS;
-- 查看已有直方图
SELECT * FROM information_schema.column_statistics
WHERE table_name = 'clinic_order'\G
​

直方图不会自动更新，大表数据分布发生结构性变化后要重跑。它是"统计过期"这类误诊的预防针，也特别适合那种"列上有索引、列也在条件里、优化器就是不用"的悬案——先看直方图在不在、还准不准。

三个改写手法的适用边界

延迟关联、UNION 拆分、查询拆分三板斧各有边界，用错场合会白忙。延迟关联适合"取整行但筛选排序能在索引里完成"的深分页与列表页，前提是有一棵能覆盖筛选排序的联合索引，没有的话先补索引。UNION 拆分适合 OR 两端各自高区分度、合在一起反而让优化器为难的场景，两端条件若高度重叠，拆开等于扫两遍，更慢。查询拆分适合列表页"数据加总数"的需求，总数走缓存或定时统计表，别让每页翻页都付一次 COUNT 的钱。

-- 延迟关联的完整形态：内层只碰索引列，外层才取整行
SELECT o.* FROM clinic_order o
JOIN (SELECT order_id FROM clinic_order
      WHERE user_id = 1001
      ORDER BY created_at DESC LIMIT 990, 20) t
  ON o.order_id = t.order_id;
​

一个容易被忽略的对照实验：改写前后各拍一次 EXPLAIN，对比驱动顺序与 rows。没有对照的优化是玄学，有对照的优化才叫处方。

优化器提示：最后的手铐

当统计喂饱了、直方图补了、优化器仍固执选错路，才动用提示：

SELECT * FROM clinic_order FORCE INDEX (idx_user_created)
WHERE user_id = 1001 AND status = 2;
​

手铐上容易摘不下：数据量变化后强制的选择可能从正确变成灾难，而提示会躺在 SQL 里继续生效。团队里每一条 FORCE INDEX 都应该有注释说明当初为什么强制，并列入定期复评清单。多数情况下，更耐用的做法是让统计信息与索引设计说服优化器，而不是给它上手铐。

一个改写实例的完整账目

收一个完整实例做本章的压轴账目。症状：订单列表页按状态筛加时间倒序，四秒。拍片：type 为 ref，rows 一百二十万，Extra 出现 filesort。病理：单列 status 索引低区分度，扫完百万行还要按 created_at 排序。手术分两刀，先建联合索引，再用延迟关联：

ALTER TABLE clinic_order ADD KEY idx_status_created (status, created_at);

SELECT o.* FROM clinic_order o
JOIN (SELECT order_id FROM clinic_order
      WHERE status = 2
      ORDER BY created_at DESC LIMIT 40, 20) t ON o.order_id = t.order_id;
​

复测：内层 Using index 完全覆盖，rows 降到六十，外层二十次主键点查，整页两百毫秒内。账目里最值钱的不是那两个索引技巧，而是"拍片、病理、手术、复测"四步闭环——任何一条 SQL 的优化都值得留一份这样的四步记录，它就是团队的性能病例档案。

本节要点回顾

• 成本模型：行数、回表、内存是三大成本项，低区分度列不值得单列索引
• 误诊根源：统计过期、复杂 OR、超大偏移
• 改写三板斧：延迟关联、UNION 拆分、查询拆分