【MySQL】效能優化之 index merge (1)

楊奇龍發表於2013-10-20
MySql優化Index
一 序言介紹
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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-774687/，如需轉載，請註明出處，否則將追究法律責任。

相關文章