order by 排序原理及性能优化

排序是我们在写项目中经常用的sql语句的关键字。 往往order by 用不好的话也会对sql性能有一定的影响。我们现在就来介绍一下他的执行过程，并介绍一下优化。

正言

首先我们来举个例子，假设你要查询城市是”杭州“的所有人的名字，并且按照姓名排序返回前1000个人的姓名、年龄。
表定义：

这时，你的sql语句可以这么写：

这个语句看上去逻辑很清晰，我们还是需要看看他的sql执行过程。

执行过程一：全字段索引

我们需要在city上添加一个索引。 我们在执行explain命令来看看这个语句的执行情况。

Extra这个字段中的”Using filesort“ 表示的就是需要排序，MySql会给每个线程分配分配一块内存用于排序，称为sort_buffer。

下面是图示：

从图中可以看到，满足city=‘杭州’条件的行，是从ID_X到ID_(X+N)的这些记录。
通常情况下，这个语句执行流程如下所示：

1. 初始化sort_buffer, 确定放入name、city、age这三个字段;
2. 从索引city找到第一个满足city=“杭州” 条件的主键id，也就是图中的ID_X;
3. 到主建id索引取出整行，取name、city、age 三个 字段的值，存入sort_buffer中;
4. 从索引city取下一个记录的主键id ;
5. 重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y;
6. 对sort_buffer 中的数据按照字段name做快译排序;
7. 按照排序结果取前1000行返回给客户端;

我们暂且把这个排序过程，称为全字段排序，执行流程的示意图

图中“按name排序 ”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size;

routId 排序

在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中执行的。但这个算法有一个问题，如果查询返回的数据字段多，那么内存中放的数据就要分成多个临时文件，排序的性能就会很差。

修改参数 max_length_for_sort_data,是MySql中专门控制用于排序的行数据的长度的一个参数，它的意思是，如果单行的长度超过这个值 ，MySQL就认为单行太大，要换一个算法。

city,name,age这三个字段的定义总长度是36，我把max_length_for_sort_data设置为16，那么放入sort_buffer的字段只有要排序的列(即 name 字段 是order by 后面的关键字)和主键id。
但这时，排序的结果就因为少了city和age字段的值 ，不能直接返回了，整个执行的流程就变成 如下所示的样子:

1. 初始化sort_buffer，确定放入两个字段，即name和id;
2. 从索引city找到第一个满足city='上海’条件的主键,也就是图中的ID_X;
3. 到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中;
4. 从索引city取下一个记录的主键id;
5. 重复3、4步骤，直到找到不city!=‘上海’;
6. 对sort_buffer中的数据按照name进行排序;
7. 遍历排序结果，取前1000行，并按照id的值回到原表中的city、name和age三个字段返回给客户端。
   

对排序进行优化

alter table t add index_city(city,name);

这样数据的索引就有按照name进行排序。 这样整个排序过程就变成了下面这样。

1. 从索引(city,name)找到第一个满足city="杭州"条件的主键id;
2. 到主键id索引取出整行,取name、city、age三个字段的值，作为结果集的一部分直接返回。
3. 从当前索引取出一个索引值，id.
4. 重复2、3步。
   

在使用explain 来分析这个sql

可以看到extra字段中没有Using filesort了，也就是不需要排序了。
由于覆盖索引的原则，我们可以在进行优化。

alter table t add index_city(city,name,age);

在使用explain 对sql进行分析

发现使用Using index，表示的就是使用了覆盖索引，性能上会快很多。

总结

创建索引在添加和修改数据的时候是会对性能有损耗的， 在优化的时候要考虑使用的场景。

文中的内容借鉴了，极客时间中的Mysql的内容。

猿来衣舍

这是博主开的淘宝小店，主要经营舒适保暖的服饰，有袜子、主题卫衣、保暖衣。欢迎大家选购。

我们也在着手开发脚手架，到时候会做为商店福利的赠送给老用户

猿来衣舍
猿来衣舍
猿来衣舍
猿来衣舍

打开淘宝搜索 “猿来衣舍”这四个字就可以搜到小店，希望大家多多支持。

一个人能够走多远，关键在于与谁同行，我用跨越山海的一路相伴，希望得到您用金钱的称赞。