如何优化使用SELECT WHERE IN子句的MySQL批量插入操作

一、优化查询语句本身 #

1. 替换IN子查询为JOIN #

IN子查询在数据量较大时易引发性能瓶颈，改用JOIN能让数据库优化器生成更高效的执行计划：

-- 页面类型归档优化写法
INSERT INTO my_data_archive.my_table (id, FieldName, Value, Position, PageId)
SELECT t.id, t.FieldName, t.Value, t.Position, t.PageId
FROM my_data.my_table t
JOIN my_data.page p ON t.PageId = p.id
WHERE p.PageTypeId BETWEEN 33 AND 36 AND p.Deleted IS NULL;

-- 创建时间归档优化写法
INSERT INTO my_data_archive.my_table (id, FieldName, Value, Position, PageId)
SELECT t.id, t.FieldName, t.Value, t.Position, t.PageId
FROM my_data.my_table t
JOIN my_data.page p ON t.PageId = p.id
WHERE p.CreatedTimestamp <= DATE_SUB(CURRENT_DATE(),INTERVAL 3 YEAR) AND p.Deleted IS NULL;

2. 给关键字段添加索引 #

• 针对my_data.page表的查询场景，创建组合索引：

  -- 适配页面类型查询的索引
  CREATE INDEX idx_page_pagetype_deleted ON my_data.page(PageTypeId, Deleted);
  -- 适配创建时间查询的索引
  CREATE INDEX idx_page_created_deleted ON my_data.page(CreatedTimestamp, Deleted);
  

• 给my_data.my_table的关联字段建索引：

  CREATE INDEX idx_mytable_pageid ON my_data.my_table(PageId);
  

二、优化插入操作性能 #

1. 临时关闭非必要约束 #

插入前暂时关闭归档表的外键约束、触发器，完成后再恢复，减少插入时的校验开销：

-- 关闭外键检查
SET FOREIGN_KEY_CHECKS = 0;
-- 关闭触发器（若存在）
ALTER TABLE my_data_archive.my_table DISABLE TRIGGER ALL;

-- 执行插入操作...

-- 恢复约束和触发器
SET FOREIGN_KEY_CHECKS = 1;
ALTER TABLE my_data_archive.my_table ENABLE TRIGGER ALL;

如果归档表是新建的，建议先插入数据再创建索引，比边插边建索引的效率高很多。

2. 分批执行批量插入 #

一次性插入超大量数据会占用过多内存和锁资源，改成分批插入：

SET @last_id = 0;
WHILE 1 DO
    INSERT INTO my_data_archive.my_table (id, FieldName, Value, Position, PageId)
    SELECT t.id, t.FieldName, t.Value, t.Position, t.PageId
    FROM my_data.my_table t
    JOIN my_data.page p ON t.PageId = p.id
    WHERE p.CreatedTimestamp <= DATE_SUB(CURRENT_DATE(),INTERVAL 3 YEAR) AND p.Deleted IS NULL
    AND t.id > @last_id
    ORDER BY t.id
    LIMIT 10000; -- 每次插入10000条，可根据服务器性能调整

    SET @last_id = (SELECT MAX(id) FROM my_data_archive.my_table);
    IF ROW_COUNT() = 0 THEN
        LEAVE;
    END IF;
COMMIT;
END WHILE;

3. 调整数据库参数 #

• 增大innodb_buffer_pool_size，让更多数据在内存中处理，减少磁盘IO；
• 调优innodb_log_file_size和innodb_log_buffer_size，提升事务日志处理效率；
• 关闭自动提交（SET AUTOCOMMIT = 0），批量插入后手动执行COMMIT，减少事务提交的开销。

三、更优的归档实现方案 #

1. 分区表交换归档 #

如果业务允许，将原表按CreatedTimestamp或PageTypeId做分区，直接通过交换分区完成归档，操作几乎瞬时完成：

-- 假设原表按年份分区，将2020年分区交换到归档表
ALTER TABLE my_data.my_table EXCHANGE PARTITION p_2020 WITH TABLE my_data_archive.my_table_2020;

2. 离线导出导入 #

超大规模数据场景下，用数据库自带工具（如mysqldump、mysqlpump）先导出符合条件的数据，再导入归档表，比在线INSERT SELECT效率更高：

-- 导出符合条件的数据
mysqldump -u username -p my_data my_table --where="PageId IN (SELECT id FROM my_data.page WHERE CreatedTimestamp <= DATE_SUB(CURRENT_DATE(),INTERVAL 3 YEAR) AND Deleted IS NULL)" > archive_data.sql

-- 导入到归档库
mysql -u username -p my_data_archive < archive_data.sql

3. 增量归档机制 #

如果是定期执行归档，记录上次归档的时间点或最大ID，仅处理新增的待归档数据，避免每次全表扫描。

内容的提问来源于stack exchange，提问作者ses