MySQL ORDER BY 实现原理

2024-01-26 10:37:26 浏览数 (1)

1.何为 Sort Buffer？

假设有一张表 tb_user 表，表里有 5 个字段 id、name、age、city、created_at。

给定如下 SQL：

代码语言：javascript复制
select * from tb_user where age=18 order by create_time desc;

上面这条SQL执行过程如下： 1.根据 SQL 条件过滤数据，这里会把 age=18 之外的数据先过滤掉。 2.把符合条件的数据放到 sort buffer 里（sort buffer 是在内存的）。 3.在 sort buffer 里根据 created_at 对数据进行排序。 4.返回客户端排完序的数据。

MySQL 会为每个查询线程分配一块内存，叫做 Sort Buffer，这块内存的作用是用来排序的。

2.Sort Buffer 空间不够怎么办？

Sort Buffer 大小由参数 sort_buffer_size 控制，可以通过如下命令来查看和修改：

代码语言：javascript复制
-- 查看 sort_buffer 的大小
show variables like 'sort_buffer_size';

-- 修改 sort_buffer 的大小
set global sort_buffer_size = 262144;

（1）临时文件排序

如果当 sort buffer 空间无法容纳我们需要排序的数据时，这时会采用另外一种临时文件的方式进行排序，临时文件排序采用归并排序的算法，首先会把需要排序的数据拆分到多个临时文件里同步进行排序操作，然后把多个排好序的文件合并成一个结果集返回给客户端，不过在临时文件里排序相对于在 sort buffer 里排序来说，性能会慢很多，因为一个是在内存里操作，一个是在磁盘里操作。

（2）避免临时文件排序：rowid 排序

临时文件排序性能低下，所以 MySQL 会尽量避免使用临时文件排序。

这里 MySQL 根据单行数据的长度是否大于 max_length_for_sort_data 参数设置的值来判断是否可能会用到文件排序，当行数据长度大于 max_length_for_sort_data 时，它会进行优化，这里优化思路是尽量不把非必要的字段放到 sort buffer 中去。

什么是非必要的数据呢？以上面的案例来说，我们要对 created_at 字段排序，那么除了 created_at 字段外，其它的数据都可不必放到 sort buffer 中去，我们是不是可以先把 created_at 放到 sort buffer 里面排好序，然后再回表查询出其它关联字段返回给客户端。

因为排好序之后还要关联查询出其它列的数据，所以除了 created_at 之外，我们还需要有 id 字段，所以 id 字段我们也是必须要放到 sort buffer 里面的。这样的话执行流程大致如下：

  1. 把符合条件 created_at、id 列查询出来放到 sort buffer 里。
  2. 在 sort buffer 里根据 create_time 字段对数据进行排序。
  3. 把排好序的数据根据 id 再拿到 city、name 等其他字段。
  4. 返回结果给客户端。

3.ORDER BY 优化思路

根据 ORDER BY 的原理我们可以得到一些 SQL 优化思路。

（1）可以适当调大一些 sort_buffer_size。

（2）避免非必要的字段查询，因为这些字段越多，所需要的空间越大，就很可能导致 sort buffer 空间不够，转而使用其他效率低的排序策略，比如临时文件排序和 rowid 排序。

（3）尽量使用索引排序，如果这里使用 ID 排序的话，因为 ID 是索引字段，天生就具备有序特性，所以这种情况都不需要放到 sort buffer 额外进行排序。

（4）将 ORDER BY 字段与 WHERE 字段建立联合索引，即利用联合索引的有序性，优化 ORDER BY。

  • ORDER BY 的索引优化
代码语言：javascript复制
SELECT [column1],[column2],… FROM [table] ORDER BY [sort];

在 [sort] 栏位上建立索引就可以利用索引优化 ORDER BY。

  • WHERE ORDER BY 的索引优化
代码语言：javascript复制
SELECT [column1],[column2],…. FROM [table] WHERE [columnX] = [value] ORDER BY [sort];

建立一个联合索引 (columnX,sort) 来实现 ORDER BY 优化。

注意：如果 columnX 对应多个值，如下面语句就无法利用联合索引实现 ORDER BY 的优化。因为联合索引是按照 columnX 排序，再按照 sort 排，columnX 不同值对应的 sort 列之间无顺序关系。

代码语言：javascript复制
SELECT [column1],[column2],…. FROM [table] WHERE [columnX] IN ([value1],[value2],…) ORDER BY [sort];
  • WHERE 多个字段 ORDER BY
代码语言：javascript复制
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

建立索引 (uid,x,y) 实现 ORDER BY 优化比建立 (x,y,uid) 索引效果要好得多。

总的来说，MySQL 的 ORDER BY 实现原理是复杂的，它依赖于查询优化器的决策，可能涉及索引排序、内存排序和磁盘排序等策略。目的是为了在尽可能短的时间内返回有序的查询结果。优化查询和适当的索引设计可以改善排序性能。

参考文献

Mysql order by实现原理 - 知乎专栏 MySQL中order by语句的实现原理以及优化手段 - InfoQ 写作社区 MySQL如何利用索引优化ORDER BY排序语句 - CSDN 【原创】面试官:谈谈你对mysql联合索引的认识？

原理 mysql 排序 索引 优化

0 人点赞