怎樣正確創(chuàng)建MySQL索引的方法詳解

索引類似大學圖書館建書目索引，可以提高數據檢索的效率，降低數據庫的IO成本。MySQL在300萬條記錄左右性能開始逐漸下降，雖然官方文檔說500~800w記錄，所以大數據量建立索引是非常有必要的。MySQL提供了Explain，用于顯示SQL執(zhí)行的詳細信息，可以進行索引的優(yōu)化。

什么是索引？

MySQL官方對索引的定義為：索引(Index)是幫助MySQL高效獲取數據的數據結構。我們可以簡單理解為：快速查找排好序的一種數據結構。Mysql索引主要有兩種結構：B+Tree索引和Hash索引。我們平常所說的索引，如果沒有特別指明，一般都是指B樹結構組織的索引(B+Tree索引)。索引如圖所示：

最外層淺藍色磁盤塊1里有數據17、35（深藍色）和指針P1、P2、P3（黃色）。P1指針表示小于17的磁盤塊，P2是在17-35之間，P3指向大于35的磁盤塊。真實數據存在于子葉節(jié)點也就是最底下的一層3、5、9、10、13……非葉子節(jié)點不存儲真實的數據，只存儲指引搜索方向的數據項，如17、35。

查找過程：例如搜索28數據項，首先加載磁盤塊1到內存中，發(fā)生一次I/O，用二分查找確定在P2指針。接著發(fā)現28在26和30之間，通過P2指針的地址加載磁盤塊3到內存，發(fā)生第二次I/O。用同樣的方式找到磁盤塊8，發(fā)生第三次I/O。

真實的情況是，上面3層的B+Tree可以表示上百萬的數據，上百萬的數據只發(fā)生了三次I/O而不是上百萬次I/O，時間提升是巨大的。

數據表索引可以提高數據的檢索效率，也可以降低數據庫的IO成本，并且索引還可以降低數據庫的排序成本。排序分組操作主要消耗的就是CPU資源和內存，所以能夠在排序分組操作中好好的利用索引將會極大地降低CPU資源的消耗。下面我們將簡單的分析一下怎樣正確創(chuàng)建MySQL數據索引。

怎樣判斷是否需要創(chuàng)建索引？

1、某些字段需要頻繁用作查詢條件時需要為它建立索引

這個應該都知道，什么樣才是頻繁呢？綜合分析你執(zhí)行的所有SQL語句。最好將他們一個個都列出來。然后分析，發(fā)現其中有些字段在大部分的SQL語句查詢時候都會用到，那么就果斷為他建立索引。

2、唯一性太差的字段不適合建立索引

什么是唯一性太差的字段？如狀態(tài)字段、類型字段。那些只存儲固定幾個值的字段，例如用戶登錄狀態(tài)、消息的status等。這個涉及到了索引掃描的特性。例如：通過索引查找鍵值為A和B的某些數據，通過A找到某條相符合的數據，這條數據在X頁上面，然后繼續(xù)掃描，又發(fā)現符合A的數據出現在了Y頁上面，那么存儲引擎就會丟棄X頁面的數據，然后存儲Y頁面上的數據，一直到查找完所有對應A的數據，然后查找B字段，發(fā)現X頁面上面又有對應B字段的數據，那么他就會再次掃描X頁面，等于X頁面就會被掃描2次甚至多次。以此類推，所以同一個數據頁可能會被多次重復的讀取，丟棄，在讀取，這無疑給存儲引擎極大地增加了IO的負擔。

3、更新太頻繁地字段不適合創(chuàng)建索引

當你為某個字段創(chuàng)建索引時候，如果再次更新這個字段數據時，數據庫就會自動更新他的索引，所以當這個字段更新太頻繁地時候那么就會不斷的更新索引，性能的影響可想而知。大概被檢索幾十次才會更新一次的字段才比較符合建立索引的規(guī)范。而如果一個字段同一個時間段內被更新多次，那么果斷不能為他建立索引。

4、不會出現在where條件中的字段不該建立索引

這個其實沒什么好說的，不會用作查詢條件的字段建立了索引也沒用。

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，謝謝大家對腳本之家的支持。如果你想了解更多相關內容請查看下面相關鏈接