實(shí)際上，并沒有一個(gè)非常明確的定律可以清晰地定義什么字段應(yīng)該創(chuàng)建索引，什么字段不該創(chuàng)建索引。因?yàn)閼?yīng)用場景實(shí)在是太復(fù)雜，存在太多的差異。當(dāng)然，還是仍然能夠找到幾點(diǎn)基本的判定策略來幫助分析的。
1、較頻繁的作為查詢條件的字段應(yīng)該創(chuàng)建索引
提高數(shù)據(jù)查詢檢索的效率最有效的辦法就是減少需要訪問的數(shù)據(jù)量，我們知道索引正是減少通過索引鍵字段作為查詢條件的query的io量之最有效手段。所以一般來說應(yīng)該為較為頻繁的查詢條件字段創(chuàng)建索引。
2、唯一性太差的字段不適合單獨(dú)創(chuàng)建索引，即使頻繁作為查詢條件
唯一性太差的字段主要是指哪些呢？如狀態(tài)字段、類型字段等這些字段中存放的數(shù)據(jù)可能總共就是那么幾個(gè)或幾十個(gè)值重復(fù)使用，每個(gè)值都會(huì)存在于成千上萬或更多的記錄中。碎玉這類字段，完全沒有必要?jiǎng)?chuàng)建單獨(dú)的索引。因?yàn)榧词箘?chuàng)建了索引，mysql query optimizer大多數(shù)時(shí)候也不會(huì)去選擇使用，如果什么時(shí)候mysql query optimizer選擇了這種索引，那么非常遺憾地告訴你，這可能會(huì)帶來極大的性能問題。由于索引字段中每個(gè)值都含有大量的記錄，那么存儲(chǔ)引擎在根據(jù)索引訪問數(shù)據(jù)的時(shí)候會(huì)帶來大量的隨機(jī)io，甚至有些時(shí)候還會(huì)出現(xiàn)大量的重復(fù)io。
這主要是由于數(shù)據(jù)基于索引掃描的特點(diǎn)引起的。當(dāng)我們通過索引訪問表中數(shù)據(jù)時(shí)，mysql會(huì)按照索引鍵的鍵值順序來依序訪問。一般來說，每個(gè)數(shù)據(jù)頁中大都會(huì)存放多條記錄，但是這些記錄可能大多數(shù)都不會(huì)和你所使用的索引鍵的鍵值順序一致。
假如有以下場景，我們通過索引查找鍵值為a和b的某些數(shù)據(jù)。在通過a鍵值找到第一條滿足要求的記錄后，會(huì)讀取這條記錄所在的x數(shù)據(jù)頁，然后繼續(xù)往下查找索引，發(fā)現(xiàn)a鍵值所對應(yīng)的另外一條記錄也滿足要求，但是這條記錄不在x數(shù)據(jù)頁上，而在y數(shù)據(jù)頁上，這時(shí)候存儲(chǔ)引擎就會(huì)丟棄x數(shù)據(jù)頁，而讀取y數(shù)據(jù)頁。如此繼續(xù)一直到查找完a鍵值所對應(yīng)的所有記錄。然后輪到b鍵值了，這時(shí)發(fā)現(xiàn)正在查找的記錄又在x數(shù)據(jù)頁上，可之前讀取的x數(shù)據(jù)頁已經(jīng)被丟棄了，只能再次讀取x數(shù)據(jù)頁。這時(shí)候，實(shí)際上已經(jīng)重復(fù)讀取x數(shù)據(jù)頁兩次了。在繼續(xù)往后的查找中，可能還會(huì)出現(xiàn)一次又一次的重復(fù)讀取，這無疑給存儲(chǔ)引擎極大地增加了io訪問量。
不僅如此，如果一個(gè)鍵值對應(yīng)了太多的數(shù)據(jù)記錄，也就是說通過該鍵值會(huì)返回占整個(gè)表比例很大的記錄時(shí)，由于根據(jù)索引掃描產(chǎn)生的都是隨機(jī)io，其效率比進(jìn)行全表掃描的順序io效率低很多，即使不會(huì)出現(xiàn)重復(fù)io的讀取，同樣會(huì)造成整體io性能的下降。
很多比較有經(jīng)驗(yàn)的query調(diào)優(yōu)專家經(jīng)常說，當(dāng)一條query返回的數(shù)據(jù)超過了全表的15%%u65f6，就不應(yīng)該再使用索引掃描來完成這個(gè)query了。對于15%%u8fd9個(gè)數(shù)字我們并不能判斷是否很準(zhǔn)確，但是至少側(cè)面證明了唯一性太差的字段并不適合創(chuàng)建索引。
3、更新非常頻繁的字段不適合創(chuàng)建索引
索引中的字段被更新的時(shí)候，不僅要更新表中的數(shù)據(jù)，還要更新索引數(shù)據(jù)，以確保索引信息是準(zhǔn)確的。這個(gè)問題致使io訪問量較大增加，不僅僅影響了更新query的響應(yīng)時(shí)間，還影響了整個(gè)存儲(chǔ)系統(tǒng)的資源消耗，加大了整個(gè)存儲(chǔ)系統(tǒng)的負(fù)載。
當(dāng)然，并不是存在更新的字段就不適合創(chuàng)建索引，從判定策略的用語上也可以看出，是“非常頻繁”的字段。到底什么樣的更新頻率 應(yīng)該算是“非常頻繁”呢？每秒？每分鐘？還是每小時(shí)呢？說實(shí)話，還真難定義。很多時(shí)候是通過比較同一時(shí)間段內(nèi)被更新的次數(shù)和利用該字段作為條件的查詢次數(shù)來判斷的，如果通過該字段的查詢并不是很多，可能幾個(gè)小時(shí)或是更長才會(huì)執(zhí)行一次，更新反而比查詢更頻繁，那這樣的字段肯定不適合創(chuàng)建索引。反之，如果我們通過該字段的查詢比較頻繁，但更新并不是特別多，比如查詢幾十次或更多才可能會(huì)產(chǎn)生一次更新，那我個(gè)人覺得更新所帶來的附加成本也是可以接受的。
4、不會(huì)出現(xiàn)在where子句中的字段不該創(chuàng)建索引
關(guān)于這一點(diǎn)我想不用分析大家都知道了。