2.2 查询语句重写与优化技巧

2.2 查询语句重写与优化技巧

本节摘要：索引对了，查询写法也可能拖慢。本节讲清楚常见查询反模式（SELECT */无分页大结果/深分页/子查询/JOIN 过多）和重写技巧，让你写出高效 SQL。

SELECT * 的危害

**SELECT *** 返回所有列，危害：

• 传输多：返回不需要的列，浪费网络带宽。
• 覆盖索引失效：覆盖索引只含部分列，SELECT * 要回表。
• 排序/聚合慢：多余列参与排序/聚合，增加内存和 CPU。
• schema 耦合：表加列，SELECT * 返回新列，可能破坏应用。

改：只 SELECT 需要的列——SELECT id, name, status。配合覆盖索引，免回表。

大结果集与分页

无分页大结果集：SELECT * FROM orders 返回百万行，OOM 或超时。

改：加分页——LIMIT/OFFSET 或游标分页。

LIMIT OFFSET 深分页问题：

• LIMIT 100000, 20——先扫 100020 行再丢 100000，慢。
• 改：游标分页（keyset pagination）——WHERE id > 上次最大id LIMIT 20，用索引直接定位，O(20) 而非 O(100020)。

游标分页适合有序主键，深分页高效。但不支持跳页（只能上一页/下一页）。需要跳页用 LIMIT OFFSET 但限制深度。

JOIN 优化

JOIN 过多：多表 JOIN 笛卡尔积爆炸，慢。

优化：

• 减少 JOIN 表数：冗余/反范式减少关联，或应用层组装。
• JOIN 顺序：小表驱动大表（小表先过滤），优化器通常自动选，但可 hint。
• JOIN 列索引：JOIN 列要索引，否则全表扫描。
• JOIN 类型：INNER JOIN 比 OUTER JOIN 快（OUTER 要保留所有行）。
• 子查询转 JOIN：某些子查询转 JOIN 更快（优化器可能自动转，但不总是）。

子查询优化

子查询可能慢：

• 相关子查询：子查询依赖外查询，每行执行一次——N 次执行。
• 改：转 JOIN——IN 子查询转 JOIN EXISTS 或 JOIN。

例：

• 慢：SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status=1)（相关子查询可能每行执行）。
• 快：SELECT orders.* FROM orders JOIN users ON orders.user_id=users.id WHERE users.status=1（JOIN 一次）。

EXISTS vs IN：EXISTS 通常比 IN 快（找到即停），尤其子查询大时。

聚合与分组优化

GROUP BY 优化：

• GROUP BY 列要索引——否则临时表排序。
• 减少分组列——分组列多则组合多。
• WHERE 先过滤——减少分组前数据量。

COUNT 优化：

• COUNT(*) 比 COUNT(列) 快（不解析列，除非 COUNT(列) 排除 NULL）。
• 大表 COUNT 慢——考虑缓存计数或估算（如 SHOW TABLE STATUS 的行数估算）。

DISTINCT 优化：

• DISTINCT 要排序去重，慢。
• 改：GROUP BY 或应用层去重。

UNION 优化

UNION vs UNION ALL：

• UNION 去重（排序），UNION ALL 不去重。
• 不需要去重用 UNION ALL——快很多。

UNION 优化：

• 每个 SELECT 用索引——UNION 是多个 SELECT 合并。
• 减少 SELECT 数——合并条件而非 UNION。

排序优化

ORDER BY 优化：

• 排序列索引——索引已排序，免 filesort。
• 排序方向一致——多列排序方向要一致（都 ASC 或都 DESC），否则不能全用索引。
• 减少排序数据——WHERE 先过滤再排序。

filesort：排序不在索引则 filesort（内存/磁盘排序），慢。监控 Extra 列的 "Using filesort"。

其他技巧

1. 避免函数操作列（索引失效，见 2.1）。

2. 用 EXPLAIN 验证：每条优化后 EXPLAIN 确认执行计划。

3. 批量操作：批量 INSERT（INSERT ... VALUES (...),(...),...）比循环单条快 10-100 倍。

4. 避免大事务：长事务锁竞争，拆小事务。

5. 用合适类型：INT 比 VARCHAR 快，TIMESTAMP 比 DATETIME 省空间。

⚠️ 常见误读：以为"SQL 写法不影响性能，优化器会优化"。优化器有限——如深分页 OFFSET、SELECT * 回表、相关子查询，优化器不一定能优化。要写 SQL 时就考虑性能。

💡 关键直觉：查询重写技巧——避免 SELECT （只取必要列配覆盖索引）、游标分页（避免深 LIMIT OFFSET）、JOIN 优化（减表数/小驱大/列索引/子查询转 JOIN/EXISTS）、聚合（GROUP BY 索引/先 WHERE/COUNT()/避免 DISTINCT）、UNION ALL 不去重、排序（索引列/方向一致/避免 filesort）、批量操作/避免大事务/合适类型。优化器有限，写 SQL 就要考虑性能。

重写技巧清单

• **SELECT ***：返回所有列，传输多/覆盖索引失效回表/排序聚合慢/schema 耦合，改只取必要列。
• 大结果集：无分页返回百万行 OOM，加分页——游标分页（WHERE id>上次 LIMIT，O(20)）适合有序主键深分页，LIMIT OFFSET 限制深度。
• JOIN 优化：减表数（冗余/反范式/应用层组装）、小驱大、JOIN 列索引、INNER 比 OUTER 快、子查询转 JOIN。
• 子查询：相关子查询每行执行慢，转 JOIN 一次，EXISTS 比 IN 快（找到即停）。
• 聚合：GROUP BY 列索引（免临时表排序）、减分组列、WHERE 先过滤、COUNT(*) 比 COUNT(列) 快、大表 COUNT 缓存/估算、DISTINCT 慢用 GROUP BY 或应用层去重。
• UNION：UNION ALL 不去重比 UNION 快，每个 SELECT 用索引，减少 SELECT 数。
• 排序：排序列索引免 filesort、方向一致、WHERE 先过滤，监控 "Using filesort"。
• 其他：避免函数操作列、EXPLAIN 验证、批量 INSERT 快 10-100x、避免大事务、合适类型（INT 比 VARCHAR 快）。
• 核心：优化器有限，写 SQL 就要考虑性能，不能全靠优化器。

深分页与大数据量导出的两道经典难题

语句重写的深水区是两道经典难题。难题一，深分页：limit 一百万，十——数据库要扫过一百万零十行再丢掉前一百万行，页码越深越慢。三种解法按优雅程度排列：其一，游标分页（记录上一页末尾的排序键，下一页从该键之后取），彻底消除深扫，代价是页码跳转受限；其二，延迟关联（先用覆盖索引定位主键集合，再回表取整行），把扫描限制在窄索引内；其三，业务上直接限制最大页码——产品层面承认"没人会翻到第一万页"。难题二，大批量导出：一次性取百万行会撑爆应用内存与网络缓冲。标准解法是流式分批（按主键区间或游标每次取五千行，应用侧边取边写文件），配合只读从库执行，把导出流量与生产隔离。两道难题的共同启示：SQL 优化到深处是"用正确的访问模式换掉看似自然的写法"——自然写法对人友好，对引擎未必；调优师的职业敏感就是能看出"自然"背后的扫描代价。

子查询与连接的改写艺术

语句重写的高级篇是子查询与连接的相互改写，这是执行计划不良时的主要手术刀。场景一，相关子查询转连接："找出最近有订单的用户"写成"用户 where exists 订单"多数库能优化好，但写成"用户 join（按用户聚合的订单表）"在某些版本上更稳——相关子查询的每行触发一次内层查询，优化器能不能转成哈希执行，决定了它是毫秒还是分钟；判断方法是看执行计划里内层是否被展开。场景二，聚合子查询提前过滤：先在子查询里把大表按条件聚合成小结果，再连接主表，比"先连接后聚合"扫的数据少一个量级——把过滤与聚合尽量推到靠近数据源的位置，是所有改写的共同方向。场景三，or 改 union all：跨列的 or 条件（状态等于A 或 类型等于B）常让索引失效，拆成两个子查询各自命中索引再合并，各自都快的两条路比一条慢路强。三个场景共同的元规则：改写的本质是"替优化器做它做不到的决策"——先读懂它为什么选了坏计划（第 3 节的技能），再决定用哪种改写喂给它一条明路。

重写技术的速查决策卡

语句重写的知识收成一张决策卡，遇到慢 SQL 时按序取用。卡一，先看类型：等值查询慢查索引（第 1 节），聚合慢查预聚合与物化（第 3 章），多表慢查连接顺序与中间结果规模。卡二，三板斧：改写函数包裹列为区间比较、拆或条件为各自命中索引的子查询、深分页改游标或延迟关联——八成的不良写法在这三板斧内解决。卡三，慎用区：hint 强制索引是应急手段（优化器统计修好后要回收），视图嵌套超过两层就该展开重写，动态拼接的语句要抓实际形态再优化而非优化模板。卡四，验证纪律：每次重写跑改写前后对照（时延、执行计划、扫描行数），效果存档——重写收益的分布高度偏态（少数语句贡献绝大多数提升），存档帮你识别哪些模式最值得优先处理。这张卡的精髓是克制与纪律：重写是精细手术，按卡出刀，不凭感觉。

一个慢查询的分诊台

把重写知识组织成一个"分诊台"流程，慢查询进来先分诊再动刀。分诊一，频率型还是偶发型：高频小慢（每次两秒、每天十万次）优先治理——总时延占比决定优先级；低频大慢（每次十分钟、每天两次）看影响（是否占用资源阻塞别人）。分诊二，稳定型还是波动型：稳定的慢是结构问题（索引、写法），波动的慢是环境问题（并发、统计、缓存）——后者先抓波动时刻的现场再定位层。分诊三，新发还是陈旧：新查询慢查设计与统计；老查询变慢查变化（数据量临界、统计漂移、执行计划翻转）——"老查询"的优化入口常在第 5 章的统计与碎片，不在本章。三个分诊问题各配一条转诊路径，分诊台的价值是防止"看到慢就改写"的条件反射——据行业经验，至少三成的慢查询正确入口不在语句本身，动刀之前先分诊，是效率与口碑的双重保险。

补最后一块拼图：重写的边界意识。重写能救的是"引擎执行方式"的问题，救不了"业务需求本身过重"的问题——一天要扫全表出报表的需求，语句写得再花也是全表扫；此时正确的动作是往上游走（预聚合、缓存、改变产品形态），而不是在 SQL 上继续雕花。识别这个边界的信号：重写后扫描量已无压缩空间、时延仍不达标——到顶了，该谈需求了。知道刀往哪里不能砍，与知道往哪里砍同样重要。

重写案例的三个补充细节

主案例之外补三个容易被追问的细节。细节一，改写后的结果等价性验证：重写前后各跑一次，结果集比对（行数、排序、聚合值）——重写引入语义偏差（空值处理、去重范围、排序稳定性）是真实事故，等价性验证是改写的收尾动作，不是可选动作。细节二，改写对优化器版本的同理性：今天为绕开优化器缺陷而写的改写，下个版本可能变成多余甚至有害（手工改写挡住了新优化器的路）；所以每条"绕行式改写"要在注释里标注针对的缺陷与复查条件，版本升级时集中复审。细节三，改写的团队知识化：把验证过的改写模式收进团队的"改写模式库"（场景、写法、原理、案例四元组），新模式入库要附测试——三个月后你就有了带回归保护的团队智慧库，新人按库执行而不是按记忆摸索。三个细节的共同主题：改写是资产管理而不是一次性劳动，管起来的改写会增值，不管的改写会腐烂。

再补一条实战心得：改写的收益要在真实并发下复验——低峰单跑时延漂亮的改写，高峰并发下可能因锁模式变化而劣化（改写改变了锁的持有范围与时长）；所以重写的验证清单要有两栏：单线程时延与高峰时段的业务指标，两栏都绿才算过关。这条心得的代价是几个团队用一次故障换来的，写在这里希望它成为你免费的部分。