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