「Mysql索引原理（七）」覆盖索引

       通常大家都会根据查询的WHERE条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是WHERE条件部分。索引确实是一种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回到表中查询呢？ 如果一个索引覆盖所有需要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引是非常有用的工具，能够极大地提高性能：

       在所有这些场景中，在索引中满足查询的成本一般比查询行要小得多。

       不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引都不存储索引列的值，所以MySQL只能使用B+Tree索引所覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。

       当发起一个呗索引覆盖的查询是，在EXPLAIN的Extra列可以看到“Using index”的信息。

如： explain select col1 from layout_test where col2=99

       索引覆盖查询还有很多陷阱可能会导致无法实现优化。MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了wehre条件中的字段，但不是整个查询涉及的字段。mysql5.5和更早的版本也总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。

如： EXPLAIN select * from people where last_name='Allen' and first_name like '%Kim%'

这里索引无法覆盖该查询，有两个原因：

这条语句只检索1行，而之前的 like '%Kim%'要检索3行。

也有办法解决上面所说的两个问题，需要重写查询并巧妙设计索引。

       这种方式叫做延迟关联，因为延迟了对列的访问。在查询第一个阶段MySQL可以使用覆盖索引，因为索引包含了主键id的值，不需要做二次查找。

       在FROM子句的子查询中找到匹配的id，然后根据这些id值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询，但总算比完全无法利用索引覆盖的好吧。

数据量大了怎么办？

       这样优化的效果取决于WHERE条件匹配返回的行数。假设这个people表有100万行，我们看一下上面两个查询在三个不同的数据集上的表现，每个数据集都包含100万行。

实例1中 ，查询返回了一个很大的结果集，因此看不到优化的效果。大部分时间都花在读取和发送数据上了。

实例2中 ，经过索引过滤，尤其是第二个条件过滤后只返回了很少的结果集，优化的效果非常明显：在这个数据及上性能提高了很多，优化后的查询效率主要得益于只需读取40行完整数据行，而不是原查询中需要的30000行。

实例3中 ，子查询效率反而下降。因为索引过滤时符合第一个条件的结果集已经很小了，所以子查询带来的成本反而比从表中直接提取完整行更高。

       在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。不过，可以更进一步优化InnoDB。回想一下，InnoDB的二级索引的叶子节点都包含了主键的值，这意味着InnoDB的二级索引可以有效地利用这些额外的主键列来覆盖查询。

       例如，people表中last_name字段有一个二级索引，虽然该索引的列不包括主键id，但也能够用于对id做覆盖查询：

select id,last_name from people where last_name='hua'

1、要想高效利用索引，我们首先要考虑如何正确建立索引。

（1）在经常做搜索的列上，也就是WHERE子句里经常出现的列，考虑加上索引，加快搜索速度。

（2）唯一标识记录的列，应该加上唯一索引，强制该列的唯一性并且加快按该列查找记录的速度。

（3）在内连接使用的列上加上索引，最好是在内连接用到字段都加上，因为MySQL优化器会自动地选择连接顺序，然后观察索引的使用情况，将没用的索引删除即可。

（4）在需要排序的列上加上索引，因为索引本身是按顺序的组织的，它可以避免 filesort，要知道，Server层在进行排序时是在内存中进行的，非常消耗资源。

（5）可以考虑实现覆盖索引，即根据 SELECT 的所有字段上创建联合索引，这样存储引擎只用读取索引而不用去回表查询，极大地减少了对数据表的访问，大大地提高了性能。

（6）对于那些选择性很小的列，比如性别列，增加索引并不能明显加快查询速度，反而该索引会成为表的累赘。

（7）对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的要么数据量相当大，要么取值很少。

（8）当对写性能的要求远远大于读性能时，不应该创建索引。写性能和读性能是互相矛盾的。这是因为，维护一个 B+Tree 成本是非常大的，对索引的写会涉及到页的分裂等。

（9）复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引，否则考虑单字段索引。这还是说明，满足查询性能的前提下，索引越少越好。

（10）如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段。

（11）在用于GROUP BY的列上加上索引，避免使用临时表。

（12）对于较长的字符列，如 char、varchar等，由于字符串的比较相对来说非常耗时，因此考虑使用前缀索引减少索引长度，或者创建自定义哈希索引，将字符串映射成整数，然后以该整数作为索引，同时以字符串的值作为过滤条件。

我们在创建索引时，可以根据下面原则进行简单判断：索引是否将相关记录集合到了一起，从未减少了磁盘I/O，加快搜索速度？索引中数据的排列顺序是否和查找的数据的排列顺序一致，从而避免了Server层的排序？索引中的列是否包含了查询中需要的全部列从而实现了覆盖索引？ 这几个条件层层递进，满足得越多越好。

2、索引正确地建立了，我们还需要正确地使用它们：

（1）使用了运算符 !=，以及关键字not in，not exist，>，<等，总之产生的结果集很大时（也在where条件进行大范围的选择时），往往导致引擎不使用索引而是走全盘扫描。因为如果使用索引会造成大量的随机I/O，得不偿失。

（2）如果对索引列进行运算，如 WHERE substr(name, 1, 3)=‘mark’，存储引擎并不能聪明地判断哪些索引满足等式，因此不能使用到索引。

（3）使用到了LIKE，并且通配符在最前面时，不能使用索引。

（4）对于联合索引 (a, b, c)，如果没用到最左列，那么一般情况下都使用不到索引。但是，比如统计 *** 作 count(*) where a >xxx，是可以使用到该联合索引的。毕竟统计这类 *** 作，它不是检索，并不需要索引完全有序。

（5）对于联合索引，如果某个列使用了范围查找，那么其右边的列都无法作为索引优化查询，但是由于 ICP（Index Condition Pushdown），这些列能作为过滤条件在存储引擎中对数据进行过滤。

（6）如果条件中有 OR，则必须每个OR用到的字段都有索引，否则不能使用任何索引。

（7）想在联合查询中使用索引来避免 filesort，则关联查询中的ORDER BY用到的字段必须全部是第一张表（驱动表）上的。

---