mysql - 第5课 - Order by与Group by优化

注：当前测试mysql版本：mysql5.7，编码utf8mb4

测试数据脚本：

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `std_name` varchar(30) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  `class_id` int(11) unsigned NOT NULL,
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_std_age` (`age`),
  KEY `idx_std_name_age_class` (`std_name`,`age`,`class_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100766 DEFAULT CHARSET=utf8mb4;

--添加测试数据的存储过程
drop procedure if exists proc_insert_student; 
create procedure proc_insert_student()        
begin
  declare i int;                    
  set i=1;                          
  while(i<=100000)do                 
    insert into t_student(std_name,age,class_id) values(CONCAT('Li Lei',i), (i mod 120)+1 ,(i mod 3)+1);   
    set i=i+1;                       
  end while;
end;

-- 执行存储过程
call proc_insert_student();

Order by与Group by优化

1.explain select * from t_student t where t.std_name = 'Li Lei100' and t.class_id=2 order by age;

utf8mb4编码每个字符占用4个字节，2个字节用来记录字符串长度，所以4*30 + 2 = 122。

由此知道sql只走了std_name这一列索引，因为根据最左匹配原则，std_name后面没有age条件，中间断掉了，所以只走了std_name一列索引。但是从extra可以看出，排序使用了age索引，因为没有 using filesort

 

2. explain select * from t_student t where t.std_name = 'Li Lei100' order by t.class_id;

从using filesort可以看出，使用文件排序，这个sql只用了std_name一列索引，排序没用索引。因为根据最左前缀法则，索引的中间的age字段断了。

 

3.explain select * from t_student t where t.std_name = 'Li Lei100' order by t.age,t.class_id;

extra看到，没有using filesort，可以推断是走了索引排序的，走索引排序效率更高。

 

4.explain select * from t_student t where t.std_name = 'Li Lei100' order by t.class_id,t.age;

看到有using filesort，这时候使用文件排序，没有走索引排序。只是把排序字段的位置调换了，就不能走索引排序了，还是因为最左前缀法则的中间索引字段断了。

 

5.explain select * from t_student t where t.std_name = 'Li Lei100' order by t.age asc,t.class_id desc;

age升序，class_id降序就不能走索引排序。

 

6.  explain select * from t_student t where t.std_name = 'Li Lei100' order by t.age desc;

如果只用age倒序排序，extra没有看到using filesort，说明是可以走索引排序的。

 

7.explain select * from t_student t where t.std_name in ('Li Lei','Han Mei') order by t.age,t.class_id;

使用in查询的时候没有走index排序，因为满足in查询多个参数后，age后面的索引可能就无序了，需要using filesort查询。

 

8.explain select t.* from t_student t where t.std_name in ('Li Lei100','Han Mei') order by t.age,t.class_id;

使用覆盖索引也看到using filesort，也是没走索引排序。

Using filesort文件排序原理详解

using filesort分两种排序方式

单路排序：

一次性取出满足条件行的所有字段，然后在 sort_buffer 中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >

双路排序：

（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort_buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >

 

sort_buffer 是mysql分配的一块内存，专门用来做排序，可以通过参数max_length_for_sort_data设置大小。

 

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

• 如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；
• 如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模·式。

 

单路排序过程：

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

 

双路排序过程：

1. 从索引 name 找到第一个满足 name = 'Li Lei' 的主键id
2. 根据主键 id 取出整行，把排序字段 age,class_id 和主键 id 这3个字段放到 sort_buffer  中
3. 从索引 name 取下一个满足 name = 'Li Lei' 记录的主键 id
4. 重复 2、3 直到不满足 name = 'Li Lei'
5. 对 sort_buffer 中的字段 和主键 id 按照字段 age,class_id 进行排序
6. 遍历排序好的 id 和字段 age,class_id，按照 id 的值回到原表中取出 所有字段的值返回给客户端

 

因为双路排序比单路排序要多一步，所以单路排序效率更高。

 

但是实际使用过程中要根据硬件大小进行参数配置。

如果服务器内存较低，sort_buffer 配置的比较小，可以适当把 max_length_for_sort_data 配置小点，这样优化器会选择使用双路排序算法。

如果硬件容许sort_buffer 可以配置的大点，可以适当增大 max_length_for_sort_data 的值，这样化器优先选择单路排序。

 

注意：如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

总结： 

1.MySQL支持两种方式的排序 Using filesort 和 Using index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

2.order by满足两种情况会使用Using index。

    1) order by语句使用索引最左前列。

    2) 使用where子句与order by子句条件列组合满足索引最左前列。

3.尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4.如果order by的条件不在索引列上，就会产生Using filesort。

5.能用覆盖索引尽量用覆盖索引

6.group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。