MySQL索引优化实战：EXPLAIN执行计划深度解读与调优案例

MySQL性能调优的核心在于索引优化。一条SQL查询从扫描百万行缩减到扫描几十行，性能差距可以达到上千倍。EXPLAIN是分析SQL执行计划的标准工具，本文通过实际调优案例拆解EXPLAIN输出字段，并给出索引创建和优化的具体操作步骤。

EXPLAIN执行计划字段详解

在SQL前加上EXPLAIN关键字，MySQL返回执行计划而非实际执行结果。以下是一条慢查询的EXPLAIN输出：

EXPLAIN SELECT o.id, o.amount, u.username, p.product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.create_time >= '2026-07-01'
  AND o.status = 'paid'
  AND u.region = '华东'
ORDER BY o.create_time DESC
LIMIT 20;

+----+--------+-------------+-------+------+------------------------+-------------------+---------+----------------+--------+----------------------------------------------+
| id | select | table       | type  | key  | key_len                | ref               | rows    | filtered | Extra                                        |
+----+--------+-------------+-------+------+------------------------+-------------------+---------+----------+----------------------------------------------+
| 1  | SIMPLE | o           | range | idx_create_time        | 5  | NULL    | 50000  | 33.33    | Using index condition; Using filesort        |
| 1  | SIMPLE | u           | eq_ref| PRIMARY                | 8  | db.o.user_id | 1   | 10.00    | Using where                                  |
| 1  | SIMPLE | p           | eq_ref| PRIMARY                | 8  | db.o.product_id| 1  | 100.00   | NULL                                         |
+----+--------+-------------+-------+------+------------------------+-------------------+---------+----------+----------------------------------------------+

关键字段解读：

type：访问类型，从好到差依次为system > const > eq_ref > ref > range > index > ALL。ALL表示全表扫描，必须优化。range表示索引范围扫描，通常可接受。

key：实际使用的索引名。如果为NULL，说明没有走索引，需要排查原因。

rows：MySQL预估的扫描行数。这个值越接近1越好。rows乘以filtered百分比就是预估结果行数。

Extra：额外信息。Using index表示覆盖索引（不需要回表）；Using filesort表示需要额外排序操作（需要优化）；Using temporary表示使用了临时表（严重需要优化）。

上面的执行计划中，orders表用了idx_create_time做范围扫描，预估扫描5万行，但有Using filesort，说明排序没有走索引。

索引类型选择与创建策略

MySQL 8.0支持的索引类型包括B+Tree索引、Hash索引（Memory引擎）、Full-text索引和空间索引。InnoDB引擎默认使用B+Tree索引。

创建索引需要权衡查询收益和写入开销。每个索引都会增加INSERT/UPDATE/DELETE的耗时，因为索引也需要同步维护。一般建议单表索引数量不超过5个。

-- 创建联合索引（注意字段顺序）
ALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time);

-- 创建前缀索引（长字符串字段优化）
ALTER TABLE users ADD INDEX idx_email_prefix (email(20));

-- 创建函数索引（MySQL 8.0+）
ALTER TABLE orders ADD INDEX idx_create_date ((DATE(create_time)));

-- 创建不可见索引（先测试再决定是否启用）
ALTER TABLE orders ADD INDEX idx_test_column (column_name) INVISIBLE;
-- 验证无影响后设为可见
ALTER TABLE orders ALTER INDEX idx_test_column VISIBLE;

前缀索引的选择性计算方法：SELECT COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) FROM users; 如果选择性接近1，说明前缀长度足够。

联合索引最左前缀匹配原则

联合索引 (status, create_time, user_id) 的B+Tree按status排序，status相同的按create_time排序，以此类推。查询条件必须从最左列开始才能使用索引。

-- 能命中联合索引 idx(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 = 100;

-- 只能部分命中（status走索引，create_time后的条件无法使用索引）
SELECT * FROM orders WHERE status = 'paid' AND user_id = 100;

-- 无法命中索引（跳过了status）
SELECT * FROM orders WHERE create_time >= '2026-07-01';
SELECT * FROM orders WHERE user_id = 100;

索引列顺序的确定原则：把等值查询条件放前面，范围查询条件放后面。把选择性高的列放前面（不同值数量多的列）。把排序字段放最后。

索引失效场景排查与修复

常见索引失效场景及修复方法：

场景1：函数操作导致索引失效

-- 索引失效：对索引列使用函数
SELECT * FROM orders WHERE DATE(create_time) = '2026-07-29';
-- 修复：改为范围查询
SELECT * FROM orders
WHERE create_time >= '2026-07-29 00:00:00'
  AND create_time < '2026-07-30 00:00:00';

场景2：隐式类型转换

-- 索引失效：status是varchar，传入整数
SELECT * FROM orders WHERE status = 1;
-- 修复：传入正确的字符串类型
SELECT * FROM orders WHERE status = '1';

场景3：LIKE前缀通配符

-- 索引失效：以%开头
SELECT * FROM products WHERE product_name LIKE '%手机%';
-- 修复：前缀匹配可以走索引
SELECT * FROM products WHERE product_name LIKE '小米%';
-- 或者使用全文索引
ALTER TABLE products ADD FULLTEXT INDEX ft_product_name (product_name);
SELECT * FROM products WHERE MATCH(product_name) AGAINST('手机');

场景4：OR条件中有非索引列

-- 索引失效：remark列无索引
SELECT * FROM orders WHERE status = 'paid' OR remark LIKE '%加急%';
-- 修复：给remark加索引，或改用UNION
SELECT * FROM orders WHERE status = 'paid'
UNION
SELECT * FROM orders WHERE remark LIKE '%加急%';

慢查询日志分析与优化案例

开启慢查询日志是发现性能问题的第一步：

-- 查看慢查询配置
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 slow_query_log_file = '/var/log/mysql/slow.log';

-- 永久配置 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

使用pt-query-digest分析慢查询日志，按总耗时排序找出最需要优化的SQL：

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

实际调优案例：一条订单列表查询，数据量200万行，执行时间3.2秒。EXPLAIN显示type为ALL（全表扫描），rows为200万。

-- 原始查询
SELECT * FROM orders
WHERE merchant_id = 500
  AND status IN ('paid', 'shipping')
  AND create_time >= '2026-06-01'
ORDER BY create_time DESC
LIMIT 20;

-- 优化：创建联合索引
ALTER TABLE orders ADD INDEX idx_merchant_status_create
  (merchant_id, status, create_time);

-- 优化后EXPLAIN：type=ref, rows=150, Extra=Using index condition

执行时间从3.2秒降至2毫秒。核心改动就是让查询条件完全匹配联合索引的最左前缀，排序字段也在索引中，消除了filesort。

覆盖索引与回表优化

当查询的所有字段都在索引中时，MySQL直接从索引返回结果，不需要回表读取数据行，这叫覆盖索引。Explain的Extra列显示Using index就是覆盖索引。

-- 没有覆盖索引：需要回表
SELECT id, amount, status FROM orders WHERE status = 'paid';
-- Extra: Using index condition

-- 创建覆盖索引
ALTER TABLE orders ADD INDEX idx_status_cover (status, amount);
-- 查询走覆盖索引
SELECT id, amount, status FROM orders WHERE status = 'paid';
-- Extra: Using index

覆盖索引的代价是索引体积增大，写入开销增加。对于高频查询的列才值得做覆盖索引。对于SELECT *的查询无法使用覆盖索引，这也是为什么不建议在生产环境用SELECT *的原因。

数据备份恢复和分库分表方案在索引优化中也有关联。分库分表后，跨库查询无法使用全局索引，需要在设计阶段就确定好分片键，让查询尽量落在单库单表内。数据库高可用架构中，主从延迟可能导致从库索引未同步，这时强制读主库可以避免因索引缺失导致的性能问题。