MySQL大表更新查询优化：5300万行数据去重并设置唯一键

核心问题分析 #

从执行计划能看到，你的UPDATE语句对主表e做了全表扫描（ALL），这是5300万行数据处理缓慢的根本原因。虽已创建idx_race_bib_id索引，但现有查询写法未充分利用它，导致优化器选择了低效的全表扫描路径。

一、索引优化：确保索引高效生效 #

你创建的idx_race_bib_id (race_id, bib, id)是合适的覆盖索引，先执行以下命令更新表统计信息，帮助优化器正确选择索引：

ANALYZE TABLE entry;

如果索引仍未被使用，建议删除冗余的idx_race_bib索引（idx_race_bib_id已包含前两个字段，功能完全覆盖它）：

DROP INDEX idx_race_bib ON entry;

二、查询写法优化：用窗口函数简化逻辑（MySQL 8.0+） #

若使用MySQL 8.0及以上版本，推荐用ROW_NUMBER()窗口函数直接标记需更新的行，逻辑清晰且执行效率更高：

WITH ranked_entries AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY race_id, bib ORDER BY id ASC) AS rn
    FROM entry
    WHERE bib IS NOT NULL -- 仅处理非空bib，减少计算量
)
UPDATE entry e
JOIN ranked_entries re ON e.id = re.id
SET e.bib = NULL
WHERE re.rn > 1;

该写法会按race_id+bib分组，给每组内的行按id升序编号，编号>1的即为需置为NULL的重复行，可充分利用idx_race_bib_id索引，避免全表扫描。

三、分批更新：避免大事务锁表 #

一次性更新大量行易导致事务过大、锁表时间过长，可按id范围分批处理：

-- 每次处理10000行，可根据服务器性能调整步长
SET @batch_size = 10000;
SET @max_id = (SELECT MAX(id) FROM entry);
SET @current_id = 0;

WHILE @current_id < @max_id DO
    UPDATE entry e
    JOIN (
        SELECT race_id, bib, MIN(id) AS min_id
        FROM entry
        WHERE id BETWEEN @current_id AND @current_id + @batch_size
          AND bib IS NOT NULL
        GROUP BY race_id, bib
        HAVING COUNT(id) > 1
    ) min_ids ON e.race_id = min_ids.race_id 
             AND e.bib = min_ids.bib 
             AND e.id > min_ids.min_id
    SET e.bib = NULL
    WHERE e.id BETWEEN @current_id AND @current_id + @batch_size;
    
    SET @current_id = @current_id + @batch_size;
    COMMIT; -- 每批提交一次，及时释放锁
END WHILE;

这种方式每次仅处理一小段数据，降低事务压力，同时利用索引缩小扫描范围。

四、临时表预存待更新ID：减少重复计算 #

先将所有需更新的id提取到临时表，再关联临时表做更新，避免每次UPDATE重复计算重复项：

-- 创建临时表存储待更新ID
CREATE TEMPORARY TABLE temp_update_ids (
    id INT PRIMARY KEY
) ENGINE=InnoDB;

-- 插入需更新的ID
INSERT INTO temp_update_ids
SELECT e.id
FROM entry e
JOIN (
    SELECT race_id, bib, MIN(id) AS min_id
    FROM entry
    WHERE bib IS NOT NULL
    GROUP BY race_id, bib
    HAVING COUNT(id) > 1
) min_ids ON e.race_id = min_ids.race_id 
         AND e.bib = min_ids.bib 
         AND e.id > min_ids.min_id;

-- 批量更新
UPDATE entry e
JOIN temp_update_ids t ON e.id = t.id
SET e.bib = NULL;

-- 清理临时表
DROP TEMPORARY TABLE temp_update_ids;

临时表的主键索引会让后续UPDATE关联更高效，且仅需计算一次重复项，节省系统资源。

五、额外优化建议 #

• 关闭自动提交：执行批量操作前运行SET AUTOCOMMIT = 0;，操作完成后再COMMIT;，减少提交次数。
• 调整内存参数：若服务器内存充足，调大innodb_buffer_pool_size（建议设为物理内存的50%-70%），让更多数据缓存到内存，降低磁盘IO。
• 低峰期操作：大表更新占用资源较多，尽量在业务低峰期执行。

内容的提问来源于stack exchange，提问作者Vladimir Štus