昨天 22:06 门头沟学院 Java 发布于北京

关注

MySQL 慢查询日志

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花

一、慢查询日志核心定义

MySQL 慢查询日志（Slow Query Log）是 MySQL 内置的性能诊断工具，专门用于记录执行时间超过预设阈值（由 long_query_time 参数定义）的 SQL 语句，同时可配置记录未使用索引的查询，是定位数据库性能瓶颈、优化 SQL 语句的核心抓手之一。

其核心价值在于精准捕捉低效 SQL，清晰呈现 SQL 执行时的资源消耗情况（如执行时间、锁等待时间、扫描行数等），为开发和运维人员提供明确的优化方向，避免因慢 SQL 占用 CPU、内存、IO 等核心资源，导致系统响应延迟、锁等待、服务雪崩等问题。

注意：慢查询日志默认处于关闭状态，开启后会带来轻微的性能开销，但相较于问题排查和性能优化的价值，该开销几乎可忽略不计，尤其适合测试环境和生产环境的非峰值时段开启。

二、核心配置参数详解

慢查询日志的行为由多个 MySQL 系统参数控制，可通过命令行查看或修改，核心参数如下（适用于 MySQL 5.7+、8.0 版本）：

slow_query_log：慢查询日志的开关，取值为 ON（开启）或 OFF（关闭），默认 OFF；部分版本支持用 1 表示开启、0 表示关闭。
slow_query_log_file：慢查询日志的存储路径及文件名，需确保 MySQL 进程对该路径拥有写入权限（如 Linux 下常用路径 /var/log/mysql/slow.log），默认路径通常为 MySQL 数据目录下的 hostname-slow.log。
long_query_time：慢查询时间阈值，单位为秒，支持微秒级（如 1.5 表示 1.5 秒），执行时间超过该值的 SQL 会被记录，默认值为 10 秒，实际业务中通常建议设为 1 秒（根据业务延迟需求调整）。
log_queries_not_using_indexes：是否记录未使用索引的 SQL 语句，取值为 ON 或 OFF，默认 OFF；开启后，即使这类 SQL 执行时间未达 long_query_time 阈值，也会被记录，有助于发现索引优化漏洞。
log_output：日志输出方式，可选值为 FILE（输出到文件）、TABLE（输出到系统表 mysql.slow_log）或两者皆选（FILE,TABLE），默认值为 FILE。
log_slow_admin_statements：可选参数，是否记录执行时间超过阈值的管理语句（如 ALTER TABLE、ANALYZE TABLE 等），默认 OFF。
log_slow_slave_statements：可选参数，是否记录从库上的慢查询语句，默认 OFF，适用于主从复制架构。
min_examined_row_limit：可选参数，最少检查行数阈值，低于该值的慢查询不会被记录，默认 0，可根据业务调整（如设为 100，避免记录扫描少量行的低效查询）。

查看当前慢查询配置的命令：

-- 查看所有慢查询相关参数
SHOW VARIABLES LIKE '%slow%';
-- 查看慢查询阈值
SHOW VARIABLES LIKE 'long_query_time';
-- 查看是否记录未使用索引的查询
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

三、慢查询日志的开启与配置方法

慢查询日志的配置分为两种方式：临时配置（重启 MySQL 后失效，适合快速调试）和永久配置（修改配置文件，适合生产环境），以下以 Linux 系统 MySQL 8.0 为例说明，Windows 系统仅需修改配置文件名为 my.ini 即可。

3.1 临时开启与配置（重启失效）

通过 MySQL 命令行登录数据库后，执行以下命令即可快速开启和配置，无需重启服务：

-- 登录 MySQL
mysql -u root -p

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;

-- 配置日志存储路径（需提前创建目录并授权）
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

-- 设置慢查询阈值为 1 秒（根据业务调整）
SET GLOBAL long_query_time = 1;

-- 记录未使用索引的 SQL 语句
SET GLOBAL log_queries_not_using_indexes = ON;

-- 配置日志输出方式为文件+表
SET GLOBAL log_output = 'FILE,TABLE';

验证配置生效：修改 global 级别的参数后，需重新登录 MySQL 才能查看最新值，执行以下命令验证：

exit;
mysql -u root -p
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

若参数值与配置一致，则临时配置成功。

3.2 永久开启与配置（生产环境推荐）

临时配置会在 MySQL 重启后失效，生产环境需通过修改 MySQL 配置文件实现永久生效，步骤如下：

编辑 MySQL 配置文件：Linux 系统配置文件通常为 /etc/my.cnf 或 /etc/mysql/my.cnf，执行命令 vim /etc/my.cnf 打开编辑。
在 [mysqld] 节点下添加或修改以下配置（根据需求调整参数值）：

[mysqld]
# 开启慢查询日志（1=开启，0=关闭）
slow_query_log = 1
# 慢查询日志存储路径（确保 mysql 用户有写入权限）
slow_query_log_file = /var/log/mysql/mysql-slow.log
# 慢查询阈值（单位：秒，支持小数，如 0.5 表示 500 毫秒）
long_query_time = 1
# 记录未使用索引的 SQL 语句
log_queries_not_using_indexes = 1
# 日志输出方式（文件+表）
log_output = FILE,TABLE
# 记录慢管理语句（可选）
log_slow_admin_statements = 1
# 记录从库慢查询（可选，主从架构适用）
log_slow_slave_statements = 1
# 最少检查行数阈值（可选）
min_examined_row_limit = 100

授权日志目录权限：若日志路径为新建目录，需授权 MySQL 用户访问，避免写入失败：

mkdir -p /var/log/mysql
chown mysql:mysql /var/log/mysql -R
chmod 755 /var/log/mysql -R

重启 MySQL 服务使配置生效：

# 系统d服务（CentOS 7+/Ubuntu 16+）
systemctl restart mysqld
# 传统service命令（旧版本系统）
service mysqld restart

重启后，登录 MySQL 执行查看配置的命令，确认参数已生效即可。

四、慢查询日志格式解析

慢查询日志（文件格式）每条记录包含 SQL 执行的关键信息，格式清晰，核心字段可直接用于分析问题，典型日志条目及字段解释如下：

# Time: 2026-03-14T21:00:00.123456Z
# User@Host: root(root) @ localhost (127.0.0.1) Id: 5
# Query_time: 5.123456  Lock_time: 0.001000  Rows_sent: 10  Rows_examined: 1000000
SET timestamp=1710476400;
SELECT * FROM users WHERE last_name LIKE '%smith%' ORDER BY create_time DESC;

各关键字段解释：

Time：SQL 查询执行的时间戳（UTC 时间），可转换为本地时间用于定位问题发生时段。
User@Host：执行该 SQL 的 MySQL 用户名、主机地址及连接 ID，用于定位具体操作来源。
Query_time：SQL 总执行时间（单位：秒，含微秒），核心指标，数值越大说明查询效率越低。
Lock_time：SQL 执行过程中锁等待的时间（单位：秒），锁等待时间过长可能导致并发阻塞。
Rows_sent：查询返回给客户端的行数，若返回行数少但扫描行数多，说明查询效率低。
Rows_examined：MySQL 执行查询时扫描的行数，核心优化指标，扫描行数越多，IO 消耗越大，性能越差。
timestamp：SQL 查询开始执行的 UNIX 时间戳，可用于关联系统其他日志。
最后一行：具体的慢查询 SQL 语句，是优化的核心对象。

五、慢查询日志分析工具

原始慢查询日志冗长、可读性差，尤其是生产环境中日志量较大时，需借助工具高效分析，筛选出高频、高耗时的核心慢 SQL，常用工具分为两类：MySQL 自带工具和第三方工具。

5.1 MySQL 自带工具：mysqldumpslow

mysqldumpslow 是 MySQL 内置的轻量级慢查询分析工具，无需额外安装，适合快速汇总慢查询，支持按执行时间、执行次数、锁时间等排序，常用命令如下：

# 按查询总耗时排序，显示最慢的前10条（-s t：按时间排序，-t 10：显示前10条）
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# 按执行次数排序，显示执行次数最多的前10条（-s c：按次数排序）
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

# 按锁等待时间排序，显示锁等待最长的前10条（-s l：按锁时间排序）
mysqldumpslow -s l -t 10 /var/log/mysql/mysql-slow.log

# 分析特定用户的慢查询，保留原始SQL（-a：保留原始SQL，-g "root"：筛选用户为root的查询）
mysqldumpslow -a -g "root" /var/log/mysql/mysql-slow.log

工具输出会汇总相同类型的慢查询（替换变量为 N），清晰显示每条慢查询的执行次数、平均耗时、锁时间、扫描行数等信息，快速定位高频低效 SQL。

5.2 第三方工具：pt-query-digest（推荐）

pt-query-digest 是 Percona Toolkit 中的核心工具，功能比 mysqldumpslow 更强大，支持更细致的统计分析（如按时间分布、用户、SQL 类型统计），能生成可视化分析报告，还能给出优化建议，适合生产环境大规模慢查询分析。

核心特点：

按执行时间、频率、锁等待时间等多维度统计慢 SQL；
识别重复或相似的慢查询，合并分析；
生成详细的分析报告，包含 SQL 执行详情、优化建议；
支持分析文件日志、表日志，还能实时抓取慢查询。

常用命令（CentOS 系统为例）：

# 安装 Percona Toolkit（需先配置 yum 源）
yum install percona-toolkit -y

# 分析慢查询日志，生成详细报告（输出到屏幕）
pt-query-digest /var/log/mysql/mysql-slow.log

# 分析慢查询日志，将报告输出到文件
pt-query-digest /var/log/mysql/mysql-slow.log > /var/log/mysql/slow-query-report.txt

5.3 其他常用工具

Navicat Monitor、阿里云 DAS：可视化监控平台，支持实时监控慢查询、趋势分析、告警提醒，适合运维人员日常监控。
MySQL Enterprise Monitor：MySQL 官方监控工具，深度适配 MySQL 内核，支持慢查询实时分析、性能诊断。
Zabbix、Prometheus + Grafana：可配置慢查询数量监控和告警，结合 mysqld_exporter 实现全链路性能监控。

六、常见慢查询场景与优化建议

分析慢查询日志的最终目的是优化 SQL 和数据库性能，常见慢查询场景及对应优化方法如下，结合日志中的 Rows_examined、Query_time 等字段可快速定位根因：

6.1 索引相关问题（最常见）

场景：日志中 Rows_examined 数值极大（接近表数据量），且 log_queries_not_using_indexes 开启后记录了该 SQL，说明未命中索引或索引失效。

优化建议：

为查询条件、关联字段（JOIN 字段）创建合适的索引，优先选择区分度高的字段（如用户 ID、订单号，而非性别、状态）。
避免索引失效场景：不对索引字段使用函数、运算（如 WHERE SUBSTR(name,1,3) = 'abc'），不使用 %前缀模糊查询（如 LIKE '%smith'），避免隐式类型转换（如字符串索引字段用数字查询）。
合理设计联合索引，遵循最左匹配原则（如联合索引 (a,b,c)，仅 a、a+b、a+b+c 能命中索引）。
避免索引过多，索引会降低 INSERT/UPDATE/DELETE 等写入操作的性能，按需创建索引。

6.2 SQL 语句编写不规范

场景：Query_time 长，Rows_sent 少但 Rows_examined 多，或存在多层嵌套子查询、滥用 SELECT *。

优化建议：

禁止滥用 SELECT *，只查询业务需要的字段，减少磁盘 IO、网络传输和内存占用。
优化 JOIN 操作：关联字段必须创建索引，优先用小表驱动大表，避免超过 3 张表的 JOIN，可拆分查询或冗余字段。
减少子查询，改用 JOIN 或临时表，子查询嵌套过深会产生临时表和文件排序，效率极低。
分页查询使用 LIMIT，大数据量分页优化为 WHERE id > ? LIMIT 20，避免使用LIMIT 100000, 20 导致全表扫描。
避免不必要的 DISTINCT、ORDER BY，若必须使用，确保排序字段有索引。

6.3 数据库设计不合理

场景：大量慢查询集中在某张表，且表数据量极大（超千万），或查询时关联字段混乱。

优化建议：

拆分大表：单表数据量超千万时，采用分库分表（水平分表或垂直分表），降低单表查询压力。
优化表结构：避免字段冗余，合理选择字段类型（如用 INT 存储数字，不用 VARCHAR；用 DATE/DATETIME 存储时间，不用字符串）。
规范关联表设计，统一关联字段，避免无意义的 JOIN 操作。

6.4 其他优化场景

定时任务 SQL：避免在业务高峰期执行批量操作、报表统计等耗时 SQL，可调整执行时间至非峰值时段，或拆分任务分批执行。
锁等待优化：若 Lock_time 过长，检查是否存在长事务、行锁冲突，优化事务逻辑，减少事务执行时间。

七、慢查询日志使用注意事项

日志轮转：慢查询日志会持续增长，需配置日志轮转（如 Linux 下用 logrotate），避免日志文件过大占用磁盘空间。
阈值调整：long_query_time 需根据业务场景调整，对延迟敏感的业务（如电商支付）可设为 0.5~1 秒，非敏感业务可设为 2~3 秒，避免阈值过低导致日志量过大，或阈值过高遗漏慢查询。
生产环境建议：生产环境峰值时段可根据服务器性能决定是否开启，若开启后影响性能，可仅在非峰值时段开启，或调整日志输出方式（如仅输出到表，减少文件 IO）。
日志安全：慢查询日志可能包含敏感数据（如用户信息、密码），需限制日志文件的访问权限（如仅允许 root 和 mysql 用户访问），避免数据泄露。
定期分析：建立慢查询定期分析机制（如每日/每周分析），及时发现并优化新出现的慢查询，形成性能优化闭环。