MySQL选错索引的原因及解决方案

问题

为什么MySQL会选错索引，如何解决？

答案

核心概念

MySQL优化器基于成本模型选择索引，但成本计算依赖于统计信息的准确性和成本模型的假设。当统计信息不准确或场景特殊时，优化器可能做出错误的选择。

MySQL选错索引的常见原因

1. 统计信息过期或不准确

原因：MySQL通过采样统计表和索引信息，采样数据可能与实际数据分布不一致。

场景示例：

-- 表经过大量INSERT/DELETE后,统计信息未更新
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_create_time ON orders(create_time);

-- 假设status='completed'的记录占90%
-- 但统计信息显示只占10%
SELECT * FROM orders WHERE status = 'completed';
-- 优化器可能错误地选择idx_status,导致大量回表

验证方法：

-- 查看索引统计信息
SHOW INDEX FROM orders;
-- 关注Cardinality列（索引基数）

-- 查看表统计信息
SHOW TABLE STATUS LIKE 'orders';

2. 回表成本估算偏差

原因：优化器假设回表的数据页是随机分布的，但实际可能是连续的（或相反）。

场景示例：

-- 实际数据按create_time连续存储
SELECT * FROM orders
WHERE create_time >= '2024-01-01'
  AND create_time < '2024-01-02';

-- 优化器可能高估回表成本,错误地选择全表扫描

原因分析：

如果数据按主键顺序存储，但查询使用二级索引
回表时实际是顺序IO，但优化器按随机IO计算
导致成本估算过高，放弃索引

3. 范围查询边界不明确

原因：范围查询的选择性难以精确估算。

-- 假设age有索引
SELECT * FROM user WHERE age > 20;

-- 如果age分布不均匀:
-- - age在20-25之间有100万行
-- - age在25-100之间只有1万行
-- 优化器的统计信息可能无法准确反映这种倾斜

4. 多列索引前缀失效

原因：联合索引只能使用最左前缀，后续条件可能被忽略。

CREATE INDEX idx_abc ON orders(status, user_id, create_time);

-- 只用到了status部分,user_id和create_time无法过滤
SELECT * FROM orders WHERE status = 'pending';

-- 如果idx_status单列索引选择性更好,优化器可能选错

5. ORDER BY和WHERE优先级冲突

原因：优化器在”使用索引过滤”和”使用索引排序”之间做出错误选择。

CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_time ON orders(create_time);

SELECT * FROM orders
WHERE status = 'pending'
ORDER BY create_time DESC
LIMIT 10;

-- 可能的选择:
-- 方案A: 使用idx_status过滤,再filesort排序
-- 方案B: 使用idx_time排序,再过滤status (可能选错)

6. JOIN时驱动表选择错误

原因：多表JOIN时，选择大表作为驱动表导致嵌套循环效率低。

-- users表100万行, orders表1000行
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'pending';

-- 如果选择users作为驱动表,需要100万次索引查找
-- 正确应该选择orders作为驱动表

解决方案

方案1：更新统计信息

适用场景：数据变化频繁，统计信息过期

-- 重新收集表统计信息
ANALYZE TABLE orders;

-- 查看更新后的统计信息
SHOW INDEX FROM orders;

自动更新配置：

-- 设置统计信息自动更新阈值
SET GLOBAL innodb_stats_auto_recalc = ON;
SET GLOBAL innodb_stats_sample_pages = 20;  -- 增加采样页数

方案2：使用FORCE INDEX强制索引

适用场景：确定优化器选择错误，强制使用特定索引

-- 强制使用idx_create_time索引
SELECT * FROM orders FORCE INDEX(idx_create_time)
WHERE create_time >= '2024-01-01';

-- 忽略某个索引
SELECT * FROM orders IGNORE INDEX(idx_status)
WHERE status = 'completed';

-- 建议使用某个索引(优化器可能忽略)
SELECT * FROM orders USE INDEX(idx_create_time)
WHERE create_time >= '2024-01-01';

优缺点：

优点：立即生效，强制执行
缺点：硬编码索引名，数据分布变化后可能不再适用

方案3：优化SQL改写

3.1 使用覆盖索引

-- 改写前：需要回表
SELECT * FROM orders WHERE status = 'pending';

-- 改写后：覆盖索引
SELECT id, status, create_time FROM orders WHERE status = 'pending';
-- 或创建覆盖索引
CREATE INDEX idx_status_cover ON orders(status, id, create_time);

3.2 改变查询条件

-- 改写前：范围查询不精确
SELECT * FROM orders WHERE create_time > '2024-01-01';

-- 改写后：缩小范围
SELECT * FROM orders
WHERE create_time >= '2024-01-01'
  AND create_time < '2024-02-01';

3.3 拆分复杂查询

-- 改写前：ORDER BY和WHERE冲突
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY create_time DESC
LIMIT 10;

-- 改写后：先用子查询获取ID,再排序
SELECT * FROM orders
WHERE id IN (
    SELECT id FROM orders WHERE status = 'pending'
)
ORDER BY create_time DESC
LIMIT 10;

方案4：调整索引设计

4.1 创建更合适的联合索引

-- 原索引：单列索引
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_time ON orders(create_time);

-- 优化：创建联合索引
CREATE INDEX idx_status_time ON orders(status, create_time);

-- 同时满足WHERE和ORDER BY
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY create_time DESC;

4.2 删除冗余或低效索引

-- 删除选择性差的索引
DROP INDEX idx_status ON orders;  -- 如果status只有2-3个值

-- 保留选择性好的索引
-- Cardinality / Rows比值越接近1越好

方案5：使用Optimizer Hints（MySQL 8.0+）

语法：

-- 强制使用某个索引
SELECT /*+ INDEX(orders idx_create_time) */ *
FROM orders
WHERE create_time >= '2024-01-01';

-- 强制表连接顺序
SELECT /*+ JOIN_ORDER(o, u) */ *
FROM orders o
JOIN users u ON o.user_id = u.id;

-- 强制使用嵌套循环连接
SELECT /*+ NO_BNL(o, u) */ *
FROM orders o
JOIN users u ON o.user_id = u.id;

方案6：调整优化器参数

调整成本常量（谨慎使用）：

-- 降低随机IO的成本权重(使优化器更倾向于使用索引)
UPDATE mysql.engine_cost
SET cost_value = 0.8
WHERE cost_name = 'io_block_read_cost';

-- 刷新成本
FLUSH OPTIMIZER_COSTS;

调整优化器开关：

-- 禁用某些优化特性
SET optimizer_switch = 'index_merge=off';
SET optimizer_switch = 'mrr=off';

诊断流程

步骤1：使用EXPLAIN分析执行计划

EXPLAIN SELECT * FROM orders WHERE status = 'pending';

关键字段：

type：访问类型（ALL=全表扫描，range=范围扫描，ref=索引查找）
possible_keys：可能使用的索引
key：实际使用的索引
rows：估算扫描行数
Extra：额外信息（Using filesort, Using temporary等）

步骤2：查看成本详情

EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE status = 'pending'\G

输出包含详细的成本计算：

{
  "query_cost": "2500.50",
  "read_cost": "2300.50",
  "eval_cost": "200.00",
  "rows_examined": 1000
}

步骤3：对比不同索引的成本

-- 测试使用idx_status
EXPLAIN FORMAT=JSON
SELECT * FROM orders FORCE INDEX(idx_status)
WHERE status = 'pending';

-- 测试使用全表扫描
EXPLAIN FORMAT=JSON
SELECT * FROM orders IGNORE INDEX(idx_status)
WHERE status = 'pending';

-- 对比query_cost

最佳实践

定期维护统计信息
- 对于变化频繁的表，每天或每周执行ANALYZE TABLE
- 设置自动统计信息更新

监控慢查询日志

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 记录超过1秒的查询

避免过度优化
- 不要盲目使用FORCE INDEX
- 首先尝试ANALYZE TABLE和索引优化
- 最后才考虑强制索引或SQL改写
定期Review执行计划
- 对核心SQL定期检查EXPLAIN结果
- 数据量增长后重新评估索引选择

面试总结

简洁版回答：

MySQL选错索引的主要原因：

统计信息过期：表数据变化后未及时更新
成本估算偏差：回表成本、数据分布估算不准确
范围查询不精确：边界条件难以估算
ORDER BY和WHERE冲突：选择过滤还是排序的两难

解决方案：

更新统计信息：ANALYZE TABLE
强制索引：FORCE INDEX(index_name)
优化SQL：改写查询、使用覆盖索引
调整索引：创建联合索引、删除冗余索引
Optimizer Hints：MySQL 8.0+使用提示符

诊断方法：EXPLAIN / EXPLAIN FORMAT=JSON 查看执行计划和成本。