PHP method to implement database sharding expansion

In the Internet era, data has become one of the most important assets of an enterprise. As the demand for data storage and processing continues to grow, database expansion has become an inevitable choice for many enterprises. When a single database cannot meet the needs of the enterprise, database sharding becomes an effective expansion solution.

Database sharding refers to horizontally dividing a database into multiple independent libraries, each of which stores part of the data, thereby reducing the load on a single library and improving system performance. In actual application scenarios, database sharding is generally divided into two methods: vertical sharding and horizontal sharding. This article mainly introduces the horizontal sharding expansion method implemented by PHP.

1. The basic idea of ​​horizontal sharding of database

First, the data needs to be divided into different shards according to certain rules. Specific partitioning rules can be formulated according to business needs. Commonly used rules are:

• Range-based sharding: sharding is performed based on the value of a certain column to ensure that the data range stored in each shard is mutually exclusive. Independent;
• Hash-based sharding: perform a hash operation on the value of a certain column to obtain a value, and then distribute the value to different shards to ensure that the data stored in each shard is approximately balanced.

After dividing the shards, the connection layer needs to be modified so that it can select the corresponding database for access according to the shard where the data is located. Specifically, the connection layer needs to record the relevant information of each fragment, such as fragment capacity, fragment start value, fragment end value, etc., and expose interfaces for use by the business layer.

Finally, the business layer needs to send read and write requests to the database to the corresponding shards according to the partitioning rules. The database operation in the business layer is actually an encapsulation of the connection layer. It needs to select the corresponding database according to the sharding rules to perform CRUD operations.

2. PHP’s method of implementing database sharding expansion

In PHP, PDO can be used to achieve sharding expansion of the MySQL database. Specifically, you need to follow the following steps:

2.1 Create a PDO connection

When creating a PDO connection, you need to pay attention to some details. First, the PDO connection needs to specify the relevant configuration information of the main library and the list of sharded libraries. Secondly, you need to set the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION so that you can capture and handle PDO exceptions. Finally, you need to set the PDO::ATTR_EMULATE_PREPARES attribute to false so that real preprocessing can be achieved.

The sample code is as follows:

// 主库配置信息
$masterConfig = [
    'dsn'      => 'mysql:host=127.0.0.1;port=3306;dbname=test',
    'username' => 'root',
    'password' => 'root',
];

// 分片库列表
$shardConfigList = [
    [
        'dsn'      => 'mysql:host=127.0.0.1;port=3306;dbname=test_shard_0',
        'username' => 'root',
        'password' => 'root',
    ],
    [
        'dsn'      => 'mysql:host=127.0.0.1;port=3306;dbname=test_shard_1',
        'username' => 'root',
        'password' => 'root',
    ],
];

// 创建PDO连接
$pdo = new PDO($masterConfig['dsn'], $masterConfig['username'], $masterConfig['password'], [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

2.2 Execute shard query

When performing database operations in an application, the data needs to be allocated to the corresponding shards according to the partitioning rules. Typically, the structures in a set of sharded libraries are the same, only the data differs. Therefore, when performing a shard query, you can first obtain the shard information from the main library, and forward the query request to the corresponding shard library based on the shard information.

The sample code is as follows:

// 获取分片信息
$sql = 'SELECT * FROM `shard_info` WHERE shard_id = ?';
$stmt = $pdo->prepare($sql);
$stmt->execute([$shardId]);
$info = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$info) {
    throw new RuntimeException('Shard not found');
}

// 创建分片PDO连接
$pdoShard = new PDO($info['dsn'], $info['username'], $info['password'], [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

// 执行查询
$sql = 'SELECT * FROM `table` WHERE `key` = ?';
$stmt = $pdoShard->prepare($sql);
$stmt->execute([$key]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

2.3 Executing sharded transactions

When conducting distributed transactions, the operations of multiple shards involved need to be treated as a whole . Specifically, a two-phase commit protocol can be utilized to achieve consistency in distributed transactions.

Among them, the first phase_Prepare phase requires sending Prepare requests to all involved shards to obtain the corresponding transaction ID. After all shards return successful responses, Commit/Abort requests need to be sent to all shards to commit or rollback the transaction.

The sample code is as follows:

// 开始分布式事务
$pdo->beginTransaction();

try {
    // 准备分片事务
    $xid = uniqid();
    $prepares = [];

    foreach ($shardConfigList as $shardConfig) {
        $pdoShard = new PDO($shardConfig['dsn'], $shardConfig['username'], $shardConfig['password'], [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]);

        $pdoShard->beginTransaction();
        $stmt = $pdoShard->prepare('INSERT INTO `table` (`key`, `value`) VALUES (?, ?)');
        $stmt->execute([$key, $value]);
        $prepares[] = [$pdoShard, $xid];
    }

    // 提交分片事务
    foreach ($prepares as [$pdoShard, $xid]) {
        $stmt = $pdoShard->prepare('PREPARE TRANSACTION ?');
        $stmt->execute([$xid]);
    }

    foreach ($prepares as [$pdoShard, $xid]) {
        $stmt = $pdoShard->prepare('COMMIT PREPARED ?');
        $stmt->execute([$xid]);
    }

    // 提交整个事务
    $pdo->commit();
} catch (Exception $ex) {
    // 回滚分片事务
    foreach ($prepares as [$pdoShard, $xid]) {
        $stmt = $pdoShard->prepare('ROLLBACK PREPARED ?');
        $stmt->execute([$xid]);
    }

    // 回滚整个事务
    $pdo->rollback();
}

3. Summary

Database sharding is an effective expansion solution that can help solve the problem of excessive load on a single database. question. In PHP, PDO can be used to achieve shard expansion of the MySQL database. The specific operation process includes creating a PDO connection, executing shard queries, and executing shard transactions. In practical applications, attention needs to be paid to the rules of data partitioning and modifications to the connection layer, as well as the consistency of distributed transactions.

The above is the detailed content of PHP method to implement database sharding expansion. For more information, please follow other related articles on the PHP Chinese website!