Yii 2 實現多資料庫主從讀寫分離：DbConnection 元件擴充實作

需求背景

高流量 Yii 2 應用需將寫入操作導向 Master，讀取操作分散至多台 Slave，並支援故障切換：

• Master 掛掉 → Slave 升級可寫 (可選)
• 所有 Slave 掛掉 → Master 降級可讀 (可選)
• Slave 連線失敗 → 暫時標記失效，N 秒內不重試

核心元件：DbConnectionMan

<?php
namespace app\components;

use Yii;
use yii\db\Connection;
use yii\db\Exception;
use yii\caching\CacheInterface;

class DbConnectionMan extends Connection {
    // 從庫設定陣列，每項符合 Connection 設定
    public $slaves = [];
    
    // 是否啟用從庫
    public $enableSlave = true;
    
    // Master 不可用時，允許 Slave 寫入
    public $slavesWrite = false;
    
    // 所有 Slave 不可用時，允許 Master 讀取
    public $masterRead = false;
    
    // 連線逾時 (秒)
    public $timeout = 10;
    
    // Slave 失效標記快取秒數
    public $markDeadSeconds = 600;
    
    // 快取元件 ID
    public $cacheID = 'cache';
    
    private $_slave = null;
    private $_disableWrite = true; // Slave 預設唯讀

    /**
     * 重寫 createCommand：讀操作導向 Slave
     */
    public function createCommand($sql = null) {
        if ($this->enableSlave 
            && !empty($this->slaves) 
            && is_string($sql) 
            && !$this->getTransaction() 
            && self::isReadOperation($sql) 
            && ($slave = $this->getSlave())
        ) {
            return $slave->createCommand($sql);
        }
        
        // 寫操作或無可用 Slave
        if (!$this->masterRead && $this->_disableWrite && !self::isReadOperation($sql)) {
            throw new Exception('Master DB unavailable! Write operation on slave disabled.');
        }
        return parent::createCommand($sql);
    }

    /**
     * 取得可用 Slave 連線 (含快取失效檢查、隨機負載均衡)
     */
    public function getSlave(): ?Connection {
        if ($this->_slave !== null) {
            return $this->_slave;
        }
        
        // 隨機打亂，簡易負載均衡
        $slaves = $this->slaves;
        shuffle($slaves);
        
        $cache = Yii::$app->get($this->cacheID);
        
        foreach ($slaves as $config) {
            $dsn = $config['dsn'] ?? ($config['connectionString'] ?? '');
            if (!$dsn) continue;
            
            // 檢查快取標記
            if ($cache instanceof CacheInterface && $cache->get("DeadServer::$dsn") === 1) {
                continue;
            }
            
            // 建立連線
            $slaveConfig = array_merge([
                'class' => Connection::class,
                'enableSlave' => false, // 避免遞迴
                'autoConnect' => false,
            ], $config);
            
            try {
                /** @var Connection $slave */
                $slave = Yii::createObject($slaveConfig);
                $slave->setAttribute(\PDO::ATTR_TIMEOUT, $this->timeout);
                $slave->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
                $slave->open();
                
                $this->_slave = $slave;
                return $slave;
            } catch (\Exception $e) {
                // 標記失效
                if ($cache instanceof CacheInterface) {
                    $cache->set("DeadServer::$dsn", 1, $this->markDeadSeconds);
                }
                Yii::warning("Slave connection failed: $dsn - " . $e->getMessage());
                continue;
            }
        }
        
        // 全部失效
        $this->_slave = null;
        $this->enableSlave = false;
        return null;
    }

    /**
     * 重寫 setActive：Master 連線失敗時嘗試切換
     */
    public function setActive($value) {
        if ($value !== $this->isActive) {
            if ($value) {
                // 嘗試連線 Master
                try {
                    if ($this->isDeadServer($this->connectionString)) {
                        throw new Exception('Master already marked dead');
                    }
                    parent::setActive(true);
                } catch (\Exception $e) {
                    // Master 失效，嘗試 Slave
                    $slave = $this->getSlave();
                    if ($slave) {
                        // 切換連線參數至 Slave
                        $this->connectionString = $slave->connectionString;
                        $this->username = $slave->username;
                        $this->password = $slave->password;
                        if ($this->slavesWrite) {
                            $this->_disableWrite = false;
                        }
                        parent::setActive(true);
                    } elseif ($this->masterRead) {
                        // 僅允許 Master 讀取
                        parent::setActive(true);
                    } else {
                        throw new Exception('No database available: ' . $e->getMessage());
                    }
                }
            } else {
                parent::setActive(false);
                $this->_slave = null; // 重置
            }
        }
    }

    /**
     * 判斷 SQL 是否為讀操作
     */
    public static function isReadOperation(string $sql): bool {
        $sql = ltrim($sql);
        $firstWord = strtoupper(substr($sql, 0, 6)); // 取前 6 字元足夠
        return in_array($firstWord, ['SELECT', 'SHOW ', 'DESCR', 'EXPLA', 'PRAGM', 'WITH ']); // WITH 為 CTE
    }

    private function isDeadServer(string $dsn): bool {
        $cache = Yii::$app->get($this->cacheID);
        return $cache instanceof CacheInterface && $cache->get("DeadServer::$dsn") === 1;
    }
}

設定檔 (config/main.php)

return [
    'components' => [
        'cache' => [
            'class' => 'yii\caching\FileCache', // 或 RedisCache
        ],
        'db' => [
            'class' => 'app\components\DbConnectionMan',
            'dsn' => 'mysql:host=192.168.1.10;dbname=myapp', // Master
            'username' => 'root',
            'password' => 'password',
            'charset' => 'utf8mb4',
            'tablePrefix' => 'tbl_',
            
            // 讀寫分離設定
            'enableSlave' => true,
            'slavesWrite' => true,   // Master 掛掉允許 Slave 寫入
            'masterRead' => true,    // Slave 全掛允許 Master 讀取
            'timeout' => 5,
            'markDeadSeconds' => 300,
            
            'slaves' => [
                [
                    'dsn' => 'mysql:host=192.168.1.11;dbname=myapp',
                    'username' => 'root',
                    'password' => 'password',
                    'charset' => 'utf8mb4',
                    'tablePrefix' => 'tbl_',
                ],
                [
                    'dsn' => 'mysql:host=192.168.1.12;dbname=myapp',
                    'username' => 'root',
                    'password' => 'password',
                    'charset' => 'utf8mb4',
                    'tablePrefix' => 'tbl_',
                ],
            ],
        ],
    ],
];

使用方式

<?php
// 寫操作 → 自動導向 Master
Yii::$app->db->createCommand()
    ->insert('user', ['name' => 'John', 'email' => 'john@example.com'])
    ->execute();

// 讀操作 → 自動導向 Slave (隨機)
$users = Yii::$app->db->createCommand('SELECT * FROM user WHERE status=1')
    ->queryAll();

// 交易中強制 Master
$transaction = Yii::$app->db->beginTransaction();
try {
    // 交易內的 SELECT 也會走 Master (getTransaction() 非 null)
    $user = User::findOne(1);
    $user->balance -= 100;
    $user->save();
    $transaction->commit();
} catch (\Exception $e) {
    $transaction->rollBack();
}

關鍵設計說明

生產環境建議

• 使用 Redis 快取 取代 FileCache，跨多台 Web Server 共享失效狀態
• 監控 DeadServer 快取 Key，設定告警
• Slave 同步延遲監控：SHOW SLAVE STATUS 的 Seconds_Behind_Master
• 考慮引入 ProxySQL 或 MyCat 中介層，將讀寫分離下沉至基礎設施層