MySQL優(yōu)化中index_merge有什么作用

這篇文章主要介紹“MySQL優(yōu)化中index_merge有什么作用”，在日常操作中，相信很多人在MySQL優(yōu)化中index_merge有什么作用問題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”MySQL優(yōu)化中index_merge有什么作用”的疑惑有所幫助！接下來，請(qǐng)跟著小編一起來學(xué)習(xí)吧！

1. 為什么會(huì)有index merge

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

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

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

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: 同一個(gè)表的多個(gè)索引的范圍掃描可以對(duì)結(jié)果進(jìn)行合并，合并方式分為三種：union, intersection, 以及它們的組合(先內(nèi)部intersect然后在外面union)。

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

2. index merge 之 intersect

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

3. index merge 之 union

簡(jiǎn)單而言，index uion merge就是多個(gè)索引條件掃描，對(duì)得到的結(jié)果進(jìn)行并集運(yùn)算，顯然是多個(gè)條件之間進(jìn)行的是 OR 運(yùn)算。

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

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

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

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

上面三種 where 條件進(jìn)行 OR 運(yùn)算時(shí)，可能會(huì)使用 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.(多個(gè)條件掃描進(jìn)行 OR 運(yùn)算，但是不符合 index union merge算法的，此時(shí)可能會(huì)使用 sort_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:

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

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

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

上面我們說到，對(duì)復(fù)合索引的非最左前綴字段進(jìn)行 OR 運(yùn)算，是無法使用到復(fù)合索引的

SQL如下：
select cd.coupon_id, count(1) total from AAA cd
where  cd.coupon_act_id = 100476 and cd.deleted=0 and cd.pick_time is not null
group by cd.coupon_id ;
在AAA表中，coupon_act_id 和 deleted 都是獨(dú)立的索引
select count(*) from AAA  where coupon_act_id = 100476;   結(jié)果為12360行
select count(*) from AAA where deleted=0;  結(jié)果為1300W行
從上面的解釋我們可以看出來，index merge其實(shí)就是分別通過對(duì)兩個(gè)獨(dú)立的index進(jìn)行過濾之后，將過濾之后的結(jié)果聚合在一起，然后在返回結(jié)果集。
在我們的這個(gè)例子中，由于deleted字段的過濾性不好，故返回的rows依然很多，所以造成的很多的磁盤read，導(dǎo)致了cpu的負(fù)載非常的高，直接就出現(xiàn)了延遲。
ps：其實(shí)在這個(gè)case中，并不需要加2個(gè)條件的index，只需要將deleted這個(gè)index干掉，直接使用coupon_act_id這個(gè)index即可，畢竟這個(gè)index的過濾的結(jié)果集已經(jīng)很小了。
或者通過關(guān)閉index intersect功能也可以。

到此，關(guān)于“MySQL優(yōu)化中index_merge有什么作用”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！