MySQL-order by 逻辑
文章图片来自《MySQL 45讲》非原创 ,本文是学习总结
概述
本文将会介绍 order by 排序中的两种情况
- 内存充足,全字段排序
- 内存不充裕,rowid 排序
什么意思呢?全字段排序讲的是整行拿到 sort buffer 中进行排序,然后返回正确的结果给客户端,而 rowid 排序只是取一行中部分字段到 sort buffer 中进行排序,而是否使用到了临时文件则是sort buffer 不够(也有可能需要排序的行数过多,总的来说就是需要排序的行数相对于sort buffer 太大了)这时就会使用到临时文件,使用到临时文件肯定相对于使用内存速度会慢,假如出现这种情况可以优化 SQL 语句对查询进行优化。
示例
例子来自 《MySQL 45讲》非原创
加入我们存在以下表 :
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE=InnoDB;
查询语句如下
select city,name,age from t where city='杭州' order by name limit 1000 ;
然后我们使用 explain
查看一下这条语句的执行逻辑。
Extra这个字段中的“Using filesort”表示的就是需要排序,MySQL会给每个线程分配一块内存用于排序,称为sort_buffer。
为了说明这个SQL查询语句的执行过程,我们先来看一下city这个索引的示意图。
全排序排序
通常情况下,这个语句执行流程如下所示 :
- 初始化sort_buffer,确定放入name、city、age这三个字段;
- 从索引city找到第一个满足city='杭州’条件的主键id,也就是图中的ID_X;
- 到主键id索引取出整行,取name、city、age三个字段的值,存入sort_buffer中;
- 从索引city取下一个记录的主键id;
- 重复步骤3、4直到city的值不满足查询条件为止,对应的主键id也就是图中的ID_Y;
- 对sort_buffer中的数据按照字段name做快速排序;
- 按照排序结果取前1000行返回给客户端。
我们暂且把这个排序过程,称为全字段排序,执行流程的示意图如下所示,下一篇文章中我们还会用到这个排序。
rowid 排序
在全字段排序中,我们是取整一行数据放在 sort buffer 中进行排序,而 rowid 排序则是取部分字段,然后排好后,再回表
找到想要返回的字段,回表的操作必定比不回表的操作慢,假如需要我们优化查询语句可以使用覆盖索引进行优化查询语句
那为什么使用覆盖索引就可以加快查询的结果呢?
这是因为放在 sort buffer 本身是无序的,而覆盖索引有最左前缀原则,间接地排好了序,所以查询速度就更加快了。
补充 : 开启optimizer_trace
如何查看 order by
的执行逻辑呢?这中间又经历了优化器如何的选择过程呢?optimizer_trace 的选项可以让你知道一条语句的执行逻辑,为什么选择这个路径执行,而不是另外一种路径执行,往往这个功能有利于我们优化 SQL 语句,同时也要知道知道开启这个选项可能会带来性能损耗。
比如有以下语句,我需要知道他的执行过程 :
USE gfp_sett;
SET optimizer_trace='enabled=on';
/**需要跟踪优化的SQL 语句**/
SELECT * FROM t_gsm_sett_settlement WHERE id = 1 ;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`
参考
- 《MySQL 45讲》