索引越多写入越慢,先把重复的砍掉。这话听着糙,可生产库里我见太多次了。一张订单表挂七八个索引,其中两三个的列组合几乎一模一样。平时读的时候不觉得,一到月底批量对账就露馅——insert 和 update 全堵在索引维护上,磁盘写队列被压低,业务那边开始催。索引不是白送的福利。说白了,每次写操作都得付一笔手续费,只是账单不在建索引那天来。

索引不是白送的,写的时候都在结账
二级索引在 InnoDB 里照样是一棵 B+ 树,叶子节点放主键值。你往主表插一行,聚簇索引要动,每个二级索引也得跟着动。这些页在磁盘上大概率不挨着,写下去就是随机 IO。change buffer 能兜住一部分非唯一二级索引的写,先缓存着,等页被读了再合并进去。唯一索引就不行——它得立刻去页里确认有没有重复。这道检查躲不掉。索引一多,一次批量导入就变成几百次零散的页修改,写入放大全花在那些你半年没查过的索引上。写入是要还债的。
数据库索引越多写入越慢,先砍掉重复索引这句话我认一半
认的那一半很实在。重复索引基本分三种形态:完全重复的,同一个列上一会儿叫 idx_a、一会儿叫 idx_a_2,命名不一样其实干的是同一件事;前缀重复的,(a) 和 (a,b) 同时存在,前者完全能被后者顶替掉,留着只是多一份维护成本;还有一种是隐式重复,主键本身已经是唯一索引了,再给同一列加个 unique 就是白挂,这种最容易被漏掉,因为它在建表语句里看起来特别合理。查的时候我一般先敲 SHOW INDEX FROM 表名;,看 Key_name、Seq_in_index、Non_unique 三个字段,把同一张表的索引按列顺序在纸上排一遍,前缀包含关系肉眼就能撞出来。
不认的那一半也得说清楚。索引数量不是唯一变量,写入慢还可能是大事务拖着、redo log 刷盘策略太激进、自增主键被打散、甚至是 binlog 格式不合适。别一听索引多就无脑删,先看证据呗。
还有一种假冗余,名字像重复其实各管一段
MySQL 8 支持降序索引,所以 (a ASC) 和 (a DESC) 这两种写法在 8 上不能互相顶替,你要是拿 5.7 的经验去删,很可能把 ORDER BY 的支撑索引给砍了。前缀索引也是,name(10) 和 name(20) 看着像一家人,实际能覆盖的查询长度不一样。字符集和排序规则不同的两列上建的索引,也别混为一谈。这类“假冗余”我踩过坑,删完当天晚上列表页的翻页查询就慢了一截,只能再加回去。
唯一索引别乱删,那不是性能问题
唯一约束承担的是数据正确性,脏数据进来了再修,成本比写入慢高出好几个量级。写入压力大可以换别的路子:小批量提交、把非核心的唯一校验挪到异步去重、或者按业务拆表。性能问题能用加机器缓解,数据错乱只能靠人肉擦屁股。唯一约束不能拿性能换。
找从没被用过的索引,看这两个地方
结构看完再看使用情况。performance_schema.table_io_waits_summary_by_index_usage 里有 count_star,值长期为 0 的,基本就是从建库那天起没人碰过。这个统计在实例重启、或者统计表被 truncate 之后会归零,别拿刚重启的库直接下结论。表特别多的时候我直接查 information_schema:SELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) cols, non_unique FROM information_schema.statistics WHERE table_schema=’库名’ GROUP BY table_name, index_name, non_unique ORDER BY table_name, cols; 结果拉出来按 cols 排一下序,重复的前缀一眼就能看出来,比一个个点客户端快得多。
拿不准的时候开观察期就好。一周、一个月都行,反正索引删了还能加回来,加回去的成本远低于长期背着一堆废索引跑批量任务。有些云数据库控制台会给出索引推荐和冗余提示,具体口径以服务商控制台和官方文档说明为准,别把推荐当成命令直接执行。
删索引也是 DDL,别挑白天动手
大表上 DROP INDEX 不是瞬间完成的事,MySQL 8 大多能走 online DDL,但依旧要拿 metadata lock,碰上没提交的长事务就排队,后面所有访问这张表的语句跟着一起等。我有次下午手欠删了个索引,一个忘了提交的查询把 MDL 卡住,整张表的读写停了好一阵,报警跟着一起响。
流程我一般这么走:先在从库或者预发环境跑一遍 ALTER TABLE 表名 DROP INDEX 索引名, ALGORITHM=INPLACE, LOCK=NONE;,计时看看要多久,再决定放到哪个窗口。主库执行前顺手 SHOW PROCESSLIST 扫一眼有没有长事务,有就先处理掉,别硬上。主从环境还得留意删完之后 DDL 要同步过去,延迟本来就高的库,最好等追平再说。从库上跑完记得比对行数和索引定义,确认结构真的一致。
数量减下来了,列顺序还得再看一眼
冗余砍掉不代表就最优了。联合索引的列顺序决定它能被哪些查询用上,等值条件放前面、范围条件放后面这个大方向没错,但真正管用的是看 EXPLAIN 里的 key_len 和 rows,别凭感觉排。覆盖索引也值得留心——查询要的列刚好都在索引里,就不用回表,这种索引哪怕 count_star 不高也建议留着,它省下的是随机 IO,而这恰恰是写入高峰最紧张的资源。
清理完回头看写入,别删完就完事
隔一两天再对比几个指标更有说服力:同样的批量任务跑多久、磁盘写入量、binlog 增长的速度、还有 innodb_row_lock_time 这类锁等待。有压测环境就在 sysbench 上跑 oltp_write_only,前后差多少心里大概就有数了,数据不需要多精确,方向对了就够。
真要动手,我的建议是每次只删一两个,观察一两天再动下一个,出问题好回滚。删之前把原来的建表语句和索引定义备份一份,写进变更记录里,谁哪天删的、为什么删,都留个痕。索引这东西跟家里堆的杂物差不多,扔的时候心疼,扔完才发现清爽。
相关阅读
【版权声明】:本站内容若无意侵犯到您的权利,请及时与我们联系将尽快删除相关内容!