Yii 實現 MySQL 多資料庫與讀寫分離實戰

Yii 實現 MySQL 多資料庫與讀寫分離實戰

針對高併發大數據場景，利用 Yii 事件驅動機制實現多庫切換與主從讀寫分離。

架構設計

• 透過 CActiveRecord 重寫 getDbConnection() 切換資料庫連線
• 利用 beforeSave、beforeDelete、beforeFind 事件自動切換讀寫庫
• 兩個設定檔：DBConfig.php（主從連線參數）、ModelConfig.php（Model 對應資料庫）

關鍵程式碼

DBConfig.php

return array(
    'passport' => array(
        'write' => array(
            'class' => 'CDbConnection',
            'connectionString' => 'mysql:host=10.1.39.2;dbname=db1',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8',
        ),
        'read' => array(
            array('connectionString' => 'mysql:host=10.1.39.3;dbname=db1', ...),
            array('connectionString' => 'mysql:host=10.1.39.4;dbname=db3', ...),
        ),
    ),
);

ModelConfig.php

return array(
    'passport' => array('User', 'Post'),
    'microblog' => array('...'),
);

ActiveRecord 基底類別

class ActiveRecord extends CActiveRecord {
    public $modelConfig = '';
    public $dbConfig = '';
    static $dataBase = array();
    public $dbName = '';
    public $dbType = 'read';

    public function __construct($scenario = 'insert', $dbname = '') {
        if (!empty($dbname)) $this->dbName = $dbname;
        parent::__construct($scenario);
    }

    public function getDbConnection() {
        if (isset(self::$dataBase[$this->dbName])) return self::$dataBase[$this->dbName];
        if ($this->dbName == 'db') {
            self::$dataBase[$this->dbName] = Yii::app()->getDb();
        } else {
            $this->changeConn($this->dbType);
        }
        if (self::$dataBase[$this->dbName] instanceof CDbConnection) {
            self::$dataBase[$this->dbName]->setActive(true);
            return self::$dataBase[$this->dbName];
        }
        throw new CDbException('Model requires a "db" CDbConnection application component.');
    }

    protected function changeConn($dbtype = 'read') {
        if ($this->dbType == $dbtype && isset(self::$dataBase[$this->dbName])) return self::$dataBase[$this->dbName];
        $this->dbName = $this->getDbName();
        if (Yii::app()->getComponent($this->dbName . '_' . $dbtype) !== null) {
            self::$dataBase[$this->dbName] = Yii::app()->getComponent($this->dbName . '_' . $dbtype);
            return self::$dataBase[$this->dbName];
        }
        $this->dbConfig = $this->getConfig('dbConfig', $this->dbName);
        if ($dbtype == 'write') {
            $config = $this->dbConfig[$dbtype];
        } else {
            $slavekey = array_rand($this->dbConfig[$dbtype]);
            $config = $this->dbConfig[$dbtype][$slavekey];
        }
        if ($dbComponent = Yii::createComponent($config)) {
            Yii::app()->setComponent($this->dbName . '_' . $dbtype, $dbComponent);
            self::$dataBase[$this->dbName] = Yii::app()->getComponent($this->dbName . '_' . $dbtype);
            $this->dbType = $dbtype;
            return self::$dataBase[$this->dbName];
        }
        throw new CDbException('Model requires a "changeConn" CDbConnection application component.');
    }

    protected function beforeSave() { parent::beforeSave(); $this->changeConn('write'); return true; }
    protected function beforeDelete() { parent::beforeDelete(); $this->changeConn('write'); return true; }
    protected function beforeFind() { parent::beforeFind(); $this->changeConn('read'); return true; }

    public function dbWrite() { return $this->changeConn('write'); }
    public function dbRead() { return $this->changeConn('read'); }
}

所有 Model 繼承此 ActiveRecord 即可自動享有多庫與讀寫分離功能，原生 SQL 亦支援。