轉(zhuǎn)載自：聯(lián)合索引優(yōu)化多條件查詢

聯(lián)合索引是由多個(gè)字段組成的組合索引。若經(jīng)常需要使用多個(gè)字段的多條件查詢（WHERE col1 = … AND col2 = … AND col3 = …），可以考慮使用聯(lián)合索引。

現(xiàn)在數(shù)據(jù)表myIndex中i_testID是主鍵列，其他列無任何索引：

這里寫圖片描述

多條件查找名字為xiaoming，城市為beijing，年齡為21的人：

這里寫圖片描述

返回了一行數(shù)據(jù)，從執(zhí)行計(jì)劃中看到，查詢沒有使用任何索引，進(jìn)行了全表掃描，磁盤IO大。

為vc_Name建立索引：

這里寫圖片描述

進(jìn)行同樣的查詢并查看執(zhí)行計(jì)劃：

這里寫圖片描述

返回了相同的結(jié)果，分析執(zhí)行計(jì)劃：SQL通過剛剛建立的index_of_name索引，不再進(jìn)行全表掃描，而是先在索引中查找滿足節(jié)點(diǎn)值為xiaoming的節(jié)點(diǎn)（有5個(gè)），再指向數(shù)據(jù)庫中相應(yīng)的行，返回一個(gè)初步結(jié)果集后再由另外兩個(gè)條件進(jìn)行一步步篩選得到最終結(jié)果。大大減少了磁盤IO，查詢效率也高于前者。

雖然在 vc_Name 上建立了索引，查詢時(shí)MYSQL不用掃描整張表，效率有所提高，但離我們的要求還有一定的距離。同樣的，在 vc_City 和 i_Age 分別建立的MySQL單列索引的效率相似。為了進(jìn)一步榨取 MySQL 的效率，就要考慮建立組合索引。

為多條件涉及的列建立聯(lián)合索引：

這里寫圖片描述

值得注意的是，建立索引的時(shí)候應(yīng)該根據(jù)需要規(guī)定索引長度，例如一個(gè)人的名字長度應(yīng)該不會超過10個(gè)字符，通過vc_Name(10)規(guī)定索引長度后一定長度可以減少索引所占內(nèi)存。

現(xiàn)在表中的結(jié)構(gòu)：

這里寫圖片描述

進(jìn)行相同的查詢并查看執(zhí)行計(jì)劃：

這里寫圖片描述

返回相同的結(jié)果，從執(zhí)行計(jì)劃可以看到：本次查詢使用了聯(lián)合索引name_city_age，在遍歷索引時(shí)就確定了只有一個(gè)節(jié)點(diǎn)滿足條件，直接指向數(shù)據(jù)庫表進(jìn)行查詢（rows：1）。有更少的磁盤IO，所用時(shí)間更少！

使用聯(lián)合索引應(yīng)該注意：

1. MySQL使用聯(lián)合索引只能使用左側(cè)的部分，例如INDEX(a,b,c)，當(dāng)條件為a或a,b或a,b,c時(shí)都可以使用索引，但是當(dāng)條件為b,c時(shí)將不會使用索引。這好比一本先根據(jù)姓，再根據(jù)名進(jìn)行排序的電話簿，當(dāng)查找的時(shí)候有姓的條件，效率會比沒有任何條件高；如果在姓的基礎(chǔ)上還有名的條件，效率會更高；但若只有名的條件，電話簿將不起作用。
2. 離散度更高的索引應(yīng)該放在聯(lián)合索引的前面，因?yàn)殡x散度高索引的可選擇性高。考慮一種極端的情況，數(shù)據(jù)表中有100條記錄，若INDEX(a,b)中a只有兩種情況，而b有100種情況。這樣對于查詢唯一記錄a = …，b = …時(shí)，先遍歷全部索引看滿足a條件的有50個(gè)索引節(jié)點(diǎn)，接下來還要再一個(gè)個(gè)遍歷這50個(gè)索引節(jié)點(diǎn)。如果是INDEX(b,a)，先遍歷全部索引發(fā)現(xiàn)滿足b條件的索引節(jié)點(diǎn)只有一個(gè)，再遍歷這個(gè)節(jié)點(diǎn)發(fā)現(xiàn)也滿足a條件。雖然最后都能找到那個(gè)唯一的索引節(jié)點(diǎn)，但是第二種索引順序?qū)σ姹闅v索引效率有很大的提高（用電話薄的思想去思考問題）。
3. 查看列的離散程度：

這里寫圖片描述

customer_id列的離散程度更高，建聯(lián)合索引時(shí)應(yīng)該INDEX(customer_id,staff_id);