多租戶架構生存指南：一個資料庫一個機構的愛與愁

🎯 前言

「為什麼不用 row-level multi-tenancy？每個表加一個 org_id 欄位就好了，幹嘛這麼複雜？」

這是我被問過最多次的問題。

我維護的 AppSystem 採用的是 database-per-tenant 架構：每個機構（OrganizationA、OrganizationB…）都有自己獨立的資料庫實例。這個設計在當初有其理由，但也帶來了一系列的維護挑戰。

這篇文章想分享這個架構的真實維護經驗——不只是優點，更多是實際遇到的坑，以及我們如何應對的。

🏗️ 架構選擇的背景

為什麼選 database-per-tenant？

AppSystem 最初設計時，選擇 database-per-tenant 有幾個主要原因：

1. 資料隔離的強硬需求

系統服務的是多個機構，各機構之間的資料需要完全隔離。

database-per-tenant 提供了最強的隔離保證：

  • 一個機構的操作不可能影響另一個機構的資料
  • 不需要在每個 SQL 查詢中加 WHERE org_id = ?
  • 資料庫備份/還原以機構為單位，靈活性高

2. 歷史原因

系統最初只服務單一機構，後來才擴展到多機構。 每個新機構就是複製一份現有資料庫結構，這個模式延續下來了。

3. 各機構的客製化需求

有些機構需要特殊的欄位或報表格式。 database-per-tenant 讓我們可以在特定機構的資料庫中加欄位， 而不影響其他機構。（後來這個「優點」變成了「痛點」，後面會說到）

⚙️ Session 動態切換資料庫

多租戶系統最核心的機制是：同一套程式碼，根據當前登入的機構，切換到對應的資料庫。

資料庫連線架構

AppSystem 使用一個自訂的資料庫連線類別 DBPDO，負責管理連線池：

class DBPDO {
    private static $connections = [];

    public static function getConnection(string $db_name): PDO {
        if (!isset(self::$connections[$db_name])) {
            self::$connections[$db_name] = new PDO(
                "mysql:host=localhost;dbname={$db_name};charset=utf8mb4",
                DB_USER,   // dbuser
                DB_PASS    // dbpass
            );
        }
        return self::$connections[$db_name];
    }
}

連線池設計的好處：同一個 request 中，如果需要多次查詢同一個資料庫，不會重複建立連線。

Session 中的機構資訊

用戶登入後，Session 中會儲存：

// 登入成功後設定
$_SESSION['mcareID']    = $user['id'];
$_SESSION['mcareDBno']  = $org['db_number'];  // 對應到 demo-org-{id}
$_SESSION['mOrgID']     = $org['id'];
$_SESSION['mOrgName']   = $org['name'];

業務程式的使用方式

在業務邏輯中，取得資料庫連線的方式：

// 業務程式不直接寫死資料庫名稱
$db_name = 'demo-org-' . $_SESSION['mcareDBno'];
$pdo = DBPDO::getConnection($db_name);

// 查詢
$stmt = $pdo->prepare("SELECT * FROM typeB_records WHERE id = ?");
$stmt->execute([$record_id]);
$data = $stmt->fetch(PDO::FETCH_ASSOC);

跨資料庫查詢

有些功能需要同時查詢多個機構的資料（例如全系統的統計報表）。 這時候 PHP 會逐一連接每個機構的資料庫：

$org_list = getAllOrganizations(); // 從主設定資料庫取得機構清單

$all_data = [];
foreach ($org_list as $org) {
    $db_name = 'demo-org-' . $org['db_number'];
    $pdo = DBPDO::getConnection($db_name);
    $records = getRecords($pdo, $date_range);
    $all_data[$org['name']] = $records;
}

😤 真正的痛點：Schema Migration 同步

這是 database-per-tenant 最大的維護挑戰。

問題的根源

我們目前有約 20 個機構，每個機構都有獨立的資料庫。 當系統需要變更資料表結構（新增欄位、修改索引、新增資料表）， 就需要對 20 個資料庫 執行相同的 migration。

聽起來只是「跑 20 次 SQL」的問題，但實際上遠比這複雜。

挑戰一：各機構版本不同步

由於歷史原因，各機構的資料庫結構不完全相同：

  • 有些機構申請了額外功能，資料表有額外欄位
  • 有些舊機構從來沒有執行某些歷史 migration
  • 有些機構在測試期間手動改過資料表結構

這代表你不能假設「所有機構的資料表結構都一樣」。 執行 migration 前，必須先確認每個機構的當前狀態。

挑戰二：Migration 失敗的處理

如果 20 個機構的 migration，第 15 個失敗了怎麼辦？

前 14 個已經執行了，第 15-20 個還沒執行。 這個中間狀態很難處理，尤其是當 migration 包含了資料轉換（不只是 DDL）的時候。

挑戰三：MySQL 5.7 的限制

AppSystem 使用 MySQL 5.7，有一些 DDL 操作在 5.7 上的行為和 8.x 不同：

  • ALTER TABLE 在 5.7 會鎖表，在大資料量時影響很大
  • JSON 欄位的某些操作在 5.7 有限制
  • 部分欄位類型的預設值規則不同

🛠️ 我們的解決方案

方案一：批次執行腳本

我們有一套 shell 腳本，可以對所有機構批次執行 SQL：

#!/bin/bash
# 對所有機構執行同一個 SQL migration

SQL_FILE="$1"
LOG_FILE="migration_$(date +%Y%m%d_%H%M%S).log"

# 取得所有機構清單
ORG_LIST=$(mysql -u dbuser -pdbpass system_main -e \
  "SELECT db_number FROM organizations WHERE status='active'" -N)

for ORG_NO in $ORG_LIST; do
    DB_NAME="demo-org-${ORG_NO}"
    echo "執行 ${DB_NAME}..." | tee -a "$LOG_FILE"

    mysql -u dbuser -pdbpass "$DB_NAME" < "$SQL_FILE" 2>&1 \
        | tee -a "$LOG_FILE"

    if [ ${PIPESTATUS[0]} -ne 0 ]; then
        echo "❌ ${DB_NAME} 失敗！請手動檢查。" | tee -a "$LOG_FILE"
        # 不中斷，繼續執行其他機構
    else
        echo "✅ ${DB_NAME} 成功" | tee -a "$LOG_FILE"
    fi
done

echo "完成。結果記錄在 ${LOG_FILE}"

方案二：Idempotent Migration

所有的 migration SQL 都設計成冪等的（可以重複執行、結果相同）：

-- ❌ 不好：執行第二次會報錯
ALTER TABLE typeB_records ADD COLUMN new_field VARCHAR(255);

-- ✅ 好：有欄位就跳過，沒有就新增
ALTER TABLE typeB_records
ADD COLUMN IF NOT EXISTS new_field VARCHAR(255) DEFAULT '';

-- ❌ 不好：索引已存在時報錯
CREATE INDEX idx_date ON typeB_records(date);

-- ✅ 好：索引已存在就不動
CREATE INDEX IF NOT EXISTS idx_date ON typeB_records(date);

注意：MySQL 5.7 不支援 ADD COLUMN IF NOT EXISTS，需要用 stored procedure 或 conditional SQL 實作。在 5.7 上，我們通常用這個方式：

-- MySQL 5.7 的 IF NOT EXISTS 替代方案
SET @dbname = DATABASE();
SET @tablename = 'typeB_records';
SET @columnname = 'new_field';

SET @preparedStatement = (
    SELECT IF(
        (SELECT COUNT(*) FROM information_schema.columns
         WHERE table_schema = @dbname
         AND table_name = @tablename
         AND column_name = @columnname) = 0,
        'ALTER TABLE typeB_records ADD COLUMN new_field VARCHAR(255) DEFAULT ""',
        'SELECT "Column already exists"'
    )
);

PREPARE stmt FROM @preparedStatement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

複雜，但可靠。

方案三：Migration 版本追蹤

我們在每個機構的資料庫中都有一個 schema_versions 表：

CREATE TABLE IF NOT EXISTS schema_versions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version VARCHAR(20) NOT NULL,
    description VARCHAR(255),
    applied_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_version (version)
);

執行 migration 前，先檢查這個機構是否已經執行過：

# 檢查 demo-org-1 是否已執行 v2.5.0 的 migration
APPLIED=$(mysql -u dbuser -pdbpass demo-org-1 -e \
  "SELECT COUNT(*) FROM schema_versions WHERE version='v2.5.0'" -N)

if [ "$APPLIED" -eq "0" ]; then
    # 執行 migration
    mysql -u dbuser -pdbpass demo-org-1 < migration_v2.5.0.sql
    # 記錄已執行
    mysql -u dbuser -pdbpass demo-org-1 -e \
      "INSERT INTO schema_versions (version, description) VALUES ('v2.5.0', '新增 new_field 欄位')"
fi

📊 與 Row-Level Tenancy 的比較

回到開頭的問題：「為什麼不用 row-level？」

老實說，在維護了幾年 database-per-tenant 之後，我有時候也會想這個問題。

面向Database-per-tenantRow-level Tenancy
資料隔離強（OS 層面的隔離）弱（應用層面，依賴 WHERE 條件）
跨機構查詢複雜（需要逐一連接）簡單（一個查詢搞定）
Schema Migration複雜（需要跑 N 次）簡單（一次搞定）
機構個別客製化容易（直接改該機構的DB）複雜（需要 EAV 或 JSONB）
備份/還原靈活（以機構為單位）複雜（資料混在一起）
新增機構複製一份 schema只需要新增 org 紀錄
水平擴展容易（不同機構分不同 DB server）較複雜（需要分片策略）

對於 AppSystem 的使用情境，database-per-tenant 的優點（強隔離、備份彈性、個別客製化）是關鍵需求，所以當初的選擇是合理的。

但如果讓我重新設計，我會更謹慎地考慮各機構「客製化」的邊界，避免讓各機構的 schema 差異越來越大。

🚨 遇過的真實事故

事故 1：忘記對某個機構執行 Migration

情境：新增一個欄位，批次腳本執行時，其中一個機構因為資料庫鎖定而失敗，但沒有被注意到。

症狀：那個機構的某個功能出現 Unknown column 'xxx' in field list 錯誤。

教訓：Migration 完成後，要用 SQL 驗證所有機構的 schema 確實一致：

-- 檢查所有機構的 typeB_records 是否都有 new_field 欄位
SELECT
    TABLE_SCHEMA as db_name,
    COLUMN_NAME,
    COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'typeB_records'
AND COLUMN_NAME = 'new_field'
ORDER BY TABLE_SCHEMA;

如果這個查詢的結果數量不等於機構總數，代表有機構沒有執行成功。

事故 2：Session 切換資料庫失效

情境：PHP 程式中，在 Session 更新後，某個函式仍然在查詢舊的資料庫。

根本原因：某個舊的函式有一個「本地快取」的資料庫連線，沒有透過 DBPDO::getConnection() 取得，而是直接使用了一個 module 層級的 $db 變數。

教訓：CLAUDE.md 中明確記錄這個問題，告知 Claude Code 不要直接使用本地 $db 變數，一律透過 DBPDO::getConnection() 取得連線。

💡 給正在考慮這個架構的人

如果你正在設計一個多租戶系統，以下幾點值得考慮：

1. 預先規劃 Schema 版本管理

不要等到有 10+ 個機構才開始思考 migration 策略。 從第一個機構開始就建立 schema_versions 追蹤，養成 idempotent SQL 的習慣。

2. 嚴格控制各機構的 Schema 差異

每一個「只在某機構部署的欄位」，未來都是維護成本。 盡量讓所有機構的 schema 保持一致，特殊需求用 JSON 欄位或設定表處理。

3. 建立自動化驗證

# 定期執行：驗證所有機構的 schema 版本
for ORG_NO in $(get_all_org_numbers); do
    LATEST_VERSION=$(get_latest_migration_version)
    APPLIED_VERSION=$(get_org_schema_version $ORG_NO)

    if [ "$LATEST_VERSION" != "$APPLIED_VERSION" ]; then
        echo "警告：demo-org-${ORG_NO} 的 schema 版本落後！"
        echo "  目前版本：${APPLIED_VERSION}"
        echo "  最新版本：${LATEST_VERSION}"
    fi
done

4. Migration 一定要先在測試環境驗證

特別是對 MySQL 5.7，某些 DDL 操作的行為和你預期的可能不同。 先在測試環境的所有機構跑一遍，確認沒問題再上正式環境。

🎉 結語

Database-per-tenant 不是一個完美的架構，但在特定情境下（強隔離需求、大量個別客製化），它是合理的選擇。

關鍵在於：接受它帶來的維護成本，並建立對應的工具和流程來管理它。

批次執行腳本、idempotent migration、schema 版本追蹤——這些不是「過度工程」，而是讓 database-per-tenant 可持續運作的必要基礎建設。

如果你也在維護類似的架構，希望這篇文章能讓你少踩一些坑。

📎 相關文章：