MySQL:关于排序order by limit值不稳定的说明（1）-阿里云开发者社区

水平有限，有过有误请谅解和指正，仅仅作为抛砖引玉。谢谢！

源码版本：5.7.14
本文约定：PQ 就是 Priority Queue 及优先队列其核心是堆排序，文中代表一种算法。

一、问题抛出

数据如下：

CREATE TABLE `testse` (
  `id` int(11) NOT NULL,
  `nu` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `nu` (`nu`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `testse` VALUES (-1,14,'gaopeng'),(0,14,'gaopeng'),(1,1,'gaopeng'),(2,99,'gaopeng'),(3,55,'gaopeng'),(4,20,'gaopeng'),(5,24,'gaopeng'),(6,14,'gaopeng'),(7,1
4,'gaopeng'),(8,13,'gaopeng'),(9,9,'gaopeng'),(10,19,'gaopeng'),(11,20,'gaopeng'),(12,14,'gaopeng'),(13,15,'gaopeng'),(14,16,'gaopeng'),(15,20,'gaopeng'),(100,14,'gaope
ng'),(111,14,'gaopeng');

问题如下：

mysql>  select *  from testse order by  nu limit 3,1;
+-----+------+---------+
| id  | nu   | name    |
+-----+------+---------+
| 111 |   14 | gaopeng |
+-----+------+---------+
1 row in set (2.76 sec)

mysql>  select *  from testse force index(nu) order by  nu limit 3,1;
+----+------+---------+
| id | nu   | name    |
+----+------+---------+
| -1 |   14 | gaopeng |
+----+------+---------+
1 row in set (0.00 sec)

问为什么这两个语句得到的数据不一样。

二、问题原因

这里首先给出原因，在MySQL排序的时候可以使用索引来避免排序及不需要使用filesort，其次当使用filesort的时候可能在内存中出现两种情况及堆排序列和快速排序两种方式。所以MySQL内存排序可以使用的途径包含：

直接利用索引避免排序
快速排序
堆排序

具体使用哪一种排序方式是优化器决定的，总的说来如下：

直接利用索引避免排序：用于有索引且回表效率高的情况下
快速排序算法：如果没有索引大量排序的情况下
堆排序算法：如果没有索引排序量不大的情况下

而快速排序和堆排序是不稳定的排序算法，也就是对于重复值是不能保证顺序的。而直接利用索引的话其返回数据是稳定的，因为索引的B+树叶子结点的顺序是唯一且一定的。如上的key nu，其叶子结点包含了nu+id，它是唯一的顺序也是递增的。因此在这种不稳定的算法情况下上面的查询出现了不一样的结果，归根结底就是使用索引避免排序和堆排序对于重复值的处理上是不同的。
也许你会问为什么存在两种排序方式，实际上在大量排序的情况下快速排序是有优势的，而堆排序使用优先队列只完成少量的排序是有优势的，因为它根本不需要排序完成只排序你需要的数据量就可以了。

MySQL认为快速排序的速度是堆排序的3倍如下：

  /*
    How much Priority Queue sort is slower than qsort.
    Measurements (see unit test) indicate that PQ is roughly 3 times slower.
  */
  const double PQ_slowness= 3.0;

那么在使用排序算法的时候会根据待排序数据量的大小进行切换具体根据函数check_if_pq_applicable进行判定的。在filesort函数里面有如下代码：

if (check_if_pq_applicable(trace, &param, &table_sort,
                             table, num_rows, memory_available,
                             subselect != NULL))
  {
    DBUG_PRINT("info", ("filesort PQ is applicable")); //使用堆排序
    /*
      For PQ queries (with limit) we know exactly how many pointers/records
      we have in the buffer, so to simplify things, we initialize
      all pointers here. (We cannot pack fields anyways, so there is no
      point in doing lazy initialization).
     */
    table_sort.init_record_pointers();
    .....
   filesort->using_pq= true;
    param.using_pq= true;
  }
  else//使用快速排序
  {
    DBUG_PRINT("info", ("filesort PQ is not applicable"));
    filesort->using_pq= false;
    param.using_pq= false;

 .....
  }

三、如何确定是使用的那种方法返回排序的结果。

对于直接利用索引避免排序，这个直接看执行计划就好了，不会出现filesort字样。但是对于到底使用额快速排序还是堆排序则不好判断因为执行计划是一样的，我想到的只能是在调试的时候做断点，不知道还有其他办法没有。因此我在上面代码的if分支里面做了2个断点，其中一个断点在堆排序算法里面，一个断点在快速排序算法里面如下：

3       breakpoint     keep y   0x0000000000f50e62 in filesort(THD*, Filesort*, bool, ha_rows*, ha_rows*, ha_rows*)
                                               at /root/mysql5.7.14/percona-server-5.7.14-7/sql/filesort.cc:359
        breakpoint already hit 3 times
4       breakpoint     keep y   0x0000000000f50d41 in filesort(THD*, Filesort*, bool, ha_rows*, ha_rows*, ha_rows*)
                                               at /root/mysql5.7.14/percona-server-5.7.14-7/sql/filesort.cc:333
        breakpoint already hit 1 time

其中断点3代表快速排序命中，断点4代表堆排序命中。

四、额外的测试

如上所述我们可以将结果的返回定义为三种方式，我们将在这里测试这三种方式的得到数据不同。

使用索引避免排序的结果

语句：
select * from testse force index(nu) order by nu;

mysql> desc select *  from testse force index(nu) order by  nu;
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | testse | NULL       | index | NULL          | nu   | 5       | NULL |   19 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select *  from testse force index(nu) order by  nu;
+-----+------+---------+
| id  | nu   | name    |
+-----+------+---------+
|   1 |    1 | gaopeng |
|   9 |    9 | gaopeng |
|   8 |   13 | gaopeng |
|  -1 |   14 | gaopeng |
|   0 |   14 | gaopeng |
|   6 |   14 | gaopeng |
|   7 |   14 | gaopeng |
|  12 |   14 | gaopeng |
| 100 |   14 | gaopeng |
| 111 |   14 | gaopeng |
|  13 |   15 | gaopeng |
|  14 |   16 | gaopeng |
|  10 |   19 | gaopeng |
|   4 |   20 | gaopeng |
|  11 |   20 | gaopeng |
|  15 |   20 | gaopeng |
|   5 |   24 | gaopeng |
|   3 |   55 | gaopeng |
|   2 |   99 | gaopeng |
+-----+------+---------+
19 rows in set (0.01 sec)

使用快速排序的结果

语句：
select * from testse order by nu;

因为我前面设置了断点，其断点命中如下：

Breakpoint 3, filesort (thd=0x7fff300128c0, filesort=0x7fff30963e90, sort_positions=false, examined_rows=0x7ffff01158a0, found_rows=0x7ffff0115898, 
    returned_rows=0x7ffff0115890) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/filesort.cc:359
359         DBUG_PRINT("info", ("filesort PQ is not applicable"));

可以看到PQ 没有开启，也就是堆排序没有使用使用的优先队列堆排序方式。那么它的结果是

mysql> desc select *  from testse order by  nu;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | testse | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   19 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select *  from testse order by  nu;
+-----+------+---------+
| id  | nu   | name    |
+-----+------+---------+
|   1 |    1 | gaopeng |
|   9 |    9 | gaopeng |
|   8 |   13 | gaopeng |
| 111 |   14 | gaopeng |
| 100 |   14 | gaopeng |
|  12 |   14 | gaopeng |
|   7 |   14 | gaopeng |
|   6 |   14 | gaopeng |
|   0 |   14 | gaopeng |
|  -1 |   14 | gaopeng |
|  13 |   15 | gaopeng |
|  14 |   16 | gaopeng |
|  10 |   19 | gaopeng |
|   4 |   20 | gaopeng |
|  11 |   20 | gaopeng |
|  15 |   20 | gaopeng |
|   5 |   24 | gaopeng |
|   3 |   55 | gaopeng |
|   2 |   99 | gaopeng |
+-----+------+---------+
19 rows in set (1.74 sec)

使用堆排序

语句：
select * from testse order by nu limit 8;

其断点命中

Breakpoint 4, filesort (thd=0x7fff300128c0, filesort=0x7fff3095ecc8, sort_positions=false, examined_rows=0x7ffff01158a0, found_rows=0x7ffff0115898, 
    returned_rows=0x7ffff0115890) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/filesort.cc:333
333         DBUG_PRINT("info", ("filesort PQ is applicable"));

可以看到PQ 开启了，也就是堆排序。

mysql> desc  select *  from testse order by  nu limit 8;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | testse | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   19 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select *  from testse order by  nu limit 8;
+-----+------+---------+
| id  | nu   | name    |
+-----+------+---------+
|   1 |    1 | gaopeng |
|   9 |    9 | gaopeng |
|   8 |   13 | gaopeng |
|  -1 |   14 | gaopeng |
|   0 |   14 | gaopeng |
|  12 |   14 | gaopeng |
| 111 |   14 | gaopeng |
|   6 |   14 | gaopeng |
+-----+------+---------+
8 rows in set (2.20 sec)

可以看到从前面8行来看，每种方法返回的数据是不一样的：

使用索引避免排序：
+-----+------+---------+
| id  | nu   | name    |
+-----+------+---------+
|   1 |    1 | gaopeng |
|   9 |    9 | gaopeng |
|   8 |   13 | gaopeng |
|  -1 |   14 | gaopeng |
|   0 |   14 | gaopeng |
|   6 |   14 | gaopeng |
|   7 |   14 | gaopeng |
|  12 |   14 | gaopeng |


使用快速排序：

+-----+------+---------+
| id  | nu   | name    |
+-----+------+---------+
|   1 |    1 | gaopeng |
|   9 |    9 | gaopeng |
|   8 |   13 | gaopeng |
| 111 |   14 | gaopeng |
| 100 |   14 | gaopeng |
|  12 |   14 | gaopeng |
|   7 |   14 | gaopeng |
|   6 |   14 | gaopeng |

使用堆排序：
+-----+------+---------+
| id  | nu   | name    |
+-----+------+---------+
|   1 |    1 | gaopeng |
|   9 |    9 | gaopeng |
|   8 |   13 | gaopeng |
|  -1 |   14 | gaopeng |
|   0 |   14 | gaopeng |
|  12 |   14 | gaopeng |
| 111 |   14 | gaopeng |
|   6 |   14 | gaopeng |
+-----+------+---------+

五、总结

可以看到在不同的获取数据的方式得到的数据是一样的，但是这只是重复数据排序的部分，这是排序算法的不稳定性决定的。快速排序适合大数据量排序，堆排序在少量排序上有优势，因此当order by limit n，n到了一个数量级的时候会切换排序算法，这个在执行计划是看不到的，具体使用那种算法是优化器通过函数check_if_pq_applicable进行判定的。
其次这只是一种造成排序数据不一致的情况还有一种情况是由于参数 max_length_for_sort_data的参数影响的一次排序和二次排序，这个有机会在研究一下代码。一般默认1024个字节还是很少会超过的。

六、堆排序算法简单说明

最后我还是想简单描述一下堆排序算法，也当复习一下。具体可以参考算法导论等书籍，我们以大顶堆为例，实际上任何一个待排序的数组都可以看成一棵完全二叉树，用算法导论的截图如下：

这棵树是满足大顶堆定义的，在大顶堆中有如下特性：

必须满足完全二叉树
关于完全二叉树参考

http://blog.itpub.net/7728585/viewspace-2125889/

很方便的根据父节点的位置计算出两个叶子结点的位置
如果父节点的位置为i/2，左子节点为 i，右子节点为i+1这是完全二叉树的特性决定
所有子节点都可以看做一个子堆那么所有结点都有
父节点>左子节点 && 父节点>右节点
很明显的可以找到最大的元素，就是整个堆的根结点

在这个算法中，最重要也是最主要的就是堆的维护，堆的构建。

维护：
维护：采用的是自上而下的维护，可以用递归完成。

这里电子版有点不清晰，黑色结点就是值4

对应我最后的代码 bigheapad函数

构建
构建：是采用自下而上的构建，构建就是不断循环的对各个父结点做维护，以达到对任何一个无序数组满足大顶堆的条件。因为下层的子树满足了大顶堆条件那么上层就一定满足大顶堆的条件。

对应我最后的代码bigheapbulid函数

排序

实际上排序就是将数组中的第一个数字也就是最大的数字和最后一个数字交换，然后再次做一次维护做好大顶堆结构即可，如果反复不断做这个操作那么整个素组都会排序完成。

我的函数参考biglimitn和bigheapsort函数。对于MySQL的源码中堆排序的代码存在于priority_queue.h文件中，其中可以看到一些方法如：

维护 heapify函数
构建 build_heap函数
排序 sort函数
当然源码的实现复杂得多，有兴趣的朋友可以深入。栈帧备查：

#0  Priority_queue<uchar*, std::vector<uchar*, Malloc_allocator<uchar*> >, <unnamed>::Mem_compare>::heapify(size_t, size_t) (this=0x7ffff0115650, i=0, last=8)
    at /root/mysql5.7.14/percona-server-5.7.14-7/include/priority_queue.h:124
#1  0x0000000000f5807a in Priority_queue<uchar*, std::vector<uchar*, Malloc_allocator<uchar*> >, <unnamed>::Mem_compare>::heapify(size_t) (this=0x7ffff0115650, i=0)
    at /root/mysql5.7.14/percona-server-5.7.14-7/include/priority_queue.h:147
#2  0x0000000000f57d1e in Priority_queue<uchar*, std::vector<uchar*, Malloc_allocator<uchar*> >, <unnamed>::Mem_compare>::update_top(void) (this=0x7ffff0115650)
    at /root/mysql5.7.14/percona-server-5.7.14-7/include/priority_queue.h:354
#3  0x0000000000f57814 in Bounded_queue<uchar*, uchar*, Sort_param, <unnamed>::Mem_compare>::push(uchar *) (this=0x7ffff0115650, element=0x7fff309166a0 "o")
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/bounded_queue.h:106
#4  0x0000000000f52da7 in find_all_keys(Sort_param *, QEP_TAB *, Filesort_info *, IO_CACHE *, IO_CACHE *, Bounded_queue<uchar*, uchar*, Sort_param, <unnamed>::Mem_compare> *, ha_rows *) (param=0x7ffff01154c0, qep_tab=0x7fff309268e8, fs_info=0x7ffff0115550, chunk_file=0x7ffff0115200, tempfile=0x7ffff0115360, pq=0x7ffff0115650, 
    found_rows=0x7ffff0115898) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/filesort.cc:1013
#5  0x0000000000f51165 in filesort (thd=0x7fff30000bc0, filesort=0x7fff30926bd8, sort_positions=false, examined_rows=0x7ffff01158a0, found_rows=0x7ffff0115898, 
    returned_rows=0x7ffff0115890) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/filesort.cc:425

下面是一个我以前参考算法导论第六章堆排序写的一个实现，其中包含了大顶堆和小顶堆，供大家参考：


/*************************************************************************
  > File Name: heapsort.c
  > Author: gaopeng QQ:22389860 all right reserved
  > Mail: gaopp_200217@163.com
  > Created Time: Sun 08 Jan 2017 11:22:14 PM CST
 ************************************************************************/

#include<stdio.h>
#include<stdlib.h>

#define LEFT(i) i<<1
#define RIGTH(i) (i<<1)+1
//堆排序的性能不及快速排序但是在某些情况下非常有用
//数据库的order by limit使用了优先队列，基于堆排序

int swap(int k[],int i,int j)
{
        int temp;

        temp = k[i];
        k[i] = k[j];
        k[j] = temp;
        return 0;
}


int bigheapad(int k[],int s,int n) //s=5,n=10 自上而下的维护
{
        /*
         * one:
         int i;
         int temp = k[s]; //temp=9=k[4] 父节点值保存到temp
         for(i=2*s;i<=n;i=i*2)// i=8
         {
         if(i<n && k[i]<k[i+1])//如果左子节点小于右子节点
         {
         i++; //右节点
         }

         if(temp>=k[i])
         {
         break;
         }

         k[s] = k[i];
         s = i;
         }

         k[s] = temp;
         */
        // two: 参考算法导论P155页,整个方法更容易理解其原理，调整使用逐层下降的方法进行调整
        int l; //s 左节点编号
        int r; //s 右节点编号
        int largest; 

        l=LEFT(s); //左节点编号 10
        r=RIGTH(s);//右节点编号 11

        if(s<=n/2) // n/2为最小节点编号的父亲节点 如果s大于这个值说明这个节点不会有任何子节点不需要进行调整 ！！,这是整个算法的核心中的核心。搞了我老半天
        {
                if (l<=n && k[l] > k[s])
                {
                        largest = l; //10
                }
                else
                {
                        largest = s;
                }

                if(r<=n && k[r] > k[largest]) //11<=10 不成立直接不运行
                {
                        largest = r;
                }

                if(largest != s) //如果较大的数字在孩子节点则交换父节点和孩子节点的值，否则不动
                {
                        swap(k,largest,s); //这里做孩子节点和父节点的交换
                        bigheapad(k,largest,n); //这里做递归下层操作，需要做到本次操作不会破坏堆的特性
                }
        }
return 0;
}


int bigheapbulid(int k[],int n) // bigheapbulid(a,10);
{
        int i;
        for(i=n/2;i>0;i--)//采用自底向上的方法构建 算法导论P156 EXP 1:i= n/2 p:4 l:8 r:9 2: i = p:3 l:6 r:7  n/2刚好是最后一个节点的父亲节点所以自下而上
        { //eg 10  i=5 4 3 2 1
                bigheapad(k,i,n);  //i=5 n=10 
        }
return 0;

}

int bigheapsort(int k[],int n) //sort的过程就是将最大元素放到最后，然后逐层下降的方法进行调整
{
        int i;
        for(i=n;i>1;i--)
        {
                swap(k,1,i); // 这里k[1] 就是最大值了，我们将他交换到最后面
                bigheapad(k,1,i-1);//重新构建大顶堆
        }
return 0;
}

int biglimitn(int k[],int n,int limitn)//limit 也是我关心的 这里明显可以看到他的优势实际它不需要对整个数组排序，你要多少我排多少给你就好，也是最大元素放到最后，然后逐层下降的方法进行调整的原理
{
        int i;
        for(i=n;i>n-limitn;i--)
        {
                swap(k,1,i);
                bigheapad(k,1,i-1);
        }
return 0;
}

int smallheapad(int k[],int s,int n) //s=4,n=9
{

        int l; //s 左节点编号
        int r; //s 右节点编号
        int smallest; 

        l=LEFT(s); //左节点编号
        r=RIGTH(s);//右节点编号

        if(s<=n/2) // n/2为最小节点编号的父亲节点 如果s大于这个值说明这个节点不会有任何子节点不需要进行调整 ！！
        {

                if (l<=n && k[l] < k[s])
                {

                        smallest = l;
                }
                else
                {

                        smallest = s;
                }

                if(r<=n && k[r] < k[smallest])
                {

                        smallest = r;
                }

                if(smallest != s)
                {

                        swap(k,smallest,s);
                        smallheapad(k,smallest,n); //对数据调整后可能的子节点树继续进行调整直到达到递归退出条件
                }
        } 
return 0;
}


int smallheapbulid(int k[],int n)
{

        int i;
        for(i=n/2;i>0;i--)
        {

                smallheapad(k,i,n); 
        }
return 0;
}

int smallheapsort(int k[],int n) 
{

        int i;
        for(i=n;i>1;i--)
        {

                swap(k,1,i);
                smallheapad(k,1,i-1);
        }
return 0;
}

int smalllimitn(int k[],int n,int limitn)
{

        int i;
        for(i=n;i>n-limitn;i--)
        {

                swap(k,1,i);
                smallheapad(k,1,i-1);
        }
return 0;
}


int main()
{

        int i,a[11]={0,999,3,2,9,34,5,102,90,2222,1}; //测试数据 a[0]不使用
        int b[11]={0,999,3,2,9,999,888888,102,90,2222,111};//测试数据 b[0]不使用
        bigheapbulid(a,10);
        biglimitn(a,10,3); 

        printf("大顶堆:\n");
        printf("order by desc a array limit 3 result:");
        for(i=10;i>10-3;i--)
        {
                printf("%d ",a[i]);
        }
        printf("\n");
        bigheapbulid(b,10);
        printf("max values b array reulst:");
        printf("%d \n",b[1]);

        smallheapbulid(a,10);
        smalllimitn(a,10,3);
        printf("小顶堆:\n");
        printf("order by asc a array limit 3 result:");
        for(i=10;i>10-3;i--)
        {
                printf("%d ",a[i]);
        }
        printf("\n");
        smallheapbulid(b,10);
        printf("min values b array reulst:");
        printf("%d \n",b[1]);
return 0;
}

MySQL:关于排序order by limit值不稳定的说明（1）

水平有限，有过有误请谅解和指正，仅仅作为抛砖引玉。谢谢！

一、问题抛出

二、问题原因

三、如何确定是使用的那种方法返回排序的结果。

四、额外的测试

五、总结

六、堆排序算法简单说明

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像