MySQL高效向关联表迁移数据：分类关联迁移优化诉求

嘿，我太懂这种跑了一整晚还没完成的数据迁移有多闹心了！你的需求是把companies_1表的分类迁移到company_categories，还要通过name字段关联companies_1和companies_2来匹配正确的company_id——这种跨表关联的批量操作，慢的根源基本都是没做好索引或者用了低效的写法，下面给你几个实打实的优化步骤：

1. 先给核心关联字段加索引（提速的基础中的基础） #

没有索引的话，大表之间的关联就是全表扫描，哪怕数据量几十万都能跑崩。赶紧给这几个字段加索引：

• companies_1.name：关联companies_2的核心字段
• companies_2.name：同上，必须加索引才能快速匹配
• companies_2.id：如果这个字段还不是主键，一定要设为主键或者加唯一索引（毕竟要关联到company_categories的company_id）

执行的SQL命令：

-- 给companies_1的name字段加索引
CREATE INDEX idx_companies_1_name ON companies_1(name);

-- 给companies_2的name字段加索引
CREATE INDEX idx_companies_2_name ON companies_2(name);

-- 若companies_2.id不是主键，设置为主键（如果业务允许的话）
ALTER TABLE companies_2 ADD PRIMARY KEY (id);

2. 用批量插入代替逐行操作（效率提升几十倍的关键） #

很多人会犯的错：用程序循环逐行查询再插入，这种方式在数据量大的时候完全行不通。直接用SQL的INSERT ... SELECT让数据库引擎批量处理，速度会飞起来：

INSERT INTO company_categories (company_id, category)
SELECT 
    c2.id AS company_id,
    c1.category
FROM 
    companies_1 c1
JOIN 
    companies_2 c2 ON c1.name = c2.name
-- 如果你需要避免重复插入已有的分类数据，加上这个判断
WHERE 
    NOT EXISTS (
        SELECT 1 FROM company_categories cc 
        WHERE cc.company_id = c2.id AND cc.category = c1.category
    );

要是你确定company_categories是空表，可以去掉NOT EXISTS的条件，速度还能再提一档。

3. 超大数据量？拆成小批次处理 #

如果你的表数据是百万级甚至千万级，哪怕用批量插入也可能因为事务太大导致超时或者锁表。这时候把任务拆成小批次，比如每次处理1000条：

-- 设置批次大小和偏移量
SET @batch_size = 1000;
SET @offset = 0;

REPEAT
    INSERT INTO company_categories (company_id, category)
    SELECT 
        c2.id AS company_id,
        c1.category
    FROM 
        (SELECT name, category FROM companies_1 LIMIT @offset, @batch_size) c1
    JOIN 
        companies_2 c2 ON c1.name = c2.name
    WHERE 
        NOT EXISTS (
            SELECT 1 FROM company_categories cc 
            WHERE cc.company_id = c2.id AND cc.category = c1.category
        );
    
    SET @offset = @offset + @batch_size;
-- 直到某次插入没有数据，结束循环
UNTIL ROW_COUNT() = 0 END REPEAT;

这种方式每次只处理一小部分数据，不会占满数据库资源，也能避免长时间锁表影响其他业务。

4. 迁移期间临时关闭不必要的数据库特性（仅限临时用！） #

如果是MySQL环境，迁移时可以临时关闭几个影响插入速度的特性，迁移完一定要恢复：

-- 临时关闭自动提交、唯一性检查和外键检查
SET AUTOCOMMIT = 0;
SET UNIQUE_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 0;

-- 执行你的插入语句...

-- 迁移完成后立刻恢复
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;
COMMIT;

⚠️ 注意：这一步一定要谨慎，迁移完必须恢复，不然会导致数据一致性问题！

5. 检查程序端的低效逻辑（如果原代码是用程序处理的） #

要是你的原代码是用Python/Java这类语言写的，那还要排查这些坑：

• 每次循环都新建数据库连接：换成连接池复用连接
• 逐行查询+逐行插入：改成批量查询+批量插入（比如Python的executemany）
• 一次性把全表数据加载到内存：会导致内存溢出，必须分页处理

最后再啰嗦一句：迁移前一定要备份数据！别因为优化操作搞丢了数据。

内容的提问来源于stack exchange，提问作者Justin La France