MySQL慢查询诊断与索引优化实战：从EXPLAIN到性能提升

MySQL慢查询是数据库性能问题的头号杀手。一条全表扫描的SQL在百万级数据表上执行时间可能超过30秒，拖垮整个数据库实例。通过慢查询日志定位问题SQL，使用EXPLAIN分析执行计划，针对性创建索引，是MySQL性能调优的核心流程。本文以电商订单系统为例，记录从慢查询发现到索引优化的完整实战过程。

开启慢查询日志与阈值配置

修改MySQL配置文件/etc/my.cnf，开启慢查询日志：

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

# 慢查询阈值：执行时间超过1秒的SQL记录
long_query_time = 1

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

# 慢查询日志文件大小限制
log_slow_verbosely = ON
log_timestamps = SYSTEM

动态修改参数（无需重启）：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

验证配置生效：

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

使用mysqldumpslow分析慢查询日志

慢查询日志文件 grows rapidly，使用mysqldumpslow工具聚合分析：

# 按总耗时排序，显示Top 10慢SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按次数排序（高频慢SQL优先处理）
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

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

输出示例：

Count: 342  Time=12.58s (4301s)  Lock=0.00s (0s)  Rows=1500000.0 (513000000)
  SELECT * FROM orders WHERE status = 'S' AND create_time >= 'S' ORDER BY user_id DESC LIMIT N

该SQL执行342次，平均每次12.58秒，扫描150万行。这是首要优化目标。

EXPLAIN执行计划深度解读

对慢SQL执行EXPLAIN分析：

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

EXPLAIN输出关键字段解读：

+----+-------------+--------+------+---------------+------+---------+------+---------+--------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra                          |
+----+-------------+--------+------+---------------+------+---------+------+---------+--------------------------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 1500000 | Using where; Using filesort    |
+----+-------------+--------+------+---------------+------+---------+------+---------+--------------------------------+

关键问题定位：

• type=ALL：全表扫描，未使用任何索引
• possible_keys=NULL：没有可用索引
• rows=1500000：扫描150万行
• Extra=Using filesort：ORDER BY需要额外排序操作

复合索引设计与创建策略

该查询涉及三个条件字段：status（等值查询）、create_time（范围查询）、user_id（排序）。根据最左前缀原则和索引列顺序规则设计复合索引。

索引设计原则：等值条件在前，范围条件在后，排序列最后。

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

创建索引后重新EXPLAIN：

+----+-------------+--------+-------+------------------------+------------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys          | key                    | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+------------------------+------------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | orders | range | idx_status_time_user   | idx_status_time_user   | 14      | NULL | 8500 | Using index condition |
+----+-------------+--------+-------+------------------------+------------------------+---------+------+------+-----------------------+

优化效果：

• type=range：索引范围扫描，从ALL提升到range
• key=idx_status_time_user：使用了新创建的复合索引
• rows=8500：扫描行数从150万降至8500，减少99.4%
• Extra：filesort消失，索引已覆盖排序

执行时间从12.58秒降至0.03秒。

覆盖索引避免回表查询

如果查询只需要索引包含的列，MySQL直接从索引返回数据，无需回表读取数据行。这就是覆盖索引优化：

-- 原始查询（需要回表）
SELECT user_id, status, create_time FROM orders 
WHERE status = 'PAID' AND create_time >= '2026-07-01';

-- 创建覆盖索引（包含所有查询列）
ALTER TABLE orders ADD INDEX idx_cover (status, create_time, user_id);

-- EXPLAIN验证
EXPLAIN SELECT user_id, status, create_time FROM orders 
WHERE status = 'PAID' AND create_time >= '2026-07-01';
-- Extra: Using where; Using index
-- "Using index"表示覆盖索引生效，无需回表

分库分表方案下的索引优化

单表数据量超过1000万行后，B+树索引层级增加，查询性能下降。分库分表是数据迁移实战中的常见方案。以订单表按user_id取模分表为例：

-- 原始表结构保持不变，按user_id分16张表
-- orders_0, orders_1, ..., orders_15

-- 分表路由规则：user_id % 16
-- 查询时必须携带分表键
SELECT * FROM orders_5 WHERE user_id = 10005 AND status = 'PAID';

分表后索引策略调整：分表键必须在每个查询条件中，否则需要扫描所有分表。跨分表查询使用ShardingSphere等中间件自动路由。

SQL查询优化常见模式

避免SELECT *：只查询需要的列，减少网络传输和内存占用，同时为覆盖索引创造条件。

-- 差：SELECT * FROM orders WHERE user_id = 100;
-- 优：SELECT order_id, amount, status FROM orders WHERE user_id = 100;

避免前置模糊查询：LIKE ‘%keyword’无法使用索引。

-- 差：SELECT * FROM products WHERE name LIKE '%手机%';
-- 优：SELECT * FROM products WHERE name LIKE '手机%';
-- 或使用全文索引
ALTER TABLE products ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('手机');

避免隐式类型转换：字段类型与查询值类型不一致导致索引失效。

-- 字段order_no为VARCHAR类型
-- 差：SELECT * FROM orders WHERE order_no = 123456;  -- 隐式转换，索引失效
-- 优：SELECT * FROM orders WHERE order_no = '123456';

OR条件优化：OR两侧字段都有索引时，MySQL使用index_merge。如果一侧无索引则全表扫描。

-- 差：SELECT * FROM orders WHERE user_id = 100 OR amount > 5000;
-- 优：使用UNION ALL
SELECT * FROM orders WHERE user_id = 100
UNION ALL
SELECT * FROM orders WHERE amount > 5000 AND user_id != 100;

索引维护与监控告警

定期检查索引使用情况，清理冗余索引：

-- 查看索引使用统计（MySQL 8.0+）
SELECT 
    object_schema,
    object_name,
    index_name,
    count_read,
    count_fetch,
    count_insert,
    count_update
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_db'
ORDER BY count_read DESC;

未使用的索引浪费写入性能和存储空间，识别后删除：

-- 删除未使用索引
ALTER TABLE orders DROP INDEX idx_unused;

使用pt-duplicate-key-checker检查重复索引：

pt-duplicate-key-checker --host=localhost --user=root --password=xxx

在数据库高可用架构中，索引变更需要在主库执行并自动同步到从库。大表加索引使用Online DDL避免锁表：

-- MySQL 8.0默认INPLACE，支持并发DML
ALTER TABLE orders ADD INDEX idx_new (column), ALGORITHM=INPLACE, LOCK=NONE;

配置Prometheus MySQL Exporter采集慢查询指标，设置告警：慢查询数量每分钟超过10条、全表扫描SQL出现、索引使用率低于30%时触发告警通知。配合慢查询审计日志定期Review，建立SQL优化SOP流程。