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

开启慢查询日志定位问题SQL

MySQL性能调优的第一步是找到执行缓慢的SQL语句。通过慢查询日志记录执行时间超过阈值的SQL，是数据库运维的标准排查手段。

-- 查看慢查询日志状态
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 永久生效（my.cnf配置）
-- [mysqld]
-- slow_query_log = 1
-- long_query_time = 1
-- slow_query_log_file = /var/log/mysql/slow.log

也可以使用performance_schema实时监控当前执行的SQL：

SELECT digest_text, count_star, avg_timer_wait/1000000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;

EXPLAIN执行计划关键字段解读

定位到慢SQL后，使用EXPLAIN分析执行计划。以下面查询为例：

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

EXPLAIN输出的关键字段：

+----+--------+-----------+-------+---------+------+----------+-----------------------+
| id | type   | table     | key   | key_len | rows | filtered | Extra                 |
+----+--------+-----------+-------+---------+------+----------+-----------------------+
| 1  | SIMPLE | o         | NULL  | NULL    | 500K | 10.0     | Using filesort         |
| 1  | SIMPLE | u         | PRIMARY| 8      | 1    | 100.0    | NULL                   |
+----+--------+-----------+-------+---------+------+----------+-----------------------+

各字段含义：
– type：访问类型，从优到差依次为system > const > eq_ref > ref > range > index > ALL。出现ALL表示全表扫描，必须优化。
– key：实际使用的索引。NULL表示未使用索引。
– rows：预估扫描行数，越少越好。
– filtered：过滤后剩余记录百分比。
– Extra：附加信息。Using filesort（文件排序）和Using temporary（临时表）是需要重点优化的信号。

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

上面的EXPLAIN结果显示orders表type为ALL（全表扫描），且出现Using filesort。需要创建合适的复合索引。

复合索引遵循最左前缀原则：索引(a, b, c)可以用于a、a+b、a+b+c的查询，但不能用于b或b+c的查询。根据WHERE和ORDER BY条件设计索引：

-- 针对查询创建复合索引
-- WHERE status = 'PAID' AND create_time > '2026-01-01'
-- ORDER BY create_time DESC
CREATE INDEX idx_status_create_time ON orders(status, create_time);

-- 验证索引使用情况
EXPLAIN SELECT o.order_id, o.amount, u.user_name
FROM orders o FORCE INDEX(idx_status_create_time)
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID' AND o.create_time > '2026-01-01'
ORDER BY o.create_time DESC
LIMIT 20;

创建索引后EXPLAIN结果变化：

+----+--------+-----------+----------------------+---------+------+----------+-------+
| id | type   | table     | key                  | key_len | rows | filtered | Extra |
+----+--------+-----------+----------------------+---------+------+----------+-------+
| 1  | SIMPLE | o         | idx_status_create_time| 14      | 200  | 33.3     | NULL  |
| 1  | SIMPLE | u         | PRIMARY              | 8       | 1    | 100.0    | NULL  |
+----+--------+-----------+----------------------+---------+------+----------+-------+

type从ALL变为ref，rows从500K降到200，Using filesort消失。索引覆盖了WHERE和ORDER BY，利用索引本身的有序性避免了排序操作。

索引失效场景排查

SQL查询优化中常见导致索引失效的场景：

-- 1. 函数操作导致索引失效
-- 错误：对索引列使用函数
SELECT * FROM orders WHERE DATE(create_time) = '2026-08-03';
-- 优化：改为范围查询
SELECT * FROM orders WHERE create_time >= '2026-08-03' AND create_time < '2026-08-04';

-- 2. 隐式类型转换
-- 错误：status字段是VARCHAR(20)但传入数字
SELECT * FROM orders WHERE status = 1;
-- 优化：传入字符串
SELECT * FROM orders WHERE status = '1';

-- 3. LIKE前导通配符
-- 错误：以%开头无法使用索引
SELECT * FROM orders WHERE order_no LIKE '%20260803%';
-- 优化：如果需要后缀匹配，考虑全文索引或将搜索逻辑移到搜索引擎

-- 4. OR条件中部分列无索引
-- 错误：status有索引但remark无索引，OR导致全表扫描
SELECT * FROM orders WHERE status = 'PAID' OR remark LIKE '%urgent%';
-- 优化：拆分为UNION查询
SELECT * FROM orders WHERE status = 'PAID'
UNION
SELECT * FROM orders WHERE remark LIKE '%urgent%';

分库分表场景下的慢查询处理

当单表数据量超过千万级，即使索引优化到位，部分复杂查询仍可能变慢。分库分表方案实施前，先尝试以下手段：

-- 1. 查看表碎片率，必要时优化表
SELECT table_name, data_free / (data_length + index_length) AS frag_ratio
FROM information_schema.tables
WHERE table_schema = 'your_db' AND data_free > 0;

-- 碎片率超过30%时执行
OPTIMIZE TABLE orders;

-- 2. 冷热数据分离
-- 将历史订单迁移到归档表
CREATE TABLE orders_archive LIKE orders;
INSERT INTO orders_archive
SELECT * FROM orders WHERE create_time < '2025-01-01';
DELETE FROM orders WHERE create_time < '2025-01-01';

-- 3. 使用覆盖索引避免回表
SELECT order_id, status, create_time FROM orders
WHERE status = 'PAID' AND create_time > '2026-01-01';
-- 如果索引包含所有查询列，直接从索引获取数据，无需回表

数据备份恢复与索引变更安全

在生产环境创建或修改索引前，确认数据备份恢复机制正常工作。对于大表，使用pt-online-schema-change在线变更索引，避免长时间锁表：

# 在线添加索引（不阻塞读写）
pt-online-schema-change \
  --alter "ADD INDEX idx_status_create_time (status, create_time)" \
  --execute \
  --chunk-size=1000 \
  D=your_db,t=orders

数据库高可用架构下，优先在从库上创建索引，验证执行计划和性能后再在主库操作。对于数据迁移实战场景，可以先在目标库创建好索引再迁移数据，避免迁移后补建索引的停机风险。Redis缓存策略与MySQL的配合也很关键，热点查询应通过缓存层拦截，减少数据库压力。