如何优化为50万行以上用户表批量更新随机姓名的SQL语句？

你现在的写法会为users表的每一行都执行两次独立的子查询：每次子查询都要全量扫描temp_names表、做全表随机排序再取第一条数据。50万行用户数据会触发100万次全表排序和扫描，计算和IO开销直接溢出，自然跑不完。
另外子查询里的u.id is not null是完全无效的过滤条件，对性能没有任何帮助。

核心逻辑是避免每行循环查询，一次性完成随机姓名和用户的关联匹配，仅对temp_names做1次随机排序，后续全部走等值关联，性能会提升几个数量级。

PostgreSQL 优化写法 #

WITH shuffled_names AS (
    -- 仅执行1次：打乱姓名表，生成连续行号，同时统计姓名总数
    SELECT 
        firstname,
        lastname,
        ROW_NUMBER() OVER (ORDER BY random()) AS rn,
        COUNT(*) OVER () AS total
    FROM temp_names
),
users_with_rn AS (
    -- 给用户表生成连续行号，主键排序无额外开销
    SELECT 
        id,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM users
)
UPDATE users u
SET 
    first_name = sn.firstname,
    last_name = sn.lastname
FROM users_with_rn ur
-- 用用户行号对姓名总数取模，匹配到对应的随机姓名
JOIN shuffled_names sn ON sn.rn = (ur.rn % sn.total) + 1
WHERE u.id = ur.id;

50万行数据该写法通常可以在10秒内执行完成。

MySQL 优化写法 #

-- 1. 生成打乱后的临时姓名表，带连续行号
SET @name_rn = 0;
CREATE TEMPORARY TABLE shuffled_names ENGINE=MEMORY AS
SELECT firstname, lastname, @name_rn := @name_rn + 1 AS rn
FROM temp_names
ORDER BY RAND();

-- 2. 统计姓名总条数
SELECT COUNT(*) INTO @name_total FROM shuffled_names;

-- 3. 关联更新用户表
SET @user_rn = 0;
UPDATE users u
JOIN (
    SELECT id, @user_rn := @user_rn + 1 AS rn
    FROM users ORDER BY id
) ur ON u.id = ur.id
JOIN shuffled_names sn ON sn.rn = (ur.rn % @name_total) + 1
SET u.first_name = sn.firstname, u.last_name = sn.lastname;

超大表避锁优化 #

如果业务不允许长时间锁表，可以拆分分批更新，每次只更新1-10万行：

-- 示例（PostgreSQL），循环执行直到所有用户更新完成
UPDATE users u
SET 
    first_name = sn.firstname,
    last_name = sn.lastname
FROM (
    SELECT firstname, lastname, ROW_NUMBER() OVER (ORDER BY random()) AS rn 
    FROM temp_names
) sn
-- 每次取1万条未更新的用户
WHERE u.id IN (SELECT id FROM users WHERE first_name IS NULL LIMIT 10000)
AND sn.rn = (u.id % (SELECT COUNT(*) FROM temp_names)) + 1;

• 提前备份users表数据，避免更新出错无法回滚
• 确保users表id字段是主键/有索引，关联匹配会更快
• 如果temp_names数据量超过10万，可以给临时生成的rn字段加索引，进一步降低关联开销

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