萬盛學電腦網

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

MySQL 分表策略總結:為什麼分表、三大主流方案與實戰代碼

當單表資料達到百萬級,查詢效能驟降、鎖競爭劇增、備份維護困難,分表成為必經之路。本文系統梳理分表動機、三大主流架構方案(叢集、預分表、Merge 引擎)、核心路由算法與 PHP 實作代碼,並分析各方案優劣適用場景。

為什麼要分表?

  • 單表過大: 查詢掃描行數暴增,索引深度增加,I/O 放大。
  • 鎖競爭: MyISAM 表鎖、InnoDB 行鎖在高併發寫入下嚴重阻塞。
  • 維護困難: DDL 鎖表時間過長、備份/還原耗時、統計資訊更新慢。

核心目標:**縮短單次 SQL 執行時間,降低排隊等待,提升併發吞吐。**

方案一:MySQL 叢集/複寫(非真正分表,但能分流壓力)

技術棧

  • MySQL Cluster (NDB)
  • MySQL Replication (主從/雙主)
  • MySQL Proxy / ProxySQL(讀寫分離)
  • DRBD(共享存儲高可用)

原理

將讀請求分流到多個從庫,寫入集中主庫,減少單庫併發隊列長度。

優缺點

優點缺點
擴展性好、應用層無感(無需改代碼)單表資料量未減、單查詢耗時不變
高可用、災備內建硬體成本高(多台伺服器)、複寫延遲風險

方案二:應用層預分表(Hash/Range 路由)

核心思想

預估高增長表(如討論區貼文、聊天記錄),預先建立 N 張同結構表(message_00~message_99),應用層根據鍵值(如 user_id)計算目標表名。

PHP Hash 路由實作

<?php
function get_hash_table($table, $userid) {
    $str = crc32($userid);               // 取得 32 位整數
    if ($str < 0) {
        $hash = "0" . substr(abs($str), 0, 1); // 負數處理
    } else {
        $hash = substr($str, 0, 2);      // 取前兩位 00~99
    }
    return $table . "_" . $hash;
}

// 使用範例
echo get_hash_table('message', 'user18991'); // message_10
echo get_hash_table('message', 'user34523'); // message_13
?>

優缺點

優點缺點
單表資料量可控、查詢快、無中介層路由規則一旦確定極難變更(算法變更導致資料分佈錯亂)
架構簡單、無額外組件跨表查詢/統計極其痛苦(需 UNION 或應用層聚合)
擴展性差(擴容需重新分表遷移)

方案三:MERGE 存儲引擎(MyISAM 專用,透明聯合視圖)

原理

建立多張同結構 MyISAM 子表,再建一張 ENGINE=MERGE UNION=(t1,t2,...) INSERT_METHOD=LAST 總表。查詢總表自動路由到子表,寫入按 INSERT_METHOD 導向首/尾/不允許寫入。

建表示例

CREATE TABLE `user1` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT NULL,
  `sex` INT(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `user2` LIKE `user1`;

INSERT INTO `user1` (`name`, `sex`) VALUES ('張映', 0);
INSERT INTO `user2` (`name`, `sex`) VALUES ('tank', 1);

CREATE TABLE `alluser` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT NULL,
  `sex` INT(1) NOT NULL DEFAULT '0',
  INDEX(`id`)
) ENGINE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST;

SELECT id, name, sex FROM alluser;  -- 自動聯合查詢
INSERT INTO `alluser` (`name`, `sex`) VALUES ('tank2', 0);  -- 寫入 user2 (LAST)

優缺點

優點缺點
應用層幾乎無需改動(視為單表)僅支援 MyISAM,無事務、無行鎖、無外鍵
可解決「已有大表拆分不想改代碼」子表過多時開啟/關閉開銷大、檔案描述符耗盡風險
備份/還原可單獨操作子表不支援 REPLACE、AUTO_INCREMENT 衝突風險

方案選型建議

場景推薦方案備註
新專案、預估大量寫入、需事務應用層分表 + InnoDB配合 ShardingSphere、MyCat 等中間件
舊系統 MyISAM 大表、不想改代碼MERGE 引擎過渡方案,長期建議遷移 InnoDB+應用層分表
讀多寫少、可接受最終一致性主從複寫 + 讀寫分離ProxySQL 自動路由
需跨分片查詢/報表應用層分表 + 彙總表/數據倉庫定時同步至 ClickHouse/Doris

分表後的常見挑戰與對策

  • 全域唯一 ID: 使用 Snowflake 算法、UUID、或 auto_increment_offset/increment 避免主鍵衝突。
  • 跨分片事務: 避免分布式事務,改用最終一致性(事件驅動、Saga 模式)。
  • 分頁查詢: 禁止大偏移 LIMIT 100000, 20,改用游標/ID 續傳。
  • 擴容遷移: 雙寫遷移、影子表驗證、流量逐步切換。

分表是系統演進的必經階段,**沒有銀彈,只有權衡**。根據業務增長曲線、團隊技術棧、運維能力,選擇最合適的方案並建立完善的遷移預案,才能平滑度過資料量爆發期。

copyright © 萬盛學電腦網 all rights reserved