Index 填充因子 不只是一個傳說

• 2019 年 10 月 7 日
• 筆記

填充因子，聽說過，不過….. 這個需要我去設置嗎？ 默認不就可以了。

我想聽到填充因子這個詞，大部分的反映基本上邊就可以總結 80-90% 的回復。

因為另一句話在大多數書籍或者文字中，會提到，亂去設置填充因子，會降低性能。那這篇文字到底要說什麼？

我們的從B+ TREE 來說起，當然這不是去講述b+tree的，知識因為要講填充因子，不得不從b+ tree說起。

但要談一個東西我們的有一個前提，索引或者說大部分索引，或這裡提到的索引是 b+tree 方式組成的。並且您對B+tree 大致的原理是有了解的基礎上在展開這個話題。

首先需要畫一個簡易的 B + tree 的圖， 期間部分 leaf page 已經滿了，有些還有空的位子根節點通過指針與葉子節點進行連接，同時每個葉子節點的末端也通過指針指向下一個葉子節點開始的位置。

這時我們要插入65 這個數字，很明顯葉子節點已經沒有位置可以插入這個數字，那葉子節點就需要進行拆分

下面這張圖可以很清晰的看到如果葉子節點滿了的情況下，在進行數據的插入就需要進行 葉子節點的拆分，以及根節點部分數據的變更。

致拆分的規律將中間的節點放入到根節點，小於中間節點的數據放到記錄的左邊，大於中間節點的放到數據記錄的右邊。

所以B+TREE 總是會保持一個平衡，那填充因子到底在這裡起一個什麼作用，InnoDB在創建或重建b樹索引時是進行批量加載的，這樣創建索引的途徑稱為排序索引構建。innodb_fill_factor配置選項定義在排序索引構建期間填充的每個B-tree頁面上的空間百分比，其餘空間保留給未來索引增長。

根據官方的資料，如果是cluster索引的情況下（其實就是所謂的索引組織表（oracle）， 聚簇索引+主鍵（sql server）），innodb 會留下 1/16的頁面來保證後續的插入和升級索引記錄的空間預留，當然填充索引的作用並不是僅僅於此，在我們大量刪除row 的情況下，默認的情況下，當一個頁面的數據不足50%的情況下，就要開始收縮頁面，或者我們稱之為，合併頁面。

所以填充因子的設計尤其針對一些特殊的表來說，是有必要進行單體設計的。尤其對性能要求極高的領域，你願意你的數據頁面一會合併，一會分割嗎，所以下面就需要開始解決這個問題。

下面有幾個業務場景的假設

假設1， 業務比較穩定，數據在落盤後不會經常的更改，或者刪除。

假設2， 數據屬於記錄變更性質，會經常被UPDATE ，例如一些訂單的狀態，一個訂單至少有3個狀態，在幾分鐘中內會進行變化，並且會經常更改一些注釋。

假設3，表屬於統計表，數據在記錄後不會改變，大部分都是在進行查詢活動

以上三個場景，對於填充因子的需求是各種各樣的

我們待會來說這三個需求，現在我們回到MYSQL的填充因子

innodb_fill_factor 這個參數，如果你使用的MYSQL 5.7 的版本，

show variables like '%fill%';

你會看到一個 100的值，說明默認 innodb_fill_factor 的填充率100，當然這不是針對 primary key 也就是我們的主鍵以及聚簇索引來說的，MYSQL的 聚簇索引，強制的填充率是 93.75%

而剩下的填充率的設置都是給二級索引準備的，回到我們剛才的那個話題三個場景。

1 如果我們的業務大致是這樣的，可以考慮設置填充率在 0.9 左右，也就是 90

2 如果我們的業務中的數據會經常進行更改，則填充率可以考慮 0.8 到0.75 也即使 75-80

3 業務場景三，基本上我們可以不改變默認的值

那可能會有人問，我的表經常變動，平均幾秒就變動一次數據，我是不是可以將這個填充率設置為 50%，讓我的數據頁面不經常被split 或者 merge，給足夠的空間來防止上面的那些操作。這樣做可能會產生，索引佔用更多的磁盤空間。對於值較低的索引，索引中有更多的頁面，索引統計數據抽樣可能不是最優的。優化器可能會選擇具有次優統計信息的錯誤查詢計劃。

所以，看完這篇文字後，可以去看看您的MYSQL的 innodb_fill_factor 的系統當前設置時多少，並且根據您庫中重要的表的情況，適當的調整您的值，這樣對於某些系統會有性能上的幫助。