首先排序功能由 ORDER BY 实现,具体排列顺序取决于优化器的选择。若优化器认为索引排序更有效率,则使用索引排序;反之,则使用 filesort(执行计划中额外信息提示:使用 filesort)。然而,索引排序的适用情况有限,且不确定性较高,通常还是会采用 filesort。
在 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;-- 不走索引,使用filesortselect 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
在使用索引字段进行排序时,优化器会根据成本评估来选择是否通过索引进行排序。经过我的多次验证,以下情况中,索引排序的概率较高:
filesort 排序
如果无法使用或优化器认为索引排序效率不高,MySQL 将执行 filesort 操作,以读取表中的行并对它们进行排序。
在排序过程中,MySQL 为每个线程分配一块内存用于排序,称为sort_buffer,其大小由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 中进行排序,排序完成后返回给客户端的大致过程如下:
以上过程中,如果数据在 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 几个字段查询出来,然后返回给客户端的大致过程如下:
以上的第五步,与全字段排序算法相比确实多了一次回表操作。因此,这种方案的效率肯定会稍慢一些。
如何选择
如何选择排序方式?
实际上,row_id 是 MySQL 的一种优化算法,它首先考虑使用全字段排序。只有在认为字段长度过长可能影响效率时,才会采用 row_id 排序方式。此外,如果能够利用 sort_buffer 完成排序,MySQL 就不会使用临时文件。
综上所述,MySQL 在选择排序方式时会优先考虑速度、内存和减少回表次数等因素。
本网站的文章部分内容可能来源于网络和网友发布,仅供大家学习与参考,如有侵权,请联系站长进行删除处理,不代表本网站立场,转载者并注明出处:https://www.jmbhsh.com/xinwenzixun/34234.html