一亿行的 MySQL 表，主键 ID 不连续，怎样随机取 30 行速度最快？

主键

rand

MySQL

query

46 条回复 • 2021-02-20 23:08:56 +08:00

1

huabalance

2021-02-19 11:37:45 +08:00 via Android

不断 rand 直到取慢三十个可能还要去重

2

huabalance

2021-02-19 11:38:26 +08:00 via Android

我是说 php 生成随机数。。。

3

0TSH60F7J2rVkg8t

2021-02-19 11:38:36 +08:00

随机生成 30 个数字，WHERE ID >= 你的某个随机数字 LIMIT 1，执行 30 次查询，这样呢？

4

honeycomb

2021-02-19 11:38:51 +08:00 via Android

另外建一个索引表，从那个表里取随机值？

5

leeyuzhe

2021-02-19 11:40:45 +08:00

@ahhui 要去重啊，跟等于实际上是一样的，不断去取然后去重直到取满

6

caola

2021-02-19 11:56:43 +08:00

```
SELECT * FROM XXX
WHERE id >= ((SELECT MAX(id) FROM XXX)-(SELECT MIN(id) FROM XXX)) * RAND() + (SELECT MIN(id) FROM XXX)
limit 30;
```

在网上抄来的

7

Rocketer

2021-02-19 11:59:04 +08:00 via iPhone

@caola 你这不是随机 30 个，而是自然排序取连续 30 个，只是起点随机而已

8

codingadog

2021-02-19 12:05:53 +08:00 via Android

我觉得只要 sql 里有 rand 就会慢。
还是维护个 set，保存生成的随机数，然后生成满 30 个随机数每次取一个最快。

9

bthulu

2021-02-19 12:09:38 +08:00

每次随机三十个数, 数字间大小至少相差十万万, 比如[5001, 235555, 1053052, ...], 然后查询 id>5001 limit 1 union id>23555 limit1 union id>1053052 limit1 ...

10

dafsic

2021-02-19 12:11:35 +08:00

1

随机生成 100 个数，然后 query，结果中大概率会有 30 个

11

caola

2021-02-19 12:11:51 +08:00

1

@Rocketer #7 如果一定要很随机的话，可以把所有的 ID 保存到 redis 并维护这个 ID 列表，然后 srandmember 命令去取

12

rahuahua

2021-02-19 12:27:07 +08:00

@leeyuzhe 这个方式去重不是问题，问题是要读取三十次数据库

13

siweipancc

2021-02-19 14:00:35 +08:00 via iPhone

不上 redis 还有个折衷的方法，额外生成一列 hash，生成索引，取 30 行列值>=随机生成的 hash 的就行了

14

shyling

2021-02-19 14:13:28 +08:00

增加一个存随机数的列 a 加 index，查询的时候再生成一个随机数 b，找 a 在 b 附近的 30 条

15

Amayadream

2021-02-19 14:16:05 +08:00

一般这种不符合常理的需求都不是原始需求，可以说一下你的原始需求是什么。

16

aeli

2021-02-19 14:35:06 +08:00

取最大最小 id，然后计算出差值，再在这个差值中，取 60 个随机数后排序，select * from id in( ( a1> and < a2 )or( a3 > < a4) ....)... 大概的思路吧，取两个值之间的一个。

17

lovecy

2021-02-19 14:50:06 +08:00

假设只有 100 条数据
```sql
SELECT * FROM (SELECT * FROM xxx WHERE id > FLOOR(RAND()*100) LIMIT 1) AS n1
UNION
SELECT * FROM (SELECT * FROM xxx WHERE id > FLOOR(RAND()*100) LIMIT 1) AS n2
UNION
SELECT * FROM (SELECT * FROM xxx WHERE id > FLOOR(RAND()*100) LIMIT 1) AS n3
#写 30 遍，查出来有重复的再查一次
```
FLOOR(RAND()*100)，这一步可以先在代码里算出来，整个方法前提是要知道 id 的最大范围

18

laminux29

2021-02-19 14:53:54 +08:00

这种问题明显应该用空间去换时间，比如在数据录入时，就概率性地取出某些行，直接作为最终数据。

19

xxxyh

2021-02-19 15:01:38 +08:00

order by rand 全表扫描，还排序，如果对随机性要求不是很高的话，可以在取最大和最小 id，在应用层随机取 30 个 id，然后 select * from table where id > (随机出来的 id) limit 1，重复 30 次

20

xxxyh

2021-02-19 15:08:44 +08:00

如果一定要非常精确的随机的话，可以 select count(*) from table，然后在应用层随机出来 30 个 id，把这 30 个 id 排序，从最小的 id@1 开始,select * from table limit id@1,1，从第二条开始 select * from table limit (id@2 - id@1),1

21

xxxyh

2021-02-19 15:11:53 +08:00

如果一定要非常精确的随机的话，可以 select count(*) from table，然后在应用层随机出来 30 个 id，把这 30 个 id 排序，从最小的 id@1 开始,select * from table limit id@1,1，从第二条开始 select * from table where id > (第一次取出来的 id) limit (id@2 - id@1),1

22

eastphoton

2021-02-19 15:30:51 +08:00

既然主键 ID 没有更多规律，总得查一下或者干脆拿到所有 ID 才能得知具体某个 ID 存不存在这个信息了。。

所有 ID 单独维护一份感觉比较合适，上 redis 或者自己维护个 set 。

要么就用一些不那么随机的方案。

23

hannibalm

2021-02-19 15:47:11 +08:00

再建一个临时表，一列现有主键，一列从 1 开始的序号。然后随机抽 30 个。

24

GTim

2021-02-19 16:51:08 +08:00

@dafsic 这个目前是最优，不过 2 倍左右就可以了，如果不够再随机 2 倍去取

25

bertonzh

2021-02-19 16:54:25 +08:00

先加一个自增字段进去...

26

zlowly

2021-02-19 16:58:31 +08:00

mariadb.com 的 Knowledge Base 知识库上有一篇专门讲这个问题的文章，里面提到一些较快但没那么随机的方法，你可以搜索来参考下
data-sampling-techniques-for-efficiently-finding-a-random-row

27

stevenkang

2021-02-19 17:14:23 +08:00

数据库不是用来存取数据的吗，用来做这些复杂的活干嘛。

直接程序随机生成 ID 呗，如 #3 所说取满 30 个就行了。

28

yixiugegegege

2021-02-19 17:20:31 +08:00

SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 30;

29

Still4

2021-02-19 17:39:06 +08:00

我觉得直接说业务吧，啥需求要一亿行里面去随机啊，上面说加自增字段也好，加表也好都是空间换时间，根源上如果需求可以变通的话根本就不需要这么做

30

ivanMeng

2021-02-19 17:42:33 +08:00

布隆过滤器吧，可以快速判断 key 是否存在，id 乱序的 mysql 聚族索引也没发挥出，又是随机的记录地址完全不相邻，更加缓慢，还有这问题本身就是问题。1 亿的表是否有读写分离？上层是否有缓存冷热数据分离？

31

mosliu

2021-02-19 17:47:23 +08:00

看你的数据 id 有多稀疏
不是太稀疏的话
先获取 3*稀疏倍数*2 个随机数记为 idlist1
然后按 id in idlist1 limit 30 取架设取出 n 个
然后不够的再按缺少数量取随机数 rand 循环（ 30-n ）次 {取 id>rand and not in （已取） limit 1}

32

akira

2021-02-19 17:48:28 +08:00

同意 25 楼的，加个连续的自增字段，立马简单起来了

33

love2020

2021-02-19 18:00:12 +08:00

众所周知，随机数是一个难点

34

fox0001

2021-02-19 19:19:00 +08:00 via Android

@bertonzh #25
@akira #32
如果是静态数据，这是最简单的方法。然而楼主丢下问题就没有再回复了

35

xcstream

2021-02-19 19:27:26 +08:00

最快当然不用 mysql 直接写程序用偏移量读取数据文件

36

ipwx

2021-02-19 19:34:05 +08:00

说实话，如果原始 id 不是均匀分布的，那么 id >= RAND() 这种操作去取数字，得出来的结果也不是均匀分布的。

很简单，举个例子：原始 id 是 1, 5, 6, 7 。那么用 id >= min(id) + rand() * (max(id) - min(id)) limit 1 取数字，每个数字被选取的概率为：

1 = 4/7
5 = 1/7
6 = 1/7
7 = 1/7

37

ipwx

2021-02-19 19:34:42 +08:00

生成第二列连续的 id 然后去取，就不会产生这种不均匀分布的问题了。

38

ipwx

2021-02-19 19:36:16 +08:00

1 亿 id 的 int 数组在内存中也就几个 gb 的事情。说实话，单独维护一个索引文件大概都容易搞得定。

39

stevefan1999

2021-02-19 19:39:52 +08:00

你要先定義什麼是隨機
譬如我可以定義 generateRandomNumber() { return 4 }
然後按照這個 random number 做 seed 開一個 LNG 然後生成一個次序...
因爲隨機不代表不能重複啊不過如果要去重那更加要命了

40

xumng123

2021-02-19 19:41:26 +08:00 via iPhone

将 id 放在一个数组里，随机取 39 个后，然后去查一下数据库。

41

akira

2021-02-19 22:27:48 +08:00

@fox0001 就算是动态数据，只要不是动态的太厉害，定时，例如每天，处理一下自增字段应该没太大问题的

42

renmu123

2021-02-19 22:36:38 +08:00 via Android

搞点伪随机，数据量大了之后，谁也不知道你是真随机还是假随机了

43

bthulu

2021-02-20 11:06:27 +08:00

@akira 连续的自增字段怎么加, 你如何保证严格的连续性, 删除数据, 插入未提交等造成的不连续你怎么处理

44

ckdxc

2021-02-20 11:21:14 +08:00

随机行号,行不? 感觉不需要随机 id, round(count), 30 个行号必命中

45

wakzz

2021-02-20 15:09:35 +08:00

先看看业务，是不是必须要真随机。有很多业务实际上压根不需要真随机，搞个差一点的伪随机就够用了

46

akira

2021-02-20 23:08:56 +08:00

@bthulu 不需要严格保证的连续性呀