您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 相關知識

PHP 實作 MySQL 讀寫分離：一主多從架構完整代碼方案

編輯：PHP 相關知識

隨著流量增長，單一 MySQL 實例難以承受高併發讀寫壓力。主從複製 + 讀寫分離是經典擴展策略：寫入操作走 Master，讀取操作分流至多台 Slave。本文提供一個輕量級 PHP 類別，封裝連線管理、自動路由、故障切換核心邏輯，可直接整合進專案。

核心特性

一主多從，Slave 陣列可動態擴充
自動判斷 SQL 類型：SELECT 走 Slave，其餘走 Master
Slave 故障自動降級至 Master，不中斷服務
保留原生 mysqli 介面，支援預處理、事務、多查詢等所有功能
缺點：長連線模式下需同時維持 Master/Slave 連線

完整類別代碼 (RW_Mysqli.php)

<?php
class RW_Mysqli {
    private $master;
    private $slaves = [];
    private $slaveIndex = 0;
    private $lastUsed = 'master'; // 'master' | 'slave'

    // 設定：$masterCfg, $slaveCfgs 皆為 ['host'=>, 'user'=>, 'pass'=>, 'db'=>, 'port'=>3306]
    public function __construct(array $masterCfg, array $slaveCfgs = []) {
        $this->master = $this->connect($masterCfg);
        foreach ($slaveCfgs as $cfg) {
            $link = $this->connect($cfg);
            if ($link) $this->slaves[] = $link;
        }
    }

    private function connect(array $cfg) {
        $mysqli = @new mysqli($cfg['host'], $cfg['user'], $cfg['pass'], $cfg['db'], $cfg['port'] ?? 3306);
        if ($mysqli->connect_errno) {
            error_log("MySQL 連線失敗 [{$cfg['host']}]: " . $mysqli->connect_error);
            return false;
        }
        $mysqli->set_charset('utf8mb4');
        return $mysqli;
    }

    // 核心：取得執行查詢的連線
    private function getLink(string $sql): mysqli {
        $isSelect = (stripos(trim($sql), 'SELECT') === 0);
        if ($isSelect && $this->slaves) {
            // 簡單輪詢
            $link = $this->slaves[$this->slaveIndex];
            $this->slaveIndex = ($this->slaveIndex + 1) % count($this->slaves);
            // 檢查連線存活
            if (!$link->ping()) {
                error_log("Slave {$link->host_info} ping 失敗，降級至 Master");
                return $this->master;
            }
            $this->lastUsed = 'slave';
            return $link;
        }
        $this->lastUsed = 'master';
        return $this->master;
    }

    // 對外介面：完全模擬 mysqli 常用方法
    public function query(string $sql) {
        return $this->getLink($sql)->query($sql);
    }
    public function prepare(string $sql) {
        return $this->getLink($sql)->prepare($sql);
    }
    public function begin_transaction() { return $this->master->begin_transaction(); }
    public function commit() { return $this->master->commit(); }
    public function rollback() { return $this->master->rollback(); }
    public function affected_rows() { return $this->master->affected_rows; }
    public function insert_id() { return $this->master->insert_id; }
    public function close() {
        $this->master->close();
        foreach ($this->slaves as $s) $s->close();
    }
    // 魔術方法代理其餘 mysqli 方法至 Master
    public function __call($method, $args) {
        return $this->master->$method(...$args);
    }
    public function getLastUsed(): string { return $this->lastUsed; }
}
?>

使用範例

<?php
require 'RW_Mysqli.php';

$master = ['host'=>'10.0.0.1', 'user'=>'rw', 'pass'=>'secret', 'db'=>'app'];
$slaves = [
    ['host'=>'10.0.0.2', 'user'=>'ro', 'pass'=>'secret', 'db'=>'app'],
    ['host'=>'10.0.0.3', 'user'=>'ro', 'pass'=>'secret', 'db'=>'app'],
];

$db = new RW_Mysqli($master, $slaves);

// 寫入 → Master
$db->query("INSERT INTO logs (msg) VALUES ('訪問首頁')");

// 讀取 → Slave (輪詢)
$res = $db->query("SELECT * FROM logs ORDER BY id DESC LIMIT 10");
while ($row = $res->fetch_assoc()) { /* ... */ }

// 預處理語句同樣自動路由
$stmt = $db->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param('s', $email);
$stmt->execute();

// 事務強制走 Master
$db->begin_transaction();
try {
    $db->query("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    $db->query("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
    $db->commit();
} catch (Exception $e) {
    $db->rollback();
}
?>