MySQL慢查询诊断与索引优化实战指南

慢查询是数据库性能问题的根源

MySQL性能调优的第一步是定位慢查询。线上数据库80%以上的性能问题由不到5%的SQL语句造成——全表扫描、缺失索引、低效JOIN、子查询改写不当是四大元凶。数据库运维的核心工作不是调参数，而是找出这些慢SQL并从根本上优化执行计划。

慢查询日志配置与分析

1. 开启慢查询日志

-- my.cnf动态配置
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;      -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未用索引的查询
SET GLOBAL min_examined_row_limit = 100;  -- 扫描行低于100不记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

生产环境建议long_query_time设0.5-1秒，避免日志量过大。min_examined_row_limit过滤掉扫描行数很少的慢查询。

2. pt-query-digest分析Top N慢查询

pt-query-digest /var/log/mysql/slow.log --limit 95% --order-by Query_time:sum

# 输出示例
# Rank Query ID           Response time  Calls  R/Call  V/M
# ==== ================== ============== ====== ======= ====
#    1 0x7A3B2C1D4E5F6...  2543.1234 45%  1234   2.0639  0.12
#    2 0x8B9C0D1E2F3A4...  1876.5678 33%   567   3.3012  0.08
#    3 0x9C0D1E2A3B4C5...   432.9012  8%  3456   0.1253  0.01

Response time占比最高的Query ID就是优化目标。查看具体SQL：

pt-query-digest slow.log --filter '$event->{arg} =~ /0x7A3B2C1D4E5F6/' --print

EXPLAIN执行计划深度解读

拿到目标SQL后用EXPLAIN分析执行计划：

EXPLAIN FORMAT=JSON
SELECT o.order_id, o.amount, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.status = 'PAID' AND o.created_at > '2026-01-01'
ORDER BY o.amount DESC
LIMIT 20;

重点关注以下字段：

– **type**：access类型，从优到劣：system > const > eq_ref > ref > range > index > ALL。出现ALL（全表扫描）必须优化
– **key**：实际使用的索引，NULL表示未用索引
– **rows**：预估扫描行数，越大越危险
– **Extra**：附加信息，出现Using filesort或Using temporary需要重点优化
– **Filtered**：过滤比例，低于10%说明索引选择性差

JSON格式输出更详细，attached_condition字段展示MySQL在存储引擎层还是服务层做过滤。过滤在存储引擎层（ICP）效率更高。

索引设计原则与实战案例

案例1：联合索引最左前缀匹配

-- 原始查询
SELECT * FROM orders 
WHERE user_id = 1001 AND status = 'PAID' 
ORDER BY created_at DESC 
LIMIT 10;

-- 错误索引：where条件和排序分属不同索引
ALTER TABLE orders ADD INDEX idx_user (user_id);
ALTER TABLE orders ADD INDEX idx_status (status);
ALTER TABLE orders ADD INDEX idx_created (created_at);

-- 正确索引：联合索引覆盖where + order
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

-- EXPLAIN验证
type: ref
key: idx_user_status_created
rows: 45
Extra: Backward index scan  -- MySQL 8.0+降序索引优化

联合索引(user_id, status, created_at)同时满足等值过滤和排序，避免了filesort。

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

-- 查询只需要少量列
SELECT order_id, amount FROM orders 
WHERE user_id = 1001 AND status = 'PAID';

-- 覆盖索引：查询列全部包含在索引中
ALTER TABLE orders ADD INDEX idx_cover (user_id, status, order_id, amount);

-- EXPLAIN输出
Extra: Using index  -- 无回表，直接从索引树返回数据

覆盖索引将查询从5万次回表降到0次，IO减少90%以上。代价是索引占用更多磁盘空间，适合高频查询场景。

案例3：索引下推ICP优化

-- MySQL 5.6+支持Index Condition Pushdown
SELECT * FROM orders 
WHERE user_id = 1001 AND amount > 1000;

-- 索引
ALTER TABLE orders ADD INDEX idx_user_amount (user_id, amount);

-- ICP开启时：存储引擎层先用user_id定位，再用amount > 1000过滤
-- ICP关闭时：存储引擎只按user_id过滤，amount在Server层判断
-- ICP减少回表次数

SET optimizer_switch = 'index_condition_pushdown=on';  -- 默认开启

JOIN优化与子查询改写

1. 小表驱动大表

-- 慢写法：大表做驱动表
SELECT o.* FROM orders o 
JOIN order_items i ON o.id = i.order_id 
WHERE o.status = 'PAID';

-- 优化：确保小结果集做驱动
-- EXPLAIN中rows少的是驱动表
-- 添加合适的索引让优化器选对驱动顺序
ALTER TABLE order_items ADD INDEX idx_order_id (order_id);

2. 子查询改写JOIN

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

-- 快：改写为JOIN + 派生表
SELECT o.* FROM orders o
JOIN (
  SELECT user_id, AVG(amount) as avg_amount 
  FROM orders GROUP BY user_id
) avg_t ON o.user_id = avg_t.user_id
WHERE o.amount > avg_t.avg_amount;

MySQL 8.0对子查询有大量优化（semijoin、materialization），但复杂相关子查询的性能仍不如JOIN。

分页查询优化方案

深分页问题

-- 慢：LIMIT 100000, 20扫描100020行丢弃100000行
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 优化方案1：游标分页（推荐）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- 优化方案2：延迟关联
SELECT o.* FROM orders o
JOIN (
  SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) tmp ON o.id = tmp.id;

延迟关联的核心思路：子查询走覆盖索引只取ID（无回表），再关联主表取完整数据。100万行表的深分页从3秒降到50毫秒。

在线诊断工具与监控

1. sys库快速诊断

-- 当前正在执行的SQL
SELECT * FROM sys.session 
WHERE command != 'Sleep' AND time > 1\G

-- 等待锁的会话
SELECT * FROM sys.innodb_lock_waits\G

-- 索引使用统计（发现冗余索引）
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

2. Performance Schema持续监控

-- 开启statements_digest
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' 
WHERE NAME = 'statements_digest';

-- Top 10慢SQL
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000 as total_sec,
  AVG_TIMER_WAIT/1000000 as avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

3. Prometheus + mysqld_exporter监控

核心告警指标：

– mysql_global_status_slow_queries增长速率 > 10/min
– mysql_global_status_innodb_row_lock_waits > 5/min
– mysql_global_status_threads_running接近max_connections的80%

索引优化没有银弹，核心方法论是：慢查询日志定位 → EXPLAIN分析执行计划 → 针对性建索引 → 验证优化效果 → 持续监控。定期用pt-query-digest做周度回顾，防止新上线的SQL引入性能回退。