【MySQL】性能优化之 index merge (1)

转载自 http://blog.itpub.net/22664653/viewspace-774687/
一 序言介绍
MySQL 5.0 版本之前,每条个表在查询时 只能使用一个索引,有些不知道此功能限制的开发总是在一个表上创建很多单独列的索引,以便当where条件中含有这些列是能够走上索引。但是这样并不是一个好方法,或者是“好心办坏事”,索引能够提供查询速度,但是也能给日常维护和IUD 操作带来维护成本。
MySQL 5.0 和之后的版本推出了一个新特性---索引合并优化(Index merge optimization),它让MySQL可以在查询中对一个表使用多个索引,对它们同时扫描,并且合并结果。
二 使用场景
Index merge算法有 3 种变体:例子给出最基本常见的方式:
2.1 对 OR 取并集 
In this form, where the index has exactly N parts (that is, all index parts are covered):
1 key_part1=const1 AND key_part2=const2 ... AND key_partN=constN
2 Any range condition over a primary key of an InnoDB table.
3 A condition for which the Index Merge method intersection algorithm is applicable.
root@odbsyunying 02:34:41>explain  select count(*) as cnt from `order` o  WHERE  o.order_status = 2  or  o.buyer_id=1979459339672858 \G   
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: o
         type: index_merge
possible_keys: buyer_id,order_status
          key: order_status,buyer_id
      key_len: 1,9
          ref: NULL
         rows: 8346
        Extra: Using union(order_status,buyer_id); Using where
1 row in set (0.00 sec)
当 where 条件中 含有对两个索引列的 or 交集操作时 ,执行计划会采用 union merge 算法。
2.2 对 AND 取交集:
”In this form, where the index has exactly N parts (that is, all index parts are covered):
key_part1=const1 AND key_part2=const2 ... AND key_partN=constN
Any range condition over a primary key of an InnoDB table.“
root@odbsyunying 02:33:59>explain  select count(*) as cnt from `order` o  WHERE  o.order_status = 2  and o.buyer_id=1979459339672858 \G                       
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: o
         type: index_merge
possible_keys: buyer_id,order_status
          key: buyer_id,order_status
      key_len: 9,1
          ref: NULL
         rows: 1
        Extra: Using intersect(buyer_id,order_status); Using where; Using index
1 row in set (0.00 sec)
当where条件中含有索引列的and操作时,执行计划会采用intersect 并集操作。

2. 3 对 AND 和 OR 的组合取并集。
root@odbsyunying 02:42:19>explain  select count(*) as cnt from `order` o  WHERE  o.order_status > 2  or  o.buyer_id=1979459339672858 \G    
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: o
         type: index_merge
possible_keys: buyer_id,order_status
          key: order_status,buyer_id
      key_len: 1,9
          ref: NULL
         rows: 4585
        Extra: Using sort_union(order_status,buyer_id); Using where
1 row in set (0.00 sec)

The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all rows and sort them before returning any rows.

三 Index merge的 限制
MySQL在5.6.7之前,使用index merge有一个重要的前提条件:没有range可以使用。这个限制降低了MySQL index merge可以使用的场景。理想状态是同时评估成本后然后做出选择。因为这个限制,就有了下面这个已知的bad case :
SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
优化器可以选择使用goodkey1和goodkey2做index merge,也可以使用badkey做range。因为上面的原则,无论goodkey1和goodkey2的选择度如何,MySQL都只会考虑range,而不会使用index merge的访问方式。这是一个悲剧...(5.6.7版本针对此有修复)
四 推荐阅读
[1] http://www.orczhou.com/index.php/2013/01/mysql-source-code-query-optimization-index-merge/#42
[2] http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html
注:看到这篇好文,想留个记录,就转载过来了,若有版权问题,留言删除
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值