当前演示的数据库版本5.7
一、一个简单使用示例
先创建一张订单表
CREATETABLE `order_info` (
`id` intNOTNULL AUTO_INCREMENT COMMENT '主键',
`order_no` intNOTNULL COMMENT '订单号',
`goods_id` intNOTNULLDEFAULT'0' COMMENT '商品id',
`name` varchar(50) NOTNULL COMMENT '商品名称',
`status` intNOTNULLDEFAULT'0' COMMENT '订单状态:1待支付,2成功支付,3支付失败,4已关闭',
`pay_type` intNOTNULLDEFAULT'0' COMMENT '支付方式:1微信支付,2支付宝支付',
`price` decimal(11,2) DEFAULTNULL COMMENT '订单金额',
`pay_time` datetime DEFAULTNULL COMMENT '支付时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='订单信息表';
同时也在表里插了一些数据
![](https://i-blog.csdnimg.cn/blog_migrate/24e74c4608549ea68d0af4d93c744d5e.jpeg)
现在有一个需求: 查询商品名称是耳机,订单号按照从小到大排序的前10个,查询结果只需订单号,商品名称,订单状态。
现在我们这里执行SQL语句
select order_no, name, status from order_info where name ='耳机'orderby order_no limit 10
![](https://i-blog.csdnimg.cn/blog_migrate/48e3690d79b826478d031a9d59bd0f54.jpeg)
这条语句我们都会写,但我们知道它的执行的流程是怎么样的吗?
二、order by 原理分析
2.1、explain 分析
为了避免全表扫描,这里我们在name加上一个普通索引
altertable order_info add index idx_name(name)
我们再看下执行计划
explain select order_no, name, status from order_info where name ='耳机'orderby order_no limit 10
![](https://i-blog.csdnimg.cn/blog_migrate/fadb300692cd7a53f3faeae7f1fa54d8.jpeg)
Extra 这个字段的Using index condition 表示该查询走了索引,但需要回表查询
Extra 这个字段的 Using filesort 表示使用了内部排序
一般出现Using filesort 也是我们需要考虑优化的点。
Using filesort: 表示没有走索引排序,而是走了内部排序,这时MySQL会给每个线程分配一块内存用于排序,称为sort_buffer。
2.2 全字段排序
为了说明上面这条SQL查询语句的执行过程,我们先来看一下idx_name这个索引的示意图。
![](https://i-blog.csdnimg.cn/blog_migrate/596e64e24248fad07e2158d22abf9bcc.jpeg)
从图中可以看到,满足name='耳机'条件的行,是id = (2,3,5) 的这些记录。
整个完整流程如下图所示:
![](https://i-blog.csdnimg.cn/blog_migrate/56c2c96c6e5b163e9581f0b96cf95fb4.jpeg)
这里的执行流程:
1)、初始化sort_buffer,确定放入order_no、name、status这三个字段;
2)、从索引idx_name找到第一个满足name='耳机'条件的主键id,也就是图中的id=2;
3)、到主键索引取出整行,取order_no、name、status三个字段的值,存入sort_buffer中;
4)、从索引idx_name取下一个满足name='耳机'条件的主键id;
5)、重复步骤3、4直到name的值不满足查询条件为止;
6)、对sort_buffer中的数据按照字段name做快速排序;
7)、按照排序结果取前10行返回给客户端。
因为需要查询的字段不能够在idx_name索引中全部找到,所以才需要拿着idx_name索引中获取的主键,再到主键索引中获取其它属性。这个过程也叫回表。
回表: 就是指拿到主键再回到主键索引查询的过程。
全字段排序,就是把查询所需要的字段全部读取到sort_buffer中。
但如果查询的字段数据量很大呢,大到当前的sort_buffer放不下了,那怎么办呢?
那就不得不利用磁盘临时文件辅助排序。
实际上,sort_buffer的大小是由一个参数控制的:sort_buffer_size。
如果要排序的数据小于sort_buffer_size,排序在sort_buffer 内存中完成,如果要排序的数据大于sort_buffer_size,则借助磁盘文件来进行排序
我们可以通过下面的方法来确定一个排序语句是否使用了临时文件。
/* 打开optimizer_trace,只对本线程有效 */SET optimizer_trace='enabled=on';
/* 执行语句 */select order_no, name, status from order_info where name ='耳机'orderby order_no limit 10;
/* 查看 OPTIMIZER_TRACE 输出 */SELECT*FROM `information_schema`.`OPTIMIZER_TRACE`;
![](https://i-blog.csdnimg.cn/blog_migrate/6785cb428be752d60c6b14e94c99b624.jpeg)
number_of_tmp_files 表示使用来排序的磁盘临时文件数。
如果number_of_tmp_files>0,则表示使用了磁盘文件来进行排序。
使用了磁盘临时文件,整个排序过程又是怎样的呢?
1)、从主键索引树,拿到需要的数据,并放到sort_buffer内存块中。当sort_buffer快要满时,就对sort_buffer中的数据排序,排完后,把数据临时放到磁盘一个小文件中。
2)、继续回到主键索引树取数据,继续放到sort_buffer内存中,排序后,也把这些数据写入到磁盘临时小文件中。
3)、继续循环,直到取出所有满足条件的数据。
4)、 最后把磁盘的临时排好序的小文件,合并成一个有序的大文件。
2、rowid排序
既然查询的字段数据量很大,大到当前的sort_buffer放不下了,就会使用磁盘临时文件,排序的性能会很差。
那是不是可以不把所有查询字段都放入sort_buffer中,而仅仅是放排序字段和该记录主键到sort_buffer中呢,这其实就是rowid 排序。
这里我们思考两个问题?
什么是rowid排序?
就是只把查询SQL需要用于排序的字段和主键id,放到sort_buffer中。
什么情况走全字段排序,什么情况下走rowid排序?
它们的切换通过一个参数控制的这个参数就是max_length_for_sort_data 它表示MySQL用于排序行数据的长度的一个参数,如果单行的长度超过这个值,MySQL 就认为
单行太大,就换rowid 排序。
我们可以通过命令看下这个参数取值。
show variables like'max_length_for_sort_data';
![](https://i-blog.csdnimg.cn/blog_migrate/d776898c43d671d790b566d6cfd44529.jpeg)
默认是1024,。
因为本文示例中name,order_no,status长度=50+4+4 =58 < 1024, 所以走的是全字段排序。
接下来,我来修改一个参数,让MySQL采用另外一种算法。
SET max_length_for_sort_data =16;
新的算法放入sort_buffer的字段,只有要排序的列(即order_no字段)和主键id。
但这时,排序的结果就因为少了name和status字段的值,不能直接返回了,整个执行流程就变成如下所示的样子:
![](https://i-blog.csdnimg.cn/blog_migrate/0cc92033f9c8a9d3174672058c79db7f.jpeg)
1)、初始化sort_buffer,确定放入两个字段,即order_no和id;
2)、从索引idx_name找到第一个满足name='耳机’条件的主键id,也就是图中的id=2;
3)、到主键索引取出整行,取order_no、id这两个字段,存入sort_buffer中;
4)、从索引idx_name取下一个满足name='耳机’条件的主键id;
5)、重复步骤3、4直到不满足name='耳机’条件为止;
6)、对sort_buffer中的数据按照字段order_no进行排序;
7)、 遍历排序结果,取前10行,并按照id的值回到主键索引中取出order_no、name和status三个字段返回给客户端。
这个执行流程的示意图如下,我把它称为rowid排序。
3、全字段排序 VS rowid排序
全字段排序:如果sort_buffer内存足够,那效率是最高的,但如果sort_buffer内存不够的话,就需要用到磁盘临时文件,排序的性能会很差。
rowid排序:虽然sort_buffer可以放更多数据了,相对于全字段排序而言,rowid排序会多一次回表查询。
如果MySQL认为内存足够大,会优先选择全字段排序,把需要的字段都放到sort_buffer中,这样排序后就会直接从内存里面返回查询结果了,不用再回到原表去取数据。
这也就体现了MySQL的一个设计思想:如果内存够,就要多利用内存,尽量减少磁盘访问。
三、排序字段添加索引
上面的案例中,我们仅仅是在查询字段添加索引,那如果我们在查询条件和排序字段添加组合索引呢,那整个排序的流程又是怎么样的?
这里添加一条组合索引
drop index idx_name on order_info;
altertable order_info add index idx_name_order_no(name,order_no);
我们再来看下执行计划
explain select order_no, name, status from order_info where name ='耳机'orderby order_no limit 10
![](https://i-blog.csdnimg.cn/blog_migrate/b41408484f5ee18776775c514160170e.jpeg)
Extra 这个字段的Using index condition 表示该查询走了索引
我们发现已经没有上面的Using filesort,说明这个查询过程不需要内部排序。
那我们再看下它的执行流程是怎么样的?
![](https://i-blog.csdnimg.cn/blog_migrate/e187cf0bc09d4f338469a41cd576bb09.jpeg)
我们发现这里流程里已经不需要排序,但还需要走一次回表,那是因为我们查询字段有个status,在idx_name_order_no索引中并没有。
所以需要回表查询这个值。
那如果我们将所以改成如下呢?
-- 删除索引drop index idx_name_order_no on order_info;
-- 新增索引altertable order_info add index idx_name_order_no_status(name,order_no,status);
我们再来看下执行计划
explain select order_no, name, status from order_info where name ='耳机'orderby order_no limit 10
![](https://i-blog.csdnimg.cn/blog_migrate/89a5a096345eeb3b6c1c8b9422213b5d.jpeg)
Extra 这个字段的Using index 表示走了索引覆盖,也就是说都不需要回表查询主键索引了。
索引覆盖: 只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。
我们再来看下当前的执行流程
![](https://i-blog.csdnimg.cn/blog_migrate/21a3fdf21f3054982f9769f4453c956c.jpeg)
这样整个查询语句的执行流程就变成了:
从索引(name,order_no,status)找到第一个满足name='耳机'条件的记录,取出其中的ame,order_no,status这三个字段的值,作为结果集的一部分直接返回;
从索引(name,order_no,status)取下一个记录,同样取出这三个字段的值,作为结果集的一部分直接返回;
重复执行步骤2,直到查到第10条记录,或者是不满足name='耳机'条件时循环结束。
使用了覆盖索引,性能上会快很多。
当然,这里并不是说要为了每个查询能用上覆盖索引,就要把语句中涉及的字段都建上联合索引,毕竟索引还是有维护代价的。这是一个需要权衡的决定。
四、order by如何优化?
这里总结4点优化经验
1)、排序字段加索引
2)、只select需要的字段
3)、尝试提高 sort_buffer_size
4)、尝试提高 max_length_for_sort_data
4.1 排序字段加索引
尽量使用索引排序,如果这里使用ID排序的话,因为ID是索引字段,天生就具备有序的特性,所以这种情况都不需要放到sort buffer中去额外进行排序操作。
4.2 只select需要的字段
避免非必要的字段查询,只select需要的字段, 这点非常重要。
在这里的影响是:
1)、因为查询的字段较多可能导致数据会超出sort_buffer的容量,超出之后就需要用到磁盘临时文件,排序的性能会很差。
2)、 当select的字段大小总和>max_length_for_sort_data,排序算法会将 全字段排序 改为 rowid排序 增加一次回表查询。
4.3 尝试提高 sort_buffer_size
不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的。
4.4 尝试提高 max_length_for_sort_data
提高这个参数, 会增加用改进算法的概率。但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,超出之后就需要用到磁盘临时文件,排序的性能会很差。