?

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

?

当查询语句的 order BY 条件和查询的执行计划中所利用的 Index 的索引键（或前面几个索引键）完全一致，且索引访问方式为 range,ref 或者 index 的时候，MySQL 可以利用索引顺序而直接取得已经排好序的数据。这种方式的 order BY 基本上可以说是最优的排序方式了,因为 MySQL 不需要进行实际的排序操作，需要注意的是使用索引排序也有很多限制。

?

当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”。

?

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

?

mysql> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `addtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `a` int(11) unsigned NOT NULL,
  `b` int(11) unsigned NOT NULL,
  `c` int(11) unsigned NOT NULL,
  `data` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`addtime`,`a`,`b`),
  KEY `a` (`a`),
  KEY `b` (`b`),
  KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8

?

执行查询1

?

mysql> explain ?select * from test where addtime='2015-10-13 15:38:32' order by ?a , b ;

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

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

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

| ?1 | SIMPLE ? ? ?| test ?| ref ?| u ? ? ? ? ? ? | u ? ?| 8 ? ? ? | const | ? ?4 | Using where |

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

? 执行查询2

?

mysql> explain ?select * from test where addtime='2015-10-13 15:38:32' order by ?a , b ,c;

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

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

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

| ?1 | SIMPLE ? ? ?| test ?| ref ?| u ? ? ? ? ? ? | u ? ?| 8 ? ? ? | const | ? ?4 | Using where; Using filesort |

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

使用filesort的情况

?

a、where语句与order by语句，使用了不同的索引

?

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

?

c、对索引列同时使用了ASC和DESC

?

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

?

e、where 语句与ORDER BY语句组合满足最左前缀，但where语句中使用了条件查询

?

f、 当使用left join，使用右边的表字段排序

2 利用内存/磁盘文件排序获取结果

?

由于没有可以利用的有序索引取得有序的数据，MySQL需要通过相应的排序算法，将取得的数据在sort_buffer_size 系统变量所设置大小的排序区进行排序，这个排序区是每个Thread 独享的，所以说可能在同一时刻在 MySQL 中可能存在多个 sort buffer 内存区域

?

MySQL中filesort 的实现算法有两种:

?

1) 双路排序: 是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序（but this will be essentially hit the table in random order and is not very fast）。

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

?

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

?