從MySQL5.7開始，開發人員改變了InnoDB構建二級索引的方式，采用自下而上的方法，而不是早期版本中自上而下的方法了。在這篇文章中，我們將通過一個示例來說明如何構建InnoDB索引。最后，我將解釋如何通過為innodb_fill_factor設置更合適的值。

索引構建過程

在有數據的表上構建索引，InnoDB中有以下幾個階段：1.讀取階段（從聚簇索引讀取并構建二級索引條目）2.合并排序階段3.插入階段（將排序記錄插入二級索引）在5.6版本之前，MySQL通過一次插入一條記錄來構建二級索引。這是一種“自上而下”的方法。搜索插入位置從樹的根部（頂部）開始并達到葉頁（底部）。該記錄插入光標指向的葉頁上。在查找插入位置和進行業面拆分和合并方面開銷很大。從MySQL5.7開始，添加索引期間的插入階段使用“排序索引構建”，也稱為“批量索引加載”。在這種方法中，索引是“自下而上”構建的。即葉頁（底部）首先構建，然后非葉級別直到根（頂部）。

示例

在這些情況下使用排序的索引構建：

• ALTERTABLEt1ADDINDEX（orCREATEINDEX）
• ALTERTABLEt1ADDFULLTEXTINDEX
• ALTERTABLEt1ADDCOLUMN,ALGORITHM=INPLACE
• OPIMIZEt1
• 對于最后兩個用例，ALTER會創建一個中間表。中間表索引（主要和次要）使用“排序索引構建”構建。
• 算法
• 在0級別創建頁，還要為此頁創建一個游標
• 使用0級別處的游標插入頁面，直到填滿
• 頁面填滿后，創建一個兄弟頁（不要插入到兄弟頁）
• 為當前的整頁創建節點指針（子頁中的最小鍵，子頁碼），并將節點指針插入上一級（父頁）
• 在較高級別，檢查游標是否已定位。如果沒有，請為該級別創建父頁和游標
• 在父頁插入節點指針
• 如果父頁已填滿，請重復步驟3,4,5,6
• 現在插入兄弟頁并使游標指向兄弟頁
• 在所有插入的末尾，每個級別的游標指向最右邊的頁。提交所有游標（意味著提交修改頁面的迷你事務，釋放所有鎖存器）
• 為簡單起見，上述算法跳過了有關壓縮頁和BLOB（外部存儲的BLOB）處理的細節。
• 通過自下而上的方式構建索引為簡單起見，假設子頁和非子頁中允許的最大記錄數為3
• CREATETABLEt1(aINTPRIMARYKEY,bINT,cBLOB);
• INSERTINTOt1VALUES(1,11,'hello111');
• INSERTINTOt1VALUES(2,22,'hello222');
• INSERTINTOt1VALUES(3,33,'hello333');
• INSERTINTOt1VALUES(4,44,'hello444');
• INSERTINTOt1VALUES(5,55,'hello555');
• INSERTINTOt1VALUES(6,66,'hello666');
• INSERTINTOt1VALUES(7,77,'hello777');
• INSERTINTOt1VALUES(8,88,'hello888');
• INSERTINTOt1VALUES(9,99,'hello999');
• INSERTINTOt1VALUES(10,1010,'hello101010');
• ALTERTABLEt1ADDINDEXk1(b);
• InnoDB將主鍵字段追加到二級索引。二級索引k1的記錄格式為(b,a)。在排序階段完成后，記錄為：
• (11,1),(22,2),(33,3),(44,4),(55,5),(66,6),(77,7),(88,8),(99,9),(1010,10)
• 初始插入階段
• 讓我們從記錄(11,1)開始。
• 在0級別（葉級別）創建頁
• 創建一個到頁的游標
• 所有插入都將轉到此頁面，直到它填滿了
• 箭頭顯示游標當前指向的位置。它目前位于第5頁，下一個插入將轉到此頁面。
• 還有兩個空閑插槽，因此插入記錄(22,2)和(33,3)非常簡單對于下一條記錄(44,4)，頁碼5已滿（前面提到的假設最大記錄數為3）。這就是步驟。頁填充時的索引構建
• 創建一個兄弟頁，頁碼6
• 不要插入兄弟頁
• 在游標處提交頁面，即迷你事務提交，釋放鎖存器等
• 作為提交的一部分，創建節點指針并將其插入到【當前級別+1】的父頁面中（即在1級別）
• 節點指針的格式(子頁面中的最小鍵,子頁碼)。第5頁的最小鍵是(11,1)。在父級別插入記錄((11,1)，5)。
• 1級別的父頁尚不存在，MySQL創建頁碼7和指向頁碼7的游標。
• 將((11,1)，5)插入第7頁
• 現在，返回到0級并創建從第5頁到第6頁的鏈接，反之亦然
• 0級別的游標現在指向兄弟頁，頁碼為6
• 將(44,4)插入第6頁
• 下一個插入-(55,5)和(66,6)-很簡單，它們轉到第6頁。
• 插入記錄(77,7)類似于(44,4)，除了父頁面（頁面編號7）已經存在并且它有兩個以上記錄的空間。首先將節點指針((44,4),8)插入第7頁，然后將(77,7)記錄到同級8頁中。
• 插入記錄(88,8)和(99,9)很簡單，因為第8頁有兩個空閑插槽。
• 下一個插入(1010,10)。將節點指針((77,7),8)插入1級別的父頁（頁碼7）。MySQL在0級創建同級頁碼9。將記錄(1010,10)插入第9頁并將光標更改為此頁面。以此類推。在上面的示例中，數據庫在0級別提交到第9頁，在1級別提交到第7頁。
• 我們現在有了一個完整的B+-tree索引，它是自下至上構建的！
• 索引填充因子全局變量innodb_fill_factor用于設置插入B-tree頁中的空間量。默認值為100，表示使用整個業面（不包括頁眉）。聚簇索引具有innodb_fill_factor=100的免除項。在這種情況下，聚簇索引也空間的1/16保持空閑。即6.25%的空間用于未來的DML。
• 值80意味著MySQL使用了80%的頁空間填充，預留20%于未來的更新。如果innodb_fill_factor=100則沒有剩余空間供未來插入二級索引。如果在添加索引后，期望表上有更多的DML，則可能導致業面拆分并再次合并。在這種情況下，建議使用80-90之間的值。此變量還會影響使用OPTIMIZETABLE和ALTERTABLEDROPCOLUMN,ALGOITHM=INPLACE重新創建的索引。也不應該設置太低的值，例如低于50。因為索引會占用浪費更多的磁盤空間，值較低時，索引中的頁數較多，索引統計信息的采樣可能不是最佳的。優化器可以選擇具有次優統計信息的錯誤查詢計劃。
• 排序索引構建的優點
• 沒有頁面拆分（不包括壓縮表）和合并
• 沒有重復搜索插入位置
• 插入不會被重做記錄（頁分配除外），因此重做日志子系統的壓力較小
• 缺點
• ALTER正在進行時，插入性能降低Bug＃82940，但在后續版本中計劃修復。