注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

MySQL覆盖索引学习

MySQL InnoDB 彭东稳 9730次浏览 已收录 1个评论

覆盖索引

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

覆盖索引是非常有用的工具,能够极大地提高性能。考虑一下如果查询只需要扫描索引而无须回表,会带来多少好处:

  • 索引条目通常远小于数据行大小,索引如果只需要读取索引,那么MySQL就会极大地减少数据访问量。这对缓存的负载非常重要,因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助,因为索引比数据更小,更容易全部放入内存中(这对于MyISAM尤其正确,因为MyISAM能压缩索引以变的更小)。
  • 因为索引是按照列值顺序存储的(至少在单个页内是如此),所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。对于某些存储引擎,例如MyISAM和XtrDB,甚至可以通过OPTIMIZE命令使得索引完全顺序排列,这让简单的范围查询能使用完全顺序的索引访问。
  • 一些存储引擎,如MyISAM在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用。这可能会导致严重的性能问题,尤其是那些系统调用占了数据访问中的最大开销的场景。
  • 由于InnoDB的聚簇索引,覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询。

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

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

当发起一个被索引覆盖的查询(也叫做索引覆盖查询)时,在explain的extra列可以看到”using index”的信息。例如,表wp_postmeta有两个普通索引(meta_id,post_id)。MySQL如果只需要访问这两列,就可以使用这个索引做覆盖索引,如下所示:

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

没有任何索引能覆盖这个查询,因为查询从表中选择了所有的列,而没有任何索引覆盖所有的列,因此这个查询需要回表(就是根据where条件重新查询一次数据,然后返回结果)。但是索引还是用到了。

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

在大多数存储引擎中,覆盖索引只能覆盖那些只访问索引中部分列的查询。我们来看看这种情况,以及如何重写查询以解决该问题,从下面的查询开始:

这个语句无法使用覆盖索引有两个原因:

1. 没有任何的列能够覆盖这个查询,因为查询中选择了所有列,而没有任何索引能覆盖所有的列。但是MySQL利用ICP机制还是有捷径可以利用,WHERE条件中的列是有索引的可以覆盖的,因此MySQL可以使用该索引找到对应的actor并检查title是否匹配,过滤之后再去InnoDB层读取需要的数据行,这就是MySQL 5.6的ICP(index condition pushdown)机制。

2. MySQL不能再索引中执行LIKE %string%操作,这是底层引擎的API限制。但是可以执行LIKE string%,是可以根据左前缀匹配利用索引,因为该操作可以转换为简单的比较操作。

也有办法可以解决上面说的问题的,需要重写查询并巧妙地设计索引。先将索引扩展至覆盖三个数据列的联合索引(actor,title,prod_id),然后按照如下方式重写查询:

我们把这种方式叫做延迟关联(deferred join),因为延迟了对列的访问,在查询的第一阶段MySQL可以使用覆盖索引,在from子句的子查询中找到匹配的prod_id,然后根据这些prod_id值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询,但总算比较完全无法利用索引覆盖的好。

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

1. 第一个数据集,Jerry出演了30000部作品,其中有20000部的标题中包含Tech。

2. 第二个数据集,Jerry出演了20000部作品,其中40部的标题包含了Tech。

3. 第三个数据集,Jerry出现了50部作品,其中10部的标题中包含了Tech。

使用上面的三种数据集来测试两种不同的查询,得到的记过如下表:

示例1:原查询每秒5次,优化后每秒5次;

示例2:原查询每秒7次查询,优化后每秒35次查询;

示例3:原查询每秒2400次查询,优化后每秒2000次查询;

下面是对结果的分析:

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

示例2中,经过索引过滤,尤其是第二个条件过滤后返回了很少的数据集,优化的效果非常明显,优化后的查询效率主要得益于只需要读取40行完整的数据行,而不是30000行。

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

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

例如,users使用InnoDB存储引擎,并在last_name字段有二级索引,虽然该索引的列不包括主键actor_id,但也能够用于对actor_id做覆盖查询,如下:

此外,还有一种利用覆盖索引的查询,如下查询:

对于这种查询,可以添加一个(last_name,address)索引,这样就可以利用到覆盖所以了,这个SQL优化技巧在实际中很多很常用的。

执行计划EXPLAIN类型

full index scan:全索引扫描,查询时,遍历索引树来获取数据行。如果数据不是密集的会产生随机IO,在执行计划中是Type列,index。

full table scan:通过读物理表获取数据,顺序读磁盘上的文件。这种情况会顺序读磁盘上的文件。在执行计划中是Type列,all。

covering index:覆盖索引,如果where条件的列和返回的数据在一个索引中,那么不需要回查表,那么就叫覆盖索引。在执行计划中是extra那一列,using index。

full index scan  vs full table scan

全索引扫描并不一定就比全表扫描好,取决于数据存储位置。如果数据在内存,那么这两种没有太大区别。如果数据在磁盘,全表扫描比全索引扫描要好,这是因为,全表扫描是顺序读数据,sequential read,是顺序IO。而全索引扫描,可能会产生随机读(reandom read),随机IO,显然,顺序读要比随机读快很多。


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (4)or分享 (0)
关于作者:

您必须 登录 才能发表评论!

(1)个小伙伴在吐槽
  1. mysql> explain select * from products where actor='Jerry' and title like '%Tech%'; 这个语句无法使用覆盖索引有两个原因: 1. 没有任何的列能够覆盖这个查询,因为查询中选择了所有列,而没有任何索引能覆盖所有的列。但是MySQL利用ICP机制还是有捷径可以利用,WHERE条件中的列是有索引的可以覆盖的,因此MySQL可以使用该索引找到对应的actor并检查title是否匹配,过滤之后再去InnoDB层读取需要的数据行,这就是MySQL 5.6的ICP(index condition pushdown)机制。 2. MySQL不能再索引中执行LIKE %string%操作,这是底层引擎的API限制。但是可以执行LIKE string%,是可以根据左前缀匹配利用索引,因为该操作可以转换为简单的比较操作。 能解释下,如果是5.6里用到了ICP,那这个ICP和延迟关联的做法纠结会选哪个呢,这种延迟关联会比ICP快吗
    johny6662017-12-25 10:47 Windows 10 | Chrome 55.0.2883.87