前置说明

如果大家有更好的思路或者方法，亦或对文章存疑、有意见和见解，欢迎在评论区友善讨论或私聊我，谢谢。

参考文章：使用 MySQL + 基准测试结果选择随机行

请留意该文章日期为 2011-3-23，这离 mysql 5.5 发布很近，我怀疑他使用的是 MyISAM 引擎，而非 InnoDB。因为他的测试中 COUNT(*) 的执行速度貌似很快，而对于 InnoDB 来说，这不太可能。因此参考时注意辨别。

另本文所有测试均为 10w 行 text 类型文本，并带有自增主键。使用 MySQL 8.0.23 版本。

概述

本文通过基础语句分析来说明获取随机行是如何实现的

然后通过思路说明按照什么思路来优化基础的获取随机行的语句

并通过举例与分析说明思路的应用

在最后为本文做一个总结性说明

基础语句分析

一般来说，一句基本的获取随机行的 SQL 是这样：

SELECT * FROM myTable ORDER BY RAND() LIMIT 1;

执行流程为：为每一行生成一个随机值 -> 根据随机值进行排序

前者为筛选，通过为行生成随机值来筛选行，后者为排序，将筛选过的行进行排序，然后获取所需数量(LIMIT 1)的随机行

对于这句 SQL 而言，首先通过筛选为所有行生成随机值，然后通过排序，将所有随机行按顺序排好，以获得指定数量的随机行。

可优化地方

不难看出，这句 SQL 在筛选时，将全部列都筛选出来，并在排序时对所有随机行进行排序，然后获取，因此导致了昂贵的查询开销：行数越多，查询越慢，为所有行生成随机值时间增加，排序时间增加

实测 10w 数据，该语句约 4s 的查询时间

因此，对筛选和排序阶段进行合理的优化，才能让语句跑的更快，即如何做到更好的筛选和更好的排序

思路

筛选：避免为所有行生成随机值

思路

不再为所有行生成随机值，而是计算随机 id，生成符合范围的随机 id，进而通过 id "精确匹配"

• id 代表主键，其他能唯一标识一行数据就行，最好是带索引的
• 注意是合适范围，主要是这个不容易做到，导致很多场景只能用基础的获取随机行语句

方法举例

通过行数 * 随机值，并取整，可以得到一个随机 id

分析

生成随机 id，而不是为每一行生成随机值，避免了行数越多，生成随机值总时间越长的问题

排序：根据随机值进行排序

显然，排序涉及的行数越少越好

如果可以筛选出叫小范围的合适的行，再进行排序，会比对全表排序更好

如果在筛选阶段就获取到了足够的行，就不需要通过排序进行选取了

几种方式举例与对比

基础获取随即行

# 1 代表获取随即行的数量
SELECT * FROM myTable ORDER BY RAND() LIMIT 1;

工作原理：

为每一行生成一个随机值，并进行排序选择一定数量的随机行

好处：

简单，易用

坏处：

如果表行数过多，就会很慢

按概率筛选后再进行排序

SELECT * FROM myTable
-- 1 / COUNT(*) * 10 说明： 1 是获取的随机行数，10 是为了扩大筛选概率，避免极端情况
WHERE RAND() < (SELECT ((1 / COUNT(*)) * 10) FROM myTable) 
-- 1 是要获取的随机行数
ORDER BY RAND() LIMIT 1;

注意

此方法在使用 MyISAM 引擎时才可以，因为该引擎不需要考虑事务，因此留有一个字段保存当前表行数，可以直接返回，所以 SELECT COUNT(*) 速度很快，这个方式就会有所提升。

但是 InnoDB 引擎因为要考虑事务，不同隔离级别看到的行数不同，所以每次 SELECT COUNT(*) 都要遍历表来统计行数，为这个方式又加上一次表遍历，反而拖慢了执行速度。

另一种方案

-- 将 COUNT(*) 替换为 MAX(id)
-- id 替换为其他能够标识行数的列也行
WHERE RAND() < (SELECT ((1 / MAX(id)) * 10) FROM myTable)

这种方式需要 id 列连续不存在跳跃，否则 1 / MAX(id) 得到的概率不容易把握

工作原理：

通过为每一行生成随机值，并匹配该行出现的概率，决定该行是否被选中，做一个初步的获取随机行，然后通过排序，选取所需数量的随机行

关键：RAND() < (SELECT ((1 / COUNT(*)) * 10) FROM myTable)

SELECT ((1 / COUNT(*)) * 10) FROM myTable：每行出现的概率

1 / COUNT(*)：是每行对应的概率，* 10 是怕出现极端情况，导致筛选行数少于要获取的量，扩大概率以避免这种方式发生，这里的 1 对应要获取的量，10 是扩大概率，可以自己更改，不要太小就行。

RAND() < 每行出现的概率：每行按概率出现或者不出现

优点

简单

缩小了要排序的数量

缺点

MyISAM 引擎才可以

要么 id 连续不跳跃

随机生成 id 精确查询

SELECT fatherTable.* FROM myTable AS fatherTable 
JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM myTable)) AS sid) AS sonTable 
WHERE fatherTable.id >= sonTable.sid LIMIT 1;

工作原理

通过最大 id 值 (MAX(id))，与随机数相乘得到一个符合 id 范围的随机 id

通过这个 id 匹配表中的列，即可得到一个随机列

优点

自己生成符合范围的随机 id，然后几乎是精确匹配，和行数没什么关系，所以即使行数很多，该 sql 也可以执行的很快

缺点

一次只能查一行随机列

通过用户变量

这个没太看懂，那个变量不知道干啥用的

SELECT t.* FROM 
(
    SELECT ROUND(RAND() * (SELECT MAX(id) FROM myTable)) AS num, @num:=@num+1 
    FROM (SELECT @num:=0) AS a, myTable 
    LIMIT 1
) AS b, myTable AS t 
WHERE b.num = t.id;

工作原理

随机出一定数量符合范围的随机 id，然后将其与表匹配，从而同时查询出多个随机行

优点

同时查多个随机行

缺点

必须要求主键连续自增，即 1，2，3，4……，否则会出现缺行和重复行的情况

总结

-- 基础 SQL：该 SQL 随着行数增多，执行时间也线性增长
SELECT * FROM myTable ORDER BY RAND() LIMIT 1;

• 考虑生成随机 id，避免对全行生成随机值
  • 例如 RAND() * MAX(id) 生成随机 id
• 考虑减少排序涉及的行数
  • 例如先筛选出一部分随机行，再对筛选出的行进行排序

我的应用

选取 weight(权重) 较少的随机行

SELECT * 
FROM (SELECT * FROM myTable ORDER BY weight LIMIT 5) AS son
ORDER BY RAND()
LIMIT 1;

这里缩减了 RAND() 的范围，先通过 weight 查出 5 个符合要求的行，然后随机选取一行。