MySQL海量存儲的索引與分表設計的方法教程

這篇文章主要講解了“MySQL海量存儲的索引與分表設計的方法教程”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“MySQL海量存儲的索引與分表設計的方法教程”吧！

一、什么是InnoDB記錄存儲方式?

大家都知道在InnoDB存儲引擎中記錄是按主鍵順序存儲，并且依靠這個特性為表創建了主鍵聚簇索引。

InnoDB是如何實現記錄“順序存儲”的呢?首先要知道“順序”分頁內順序和頁間順序，頁為InnoDB內外存交換的基本單位。

• 頁間順序：磁盤文件中頁與頁之間使用雙向鏈表連接，頁間有可能是物理有序。大多數情況是邏輯上的有序;

• 頁內順序：頁內各記錄使用單項鏈表把記錄連接起來，所以頁內是邏輯有序，配合slot數據結構實現頁內接近二分查找的查詢效率。

圖為InnoDB頁內空間分布：

根據以上特點，我們來分析下使用不同的主鍵對存儲會造成哪些影響：

• 自增主鍵：主鍵值遞增，數據是順序插入的，所以在頁內數據物理連續，寫滿一頁后在順序分配下一頁。在沒有刪除操作的情況下，整個表的記錄在磁盤文件中都是按照寫入順序連續存儲的。這中存儲方式磁盤利用率非常高，且隨機IO很低。插入效率相當高。

• 業務主鍵：比如用戶表使用uid做主鍵，商品表使用infoId做主鍵，這種有意義的主鍵，我們稱為業務主鍵。很明顯，業務主鍵不但無法做到記錄物理連續而且在插入數據時還可能造成頁的分裂，從而導致頁內碎片，例如如果一個頁空間已滿，存儲主鍵值0~99，100條數據，如果要插入55這條記錄，頁內已經放不下，需要分裂成兩個頁才能完成插入操作，而分裂后的兩個頁很難被寫滿，會造成頁內碎片，所以業務主鍵在寫入性能和磁盤利用率上都不如自增主鍵。

通過上面的分析，我們是不是可以得出結論：使用自增主鍵一定好呢?在我們分析完InnoDB的索引以前，現在下結論還有些早。

二、什么是主鍵索引?

InnoDB會自動在表的主鍵上創建索引，數據結構使用B+Tree。根據存儲上的特點主鍵索引也被稱為聚簇索引。聚簇索引的索引結構和實際數據是存儲在一起的，B+Tree葉子節點存儲的就是實際的記錄，如圖所示：

三、什么是非主鍵索引?

既然記錄存儲在主鍵索引結構中，那么在其他列創建的索引是如何找到記錄的呢?我們可以很自然的想到，非主鍵列上的索引可以先通過自身索引結構查找到主鍵值，然后在用主鍵值在聚簇索引上找到相應的記錄。InnoDB就是這么做的，所以我們也稱非主鍵列上的索引為二級索引(因為一次查詢需要查找兩個索引樹)

二級索引有以下特點：

• 除了主鍵索引以外的索引;

• 索引結構葉子節點中的Data是主鍵值;

• 一次查詢需要查找自身和主鍵兩個索引。

四、什么是聯合索引?

聯合索引也叫多列索引，索引結構的key包含多個字段，排序時先第一列比較，如果相同再按第二列比較，以此類推。聯合索引結構圖如圖所示：

聯合索引上的查詢要滿足以下特點：

• key按照最左開始查找，否則無法使用索引;

• 跳過中間列，會導致后面的列不能使用索引;

• 某列使用范圍查詢是，后面的列不能使用索引。

根據前綴索引特性，聯合索引(a,b,c)，可以滿足(a)，(a,b)，(a,b,c)三種查詢。

五、小結

了解了InnoDB的索引后，我們再來分析自增主鍵和業務主鍵優缺點：

• 自增主鍵：寫入、查詢效率和磁盤利用率都高，但每次查詢都需要兩級索引，因為線上業務不會有直接使用主鍵列的查詢。

• 業務主鍵：寫入、查詢效率和磁盤利用率都低，但可以使用一級索引，依賴覆蓋索引的特性，某些情況下在非主鍵索引上也可以實現1次索引完成查詢(后面的案例中會詳細介紹)。

自增主鍵相對業務主鍵在IO效率上優勢在SSD硬盤下幾乎可以忽略，而在業務查詢性能上業務主鍵有明顯優勢，所以在業務數據庫中，我們使用的都是業務主鍵。

六、電商業務分表設計與實踐

針對MyQL數據庫特性結合自身業務特點制定了一系列數據庫使用規范，可以有效的指導一線RD在項目開發過程中數據庫表和索引的設計工作。下面介紹電商業務中表和索引的重點設計原則以及兩個實際案例。

1、表設計原則

• 主鍵選擇：前面我們已經對比分析過業務主鍵和自增主鍵的優缺點，結論是業務主鍵更符合業務的查詢需求，而互聯網業務大多都符合讀多寫少的特性，所以所有線上業務都使用業務主鍵。

• 索引個數：由于過多的索引會造成索引文件過大，所以要求索引數不多于5個。

• 列類型選擇：通常越小、越簡單越好，例如：BOOL字段統一使用TINYINT，枚舉字段統一使用TINYINT，交易金額統一使用LONG。因為BOOL和枚舉類型使用TINYINT可以很方便的擴展，針對金額數據，雖然InnoDB提供了支持精確計算的DECIMAL類型，但DECIMAL是存儲類型不是數據類型，不支持CPU原聲計算，效率會低一些，所以我們簡單處理將小數轉換為整數用LONG存儲。

• 分表策略：首先要明確數據庫出現性能問題一般在數據量到達一定程度后!所以要求我們提前做好預估，不要等需要拆分時再拆，一般把表的數據量控制在千萬級別;常用分表策略有兩種：按key取模，讀寫均勻;按時間分，冷熱數據明確。

2、實際案例

案例一：用戶表設計

用戶表包含字段：uid，nickname，mobile，addr，image…..，switch;uid為主鍵，業務上有按uid和mobile兩種查詢需求，所以要在moblie上創建索引。

switch列比較特殊，類型為BIGINT，用來保存用戶的BOOL類型的屬性，每一位可以保存用戶的一個屬性，例如我們用第一位保存是否接收推送，第二位保存是否保存離線消息等等。

這種設計有很高的擴展性(因為BIGINT有64位，可以保存64個狀態，一般情況很難用滿)，但是同時也帶來一些問題，switch有很高的查詢頻率。由于InnoDB是行存儲，要找查詢switch需要把正行數據取出來。

針對上述場景，我們在表設計上可以做哪些優化呢?常用的方案是把表垂直查分，這種很常見我們不做過多討論。

還有一種方案我們可以利用InnoDB覆蓋索引的特性，在uid和switch兩列上創建聯合索引，這樣在二級索引上包含uid和switch兩列的值，這樣用uid查詢switch時，只通過二級所以就能找到switch，不需要訪問記錄，甚至不需要到二級索引的葉子節點就可以找到要查詢的switch值，查詢效率非常高。

另外有一點需要考慮，可以想象switch的變更也是相當頻繁的，switch值得改變會導致聯合索引的變更嗎(這里的變更指索引節點分裂或順序調整)?

答案是不會!因為聯合索引的第一列uid是唯一且不會變的，所以uid就已經決定了索引的順序，switch列的改變只會改變索引節點上第二個key的值，不會改變索引結構。

案例二：IM子系統分表方案

IM子系統包含：用戶、聯系人、云消息、系統消息四個主要的業務表。數據庫按業務拆分，每個業務使用單獨的實例。除系統消息表外，其他表都是以uid做key按128取模分了128個表。由于系統消息的業務比較特殊，所以其分表方案與其他業務不太一樣。

我們先來了解下系統消息的業務特點：系統消息表保存的是服務器發出通知類型的消息，既然是通知，就會有實效性，我們規定系統消息有效期為30天，所以針對以上特點我們采取如下分表方案：

• 按月對系統消息表進行分表，每個月的數據又分為128個表。

大家思考一個問題：查詢一個人的系統消息時，由于是按月分表，而大多數查詢都是跨月的(因為需要查找30天內的消息)，所以需要兩次數據庫交互。是否可以優化呢?

我們可以冗余存儲，具體優化方案如下：

• 插入系統消息時寫當前月和上個月兩個表;

• 讀從上一個月開始讀;

這個方案我們可以保證一次查詢可以找到用戶所有有效期內的系統消息，但是通過犧牲了存儲空間和寫入效率換取的，不一定是最優的方案，但在總數據量不大，且比較注重查詢性能的業務場景下還是可以選用的。

七、總結

• 自增主鍵性能不一定高，需要結合實際業務場景做分析;

• 大多數場景數據類型選擇上盡量使用簡單的類型;

• 索引不是越多越好，太多的索引會導致過大的索引文件;

• 如果要查詢的數據可以在索引文件中找到，存儲引擎就不會查找主鍵索引訪問實際記錄。

感謝各位的閱讀，以上就是“MySQL海量存儲的索引與分表設計的方法教程”的內容了，經過本文的學習后，相信大家對MySQL海量存儲的索引與分表設計的方法教程這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！