十一、mysql如何正确地显示随机消息？

1、使用内存临时表

可以使用order by rand()来实现这个逻辑。

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

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

以上查询语句的执行流程如下：

1、创建一个临时表，这个临时表使用的是memory引擎，表里有两个字段，第一个字段是double类型，为了后面描述方便，记为字段R，第二个字段是varchar(64)类型，记为字段W。并且，这个表没有建索引；

2、从words表中，按主键顺序取出所有的word值。对于每一个word值，调用rand()函数生成一个大于0小于1的随机小数，并把这个随机小数和word分别存入临时表的R和W字段中，到此，扫描行数是10000.

3、现在临时表有10000行数据了，在这个没有索引的内存临时表上，按照字段R排序；

4、初始化sort_buffer。sort_buffer中有两个字段，一个是double类型，另一个是整型；

5、从内存临时表中一行一行地取出R值和位置信息，分别存入sort_buffer中的两个字段里。合格过程要对内存临时表做全表扫描，此时扫描行数增加10000，变成了20000.

6、在sort_buffer中根据R的值进行排序，这个过程没有涉及到表操作，所以不会增加扫描行数。

7、排序完成后，取出前三个结果的位置信息，一次到内存临时表中取出word值，返回。这个过程中，访问了表的三行数据，总扫描行数变成了20003.

 

随机排序完整流程图

 

上图中pos就是位置信息。

MySQL的表是用什么方式来定位“一行数据”？

如果一个表没有主键，那么InnoDB就会生成一个长度为6字节的rowid来作为主键；这也就是排序模式里面，rowid名字的来历，实际上它表示的是：每个引擎用来唯一标识数据行的信息。

    对于有主键的InnoDB表来说，这个rowid就是主键ID；

    对于没有主键的InnoDB表来说，这个rowid就是由系统生成的；

    MEMORY引擎不是索引组织表，可以认为它就是一个数组，因此，这个rowid其实是数组的小标。

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

 

---

2、磁盘临时表

是否所有的临时表都是内存表？

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

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

当使用磁盘临时表的时候，对应的就是一个没有显式索引的InnoDB表的排序过程。

为了复现这个过程，把tmp_table_size设置成1024，把sort_buffer_size设置成32768，把max_length_for_sort_data 设置成16.

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

 

3、随机排序方法

如果只随机选择1个word值，可以这么做：

1、取得这个表的主键id的最大值M和最小值N；

2、用随机函数生成一个最大值和最小值之间的数X=(M-N)*rand()+N;

3、取不小于X的第一个ID的行。

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;

这个方法效率很高，因为取max(id)和min(id)都是不需要扫描索引的，而第三步的select也可以用索引快速定位，可以认为就只扫描了3行。但实际上，这个算法本身并不严格满足题目的随机要求，因为ID中间可能有空洞，因此选择不同行的概率不一样，不是真正的随机。

 

4、为了得到严格随机的结果，可以使用下面这个流程：

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

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

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

mysql> select count(*) into @C from t;

set @Y = floor(@C * rand());

set @sql = concat("select * from t limit ", @Y, ",1");

prepare stmt from @sql;

execute stmt;

DEALLOCATE prepare stmt;

 

按照随机算法的思路，要随机取3个word值可以这么执行：

1、取得整个表的行数，记为C;

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

3、再执行三个limitY,1语句得到三行数据。

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；

 

---

6、小结

如果直接使用order by rand()，这个语句需要Using temporary和Using filesort，查询的执行代价比较大，所以在设计的时候要尽量避免这种写法。