MySQL慢查询定位分析与索引优化实战：性能调优完整方案

MySQL慢查询是数据库性能问题的首要瓶颈。一条全表扫描SQL在高并发下可导致CPU打满、响应超时甚至主从延迟雪崩。本文从slow log配置、EXPLAIN执行计划解读到索引优化策略，提供一套完整的MySQL慢查询诊断与优化流程。

慢查询日志配置与采集

开启慢查询日志，设定阈值捕获执行时间超过指定值的SQL：

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 动态开启（运行时生效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;       -- 超过1秒的查询记录
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 未使用索引的查询也记录
SET GLOBAL min_examined_row_limit = 100;  -- 扫描行数超过100才记录

-- 永久生效：my.cnf配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

使用pt-query-digest分析慢日志，按执行频率和总耗时排序：

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

# 输出示例：
# Profile
# Rank Query ID           Response time  Calls  R/Call  V/M
# ==== ================== ============== ====== ======= =====
#    1 0xABC123...         1250.5 45.2%    320  3.9141  0.12
#    2 0xDEF456...          830.2 30.0%     15  55.34   0.45
#    3 0x789GHI...          420.8 15.2%    890  0.4728  0.03

# Query 1: 占总慢查询时间45.2%，平均执行3.9秒，调用320次
# 这是优先优化的目标

EXPLAIN执行计划深度解读

对慢查询SQL执行EXPLAIN分析，判断扫描方式和索引使用情况：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 
  AND status = 'PAID' 
  AND created_at >= '2026-07-01'
ORDER BY created_at DESC
LIMIT 20;

-- type列为ALL表示全表扫描，rows列为18500000表示扫描1850万行
-- possible_keys和key均为NULL表示没有走索引

关键列含义解读：

• type：访问类型，从优到差：const > eq_ref > ref > range > index > ALL。ALL表示全表扫描，必须优化。
• possible_keys：可能使用的索引。NULL表示没有可用索引。
• key：实际使用的索引。NULL表示未走索引。
• rows：预估扫描行数。18500000意味着全表1850万行扫描。
• Extra：额外信息。Using filesort（文件排序）和Using temporary（临时表）均需优化。

联合索引设计与最左前缀原则

上述查询涉及user_id、status、created_at三个条件和一个排序字段。设计联合索引时遵循最左前缀原则，将等值查询字段放前，范围查询字段放后：

-- 创建联合索引
ALTER TABLE orders ADD INDEX idx_user_status_created 
    (user_id, status, created_at);

-- 再次EXPLAIN验证
EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 AND status = 'PAID' 
  AND created_at >= '2026-07-01'
ORDER BY created_at DESC LIMIT 20;

-- type变为ref，key为idx_user_status_created，rows降至约200
-- Extra: Using index condition（索引下推）

索引列顺序设计原则：等值查询放最前（区分度最高优先），范围查询放最后（避免后续列无法走索引），排序字段紧跟等值查询后（利用索引有序性消除filesort）。

-- 冗余索引检测
SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME, 
       b.INDEX_NAME AS redundant_index
FROM information_schema.STATISTICS a
JOIN information_schema.STATISTICS b 
  ON a.TABLE_SCHEMA = b.TABLE_SCHEMA 
  AND a.TABLE_NAME = b.TABLE_NAME 
  AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
  AND a.COLUMN_NAME = b.COLUMN_NAME
  AND a.INDEX_NAME < b.INDEX_NAME
GROUP BY a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME, b.INDEX_NAME;

覆盖索引消除回表查询

当查询字段全部包含在索引中时，InnoDB直接从索引树返回数据，无需回表读取聚簇索引：

-- 原查询：SELECT * 需要回表
SELECT * FROM orders WHERE user_id = 12345 AND status = 'PAID';

-- 优化为覆盖索引查询
SELECT user_id, status, created_at, order_no 
FROM orders WHERE user_id = 12345 AND status = 'PAID';

-- 创建覆盖索引（包含查询所需全部字段）
ALTER TABLE orders ADD INDEX idx_covering 
    (user_id, status, created_at, order_no);

-- EXPLAIN结果Extra列显示：Using index（覆盖索引）

覆盖索引将随机IO（回表）转为顺序IO（索引扫描），在批量查询场景下性能提升可达5-10倍。但索引字段过多会增加写入开销和索引存储空间，需要权衡读写比例。

分页查询深度优化

传统LIMIT offset, size在offset较大时性能急剧下降，MySQL需扫描offset+size行后丢弃前offset行：

-- 慢查询：LIMIT 1000000, 20 需扫描1000020行
SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20;
-- 执行时间：约8.5秒

-- 优化方案1：延迟关联（子查询走覆盖索引）
SELECT t.* FROM orders t
INNER JOIN (
    SELECT id FROM orders 
    ORDER BY created_at DESC 
    LIMIT 1000000, 20
) tmp ON t.id = tmp.id;
-- 执行时间：约0.3秒（子查询走idx_covering索引）

-- 优化方案2：游标分页（基于上一页最后一条记录）
SELECT * FROM orders 
WHERE created_at < '2026-07-15 10:30:00'  -- 上一页最后记录的时间
ORDER BY created_at DESC 
LIMIT 20;
-- 执行时间：约0.02秒（直接走索引范围扫描，无offset）

游标分页无法跳页，适用于App端无限滚动加载场景。如果必须支持跳页，延迟关联是折中方案。

查询重写与SQL优化技巧

常见SQL写法导致的性能问题及改写方案：

-- 1. 避免在索引列上使用函数（索引失效，全表扫描）
-- 慢：
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-05';
-- 快：改为范围查询
SELECT * FROM orders 
WHERE created_at >= '2026-08-05 00:00:00' 
  AND created_at < '2026-08-06 00:00:00';

-- 2. 避免隐式类型转换（user_id是INT，传字符串导致索引失效）
-- 慢：
SELECT * FROM orders WHERE user_id = '12345';
-- 快：
SELECT * FROM orders WHERE user_id = 12345;

-- 3. 避免SELECT *，明确指定字段
-- 慢：查询所有列，无法走覆盖索引
SELECT * FROM orders WHERE user_id = 12345;
-- 快：只查需要的列
SELECT order_no, amount, status FROM orders WHERE user_id = 12345;

-- 4. OR改UNION ALL（当OR两侧走不同索引时）
-- 慢：可能只用一个索引或全表扫描
SELECT * FROM orders WHERE user_id = 12345 OR amount > 10000;
-- 快：分别走各自索引
SELECT * FROM orders WHERE user_id = 12345
UNION ALL
SELECT * FROM orders WHERE amount > 10000 AND user_id != 12345;

-- 5. IN子句限制数量（大量IN值导致解析缓慢和执行计划不稳定）
-- 慢：
SELECT * FROM orders WHERE user_id IN (/* 10000+个ID */);
-- 快：改用JOIN临时表
CREATE TEMPORARY TABLE tmp_user_ids (id INT PRIMARY KEY);
INSERT INTO tmp_user_ids VALUES (12345),(67890);
SELECT o.* FROM orders o JOIN tmp_user_ids t ON o.user_id = t.id;

优化完成后持续监控慢查询日志，结合pt-query-digest定期分析，确保新上线的SQL不会引入性能回退。