SQL触发器如何减少对批量写入的影响-SQL-PHP中文网

mysql触发器逐行执行导致批量写入延迟激增，应改用语句级触发器（8.0.23+）、应用层模拟、临时禁用或优化日志表结构。

sql触发器如何减少对批量写入的影响

批量写入时触发器逐行执行导致延迟爆炸

MySQL 的 FOR EACH ROW 触发器在 INSERT ... VALUES (...), (...), (...) 或 LOAD DATA INFILE 场景下，会为每一行数据单独执行一次——1000 行就调用 1000 次，哪怕触发器只做 INSERT INTO log_table，CPU 和事务锁开销也线性放大。实测 p95 延迟可翻 3 倍以上，SHOW PROCESSLIST 中常看到大量 Updating 状态且 Info 列含触发器名。

确认是否触发器拖慢：开启 slow_query_log 并设置 long_query_time = 0，再查慢日志里是否有 Trigger 关键字；或直接用 EXPLAIN FORMAT=TRADITIONAL 分析触发器内 SQL
避免 INSERT INTO ... SELECT 类操作——尤其没索引的 WHERE 条件，每行都扫全表，CPU 直接拉满
禁用 LIKE '%xxx%' 或无索引字段的 JOIN，这类操作在逐行上下文中无法被优化器合并

改用语句级触发器（MySQL 8.0.23+）或过渡表模拟

MySQL 8.0.23 起支持 FOR EACH STATEMENT，配合 NEW TABLE/OLD TABLE 可一次性处理整批数据，把 N 次函数调用压缩为 1 次。老版本无法直接使用，但可用 CTE + INSERT ... SELECT 在应用层模拟类似效果。

新版本写法示例：

CREATE TRIGGER tr_orders_batch AFTER INSERT ON orders
  FOR EACH STATEMENT
  INSERT INTO sync_event_log (table_name, op_type, created_at)
    SELECT 'orders', 'INSERT', NOW() FROM NEW_TABLE;

老版本折中方案：把批量逻辑提到应用层，触发器仅留空壳，由应用在事务提交后统一写 sync_event_log 并发消息
注意：NEW TABLE 是只读临时表，不能在其中做 UPDATE 或 DELETE，也不能加索引

批量导入前临时禁用触发器最安全

对 LOAD DATA INFILE 或定时批量同步任务，禁用触发器比硬扛性能更可控。关键是别用 DROP，要用 DISABLE，且必须配对启用，否则上线后数据不一致。

禁用命令：ALTER TABLE orders DISABLE TRIGGER tr_orders_after_insert
启用命令：ALTER TABLE orders ENABLE TRIGGER tr_orders_after_insert
禁用后务必验证：插入几条测试数据，确认触发器确实没生效；导入完成立即启用，并检查日志表是否开始接收新事件
禁止在存储过程中嵌套 DISABLE/ENABLE——DDL 会隐式提交事务，破坏主业务原子性

触发器内写日志表也得防坑

很多人以为“只写日志”就绝对安全，但若 sync_event_log 表引擎选错、主键缺失或并发写入无序，照样卡住连接池。它不是垃圾桶，是异步链路的第一环。

必须用 InnoDB 引擎，禁用 MyISAM（表锁风险）
主键必须明确（如自增 id），避免无主键导致隐式锁升级
字段精简：只保留 event_id、table_name、op_type、created_at，删掉所有冗余字段和索引
写入前不查、不 JOIN、不调函数——哪怕只是 NOW() 也要写成 VALUES (..., NOW(), ...)，别在触发器里 SET @t = NOW()