PHP MySQL 資料庫分表策略與實作完整指南

PHP MySQL 資料庫分表策略與實作完整指南

當單表資料超過 100 萬筆，查詢效能明顯下降，需考慮分表或分區。本文詳述基於「自增 ID 尾數取模」的水平分表方案，包含建表、合併表、ID 產生器、寫入/查詢/更新流程。

1. 建立 10 張分表

CREATE TABLE `article_0` (
  `id` BIGINT(20) NOT NULL,
  `subject` VARCHAR(200) NOT NULL,
  `content` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM CHARSET=utf8;
-- 依此類推建立 article_1 ~ article_9

關鍵點：id 不設自增，所有表結構必須完全一致（欄位順序、型別、長度）。

2. 建立合併表（MRG_MyISAM）供查詢

CREATE TABLE `article` (
  `id` BIGINT(20) NOT NULL,
  `subject` VARCHAR(200) NOT NULL,
  `content` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8
INSERT_METHOD=0
UNION=(`article_0`,`article_1`,...`article_9`);

INSERT_METHOD=0 禁止直接對合併表寫入，僅用於 SELECT。

3. 專用 ID 產生表

CREATE TABLE `create_id` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=MYISAM;

4. PHP 核心邏輯

<?php
function get_AI_ID() {
    $this->db->query("INSERT INTO create_id (id) VALUES('')");
    return $this->db->insertID();
}

function get_Table_Name($id) {
    return 'article_' . intval($id) % 10;
}

function new_Article() {
    $id = $this->get_AI_ID();
    $table = $this->get_Table_Name($id);
    $sql = "INSERT INTO {$table} (id, subject, content) VALUES('{$id}', '測試標題', '測試內容')";
    $this->db->query($sql);
}

5. 更新與查詢

• UPDATE：同樣先算 id % 10 定位分表，再執行更新
• SELECT：直接查詢合併表 article，MySQL 自動路由到對應分表

擴充建議

• 用戶表：核心欄位（id、username、password）放主表，擴充資訊依 user_id 分表
• 分表依據：可依業務改為年份、月份、地區等，核心邏輯不變

分表雖增加維護複雜度，但能線性擴充寫入吞吐量，是大流量系統常用手段。