当你发现应用响应变慢，后台任务堆积时，数据库慢查询往往是首要怀疑对象。如何系统性地诊断、分析并最终解决这些性能瓶颈？本文将带你走完一次完整的MySQL慢查询分析与调优实战，涵盖从日志配置、工具分析到SQL与索引优化的全流程。
适用场景 & 前置条件
适用场景：SQL性能优化、慢查询定位、索引优化、数据库调优。
前置条件：
- MySQL 5.7+ / 8.0+
- root 或 PROCESS 权限
- 理解索引、执行计划概念
环境与版本矩阵
| 组件 |
版本 |
说明 |
| MySQL |
5.7 / 8.0 |
生产推荐 8.0 |
| pt-query-digest |
3.x |
Percona Toolkit |
| mysqltuner |
1.9+ |
性能调优工具 |
快速清单
- 启用慢查询日志
- 配置慢查询阈值
- 分析慢查询日志
- 使用 EXPLAIN 分析执行计划
- 创建/优化索引
- 优化 SQL 语句
- 配置数据库参数
- 监控慢查询告警
1. 启用慢查询日志
查看当前配置
SHOW VARIABLES LIKE 'slow%';
-- slow_query_log | OFF
-- slow_query_log_file | /var/lib/mysql/slow.log
-- long_query_time | 10.000000
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
-- log_queries_not_using_indexes | OFF
动态启用（立即生效，重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 超过 2 秒记录
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- 记录未使用索引的查询
永久配置（修改 my.cnf）
# /etc/my.cnf 或 /etc/mysql/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
min_examined_row_limit = 100 # 至少扫描 100 行才记录
# 重启 MySQL
sudo systemctl restart mysqld
2. 慢查询日志分析
查看慢查询日志
# 查看最后 20 条
tail -n 20 /var/lib/mysql/slow.log
# 实时监控
tail -f /var/lib/mysql/slow.log
日志示例：
# Time: 2025-10-24T10:15:30.123456Z
# User@Host: app[app] @ [192.168.1.100]
# Query_time: 5.123456 Lock_time: 0.000123 Rows_sent: 1000 Rows_examined: 500000
SET timestamp=1729764930;
SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending';
关键指标：
Query_time：查询耗时（秒）Lock_time：锁等待时间Rows_sent：返回行数Rows_examined：扫描行数（关键，与 Rows_sent 差距越大越差）
使用 pt-query-digest 分析
安装：
# RHEL/CentOS
sudo yum install -y percona-toolkit
# Ubuntu
sudo apt install -y percona-toolkit
分析慢查询日志：
# 生成报告
pt-query-digest /var/lib/mysql/slow.log > slow-report.txt
# 只看 TOP 10
pt-query-digest /var/lib/mysql/slow.log --limit 10
# 分析指定时间范围
pt-query-digest /var/lib/mysql/slow.log \
--since '2025-10-24 00:00:00' \
--until '2025-10-24 23:59:59'
报告示例：
# Query 1: 0.50 QPS, 2.50s avg time, ID 0xABC123
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 100 1000
# Exec time 80 2500s 0.5s 10s 2.5s 5.0s 1.2s 2.0s
# Rows sent 50 50000 1 100 50 80 20 50
# Rows examine 90 500000 100 10000 500 1000 300 500
SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending'\G
关键指标：
QPS：每秒查询次数Exec time：总执行时间（pct=占比）Rows examined/Rows sent：扫描行数与返回行数比值（理想接近 1）
3. EXPLAIN 执行计划分析
基础 EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending'\G
输出示例：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ALL # 全表扫描（差）
possible_keys: NULL
key: NULL # 未使用索引（差）
key_len: NULL
ref: NULL
rows: 500000 # 预估扫描行数
filtered: 1.00
Extra: Using where
关键字段详解
| 字段 |
说明 |
优化目标 |
| type |
访问类型 |
const > eq_ref > ref > range > index > ALL |
| key |
实际使用的索引 |
应为非 NULL |
| rows |
预估扫描行数 |
越小越好 |
| Extra |
额外信息 |
避免 Using filesort、Using temporary |
type 访问类型（从优到差）：
const：主键/唯一索引常量查询（最优）eq_ref：唯一索引 JOINref：非唯一索引查询range：范围查询（BETWEEN, IN, >）index：全索引扫描ALL：全表扫描（最差）
EXPLAIN 分析实战
案例 1：全表扫描优化
问题 SQL：
EXPLAIN SELECT * FROM orders WHERE user_id = 12345\G
-- type: ALL
-- rows: 500000
-- key: NULL
优化：添加索引
CREATE INDEX idx_user_id ON orders(user_id);
-- 再次 EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 12345\G
-- type: ref
-- rows: 100
-- key: idx_user_id # 使用索引
案例 2：联合索引优化
问题 SQL：
SELECT * FROM orders
WHERE user_id = 12345 AND status = 'pending'
ORDER BY created_at DESC;
-- EXPLAIN 结果：
-- type: ref
-- key: idx_user_id
-- Extra: Using where; Using filesort # filesort 性能差
优化：创建覆盖索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);
-- 再次 EXPLAIN
-- type: ref
-- key: idx_user_status_created
-- Extra: Using index # 索引覆盖（最优）
4. 索引优化策略
联合索引最左前缀原则
-- 索引：idx_abc(a, b, c)
-- 有效使用索引
SELECT * FROM t WHERE a = 1; # 使用 a
SELECT * FROM t WHERE a = 1 AND b = 2; # 使用 a, b
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3; # 使用 a, b, c
-- 无法使用索引（跳过 a）
SELECT * FROM t WHERE b = 2; # 不使用索引
SELECT * FROM t WHERE c = 3; # 不使用索引
索引选择性
定义：选择性 = DISTINCT(column) / COUNT(*)，越接近 1 越好。
查看选择性：
SELECT
COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity,
COUNT(DISTINCT status) / COUNT(*) AS status_selectivity
FROM orders;
-- user_id_selectivity: 0.85 # 好
-- status_selectivity: 0.02 # 差（只有几种状态）
索引建议：
- 高选择性字段优先建索引（如 user_id）
- 低选择性字段不单独建索引（如 status）
- 联合索引：高选择性在前（user_id, status）
索引检查与优化
查看未使用的索引：
-- MySQL 8.0+
SELECT * FROM sys.schema_unused_indexes;
查看索引大小：
SELECT
table_name,
index_name,
ROUND(stat_value * @@innodb_page_size/1024/1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'your_db'
ORDER BY stat_value DESC;
删除重复索引：
-- 检查重复索引
SELECT * FROM sys.schema_redundant_indexes;
-- 删除
ALTER TABLE orders DROP INDEX idx_user_id_old;
5. SQL 优化技巧
避免 SELECT *
-- 差
SELECT * FROM orders WHERE user_id = 12345;
-- 好（减少网络传输）
SELECT id, user_id, total_amount FROM orders WHERE user_id = 12345;
分页优化
-- 差（深度分页慢）
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 好（使用主键范围）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;
IN vs EXISTS
-- 小表驱动大表：用 IN
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE active = 1);
-- 大表驱动小表：用 EXISTS
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.active = 1);
避免函数/计算破坏索引
-- 差（索引失效）
SELECT * FROM orders WHERE DATE(created_at) = '2025-10-24';
-- 好（使用索引）
SELECT * FROM orders
WHERE created_at >= '2025-10-24 00:00:00'
AND created_at < '2025-10-25 00:00:00';
6. 数据库参数优化
InnoDB 缓冲池
-- 查看当前值
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- 推荐：物理内存 50-80%
SET GLOBAL innodb_buffer_pool_size = 8G; # 16GB 内存服务器
查询缓存（MySQL 5.7，8.0 已移除）
SHOW VARIABLES LIKE 'query_cache%';
-- MySQL 8.0 不再支持 query_cache
连接数
SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections = 500;
7. 监控与告警
Prometheus + mysqld_exporter
安装 mysqld_exporter：
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz
tar xf mysqld_exporter-*.tar.gz
cd mysqld_exporter-*
# 创建监控用户
mysql -e "CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password';"
mysql -e "GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';"
# 启动
export DATA_SOURCE_NAME='exporter:password@(localhost:3306)/'
./mysqld_exporter &
PromQL 慢查询告警：
# 慢查询速率
rate(mysql_global_status_slow_queries[5m]) > 10
# 慢查询占比
rate(mysql_global_status_slow_queries[5m]) / rate(mysql_global_status_questions[5m]) > 0.05
实时慢查询监控
-- 查看当前执行中的慢查询
SELECT
id,
user,
host,
db,
command,
time,
state,
info
FROM information_schema.processlist
WHERE time > 2 -- 超过 2 秒
ORDER BY time DESC;
-- 杀死慢查询
KILL 12345; -- id 为 12345 的查询
8. 实战案例
案例：订单查询优化
初始 SQL（耗时 5 秒）：
SELECT o.*, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;
EXPLAIN 分析：
table | type | key | rows | Extra
orders| ALL | NULL | 500000 | Using where; Using filesort
users | ref | PRIMARY | 1 | NULL
问题：
- orders 全表扫描
- Using filesort（排序未使用索引）
优化步骤：
- 创建联合索引：
CREATE INDEX idx_status_created ON orders(status, created_at);
- 再次 EXPLAIN：
table | type | key | rows | Extra
orders| ref | idx_status_created | 1000 | Using index
users | ref | PRIMARY | 1 | NULL
- 结果：耗时从 5 秒降至 50ms。
最佳实践
- 慢查询阈值：生产环境设为 1-2 秒
- 索引原则：
- 频繁查询字段建索引
- WHERE、JOIN、ORDER BY 字段优先
- 避免过多索引（影响写入性能）
- 定期分析：每周用 pt-query-digest 分析慢查询
- EXPLAIN 先行：上线前 EXPLAIN 所有 SQL
- 监控告警：慢查询速率 > 10/s 告警
- 索引维护：定期检查未使用索引并删除
- 参数调优：innodb_buffer_pool_size = 内存 * 0.7
- 读写分离：大量查询使用从库
- 分库分表：单表超过 1000 万行考虑分表
- 缓存层：热点数据加 Redis 缓存
工具汇总
| 工具 |
用途 |
命令示例 |
| EXPLAIN |
执行计划分析 |
EXPLAIN SELECT ... |
| pt-query-digest |
慢查询日志分析 |
pt-query-digest slow.log |
| mysqltuner |
参数优化建议 |
./mysqltuner.pl |
| mysqldumpslow |
慢查询日志汇总 |
mysqldumpslow -s t -t 10 slow.log |
| sys schema |
内置性能分析 |
SELECT * FROM sys.schema_unused_indexes |
文档版本：1.0 测试环境：MySQL 8.0 测试日期：2025-10
数据库性能调优是一项持续性的工作，需要结合监控、日志分析和实战经验。在云栈社区的数据库板块，汇聚了许多关于MySQL、索引优化等深度讨论与实战案例，欢迎和更多开发者一起交流学习。
发表于 1 小时前
|
查看: 2|
回复: 0
