更新时间：2025-10-31 GMT+08:00
优化阶段拦截写事务
功能介绍
在MySQL中，单个事务如果涉及大规模数据操作（如插入、更新或删除数百万行），会长时间持有锁并大量消耗I/O资源，严重影响后续业务的正常执行。用户往往不得不强制终止会话，触发回滚操作。回滚过程通常与原事务执行时间相当，期间仍会占用数据库锁和I/O资源，进一步阻塞其他工作负载。
为避免此类性能瓶颈，华为云RDS for MySQL在此类大规模写入事务执行前进行拦截，防止因数据变更过大导致系统整体性能下降，并及时提醒客户关注相关风险，从而保障数据库的稳定运行和服务的高可用性。此外，该特性开启后对数据库写性能会有轻微影响。
前提条件
- 对于RDS for MySQL 5.7，要求内核版本大于等于5.7.44.241207。
- 对于RDS for MySQL 8.0，要求内核版本大于等于8.0.41.250900。
配置参数
|
参数名称 |
是否动态参数 |
数据类型 |
取值范围 |
描述 |
|---|---|---|---|---|
|
rds_max_data_mutation_num_per_dml |
是 |
integer |
[0, 10000-9999999999999] |
INSERT/UPDATE/DELETE可影响的行数的最大值，超过了该配置值，则提前拦截报错。 该参数是会话级参数。 默认值为0，表示不拦截。可配置0，以及[10000, 9999999999999]。 |
|
rds_data_mutation_counting_mode |
是 |
enum |
[PRECISE, ESTIMATED] |
INSERT/UPDATE/DELETE影响的行数评估模式。在无法获取结果集行数的相对精确值时，ESTIMATED模式下依赖优化器估算值，PRECISE模式下则不做拦截。 该参数是会话级参数。 默认值为ESTIMATED。 |
使用限制
当前仅支持语句级的限制，以下语句在执行前会提前风险识别并拦截：
|
SQL语句 |
约束条件 |
|---|---|
|
INSERT ... VALUES |
仅对SQL语句VALUES增加的行数进行限制，非实际插入的行数。 |
|
REPLACE ... VALUES |
仅对SQL语句VALUES增加的行数进行限制，非实际插入的行数。 |
|
INSERT ... SELECT |
|
|
REPLACE ... SELECT |
|
|
CREATE TABLE ... SELECT |
|
|
UPDATE TABLE ... |
|
|
DELETE FROM TABLE ... |
|
由于SQL尚未实际执行，系统只能依赖优化器的估算值进行拦截判断，因此无法做到绝对精确。然而，在以下几类场景中，可以获取到相对准确的行数信息：
- 对表执行无限制条件的INSERT ... VALUES操作。
- INSERT/REPLACE ... SELECT或CREATE TABLE ... SELECT语句，其中 SELECT 的结果在插入前需要缓存（比如物化临时表、SORT缓存等）。
- UPDATE/DELETE语句在执行前需要缓存所有待更新行的ROW ID（比如临时文件、SORT 缓存等）。
- 单表查询可直接使用索引访问（如ref或者range），无额外过滤等操作。
在上述场景中，无论当前配置为PRECISE模式还是ESTIMATED模式，系统都会基于获取到的行数来决定是否进行拦截处理。
使用示例
以下是优化阶段拦截写事务的使用示例：
- 表结构。
mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int NOT NULL AUTO_INCREMENT, `category` int NOT NULL, `value` int NOT NULL, `flag` enum('active','inactive') DEFAULT 'active', `created_at` timestamp NOT NULL DEFAULT '2025-05-05 01:01:01', PRIMARY KEY (`id`), KEY `idx_category` (`category`), KEY `idx_value` (`value`), KEY `idx_flag` (`flag`), KEY `idx_category_value` (`category`,`value`) ) ENGINE=InnoDB AUTO_INCREMENT=120109 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> CREATE TABLE t2 LIKE t1; Query OK, 0 rows affected (0.15 sec) - INSERT ... SELECT语句拦截示例。
mysql> EXPLAIN FORMAT=TREE INSERT INTO t2 SELECT * FROM t1 WHERE category=1; +------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------+ | -> Insert into t2 -> Index lookup on t1 using idx_category (category=1) (cost=6276 rows=60112) | +------------------------------------------------------------------------------------------------------+ 1 row in set (0.02 sec) mysql> SET rds_max_data_mutation_num_per_dml=60111; Query OK, 0 rows affected (0.00 sec) # 单表走ref索引无过滤条件，估算值超过门限，'ESTIMATED'模式下会拦截 mysql> SET rds_data_mutation_counting_mode='ESTIMATED'; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t2 SELECT * FROM t1 WHERE category=1; ERROR 4167 (HY000): The number [60112] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'. mysql> # 单表走ref索引无过滤条件，估算值超过门限，'PRECISE'模式下会拦截 mysql> SET rds_data_mutation_counting_mode='PRECISE'; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t2 SELECT * FROM t1 WHERE category=1; ERROR 4167 (HY000): The number [60112] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'. mysql> mysql> EXPLAIN FORMAT=TREE INSERT INTO t2 SELECT * FROM t1 WHERE category=1 AND flag='active'; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Insert into t2 -> Filter: (t1.flag = 'active') (cost=3270 rows=30056) -> Index lookup on t1 using idx_category (category=1) (cost=3270 rows=60112) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SET rds_max_data_mutation_num_per_dml=30055; Query OK, 0 rows affected (0.00 sec) # 存在无法走索引的过滤条件，且估算值超过门限，'ESTIMATED'模式下会拦截 mysql> SET rds_data_mutation_counting_mode='ESTIMATED'; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t2 SELECT * FROM t1 WHERE category=1 AND flag='active'; ERROR 4167 (HY000): The number [30056] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'. mysql> mysql> SET rds_data_mutation_counting_mode='PRECISE'; Query OK, 0 rows affected (0.00 sec) # 存在无法走索引的过滤条件，且估算值超过门限，'PRECISE'模式下不拦截 mysql> INSERT INTO t2 SELECT * FROM t1 WHERE category=1 AND flag='active'; Query OK, 32275 rows affected (37.11 sec) Records: 32275 Duplicates: 0 Warnings: 0 mysql> - UPDATE语句拦截示例。
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE category=1; +--------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------+ | -> Index lookup on t1 using idx_category (category=1) (cost=4710 rows=44448) | +--------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SET rds_max_data_mutation_num_per_dml=44447; Query OK, 0 rows affected (0.00 sec) # 单表走ref索引无过滤条件，估算值超过门限，'ESTIMATED'模式下会拦截 mysql> SET rds_data_mutation_counting_mode='ESTIMATED'; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE t1 SET value=value+100 WHERE category=1; ERROR 4167 (HY000): The number [44448] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'. mysql> # 单表走ref索引无过滤条件，估算值超过门限，'PRECISE'模式下会拦截 mysql> SET rds_data_mutation_counting_mode='PRECISE'; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE t1 SET value=value+100 WHERE category=1; ERROR 4167 (HY000): The number [44448] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'. mysql> mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE category=1 AND flag='active'; +--------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (t1.flag = 'active') (cost=2487 rows=22224) -> Index lookup on t1 using idx_category (category=1) (cost=2487 rows=44448) | +--------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> mysql> SET rds_max_data_mutation_num_per_dml=22223; Query OK, 0 rows affected (0.00 sec) mysql> # 存在无法走索引的过滤条件，且估算值超过门限，'ESTIMATED'模式下会拦截 mysql> SET rds_data_mutation_counting_mode='ESTIMATED'; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE t1 SET value=value+100 WHERE category=1 AND flag='active'; ERROR 4167 (HY000): The number [22224] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'. mysql> # 存在无法走索引的过滤条件，且估算值超过门限，'PRECISE'模式下不拦截 mysql> SET rds_data_mutation_counting_mode='PRECISE'; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE t1 SET value=value+100 WHERE category=1 AND flag='active'; Query OK, 32275 rows affected (43.55 sec) Rows matched: 32275 Changed: 32275 Warnings: 0 - DELETE语句拦截示例。
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE category=2; +--------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------+ | -> Index lookup on t1 using idx_category (category=2) (cost=6276 rows=60112) | +--------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SET rds_max_data_mutation_num_per_dml=60111; Query OK, 0 rows affected (0.00 sec) # 单表走ref索引无过滤条件，估算值超过门限，'ESTIMATED'模式下会拦截 mysql> SET rds_data_mutation_counting_mode='ESTIMATED'; Query OK, 0 rows affected (0.00 sec) mysql> DELETE FROM t1 WHERE category=2; ERROR 4167 (HY000): The number [60112] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'. mysql> # 单表走ref索引无过滤条件，估算值超过门限，'PRECISE'模式下会拦截 mysql> SET rds_data_mutation_counting_mode='PRECISE'; Query OK, 0 rows affected (0.00 sec) mysql> DELETE FROM t1 WHERE category=2; ERROR 4167 (HY000): The number [60112] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'. mysql> mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE category=2 AND flag='active'; +--------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (t1.flag = 'active') (cost=3270 rows=30056) -> Index lookup on t1 using idx_category (category=2) (cost=3270 rows=60112) | +--------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.12 sec) mysql> SET rds_max_data_mutation_num_per_dml=30055; Query OK, 0 rows affected (0.00 sec) # 存在无法走索引的过滤条件，且估算值超过门限，'ESTIMATED'模式下会拦截 mysql> SET rds_data_mutation_counting_mode='ESTIMATED'; Query OK, 0 rows affected (0.00 sec) mysql> DELETE FROM t1 WHERE category=2 AND flag='active'; ERROR 4167 (HY000): The number [30056] of data mutations in the SQL statement has exceeded the maximum limit. Please check the configuration parameter 'rds_max_data_mutation_num_per_dml'. mysql> # 存在无法走索引的过滤条件，且估算值超过门限，'PRECISE'模式下不拦截 mysql> SET rds_data_mutation_counting_mode='PRECISE'; Query OK, 0 rows affected (0.00 sec) mysql> DELETE FROM t1 WHERE category=2 AND flag='active'; Query OK, 32040 rows affected (41.72 sec)
