MySQL高效索引之覆盖索引(2)

CREATE TABLE `actor` (
  `actor_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL DEFAULT '',
  `password` varchar(16) NOT NULL DEFAULT '',
  PRIMARY KEY (`actor_id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
insert into actor(name,password) values ('cat01','1234567'),('cat02','1234567'),('ddddd','1234567'),('aaaaa','1234567');
1、 explain select actor_id from actor order by actor_id \G

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)

2、explain select actor_id from actor order by password \G
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)
 3、explain select actor_id from actor order by name \G

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: 50
          ref: NULL
        rows: 4
        Extra: Using index
1 row in set (0.00 sec)

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

Linux公社的RSS地址：https://www.linuxidc.com/rssFeed.aspx