MySQL亿级大表批量插入优化咨询：前快后慢问题求解

一、当前SQL语句的优化 #

原SQL核心问题 #

1. 重复冲突检查：既通过LEFT JOIN A过滤已存在记录，又使用INSERT IGNORE，额外增加数据库开销
2. 低效关联：子查询SELECT * FROM A WHERE cur.c_id = {C_ID}选取全部字段，且若无合适索引，随着表A数据量增大，关联耗时呈指数增长
3. 索引缺失：TEMP_A、C_A的查询条件未匹配最优索引，导致数据筛选和关联变慢

优化后的SQL #

INSERT INTO A (
    u_id,
    a_id,
    c_id,
    loc_id,
    fr_owner,
    dis_group_ids,
    created_at,
    updated_at
)
WITH temp AS (
    SELECT
        temp.u_id,
        temp.a_id,
        temp.c_id,
        ca.job_loc_id AS loc_id,
        temp.fr_owner,
        temp.dis_group_ids,
        CURRENT_TIMESTAMP(6) AS created_at,
        CURRENT_TIMESTAMP(6) AS updated_at
    FROM
        TEMP_A AS temp
    INNER JOIN C_A AS ca 
        ON temp.a_id = ca.id
        AND temp.c_id = ca.c_id
    WHERE
        temp.chunk_id = {CHUNK_ID}
        AND temp.file_id = {FILE_ID}
        AND temp.c_id = {C_ID}
    LIMIT {LIMIT}
)
SELECT
    temp.*
FROM
    temp
LEFT JOIN A AS cur 
    ON temp.u_id = cur.u_id
    AND temp.a_id = cur.a_id
    AND cur.c_id = {C_ID}
WHERE
    cur.id IS NULL;

配套索引优化 #

• 表A创建联合索引：CREATE INDEX idx_a_cid_uid_aid ON A(c_id, u_id, a_id);，快速定位已存在记录，减少关联耗时
• TEMP_A创建联合索引：CREATE INDEX idx_temp_chunk_file_cid ON TEMP_A(chunk_id, file_id, c_id);，加速WITH子句的数据筛选
• C_A创建联合索引：CREATE INDEX idx_ca_id_cid ON C_A(id, c_id);，提升INNER JOIN的关联效率

二、亿级大表数据插入的通用解决方案 #

• 动态调整批量大小：不要固定10000条，初期数据量小时可设为20000条，随着表A数据增多逐步降至5000-8000条；每批插入后添加0.1-0.5秒的休眠，避免数据库负载过高
• 临时禁用非必要约束与索引：插入前禁用表A的非唯一索引（唯一索引保留以保证去重），插入完成后重建；若有外键约束也可临时禁用，恢复后再校验数据，大幅降低插入时的索引维护开销
• 优化数据库配置：
  • 调大innodb_buffer_pool_size至服务器内存的50%-70%，让更多数据缓存到内存
  • 增大innodb_log_file_size至1-4G，减少日志切换频率
  • 临时将innodb_flush_log_at_trx_commit设为2（牺牲部分实时一致性换性能，插入完成后改回1）
  • 关闭autocommit，每批插入后手动执行COMMIT
• 分表拆分：对表A按c_id或其他高频过滤字段做水平分表，将亿级数据分散到多个子表，降低单表的插入和查询压力
• 低峰期执行+隔离级别优化：选择业务低峰期执行插入操作；将事务隔离级别设为READ COMMITTED，减少锁的持有时间，降低死锁概率
• 临时表预处理：先将待插入数据导入无索引的临时表，完成数据清洗和关联后，再批量插入到主表，避免主表持续的索引维护开销

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