MySQL索引及查詢優化

jopen 9年前發布 | 12K 次閱讀 MySQL 數據庫服務器

1. MySQL索引的原理

1.1 索引目的

索引的目的在于提高查詢效率，可以類比字典，如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然后從下往下找到y字母，再找到剩下的sql。如果沒有索引，那么你可能需要把所有單詞看一遍才能找到你想要的，如果我想找到m開頭的單詞呢？或者ze開頭的單詞呢？

1.2 索引原理

除了詞典，生活中隨處可見索引的例子，如火車站的車次表、圖書的目錄等。它們的原理都是一樣的，通過不斷的縮小想要獲得數據的范圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是我們總是通過同一種查找方式來鎖定數據。
每個數據表都有一個主鍵（如果沒有主鍵，數據庫會將該表中的唯一索引當作主鍵使用？），MySQL會以主鍵的方式構造一棵樹，葉子節點存放該主鍵對應的整行數據。
MySQL索引及查詢優化
自己建立的索引，一般叫做輔助索引，輔助索引的樹，也自己節點存放了兩個東西，一個是索引自身的值，另外一個是索引對應主鍵的值。
MySQL索引及查詢優化

如果索引是聯合索引，比如UserID和AddTime索引的方式，索引葉子節點會存儲UserID和AddTime之間的配對+主鍵的配對數據。

1.3 索引的類型

1.3.1 B-樹索引

B-樹索引在生產環境更為廣泛，這里我只針對B-樹索引進行討論
B-樹索引是一個復雜的內容，可以參見B-tree。

1.3.2 Hash索引

哈希索引（Hash Index）建立在哈希表的基礎上，它只對使用了索引中的每一列的精確查找有用。對于每一行，存儲引擎計算出了被索引的哈希碼（Hash Code），它是一個較小的值，并且有可能和其他行的哈希碼不同。它把哈希碼保存在索引中，并且保存了一個指向哈希表中的每一行的指針。

在mysql中，只有memory存儲引擎支持顯式的哈希索引。

  • Hash 索引僅僅能滿足"=","IN"和"<=>"查詢，不能使用范圍查詢。

  • Hash 索引無法被用來避免數據的排序操作。

  • Hash 索引不能利用部分索引鍵查詢。

  • Hash 索引在任何時候都不能避免表掃描。

  • Hash 索引遇到大量Hash值相等的情況后性能并不一定就會比B-Tree索引高
    • </ul>

    1.3.3 空間索引（R-樹）索引

    主要用于GIS中空間數據的存儲，但是MySQL的空間索引支持并不好，現在多使用PostgreSQL。

    1.3.4 全文索引(Full-text)索引

    文本字段上的普通索引只能加快對出現在字段內容最前面的字符串(也就是字段內容開頭的字符)進行檢索操作。如果字段里存放的是由幾個、甚至是多個單 詞構成 的較大段文字，普通索引就沒什么作用了。這種檢索往往以LIKE %word%的形式出現，這對MySQL來說很復雜，如果需要處理的數據量很大，響應時間就會很長。

    這類場合正是全文索引(full-text index)可以大顯身手的地方。在生成這種類型的索引時，MySQL將把在文本中出現的所有單詞創建為一份清單，查詢操作將根據這份清單去檢索有關的數 據記錄。全文索引即可以隨數據表一同創建，也可以等日后有必要時再使用下面這條命令添加： 

    ALTER TABLE tablename ADD FULLTEXT(column1, column2)

    2. 索引的合理使用

    </tr> </tbody>

    </tr>

    </tr>

    </tr>

    </tr>

    </tr>

    </tr> </tbody> </table>

    下面我們的分析都會這個數據表為例。

    2.1 索引在查詢中的使用

    不使用索引

    MySQL索引及查詢優化

    現在剛建立的表上沒有任何索引，但是我們想通過UserID找出ArriveDate這個數據，SQL將會寫成如下方式： 

    select ArriveDate from TestSQL where UserID = 10;

    MySQL索引及查詢優化

    這個SQL沒有走索引，數據庫就會根據主鍵（ID）掃描全表，每拿到一條數據庫記錄就與where條件比對，如果符合條件則將這條記錄返回，重復直到全表掃描完畢。
    在大數據量的情況下，不使用索引進行查詢幾乎是不可行的。

    • 使用UserID作為索引
      • </ul>

      MySQL索引及查詢優化
      這時候看到where條件是使用了UserID索引的。這時候數據庫引擎會根據UserID到索引上找到ID，然后根據ID去查詢對應記錄，從而取出ArriveDate數據。
      現在我們將UserID的索引更換為UserID,ArriveDate的聯合索引。
      現在再來查詢：
      MySQL索引及查詢優化
      可以發現這個Extra里面也是使用了索引的，這就意味這個SQL是完全走了索引，數據庫引擎根據UserID找到對應的索引， 因為Select的字段是索引的一部分，所以找到索引之后不需要再讀取表記錄了。

      當一個查詢語句中使用設計到多個索引時，MySQL數據庫引擎會計算不同索引涉及到的行數大小，選取行數最小的索引作為實際執行時使用的索引，如：
      MySQL索引及查詢優化
      一次查詢同一張表，MySQL每次只會使用一個索引。

      2.2 索引在范圍查詢的使用

      范圍查詢主要是指查詢字段值在某個范圍內的記錄，表現在where條件中為>,<,between等關鍵字。如，我們使用如下SQL進行查詢： 

      select UserID,ArriveDate from TestSQL where UserID >0 and UserID <100 and ArriveDate = '2015-09-23 00:00:00';

      作為對比，我們使用另一種SQL查詢相同記錄： 

      select UserID,ArriveDate from TestSQL where UserID in(2,12) and ArriveDate = '2015-09-23 00:00:00';

      聯調SQL都會查詢出相同的記錄：

    字段名 數據類型 NULL INDEX
    ID int(10) NOT NULL pk
    UserID int(10) NOT NULL
    Mobile varchar(15) NOT NULL
    ArriveDate DateTime NOT NULL
    AddDate DateTime NOT NULL
    UpdateTime timetamp NOT NULL

    </tr> </tbody>

    </tr>

    </tr> </tbody> </table>

    第一個SQL（使用范圍查詢）的explain結果為：

    MySQL索引及查詢優化
    第二個SQL（未使用范圍查詢）的explain結果為：

    MySQL索引及查詢優化
    對 比可見，兩種sql的索引長度是不一樣的。在范圍查詢中，索引的使用是遵循最左（leftmost）原則，例如這個表的使用的索引是 IX_UserID_ArriveDate，但是因為UserID使用了范圍查詢(Range query)，就不再使用ArrvieDate的索引了。

    2.3 排序使用索引

    在排序中以下情況無法使用索引：

    • Order by 的字段并不是索引

    • 使用了兩種排序方向，但是索引都是使用升序排列的
      在索引的原理里我們講到過

      如果索引是聯合索引，比如UserID和AddTime索引的方式，索引葉子節點會存儲UserID和AddTime之間的配對+主鍵的配對數據。

      </blockquote>

      這種情況下，索引會以UserID進行排序，當UserID相同時再以AddTime進行排序（默認為升序），以我們現有的數據庫為例： 

      select UserID,ArriveDate from TestSQL where UserID in(2,3,4) order by UserID asc,ArriveDate desc;

      這種情況將只使用UserID 作為索引，而ArriveDate 將不再作為索引。

      </li>

    • 不符合最左（Left most）的條件 

       select * from TestSQL where ArriveDate = '2015-09-23 00:00:00' order by UserID desc

      這種情況將不再使用UserID_ArriveDate索引

      </li>

    • 聯合查詢的情況下，如果第一個篩選條件是范圍查詢，MySQL不再使用剩下的索引 

      select * from TestSQL where UserID >1 and UserID < 5 order by UserID desc,ArriveDate desc

      這種情況下，因為UserID 為范圍查詢，所以就不會再使用ArriveDate 索引了。

      </li> </ul>

      2.4 join 中使用索引

      我們來新建一個TestSQL_join表，其結構與TestSQL 相同，但只有Mobile索引。首先來看一個簡單的join操作 

      select * from TestSQL join TestSQL_join on TestSQL.id = TestSQL.id

      MySQL首先比較TestSQL和 TestSQL_join表那個行數少，如TestSQL中的記錄較少，TestSQL就是一個小表，而TestSQL_join則是大表，MySQL引 擎先把TestSQL中的ID全部去出來，然后根據id到TestSQL_join中查詢相關的記錄。

      MySQL索引及查詢優化

      在這里，TestSQL中行數決定了循環的次數，但是TestSQL_join則決定了每次循環查詢所需要查詢的時間；這時如果TestSQL_join中的ID是索引則會大大減少查詢時間如下SQL: 

      select * from TestSQL a join TestSQL_join b on a.ID = b.ID

      由于b.ID 是TestSQL_join的主鍵，查詢使用了TestSQL_join主鍵索引。
      MySQL索引及查詢優化
      如果此時對TestSQL增加條件篩選： 

       select * from TestSQL a join TestSQL_join b on a.ID = b.ID where a.UserID = 1;

      因為a.UserID 是TestSQL的索引，所以在過濾TestSQL表的行數時，采用次索引查詢對應ID，然后根據ID查詢TestSQL_join的記錄。

      MySQL索引及查詢優化
      如果此時針對TestSQL_join 增加where條件過濾： 

       select * from TestSQL a join TestSQL_join b on a.ID = b.ID where a.UserID >1 and b.Mobile = "2147483647"

      這種時候，因為TestSQL 和 TestSQL_join 根據where條件所篩選出來的行數大小可能會有變化，也就是說TestSQL_join 有可能會變成小表，這時候將會優先從TestSQL_join 查詢出相關ID，然后根據ID去查詢TestSQL。

      join操作時，大表小表的概念，主要是按照兩張表分別執行對應查詢條件，哪個開銷更小，哪個就是小表。

      join操作雖然在SQL層面很方便，而且在線上大流量的情況下，一旦SQL的join操作導致查詢緩慢，較難即使優化。另外在服務化的系統中，容易導致業務領域不清晰，所以在互聯網大流量的應用中是不推薦使用join操作的。

      3. 索引建立的原則

    UserID ArriveDate
    2 2015-09-23 00:00:00
    12 2015-09-23 00:00:00
sesese色