MySQL Order By原理（学习笔记）

1. 场景1

1.1 表结构+索引+SQL

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 10  ;

1.2 索引结构

1.3 执行计划

1.4 分析

执行计划（prossible_keys=city；Extra=Using filesort）

prossible_keys=city：说明最终SQL使用了city索引
Extra=Using filesort：说明需要使用额外的空间对数据排序（过程如下）
MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

1.4.1 全字段排序

sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

1. 初始化sort_buffer，确定放入name、city、age这三个字段；
2. 从索引city找到第一个满足city='杭州’条件的主键id；
3. 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；
4. 从索引city取下一个记录的主键id；重复步骤2、3直到city的值不满足查询条件为止；
5. 对sort_buffer中的数据按照字段name做快速排序；然后返回结果给客户端

1.4.2 rowid 排序

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

1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id
3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 重复步骤 3、4 直到不满足 city='杭州’条件为止，
6. 对 sort_buffer 中的数据按照字段 name 进行排序；
7. 遍历排序结果，返回给客户端
   

2. 如何避免排序

alter table t2 add index city_name(city, name);

select city,name,age 
from t2 
where city='杭州' 
order by name limit 10;  1000

2.1 分析

我们可以看到执行计划：Extra=Using index Condition，没有 Using filesort 了，也就是不需要排序了
因为索引本身就是有序的，直接查询拿到结果即可，这里不在过多展开讲解索引，可以自行搜索相关资料