MySQL慢查询排查与索引优化实战：EXPLAIN执行计划深度解读

MySQL性能调优是数据库运维的核心工作，慢查询是性能问题的头号元凶。一条低效SQL在高并发下能拖垮整个数据库实例。掌握慢查询日志分析、EXPLAIN执行计划解读和索引优化方法的组合拳，能解决90%以上的数据库性能问题。本文以真实排查案例拆解完整调优流程。

慢查询日志配置与采集

MySQL默认不开启慢查询日志，需要手动配置。生产环境中建议通过配置文件持久化设置。

# my.cnf / my.ini 配置
[mysqld]
# 开启慢查询日志
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log

# 慢查询阈值（秒），超过此值记录
long_query_time = 1

# 记录未使用索引的查询
log_queries_not_using_indexes = ON

# 慢查询日志文件大小上限
max_binlog_size = 256M

运行时动态修改（重启失效，需配合配置文件持久化）：

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

-- 动态开启
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

采集到慢查询日志后，使用mysqldumpslow或pt-query-digest工具分析。pt-query-digest是Percona Toolkit中的工具，能按执行频率、总耗时、平均耗时等维度聚合排序：

# 按总耗时排序，取TOP 20
pt-query-digest /var/log/mysql/slow.log --order-by Query_time:sum --limit 20

# 输出示例
# Profile
# Rank Query ID           Response time  Calls  R/Call  V/M
# ==== ================== ============== ====== ======= =====
#    1 0xABC123...         150.5600 45.2%   1200 0.1255  0.03
#    2 0xDEF456...          80.3200 24.1%    856 0.0938  0.05

Response time占比最高的语句是优化重点。R/Call是平均单次执行耗时，V/M是方差均值比，值越大说明执行时间波动越大，可能存在数据倾斜问题。

EXPLAIN执行计划字段详解

EXPLAIN是分析SQL执行计划的核心工具。在SQL前加EXPLAIN关键字即可查看优化器选择的执行路径。

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

+----+-------------+-------+------+-------------------+--------+---------+--------------+------+-------------+
| id | select_type | table | type | possible_keys     | key    | key_len | ref          | rows | Extra       |
+----+-------------+-------+------+-------------------+--------+---------+--------------+------+-------------+
|  1 | SIMPLE      | o     | ref  | idx_user,idx_time | idx_time| 8       | const        | 5800 | Using where |
|  1 | SIMPLE      | u     | eq_ref| PRIMARY           | PRIMARY| 8       | test.o.user_id|   1 | NULL        |
+----+-------------+-------+------+-------------------+--------+---------+--------------+------+-------------+

关键字段解读：

• type：访问类型，性能从好到差依次为system > const > eq_ref > ref > range > index > ALL。ALL表示全表扫描，必须优化。index表示扫描整个索引树，也比ALL好但仍有优化空间。
• key：实际使用的索引。NULL表示未使用索引。
• rows：优化器估计需要扫描的行数。值越小越好。
• Extra：额外信息。Using index表示覆盖索引，Using filesort表示需要额外排序操作，Using temporary表示使用临时表，后两者都是性能隐患。

索引类型选择与联合索引最左前缀原则

MySQL索引分主键索引（聚簇索引）和二级索引（非聚簇索引）。二级索引的叶子节点存储主键值，查询需要的列不在索引中时需要回表。覆盖索引指索引包含了查询需要的所有列，无需回表。

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

-- 能命中索引的查询
SELECT * FROM orders WHERE status = 'PAID';  -- 命中最左前缀
SELECT * FROM orders WHERE status = 'PAID' AND create_time >= '2026-07-01';  -- 命中两列
SELECT * FROM orders WHERE status = 'PAID' AND create_time >= '2026-07-01' AND user_id = 10086;  -- 全命中

-- 不能命中索引的查询
SELECT * FROM orders WHERE create_time >= '2026-07-01';  -- 跳过status，无法使用索引
SELECT * FROM orders WHERE user_id = 10086;  -- 跳过前两列，无法使用索引

联合索引的最左前缀原则要求查询条件从索引最左列开始连续匹配。索引列顺序设计原则：等值查询列放前面，范围查询列放后面；区分度高的列放前面，区分度低的放后面。

SQL查询优化典型案例

案例一：深度分页性能劣化。LIMIT 1000000, 20的查询需要扫描前100万行再丢弃，效率极低。

-- 优化前：扫描100万+行
SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 20;

-- 优化后：通过子查询先定位主键，再关联查询
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY create_time DESC LIMIT 1000000, 20
) tmp ON o.id = tmp.id;

-- 更优方案：使用游标分页（记住上一页最后一条的create_time和id）
SELECT * FROM orders 
WHERE create_time < '2026-06-15 10:30:00'
ORDER BY create_time DESC LIMIT 20;

案例二：函数导致索引失效。WHERE字段上使用函数会导致索引无法使用。

-- 索引失效：DATE函数阻止索引使用
SELECT * FROM orders WHERE DATE(create_time) = '2026-08-04';

-- 索引有效：改为范围查询
SELECT * FROM orders 
WHERE create_time >= '2026-08-04 00:00:00' 
  AND create_time < '2026-08-05 00:00:00';

案例三：隐式类型转换导致索引失效。VARCHAR字段与数字比较时MySQL会进行隐式转换。

-- 索引失效：phone是VARCHAR类型，与数字比较触发隐式转换
SELECT * FROM users WHERE phone = 13800138000;

-- 索引有效：使用字符串比较
SELECT * FROM users WHERE phone = '13800138000';

数据库高可用架构中的索引维护

索引不是越多越好。每个索引增加写入开销和存储空间，update/insert/delete操作需要同步维护所有索引。数据库高可用架构中，主从复制的延迟部分原因就是从库需要重放所有索引变更。

索引维护建议：定期使用sys.schema_unused_indexes视图检查无用索引并清理；使用sys.schema_redundant_indexes检查冗余索引；对大表加索引使用ONLINE DDL避免锁表。数据迁移实战中，索引创建顺序影响迁移时间——先迁数据再建索引比先建索引再迁数据快得多。

-- 检查无用索引
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema');

-- 在线添加索引（MySQL 8.0默认支持）
ALTER TABLE orders ADD INDEX idx_status (status), ALGORITHM=INPLACE, LOCK=NONE;

-- 大表索引重建
ALTER TABLE orders ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

对于数据备份恢复场景，建议在恢复数据后重建统计信息（ANALYZE TABLE），让优化器基于最新数据选择最优执行计划。国产数据库如OceanBase、TiDB在SQL兼容性上与MySQL高度一致，上述优化方法同样适用，但执行计划查看命令需替换为各自的管理工具。