为什么MySQL执行大DML操作会急剧消耗UNDO表空间？-mysql教程-PHP中文网

根本原因是长事务阻塞Purge线程导致旧版本无法释放；需先查INNODB_TRX定位trx_state='RUNNING'且trx_query IS NULL的最老事务并KILL，再新增undo表空间、设旧表空间为inactive，待History list length回落后再TRUNCATE。

为什么mysql执行大dml操作会急剧消耗undo表空间？

根本原因不是“写得多”，而是“删不掉”——UNDO必须等所有依赖它的事务结束才能释放，大DML让旧版本堆积如山，Purge线程却卡在长事务后面干瞪眼。

大DML如何把UNDO表空间撑爆

执行一条UPDATE t SET status=1 WHERE created_at （影响500万行），InnoDB会为每一行生成undo记录，存入当前活跃的undo segment。这些记录不会随事务提交立刻消失，而要等所有可能读到该快照的事务（即最老的read view）都退出后，Purge线程才敢清理。

常见错误现象包括：

ERROR 1114 (HY000): The table 't' is full（实际是undo表空间满，不是数据表）
SHOW ENGINE INNODB STATUS\G中HISTORY LIST LENGTH持续高于100万
新事务开始报Lock wait timeout exceeded，查INFORMATION_SCHEMA.INNODB_TRX发现一堆trx_state = RUNNING但trx_started极早的事务

关键点：

触发器、存储过程、显式BEGIN…COMMIT包裹的批量操作，都会延长事务生命周期，间接锁死undo
binlog_cache_size太小导致DML中途刷盘生成临时文件，也会拖慢整体提交节奏
MySQL 8.0默认innodb_undo_log_truncate=OFF，即使设了innodb_max_purge_lag，也不会自动收缩已分配的undo文件

为什么innodb_max_purge_lag不能解决UNDO爆满

innodb_max_purge_lag只是给新DML“踩刹车”，不是给undo“清垃圾”。它检测到History list过长时，会在每个INSERT/UPDATE前插入休眠，但已生成的undo一条都不会少，Purge线程仍受制于最老read view。

使用场景局限明显：

它对已存在的长事务完全无效——比如一个未提交的SELECT * FROM t FOR UPDATE挂了2小时，History list就卡死2小时
它不提升Purge吞吐，只降低写入速率；若innodb_purge_threads=1且磁盘IO弱，History list仍会缓慢爬升
设为0等于关闭保护，设得过小（如10000）会导致业务TPS断崖下跌，但History list可能毫无改善

真正有效的动作顺序是：KILL长事务 → 调高innodb_purge_threads（建议4）→ 确认innodb_undo_log_truncate=ON → 观察INFORMATION_SCHEMA.INNODB_METRICS中purge_truncated是否递增。

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

怎么快速定位谁在拖住UNDO释放

别只盯着INFORMATION_SCHEMA.INNODB_TRX看trx_rows_modified大的事务——很多问题出在“看不见”的地方。

必须查三处：

SELECT trx_id, trx_started, trx_state, trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX ORDER BY trx_started LIMIT 5; —— 找出最老的几个trx_id
SELECT * FROM performance_schema.threads WHERE THREAD_ID = ?（填上上面查到的trx_mysql_thread_id）—— 看它是不是application或monitor类连接，有没有长时间sleep
SELECT EVENT_NAME, COUNT_STAR FROM performance_schema.events_statements_summary_by_event_name WHERE COUNT_STAR > 0 AND EVENT_NAME LIKE '%trigger%' ORDER BY COUNT_STAR DESC LIMIT 3; —— 触发器调用频次异常高，往往意味着隐式事务被反复拉长

特别注意：trx_query为空的事务，大概率是触发器或存储过程内部执行，需结合performance_schema.events_statements_history反查完整调用链。

UNDO表空间本身也能“卡住”释放

MySQL 8.0支持独立undo表空间（innodb_undo_tablespaces > 2），但删除旧undo文件不是自动的。即使某undo表空间已无活跃segment，只要没被标记为inactive，就不会被truncate。

操作路径很窄：

先执行ALTER UNDO TABLESPACE undo001 SET INACTIVE;（需确保该space里无任何活跃事务）
再等Purge线程完成清理，SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'undo001';中STATE变为empty
最后执行DROP UNDO TABLESPACE undo001;（注意：文件名必须以.ibu结尾，否则报错）

最容易被忽略的是第一步的前置检查——SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_undo_no > 0 AND trx_undo_bytes > 0 AND ...必须确认没有事务还在用这个undo space，否则SET INACTIVE会静默失败。