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

<b>一 序言介紹</b>

MySQL 5.0 版本之前，每條個表在查詢時 隻能使用一個索引，有些不知道此功能限制的開發總是在一個表上建立很多單獨列的索引，以便當where條件中含有這些列是能夠走上索引。但是這樣并不是一個好方法，或者是“好心辦壞事”，索引能夠提供查詢速度，但是也能給日常維護和IUD 操作帶來維護成本。

MySQL 5.0 和之後的版本推出了一個新特性---索引合并優化（Index merge optimization），它讓MySQL可以在查詢中對一個表使用多個索引,對它們同時掃描，并且合并結果。

<b>二 使用場景</b>

Index merge算法有 3 種變體：例子給出最基本常見的方式：

<b>2.1 對 OR 取并集 </b>

<b>In this form, where the index has exactly N parts (that is, all index parts are covered):</b>

<b>1 key_part1=const1 AND key_part2=const2 ... AND key_partN=constN</b>

<b>2 Any range condition over a primary key of an InnoDB table.</b>

<b>3 A condition for which the Index Merge method intersection algorithm is applicable.</b>

root@odbsyunying 02:34:41&gt;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

        <b> </b>type: <b>index_merge</b>

possible_keys: buyer_id,order_status

          key: order_status,buyer_id

      key_len: 1,9

          ref: NULL

         rows: 8346

        Extra: <b>Using union(order_status,buyer_id)</b>; Using where

1 row in set (0.00 sec)

<b>當 where 條件中 含有對兩個索引列的 or 交集操作時 ，執行計劃會采用 union merge 算法。</b>

<b>2.2 對 AND 取交集：</b>

<b>”In this form, where the index has exactly N parts (that is, all index parts are covered):</b>

<b>key_part1=const1 AND key_part2=const2 ... AND key_partN=constN</b>

<b>Any range condition over a primary key of an InnoDB table.“</b>

root@odbsyunying 02:33:59&gt;explain  select count(*) as cnt from `order` o  WHERE  o.order_status = 2  and o.buyer_id=1979459339672858 \G                       

         type: <b>index_merge</b>

          key: buyer_id,order_status

      key_len: 9,1

         rows: 1

        Extra: <b>Using intersect(buyer_id,order_status);</b> Using where; Using index

<b>當where條件中含有索引列的and操作時，執行計劃會采用intersect 并集操作。</b>

<b>2. 3 對 AND 和 OR 的組合取并集。</b>

root@odbsyunying 02:42:19&gt;explain  select count(*) as cnt from `order` o  WHERE  o.order_status &gt; 2  or  o.buyer_id=1979459339672858 \G    

         rows: 4585

        Extra: <b>Using sort_union(order_status,buyer_id);</b> Using where

<b></b>

<b>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.</b>

<b>三 Index merge的 限制</b>

MySQL在5.6.7之前，使用index merge有一個重要的前提條件：沒有range可以使用。這個限制降低了MySQL index merge可以使用的場景。理想狀态是同時評估成本後然後做出選擇。因為這個限制，就有了下面這個已知的bad case ：

SELECT * FROM t1 WHERE (goodkey1 &lt; 10 OR goodkey2 &lt; 20) AND badkey &lt; 30;

優化器可以選擇使用goodkey1和goodkey2做index merge，也可以使用badkey做range。因為上面的原則，無論goodkey1和goodkey2的選擇度如何，MySQL都隻會考慮range，而不會使用index merge的通路方式。這是一個悲劇...（5.6.7版本針對此有修複)