MySQL 效能優化之索引優化

大家都知道索引對於資料訪問的效能有非常關鍵的作用，都知道索引可以提高資料訪問效率。

為什麼索引能提高資料訪問效能？他會不會有“副作用”？是不是索引建立越多，效能就越好？到底該如何設計索引，才能最大限度的發揮其效能？

這篇文章主要是帶著上面這幾個問題來做一個簡要的分析，同時排除了業務場景所帶來的特殊性，請不要糾結業務場景的影響。

• 索引為什麼能提高資料訪問效能？
  很多人只知道索引能夠提高資料庫的效能，但並不是特別瞭解其原理，其實我們可以用一個生活中的示例來理解。

  
  

  我們讓一位不太懂計算機的朋友去圖書館確認一本叫做《MySQL效能調優與架構設計》的書是否在藏，這樣對他說：“請幫我借一本計算機類的資料庫書籍，是屬於 MySQL 資料庫範疇的，叫做《MySQL效能調優與架構設計》”。朋友會根據所屬類別，前往存放“計算機”書籍區域的書架，然後再尋找“資料庫”類存放位置，再找到一堆講述“MySQL”的書籍，最後可能發現目標在藏（也可能已經借出不在書架上）。

  在這個過程中： “計算機”->“資料庫”->“MySQL”->“在藏”->《MySQL效能調優與架構設計》其實就是一個“根據索引查詢資料”的典型案例，“計算機”->“資料庫”->“MySQL”->“在藏” 就是朋友查詢書籍的索引。

  假設沒有這個索引，那查詢這本書的過程會變成怎樣呢？朋友只能從圖書館入口一個書架一個書架的“遍歷”，直到找到《MySQL效能調優與架構設計》這本書為止。如果幸運，可能在第一個書架就找到。但如果不幸呢，那就慘了，可能要將整個圖書館所有的書架都找一遍才能找到我們想要的這本書。

  注：這個例子中的“索引”是記錄在朋友大腦中的，實際上，每個圖書館都會有一個非常全的實際存在的索引系統（大多位於入口顯眼處），由很多個貼上了明顯標籤的小抽屜構成。這個索引系統中存放這非常齊全詳盡的索引資料，標識出我們需要查詢的“目標”在某個區域的某個書架上。而且每當有新的書籍入庫，舊的書籍銷燬以及書記資訊修改，都需要對索引系統進行及時的修正。

下面我們通過上面這個生活中的小示例，來分析一下索引，看看能的出哪些結論？

• 索引有哪些“副作用”？
  1. 圖書的變更（增，刪，改）都需要修訂索引，索引存在額外的維護成本
  2. 查詢翻閱索引系統需要消耗時間，索引存在額外的訪問成本
  3. 這個索引系統需要一個地方來存放，索引存在額外的空間成本
• 索引是不是越多越好？
  1. 如果我們的這個圖書館只是一個進出中轉站，裡面的新書進來後很快就會轉發去其他圖書館而從這個館藏中“清除”，那我們的索引就只會不斷的修改，而很少會被用來查詢圖書
     所以，對於類似於這樣的存在非常大更新量的資料，索引的維護成本會非常高，如果其檢索需求很少，而且對檢索效率並沒有非常高的要求的時候，我們並不建議建立索引，或者是儘量減少索引。
  2. 如果我們的書籍量少到只有幾本或者就只有一個書架，索引並不會帶來什麼作用，甚至可能還會浪費一些查詢索引所花費的時間。
     所以，對於資料量極小到通過索引檢索還不如直接遍歷來得快的資料，也並不適合使用索引。
  3. 如果我們的圖書館只有一個10平方的面積，現在連放書架都已經非常擁擠，而且館藏還在不斷增加，我們還能考慮建立索引嗎？
     所以，當我們連儲存基礎資料的空間都捉襟見肘的時候，我們也應該儘量減少低效或者是去除索引。
• 索引該如何設計才高效？
  1. 如果我們僅僅只是這樣告訴對方的：“幫我確認一本資料庫類別的講述 MySQL 的叫做《MySQL效能調優與架構設計》的書是否在藏”，結果又會如何呢？朋友只能一個大類區域一個大類區域的去尋找“資料庫”類別，然後再找到 “MySQL”範疇，再看到我們所需是否在藏。由於我們少說了一個“計算機類”，朋友就必須到每一個大類去尋找。
     所以，我們應該儘量讓查詢條件儘可能多的在索引中，儘可能通過索引完成所有過濾，回表只是取出額外的資料欄位。
  2. 如果我們是這樣說的：“幫我確認一本講述 MySQL 的資料庫範疇的計算機叢書，叫做《MySQL效能調優與架構設計》，看是否在藏”。如果這位朋友並不知道計算機是一個大類，也不知道資料庫屬於計算機大類，那這位朋友就悲劇了。首先他得遍歷每個類別確認“MySQL”存在於哪些類別中，然後從包含 “MySQL” 書籍中再看有哪些是“資料庫”範疇的（有可能部分是講述PHP或者其他開發語言的），然後再排除非計算機類的（雖然可能並沒有必要），然後才能確認。
     所以，欄位的順序對組合索引效率有至關重要的作用，過濾效果越好的欄位需要更靠前。
  3. 如果我們還有這樣一個需求（雖然基本不可能）：“幫我將圖書館中所有的計算機圖書借來”。朋友如果通過索引來找，每次都到索引櫃找到計算機書籍所在的區域，然後從書架上搬下一格（假設只能以一格為單位從書架上取下，類比資料庫中以block/page為單位讀取），取出第一本，然後再從索引櫃找到計算機圖書所在區域，再搬下一格，取出一本… 如此往復直至取完所有的書。如果他不通過索引來找又會怎樣呢？他需要從地一個書架一直往後找，當找到計算機的書，搬下一格，取出所有計算機的書，再往後，直至所有書架全部看一遍。在這個過程中，如果計算機類書籍較多，通過索引來取所花費的時間很可能要大於直接遍歷，因為不斷往復的索引翻閱所消耗的時間會非常長。（延伸閱讀：這裡有一篇以前寫的關於Oracle的文章，索引掃描還是全表掃描（Index Scan Or Full Table Scan））
     所以，當我們需要讀取的資料量佔整個資料量的比例較大抑或者說索引的過濾效果並不是太好的時候，使用索引並不一定優於全表掃描。
  4. 如果我們的朋友不知道“資料庫”這個類別可以屬於“計算機”這個大類，抑或者圖書館的索引系統中這兩個類別屬性並沒有關聯關係，又會怎樣呢？也就是說，朋友得到的是2個獨立的索引，一個是告知“計算機”這個大類所在的區域，一個是“資料庫”這個小類所在的區域（很可能是多個區域），那麼他只能二者選其一來搜尋我的需求。即使朋友可以分別通過2個索引檢索然後自己在腦中取交集再找，那這樣的效率實際過程中也會比較低下。
     所以，在實際使用過程中，一次資料訪問一般只能利用到1個索引，這一點在索引建立過程中一定要注意，不是說一條SQL語句中Where子句裡面每個條件都有索引能對應上就可以了。

看完這些分析，我想大家應該瞭解索引優化的一些基本思路了吧 

作者：Sky.Jian | 可以任意轉載, 但轉載時務必以超連結形式標明文章原始出處 和 作者資訊 及 版權宣告 
連結：http://isky000.com/database/mysql-performance-tuning-index 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1481471/，如需轉載，請註明出處，否則將追究法律責任。