索引類似大學(xué)圖書館建書目索引,可以提高數(shù)據(jù)檢索的效率,降低數(shù)據(jù)庫的IO成本。MySQL在300萬條記錄左右性能開始逐漸下降,雖然官方文檔說500~800w記錄,所以大數(shù)據(jù)量建立索引是非常有必要的。MySQL提供了Explain,用于顯示SQL執(zhí)行的詳細(xì)信息,可以進(jìn)行索引的優(yōu)化。
什么是索引?
MySQL官方對索引的定義為:索引(Index)是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。我們可以簡單理解為:快速查找排好序的一種數(shù)據(jù)結(jié)構(gòu)。Mysql索引主要有兩種結(jié)構(gòu):B+Tree索引和Hash索引。我們平常所說的索引,如果沒有特別指明,一般都是指B樹結(jié)構(gòu)組織的索引(B+Tree索引)。索引如圖所示:
最外層淺藍(lán)色磁盤塊1里有數(shù)據(jù)17、35(深藍(lán)色)和指針P1、P2、P3(黃色)。P1指針表示小于17的磁盤塊,P2是在17-35之間,P3指向大于35的磁盤塊。真實(shí)數(shù)據(jù)存在于子葉節(jié)點(diǎn)也就是最底下的一層3、5、9、10、13……非葉子節(jié)點(diǎn)不存儲真實(shí)的數(shù)據(jù),只存儲指引搜索方向的數(shù)據(jù)項(xiàng),如17、35。
查找過程:例如搜索28數(shù)據(jù)項(xiàng),首先加載磁盤塊1到內(nèi)存中,發(fā)生一次I/O,用二分查找確定在P2指針。接著發(fā)現(xiàn)28在26和30之間,通過P2指針的地址加載磁盤塊3到內(nèi)存,發(fā)生第二次I/O。用同樣的方式找到磁盤塊8,發(fā)生第三次I/O。
真實(shí)的情況是,上面3層的B+Tree可以表示上百萬的數(shù)據(jù),上百萬的數(shù)據(jù)只發(fā)生了三次I/O而不是上百萬次I/O,時間提升是巨大的。
數(shù)據(jù)表索引可以提高數(shù)據(jù)的檢索效率,也可以降低數(shù)據(jù)庫的IO成本,并且索引還可以降低數(shù)據(jù)庫的排序成本。排序分組操作主要消耗的就是CPU資源和內(nèi)存,所以能夠在排序分組操作中好好的利用索引將會極大地降低CPU資源的消耗。下面我們將簡單的分析一下怎樣正確創(chuàng)建MySQL數(shù)據(jù)索引。
怎樣判斷是否需要創(chuàng)建索引?
1、某些字段需要頻繁用作查詢條件時需要為它建立索引
這個應(yīng)該都知道,什么樣才是頻繁呢?綜合分析你執(zhí)行的所有SQL語句。最好將他們一個個都列出來。然后分析,發(fā)現(xiàn)其中有些字段在大部分的SQL語句查詢時候都會用到,那么就果斷為他建立索引。
2、唯一性太差的字段不適合建立索引
什么是唯一性太差的字段?如狀態(tài)字段、類型字段。那些只存儲固定幾個值的字段,例如用戶登錄狀態(tài)、消息的status等。這個涉及到了索引掃描的特性。例如:通過索引查找鍵值為A和B的某些數(shù)據(jù),通過A找到某條相符合的數(shù)據(jù),這條數(shù)據(jù)在X頁上面,然后繼續(xù)掃描,又發(fā)現(xiàn)符合A的數(shù)據(jù)出現(xiàn)在了Y頁上面,那么存儲引擎就會丟棄X頁面的數(shù)據(jù),然后存儲Y頁面上的數(shù)據(jù),一直到查找完所有對應(yīng)A的數(shù)據(jù),然后查找B字段,發(fā)現(xiàn)X頁面上面又有對應(yīng)B字段的數(shù)據(jù),那么他就會再次掃描X頁面,等于X頁面就會被掃描2次甚至多次。以此類推,所以同一個數(shù)據(jù)頁可能會被多次重復(fù)的讀取,丟棄,在讀取,這無疑給存儲引擎極大地增加了IO的負(fù)擔(dān)。
3、更新太頻繁地字段不適合創(chuàng)建索引
當(dāng)你為某個字段創(chuàng)建索引時候,如果再次更新這個字段數(shù)據(jù)時,數(shù)據(jù)庫就會自動更新他的索引,所以當(dāng)這個字段更新太頻繁地時候那么就會不斷的更新索引,性能的影響可想而知。大概被檢索幾十次才會更新一次的字段才比較符合建立索引的規(guī)范。而如果一個字段同一個時間段內(nèi)被更新多次,那么果斷不能為他建立索引。
4、不會出現(xiàn)在where條件中的字段不該建立索引
這個其實(shí)沒什么好說的,不會用作查詢條件的字段建立了索引也沒用。
總結(jié)
以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,謝謝大家對腳本之家的支持。如果你想了解更多相關(guān)內(nèi)容請查看下面相關(guān)鏈接
您可能感興趣的文章:- MySQL 索引的優(yōu)缺點(diǎn)以及創(chuàng)建索引的準(zhǔn)則
- MySQL字符串索引更合理的創(chuàng)建規(guī)則討論
- MySQL使用命令創(chuàng)建、刪除、查詢索引的介紹
- 圖文詳解MySQL中兩表關(guān)聯(lián)的連接表如何創(chuàng)建索引
- MySQL創(chuàng)建全文索引分享
- 詳解mysql索引總結(jié)----mysql索引類型以及創(chuàng)建
- MySQL查看、創(chuàng)建和刪除索引的方法
- mysql 添加索引 mysql 如何創(chuàng)建索引
- MySQL創(chuàng)建索引需要了解的