[MySQL SQL优化系列]之如何高效获取随机数据

起因

前几天有位同事问我MySQL要怎么返回一张表的几行随机数据。这个问题其实网上随便一搜答案一大把，但是效果都不太理想，当你要获取随机几行（不是1行）时，得到的数据不是全随机的，而是随机区域。那么有没有办法真正的返回全随机的数据呢？

分析

一般来说获取随机数据，我们第一时间想到的应该是rand()函数。最直接的是order by rand() limit n，这完全能够符合我们的需求，但是效率之地令人发指。我们来看看手册里面是怎么对这个函数描述的：

Returns a random floating-point value v in the range 0 <= v < 1.0.
…
You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.
…
RAND() is not meant to be a perfect random generator. It is a fast way to generate random numbers on demand that is portable between platforms for the same MySQL version.

简单说rand()能够随机返回一个从0~1之间的值，直接使用order by rand() limit m可以在表里获取随机样本，但是也会导致order by重复计算，因此效率非常低下，我们看一个例子。

方案1

test 1-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select id from t_user order by rand() limit 10; 
+--------+
| id     |
+--------+
|  96979 | 
|  82638 | 
| 115767 | 
| 110451 | 
|  47689 | 
| 120664 | 
| 110043 | 
|  75426 | 
|  35199 | 
| 120880 | 
+--------+
10 rows in set (0.12 sec)

上面案例中的表有10w行，在高并发的场景中已经已经是几乎完全不能接受，要是更大的表比如100w1000w呢？按照我们一贯贯彻的SQL优化思路来思考，是不是我得出m个随机值然后直接通过主键自增ID来比较即可（所有测试都是建立在表存在自增ID主键的前提下）？

test 1-2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> select id from t_user_log where id > (select max(id) from t_user) * rand() limit 10;
+------+
| id   |
+------+
|  421 | 
|  478 | 
|  514 | 
|  772 | 
| 1070 | 
| 1152 | 
| 1230 | 
| 1452 | 
| 1501 | 
| 1504 | 
+------+
10 rows in set (0.00 sec)
// 注：t_user_log表约3000万行数据

OK，非常高效，也是全部随机数据。但是注意到没有，返回的数据都是非常小，几乎都在表的最前面的一部分数据。那是因为limit 10的缘故，limit 10会直接在结果集做顺序匹配，一直到返回足够数量（10）的数据就结束查询。而rand()的值在每次递归查询时都是变化的，因此的出来值得几乎都是靠表的最前面部分，而得不到真正意思上的随机样本。这里我们可以explain验证下，是否走全表扫描/主键扫描/或者其他顺序跟主键排列一致的索引扫描。

1
2
3
4
5
6
7
8
mysql> explain  select id from t_user_log  where id > (select max(id) from t_user) * rand() limit 10;                   
+----+-------------+------------+-------+---------------+-----------+---------+------+----------+------------------------------+
| id | select_type | table      | type  | possible_keys | key       | key_len | ref  | rows     | Extra                        |
+----+-------------+------------+-------+---------------+-----------+---------+------+----------+------------------------------+
|  1 | PRIMARY     | t_user_log | index | NULL          | logintime | 4       | NULL | 29345201 | Using where; Using index     | 
|  2 | SUBQUERY    | NULL       | NULL  | NULL          | NULL      | NULL    | NULL |     NULL | Select tables optimized away | 
+----+-------------+------------+-------+---------------+-----------+---------+------+----------+------------------------------+
2 rows in set (0.00 sec)

如我们所料，查询是logintime索引扫描，而logintime是主键递增与自增ID主键的排列顺序是一致的。为了再彻底验证我们的想法，再做一个实验，分别强制使用主键以及另外的排列顺序与主键不一致的索引看看，返回数据区间是否发生了变化。

test 1-3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select id from t_user_log force index (primary) where id > (select max(id) from t_user) * rand() limit 10;          
+------+
| id   |
+------+
|  288 | 
|  928 | 
| 1689 | 
| 1692 | 
| 1745 | 
| 1753 | 
| 1761 | 
| 1823 | 
| 1921 | 
| 1923 | 
+------+
10 rows in set (0.00 sec)
mysql> select id from t_user_log force index (loginip) where id > (select max(id) from t_user) * rand() limit 10;       
+----------+
| id       |
+----------+
| 25142689 | 
| 25680519 | 
| 17320096 | 
| 23015757 | 
|  8516312 | 
| 28329444 | 
| 28327037 | 
|  2838989 | 
|  9054950 | 
| 14630833 | 
+----------+
10 rows in set (0.00 sec)

测试证明了我们的想法是正确的，走主键依然跟之前走logintime一样都是返回表前面部分的数据，换了一个排列顺序与主键不一致的loginip作为索引，返回的结果看起来就比较随机化。这里也证明我们的第一种SQL改造达不到理想的效果，这与优化器对索引的选择有关。

方案2

这里我们使用网上流传比较广泛的关联写法来看看能否达到我们的预期。

test 2-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> select a.id from  t_user_log a join (select rand() * (select max(id) from t_user_log ) as rid) b 
    -> on a.id > b.rid limit 10;
+----------+
| id       |
+----------+
| 23702733 | 
| 23702734 | 
| 23702735 | 
| 23702736 | 
| 23702737 | 
| 23702738 | 
| 23702739 | 
| 23702740 | 
| 23702741 | 
| 23702742 | 
+----------+
10 rows in set (0.01 sec)

效率很高，但是返回的数据其实是随机区间，而不是真正的随机几条样本。也就是说你要获取的是随机一行那OK，要是随机几行或许还达不到真正的需求。
前面我们也说了rand()在where中，每行计算都会重新计算也就是说它是一个变量，但是在上面的例子中，rand()出现在匹配表中，而不是后面的过滤条件，因此只会产生一个值，可以近似理解为一个常量，最后在这个常量只是做过滤匹配，选择10行数据因此，得到的是一个随机区间的数据。

方案3

我们的需求是得到完全随机的N行数据，那么换个思路，我们能不能得到N个小于最大ID的随机数据数字ID，再使用主键匹配关联呢？MayBe.

test 3-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
mysql> select (select max(id) from t_user_log) * rand() from t_user_log limit 10;
+-------------------------------------------+
| (select max(id) from t_user_log) * rand() |
+-------------------------------------------+
|                        18017949.208536115 | 
|                         792399.6698163977 | 
|                        11028577.662700385 | 
|                         22205477.24327947 | 
|                        16821228.167522915 | 
|                        17489710.047002923 | 
|                         6424653.671672591 | 
|                        10214352.156580932 | 
|                        1237587.7071136252 | 
|                        6105096.0050520655 | 
+-------------------------------------------+
10 rows in set (0.00 sec)
mysql> select a.id from t_user_log a join (select round((select max(id) from t_user_log) * rand())  as rid 
    -> from t_user_log limit 10) b where a.id = b.rid;
+----------+
| id       |
+----------+
| 22648946 | 
| 25595789 | 
| 29471892 | 
|  9451671 | 
| 19963103 | 
| 10340078 | 
| 22371296 | 
| 19715821 | 
|   905005 | 
|  6497987 | 
+----------+
10 rows in set (0.05 sec)

OK，到这里是不是就能完美的解决我们的问题呢。从3000W行的大表中真正的随机返回10行数据数据就是这么简单。如果你更处女座一点，又或者你的表数据被截断过min(id)可能都非常大，那么我们可以再彻底点。

test 3-2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 mysql> select a.id from  t_user_log a join 
     -> (select round(( select max(id) - min(id) from t_user_log ) * rand() + (select min(id) from t_user_log))  as rid from t_user_log limit 10 ) 
     -> b on a.id = b.rid ;
+----------+
| id       |
+----------+
| 15740358 | 
| 28635240 | 
|  4274489 | 
| 27147362 | 
|   672493 | 
| 13601017 | 
|  4867180 | 
| 14093062 | 
| 25303557 | 
| 23118176 | 
+----------+
10 rows in set (0.03 sec)

不过如果你表中间删除的数据太多（数据空洞大），使用这种方式可能会造成最终返回的数据小于指定的行数（感谢金荣同学的提醒）。

总结

最后总结下：

  1. 方案二的优点是即使存在数据空洞依然能够返回足够的数据，但也存在返回的数据是随机区间而不是真正随机样本的缺点
  2. 方案三的优点是能高效的返回真正随机样本数据，但是在存在数据空洞的情况下可能返回的数据量小于你的需求
    因此大家还是根据自己的需求灵活选择那种才是最好的解决方案。

其他注意

1
2
bad : select max(id) * rand() from t_user_log;
good : select (select max(id) from t_user_log) * rand();

上面两种写法的效果是一样的，但是代价却是天差地别，在一张表里面使用rand()计算跟与一个常量进行计算的效率差距是非常大的。