萬盛學電腦網

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

MySQL 分表策略全解析:三大方案優劣對比與選型建議

什麼時候需要分表?

單表超過 2000 萬行、查詢延遲明顯、索引維護成本過高時,應考慮分表。中小型專案通常不需要。

方案一:資料庫叢集/讀寫分離(架構層解決)

技術棧

  • MySQL Replication(主從同步)
  • MySQL Router / ProxySQL(讀寫分離)
  • MySQL Group Replication / InnoDB Cluster(高可用叢集)

優點

  • 應用層無感,SQL 無需改動
  • 擴展性好,可水平增加從庫分擔讀壓力

缺點

  • 單表資料量未減少,寫入效能瓶頸依然存在
  • 硬體成本高,主從同步有延遲風險
  • 不解決單表過大導致的 DDL 鎖表、備份慢等問題

方案二:應用層預分表(Sharding)

核心邏輯

根據業務鍵(user_id、order_id、日期等)哈希或取模,路由到固定分表:

-- PHP 範例:CRC32 哈希取模 100 表
function getTable($base, $userId, $total = 100) {
    $hash = crc32($userId);
    $idx = $hash < 0 ? abs($hash) : $hash;
    return sprintf('%s_%02d', $base, $idx % $total);
}
// getTable('message', 'user18991') → 'message_10'

優點

  • 單表資料量可控,查詢/索引/備份效能大幅提升
  • 無中間件依賴,架構簡單

缺點

  • 擴容極其痛苦:改算法需全量遷移資料,停機時間長
  • 跨分表查詢、事務、JOIN 極其複雜
  • 業務邏輯耦合分表邏輯,維護成本高

方案三:MERGE 引擎(MyISAM 限定)

原理

建立多個結構相同的 MyISAM 子表,再建一個 MERGE 總表映射它們:

CREATE TABLE user_1 (...) ENGINE=MyISAM;
CREATE TABLE user_2 (...) ENGINE=MyISAM;
CREATE TABLE all_user (...) ENGINE=MERGE UNION=(user_1,user_2) INSERT_METHOD=LAST;

優點

  • 應用層只操作 all_user,改動極小
  • 可將歷史大表拆分後透過 MERGE 統一訪問

缺點

  • 僅支援 MyISAM,無事務、無外鍵、表鎖並發差
  • 無法跨子表保證唯一約束
  • 子表結構不一致會導致詭異錯誤
  • REPLACE、ALTER TABLE 等操作受限
  • MySQL 8.0 已棄用 MERGE,不建議新專案採用

綜合選型建議

場景推薦方案
讀多寫少、單表未超 5000 萬主從讀寫分離 + 索引優化
寫入量大、單表增長快、需水平擴展中間件分表
舊系統 MyISAM 大表、不想改代碼MERGE 過渡,規劃遷移 InnoDB + 中間件
新專案預期超大規模初期預分表 + 遷移方案,或直接上 TiDB/ShardingSphere

避坑原則

  • 分表數量不宜過多(建議 < 1024),否則檔案描述符、元資料查詢成為新瓶頸
  • 務必預留擴容方案(如預分 1024 表,初期只用 64 表)
  • 跨分表事務採用最終一致性(TCC、Saga)而非強一致
copyright © 萬盛學電腦網 all rights reserved