聊到 MySQL 索引大部分人的第一反应是查询提速神器。但在我做数据库运维和性能优化的这几年里索引从来不是什么免费的午餐它更像是贷款买房——前期帮你快速住进新房但每个月都得还月供还不上或者还错了照样能把日子过乱。最近正好在帮一个业务团队做线上数据库的索引体检发现几张核心表的索引数量已经膨胀到了七八个写入延迟从原来的 3 毫秒涨到了 25 毫秒而其中好几个索引实际上压根没被查询用过。这个场景太典型了今天就把 MySQL 索引的缺点和代价一次性说透包括索引为什么会让写入变慢、什么情况下索引会失效甚至帮倒忙、索引设计失误埋下的坑以及我平时做索引取舍时的一些判断方法。如果你正在用 MySQL或者正准备给业务表加索引这篇文章值得读完。它可能不会告诉你索引有多好用但能帮你避开那些把数据库搞到半夜报警的愚蠢操作。1. 索引的本质是一份额外的账本先搞清楚代价从哪来很多开发者在理解索引时脑子里只有一个模糊概念索引能让查询变快。至于为什么快快的同时付出了什么基本没细想。这里我换个生活化的说法一张没有索引的表就像一堆散落在地上的快递你要找一个包裹只能一个个翻。而索引就是一本按序排列的登记册告诉你第几排第几格放着谁的包裹。听起来很美好对吧但这本登记册本身是有成本的而且要一直维护。我们要聊缺点首先得把这本账算清楚。1.1 存储空间是明面上的第一笔开销MySQL 的 InnoDB 存储引擎里数据本身存在聚簇索引的 B 树中而每一个二级索引都是一棵独立的 B 树。也就是说你每建一个索引MySQL 就要额外创建一棵树出来。这棵树里存了什么东西索引列的值加上对应的主键值。注意不是存完整行数据而是索引列和主键。比如一张一千万行的用户表一个status字段的索引可能就要额外占用几百 MB 甚至上 GB 的空间。如果这张表再建上四五个索引索引占用的总空间比数据本身还大这种情况我见得太多了。提示InnoDB 表的数据和索引一旦占用空间变大备份恢复时间、缓冲池命中率都会受到连锁影响。很多团队只盯着查询耗时很少去看表空间大小结果磁盘报警时才发现某个索引占了几个 G。1.2 写入链路变重的三个关键环节空间开销只是最直观的一层真正让数据库变慢的是写入链路的放大效应。插入一行数据时InnoDB 要做的事远不止往聚簇索引里插一条记录写入聚簇索引主键对应的 B 树写入每一个二级索引如果有多个就得写多棵树如果二级索引的页满了需要触发页分裂更新操作更复杂。如果更新的是普通列那相对好办只改聚簇索引里的那条记录就行二级索引不用动。但如果你更新了一个索引列那情况完全不同——InnoDB 需要删除旧索引记录再插入新索引记录标记为删除的那部分还得等后台 purge 线程清理。至于更新主键那就更劝退了所有二级索引里的指针全部要跟着改等于是把这张表的所有索引都重写一遍。删除操作也没好到哪里去。InnoDB 的删除是标记删除数据不是立刻物理消失而是先在 undo log 里记一笔记录对应的二级索引位置也要标记删除。大量删除之后历史版本堆积purge 线程忙不过来undo log 膨胀查询的可见性判断也会变慢。所以你看每增加一个索引表面上只是多了一条ALTER TABLE ADD INDEX的语句实际上每一次 INSERT、UPDATE、DELETE 都在给这个新索引付费。1.3 页分裂与随机 IO 的连锁反应二级索引的写入还牵扯到磁盘 IO 的随机性问题。自增主键的写入是顺序的新数据永远往 B 树最右边追加写起来很顺。但如果你在一个非顺序的列上建索引比如 UUID、随机字符串新插入的数据可能在 B 树的任意位置页满了就要分裂把一部分数据移到新页里。页分裂是什么概念想象一个书架每层都摆满了书新书来了你得把一层书挪到另一层去。这个挪的过程在数据库里就是额外的 IO 和锁开销而且分裂后留下来的页可能会填充不满产生碎片。碎片多了即使查询走索引扫描的页数量也会增加本来很快的查询会慢慢变钝。MySQL 的 change buffer 机制可以在一定程度上缓冲二级索引的写入但它对唯一索引不生效而且如果二级索引命中率不高后台合并刷盘时反而会造成更高的 IO 压力。所以别指望 change buffer 能完全抹平索引写入成本。2. 查询变快写入变慢索引对 DML 语句的真实拖累讲清楚代价来源之后我们来看实际场景。很多时候业务上线初期数据量小几十万行索引再多也不会慢。但数据量涨到千万级、亿级写入吞吐量开始暴跌这个时候你才会意识到索引的拖累有多明显。2.1 INSERT 的乘法效应插入性能与索引数量基本是近似乘法关系。我做过一个简单的压测对比一张 500 万行的表不加二级索引时批量插入一秒钟能跑 8000 条加上两个索引后直接掉到 2500 条加到四个索引只剩 800 条左右。这个比例不是精确值受字段宽度、缓冲区命中情况影响但趋势非常明显——每加一个索引插入吞吐都会掉一大截。原因不难理解每插入一行所有二级索引的 B 树都要定位插入位置而定位本身涉及树的遍历和页的读写。如果多个索引列的值都是无规律的那每次插入都可能触发多次随机 IO这个开销远大于往聚簇索引尾部追加数据。所以我在处理定时任务、数据迁移、ETL 这类批量导入场景时有个固定操作习惯先ALTER TABLE ... DROP INDEX把不必要的索引删掉跑完数据再重新建。实测下来一个大表的数据导入时间能从四十多分钟缩短到十分钟左右差距就是这么大。2.2 UPDATE 的隐藏代价UPDATE 的代价要分情况看很多开发者在评估索引对 UPDATE 的影响时容易想当然觉得所有更新都慢。其实这里有个关键区别更新非索引列只需要在聚簇索引中找到那条记录改掉字段值即可二级索引完全不受影响。更新索引列等于先删除旧索引条目再插入新索引条目涉及两棵 B 树的操作代价翻倍。更新主键所有二级索引的引用都要变更这个操作通常不应该出现在线上但如果真有人这么写后果就是卡到怀疑人生。另外还有个容易忽略的细节UPDATE 的 WHERE 条件如果走了索引那查找记录本身是快的这个没问题。但如果你更新的是索引列并且 WHERE 条件本身还能命中索引那 InnoDB 需要先通过索引找到旧记录再把旧索引记录标记删除同时插入新记录到新位置。InnoDB 为了保持索引有序新位置往往和旧位置不在一起又引入随机 IO。2.3 DELETE 的标记删除与堆积问题DELETE 在 InnoDB 里并不是立刻物理删除而是先在记录上打删除标记真正的清理交给后台 purge 线程。这个机制本身是为了支持多版本并发控制但它带来的缺点也很明显。当你的二级索引很多、删除量又大时purge 线程需要处理所有二级索引上的历史记录标记CPU 和 IO 都会受到影响。更麻烦的是删除操作会产生大量 undo log如果 purge 跟不上undo log 会膨胀甚至出现history list length居高不下的情况直接拖慢所有查询的 ReadView 判断。在实际运维中我遇到过一张大表定期清理过期数据因为索引过多每次 DELETE 几万行都能把主库延迟打满从库复制直接告警。后来把负责排序的一个超大索引去掉同样批量的 DELETE 时间缩短了一半以上。2.4 日志表和流水表为什么要克制索引基于上面这几点我给自己定了个经验法则写多读少的表索引一定克制。最典型的就是操作日志表、流水表、埋点数据表。这些表的特征是写入量巨大、查询场景单一通常只按时间或某个业务 ID 查最近数据查询频率远比写入频率低。这种表上每多一个索引都是在给核心写入链路加负担。很多时候一个二级索引的收益是一天只跑几次的报表查询代价却是每秒钟几千次的写入都在为它买单。这笔账怎么算都不划算。反过来读多写少的表比如配置表、商品基础信息表索引适当多一些没问题。判断标准不是索引多不多而是读写比例和查询模式。3. 索引并非万能失效场景与帮倒忙的典型情况索引的缺点不止是性能代价还有一个更隐蔽的问题你以为它一定能帮上忙结果在特定条件下它根本不生效甚至让查询变得更慢。这不是说索引没用而是很多人对索引的能力边界缺乏认知。3.1 最左前缀原则联合索引的顺序陷阱联合索引看起来可以覆盖多个列但它遵循最左前缀原则。我经常遇到一类问题业务方建了一个(user_id, order_status, create_time)的联合索引结果查询条件是where order_status 1 and create_time 2024-01-01没带user_id索引直接无法使用。为什么会这样联合索引在 B 树里的排序规则是先按第一个列排序第一列相同再按第二列排序依次类推。如果查询条件里没有第一列那在索引树里就没法确定搜索起点只能全树扫描优化器通常不会选这条路。联合索引的缺点在于它看似灵活实则僵硬。你要它覆盖更多的查询场景就得精确设计列的顺序设计错了这个索引就成了一个占地大、写入贵、但查询用不上的摆设。注意这里的核心结论是联合索引可以覆盖多个列但绝不能认为建了联合索引就万事大吉。顺序和查询条件的匹配关系必须在设计阶段就考虑清楚否则就是花钱买罪受。3.2 隐式类型转换和函数操作让索引失效另一个高频踩坑点是隐式类型转换。比如表中phone字段是 varchar 类型查询却写成where phone 13800138000MySQL 会把字段转换成数字再比较这种情况下字段上即使有索引也无法正常使用。还有更常见的在索引列上套函数SELECT * FROM order_info WHERE DATE(create_time) 2024-06-01;create_time上建了索引也没用因为DATE()函数会先把每一行的create_time取出来运算一遍索引树里存的是原始值没法直接定位。正确写法是改成范围查询SELECT * FROM order_info WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00;为什么这类问题属于索引的缺点因为很多人建了索引之后下意识认为我有索引查询肯定走索引。但实际上索引能不能被用上取决于你的 SQL 写法是否符合索引的匹配规则。一个带函数的条件就能让精心设计的索引直接失效。3.3 低选择性索引优化器宁愿全表扫描还有一种情况更让开发者难受索引建了SQL 也没写错但执行计划出来还是全表扫描。原因出在索引的选择性上。选择性可以简单理解为这个索引列有多少个不同的值。性别列可能只有 0 和 1 两个值状态列可能只有三五个值这些低区分度的列上建索引优化器经过成本估算后会觉得走索引和全表扫描差不多甚至全表扫描更便宜于是放弃索引。之前我接手过一个案例业务方给一个is_deleted字段建了索引这个字段只有 0 和 1 两个取值。查询时优化器预计会扫出一半的行走索引需要大量回表不如直接全表扫描。结果好端端一个索引既占空间又拖慢写入查询却一次都没被用到。这说明一个扎心的事实索引并不是建了就会被用它只是给了优化器一个可选方案。如果这个方案不够好优化器会毫不留情地忽略它。3.4 回表代价被低估二级索引的叶子节点存储的是索引列和主键值不是完整数据行。当查询需要的数据列不在索引中时MySQL 需要根据主键回到聚簇索引里找完整行这个过程就是回表。回表不是免费的每回表一次就是一次主键查找。如果查询命中了大量二级索引记录比如一万行那可能要回表一万次。在数据量小的时候没什么感觉数据量大且内存紧张时这上万次回表产生的随机 IO 足以让查询慢到令人崩溃。一个常见的解决思路是覆盖索引也就是把查询需要的列都放进索引里避免回表。但这里有个两难覆盖索引要求索引包含更多列而索引列越多写入成本越高索引占用空间也越大。于是你又回到了我们前面说的那个矛盾——查询想快就要加索引写入想快就要减索引。真实业务环境里这个平衡永远在动态摇摆。4. 过度索引与设计失误比没有索引更麻烦如果说前面聊的都是索引本身的物理缺点那接下来要说的就是人的问题——过度设计、重复建设、粗心大意。这些问题在线上经常比索引的物理代价更致命。4.1 冗余索引和重复索引数据库里最隐蔽的浪费冗余索引指的是两个索引的前缀完全相同比如建了(user_id, order_status)又单独建了(user_id)。后者其实被前者覆盖完全没必要。重复索引则是同一列在多个索引中反复出现比如某个字段既在联合索引里又有自己的独立索引。我见过最离谱的一张表一共 12 个字段建了 9 个索引。其中user_id同时出现在 6 个索引里order_id出现在 5 个索引里。这不是业务需要而是不同开发者在不同时期各自加了自己的索引没有做全局梳理。这种冗余的直接后果有两点第一写入时要维护的 B 树从 9 棵变成实际有效的三四棵白白增加成本第二查询时优化器需要从更多索引候选中做成本评估评估本身也有开销。虽然这个开销通常很小但在高并发环境下能省则省。MySQL 其实自带了发现工具用起来很方便SELECT * FROM sys.schema_redundant_indexes;这个视图会直接列出冗余索引和重复索引拿这个结果去和开发确认基本一删一个准。4.2 大字段索引与索引列过宽索引列本身越宽代价越高。有些开发者在 text 类型或者超长 varchar 字段上直接建索引这样一棵 B 树的体积会非常夸张每个数据页能存储的索引记录数量变少查询时扫描的页数变多性能反而不如不加索引。针对长文本字段前缀索引是个折中方案ALTER TABLE article ADD INDEX idx_title(title(20));只对title字段的前 20 个字符建立索引体积大幅缩小。但前缀索引也有局限无法用于覆盖索引因为索引里没有完整值排序时可能不准确因为只取了一部分字符。所以这始终是个退而求其次的办法能不用就别用实在没办法再考虑。还有一个容易被忽略的细节单列索引的列过长会导致单页能放下的记录变少B 树层数可能增加。从三层变成四层就意味著每次索引查找多一次磁盘 IO。别小看这一次 IO千万级数据量的表上这可能是几十毫秒的差距。4.3 索引维护引发锁竞争与碎片化索引维护还会带来锁和碎片问题。InnoDB 的 B 树在插入时如果发生页分裂可能需要持有相关的锁高并发写入时锁等待概率会增加。虽然 InnoDB 在很多时候能通过乐观插入避免加锁但分裂操作依然是最容易产生锁竞争的点之一。碎片化问题前面提过一次这里展开说。碎片的主要来源是随机顺序的插入和大量删除。碎片多的索引逻辑上相邻的记录在物理页上不连续范围扫描时读到的页更多性能下降。解决办法是定期做ALTER TABLE ... ENGINEInnoDB或者OPTIMIZE TABLE来重建表但这又是一个运维窗口问题——大表做 OPTIMIZE 时锁表时间长直接影响线上业务。这就是索引维护的隐性成本你不仅要为查询加速付费还要为索引的保养付费。很多小团队的运维节奏根本跟不上等索引碎片累积到一定程度才会在某个深夜被慢查询报警打到魂飞魄散。5. 我的取舍经验什么时候该砍索引怎么判断讲了这么多缺点不是劝大家不建索引。相反一个设计良好的索引能带来的查询收益是不可替代的。问题是你要清楚手上的索引哪些在创造价值哪些只在消耗成本然后果断砍掉后者。5.1 先找出吃了资源不出力的索引我每次做索引体检第一件事是查索引的实际使用情况。MySQL 的 performance_schema 里记录了每个索引的读写次数直接查这个表就能知道哪些索引几乎没被用过SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR AS io_count, COUNT_READ AS read_count, COUNT_WRITE AS write_count FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db ORDER BY COUNT_STAR DESC;COUNT_WRITE很高但COUNT_READ几乎为零的索引基本就是纯粹的负担。正常情况下我会把这个列表拉出来和开发同学逐条确认能删就删。还有一个配合手段是慢查询日志加 EXPLAIN。慢慢把线上的慢 SQL 收集起来逐条看执行计划确认哪些索引在真正服务这些慢查询。两条交叉验证下来索引的去留就很清楚了。5.2 读写比例与查询模式一个简单的判断框架如果没有条件做全量监控可以按一套简单的经验框架来判断一次性业务如临时导入、一次性报表用完就删不要让临时索引长期驻留在表上。高频写入表日志、流水、消息记录每个索引都要有明确且高频的查询场景支撑否则不加。高频查询表商品、用户、配置索引多一些可以接受但必须检查冗余。明显低区分度字段状态、类型、是否删除除非与其他字段组合后区分度明显提高否则不要单独建索引。大字段text、超长 varchar不建全字段索引实在需要就用前缀索引。这套框架不复杂但很实用。遇到拿不准的情况宁可先不加索引等查出慢查询再加也好过加了一堆无用的索引然后在某一天被写入性能反噬。5.3 删索引不是小事时机和工具都得讲究删除索引千万别在业务高峰期直接执行。DROP INDEX在 InnoDB 里会触发表级元数据锁MDL虽然不同的 DDL 策略表现不同但高并发时刻执行 DDL 永远是在刀尖上跳舞。我见过不止一次因为半夜赶工在线上直接删索引结果把整个业务的写入全部堵死的情况。正确做法是把 DDL 放在低峰期执行提前在测试环境验证表结构和数据访问不受影响。如果表太大或者目标表是核心大表建议使用在线变更工具比如pt-online-schema-change或 GitHub 的gh-ost。这类工具通过临时表、触发器或 binlog 同步的方式在不停服的情况下完成索引变更和重建风险会小很多。删完之后也别急着收工。观察至少一到两个完整的业务周期确认慢查询没有突增写入延迟确实下降再算彻底完成。如果发现删错了重建索引也不难但每一次加删索引都是一次折腾所以动手之前宁可多确认几次。我个人的习惯是每次调整索引之前把当前表的 DDL、索引使用统计、典型慢查询和执行计划四个东西截图存档。这样即使调挂了也有据可查、能快速回滚。数据库这个东西稳永远比炫重要。索引就像生活中的收纳柜合理的分类能让你几秒钟找到东西但如果你什么杂物都往里塞柜子本身就成了新的灾难源头。对 MySQL 索引的态度也一样把它当成一种成本来管理而不是一种奖励来堆砌。每一棵 B 树都要有它存在的理由没有理由的索引早删早轻松。