知識星球（點選上方公眾號，可快速關註）
來源：Nicol ，
taozj.net/201809/innodb-index.html
資料庫最佳化可以說是後臺開發中永恆的話題，資料庫的效能通常是整個服務吞吐量的瓶頸之所在。
1. 索引概述
InnoDB中的表都是按照主鍵順序組織存放的，這種組織方式稱之為索引組織表，對比於MyISAM的表組織方式。在InnoDB中每張表都必須有一個主鍵，如果在建立表的時候沒有顯式定義主鍵，則InnoDB首先會判斷表中是否有非空的唯一索引，如果有則將該列作為主鍵；否則InnoDB會自動建立一個6位元組大小的指標作為主鍵。除主鍵之外，InnoDB還可以有輔助索引，而輔助索引頁中僅僅存放鍵值和指向資料頁的偏移量，而不像主鍵資料頁儲存的是一個完整的行記錄。
InnoDB儲存引擎中，所有的資料都被邏輯地存放在一個表空間中，表空間又被分為段(Segment)、區(Extent)、頁(Page)組成，其中段由儲存引擎自動管理，區的大小固定為1M，然後預設情況下頁的大小為16KB，也就是一個區總共有64個連續的頁組成。不過在MySQL5.6開始，頁的大小可以設定為4K、8K了，設定成4K除了可以提高磁碟的利用率之外，對於現代SSD硬碟將更加合適，不過這中更新比較的麻煩，需要將輸入匯出後再重新匯入，一般的備份恢復工具都是原樣複製資料，沒有辦法支援變更頁大小。
預設的B+樹索引其查詢次數(效率)取決於B+樹的高度，生產環境下一般樹高為3~4層，即查詢一條記錄需要經過3~4個索引頁，而且B+樹索引並不能找到一個給定鍵值的具體行，其只能根據鍵和索引找到資料行所在的頁，然後資料庫把對應的頁讀取到記憶體，再在記憶體中執行查詢，並最後得到需要查詢的資料。InnoDB還會監控對錶上各索引頁的查詢操作，如果觀察到透過建立hash索引可以帶來速度提升，則會根據訪問頻率和訪問樣式自動為部分熱點頁建立hash索引，這個過程稱之為自適應雜湊索引，而且該過程是人為無法幹預、儲存引擎自動實現的。
使用索引的一大禁忌是不要在取用索引列的時候使用函式，比如max(id)、id+3>5等，或者隱式的資料型別轉換操作，這樣會導致索引失效導致全掃描。
2. 線上修改資料表
在MySQL 5.5之前修改表結構、或者建立新索引的時候，需要經過：先鎖定原始表，建立一張新的臨時表(臨時使用tmpdir路徑，確保有足夠空間可用)，然後把原表中的資料匯入到新的臨時表中，接著刪除原表，最後再把臨時表重新命名為原來的表名。所以修改表結構需要註意，將對同一個表的ALTER TABLE多個操作合併到一條陳述句中，減少上述重覆的步驟。同時，針對修改列名、修改數值型別的表示長度INT(3)->INT(10)、修改資料表註釋、向ENUM增加新的型別、修改資料表名這些操作不需要將資料表中的所有記錄都複製到臨時表。
新版MySQL支援Fast Index Creation，具體說來就是對於新輔助索引的建立，InnoDB會對要建立索引的表上一個S鎖，使該表以只讀的可用性提供服務，由於不需要重新建立表、複製資料，因而輔助索引的建立速度也快很多；刪除索引的時候InnoDB只需更新內部試圖示記輔助索引的空間為可用，同時刪除MySQL資料庫內部試圖上對應表的索引定義即可。
MySQL 5.6的版本支援Online DDL，允許在輔助索引建立的同時，還允許對錶同時執行諸如INSERT、UPDATE、DELETE等DML操作而不會被阻塞，其原理是在執行索引建立或者刪除操作的時候，將INSERT、UPDATE、DELETE這類的操作日誌先記錄到一個叫做“線上修改日誌”的記憶體空間中，當索引完成後再重新應用這些更新到表上，以此達到資料的一致性。不過“線上修改日誌“只存留在記憶體中，預設大小是128MB，如果修改表結構時候DML操作太多，會導致該空間不夠用而撤銷修改。
3. 建立索引
建立索引的時候講求一個Cardinality指標，該值表示索引中唯一值的估計數目，理想情況下該值除以錶行數應該盡可能接近1，否則表示該列選擇性太低而應該考慮刪除該索引。 對Cardinality的統計是使用取樣方式進行估算的，當表的修改數目超過總記錄的1/16、或者修改總次數超過20億次，則會隨機選擇8個資料頁重新統計該值，不過透過ANALYZE TABLE命令可以強制讓資料庫重新收集相關的統計資訊。
實踐中OLTP和OLAP對索引的要求是有差異的，在OLTP應用中查詢操作通常只從資料庫傳回很小部分資料集，此時根據查詢條件選擇高區分度的列來建立索引是很有意義的；對於OLAP應用通常都需要傳回大批次的資料，很多情況下建立索引意義不是很大，因為大量資料傳回的話往往全表順序掃描效率更高，不過OLAP中對時間建立索引是很常見的操作。
4. 改寫索引
表示直接從輔助索引中就可以得到需要的查詢記錄，而不需要再從聚簇索引中查詢行記錄。使用改寫索引的好處是輔助索引不包含整行記錄，所以索引大小會遠遠小於聚簇索引，單個索引頁就可以儲存更多的索引項，那麼訪問索引本身的操作就可以減少順序IO操作了。有些情況，比如在MySQL中SELECT COUNT(*) FROM t;最佳化器是可以選擇使用輔助索引來最佳化查詢速度的，因為可以訪問更少的索引頁就可以統計到查詢結果了。
如果SELECT列不能使用改寫索引完成，那麼除了在輔助索引上查到指定記錄後，還需要進行一次書簽訪問才能查詢到整行中其他列的資料，並且此時的查詢將是成本很高的隨機離散讀操作(相對於傳統機械磁碟)。
所以如果最佳化器覺得需要傳回的資料量很少，則最佳化器還是可能會選擇使用輔助索引外加訪問聚簇索引的方式來傳回記錄的；但是當訪問資料量佔整個表記錄中挺大一部分的時候(比如20%)，則最佳化器可能會選擇全表掃描的方式來查詢資料，因為全表順序讀的代價可能比大量隨機讀的效率要高。大部分時候最佳化器都能做的不錯，不過當使用者有對索引的使用有足夠信心的時候還是可以影響最佳化器執行計劃的生成的，比如：可以使用USE INDEX的方式來提示最佳化器使用某個索引，不過實際上最佳化器還是會根據自己的判斷確定是否需要使用該索引；而透過FORCE INDEX則會強制選擇使用該索引；使用IGNORE INDEX會使最佳化器不能使用指定的索引，這通常可以誘導觸發執行全表掃描。
5. Multi-Range Read(MRR)最佳化
為了防止非改寫索引取資料的時候造成的大量隨機I/O，MyISAM和InnoDB會將查詢到的輔助索引存放在一個快取中，然後將他們透過主鍵進行排序，並按排序後的主鍵進行順序書簽查詢。透過這種方式可以將低效隨機訪問轉化為高效順序資料訪問，而且同一資料塊確保只需要被訪問一次，同時也減少緩衝池中頁被替換的次數，所以可以帶來查詢效能的極大提升。
MySQL5.6開始支援該項最佳化，使用的時候需要SET optimizer_switch=’mrr=on|off’的方式開啟。MRR特性可以用於range、ref、eq_ref型別的查詢操作，當查詢使用到該特性的時候就可以在Extra看到Using MRR提示了，當在有表連線的情況下，如果連線鍵是被驅動表的主鍵的時候，也會先基於驅動表的連線鍵進行排序，按照這個順序就可以MRR按照被驅動表的主鍵訪問資料了。
從上面的介紹看到MRR是一個思路簡單但是卻很重要的最佳化，但是在某些情況下使用也可能會有負面效應。當表的資料量很小，大部分資料也都被快取的時候，使用MRR不會帶來隨機訪問的收益，反而會因為額外的排序操作增加資源消耗；當限制只需要傳回LIMIT n的時候，這種最佳化會讀取排序很多不需要的索引，效能反而會降低；排序使用的記憶體空間大小由mrr_buffer_size設定的，如果該記憶體較小但是待排序的索引數量大的時候，就需要使用磁碟輔助進行多塊排序歸併，這也會降低效能。
6. Index Condition Pushdown(ICP)最佳化
老舊資料庫版本只有索引可用的限制條件才會被傳輸到儲存引擎層，在新版本開啟ICP最佳化的時候，針對選用索引涉及到的資料列條件就都會被傳輸到儲存引擎層，所以在支援ICP特性後，儲存引擎在處理索引的同時就可以判斷是否可以透過下推的選擇條件對部分記錄直接進行過濾操作了。所以在老版本的資料庫，都是儲存引擎對索引可以直接使用的條件進行操作，然後再將這些資料傳遞給MySQL引擎，這樣就會涉及到大量資料條目的讀取、傳遞和篩選工作，這時候在Extra中肯定會看到Using where的提示，因為MySQL引擎對儲存引擎傳遞來的資料進行了篩選加工；現在將索引涉及到的篩選條件下推放到了儲存引擎層，就大大減少了上面的操作任務。
該功能可以使用SET optimizer_switch=’index_condition_pushdown=on|off’的方式開啟或者關閉。ICP最佳化可以用於range、ref、req_ref、ref_or_null型別的查詢，當查詢使用到該特性的時候可以在Extra看到Using index condition。
7. 索引合併
當查詢WHERE中羅列有多個條件，他們都可以使用不同的索引進行最佳化查詢的時候，如果最佳化器發現某一個索引傳回的記錄相比其他索引顯著的要少，那麼執行計劃就會選用這個索引；而如果最佳化器發現多個索引都不高效的時候，最佳化器會將這些查詢條件分離，用各自的索引分別獨立執行檢索，最後再將多個結果集合進行合併後傳回。當然，這種情況最佳化器也可能使用全表掃面的方式處理。
本文完！
參考
-
MySQL技術內幕 – InnoDB儲存引擎
https://book.douban.com/subject/24708143/
【關於投稿】
如果大家有原創好文投稿，請直接給公號傳送留言。
① 留言格式：
【投稿】+《 文章標題》+ 文章連結
② 示例：
【投稿】《不要自稱是程式員，我十多年的 IT 職場總結》：http://blog.jobbole.com/94148/
③ 最後請附上您的個人簡介哈~
看完本文有收穫？請轉發分享給更多人
關註「ImportNew」，提升Java技能
