SQL 优化实战：从慢查询定位到执行计划精读的完整指南

SQL 优化是后端工程师性价比最高的技能之一：改对一条索引，可能让查询从 10 秒降到 10 毫秒。本文以"定位慢 SQL → 读懂执行计划 → 对症下药"为主线，给出可复用的优化方法论。

一、第一步：把慢 SQL 找出来

1.1 MySQL 慢查询日志

# my.cnf
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

-- 查看当前慢查询
SHOW GLOBAL STATUS LIKE 'Slow_queries';
-- 或直接看日志
tail -100 /var/log/mysql/slow.log

1.2 实时抓取

-- MySQL 8.0 performance_schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- 通用：processlist 抓正在跑的慢查询
SELECT * FROM information_schema.PROCESSLIST
WHERE TIME > 5 AND COMMAND != 'Sleep';

二、第二步：读懂执行计划

对目标 SQL 执行 EXPLAIN，重点看五列：

EXPLAIN SELECT o.order_id, u.nickname
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 1 AND o.created_at > '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

| 列 | 本例表现 | 解读 | |----|----------|------| | type | orders: ALL, users: eq_ref | orders 全表扫描，users 走主键 | | possible_keys | idx_status, idx_created | 可用的索引 | | key | NULL | 一个都没用上！ | | rows | 1200000 | 预计扫 120 万行 | | Extra | Using filesort | 排序未用索引 |

诊断：orders 表 WHERE 与 ORDER BY 各自有单列索引，但联合索引缺失导致只能全扫 + 文件排序。

三、第三步：对症下药

3.1 补联合索引

-- 等值条件 status 在前，范围/排序 created_at 在后
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- 验证
EXPLAIN SELECT ...;  -- type: ref, rows: 2万, Extra: 无 filesort

3.2 改写 SQL 的常见手法

避免函数与隐式转换：

-- 反例
WHERE DATE(created_at) = '2026-10-20'
-- 正解
WHERE created_at >= '2026-10-20 00:00:00'
  AND created_at <  '2026-10-21 00:00:00'

NOT IN 改 LEFT JOIN：

-- 反例（子查询物化、扫描大）
SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM blacklist);

-- 正解
SELECT p.* FROM products p
LEFT JOIN blacklist b ON b.product_id = p.id
WHERE b.id IS NULL;

OR 改 UNION / IN：

-- 反例：OR 导致索引失效
WHERE category = 'a' OR category = 'b'
-- 正解（或直接用 IN）
WHERE category IN ('a', 'b')

LIMIT 深翻页：

-- 反例：OFFSET 大
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 正解：键集分页
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

四、JOIN 优化的执行顺序认知

MySQL 的 JOIN 执行是驱动表 + 被驱动表的模式（小表驱动大表）：

-- 小表 categories（几千行）驱动大表 articles（百万行）
SELECT a.* FROM articles a
INNER JOIN categories c ON c.id = a.category_id
WHERE c.slug = 'backend';

• 驱动表（外层）：尽量是小表/高过滤率表；
• 被驱动表（内层）：join 字段必须有索引（否则每行内层全扫）；
• 通过 EXPLAIN 观察谁先执行，必要时用 STRAIGHT_JOIN 或调整 JOIN 顺序。

五、聚合与排序的优化

5.1 GROUP BY 走索引

-- 反例：Using temporary + Using filesort
SELECT category, COUNT(*) FROM articles GROUP BY category;

-- 正解：category 建索引后，GROUP BY 直接走索引有序聚合
ALTER TABLE articles ADD INDEX idx_category (category);

5.2 大表 COUNT 优化

-- 条件 COUNT 尽量走覆盖索引
SELECT COUNT(*) FROM orders WHERE status = 1;
-- (status) 单列索引即可覆盖，无需回表

5.3 避免 SELECT *

只取需要的列，不仅减少网络传输，还能让覆盖索引生效（查询直接命中索引树，不回表）。

六、执行计划进阶：EXPLAIN ANALYZE

MySQL 8.0.18+ 的 EXPLAIN ANALYZE 直接执行并给出真实耗时：

EXPLAIN ANALYZE
SELECT ... FROM orders WHERE ...;

-- 输出包含每步的实际行数、耗时，比估算更真实

配合 EXPLAIN FORMAT=JSON，能看到成本估算与索引命中细节。

七、优化闭环与禁忌

慢查询日志 → 定位 TOP SQL → EXPLAIN → 诊断 → 加索引/改写 → 复测耗时 → 回归

禁忌清单：

1. 生产环境直接加索引要看锁表影响（MySQL 8.0 用 INSTANT 算法或在线 DDL 工具）；
2. 不要为每个 WHERE 条件都建单列索引（索引过多拖垮写入）；
3. 不要迷信"索引越多越快"，覆盖索引要精确设计；
4. 先优化影响面大的（高频 × 慢），别在一年跑两次的报表 SQL 上恋战。

结语

SQL 优化是有章可循的工程方法：日志找慢、计划诊断、索引对症、复测验证。绝大多数慢查询的根因不外乎三类——缺索引、错误写法、深翻页，对应的解药也相对固定。把本文的检查清单固化为每次发布前的 review 步骤，SQL 性能问题就能在进生产之前被拦截，而不是在线上告警之后才手忙脚乱。