加了个索引，SQL性能竟然慢了十倍！
前言
最近星球中有小伙伴问我：加了索引，SQL查询效率一定会提升吗？
答案是否定的。
让我想起了几年前查询订单的场景。
优化前下面这条SQL的查询耗时是0.5s。
添加了下面的索引：
再次执行上面的查询SQL语句，此时的耗时却是5.2s。
出现了非常神奇的一幕：加了索引，SQL查询性能反而慢了10倍。
作为一个踩过无数数据库性能坑的老司机，今天跟大家一起聊聊那些“加了索引反而更慢”的诡异场景。
1. 索引失效
加了索引之后，你以为它在工作，其实它在摸鱼，因为它可能已经失效了。
1.1 最左前缀原则如果查询条件的顺序不对，努力白费。
复合索引 (a, b, c) 生效的关键在于最左前缀匹配。
用户表创建了名称、年龄和城市这三个字段的复合索引：
✅ 有效：使用最左列 name
✅ 有效：使用最左前缀 (name, age)
❌ 失效：跳过了最左列 name
❌ 失效：未使用最左前缀
原理：复合索引的存储结构类似于电话簿（先按姓排序，再按名排序）。
如果跳过“姓”直接查“名”，索引就失效了。
1.2 函数操作函数操作会让索引瞬间失忆。
对索引列做计算、函数转换或类型转换，会导致索引失效：
❌ 失效：对索引列使用函数：
❌ 失效：隐式类型转换 (phone 是 varchar)
✅ 有效：避免函数操作
可以使用BETWEEN...AND查询时间范围。
原理：索引存储的是列的原始值。对值进行修改后，数据库无法在索引树中定位原始值。
1.3 范围查询范围查询（>、<、BETWEEN）会截断复合索引中后续列的匹配：
索引 (age, salary)
会导致salary的索引失效。
原理：age>25 匹配到的是一系列值（非精确值），数据库无法高效地对 salary 进行索引过滤。
2. 索引维护成本
天下没有免费的午餐，索引是有额外的维护成本的。
2.1 DML 操作变慢每一次写入都可能在负重前行。
每次 INSERT、UPDATE、DELETE 操作，数据库不仅要修改数据，还要维护相关索引。
无索引表插入 100w 行：1.2 秒。
有 5 个索引的表插入 100w 行：15.8 秒 (实测差距 10 倍以上)。
场景：在写多读少的高并发场景下（如流水记录），索引反而会成为性能瓶颈。
2.2 索引占用空间索引可能会给磁盘和内存带来双重压力。
每个索引都是一棵 B+ 树，存储完整的索引列值（或组合值）。大表的索引轻松占据几十GB空间。
MySQL中可以通过下面的SQL查看表索引大小：
后果：索引过大导致内存中缓存命中率降低，物理 I/O 增加。
3. 优化器的“错误”选择
有时候，可能会出现聪明反被聪明误，加了索引可能会导致性能变慢。
3.1 统计信息过时导航用了旧地图。
优化器依赖统计信息（如索引区分度、数据分布）来选择索引。
如果统计信息过期，优化器可能选择性能更差的索引。
MySQL中我们可以通过下面的命令强制更新表统计信息：
场景：当表中数据发生剧烈变化（如大批量删除/导入）后，统计信息未及时更新。
3.2 回表代价高昂索引再好也怕“绕路”。
假如给user表给age创建了索引：
即使 age 索引被使用，数据库仍需根据索引中的主键 ID 回表查询name、email 字段。
当符合条件的数据量很大时，回表 I/O 可能远超索引扫描本身。
优化方案：使用覆盖索引（Covering Index），让索引包含查询所需的所有列。
创建覆盖索引 (包含 age, name, email)：
查询可直接从索引获取数据，无需回表
4. 索引过多
当表上存在多个索引时，优化器需要评估每个索引的成本，选择越多，决策时间越长。
下面的查询可能使用索引 A 或索引 B
优化建议：
删除重复或冗余索引合并可组合的索引使用工具分析索引使用率（如 sys.schema_unused_indexes）5. 锁与并发
锁的竞争是一个看不见的战场。
5.1 行锁升级在事务中通过索引检索并锁定行时，如果锁数量过多（超过阈值），数据库可能将锁升级为表锁，严重降低并发性能。
场景：全表更新或删除大量数据时，索引的存在可能导致锁升级。
5.2 索引分裂的阻塞B+树索引在插入数据时可能发生页分裂。
这个过程需要加锁，在高并发写入场景下可能导致短暂阻塞。
总结
索引是把双刃剑，用对场景是关键。
理解原理：掌握最左前缀、索引失效条件、覆盖索引等核心机制权衡成本：在写密集场景谨慎添加索引，评估维护代价精准设计：按实际查询模式设计复合索引，避免冗余关注统计信息：定期更新统计信息，确保优化器决策准确监控分析：使用 EXPLAIN、慢查询日志等工具持续跟踪索引效果某电商平台曾因在流水表上盲目添加索引，导致高峰时段写入延迟飙升。
后经分析，移除两个非核心索引，写入速度提升8倍，而相关查询仅增加20毫秒——这正是索引取舍的艺术。
记住：索引不是越多越好，而是越准越好。
真正的高手，懂得在索引的利刃上优雅行走。
