很多后端新手遇到接口变慢的第一反应是”加机器”。但在大多数情况下，拖垮你系统性能的往往不是资源不足，而是一条连索引都没走对的 SQL 语句。

今天我们深入 MySQL 的性能调优，拆解那些在生产环境中真正有效的优化策略。

一、开启慢查询日志

调优的第一步永远是量化问题。开启 MySQL 的慢查询日志，明确哪些 SQL 是瓶颈：

1
2
3
4
5
6
7
8
-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 超过 2 秒即记录
SET GLOBAL log_queries_not_using_indexes = 'ON';

日志文件位置：SHOW VARIABLES LIKE 'slow_query_log_file';

使用 pt-query-digest（Percona Toolkit）分析慢查询日志，自动汇总最常见的慢 SQL 及其影响：

1
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

二、EXPLAIN：读懂执行计划

找到慢查询后，用 EXPLAIN 分析 SQL 的执行计划：

1
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'pending';

关键字段解读：

字段 含义 关注值
type 访问类型 最优: const > eq_ref > ref > range > index > ALL（全表）
key 实际使用的索引 NULL = 没走索引
rows 预估扫描行数 越小越好
Extra 额外信息 Using filesort / Using temporary = 危险信号
key_len 索引使用长度 越大说明联合索引利用越充分

如果你看到 type: ALL、rows: 1000000、Extra: Using filesort，说明这条 SQL 是全表扫描 + 文件排序，必须立刻优化。

三、B+ 树索引原理

MySQL InnoDB 的默认索引结构是 B+ 树。理解它的工作原理，才能写出正确的索引策略：

  • B+ 树的叶子节点形成有序链表，非常适合范围查询
  • 非叶子节点只存 key，不存数据，单次 IO 能加载更多索引条目
  • 最左前缀原则：联合索引 (A, B, C) 支持 A、A,B、A,B,C 的查询，但不支持 B、B,C 的查询
1
2
3
4
5
6
7
8
9
10
11
-- 创建联合索引
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);

-- ✅ 能用到索引的查询
SELECT * FROM orders WHERE user_id = 100;
SELECT * FROM orders WHERE user_id = 100 AND status = 'done';
SELECT * FROM orders WHERE user_id = 100 AND status = 'done' ORDER BY created_at;

-- ❌ 用不到索引的查询（跳过了 user_id）
SELECT * FROM orders WHERE status = 'done';
SELECT * FROM orders WHERE status = 'done' AND created_at > '2024-01-01';

四、索引失效的常见雷区

即使建了索引，以下情况依然会导致索引失效：

  1. 函数包裹列：WHERE DATE(created_at) = '2024-01-01' → 改为 WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
  2. 隐式类型转换：WHERE phone = 13800138000（phone 是 varchar）→ 改为 WHERE phone = '13800138000'
  3. LIKE 前置通配符：WHERE name LIKE '%张三' → 改为全文索引或 Elasticsearch
  4. OR 条件跨索引：WHERE id = 1 OR name = 'test' → 改为 UNION 或拆分查询
  5. 不等于/NOT IN：这些通常无法利用索引，考虑改写

五、覆盖索引

如果查询的所有列都在索引中，MySQL 可以直接从索引返回结果而无需回表查询，速度极快：

1
2
3
4
5
6
7
8
-- 覆盖索引：查询列全在索引中
CREATE INDEX idx_user_status ON orders(user_id, status);
SELECT user_id, status FROM orders WHERE user_id = 100;
-- Extra: Using index  ← 说明走了覆盖索引，无需回表

-- 非覆盖索引：需要回表
SELECT * FROM orders WHERE user_id = 100;
-- Extra: NULL  ← 走了索引但需要回表获取完整行数据

总结

MySQL 调优的核心在于”找对慢 SQL、读懂执行计划、写好索引”。慢查询日志让你知道哪里慢了，EXPLAIN 告诉你为什么慢，B+ 树索引理解帮你写出最优的 SQL。三者形成闭环，比盲目加机器有效得多。