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

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)

SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort]"htmlcode">

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort]"htmlcode">

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort]"htmlcode">

SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10"htmlcode">

SELECT * FROM t1 ORDER BY key1, key2"htmlcode">

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2"htmlcode">

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC"htmlcode">

SELECT * FROM t1 WHERE key2=constant ORDER BY key1"htmlcode">

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”。

        

            
上一篇：CentOS下重置MySQL的root密码的教程
            
下一篇：快速学习MySQL索引的入门超级教程