使用 PHP 實現 MySQL 讀寫分離

PHP MySQL 讀寫分離類別實作：基於 UCHome 改進

高併發場景下，單一 MySQL 實例難以承受讀寫壓力。讀寫分離架構：一主庫寫入、多從庫讀取，顯著提升查詢吞吐。本文提供輕量級 PHP 實現。

核心特性

• 簡單的讀寫分離邏輯：SELECT 走從庫，其餘走主庫
• 支援一主多從，隨機選擇從庫連接
• 兼容原有代碼，無侵入式改造
• 缺點：每次請求同時連接主從兩庫 (可優化為延遲連接)

完整類別代碼 (mysql_rw_php.class.php)

<?php
class mysql_rw_php {
    var $querynum = 0;
    var $link = null;
    var $charset;
    var $cur_db = '';
    var $ro_exist = false;
    var $link_ro = null;
    var $link_rw = null;

    function connect($dbhost, $dbuser, $dbpw, $dbname = '', $pconnect = 0, $halt = TRUE) {
        if ($pconnect) {
            $this->link = @mysql_pconnect($dbhost, $dbuser, $dbpw);
        } else {
            $this->link = @mysql_connect($dbhost, $dbuser, $dbpw);
        }
        if (!$this->link && !$halt) return false;
        
        // 首次連接作為主庫
        if ($this->link_rw == null) $this->link_rw = $this->link;
        
        if ($this->version() > '4.1') {
            if ($this->charset) {
                @mysql_query("SET character_set_connection=$this->charset, character_set_results=$this->charset, character_set_client=binary", $this->link);
            }
            if ($this->version() > '5.0.1') {
                @mysql_query("SET sql_mode=''", $this->link);
            }
        }
        if ($dbname) $this->select_db($dbname);
    }

    // 連接從庫
    function connect_ro($dbhost, $dbuser, $dbpw, $dbname = '', $pconnect = 0) {
        if ($this->link_rw == null) $this->link_rw = $this->link;
        $this->link = null;
        $this->connect($dbhost, $dbuser, $dbpw, $dbname, $pconnect, false); // 不報錯
        
        if ($this->link) {
            $this->ro_exist = true;
            $this->link_ro = $this->link;
            if ($this->cur_db) @mysql_select_db($this->cur_db, $this->link_ro);
        } else {
            $this->link = &$this->link_rw; // 失敗回退主庫
        }
    }

    // 設置從庫列表並隨機連接一個
    function set_ro_list($ro_list) {
        if (is_array($ro_list)) {
            $link_ro = $ro_list[array_rand($ro_list)];
            $this->connect_ro($link_ro['dbhost'], $link_ro['dbuser'], $link_ro['dbpw']);
        }
    }

    function select_db($dbname) {
        $this->cur_db = $dbname;
        if ($this->ro_exist) @mysql_select_db($dbname, $this->link_ro);
        return @mysql_select_db($dbname, $this->link_rw);
    }

    function query($sql, $type = '') {
        $this->link = &$this->link_rw; // 預設主庫
        
        // 讀寫分離核心邏輯
        if ($this->ro_exist && preg_match("/^(\s*)select/i", $sql)) {
            $this->link = &$this->link_ro; // SELECT 走從庫
        }
        
        $func = ($type == 'UNBUFFERED' && function_exists('mysql_unbuffered_query')) 
            ? 'mysql_unbuffered_query' : 'mysql_query';
        
        if (!($query = $func($sql, $this->link)) && $type != 'SILENT') {
            $this->halt('MySQL Query Error', $sql);
        }
        $this->querynum++;
        return $query;
    }

    // 其他委託方法...
    function fetch_array($query, $result_type = MYSQL_ASSOC) { return mysql_fetch_array($query, $result_type); }
    function fetch_one_array($sql, $type = '') { return $this->fetch_array($this->query($sql, $type)); }
    function affected_rows() { return mysql_affected_rows($this->link); }
    function error() { return ($this->link) ? mysql_error($this->link) : mysql_error(); }
    function errno() { return intval(($this->link) ? mysql_errno($this->link) : mysql_errno()); }
    function result($query, $row) { return @mysql_result($query, $row); }
    function num_rows($query) { return mysql_num_rows($query); }
    function num_fields($query) { return mysql_num_fields($query); }
    function free_result($query) { return mysql_free_result($query); }
    function insert_id() { return ($id = mysql_insert_id($this->link)) >= 0 ? $id : $this->result($this->query("SELECT last_insert_id()"), 0); }
    function fetch_row($query) { return mysql_fetch_row($query); }
    function fetch_fields($query) { return mysql_fetch_field($query); }
    function version() { return mysql_get_server_info($this->link); }
    function close() { return mysql_close($this->link); }
    function halt($message = '', $sql = '') {
        echo "<div style='position:absolute;font-size:11px;background:#EBEBEB;padding:0.5em;'>
            <b>MySQL Error</b><br>Message: $message<br>SQL: $sql<br>Error: " . $this->error() . "<br>Errno: " . $this->errno() . "</div>";
        exit();
    }
}

使用範例

<?php
require_once 'mysql_rw_php.class.php';

$db = new mysql_rw_php();
// 連接主庫
$db->connect('master.db.host', 'user', 'pass', 'dbname');

// 設置從庫列表 (可配置多個)
$slaves = array(
    array('dbhost' => 'slave1.db.host', 'dbuser' => 'user', 'dbpw' => 'pass'),
    array('dbhost' => 'slave2.db.host', 'dbuser' => 'user', 'dbpw' => 'pass'),
);
$db->set_ro_list($slaves); // 隨機選一個連接

// 業務代碼無感知
$users = $db->fetch_one_array("SELECT * FROM users WHERE status=1"); // 走從庫
$db->query("UPDATE users SET last_login=NOW() WHERE id=1");         // 走主庫

架構圖解

應用層
    │
    ├── mysql_rw_php 實例
    │       │
    │       ├── $link_rw (主庫連接) ← INSERT/UPDATE/DELETE
    │       │
    │       └── $link_ro (從庫連接) ← SELECT (隨機負載均衡)
    │
    ↓
MySQL 主庫 (寫入) ── 同步複製 ──→ MySQL 從庫1 (讀取)
                              └──→ MySQL 從庫2 (讀取)

現代化改進建議

生產級替代方案

• ProxySQL：MySQL 中間件，透明讀寫分離、查詢重寫、連接池
• MyCat / ShardingSphere：分庫分表中間件
• PDO + 隨機從庫 DSN：應用層簡單實現
• Laravel/ThinkPHP 內建：框架級讀寫分離配置

此類別作為早期 PHP 讀寫分離參考實現，理解其核心邏輯（SQL 解析分發、多連接管理）有助於掌握資料庫架構演進脈絡。