怎樣正確創(chuàng)建MySQL索引的方法詳解
索引類似大學(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)不存儲(chǔ)真實(shí)的數(shù)據(jù)，只存儲(chǔ)指引搜索方向的數(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í)間提升是巨大的。
數(shù)據(jù)表索引可以提高數(shù)據(jù)的檢索效率，也可以降低數(shù)據(jù)庫的IO成本，并且索引還可以降低數(shù)據(jù)庫的排序成本。排序分組操作主要消耗的就是CPU資源和內(nèi)存，所以能夠在排序分組操作中好好的利用索引將會(huì)極大地降低CPU資源的消耗。下面我們將簡單的分析一下怎樣正確創(chuàng)建MySQL數(shù)據(jù)索引。
怎樣判斷是否需要?jiǎng)?chuàng)建索引？
1、某些字段需要頻繁用作查詢條件時(shí)需要為它建立索引
這個(gè)應(yīng)該都知道，什么樣才是頻繁呢？綜合分析你執(zhí)行的所有SQL語句。最好將他們一個(gè)個(gè)都列出來。然后分析，發(fā)現(xiàn)其中有些字段在大部分的SQL語句查詢時(shí)候都會(huì)用到，那么就果斷為他建立索引。
2、唯一性太差的字段不適合建立索引
什么是唯一性太差的字段？如狀態(tài)字段、類型字段。那些只存儲(chǔ)固定幾個(gè)值的字段，例如用戶登錄狀態(tài)、消息的status等。這個(gè)涉及到了索引掃描的特性。例如：通過索引查找鍵值為A和B的某些數(shù)據(jù)，通過A找到某條相符合的數(shù)據(jù)，這條數(shù)據(jù)在X頁上面，然后繼續(xù)掃描，又發(fā)現(xiàn)符合A的數(shù)據(jù)出現(xiàn)在了Y頁上面，那么存儲(chǔ)引擎就會(huì)丟棄X頁面的數(shù)據(jù)，然后存儲(chǔ)Y頁面上的數(shù)據(jù)，一直到查找完所有對應(yīng)A的數(shù)據(jù)，然后查找B字段，發(fā)現(xiàn)X頁面上面又有對應(yīng)B字段的數(shù)據(jù)，那么他就會(huì)再次掃描X頁面，等于X頁面就會(huì)被掃描2次甚至多次。以此類推，所以同一個(gè)數(shù)據(jù)頁可能會(huì)被多次重復(fù)的讀取，丟棄，在讀取，這無疑給存儲(chǔ)引擎極大地增加了IO的負(fù)擔(dān)。
3、更新太頻繁地字段不適合創(chuàng)建索引
當(dāng)你為某個(gè)字段創(chuàng)建索引時(shí)候，如果再次更新這個(gè)字段數(shù)據(jù)時(shí)，數(shù)據(jù)庫就會(huì)自動(dòng)更新他的索引，所以當(dāng)這個(gè)字段更新太頻繁地時(shí)候那么就會(huì)不斷的更新索引，性能的影響可想而知。大概被檢索幾十次才會(huì)更新一次的字段才比較符合建立索引的規(guī)范。而如果一個(gè)字段同一個(gè)時(shí)間段內(nèi)被更新多次，那么果斷不能為他建立索引。
4、不會(huì)出現(xiàn)在where條件中的字段不該建立索引
這個(gè)其實(shí)沒什么好說的，不會(huì)用作查詢條件的字段建立了索引也沒用。
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對腳本之家的支持。如果你想了解更多相關(guān)內(nèi)容請查看下面相關(guān)鏈接
相關(guān)文章
詳解Windows?Server?2012下安裝MYSQL5.7.24的問題
這篇文章主要介紹了Windows?Server?2012下安裝MYSQL5.7.24的詳細(xì)過程，本文通過圖文并茂實(shí)例代碼相結(jié)合給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-02-02
mysql百萬數(shù)據(jù)表加索引優(yōu)化的方法
在大數(shù)據(jù)時(shí)代,隨著數(shù)據(jù)量的快速增長,對數(shù)據(jù)庫的索引優(yōu)化變得尤為重要,本文主要介紹了mysql百萬數(shù)據(jù)表加索引優(yōu)化的方法,感興趣的可以了解一下2024-02-02
看到園子里總結(jié)的Mysql用法，我覺得沒有我的全面，我的從登錄到高級(jí)的存儲(chǔ)過程都涉及到，這部分是我平常不會(huì)或是出現(xiàn)問題都會(huì)拿來看，不過現(xiàn)在就和我一起來使用命令模式學(xué)習(xí)一下數(shù)據(jù)庫最基本的吧2012-05-05
在開始安裝前，先說明一下mysql-5.6.4與較低的版本在安裝上的區(qū)別，從mysql-5.5起，mysql源碼安裝開始使用cmake了，因此當(dāng)我們配置安裝目錄./configure --perfix=/.....的時(shí)候和以前的會(huì)有些區(qū)別，這點(diǎn)我們稍后會(huì)提到2013-06-06
這篇文章主要介紹了MySQL中RANK()的介紹和用法,通過RANK()函數(shù)，我們可以方便地為查詢結(jié)果進(jìn)行排序并為每個(gè)行分配排名，本文介紹了RANK()函數(shù)的概念和使用方法，并通過示例和輸出結(jié)果向讀者展示了具體的操作步驟和效果，感興趣的朋友一起看看吧2023-07-07
Windows中MySQL數(shù)據(jù)庫下載以及安裝教程(最最新版)
這篇文章主要給大家介紹了關(guān)于Windows中MySQL數(shù)據(jù)庫下載以及安裝的相關(guān)資料,很多朋友剛開始接觸mysql數(shù)據(jù)庫服務(wù)器,對安裝使用教程不太明白,這里給大家總結(jié)下,需要的朋友可以參考下2023-09-09
這篇文章主要介紹了詳解MySQL拼接函數(shù)CONCAT的使用心得,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
這篇文章主要為大家介紹了Mysql索引創(chuàng)建刪除及使用代價(jià)詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2022-05-05
這篇文章主要為大家詳細(xì)介紹了mysql 5.7.17 源碼編譯安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
mysql多行子查詢實(shí)戰(zhàn)案例(只包含不相關(guān)子查詢)
在MySQL中多行子查詢(也稱為 IN 子查詢)是指子查詢返回多行數(shù)據(jù),并且這些數(shù)據(jù)用于主查詢中的某個(gè)條件判斷,這篇文章主要介紹了mysql多行子查詢(只包含不相關(guān)子查詢)的相關(guān)資料,需要的朋友可以參考下2024-10-10