建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
前面一篇文章，松哥和大家聊了 MySQL 中的索引合并，雖然 MySQL 提供了索引合并機(jī)制來(lái)提升 SQL 執(zhí)行的效率，然而在具體實(shí)踐中，如果能避免發(fā)生索引合并是最好的，畢竟這是沒(méi)辦法的辦法，是一個(gè)下下策。發(fā)生索引合并大概率是因?yàn)槲覀兯饕谠O(shè)計(jì)的時(shí)候就有問(wèn)題，設(shè)計(jì)好聯(lián)合索引，我們就能在一定程度上避免發(fā)生索引合并問(wèn)題。
公司主營(yíng)業(yè)務(wù)：成都網(wǎng)站設(shè)計(jì)、網(wǎng)站制作、移動(dòng)網(wǎng)站開(kāi)發(fā)等業(yè)務(wù)。幫助企業(yè)客戶(hù)真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競(jìng)爭(zhēng)能力。創(chuàng)新互聯(lián)是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開(kāi)放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對(duì)我們的高要求，感謝他們從不同領(lǐng)域給我們帶來(lái)的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶(hù)帶來(lái)驚喜。創(chuàng)新互聯(lián)推出高陵免費(fèi)做網(wǎng)站回饋大家。
1. 聯(lián)合索引
1.1 什么是聯(lián)合索引
聯(lián)合索引就是數(shù)據(jù)表中的多個(gè)字段，共同組成一個(gè)索引。由于 InnoDB 中索引的數(shù)據(jù)結(jié)構(gòu)是一個(gè) B+Tree，當(dāng)是一個(gè)聯(lián)合索引的時(shí)候，排序的時(shí)候會(huì)首先按照聯(lián)合索引的第一個(gè)字段排序，如果第一個(gè)字段的值相同，則按照第二個(gè)字段排序，如果第二個(gè)字段的值也相同，則按照第三個(gè)字段排序，以此類(lèi)推。
舉一個(gè)簡(jiǎn)單的例子，假設(shè)我有如下數(shù)據(jù)：
|
id |
username |
age |
address |
gender |
|
1 |
ab |
99 |
深圳 |
男 |
|
2 |
ac |
98 |
廣州 |
男 |
|
3 |
af |
88 |
北京 |
女 |
|
4 |
bc |
80 |
上海 |
女 |
|
5 |
bg |
85 |
重慶 |
女 |
|
6 |
bw |
95 |
天津 |
男 |
|
7 |
bw |
99 |
?？?/p> |
女 |
|
8 |
cc |
92 |
武漢 |
男 |
|
9 |
ck |
90 |
深圳 |
男 |
|
10 |
cx |
93 |
深圳 |
男 |
現(xiàn)在我給 username 和 age 字段建立聯(lián)合索引，那么 B+Tree 在排序的時(shí)候，會(huì)首先按照 username 排序，當(dāng) username 相同的時(shí)候，再按照 age 進(jìn)行排序。畫(huà)出來(lái)的 B+Tree 如下圖：
如上圖，bw 相同的時(shí)候，按照 age 進(jìn)行排序。
如果我們想要在 MySQL 中，讓聯(lián)合索引發(fā)揮最大作用，就要充分考慮到聯(lián)合索引中各字段的順序。
1.2 聯(lián)合索引順序要考慮哪些因素？
在設(shè)計(jì)聯(lián)合索引的時(shí)候，我們最容易想到的原則是查詢(xún)條件影響了聯(lián)合索引中各個(gè)字段的順序，要根據(jù)查詢(xún)條件來(lái)設(shè)計(jì)聯(lián)合索引中各個(gè)字段的順序。
實(shí)際上，除了上面提到的查詢(xún)條件之外，聯(lián)合索引的順序還會(huì)影響到查詢(xún)的排序和分組等，所以，設(shè)計(jì)聯(lián)合索引的順序可以算是一個(gè)真真正正的技術(shù)活。
2. 案例分析
松哥這里還是使用官方的案例吧，小伙伴們?cè)诠娞?hào)后臺(tái)回復(fù) mysql官方案例 可以獲取到這個(gè)數(shù)據(jù)庫(kù)腳本的下載地址。
在 MySQL 的官方案例中，有一個(gè)支付表 payment，如下圖：
小伙伴們從圖中可以看到，這個(gè)表中有一個(gè) customer_id 和一個(gè) staff_id，現(xiàn)在假設(shè)我想要按照這兩個(gè)來(lái)進(jìn)行搜索，例如執(zhí)行如下 SQL：
select * from payment where customer_id=1 and staff_id=2;查詢(xún)條件有兩個(gè)，我想建立一個(gè)聯(lián)合索引，那么究竟是把 customer_id 放在前面還是把 staff_id 放在前面呢？
一個(gè)比較常用的法則是看字段的選擇性，選擇性高的字段應(yīng)該是放在前面。有的小伙伴可能還不清楚什么是字段的選擇性，可以參考松哥之前的文章：前綴索引，在性能和空間中尋找平衡。
那么怎么獲取各個(gè)字段的選擇性呢？這個(gè)很好計(jì)算，一個(gè) SQL 搞定，如下：
select count(distinct customer_id)/count(1) as c,count(distinct staff_id)/count(1) as s from payment;執(zhí)行結(jié)果如下：
可以看到，customer_id 的選擇性為 0.0373，而 staff_id 的選擇性為 0.0001，那么在建立聯(lián)合索引的時(shí)候，將 customer_id 放在第一列顯然更合適一些，因?yàn)樗倪x擇性更高（意味著字段里邊重復(fù)的值相對(duì)來(lái)說(shuō)會(huì)少一些），根據(jù) customer_id 更容易鎖定一行，查詢(xún)效率要更高一些。
不過(guò)需要注意，上面的法則并非放之四海而皆準(zhǔn)，還是要具體問(wèn)題具體分析。在一些特別極端的情況下，索引選擇性非常之低，那個(gè)時(shí)候就沒(méi)有必要建立聯(lián)合索引了。特殊情況甚至需要我們從業(yè)務(wù)邏輯上去解決。
松哥舉一個(gè)例子來(lái)說(shuō)明這個(gè)問(wèn)題。
在我第一版的 vhr 中，當(dāng)時(shí)有一個(gè)系統(tǒng)通知的功能，就是管理員可以給所有的用戶(hù)群發(fā)消息。用戶(hù)之間也可以互發(fā)消息，如果發(fā)送消息的時(shí)候，用戶(hù)不在線(xiàn)，就需要先把消息存到數(shù)據(jù)庫(kù)中，等用戶(hù)上線(xiàn)了再推給用戶(hù)，那么就需要一張表來(lái)保存消息。這個(gè)表中有一個(gè)字段就是消息發(fā)送者，由于網(wǎng)站經(jīng)常需要發(fā)送通知，就導(dǎo)致這個(gè)字段的值分布非常不均，大約有 50% 的值都是 admin，剩下的 50% 則是其他普通用戶(hù)，那么查詢(xún)的時(shí)候，據(jù)此字段建立的聯(lián)合索引，如果查詢(xún)條件不是 admin，則過(guò)濾效果不錯(cuò)，如果查詢(xún)條件是 admin，則過(guò)濾效果就非常差。對(duì)于這樣的問(wèn)題，我們就需要從業(yè)務(wù)上去解決，例如禁止根據(jù) admin 去查詢(xún)等等?？傊?，建立聯(lián)合索引時(shí)，我們前面所所說(shuō)的字段選擇性最高的原則，并不是放之四海而皆準(zhǔn)的，小伙伴們還是要具體情況具體分析。
3. 注意事項(xiàng)
由于聯(lián)合索引也是存儲(chǔ)在 B+Tree 中，如 1.1 小節(jié)圖示，username 在整棵 B+Tree 中是有序的，但是從整體上來(lái)看，age 是無(wú)序的，所以對(duì)于聯(lián)合索引在搜索的時(shí)候，需要滿(mǎn)足最做匹配原則才是有效的，否則會(huì)失效。舉例來(lái)說(shuō)，如果查詢(xún)條件里只有 age，則索引就會(huì)失效，因?yàn)轫樦饕?B+Tree 去查詢(xún)滿(mǎn)足條件的記錄，得一個(gè)一個(gè)找，還不如直接遍歷主鍵索引。
當(dāng)前名稱(chēng)：聯(lián)合索引該如何選擇合適的列？
文章路徑：http://m.5511xx.com/article/coeghoh.html
