MySQL/MariaDB大表ORDER BY RAND()随机抽样性能优化

原SQL性能差的核心原因 #

你当前用的ORDER BY RAND() LIMIT 100000逻辑，会为全表660万行每一行都计算一个随机浮点值，再基于这个随机值做全量排序，最后截取前10万条。整个过程会产生大量的CPU计算、内存/磁盘临时表IO开销，InnoDB引擎下还会回表取字段，数据量稍大就会出现执行时间数分钟甚至打满实例资源的问题。

下面是按执行效率从高到低排列的可落地方案，均适用于660万行规模的表：

方案1：基于主键的随机ID关联采样（推荐，秒级返回） #

绝大多数InnoDB表都有自增数值型主键，这个方案性能最高，走主键索引关联，完全不需要全表排序。
核心逻辑是先拿到主键的取值范围，生成足够数量的随机主键值，直接通过主键匹配取数，多生成20%左右的随机ID抵消删除数据导致的主键断层问题。
参考SQL：

SELECT t.`column`
FROM `table` t
INNER JOIN (
    -- 生成12万个随机ID，抵消主键断层，保证能凑够10万条有效数据
    SELECT ROUND(
        RAND() * (max_id - min_id) + min_id
    ) AS random_id
    FROM (
        SELECT MIN(id) min_id, MAX(id) max_id FROM `table`
    ) AS id_range,
    -- 借助系统表生成连续序号，不需要额外建序号表
    information_schema.tables t1,
    information_schema.tables t2
    LIMIT 120000
) AS rand_map
ON t.id = rand_map.random_id
LIMIT 100000;

• 优点：执行速度最快，660万行规模下通常1-3秒即可返回结果，资源占用极低
• 注意：如果表主键断层比例超过20%（比如删过超过20%的历史数据），可以把内层LIMIT的120000调大到130000或更高，保证最终能取够10万条即可。

方案2：概率阈值过滤采样（无主键场景适用，实现最简单） #

如果表没有数值型连续主键，可以直接用随机值阈值过滤，省去全表排序的开销：先计算抽样比例（100000/6600000≈1.51%），把阈值稍微调高一点避免样本不足，逐行判断随机值是否低于阈值，凑够10万条就终止扫描。
参考SQL：

SELECT `column` FROM `table`
WHERE RAND() < 0.016 -- 阈值比理论抽样比例高6%左右，避免样本量不足
LIMIT 100000;

• 优点：不需要依赖主键，写法极简，执行速度比原ORDER BY RAND()快10倍以上，没有排序开销
• 缺点：抽样均匀性略低于全量随机排序，但绝大多数业务分析、数据抽检场景完全够用。

方案3：临时表主键排序采样（最高随机均匀性要求适用） #

如果业务对抽样的随机性要求极高，不能接受主键断层、概率过滤带来的均匀性偏差，可以把主键单独抽到临时表做随机排序，再关联原表取数。因为临时表只存主键和随机值，体积远小于原表，排序开销比全表带字段排序低一个数量级。
参考SQL：

-- 临时表存主键和对应随机值
CREATE TEMPORARY TABLE temp_rand_pool (
    id BIGINT PRIMARY KEY,
    rand_score DOUBLE
);
-- 灌入全表主键
INSERT INTO temp_rand_pool
SELECT id, RAND() FROM `table`;
-- 排序取10万个随机选中的主键
CREATE TEMPORARY TABLE temp_pick_ids (
    id BIGINT PRIMARY KEY
);
INSERT INTO temp_pick_ids
SELECT id FROM temp_rand_pool
ORDER BY rand_score
LIMIT 100000;
-- 关联原表取目标字段
SELECT t.`column`
FROM `table` t
INNER JOIN temp_pick_ids p ON t.id = p.id;
-- 清理临时表
DROP TEMPORARY TABLE IF EXISTS temp_rand_pool, temp_pick_ids;

• 优点：随机均匀性和原ORDER BY RAND()逻辑完全一致，性能比原SQL高5-10倍
• 缺点：需要扫描一次全表提取主键，执行速度比前两个方案慢，适合对随机性要求严苛的场景。

注意事项 #

• 禁止在生产业务库直接执行原ORDER BY RAND()逻辑，全表排序会占用大量磁盘临时空间和IO资源，极易影响线上正常业务
• InnoDB引擎的表行数统计是估算值，用概率阈值采样时记得把阈值调高5%-10%，避免最终返回的样本量不足10万条

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