什么是索引合優(yōu)化？探究一下索引合并的幾種情況

什么是索引合優(yōu)化

在使用 explain 命令分析 SQL 執(zhí)行情況的時(shí)候，type列會(huì)描述了表如何被連接，這個(gè)列的內(nèi)容直接反映了 SQL 執(zhí)行的效率。當(dāng)里面的內(nèi)容展示為 index_merge時(shí)表示使用了索引合并優(yōu)化,在這種情況下輸出行中的key列包含具體使用的索引。

MySQL 的索引合并優(yōu)化是一種查詢優(yōu)化技術(shù)，它利用多個(gè)索引來(lái)加速查詢的執(zhí)行。當(dāng)一個(gè)查詢中包含多個(gè)條件，并且這些條件分別適用于不同的索引時(shí)，MySQL 可以將這些索引合并起來(lái)使用，減少了回表的次數(shù)，以加速查詢的執(zhí)行。

簡(jiǎn)單來(lái)說(shuō)過(guò)程是這樣：

查詢條件同時(shí)包含 index1 和 index2。

在根據(jù) index1 和 index2 查詢到主鍵后并沒(méi)有直接分別去聚簇索引中查詢，而是先對(duì)他們查到的主鍵做處理，合并到一起。

根據(jù)處理后的主鍵去聚簇索引執(zhí)行查詢，只需一次回表就可以拿到結(jié)果。

下面我們探究一下索引合并的幾種情況。

準(zhǔn)備

我們使用如下數(shù)據(jù)做測(cè)試，并且建立了三個(gè)索引。

CREATETABLE`test_table`(
`id`bigint(20)NOTNULLAUTO_INCREMENT,
`user_id`bigint(20)NOTNULL,
`name`varchar(255)DEFAULT'',
`merchant_id`bigint(20)NOTNULL,
`area`int(11)DEFAULTNULL,
PRIMARYKEY(`id`),
UNIQUEKEY`uq_user_id`(`user_id`)USINGBTREE,
KEY`idx_merchant_id`(`merchant_id`)USINGBTREE,
KEY`idx_area`(`area`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=410DEFAULTCHARSET=utf8mb4

在表中，我初始化了 100 多條數(shù)據(jù)用于測(cè)試。

基本用法

Index Merge 通過(guò)多次 range掃描檢索行并將它們的結(jié)果合并為一個(gè)。僅限合并來(lái)自單個(gè)表的索引掃描，而不是跨多個(gè)表的掃描。合并可以產(chǎn)生其底層掃描的并集、交集或交集并集，所以產(chǎn)生了三種算法。

可以使用索引合并的示例查詢：

SELECT*FROMtest_tableWHEREmerchant_id=3ORarea=3;

SELECT*FROMtest_tableWHERE(merchant_id=3ORarea=3)ANDname='daniel';

SELECT*FROMt1,t2
WHERE(t1.key1IN(1,2)ORt1.key2LIKE'value%')
ANDt2.key1=t1.some_col;

SELECT*FROMt1,t2
WHEREt1.key1=1
AND(t2.key1=t1.some_colORt2.key2=t1.some_col2);

索引合并優(yōu)化算法注意事項(xiàng)如下 ：

1、如果您的查詢有一個(gè)復(fù)雜的WHERE 子句，帶有深度 AND/OR 嵌套，而 MySQL 沒(méi)有選擇最佳執(zhí)行計(jì)劃，請(qǐng)嘗試使用以下恒等變換。

(xANDy)ORz=>(xORz)AND(yORz)
(xORy)ANDz=>(xANDz)OR(yANDz)

2、Index Merge 不適用于全文索引。

三種算法

Index Merge 訪問(wèn)方法有幾種算法，顯示在輸出Extra字段 中EXPLAIN：

交集算法Using intersect(...)

并集算法 Using union(...)

排序并集算法Using sort_union(...)

下面我們?cè)敿?xì)介紹這些算法，優(yōu)化器根據(jù)各種可用選項(xiàng)的成本估算，在不同的索引合并算法之間進(jìn)行選擇。

Index Merge 的使用受制于 系統(tǒng)變量的index_merge、 index_merge_intersection、 index_merge_union和 index_merge_sort_unionflags 的值optimizer_switch 。默認(rèn)情況下，所有這些標(biāo)志都是on. 要僅啟用某些算法，請(qǐng)?jiān)O(shè)置index_merge 為off，并僅啟用應(yīng)允許的其他算法。

1.交集算法

當(dāng)查詢條件是多個(gè)查詢的時(shí)候，并且條件用 and 關(guān)聯(lián)，這種情況會(huì)使用交集(intersect)算法，滿足以下條件之一都可以。

這種形式的 -part 表達(dá)式*N*，其中索引具有精確的 *N*部分（即，所有索引部分都被覆蓋）：

key_part1=const1ANDkey_part2=const2...ANDkey_partN=constN

InnoDB 引擎下使用主鍵范圍條件查詢。如果其中一個(gè) merge 條件是對(duì)表主鍵的范圍查詢，則它不用于行檢索，而是用于過(guò)濾掉使用其他條件檢索的行。

explainselect*fromtest_tablewhereid

	

	索引合并交集算法對(duì)所有使用的索引執(zhí)行同時(shí)掃描，并生成它從合并索引掃描中接收到的主鍵的交集。如果查詢中使用的所有列都被使用的索引覆蓋，則不會(huì)檢索完整的表行（具體使用的算法輸出在 Extra 字段中）。

	2.并集算法

	并集（union）算法適用于將表的WHERE 子句轉(zhuǎn)換為不同索引列組合的多個(gè)范圍條件，并且使用OR關(guān)聯(lián)，且每個(gè)條件為以下之一：

	不同普通索引列使用 or 關(guān)聯(lián)

	
key_part1=const1ORkey_part2=const2...ORkey_partN=constN


	

	InnoDB引擎下主鍵使用范圍查詢

	例子：

	
explainselect*fromtest_tablewheremerchant_id=3orarea=3;


	

	3.排序并集算法

	排序并集（sort_union）算法適用于由 or 關(guān)鍵詞組合的多個(gè)范圍查詢。

	例子：

	
explainselect*fromtest_tablewheremerchant_id

	

	sort-union 算法和 union 算法之間的區(qū)別在于，sort-union 算法必須首先獲取所有行的行 ID ，然后在回表之前它們進(jìn)行排序。

	

	







	審核編輯：劉清