參考地址https://blog.csdn.net/Abysscarry/article/details/80792876
為了提高數(shù)據(jù)庫效率蛾绎，建索引是家常便飯裹匙；那么當(dāng)查詢條件為2個及以上時，我們是創(chuàng)建多個單列索引還是創(chuàng)建一個聯(lián)合索引好呢赌髓？他們之間的區(qū)別是什么？哪個效率高呢？我在這里詳細(xì)測試分析下蝴猪。
一调衰、聯(lián)合索引測試
注：Mysql版本為 5.7.20
創(chuàng)建測試表(表記錄數(shù)為63188)：
CREATE TABLE `t_mobilesms_11` (
? `id` bigint(20) NOT NULL AUTO_INCREMENT,
? `userId` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '用戶id，創(chuàng)建任務(wù)時的userid',
? `mobile` varchar(24) NOT NULL DEFAULT '' COMMENT '手機(jī)號碼',
? `billMonth` varchar(32) DEFAULT NULL COMMENT '賬單月',
? `time` varchar(32) DEFAULT NULL COMMENT '收/發(fā)短信時間',
? `peerNumber` varchar(64) NOT NULL COMMENT '對方號碼',
? `location` varchar(64) DEFAULT NULL COMMENT '通信地(自己的)',
? `sendType` varchar(16) DEFAULT NULL COMMENT 'SEND-發(fā)送; RECEIVE-收取',
? `msgType` varchar(8) DEFAULT NULL COMMENT 'SMS-短信; MSS-彩信',
? `serviceName` varchar(256) DEFAULT NULL COMMENT '業(yè)務(wù)名稱. e.g. 點(diǎn)對點(diǎn)(網(wǎng)內(nèi))',
? `fee` int(11) DEFAULT NULL COMMENT '通信費(fèi)(單位分)',
? `createTime` datetime DEFAULT NULL COMMENT '創(chuàng)建時間',
? `lastModifyTime` datetime DEFAULT NULL COMMENT '最后修改時間',
? PRIMARY KEY (`id`),
? KEY `聯(lián)合索引` (`userId`,`mobile`,`billMonth`)
) ENGINE=InnoDB AUTO_INCREMENT=71185 DEFAULT CHARSET=utf8 COMMENT='手機(jī)短信詳情'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
我們?yōu)閡serId, mobile, billMonth三個字段添加上聯(lián)合索引自阱！
我們選擇 explain 查看執(zhí)行計劃來觀察索引利用情況：
1.查詢條件為 userid
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222'
1
可以通過key看到嚎莉，聯(lián)合索引有效
2.查詢條件為 mobile
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972'
1
可以看到聯(lián)合索引無效
3.查詢條件為 billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE billMonth='2018-04'
1
聯(lián)合索引無效
4.查詢條件為 userid and mobile
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND mobile='13281899972'
1
聯(lián)合索引有效
5.查詢條件為 mobile and userid
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE? mobile='13281899972' AND userid='2222'
1
在4的基礎(chǔ)上調(diào)換了查詢條件的順序，發(fā)現(xiàn)聯(lián)合索引依舊有效
6.查詢條件為 userid or mobile
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' OR mobile='13281899972'
1
把 and 換成 or沛豌，發(fā)現(xiàn)聯(lián)合所索引無效趋箩！
7.查詢條件為 userid and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND billMonth='2018-04'
1
這兩個條件分別位于聯(lián)合索引位置的第一和第三，測試聯(lián)合索引依舊有效加派！
8.查詢條件為 mobile and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'
1
這兩個條件分別位于聯(lián)合索引位置的第二和第三叫确，發(fā)現(xiàn)聯(lián)合索引無效！
9.查詢條件為 userid and mobile and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE? userid='2222' AND mobile='13281899972' AND billMonth='2018-04'
1
所有條件一起查詢哼丈，聯(lián)合索引有效Ｆ裘谩（當(dāng)然，這才是最正統(tǒng)的用法白淼Ｈ拿住）
二、單列索引測試
創(chuàng)建三個單列索引：
1.查詢條件為 userid and mobile and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE? userid='2222' AND mobile='13281899972' AND billMonth='2018-04'
1
我們發(fā)現(xiàn)三個單列索引只有 userid 有效（位置為查詢條件第一個）车胡，其他兩個都沒有用上檬输。
那么為什么沒有用上呢？按照我們的理解匈棘，三個字段都加索引了丧慈，無論怎么排列組合查詢，應(yīng)該都能利用到這三個索引才對主卫！
其實這里其實涉及到了mysql優(yōu)化器的優(yōu)化策略逃默！當(dāng)多條件聯(lián)合查詢時，優(yōu)化器會評估用哪個條件的索引效率最高簇搅！它會選擇最佳的索引去使用完域，也就是說，此處userid 瘩将、mobile 吟税、billMonth這三個索引列都能用，只不過優(yōu)化器判斷只需要使用userid這一個索引就能完成本次查詢姿现，故最終explain展示的key為userid肠仪。
當(dāng)然，如果優(yōu)化器判斷本次查詢非要全使用三個索引才能效率最高备典，那么explain的key就會是userid 异旧、mobile 、billMonth提佣，都會生效泽艘！
2.查詢條件為 mobile and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'
1
我們發(fā)現(xiàn)此處兩個查詢條件只有 mobile 生效（位置也為查詢條件第一個）
3.查詢條件為 userid or mobile
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE? userid='2222' OR mobile='13281899972'
1
這次把 and 換成 or欲险，發(fā)現(xiàn)兩個查詢條件都用上索引了！
我們在網(wǎng)上可能常称ヤ蹋看到有人說or會導(dǎo)致索引失效天试，其實這并不準(zhǔn)確。而且我們首先需要判斷用的是哪個數(shù)據(jù)庫哪個版本然低，什么引擎喜每？
比如我用的是mysql5.7版本，innodb引擎雳攘，在這個環(huán)境下我們再去討論索引的具體問題带兜。
關(guān)于or查詢的真相是：
所謂的索引失效指的是：假如or連接的倆個查詢條件字段中有一個沒有索引的話,引擎會放棄索引而產(chǎn)生全表掃描。我們從or的基本含義出發(fā)應(yīng)該能理解并認(rèn)可這種說法吨灭，沒啥問題刚照。
此刻需要注意type類型為index_merge。
我查資料說mysql 5.0 版本之前 使用or只會用到一個索引（即使如上我給userid和mobile都建立的單列索引）喧兄，但自從5.0版本開始引入了index_merge索引合并優(yōu)化无畔！也就是說，我們現(xiàn)在可以利用上多個索引去優(yōu)化or查詢了吠冤。
index_merge作用：
1浑彰、索引合并是把幾個索引的范圍掃描合并成一個索引。
2拯辙、索引合并的時候郭变，會對索引進(jìn)行并集，交集或者先交集再并集操作涯保，以便合并成一個索引诉濒。
3、這些需要合并的索引只能是一個表的夕春。不能對多表進(jìn)行索引合并未荒。
index_merge應(yīng)用場景：
1.對OR語句求并集，如查詢SELECT * FROM TB1 WHERE c1="xxx" OR c2=""xxx"時撇他，如果c1和c2列上分別有索引茄猫，可以按照c1和c2條件進(jìn)行查詢狈蚤，再將查詢結(jié)果合并（union）操作困肩，得到最終結(jié)果
2.對AND語句求交集，如查詢SELECT * FROM TB1 WHERE c1="xxx" AND c2=""xxx"時脆侮，如果c1和c2列上分別有索引锌畸，可以按照c1和c2條件進(jìn)行查詢，再將查詢結(jié)果取交集（intersect）操作靖避，得到最終結(jié)果
3.對AND和OR組合語句求結(jié)果
三潭枣、結(jié)論
通俗理解：
利用索引中的附加列比默，您可以縮小搜索的范圍，但使用一個具有兩列的索引 不同于使用兩個單獨(dú)的索引盆犁。復(fù)合索引的結(jié)構(gòu)與電話簿類似命咐，人名由姓和名構(gòu)成，電話簿首先按姓氏對進(jìn)行排序谐岁，然后按名字對有相同姓氏的人進(jìn)行排序醋奠。如果您知道姓，電話簿將非常有用伊佃；如果您知道姓和名窜司，電話簿則更為有用，但如果您只知道名不姓航揉，電話簿將沒有用處塞祈。
所以說創(chuàng)建復(fù)合索引時，應(yīng)該仔細(xì)考慮列的順序帅涂。對索引中的所有列執(zhí)行搜索或僅對前幾列執(zhí)行搜索時议薪，復(fù)合索引非常有用；僅對后面的任意列執(zhí)行搜索時漠秋，復(fù)合索引則沒有用處笙蒙。
重點(diǎn)：
多個單列索引在多條件查詢時優(yōu)化器會選擇最優(yōu)索引策略，可能只用一個索引庆锦，也可能將多個索引全用上捅位！ 但多個單列索引底層會建立多個B+索引樹，比較占用空間搂抒，也會浪費(fèi)一定搜索效率艇搀，故如果只有多條件聯(lián)合查詢時最好建聯(lián)合索引！
最左前綴原則：
顧名思義是最左優(yōu)先求晶，以最左邊的為起點(diǎn)任何連續(xù)的索引都能匹配上焰雕，
注：如果第一個字段是范圍查詢需要單獨(dú)建一個索引
注：在創(chuàng)建聯(lián)合索引時，要根據(jù)業(yè)務(wù)需求芳杏，where子句中使用最頻繁的一列放在最左邊矩屁。這樣的話擴(kuò)展性較好橄碾，比如 userid 經(jīng)常需要作為查詢條件山橄，而 mobile 不常常用硫痰，則需要把 userid 放在聯(lián)合索引的第一位置姐赡，即最左邊
同時存在聯(lián)合索引和單列索引（字段有重復(fù)的）戈毒，這個時候查詢mysql會怎么用索引呢幌墓？
這個涉及到mysql本身的查詢優(yōu)化器策略了悲伶，當(dāng)一個表有多條索引可走時, Mysql 根據(jù)查詢語句的成本來選擇走哪條索引郊尝；
有人說where查詢是按照從左到右的順序，所以篩選力度大的條件盡量放前面约郁。網(wǎng)上百度過缩挑，很多都是這種說法，但是據(jù)我研究鬓梅，mysql執(zhí)行優(yōu)化器會對其進(jìn)行優(yōu)化供置，當(dāng)不考慮索引時，where條件順序?qū)π蕸]有影響绽快，真正有影響的是是否用到了索引士袄！
聯(lián)合索引本質(zhì)：
當(dāng)創(chuàng)建**(a,b,c)聯(lián)合索引時，相當(dāng)于創(chuàng)建了(a)單列索引**谎僻，(a,b)聯(lián)合索引以及**(a,b,c)聯(lián)合索引**
想要索引生效的話,只能使用 a和a,b和a,b,c三種組合娄柳；當(dāng)然，我們上面測試過艘绍，a,c組合也可以赤拒，但實際上只用到了a的索引，c并沒有用到诱鞠！
注：這個可以結(jié)合上邊的 通俗理解 來思考挎挖！
其他知識點(diǎn)：
1、需要加索引的字段航夺，要在where條件中
2蕉朵、數(shù)據(jù)量少的字段不需要加索引；因為建索引有一定開銷阳掐，如果數(shù)據(jù)量小則沒必要建索引（速度反而慢）
3始衅、避免在where子句中使用or來連接條件,因為如果倆個字段中有一個沒有索引的話,引擎會放棄索引而產(chǎn)生全表掃描
4、聯(lián)合索引比對每個列分別建索引更有優(yōu)勢缭保，因為索引建立得越多就越占磁盤空間汛闸，在更新數(shù)據(jù)的時候速度會更慢。另外建立多列索引時艺骂，順序也是需要注意的诸老，應(yīng)該將嚴(yán)格的索引放在前面，這樣篩選的力度會更大钳恕，效率更高别伏。
最后的說明：
網(wǎng)上關(guān)于索引優(yōu)化等文章太多了，針對各個數(shù)據(jù)庫各個版本各種引擎都可能存在不一樣的說法忧额！
我們的SQL引擎自帶的優(yōu)化也越來越強(qiáng)大厘肮，說不定你的某個SQL優(yōu)化認(rèn)知，其SQL引擎在某次升級中早就自優(yōu)化了宙址。
所以要么跟進(jìn)官方文檔轴脐，要么關(guān)注數(shù)據(jù)庫大牛的最新文章调卑，要么在現(xiàn)有數(shù)據(jù)庫環(huán)境下自己去親手測試抡砂！
數(shù)據(jù)庫領(lǐng)域的水很深大咱。。大家加油注益。碴巾。共勉 ~
————————————————
版權(quán)聲明：本文為CSDN博主「深寒丶」的原創(chuàng)文章，遵循 CC 4.0 BY-SA 版權(quán)協(xié)議丑搔，轉(zhuǎn)載請附上原文出處鏈接及本聲明厦瓢。
原文鏈接：https://blog.csdn.net/Abysscarry/article/details/80792876
