Mysql index_merge

MySQL 5.0 和之后的版本推出了一个新特性---索引合并优化（Index merge optimization），它让MySQL可以在查询中对一个表使用多个索引,对它们同时扫描，并且合并结果。
Index merge算法有 3 种变体：例子给出最基本常见的方式：
对 AND 取交集:index_merge_intersection:Using intersect(b,a)
对 AND 和 OR 的组合取并集:Using sort_union(order_status,buyer_id)
对 OR 取并集 :Using union(order_status,buyer_id)
 


对 AND 取交集:index_merge_intersection:Using intersect(b,a)：

create table t (a int,b int,key(a),key(b)) engine=innodb;
insert into t select 1,1;
insert into t select 1,2;
insert into t select 2,3;
insert into t select 2,4;
insert into t select 1,2;



mysql> explain select * from t where a=1 and b=2;
+----+-------------+-------+-------------+---------------+------+---------+------+------+------------------------------------------------+
| id | select_type | table | type        | possible_keys | key  | key_len | ref  | rows | Extra                                          |
+----+-------------+-------+-------------+---------------+------+---------+------+------+------------------------------------------------+
|  1 | SIMPLE      | t     | index_merge | a,b           | b,a  | 5,5     | NULL |    1 | Using intersect(b,a); Using where; Using index |
+----+-------------+-------+-------------+---------------+------+---------+------+------+------------------------------------------------+
1 row in set (0.00 sec)


关闭组合索引算法
show variables like '%optimizer_switch%';
set optimizer_switch = 'index_merge_intersection=off'


mysql> explain select * from t where a=1 and b=2;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | a,b           | b    | 5       | const |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+



对 AND 和 OR 的组合取并集:Using sort_union(order_status,buyer_id)：

mysql> explain SELECT * FROM tmp_index_merge where key1_part1 = 2 or key2_part1 = 4;
+----+-------------+-----------------+-------------+---------------+-----------+---------+------+------+------------------------------------------+
| id | select_type | table           | type        | possible_keys | key       | key_len | ref  | rows | Extra                                    |
+----+-------------+-----------------+-------------+---------------+-----------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | tmp_index_merge | index_merge | ind2,ind1     | ind1,ind2 | 4,4     | NULL |    5 | Using sort_union(ind1,ind2); Using where |
+----+-------------+-----------------+-------------+---------------+-----------+---------+------+------+------------------------------------------+

mysql> explain SELECT * FROM tmp_index_merge 
    -> where (key1_part1 = 2 and key1_part2 = 7) or key2_part1 = 4;
+----+-------------+-----------------+-------------+---------------+-----------+---------+------+------+------------------------------------------+
| id | select_type | table           | type        | possible_keys | key       | key_len | ref  | rows | Extra                                    |
+----+-------------+-----------------+-------------+---------------+-----------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | tmp_index_merge | index_merge | ind2,ind1     | ind1,ind2 | 8,4     | NULL |    5 | Using sort_union(ind1,ind2); Using where |
+----+-------------+-----------------+-------------+---------------+-----------+---------+------+------+------------------------------------------+

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/91975/viewspace-1762749/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/91975/viewspace-1762749/