慢查询：数据库性能的头号杀手
一条慢 SQL 足以拖垮整个数据库。在 MySQL 优化中，慢查询分析是最直接、回报最高的工作。本文将建立一套从发现到优化、从优化到验证的完整方法论。
慢查询日志配置
-- 查看当前配置
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 1; -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未使用索引的查询
SET GLOBAL log_slow_admin_statements = ON; -- 记录管理语句
-- 持久化（my.cnf）
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/slow-query.log
-- long_query_time = 1
-- log_queries_not_using_indexes = 1
pt-query-digest 分析工具
Percona Toolkit 中的 pt-query-digest 是慢查询分析的标配工具：
# 安装 Percona Toolkit
yum install -y percona-toolkit
# 分析慢查询日志
pt-query-digest /var/log/mysql/slow-query.log > slow_report.txt
# 实时监控（从 processlist）
pt-query-digest --processlist h=localhost,u=root --interval=1
# 分析指定时间范围
pt-query-digest \
--since '2025-06-18 00:00:00' \
--until '2025-06-18 23:59:59' \
/var/log/mysql/slow-query.log
关键输出指标：
| 指标 | 含义 | 关注点 |
|---|---|---|
| Response time | 总响应时间 | 占比越大越需要优化 |
| Calls | 执行次数 | 高频查询即使单次快也值得优化 |
| Rows examine | 扫描行数 | 扫描越多越糟糕 |
| Rows sent | 返回行数 | examine/send 比值是关键 |
EXPLAIN 完全解读
EXPLAIN FORMAT=JSON
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
AND o.created_at > '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 20;
JSON 格式输出字段解读：
{
"query_block": {
"select_id": 1,
"cost_info": { "query_cost": "1258.50" },
"ordering_operation": {
"using_filesort": false, // ✅ 使用了索引排序
"table": {
"table_name": "orders",
"access_type": "range", // range = 范围扫描，优于 ALL 但不如 ref
"possible_keys": ["idx_status_created"],
"key": "idx_status_created", // 实际使用的索引
"key_length": "9",
"rows_examined_per_scan": 500,
"filtered": 100,
"using_index": false // false = 需要回表
}
}
}
}
关键字段优先级
性能从优到劣：
system > const > eq_ref > ref > range > index > ALL
1 1 很好 好 一般 差 灾难
常见慢查询模式与优化
模式1：隐式类型转换
-- ❌ 慢：phone 是 VARCHAR，传入数字导致全表扫描
SELECT * FROM users WHERE phone = 13912345678;
-- ✅ 修复：使用正确类型
SELECT * FROM users WHERE phone = '13912345678';
模式2：函数包裹索引列
-- ❌ 慢：DATE() 函数导致无法使用索引
SELECT * FROM orders WHERE DATE(created_at) = '2025-06-18';
-- ✅ 修复：用范围查询替代
SELECT * FROM orders
WHERE created_at >= '2025-06-18 00:00:00'
AND created_at < '2025-06-19 00:00:00';
模式3：前置模糊匹配
-- ❌ 慢：LIKE '%keyword' 无法使用索引
SELECT * FROM articles WHERE title LIKE '%MySQL优化%';
-- ✅ 方案A：全文索引
ALTER TABLE articles ADD FULLTEXT(title, content);
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('MySQL优化');
-- ✅ 方案B：Elasticsearch 等外部搜索引擎
模式4：大偏移量分页
-- ❌ 慢：OFFSET 100000 需要扫描前 100020 行
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;
-- ✅ 修复：基于游标的分页（记住上一页最后一条的 id）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;
-- 如果必须用 OFFSET，加一个子查询减少回表
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 100000) tmp
ON o.id = tmp.id;
模式5：OR 条件
-- ❌ 慢：OR 可能导致全表扫描
SELECT * FROM users WHERE email = 'test@test.com' OR phone = '13912345678';
-- ✅ 修复：UNION 让每个子查询分别走索引
SELECT * FROM users WHERE email = 'test@test.com'
UNION
SELECT * FROM users WHERE phone = '13912345678';
索引优化原则
-- 联合索引的"最左前缀"原则
-- 索引: (status, created_at, user_id)
-- ✅ 可以使用索引
WHERE status = 'pending'
WHERE status = 'pending' AND created_at > '2025-01-01'
WHERE status = 'pending' AND created_at > '2025-01-01' AND user_id = 100
-- ❌ 不能使用索引（跳过了最左列 status）
WHERE created_at > '2025-01-01'
WHERE user_id = 100
-- ⚠️ 部分使用（范围查询后的列无法使用）
WHERE status = 'pending' AND created_at > '2025-01-01' AND user_id = 100
-- ↑ 范围查询 ↑ 此列索引失效
覆盖索引
-- ❌ 需要回表（Using index condition）
SELECT * FROM orders WHERE status = 'pending';
-- ✅ 覆盖索引（Using index，Extra 中出现）
-- 索引: (status, created_at, user_id)
SELECT status, created_at, user_id FROM orders WHERE status = 'pending';
验证优化效果
-- 开启 profiling
SET profiling = 1;
-- 执行优化前后的查询
SELECT ... ;
-- 查看执行耗时明细
SHOW PROFILES;
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;
-- 或使用 EXPLAIN ANALYZE (MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';
黄金法则：优化之前先量化。没有基准数据的优化是盲目的，无法证明优化是否有效。