本文主要基於《高效能MySQL》，另外參考了網路上的部落格

一、B-Tree索引

索引的建立和使用都是在儲存引擎層，InnoDB使用的是B+Tree，屬於B-Tree的變種，關於B-Tree和B+Tree本文不做介紹，大家可以檢視相關資料。

通過對B-Tree資料結構分析，可以知道InnoDB支援以下型別查詢使用索引：

1. 全值匹配，這裡的全值指的是和索引中所有列進行匹配；
2. 索引中列最左字首匹配；
3. 列字首匹配；
4. 範圍匹配；
5. 精確匹配某一列並範圍匹配另外一列，比如索引有A、B、C三列，查詢要求A列等於某值，B列使用範圍，此時索引可以使用A、B列快速定位，如果查詢條件中還有C列，因為B列使用了範圍導致C列無法利用索引；
6. 只訪問索引的查詢；
7. IN裡面放的是具體的值，相當於多個等值條件查詢，也是可以使用索引的，IN相當於精確匹配。

在InnoDB中，B+Tree的葉子節點存放的是主鍵，而不是指向行記錄的指標。B-Tree索引的優點：

1. 索引可以加快排序和group by操作；
2. 如果查詢需要的資料索引就能滿足，那麼可以避免查表，大大減少伺服器需要掃描的資料量；
3. 因為索引是有序的，相關的列值儲存在一起，所以查詢時索引可以將隨機IO變為順序IO。

B-Tree索引是按照順序排列資料的，所以如果語句有order by子句，恰好order by的順序和索引完全一致，那麼mysql便有可能使用索引做排序。如果查詢需要多表關聯，則只有當order by子句引用的欄位全部為第一個表時，才能使用索引做排序。order by子句使用索引做排序也需要滿足索引最左字首的要求。order by可以使用索引的場景和查詢使用索引的場景基本是一致的。
下面介紹一下什麼是冗餘索引（基於B-Tree）。如果有索引(A,B)，那麼此時再建立索引(A)，後者建立的索引就是冗餘索引，因為能使用索引(A)的查詢，也可以使用索引(A,B)滿足要求，完全沒有必要建立索引(A)。另外建立索引(A,ID)也是冗餘的，因為二級索引中已經包含了主鍵ID。不過也有例外，如果一個索引非常大，佔用空間特別多，出於效能考慮建立冗餘索引也是可以的。

二、雜湊索引

除了B-Tree索引，Memory儲存引擎還支援雜湊索引。雜湊索引基於雜湊表實現，它要求精確匹配索引，Memory儲存引擎實現的非唯一雜湊索引，如果雜湊值相同，索引會以連結串列的方式存放多個記錄指標到同一個雜湊條目中。通過對雜湊演算法的瞭解，可以知道雜湊索引的速度非常快。但是雜湊索引也有一些缺點：

1. 雜湊索引只包含雜湊值和行指標，不儲存欄位值，所以不能使用索引中的值來避免讀取表；
2. 雜湊索引無法用於排序；
3. 雜湊索引不支援部分索引列的匹配查詢；
4. 雜湊索引不支援範圍查詢，只支援等值查詢，比如=，in；
5. 當雜湊衝突非常多的時候，會降低雜湊索引速度；
6. 如果雜湊衝突非常多，那麼索引維護操作的代價也會非常高，比如從表中刪除一行資料，接下來需要遍歷對應雜湊值的連結串列中的每一行，找到並刪除，如果衝突非常多，那麼遍歷所需要的時間就會越長。

三、自適應雜湊索引

InnoDB提供了一種基於雜湊演算法的自適應雜湊索引的功能。當InnoDB發現某些索引值使用的非常頻繁，它便會在記憶體中基於B-Tree索引再建立一個自適應雜湊索引。自適應雜湊索引是完全基於記憶體的，MySQL可以在為B+樹定義的任意長度的索引字首上構建雜湊索引，具體取決於針對索引的搜尋模式。雜湊索引可以是部分的，整個B樹索引不需要快取在緩衝池中。使用雜湊索引可以大大加快=或者in操作的速度。可以通過引數innodb_adaptive_hash_index啟用或者關閉自適應雜湊索引，預設是開啟的。自適應雜湊索引是InnoDB的內部行為，完全自動的，有資料介紹當達到以下條件時便會建立自適應雜湊索引：

1. 對這個頁的連續訪問模式必須是一致的，也就是查詢的條件必須是一致的。
   這裡以一個聯合索引作為例子講解:
   現在存在表: tableA,對錶中的兩個column建立了聯合索引(a,b)
   那麼有兩種情況可以觸發這個索引:
   select a from tableA where a = xxx;
   select a,b from tableA where a = xxx and b = xxx;
   這兩個查詢都遵循了最左匹配原則，但是他們用到的索引是不一樣的，第一個查詢用到了a這個索引，第二個查詢才完美地覆蓋了(a,b)這個聯合索引。InnoDB不會對這種交替查詢的索引建立自適應雜湊索引。也就是說，必須得保證N次請求，查詢的條件都是一致的。
2. 以同一查詢條件進行了100次以上的訪問或者索引頁通過該模式訪問了N次，其中N=頁中記錄 /16。

自適應雜湊索引也需要維護，所以它也會帶來一定的效能消耗。有時，訪問時保護自適應雜湊索引的read/write lock會帶來非常嚴重的效能消耗，例如高併發的joins。查詢使用LIKE操作和% 萬用字元也往往不會受益於AHI。對於不需要自適應雜湊索引的工作負載，將其關閉可減少不必要的效能開銷。由於很難預先預測此功能是否適用於特定系統，因此請考慮使用實際工作負載執行啟用和禁用的基準測試。
索引儘管有很多好處，但不是說建立索引就一定能帶來效能提升。插入和更新都需要維護索引，索引越多，維護成本也就越高，甚至可能會降低效能。
在InnoDB中1個表最多隻能建立64個2級索引， 加上主鍵，就是65個，複合索引最多隻能16列。
這裡還要注意一點，InnoDB鎖是基於索引加的。

四、如何高效使用索引

1. 索引欄位在查詢語句中不能作為表示式的一部分，也不能作為函式的入參，否則mysql無法使用索引；
2. 如果需要索引很長的字串列，最好建立列字首索引，使用列字首索引可以大大減少索引空間，從而提高索引效率，但這樣做的前提是列字首具有很高區分度，也就是說具有相同列字首的資料越少越好，可以通過不斷增加需要索引的字首長度來滿足需求，一個簡單的辦法是計算列字首的選擇性，選擇性指的是不重複索引值和資料表記錄總數的比值，索引的選擇性越高查詢效率越高，下面是一個建立列字首索引的例子：alter table test add key(address(10))，這個SQL語句根據address欄位的前10個字元建立了列字首索引。列字首索引的缺點是無法使用字首索引做排序和group by，也無法使用覆蓋掃描（經過驗證）。
3. 5.0及以後版本的mysql在一次查詢中可以使用多個索引，這叫做索引合併，如果執行explain，發現extra列顯示了多個索引，這意味著查詢需要做索引合併，儘管mysql可以同時使用多個索引定位資料，但是這對資料庫來說執行是非常低效的，當出現這種情況時，意味著我們需要優化索引或者修改查詢語句。我們也可以通過引數optimizer_switch來關閉索引合併功能(optimizer_switch是一個標誌位的集合，其中一個標誌位控制索引合併開關)。
4. 當不考慮排序和分組時，為了獲得較好的查詢效能，通常將選擇性最高的列放在索引的最左邊。但是查詢效能也不完全依賴於索引列的選擇性，也和查詢條件的具體值有關，也就是和值的分佈有關，最好是根據那些執行頻率最高的查詢來調整索引列的順序。

五、InnoDB聚簇索引

聚簇索引是一類特殊的索引，是一種對磁碟上實際資料重新組織以按指定的一個或多個列的值排序。由於聚簇索引的索引頁面指標指向資料頁面，所以使用聚簇索引查詢資料幾乎總是比使用非聚簇索引快。每張表只能建一個聚簇索引。InnoDB的主鍵索引便是聚簇索引，行資料儲存在B-Tree的葉子節點，B-Tree的非葉子節點儲存的是主鍵值。這裡說一點非聚簇索引和聚簇索引的區別，聚簇索引的葉子節點是行資料，非聚簇索引的葉子節點是主鍵值，所以使用非聚簇索引查詢資料行時需要檢索兩次索引。
如果InnoDB的表沒有定義主鍵，那麼mysql使用一個唯一的非空索引作為聚簇索引，如果這樣的索引也沒有，那麼InnoDB會隱式的定義一個主鍵作為聚簇索引。
聚簇索引將相關資料聚集在一起，查詢相關資料時可以只從磁碟讀取少數頁就可以獲得全部所需資料，而且資料行儲存在葉子節點，相對於非聚簇索引，資料訪問速度更快。從這裡可以看到聚簇索引可以大大加快查詢速度，但是其也有一些缺點：

1. 如果資料表比較小，可以全部放在記憶體，那麼聚簇索引也就沒有什麼優勢了；
2. 插入速度嚴重依賴於插入順序，按照主鍵順序插入是載入資料到InnoDB表中速度最快的方式，如果不是，那麼在載入完後最好使用optimize table命令重新組織一下表；
3. 更新聚簇索引的代價很高，因為InnoDB會強制將每個被更新的行移動到新的位置，這可能導致頁分裂，頁分裂會降低插入和更新速度，同時也會導致表佔用更多的空間；
4. 如果插入的資料不是順序的，這可能會出現要寫入的目標頁不再快取中，那麼還需要重新載入目標頁到記憶體，這會導致大量的隨機IO，更新也是一樣；
5. 如果由於頁分裂導致很多資料頁不連續，那麼全表掃描的速度也會變慢；
6. 如果主鍵很大，比如使用很長的字串作為主鍵，那麼會導致二級索引也很大，因為二級索引的葉子節點是主鍵值。
   由於聚簇索引按照順序在磁碟上組織，所以對於主鍵，最好使用一個與業務無關且順序遞增的整數列。

InnoDB資料頁的最大填充因子是15/16，剩餘的空間用作更新操作，這樣可以避免也分裂。

六、覆蓋索引

覆蓋索引指的是如果一個索引包含所有需要查詢的欄位的值，那麼該索引就稱為覆蓋索引。使用覆蓋索引查詢時，無需再回表查詢資料。這將帶來如下好處：

1. 索引的條目遠遠小於資料行的大小，甚至索引可以完全放在記憶體中，這可以大大加快查詢速度；
2. 索引是按照順序存放的，對於範圍查詢、排序和分組具有非常好的優勢；
3. 如果InnoDB的二級索引是覆蓋索引，可以減少一次回表查詢。

覆蓋索引需要儲存被索引列的值，所以對於InnoDB來說，只有B-Tree索引可以做覆蓋索引（自適應雜湊索引儲存的是指向資料行的指標）。
判斷一個查詢是否使用了覆蓋索引，可以檢視Explain的extra列。如果是覆蓋索引，extra列會顯示“Using index”。
mysql的查詢優化器在執行前會判斷查詢語句中涉及的所有欄位是否在一個索引中都有，如果有那麼便使用該覆蓋索引做查詢。

七、索引下推

詳細內容可以參見網站：http://fivezh.github.io/2020/01/19/mysql-icp/
索引下推英文縮寫是ICP，其作用簡單的說是可以通過索引過濾一部分不符合where條件的資料。索引下推是5.6版本才加入的，以前必須通過獲取表資料才能過濾。下面通過一個例子介紹ICP。比如如下SQL：

INDEX (zipcode, lastname, firstname)
SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

在zipcode，lastname ，address上建立了一個聯合索引。在5.6以前的版本里面，根據最左匹配原則，儲存引擎使用索引過濾出所有zipcode='95054’的主鍵，然後回表查詢出記錄行返回給伺服器層，之後伺服器層根據後面兩個條件過濾記錄行。索引上已經包含了lastname和address，但是這兩個條件並沒有通過索引過濾，反而回表查詢，引發了大量的隨機讀，還造成了伺服器層和儲存引擎層大量的資料互動。那麼是否可以通過索引對lastname和address進行過濾呢？從5.6版本開始引入了ICP使得使用索引過濾成為了現實。查詢開始時，伺服器層將上面SQL語句中的三個where條件傳送給儲存引擎，儲存引擎使用索引對這三個條件進行過濾。
從例子中可以看出，ICP減少了回表操作，隨機讀也隨之減少，也降低了伺服器層和儲存引擎之間互動的資料量。ICP還有以下注意點：

• ICP 適用於range, ref, eq_ref, and ref_or_null的回表操作前過濾資料
• 支援InnoDB和MyISAM引擎
• InnoDB中 ICP 僅支援二級索引，不支援聚簇索引。因InnoDB引擎下，聚簇索引的欄位資訊已全部在索引中。
• 子查詢條件無法下推。
• 函式或觸發器無法利用 ICP
• 當使用ICP優化時，執行計劃的Extra列顯示Using indexcondition提示

八、索引和表維護

由於資料的插入、刪除和更新導致索引和表產生碎片，也會造成索引統計資訊的不準確，這都會帶來效能問題，所以需要維護表和索引。

（1）表或索引損壞

系統發生崩潰或者硬體問題等原因會導致表或者索引損壞，表損壞會導致系統無法工作，索引損壞會導致結果返回錯誤，嚴重會導致系統崩潰。
可以通過執行check table來檢查是否發生了表或索引損壞，有些資料介紹修復使用命令repair table，InnoDB是不支援repair table命令的，mysql會返回如下錯誤資訊：

The storage engine for the table doesn't support repair

修復InnoDB表可以先將資料匯出一份然後重新建表重新匯入，也可以使用alter table 表名 engine=INNODB。

（2）索引統計資訊

查詢優化器根據索引統計資訊制定執行計劃，如果索引統計資訊是錯誤的，那麼執行計劃也是錯誤的。這裡的索引統計資訊包括了值分佈情況、某個範圍內資料條數等資訊。
InnoDB不在磁碟儲存索引統計資訊，是通過隨機索引訪問少量索引頁進行評估並將其儲存在記憶體中。所以InnoDB的索引統計資訊是估算值，可以通過引數innodb_stats_sample_pages設定取樣的資料頁數量。
當發生以下場景時，InnoDB會更新索引統計資訊：

1. 表首次開啟；
2. 執行analyze table命令；
3. 表的大小發生非常大的變化（大小變化超過1/16，或者新插入了20億條資料）會觸發索引統計資訊更新；
4. 執行show table status或者show index命令；
5. 從INFORMATION_SCHEMA.TABLES和INFORMATION_SCHEMA.STATISTICS表中查詢時。

更新索引統計資訊是非常耗效能的，尤其是大表，InnoDB提供了引數innodb_stats_on_metadata，可以在發生上述第4和第5兩個場景的時候不自動更新統計資訊。該引數預設也是關閉的。
InnoDB在mysql5.6版本里面提供了引數innodb_stats_persistent，該引數用於控制是否將統計資訊持久化到磁碟上，預設是開啟的。持久化到磁碟後，系統重啟後可以更快速的生成統計資訊，而且可以保持執行計劃的穩定。
下面介紹一下show index命令。show index from 表名可以展示索引資訊，裡面有一個欄位是cardinality（列基數），該欄位的含義是估算索引列有多少個不同的取值。

（3）減少碎片

索引或者表的碎片越多，那麼查詢時作業系統載入的資料頁也就越多，這會導致查詢效能降低。碎片也可能會使磁碟順序掃描變為隨機掃描。
innodb不支援optimize table命令來減少碎片。可以通過匯出再匯入資料的方式重新整理資料，或者刪除索引再重建索引，也可以通過alter table 表名 engine=INNODB的方式重建。

Innodb 自適應雜湊索引的優缺點
[MySQL]淺談InnoDB儲存引擎(六）自適應雜湊索引
MySQL索引下推
mysql索引長度的一些限制