详解MySQL 慢查询日志( 附详细案例 )

概念

慢查询日志（slow query log）是 MySQL 记录执行时间超过指定阈值SQL 的日志，用来定位慢 SQL、性能瓶颈，是 SQL 优化最基础的工具。

一、慢 SQL

慢 SQL 不是语法错误的 SQL，是执行效率低下，消耗大量数据库资源（CPU、IO、锁），执行耗时较长的 SQL 语句。 简单说：跑的慢、耗资源，拖垮 MySQL 的 SQL，就叫慢 SQL。

慢查询日志只是用来捕获慢 SQL 的工具；慢 SQL 是对象，慢查询日志是记录工具，二者不要混淆。

判定标准

有两个核心判断维度，不能只看执行时间

举例：一条 SQL 执行 0.4 秒，没达到 1s 阈值不会进慢日志，但扫描 20 万行。数据量增长后，未来会变成严重慢 SQL，这就是隐形慢 SQL，最容易被忽略。

慢 SQL 常见成因

1. 索引问题（占 80% 以上场景）

• 没建索引，发生全表扫描
• 索引失效：隐式类型转换、like %xxx、or 条件、对索引列做函数运算、not in 等
• 索引选择错误：优化器选错索引
• 索引区分度差，低选择性索引

2. SQL 写法问题

• select * 查询大量不需要字段，回表开销大，网络传输量大
• 大表 join，关联字段无索引
• 不合理子查询，衍生表没有索引
• limit 大偏移：limit 100000,10，扫描前面十万行
• in 里面放成千上万的值
• order by /group by 没有合适索引，触发文件排序、临时表

3. 业务 & 数据层面

• 单表数据量过大，千万级别以上，没有分库分表
• 事务过大：一个事务里面执行大量 DML，锁持有时间长，导致大量 SQL 阻塞等待锁
• 批量一次性操作海量数据

4. 数据库环境因素

• IO 压力高、CPU 打满，再好的 SQL 也跑慢
• 锁等待：不是 SQL 本身慢，是等行锁 / 表锁，Lock_time很高
• buffer pool 太小，大量数据读磁盘而不是内存

慢 SQL 带来的危害

如何发现慢 SQL

常见误区

❌慢 SQL 就是执行时间很长的 SQL

错，存在大量隐形慢 SQL：耗时短，但扫描行数巨大。

❌没出现在慢查询日志，就不是慢 SQL

阈值设置过高，隐形慢 SQL 不会落日志。

❌ explain 看到 type 不是 ALL 就没问题

range 也可能扫描几十万行，依然是慢 SQL。

❌测试环境跑很快，线上就是慢

测试库数据量小，看不出全表扫描问题；线上真实数据量才会暴露。

简单例子

— 坏：name无索引，全表扫描百万行
select * from user where name = '张三';

— 坏：大偏移分页，扫描前面10万行
select id,name from order limit 100000,10;

— 坏：索引列上用函数，索引失效
select * from user where DATE(create_time)='2026‑08‑27';

慢 SQL 和慢查询日志的区别

• 慢 SQL：低效 SQL 语句本身
• 慢查询日志：MySQL 提供记录慢 SQL 的日志文件，是发现慢 SQL 的手段。

二、慢 SQL日志

1. 核心参数

注意：long_query_time统计的是实际执行时间，不包含锁等待时间。SQL 命中慢查询条件才写入日志。

2. 开启慢查询

方式 1：临时开启（重启 MySQL 失效）

set global slow_query_log = ON;
set global long_query_time = 1; — 超过1秒就记录
set global log_queries_not_using_indexes = OFF;

global 修改后，已建立的会话不生效，需要重新连接数据库。

方式 2：永久配置 my.cnf/my.ini

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 0
log_output = FILE

⚠️：修改配置文件需要重启 MySQL 服务。

查看当前配置

show variables like '%slow_query%';
show variables like 'long_query_time';

3. 慢查询日志文件内容解读

示例日志片段：

# Time: 260827 10:20:30
# User@Host: root[root] @ localhost [] Id: 12
# Query_time: 2.345678 Lock_time: 0.000123 Rows_sent: 100 Rows_examined: 100000
SET timestamp=1787826030;
select * from big_table where name='xxx';

字段释义：

重点看：Query_time 看慢；Rows_examined看扫描量，很多 SQL 耗时不长，但扫描百万行，未来数据量上涨就会变卡。

4. 分析工具：mysqldumpslow

MySQL 自带工具，解析慢日志，汇总统计，不用肉眼看庞大日志文件。

常用命令

# 查看帮助
mysqldumpslow –help

# 按照查询时间排序，取前10条慢SQL
mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log

# 按照扫描行数排序（看扫描量大的SQL）
mysqldumpslow -s r -t 10 mysql-slow.log

# 匹配包含select的语句
mysqldumpslow -s t -g "select" mysql-slow.log

参数说明：

• -s：排序方式
  • t：Query_time 查询时间
  • r：Rows_sent 返回行数
  • a：平均查询时间
• -t：top N，输出多少条
• -g：正则过滤 SQL

mysqldumpslow 会把值替换成N、S做聚合，相同结构不同参数的 SQL 合并统计，方便看高频慢 SQL。

第三方工具

• pt-query-digest（percona toolkit）：工业级慢日志分析，比 mysqldumpslow 强大，支持分析文件、binlog、general log，输出报告包含占比、次数、平均耗时。生产环境最常用。

5. 生产最佳实践

6. 常见误区

7. 和 show processlist、performance_schema 的区别

• 慢查询日志：事后记录，已经跑完的慢 SQL；
• show processlist：实时看当前正在运行 SQL；
• performance_schema：可以做更细粒度监控，不需要文件日志，适合线上持续监控。

三、拿到慢 SQL 之后标准排查优化流程

整体步骤

1. Explain 重点看哪些字段

explain select * from t where name='xxx';

• type：访问类型，性能从优到劣：system > const > eq_ref > ref > range > index > ALL
  • ALL：全表扫描，重点优化
  • range：范围扫描，要看扫描行数是否过大
• key：实际使用的索引，NULL 代表没用到索引
• rows：预估扫描行数，数值越大越危险
• Extra：额外信息
  • Using filesort：文件排序，需要磁盘排序，性能差，要建立联合索引避免
  • Using temporary：使用临时表，常见 group by /distinct，开销大
  • Using index：覆盖索引，很好，不需要回表
  • Using where：存储引擎返回数据后，MySQL 服务层再过滤

⚠️ explain 是预估，不是真实运行结果，优化器会根据统计信息选择索引，统计信息不准会导致 explain 结果和真实执行不一致。

2. 对应原因

实战示例

建表：

CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(32) DEFAULT NULL,
`age` int DEFAULT NULL,
`status` tinyint DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_status_createtime` (`status`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

示例 1：全表扫描 type=ALL（最坏）

explain select * from user where name='zhangsan';

解读：

• type=ALL：全表扫描
• possible_keys=NULL：没有可选索引
• key=NULL：没使用索引
• rows=10000：预估扫描 10000 行
• Extra:Using where：引擎把全部数据返回给 MySQL 服务层，在服务层做name='zhangsan'过滤。

问题：没有索引，全表扫描，扫描全部 10000 行，在 server 层过滤数据。 解决方法

— 给查询条件建立索引
create index idx_name on user(name);

注意：字段区分度很低不要建索引（比如性别），索引收益极小。

示例 2：range 范围扫描（风险场景）

explain select * from user where id between 1 and 8000;

问题：type 虽然是 range，但是预估扫描 8000 行，扫描数据量大，依然是慢 SQL。range 不等于快。 解决方法

— 书签分页示例
select id,name from user where id>100 limit 10;

示例 3：Using filesort 文件排序

删除 idx_status_createtime 索引后执行

explain select * from user where status=1 order by create_time desc;

问题：查询出满足 where 的数据之后，MySQL 额外做排序操作，内存不够就落到磁盘文件排序，性能差。

解决方法

建立联合索引：where 过滤字段放前面，order by 排序字段紧跟其后。

create index idx_status_createtime on user(status,create_time);

建立索引后，索引本身已经有序，不需要 filesort。

注意：排序方向要一致，不能一个 asc 一个 desc，会失效。

示例 4：Using temporary 临时表

status 无索引：

explain select status,count(*) from user group by status;

表格

问题：group by /distinct 需要创建临时表存储中间分组结果，开销高。 解决方法

— group by的字段建立索引
create index idx_status on user(status);

索引有序，可以直接分组，不需要临时表。

如果业务无法建索引：减少 group by 分组基数，业务内存做聚合，不要数据库大表分组。

示例 5：Using index ✅覆盖索引（优秀）

explain select status,create_time from user where status=1;

说明：查询字段全部包含在索引中，不需要回表查询主键数据，性能很高。

如何实现覆盖索引 select 后面查询的列，全部放到联合索引里。

— 查询 status,create_time，索引包含这两个字段
KEY `idx_status_createtime` (`status`,`create_time`)

❌不要 select *，会破坏覆盖索引，触发回表。

示例 6：possible_keys 有索引，key 为 NULL（有索引但是不走）

场景：索引区分度差。status 只有 0 和 1 两个值。

explain select * from user where status=1;

possible_keys 显示idx_status，key=null，type=ALL。

问题：优化器判断走索引回表代价高于全表扫描，放弃索引直接全表扫描。 解决方法

select * from user force index(idx_status) where status=1;

示例 7：索引失效（隐式类型转换）

name 是 varchar 字符串字段，查询传入数字

explain select * from user where name=1234;

即使有idx_name索引，key 依旧为 NULL，type=ALL。 原因：字段发生隐式类型转换，索引失效。 解决方法：保持类型一致

select * from user where name='1234';

示例 8：type=index（扫描整个索引树）

explain select name from user;

type=index，Extra 为空。

type=index：遍历整个二级索引树，不是全表，但扫描大量索引页，性能差；不要和Using index混淆。 解决方法：业务加 where 条件，不要不加条件查询大量数据。

Extra 字段速查表

表格

3. 区分：是 SQL 本身慢，还是锁等待慢

看慢日志里 Lock_time：

• Lock_time 大，Query_time 不大：SQL 本身很快，大量时间在等锁，属于锁冲突问题，不是 SQL 索引问题。 排查方向：大事务、长事务，行锁冲突。
• Lock_time 很小，Query_time 很大：SQL 执行本身慢，优先优化索引和 SQL 写法。

4. 优化后验证

总结

拿到 explain 执行计划：

注意：rows 是预估值，MySQL 统计信息不准，explain 结果不等于真实执行情况。