共计 4614 个字符，预计需要花费 12 分钟才能阅读完成。
概念
如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index)，也就是平时所说的不需要回表操作
判断标准
使用 explain，可以通过输出的 extra 列来判断，对于一个索引覆盖查询，显示为 using index,MySQL 查询优化器在执行查询前会决定是否有索引覆盖查询
注意
1、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值
2、Hash 和 full-text 索引不存储值，因此 MySQL 只能使用 B -TREE
3、并且不同的存储引擎实现覆盖索引都是不同的
4、并不是所有的存储引擎都支持它们
5、如果要使用覆盖索引，一定要注意 SELECT 列表值取出需要的列，不可以是 SELECT *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降，不能为了利用覆盖索引而这么做
InnoDB
1、覆盖索引查询时除了除了索引本身的包含的列，还可以使用其默认的聚集索引列
2、这跟 INNOB 的索引结构有关系，主索引是 B + 树索引存储，也即我们所说的数据行即索引，索引即数据
3、对于 INNODB 的辅助索引，它的叶子节点存储的是索引值和指向主键索引的位置，然后需要通过主键在查询表的字段值，所以辅助索引存储了主键的值
4、覆盖索引也可以用上 INNODB 默认的聚集索引
5、innodb 引擎的所有储存了主键 ID，事务 ID，回滚指针, 非主键 ID，他的查询就会是非主键 ID 也可覆盖来取得主键 ID
覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不用读取数据有以下一些优点
1、索引项通常比记录要小，所以 MySQL 访问更少的数据
2、索引都按值的大小顺序存储，相对于随机访问记录，需要更少的 I /O
3、大多数据引擎能更好的缓存索引，比如 MyISAM 只缓存索引
4、覆盖索引对于 InnoDB 表尤其有用，因为 InnoDB 使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了
在 sakila 的 inventory 表中，有一个组合索引 (store_id,film_id)，对于只需要访问这两列的查 询，MySQL 就可以使用索引，如下
表结构
CREATE TABLE `inventory` (
`inventory_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`film_id` smallint(5) unsigned NOT NULL,
`store_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`inventory_id`),
KEY `idx_fk_film_id` (`film_id`),
KEY `idx_store_id_film_id` (`store_id`,`film_id`),
CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8 |
查询语句
mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: inventory
type: index
possible_keys: NULL
key: idx_store_id_film_id
key_len: 3
ref: NULL
rows: 4581
Extra: Using index
1 row in set (0.03 sec)
在大多数引擎中，只有当查询语句所访问的列是索引的一部分时，索引才会覆盖。但是，InnoDB 不限于此，InnoDB 的二级索引在叶子节点中存储了 primary key 的值。因此，sakila.actor 表使用 InnoDB，而且对于是 last_name 上有索引，所以，索引能覆盖那些访问 actor_id 的查 询，如下
mysql> EXPLAIN SELECT actor_id, last_name FROM sakila.actor WHERE last_name = ‘HOPPER’\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: const
rows: 2
Extra: Using where; Using index
1 row in set (0.00 sec)
使用索引进行排序
MySQL 中，有两种方式生成有序结果集：一是使用 filesort，二是按索引顺序扫描
利用索引进行排序操作是非常快的，而且可以利用同一索引同时进 行查找和排序操作。当索引的顺序与 ORDER BY 中的列顺序相同且所有的列是同一方向 (全部升序或者全部降序) 时，可以使用索引来排序，如果查询是连接多个表，仅当 ORDER BY 中的所有列都是第一个表的列时才会使用索引，其它情况都会使用 filesort
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”
：
