MySQL Sort原理

r囧r小猫 2022-09-28 13:09 146阅读 0赞

# MySQL Sort 分页 #

## 背景 ##

6.5号，小编在 Aliyun 的论坛中发现一位开发者提的一个问题，说 RDS 发现了一个超级大BUG，吓的小编一身冷汗 = =!!  
赶紧来看看，背景是一个RDS用户创建了一张表，在一个都是NULL值的非索引字段上进行了排序并分页，用户发现第二页和第一页的数据有重复，然后以为是NULL值的问题，把这个字段都更新成相同的值，发现问题照旧。详细的信息可以登录阿里云的官方论坛查看。

小编进行了尝试，确实如此，并且5.5的版本和5.6的版本行为不一致，所以，必须要查明原因。

## 原因调查 ##

在MySQL 5.6的版本上，优化器在遇到order by limit语句的时候，做了一个优化，即使用了priority queue。参考伪代码：

while (get_next_sortkey())
         {
           if (using priority queue)
             push sort key into queue
           else
           {
             if (no free space in sort_keys buffers)
             {
               sort sort_keys buffer;
               dump sorted sequence to 'tempfile';
               dump BUFFPEK describing sequence location into 'buffpek_pointers';
             }
             put sort key into 'sort_keys';
           }
         }
         if (sort_keys has some elements && dumped at least once)
           sort-dump-dump as above;
         else
           don't sort, leave sort_keys array to be sorted by caller.

使用 priority queue 的目的，就是在不能使用索引有序性的时候，如果要排序，并且使用了limit n，那么只需要在排序的过程中，保留n条记录即可，这样虽然不能解决所有记录都需要排序的开销，但是只需要 sort buffer 少量的内存就可以完成排序。

之所以5.6出现了第二页数据重复的问题，是因为 priority queue 使用了堆排序的排序方法，而堆排序是一个不稳定的排序方法，也就是相同的值可能排序出来的结果和读出来的数据顺序不一致。

5.5 没有这个优化，所以也就不会出现这个问题。

## 解决方法 ##

1.  索引排序字段
    
    之前的月报中，我们讨论过三星索引的设计，其中第二条就是利用索引的有序性，如果用户在字段添加上索引，就直接按照索引的有序性进行读取并分页，从而可以规避遇到的这个问题。
2.  正确理解分页
    
    还是要正确理解分页，分页是建立在排序的基础上，进行了数量范围分割。排序是数据库提供的功能，而分页却是衍生的出来的应用需求。在MySQL和Oracle的官方文档中提供了limit n和rownum < n的方法，但却没有明确的定义分页这个概念。还有重要的一点，虽然上面的解决方法可以缓解用户的这个问题，但按照用户的理解，依然还有问题：比如，这个表插入比较频繁，用户查询的时候，在read-committed的隔离级别下，第一页和第二页仍然会有重合。
    
    分页一直都有这个问题，我们看分页常用的场景：1)早期的论坛 2)个人交易记录。这些场景都对数据分页都没有非常高的准确性要求。

## 究竟是不是BUG ##

究竟归于bug问题还是用户使用理解上的问题？

小编觉得应该分开看待这个问题，如果是排序的问题，那就算是BUG，如果是分页的这个问题，那它确实完成了order by的功能，也完成了limit n功能，那就不能说它是BUG，分页就纯粹变成了用户理解的问题了。

## 用户在使用数据库的时候常见的一些问题： ##

1.  不加order by的时候的排序问题
    
    用户在使用Oracle或MySQL的时候，发现MySQL总是有序的，Oracle却很混乱，这个主要是因为Oracle是堆表，MySQL是索引聚簇表的原因。所以没有order by的时候，数据库并不保证记录返回的顺序性，并且不保证每次返回都一致的。
2.  分页问题
    
    分页重复的问题，就如前面所描述的，分页是在数据库提供的排序功能的基础上，衍生出来的应用需求，数据库并不保证分页的重复问题。
3.  NULL值和空串问题
    
    不同的数据库对于NULL值和空串的理解和处理是不一样的，比如Oracle NULL和NULL值是无法比较的，既不是相等也不是不相等，是未知的。而对于空串，在插入的时候，MySQL是一个字符串长度为0的空串，而Oracle则直接进行NULL值处理。

## 原出处 ##

[数据库内核月报 － 2015 / 06][_ 2015 _ 06]

# MySQL排序原理与案例分析 #

## 前言 ##

排序是数据库中的一个基本功能，MySQL也不例外。用户通过Order by语句即能达到将指定的结果集排序的目的，其实不仅仅是Order by语句，Group by语句，Distinct语句都会隐含使用排序。本文首先会简单介绍SQL如何利用索引避免排序代价，然后会介绍MySQL实现排序的内部原理，并介绍与排序相关的参数，最后会给出几个“奇怪”排序例子，来谈谈排序一致性问题，并说明产生现象的本质原因。

## 排序优化与索引使用 ##

为了优化SQL语句的排序性能，最好的情况是避免排序，合理利用索引是一个不错的方法。因为索引本身也是有序的，如果在需要排序的字段上面建立了合适的索引，那么就可以跳过排序的过程，提高SQL的查询速度。下面我通过一些典型的SQL来说明哪些SQL可以利用索引减少排序，哪些SQL不能。假设t1表存在索引`key1(key_part1,key_part2),key2(key2)`

### 可以利用索引避免排序的SQL ###

SELECT * FROM t1 ORDER BY key_part1,key_part2;
    SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
    SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;
    SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2;

### 不能利用索引避免排序的SQL ###

//排序字段在多个索引中，无法使用索引排序
    SELECT * FROM t1 ORDER BY key_part1,key_part2, key2;
    
    //排序键顺序与索引中列顺序不一致，无法使用索引排序
    SELECT * FROM t1 ORDER BY key_part2, key_part1;
    
    //升降序不一致，无法使用索引排序
    SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
    
    //key_part1是范围查询，key_part2无法使用索引排序
    SELECT * FROM t1 WHERE key_part1> constant ORDER BY key_part2;

## 排序实现的算法 ##

对于不能利用索引避免排序的SQL，数据库不得不自己实现排序功能以满足用户需求，此时SQL的执行计划中会出现“Using filesort”，这里需要注意的是filesort并不意味着就是文件排序，其实也有可能是内存排序，这个主要由sort\_buffer\_size参数与结果集大小确定。**MySQL内部实现排序主要有3种方式，常规排序，优化排序和优先队列排序，主要涉及3种排序算法：快速排序、归并排序和堆排序。**假设表结构和SQL语句如下：

CREATE TABLE t1(id int, col1 varchar(64), col2 varchar(64), col3 varchar(64), PRIMARY KEY(id),key(col1,col2));
    SELECT col1,col2,col3 FROM t1 WHERE col1>100 ORDER BY col2;

### 常规排序 ###

1.  从表t1中获取满足WHERE条件的记录
2.  对于每条记录，将记录的主键+排序键(id,col2)取出放入sort buffer
3.  如果sort buffer可以存放所有满足条件的(id,col2)对，则进行排序；否则sort buffer满后，进行排序并固化到临时文件中。(排序算法采用的是快速排序算法)
4.  若排序中产生了临时文件，需要利用归并排序算法，保证临时文件中记录是有序的
5.  循环执行上述过程，直到所有满足条件的记录全部参与排序
6.  扫描排好序的(id,col2)对，并利用id去捞取SELECT需要返回的列(col1,col2,col3)
7.  将获取的结果集返回给用户。

从上述流程来看，是否使用文件排序主要看sort buffer是否能容下需要排序的(id,col2)对，这个buffer的大小由`sort_buffer_size`参数控制。此外一次排序需要两次IO，一次是捞(id,col2),第二次是捞(col1,col2,col3)，由于返回的结果集是按col2排序，因此id是乱序的，通过乱序的id去捞(col1,col2,col3)时会产生大量的随机IO。**对于第二次MySQL本身一个优化，即在捞之前首先将id排序，并放入缓冲区**，这个缓存区大小由参数`read_rnd_buffer_size`控制，然后有序去捞记录，将随机IO转为顺序IO。

### 优化排序 ###

常规排序方式除了排序本身，还需要额外两次IO。**优化的排序方式相对于常规排序，减少了第二次IO。**主要区别在于，放入sort buffer不是(id,col2),而是(col1,col2,col3)。由于sort buffer中包含了查询需要的所有字段，因此排序完成后可以直接返回，无需二次捞数据。这种方式的代价在于，同样大小的sort buffer，能存放的(col1,col2,col3)数目要小于(id,col2)，如果sort buffer不够大，可能导致需要写临时文件，造成额外的IO。当然MySQL提供了参数`max_length_for_sort_data`，**只有当排序元组小于`max_length_for_sort_data`时，才能利用优化排序方式，否则只能用常规排序方式。**

### 优先队列排序 ###

为了得到最终的排序结果，无论怎样，我们都需要将所有满足条件的记录进行排序才能返回。那么相对于优化排序方式，是否还有优化空间呢？5.6版本针对Order by limit M，N语句，在空间层面做了优化，加入了一种新的排序方式–**优先队列，这种方式采用堆排序实现。**堆排序算法特征正好可以解limit M，N 这类排序的问题，虽然仍然需要所有元素参与排序，但是只需要M+N个元组的sort buffer空间即可，对于M，N很小的场景，基本不会因为sort buffer不够而导致需要临时文件进行归并排序的问题。对于升序，采用大顶堆，最终堆中的元素组成了最小的N个元素，对于降序，采用小顶堆，最终堆中的元素组成了最大的N的元素。

## 排序不一致问题 ##

### 案例1 ###

**Mysql从5.5迁移到5.6以后，发现分页出现了重复值。**

测试表与数据：

create table t1(id int primary key, c1 int, c2 varchar(128));
    insert into t1 values(1,1,'a');
    insert into t1 values(2,2,'b');
    insert into t1 values(3,2,'c');
    insert into t1 values(4,2,'d');
    insert into t1 values(5,3,'e');
    insert into t1 values(6,4,'f');
    insert into t1 values(7,5,'g');

假设每页3条记录，第一页limit 0,3和第二页limit 3,3查询结果如下：

![1][]

我们可以看到 id为4的这条记录居然同时出现在两次查询中，这明显是不符合预期的，而且在5.5版本中没有这个问题。产生这个现象的原因就是**5.6针对limit M,N的语句采用了优先队列，而优先队列采用堆实现**，比如上述的例子order by c1 asc limit 0，3 需要采用大小为3的大顶堆；limit 3，3需要采用大小为6的大顶堆。由于c1为2的记录有3条，而**堆排序是非稳定的(对于相同的key值，无法保证排序后与排序前的位置一致)，所以导致分页重复的现象。**为了避免这个问题，我们可以在排序中加上唯一值，比如主键id，这样由于id是唯一的，确保参与排序的key值不相同。将SQL写成如下：

select * from t1 order by c1,id asc limit 0,3;
    select * from t1 order by c1,id asc limit 3,3;

### 案例2 ###

两个类似的查询语句，除了返回列不同，其它都相同，但排序的结果不一致。

测试表与数据：

create table t2(id int primary key, status int, c1 varchar(255),c2 varchar(255),c3 varchar(255),key(c1));
    insert into t2 values(7,1,'a',repeat('a',255),repeat('a',255));
    insert into t2 values(6,2,'b',repeat('a',255),repeat('a',255));
    insert into t2 values(5,2,'c',repeat('a',255),repeat('a',255));
    insert into t2 values(4,2,'a',repeat('a',255),repeat('a',255));
    insert into t2 values(3,3,'b',repeat('a',255),repeat('a',255));
    insert into t2 values(2,4,'c',repeat('a',255),repeat('a',255));
    insert into t2 values(1,5,'a',repeat('a',255),repeat('a',255));

分别执行SQL语句：

select id,status,c1,c2 from t2 force index(c1) where c1>='b' order by status;
    select id,status from t2 force index(c1) where c1>='b' order by status;

执行结果如下：

![2][]

看看两者的执行计划是否相同

![3][]

为了说明问题，我在语句中加了force index的hint，确保能走上c1列索引。语句通过c1列索引捞取id，然后去表中捞取返回的列。根据c1列值的大小，记录在c1索引中的相对位置如下：

`(c1,id)===(b,6),(b,3),(c,5),(c,2)`，对应的status值分别为2 3 2 4。从表中捞取数据并按status排序，则相对位置变为`(6,2,b),(5,2,c),(3,3,c),(2,4,c)`，这就是第二条语句查询返回的结果，那么为什么第一条查询语句`(6,2,b),(5,2,c)`是调换顺序的呢？这里要看我之前提到的a.常规排序和b.优化排序中标红的部分，就可以明白原因了。由于第一条查询返回的列的字节数超过了`max_length_for_sort_data`，导致排序采用的是常规排序，而在这种情况下MYSQL将rowid排序，将随机IO转为顺序IO，所以返回的是5在前，6在后；而第二条查询采用的是优化排序，没有第二次捞取数据的过程，保持了排序后记录的相对位置。对于第一条语句，若想采用优化排序，我们将`max_length_for_sort_data`设置调大即可，比如2048。

![4][]

## 原出处 ##

[天士梦][Link 1]

--------------------

参考资料：  
如上  
备注：  
转载请注明出处：[http://blog.csdn.net/wsyw126/article/details/61428158][http_blog.csdn.net_wsyw126_article_details_61428158]  
作者：WSYW126

[_ 2015 _ 06]: http://mysql.taobao.org/monthly/2015/06/04/
[1]: /images/20220708/4ea2175032e244e1ac68d88d6e187849.png
[2]: /images/20220708/920d045075204e12b993a40f47a94d77.png
[3]: /images/20220708/2355a69d08504488a6c6a39ae472cd1f.png
[4]: /images/20220708/0b0f866a29d242fc85f3f2d3a867127d.png
[Link 1]: http://www.cnblogs.com/cchust/p/5304594.html
[http_blog.csdn.net_wsyw126_article_details_61428158]: http://blog.csdn.net/wsyw126/article/details/61428158