MySQL中关于排序order by limit值不稳定分析

这篇文章主要介绍“MySQL中关于排序order by limit值不稳定分析”，在日常操作中，相信很多人在MySQL中关于排序order by limit值不稳定分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL中关于排序order by limit值不稳定分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！数据如下：问题如下：问为什么这两个语句得到的数据不一样。这里首先给出原免费主机域名因，在MySQL排序的时候可以使用索引来避免排序及不需要使用filesort，其次当使用filesort的时候可能在内存中出现两种情况及堆排序列和快速排序两种方式。所以MySQL内存排序可以使用的途径包含：直接利用索引避免排序快速排序堆排序具体使用哪一种排序方式是优化器决定的，总的说来如下：直接利用索引避免排序：用于有索引且回表效率高的情况下快速排序算法：如果没有索引大量排序的情况下堆排序算法：如果没有索引排序量不大的情况下而快速排序和堆排序是不稳定的排序算法，也就是对于重复值是不能保证顺序的。而直接利用索引的话其返回数据是稳定的，因为索引的B+树叶子结点的顺序是唯一且一定的。如上的key nu，其叶子结点包含了nu+id，它是唯一的顺序也是递增的。因此在这种不稳定的算法情况下上面的查询出现了不一样的结果，归根结底就是使用索引避免排序和堆排序对于重复值的处理上是不同的。
也许你会问为什么存在两种排序方式，实际上在大量排序的情况下快速排序是有优势的，而堆排序使用优先队列只完成少量的排序是有优势的，因为它根本不需要排序完成只排序你需要的数据量就可以了。MySQL认为快速排序的速度是堆排序的3倍如下：那么在使用排序算法的时候会根据待排序数据量的大小进行切换具体根据函数check_if_pq_applicable进行判定的。在filesort函数里面有如下代码：对于直接利用索引避免排序，这个直接看执行计划就好了，不会出现filesort字样。但是对于到底使用额快速排序还是堆排序则不好判断因为执行计划是一样的，我想到的只能是在调试的时候做断点，不知道还有其他办法没有。因此我在上面代码的if分支里面做了2个断点，其中一个断点在堆排序算法里面，一个断点在快速排序算法里面如下：其中断点3代表快速排序命中，断点4代表堆排序命中。如上所述我们可以将结果的返回定义为三种方式，我们将在这里测试这三种方式的得到数据不同。使用索引避免排序的结果语句：
select * from testse force index(nu) order 免费主机域名by nu;使用快速排序的结果语句：
select * from testse order by nu;因为我前面设置了断点，其断点命中如下：可以看到PQ 没有开启，也就是堆排序没有使用使用的优先队列堆排序方式。那么它的结果是使用堆排序语句：
select * from testse order by nu limit 8;其断点命中可以看到PQ 开启了，也就是堆排序。可以看到从前面8行来看，每种方法返回的数据是不一样的：可以看到在不同的获取数据的方式得到的数据是一样的，但是这只是重复数据排序的部分，这是排序算法的不稳定性决定的。快速排序适合大数据量排序，堆排序在少量排序上有优势，因此当order by limit n，n到了一个数量级的时候会切换排序算法，这个在执行计划是看不到的，具体使用那种算法是优化器通过函数check_if_pq_applicable进行判定的。
其次这只是一种造成排序数据不一致的情况还有一种情况是由于参数 max_length_for_sort_data的参数影响的一次排序和二次排序，这个有机会在研究一下代码。一般默认1024个字节还是很少会超过的。最后我还是想简单描述一下堆排序算法，也当复习一下。具体可以参考算法导论等书籍，我们以大顶堆为例，实际上任何一个待排序的数组都可以看成一棵完全二叉树，用算法导论的截图如下：image.png这棵树是满足大顶堆定义的，在大顶堆中有如下特性：必须满足完全二叉树
很方便的根据父节点的位置计算出两个叶子结点的位置
如果父节点的位置为i/2，左子节点为 i，右子节点为i+1这是完全二叉树的特性决定所有子节点都可以看做一个子堆那么所有结点都有
父节点>左子节点 && 父节点>右节点很明显的可以找到最大的元素，就是整个堆的根结点在这个算法中，最重要也是最主要的就是堆的维护，堆的构建。维护：
维护：采用的是自上而下的维护，可以用递归完成。
这里电子版有点不清晰，黑色结点就是值4image.png对应我最后的代码 bigheapad函数构建
构建：是采用自下而上的构建，构建就是不断循环的对各个父结点做维护，以达到对任何一个无序数组满足大顶堆的条件。因为下层的子树满足了大顶堆条件那么上层就一定满足大顶堆的条件。image.pngimage.png对应我最后的代码bigheapbulid函数排序实际上排序就是将数组中的第一个数字也就是最大的数字和最后一个数字交换，然后再次做一次维护做好大顶堆结构即可，如果反复不断做这个操作那么整个素组都会排序完成。image.pngimage.png我的函数参考biglimitn和bigheapsort函数。对于MySQL的源码中堆排序的代码存在于priority_queue.h文件中，其中可以看到一些方法如：维护 heapify函数构建 build_heap函数排序 sort函数
当然源码的实现复杂得多，有兴趣的朋友可以深入。栈帧备查：下面是一个我以前参考算法导论第六章堆排序写的一个实现，其中包含了大顶堆和小顶堆，供大家参考：到此，关于“MySQL中关于排序order by limit值不稳定分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注云技术网站，小编会继续努力为大家带来更多实用的文章！

相关推荐: MySQL运行原理与基础架构是什么

本篇内容主要讲解“MySQL运行原理与基础架构是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL运行原理与基础架构是什么”吧!下面是关于上述部件的介绍：connectors与其他编程语言中的sql 语句…