mysql高效索引覆盖索引_高性能的MySQL（5）索引策略-覆盖索引与索引排序

一、覆盖索引

索引是一种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要回表查询呢？

如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引的好处：

1、索引条目通常远小于数据行大小，所以如果只需要读取索引，就极大的减少数据访问量。这对MyISAM尤其正确，因为MyISAM能压缩索引以变得更小。

2、因为索引是按照顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少很多。

3、由于InnoDB的聚簇索引，覆盖索引对InnoDB特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

覆盖索引必须要存储索引列的值，而哈希索引、空间索引、和全文索引都不能存储列的值，所以MySQL只能使用B-Tree索引做覆盖索引。

当发起一个索引覆盖的查询时，在EXPLAIN的Extra列可以看到Usingindex的信息。例如：

如果索引覆盖了WHERE条件中的字段，但不是整个查询涉及的字段，看看是什么情况

没有任何索引能覆盖这个查询，因为查询从表中选择了所有的列，而没有任何索引覆盖所有的列。但是索引还是用到了。

接下来可以对比一下，可以使用聚簇索引的InnoDB和MyISAM对覆盖索引的区别。

首先看MyISAM表，表结构如下

看一下如下这个查询，没有用到覆盖索引

对同样结构的InnoDB引擎，来看下会有什么不同的结果。

同样的查询，只是表引擎不一样，看看结果

这是因为InnoDB的二级索引的叶子节点包含了主键的值，这意味着InnoDB的二级索引可以有效的利用这些额外的主键来覆盖查询。

由于InnoDB的聚簇索引，虽然查询条件的索引列并不包含主键，但是也能够做到对主键做覆盖查询。

二、使用索引扫描来排序

MySQL有两种方式可以生成有序结果。

1、通过排序操作

2、按索引顺序扫描

如果EXPLAIN出来的type列的值为“index”，则说明使用了索引扫描排序。

MySQL可以使用同一个索引既满足排序，有用于查找行，设计索引时应该进可能的满足这两种任务才是最好的。

只有当索引的顺序和ORDERBY的顺序完全一致，并且所有列的排序方向都一样时，才能使用索引来对结果进行排序。如果是关联多个张表，则只有ORDERBY子句引用的字段全部是第一个表时，才能使用索引排序。同时ORDERBY也需要满足最左前缀的要求。

有一种情况下ORDERBY可以不满足最左前缀要求，那就是前导列为常量的时候，接下来我们用例子来看看。

虽然ORDERBY子句不满足最左前缀，但是依然可以索引排序，这是因为索引的第一列被指定为一个常数。

如果不是常数，不会使用索引排序

下面这也可以使用索引排序

不能使用索引排序的查询

1、查询使用了不同的排序方向，索引列都是正序定义的

2、排序字段不在索引列

3、无法组成最左前缀

4、某个列有范围查询

DONE!!

本文转自shayang8851CTO博客，原文链接：http://blog.51cto.com/janephp/1311417，如需转载请自行联系原作者