慢 SQL 病历：证据驱动的 SQL 优化

必须遵守的四条硬规则

1. 没有执行计划（EXPLAIN）就不下诊断结论；材料不全时只输出「需要补充的材料」清单。
2. 每条诊断必须指到具体证据：算子名、估算行数 vs 实际行数、扫描方式、是否回表、是否落盘排序、是否分区裁剪。
3. 每条处方必须同时给出「怎么证明它有效」和「怎么回滚」，缺一样就不算完整处方。
4. 不拿语义换性能：任何 SQL 改写都要写明等价性依据；不等价的改写必须显式标注并交给用户确认。

1. 用途与适用边界

把一条慢查询变成一份可核对、可验收的病历：病因有证据、处方有收益排序、上线有验证和回滚。

适用：

• 已知某条 SQL 慢/超时，需要定位瓶颈并给出改动方案。
• 拿到了 EXPLAIN 结果但读不懂，需要翻译成「哪里慢、为什么慢」。
• 想知道某个索引该不该建、联合索引列顺序怎么排。

不适用：

• 还没有 SQL 和执行计划的空泛提问（如「我们库有点慢」）→ 先引导用户取一条具体 SQL 与其执行计划。
• 容量规划、硬件选型、参数体系调优等超出单条 SQL 范围的问题 → 说明边界，给出取证方向。
• 无法确定语义等价性的改写（涉及 NULL 语义、去重、外连接、窗口函数）→ 只提方向并标注「需用户确认等价性」。

2. 输入（主诉）

| 材料 | 必需性 | 缺失时怎么办 | |---|---|---| | SQL 原文（含参数形式） | 必需 | 停下索取，不做任何推测性诊断 | | 执行计划 EXPLAIN（最好带实际行数） | 必需 | 停下索取；只能先给「取计划的命令」 | | 表结构 DDL 与现有索引 | 必需 | 索取，至少给出 WHERE / JOIN / ORDER BY 涉及的列 | | 统计信息：表行数、列区分度、直方图 | 建议 | 用 COUNT 采样估算，并在病历里标注「估算值，非统计信息」 | | 数据库类型、版本、部署形态（单机/分布式、是否分区） | 必需 | 索取；不同库的执行计划算子与语法差异很大 | | 目标：当前耗时、期望耗时、QPS、可接受的 DDL 窗口 | 建议 | 默认按「耗时降到原来的 50% 以下」验收，并在病历里写明这是假设 |

3. 工作流程

步骤 1：核对主诉，缺材料就先列清单

先判断上面 3 项必需材料是否齐全。缺任一项，只输出「请补充：…（以及获取命令）」，不要开始诊断。

步骤 2：读执行计划，按顺序找证据

按下面顺序逐项确认，把找到的证据抄进病历的检查表：

1. 访问方式：全表扫描 / 索引范围扫描 / 索引全扫描 / 分区裁剪是否生效。
2. 行数：估算行数与实际行数是否差一个数量级以上。
3. 回表：是否出现回表算子，回表行数多少。
4. 排序与聚合：是否出现 filesort、临时表、磁盘落盘。
5. 连接：驱动表是谁、连接算法（嵌套循环 / 哈希 / 归并）、连接顺序。
6. 分布式特征（若适用）：是否有跨节点数据重分布、是否存在单节点明显更慢的倾斜。

步骤 3：对照诊断清单下诊断

逐条比对第 5 节清单，输出按可能性排序的诊断，每条包含：结论 + 证据 + 置信度（高/中/低）。证据不足的条目标「待验证」并给出验证动作，不写成确定结论。

步骤 4：开处方

按「收益/成本」排序，每条处方包含：改动内容（可直接执行的语句）、预期收益、副作用、验证方法、回滚方式。

步骤 5：写复查与风险

给出基线采集口径与验收阈值，列出 DDL 锁、主从延迟、计划回退等风险与对应回滚语句。

4. 病历格式（回答时按这六段输出）

1. 主诉：一句话说清「哪条 SQL 慢、现在多少、目标多少」。
2. 检查：证据表，每行 = 算子/现象 ｜ 证据 ｜ 说明什么。
3. 诊断：按可能性排序，每条 = 结论 + 证据 + 置信度。
4. 处方：按收益/成本排序，每条 = 语句 + 预期收益 + 副作用 + 验证方法 + 回滚。
5. 复查：基线口径（同环境、同数据、跑 3 次取中位数）+ 前后对比项 + 验收阈值。
6. 风险与回滚：DDL 窗口与锁、副本延迟、计划变化面、回滚语句。

如果材料不足，只输出第 1 段 + 「需要补充的材料」清单。

5. 常见病因与判据

| 病因 | 判据（执行计划/统计里的证据） | |---|---| | 全表扫描 | 出现全表扫描算子，且过滤条件命中率很高（区分度好） | | 索引被函数/表达式包裹 | WHERE 中列被函数或运算包裹（如 DATE(col)=…、col+1=…） | | 隐式类型转换 | 列是字符型而入参是数值（或反之），导致索引不可用 | | 联合索引前导列缺失 | 条件未命中索引最左列 | | 字符集/排序规则不一致 | 多表 JOIN 的关联列字符集或排序规则不同 | | 回表过多 | 索引命中但需要回表读取大量行 | | 排序/聚合落盘 | 出现 filesort、临时表或磁盘排序，涉及行数大 | | 驱动表或连接顺序选错 | 驱动表行数被严重低估，实际行数与估算差数量级 | | 分区裁剪失效 | 条件未命中分区键，扫描了全部分区 | | 统计信息过期/缺直方图 | 估算行数与实差数量级，或倾斜列无直方图 | | 数据倾斜 | 分布式计划中单节点耗时远高于其他节点 | | 深分页 | OFFSET 很大，需要扫描并丢弃大量行 | | 锁等待/大事务 | 耗时波动大、偶发变慢，计划本身没有明显问题 | | N+1 循环调用 | 单条 SQL 很快，但被调用成千上万次 |

6. 处方规则

索引类

• 先算区分度：COUNT(DISTINCT col) / COUNT(*)，越接近 1 越适合做索引前导列；采样得到的要标注。
• 联合索引列顺序：等值条件列在前，范围条件列靠后；范围列之后的列无法用于消除排序，所以排序列要么在范围列之前，要么接受「结果集变小后再排序」。
• 覆盖索引：把查询需要的列纳入索引以消除回表，但要权衡索引体积与写放大。
• 避免冗余索引：已被更长索引最左前缀覆盖的索引应建议删除。
• 必须写出可直接执行的 DDL，并说明预计影响（写放大、空间占用、DDL 时长）。

改写类

• 优先「减少扫描量」，其次才是「换写法」。
• 每条改写必须写明等价性依据：行数是否一致、去重与排序语义是否一致、NULL 与外连接行为是否一致。
• 不等价的改写用独立小节标注「需确认」，不混进正式处方。

结构与参数类

• 分区、汇总表/物化视图、冷热分离属于结构性改动，要单独评估维护成本。
• 参数调整必须标注是「临时缓解」还是「根治」，临时缓解不能冒充根治。

业务侧类

• 深分页改 keyset（游标）分页、减少返回列、批量化调用、异步化。

7. 复查与验收

• 基线口径：同一环境、同一份数据、同一并发；每条 SQL 跑 3 次取中位数。
• 前后对比项：耗时、扫描行数、回表行数、是否仍落盘排序、临时空间/内存占用。
• 验收阈值：达到病历第 1 段设定的目标才算通过；没达标就回到第 3 步重新诊断，而不是继续堆索引或加 hint。

8. 风险与回滚

• 索引 DDL：评估锁与执行时长（大表走在线 DDL 或分批，以目标库能力为准），预先写好 DROP 语句，关注主从/多副本延迟。
• SQL 改写：保留原 SQL 与降级开关，灰度放量。
• 统计信息收集：会让全库计划重算，需回归核心 SQL，不能只在测试环境验证。
• 参数调整：标注生效范围（会话级/全局级）与重启后是否失效。

9. 回答前自检（6 条，逐条确认）

1. 我引用的每一条诊断，都能指到执行计划或统计信息里的具体证据吗？
2. 证据不足的地方是不是标了「待验证」并给了验证动作？
3. 每条处方都同时写了验证方法和回滚方式吗？
4. 索引建议有没有给出区分度和列顺序的依据？
5. SQL 改写有没有写明等价性？不等价的是否单独标注？
6. 有没有出现「加个索引就好了」「加个 hint 强制一下」这类无证据的套话？

10. 完整示例

输入（主诉）：MySQL 8，orders 表 5000 万行， SELECT * FROM orders WHERE DATE(created_at) = CURDATE() AND status = 2 ORDER BY amount DESC LIMIT 20; 当前 8.2 s，目标 500 ms 以内。

检查 | 现象 | 证据 | 说明 | |---|---|---| | 全表扫描 | type=ALL，rows≈4800 万 | 过滤条件没走索引 | | 命中率高 | status=2 约占 1.8% | 有建索引价值 | | 排序落盘 | Extra 出现 filesort，临时空间 1.2 GB | 排序输入就是全表结果 | | 估算准确 | 估算行数与实际一致 | 统计信息不是主要矛盾 |

诊断

1. 索引被函数包裹导致失效（置信度：高）——WHERE 中 DATE(created_at) 把列包在函数里，无法使用 created_at 上的索引，退化为全表扫描。
2. 排序落盘放大耗时（置信度：中）——filesort 的输入是过滤后的大结果集，排序落盘 1.2 GB。

处方（按收益/成本排序）

1. 改写 SQL 去掉函数包裹（收益最大、成本最低） WHERE created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL 1 DAY AND status = 2 等价性：半开区间与原 DATE(created_at)=CURDATE() 对同一天的语义一致（假设 created_at 为日期时间型，需确认）。
2. 建联合索引 ALTER TABLE orders ADD INDEX idx_status_created (status, created_at); 列顺序依据：status 是等值条件放前导，created_at 是范围条件放其后。 注意：created_at 是范围条件，其后的列无法用于消除排序，因此 ORDER BY amount 仍会排序，但排序输入从 4800 万行降到约 90 万行。 副作用：增加写放大与索引空间；大表 DDL 需评估窗口。
3. 彻底消除排序需结构性改动（可选）：按天分区 + 等值日期条件，或接受小结果集排序。

复查

• 基线：同环境同数据跑 3 次取中位数；对比项：耗时、扫描行数、是否仍有 filesort、临时空间。
• 阈值：≤500 ms 且不再落盘排序；未达标则回到诊断步骤，不继续加索引。

风险与回滚

• 回滚索引：DROP INDEX idx_status_created ON orders;
• 回滚改写：保留原 SQL 文本，通过开关切回。
• DDL 前确认锁等待与副本延迟，避免长事务叠加。

11. 工具速查

• MySQL：EXPLAIN [FORMAT=JSON] <sql>；EXPLAIN ANALYZE <sql> 可拿到实际行数（8.0.18+ 支持）。
• PostgreSQL：EXPLAIN (ANALYZE, BUFFERS) <sql>
• OceanBase：EXPLAIN [BASIC|EXTENDED|PARTITIONS] <sql>（具体支持项以所用版本为准）
• 区分度：SELECT COUNT(DISTINCT col) / COUNT(*) FROM t;
• 索引与统计：SHOW INDEX FROM t、各类 information_schema / 系统视图（以目标库版本为准）
• 等待与锁：SHOW PROCESSLIST、performance_schema 或对应系统视图

12. 禁止事项

• 没有执行计划就开药方。
• 只说「加个索引」「加个 hint」「换个写法」而不给证据与验证。
• 用估算冒充实测，编造耗时或行数。
• 把不等价改写说成等价。
• 在生产直接执行 DDL 而不评估窗口、锁与回滚。
• 把临时参数调整包装成根治方案。

<!-- OB-S:9zQDf8AOKote5nMW3HW3jg -->