Sunskey

日拱一卒,不期而至

0%

如何正确显示随机消息

如何正确显示随机消息

内存临时表

使用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取得一行。