MySQL分庫分表面試知識總結

場景分析

• Web開發工作，亦或是海量資料開發工作，學習分庫、分表、分區等知識都是很有必要的 。
• 面試的時候，也有可能也會被問到。不過作為一個有經驗的Coder，不熟悉分庫、分表技術确實有些 low。

基礎概念

• 分表，能夠解決單表資料量過大帶來的查詢效率下降的問題；
• 分庫，面對高并發的讀寫通路，當資料庫master伺服器無法承載寫操作壓力時，不管如何擴充slave伺服器，此時都沒有意義。此時，則需要通過資料分庫政策，提高資料庫并發通路能力。
• 優點，分庫、分表技術優化了資料存儲方式，有效減小資料庫伺服器的負擔、縮短查詢響應時間。
• 資料分庫、分表存儲場景條件

• 關系型資料庫
• 主從架構(master-slave)
• 單表資料量在百萬、千萬級别
• 資料庫面臨極高的并發通路

• 分庫、分表實作政策

• 關鍵字取模，實作對資料通路進行路由。

分庫

• 舉例

• 按功能分

• 使用者類庫、商品類庫、訂單類庫、日志類庫等

• 按地區分

• 每個城市或省市一個同樣的庫，如: db_click_bj、db_click_sh 等

• 主要解決問題：

• 單表過大造成的性能問題；
• 單表過大造成的單伺服器空間問題。

• 按某個字段分

• 如：将使用者資料附件表分成3個附件分表pre_forum_attachment_[0|1|2]，和1個附件索引表（存儲tid和附件id關系），根據tid最後一位判斷附件儲存在哪個分表中。

• 按日期分表

• 日志類、統計類資料表按年、月、日、周分表。如：點選量統計click_201801、click_201802

• 通過MySQL的merge存儲引擎實作

• 需要建立分表、總表，總表需要merge存儲引擎。

• 示例代碼

create table log_merge (
dt datetime not null,
info varchar (100) not null,
　 index (dt)
) engine = merge
union= (log_2017,log_2018) insert_method = last;      

• 縱向分表常見的方式有根據活躍度分表、根據重要性分表等。
• 主要解決問題：

• 表與表之間資源争用問題；
• 鎖争用機率小；
• 實作核心與非核心的分級存儲，如UDB登陸庫拆分成一級二級三級庫；
• 資料庫同步壓力問題。

• 具體政策

• 經常組合查詢的列放在一個表，常用字段的表可考慮Memory引擎。
• 不經常使用的字段單獨成表。
• 把text、blob等大字段拆分放在附表。如：把使用者文章表分成主表news和從表news_data，主表存标題、關鍵字、浏覽量等，從表存具體内容、模闆等。

分庫、分表注意事項

• 次元問題

• 針對使用者購買記錄資料，如果按照使用者緯度分表，則每個使用者的交易記錄都儲存在同一表中，是以很快很友善的查找到某使用者的購買情況，但是某商品被購買的情況則可能分布在多張表中，查找起來比較麻煩。
• 若按照商品次元分表，友善查找商品購買情況，但查找個人交易記錄比較麻煩。
• 常見解決方案：

• 通過掃表方式解決，效率太低，不可行。
• 記錄兩份資料，一份按照使用者緯度分表，一份按照商品次元分表。
• 通過搜尋引擎解決，但如果實時性要求很高，則牽涉到實時搜尋問題。

• 避免分表join操作。關聯的表有可能不在同一資料庫中。
• 避免跨庫事務

• 避免在一個事務中修改db0、db1中的表，不僅操作複雜，而且影響效率。

• 分表宜多不宜少；避免後期可能二次拆分。
• 盡量同組資料統一DB伺服器。例如将賣家a的商品和交易資訊都放到db0中，當db1挂了的時候，賣家a相關的東西可以正常使用。即避免多個資料庫中的資料産生依賴。

References

• ​​Mysql快速插入千萬條測試資料. 造資料.方式有利有弊​​
• ​​mysql中迅速插入百萬條測試資料的方法. 造資料. 另3種方法總結​​
• ​​mysql資料庫分表及實作. 簡單示例​​
• ​​mysql分庫分表實戰及php代碼操作完整執行個體. php代碼實作​​
• ​​MySQL之——基于Amoeba實作讀寫分離​​