让order by、group by查询更快

短命女 2023-03-14 13:21 19阅读 0赞

## 1. Order By原理 ##

### 1.1 MySQL的排序方式 ###

按照排序原理分，MySQL排序方式分两种：

*  通过有序索引直接返回有序数据
 *  通过Filesort进行排序

我们可以使用explain来查看该排序SQL的执行计划，主要看Extra字段：

*  如果该字段里显示是Using index，则表示通过有序索引直接返回有序数据；
 *  如果该字段里显示是Using filesort，则表示该SQL通过filesort进行排序；

### 1.2 Filesort是在内存中还是在磁盘中完成排序的？ ###

filesort并不一定是在磁盘文件中进行排序，也有可能在内存中排序，内存排序还是磁盘排序取决于排序的数据大小和sort\_buffer\_size配置的大小：

*  如果“排序的数据大小” < sort\_buffer\_size：内存排序
 *  如果“排序的数据大小” > sort\_buffer\_size：磁盘排序

### 1.3 怎么确定使用Filesort排序的SQL是在内存中还是在磁盘中进行？ ###

可以使用trace进行分析，关注number\_of\_tmp\_files字段，如果等于0，则表示排序过程没使用临时文件，在内存中就完成排序；如果大于0，则表示排序过程中使用了临时文件。如下图number\_of\_tmp\_files等于0  
，表示未使用临时文件进行排序，所以是内存排序。  
![在这里插入图片描述][20200518134610847.png]  
rows：预计扫描的行数  
examined\_row：参与排序的行  
number\_of\_tmp\_files：使用临时文件的个数  
sort\_buffers\_size：sort\_buffer的大小  
sort\_mode：排序模式

如果number\_of\_tmp\_files等于10，表示使用的是磁盘排序，该SQL将需要排序的数据分为7分，然后没分单独排序，再存放在7个临时文件中，最后把7个临时文件合并成一个大的有序文件。

### 1.4 Filesort下的排序模式 ###

Filesort下的排序模式总体上由两种：

*  双路排序：首先根据相应的条件取出响应的排序字段和可以直接定位行数据的行ID（主键ID），然后在sort buffer中进行排序，排序完后需要再次取回ID对应的其它字段；
 *  单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；

MySQL通过比较系统变量max\_length\_for\_sort\_data 的大小和需要查询的字段总大小来判断使用哪种排序模式。

*  如果max\_length\_for\_sort\_data 比查询字段的总长度大，那么使用单路排序；
 *  如果max\_length\_for\_sort\_data 比查询字段的总长度小，那么使用双路排序；

比如：

select a,c,d from t1 where a=1000 order by d;

如果是双路排序的，详细过程如下：

1.  从索引a找到第一个满足a=1000的主键ID；
2.  然后根据ID找出整行，把排序字段d和主键ID这两个字段放到sort\_buffer中；
3.  从索引a取出下一个满足a=1000记录的主键ID；
4.  重复2、3，知道不满足a=1000；
5.  对sort buffer中的字段d和主键ID按照字段d进行排序；
6.  遍历排序好的ID和字段d，按照id的值回到原表中取出a、c、d三个字段的值返回给客户端；

如果是单路排序的，详细过程如下：

1.  从索引a找到第一个满足a=1000条件的主键ID；
2.  根据主键ID取出整行，取出a、c、d三个字段的值，放入sort buffer中；
3.  从索引a找到下一个满足a=1000的主键ID；
4.  重复2、3，直到a不满足条件；
5.  对sort buffer中数据按照字段d进行排序；
6.  返回结果给客户端；

对比两个排序模式，单路排序会把所有需要查询的字段都放到sort buffer中，而双路排序只会把主键和需要排序的字段放到sort buffer中进行排序，然后再通过主键找到原表查询需要的字段。

如果内存足够的话，可以通过增大max\_length\_for\_sort\_data和sort\_buffer\_size的大小，让优化器选择单路排序，把需要的字段都放到sort buffer里，不用排序后再回原表找到其他字段，直接返回排序结果。

## 2. Order By优化 ##

### 2.1 添加合适索引 ###

*  排序字段添加索引；
 *  如果排序字段是多个字段，可以在多个排序字段上添加联合索引来优化排序语句；
 *  先等值查询再排序的语句，可以通过在条件字段和排序字段添加联合索引来优化此类排序语句；

### 2.2 去掉不必要的返回字段 ###

即便排序字段添加了索引，如果返回字段是所有字段、包含没有索引的字段、与排序字段不是联合索引的字段（该字段有索引），也不会走索引。  
原因是：扫描整个索引并找到没有索引的字段或者非联合索引的字段表扫描全表的成本更高，所有优化器放弃使用索引。

### 2.3 修改参数 ###

*  适当增大max\_length\_for\_sort\_data的值，让优化器优先选择全字段排序，不能设置过大，过大会导致IO过高；
 *  适当增大sort\_buffer\_size的大小，让排序尽可能在内存中完成，不能设置过大，过大会导致服务器内存溢出；

### 2.4 几种无法利用索引排序的情况 ###

*  范围查询后排序无法使用索引

explain select id,a,b from t1 where a>9000 order by b;

上面的条件查询是范围查询，导致排序结果不走索引。因为a、b两个字段是联合索引，对于单个a的值，b是有序的。而对于a字段的范围查询，也就是a字段会有多个值，取到a、b的值b就不一定有序了，因此要进行重新排序。  
![在这里插入图片描述][20200518145514428.png]

*  ASC和DESC混合使用无法使用索引

explain select id,a,b from t1 order by a asc,b desc;

如上SQL，对联合索引多个字段同时排序，如果一个是顺序，一个是倒序，则使用不了索引。

## 3. Group By 优化 ##

默认情况下，会对group by字段排序，因此优化方式和order by基本一致，如果目的只是分组而不用排序，可以指定order by null禁止排序。

[20200518134610847.png]: /images/20230312/f3af037746db4d92896f39a8a49c96db.png
[20200518145514428.png]: /images/20230312/27b5a29206a142969a3f36ddad85f07f.png