在运维与开发场景中，MySQL 慢查询分析是保障数据库性能的核心能力。本篇文章聚焦“诊断、定位与优化实战”的全流程，面向运维工程师和开发者，帮助从日志到执行计划再到结构设计，形成一套可落地的慢查询解决方案。
1. 慢查询分析全流程：诊断入口
1.1 采集与准备数据
在第一阶段，数据源的完整性与一致性决定后续诊断的准确性。需要从慢查询日志、通用日志、错误日志，以及系统视图、性能模式（Performance Schema）中汇聚信息，形成统一的数据源。关键点包括设定合理的 long_query_time、开启 slow_query_log 以及可选的 log_queries_not_using_indexes，以捕捉潜在的慢操作与非索引查询。
另一部分是对数据库运行时指标的基线建立。InnoDB 缓冲池命中率、每秒锁等待、每秒 I/O 等指标，能帮助判断是否存在 I/O 瓶颈或缓存效果不足的问题。若有分库分表或分区场景，需同步关注分区命中与分布特征。
-- 开启慢查询日志与日志阈值的示例配置（根据版本调整）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1; -- 单位：秒，低于该阈值的查询会被记录
SET GLOBAL log_queries_not_using_indexes = 'ON';
1.2 识别高成本查询的特征
通过聚合与排序等手段，定位高成本查询的“聚焦点”是诊断的核心。查询频率、累计耗时、平均耗时、最大耗时等维度构成了分析的核心。结合 performance_schema 的摘要表，可以快速找出消耗最大的语句模式。
常见特征包括：未使用索引的全表扫描、导致大量临时表/排序的查询、返回结果集过大、以及跨表连接中缺乏覆盖索引的情况。将这些要点记录为待排查的“候选慢查询”，方便后续逐条分析。
-- 使用 Performance Schema 汇总慢查询消耗
SELECT digest_text, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
1.3 工具与数据源总览
除了 MySQL 自带的日志与系统视图，sys schema、Performance Schema、以及 第三方工具（如 Percona Toolkit 的 pt-query-digest）共同构成了慢查询分析的工具链。通过对多源数据的对比，可以提高定位准确性。
常见工作流包括：从慢查询日志提取后聚合、结合 EXPLAIN 产出执行计划、再结合系统指标判断是否因资源导致慢查。以下展示一个简化的工作流示例。
# 通过 pt-query-digest 对慢查询日志进行分析
pt-query-digest slow-query.log
2. 精准定位：定位慢查询的根因
2.1 打开慢查询日志与系统视图的组合应用
在定位阶段，慢查询日志提供了事件级别的证据，而Performance Schema与 sys schema 则提供了结构化的统计视图，帮助快速锁定热点SQL与执行路径。通过对两者的对比，可以排除缓存或短暂波动因素。
一个有效的做法是先从日志导出最耗时的查询，再用执行计划进行逐条分析。下列查询示例展示了如何从摘要表中提取高消耗语句的摘要文本与计数信息，以便后续分析。
SELECT digest_text, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
2.2 使用 EXPLAIN/EXPLAIN ANALYZE 进行查询级分析
在明确了候选慢查询后，EXPLAIN（以及 MySQL 8 的 EXPLAIN FORMAT=json 或 EXPLAIN ANALYZE）是验证执行计划的关键工具。通过对比实际执行与理论执行路径，可以发现索引缺失、连接顺序不佳、临时表与文件排序的产生点。
要点包括关注 type、possible_keys、key、rows、Filtered 等字段，以及对 EXPLAIN FORMAT=json 的输出进行结构化分析。若存在显式的子查询或派生表，需要额外关注是否有冗余扫描或重复计算。
-- 常见的 EXPLAIN 语句
EXPLAIN SELECT u.id, u.name, o.total
FROM users AS u
JOIN (SELECT user_id, SUM(amount) AS totalFROM ordersGROUP BY user_id
) AS o ON u.id = o.user_id
WHERE u.status = 'active';
-- MySQL 8 的 EXPLAIN ANALYZE（附带实际执行信息）
EXPLAIN ANALYZE
SELECT u.id, u.name, o.total
FROM users AS u
JOIN orders AS o ON u.id = o.user_id
WHERE o.created_at > '2024-01-01';
2.3 通过 Performance Schema 深度诊断
Performance Schema 提供了更细粒度的执行数据，如每个 Digest 的统计、等待事件、锁等待等。通过聚合不同维度的数据，可以深入理解慢查询的根因，确保不是单次峰值导致的误诊。
以下示例展示了如何按 Digest 聚合等待时间与调用次数，定位持续高耗时的查询族群，进而聚焦到具体 SQL 文本。
SELECT digest_text, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5;
3. 优化实战：索引与查询改写策略
3.1 索引设计原则与覆盖索引
面对慢查询，索引设计是首要的优化手段。优先考虑创建覆盖查询所需的最小索引，并尽量让 where 条件、连接条件、排序条件都能在一个索引中覆盖，降低回表与排序成本。
设计原则包括：最左前缀、联合索引的顺序、避免在不可用索引列上的条件筛选等。对于大表，尽量避免在 where 条件中对未索引的列进行函数运算。
-- 创建覆盖索引的示例
CREATE INDEX idx_user_id ON users (user_id);
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);
3.2 查询改写与覆盖索引
有时慢查询并非因为没有索引，而是因为连接顺序、子查询的重复计算等结构问题。通过查询改写、使用子查询改写为 JOIN、以及推导出更具覆盖性的查询，可以显著提升性能。
示例中通过将聚合结果提前计算、再与主表进行简单连接，减少了重复计算的成本。
SELECT u.id, u.name, o.total
FROM users AS u
JOIN (SELECT user_id, SUM(amount) AS totalFROM ordersGROUP BY user_id
) AS o ON u.id = o.user_id
WHERE u.status = 'active';
3.3 数据分区、分表与存储引擎优化
在数据量极大或时间维度分布明显的场景，分区或分表可以有效减小单次扫描的数据量。结合分区键、分区策略（如 RANGE、LIST、HASH），可显著降低查询的 I/O 成本。也可以结合存储引擎特性，考虑将热数据放在 InnoDB，冷数据归档到成本较低的存储。
分区示例仅作参考，实际实现需结合版本、DDL 兼容性与应用场景进行设计。以下为一个分区的示意性 SQL：
-- 按日期分区的示意
ALTER TABLE events
PARTITION BY RANGE ( TO_DAYS(event_time) ) (PARTITION p202001 VALUES LESS THAN (TO_DAYS('2020-02-01')),PARTITION p202002 VALUES LESS THAN (TO_DAYS('2020-03-01'))
);
4. 稳定性与监控：持续优化的实践
4.1 周期性回顾与回放分析
持续优化需要把握节奏，定期对慢查询进行回放分析和基线对比。通过对最近一段时间的慢查询进行聚合与排序，可以发现新的热点模式，进而进行迭代优化。
使用 Performance Schema 的摘要表，可以快速看到高消耗 Digest 的演变趋势，以及随时间变化的总等待时间。
SELECT digest_text, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;
4.2 自动化告警与指标体系
为避免人工频繁排查，建立自动化告警与指标体系非常重要。结合应用监控系统，可以对慢查询速率、平均耗时、错误率等关键指标设定阈值，触发告警并产出分析报告。
# Prometheus 报警配置示例（简化版）
alert: MySQLSlowQuery
expr: rate(mysql_global_status{type="slow_queries"}[5m]) > 100
for: 10m
labels:severity: critical
annotations:summary: "High rate of slow queries detected"
# 典型的性能回顾脚本（简化演示）
pt-query-digest --limit 0 slow-query.log > slow_summary.txt
