MySQL索引优化实战：慢查询诊断与执行计划深度分析

数据库运维工作中，MySQL性能调优最常遇到的问题是慢查询。索引设计不合理导致全表扫描、排序临时表过大、回表开销高，是线上系统响应慢的根本原因。本文从慢查询诊断、EXPLAIN执行计划分析到索引优化策略，提供一套完整的SQL查询优化方法论。

慢查询日志开启与分析工具使用

定位慢查询的第一步是开启慢查询日志。MySQL 8.0支持在线动态开启，无需重启实例。

-- 查看慢查询日志状态
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;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 永久生效需写入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

mysqldumpslow工具汇总慢查询日志，按出现次数排序定位高频问题SQL：

# 按查询次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 按总耗时排序
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按返回行数排序
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

pt-query-digest提供更详细的分析报告，包括SQL指纹、执行次数、平均耗时、95%分位耗时、扫描行数等：

pt-query-digest /var/log/mysql/slow.log > slow_report.txt

EXPLAIN执行计划关键字段解读

EXPLAIN是SQL查询优化的核心工具。通过执行计划可以判断SQL是否使用索引、扫描行数估算、连接顺序和使用的访问类型。

EXPLAIN SELECT * FROM orders 
WHERE user_id = 10086 AND status = 'PAID' 
ORDER BY create_time DESC LIMIT 20;

执行计划输出字段解读：

• type：访问类型，性能从优到差：system > const > eq_ref > ref > range > index > ALL。ALL表示全表扫描，必须优化
• key：实际使用的索引名称。NULL表示未使用索引
• key_len：索引使用的字节数，判断联合索引用了几个列
• rows：MySQL估算的扫描行数，越小越好
• Extra：附加信息，关注Using filesort（需额外排序）、Using temporary（使用临时表）、Using where（索引过滤后需回表过滤）

-- 查看完整执行计划（含cost信息）
EXPLAIN FORMAT=JSON SELECT * FROM orders 
WHERE user_id = 10086 AND status = 'PAID' 
ORDER BY create_time DESC LIMIT 20\G

JSON格式输出包含cost信息，可以更精确地评估查询成本。

索引设计原则与覆盖索引优化

索引设计遵循高选择性、查询覆盖和最左前缀三个原则。高选择性指索引列的基数（distinct值数量）接近表行数，区分度高的列适合建索引。

覆盖索引指查询所需的所有列都包含在索引中，无需回表读取数据行。Extra列显示Using index表示命中覆盖索引：

-- 原始查询：需要回表
SELECT user_id, status, amount FROM orders WHERE user_id = 10086;

-- 创建覆盖索引
CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount);

-- 优化后：Using index，无需回表
SELECT user_id, status, amount FROM orders WHERE user_id = 10086;

联合索引列顺序设计遵循等值查询在前、范围查询在后原则。以订单查询为例：

-- 查询场景：按用户ID查询某状态的最近订单
SELECT * FROM orders 
WHERE user_id = 10086 AND status = 'PAID' 
ORDER BY create_time DESC LIMIT 20;

-- 索引设计：user_id等值 -> status等值 -> create_time排序
CREATE INDEX idx_uid_status_time ON orders(user_id, status, create_time);

-- 该索引可实现：
-- 1. 通过user_id和status定位数据（ref访问）
-- 2. create_time在索引中有序，避免filesort
-- 3. LIMIT 20提前终止扫描

EXPLAIN验证结果应显示type=ref，key=idx_uid_status_time，Extra不含Using filesort。

联合索引最左前缀匹配与索引失效场景

联合索引遵循最左前缀原则，查询条件必须从索引最左列开始使用。跳过中间列会导致后续列无法走索引。

-- 联合索引 (a, b, c)

-- 能命中索引
WHERE a = 1                      -- 使用a
WHERE a = 1 AND b = 2            -- 使用a, b
WHERE a = 1 AND b = 2 AND c = 3  -- 使用a, b, c
WHERE a = 1 AND c = 3            -- 仅使用a，c无法走索引

-- 不能命中索引
WHERE b = 2                      -- 跳过a
WHERE c = 3                      -- 跳过a, b
WHERE b = 2 AND c = 3            -- 跳过a

常见索引失效场景及排查方法：

-- 1. 函数操作导致索引失效
-- 失效写法
WHERE DATE(create_time) = '2026-07-23'
-- 优化写法
WHERE create_time >= '2026-07-23 00:00:00' 
  AND create_time < '2026-07-24 00:00:00'

-- 2. 隐式类型转换
-- 失效写法（user_id为bigint，传字符串）
WHERE user_id = '10086'
-- 优化写法
WHERE user_id = 10086

-- 3. OR条件部分无索引
-- 失效写法（status无索引）
WHERE user_id = 10086 OR status = 'PAID'
-- 优化方案：用UNION ALL
SELECT * FROM orders WHERE user_id = 10086
UNION ALL
SELECT * FROM orders WHERE status = 'PAID' AND user_id != 10086

-- 4. LIKE前缀通配符
-- 失效写法
WHERE product_name LIKE '%手机%'
-- 优化写法（后缀通配可走索引）
WHERE product_name LIKE '手机%'

-- 5. NOT IN / NOT EXISTS
-- 通常不走索引，改用LEFT JOIN ... IS NULL
SELECT o.* FROM orders o
LEFT JOIN blacklist b ON o.user_id = b.user_id
WHERE b.user_id IS NULL

数据备份恢复场景中，索引重建也是性能维护环节。大批量数据导入后建议先删除索引、导入数据、再重建索引，减少索引维护开销。数据库高可用架构下，主库负责写入和复杂查询，从库承担报表和统计查询，通过读写分离分散索引扫描压力。分库分表方案中，分片键选择需保证查询条件包含分片键，避免跨分片扫描导致索引失效。