明明是同一条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如下： ```sql SELECT * FROM orders WHERE status = 'paid' -- 状态条件 AND create_time > '2025-01-01' -- 时间条件 ORDER BY amount DESC LIMIT 100; ``` **周一执行计划如下**： ```plain 使用索引：idx_status（状态索引） 扫描行数：500行 耗时：0.1秒 ``` **周二执行计划如下**： ```plain 使用索引：idx_create_time（时间索引） 扫描行数：50万行 耗时：8秒 ``` 周一只扫描了500行数据，而周二却扫描了50万行数据。 周一耗时0.1秒，而周二耗时却又8秒。 同一SQL在不同时间性能差异80倍！ 让我们拆解背后的原因。 ## 2 揭秘优化器的"决策三步曲" MySQL优化器的决策流程如下：  **成本计算示例**： | 索引名称 | 预估扫描行数 | 回表次数 | 排序成本 | 总成本 | | --- | --- | --- | --- | --- | | idx\_status | 50万 | 50万次 | 需要排序 | 1050分 | | idx\_create\_time | 5万 | 5万次 | 无需排序 | 600分 | 根据扫描行数、回表次数、排序成本，计算一个总成本的分数。 优化器会选择总成本更低的idx\_create\_time索引。 ## 3 导致索引切换的四大真凶 ### 真凶1：数据分布变化 **场景还原**： - 周一数据：已支付订单5万条，其中2025年的5万条 - 周二数据：已支付订单50万条，其中2025年的50万条 这个例子中数据分布变化很大，周二的数据，比周一的数据一下子多了45万。 可能会影响总成本的分数。 我们可以通过下面的SQL查看数据分布： ```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命令删除统计信息： ```sql ANALYZE TABLE orders DELETE STATISTICS; ``` 这时候查询可能变成全表扫描： ```sql EXPLAIN SELECT... ``` 显示type: ALL **那么，如何解决这个问题呢？** 使用ANALYZE TABLE命令，刷新统计信息（相当于更新地图）： ```sql ANALYZE TABLE orders; ``` ### 真凶3：索引覆盖度差异 **点餐类比**： - 菜单A能直接看到菜品价格 → 无需问厨师（覆盖索引） - 菜单B只能看到菜品名 → 需要问厨师详情（回表查询） 下面的SQL会走idx\_status（需要回表）： ```sql SELECT * FROM orders WHERE status='paid'; ``` 下面的SQL会走idx\_create\_time（覆盖索引）： ```sql SELECT create_time FROM orders WHERE create_time>'2023-01-01'; ``` ### 真凶4：索引碎片化 索引碎片化就像书本的目录页被撕破，找内容变得困难。 **检查方法**： ```sql SHOW TABLE STATUS LIKE 'orders'; ``` 查看Data\_free字段，值越大碎片越多。 **优化方案**： 使用ALTER TABLE命令重建索引。 ```sql ALTER TABLE orders ENGINE=INNODB; ``` ## 4 问题排查四步法 ### 第一步：查看当前执行计划 使用EXPLAIN查看当前SQL的执行计划： ```sql EXPLAIN SELECT * FROM orders WHERE status='paid' AND create_time>'2023-01-01'; ``` ### 第二步：检查统计信息 使用SHOW INDEX命令检查索引的统计信息： ```sql SHOW INDEX FROM orders; ``` 关注Cardinality字段，值越接近真实数据越好。 ### 第三步：分析数据分布 使用下面的SQL分析数据分布: ```sql SELECT COUNT(*) AS total, AVG(LENGTH(status)) AS status_avg_len FROM orders; ``` ### 第四步：追踪优化器思考过程 ```sql 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强制使用指定索引： ```sql SELECT * FROM orders FORCE INDEX(idx_status) WHERE ...; ``` ### 方案2：创建更优索引 创建更优的联合索引： ```sql 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. **防御编程**：核心查询明确指定索引