引言
國內較多的網際網路公司都是採用MySQL作為資料庫系統，隨著業務的發展，難免會碰到需要新建索引來優化某些SQL執行效能的情況。在MySQL實現online create index之前，新建索引意味著業務要停止寫入，這是非常影響使用者使用體驗的，為此，MySQL引入了online create index，極大地減少了業務停寫的時間，使得新建索引期間業務能夠持續正常的工作。本文主要是對其實現原理的總結以及關鍵步驟的解釋說明。
MySQL online create index原理
在MySQL中表格至少需要設定一個主鍵，如果使用者未指定主鍵的話，內部會自動生成一個。對於帶主鍵的表格，MySQL會以聚集索引的方式實現，即表格的資料都是完整的儲存在聚集索引上的。對於主鍵的變更，相當於對聚集索引進行變更，這個過程目前MySQL還是以停寫的方式實現的，本文主要討論的是新建二級索引的實現，為了方便描述，以一個例子來說明本文要討論的場景。
create table t1(
c1 int primary key,
c2 int,
c3 int,
);
複製程式碼剛開始業務中的SQL都是以主鍵c1來做查詢的，後來隨著業務的發展，可能出現了以c2做查詢的SQL，此時，為了優化此類SQL的執行效能，需要在c2列上構建索引，即
create index index_c2 on t1(c2);
複製程式碼MySQL online create index主要分為兩個階段，第一階段為從主表讀取索引列並排序生成索引表的資料，稱為基線資料；第二階段為把新建索引階段索引表的增量資料更新到第一階段的基線資料上。具體來看，主要過程如下。
- 使用者執行create index
- 等待當前所有事務執行結束，但不影響新事務的開啟；新開啟的事務更新時會把新建索引的記錄到增量資料，稱為Row Log
- 開始構建索引，主要是從主表讀出資料並排序
- 把新建索引表期間產生的增量資料更新到索引表中
- 構建的收尾工作
接下來將略過不太重要的步驟1和步驟5，主要描述步驟2-4的詳細實現。
等事務結束
在執行create index語句之後，MySQL會先等待之前開啟的事務先結束後，再真正開始索引的構建工作，這麼做的原因是在執行create index之前開啟的事務可能已經執行過某些更新SQL語句，這些SQL語句沒有生成新建索引表的增量資料(Row Log)，如果不等待這部分事務結束，可能會出現基線資料中沒有此部分資料，且Row Log中也沒有此部分資料，最終該部分資料在索引表中不存在。
MySQL的等事務結束是通過MDL(Meta Data Lock)實現的，MDL會按序喚醒鎖等待者，這樣就能保證create index之前開啟的事務一定執行完成了。
實際測試中，可以觀察到當create index之前的事務一直沒有結束時，create index語句會一直卡在thd->mdl_context.upgrade_shared_lock(sql_table.cc:7381)上。
排序
索引構建的第一階段的工作是根據主表的資料，來構建索引表的資料。此過程總共有兩個步驟，第一是讀取主表中所需要的索引列資料；第二是將資料按照索引列排序。
其中讀取主表資料和普通的全表掃描區別不大，而將資料按照索引列排序則是一個外部排序的過程。MySQL對外部排序實現較為簡單，僅為最普通的單執行緒兩路歸併演算法，優點是實現簡單，佔用記憶體資源少，缺點是效能較差。
更新增量資料到構建好的索引表
一般地，對於資料量較大的表格，構建索引的時間較長，通常是小時級別的，這期間往往會有新事務的提交，其中就可能包含對新建索引表的修改。因此，在索引基線資料構建好之後，還需要把構建期間的增量資料更新到索引表中，那麼問題來了，在更新增量資料到索引表中會不斷的有新事務修改資料，這樣何時才能保證所有的修改都更新到索引表上呢？答案是加鎖，粗暴一點的加鎖方式是在整個增量資料更新到索引表期間停寫，完成之後，再放開寫入。但是，因為索引構建時間長，增量資料的資料量一般也較大，如果更新整個增量資料到索引表期間都停寫的話，會較大地影響使用者使用體驗。因此，MySQL對加鎖過程做了優化。
首先Row Log會被拆分為多個較小的Block，事務的更新會把資料寫入到最後一個Block中，因此，普通的DML更新的時候會對最後一個Block加鎖。同樣的，在更新每個Block到索引表的時候，會先加鎖，如果當前Block不是最後一個Block時，會把鎖釋放，如果是最後一個Block，則保持加鎖狀態，直到更新結束。因此，在更新Row Log到索引表期間，加鎖的時間比較短，僅在最後一個Block更新到索引表時會持有鎖一段時間。
總結
MySQL online create index的整體思路分為兩步構建基線以及更新增量，構建基線時採用的歸併演算法比較簡單，資源佔用少，但效能會比較差；在更新增量時，採用將增量切分成更小的塊，來減少停寫的時間，是比較通用的方法。
PS: 本部落格更新會在第一時間推送到微信公眾號，歡迎大家關注。