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

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

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

索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中。

因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。对于某些存储引擎，例如MyISAM甚至可以通过OPTIMIZE命令使得索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问。

一些存储引擎，如MyISAM在内存中只缓存索引，数据则依赖操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用找了数据访问中的最大的开销的场景。

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

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

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引都不存储索引列的值，所以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%'

image.png

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

没有任何索引能够覆盖这个查询。因为查询从表中选择了所有的列，而没有任何索引覆盖了所有的列。不过理论上mysql有一个捷径可以利用：where条件中的列是由索引可以覆盖的，因此Mysql可以使用该索引找到对应的last_name并检查是否first_name是否匹配，过滤之后再读取所需要的数据行。

MySQL不能在索引中执行like操作。这是底层存储引擎API的限制。MySQL5.5和更早的版本只允许在索引中做简单的比较操作(等于、不等于及大于)。MySQL能在索引中做最左前缀匹配的LIKE比较，因为该操作可以转换为简单的比较操作，但是如果是通配符开头的LIKE查询，存储引擎无法做比较匹配。这种情况下，MySQL服务器只能提取数据行的值而不是索引值来做比较。

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

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