选错索引会发生什么

选错索引会发生什么

• 定义：
• 选错索引是指 MySQL 优化器在执行查询时未选择最优索引，导致查询性能下降。
• 后果：
• 查询变慢：扫描行数增加，效率低。
• 资源占用高：CPU、IO 负载加重。
• 全表扫描：未用索引或用错索引。
• 排序失败：无法利用索引排序。
• 高并发阻塞：慢查询影响其他操作。

核心点

• 选错索引降低性能，影响系统稳定性。

---

1. 选错索引的后果详解

(1) 查询变慢

• 原因：
• 优化器选择低效索引（如普通索引而非复合索引），扫描更多行。
• 示例：

CREATE INDEX idx_age ON user(age);
CREATE INDEX idx_age_name ON user(age, name);
SELECT * FROM user WHERE age > 20 AND name = 'Alice';

• 用 idx_age 而非 idx_age_name，扫描大量行。
• 表现：
• EXPLAIN 中 rows 值大。

(2) 资源占用高

• 原因：
• 扫描行多，增加 CPU 计算和磁盘 IO。
• 示例：
• 全表 100 万行，用错索引扫描 50 万行。
• 表现：
• CPU 使用率飙升，IO 等待时间长。

(3) 全表扫描

• 原因：
• 索引不匹配条件，优化器放弃索引。
• 示例：

CREATE INDEX idx_name ON user(name);
SELECT * FROM user WHERE age > 20;

• idx_name 无用，触发 type: ALL。
• 表现：
• EXPLAIN 显示 key: NULL。

(4) 排序失败

• 原因：
• 索引未覆盖排序字段，导致文件排序。
• 示例：

CREATE INDEX idx_age ON user(age);
SELECT * FROM user WHERE age > 20 ORDER BY name;

• 用 idx_age，仍需 Using filesort。
• 表现：
• Extra: Using filesort。

(5) 高并发阻塞

• 原因：
• 慢查询占用锁资源，阻塞其他事务。
• 示例：
• 更新操作等待慢查询释放行锁。
• 表现：
• 并发请求超时，系统响应变慢。

---

2. 选错索引的原因

(1) 索引设计不当

• 未建合适索引或冗余索引。
• 示例：只建 idx_age，漏建 idx_age_name。

(2) 优化器误判

• 统计信息不准：
• 表统计未更新（ANALYZE TABLE）。
• 代价估计错误：
• 优化器认为全表扫描比索引快。
• 示例：小表用索引反而慢。

(3) SQL 写法问题

• 函数破坏索引：

SELECT * FROM user WHERE UPPER(name) = 'ALICE';

• idx_name 失效。
• 条件不匹配：
• WHERE age > 20 OR name = 'Alice' 不走复合索引。

---

3. 如何发现和解决

(1) 发现问题

• EXPLAIN：

EXPLAIN SELECT * FROM user WHERE age > 20 AND name = 'Alice';

• 检查 key 是否预期，rows 是否过多。
• 慢查询日志：
• 记录执行时间长的 SQL。

(2) 解决方法

• 强制索引：

SELECT * FROM user FORCE INDEX(idx_age_name) WHERE age > 20 AND name = 'Alice';

• 更新统计：

ANALYZE TABLE user;

• 优化索引：
• 加复合索引：CREATE INDEX idx_age_name ON user(age, name)。
• 改写 SQL：
• 避免函数：WHERE name = 'Alice'。

---

4. 延伸与面试角度

• 与优化器：
• 优化器基于成本选择，统计不准易出错。
• 实际应用：
• 电商：订单查询用错索引导致超时。
• 预防：
• 定期检查索引覆盖率。
• 面试点：
• 问“后果”时，提查询慢和阻塞。
• 问“解决”时，提 EXPLAIN 和强制索引。

---

总结

选错索引导致查询变慢、资源高、全表扫描等问题，源于设计、优化器或 SQL 问题。通过 EXPLAIN 分析和调整索引解决。面试时，可提示例或优化方案，展示实战能力。