Order By文件排序和索引排序

在MySQL中的ORDER BY有两种排序实现方式:

1、利用有序索引获取有序数据

2、文件排序

在使用explain分析查询的时候，利用有序索引获取有序数据显示Using index。而文件排序显示Using filesort。

1.利用有序索引获取有序数据

          取出满足过滤条件作为排序条件的字段，以及可以直接定位到行数据的行指针信息，在 Sort Buffer 中进行实际的排序操作，然后利用排好序的数据根据行指针信息返回表中取得客户端请求的其他字段的数据，再返回给客户端.

         注意：mysql在查询时最多只能使用一个索引。因此，如果WHERE条件已经占用了索引，那么在排序中就不使用索引了。

按照索引对结果进行排序：order by 使用索引是有条件：

1)  返回选择的字段，即只包括在有选择的此列上（select后面的字段），不一定适应*的情况)：

1. CREATE TABLE `test` (  
2.   `id` int(11) NOT NULLAUTO_INCREMENT,  
3.   `rdate` datetime NOT NULL,  
4.   `inventid` int(11) NOT NULL,  
5.   `customerid` int(11) NOT NULL,  
6.   `staffid` int(11) NOT NULL,  
7.   `data` varchar(20) NOT NULL,  
8.   PRIMARY KEY (`id`),  
9.   UNIQUE KEY `rdate`(`rdate`,`inventid`,`customerid`),  
10.   KEY `inventid` (`inventid`),  
11.   KEY `customerid` (`customerid`),  
12.   KEY `staffid` (`staffid`)  
13. ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1  

如：

1. mysql>   
2. explain select inventid from test where rdate='2011-12-1400:00:00' order by  inventid , customerid;  

+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

| id | select_type | table | type | possible_keys |key    | key_len |ref      | rows |Extra                    |

+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

|  1 |  SIMPLE      | test    |ref   |        rdate          |rdate  |      8     |const |   10   | Using where; Using index |

+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

1 row in set (0.00 sec)

Select选择的列使用索引，而下面不使用索引：

mysql> explain select * from test where rdate='2011-12-14 00:00:00'order by  inventid , customerid ;

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+

| id | select_type | table | type | possible_keys | key     | key_len|ref     | rows | Extra                       |

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+

|  1 | SIMPLE      | test   | ALL  | rdate               | NULL  | NULL    |NULL |  13   |Using where;Using filesort|

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+

2)  只有当ORDER BY中所有的列必须包含在相同的索引，并且索引的顺序和order by子句中的顺序完全一致，并且所有列的排序方向（升序或者降序）一样才有，（混合使用ASC模式和DESC模式则不使用索引）

1. mysql>   
2. xplain select inventid from test order byrdate, inventid ;  

+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+

| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |

+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+

|  1 | SIMPLE      | test  | index | NULL          | rdate |16      | NULL |   13 |Using index|

+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+

1 row in set (0.00 sec)

1. mysql>   
2. explain select inventid from test where rdate="2011-12-16" order by  inventid ,staffid;  

+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------

| id | select_type | table | type | possible_keys |key   | key_len | ref   | rows | Extra                       |

+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------

|  1 | SIMPLE      | test | ref  | rdate         | rdate | 8       | const |    1 |Using where;Using filesort

+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------

1 row in set (0.00 sec)

由于rdate, inventid使用了同一个索引。排序使用到了索引。这个也是满足了前缀索引。但是order  by  inventid ,staffid;就不是使用了索引，因为staffid和inventid不是同一个索引

3)     where 语句与ORDER BY语句组合满足最左前缀:

1. mysql>   
2. explain select inventid from test whererdate="2011-12-16" order by  inventid ;  

+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                    |

+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

|  1 | SIMPLE      | test | ref  | rdate         | rdate | 8       | const |    1 | Using where;Using index |

+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

1 row in set (0.00 sec)

4)     如果查询联接了多个表，只有在order by子句的所有列引用的是第一个表的列才可以。

5)     在其他的情况下，mysql使用文件排序   例如：

1）   where语句与order by语句，使用了不同的索引

2）   where语句和order by同时使用1个索引，此时where已经做好排序了，order by将不会使用index排序也不会使用文件排序

3）   检查的行数过多，且没有使用覆盖索引

4）   ORDER BY中的列不包含在相同的索引，也就是使用了不同的索引

5）   对索引列同时使用了ASC和DESC

6）   where语句或者ORDER BY语句中索引列使用了表达式，包括函数表达式

7）   where 语句与ORDER BY语句组合满足最左前缀，但where语句中使用了条件查询。查见第10句,虽然where与order by构成了索引最左有缀的条件，但是where子句中使用的是条件查询。

mysql> explain select inventid from test where  rdate>"2011-12-16" order by  inventid;

+----+-------------+-------+-------+---------------+-------+---------+------+------+----------------

| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                                    

+----+-------------+-------+-------+---------------+-------+---------+------+------+----------------

|  1 |SIMPLE      | test  | range | rdate         | rdate | 8       | NULL |    1 | Using where; Using index;Usingfilesort |

+----+-------------+-------+-------+---------------+-------+---------+------+------+----------------

1 row in set (0.00sec)

        8）   当使用left join，使用右边的表字段排序

2.文件排序

       这个 filesort 并不是说通过磁盘文件进行排序，而只是告诉我们进行了一个排序操作。即在MySQL Query Optimizer 所给出的执行计划(通过 EXPLAIN 命令查看)中被称为文件排序（filesort）

      文件排序是通过相应的排序算法,将取得的数据在内存中进行排序: MySQL需要将数据在内存中进行排序，所使用的内存区域也就是我们通过sort_buffer_size 系统变量所设置的排序区。这个排序区是每个Thread 独享的，所以说可能在同一时刻在MySQL 中可能存在多个 sort buffer 内存区域。

在MySQL中filesort 的实现算法实际上是有两种：

双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。

在MySQL4.1版本之前只有第一种排序算法双路排序，第二种算法是从MySQL4.1开始的改进算法，主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作，将两次变成了一次，但相应也会耗用更多的sortbuffer 空间。当然，MySQL4.1开始的以后所有版本同时也支持第一种算法，

MySQL主要通过比较我们所设定的系统参数 max_length_for_sort_data的大小和Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data更大，则使用第二种优化后的算法，反之使用第一种算法。所以如果希望 ORDER BY 操作的效率尽可能的高，一定要主义max_length_for_sort_data 参数的设置。曾经就有同事的数据库出现大量的排序等待，造成系统负载很高，而且响应时间变得很长，最后查出正是因为MySQL 使用了传统的第一种排序算法而导致，在加大了max_length_for_sort_data 参数值之后，系统负载马上得到了大的缓解，响应也快了很多。