MySQL InnoDB表更新速度与记录数相关性及大表更新优化咨询

问题背景 #

使用MySQL 8.0.30搭建数据库，存在若干结构一致（列及类型相同）但记录数不同的InnoDB表。需为所有表新增列col_a，且每条记录的col_a值唯一，因此需逐行更新该列。采用peewee 3.15.1执行分批次更新：每次批量处理10万条记录，先查询指定id范围的记录，修改col_a值后通过bulk_update批量提交。

发现单轮迭代的运行时间与表总记录数高度相关，排除13号表后，二者皮尔逊相关系数达0.9815（p值=1.649e-08）。现咨询两个问题：

1. 为何更新相同数量记录的时间与表总记录数相关？是否是行锁时间随表规模增大而增加？
2. 13号表（含10.3亿条记录）更新时间过长，是否应通过mysqldump导出为CSV，再导入含新列的新表来完成操作？

问题解答 #

1. 相同批量更新时长与表总记录数相关的原因 #

并非行锁时间随表规模增大而增加——行锁是针对单条记录的锁，相同数量的记录锁开销基本一致。核心原因在于InnoDB的存储特性与数据访问成本差异：

• 缓冲池命中率差异：小表的大部分数据页通常能被MySQL缓冲池（Buffer Pool）缓存，批量查询和更新时几乎都是内存操作，耗时极低；大表的数据量远超过缓冲池容量，每次批量操作需要从磁盘读取大量未缓存的数据页，IO耗时占比极高。
• 数据页分布与碎片化：大表的记录分布在更多磁盘页上，即使按主键id范围查询，也可能涉及更多随机IO（若数据存在碎片化）；小表的记录集中在少量页中，IO操作更高效。
• undo日志与事务开销：大表批量更新产生的undo日志量更大，日志写入、刷盘的开销也会随表规模间接增加，进一步拉长单次迭代的时间。

2. 10.3亿条记录大表的改造方案 #

推荐采用导出原数据→生成新列值→导入新表的方案，而非继续批量更新，原因如下：

• 10亿级表的批量更新耗时极长，且持续的更新操作会占用大量IO资源、生成海量undo日志，不仅可能耗尽磁盘空间，还会严重影响数据库的其他在线业务。
• 导出导入的方式效率更高：SELECT ... INTO OUTFILE导出CSV、LOAD DATA INFILE导入的速度远快于逐行更新，且属于批量顺序IO操作，对数据库的负载影响更小。

具体操作步骤：

1. 提前创建与原表结构一致、但新增col_a列的新表，确保主键、索引与原表完全匹配。
2. 使用SELECT id, 其他必要列 INTO OUTFILE '原表数据.csv' FIELDS TERMINATED BY ',' FROM 原表;导出原表核心数据（仅保留后续生成col_a所需的字段，比如主键id）。
3. 用脚本（如Python、Shell）处理导出的CSV，为每一行生成唯一的col_a值并追加到对应行。
4. 使用LOAD DATA INFILE '带col_a的新数据.csv' INTO TABLE 新表 FIELDS TERMINATED BY ',';将处理后的数据导入新表。
5. 验证新表数据的完整性与正确性后，通过表重命名（RENAME TABLE 原表 TO 旧表, 新表 TO 原表;）完成业务切换。

内容的提问来源于stack exchange，提问作者Ningshan Li