mysql+filesort+index_mysqlfilesort介绍

在MySQL中的ORDER BY有两种排序实现方式: 1、利用有序索引获取有序数据 2、文件排序 在使用explain分析查询的时候，利用有序索引获取有序数据显示Using index。而文件排序显示Using filesort。 1.利用有序索引获取有序数据 取出满足过滤条件作为排序条件的字

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

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

2、文件排序

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

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

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

这种方式，在使用explain分析查询的时候，显示Using index。而文件排序显示Using filesort。

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

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

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

CREATE TABLE `test` (

`id` int(11) NOT NULLAUTO_INCREMENT,

`rdate` datetime NOT NULL,

`inventid` int(11) NOT NULL,

`customerid` int(11) NOT NULL,

`staffid` int(11) NOT NULL,

`data` varchar(20) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `rdate`(`rdate`,`inventid`,`customerid`),

KEY `inventid` (`inventid`),

KEY `customerid` (`customerid`),

KEY `staffid` (`staffid`)

) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1

mysql>

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模式则不使用索引)

mysql>

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)

mysql>

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语句组合满足最左前缀:

mysql>

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) 检查的行数过多，且没有使用覆盖索引

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

4) 对索引列同时使用了ASC和DESC

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

6) 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)

7) 当使用left join，使用右边的表字段排序

2.文件排序

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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！