在 MySQL 性能优化中，慢查询日志是定位性能瓶颈的核心依据，但绝大多数开发者仅停留在「开启日志→找到慢 SQL」的浅层操作，无法区分：SQL 是执行效率低（索引失效、全表扫描），还是锁等待阻塞（事务未提交、行锁竞争）？也无法解释「明明建了索引，MySQL 却选择全表扫描」的诡异问题。

本文将带你完成全流程深度分析：从生产级慢查询配置，到用 pt-query-digest 自动化提取高频慢 SQL，再通过 EXPLAIN ANALYZE 拆解索引扫描/全表扫描的切换逻辑，最后解读锁等待场景的慢查询日志。全程附带可直接落地的代码、命令、日志案例，形成「采集→分析→定位→优化→验证」的完整闭环。

一、基础：生产环境慢查询日志精准配置

慢查询日志不是「开了就行」，不合理的配置会导致日志爆炸、遗漏关键问题。我们需要只记录有效慢 SQL，过滤无用日志。

1. 核心配置（my.cnf，永久生效）

   [mysqld]
   # 开启慢查询日志（生产必开）
   slow_query_log = 1
   # 慢日志存储路径（建议独立磁盘）
   slow_query_log_file = /var/log/mysql/slow.log
   # 慢查询阈值：0.1秒（核心业务），超过则记录
   long_query_time = 0.1
   # 记录【未使用索引】的SQL（排查隐形索引失效）
   log_queries_not_using_indexes = 1
   # 记录管理类慢SQL（ALTER/OPTIMIZE 等）
   log_slow_admin_statements = 1
   # MySQL 8.0+：记录额外执行信息（优化分析）
   log_slow_extra = 1
   # 日志输出方式：文件+系统表
   log_output = FILE,TABLE

2. 动态生效（无需重启MySQL，测试/应急用）

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

3. 验证配置

   SHOW VARIABLES LIKE '%slow%';

4. 测试数据准备（模拟慢SQL场景）

   CREATE TABLE `t_order` (
     id BIGINT PRIMARY KEY AUTO_INCREMENT,
     user_id INT NOT NULL,
     order_status TINYINT NOT NULL,
     create_time DATETIME NOT NULL,
     remark VARCHAR(255)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   
   -- 插入100万测试数据
   DELIMITER //
   CREATE PROCEDURE init_data()
   BEGIN
     DECLARE i INT DEFAULT 1;
     WHILE i <= 1000000 DO
       INSERT INTO t_order(user_id, order_status, create_time)
       VALUES (FLOOR(RAND()*10000), FLOOR(RAND()*4), NOW());
       SET i = i+1;
     END WHILE;
   END //
   DELIMITER ;
   
   CALL init_data();

创建订单表，插入100万数据，用于后续慢查询分析：

二、进阶：pt-query-digest 自动化分析高频慢SQL

手动查看慢日志文件效率极低，pt-query-digest（Percona 工具）是行业标准的慢日志分析工具，可聚合、排序、统计慢 SQL，快速定位「最耗性能、执行最频繁」的问题 SQL。

1. 工具安装

   # CentOS
   yum install percona-toolkit -y
   # Ubuntu
   apt install percona-toolkit -y

2. 核心使用命令

   # 1. 基础分析：输出完整慢查询报告
   pt-query-digest /var/log/mysql/slow.log
   
   # 2. 只分析最近1小时的慢SQL
   pt-query-digest --since=1h /var/log/mysql/slow.log
   
   # 3. 提取TOP10高耗时慢SQL，输出到文件
   pt-query-digest --limit=10 /var/log/mysql/slow.log > top10_slow.sql
   
   # 4. 筛选【锁等待超时】的慢SQL（重点）
   pt-query-digest --filter '$event->{lock_time} > 0.5' /var/log/mysql/slow.log

3. 报告核心指标解读（关键！）

分析报告分为三部分，我们只关注性能瓶颈核心字段：

字段	含义	异常判断
Count	SQL 执行次数	越高越危险
Exec time	总执行耗时	占比>50% 为核心瓶颈
Lock time	锁等待耗时	>0.5s 说明锁竞争
Rows examine	扫描行数（最核心指标）	远大于 Rows sent = 全表扫描
Rows sent	返回行数	-

示例：全表扫描的慢SQL报告

# Query 1: 40% of total time
# Exec time: 120s total, 1.2s avg, 2s max
# Rows examine: 1000000 total (全表扫描)
# Rows sent: 1 total
SELECT * FROM t_order WHERE user_id = 1001 AND order_status = 2;

✅ 结论：扫描100万行，仅返回1行 → 索引失效，全表扫描。

三、深度：EXPLAIN ANALYZE 剖析「索引→全表扫描」切换原因

传统 EXPLAIN 只能看预估执行计划，而 MySQL 8.0.18+ 提供的 EXPLAIN ANALYZE 会真实执行SQL，输出实际耗时、实际扫描行数，精准定位索引失效/优化器误判的根因。

1. 基础语法

   EXPLAIN ANALYZE
   SELECT * FROM t_order WHERE user_id = 1001 AND order_status = 2;

2. 场景1：无索引 → 强制全表扫描

执行结果：

-> Table scan on t_order  (cost=100000.00, actual time=0.01..1200.00 rows=1000000)

• Table scan= 全表扫描
• 实际扫描100万行，耗时2s

1. 场景2：有索引，但优化器选择全表扫描（高频坑）

我们先创建索引：

CREATE INDEX idx_user_status ON t_order(user_id, order_status);

再次执行，若仍走全表扫描，只有3个核心原因：

原因1：统计信息过期

MySQL 依赖表统计信息选择执行计划，信息过期会导致误判：

-- 刷新统计信息（修复）
ANALYZE TABLE t_order;

原因2：查询筛选性太差

匹配数据占比>30%，优化器认为全表扫描更快：

-- 验证筛选性
SELECT COUNT(*) FROM t_order WHERE user_id=1001;

✅ 优化方案：强制使用索引 FORCE INDEX(idx_user_status)

原因3：索引失效（隐式转换/函数操作）

-- 错误：user_id是INT，传入字符串（隐式转换，索引失效）
EXPLAIN ANALYZE SELECT * FROM t_order WHERE user_id = '1001';

✅ 优化方案：严格匹配字段类型

1. 优化后：索引扫描（理想状态）

   -> Index lookup on t_order using idx_user_status (user_id=1001, order_status=2)
      (cost=1.00, actual time=0.01..0.02 rows=1)

• Index lookup= 索引命中
• 耗时02ms，性能提升 6万倍

四、高阶：锁等待场景的慢查询日志解读技巧

90% 的开发者会混淆：SQL执行慢 ≠ 锁等待慢。

锁等待导致的慢查询，SQL本身无问题，而是被其他事务阻塞，日志特征极其明显。

1. 锁等待慢日志典型特征

   # Time: 2025-08-01T10:00:00
   # Query_time: 5.000000  (总耗时5s)
   # Lock_time: 4.999000   (锁等待4.999s，99%耗时在等锁)
   # Rows_examined: 0      (未扫描任何数据)
   # Rows_sent: 0
   UPDATE t_order SET order_status=3 WHERE id=1000;

✅ 核心判断：

Lock_time ≈ Query_time + Rows_examined=0 → 纯锁等待阻塞。

1. 定位锁阻塞源（MySQL 8.0+）

通过性能模式视图，直接找到「阻塞事务」：

-- 查询锁等待详情
SELECT
  w.waiting_query AS 等待SQL,
  b.blocking_query AS 阻塞SQL,
  w.waiting_thread_id AS 等待线程ID
FROM performance_schema.data_lock_waits w
JOIN performance_schema.threads b ON w.blocking_thread_id = b.thread_id;

1. 锁等待解决方案
2. 终止长事务（应急）：KILL 阻塞线程ID;
3. 缩小事务范围：禁止在事务中执行远程调用、sleep 等操作
4. 调整锁超时时间：

   SET GLOBAL innodb_lock_wait_timeout = 5; -- 5秒超时，避免无限阻塞​

五、实战：从慢查询到优化的完整闭环

我们用一个真实案例，走完整个优化流程：

步骤1：采集慢日志

pt-query-digest /var/log/mysql/slow.log > slow_report.txt

发现：SELECT * FROM t_order WHERE user_id=1001 AND order_status=2 平均耗时1.2s，扫描100万行。

步骤2：分析执行计划

EXPLAIN ANALYZE
SELECT * FROM t_order WHERE user_id=1001 AND order_status=2;

结论：无索引，全表扫描。

步骤3：优化（创建复合索引）

CREATE INDEX idx_user_status ON t_order(user_id, order_status);

步骤4：验证优化效果

-- 再次执行，耗时降至0.02ms
SELECT * FROM t_order WHERE user_id=1001 AND order_status=2;

步骤5：二次分析慢日志

pt-query-digest --since=10m /var/log/mysql/slow.log

该 SQL 不再出现在慢日志中，优化完成。

六、生产环境最佳实践

1. 阈值设置：核心业务 long_query_time=0.1s，非核心 1s
2. 优先优化高频SQL：执行次数>1000的慢SQL，比单次耗时高的SQL更危险
3. 必用 EXPLAIN ANALYZE：放弃传统 EXPLAIN，用真实执行数据定位问题
4. 区分两类慢SQL：

• 执行慢 → 优化索引、SQL 语法
• 锁等待慢 → 优化事务、锁策略

1. 定期清理日志：避免慢日志占满磁盘
2. 监控告警：对接 Prometheus，慢查询数量突增时实时告警

总结

MySQL 慢查询深度分析的核心，是透过现象看本质：

1. 工具层：用 pt-query-digest快速筛选高频、高耗慢 SQL；
2. 执行层：用 EXPLAIN ANALYZE拆解索引失效、优化器误判的根因；
3. 阻塞层：通过 Lock_time识别锁等待，直接定位阻塞源；
4. 落地层：形成「采集→分析→优化→验证」的闭环，彻底解决性能瓶颈。

掌握这套方法，你可以独立解决生产中 95% 以上的 MySQL 慢查询问题，让数据库性能始终保持最优状态。