1. 首页
  2. 新手教程
  3. MySQL 慢查询分析

MySQL 慢查询日志分析优化教程

慢查询日志是 MySQL 内置的性能诊断工具，它会自动记录执行时间超过设定阈值的 SQL 语句。通过分析慢查询日志，可以快速定位数据库性能瓶颈，有针对性地进行索引优化和 SQL 改写。本文将在搬瓦工 VPS 上演示慢查询日志的完整使用流程，从开启配置到分析工具再到优化实践。

一、开启慢查询日志

1.1 通过配置文件永久开启

编辑 MySQL 配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100
log_slow_admin_statements = 1

参数说明：

  • long_query_time = 1：记录执行时间超过 1 秒的查询。初期可以设为 2 秒，优化后逐步降低。
  • log_queries_not_using_indexes：记录未使用索引的查询，即使执行时间未超过阈值。
  • min_examined_row_limit = 100：只记录扫描行数超过 100 行的查询，过滤掉小表查询。
  • log_slow_admin_statements：同时记录 ALTER TABLE、OPTIMIZE TABLE 等管理语句。
systemctl restart mysql

1.2 在线动态开启（无需重启）

mysql -u root -p

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;

动态设置在 MySQL 重启后会失效，建议同时写入配置文件。

1.3 验证慢查询日志状态

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

二、慢查询日志分析工具

2.1 mysqldumpslow（内置工具）

MySQL 自带的慢查询汇总工具，功能简单但够用：

# 按查询时间排序，显示前 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# 按查询次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log

# 按平均锁定时间排序
mysqldumpslow -s l -t 10 /var/log/mysql/slow-query.log

常用排序参数：-s t 按总时间、-s c 按次数、-s r 按总行数、-s at 按平均时间。

2.2 pt-query-digest（推荐）

Percona Toolkit 中的 pt-query-digest 是更强大的慢查询分析工具，提供详细的统计报告。

# 安装 Percona Toolkit
apt install percona-toolkit -y

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow-query.log > /tmp/slow-report.txt

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

# 过滤特定数据库
pt-query-digest --filter='$event->{db} eq "mydb"' /var/log/mysql/slow-query.log

pt-query-digest 的报告包含：查询响应时间占比、调用次数、平均/最大/最小执行时间、扫描行数、发送行数等关键指标，并按影响度从高到低排序。

三、EXPLAIN 执行计划分析

找到慢查询后，使用 EXPLAIN 分析执行计划是优化的第一步：

EXPLAIN SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending' ORDER BY created_at DESC LIMIT 10\G

3.1 关键字段解读

  • type：访问类型，从优到差依次为 system > const > eq_ref > ref > range > index > ALL。出现 ALL 表示全表扫描，需要优化。
  • key：实际使用的索引。为 NULL 表示未命中任何索引。
  • rows：预估扫描行数。数值越大性能越差。
  • Extra：额外信息。出现 Using filesort 或 Using temporary 通常需要优化。

3.2 EXPLAIN ANALYZE（MySQL 8.0.18+）

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending'\G

EXPLAIN ANALYZE 会实际执行查询并返回真实的执行时间和行数，比普通 EXPLAIN 的预估更准确。

四、常见优化策略

4.1 添加合适的索引

# 为频繁查询的 WHERE 条件添加组合索引
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

# 查看表的索引情况
SHOW INDEX FROM orders;

# 查看索引使用统计
SELECT * FROM sys.schema_index_statistics WHERE table_schema = 'mydb';

索引设计原则：将选择性高的列放在前面，覆盖 WHERE、ORDER BY 和 SELECT 字段（覆盖索引）。

4.2 SQL 改写技巧

# 避免 SELECT *，只查需要的列
SELECT id, user_id, amount, created_at FROM orders WHERE user_id = 12345;

# 避免在索引列上使用函数
# 差：WHERE YEAR(created_at) = 2026
# 好：WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

# 避免隐式类型转换
# 差：WHERE phone = 13800138000（phone 是 varchar）
# 好：WHERE phone = '13800138000'

# LIMIT 优化深分页
# 差：SELECT * FROM orders ORDER BY id LIMIT 1000000, 10
# 好：SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10

4.3 查询缓存与参数调优

# 查看 InnoDB Buffer Pool 使用情况
SHOW STATUS LIKE 'Innodb_buffer_pool%';

# 调整 Buffer Pool 大小（建议设为物理内存的 60-80%）
# 在配置文件中设置
innodb_buffer_pool_size = 512M

# 查看临时表使用情况
SHOW STATUS LIKE 'Created_tmp%';

五、Performance Schema 监控

MySQL 8.0 的 Performance Schema 提供更细粒度的性能监控：

# 查看最耗时的 SQL 语句（Top 10）
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;

# 查看等待事件统计
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

六、sys 库常用视图

MySQL sys 库提供了人类可读的性能视图：

# 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

# 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;

# 查看表的 I/O 统计
SELECT * FROM sys.schema_table_statistics ORDER BY total_latency DESC LIMIT 10;

# 查看全表扫描的语句
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

七、慢查询日志管理

7.1 日志轮转

慢查询日志文件会持续增长，需要定期轮转：

# 手动轮转
mv /var/log/mysql/slow-query.log /var/log/mysql/slow-query.log.old
mysqladmin flush-logs

或配置 logrotate，创建 /etc/logrotate.d/mysql-slow：

/var/log/mysql/slow-query.log {
    daily
    rotate 7
    compress
    missingok
    notifempty
    postrotate
        mysqladmin flush-logs
    endscript
}

7.2 监控慢查询数量趋势

# 查看慢查询总数
SHOW GLOBAL STATUS LIKE 'Slow_queries';

# 通过脚本定时记录
echo "$(date): $(mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';" | grep Slow)" >> /var/log/slow-count.log

八、实战优化案例

假设通过 pt-query-digest 发现以下查询耗时最长：

SELECT * FROM products WHERE category_id = 5 AND price BETWEEN 100 AND 500 ORDER BY sales_count DESC LIMIT 20;

优化步骤：

# 1. 查看当前执行计划
EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price BETWEEN 100 AND 500 ORDER BY sales_count DESC LIMIT 20\G

# 2. 添加组合索引
ALTER TABLE products ADD INDEX idx_cat_price_sales (category_id, price, sales_count);

# 3. 再次查看执行计划，确认索引被使用
EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price BETWEEN 100 AND 500 ORDER BY sales_count DESC LIMIT 20\G

总结

慢查询日志分析是 MySQL 性能优化的第一道工序。在搬瓦工 VPS 资源有限的环境下，通过精准的索引优化和 SQL 改写，可以让数据库性能提升数倍甚至数十倍。建议将 pt-query-digest 分析纳入日常运维流程，持续监控数据库健康状况。更多数据库教程可参考 MySQL 主从复制 和 XtraBackup 热备份。选购搬瓦工 VPS 请查看 全部方案，使用优惠码 NODESEEK2026 享受 6.77% 折扣，通过 bwh81.net 购买。

关于本站

搬瓦工VPS中文网（bwgvps.com）是非官方中文信息站，整理搬瓦工的方案、优惠和教程。我们不销售主机，不提供技术服务。

新手必读
搬瓦工优惠码

NODESEEK2026（优惠 6.77%）

购买时填入即可抵扣。