laravel 读操连接了读库和写库

来源：互联网发布：淘宝创意挂钩编辑：程序博客网时间：2024/05/01 08:06

今天在laravel框架中配置读写分离时，发现ORM的读操作分别连接了主库和从库，由于我们的产品是在海外推广，用的AWS 的 RDS Mysql版本，主库在美国，在新加坡开了个从库，发现查询时连接了主库和从库，导致查询非常慢，因此只能通过原生SQL查询。

laravel的读写分离配置如下（database.php)：

'connections' => [            'mysql' => [                'driver'    => 'mysql',                'host'      => env('DB_HOST', 'localhost'),                'port'      => env('DB_PORT', 3306),                'database'  => env('DB_DATABASE', 'forge'),                'username'  => env('DB_USERNAME', 'forge'),                'password'  => env('DB_PASSWORD', ''),                'charset'   => 'utf8mb4',                'collation' => 'utf8mb4_unicode_ci',                'prefix'    => env('DB_PREFIX', 'tbl_'),                'timezone'  => env('DB_TIMEZONE', '+00:00'),                'strict'    => false,                'options'   => array(                    PDO::ATTR_STRINGIFY_FETCHES => true,                    PDO::ATTR_EMULATE_PREPARES => false                ),            ],            'mysql-read' => [                'driver'    => 'mysql',                'host'      => env('DB_HOST_READ', 'localhost'),                'port'      => env('DB_PORT', 3306),                'database'  => env('DB_DATABASE', 'forge'),                'username'  => env('DB_USERNAME', 'forge'),                'password'  => env('DB_PASSWORD', ''),                'charset'   => 'utf8mb4',                'collation' => 'utf8mb4_unicode_ci',                'prefix'    => env('DB_PREFIX', 'tbl_'),                'timezone'  => env('DB_TIMEZONE', '+00:00'),                'strict'    => false,                'options'   => array(                    PDO::ATTR_STRINGIFY_FETCHES => true,                    PDO::ATTR_EMULATE_PREPARES => false                ),            ],            'mysql-write' => [                'driver'    => 'mysql',                'host'      => env('DB_HOST_WRITE', 'localhost'),                'port'      => env('DB_PORT', 3306),                'database'  => env('DB_DATABASE', 'forge'),                'username'  => env('DB_USERNAME', 'forge'),                'password'  => env('DB_PASSWORD', ''),                'charset'   => 'utf8mb4',                'collation' => 'utf8mb4_unicode_ci',                'prefix'    => env('DB_PREFIX', 'tbl_'),                'timezone'  => env('DB_TIMEZONE', '+00:00'),                'strict'    => false,                'options'   => array(                    PDO::ATTR_STRINGIFY_FETCHES => true,                    PDO::ATTR_EMULATE_PREPARES => false                ),            ],        ],

建一个连接数据的类：

class DBConnService{    private static $read_conn = null;    private static $write_conn = null;    private function __construct() {}    public static function writeConn() {        if (self::$write_conn === null) {            self::$write_conn = DB::connection('mysql-write');        }        return self::$write_conn;    }    public static function readConn() {        if (self::$read_conn === null) {            self::$read_conn = DB::connection('mysql-read');        }        return self::$read_conn;    }}

在程序中使用：

    $sql = "SELECT $fields FROM $table WHERE field = ? ";    $result = DBConnService::readConn()->select($sql, [$field]);

这样查询时只连接从库一次，而且一次请求中也只连接从库一次

0 0