MySQL 最佳化之 index merge(索引合并)

標籤：res   mit   首碼   局限   select   ica   mysql5.0   like   explain   

深入理解 index merge 是使用索引進行最佳化的重要基礎之一。理解了 index merge 技術，我們才知道應該如何在表上建立索引。

1. 為什麼會有index merge

我們的 where 中可能有多個條件(或者join)涉及到多個欄位，它們之間進行 AND 或者 OR，那麼此時就有可能會使用到 index merge 技術。index merge 技術如果簡單的說，其實就是：對多個索引分別進行條件掃描，然後將它們各自的結果進行合并(intersect/union)。

MySQL5.0之前，一個表一次只能使用一個索引，無法同時使用多個索引分別進行條件掃描。但是從5.1開始，引入了 index merge 最佳化技術，對同一個表可以使用多個索引分別進行條件掃描。

相關文檔：http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html (注意該文檔中說的有幾處錯誤)

The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. This access method merges index scans from a single table; it does not merge scans across multiple tables.

In EXPLAIN output, the Index Merge method appears as index_merge in the type column. In this case, the key column contains a list of indexes used, and key_lencontains a list of the longest key parts for those indexes.

index merge: 同一個表的多個索引的範圍掃描可以對結果進行合并，合并方式分為三種：union, intersection, 以及它們的組合(先內部intersect然後在外面union)。

官方文檔給出了四個例子：

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key=30;SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE ‘value%‘) AND t2.key1=t1.some_col;SELECT * FROM t1, t2 WHERE t1.key1=1 AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);

但是第四個例子，感覺並不會使用 index merge. 因為 t2.key1=t1.some_col 和 t2.key2=t1.some_col2 之間進行的是 OR 運算，而且 t2.key2 是複合索引的第二個欄位(非第一個欄位)。所以：t2.key2 = t1.some_col2 並不能使用到複合索引。(文檔這裡應該是錯誤的)

index merge 演算法根據合并演算法的不同分成了三種：intersect, union, sort_union. 

2. index merge 之 intersect

簡單而言，index intersect merge就是多個索引條件掃描得到的結果進行交集運算。顯然在多個索引提交之間是 AND 運算時，才會出現 index intersect merge. 下面兩種where條件或者它們的組合時會進行 index intersect merge:

1) 條件使用到複合索引中的所有欄位或者左首碼欄位(對單欄位索引也適用)

key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

2) 主鍵上的任何範圍條件

例子：

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;SELECT * FROM tbl_name WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

上面只說到複合索引，但是其實單欄位索引顯然也是一樣的。比如 select * from tab where key1=xx and key2 =xxx; 也是有可能進行index intersect merge的。另外上面兩種情況的 AND 組合也一樣可能會進行 index intersect merge.

The Index Merge intersection algorithm performs simultaneous scans on all used indexes and produces the intersection of row sequences that it receives from the merged index scans. (intersect merge運行方式：多個索引同時掃描，然後結果取交集)

If all columns used in the query are covered by the used indexes, full table rows are not retrieved (EXPLAIN output contains Using index in Extra field in this case). Here is an example of such a query:(索引覆蓋掃描，無需回表)

SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;

If the used indexes do not cover all columns used in the query, full rows are retrieved only when the range conditions for all used keys are satisfied.(索引不能覆蓋，則對滿足條件的再進行回表)

If one of the merged conditions is a condition over a primary key of an InnoDB table, it is not used for row retrieval, but is used to filter out rows retrieved using other conditions.

3. index merge 之 union

簡單而言，index uion merge就是多個索引條件掃描，對得到的結果進行並集運算，顯然是多個條件之間進行的是 OR 運算。

下面幾種類型的 where 條件，以及他們的組合可能會使用到 index union merge演算法：

1) 條件使用到複合索引中的所有欄位或者左首碼欄位(對單欄位索引也適用)

2) 主鍵上的任何範圍條件

3) 任何符合 index intersect merge 的where條件；

上面三種 where 條件進行 OR 運算時，可能會使用 index union merge演算法。

例子：

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR (key3=‘foo‘ AND key4=‘bar‘) AND key5=5;

第一個例子，就是三個 單欄位索引 進行 OR 運算，所以他們可能會使用 index union merge演算法。

第二個例子，複雜一點。(key1=1 AND key2=2) 是符合 index intersect merge; (key3=‘foo‘ AND key4=‘bar‘) AND key5=5 也是符合index intersect merge，所以 二者之間進行 OR 運算，自然可能會使用 index union merge演算法。

4. index merge 之 sort_union

This access algorithm is employed when the WHERE clause was converted to several range conditions combined by OR, but for which the Index Merge method union algorithm is not applicable.(多個條件掃描進行 OR 運算，但是不符合 index union merge演算法的，此時可能會使用 sort_union演算法)

官方文檔給出了兩個例子：

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;SELECT * FROM tbl_name WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;

但是顯然：因為 key_col2 不是複合索引的第一個欄位，對它進行 OR 運算，是不可能使用到索引的。所以這兩個例子應該也是錯誤的，它們實際上並不會進行 index sort_union merge演算法。

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.(sort-union合并演算法和union合并演算法的不同點，在於返回結果之前是否排序，為什麼需要排序呢？可能是因為兩個結果集，進行並集運算，需要去重，所以才進行排序？？？)

5. index merge的局限

1）If your query has a complex WHERE clause with deep AND/OR nesting and MySQL does not choose the optimal plan, try distributing terms using the following identity laws:

(x AND y) OR z = (x OR z) AND (y OR z)(x OR y) AND z = (x AND z) OR (y AND z)

如果我們的條件比較複雜，用到多個 and / or 條件運算，而MySQL沒有使用最優的執行計畫，那麼可以使用上面的兩個等式將條件進行轉換一下。

2）Index Merge is not applicable to full-text indexes. We plan to extend it to cover these in a future MySQL release.(全文索引沒有index merge)

3）Before MySQL 5.6.6, if a range scan is possible on some key, the optimizer will not consider using Index Merge Union or Index Merge Sort-Union algorithms. For example, consider this query:

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

For this query, two plans are possible:

An Index Merge scan using the (goodkey1 < 10 OR goodkey2 < 20) condition.

A range scan using the badkey < 30 condition.

However, the optimizer considers only the second plan.

這一點對以低版本的MySQL是一個很大的缺陷。就是如果where條件中有 >, <, >=, <=等條件，那麼最佳化器不會使用 index merge，而且還會忽略其他的索引，不會使用它們，哪怕他們的選擇性更優。

6. 對 index merge 的進一步最佳化

index merge使得我們可以使用到多個索引同時進行掃描，然後將結果進行合并。聽起來好像是很好的功能，但是如果出現了 index intersect merge，那麼一般同時也意味著我們的索引建立得不太合理，因為 index intersect merge 是可以通過建立 複合索引進行更一步最佳化的。

比如下面的select:

SELECT * FROM t1 WHERE key1=1 AND key2=2 AND key3=3;

顯然我們是可以在這三個欄位上建立一個複合索引來進行最佳化的，這樣就只需要掃描一個索引一次，而不是對三個所以分別掃描一次。

percona官網有一篇 比較複合索引和index merge 的好文章：Multi Column indexes vs Index Merge

7. 複合索引的最左首碼原則

上面我們說到，對複合索引的非最左首碼欄位進行 OR 運算，是無法使用到複合索引的。比如：

SELECT * FROM tbl_name WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;

其原因是，MySQL中的索引，使用的是B+tree, 也就是說他是：先按照複合索引的 第一個欄位的大小來排序，插入到 B+tree 中的，當第一個欄位值相同時，在按照第二個欄位的值比較來插入的。那麼如果我們需要對： OR key_col2 = 20 這樣的條件也使用複合索引，那麼該怎麼操作呢？應該要對複合索引進行全掃描，找出所有 key_col2 =20 的項，然後還要回表去判斷 nonkey_col=30，顯然代價太大了。所以一般而言 OR key_col2 = 20 這樣的條件是無法使用到複合索引的。如果一定要使用索引，那麼可以在 col2 上單獨建立一個索引。

複合索引的最左首碼原則：

MySQL中的複合索引，查詢時只會使用到最左首碼，比如：

mysql> show index from role_goods;+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| role_goods |          0 | PRIMARY  |            1 | id          | A         |       22816 |     NULL | NULL   |      | BTREE      |         |               || role_goods |          1 | roleId   |            1 | roleId      | A         |        1521 |     NULL | NULL   | YES  | BTREE      |         |               || role_goods |          1 | goodsId  |            1 | goodsId     | A         |        1521 |     NULL | NULL   | YES  | BTREE      |         |               || role_goods |          1 | roleId_2 |            1 | roleId      | A         |        1901 |     NULL | NULL   | YES  | BTREE      |         |               || role_goods |          1 | roleId_2 |            2 | status      | A         |        4563 |     NULL | NULL   | YES  | BTREE      |         |               || role_goods |          1 | roleId_2 |            3 | number      | A         |       22816 |     NULL | NULL   | YES  | BTREE      |         |               |+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+6 rows in set (0.00 sec)

上面有一個複合索引：roleId_2(roleId,status,number)，如果條件是： where roleId=xxx and number=xxx，那麼此時只會使用到最左首碼roleId，而不會使用到 number 來進行過濾。因為它們中間存在一個欄位 status 沒有出現在where條件中。實驗如下所示：

mysql> explain select * from role_goods where roleId=100000001 and status=1 and number=1 limit 1;+----+-------------+------------+------+-----------------+----------+---------+-------------------+------+-------+| id | select_type | table      | type | possible_keys   | key      | key_len | ref               | rows | Extra |+----+-------------+------------+------+-----------------+----------+---------+-------------------+------+-------+|  1 | SIMPLE      | role_goods | ref  | roleId,roleId_2 | roleId_2 | 23      | const,const,const |   13 | NULL  |+----+-------------+------------+------+-----------------+----------+---------+-------------------+------+-------+1 row in set (0.00 sec)mysql> explain select * from role_goods where roleId=100000001 and status=1 limit 1;+----+-------------+------------+------+-----------------+----------+---------+-------------+------+-------+| id | select_type | table      | type | possible_keys   | key      | key_len | ref         | rows | Extra |+----+-------------+------------+------+-----------------+----------+---------+-------------+------+-------+|  1 | SIMPLE      | role_goods | ref  | roleId,roleId_2 | roleId_2 | 14      | const,const |   13 | NULL  |+----+-------------+------------+------+-----------------+----------+---------+-------------+------+-------+1 row in set (0.00 sec)mysql> explain select * from role_goods where roleId=100000001 and number=1 limit 1;+----+-------------+------------+------+-----------------+--------+---------+-------+------+-------------+| id | select_type | table      | type | possible_keys   | key    | key_len | ref   | rows | Extra       |+----+-------------+------------+------+-----------------+--------+---------+-------+------+-------------+|  1 | SIMPLE      | role_goods | ref  | roleId,roleId_2 | roleId | 9       | const |   14 | Using where |+----+-------------+------------+------+-----------------+--------+---------+-------+------+-------------+1 row in set (0.01 sec)
mysql> explain select * from role_goods ignore index(roleId) where roleId=100000001 and number=1 limit 1;
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | role_goods | ref  | roleId_2      | roleId_2 | 9       | const |   14 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)

可以看到 key_len 的變化：

顯然最後一個查詢僅僅使用到符合索引中的 roleId, 沒有使用到 number. number使用在了 index conditon(也就是索引的push down技術)

注意最左首碼，並不是是指：一定要按照各個欄位出現在where中的順序來建立複合索引的。比如

where status=2 and roleId=xxx and number = xxx

該條件建立符合索引，我們並不需要按照status,roleId，number它們出現的順序來建立索引：

alter table role_goods add index sin(status,roleId,number)

這是對最左首碼極大的誤解。因為 where status=2 and roleId=xxx and number = xxx 和 where roleId=xxx and number = xxx and status=2它們是等價的。複合索引，哪個欄位放在最前面，需要根據哪個欄位經常出現在where條件中，哪個欄位的選擇性最好來判斷的。

 

進一步可以參考的文章：

http://www.orczhou.com/index.php/2013/01/mysql-source-code-query-optimization-index-merge/

http://www.cnblogs.com/nocode/archive/2013/01/28/2880654.html

MySQL 最佳化之 index merge(索引合并)