MySQL 慢查询日志深度分析与优化实践

在生产环境中，数据库性能问题往往不是 CPU、内存或磁盘故障引起，而是少量低效 SQL 长期积累造成的。很多服务器负载居高不下，最终排查发现仅仅是几条慢查询拖垮了整个数据库。

MySQL 提供了完善的慢查询日志（Slow Query Log）功能，通过分析慢查询日志，可以快速定位性能瓶颈。

本文介绍慢查询日志开启、分析以及优化思路。

开启慢查询日志

查看当前配置：

SHOW VARIABLES LIKE '%slow_query%';

查看执行时间阈值：

SHOW VARIABLES LIKE 'long_query_time';

默认输出：

slow_query_log      OFF

long_query_time     10

表示：

执行时间超过10秒

才记录日志

生产环境建议：

[mysqld]

slow_query_log=ON

slow_query_log_file=/data/mysql/logs/slow.log

long_query_time=1

log_queries_not_using_indexes=ON

动态开启：

SET GLOBAL slow_query_log='ON';

SET GLOBAL long_query_time=1;

验证：

SHOW VARIABLES LIKE 'slow_query_log';

输出：

ON

说明已生效。

生成测试慢查询

执行：

SELECT SLEEP(5);

查看日志：

tail -20 /data/mysql/logs/slow.log

示例：

# Query_time: 5.000923

# Lock_time: 0.000000

# Rows_sent: 1

# Rows_examined: 0

SELECT SLEEP(5);

重要字段：

使用 mysqldumpslow 分析

MySQL 自带分析工具：

mysqldumpslow /data/mysql/logs/slow.log

按执行次数排序：

mysqldumpslow -s c /data/mysql/logs/slow.log

按平均执行时间排序：

mysqldumpslow -s t /data/mysql/logs/slow.log

查看前10条：

mysqldumpslow -s t -t 10 /data/mysql/logs/slow.log

输出示例：

Count: 532 Time=12.35s

SELECT * FROM orders WHERE uid=N

表示：

执行532次

平均耗时12.35秒

优先优化此类 SQL。

pt-query-digest 深度分析

生产环境推荐使用 Percona Toolkit。

安装：

yum install -y percona-toolkit

分析：

pt-query-digest slow.log

输出：

Rank Query ID Response time Calls R/Call

1    0x123456      65.3%  523   1.35s

2    0x789abc      18.4%  841   0.25s

可以快速发现：

最耗时SQL

执行频率最高SQL

锁等待SQL

扫描行数过大SQL

生成报告：

pt-query-digest slow.log > report.txt

便于长期归档分析。

常见低效 SQL

假设存在订单表：

CREATE TABLE orders (

    id BIGINT PRIMARY KEY,

    uid BIGINT,

    amount DECIMAL(10,2),

    status TINYINT,

    create_time DATETIME

);

错误示例：

SELECT * FROM orders

WHERE uid=10001;

如果 uid 无索引：

全表扫描

数据量：

5000万行

性能极差。

查看执行计划：

EXPLAIN

SELECT * FROM orders

WHERE uid=10001;

结果：

type: ALL

rows: 50000000

表示全表扫描。

创建合适索引

优化：

ALTER TABLE orders

ADD INDEX idx_uid(uid);

再次查看：

EXPLAIN

SELECT * FROM orders

WHERE uid=10001;

结果：

type: ref

rows: 12

扫描行数从：

50000000

↓

12

性能提升极大。

避免索引失效

错误写法：

SELECT *

FROM orders

WHERE DATE(create_time)='2026-06-01';

执行时：

索引失效

优化：

SELECT *

FROM orders

WHERE create_time >= '2026-06-01 00:00:00'

AND create_time <  '2026-06-02 00:00:00';

保持索引可用。

避免 SELECT *

错误：

SELECT *

FROM users

WHERE id=100;

问题：

读取所有字段

增加IO

增加网络传输

优化：

SELECT id,name,email

FROM users

WHERE id=100;

仅返回需要的数据。

利用覆盖索引

创建索引：

ALTER TABLE orders

ADD INDEX idx_uid_status(uid,status);

查询：

SELECT uid,status

FROM orders

WHERE uid=10001;

执行计划：

Using index

表示：

无需回表

性能进一步提升。

分页优化

错误写法：

SELECT *

FROM orders

LIMIT 1000000,20;

MySQL需要扫描：

1000020行

优化：

SELECT *

FROM orders

WHERE id > 1000000

LIMIT 20;

利用主键递增分页。

性能提升明显。

锁等待分析

查看当前事务：

SHOW PROCESSLIST;

查看锁：

SHOW ENGINE INNODB STATUS\\G

MySQL 8.0：

SELECT *

FROM performance_schema.data_locks;

常见问题：

长事务未提交

大批量更新

表锁竞争

这些都会导致慢查询增加。

定期清理慢日志

查看大小：

ls -lh slow.log

超过数 GB 后分析效率下降。

轮转示例：

mv slow.log slow.log.$(date +%F)

mysqladmin flush-logs

保留最近30天：

find /data/mysql/logs \\

-name "slow.log*" \\

-mtime +30 \\

-delete

加入计划任务：

0 3 * * * /opt/mysql_slow_rotate.sh

生产环境推荐参数

[mysqld]

slow_query_log=ON

slow_query_log_file=/data/mysql/logs/slow.log

long_query_time=1

log_queries_not_using_indexes=ON

log_slow_admin_statements=ON

min_examined_row_limit=1000

适用于：

MySQL 5.7

MySQL 8.0

MariaDB

Percona Server

慢查询日志是数据库优化过程中最重要的排障工具之一。相比盲目升级硬件或增加数据库节点，通过分析慢查询日志找到真正的性能瓶颈，往往能够用最小的成本获得最大的收益。生产环境建议长期开启慢查询日志，并结合 pt-query-digest 定期生成分析报告，将数据库性能问题提前发现并处理，而不是等到业务高峰期出现故障后再被动排查。