在 filesort 排序中,如果排序的数据较少,可在内存中的 sort_buffer 上完成;否则,需借助临时文件进行排序。实际排序过程中,如果字段长度较短,可直接在 sort_buffer 中进行全字段排序并返回结果集。若字段长度较长,可能出于空间考量,采用基于 row_id 的排序,此时会进行二次回表后返回结果集。
索引排序
众所周知,索引具备天然的排序属性,因此在使用 ORDER BY 时,若能充分利用索引,则效率必然最佳。
MySQL 确实可以基于索引执行 ORDER BY 查询,然而这一过程是否必然使用索引完全由优化器决定。
CREATE TABLE `t2` (
`id` INT(11),
`a` varchar(64) NOT NULL,
`b` varchar(64) NOT NULL,
`c` varchar(64) NOT NULL,
`d` varchar(64) NOT NULL,
`f` varchar(64) DEFAULT NULL,
PRIMARY KEY(id),
UNIQUE KEY `f` (`f`),
KEY `idx_abc` (`a`,`b`,`c`)
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
假设存在如上所述的表格,在排序过程中可能会出现以下情况(由于优化器的干预,以下结果并不一定可以 100%复现。根据我的实验,在 MySQL 5.7 环境中是可以的)。
select * from t2 order by a;
-- 不走索引,使用filesort(后面介绍啥是filesort)
select * from t2 order by a limit 100;
-- 走索引
select a,b from t2 order by a limit 100;
-- 走索引
select a,b,c from t2 order by a;
-- 走索引
select a,b,c,d from t2 order by a;
-- 不走索引,使用filesort
select a,b,c,d from t2 where a = "Paidaxing" order by b;
-- 走索引
select a,b,c,d from t2 where b = "Paidaxing" order by a;
-- 不走索引,使用filesort
在使用索引字段进行排序时,优化器会根据成本评估来选择是否通过索引进行排序。经过我的多次验证,以下情况中,索引排序的概率较高:
- 查询的字段和 ORDER BY 的字段组成了一个联合索引,并且查询条件符合最左前缀匹配,使得查询可以使用索引覆盖。例如:SELECT a, b, c FROM t2 ORDER BY a;
- 查询条件中包含了 LIMIT,并且 LIMIT 的数量不是很大。(在我测试的表中,数据量为 80 万,当 LIMIT 超过 2 万多时就不再使用索引排序),例如:ORDER BY a LIMIT 100
- 虽然未遵循最左前缀匹配,但是前导列通过常量进行了查询,例如:WHERE a = "Paidaxing" ORDER BY b
filesort 排序
如果无法使用或优化器认为索引排序效率不高,MySQL 将执行 filesort 操作,以读取表中的行并对它们进行排序。
在排序过程中,MySQL 为每个线程分配一块内存用于排序,称为sort_buffer,其大小由 sort_buffer_size控制。
根据 sort_buffer_size 的大小不同,排序操作会发生在不同的位置:
- 如果排序数据量小于 sort_buffer_size,则排序在内存中完成。
- 如果排序数据量大于 sort_buffer_size,则需要利用磁盘临时文件辅助排序。
临时文件排序采用归并排序算法,首先将需要排序的数据分配到多个临时文件中,同时进行排序操作,然后将多个排序完成的文件合并成一个结果集返回给客户端。相对于在内存中的 sort_buffer 排序,磁盘上的临时文件排序速度较慢。
除了sort_buffer_size参数之外,影响排序算法的另一个关键参数是 max_length_for_sort_data。
max_length_for_sort_data 是 MySQL 中控制用于排序的行数据的长度的一个参数,默认值为 1024 字节。如果单行长度超过该值,MySQL 就会认为单行太大,因此会采用 rowid 排序;否则,会进行全字段排序。
全字段排序
所谓全字段排序,即将要查询的所有字段都放入 sort_buffer 中,然后根据排序字段进行排序,排序完成后直接将结果集返回给客户端。
假设我们有以下查询 SQL:
select a,d,f from t2 where a = "Paidaxing" order by d;
--
因为此处涉及的字段为 a、d、f 三个,因此将满足 WHERE 条件的所有数据的 a、d、f 字段都放入 sort_buffer 中,然后根据 d 字段在 sort_buffer 中进行排序,排序完成后返回给客户端的大致过程如下:
- 从索引 a 中取出满足条件 a = "Paidaxing" 的第一条记录的主键 ID。
- 根据主键 ID 回表,提取 a、d、f 三个字段的值,并将它们存入 sort_buffer。
- 继续查询下一个符合 a = "Paidaxing" 条件的记录,重复执行第 1 和第 2 步骤。
- 在 sort_buffer 中根据 d 字段进行排序。
- 将排序后的结果集返回给客户端。
图片
以上过程中,如果数据在 sort_buffer 中无法全部存放,则会使用临时文件,并对临时文件进行归并排序。
全字段排序的优点在于只需要对原表进行一次回表查询(每条记录只需回表一次),排序完成后可以直接返回所需字段,因此效率较高。但其缺点在于,如果要查询的字段较多,会占用大量 sort_buffer 空间,导致可存储的数据量减少。当需要排序的数据量增大时,可能会使用临时文件,从而导致整体性能下降。
为避免这个问题,可以采用 row_id 排序的方式。
row_id 排序
这个也很好理解,即在构建 sort_buffer 时,不需要将所有要查询的字段都放进去,只需要将排序字段和主键放入即可。
select a,d,f from t2 where a = "Paidaxing" order by d;
比如这个 SQL,只需要将 d 和 id 放入 sort_buffer 中,首先按照 d 进行排序。排序完成后,根据 id 将对应的 a、d、f 几个字段查询出来,然后返回给客户端的大致过程如下:
- 从索引 a 中取出符合条件 a = "Paidaxing" 的第一条记录的主键 ID。
- 根据主键 ID 回表,提取 d 字段的值,并将其存入 sort_buffer。
- 继续查询下一个符合条件 a = "Paidaxing" 的记录,重复执行第 1 和第 2 步骤。
- 在 sort_buffer 中根据 d 进行排序。
- 根据排序后的 id,回表查询出对应的 a、d、f 几个字段。
- 将结果集返回给客户端。
图片
以上的第五步,与全字段排序算法相比确实多了一次回表操作。因此,这种方案的效率肯定会稍慢一些。
如何选择
如何选择排序方式?
实际上,row_id 是 MySQL 的一种优化算法,它首先考虑使用全字段排序。只有在认为字段长度过长可能影响效率时,才会采用 row_id 排序方式。此外,如果能够利用 sort_buffer 完成排序,MySQL 就不会使用临时文件。
综上所述,MySQL 在选择排序方式时会优先考虑速度、内存和减少回表次数等因素。