MySQL深入-正确显示随机消息

现在很多手机端首页会推送几个随机的页面，这种随机页面有两个关键点：

• 如何高效保证随机性。
• 保证随机性的同时确保推荐的页面不会和之前推荐的重复。

内存临时表

首先我们使用order by rand()来实现这个逻辑。

select word from words order by rand() limit 3;

使用explain命令来查看一下这个语句的执行情况：

Extra字段显示using temporary表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。对于该语句而言，就是需要用到临时表，而且临时表需要进行排序。

由于涉及到排序，那么就不得不回顾一下全字段排序和rowid排序的差别，并且分析出该排序是使用的何种排序。

• 全字段排序，由于一行数据较多，所以内存能够放下的行相对较多，需要用到的临时文件相对较多。
• rowid排序，一行数据较小，所以内存能够放下的行相对较少，需要用到的临时文件相对较少，但是会多去主键索引表回表一次。

针对InnoDB 表为了减少磁盘IO，MySQL 会选择全字段排序。但是针对内存表来说，回表操作也只是进行一次访存，执行效率高，因为内存比较紧张时，需要减少排序消耗的内存，所以对于内存表来说，会选择使用rowId来排序。所以这个SQL语句会采用rowid排序。

下面来分析一下执行过程：

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

set long_query_time = 0；可以让慢查询的阈值为0，这样即使是快速的SQL语句也能够被记录在慢查询日志中。

不同版本的MySQL这边得到的扫描行数可能不相同。

执行流程如下：

图中的pos位置信息就是每个引擎用来唯一标识数据行的信息：

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

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

磁盘临时表

我们在分析刚才那条sql语句的时候Extra字段显示using temporary表示的是需要使用临时表，而临时表的使用也是有一个范围的，tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。

磁盘临时表使用的引擎默认是 InnoDB，是由参数 internal_tmp_disk_storage_engine 控制。

为了让刚才那条语句使用磁盘临时表，我们需要把tmp_table_size这个数值设小一点。

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`

因为max_length_for_sort_data设置成了16，小于word字段定义的长度，因此会采用rowid排序。

参与排序的是随机值R字段和rowid字段组成的行。但是number_of_tmp_files为0，意味着不需要使用临时文件吗？

这里采用的优先队列排序算法，而不是传统的归并排序算法。因为我们只需要前三个有序就可以了，传统的归并排序算法会把10000行数据都排序好，浪费了很多计算量。

优先队列排序算法执行过程如下：

1. 对10000行准备排序的数据，先取前三行，构造成一个堆。
2. 取下一行数据，与这个堆的最大值进行比较，如果下一行数据更小，则于这个堆的最大值替换。
3. 重复步骤2的操作，直到所有行比较完成。

流程结束后，我们所构造的堆就是1000行中R值最小的行。然后，依次把它们的 rowid 取出来，去临时表里面拿到 word 字段。

对于使用优先队列排序还是使用归并排序主要取决于sort buffer排序内存的大小。

我们看到使用order by rand()这种方法计算过程和扫描表的次数都是比较多，因此排序过程的资源消费也会很大。

随机排序方法

方法1

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

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;

这个方法效率很高，因为取 max(id) 和 min(id) 都是不需要扫描索引的，而第三步的 select 也可以用索引快速定位，可以认为就只扫描了 3 行。但是这个方法是不能做到真正的随机的。

假设现在数据为[1，2，6，7]，意味着2-6之间存在大量的空洞，假设通过上面的计算最终X得到为2-6之间的数字，尤其2-6不存在，所以x会往前推，最终取到6。这样会导致取到6的概率比取到其他值概率更大。

方法2

1. 取得整个表的行数，并记为 C。

2. 取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。

3. 再用 limit Y,1 取得一行。

步骤1会扫描C行，步骤3的做法就是按顺序一个一个读出来，丢掉前Y个，然后把下一个记录作为返回结果，因此这一步需要扫描Y + 1行。那么总共需要扫描C+Y+1行，执行代价比方法一要高。由于不管存不存空洞的情况，这个方法也能保证概率均匀分布。

如果随机到的Y和C差不多，相当于和order by rand()扫描行数差不多，但是算法代价还是要比order by rand（）小很多：

• 方法2不需要使用的到临时表。
• 方法2也不需要进行排序，只需要遍历主键索引读。

如果我们要使用这个方法来选择3个随机的数：

1. 取得整个表的行数，记为 C；

2. 根据相同的随机方法得到 Y1、Y2、Y3；

3. 再执行三个 limit Y, 1 语句得到三行数据。

参考

MySQL 实战 45 讲-极客时间 (opens new window)