第十六章 “order by”是怎么工作的?
全字段排序
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;
- 假设你要查询城市是“杭州”的所有人名字,并且按照姓名排序返回前 1000 个人的姓名、年龄
select city,name,age from t where city='杭州' order by name limit 1000 ;
city 索引示意图
满足 city='杭州’条件的行,是从 ID_X 到 ID_(X+N) 的这些记录
这个语句的执行流程:
- 初始化 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 行返回给客户端。
图中“按 name 排序”这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和参数
sort_buffer_size
sort_buffer_size
,就是 MySQL 为排序开辟的内存(sort_buffer)的大小- 如果要排序的数据量小于
sort_buffer_size
,排序就在内存中完成 - 如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序 (归并排序)
为什么要使用 外部排序 ?
- 一般是待排序数据量很大,在内存中放不下,不能使用内存排序算法
- 外部排序,常见为
多路归并排序算法
分隔
: (分治思想) 分隔为多个外部子文件,单个子文件大小小于
排序可用的内存大小,即子文件可以加载入内存内存排序
: 对每个子文件
进行内存排序
归并
: (多路)归并已有序的子文件,最终形成一个完整的有序文件- 如归并
有序子文件 X
和有序子文件 Y
为文件Z的过程:从 X 读取最小元素 x1 至内存,从 Y 读取最小元素 y1 至内存 - 比较 x1 和 y1 ,将两者中较小的写入文件Z。依次重复此过程,直至 X、Y 文件中的元素都进入 Z 中
关于
OPTIMIZER_TRACE
- 查询
OPTIMIZER_TRACE
这个表时,需要用到临时表 - 如果使用的是 InnoDB 引擎的话,把数据从临时表取出来的时候,会让
Innodb_rows_read
的值加 1 - 如果是 MyIsAm 引擎则不会
全字段排序的缺点:
查询的数据如果单行太大的话,内存里能够同时放下的行数就会很少,要分成很多个临时文件,排序的性能会很差
rowid 排序
决定是否更改查询时所使用的算法
SET max_length_for_sort_data = 长度;
- max_length_for_sort_data,是 MySQL 中专门控制用于排序的行数据的长度的一个参数
- 它的意思是,如果单行的长度超过这个值,MySQL 就认为单行太大,要换一个算法
rowid 排序执行流程:
- 初始化 sort_buffer,确定放入两个字段,即 name 和 id;
- 从索引 city 找到第一个满足 city='杭州’条件的主键 id,也就是图中的 ID_X;
- 到主键 id 索引取出整行,取 name、id 这两个字段,存入 sort_buffer 中;
- 从索引 city 取下一个记录的主键 id;
- 重复步骤 3、4 直到不满足 city='杭州’条件为止,也就是图中的 ID_Y;
- 对 sort_buffer 中的数据按照字段 name 进行排序;
- 遍历排序结果,取前 1000 行,并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
注意:
- rowid 方式和全字段方式一样,需要先把查询到的结果全部放在内存或硬盘中,再使用
相关算法
进行排序 - 而排序后由于没有保存所需的字段,需要按顺序使用主键再从索引树上查询,
查到一个就返回一个
,而不用把所有内容查完放到内存上再一并返回
全字段排序 VS rowid 排序
- 如果 MySQL 实在是担心排序内存太小,会影响排序效率,才会采用
rowid 排序算法
,这样排序过程中一次可以排序更多行,但是需要再回到原表去取数据 - 如果 MySQL 认为内存足够大,会优先选择
全字段排序
,把需要的字段都放到sort_buffer
中,这样排序后就会直接从内存里面返回查询结果了,不用再回到原表去取数据 - 如果内存够,就要多利用内存,尽量减少磁盘访问
- 对于 InnoDB 表来说,
rowid 排序
会要求回表多造成磁盘读,因此不会被优先选择
关于 explain 查询语句里面的 Extra 字段
Using filesort
:本次查询语句中有order by
,且排序依照的字段不在本次使用的索引中,不能自然有序,需要进行额外
的排序工作Using index
:使用了覆盖索引
,即本次查询所需的所有信息字段都可以从利用的索引上取得。无需回表,额外去主索引上取数据Using index condition
:使用了索引下推技术 ICP
。(虽然本次查询所需的数据,不能从利用的索引上完全取得,还是需要回表去主索引获取。但在回表前,充分利用索引中的字段,根据 where 条件进行过滤。提前排除了不符合查询条件的列。这样就减少了回表的次数,提高了效率。)Using where
:表示本次查询要进行筛选过滤