【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

注：看到这篇好文，想留个记录，就转载过来了，若有版权问题，留言删除