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 条件

热门相关:骑士归来   时间都知道(唐嫣、窦骁、杨烁主演)   寂静王冠   天启预报   天启预报