MySQL排序的艺术：你真的懂 Order By吗？

可以看到当查询条件本身有索引可用的话，全字段排序的排序过程都在 sort buffer（内存）进行，回表次数为符合条件的数据个数。

当然，如果我们建立的是 city、nick_name、age、phone 的联合索引，还可以实现“索引覆盖”，即在一棵索引树上取得全部所需数据，减少回表（随机读）次数。

不过针对每个查询或排序语句建立联合索引，会导致索引过多，大大降低写入更新数据的速度，以及大大提升数据所需要的存储空间。

生产商对索引的建立修改需要格外谨慎。

rowId 排序

========

rowId 就是 MySQL 对每行数据的唯一标识符。

当数据表有主键时，rowId 就是表主键；当数据表没有主键或者主键被删除时，MySQL 会自动生成一个长度为 6 字节的 rowId 为作为 rowId。

「rowId 排序是指只将与排序相关的字段和 rowId 放入 sort buffer，其余结果集需要用到的数据在排序完成后，通过 rowId 回表取得。」

全字段排序的流程看着已经十分合理，为什么还需要有个 rowId 排序？

这是我们只需要输出三个字段的情况，假如我们有上百个字段需要返回呢？sort buffer 默认只有 256 kb。能够装下多少行的原始数据行？

所以当待排序的数据行很大的时候，使用全字段排序必然会导致“外部排序”。而且是使用很多临时文件的“外部排序”，效率很低下。

相比全字段排序，rowId 排序的好处是在 sort buffer 大小固定的情况下，sort buffer 能够容纳更多的数据行，能够避免使用或者少使用“外部排序文件”。

缺点是最终返回结果集的时候，需要再次进行回表。

还是之前那个例子：

SELECT nick_name, age, phone

FROM t_user

WHERE city = “深圳”

ORDER BY nick_name;

rowId 排序全过程：

1. 从 city 索引树上找到第一条值为深圳的数据，取得 id 之后回表（回到主键索引）取得 nick_name 这个与排序相关的字段和主键 id 一起放入 sort buffer

2. 从 city 索引树取下一条值为深圳的数据，重复 1 过程，直到下一条数据不满足值为深圳条件

3. 这时候，所有 city = 深圳 的数据都在 sort buffer 了（sort buffer 里面的数据包含两个字段： id 和 nick_name）。对 nick_name 执行快速排序

4. 利用排序好的数据，使用主键 id 再次回表取其他字段，将结果返回

注意：该步骤 4 中不会等所有排序好的 id 回表完再返回，而是每个 id 回表一次，取得该行数据之后立即返回，所以不会消耗额外的内存。

优先队列排序

======

无论是使用全字段排序还是 rowId 排序，都不可避免了对所有符合 WHRER 条件的数据进行了排序。

有读者可能会认为，那不是应该的吗？

设想一下，如果我们还搭配着 LIMIT 使用呢？

例如我们在排序语句后添加 LIMIT 3 ，哪怕查出来的数据有 10W 行，我们也只需要前 3 行有序。

为了得到前 3 行数据，而不得不将 10W 行数据载入内存，大大降低了 sort buffer 的利用率。

这时候你可能想到利用“最小堆”、“最大堆”来进行排序。

没错，这正是 MySQL 针对带有 LIMIT 的 ORDER BY 语句的优化：使用优先队列进行排序。

以下面的 SQL 为例子：

SELECT nick_name, age, phone

FROM t_user

WHERE city = “深圳”

ORDER BY nick_name LIMIT 3;

优先队列进行排序的流程：

1. 在所有待排序的数据，取数量为 LIMIT （本例中为 3）的数据，构建一个堆

2. 不断地取下一行数据，更新堆节点

3. 当所有行的扫描完，得到最终的排序结果

如何选择?

=====

现在我们知道有全字段排序和 rowId 排序，那么 MySQL 是如何在这两种排序方案中做选择呢？

由于 rowId 排序相对于全字段排序，不可避免的多了一次回表操作，回表操作意味着随机读，而随机 IO 是数据库中最昂贵的操作。

所以 MySQL 会在尽可能的情况下选择全字段排序。

那么什么情况下 MySQL 会选择 rowId 排序呢，是否有具体的值可以量度？

答案是有的，通过参数 max_length_for_sort_data 可以控制用于排序的行数据最大长度，默认值为 1024 字节。

当单行数据长度超过该值，MySQL 就会觉得如果还用全字段排序，会导致 sort buffer 容纳下的行数太少，从而转为使用 rowId 排序。

---

临时表排序

=====

通常对于一个执行较慢的排序语句，在使用 EXPLAIN 进行执行过程分析的时候除了能看到 Using filesort 以外，还能看到 Using temporary，代表在排序过程中使用到了临时表。

内存临时表排序

=======

MySQL 优先使用内存临时表。当 MySQL 使用内存临时表时，临时表存储引擎为 memory 。

如果当前 MySQL 使用的是内存临时表的话，将会直接使用 rowId 排序，因为这时候所谓的“回表”只是在内存表中读数据，操作不涉及硬盘的随机 IO 读。

使用 rowId 可以在 sort buffer 容纳给多的行，避免或减少外部排序文件的使用。

磁盘临时表排序

=======

如果系统中很多需要使用临时表的排序语句执行，而又不加以限制，全都使用临时表的话，内存很快就会被打满。

所以 MySQL 提供了 tmp_table_size 参数限制了内存临时表的大小，默认值是 16M。

如果临时表大小超过了tmp_table_size，那么内存临时表就会转成磁盘临时表。

总结

在这里，由于面试中MySQL问的比较多，因此也就在此以MySQL为例为大家总结分享。但是你要学习的往往不止这一点，还有一些主流框架的使用，Spring源码的学习，Mybatis源码的学习等等都是需要掌握的，我也把这些知识点都整理起来了

_size，那么内存临时表就会转成磁盘临时表。

总结

在这里，由于面试中MySQL问的比较多，因此也就在此以MySQL为例为大家总结分享。但是你要学习的往往不止这一点，还有一些主流框架的使用，Spring源码的学习，Mybatis源码的学习等等都是需要掌握的，我也把这些知识点都整理起来了

[外链图片转存中…(img-1evO3IA5-1719657167652)]

[外链图片转存中…(img-WzuRVnE5-1719657167653)]