list对oracle结果集排序了_mysql 002 | order by的排序算法原理原来是这样的

最新推荐文章于 2023-04-06 21:05:24 发布

weixin_39601657

最新推荐文章于 2023-04-06 21:05:24 发布

阅读量147

点赞数

文章标签： list对oracle结果集排序了

前言

平常写业务的时候，我们经常需要对一些业务数据进行排序，例如点赞排名，浏览量排名等等。虽然这个字段很常用了，可能出于对服务器是 MySQL 的原因，使我们会胆怯或不想去了解它背后的执行流程，纯黑箱子使用。但是如果不了解其内部机制，又何谈以优化使用？今天决定在这篇文章介绍一些 Order By 相关知识。

抛出本文问题

order by 的排序流程是什么？
order by 的排序算法是什么？
order by 的优化点在于什么？

解答疑问

排序流程

关于排序过程，MySQL 会通过判断 sort_buffer_size 来执行不同的排序流程。

sort_buffer_size 其实是 MySQL 的一个系统参数，它可以控制 MySQL 会在排序的时候分配的缓冲区大小，这个参数可以作用于 Global 或者 Session 级别。它的特点是允许动态变化，而且不针对特定的存储引擎。每每当有新的线程的时候，都会分配相应参数值的排序缓冲区大小。

了解完了这个参数值，我们可以介绍两种排序流程：

一次排序
二次排序

一次排序

一次排序不是一个专业术语，按照我的理解来解读，排序流程需要一次读取磁盘。

可能你会感觉困惑，那么来了解一下整个流程：

MySQL 会初始化排序缓冲区，然后读取目标 SQL 所有涉及到的字段(选择列，筛选列，排序列) 。
如果筛选列是索引，则会根据索引进行查找数据；如果非索引就全局扫描
根据选择列将所需的数据加进排序缓冲区
现在去寻找第二条数据，所以它会重复去走 2⃣️，3⃣️ 步。直到所有符合筛选列的数据都被加载进缓冲区完。
目前进入了排序阶段，根据排序列的要求，把排序缓冲区的数据加载进内存进行排序
返回客户端

二次排序

看完了一次排序，或许你对二次排序也有一定的明白了。是的，二次排序流程需要对磁盘进行两次读取。

那我们还是对这个流程梳理：

MySQL 会初始化排序缓冲区，仅仅把 SQL 的目标表的主键ID以及排序列加载进去。
根据筛选列进行数据的筛选。如果筛选列是索引，则会根据索引进行查找数据；如果非索引就全局扫描
假设查找到了第一条数据，然后将这条数据的主键ID和排序列加载进内存。
现在去寻找第二条数据，所以它会重复去走 2，3 步，直到获取了所有符合筛选列的数据。
目前进入了排序阶段，根据排序列的要求，对排序缓冲区的数据进行排序并结果
拿到了排序后的结果，根据每一条数据的主键ID回到原表扫描，获取到选择列的信息。
返回给客户端

比对

/优点缺点一次排序磁盘 IO 次数少，仅有一次读取，减轻内心的压力对排序缓冲区有更大的容量，还可能出现更大临时文件占用。二次排序两次磁盘 IO，需要重新读取磁盘二次 IO，需要重新读取磁盘

排序算法

刚刚在排序流程中我们可以知道，实际进行排序的操作是放在内存的。其实上面的流程的都是在内存进行排序。但是实际应用场景上，有可能是一次性需要排序的数据集超出了内存范围；或者选择列太多，，导致即使是少数据量也会内存爆满。所以当 MySQL 面对大数据量的情况下，会使用临时文件进行辅助存储已排序数据。

内存

内存当中主要使用的是比较常见的快速排序，基本思想是：通过一趟排序将要排序的数据分割成独立的两部分，其中一部分的所有数据都比另外一部分的所有数据都要小，然后再按此方法对这两部分数据分别进行快速排序，整个排序过程可以递归进行，以此达到整个数据变成有序序列。

内存+物理文件

由于内存不足的问题，MySQL 会对数据集进行多次读取，多次排序处理。假设目前内存已经满了，MySQL 会对内存的数据集使用快速排序，然后将结果保存到一个临时文件当中。然后 MySQL 会将内存清掉，重新根据筛选列再次读取数据。当内存再次爆满，然后会继续在内存进行排序，然后保存到临时文件。当数据完全准备好了，那么 MySQL 会使用多路归并排序对文件进行排序，原理跟归并排序中的二路归并比较类型，建议去了解一下。

优化点

提高 sort_buffer_size。该值应该足够大，可以让大数据集也能加载进去，这样可以让 MySQL 减少在排序的过程中对排序数据进行切分，避免读写磁盘和合并文件。
根据系统情况调节 max_length_for_sort_data，因为这个参数的大小会影响 MySQL 选择使用一次排序还是二次排序。当值比较小的时候，会使用二次排序，相反使用一次排序。(如果数值设的太高，会导致磁盘活动太高，CPU 活动太低)。
提高 read_rnd_buffer_size 可以提高读取的行数，减少的读的时间。
尽量在 SQL 上使用索引满足 order by 的，避免执行文件排序操作时涉及的额外排序。而且有些通过索引扫描比通过表扫描更加廉价。