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

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

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

createtableactor( actor_idintunsignedNOTNULLAUTO_INCREMENT, namevarchar(16)NOTNULLDEFAULT'', passwordvarchar(16)NOTNULLDEFAULT'', PRIMARYKEY(actor_id), KEY(name) )ENGINE=InnoDB insertintoactor(name,password)values('cat01','1234567'); insertintoactor(name,password)values('cat02','1234567'); insertintoactor(name,password)values('ddddd','1234567'); insertintoactor(name,password)values('aaaaa','1234567');

mysql>explainselectactor_idfromactororderbyactor_idG ***************************1.row*************************** id:1 select_type:SIMPLE table:actor type:index possible_keys:NULL key:PRIMARY key_len:4 ref:NULL rows:4 Extra:Usingindex 1rowinset(0.00sec)

mysql>explainselectactor_idfromactororderbypasswordG ***************************1.row*************************** id:1 select_type:SIMPLE table:actor type:ALL possible_keys:NULL key:NULL key_len:NULL ref:NULL rows:4 Extra:Usingfilesort 1rowinset(0.00sec)

mysql>explainselectactor_idfromactororderbynameG ***************************1.row*************************** id:1 select_type:SIMPLE table:actor type:index possible_keys:NULL key:name key_len:18 ref:NULL rows:4 Extra:Usingindex 1rowinset(0.00sec)

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

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

SELECT[column1],[column2],….FROM[TABLE]ORDERBY[sort];

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

SELECT[column1],[column2],….FROM[TABLE]WHERE[columnX]=[value]ORDERBY[sort];

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

SELECT[column1],[column2],….FROM[TABLE]WHERE[columnX]IN([value1],[value2],…)ORDERBY[sort];

3、WHERE+多个字段ORDERBY

SELECT*FROM[table]WHEREuid=1ORDERx,yLIMIT0,10;

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

SELECT*FROMt1ORDERBYkey1,key2;

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

SELECT*FROMt1WHEREkey2=constantORDERBYkey_part2;

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

SELECT*FROMt1ORDERBYkey_part1DESC,key_part2ASC;

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

SELECT*FROMt1WHEREkey2=constantORDERBYkey1;

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

SELECT*FROMt1ORDERBYYEAR(logindate)LIMIT0,10;

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

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

上述就是数据库技术:MySQL中利用索引对数据进行排序的基础教程分享的全部内容，如果对大家有所用处且需要了解更多关于mysql数据库学习教程，希望大家多多关注—计算机技术网(www.ctvol.com)! 您可能感兴趣的文章:MySQLInnoDB二级索引的排序示例详解mysql添加索引mysql如何创建索引MySQL索引类型