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讲》
