如何高效从另一表取随机值批量更新多列？

针对t1（约100万行）用t2（5万行）的随机行更新多列的场景，你之前的两种写法性能差的核心原因是每行t1都要多次全表扫描t2，以下是高效的优化方案：

核心思路 #

先为t1的每一行生成一个随机的t2主键ID，再通过关联t2一次性获取对应字段，最后批量更新t1。这样只需要对t2做有限次扫描，避免重复计算。

方案1：适用于t2主键ID连续的场景 #

如果t2的id是连续自增（从1到50000），可以直接生成随机ID关联：

-- 1. 创建临时表存储t1的id和对应的随机t2_id
CREATE TEMPORARY TABLE temp_t1_update 
ENGINE=InnoDB -- 避免内存不足，用InnoDB引擎
AS
SELECT 
    t1.id,
    FLOOR(1 + RAND() * (SELECT MAX(id) FROM t2)) AS random_t2_id
FROM t1;

-- 2. 关联临时表和t2，批量更新t1
UPDATE t1
JOIN temp_t1_update ON t1.id = temp_t1_update.id
JOIN t2 ON temp_t1_update.random_t2_id = t2.id
SET t1.field1 = t2.field1,
    t1.field2 = t2.field2;

方案2：适用于t2主键ID不连续的场景 #

如果t2的id存在缺失，用行号映射的方式确保选中的都是有效行：

-- 1. 为t2生成带随机行号的临时表
CREATE TEMPORARY TABLE temp_t2_rand
ENGINE=InnoDB
AS
SELECT 
    id,
    field1,
    field2,
    ROW_NUMBER() OVER (ORDER BY RAND()) AS rn
FROM t2;

-- 2. 获取t2总行数
SET @t2_total = (SELECT COUNT(*) FROM t2);

-- 3. 生成t1的随机行号，关联更新
UPDATE t1
JOIN (
    SELECT 
        id,
        FLOOR(1 + RAND() * @t2_total) AS random_rn
    FROM t1
) AS t1_rand ON t1.id = t1_rand.id
JOIN temp_t2_rand ON t1_rand.random_rn = temp_t2_rand.rn
SET t1.field1 = temp_t2_rand.field1,
    t1.field2 = temp_t2_rand.field2;

方案3：MySQL 8.0+ 用CTE简化写法 #

如果你的MySQL版本是8.0及以上，可用公共表表达式代替临时表：

WITH t1_rand AS (
    SELECT 
        id,
        FLOOR(1 + RAND() * (SELECT MAX(id) FROM t2)) AS random_t2_id
    FROM t1
)
UPDATE t1
JOIN t1_rand ON t1.id = t1_rand.id
JOIN t2 ON t1_rand.random_t2_id = t2.id
SET t1.field1 = t2.field1,
    t1.field2 = t2.field2;

为什么之前的写法性能差 #

1. 第一种写法：每行t1执行两次SELECT ... FROM t2 ORDER BY RAND() LIMIT 1，每次都要对t2全表扫描+排序（执行计划中的Using temporary; Using filesort），100万行t1就会产生200万次t2全表扫描，性能极低。
2. 第二种写法：虽然用了WHERE id=FLOOR(...)，但RAND()是不确定函数，MySQL无法利用t2的主键索引（执行计划显示type=ALL），仍然是全表扫描t2，每行t1两次扫描，效率同样糟糕。

另外要注意：你之前的写法会导致t1.field1和t1.field2来自t2的不同随机行，而上述优化方案是用t2的同一随机行更新两个字段，更符合“选取随机行更新多列”的合理需求。

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