Skip to main content
 首页 » 数据库

MySQL高级 之 order by、group by 优化

2022年07月18日28exmyth

参考:  https://blog.csdn.net/wuseyukui/article/details/72627667 

order by示例

示例数据:


Case 1


Case 2


Case 3


Case 4


结论:order by子句,尽量使用Index方式排序,在索引列上遵循索引的最佳左前缀原则。
复合(联合)索引形如 key (‘A1’,’A2’,’A3’ ),排序的思路一般是,先按照A1来排序,A1相同,然后按照A2排序,以此类推,这样对于(A1),(A1,A2), (A1,A2,A3)的索引都是有效的,但是对于(A2,A3)这样的索引就无效了。尽量避免因索引字段的缺失 或 索引字段顺序的不同 引起的FileSort排序。

order by 总结

FileSort排序算法

算法一:双路排序算法

只利用ORDERBY子句中包括的列对象进行排序(适用于有BLOB、TEXT类型的列对象参与的排序)

MySQL4.1之前的排序算法,完整实现过程如下:

1) 按索引键或全表扫描的方式,读取所有的元组,不匹配WHERE子句的元组被跳过;第一步需要从存储读入数据,引发I/O操作。

2) 对于每一行,在缓冲区中存储一对值(对值,包括排序关键字和元组指针)。缓冲区的大小是系统变量的sort_buffer_size设定的值。

3) 当缓冲区已满,运行快排算法(快速排序,qsort)对一个块中的数据进行排序,将结果存储在一个临时文件。保存一个指向排序后的块的指针(如果第二步所说的对值都能被缓冲区容纳,则不会创建临时文件)。

4) 重复上述步骤,直到所有的行已经被读取。

5) 执行一个多路归并操作(操作对象是第三步生成的每一个有序的块)汇集到“MERGEBUFF域”,然后存放到在第二个临时文件中。重复操作,直到第一个文件的所有块归并后存入到第二个文件;“MERGEBUFF域”是代码sql_sort.h中定义的宏,值为7。

6) 重复以下操作(第7步和第8步),直到留下少于“MERGEBUFF2域”标明的块数为止;“MERGEBUFF2域”是代码sql_sort.h中定义的宏,值为15。

7) 在最后一次多路归并操作中,把元组的指针(排序关键字的最后部分)写入到一个结果文件。

8) 在结果文件中,按照排列的顺序使用元组指针读取元组(为了优化这项操作,MySQL读入元组指针进入一个大的块,对块中元组指针进行排序而不是直接对数据排序,然后再用有序的元组指针获取元组到元组缓存,元组缓冲区的大小由read_rnd_buffer_size参数控制)。第8步需要从存储读入数据,引发I/O操作。

算法二:单路排序算法

除利用ORDERBY子句中包括的列对象外,还利用查询目标列中的所有列对象进行排序(适用于除BLOB、TEXT类型外的所有的其他类型的排序)

MySQL4.1之后出现的改进算法,减少一次I/O,需要增加缓冲区大小容纳更多信息。其具体实现过程如下:

1) 获取与WHERE子句匹配的元组。这一步需要从存储读入数据,引发I/O操作。

2) 对于每一个元组,记录排序键值、行的位置值、查询所需的列。这一步记录更多内容,需要更大缓存,内存存储一条元组的信息的长度比算法一的“对值”大许多,这可能引发排序速度问题(排序对象的长度变长,但是内存有限,所以就需把一次内存排序变为多次,进而影响排序的速度),为了控制这个问题,MySQL引入一个参数“max_length_for_sort_data”,如果这一步得到的元组长度大于这个值,则不使用算法二。需要MySQL的使用者特别注意的是,在排序中,如果存在“很高磁盘I/O和很低的CPU利用率”的现象,则需要考虑调整“max_length_for_sort_data”的大小以变更换排序算法。

3) 按照排序的键值,对元组(元组是第二步的结果)进行排序。

算法二直接从缓冲区中的排序的元组中获取有序的列信息等(查询的目的对象),而不是第二次访问该表读取所需的列。相比算法一减少一次I/O。

FileSort优化策略

当无法使用索引列排序时,为了提高Order By的速度,应该尝试一下优化:
1、避免使用 “select * ” 。查询的字段越多导致元组长度总合可能

超过max_length_for_sort_data的设置,导致无法使用单路排序算法,只能用双路排序算法。
超过sort_buffer_size的设置,超出后会创建tmp文件进行合并,导致多次IO
2、适当增大sort_buffer_size参数的设置

3、适当增大max_length_for_sort_data参数的设置

group by 示例

示例:

group by 总结

group by与order by的索引优化基本一样,group by实质是先排序后分组,也就是分组之前必排序,遵照索引的最佳左前缀原则可以大大提高group by的效率。

当无法使用索引列排序时,适当增大sort_buffer_size参数 + 适当增大max_length_for_sort_data参数可以提高filesort排序的效率。注意:可能会出现Using temporary,也就是说mysql在对查询结果排序时使用了临时表。

where高于having,能写在where限定条件中的就尽量写在where中。
---------------------
作者:走慢一点点
来源:CSDN
原文:https://blog.csdn.net/wuseyukui/article/details/72627667
版权声明:本文为博主原创文章,转载请附上博文链接!


本文参考链接:https://www.cnblogs.com/maohuidong/p/10983096.html