MySQL order by、group by底层实现及优化（非常详细）

在Innodb引擎下Order by实现：

在MySQL中order by有两种排序方式：

1.利用有序索引获取有序数据

2.文件排序

通过explain分析查询时，利用有序索引获取有序数据显示Using index。而文件排序显示Using filesort

using index：所需的数据在index中即可全部获得，无需再到表中取数据，如果是主键索引，数据直接从表中得到。

using filesort：当我们的query中包含order by操作，而且无法利用索引完成排序操作时，Mysql Query Optimizer不得不选择相应的排序算法来实现。

1.有序索引获取有序数据：

order by主键索引时： 

由于Innodb底层是B+Tree，B+Tree的叶子节点是相互指向的、有序的，并且数据都附着在叶子结点的主键上，所以order by主键索引时，直接取出有序数据即可，效率很高。

order by普通索引时：

 索引树示意图如下：

如果查询的字段是普通索引+主键，where的条件是普通索引，order by索引或者主键时，是Using index，因为order by索引时直接从索引树取数据即可，order by主键就相当于上面的第一种情况。 如果查询了多余的字段，将会导致using filelist，这是将会直接到主键索引处扫表再进行文件排序；

其实这个地方只要明白了，无论是命中索引还是命中主键，只要是在一颗树上对数据进行有序查找，就是Using index。具体sql使用explain具体分析，理解了mysql底层的存储结构，对于order by底层就会很清晰。

2.文件排序

这个filesort并不是通过磁盘文件进行排序，而只是告诉我们进行了一个排序操作。即在MySQL Query Optimizer 所给出的执行计划(通过 EXPLAIN 命令查看)中被称为文件排序（filesort）

文件排序是通过相应的排序算法，将取得的数据在内存中进行排序，MySQL需要将数据在内存中进行排序，所使用的内存区域也就是我们通过sort_buffer_size系统变量设置的排序区。这个排序区是每个线程独享的，所以同一时刻在MySQL中可能存在多个sort buffer内存区域。

双路排序（常规排序）：

首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer中进行排序。

假设按照brithday排序brithday不是索引，首先拿出查询的所有的brithday与指针，指针指向brithday所在的行，放入内存，brithday与指针一一对应，然后对brithday排序，排序后再根据指针拿到每一行数据，再组装返回结果。 需要进行两次IO效率低（这里的指针就是主键）

详细过程：

(1).从表t1中获取满足WHERE条件的记录
(2).对于每条记录，将记录的主键+排序键(id,col2)取出放入sort buffer
(3).如果sort buffer可以存放所有满足条件的(id,col2)对，则进行排序；否则sort buffer满后，进行排序并写到临时文件中。(排序算法采用的是快速排序算法)
(4).若排序中产生了临时文件，需要利用归并排序算法，保证临时文件中记录是有序的
(5).循环执行上述过程，直到所有满足条件的记录全部参与排序
(6).扫描排好序的(id,col2)队，即sort buffer，并利用主键id去取SELECT需要返回的其他列(col1,col2,col3)
(7).将获取的结果集返回给用户。

是否使用临时文件主要看sort buffer是否能容下需要排序的(id,col2)的结果集，这个buffer的大小由sort_buffer_size参数控制。此外一次排序还需要两次IO，一次是取排序字段(id,col2)到sort buffer中，第二次是通过上面取出的主键id再来取其他所需要返回列(col1,col2,col3)，由于返回的结果集是按col2排序，因此id是乱序的，通过乱序的id取(col1,col2,col3)时会产生大量的随机IO。对于第二次IO取MySQL本身会优化，即在取之前先将主键id排序，并放入缓冲区，这个缓存区大小由参数read_rnd_buffer_size控制，然后有序去取记录，将随机IO转为顺序IO。

单路排序（优化排序）：

一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。第一次copy所有要返回的字段到内存，然后在内存中拆分出brithday与指针，将brithday排序，排序完后通过指针找到指向的内存数据，再把内存按照指针排序，返回result，只与硬盘做了一次IO，效率较高，但是比较消耗内存，其实就是空间换时间。

常规排序方式除了排序本身，还需要额外两次IO。单路排序方式相对于常规排序，减少了第二次IO。主要区别在于，一次性取出sql中出现的所有字段放入sort buffer中而不是只取排序需要的字段(id,col2)。由于sort buffer中包含了查询需要的所有字段，因此排序完成后可以直接返回，无需二次取数据。这种方式的代价在于，同样大小的sort buffer，能存放的(col1,col2,col3)数目要小于(id,col2)，如果sort buffer不够大，可能导致需要写临时文件，造成额外的IO。当然MySQL提供了参数max_length_for_sort_data，只有当排序sql里出现的所有字段类型大小总和小于max_length_for_sort_data时，才能利用优化排序方式，否则只能用常规排序方式。

优先队列排序：

 为了得到最终的排序结果，我们都需要将所有满足条件的记录进行排序才能返回。那么相对于优化排序方式，是否还有优化空间呢？5.6版本针对Order by limit M，N语句，在空间层面做了优化，加入了一种新的排序方式--优先队列，这种方式采用堆排序实现。堆排序算法特征正好可以解limit M,N 这类排序的问题，虽然仍然需要所有字段参与排序，但是只需要M+N个元组的sort buffer空间即可，对于M，N很小的场景，基本不会因为sort buffer不够而导致需要临时文件进行归并排序的问题。对于升序，采用大顶堆，最终堆中的元素组成了最小的N个元素，对于降序，采用小顶堆，最终堆中的元素组成了最大的N的元素。

总结：

在MySQL4.1版本之前只有第一种排序算法双路排序，第二种算法是从MySQL4.1开始的改进算法，主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作，将两次变成了一次，但相应也会耗用更多的sortbuffer 空间。当然，MySQL4.1开始的以后所有版本同时也支持第一种算法。

MySQL主要通过比较我们所设定的系统参数 max_length_for_sort_data的大小和Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data更大，则使用第二种优化后的算法，反之使用第一种算法。所以如果希望 ORDER BY 操作的效率尽可能的高，一定要注意max_length_for_sort_data 参数的设置。sort buffer也要调大点，防止放不下而将数据分段，使用临时文件。

如何查询max_length_for_sort_data与sort buffer大小？

 

group by：

 group by前提是排序 是基于order by的，与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作，当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。

DINSTINCT:

DINSTINCT去重又是基于group by的，所以说group by与dinstinct的优化都是基于order by的优化，也就是尽量优化索引，减少排序时间。