全字段排序
- MySQL会为每个线程分配一个内存(sort_buffer)用于排序该内存大小为sort_buffer_size
1>如果排序的数据量小于sort_buffer_size,排序将会在内存中完成
2>如果排序数据量很大,内存中无法存下这么多数据,则会使用磁盘临时文件来辅助排序,也称外部排序
3>在使用外部排序时,MySQL会分成好几份单独的临时文件用来存放排序后的数据,然后在将这些文件合并成一个大文件
按name排序”这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和参数sort_buffer_size。
MySQL将需要排序的数据分成12份,每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。
rowid排序
mysql会通过遍历索引将满足条件的数据读取到sort_buffer,并且按照排序字段进行快速排序
1>如果查询的字段不包含在辅助索引中,需要按照辅助索引记录的主键返回聚集索引取出所需字段
2>该方式会造成随机IO,在MySQL5.6提供了MRR的机制,会将辅助索引匹配记录的主键取出来在内存中进行排序,然后在回表
3>按照情况建立联合索引来避免排序所带来的性能损耗,允许的情况下也可以建立覆盖索引来避免回表
两种排序对比
全字段排序
1.通过索引将所需的字段全部读取到sort_buffer中
2.按照排序字段进行排序
3.将结果集返回给客户端
缺点:
1.造成sort_buffer中存放不下很多数据,因为除了排序字段还存放其他字段,对sort_buffer的利用效率不高
2.当所需排序数据量很大时,会有很多的临时文件,排序性能也会很差
优点:MySQL认为内存足够大时会优先选择全字段排序,因为这种方式比rowid 排序避免了一次回表操作
rowid排序
1.通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据,max_length_for_sort_data
2.只将需要排序的字段和主键读取到sort_buffer中,并按照排序字段进行排序
3.按照排序后的顺序,取id进行回表取出想要获取的数据
4.将结果集返回给客户端
优点:更好的利用内存的sort_buffer进行排序操作,尽量减少对磁盘的访问
缺点:回表的操作是随机IO,会造成大量的随机读,不一定就比全字段排序减少对磁盘的访问
随机排序
首先,你会想到用order by rand()来实现这个逻辑。
mysql> select word from words order by rand() limit 3;
相关执行流程
-
创建一个临时表。这个临时表使用的是memory引擎,表里有两个字段,第一个字段是double类型,为了后面描述方便,记为字段R,第二个字段是varchar(64)类型,记为字段W。并且,这个表没有建索引。
-
从words表中,按主键顺序取出所有的word值。对于每一个word值,调用rand()函数生成一个大于0小于1的随机小数,并把这个随机小数和word分别存入临时表的R和W字段中,到此,扫描行数是10000。
-
现在临时表有10000行数据了,接下来你要在这个没有索引的内存临时表上,按照字段R排序。
-
初始化 sort_buffer。sort_buffer中有两个字段,一个是double类型,另一个是整型。
-
从内存临时表中一行一行地取出R值和位置信息(我后面会和你解释这里为什么是“位置信息”),分别存入sort_buffer中的两个字段里。这个过程要对内存临时表做全表扫描,此时扫描行数增加10000,变成了20000。
-
在sort_buffer中根据R的值进行排序。注意,这个过程没有涉及到表操作,所以不会增加扫描行数。
-
排序完成后,取出前三个结果的位置信息,依次到内存临时表中取出word值,返回给客户端。这个过程中,访问了表的三行数据,总扫描行数变成了20003。
order by rand()使用了内存临时表,内存临时表排序的时候使用了rowid排序方法。
排序确实没有用到临时文件,采用是MySQL 5.6版本引入的一个新的排序算法,即:优先队列排序算法。接下来,我们就看看为什么没有使用临时文件的算法,也就是归并排序算法,而是采用了优先队列排序算法。
其实,我们现在的SQL语句,只需要取R值最小的3个rowid。但是,如果使用归并排序算法的话,虽然最终也能得到前3个值,但是这个算法结束后,已经将10000行数据都排好序了。
也就是说,后面的9997行也是有序的了。但,我们的查询并不需要这些数据是有序的。所以,想一下就明白了,这浪费了非常多的计算量。
而优先队列算法,就可以精确地只得到三个最小值,执行流程如下:
对于这10000个准备排序的(R,rowid),先取前三行,构造成一个堆;
(对数据结构印象模糊的同学,可以先设想成这是一个由三个元素组成的数组)
取下一个行(R’,rowid’),跟当前堆里面最大的R比较,如果R’小于R,把这个(R,rowid)从堆中去掉,换成(R’,rowid’);
重复第2步,直到第10000个(R’,rowid’)完成比较。
这里我简单画了一个优先队列排序过程的示意图。
总之,不论是使用哪种类型的临时表,order by rand()这种写法都会让计算过程非常复杂,需要大量的扫描行数,因此排序过程的资源消耗也会很大。
随机排序方法
,如果只随机选择1个word值,可以怎么做呢?思路上是这样的:
取得这个表的主键id的最大值M和最小值N;
用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
取不小于X的第一个ID的行。
我们把这个算法,暂时称作随机算法1。这里,我直接给你贴一下执行语句的序列:
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个id,分别是1、2、4、5,如果按照上面的方法,那么取到 id=4的这一行的概率是取得其他行概率的两倍。
如果这四行的id分别是1、2、40000、40001呢?这个算法基本就能当bug来看待了。
所以,为了得到严格随机的结果,你可以用下面这个流程:
取得整个表的行数,并记为C。
取得 Y = floor(C * rand())。 floor函数在这里的作用,就是取整数部分。
再用limit Y,1 取得一行。
我们把这个算法,称为随机算法2。下面这段代码,就是上面流程的执行语句的序列。
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;
由于limit 后面的参数不能直接跟变量,所以我在上面的代码中使用了prepare+execute的方法。你也可以把拼接SQL语句的方法写在应用程序中,会更简单些。
这个随机算法2,解决了算法1里面明显的概率不均匀问题。
MySQL处理limit Y,1 的做法就是按顺序一个一个地读出来,丢掉前Y个,然后把下一个记录作为返回结果,因此这一步需要扫描Y+1行。再加上,第一步扫描的C行,总共需要扫描C+Y+1行,执行代价比随机算法1的代价要高。
当然,随机算法2跟直接order by rand()比起来,执行代价还是小很多的。
你可能问了,如果按照这个表有10000行来计算的话,C=10000,要是随机到比较大的Y值,那扫描行数也跟20000差不多了,接近order by rand()的扫描行数,为什么说随机算法2的代价要小很多呢?我就把这个问题留给你去课后思考吧。
现在,我们再看看,如果我们按照随机算法2的思路,要随机取3个word值呢?你可以这么做:
取得整个表的行数,记为C;
根据相同的随机方法得到Y1、Y2、Y3;
再执行三个limit Y, 1语句得到三行数据。
我们把这个算法,称作随机算法3。下面这段代码,就是上面流程的执行语句的序列。
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;