假設(shè)數(shù)據(jù) 表 T (a,b,c) rowid 為物理位置
rowid a b c
(1) 1 1 1
(2) 2 1 13
(3) 2 2 14
(4) 1 3 3
(5) 2 3 12
(6) 1 2 5
(7) 2 3 9
(8) 1 2 2
(9) 1 3 6
(10) 2 2 11
(11) 2 2 8
(12) 1 1 7
(13) 2 3 15
(14) 1 1 4
(15) 2 1 10
當(dāng)你創(chuàng)建一個索引 create index xxx on t(a,b)泰鸡,則索引文件邏輯上等同于如下
a b rowid
1 1 1
1 1 12
1 1 14
1 2 6
1 2 8
1 3 4
1 3 9
2 1 2
2 1 15
2 2 3
2 2 10
2 2 11
2 3 5
2 3 7
2 3 13
當(dāng) select * from T where a = 1 and b = 3 的時候债蓝, 數(shù)據(jù)庫系統(tǒng)可以直接從索引文件中直接二分法找到 A = 1 的記錄,然后再 B = 3 的記錄
但如果你 where b = 3 則需要遍歷這個索引表的全部
mysql 建立多列索引(聯(lián)合索引)有最左前綴的原則盛龄,即最左優(yōu)先饰迹,如:
如果有一個 2 列的索引 (col1, col2)芳誓,則已經(jīng)對 (col1)、(col1, col2) 上建立了索引啊鸭;
如果有一個 3 列索引 (col1, col2, col3)锹淌,則已經(jīng)對 (col1)、(col1, col2)赠制、(col1, col2, col3) 上建立了索引赂摆;
原理
b+ 樹的數(shù)據(jù)項是復(fù)合的數(shù)據(jù)結(jié)構(gòu),比如 (name,age,sex) 的時候钟些,b+ 樹是按照從左到右的順序來建立搜索樹的烟号,比如當(dāng) (張三,20,F) 這樣的數(shù)據(jù)來檢索的時候,b+ 樹會優(yōu)先比較 name 來確定下一步的所搜方向政恍,如果 name 相同再依次比較 age 和 sex汪拥,最后得到檢索的數(shù)據(jù);但當(dāng) (20,F) 這樣的沒有 name 的數(shù)據(jù)來的時候篙耗,b+ 樹就不知道第一步該查哪個節(jié)點喷楣,因為建立搜索樹的時候 name 就是第一個比較因子,必須要先根據(jù) name 來搜索才能知道下一步去哪里查詢
比如當(dāng) (張三, F) 這樣的數(shù)據(jù)來檢索時鹤树,b+ 樹可以用 name 來指定搜索方向铣焊,但下一個字段 age 的缺失,所以只能把名字等于張三的數(shù)據(jù)都找到罕伯,然后再匹配性別是 F 的數(shù)據(jù)了曲伊, 這個是非常重要的性質(zhì),即索引的最左匹配特性追他。(這種情況無法用到聯(lián)合索引)
mysql 查詢優(yōu)化器
如果建的索引是 (name, cid)坟募。而查詢的語句是 cid=1 AND name=’小紅’。為什么還能利用到索引邑狸?
當(dāng)按照索引中所有列進(jìn)行精確匹配(“=” 或 “IN”)時懈糯,索引可以被用到,并且 type 為 const单雾。理論上索引對順序是敏感的赚哗,但是由于 MySQL 的查詢優(yōu)化器會自動調(diào)整 where 子句的條件順序以使用適合的索引,所以 MySQL 不存在 where 子句的順序問題而造成索引失效
注意事項
范圍查詢
mysql 會一直向右匹配直到遇到范圍查詢(>硅堆、<屿储、between、like)就停止匹配渐逃。范圍列可以用到索引够掠,但是范圍列后面的列無法用到索引。即茄菊,索引最多用于一個范圍列疯潭,因此如果查詢條件中有兩個范圍列則無法全用到索引like 語句的索引問題
如果通配符 % 不出現(xiàn)在開頭赊堪,則可以用到索引,但根據(jù)具體情況不同可能只會用其中一個前綴
在 like “value%” 可以使用索引竖哩,但是 like “%value%” 不會使用索引雹食,走的是全表掃描不要在列上進(jìn)行運算
如果查詢條件中含有函數(shù)或表達(dá)式,將導(dǎo)致索引失效而進(jìn)行全表掃描
例如 select * from user where YEAR(birthday) < 1990
可以改造成 select * from users where birthday <’1990-01-01′索引不會包含有 NULL 值的列
只要列中包含有 NULL 值都將不會被包含在索引中期丰,復(fù)合索引中只要有一列含有 NULL 值群叶,那么這一列對于此復(fù)合索引就是無效的。所以在數(shù)據(jù)庫設(shè)計時不要讓字段的默認(rèn)值為 NULL盡量選擇區(qū)分度高的列作為索引钝荡,區(qū)分度的公式是 count(distinct col)/count(*)街立,表示字段不重復(fù)的比例,比例越大我們掃描的記錄數(shù)越少埠通,唯一鍵的區(qū)分度是 1赎离,而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是 0端辱。一般需要 join 的字段都要求區(qū)分度 0.1 以上梁剔,即平均 1 條掃描 10 條記錄
覆蓋索引的好處
如果一個索引包含所有需要的查詢的字段的值,我們稱之為覆蓋索引舞蔽。覆蓋索引是非常有用的工具荣病,能夠極大的提高性能。因為渗柿,只需要讀取索引个盆,而無需讀表,極大減少數(shù)據(jù)訪問量