萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 分庫分表完整實戰:從動態分表到 Snowflake 全局 ID 的落地全過程

業務場景與基礎表結構

模擬電商系統核心鏈路:用戶 → 門店 → 導購 → 商品 → 訂單 → 訂單商品。導購也是用戶,店主同時是導購與用戶。初始單表結構:

  • user、store、user_guide、goods、orders、order_goods

初始ER圖

分表策略選型:動態分表 vs 靜態分表

維度靜態分表動態分表(本文採用)
擴展性預建固定數量表,擴容需重分片按需建表,平滑擴容
路由邏輯Hash/Range 固定演算法配置表記錄用戶→分表映射,可人工干預
運維複雜度低高(需遷移工具、元數據管理)

業務拆分:出售訂單表 vs 購買訂單表

導購既賣又買,為讓「銷售端查詢/修改」高效,拆分為:

  • sell_order_n:完整訂單資訊(導購維度分表,user_guide_id 路由)。
  • buy_order_n:僅存 buy_order_id(=sell_order_id)、user_id、user_guide_id(買家維度分表,user_id 路由)。
  • goods_n:按 store_id 分表。
  • order_goods_n:按 user_guide_id 分表,關聯 sell_order。

拆分後ER圖

全局唯一 ID:Snowflake 演算法

分表後自增 ID 衝突,引入 Snowflake 產生 64 位唯一 ID(BIGINT UNSIGNED)。

部署 Snowflake 服務


pip install pysnowflake
snowflake_start_server \
  --address=192.168.137.11 \
  --port=30001 \
  --dc=1 \
  --worker=1 \
  --log_file_prefix=/tmp/pysnowflake.log

Python 客戶端獲取 ID


import snowflake.client
snowflake.client.setup('192.168.137.11', 30001)
guid = snowflake.client.get_guid()  # 返回 64 位整數
print(guid)  # 3631957913783762945

歷史數據 ID 重建與遷移(Python 腳本核心邏輯)

  1. 關閉外鍵檢查:SET FOREIGN_KEY_CHECKS=0;
  2. 修改所有 ID 欄位類型為 BIGINT UNSIGNED。
  3. 遍歷舊表主鍵,呼叫 Snowflake 產生新 ID,同步更新所有關聯表(goods、sell_order、buy_order、order_goods)。
  4. 提交事務,驗證新 ID 唯一性與關聯完整性。

分表遷移程序流程圖

分表遷移自動化腳本 sharding_table.py

核心流程

  1. 讀取 system_setting 表獲取當前最大分表號、分表規則。
  2. 批次建立新分表(CREATE TABLE ... LIKE + ALTER 調整主鍵類型)。
  3. 指定用戶遷移:move_data('username1', 2) 將該用戶相關數據搬移至第 2 組分表。
  4. 更新 user.table_flag 標記用戶所在分表號。

遷移驗證 SQL


SELECT * FROM buy_order_2 LIMIT 1;
SELECT * FROM sell_order_6 LIMIT 1;
SELECT * FROM goods_9 LIMIT 1;
SELECT user_id, table_flag FROM user WHERE user_id IN (1,6,66);

分表後查詢路由邏輯

場景 1:買家下單

  1. 從商品頁獲取 store_id、goods_id,隨機/策略選定 user_guide_id。
  2. 查詢導購的 table_flag(如 2),決定寫入 sell_order_2、order_goods_2。
  3. 查詢買家的 table_flag(如 6),寫入 buy_order_6。
  4. 同一 Snowflake ID 寫入三張表,事務保證一致性。

場景 2:買家瀏覽訂單(跨分表查詢)

買家訂單在 buy_order_9,商品詳情在導購分表 order_goods_2。

  1. 查 buy_order_9 獲取訂單列表(含 user_guide_id)。
  2. 前端 AJAX 並行請求各訂單對應導購的 order_goods_N 獲取商品明細。
  3. 避免後端一次性 Join 多張分表導致響應超時。

場景 3:導購查看/修改訂單

導購登入即知自身 table_flag(如 6),直接查 sell_order_6 + order_goods_6,單分表查詢極快。

分庫擴展準備

當單庫寫入壓力達到瓶頸,按 user_id 進一步分庫:

  • 命名規則:test_1、test_2…
  • system_setting 記錄:max_sharding_database_num、sharding_database_prefix、各分庫連線描述符(JSON)。
  • user 表新增 db_name 欄位標記所在庫。
  • 遷移腳本:CREATE TABLE test_1.xxx LIKE test.xxx + INSERT SELECT,同步更新元數據。

總結與避坑指南

  1. ID 生成器高可用: Snowflake 單點故障會導致全系統寫入不可用,生產必須部署多節點(不同 dc/worker)並配置客戶端輪詢/熔斷。
  2. 分表鍵選型: 嚴格按照「查詢最頻繁的維度」分表,導購查詢多 → 按 user_guide_id;買家查詢多 → 按 user_id。
  3. 冗餘字段謹慎: buy_order 冗餘 user_guide_id 便於路由,但業務可變欄位(如價格、狀態)嚴禁冗餘,避免不一致。
  4. 遷移工具自動化: 手工遷移不可控,務必開發通用遷移框架(如本文 Python 腳本),支援斷點續傳、校驗、回滾。
  5. 監控指標: 分表數量、單表行數、跨分表查詢耗時、ID 生成器 QPS/延遲、主從同步延遲。
copyright © 萬盛學電腦網 all rights reserved