MySQL中利用索引对数据进行排序的基础教程

mysql中，有两种方式生成有序结果集：一是使用filesort，二是按索引顺序扫描。利用索引进行排序操作是非常快的，而且可以利用同一索引同时进行查找和排序操作。当索引的顺序与order by中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序。如果查询是连接多个表，仅当order by中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort。

mysql索引通常是被用于提高where条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。
mysql也能利用索引来快速地执行order by和group by语句的排序和分组操作。
通过索引优化来实现mysql的order by语句优化：

create table actor(

actor_id int unsigned not null auto_increment,

name   varchar(16) not null default '',

password    varchar(16) not null default '',

primary key(actor_id),

 key   (name)

) engine=innodb

insert into actor(name,password) values('cat01','1234567');

insert into actor(name,password) values('cat02','1234567');

insert into actor(name,password) values('ddddd','1234567');

insert into actor(name,password) values('aaaaa','1234567');

 

mysql> explain select actor_id from actor order by actor_id \g

*************************** 1. row ***************************

      id: 1

 select_type: simple

    table: actor

     type: index

possible_keys: null

     key: primary

   key_len: 4

     ref: null

     rows: 4

    extra: using index

1 row in set (0.00 sec)

 

mysql> explain select actor_id from actor order by password \g

*************************** 1. row ***************************

      id: 1

 select_type: simple

    table: actor

     type: all

possible_keys: null

     key: null

   key_len: null

     ref: null

     rows: 4

    extra: using filesort

1 row in set (0.00 sec)

 

mysql> explain select actor_id from actor order by name \g

*************************** 1. row ***************************

      id: 1

 select_type: simple

    table: actor

     type: index

possible_keys: null

     key: name

   key_len: 18

     ref: null

     rows: 4

    extra: using index

1 row in set (0.00 sec)

下面来罗列一些常见的索引对orfer by的优化情况：

1、如果一个sql语句形如：

select [column1],[column2],…. from [table] order by [sort];

在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。
2、where + order by的索引优化，形如：

select [column1],[column2],…. from [table] where [columnx] = [value] order by [sort];

建立一个联合索引(columnx,sort)来实现order by 优化。
注意：如果columnx对应多个值，如下面语句就无法利用索引来实现order by的优化

select [column1],[column2],…. from [table] where [columnx] in ([value1],[value2],…) order by[sort];

3、where+ 多个字段order by

select * from [table] where uid=1 order x,y limit 0,10;

建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。
mysql order by不能使用索引来优化排序的情况
* 对不同的索引键做 order by ：(key1,key2分别建立索引)

select * from t1 order by key1, key2;

* 在非连续的索引键部分上做 order by：(key_part1,key_part2建立联合索引;key2建立索引)

select * from t1 where key2=constant order by key_part2;

* 同时使用了 asc 和 desc：(key_part1,key_part2建立联合索引)

select * from t1 order by key_part1 desc, key_part2 asc;

* 用于搜索记录的索引键和做 order by 的不是同一个：(key1,key2分别建立索引)

select * from t1 where key2=constant order by key1;

* 如果在where和order by的栏位上应用表达式(函数)时，则无法利用索引来实现order by的优化

select * from t1 order by year(logindate) limit 0,10;

当mysql不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。对于filesort，mysql有两种排序算法。
1.两遍扫描算法(two passes)
实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的columns。
注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机i/o操作。另一方面，内存开销较小。
2. 一次扫描算法(single pass)
该算法一次性将所需的columns全部取出，在内存中排序后直接将结果输出。
注：从 mysql 4.1 版本开始使用该算法。它减少了i/o的次数，效率较高，但是内存开销也较大。如果我们将并不需要的columns也取出来，就会极大地浪费排序过程所需要的内存。在 mysql 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 mysql 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，mysql 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 query 中仅仅取出需要的 columns 是非常有必要的。

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