MySQL优化中index

108次阅读

共计 2816 个字符，预计需要花费 8 分钟才能阅读完成。

这篇文章主要介绍“MySQL 优化中 index_merge 有什么作用”，在日常操作中，相信很多人在 MySQL 优化中 index_merge 有什么作用问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL 优化中 index_merge 有什么作用”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

1. 为什么会有 index merge

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

MySQL5.0 之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从 5.1 开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。

相关文档：http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html (注意该文档中说的有几处错误)

The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. This access method merges index scans from a single table; it does not merge scans across multiple tables.

In EXPLAIN output, the Index Merge method appears as index_merge in the type column. In this case, the key column contains a list of indexes used, and key_len contains a list of the longest key parts for those indexes.

index merge: 同一个表的多个索引的范围扫描可以对结果进行合并，合并方式分为三种：union, intersection, 以及它们的组合(先内部 intersect 然后在外面 union)。

index merge 算法根据合并算法的不同分成了三种：intersect, union, sort_union.

2. index merge 之 intersect

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

3. index merge 之 union

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

下面几种类型的 where 条件，以及他们的组合可能会使用到 index union merge 算法：

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

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

3) 任何符合 index intersect merge 的 where 条件；

上面三种 where 条件进行 OR 运算时，可能会使用 index union merge 算法。

4. index merge 之 sort_union

This access algorithm is employed when the WHERE clause was converted to several range conditions combined by OR, but for which the Index Merge method union algorithm is not applicable.(多个条件扫描进行 OR 运算，但是不符合 index union merge 算法的，此时可能会使用 sort_union 算法)

5. index merge 的局限

1）If your query has a complex WHERE clause with deep AND/OR nesting and MySQL does not choose the optimal plan, try distributing terms using the following identity laws:

6. 对 index merge 的进一步优化

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

7. 复合索引的最左前缀原则

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

SQL 如下：
select cd.coupon_id, count(1) total from AAA cd
where cd.coupon_act_id = 100476 and cd.deleted=0 and cd.pick_time is not null
group by cd.coupon_id ;
在 AAA 表中，coupon_act_id 和 deleted 都是独立的索引
select count(*) from AAA where coupon_act_id = 100476; 结果为 12360 行
select count(*) from AAA where deleted=0; 结果为 1300W 行
从上面的解释我们可以看出来，index merge 其实就是分别通过对两个独立的 index 进行过滤之后，将过滤之后的结果聚合在一起，然后在返回结果集。
在我们的这个例子中，由于 deleted 字段的过滤性不好，故返回的 rows 依然很多，所以造成的很多的磁盘 read，导致了 cpu 的负载非常的高，直接就出现了延迟。
ps：其实在这个 case 中，并不需要加 2 个条件的 index，只需要将 deleted 这个 index 干掉，直接使用 coupon_act_id 这个 index 即可，毕竟这个 index 的过滤的结果集已经很小了。
或者通过关闭 index intersect 功能也可以。

到此，关于“MySQL 优化中 index_merge 有什么作用”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！

正文完