MYSQL中 order by、group by 如何更快

MYSQL中 order by、group by 如何更快

一、order by

1.排序的原理

按照原理划分，mysql排序方式有两种：

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

如何去判断sql执行时，用的是哪一种排序方式?

可以通过 explain 来查看sql 的执行计划，重点看Extra字段的值

如果该字段里显示是 Using index，则表示是通过有序索引直接返回有序数据。

如果该字段里显示是 Using filesort，则表示该 SQL 是通过 Filesort 进行的排序。

1.2 Filesort 是否都是在磁盘中完成排序操作的呢？

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

MySQL 中的 Filesort 并不一定是在磁盘文件中进行排序的，也有可能在内存中排序，内存排序还是磁盘排序取决于排序的数据大小和 sort_buffer_size 配置的大小。

• 如果 “排序的数据大小” < sort_buffer_size: 内存排序
• 如果 “排序的数据大小” > sort_buffer_size: 磁盘排序

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

此时就可以使用 trace 进行分析（trace 的使用方法可以复习第 1 节中 2.3 小节-trace 分析 SQL 优化器）重点关注 number_of_tmp_files，如果等于 0，则表示排序过程没使用临时文件，在内存中就能完成排序；如果大于0，则表示排序过程中使用了临时文件。

如下图，因为 number_of_tmp_files 等于 0，表示未使用临时文件进行排序，所以是内存排序。

这里解释一下上面一些参数的含义：

• rows：预计扫描的行数
• examined_rows：参与排序的行
• number_of_tmp_files：使用临时文件的个数
• sort_buffer_size：sort_buffer 的大小
• sort_mode：排序模式

再看一个用到临时文件的例子，如下图，因为 number_of_tmp_files 等于 7，所以表示使用的是磁盘排序。对于 number_of_tmp_files 等于 7 表示该 SQL 将需要排序的数据分为 7 份，然后每份单独排序，再存放在 7 个临时文件中，最后把 7 个临时文件合并成一个大的有序文件。

Sort_mode参数的意义：

Filesort 下的排序模式有三种，具体介绍如下:

• < sort_key, rowid >双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
• < sort_key, additional_fields >单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；
• < sort_key, packed_additional_fields >打包数据排序模式：与单路排序相似，区别是将 char 和 varchar 字段存到 sort buffer 中时，更加紧缩。

因为打包数据排序模式是单路排序的一种升级模式，因此重点探讨双路排序和单路排序的区别。MySQL 通过比较系统变量 max_length_for_sort_data 的大小和需要查询的字段总大小来判断使用哪种排序模式。

• 如果 max_length_for_sort_data 比查询字段的总长度大，那么使用 < sort_key, additional_fields >排序模式；
• 如果 max_length_for_sort_data 比查询字段的总长度小，那么使用 <sort_key, rowid> 排序模式。

set session optimizer_trace="enabled=on",end_markers_in_json=on;

SET max_length_for_sort_data = 20;

疑问：为什么要选择不同的排序规则而不是固定使用其中一种排序规则？

解决这个疑问需要看一下max_length_for_sort_data 的重要性，在对比三种排序规则的排序过程中的区别

例如下面这个SQL：

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

我们先看单路排序的详细过程：

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

我们再看下双路排序的详细过程：

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

2. order by 优化思路

2.1 给排序字段加上索引

对 d 字段（没有索引）进行排序的执行计划：

explain select d,id from t1 order by d;

使用的是 filesort

对 c 字段（有索引）进行排序的执行计划:

explain select c,id from t1 order by c;

根据有索引的字段排序，在 Extra 中显示的就为 Using index，表示使用的是索引排序,如果数据量比较大，显然通过有序索引直接返回有序数据效率更高。

2.2 多个字段排序优化

如果多个字段排序，可以在多个排序字段上添加联合索引来优化排序语句

2.3 先等值查询再排序的优化

平时遇到最多的情况还是根据某个条件查询出一部分数据然后做排序，这类sql的优化可以先等值查询再排序的语句，可以通过在条件字段和排序字段添加联合索引来优化此类排序语句。

3. 去掉不必要的字段

返回的字段中尽量把不必要的字段放弃掉，因为查询所有字段不走索引的原因是：扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用索引。

4. 修改参数

一开的 order by 的原理，接触到两个跟排序有关的参数：max_length_for_sort_data、sort_buffer_size。

• max_length_for_sort_data：如果觉得排序效率比较低，可以适当加大 max_length_for_sort_data 的值，让优化器优先选择全字段排序。当然不能设置过大，可能会导致 CPU 利用率过低或者磁盘 I/O 过高；
• sort_buffer_size：适当加大 sort_buffer_size 的值，尽可能让排序在内存中完成。但不能设置过大，可能导致数据库服务器 SWAP。

5. 几种无法利用上索引的情况

6.使用范围查询再排序

对于先等值过滤再排序的语句，可以通过在条件字段和排序字段添加联合索引来优化；但是如果联合索引中前面的字段使用了范围查询

例:

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

这里对上面执行计划做下解释：首先条件 a>9000 使用了索引（关注 key 字段对应的值为 idx_a_b）；在 Extra 中，看到了“Using filesort”，表示使用了 filesort 排序，并没有使用索引排序。所以联合索引中前面的字段使用了范围查询，对后面的字段排序使用不了索引排序。

原因是：a、b 两个字段的联合索引，对于单个 a 的值，b 是有序的。而对于 a 字段的范围查询，也就是 a 字段会有多个值，取到 a，b 的值 b 就不一定有序了，因此要额外进行排序。联合索引结果如下图

如上图所示，对于有 a、b 两个字段联合索引的表，如果对 a 字段范围查询，b 字段整体来看是无序的（如上图 b 的值为：1，2，3，1，2，3······）。

7. ASC 和 DESC 混合使用将无法使用索引

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

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

二、group by 的优化

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