目录
MySQL 5.6版本引入的一个新的排序算法--优先队列排序算法
mysql 随机选数据
表结构
CREATE TABLE `words` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`word` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
查询语句
select word from words order by rand() limit 3;
先总结:order by rand()使用了内存临时表,内存临时表排序的时候使用了rowid排序方法。
临时内存表的排序
对于InnoDB表来说,执行全字段排序会减少磁盘访问,因此会被优先选择。
对于内存表,回表过程只是简单地根据数据行的位置,直接访问内存得到数据,根本不会导致多访问磁盘。优化器没有了这一层顾虑,那么它会优先考虑的,就是用于排序的行越少越好了,所以,MySQL这时就会选择rowid排序。
这条语句的执行流程是这样的
-
创建一个临时表。这个临时表使用的是memory引擎,表里有两个字段,第一个字段是double类型,为了后面描述方便,记为字段R,第二个字段是varchar(64)类型,记为字段W。并且,这个表没有建索引。
-
从words表中,按主键顺序取出所有的word值。对于每一个word值,调用rand()函数生成一个大于0小于1的随机小数,并把这个随机小数和word分别存入临时表的R和W字段中,到此,扫描行数是10000。
-
现在临时表有10000行数据了,接下来你要在这个没有索引的内存临时表上,按照字段R排序。
-
初始化 sort_buffer。sort_buffer中有两个字段,一个是double类型,另一个是整型。
-
从内存临时表中一行一行地取出R值和位置信息(rowid),分别存入sort_buffer中的两个字段里。这个过程要对内存临时表做全表扫描,此时扫描行数增加10000,变成了20000。
-
在sort_buffer中根据R的值进行排序。注意,这个过程没有涉及到表操作,所以不会增加扫描行数。
-
排序完成后,取出前三个结果的位置信息,依次到内存临时表中取出word值,返回给客户端。这个过程中,访问了表的三行数据,总扫描行数变成了20003。
MySQL的表是用什么方法来定位“一行数据”的。
有主键则用主键。
如果你创建的表没有主键,或者把一个表的主键删掉了,那么InnoDB会自己生成一个长度为6字节的rowid来作为主键。
这也就是排序模式里面,rowid名字的来历。实际上它表示的是:每个引擎用来唯一标识数据行的信息。
- 对于有主键的InnoDB表来说,这个rowid就是主键ID;
- 对于没有主键的InnoDB表来说,这个rowid就是由系统生成的;
- MEMORY引擎不是索引组织表。在这个例子里面,你可以认为它就是一个数组。因此,这个rowid其实就是数组的下标。
磁盘临时表
tmp_table_size这个配置限制了内存临时表的大小,默认值是16M。如果临时表大小超过了tmp_table_size,那么内存临时表就会转成磁盘临时表。
sort_buffer_size
max_length_for_sort_data 是MySQL中专门控制用于排序的行数据的长度的一个参数。它的意思是,如果单行的长度超过这个值,MySQL就认为单行太大,要换一个算法。,排序字段小于这个将按照rowid排序。
磁盘临时表使用的引擎默认是InnoDB,是由参数internal_tmp_disk_storage_engine控制的。
当使用磁盘临时表的时候,对应的就是一个没有显式索引的InnoDB表的排序过程。
使用归并排序算法
MySQL 5.6版本引入的一个新的排序算法--优先队列排序算法
执行流程如下:
- 对于这10000个准备排序的(R,rowid),先取前三行,构造成一个堆;
(对数据结构印象模糊的同学,可以先设想成这是一个由三个元素组成的数组)
-
取下一个行(R’,rowid’),跟当前堆里面最大的R比较,如果R’小于R,把这个(R,rowid)从堆中去掉,换成(R’,rowid’);
-
重复第2步,直到第10000个(R’,rowid’)完成比较。
优化随机排序
-
取得整个表的行数,记为C;
-
根据相同的随机方法得到Y1、Y2、Y3;
-
再执行三个limit Y, 1语句得到三行数据。
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;