如何正确显示随机消息
内存临时表
使用oder by rand()
对于InnoDB表,执行全字段排序会减少磁盘访问,因此会被优先选择。
对于内存表,回表过程只是简单地根据数据行的位置,直接访问内存得到数据,根本不会导致多访问磁盘,从而选择rowid排序。
select word from words order by rand() limit 3;
整个执行过程
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 中的两个字段里。
6.这个过程要对内存临时表做全表扫描,此时扫描行数增加 10000,变成了 20000。在 sort_buffer 中根据 R 的值进行排序。注意,这个过程没有涉及到表操作,所以不会增加扫描行数。
7.排序完成后,取出前三个结果的位置信息,依次到内存临时表中取出 word 值,返回给客户端。这个过程中,访问了表的三行数据,总扫描行数变成了 20003
如果你创建的表没有主键,或者把一个表的主键删掉了,那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 来作为主键。
对于有主键的InnoDB表来说,整个rowid就是主键ID。
对于没有主键的InnoDB表来说,这个rowid就是系统生成的。
order by rand() 使用了内存临时表,内存临时表排序的时候使用了rowid排序算法。
磁盘临时表
tmp_table_size限制了内存表的大小,默认值是16M。如果临时表的大小超过了tmp_table_size,那么内存临时表就会转成磁盘临时表。
磁盘临时表默认使用InnoDB,是由参数Internal_tmp_disk_storage_engine取控制。
MySQL5.6引入:优先队列排序算法,不需要使用临时文件的算法(归并排序),而是采用了优先队列排序算法。
总之,不管使用那种类型的临时表,order_by_rand()这种写法都会让计算过程非常复杂,需要大量的扫描行数。
随机排序方法
1.取得整个表的行数,并记为C.
2.取得Y = floor(C*rand())。
3.再利用你limit Y,1取得一行。