MySQL 8.0索引优化实战：从执行分析到覆盖索引与ICP的性能提升路径

MySQL性能调优中，索引优化是投入产出比最高的手段。一条慢查询从3秒优化到30毫秒，往往不需要改SQL逻辑，只需调整索引结构或启用优化器特性。本文从EXPLAIN执行计划解读出发，覆盖联合索引最左前缀、覆盖索引、索引条件下推（ICP）和索引排序优化等核心场景，给出一套可落地的MySQL 8.0索引优化方案。

EXPLAIN执行计划的关键列解读

EXPLAIN输出中，以下列决定索引优化方向：

-- 查看执行计划
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001
  AND status = 'PAID'
  AND created_at > '2026-07-01'
ORDER BY created_at DESC
LIMIT 20;

type列效率排序：const > eq_ref > ref > range > index > ALL。出现ALL说明全表扫描，必须优化。ref说明使用了非唯一索引的等值匹配，range表示索引范围扫描。possible_keys列显示可选索引，key列显示实际选用索引。如果possible_keys有多个但key只选了一个次优的，需检查统计信息是否过期（ANALYZE TABLE更新）。rows列是预估扫描行数，实际行数偏差大时执行ANALYZE TABLE。

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

联合索引(col_a, col_b, col_c)可服务于WHERE col_a=?、WHERE col_a=? AND col_b=?、WHERE col_a=? AND col_b=? AND col_c=?三种查询。跳过col_a直接查col_b无法使用索引。

索引列的顺序决定效率。等值过滤列放前面，范围过滤列放后面，排序列放最后。针对上述订单查询，最优索引：

-- 等值列(user_id, status)在前，范围+排序列(created_at)在后
ALTER TABLE orders
ADD INDEX idx_user_status_created(user_id, status, created_at);

这样查询可以：1) 通过user_id定位，2) 在user_id范围内通过status过滤，3) 直接利用索引的created_at顺序返回结果，避免filesort。

索引跳跃扫描（Index Skip Scan，MySQL 8.0+）打破了最左前缀限制，当联合索引第一列的distinct值很少时，优化器可跳过第一列。但不要依赖此特性设计索引，它只是兜底优化。

覆盖索引消除回表的性能提升

二级索引查出主键后还需回表查整行数据（Bookmark Lookup），当查询列全部包含在索引中时，可直接从索引返回结果，避免回表。这就是覆盖索引。

-- 无覆盖索引：需要回表查order_no和amount
SELECT order_no, amount FROM orders
WHERE user_id = 1001 AND status = 'PAID';

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

-- EXPLAIN中Extra列显示"Using index"即表示覆盖索引生效

覆盖索引的代价是索引体积增大，写入性能轻微下降。适用场景：高频查询且只需少量列；大表全表扫描时的统计查询；分页查询的总数统计。

索引条件下推ICP的工作机制

ICP（Index Condition Pushdown）是MySQL 5.6+的优化器特性。没有ICP时，存储引擎根据索引查出行，逐行回表给Server层判断WHERE条件。启用ICP后，部分WHERE条件下推到存储引擎层，在索引中直接过滤，只将满足条件的行回表返回。

-- 示例：联合索引(name, age)
-- 查询WHERE name LIKE '张%' AND age > 25
-- 无ICP：先查name LIKE '张%'的所有行，逐行回表后再判断age > 25
-- 有ICP：在索引中同时判断两个条件，只对满足条件的行回表

-- 确认ICP是否启用
EXPLAIN SELECT * FROM users
WHERE name LIKE '张%' AND age > 25;
-- Extra列显示"Using index condition"表示ICP生效

ICP适用条件：联合索引的最左前缀匹配部分用于索引查找，其余索引列条件下推过滤。无法下推的条件：子查询、存储函数、用户变量等。

ORDER BY与GROUP BY的索引优化

filesort是慢查询的常见元凶。当ORDER BY列与索引顺序一致时，MySQL可直接利用索引的有序性返回结果，避免额外排序。

-- 场景1：ORDER BY与索引方向一致
SELECT * FROM orders
WHERE user_id = 1001
ORDER BY created_at DESC
LIMIT 20;
-- Extra不出现"Using filesort"即为索引排序

-- 场景2：GROUP BY利用索引避免临时表
SELECT status, COUNT(*) FROM orders
WHERE user_id = 1001
GROUP BY status;
-- 索引idx_user_status(user_id, status)可使Extra不出现"Using temporary"

索引维护与监控策略

索引不是建完就不管了。冗余索引浪费空间和写入性能，缺失索引导致慢查询。

-- 查找冗余索引（MySQL 8.0 sys库）
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'your_db';

-- 查找未使用索引
SELECT * FROM sys.schema_unused_indexes
WHERE table_schema = 'your_db';

-- 查看索引区分度
SELECT index_name, cardinality,
       ROUND(cardinality/rows_in_table*100, 2) AS selectivity_pct
FROM sys.schema_index_statistics
WHERE table_schema = 'your_db'
ORDER BY selectivity_pct ASC;

区分度低于5%的索引效果很差，全表扫描可能更快。定期巡检冗余索引并清理，对未使用索引考虑降级为虚拟列索引。监控慢查询日志中rows_examined远大于rows_sent的SQL，这类SQL是索引优化的首要目标。