《博主主页》CSDN主页奈斯DBIF Club社区主页奈斯、《擅长领域》️数据库阿里云AnalyticDB分布式数据仓库、Oracle、MySQL、NoSQL(Redis)☸️云原生数据库管控平台方向K8s、Prometheus、Go、容器化调度、多集群管理如果觉得文章对你有所帮助欢迎点赞收藏加关注各位大佬们有没有遇到过这样的需求研发那边提出要对一张几千万甚至上亿级别的大表更新某个字段全部的值是不是心里一慌从系统层面来看这种量级的全表更新是非常非常消耗内存、IO资源的从数据库角度分析这种大表全表更新既是大事务也是一个非常长的事务稍有不慎就会把 UNDO 打满MySQL中大事务需整体执行完毕才会提交未及时提交的大事务会使其生成的大量 Undo Log 长期无法被 purge 线程清理叠加 MVCC 对历史版本的保留需求极易将 Undo 表空间打满。如中途需要回滚回滚的时间也就越长甚至还会产生锁因为锁导致会话连接数暴增进而产生数据库宕机的风险……所以这种操作必须慎之又慎既要保证全表更新顺利完成又得确保业务不受影响全程还得有监控、有预案。下面列举一下典型全表更新的场景-- 电话号码添加国际区号UPDATEcustomersSETphoneCONCAT(86,phone);-- 邮箱域名统一更换UPDATEusersSETemailREPLACE(email,old.com,new.com);-- 全平台服务费上涨5%UPDATEservicesSETfeefee*1.05;.....等等场景正好最近博主就接手了两个案例一张1300万 行的表做了全表更新另一张2亿 行的表通过按业务字段分组 分批更新的方式完成全表更新先上全表更新结论如下表格 写在前面1. MySQL实例的配置为48C 64G机械硬盘(SAS/SATA 10k)。需要注意实际耗时和机器性能有关在全闪盘服务器上同样对2亿行的表进行了“按业务字段分组 分批更新”操作却只耗时了1个半小时。2. 两个执行都是在业务低峰期执行并得出的耗时并没有在高峰期执行。特别注意像大批量的全表更新一定要在业务低峰期执行。表规模更新方式耗时影响1300万直接全表更新总计 6 分钟1.IO使用率执行期间平均在30%2.内存使用率执行期间内存指标波动不大可忽略3.数据库层面执行期间产生表锁因为更新了表中的绝大部分行、主从延迟时间可控2亿按业务字段分组 分批更新总计 9 小时546分钟1.IO使用率执行期间平均在80%2.内存使用率执行期间内存指标波动不大可忽略3.数据库层面执行期间产生行锁因为是将表按照业务字段分组 分批更新的方式所以每个事务更新的行数并不多、主从延迟时间可控那么这篇就来分享一下千万级大表全表更新实战亿级大表分批更新实战如何实时查看大事务的执行进度 ⏳希望能给遇到类似需求、但又不敢轻易下手的小伙伴们一些参考和借鉴附赠表情包稳住我们能更️目录长事务 or 大事务一、大事务二、长事务三、对比表格四、如何避免和优化查看事务执行进度的相关视图情况一直接进行全表更新操作案例直接对一个有1300万行的表进行全表关联更新情况二全表更新操作之分批更新案例通过业务字段非主键自增字段对一个需要进行全表更新的2亿行的分区表进行分批更新在开始全表更新案例之前先了解一下什么是大事务和长事务并且也详细介绍一下查看事务执行进度的information_schema.innodb_trx视图。长事务 or 大事务“大事务”和“长事务”经常被一起提及因为它们都可能对数据库性能和数据一致性造成负面影响但它们的侧重点和具体问题完全不同。一、大事务核心特征涉及的数据量大操作的数据行多、产生的日志多。好比一次性把整个仓库的货物全部清点、打包、并记录在案。主要关注点资源消耗日志文件暴涨事务在提交前所有的修改都会记录在日志中如MySQL的binlog PostgreSQL的WAL。大事务会产生海量的日志可能迅速占满磁盘空间。内存压力大数据库需要缓存事务中修改的数据大量数据操作会消耗巨大的内存如InnoDB Buffer Pool。锁竞争激烈它可能会锁定大量的数据行或表阻塞其他会话的读写操作甚至可能导致死锁。主从复制延迟在主从架构中一个大事务的Binlog或WAL事件需要在从库上一次性执行这会占用从库很长时间导致主从之间的数据出现严重延迟。回滚代价高如果大事务执行失败需要回滚其回滚过程会像执行它一样慢甚至更慢期间系统几乎不可用。典型场景一次性清理或删除大量历史数据例如DELETE FROM logs WHERE created_at 2020-01-01。一次性更新全表例如UPDATE users SET status inactive WHERE ...。大数据量的批量导入。二、长事务核心特征持续的时间长从开始到提交/回滚的时间间隔长。好比在仓库里拿起一件货物研究了半天然后又去喝杯咖啡再回来继续研究期间一直占着这件货物不让别人动。主要关注点锁持有时间长这是长事务最致命的问题。事务在执行过程中获得的锁会一直持有直到事务结束。这会导致其他需要访问相同数据的会话被长时间阻塞引发“锁等待”超时极大影响系统的并发性能和响应速度。MVCC快照过期对于使用多版本并发控制MVCC的数据库如PostgreSQL, MySQL InnoDB长事务会导致数据库无法清理旧的、不再需要的数据版本因为它可能还需要访问这些旧版本来保证自身事务的一致性。这会导致表膨胀性能下降。连接池占用长事务会长时间占用一个数据库连接如果连接池有限可能导致新的请求无法获取连接。典型场景在应用程序中开启了一个事务然后等待用户输入这是绝对禁止的。在一个事务中执行一系列复杂的、耗时的业务计算。不小心忘掉了提交或回滚事务例如代码中没有正确处理事务边界。三、对比表格特征大事务长事务核心问题数据量时间主要影响资源消耗日志、内存、主从延迟锁竞争、阻塞、MVCC垃圾回收好比一次性搬走一座山长期占着一条路不让别人走典型场景批量删除、大数据导入事务中等待用户输入、忘记提交优化策略分而治之拆分成小批次提交快进快出减少事务内耗时操作尽快提交四、如何避免和优化对于大事务分批处理将大批量操作拆分成多个小批次每处理一批就提交一次。例如用循环每次处理1000条数据。选择合适的时机在业务低峰期执行大批量操作。使用特定工具对于数据导入使用LOAD DATA INFILE或COPY命令通常比在事务中执行大量INSERT更高效。对于长事务避免交互绝对禁止在事务中等待用户输入或进行外部API调用。业务逻辑应在事务之外处理。设置超时在数据库或ORM框架中设置事务超时时间自动终止长时间运行的事务。优化查询确保事务内的所有SQL语句都经过优化使用了正确的索引以减少执行时间。及时提交/回滚在代码中使用try-catch-finally等结构确保事务被正确关闭。查看事务执行进度的相关视图SQL select * from information_schema.innodb_trx;—INNODB_TRX表提供了关于INNODB内当前执行的每个事务的信息包括事务是否正在等待锁、事务何时启动以及事务正在执行的SQL语句如果有的话TRX_IDInnoDB内部的唯一事务ID号。这些ID不是为只读和非锁定的传输创建的。TRX_WEIGHT事务的权重反映但不一定是事务更改的行数和锁定的行数。为了解决死锁InnoDB选择权重最小的事务作为要回滚的“牺牲品”。已更改非事务表的事务被认为比其他事务重而不管已筛选和锁定的行数是多少。TRX_STATE事务执行状态。允许的值为RUNNING、LOCK WAIT、ROLLING BACK和COMMITTING。RUNNING运行中当事务正在执行其包含的 SQL 语句时它处于 RUNNING 状态。在这个阶段事务可能会读取、写入或修改数据库中的数据。LOCK WAIT等待锁当事务尝试访问一个被另一个事务锁定的资源时它会进入 LOCK WAIT 状态。这通常发生在两个或多个事务尝试修改同一行数据或访问被另一个事务锁定的资源时。事务会等待直到锁被释放然后才能继续执行。ROLLING BACK回滚中如果事务在执行过程中遇到错误或者它被明确地要求回滚例如通过 SQL 语句 ROLLBACK那么它会进入 ROLLING BACK 状态。在这个阶段事务会撤销所有已执行的更改使数据库恢复到事务开始之前的状态。COMMITTING提交中当事务成功完成其所有操作并且没有遇到任何错误时它会进入 COMMITTING 状态。在这个阶段事务所做的所有更改都会被永久地写入数据库。一旦提交完成事务就完成了它对数据库的更改对其他事务也是可见的。TRX_STARTED事务开始时间。TRX_REQUESTED_LOCK_ID如果TRX_STATE为LOCK WAIT则事务当前正在等待的锁的ID否则为NULL。TRX_WAIT_STARTED事务开始等待锁的时间如果TRX_STATE为LOCK WAIT否则为NULL。TRX_MYSQL_THREAD_IDMySQL线程ID。获得线程的详细信息需要将该列与INFORMATION_SCHEMA.PROCESSLIST表的ID列连接起来。trx_mysql_thread_id对应的内容就是show processlist中会话的线程TRX_QUERY事务正在执行的SQL语句。TRX_OPERATION_STATE交易的当前操作如有否则为NULL。TRX_TABLES_IN_USE处理此事务的当前SQL语句时使用的InnoDB表的数量。TRX_TABLES_LOCKED当前SQL语句具有行锁的InnoDB表的数量TRX_LOCK_STRUCTS事务保留的锁数。TRX_LOCK_MEMORY_BYTES内存中此事务的锁结构所占用的总大小。TRX_ROWS_LOCKED此事务锁定的大约行数。该值可能包括删除标记的行这些行是物理显示的但对事务不可见。TRX_ROWS_MODIFIED此事务中修改和插入的行数。TRX_STATE字段为RUNNING状态TRX_ROWS_MODIFIED显示的是插入了多少行数据当源表的全部数据插入到目标表后此事务才算结束。TRX_STATE字段为ROLLING BACK状态TRX_ROWS_MODIFIED显示的是还剩余多少行数据需要回滚当数值为0时此事务的全部数据才算回滚完成。TRX_CONCURRENCY_TICKETS一个值指示当前事务在交换出去之前可以做多少工作由innodb_concurrency_ticktssystem变量指定。TRX_ISOLATION_LEVEL当前事务的隔离级别。TRX_UNIQUE_CHECKS当前事务的唯一检查是打开还是关闭。例如它们可能会在数据加载过程中关闭。TRX_FOREIGN_KEY_CHECKS当前事务的外键检查是打开还是关闭。例如它们可能在大容量数据加载期间关闭。TRX_LAST_FOREIGN_KEY_ERROR最后一个外键错误的详细错误消息如果有的话否则为NULL。TRX_ADAPTIVE_HASH_LATCHED自适应哈希索引是否被当前事务锁定。当自适应哈希索引搜索系统被分割时单个事务不会锁定整个自适应哈希索引。自适应哈希索引分区由innodb_Adaptive_hash_index_parts控制默认情况下设置为8。TRX_ADAPTIVE_HASH_TIMEOUT是立即放弃自适应哈希索引的搜索锁存还是在MySQL的调用中保留它。当不存在自适应哈希索引争用时该值保持为零语句保留锁存直到它们完成。在争用期间它倒计时到零语句在每次行查找后立即释放锁存器。当自适应哈希索引搜索系统被划分由innodb_adaptive_hash_index_parts控制时该值保持为0。TRX_IS_READ_ONLY值为1表示事务是只读的。TRX_AUTOCOMMIT_NON_LOCKING值1表示事务是一个SELECT语句它不使用FOR UPDATE或LOCK INSHARED MODE子句并且在启用自动提交的情况下执行因此事务只包含这一条语句。当thiscolumn和TRX_IS_READ_ONLY都为1时InnoDB会优化事务以减少与更改表数据的事务相关的开销。TRX_SCHEDULE_WEIGHT由内容感知事务调度CATS算法分配给等待锁定的事务的事务调度权重。该值相对于其他事务的值。数值越高中量级就越大。仅为处于LOCK WAIT状态的事务计算一个值如TRX_state列所报告的。为不等待锁定的事务报告NULL值。TRX_SCHEDULE_WEIGHT值与TRX_WEIGHT不同后者是由不同的算法为不同的目的计算的。那么下面开始千万级大表全表更新以及亿级大表全表分批更新更新的安全操作实践。情况一直接进行全表更新操作案例直接对一个有1300万行的表进行全表关联更新因市场变化需要对地区表中的价格进行多表关联全表更新事先知道被更新表的数据量在1300万行关联表的数据量在900万行因此可以直接在业务低峰期操作。SQL中部分表名和字段有点敏感所以博主用通用方式表达。mysqlselectcount(*)fromliu_oracleoltp_33020000;liu_oracleoltp_33020000表数据加索引大小总计6G。统计出来的表行数和实际表的行数出现了偏差也就是出现统计信息差别较大的问题。参考这篇文章文章直通车【MySQL篇】持久化和非持久化统计信息的深度剖析(含analyze命令和mysqlcheck工具两种收集方式)中的6解决统计信息差别较大的问题mysqlselectcount(*)fromliu_oracleoltp_csh_330200;liu_oracleoltp_csh_330200表数据加索引大小总计2G。统计出来的表行数和实际表的行数出现了偏差也就是出现统计信息差别较大的问题。参考这篇文章文章直通车【MySQL篇】持久化和非持久化统计信息的深度剖析(含analyze命令和mysqlcheck工具两种收集方式)中的6解决统计信息差别较大的问题对需要更新的业务表进行全表关联更新执行如下mysqlupdateliu_oracleoltp_33020000 tar,liu_oracleoltp_csh_330200 orisettar.bd_sc_priceori.original_local_price,tar.BD_SC_UPDATE_DATEnow(),tar.bd_sc_update_type4,tar.BD_PP_PRICEori.brand_local_priceandtar.BD_PP_UPDATE_DATEnow(),tar.bd_pp_update_type4wheretar.PPIDori.brand_idandtar.YCLJIDori.org_part_id;9147299行被全表关联更新耗时6分钟查看事务的信息包括事务是否正在等待锁、事务何时启动以及事务正在执行的SQL语句mysqlselect*frominformation_schema.innodb_trx\G;*************************** 1. row ***************************trx_id: 15417955580###InnoDB内部的唯一事务ID号trx_state: RUNNING###当事务正在执行其包含的 SQL 语句时它处于 RUNNING 状态。在这个阶段事务可能会读取、写入或修改数据库中的数据trx_started: 2026-01-29 13:40:09###事务开始时间trx_requested_lock_id: NULL###如果TRX_STATE为LOCK WAIT则事务当前正在等待的锁的ID否则为NULLtrx_wait_started: NULL###事务开始等待锁的时间如果TRX_STATE为LOCK WAIT否则为NULLtrx_weight: 4427473trx_mysql_thread_id: 966###MySQL线程ID。trx_mysql_thread_id对应的内容就是show processlist中会话的线程trx_query: update liu_oracleoltp_33020000 tar,liu_oracleoltp_csh_330200 oriset tar.bd_sc_priceori.original_local_price ,tar.BD_SC_UPDATE_DATE now(),tar.bd_sc_update_type‘4’,tar.BD_PP_PRICE ori.brand_local_price and tar.BD_PP_UPDATE_DATE now(),tar.bd_pp_update_type‘4’where tar.PPID ori.brand_id and tar.YCLJID ori.org_part_id###事务正在执行的SQL语句trx_operation_state: starting index read###交易的当前操作如有否则为NULLtrx_tables_in_use: 2###处理此事务的当前SQL语句时使用的InnoDB表的数量trx_tables_locked: 2###当前SQL语句具有行锁的InnoDB表的数量trx_lock_structs: 424317###事务保留的锁数trx_lock_memory_bytes: 43622608###内存中此事务的锁结构所占用的总大小trx_rows_locked: 32466932###此事务锁定的大约行数。该值可能包括删除标记的行这些行是物理显示的但对事务不可见trx_rows_modified: 4003156###此事务中修改和插入的行数。因为此事务是在修改TRX_STATE字段为RUNNING状态所以这里显示的是修改了多少行数据估算会更新1000万行左右的数据也就是说这里到1000万假设实际可能会多点也可能会少点后此事务才算结束也就是数值显示为10000000。这里就是事务的执行进度trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READ###当前事务的隔离级别trx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0mysqlselect*frominformation_schema.innodb_trx\G;trx_rows_modified: 9147299###此事务中修改和插入的行数。因为此事务是在修改TRX_STATE字段为RUNNING状态所以这里显示的是修改了多少行数据估算会更新1000万行左右的数据也就是说这里到1000万假设实际可能会多点也可能会少点后此事务才算结束也就是数值显示为10000000。这里就是事务的执行进度。实际全表关联更新了9147299行这里显示的就是实际修改的9147299。执行完成之后观察磁盘IO、内存使用情况可以看到执行是非常消耗磁盘IO的内存指标波动不大可忽略。1300万行的表进行全表关联更新耗时6分钟结束。情况二全表更新操作之分批更新案例通过业务字段非主键自增字段对一个需要进行全表更新的2亿行的分区表进行分批更新数据库中的 liu_oracleoltp_detail 分区表有2亿行数据。SQL中部分表名和字段有点敏感所以博主用通用方式表达。mysqlselectcount(*)fromliu_oracleoltp_detail;liu_oracleoltp_detail表数据加索引大小总计267G。统计出来的表行数和实际表的行数出现了偏差也就是出现统计信息差别较大的问题。参考这篇文章文章直通车【MySQL篇】持久化和非持久化统计信息的深度剖析(含analyze命令和mysqlcheck工具两种收集方式)中的6解决统计信息差别较大的问题业务要求需要将 liu_oracleoltp_detail 分区表中is_paintable字段的全部数据更新成“3”。上面的案例对1300万行的表进行全表更新耗时6分钟那么按照推算2亿行的表全表直接更新就得需要1.6小时那么这个事务会非常的大并且执行的时间会非常的长典型的大事务和长事务随着事务越大那么耗时时间越不可控实际肯定比1.6小时要久一定不要冒这个风险因为会导致数据库宕机。因此需要采用分批更新的方式当前这个表中没有主键自增字段如下截图因此不能通过将主键自增字段作为where自增字段 between ... and ...分批更新的where条件。和业务沟通后了解到cl_vehicle_id字段和id字段组合成了唯一索引id字段虽然不是主键但数据非空且唯一cl_vehicle_id字段为“车型id”每一个车型都有一个唯一的车型id但数据中会有多个同样车型id的记录虽然cl_vehicle_id字段默认DEFAULT NULL可以为空但实际业务中cl_vehicle_id字段的数据不能为空并数据不唯一cl_vehicle_id字段数据会重复但id字段的数据非空且唯一所以这个组合唯一索引成立。业务表示“车型id”不算很多并且每个“车型id”的记录也就在几千到几万条可以先对cl_vehicle_id字段进行分组统计出来有多少个“车型id”并观察每个“车型id”有多少数据量如果“车型id”不多并且每个“车型id”的数据量也适中才适合将cl_vehicle_id字段作为where条件对is_paintable字段的全部数据分批更新成“3”。通过对cl_vehicle_id字段进行分组可以看到总共有2亿行数据总共有100168个“车型id”且没有空值的数据。mysqlselectcl_vehicle_id,count(*)fromliu_oracleoltp_detailgroupbycl_vehicle_idorderbycount(*)asc;可以看到第一行“车型id”的数据量为2万行最后一行100168行“车型id”的数据量为1行并且没有空值的数据总共有100168个“车型id”最多“车型id”的数据量也就2万行因此非常适合做分批更新的字段。将分组后的“车型id”字段通过concat函数进行拼接整理成对应的update SQL语句mysqlselectconcat(update liu_oracleoltp_detail set is_paintable 3 where cl_vehicle_id , ,cl_vehicle_id, )fromliu_oracleoltp_detailgroupbycl_vehicle_idorderbycount(*)desc;虽然这个表很大扫描的时候会很慢但update时最多只会有2万行数据被更新对于直接不带where条件的update全表更新而言极大的减少了日志文件暴涨、内存压力大、锁竞争、主从复制延迟、回滚代价高的问题。尝试执行其中的一条update查看执行计划mysqlexplainupdateliu_oracleoltp_detailsetis_paintable3wherecl_vehicle_id3A4191098A5F632EE050A8C007505AEB;作为where条件的cl_vehicle_id字段使用复合索引定位到分区p155中的约3.5万行数据通过WHERE条件过滤后更新。索引使用正常加快了更新的速度但需回表操作。将分组后的“车型id”字段的数据作为where条件写成多个update保存成SQL文件通过“”的形式在后台执行。[rootjzdj-db2 zhixing_liufei]# mysql -u root -pfGj7aG35TS --socket/jz/mysql5.7_online_ddl/data/3306/jz.sock -o liudbywcs update_100168.sql ###对全表按业务字段分组 分批更新总计100168个update SQL语句查看事务的信息包括事务是否正在等待锁、事务何时启动以及事务正在执行的SQL语句mysqlselect*frominformation_schema.innodb_trx\G;*************************** 1. row ***************************trx_id: 15417950185###InnoDB内部的唯一事务ID号trx_state: RUNNING###当事务正在执行其包含的 SQL 语句时它处于 RUNNING 状态。在这个阶段事务可能会读取、写入或修改数据库中的数据。trx_started: 2026-01-28 23:17:11###事务开始时间trx_requested_lock_id: NULL###如果TRX_STATE为LOCK WAIT则事务当前正在等待的锁的ID否则为NULLtrx_wait_started: NULL###事务开始等待锁的时间如果TRX_STATE为LOCK WAIT否则为NULLtrx_weight: 189trx_mysql_thread_id: 1339###MySQL线程ID。trx_mysql_thread_id对应的内容就是show processlist中会话的线程trx_query: update liu_oracleoltp_detail set is_paintable ‘3’ where cl_vehicle_id ‘4028d06d8113dbf90181381ef17c554d’###事务正在执行的SQL语句。/font.trx_operation_state: fetching rows###交易的当前操作如有否则为NULLtrx_tables_in_use: 1###处理此事务的当前SQL语句时使用的InnoDB表的数量trx_tables_locked: 1###当前SQL语句具有行锁的InnoDB表的数量trx_lock_structs: 155###事务保留的锁数trx_lock_memory_bytes: 24784###内存中此事务的锁结构所占用的总大小trx_rows_locked: 6953###此事务锁定的大约行数。该值可能包括删除标记的行这些行是物理显示的但对事务不可见。trx_rows_modified: 34###此事务中修改和插入的行数。因为此事务是在修改TRX_STATE字段为RUNNING状态所以这里显示的是修改了多少行数据比如cl_vehicle_id字段为“4028d06d8113dbf90181381ef17c554d”的数据有9238行修改完成9238行后此事务才算结束也就是数值显示为9238。这里就是事务的执行进度trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READ###当前事务的隔离级别trx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0mysqlselect*frominformation_schema.innodb_trx\G;trx_query: update liu_oracleoltp_detail set is_paintable ‘3’ where cl_vehicle_id ‘0A4D6C841644B9F6E050A8C01A3262BE’###事务正在执行的SQL语句。上一个update执行完成开始执行了下一个trx_rows_modified: 92###此事务中修改和插入的行数。因为此事务是在修改TRX_STATE字段为RUNNING状态所以这里显示的是修改了多少行数据比如cl_vehicle_id字段为“0A4D6C841644B9F6E050A8C01A3262B”的数据有1232行修改完成1232行后此事务才算结束也就是数值显示为1232。这里就是事务的执行进度执行完成之后观察磁盘IO、内存使用情况可以看到执行是非常消耗磁盘IO的内存指标波动不大可忽略。2亿行的表进行全表按业务字段分组 分批更新耗时 9 小时546分钟结束。对is_paintable字段进行分组统计可以看到is_paintable字段全部的值都为“3”了和全表数据量一致表明全表更新成功。mysqlselectis_paintable,count(*)fromliu_oracleoltp_detailgroupbyis_paintableorderbycount(*)asc;实战出真知千万级全表更新监控先行亿级表更新化整为零分批推进方是王道。希望这篇文章可以帮到有同样需求进行全表更新的小伙伴让我们积攒经验下次“更新”