MySQL 生产实践-Update 二级索引导致的性能问题排查
简介：在高并发或大数据量场景下，一条看似简单的 UPDATE 语句执行缓慢，可能并非因为 SQL 本身复杂，而是隐藏在背后的索引设计缺陷所致。本文记录一次真实生产环境中的性能排查案例：某 UPDATE 操作涉及 77.6 万行数据，执行耗时高达 91 秒，而删除一个低区分度的二级联合索引后，性能提升至 35 秒，效率提升近 3 倍
背景
研发反馈遇到一个update语句，需要更新很多行。更新的字段上有二级索引，删除索引后要快很多，有索引存在的时候更新就要慢很多。
排查问题
该字段二级索引很简单。只是两个int型的一个联合索引，更新数据是要维护索引，但两者性能差距很大，这个应该不是这么简单更改索引数据引发。我将此问题扔给了DeepSeek,DeepSeek回复了几种可能，其中一种可能是 二级索引页分裂多，造成成本太大，所以引发了性能问题。
问题到此似乎该结束了，但我需要去验证是不是由页分裂产生的。
以下是验证过程
将SQL简化后，update语句如下
UPDATE bill a
SET a.pid=a.pid+1
WHERE a.proid=123 AND a.billdate > '2020-06-30 00:00:00'
更改行数为 776000行。更新时间为91S
我们来观测执行这个SQL的页分裂数
通过INFORMATION_SCHEMA.INNODB_METRICS表的三个参数来观测
index_page_splits 页分裂次数
index_page_reorg_attempts 试图合并次数
index_page_reorg_successful 成功合并次数
需要启用这三个参数
启用命令如下
SET GLOBAL innodb_monitor_enable = 'index_page_splits';
SET GLOBAL innodb_monitor_enable = 'index_page_reorg%';
测试前将次数值清零
SET GLOBAL innodb_monitor_reset = 'index_page_splits';
SET GLOBAL innodb_monitor_reset = 'index_page_reorg%';
这里遇到一个问题。官方文档说这个命令可以清零，但我执行无效
我的MySQL版本为 8.0.13
执行这个命令是可以清零的
SET GLOBAL innodb_monitor_disable = 'index_page_splits';
SET GLOBAL innodb_monitor_disable = 'index_page_reorg%';
SET GLOBAL innodb_monitor_reset_all = 'index_page_splits';
SET GLOBAL innodb_monitor_reset_all = 'index_page_reorg%';
再次打开计数器
SET GLOBAL innodb_monitor_reset = 'index_page_splits';
SET GLOBAL innodb_monitor_reset = 'index_page_reorg%';
执行上述SQL,91秒后，查询表
SELECT name, count
FROM INFORMATION_SCHEMA.INNODB_METRICS
WHERE name LIKE '%page_split%' OR name LIKE '%page_reorg%';
可以看到执行了页分裂4688次，页重组3967次
胡乱的建议：本身此索引只有一个int一个bigint字段，再加上主键bigint. 那么一行实际数据占用才（8+8+4）20字节，加上行上系统的开销【事务ID（6字节） + 回滚指针（7字节） + 行头（5字节）】 每行总大行在38字节
每页可用空间 = 16,384 - 128 = 16,256字节
每页存储的行数为 = 16,256 / 38 ≈ 427行/页
总页数为 = 776000 / 427 ≈ 1817页。
我这个索引按理总页数大概是1817页。而MySQL页分裂了4688次，证明在update这个语句中，不断分裂，不断重组。
为什么MySQL不在这个事务提交后一次性重组或分裂呢？ 是不是会节约一些时间出来。
我们删除索引
alter table billdrop index idx_pid;
再次执行上述的更新SQL
同样更新了776000行。 执行了35S
建议
我后面发现这个索引之所以页分裂那么大。是因为区分度很低很低，总共不会超10种值
所以此索引可以直接删除，查询业务中有没有强制用hint指定走此索引的，有的话，就改掉
我们实际生产的行数还要比我测试行数大10多倍，会更慢
临时建议： 先drop掉索引，再执行更新，再添加索引
有遗漏或者不对的可以在我的公众号留言哦
