4、慢查询定位：开启慢查询日志、pt-query-digest工具使用、常见慢SQL模式识别

在批次追溯数据驱动的采购计划优先级排序场景中，SQL 查询的响应时间直接影响排序算法的执行效率。本章聚焦于如何系统性地定位慢查询，涵盖 MySQL 慢查询日志的开启与配置、专业分析工具 pt-query-digest 的使用方法，以及批次追溯场景中常见的慢 SQL 模式识别。

4.1 开启慢查询日志

慢查询日志是 MySQL 提供的原生性能诊断工具，用于记录执行时间超过指定阈值的 SQL 语句。在批次追溯系统中，建议在生产环境谨慎开启，并设置合理的阈值以避免日志文件过快增长。

4.1.1 动态参数配置

以下 SQL 语句可在 MySQL 客户端中动态启用慢查询日志，无需重启数据库实例：

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

-- 设置慢查询阈值（单位：秒），建议初始设为 1 秒
SET GLOBAL long_query_time = 1;

-- 指定日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow_query.log';

-- 记录未使用索引的查询（便于发现全表扫描）
SET GLOBAL log_queries_not_using_indexes = ON;

4.1.2 持久化配置（my.cnf）

若需重启后配置依然生效，需在 MySQL 配置文件 my.cnf 的 [mysqld] 段中添加以下参数：

[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

4.2 pt-query-digest 工具使用

pt-query-digest 是 Percona Toolkit 中的核心分析工具，能够对慢查询日志进行聚合、排序和统计，快速定位最耗时的 SQL 模式。相比直接查看日志文件，pt-query-digest 提供了结构化的报告，便于识别性能瓶颈。

4.2.1 安装与基本用法

在 CentOS / Ubuntu 系统中安装 Percona Toolkit：

# CentOS / RHEL
yum install percona-toolkit

# Ubuntu / Debian
apt-get install percona-toolkit

分析慢查询日志并输出到终端：

pt-query-digest /var/log/mysql/slow_query.log

将分析结果保存到文件以便后续审查：

pt-query-digest /var/log/mysql/slow_query.log > slow_query_report.txt

4.2.2 报告解读要点

pt-query-digest 报告主要包含以下关键部分：

4.3 常见慢 SQL 模式识别

结合批次追溯数据的特点，以下四种慢 SQL 模式在采购计划优先级排序中频繁出现，需重点识别与治理。

4.3.1 模式一：全表扫描 + 大表关联

当批次追溯表（如 batch_trace）与采购计划表（如 procurement_plan）关联时，若未使用索引，MySQL 将执行全表扫描，导致响应时间随数据量线性增长。

-- 慢查询示例：未使用索引的全表关联
SELECT p.plan_id, p.priority_score, b.batch_status
FROM procurement_plan p
LEFT JOIN batch_trace b ON p.batch_no = b.batch_no
WHERE p.plan_date >= '2025-01-01';

识别特征： pt-query-digest 报告中出现 full scan 标记，且 Rows examine 远大于 Rows sent。

4.3.2 模式二：ORDER BY 未走索引导致文件排序

优先级排序通常需要按 priority_score 降序排列，若该字段未建立索引，MySQL 会使用 filesort 算法，在内存或磁盘中排序，消耗大量 CPU 和 IO。

-- 慢查询示例：ORDER BY 导致文件排序
SELECT plan_id, batch_no, priority_score
FROM procurement_plan
WHERE status = 'PENDING'
ORDER BY priority_score DESC
LIMIT 100;

识别特征： EXPLAIN 输出中 Extra 列包含 Using filesort。

4.3.3 模式三：子查询嵌套过深

批次追溯数据查询中，开发人员常使用多层子查询实现业务逻辑，导致 MySQL 生成低效的执行计划，产生派生表（Derived table）扫描。

-- 慢查询示例：三层子查询嵌套
SELECT plan_id, priority_score
FROM procurement_plan
WHERE batch_no IN (
    SELECT batch_no FROM batch_trace
    WHERE trace_id IN (
        SELECT trace_id FROM trace_detail
        WHERE material_code = 'MAT-001'
    )
);

识别特征： EXPLAIN 中 select_type 为 DEPENDENT SUBQUERY 或 DERIVED，且 rows 估算值极大。

4.3.4 模式四：函数包裹索引列导致索引失效

在 WHERE 条件中对索引列使用函数（如 DATE()、SUBSTRING()），会使 MySQL 无法使用索引，转而执行全表扫描。

-- 慢查询示例：DATE() 函数导致索引失效
SELECT plan_id, priority_score
FROM procurement_plan
WHERE DATE(create_time) = '2025-04-01';

优化写法： 改为范围查询以利用索引：

SELECT plan_id, priority_score
FROM procurement_plan
WHERE create_time >= '2025-04-01 00:00:00'
  AND create_time < '2025-04-02 00:00:00';

4.4 慢查询定位流程总览

以下 SVG 图展示了从开启慢查询日志到定位根因的完整流程，帮助团队建立标准化的性能诊断步骤。

4.5 慢查询模式对比速查表

以下表格汇总了四种常见慢 SQL 模式的识别特征、影响范围及推荐优化策略，便于团队在性能调优时快速对照。