yii连接mysql主从_Connection数据库主从连接源码剖析

连接总体流程

随机打乱从库配置(master可以选择是否打乱&＃xff0c;slave一定会打乱&＃xff1b;如果master没有配置数组则直接使用$dsn和$username作为master的配置&＃xff0c;也就是一主)

遍历配置如果该库的配置在serverStatusCache缓存中生效则说明过期时间内该配置不可用&＃xff0c;直接continue

如果缓存无值则去实例化PDO(会新实例化一个类$db &＃61; Yii::createObject($config))

实例化PDO记录info日志

记录实例化性能分析日志(可选&＃xff0c;根据$enableProfiling)

new PDO

设置PDO的ATTR_ERRMODE、ATTR_EMULATE_PREPARES、字符集属性

执行afterOpen事件

实例化失败记录serverStatusCache缓存&＃xff0c;标识该配置600秒(默认)内不可用

master与slave连接的差异

slave连接会先判断是否可以使用slave从库($this->enableSlaves)

slave如果连接不上会判断是否进而连接master

一定会打乱slave配置数组

如果没有master配置数组&＃xff0c;则直接使用$this->dns和$this->root

master连接可以选择是否打乱配置数组

涉及方法

getSlavePdo($fallbackToMaster &＃61; true)&＃xff0c;会调用getSlave(false),如果从库连不上就去连接master(根据参数$fallbackToMaster)&＃xff0c;返回的是PDO类

getSlave($fallbackToMaster &＃61; true)&＃xff0c;会调用openFromPool&＃xff0c;从slave配置数组中随机连接一个slave&＃xff0c;返回的是Connection类

getMasterPdo()&＃xff0c;会调用open()&＃xff0c;如果master配置数据为空则直接使用$dns进行连接&＃xff0c;如果master配置数组不为空则遍历连接master&＃xff0c;返回PDO类

getMaster()&＃xff0c;遍历连接master&＃xff0c;返回Connection类

openFromPool(array $pool, array $sharedConfig)&＃xff0c;随机打乱配置信息

openFromPoolSequentially(array $pool, array $sharedConfig),不随机打乱配置信息&＃xff0c;遍历配置信息&＃xff0c;连接PDO;内部还有serverStatusCache去缓存服务器可用状态

open()&＃xff0c;连接master或者slave&＃xff0c;会记录info和Profiling日志(可选)

createPdoInstance()&＃xff0c;实例化PDO

属性注入

因为Connection继承Component类&＃xff0c;可以使用属性注入&＃xff0c;所以

$db->master; //和$db->getMaster();一样

$db->slave; //和$db->getSlave();一样

$db->masterPdo; //和$db->getMasterPdo();一样

$db->slavePdo; //和$db->getSlavePdo();一样

源码细节

yii2中可以配置一主多从配置&＃xff0c;在连接从库方面数据库配置如下

public function actionD(){

$db &＃61; new \yii\db\Connection([

&＃39;dsn&＃39; &＃61;> &＃39;mysql:host&＃61;192.168.124.10;dbname&＃61;test&＃39;,

&＃39;username&＃39; &＃61;> &＃39;root&＃39;,

&＃39;password&＃39; &＃61;> &＃39;&＃39;,

&＃39;charset&＃39; &＃61;> &＃39;utf8&＃39;,

&＃39;enableSlaves&＃39;&＃61;>true, //可以使用从库

&＃39;serverRetryInterval&＃39;&＃61;>600, //其中一个从库配置不可用&＃xff0c;将缓存不可用状态600秒

&＃39;enableProfiling&＃39;&＃61;>true, //默认配置&＃xff0c;将记录连接数据库、执行语句等的性能分析日志

&＃39;emulatePrepare&＃39;&＃61;>true, //true为开启本地模拟prepare

&＃39;slaveConfig&＃39;&＃61;>[ //从库slaves属性通用配置

&＃39;username&＃39; &＃61;> &＃39;root&＃39;,

&＃39;password&＃39; &＃61;> &＃39;&＃39;,

&＃39;attributes&＃39; &＃61;> [

PDO::ATTR_TIMEOUT &＃61;> 10,

],

],

&＃39;slaves&＃39;&＃61;>[ //从库列表

["dsn"&＃61;>"mysql:host&＃61;192.168.124.11;dbname&＃61;test"],

["dsn"&＃61;>"mysql:host&＃61;192.168.124.12;dbname&＃61;test"],

[

"dsn"&＃61;>"mysql:host&＃61;192.168.124.13;dbname&＃61;test",

&＃39;username&＃39; &＃61;> &＃39;main&＃39;,

&＃39;password&＃39; &＃61;> &＃39;123456&＃39;,

],

],

&＃39;masters&＃39;&＃61;>[ //主库列表

["dsn"&＃61;>"mysql:host&＃61;192.168.124.11;dbname&＃61;test"],

["dsn"&＃61;>"mysql:host&＃61;192.168.124.12;dbname&＃61;test"],

[

"dsn"&＃61;>"mysql:host&＃61;192.168.124.13;dbname&＃61;test",

&＃39;username&＃39; &＃61;> &＃39;main&＃39;,

&＃39;password&＃39; &＃61;> &＃39;123456&＃39;,

],

],

&＃39;masterConfig&＃39;&＃61;>[ //主库master属性通用配置

&＃39;username&＃39; &＃61;> &＃39;root&＃39;,

&＃39;password&＃39; &＃61;> &＃39;&＃39;,

&＃39;attributes&＃39; &＃61;> [

PDO::ATTR_TIMEOUT &＃61;> 10,

],

],

]);

$slave &＃61; $db->getSlavePdo();

$slave &＃61; $db->getSlave();

return 123;

}

}

可以看到数据库操作的类是\yii\db\Connection&＃xff0c;该类继承Component类&＃xff0c;可见可以使用属性注入、行为和事件

针对Connection的属性注入&＃xff0c;只有以下属性是私有的&＃xff0c;以下属性一般不会在外部进行操作

private $_transaction;

private $_schema;

private $_driverName;

private $_master &＃61; false;

private $_slave &＃61; false;

private $_queryCacheInfo &＃61; [];

针对Connection的事件&＃xff0c;可以注册以下事件

const EVENT_AFTER_OPEN &＃61; &＃39;afterOpen&＃39;; //连接数据库后的事件

const EVENT_BEGIN_TRANSACTION &＃61; &＃39;beginTransaction&＃39;; //开启事务的事件

const EVENT_COMMIT_TRANSACTION &＃61; &＃39;commitTransaction&＃39;; //提交事务的事件

const EVENT_ROLLBACK_TRANSACTION &＃61; &＃39;rollbackTransaction&＃39;; //回滚的事件

Connection类使用的mysql操作对象是PDO&＃xff0c;涉及方法有

public function getSlavePdo($fallbackToMaster &＃61; true)

public function getSlave($fallbackToMaster &＃61; true)

追进在getSlavePdo方法&＃xff0c;可见当slave连接不可用时候&＃xff0c;会默认连接主库($fallbackToMaster&＃61;true)

public function getSlavePdo($fallbackToMaster &＃61; true){

$db &＃61; $this->getSlave(false); //进行slave连接

if ($db &＃61;&＃61;&＃61; null) {

return $fallbackToMaster ? $this->getMasterPdo() : null; //当slave不可用时候&＃xff0c;是否连接主库

}

return $db->pdo; //返回数据库连接资源&＃xff0c;从库和主库都连接不上的话会返回null

}

追进getSlave方法

public function getSlave($fallbackToMaster &＃61; true){

if (!$this->enableSlaves) {//判断是否可以使用slave

return $fallbackToMaster ? $this : null;

}

if ($this->_slave &＃61;&＃61;&＃61; false) { //如果还没有连接过slave库&＃xff0c;就进行连接

$this->_slave &＃61; $this->openFromPool($this->slaves, $this->slaveConfig); //将slave配置信息给openFromPool方法

}

return $this->_slave &＃61;&＃61;&＃61; null && $fallbackToMaster ? $this : $this->_slave;

}

追进openFromPool方法&＃xff0c;可见该方法就是将$this->slaves从库dsn配置打乱&＃xff0c;让第一次连接slave随机化

protected function openFromPool(array $pool, array $sharedConfig){

shuffle($pool); //打乱从库配置

return $this->openFromPoolSequentially($pool, $sharedConfig);

}

openFromPoolSequentially方法

protected function openFromPoolSequentially(array $pool, array $sharedConfig){

if (empty($pool)) { //是否有slave配置池&＃xff0c;如果没有的话就是最后返回给$this->_slave为null

return null;

}

if (!isset($sharedConfig[&＃39;class&＃39;])) { //判断$this->slaveConfig属性是否有class&＃xff0c;可以设置class将从库的连接配置成自己重新的类

$sharedConfig[&＃39;class&＃39;] &＃61; get_class($this);

}

//服务状态缓存&＃xff0c;使用依赖注入获取cache缓存类

$cache &＃61; is_string($this->serverStatusCache) ? Yii::$app->get($this->serverStatusCache, false) : $this->serverStatusCache;

//遍历slave配置池

foreach ($pool as $config) {

//合并配置

$config &＃61; array_merge($sharedConfig, $config);

if (empty($config[&＃39;dsn&＃39;])) {

throw new InvalidConfigException(&＃39;The "dsn" option must be specified.&＃39;);

}

$key &＃61; [__METHOD__, $config[&＃39;dsn&＃39;]];

//这里就是判断缓存是否有值&＃xff0c;如果有的话说明在过期时间内该配置的slave不可用

if ($cache instanceof CacheInterface && $cache->get($key)) {

// should not try this dead server now

continue;

}

//通过依赖注入创建了一个类&＃xff0c;该类专门是这个slave的

$db &＃61; Yii::createObject($config);

try {

$db->open();

return $db;

} catch (\Exception $e) {

//记录日志

Yii::warning("Connection ({$config[&＃39;dsn&＃39;]}) failed: " . $e->getMessage(), __METHOD__);

if ($cache instanceof CacheInterface) {

//将该配置的slave服务不可用状态存缓存&＃xff0c;值是1&＃xff0c;过期时间的$this->serverRetryInterval秒

$cache->set($key, 1, $this->serverRetryInterval);

}

}

}

return null;

}

在TestController控制器的配置中&＃xff0c;可见会随机打乱slaves属性&＃xff0c;如果有任何一个从库连接上了就是直接返回&＃xff0c;如果有连接不上的就会将不可用状态存缓存&＃xff0c;然后继续循环

slaveConfig属性是一个从库的通用配置&＃xff0c;会循环的去array_merge()属性slaves

所以配置

&＃39;slaveConfig&＃39;&＃61;>[ //从库slaves属性通用配置

&＃39;username&＃39; &＃61;> &＃39;root&＃39;,

&＃39;password&＃39; &＃61;> &＃39;&＃39;,

&＃39;attributes&＃39; &＃61;> [

PDO::ATTR_TIMEOUT &＃61;> 10,

],

],

&＃39;slaves&＃39;&＃61;>[ //从库列表

["dsn"&＃61;>"mysql:host&＃61;192.168.124.11;dbname&＃61;test"],

["dsn"&＃61;>"mysql:host&＃61;192.168.124.12;dbname&＃61;test"],

[

"dsn"&＃61;>"mysql:host&＃61;192.168.124.13;dbname&＃61;test",

&＃39;username&＃39; &＃61;> &＃39;main&＃39;,

&＃39;password&＃39; &＃61;> &＃39;123456&＃39;,

&＃39;class&＃39;&＃61;> yii\overload\myDB

],

]

最后生成的配置为(这个配置会被shuffle函数打乱顺序)

&＃39;slaves&＃39;&＃61;>[ //从库列表

[

"dsn"&＃61;>"mysql:host&＃61;192.168.124.11;dbname&＃61;test",

&＃39;username&＃39; &＃61;> &＃39;root&＃39;,

&＃39;password&＃39; &＃61;> &＃39;&＃39;,

&＃39;attributes&＃39; &＃61;> [

PDO::ATTR_TIMEOUT &＃61;> 10,

],

&＃39;class&＃39; &＃61;> &＃39;yii\db\Connection&＃39;,

],

[

"dsn"&＃61;>"mysql:host&＃61;192.168.124.12;dbname&＃61;test"

&＃39;username&＃39; &＃61;> &＃39;root&＃39;,

&＃39;password&＃39; &＃61;> &＃39;&＃39;,

&＃39;attributes&＃39; &＃61;> [

PDO::ATTR_TIMEOUT &＃61;> 10,

],

&＃39;class&＃39; &＃61;> &＃39;yii\db\Connection&＃39;,

],

[

"dsn"&＃61;>"mysql:host&＃61;192.168.124.13;dbname&＃61;test",

&＃39;username&＃39; &＃61;> &＃39;main&＃39;,

&＃39;password&＃39; &＃61;> &＃39;123456&＃39;,

&＃39;class&＃39;&＃61;> yii\overload\myDB

],

]

在open方法中&＃xff0c;因为是重新new&＃xff0c;所以$this->pdo和$this->master都是null

public function open(){

//因为是重新new&＃xff0c;所以$this->pdo和$this->master都是null

if ($this->pdo !&＃61;&＃61; null) {

return;

}

//因为是重新new&＃xff0c;所以$this->pdo和$this->master都是null

if (!empty($this->masters)) {

$db &＃61; $this->getMaster();

if ($db !&＃61;&＃61; null) {

$this->pdo &＃61; $db->pdo;

return;

}

throw new InvalidConfigException(&＃39;None of the master DB servers is available.&＃39;);

}

if (empty($this->dsn)) {

throw new InvalidConfigException(&＃39;Connection::dsn cannot be empty.&＃39;);

}

$token &＃61; &＃39;Opening DB connection: &＃39; . $this->dsn;

$enableProfiling &＃61; $this->enableProfiling;

try {

//记录日志

Yii::info($token, __METHOD__);

//如果开启了性能分析&＃xff0c;则记录性能分析日志(性能分析开启)

if ($enableProfiling) {

Yii::beginProfile($token, __METHOD__);

}

$this->pdo &＃61; $this->createPdoInstance();

$this->initConnection();

//如果开启了性能分析&＃xff0c;则记录性能分析日志(性能分析关闭)

if ($enableProfiling) {

Yii::endProfile($token, __METHOD__);

}

} catch (\PDOException $e) {

if ($enableProfiling) {

Yii::endProfile($token, __METHOD__);

}

throw new Exception($e->getMessage(), $e->errorInfo, (int) $e->getCode(), $e);

}

}

在createPdoInstance方法中&＃xff0c;这个没什么好说的&＃xff0c;就是执行new PDO

protected function createPdoInstance(){

$pdoClass &＃61; $this->pdoClass;

if ($pdoClass &＃61;&＃61;&＃61; null) {

$pdoClass &＃61; &＃39;PDO&＃39;;

if ($this->_driverName !&＃61;&＃61; null) {

$driver &＃61; $this->_driverName;

} elseif (($pos &＃61; strpos($this->dsn, &＃39;:&＃39;)) !&＃61;&＃61; false) {

$driver &＃61; strtolower(substr($this->dsn, 0, $pos));

}

if (isset($driver)) {

if ($driver &＃61;&＃61;&＃61; &＃39;mssql&＃39; || $driver &＃61;&＃61;&＃61; &＃39;dblib&＃39;) {

$pdoClass &＃61; &＃39;yii\db\mssql\PDO&＃39;;

} elseif ($driver &＃61;&＃61;&＃61; &＃39;sqlsrv&＃39;) {

$pdoClass &＃61; &＃39;yii\db\mssql\SqlsrvPDO&＃39;;

}

}

}

$dsn &＃61; $this->dsn;

if (strncmp(&＃39;sqlite:&＃64;&＃39;, $dsn, 8) &＃61;&＃61;&＃61; 0) {

$dsn &＃61; &＃39;sqlite:&＃39; . Yii::getAlias(substr($dsn, 7));

}

return new $pdoClass($dsn, $this->username, $this->password, $this->attributes);

}

在initConnection方法中&＃xff0c;这个也没什么好说的&＃xff0c;就是去设置PDO属性和执行afterOpen事件

protected function initConnection(){

$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if ($this->emulatePrepare !&＃61;&＃61; null && constant(&＃39;PDO::ATTR_EMULATE_PREPARES&＃39;)) {

$this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, $this->emulatePrepare);

}

if ($this->charset !&＃61;&＃61; null && in_array($this->getDriverName(), [&＃39;pgsql&＃39;, &＃39;mysql&＃39;, &＃39;mysqli&＃39;, &＃39;cubrid&＃39;], true)) {

$this->pdo->exec(&＃39;SET NAMES &＃39; . $this->pdo->quote($this->charset));

}

$this->trigger(self::EVENT_AFTER_OPEN);

}

主库连接getMaterPdo()方法

public function getMasterPdo(){

$this->open();

return $this->pdo;

}

主库连接getMaster()方法

public function getMaster(){

if ($this->_master &＃61;&＃61;&＃61; false) {

$this->_master &＃61; $this->shuffleMasters //是否随机打乱master配置数组

? $this->openFromPool($this->masters, $this->masterConfig)

: $this->openFromPoolSequentially($this->masters, $this->masterConfig);

}

return $this->_master;

}