How to realize reading and writing separation in php+mysql

PHP implements MySQL read-write separation and needs to implement the following functions:

1. Distinguish between read and write operations: In PHP code, database operations need to be performed To distinguish, place read operations and write operations on different MySQL instances.

You need to design a load balancing solution to evenly distribute read requests to multiple MySQL instances by using different algorithms.

Below, we will introduce in detail how to implement the above functions.

Separate read and write operations

The implementation of MySQL's read and write separation requires the support of MySQL master-slave replication technology. In MySQL master-slave replication, all write operations are performed in the master database, while only read operations are performed in the slave database.

In the PHP code, MySQL read and write operations need to be distinguished, write operations are sent to the main library, and read operations are sent to the slave library.

The following two methods can be used for specific implementation:

1. Manual switching of connections: Manually switch connections for read and write operations in the code. Connect the main database and slave database on different MySQL instances as needed, and execute the corresponding SQL statements.

For example, for the following SQL statement:

SELECT * FROM users WHERE age>18;

You can use the following code to switch connections for read operations:

//生成一个读取从库的连接
$slaveConn = mysqli_connect($slaveHost, $slaveUser, $slavePass, $dbName);
mysqli_query($slaveConn, "SET NAMES utf8");

//查询数据
$result = mysqli_query($slaveConn, "SELECT * FROM users WHERE age>18");

Similarly, for write operations:

INSERT INTO users (name,age,sex) VALUES ('jack',18,'male');

The following code can be used to switch connections for write operations:

//生成一个写入主库的连接
$masterConn = mysqli_connect($masterHost, $masterUser, $masterPass, $dbName);
mysqli_query($masterConn, "SET NAMES utf8");

//插入数据
$result = mysqli_query($masterConn, "INSERT INTO users (name,age,sex) VALUES ('jack',18,'male')");

In the above code, the mysqli_connect function is used to generate a database connection, and the mysqli_query method is used to operate the MySQL database. Among them, $slaveHost, $slaveUser, and $slavePass are the connection information of the slave library, and $masterHost, $masterUser, and $masterPass are the connection information of the master library.

You can use the built-in implementation in the PHP framework to switch read and write operations. In the Yii2 framework, the following code can be used to switch between read and write operations:

//生成一个读取从库的连接
$slaveConn = Yii::$app->slaveDb->getConnection();

//查询数据
$query = new \yii\db\Query();
$result = $query->from('users')->where(['age' > 18])->all($slaveConn);

Similarly, use the following code to switch between write operations:

//生成一个写入主库的连接
$masterConn = Yii::$app->masterDb->getConnection();

//插入数据
$result = Yii::$app->db->createCommand()->insert('users', [
'name' => 'jack',
'age' => 18,
'sex' => 'male'
])->execute($masterConn);

In the above code, Yii::$app ->slaveDb and Yii::$app->masterDb are both built-in database connection components of the Yii2 framework, providing methods for reading from the slave library and writing to the master library.

Load balancing strategy

For the implementation of MySQL read and write separation, load balancing is a very important part. Only if all read requests are evenly distributed on each slave database can the advantages of MySQL read and write separation be fully utilized.

Commonly used load balancing strategies include:

1. Random strategy: randomly distribute read requests to each slave database.

2. Polling strategy: allocate read requests to each slave library in turn and use them cyclically.

3. Availability priority strategy: Use an availability monitoring method to select available slave libraries before reading operations.

This article adopts the polling strategy, and the specific implementation is as follows:

//从库连接信息
$slave1 = array(
'host' => 'slave1.host.com', 
'user' => 'slave1user', 
'pass' => 'slave1pass',
'name' => 'dbname'
);

$slave2 = array(
'host' => 'slave2.host.com', 
'user' => 'slave2user', 
'pass' => 'slave2pass',
'name' => 'dbname'
);

$slave3 = array(
'host' => 'slave3.host.com', 
'user' => 'slave3user', 
'pass' => 'slave3pass',
'name' => 'dbname'
);


//增加从库列表
$slaveList = array($slave1, $slave2, $slave3);


//轮询获取从库连接信息
function getSlaveConn() {
global $slaveList;
static $index = 0;
if ($index >= count($slaveList)) {
    $index = 0;
}
$slave = $slaveList[$index];
$index++;
$conn = mysqli_connect($slave['host'], $slave['user'], $slave['pass'], $slave['name']);
mysqli_query($conn, "SET NAMES utf8");
return $conn;
}

In the above code, $slave1, $slave2, $slave3 are slave library connection information, and $slaveList is the slave library list. In the getSlaveConn function, $index is the number of connections to the slave library. When the number of connections is equal to the length of the slave library list, $index is reset to zero and the connection is reconnected from the first slave library. Each time you connect to a slave library, use the mysqli_connect function to connect to the database.

Through the above implementation, PHP's function of realizing MySQL read and write separation has been implemented. You can use the above code to implement the MySQL read-write separation function in PHP applications, and add or modify load balancing strategies as needed.

In the case of high concurrency and large data volume, using PHP to separate MySQL reading and writing can effectively improve the reading and writing performance of the MySQL database, reduce the application response time, and improve the user experience.

The above is the detailed content of How to realize reading and writing separation in php+mysql. For more information, please follow other related articles on the PHP Chinese website!