10 MySQL如何显示随机消息

2020-03-10 约 2159 字预计阅读 5 分钟

random 存在哪些问题

背景

从一个单词表中随机选出三个单词。这个表的建表语句和初始数据的命令如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20



mysql> CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

1. 方法一: order by rand()

1.1 内存临时表

select word from words order by rand() limit 3; 这个语句的意思很直白，但执行流程却有点复杂的。

我们先用 explain 命令来看看这个语句的执行情况。

Extra 字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。排序有全字段排序和 rowid 排序两种算法，对于内存表临时表，会选用哪种排序算法呢？

排序算法的选择:

对于 InnoDB 表来说，执行全字段排序会减少磁盘访问，因此会被优先选择。
对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。那么它会优先考虑的，就是用于排序的行越小越好了，所以，MySQL 这时就会选择 rowid 排序。

因此上面这个 SQL 的执行过程是这样的:

创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。
从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。
现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序
初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型
从内存临时表中一行一行地取出 R 值和位置信息
分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。
在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数
排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。

图中的 POS 位置信息表示的是：每个引擎用来唯一标识数据行的信息。

对于有主键的 InnoDB 表来说，这个 rowid 就是主键 ID；
对于没有主键的 InnoDB 表来说，这个 rowid 就是由系统生成的；
MEMORY 引擎不是索引组织表。在这个例子里面，你可以认为它就是一个数组。因此，这个 rowid 其实就是数组的下标。

order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法

1.2 磁盘临时表

tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。

磁盘临时表使用的引擎默认是 InnoDB，是由参数 internal_tmp_disk_storage_engine 控制的。当使用磁盘临时表的时候，对应的就是一个没有显式索引的 InnoDB 表的排序过程。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* 执行语句 */
select word from words order by rand() limit 3;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

max_length_for_sort_data 设置成 16，小于 word 字段的长度定义，所以我们看到 sort_mode 里面显示的是 rowid 排序
SQL 语句，只需要取 R 值最小的 3 个 rowid,MySQL 使用了优先队列排序算法，而不是归并排序，所以filesort_priority_queue_optimization 这个部分的 chosen=true，就表示使用了优先队列排序算法，这个过程不需要临时文件，因此对应的 number_of_tmp_files 是 0。

什么时候选择优先队列排序算法？如果 limit n * 待排序行的大小(上面的大小就是字段R, rowid) 小于 sort_buffer_size 就会使用优先队列排序算法。

1.3 总结

不论是使用哪种类型的临时表，order by rand() 这种写法都会让计算过程非常复杂，需要大量的扫描行数，因此排序过程的资源消耗也会很大。

2. 随机排序方法

2.1 简化方法

我们先把问题简化一下，如果只随机选择 1 个 word 值，可以怎么做呢？思路上是这样的：

取得这个表的主键 id 的最大值 M 和最小值 N;
用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
取不小于 X 的第一个 ID 的行。

1
2
3


mysql> select max(id),min(id) into @M,@N from t ;
set @X= floor((@M-@N+1)*rand() + @N);
select * from t where id >= @X limit 1;

这个算法本身并不严格满足题目的随机要求，因为 ID 中间可能有空洞，因此选择不同行的概率不一样，不是真正的随机。比如你有 4 个 id，分别是 1、2、4、5，如果按照上面的方法，那么取到 id=4 的这一行的概率是取得其他行概率的两倍。

2.2 严格随机法

为了得到严格随机的结果，你可以用下面这个流程:

取得整个表的行数，记为 C；
使用 Y = floor(C * rand())，得到 Y1、Y2、Y3；floor 函数在这里的作用，就是取整数部分。
再执行三个 limit Y, 1 语句得到三行数据。

1
2
3
4
5
6
7


mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1，1； //在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行
select * from t limit @Y2，1；
select * from t limit @Y3，1；

MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个记录作为返回结果，因此上面取一个Y值需要扫描 Y+1 行。再加上，第一步扫描的 C 行，总共需要扫描 C+Y+1 行，执行代价比随机算法 1 的代价要高。总扫描行数是 C+(Y1+1)+(Y2+1)+(Y3+1)

进一步优化的方法是取 Y1、Y2 和 Y3 里面最大的一个数，记为 M，最小的一个数记为 N，然后执行下面这条 SQL 语句：select * from t limit N, M-N+1; 如果返回的数据太多，也可以先取回 id 值，在应用中确定了三个 id 值以后，再执行三次 where id=X 的语句。

目录