SQL 优化不是背”加索引、别用 SELECT *”这类口诀，而是一条”发现 → 分析 → 索引 → 改写 → 架构兜底”的方法论链路。本文按这条链路展开，重点拆解覆盖索引、联合索引顺序、深分页、OR 改写等高频手法，每个都配反例与正例。

它解决什么问题

数据库通常是系统里最难水平扩展的组件，慢 SQL 又是数据库层面最常见的瓶颈来源。一条扫描百万行的查询拖垮的不只是自己：它长时间占住连接、把 Buffer Pool 里的热数据顶出去、加剧锁等待，最终拖累整库。SQL 优化要解决三件事——让单条 SQL 少扫数据，让数据库整体少扛压力，让每次优化可验证、可复测，而不是”感觉快了”。

核心原理：一条方法论链路

第一步，发现。 开启慢查询日志（slow_query_log、long_query_time 阈值，排查期可短期打开 log_queries_not_using_indexes 抓未走索引的查询），配合 APM 和数据库监控定位高频慢 SQL。日志里重点看 Query_time、Lock_time、Rows_examined、Rows_sent：扫描行数远超返回行数，基本就是索引缺失或选择性差。注意别只盯单次最慢的那条——线上最危险的往往是”单次 50ms、每秒执行几千次”的高频 SQL，总耗时占比远超偶发的大查询。

第二步，分析。 用 EXPLAIN 看执行计划，重点读几个列：type（从 ALL 全表扫描到 const 常量查找，由差到好）、key（实际用了哪个索引）、rows（估算扫描行数）、Extra（是否出现 Using filesort、Using temporary）。MySQL 8.0.18 之后可以用 EXPLAIN ANALYZE 拿到真实执行耗时和行数，和估算值对照。

第三步，索引。 多数慢 SQL 的正确解法是补对索引，或修正写法让已有索引可用，而不是急着改 SQL 文本。

第四步，改写。 索引解决不了的，调整查询形态：裁剪字段、重写分页、拆分 OR。

第五步，架构兜底。 单库优化到头的，热点读交给缓存、并发读交给读写分离、复杂检索交给搜索引擎。架构手段是兜底不是起点——前面四步没做就上缓存，缓存里装的还是慢查询的结果。

深入机制：索引为什么会被用废

InnoDB 二级索引是一棵 B+ 树，叶子按索引列有序存储，并带着主键值用于回表。两个结构特性决定了绝大多数使用规则：

  • 最左前缀：联合索引 (a, b, c) 按先 a、再 b、后 c 排序，查询条件带上 a 才能利用这份有序性。跳过 a 直接查 b，等于在一本没按拼音排序的字典里逐页翻字。
  • 回表成本：二级索引叶子只存索引列和主键，要拿其他列必须按主键再查一次聚簇索引。命中行数占比高时，优化器会判断随机回表比全表顺序扫描还贵，干脆放弃索引——这就是”索引明明在，EXPLAIN 却不走”的常见原因。另外，优化器的决策依赖表统计信息，数据剧烈变化后统计值陈旧也会选错计划，可用 ANALYZE TABLE 主动刷新。

工程实践：五个高频手法

覆盖索引：让查询不离开索引

反例：

SELECT order_id, status, amount FROM orders WHERE user_id = 1001;
-- 只有 user_id 单列索引：命中几千行后逐行回表取 status 和 amount

正例：建联合索引 (user_id, status, amount)，查询所需列全部在索引里，Extra 出现 Using index，零回表。覆盖索引是”索引即数据”的思路；代价是索引变宽、写入维护成本上升，只给高频且字段固定的查询建。

联合索引顺序：等值在前，范围殿后

反例：查询 WHERE status = 1 AND create_time > '2024-01-01' 却建了 (create_time, status)——范围列在前，后面的 status 用不上有序性。

正例：建 (status, create_time)，等值列先行锁定窄区间，范围列殿后，还能顺便利用索引顺序满足 ORDER BY create_time。一般原则：等值列在前、范围列在后，条件形式相同时优先区分度高的列。

避免 SELECT *

反例：

SELECT * FROM orders WHERE user_id = 1001;

正例：只写需要的字段。SELECT * 的代价是实打实的：读出不需要的大字段，浪费 I/O、网络和应用内存；只要有一列不在索引里就无法命中覆盖索引，被迫回表；表加列之后，老查询无感知地扛上额外开销。这不是代码洁癖，是性能与稳定性的边界。

深分页：别用 offset 硬扛

反例：

SELECT id, title FROM article ORDER BY id LIMIT 1000000, 20;
-- 扫描并丢弃前 100 万行，offset 越深越接近全表扫描

正例一（游标式范围查询，最推荐）：

SELECT id, title FROM article WHERE id > 上一页末条的id ORDER BY id LIMIT 20;

正例二（延迟关联，必须支持跳页时）：

SELECT a.id, a.title FROM article a
JOIN (SELECT id FROM article ORDER BY id LIMIT 1000000, 20) t ON a.id = t.id;
-- 子查询只扫主键索引、不回表，最后只对 20 行做回表关联

偏移量到百万级时，更该反问产品是否真的需要这么深的翻页，而不是只在 SQL 上硬扛。

OR 改写为 IN / UNION

反例：

SELECT * FROM user WHERE name = 'a' OR phone = '13800000000';
-- name 有索引、phone 没索引：一侧无索引，整体退化为全表扫描

正例：两侧列都有索引时 OR 可走 index merge；不确定时拆成 UNION，能确定结果不重叠就用 UNION ALL 省去临时表去重：

SELECT id, name FROM user WHERE name = 'a'
UNION ALL
SELECT id, name FROM user WHERE phone = '13800000000';

同一列的多值条件用 IN 替代一串 OR，让优化器走 range 扫描；IN 列表特别长时估算可能失真，必要时分批查询。

对比与权衡

手段 解决的问题 主要代价 所在层次
覆盖索引 回表开销 索引变宽、写放大 索引层
联合索引调序 索引用不上、用不满 需摸清全部查询模式 索引层
裁剪 SELECT 字段 读放大、回表 几乎为零 改写层
游标分页 / 延迟关联 深分页扫描 不支持跳页 / SQL 变复杂 改写层
缓存 热点读 一致性维护成本 架构层
读写分离 读并发 主从延迟 架构层

面试视角

联合索引 (a,b,c)，WHERE b=? AND c=? 能用上吗？ 不能完全用上。最左前缀要求从 a 开始连续命中，缺了 a，b、c 无法利用 B+ 树的有序性定位，只能索引全扫描加过滤（或 ICP 下推）。EXPLAIN 里的 key_len 会暴露实际用到了几列。

为什么 SELECT * 会拖累性能？ 三层原因：读放大——大字段白读白传；覆盖索引失效——只要一个列不在索引里就必须回表；演进风险——表加列后老查询无感变慢。只取所需列，顺带让覆盖索引成为可能。

深分页为什么慢，怎么优化？ LIMIT N, 20 要扫描并丢弃前 N 行，N 越大越接近全表扫。首选游标式 WHERE id > last_id；必须跳页时用延迟关联，把回表压缩到最后 20 行；更深的翻页交给搜索引擎。

有索引但 EXPLAIN 没走，常见原因有哪些？ 三类：写法破坏有序性（对索引列用函数、隐式类型转换、LIKE 前缀通配符）；违反最左前缀；优化器算完成本主动放弃（命中比例过高回表太贵、统计信息陈旧——先 ANALYZE TABLE 再复测）。

小结

  • SQL 优化走链路不走灵感：慢日志发现 → EXPLAIN 分析 → 索引修正 → 写法改写 → 缓存与读写分离兜底。
  • 索引的两个结构事实——最左前缀和回表成本——解释了绝大多数”索引失效”现象。
  • SELECT *、深分页、裸 OR 是三个最高频的写法问题，修正成本低、收益立竿见影。
  • 每次优化用相同数据量、相同条件复测执行计划，别让”改了索引但计划没变”的伪优化上线。