MySQL批量插入触发器性能瓶颈的解决方案咨询

我太懂你这种从MS SQL转MySQL遇到的批量触发器痛点了——MS SQL里inserted表一次性处理所有插入行的方式简直是批量场景的福音，而MySQL强制FOR EACH ROW的触发器在数据量大的时候性能直接拉胯，完全没法用。下面给你几个实际项目中验证过的可行方案，都是针对批量插入优化的：

方案1：改用事后批量处理代替触发器（最推荐） #

放弃在插入时自动触发的思路，改为批量插入完成后手动调用存储过程一次性处理，这是性能最优的方案，完全避开了逐行触发器的开销。

具体步骤：

• 给你的业务表新增一个is_processed字段（默认值0），用来标记数据是否已经被处理
• 批量插入数据时，保持is_processed=0即可
• 插入完成后，一次性查询所有未处理的数据，传入你的SendMultipleRecords存储过程，处理完成后更新标记为已处理

示例代码：

-- 1. 批量插入业务数据（假设表名为your_table）
INSERT INTO your_table (content, is_processed) 
VALUES ('content_1', 0), ('content_2', 0), ('content_3', 0), ...;

-- 2. 收集所有未处理的content，拼接成批量参数
SET @batchContent = (SELECT GROUP_CONCAT(content SEPARATOR ',') FROM your_table WHERE is_processed = 0);

-- 3. 调用存储过程处理批量数据
CALL SendMultipleRecords(@batchContent);

-- 4. 标记已处理数据，避免重复处理
UPDATE your_table SET is_processed = 1 WHERE is_processed = 0;

注意：如果content内容较长，需要调整MySQL的group_concat_max_len参数，避免拼接内容被截断。可以通过SET GLOBAL group_concat_max_len = 102400;临时调整，或者在配置文件中永久设置。

方案2：用临时表+队列表模拟批量触发器 #

如果需要保留“插入后自动触发”的类似逻辑，可以用临时表中转，配合队列表攒数据，最后一次性处理：

具体思路：

• 创建一个持久化的process_queue队列表，用来暂存待处理的插入数据
• 每次批量插入前创建临时表，给临时表加触发器（逐行把数据写入队列表）
• 批量插入到临时表后，调用存储过程一次性处理队列表的所有数据
• 处理完成后清空队列表，删除临时表

示例代码：

-- 1. 创建持久化的队列表（只需创建一次）
CREATE TABLE IF NOT EXISTS process_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. 创建临时表（每次批量插入前创建）
CREATE TEMPORARY TABLE temp_insert (content TEXT NOT NULL);

-- 3. 给临时表添加触发器，将插入数据写入队列表
DELIMITER //
CREATE TRIGGER tr_temp_insert_after_insert
AFTER INSERT ON temp_insert
FOR EACH ROW
BEGIN
    INSERT INTO process_queue (content) VALUES (NEW.content);
END //
DELIMITER ;

-- 4. 批量插入数据到临时表
INSERT INTO temp_insert (content) VALUES ('content_1'), ('content_2'), ...;

-- 5. 调用存储过程处理队列中的所有数据
SET @batchContent = (SELECT GROUP_CONCAT(content SEPARATOR ',') FROM process_queue);
CALL SendMultipleRecords(@batchContent);

-- 6. 清空队列表，准备下一次批量处理
TRUNCATE TABLE process_queue;

-- 7. 删除临时表
DROP TEMPORARY TABLE temp_insert;

这个方案虽然还是用到了FOR EACH ROW触发器，但临时表的写入开销极低，而且最终还是批量处理数据，性能比直接在业务表上逐行触发好很多。

方案3：优化逐行触发器的性能（迫不得已时用） #

如果必须坚持用原生触发器，只能尽量优化减少性能损耗：

• 精简触发器逻辑：触发器里只做最基础的数据记录，把复杂的处理逻辑全部移到存储过程中
• 调整MySQL参数：
  • 若不需要二进制日志，可关闭它；或者设置log_bin_trust_function_creators=1减少日志开销
  • 调整innodb_flush_log_at_trx_commit为2（牺牲一点一致性换性能，适合非核心业务）
• 用MEMORY引擎存临时数据：把队列表改成MEMORY引擎，写入速度比InnoDB快很多，处理完直接清空

内容的提问来源于stack exchange，提问作者Tomáš Kohoutek