YII支持读写分离

今天跟大家分享一个YII支持读写分离的好方法。

在include/components下建立一个JDbconnection，继承CActiveRecord的方法。代码如下。

<?php
/**
 * JDbConnection(Database Connection Manager) class is a manager of database connections.
 * 
 * for the purpose of database read/write splitting.
 * It override the createCommand method,
 * detect the sql statement to decide which connection will be used.
 * Default it use the master connection.
 *
 */
class JDbConnection extends CDbConnection
{
/**
* @var array $slaves.Slave database connection(Read) config array.
* The array value's format is the same as CDbConnection.
* <code>
* 'components'=>array(
* 'db'=>array(
* 'class' => 'JDbConnection',
* 'connectionString'=>'mysql://<master>',
* 'slaves'=>array(
* array('connectionString'=>'mysql://<slave01>'),
* array('connectionString'=>'mysql://<slave02>'),
* )
* )
* )
* </code>
*/
public $slaves = array();

/**
* Whether enable the slave database connection.
* Defaut is true.Set this property to false for the purpose of only use the master database.
* 
* @var bool $enableSlave 
*/
public $enableSlave = true;

/**
* @override
* @var bool $autoConnect Whether connect while init
*/
//public $autoConnect=false;



/**
* @var CDbConnection
*/
private $_slave;


/**
* Creates a CDbCommand object for excuting sql statement.
* It will detect the sql statement's behavior.
* While the sql is a simple read operation.
* It will use a slave database connection to contruct a CDbCommand object.
* Default it use current connection(master database).
* 
* @override 
* @param string $sql
* @return CDbCommand
*/
public function createCommand($query = null)
{
if ($this->enableSlave && !$this->getCurrentTransaction() && self::isReadOperation($query)) {
return $this->getSlave()->createCommand($query);
} else {
return parent::createCommand($query);
}
}


/**
* Construct a slave connection CDbConnection for read operation.
* 
* @return CDbConnection
*/
public function getSlave()
{
if (!isset($this->_slave)) {
foreach ($this->slaves as $slaveConfig) {
if (!isset($slaveConfig['class']))
$slaveConfig['class'] = 'CDbConnection';
try {
if ($slave = Yii::createComponent($slaveConfig)) {
Yii::app()->setComponent('dbslave', $slave);
$this->_slave = $slave;
break;
}
} catch (Exception $e) {
Yii::log('Create slave database connection failed!', 'warn');
continue;
}
}
if (!$this->_slave) {
$this->_slave = clone $this;
$this->_slave->enableSlave = false;
}
}
return $this->_slave;
}


/**
* Detect whether the sql statement is just a simple read operation.
* Read Operation means this sql will not change any thing ang aspect of the database.
* Such as SELECT,DECRIBE,SHOW etc.
* On the other hand:UPDATE,INSERT,DELETE is write operation.
* 
* @return bool
*/
public function isReadOperation($sql)
{
return !!preg_match('/^\s*(SELECT|SHOW|DESCRIBE|PRAGMA)/i', $sql);
}


/**
* 检测数据库连接是否还有效
*
* @params bool $isSlaveping主库还是从库
* @return void
*/
public function ping($isSlave = true)
{
if ($isSlave) {
if ($this->getActive()) {
$connect = $this->getSlave();
try {
$connect->getPdoInstance()->query('SELECT 1');
} catch (PDOException $e) {
$connect->setActive(false);
$connect->setActive(true);
}
}
} else {
if ($this->getActive()) {
try {
$this->getPdoInstance()->query('SELECT 1');
} catch (PDOException $e) {
$this->setActive(false);
$this->setActive(true);
}
}
}
}
}

然后改一下config里的main.php，让执行数据库操作的时候都走这个方法，代码如下。

'db' => array(
'class' => 'JDbConnection',
'connectionString' => "mysql:host=192.168.21.40;dbname=jiajing_hr;port=3306",
'emulatePrepare' => true,
'username' => 'root',
'password' => 'rootpasswd',
'charset' => 'UTF8',
'tablePrefix' => '',
'enableParamLogging' => YII_DEBUG,
'schemaCacheID' => 'cache',
'schemaCachingDuration' => JJ_DEBUG ? 0 : 1800,
'slaves' => array(
array(
'connectionString' => "mysql:host=192.168.21.40;dbname=jiajing_hr;port=3306",
'emulatePrepare' => true,
'username' => 'root',
'password' => 'rootpasswd',
'charset' => 'UTF8',
'tablePrefix' => '',
'enableParamLogging' => YII_DEBUG,
'schemaCacheID' => 'cache',
'schemaCachingDuration' => 0,
)
)
),

这样就可以了。试一下吧。