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

2018-10-12  本文已影响96人  南风nanfeng
1.多个单索引

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

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)。发现并不是简单的从区分度最高的索引选其二。那么索引合并选择的依据是什么呢?下面尝试从合并的复杂度方面比较:

索引/取值(记录数)/参数 PrdModel Destination OEM
intersect(PrdModel,Destination) NX549J(320147) 国内(8077050) XXX技术有限公司(5418791)
intersect(OEM,PrdModel) NX549J(320147) 国内(8077050) 福盛创新(11094)
intersect(PrdModel,Destination) NX549J(320147) 国内(8077050) is not null (21800063)
intersect(PrdModel,OEM) NX549J(320147) 国内(8077050) is null (537463)

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。

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

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

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

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

上一篇 下一篇

猜你喜欢

热点阅读