优化MySQL大VARCHAR字段主表的新增关联操作方案咨询

核心优化方向 #

放弃效率低下的游标循环插入，改用批量SQL操作，同时给masterSources.name添加索引（唯一索引优先），从根本上降低VARCHAR关联的开销。

1. 先给主表添加唯一索引 #

给masterSources的name字段添加唯一索引，既可以自动避免重复插入，又能极大加速后续的关联查询：

ALTER TABLE masterSources ADD UNIQUE INDEX idx_unique_name (name);

2. 批量插入新增源数据 #

替换原来的游标循环，用单条SQL完成批量插入，效率提升明显：

INSERT INTO masterSources (name)
SELECT DISTINCT n.name
FROM receivedSources n
LEFT JOIN masterSources m ON n.name = m.name
WHERE m.pk IS NULL
ON DUPLICATE KEY UPDATE name = name; -- 已存在的记录直接跳过，不做修改

这里用DISTINCT是过滤receivedSources中重复的新数据，减少无效插入请求。

3. 高效生成关联表 #

由于主表已经有了name的索引，关联查询的性能会大幅提升。如果receivedSources数据量极大，可以临时给它的name加索引进一步加速：

-- 可选：临时添加索引（数据量超大时用）
CREATE INDEX idx_temp_received_name ON receivedSources(name);

-- 生成关联表
CREATE TABLE newReceivedSources AS
SELECT m.pk, n.name
FROM receivedSources n
INNER JOIN masterSources m ON n.name = m.name;

-- 可选：用完后删除临时索引
DROP INDEX idx_temp_received_name ON receivedSources;

4. 重构后的完整存储过程 #

把所有优化整合到存储过程中，去掉冗余的游标逻辑：

CREATE PROCEDURE my_procedure()
BEGIN
    -- 批量插入唯一的新源数据
    INSERT INTO masterSources (name)
    SELECT DISTINCT n.name
    FROM receivedSources n
    LEFT JOIN masterSources m ON n.name = m.name
    WHERE m.pk IS NULL
    ON DUPLICATE KEY UPDATE name = name;

    -- 生成带关联pk的新表
    CREATE TABLE newReceivedSources AS
    SELECT m.pk, n.name
    FROM receivedSources n
    INNER JOIN masterSources m ON n.name = m.name;
END;

额外优化：哈希索引（MySQL 8.0+适用） #

如果name字段长度较长，普通B-tree索引的存储空间和查询开销仍较大，可以改用哈希索引（仅支持等值查询，完全匹配当前场景）：

ALTER TABLE masterSources ADD INDEX idx_hash_name USING HASH (name);

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