Laravel/Lumen漂亮的解决主从库延迟问题

5.5及以上版本:

// 修改config/database.php文件
// 增加sticky配置项为true
'mysql' => [
	'driver' => 'mysql',
	'host' => env('DB_HOST', '127.0.0.1'),
	'port' => env('DB_PORT', '3306'),
	'database' => env('DB_DATABASE', 'forge'),
	'username' => env('DB_USERNAME', 'forge'),
	'password' => env('DB_PASSWORD', ''),
	'unix_socket' => env('DB_SOCKET', ''),
	'charset' => 'utf8mb4',
	'collation' => 'utf8mb4_general_ci',
	'prefix' => '',
	'prefix_indexes' => true,
	'strict' => true,
	'engine' => null,
	'sticky' => true,
],

5.4及以下版本:

app/Providers/AppServiceProvider增加boot方法,监听每条执行的SQL,如果发现DML语句(INSERT,UPDATE,DELETE)时,则清空从库连接的pdo(readPdo),这样由于Laravel底层安全机制,会默认使用主库连接。这样没有使用事务时,从根本上解决执行DML语句后,再执行DQL(SELECT)语句的延迟问题。

// Laravel/Lumen Connection源码,当readPdo为null时,默认使用主库pdo实例。
public function getReadPdo()
{
     if ($this->transactions >= 1) {
            return $this->getPdo();
     }
      return $this->readPdo ?: $this->pdo;
}

Laravel/Lumen 5.1 实现代码

public function boot()
{
    \DB::listen(function ($sql, $bindings, $time, $connection) {
          $sql = ltrim($sql);
          if (stripos($sql, 'insert') === 0
                   || stripos($sql, 'update') === 0
                    || stripos($sql, 'delete') === 0
           ) {
                 //清空从库连接, 自动使用主库连接
                 \DB::connection($connection)->setReadPdo(null);
           }
     });
}

Laravel/Lumen 5.2 实现代码

public function boot()
{
     \DB::listen(function (QueryExecuted $executed) {
                $executed->sql = ltrim($executed->sql);
                if (stripos($executed->sql, 'insert') === 0
                || stripos($executed->sql, 'update') === 0
                || stripos($executed->sql, 'delete') === 0
               ) {
                     \DB::connection($executed->connection)->setReadPdo(null);//清空从连接,会自动使用主连接
            }
       });
}

参考资料: http://blog.sina.com.cn/s/blog_9bbafb790102win1.html

发表评论

电子邮件地址不会被公开。 必填项已用*标注