在Mysql建立多列索引(聯合索引)有最左首碼的原則,即最左優先。 如果我們建立了一個2列的聯合索引(col1,col2),實際上已經建立了兩個聯合索引(col1)、(col1,col2); 如果有一個3列索引(col1,col2,col3),實際上已經建立了三個聯合索引(col1)、(col1,c... ...
在Mysql建立多列索引(聯合索引)有最左首碼的原則,即最左優先。
如果我們建立了一個2列的聯合索引(col1,col2),實際上已經建立了兩個聯合索引(col1)、(col1,col2);
如果有一個3列索引(col1,col2,col3),實際上已經建立了三個聯合索引(col1)、(col1,col2)、(col1,col2,col3)。
解釋
1、b+樹的數據項是複合的數據結構,比如(name,age,sex)的時候,b+樹是按照從左到右的順序來建立搜索樹的,比如當(張三,20,F)這樣的數據來檢索的時候,b+樹會優先比較name來確定下一步的所搜方向,如果name相同再依次比較age和sex,最後得到檢索的數據;但當(20,F)這樣的沒有name的數據來的時候,b+樹就不知道第一步該查哪個節點,因為建立搜索樹的時候name就是第一個比較因數,必須要先根據name來搜索才能知道下一步去哪裡查詢。
2、比如當(張三,F)這樣的數據來檢索時,b+樹可以用name來指定搜索方向,但下一個欄位age的缺失,所以只能把名字等於張三的數據都找到,然後再匹配性別是F的數據了, 這個是非常重要的性質,即索引的最左匹配特性。(這種情況無法用到聯合索引)
mysql里創建聯合索引的意義
一個頂三個
建了一個(a,b,c)的複合索引,那麼實際等於建了(a),(a,b),(a,b,c)三個索引,因為每多一個索引,都會增加寫操作的開銷和磁碟空間的開銷。對於大量數據的表,這可是不小的開銷!
覆蓋索引
同樣的有複合索引(a,b,c),如果有如下的sql: select a,b,c from table where a=1 and b = 1。那麼MySQL可以直接通過遍歷索引取得數據,而無需回表,這減少了很多的隨機io操作。減少io操作,特別的隨機io其實是dba主要的優化策略。所以,在真正的實際應用中,覆蓋索引是主要的提升性能的優化手段之一
索引列越多,通過索引篩選出的數據越少
有1000W條數據的表,有如下sql:select * from table where a = 1 and b =2 and c = 3,假設假設每個條件可以篩選出10%的數據,如果只有單值索引,那麼通過該索引能篩選出1000W10%=100w 條數據,然後再回表從100w條數據中找到符合b=2 and c= 3的數據,然後再排序,再分頁;如果是複合索引,通過索引篩選出1000w 10% 10% 10%=1w,然後再排序、分頁,哪個更高效,一眼便知
創建聯合索引時列的選擇原則
- 經常用的列優先(最左匹配原則)
- 離散度高的列優先(離散度高原則)
- 寬度小的列優先(最少空間原則)
列的離散性計算:count(distinct col)/ count(col)
例如:
id列一共9列都不重覆 9/9 = 1
性別列一共9列只有(男或者女)兩列 2/9 約等於0.2
離散性越高選擇性越大
轉載:
mysql 最左匹配 聯合索引