order by原理

前置知识

Using filesort：表示需要用到 sort buffer 内存空间进行排序
sort buffer 是一块可调整的内存空间，如果需要排序的数据量太大而空间不够，将用到磁盘临时文件来排序，效率很低

什么情况下会用到 sort buffer 来排序？

不能根据索引直接知道排序结果，就需要用到 sort buffer

排序的执行情况？

表T：id (primary key), city (key), name, age 等字段

explain select city,name,age from T where city = 'gz' order by name;
-- 走了索引(但是是非覆盖索引)，需要排序，需要进行回表查询
-- Using index condition; Using filesort

这个 SQL语句可以知道，不能根据索引直接知道排序结果，所以需用到 sort buffer 排序

● 全字段排序 执行流程
初始化 sort buffer，确定此内存中需要存放的字段
到 city 字段索引上找到匹配的第一行
回表查询，把 city,name,age 存到 sort buffer 中
重复上述两步，直到不满足 where 条件(city 索引上找到一行不满足的数据)
对 sort buffer 中的数据排序
返回结果集给客户端

● rowid 排序执行流程
排序前，会检测放入 sort buffer 中的字段的长度，如果超过最大单行长度值(可调)，那么就会只放rowid 和 需要排序的字段

explain select city,name,age from T where city = 'gz' order by name;
-- 走了索引(但是是非覆盖索引)，需要排序，需要进行回表查询
-- Using index condition; Using filesort

MySQL如果检测到 city,name,age 等字段超过了最大单行长度值，就会只把 id, name 等字段放入 sort buffer 中

执行流程
相比全字段排序，基本流程一致。存入 sort buffer 中的字段变少了，在排序完后，又要回表查询然后返回结果集。效率变低了
这个排序机制是为了保证尽可能的使用 sort buffer 内存排序，减少内存存放的数据行，那么存放的数据量就更多。从而降低/不适用磁盘临时文件排序

如何优化？

可以这样创建普通索引 (city, name)。那么执行上述 SQL 语句时，不会用到内存排序
执行流程
到 city 字段索引上找到匹配的第一行
回表查询，把 city,name,age 作为 结果集 的一部分直接返回
重复上述两步，直到不满足 where 条件