MySQL慢查询诊断全流程：从定位到索引优化与SQL改写

MySQL慢查询诊断全流程：从定位到根治

MySQL慢查询是数据库运维中最常见也最致命的问题。一条低效SQL可能拖垮整个数据库实例，影响所有依赖该实例的业务服务。系统化的慢查询诊断流程包含：慢查询捕获→执行计划分析→索引优化→SQL改写→效果验证，每一步都有明确的方法论和工具支撑。

慢查询日志捕获与配置

生产环境必须开启慢查询日志，这是诊断的起点。推荐配置：

-- my.cnf 关键参数
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5          # 超过500ms记录
min_examined_row_limit = 100   # 扫描行数低于100的不记录
log_queries_not_using_indexes = 1  # 未走索引的查询也记录
log_slow_admin_statements = 1     # 记录慢管理语句(如ALTER)

long_query_time设置为0.5秒而非更低的理由：阈值过低会产生大量噪音日志，影响IO性能。0.5秒是平衡信号与噪音的实用起点，后续通过pt-query-digest聚合分析Top N即可。

慢查询日志分析工具：

# pt-query-digest分析Top 20慢查询
pt-query-digest --limit 20 /var/log/mysql/slow.log

# 输出示例:
# Rank Query ID         Response time  Calls  R/Call  V/M
# ==== ================ ============== ====== ======= ====
#    1 0x5A8B3C2D1E4F   12540.0000    892    14.05   0.23
#    2 0x7F9E2D4A3B1C    8320.5000    567    14.69   0.31
#    3 0x1A2B3C4D5E6F    4120.2000   1203     3.43   0.05

重点关注Response time占比最高的Query ID，这些是优化的第一优先级。

EXPLAIN执行计划深度解读

定位到具体慢查询后，通过EXPLAIN分析执行计划。MySQL 8.0+建议使用EXPLAIN FORMAT=TREE或EXPLAIN ANALYZE获取更直观的执行信息。

-- 传统EXPLAIN
EXPLAIN SELECT o.order_id, o.total_amount, u.user_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.created_at >= '2026-07-01'
  AND o.status = 'PAID'
  AND u.region = '华东'
ORDER BY o.total_amount DESC
LIMIT 50;

-- MySQL 8.0+ 真实执行分析
EXPLAIN ANALYZE
SELECT ...;  -- 同上

执行计划关键字段诊断规则：

Using filesort和Using temporary同时出现是最危险的信号，说明查询需要在内存中构建临时表并排序，数据量大时直接导致慢查询甚至OOM。

索引优化策略与实战案例

案例一：复合索引的列顺序优化

上述慢查询中，orders表的查询条件为created_at + status，排序字段为total_amount。错误的做法是分别建两个单列索引：

-- 错误方案：MySQL只能选一个索引使用
CREATE INDEX idx_created_at ON orders(created_at);
CREATE INDEX idx_status ON orders(status);

-- 正确方案：复合索引覆盖查询条件和排序
CREATE INDEX idx_status_created_amount ON orders(status, created_at, total_amount);

复合索引遵循最左前缀原则：(status, created_at, total_amount)的索引可以同时覆盖WHERE过滤和ORDER BY排序，避免filesort。索引列顺序的选择依据：等值查询列在前(status=’PAID’)，范围查询列在后(created_at >= ‘2026-07-01’)，排序列放最后(total_amount DESC)。

案例二：索引下推(ICP)优化

-- MySQL 5.6+ 索引下推示例
-- 表结构: users(id, name, region, age, created_at)
-- 索引: INDEX idx_region_name(region, name)

SELECT * FROM users 
WHERE region = '华东' AND name LIKE '张%';

-- 无ICP: 存储引擎通过idx_region_name找到region='华东'的所有记录，返回Server层过滤name
-- 有ICP: 存储引擎在索引中直接过滤name LIKE '张%'，减少回表次数

ICP在EXPLAIN的Extra列显示Using index condition。MySQL 8.0默认开启，无需手动配置。

案例三：覆盖索引消除回表

-- 原查询：每条记录都需回表获取total_amount
SELECT order_id, total_amount FROM orders 
WHERE status = 'PAID' AND created_at >= '2026-07-01';

-- 优化：创建覆盖索引
CREATE INDEX idx_status_created_id_amount ON orders(status, created_at, order_id, total_amount);

-- EXPLAIN Extra显示 Using index，零回表

SQL改写技巧

索引优化解决不了所有问题，SQL本身的写法也会影响性能。

子查询转JOIN：

-- 慢：相关子查询，每行执行一次子查询
SELECT * FROM orders o
WHERE o.total_amount > (
  SELECT AVG(total_amount) FROM orders WHERE region = o.region
);

-- 快：改为JOIN + 子查询预计算
SELECT o.* FROM orders o
JOIN (
  SELECT region, AVG(total_amount) AS avg_amount
  FROM orders GROUP BY region
) r ON o.region = r.region
WHERE o.total_amount > r.avg_amount;

避免函数导致索引失效：

-- 慢：DATE函数导致created_at索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-27';

-- 快：范围查询走索引
SELECT * FROM orders 
WHERE created_at >= '2026-07-27 00:00:00' 
  AND created_at < '2026-07-28 00:00:00';

分页优化：

-- 慢：深分页offset大时扫描大量行
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 快：游标分页（需要连续id）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- 替代方案：延迟关联
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t
ON o.id = t.id;

数据库高可用架构下的慢查询治理

在主从架构中，慢查询治理需要区分读写场景：

• 读慢查询：从库承受所有读压力，优先通过读写分离将慢查询路由到专用分析从库，避免影响在线业务
• 写慢查询：主库上的写操作是性能瓶颈，必须通过索引优化和SQL改写根治

读写分离路由配置（基于ProxySQL）：

-- 将慢分析查询路由到分析从库
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (10, 1, 'GROUP BY|COUNT|SUM|AVG', 20, 1);

-- hostgroup 10: 主库(写)
-- hostgroup 20: 分析从库(读)

慢查询治理是一个持续过程。建立自动化的慢查询巡检机制：每天通过pt-query-digest生成Top 20慢查询报告，自动创建优化工单，跟踪优化进度与效果。同时设置慢查询告警阈值，当5分钟内慢查询数量超过20条时触发P2告警，确保慢查询问题不被积压。