鑒于讀者們?cè)u(píng)論,但平時(shí)較忙用不到回來(lái)看筆記時(shí)也看不到評(píng)論,今天有時(shí)間再補(bǔ)充下實(shí)測(cè)圖片吧
可以略過(guò)
最近找工作面試時(shí)灶搜，被問(wèn)及了mysql聯(lián)合索引的一些問(wèn)題饰剥，之前看過(guò)‘高性能mysql’中第五章關(guān)于索引的部分曾掂，也看過(guò)部分類(lèi)似的問(wèn)題聋亡，但我回答的卻被面試官質(zhì)疑绷旗，我說(shuō)從mysql某版本后 where后 與a b c 順序無(wú)關(guān)了喜鼓，b = and c = and a = 也會(huì)使用聯(lián)合索引，順序不是abc時(shí)mysql索引優(yōu)化器會(huì)自動(dòng)優(yōu)化衔肢。說(shuō)我錯(cuò)了 叉 叉 叉... ... 還出來(lái)interview...
不過(guò)
確實(shí)如果在網(wǎng)上搜索關(guān)于這相關(guān)問(wèn)題庄岖，一些老的文章中真的說(shuō)這是錯(cuò)的，但近幾年的文章都有說(shuō) 和順序無(wú)關(guān)了
實(shí)際在本地測(cè)一下吧
數(shù)據(jù)準(zhǔn)備 說(shuō)明
本地 MYSQL版本 8.0.17
現(xiàn)在實(shí)測(cè)(2022-03-2) 版本 8.0.27
mysql創(chuàng)建一張表角骤，表名：‘test_models’
索引:
腳本隨機(jī)生成100萬(wàn)條數(shù)據(jù)
- id列為 主鍵隅忿，int類(lèi)型 ，自增
- a,b,c,d,e 全部是int（11）
- 為（a,b,c）添加一個(gè)聯(lián)合索引 index_abc
執(zhí)行語(yǔ)句大體這樣吧：
CREATE TABLE `test_models` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`a` INT(11) DEFAULT NULL,
`b` INT(11) DEFAULT NULL,
`c` INT(11) DEFAULT NULL,
`d` INT(11) DEFAULT NULL,
`e` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_abc` (`a`,`b`,`c`)
);
我用代碼往表中寫(xiě)入100萬(wàn)條數(shù)據(jù)
其中每行數(shù)據(jù)取值
- a 范圍 0 - 1000 隨機(jī)整數(shù)
- b 范圍 0 - 10000 隨機(jī)整數(shù)
- c 范圍 0 - 100000 隨機(jī)整數(shù)
- d 范圍 0 - 1000 隨機(jī)整數(shù)
- e 范圍 0 - 1000 隨機(jī)整數(shù)
開(kāi)始測(cè)試 聯(lián)合索引（a,b,c）
使用 ‘EXPLAIN’ sql語(yǔ)句查看執(zhí)行詳情
EXPLAIN SELECT * FROM test_models WHERE a = 100 AND b = 1000 AND c = 10000;
結(jié)論（下面結(jié)論全部都實(shí)際測(cè)試過(guò)）
AND AND 只要用到了最左側(cè)a列，和順序無(wú)關(guān) 都會(huì)使用 索引
a = 1 AND b = 2 AND c = 3 ; 使用索引
c = 1 AND b = 2 AND a = 3 ; 使用索引
a = 1 AND b = 2 ; 使用索引
a = 1 AND c = 3 ; 使用索引
c = 1 AND a = 2 ; 使用索引
測(cè)試截圖:
不包含最左側(cè)的 a 的不使用索引
c = 3 ; 未使用索引
b = 2 ; 未使用索引
b = 2 AND c = 3 ; 未使用索引
c = 1 AND b = 2 ; 未使用索引
測(cè)試截圖:
OR 不使用索引
a = 1 AND b = 2 OR c = 3 未使用索引
a = 1 OR b = 2 AND c = 3 未使用索引
a = 1 OR b = 2 OR c = 3 未使用索引
最左側(cè)的‘a(chǎn)’列 被大于背桐，小于刘陶，不等于比較的 ，不一定使用索引.
看比較后結(jié)果集是否足夠小.
鑒于此處有網(wǎng)友實(shí)測(cè)和我結(jié)論不一樣,這里我又多進(jìn)行了一些數(shù)字測(cè)試,找到了一些規(guī)律
發(fā)現(xiàn)有時(shí)會(huì)使用索引,有時(shí)不會(huì)使用索引
這應(yīng)該和MySQL現(xiàn)在對(duì)索引的優(yōu)化有關(guān).
我的數(shù)據(jù)a列取值是0-1000,似乎當(dāng)a列被極小范圍或足夠小范圍查詢(xún)時(shí)是會(huì)使用索引的,當(dāng)a列被比較小查詢(xún)或取值范圍較大時(shí),MySQL就放棄了使用索引.(先只是猜測(cè),等有時(shí)間好好查查資料)
測(cè)試
a > 1時(shí),確實(shí)不會(huì)使用索引,這里2-1000范圍太大了
a < 1時(shí),這里會(huì)使用索引,這里a取值是0-1000,<1范圍就極小了,就是0了
實(shí)測(cè)a逐漸增大 a < 10 ,a<20,a<100,a<140;時(shí)都會(huì)使用索引.
但當(dāng) a < 150時(shí),就不再使用索引了.
同時(shí)a 大于某數(shù)時(shí)情況是同樣的.
a > 1 這里沒(méi)有使用索引 ( a > 1,等于2-1000)
a > 850時(shí),會(huì)開(kāi)始使用索引
a > 1 AND b = 2 AND c = 3 此處情況不定,看a被比較后取值范圍集是否足夠小,足夠小時(shí)會(huì)開(kāi)始使用索引
a < 1 AND b = 2 AND c = 3 未使用索引
a > 1 ; 未使用索引
a <> 1 AND b = 2 AND c = 3 未使用索引
測(cè)試截圖:
當(dāng) a < 150 時(shí)就不再使用索引,這時(shí)掃描的rows值已經(jīng)很大了,和推斷一樣.
a > x 和 上面一樣, 當(dāng)a被比較后取值范圍足夠小時(shí)才會(huì)使用索引推論一樣.
999 時(shí) rows直接=1了,只掃描了一行數(shù)據(jù)
image.png
最左側(cè)a=某某牢撼，后面列大于小于無(wú)所謂匙隔，都使用索引（但后面必須 and and ）
a = 1 AND b < 2 AND c = 3 使用索引
a = 1 AND c = 2 AND b < 3 使用索引
a = 1 AND b < 2 使用索引
a = 1 AND b <> 2 AND c = 3 使用索引
// 可以說(shuō) OR一出現(xiàn)就不使用
a = 1 AND b < 2 OR c = 2 未使用索引
OR出現(xiàn)
ORDER BY
a = 某，后面order 無(wú)所謂 都 使用索引 （和最上面的最左匹配一樣）
看
a = 1 AND b = 2 AND c = 3 ORDER BY a;// 或者 ORDER BY b 熏版， ORDER BY c 纷责，ORDER BY d, 使用索引
a = 1 ORDER BY a; // 或者 ORDER BY b,ORDER BY c,ORDER BY d 使用abc索引
b = 某，不使用
b = 1 ORDER BY a; //ORDER BY b 都 未使用索引
ORDER BY 似乎沒(méi)有任何影響撼短，去掉 ORDER BY 確實(shí)和上面的測(cè)試重復(fù)
