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

MySQL查询优化:Index Merge

MySQL SQL 彭东稳 1238次浏览 已收录 0个评论

一、为什么会有Index Merge

我们的 where 中可能有多个条件(或者join)涉及到多个字段,它们之间进行 AND 或者 OR,那么此时就有可能会使用到 Index Merge 技术。Index Merge 技术如果简单的说,其实就是对多个索引分别进行条件扫描,然后将它们各自的结果进行合并(Intersect/Union)。

MySQL 5.0之前,一个表一次只能使用一个索引,无法同时使用多个索引分别进行条件扫描。但是从5.1开始,引入了Index Merge优化技术,对同一个表可以使用多个索引分别进行条件扫描。同一个表的多个索引的范围扫描可以对结果进行合并,合并方式分为三种:union,intersection,以及它们的组合(先内部intersect然后在外面union)。

什么情况下,同时使用多个索引会有利呢?比如说WHERE条件是“C1=10 AND C2 =100”,但是只有分别针对C1和C2的索引,而没有(C1,C2)这种索引,两个索引同时使用才有意义,通过两个索引都可以快速定位到一批数据,然后对这一批数据进行进一步的求交或求和操作即可,这样的效率可能比全表扫描或者只使用其中一个索引进行扫描然后再去主索引查询要快。

可以使用Index Merge的示例查询:

索引合并优化算法具有以下已知限制:

  • 如果我们的条件比较复杂,用到多个 and/or 条件运算,而MySQL没有使用最优的执行计划,那么可以使用下面的两个等式将条件进行转换一下。

(x AND y) OR z = (x OR z) AND (y OR z)

(x OR y) AND z = (x AND z) OR (y AND z)

  • 索引合并不适用于全文索引。

在EXPLAIN输出中,索引合并方法在Type列中显示为index_merge。在这种情况下,key列包含使用的索引列表,key_len包含这些索引的最长键部分列表。

Index Merge方法根据合并算法的不同分成了三种:Intersect,Union,Sort_union。它们显示在EXPLAIN输出的Extra字段中。Intersect和Union都需要使用的索引是ROR的,也就是ROWID ORDERED,即针对不同的索引扫描出来的数据必须是同时按照ROWID排序的,这里的ROWID其实也就是InnoDB的主键(如果不定义主键,InnoDB会隐式添加ROWID列作为主键)。只有每个索引是ROR的,才能进行归并排序,你懂的。 当然你可能会有疑惑,查记录后内部进行一次sort不一样么,何必必须要ROR呢,不错,所以有了Sort-union。Sort-union就是每个非ROR的索引排序后再进行Merge。MySQL至于为什么没有Sort-Intersect,就不清楚了,但是MariaDB从5.3版本开始就支持了。

以下部分更详细地描述了这些算法,优化器根据各种可用选项的成本估算在不同的索引合并算法和其他访问方法之间进行选择。

二、Index Merge算法

  • 索引合并交集访问算法(Index Merge Intersection Access Algorithm)

简单而言,Index Merge Intersect就是多个索引条件扫描得到的结果进行交集运算。显然在多个索引提交之间是 AND 运算时,才会出现Index Merge Intersect。下面两种where条件或者它们组合时会进行Index Merge Intersect。

1) 条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)

2) 主键上的任何范围条件

上面只说到复合索引,但是其实单字段索引显然也是一样的。比如“select * from tab where key1=xx and key2 =xxx”也是有可能进行Index Merge Intersect的在单个索引过滤的数据量多的情况下,这个要靠优化器根据成本估算的,如果优化器认为根据单个索引速度更快,就不会倾向于使用索引合并,毕竟所以索引合并也是没有办法的办法。另外上面两种情况的 AND 组合也一样可能会进行Index Merge Intersect。

如果查询中使用的所有列都被使用的索引覆盖,则无须回表(在这种情况下,EXPLAIN输出Extra字段中出现Using index)。以下是此类查询的示例:

如果使用的索引未涵盖查询中使用的所有列,则仅在满足所有使用的键的范围条件时才进行回表。

如果其中一个合并条件是InnoDB表的主键上的条件,则它不用于行检索,而是用于过滤掉使用其他条件检索的行。

  • 索引合并联合访问算法(Index Merge Union Access Algorithm)

简单而言,Index Merge Union就是多个索引条件扫描,对得到的结果进行并集运算,显然是多个条件之间进行的是 OR 运算。

下面几种类型的 where 条件,以及他们的组合可能会使用到Index Merge Union算法。

1) 条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)

2) 主键上的任何范围条件

3) 任何符合Index Merge Intersect的where条件

上面三种 where 条件进行 OR 运算时,可能会使用 Index Merge Union算法。

例子:

第一个例子,就是三个单字段索引进行 OR 运算,所以他们可能会使用Index Merge Union算法。

第二个例子,复杂一点。(key1=1 AND key2=2) 是符合Index Merge Intersect。 (key3=’foo’ AND key4=’bar’) AND key5=5也是符合Index Merge Intersect,所以 二者之间进行 OR 运算,自然可能会使用Index Merge Union算法。

  • 索引合并排序联合访问算法(Index Merge Sort-Union Access Algorithm)

当多个 WHERE 条件扫描进行 OR 运算,但是不符合Index Merge Union算法的,此时可能会使用sort_union算法。

官方文档给出了两个例子:

sort-union算法和union算法之间的区别在于,sort-union算法必须首先获取所有行的行ID,然后在返回结果之前对索引进行排序后再进行Merge。为什么需要排序呢?可能是因为两个结果集,进行并集运算,需要去重,所以才进行排序???

三、对Index Merge的进一步优化

Index Merge使得我们可以使用到多个索引同时进行扫描,然后将结果进行合并。听起来好像是很好的功能,但是如果出现了Index Merge Intersect,那么一般同时也意味着我们的索引建立得不太合理,因为Index Merge Intersect是可以通过建立 复合索引进行更一步优化的。

比如下面的查询:

显然我们是可以在这三个字段上建立一个复合索引来进行优化的,这样就只需要扫描一个索引一次,而不是对三个所以分别扫描一次。

Percona官网有一篇 比较复合索引和Index Merge的好文章:Multi Column indexes vs Index Merge

四、复合索引的最左前缀原则

上面我们说到,对复合索引的非最左前缀字段进行 OR 运算,是无法使用到复合索引的。比如:

其原因是,MySQL中的索引,使用的是B+tree,也就是说他是先按照复合索引的第一个字段的大小来排序,插入到 B+tree 中的,当第一个字段值相同时,在按照第二个字段的值比较来插入的。那么如果我们需要对“OR key_col2 = 20”这样的条件也使用复合索引,那么该怎么操作呢?应该要对复合索引进行全扫描,找出所有 key_col2 = 20 的项,然后还要回表去判断 nonkey_col=30,显然代价太大了。所以一般而言 OR key_col2 = 20 这样的条件是无法使用到复合索引的。如果一定要使用索引,那么可以在 col2 上单独建立一个索引。

五、生产案例

对于TicketINF表大概有一百多万数据左右,结合上面的学习来看看索引合并到底如何工作。下面先从一个简单的 OR 查询开始。

根据执行计划看出,两个字段都使用到了索引(两个普通索引,memberSysId字段是组合索引的第一个字段,memberId是一个普通索引),使用了索引合并技术,且使用的是sort-union合并算法。

根据我们上面说的,如果查询字段是复合索引的第二个字段,那么是无法使用到索引的,如下执行计划。

然后对上一个查询再加上一个 AND 运算。

从执行计划来看,同样使用到了索引合并,但这回使用的合并算法是union。

再往下,我们再次添加一个 OR 运算,增加一个mobile字段的检索。

从执行计划上看,走了全表扫描,一个索引也没有用上,更没有用上索引合并。此条SQL在此表执行时间为2s左右,导致慢查询过多,服务器负载到了200。

此时,我们再给mobile字段增加一个普通索引,再次看执行计划,如下。

从结果看可以,又可以使用上了索引合并,把相关的三个索引都使用上了。

<参考>

http://www.cnblogs.com/digdeep/archive/2015/11/18/4975977.html

https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html#index-merge-intersection


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

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