order by语句的执行优化分析

目录

1、全字段排序

2、部分字段排序+回表避免外排序和临时文件

3、联合索引避免排序

4、覆盖索引避免回表

5、问题与思考

6、小结

---

前言

    Mysql设计原则之一： 如果内存够，就使用内存，尽量减少磁盘的访问。

 

问题导读：

• 了解使用索引和不使用索引对order by语句性能的影响？
• 体会索引对一个order by sql查询优化的过程？

建表语句：

CREATE TABLE `USER` (
  `id` int(11) NOT NULL,
  `orderId` bigint DEFAULT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `city` varchar(45) NOT NULL,
  `email` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `orderId` (`orderId`)
) ENGINE=InnoDB

思考：通过一个实例来分析sql语句order by的执行过程，分析一个排序sql的优化过程，到底在避免什么，追求什么？

sql查询语句：查询城市city为“北京”的人的姓名name和年龄age，以姓名排序，返回前1000条？

select city,name,age from t where city='北京' order by name limit 1000  ;

为了避免全表扫描，这里给city添加了索引。

ALTER TABLE `USER` ADD INDEX city_index ( `city` );

1、全字段排序

正常order by的查询流程：全字段 排序

• 1、初始化sort_buffer，确定放入name，city，age这三个字段；
• 2、从索引city找到第一个满足city=“北京”条件的主键id；
• 3、在主键索引中得到整行的记录，取出name，age，city放入 sort_buffer中；
• 4、从city索引中取下一个记录的主键id；
• 5、重复2/3步骤直到取出所有的满足city =“ 北京”的数据；
• 6、对 sort_buffer中的数据按 name字段 进行 快速排序；
• 7、遍历结果返回前1000条数据给客户端；

注: 如果内存排序的sort_buffer大小不足，这其中会用到临时文件：通过extra显示：file sort；

由于使用了order by，得先排序得到结果，然后才发出去，如果读了数据直接发，那肯定不行，那是错误的结果。所以要排序了以后再发，这时候就需要中间数据结构， sort buffer；

方案小结：

• 缺点：如果排序的数据量大，则会使用大量临时文件排序，甚至是外排序，外排序是不可接受的，性能低；
• 思考：有没有一种方法可以避免使用大量的临时文件排序，消除外排序呢？

2、部分字段排序+回表避免外排序和临时文件

2.1、优化查询方案一：rowid排序，部分字段排序后，再通过id回表查询：

在上面的算法中，有一个缺点，如果排序的数据量比较大，单行的量比较大，这时候就需要有很多的临时文件，这个是比价耗时的，性能会很差。

如果myslq认为单行的量比较大会采用另一种排序算法？

SET max_length_for_sort_data = 16;  //用这个值来限制单行的大小kb；

这个时候放入sort_buffer中的数据只有：id和name，age和city就缺少了，不能返回了，需要回表进行查询；

这个时候执行流程如下：

• 1、初始化sort_buffer，确定放入name，id这两个字段；
• 2、从索引city找到第一个满足city=“北京”条件的主键id；
• 3、在主键索引中得到整行的记录，取出name，id放入sort_buffer中；
• 4、从city索引中取下一个记录的主键id；
• 5、重复2/3步骤直到取出所有的满足city =“ 北京”的数据；
• 6、对sort_buffer中的数据进行name快速排序；
• 7、遍历排序结果，取前1000行，并按ID的值 回原表中取出name，city，age三个字段 返回给客户端；【 多了一次回表的操作】

tips：

• 假如北京的用户查出来又10000，则总共扫描行数：4000+ 1000回表 = 5000
• 数据库会先按照全字段排序，内存实在不够才会安rowid排序；
• 同时也体现了数据库的一个设计细想：当内存够的时候，尽量使用内存而不是访问磁盘。

方案小结：

• 优点：减少了排序字段占用的内存，避免了大量的临时文件的排序，消除了外排序；
• 缺点：需要回表查询，此操作也是比较消耗性能；
• 思考：既然排序这么消耗性能，能不能避免排序呢，我们知道索引是有序的？

 

其实并不是所有的order by都需要排序的？需要排序是因为原来的数据就是无序的，如果有序就不用排序了。

如果说按字段city取出来的数据就是按照name排序的，那就不用排序了。

3、联合索引避免排序

优化查询方案二：使用联合索引：name索引，自然有序，避免排序；

alter table USER add index city_user(city, name);

建立这 个联合索引后，name会按照city这个索引字段进行排序；

这时候的执行流程变为：

• 1、从联合索引中取出city的主键id；
• 2、通过主键回表查询得到name，age，city的值，作为结果集的一部分直接返回给客户端；
• 3、从索引(city, name)  取下一个记录索引主键id；
• 4、重复2/3，直到查到第1000条记录，或者是不满足city=“杭州”的条件时循环结束；

方案小结：

• 优点 ：利用索引的特性，避免了排序；
• 缺点：查询需要回表，比较消耗性能；
• 思考：能不能不回表查询呢？

4、覆盖索引避免回表

优化查询方案三：覆盖索引，避免回表查询

这时候虽然不用排序，但是还需要回表，所以这里可以进一步优化：通过 覆盖索引，可以避免回表查询，进一步提高效率：

所以我们建立一下索引：

Alter table USER add index city_user_age(city,name,age);

此时执行流程：

• 1、从联合索引中得到city，name，age字段；
• 2、将得到的数据作为结果集的一部分返回给客户端；
• 3、重复1/2步骤直到city不等于杭州，或者循环结束为止；

方案小结： ：利用联合索引，覆盖索引解决了排序和回表查询的问题。

5、问题与思考

问题一： sql的优化过程，到底在避免什么，追求什么？

从sql的排序优化过程可以看出，sql的优化的核心追求目的：尽最大可能减少io，尽量使用内存。避免排序，避免临时文件，避免外排序。当内存够的时候，尽量使用内存而不是访问磁盘。

问题二：指定使用索引就一定使用吗？不一定，取决于成本分析，优化器的选择。

1、无条件查询如果只有order by create_time，即便create_time上有索引，也不会使用到。

select * from table order by create_time;

因为优化认为走二级索引再去回表成本比全表扫描更高；

所以走全表扫描，然后选择全字段排序或者是rowid排序来进行排序；

select * from table order by create_time limit m;

但是如果加上limit m就可以走索引的，因为优化器认为，根据索引的有序性回表查询数据，得到m条就立即停止，终止循环，成本比全表扫描小，选择走二级索引；具体是否走索引是根据sql执行的成本分析的出来的，所以sql的执行，但是不一定按照sql指定的索引来执行。

问题三：全字段排序过程

• 1.通过索引将所需的字段全部读取到sort_buffer中
• 2.按照排序字段进行排序
• 3.将结果集返回给客户端

缺点：

• 1.造成sort_buffer中存放不下很多数据，因为除了排序字段还存放其他字段，对sort_buffer的利用效率不高
• 2.当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差

优点：

• MySQL认为内存足够大时会优先选择全字段排序，因为这种方式比rowid 排序避免了一次回表操作；

问题四：rowid排序过程

• 1.通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据， max_length_for_sort_data
• 2.只将需要排序的字段和主键读取到sort_buffer中，并按照排序字段进行排序；
• 3.按照排序后的顺序，取id进行回表取出想要获取的数据；
• 4.将结果集返回给客户端；

优点：更好的利用内存的sort_buffer进行排序操作，尽量减少对磁盘的访问；

缺点：回表的操作是随机IO，会造成大量的随机读，不一定就比全字段排序减少对磁盘的访问；

问题五：什么是sort_buffer，排序为啥消耗性能？

  4.1.MySQL会为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size；

• 1>如果排序的数据量小于sort_buffer_size，排序将会在内存中完成；
• 2>如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序；
• 3>在使用外部排序时，MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件；

  4.2. MySQL 会通过遍历索引将满足条件的数据读取到sort_buffer，并且按照排序字段进行快速排序；

• 1>如果查询的字段不包含在辅助索引中，需要按照辅助索引记录的主键返回聚集索引取出所需字段
• 2>该方式会造成随机IO，辅助索引匹配记录的主键取出来在内存中进行排序，然后再回表；
• 3>按照情况建立联合索引来避免排序所带来的性能损耗，允许的情况下也可以建立覆盖索引来避免回表；

6、小结

      从上面一步步的我们知道，从---->使用外排序---->避免临时文件排序------>使用索引避免排序---->使用覆盖索引避免回表，一步步的优化，我们知道：

      sql的优化的核心追求目的：尽最大可能减少io，尽量使用内存。避免排序，避免临时文件，避免外排序。

      此外，sql的执行依赖数据库优化器的优化和执行成本分析，并不一定是按照sql本身去执行。

 

 

 

水滴石穿，积少成多。学习笔记，内容简单，用于复习，梳理巩固，原内容2月有更新。

 

##参考资料，

《Innodb存储引擎》

《MySql实战45讲详解》--丁奇