MySQL索引的坑怎么處理

2024-07-24 12:33:41

字體：大中小

供稿：網(wǎng)友

　　索引可以說是數(shù)據(jù)庫(kù)中的一個(gè)大心臟了，如果說一個(gè)數(shù)據(jù)庫(kù)少了索引，那么數(shù)據(jù)庫(kù)本身存在的意義就不大了，和普通的文件沒什么兩樣。所以說一個(gè)好的索引對(duì)數(shù)據(jù)庫(kù)系統(tǒng)尤其重要，今天來說說MySQL索引，從細(xì)節(jié)和實(shí)際業(yè)務(wù)的角度看看在MySQL中B+樹索引好處，以及我們?cè)谑褂盟饕龝r(shí)需要注意的知識(shí)點(diǎn)。

　　「1.普通索引的弊端」
　　現(xiàn)在有個(gè)需求需要根據(jù)用戶的身份證號(hào)找到用戶的姓名，這時(shí)候很顯然想到的第一個(gè)辦法就是在id_card上建立一個(gè)索引，嚴(yán)格來說是唯一索引，因?yàn)樯矸葑C號(hào)肯定是唯一的，那么當(dāng)我們執(zhí)行以下查詢的時(shí)候：

　　SELECT name FROM user WHERE id_card=xxx
　　它的流程應(yīng)該是這樣的：

　　先在id_card索引樹上搜索，找到id_card對(duì)應(yīng)的主鍵id
　　通過id去主鍵索引上搜索，找到對(duì)應(yīng)的name
　　從效果上來看，結(jié)果是沒問題的，但是從效率上來看，似乎這個(gè)查詢有點(diǎn)昂貴，因?yàn)樗鼨z索了兩顆B+樹，假設(shè)一顆樹的高度是3，那么兩顆樹的高度就是6，因?yàn)楦?jié)點(diǎn)在內(nèi)存里（此處兩個(gè)根節(jié)點(diǎn)），所以最終要在磁盤上進(jìn)行IO的次數(shù)是4次，以一次磁盤隨機(jī)IO的時(shí)間平均耗時(shí)是10ms來說，那么最終就需要40ms。這個(gè)數(shù)字一般，不算快。

　　MySQL索引的坑怎么解決

　　「2.主鍵索引的陷阱」
　　既然問題是回表，造成了在兩顆樹都檢索了，那么核心問題就是看看能不能只在一顆樹上檢索。這里從業(yè)務(wù)的角度你可能發(fā)現(xiàn)了一個(gè)切入點(diǎn)，身份證號(hào)是唯一的，那么我們的主鍵是不是可以不用默認(rèn)的自增id了，我們把主鍵設(shè)置成我們的身份證號(hào)，這樣整個(gè)表的只需要一個(gè)索引，并且通過身份證號(hào)可以查到所有需要的數(shù)據(jù)包括我們的姓名，簡(jiǎn)單一想似乎有道理，只要每次插入數(shù)據(jù)的時(shí)候，指定id是身份證號(hào)就行了，但是仔細(xì)一想似乎有問題。

　　這里要從B+樹的特點(diǎn)來說，B+樹的數(shù)據(jù)都存在葉子節(jié)點(diǎn)上，并數(shù)據(jù)是頁(yè)式管理的，一頁(yè)是16K，這是什么意思呢？哪怕我們現(xiàn)在是一行數(shù)據(jù)，它也要占用16K的數(shù)據(jù)頁(yè)，只有當(dāng)我們的數(shù)據(jù)頁(yè)寫滿了之后才會(huì)寫到一個(gè)新的數(shù)據(jù)頁(yè)上，新的數(shù)據(jù)頁(yè)和老的數(shù)據(jù)頁(yè)在物理上不一定是連續(xù)的，而且有一點(diǎn)很關(guān)鍵，雖然數(shù)據(jù)頁(yè)物理上是不連續(xù)的，但是數(shù)據(jù)在邏輯上是連續(xù)的。

　　總結(jié)來說，不連續(xù)的身份證號(hào)當(dāng)主鍵可能會(huì)造成頁(yè)數(shù)據(jù)的移動(dòng)、隨機(jī)IO、頻繁申請(qǐng)新頁(yè)相關(guān)的開銷。如果我們用的是自增的主鍵，那么對(duì)于id來說一定是順序的，不會(huì)因?yàn)殡S機(jī)IO造成數(shù)據(jù)移動(dòng)的問題，在插入方面開銷一定是相對(duì)較小的。

　　其實(shí)不推薦用身份證號(hào)當(dāng)主鍵的還有另外一個(gè)原因：身份證號(hào)作為數(shù)字來說太大了，得用bigint來存，正常來說一個(gè)學(xué)校的學(xué)生用int已經(jīng)足夠了，我們知道一頁(yè)可以存放16K，當(dāng)一個(gè)索引本身占用的空間越大時(shí)，會(huì)導(dǎo)致一頁(yè)能存放的數(shù)據(jù)越少，所以在一定數(shù)據(jù)量的情況下，使用bigint要比int需要更多的頁(yè)也就是更多的存儲(chǔ)空間。

（編輯：武林網(wǎng)）

上一篇：MySQL持久化和回滾該怎么領(lǐng)會(huì)

下一篇：MYSQL中鎖的模式與類別有哪些