如何进行 SQL 调优？

#中间件#面试八股

如何进行 SQL 调优？

结论：SQL 调优不是「加索引」的同义词，而是一条自上而下的流水线：先定位最贵的语句，再看执行计划，然后按「改写 SQL → 调整索引 → 改表结构 → 上架构手段」逐级加码。每加一级都要用指标验证，否则容易把一个慢查询换成三个慢查询。

一、五个优化层次

层次	手段	收益	风险/成本
SQL 写法	去掉 `SELECT *`、拆分大 SQL、批量代替循环、`EXISTS`/`JOIN` 改写	高	低，随时可回滚
索引	联合索引、覆盖索引、前缀索引、删冗余索引	高	中，影响写入
表结构	冷热分离、归档、垂直拆分、字段类型收敛	中	高，需迁移
架构	缓存、读写分离、分库分表、搜索引擎	高	很高，引入新一致性问
参数与硬件	buffer pool、连接数、IO 能力	中	低，但天花板明显

调优的顺序也应该是这个顺序：能用 SQL 改写解决的，不要动索引；能用索引解决的，不要拆表。

二、调优流程

① 定位：慢日志 / digest 表 → 按总耗时排序，取 TOP 10
        │
② 复现：拿到具体参数与数据分布，在从库或影子库复现
        │
③ 分析：EXPLAIN ANALYZE 看 type/key/rows/Extra
        │        + rows_examined/rows_sent + Handler_read%
        │
④ 改写：先改 SQL，再改索引，一次只动一个变量
        │
⑤ 验证：P99、扫描行数、IO、CPU 前后对比，观察 24 小时
        │
⑥ 固化：把 SQL 与索引写进规范，加监控防回归

EXPLAIN 的四列是必看项：type 至少要到 range，key 必须是预期索引，rows 要与返回行数同量级，Extra 里不能出现 Using filesort、Using temporary（除非业务必须）。

三、常见改写手法

1. 消除 SELECT *：减少回表与网络传输。

-- 之前
SELECT * FROM article WHERE author = 'ruilin' ORDER BY ctime DESC LIMIT 20;
-- 之后：只取列表页需要的列，并让索引覆盖
SELECT id, title, ctime FROM article WHERE author = 'ruilin' ORDER BY ctime DESC LIMIT 20;

2. 用 JOIN 代替相关子查询：相关子查询会对每行执行一次。

-- 之前：外层每行都跑一次子查询
SELECT id, (SELECT COUNT(*) FROM order_item i WHERE i.order_id = o.id) AS cnt
FROM `order` o WHERE o.user_id = 42;

-- 之后：一次聚合 + 一次 JOIN
SELECT o.id, COALESCE(x.cnt, 0) AS cnt
FROM `order` o
LEFT JOIN (
  SELECT order_id, COUNT(*) AS cnt FROM order_item GROUP BY order_id
) x ON x.order_id = o.id
WHERE o.user_id = 42;

3. 大 IN 列表拆批：单条 SQL 的参数与解析开销会随列表线性增长。

-- 每批 500 个 id
SELECT id, sku_id FROM order_item WHERE id IN (?, ?, ...);

4. 用 UNION ALL 拆 OR：OR 连接不同列时常常只能全表扫。

-- 之前
SELECT id FROM t WHERE user_id = 42 OR sku_id = 88;
-- 之后：两条都能走索引
SELECT id FROM t WHERE user_id = 42
UNION ALL
SELECT id FROM t WHERE sku_id = 88 AND user_id <> 42;

5. 批量插入代替逐条插入：

INSERT INTO t_log (user_id, action) VALUES
  (1, 'login'), (2, 'login'), (3, 'logout');

6. 计数与分页的改写：见 count(*) 与深度分页两节，核心是「别让服务器做无用的扫描」。

四、索引侧的检查清单

-- 冗余索引：被其他索引的最左前缀完全覆盖
SELECT * FROM sys.schema_redundant_indexes;

-- 从未使用的索引（需开启 userstat 或依赖 performance_schema）
SELECT * FROM sys.schema_unused_indexes;

-- 索引基数是否合理
SELECT index_name, column_name, cardinality
FROM information_schema.statistics
WHERE table_schema = 'demo' AND table_name = 't_order';

冗余索引要果断删：每个多余索引都在为每次写入增加一次 B+Tree 维护。

五、验证指标

指标	采集方式	期望变化
扫描行数	`Handler_read%`、`rows_examined`	显著下降
执行时间 P99	慢日志、APM	下降且稳定
逻辑读	`Innodb_buffer_pool_read_requests`	下降
临时表/排序	`Created_tmp_tables`、`Sort_merge_passes`	下降
写入影响	QPS、`Innodb_row_lock_time`	不恶化

常见追问

调优先改 SQL 还是先加索引？ 先改 SQL。改写法零成本、可回滚；加索引有写入代价，且可能掩盖写法问题。
EXPLAIN 好但线上慢，为什么？ 数据分布、参数不同，或慢在锁、网络、连接池；要在真实数据上 EXPLAIN ANALYZE。
要不要定期 OPTIMIZE TABLE？ 高碎片表才需要，且是重建操作；更推荐控制变长更新与批量删除的节奏。
分页、统计、导出三类查询怎么优化？ 分别对应游标分页、增量计数、异步导出到从库或数仓。
调优的终点在哪？ 单表数据量与 QPS 到达单机瓶颈时，进入分库分表与读写分离，这属于架构层而非 SQL 层。

小结

SQL 调优是「先测量、再假设、后验证」的工程动作。把五层手段按成本从低到高排列，逐层尝试并留下指标证据，比背一堆「索引失效的十种情况」更实用——因为真实事故里，最常见的原因是没人量过那条 SQL 到底扫了多少行。

笔记加载中…