當單表資料達到百萬級,查詢效能驟降、鎖競爭劇增、備份維護困難,分表成為必經之路。本文系統梳理分表動機、三大主流架構方案(叢集、預分表、Merge 引擎)、核心路由算法與 PHP 實作代碼,並分析各方案優劣適用場景。
核心目標:**縮短單次 SQL 執行時間,降低排隊等待,提升併發吞吐。**
將讀請求分流到多個從庫,寫入集中主庫,減少單庫併發隊列長度。
| 優點 | 缺點 |
|---|---|
| 擴展性好、應用層無感(無需改代碼) | 單表資料量未減、單查詢耗時不變 |
| 高可用、災備內建 | 硬體成本高(多台伺服器)、複寫延遲風險 |
預估高增長表(如討論區貼文、聊天記錄),預先建立 N 張同結構表(message_00~message_99),應用層根據鍵值(如 user_id)計算目標表名。
<?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 或應用層聚合) |
| 擴展性差(擴容需重新分表遷移) |
建立多張同結構 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 |
auto_increment_offset/increment 避免主鍵衝突。LIMIT 100000, 20,改用游標/ID 續傳。分表是系統演進的必經階段,**沒有銀彈,只有權衡**。根據業務增長曲線、團隊技術棧、運維能力,選擇最合適的方案並建立完善的遷移預案,才能平滑度過資料量爆發期。