深入理解 index merge 是使用索引進行優(yōu)化的重要基礎(chǔ)之一。理解了 index merge 技術(shù)，我們才知道應(yīng)該如何在表上建立索引。

1. 為什么會有index merge

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

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

相關(guān)文檔：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_len?contains a list of the longest key parts for those indexes.

index merge: 同一個表的多個索引的范圍掃描可以對結(jié)果進行合并，合并方式分為三種：union, intersection, 以及它們的組合(先內(nèi)部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 是復(fù)合索引的第二個字段(非第一個字段)。所以：t2.key2 = t1.some_col2 并不能使用到復(fù)合索引。(文檔這里應(yīng)該是錯誤的)

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

2. index merge 之 intersect

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

1)?條件使用到復(fù)合索引中的所有字段或者左前綴字段(對單字段索引也適用)

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;

上面只說到復(fù)合索引，但是其實單字段索引顯然也是一樣的。比如?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運行方式：多個索引同時掃描，然后結(jié)果取交集)

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就是多個索引條件掃描，對得到的結(jié)果進行并集運算，顯然是多個條件之間進行的是 OR 運算。

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

1)?條件使用到復(fù)合索引中的所有字段或者左前綴字段(對單字段索引也適用)

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算法。

第二個例子，復(fù)雜一點。(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 不是復(fù)合索引的第一個字段，對它進行 OR 運算，是不可能使用到索引的。所以這兩個例子應(yīng)該也是錯誤的，它們實際上并不會進行 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合并算法的不同點，在于返回結(jié)果之前是否排序，為什么需要排序呢？可能是因為兩個結(jié)果集，進行并集運算，需要去重，所以才進行排序？？？)

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)

如果我們的條件比較復(fù)雜，用到多個 and / or 條件運算，而MySQL沒有使用最優(yōu)的執(zhí)行計劃，那么可以使用上面的兩個等式將條件進行轉(zhuǎn)換一下。

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條件中有 >, <, >=, <=等條件，那么優(yōu)化器不會使用 index merge，而且還會忽略其他的索引，不會使用它們，哪怕他們的選擇性更優(yōu)。

6. 對 index merge 的進一步優(yōu)化

index merge使得我們可以使用到多個索引同時進行掃描，然后將結(jié)果進行合并。聽起來好像是很好的功能，但是如果出現(xiàn)了 index intersect merge，那么一般同時也意味著我們的索引建立得不太合理，因為 index intersect merge 是可以通過建立 復(fù)合索引進行更一步優(yōu)化的。

比如下面的select:

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

顯然我們是可以在這三個字段上建立一個復(fù)合索引來進行優(yōu)化的，這樣就只需要掃描一個索引一次，而不是對三個所以分別掃描一次。

percona官網(wǎng)有一篇 比較復(fù)合索引和index merge 的好文章：Multi Column indexes vs Index Merge

7. 復(fù)合索引的最左前綴原則

上面我們說到，對復(fù)合索引的非最左前綴字段進行 OR 運算，是無法使用到復(fù)合索引的。比如：

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

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

復(fù)合索引的最左前綴原則：

MySQL中的復(fù)合索引，查詢時只會使用到最左前綴，比如：

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)

上面有一個復(fù)合索引：roleId_2(roleId,status,number)，如果條件是： where roleId=xxx and number=xxx，那么此時只會使用到最左前綴roleId，而不會使用到 number 來進行過濾。因為它們中間存在一個字段 status 沒有出現(xiàn)在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技術(shù))

注意最左前綴，并不是是指：一定要按照各個字段出現(xiàn)在where中的順序來建立復(fù)合索引的。比如

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

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

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它們是等價的。復(fù)合索引，哪個字段放在最前面，需要根據(jù)哪個字段經(jīng)常出現(xiàn)在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

總結(jié)

以上是生活随笔為你收集整理的MySQL 优化之 index merge(索引合并)的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。