MySQL 查询优化之 Index Merge
MySQL 查询优化之 Index Merge
索引合并访问方法可以在查询中对一个表使用多个索引,对它们同时范围扫描，并且合并结果(intersects/unions/unions-of-intersections)。 此访问方法合并来自单个表的索引扫描; 它不会将扫描合并到多个表中。
使用索引合并的示例查询：
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);
【注意】
索引合并优化算法有以下已知的缺陷：
- 如果您的查询具有深度AND或OR嵌套的复杂WHERE子句，并且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访问方法有几种算法，它们显示在EXPLAIN输出的Extra字段中：
-
Using intersect(...)
-
Using union(...)
-
Using sort_union(...)
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版本开始就支持了。
1. Index Merge Intersection 访问算法
index intersect merge是多个索引条件扫描得到的结果进行交集运算。显然在多个索引提交之间是 AND 运算时，才会出现 index intersect merge。 下面两种where条件或者它们的组合时会进行 index intersect merge:
-
条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
-
主键上的任何范围条件
SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1 = 20; SELECT * FROM tbl_name WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;
示例1
mysql> show index from employees; +-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | employees | 0 | PRIMARY | 1 | emp_no | A | 298936 | NULL | NULL | | BTREE | | | | employees | 1 | idx_name | 1 | last_name | A | 1651 | NULL | NULL | | BTREE | | | | employees | 1 | idx_first_name | 1 | first_name | A | 1251 | NULL | NULL | | BTREE | | | +-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) mysql> explain select * from employees.employees e where e.first_name='Tzu' and e.last_name='Terkki'; +----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+-------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+-------------------------------------------------------+ | 1 | SIMPLE | e | NULL | index_merge | idx_name,idx_first_name | idx_name,idx_first_name | 66,58 | NULL | 1 | 100.00 | Using intersect(idx_name,idx_first_name); Using where | +----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+-------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
示例2
mysql> show index from employees; +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | employees | 0 | PRIMARY | 1 | emp_no | A | 298936 | NULL | NULL | | BTREE | | | | employees | 1 | idx_name | 1 | last_name | A | 1651 | NULL | NULL | | BTREE | | | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql> explain select * from employees.employees e where e.emp_no>10011 and e.last_name = 'Terkki'; +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+------------------------------------------------+ | 1 | SIMPLE | e | NULL | index_merge | PRIMARY,idx_name | idx_name,PRIMARY | 70,4 | NULL | 90 | 100.00 | Using intersect(idx_name,PRIMARY); Using where | +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
索引合并Intersection访问算法对所有使用的索引执行同时扫描，并产生从合并索引扫描中接收到的行序列的交集。
如果查询中使用的所有列都被使用的索引覆盖，则不会检索完整的表行（EXPLAIN输出包含在这种情况下在Extra字段中 Using index）。 例如：
mysql> explain select count(*) from employees.employees e where e.first_name='Tzu' and e.last_name='Terkki'; +----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------+ | 1 | SIMPLE | e | NULL | index_merge | idx_name,idx_first_name | idx_name,idx_first_name | 66,58 | NULL | 1 | 100.00 | Using intersect(idx_name,idx_first_name); Using where; Using index | +----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------+ mysql> explain select e.emp_no,e.first_name,e.last_name from employees.employees e where e.first_name='Tzu' and e.last_name='Terkki'; +----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------+ | 1 | SIMPLE | e | NULL | index_merge | idx_name,idx_first_name | idx_name,idx_first_name | 66,58 | NULL | 1 | 100.00 | Using intersect(idx_name,idx_first_name); Using where; Using index | +----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
如果使用的索引未涵盖查询中使用的所有列，则仅在满足所有使用的键的范围条件时才检索完整行。
如果其中一个合并条件是InnoDB表的主键上的条件，则它不用于行检索，而是用于过滤掉使用其他条件检索的行。
2. Index Merge Union 访问算法
index uion merge就是多个索引条件扫描，对得到的结果进行并集运算，显然是多个条件之间进行的是 OR 运算。
下面几种类型的 where 条件，以及他们的组合可能会使用到index union merge算法：
-
条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
-
InnoDB表的主键上的任何范围条件
-
任何符合 index intersect merge 的where条件
SELECT * FROM t1 WHERE key1 = 1 OR key2 = 2 OR key3 = 3; SELECT * FROM innodb_table WHERE (key1 = 1 AND key2 = 2) OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;
示例3
mysql> explain select e.emp_no,e.first_name,e.last_name from employees.employees e where e.first_name='Tzu' or e.last_name='Terkki'; +----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+ | 1 | SIMPLE | e | NULL | index_merge | idx_name,idx_first_name | idx_first_name,idx_name | 58,66 | NULL | 416 | 100.00 | Using union(idx_first_name,idx_name); Using where | +----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
示例4
mysql> explain select * from employees.employees e where e.emp_no>10011 or e.last_name = 'Terkki'; +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+--------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+--------+----------+--------------------------------------------+ | 1 | SIMPLE | e | NULL | index_merge | PRIMARY,idx_name | PRIMARY,idx_name | 4,66 | NULL | 149648 | 100.00 | Using union(PRIMARY,idx_name); Using where | +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+--------+----------+--------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
3. Index Merge Sort-Union 访问算法
当WHERE子句转换为OR组合的多个范围条件时，此访问算法适用，但Index Merge union 算法不适用。
SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20; SELECT * FROM tbl_name WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;
sort-union算法和union算法之间的区别在于sort-union算法必须首先获取所有行的行ID，然后在返回任何行之前对它们进行排序。
mysql> show index from t1; +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t1 | 1 | idx_i1_i2 | 1 | i1 | A | 5 | NULL | NULL | | BTREE | | | | t1 | 1 | idx_i1_i2 | 2 | i2 | A | 25 | NULL | NULL | | BTREE | | | | t1 | 1 | idx_i2 | 1 | i2 | A | 5 | NULL | NULL | | BTREE | | | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) mysql> explain select * from t1 force index (idx_i1_i2,idx_i2) where i1>1 or i2>5; +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | index_merge | idx_i1_i2,idx_i2 | idx_i1_i2,idx_i2 | 4,4 | NULL | 21 | 100.00 | Using sort_union(idx_i1_i2,idx_i2); Using where | +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from t1 force index (idx_i1_i2,idx_i2)where (i1>4 or i2=4) and d = '2001-01-01'; +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | index_merge | idx_i1_i2,idx_i2 | idx_i1_i2,idx_i2 | 4,4 | NULL | 10 | 10.00 | Using sort_union(idx_i1_i2,idx_i2); Using where | +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
4. 参考文档
http://www.ywnds.com/?p=14468
https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html
