ITPub博客

首页 > 数据库 > MySQL > Mysql index_merge

Mysql index_merge

原创 MySQL 作者:wzgchen 时间:2015-08-05 15:55:08 0 删除 编辑



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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-05-01

  • 博文量
    383
  • 访问量
    178622