数据库索引越多写入越慢,先砍掉重复索引

索引越多写入越慢,先把重复的砍掉。这话听着糙,可生产库里我见太多次了。一张订单表挂七八个索引,其中两三个的列组合几乎一模一样。平时读的时候不觉得,一到月底批量对账就露馅——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,前后差多少心里大概就有数了,数据不需要多精确,方向对了就够。

真要动手,我的建议是每次只删一两个,观察一两天再动下一个,出问题好回滚。删之前把原来的建表语句和索引定义备份一份,写进变更记录里,谁哪天删的、为什么删,都留个痕。索引这东西跟家里堆的杂物差不多,扔的时候心疼,扔完才发现清爽。

相关阅读

【版权声明】:本站内容若无意侵犯到您的权利,请及时与我们联系将尽快删除相关内容!

赞 (0)
爱家的头像爱家
DNS解析端口号:53端口TCP和UDP都要开
上一篇 2026-11-22 13:09
如何免费获取Windows 10操作系统?
下一篇 2024-08-23 00:23

相关推荐

  • mysql循环语句报错怎么办?解决方法与常见错误解析

    MySQL作为一种广泛使用的关系型数据库管理系统,其循环语句(如WHILE、LOOP、REPEAT等)在存储过程和函数中常用于处理重复性任务,在实际开发中,开发者可能会遇到各种与循环语句相关的报错,这些报错不仅影响代码的执行,还可能导致逻辑错误或性能问题,本文将详细分析MySQL循环语句报错的常见原因、解决方法……

    2025-09-30
    0017
  • 顶级域名查询_查询域名

    顶级域名(TLD)是指互联网域名系统中最高级别的域。.com、.org、.net等都是顶级域名。要查询一个域名是否可用,可以使用域名注册商的查询工具。

    2024-07-20
    0012
  • 国外主机备案_.xx可以用于备案吗

    国外主机备案通常指的是将网站信息提交给相关国家或地区的互联网监管机构进行登记,以便合法运营。xx域名是否可以用于备案,这取决于具体的国家和地区的法律法规。在某些国家,.xx域名可能不被允许用于备案。建议您查阅当地的互联网监管政策以获取准确信息。

    2024-07-05
    0020
  • 无规则32k服务器究竟指的是什么?

    无规则32k服务器是一种网络服务器,它允许用户在没有特定游戏规则限制的情况下进行在线游戏。这种服务器通常用于支持32,000个并发连接,适用于大型多人在线游戏或社区。

    2024-07-25
    0027

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

广告合作

QQ:14239236

在线咨询: QQ交谈

邮件:asy@cxas.com

工作时间:周一至周五,9:30-18:30,节假日休息

关注微信