MySQL优化：单索引的选择-index_merge

1.多个单索引

where子句后有多个查询条件，MySQL 5.1以前即使分别为每个条件建立索引，查询优化器也只会选择其中一个区分度高索引。笔者使用5.7.20，以下分析均基于此版本，不同版本的MySQL查询优化器给出的结果存在差异。下面开始分情况讨论MySQL查询优化器给出的索引结果。

• where子句有两个个and条件分别且三个字段都有索引

mysql> EXPLAIN SELECT * FROM order t WHERE t.PrdModel = 'NX549J' AND t.Destination = '国内' AND t.OEM = '福盛创新'; 
+----+-------------+-------+------------+-------------+--------------------------+--------------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys            | key          | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+-------------+--------------------------+--------------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | index_merge | PrdModel,Destination,OEM | OEM,PrdModel | 153,153 | NULL |  614 |    50.00 | Using intersect(OEM,PrdModel); Using where |
+----+-------------+-------+------------+-------------+--------------------------+--------------+---------+------+------+----------+--------------------------------------------+
1 row in set, 1 warning (0.00 sec)


mysql> EXPLAIN SELECT * FROM order t WHERE t.PrdModel = 'NX549J' AND t.Destination = '国内' AND t.OEM = 'XXX技术有限公司'; 
+----+-------------+-------+------------+-------------+--------------------------+----------------------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys            | key                  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+-------------+--------------------------+----------------------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | index_merge | PrdModel,Destination,OEM | PrdModel,Destination | 153,153 | NULL | 320463 |    50.00 | Using intersect(PrdModel,Destination); Using where |
+----+-------------+-------+------------+-------------+--------------------------+----------------------+---------+------+--------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

上例告诉我们实际的索引应用的index_merge，从三索引中选其二，奇怪的是每次选择结果并不是固定，第一次选择intersect(OEM,PrdModel)，第二次选择intersect(PrdModel,Destination)，尝试从区分度的角度查找原因，上例中三个索引的区分度分别为PrdModel（35045），Destination（22982），OEM（6195）。发现并不是简单的从区分度最高的索引选其二。那么索引合并选择的依据是什么呢？下面尝试从合并的复杂度方面比较：

• 下面借助 官网文档 尝试理解index_merge的使用及选择机制。
  index_merge用在范围查询中，然后尝试合并多个索引检索的结果。笔者分别尝试两个、三个、四个条件的索引发现，index_merge只会选择其二合并索引，这一点官方没有特别说明。官方提到，index_merge只能扫描单表，不能跨表扫描。index merge: 同一个表的多个索引的范围扫描可以对结果进行合并，合并方式分为三种：union, intersection, 以及它们的组合(先内部intersect然后在外面union)。
  官方给出了四个例子。

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;
SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1 = t1.some_col;
SELECT * FROM t1, t2 WHERE t1.key1 = 1 AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

经过测试，发现前三种能够得到index_merge，第四种情况看起来有点怪，两个连表条件，一个和一个查询条件，并不能使用index_merge。

• 注意，下面是官方给出的建议，where后的条件写的比较复杂时，可能引起查询优化器的错误选择索引，应遵循以下原则：

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

• index merge 之 intersect
  index intersect merge就是多个索引条件扫描得到的结果进行交集运算，结果如下：

key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

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

key1 = 1 OR key2 = 2 OR key3 = 3;

• index merge 之 Sort-Union
  简言之，就是需要先取交集再取并集，如多个OR的范围查询，可能有重复的记录，需要先取并集再取交集。

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;