如何加速同库内表间数据复制？INSERT INTO...SELECT性能优化求助

一、SQL查询语句优化 #

1. 替换IN子查询为JOIN关联
   当前查询的IN子查询会触发临时表创建逻辑，改用INNER JOIN可减少额外开销，优化执行计划：

INSERT INTO B
SELECT
    A.id,
    A.col1,
    A.col2,
    A.day,
    ... -- 补充需要复制的其他字段
FROM A
INNER JOIN C ON A.col1 = C.col1
WHERE A.day = '2023-08-14' AND C.col1 IS NOT NULL;

2. 添加针对性过滤索引
   表A现有索引ci1为(day, col6, col9)，无法覆盖查询的day + col1过滤条件，建议新增联合索引：

CREATE INDEX idx_day_col1 ON A (day, col1);

该索引可直接过滤符合条件的数据，减少回表扫描的行数，提升读取效率。

二、数据库临时配置调整（MySQL环境） #

执行批量插入前临时修改以下参数，完成后恢复原配置：

• 关闭自动提交：减少事务提交的磁盘IO开销

SET autocommit = 0;

• 禁用约束检查：临时关闭唯一键、外键校验，避免插入时的约束验证开销

SET unique_checks = 0;
SET foreign_key_checks = 0;

• 增大批量插入缓冲区：优化批量插入的内存分配

SET bulk_insert_buffer_size = 64M; -- 根据服务器内存调整，最大可设为1G

• 调整InnoDB日志参数：增大日志缓冲区，减少刷盘频率（innodb_log_file_size需重启生效，无法重启则忽略）

SET innodb_log_buffer_size = 64M;

三、表结构与索引优化 #

1. 临时删除表B的非主键索引
   插入数据时维护索引会大幅拖慢速度，建议先删除表B的非主键索引，插入完成后重建：

-- 删除索引
DROP INDEX ci1 ON B;
DROP INDEX col10 ON B;
DROP INDEX day_col1 ON B;

-- 插入完成后重建索引
CREATE INDEX ci1 ON B (day, col_1, col_6, col_9);
CREATE INDEX col10 ON B (col_10);
CREATE INDEX day_col1 ON B (day, col_1);

2. 保持主键插入顺序
   表B的id为聚簇主键，由于表A的id是自增主键，复制的id天然有序，无需额外调整，有序插入能最大化InnoDB聚簇索引的写入效率。

四、分段批量插入策略 #

避免一次性插入所有数据，按id分段批量插入（例如每次1万条），降低锁表时间与内存占用：

SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;

-- 循环执行直到无数据插入，替换[上一次最大id]为实际值
INSERT INTO B
SELECT A.id, A.col1, A.col2, A.day, ...
FROM A
INNER JOIN C ON A.col1 = C.col1
WHERE A.day = '2023-08-14' AND C.col1 IS NOT NULL
AND A.id > [上一次最大id]
LIMIT 10000;

COMMIT;

五、用LOAD DATA替代INSERT...SELECT #

超大数据量场景下，LOAD DATA INFILE的写入速度远高于INSERT...SELECT，步骤如下：

1. 将筛选后的数据导出到服务器本地文件：

SELECT A.id, A.col1, A.col2, A.day, ...
INTO OUTFILE '/tmp/data_to_import.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM A
INNER JOIN C ON A.col1 = C.col1
WHERE A.day = '2023-08-14' AND C.col1 IS NOT NULL;

2. 导入数据到表B：

LOAD DATA INFILE '/tmp/data_to_import.csv'
INTO TABLE B
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(id, col1, col2, day, ...); -- 对应表B的字段顺序

注意：需确保MySQL进程有文件读写权限，且文件路径符合配置要求。

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