SQL 优化实战：从慢查询到索引设计

线上"接口突然变慢"的头号原因：慢 SQL。一条 3 秒的查询在高峰能把连接池打满，拖垮整个服务。这篇文章是 SQL 优化的完整实战：定位 → 分析 → 优化 → 验证。

一、定位慢查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 1. 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;      -- 超过 1 秒记录

-- 2. 查看最近慢查询
SHOW GLOBAL STATUS LIKE 'Slow_queries';
-- 慢查询日志里看到：SQL 文本 + 执行时间 + 扫描行数

-- 3. 生产常用：performance_schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;   -- Top 慢 SQL 排行

二、执行计划解读

1
2
3

EXPLAIN SELECT * FROM orders 
WHERE user_id = 123 AND status = 'PAID' 
ORDER BY created_at DESC LIMIT 10;
关 键 字 t k r E 段 y e o x ： p A y w t U U U e L ： s r s s s ： L 用 ： a i i i 访 到 扫 ： n n n 问 = 的 描 g g g 类 索 行 型 全 引 数 f t i （ 表 （ （ i e n c 扫 N 越 l m d o 描 U 小 e p e n （ L 越 s o x s 重 L 好 o r t 点 ） r a = 优 = t r > 化 y 覆 对 没 = 盖 r 象 用 = 索 e ） 索 排 引 f 引 序 用 （ ！ 没 了 最 > ） 走 临 优 索 时 ） r 引 表 a （ （ n 要 要 g 优 优 e 化 化 ） ） > i n d e x > A L L ）

三、索引设计原则

1. 最左前缀

1
2
3

-- 联合索引 (user_id, status, created_at)
-- 能用：user_id；user_id+status；user_id+status+created_at
-- 不能用：status（跳过了最左列）！status+created_at 也不行

2. 覆盖索引

1
2
3
4

-- 查询的字段都在索引里 → 不用回表
CREATE INDEX idx_user_status ON orders(user_id, status, amount);
SELECT user_id, status, amount FROM orders WHERE user_id = 123;
-- Extra: Using index（全从索引拿，不回表）→ 快很多

3. 索引下推（ICP）

1
2
3
4

-- MySQL 5.6+：联合索引里，对二级字段的过滤在索引层做
CREATE INDEX idx_user_status ON orders(user_id, status);
SELECT * FROM orders WHERE user_id = 123 AND status = 'PAID';
-- status 的过滤在索引层完成（减少回表次数）

四、常见反模式与修复

反模式 1：函数导致索引失效

1
2
3
4
5

-- ❌ 对索引列用函数 → 索引失效
WHERE DATE(created_at) = '2026-09-07'
-- ✅ 改为范围查询（索引可用）
WHERE created_at >= '2026-09-07 00:00:00' 
  AND created_at < '2026-09-08 00:00:00'

反模式 2：隐式类型转换

1
2
3
4

-- ❌ user_id 是 varchar 索引列，却传数字
WHERE user_id = 12345678901   -- 数字 → 隐式转 → 索引失效
-- ✅ 传字符串
WHERE user_id = '12345678901'

反模式 3：前缀模糊

1
2
3
4
5

-- ❌ % 开头的模糊 → 索引失效
WHERE name LIKE '%李%'
-- ✅ 后缀模糊可用索引
WHERE name LIKE '李%'
-- 真需要全文模糊 → 用 ES / 全文索引

反模式 4：OR 连接

1
2
3

-- ❌ OR 两边字段不同索引 → 可能全表扫
WHERE user_id = 1 OR status = 'PAID'
-- ✅ 拆 UNION 或确认两边都有索引

反模式 5：SELECT *

1
2

-- ❌ SELECT * → 回表 + 传大字段
-- ✅ 只查需要的字段（可能命中覆盖索引）

五、分页优化（深分页）

1
2
3
4

-- ❌ 深分页：LIMIT 100000, 20 → 扫 10 万行丢弃
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- ✅ 游标分页（用 id 条件代替 offset）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

六、一个完整案例

慢 S W O 优 结 E H R 化 果 S L E D ： 1 2 3 ： Q E R E 扫 . . . 3 L C E R 描 . ： T 加 L 只 2 查 c B 1 索 I 查 询 i Y 2 引 K 必 秒 t 0 ： E 要 3 F y u i 字 → . R = p 万 d 前 段 2 O ' d 行 x 缀 （ 8 M 杭 a （ _ 模 减 秒 州 t 全 c 糊 少 毫 r ' e 表 i 问 回 秒 e _ ） t 题 表 （ s A t ， y ： ） - u N i U _ e 9 m D m s u x 9 e e i p p . e n d e 7 x D g a c % p E t t ） e S f e _ ， c C i p 扫 t l ( o 描 _ L e c s 行 p I s i i 数 o M o t t s I r y i 1 i T t , o 2 t n 0 i 2 u o 0 p 精 万 n ; d 确 a 匹 → L t 配 I e 场 2 K _ 景 E t 走 万 i ' m E % e S 后 ) （ 端 复 % 杂 ' 检 索 ） 等 值 + 排 序

七、踩坑记录

  1. 加索引不验证：加了索引但查询没走（函数/类型转换）→ 用 EXPLAIN 确认 key 用上了
  2. 冗余索引：idx_a、idx_ab、idx_abc 三个索引 → 写放大 + 占用 → 只留 (a,b,c) 和必要单列
  3. 只优化单条 SQL 不看整体：一个查询快了，其他查询被挤掉缓存 → 压测验证整体吞吐
  4. 忽略写放大：核心表加 5 个索引 → 写入慢 3 倍 → 索引要覆盖"读写平衡"

总结

SQL 优化的核心认知：

  1. 定位靠慢查询日志（Top SQL 排行）
  2. 解读靠 EXPLAIN（type/key/rows/Extra 四个关键）
  3. 索引三原则：最左前缀、覆盖索引、索引下推
  4. 反模式五类：函数、隐式转换、前缀模糊、OR、SELECT *
  5. 验证闭环：EXPLAIN + 压测，别只"觉得快了"

SQL 优化 80% 是索引问题，20% 是写法问题。 会看 EXPLAIN、懂索引原理，慢 SQL 基本都能压到毫秒级——这是后端的基本功，也是排查线上问题的第一手武器。