MySQL 慢查询优化：从分析到调优的完整流程

一、慢查询的影响与定位

在数据库性能问题中，慢查询是最常见也最致命的性能杀手。一条执行超过 1 秒的 SQL，在高并发场景下可能导致连接池耗尽、CPU 飙高、甚至整个服务不可用。

典型的慢查询症状：

API 接口响应时间突然从 100ms 飙升到 5s+
数据库 CPU 使用率持续 > 80%
大量请求排队等待数据库连接
监控告警中出现大量 Lock wait timeout

二、开启慢查询日志

-- 查看当前慢查询配置
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE '%long_query_time%';

-- 开启慢查询日志（全局生效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;        -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 记录未使用索引的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 永久生效：写入 my.cnf
-- [mysqld]
-- slow_query_log = 1
-- long_query_time = 1
-- slow_query_log_file = /var/log/mysql/slow.log
-- log_queries_not_using_indexes = 1

三、EXPLAIN 执行计划解读

EXPLAIN 是 MySQL 优化的核心工具，以下是关键字段的解读顺序：

EXPLAIN SELECT o.*, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.status = 'pending' 
  AND o.create_time > '2025-01-01';

-- 输出解读：
+----+-------------+-------+--------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | o     | ref    | idx_status_ts | idx_st  | 82      | const | 1200 | Using where |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY | 4       | o.uid |    1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+-------+------+-------------+

type 字段（从最优到最差）：

type 值	含义	性能
`system`	表只有一行记录	极佳
`const`	通过主键/唯一索引匹配一行	极佳
`eq_ref`	Join 时使用主键/唯一索引	优秀
`ref`	使用非唯一索引查找	良好
`range`	索引范围扫描（BETWEEN, >, <, IN）	良好
`index`	全索引扫描	一般
`ALL`	全表扫描	需要优化

四、索引优化策略

1. 覆盖索引（Covering Index）

查询的所有字段都在索引中，无需回表查询。这是性能最高的索引策略：

-- 慢查询：需要回表
SELECT id, name, email, phone FROM users WHERE status = 1;

-- 创建覆盖索引
CREATE INDEX idx_status_cover ON users(status, id, name, email, phone);

-- 验证：Extra 列出现 "Using index" 即表示覆盖索引
EXPLAIN SELECT id, name, email, phone FROM users WHERE status = 1;

2. 联合索引与最左前缀原则

-- 联合索引
CREATE INDEX idx_a_b_c ON orders(user_id, status, create_time);

-- 以下查询能命中索引：
WHERE user_id = 1                           -- ✅ 命中 user_id
WHERE user_id = 1 AND status = 'pending'   -- ✅ 命中 user_id + status
WHERE user_id = 1 AND create_time > '2025-01-01'  -- ✅ 命中 user_id（范围后失效）

-- 以下查询不能命中索引：
WHERE status = 'pending'                    -- ❌ 跳过最左列
WHERE user_id = 1 OR status = 'pending'    -- ❌ OR 导致索引失效

3. 避免索引失效的常见场景

WHERE 条件中对索引列使用函数：WHERE YEAR(create_time) = 2025
隐式类型转换：WHERE phone = 13800138000（phone 是 VARCHAR）
使用 != / NOT IN / NOT EXISTS
LIKE '%keyword'（前置通配符）
联合索引不遵循最左前缀原则

五、实战案例：500ms → 5ms

问题场景：订单列表页面加载超时，慢日志显示以下 SQL 平均耗时 500ms：

-- 原始 SQL（500ms）
SELECT o.id, o.order_no, o.amount, o.status, o.create_time,
       u.name AS user_name, u.phone,
       p.name AS product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.status = 'pending'
  AND o.create_time > '2025-01-01'
ORDER BY o.create_time DESC
LIMIT 20;

EXPLAIN 分析：

orders 表 type=ALL，扫描 50 万行，无可用索引。

优化方案：

-- 1. 创建精准索引（联合索引遵循最左前缀）
CREATE INDEX idx_order_status_time ON orders(status, create_time);

-- 2. 优化后 SQL（5ms）—— 索引覆盖查询条件 + 排序字段
EXPLAIN SELECT ...;
-- type: range, key: idx_order_status_time, rows: 18

优化效果对比：

指标	优化前	优化后	提升
执行时间	500ms	5ms	↓ 100x
扫描行数	500,000	18	↓ 99.9%
访问类型	ALL	range	质的飞跃

六、pt-query-digest 工具使用

# 安装 Percona Toolkit
sudo apt install percona-toolkit

# 分析慢查询日志 — 输出 Top 10 报告
pt-query-digest /var/log/mysql/slow.log --limit=10

# 分析最近 1 小时的慢日志
pt-query-digest /var/log/mysql/slow.log --since=1h

# 输出到文件
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

💡 慢查询优化黄金法则

先用 EXPLAIN 确认 type 是否为 ALL 或 index；优先创建覆盖索引减少回表；联合索引列顺序 = 等值条件在前 + 范围条件在后 + 排序字段最后；避免在索引列上使用函数或计算。定期运行 pt-query-digest 形成周报机制。