明明是同一条 SQL，为什么有时候走索引 a，有时候却走索引 b ？

前言

想象你是一家餐厅的服务员，面前有两个菜单：

• 菜单 A：按菜品分类排列（前菜、主菜、甜点）

• 菜单 B：按价格从低到高排列

当顾客说："我要最便宜的川菜"。

你会：

1. 先用菜单 B 找到所有低价菜

2. 从中筛选川菜

或者：

1. 先用菜单 A 找到所有川菜

2. 再按价格排序

这就是 MySQL 优化器的日常决策！

明明是同一条 SQL，有时候走的索引 a，而有时候走的索引 b，就是它的锅。

今天这篇文章跟大家一起聊聊，MySQL 选错索引的问题，希望对你会有所帮助。

1 一个让程序员崩溃的案例

现在有个需求：查询今年开始已付款的前 100 个订单。

给 status 字段创建了索引 idx_status。

给 create_time 字段创建了索引 idx_create_time。

查询订单的 sql 如下：

SELECT * FROM orders WHERE status = 'paid'      -- 状态条件AND create_time > '2025-01-01' -- 时间条件ORDER BY amount DESC LIMIT 100;

周一执行计划如下：

使用索引：idx_status（状态索引）  扫描行数：500行  耗时：0.1秒

周二执行计划如下：

使用索引：idx_create_time（时间索引）  扫描行数：50万行  耗时：8秒

周一只扫描了 500 行数据，而周二却扫描了 50 万行数据。

周一耗时 0.1 秒，而周二耗时却又 8 秒。

同一 SQL 在不同时间性能差异 80 倍！

让我们拆解背后的原因。

2 揭秘优化器的"决策三步曲"

MySQL 优化器的决策流程如下：

成本计算示例：

根据扫描行数、回表次数、排序成本，计算一个总成本的分数。

优化器会选择总成本更低的 idx_create_time 索引。

3 导致索引切换的四大真凶

真凶 1：数据分布变化

场景还原：

• 周一数据：已支付订单 5 万条，其中 2025 年的 5 万条

• 周二数据：已支付订单 50 万条，其中 2025 年的 50 万条

这个例子中数据分布变化很大，周二的数据，比周一的数据一下子多了 45 万。

可能会影响总成本的分数。

我们可以通过下面的 SQL 查看数据分布：

SELECT   COUNT(*) AS total,  SUM(status='paid') AS paid_count,  SUM(create_time>'2023-01-01') AS new_orders FROM orders;

真凶 2：统计信息过期

统计信息过期，就像用去年的地图导航，新修的路不会出现在地图上。

MySQL 的"地图"就是统计信息。

我们可以通过 ANALYZE TABLE ... DELETE STATISTICS 命令删除统计信息：

ANALYZE TABLE orders DELETE STATISTICS;

这时候查询可能变成全表扫描：

EXPLAIN SELECT...

显示 type: ALL

那么，如何解决这个问题呢？

使用 ANALYZE TABLE 命令，刷新统计信息（相当于更新地图）：

ANALYZE TABLE orders;

真凶 3：索引覆盖度差异

点餐类比：

• 菜单 A 能直接看到菜品价格 → 无需问厨师（覆盖索引）

• 菜单 B 只能看到菜品名 → 需要问厨师详情（回表查询）

下面的 SQL 会走 idx_status（需要回表）：

SELECT * FROM orders WHERE status='paid';

下面的 SQL 会走 idx_create_time（覆盖索引）：

SELECT create_time FROM orders WHERE create_time>'2023-01-01';

真凶 4：索引碎片化

索引碎片化就像书本的目录页被撕破，找内容变得困难。

检查方法：

SHOW TABLE STATUS LIKE 'orders';

查看 Data_free 字段，值越大碎片越多。

优化方案：

使用 ALTER TABLE 命令重建索引。

ALTER TABLE orders ENGINE=INNODB;

4 问题排查四步法

第一步：查看当前执行计划

使用 EXPLAIN 查看当前 SQL 的执行计划：

EXPLAIN SELECT * FROM orders WHERE status='paid' AND create_time>'2023-01-01';

第二步：检查统计信息

使用 SHOW INDEX 命令检查索引的统计信息：

SHOW INDEX FROM orders;

关注 Cardinality 字段，值越接近真实数据越好。

第三步：分析数据分布

使用下面的 SQL 分析数据分布:

SELECT   COUNT(*) AS total,  AVG(LENGTH(status)) AS status_avg_len FROM orders;

第四步：追踪优化器思考过程

SET optimizer_trace="enabled=on";SELECT * FROM orders WHERE ...;SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

开启 optimizer_trace，然后通过 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表查看追踪优化器思考过程。

5 三大终极解决方案

方案 1：引导优化器选择

使用 FORCE INDEX 强制使用指定索引：

SELECT * FROM orders FORCE INDEX(idx_status) WHERE ...;

方案 2：创建更优索引

创建更优的联合索引：

ALTER TABLE orders ADD INDEX idx_status_create_time(status,create_time);

方案 3：定期维护计划

1. 定期统计信息更新

2. 定期碎片率检查

3. 定期索引重建

总结

六个必须检查的点

1. WHERE 条件字段是否有合适索引

2. ORDER BY/GROUP BY 是否利用索引排序

3. 统计信息是否最新（尤其大表每天更新）

4. 是否存在索引碎片（每月检查一次）

5. 是否出现索引合并（INDEX_MERGE）

6. 是否使用覆盖索引（减少回表）

三条黄金法则

1. 二八定律：20%的索引满足 80%的查询

2. 数据驱动：定期分析查询模式调整索引

3. 防御编程：核心查询明确指定索引

文章转载自：苏三说技术

原文链接：https://www.cnblogs.com/12lisu/p/18898130

体验地址：http://www.jnpfsoft.com/?from=001YH