【MySQL筆記】MySQL使用索引的情境及真正利用索引的SQL類型

標籤：mysql

本筆記主要記錄MySQL索引方面的內容，以便為實際工作中SQL最佳化提供參考。

1. 為什麼使用索引

在無索引的情況下，MySQL會掃描整張表來尋找符合sql條件的記錄，其時間開銷與表中資料量呈正相關。對關係型資料表中的某些欄位建索引可以極大提高查詢速度（當然，不同欄位是否selective會導致這些欄位建立的索引對查詢速度的提升幅度不同，而且索引也並非越多越好，因為寫入或刪除時需要更新索引資訊）。

對於MySQL的Innodb儲存引擎來說，大部分類型的index均以B-Tree資料結構的變種B+Tree來儲存（MEMORY類型的表還支援hash類型的索引）。B-Tree是資料庫或檔案系統中常用的一種資料結構，它是一種N叉平衡樹，這種樹結構保證了同層節點儲存的key有序，對於某個節點來說，其左子樹儲存的所有key均小於該節點儲存的key，其右子樹儲存的所有key均大於該節點儲存的key。此外，在工程實現上，還結合操作系統的局部性原理做了很多最佳化，總之，b-tree的各種特性或最佳化技巧能保證：1) 查詢磁碟記錄時，讀盤次數最少；2) 任何insert和delete操作對樹結構的影響均很小；3) 樹本身的rebalance操作很高效。

本文下面的內容主要介紹關於MySQL索引的常見情境，未對B-tree/B+Tree的資料結構做深入的原理性介紹，感興趣的話，可參考wikipedia上的相關條目（B-tree或B+ tree），或者參考"MySQL索引背後的資料結構及演算法原理"或"Understanding B+tree Indexes and how they Impact Performance"  這兩篇文章，本筆記不再贅述。

2. MySQL使用索引的情境

MySQL在以下操作環境下會使用索引：
1) 快速尋找符合where條件的記錄
2) 快速確定候選集。若where條件使用了多個索引欄位，則MySQL會優先使用能使候選記錄集規模最小的那個索引，以便儘快淘汰不合格記錄。 
3) 如果表中存在幾個欄位構成的聯合索引，則尋找記錄時，這個聯合索引的最左首碼匹配欄位也會被自動作為索引來加速尋找。
例如，若為某表建立了3個欄位(c1, c2, c3)構成的聯合索引，則(c1), (c1, c2), (c1, c2, c3)均會作為索引，(c2, c3)就不會被作為索引，而(c1, c3)其實只利用到c1索引。
4) 多表做join操作時會使用索引（如果參與join的欄位在這些表中均建立了索引的話）
5) 若某欄位已建立索引，求該欄位的min()或max()時，MySQL會使用索引
6) 對建立了索引的欄位做sort或group操作時，MySQL會使用索引

3. 哪些SQL語句會真正利用索引
從MySQL官網文檔"Comparison of B-Tree and Hash Indexes"可知，下面這些類型的SQL可能會真正用到索引：

1) B-Tree可被用於sql中對列做比較的運算式，如=, >, >=, <, <=及between操作

2) 若like語句的條件是不以萬用字元開頭的常量串，MySQL也會使用索引
比如，SELECT * FROM tbl_name WHERE key_col LIKE ‘Patrick%‘或SELECT * FROM tbl_name WHERE key_col LIKE ‘Pat%_ck%‘可以利用索引，而SELECT * FROM tbl_name WHERE key_col LIKE ‘%Patrick%‘（以萬用字元開頭）和SELECT * FROM tbl_name WHERE key_col LIKE other_col（like條件不是常量串）無法利用索引。
對於形如LIKE ‘%string%‘的sql語句，若萬用字元後面的string長度大於3，則MySQL會利用Turbo Boyer-Moore algorithm演算法進行尋找。

3) 若已對名為col_name的列建了索引，則形如"col_name is null"的SQL會用到索引

4) 對於聯合索引，sql條件中的最左首碼匹配欄位會用到索引，樣本請參考本文第2節第3條對聯合索引的說明

5) 若sql語句中的where條件不只1個條件，則MySQL會進行Index Merge最佳化來縮小候選集範圍，具體的最佳化策略可以參考MySQL文檔Index Merge Optimization 

【參考資料】
1. MySQL索引背後的資料結構及演算法原理
2. Understanding B+tree Indexes and how they Impact Performance  
3. How MySQL Uses Indexes  
4. Comparison of B-Tree and Hash Indexes  
5. Index Merge Optimization  

==================== EOF =================

【MySQL筆記】MySQL使用索引的情境及真正利用索引的SQL類型