如何正确的显示随机消息

问题

如果现在有一个随机显示三个单词的需求，有什么方法实现，存在什么问题以及如何改进？

内存临时表

首先，用order by rand()来实现这个逻辑：

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

这个语句的意思很直白，随机排序取前三个，虽然这个SQL语句写法很简单，但执行流程很难

mysql> explain select * from user order by rand() limit 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   11 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec

Extra字段显示Using temporary,表示需要临时表，Using fileSort表示的是需要执行排序操作

• 因此这个Extra的意思就是，需要临时表，并且需要在临时表上排序。
  这就有一个问题：

这里到底使用的是什么排序算法呢?

对于不同引擎的表来说不同，对于InnoDB表来说，优先选择全字段排序，可以减少磁盘访问，对于MyAsim表来说，回表过程只是简单的根据数据行的位置直接访问内存得到数据，不会多访问磁盘，这时候便会使用rowid排序

Mysql是用什么办法来定位“一行数据”的

如果你创建的表没有主键，或者把一个表的主键删掉了，那么InnoDB会自己生成一个6字节的rowid来作为主键

这也就是排序模式中，rowid名字的来历，实际上它表示的是：每个引擎用来唯一标识数据行的信息

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

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

• Memory引擎不是索引组织表，在这个例子里面，你可以认为它就是一个数组，因此，这个rowID就是数组的下标

那么是不是所有的临时表都是内存表呢？

1. 不是，参数tmp_table_size这个配置限制了内存临时表的大小，默认值是16M,如果临时表大小超过了tmp_table_size，那么内存临时表就会转成磁盘临时表
2. 磁盘临时表默认的引擎是INNoDB,是由参数internal_tmp_disk_storage_engine来控制的
3. 当使用磁盘临时表时，对应的就是一个没有显示索引的InnoDB表的排序过程

Mysql5.6引入的新的排序算法（数据大小超过sort_buffer_size）

优先队列算法

我们现在的SQl语句只需要取R值最小的三个rowid,但是，如果使用归并排序算法的话，虽然最终也能得到三个值，但是这个算法结束后，已经将10000行数据都排好序了

这就有点儿浪费了！！！

优先队列算法，就可以精确地之得到三个最小的值，执行流程如下（就是最大堆排序）：

1. 对于这10000个准备排序的（R,rowid）,先取前三行，构造成一个堆
2. 取下一个行(R1,rowid1),跟当前堆里面的最大的R比较，如果R1小于R,把这个(R,rowid)从堆中去掉，换成(R1,rowid1)
3. 重复第二步，直到第10000个(R10000,rowid10000)完成比较

在OPTIMIZER_TRACE结果中：

filesort_priority_queue_optimization这个部分的chosen=true,就表示使用了优先队列排序算法，这个过程不需要临时文件，因此对应的number_of_tmp_files是0

这个过程结束后，我们构建的堆里就是需要的三个最小的元素

如何正确的随机呢？

1、 取得整个表的行数，记为C
2、 取得Y = floor(C * rand)),floor函数在这里的作用，就是取整数部分
3.、再用limit Y,1 取得一行

select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat('select * from limit ',@Y,',1');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;