Vertical sharding and horizontal sharding optimization practice in PHP programming

With the continued development of Internet business and the increasing amount of data, a single database server can no longer meet business needs. In this case, the horizontal sharding and vertical sharding technology of the database came into being. This article will explore the optimization practices of vertical sharding and horizontal sharding in PHP programming.

1. Vertical Sharding

Vertical Sharding refers to dispersing data in a single database into different databases based on business logic. The advantage of vertical sharding is that it can reduce the number of records in the table (collection) and shorten the time of data query. For business scenarios with more reads and less writes, vertical sharding can improve the read and write performance and stability of the database.

In PHP programming, the implementation of vertical sharding requires classifying data according to business logic. For example, an e-commerce website can store product information, user information, and order information in different databases. In PHP code, you can connect to multiple databases through PDO, and perform addition, deletion, modification and query operations for different databases.

Sample code:

$pdo1 = new PDO("mysql:host=localhost;dbname=db1", "user", "password");
$pdo2 = new PDO("mysql:host=localhost;dbname=db2", "user", "password");
$pdo3 = new PDO("mysql:host=localhost;dbname=db3", "user", "password");

// 查询商品信息
$pdo1->query("SELECT * FROM products");

// 添加用户信息
$pdo2->query("INSERT INTO users(name, age) VALUES('Tom', 18)");

// 查询订单信息
$pdo3->query("SELECT * FROM orders");

2. Horizontal Sharding

Horizontal Sharding (Horizontal Sharding) refers to dispersing the data of a single table (collection) into different databases , the same data can be allocated to different databases. The advantage of horizontal sharding is that it can make full use of the performance of multiple database servers and improve the reading and writing capabilities and reliability of the database.

In PHP programming, the implementation of horizontal sharding requires dividing table data into different databases based on business logic. For example, the order table can be divided according to user IDs, and orders from different users can be allocated to different databases for management. In PHP code, data can be fragmented through algorithms and written to different databases.

Sample code:

// 根据用户ID计算数据库编号
function getDbIndex($userId, $dbCount) {
    return $userId % $dbCount;
}

// 获取数据库连接
function getPdo($userId) {
    $dbCount = 3; // 数据库数量
    $dbIndex = getDbIndex($userId, $dbCount);
    $dsn = "mysql:host=localhost;dbname=db" . ($dbIndex + 1);
    return new PDO($dsn, "user", "password");
}

// 添加订单信息
function addOrder($userId, $product, $price) {
    $pdo = getPdo($userId);
    $pdo->query("INSERT INTO orders(user_id, product, price) VALUES($userId, '$product', $price)");
}

// 查询订单信息
function queryOrder($userId) {
    $pdo = getPdo($userId);
    $pdo->query("SELECT * FROM orders WHERE user_id=$userId");
}

3. Comprehensive application

In PHP programming, the comprehensive application of vertical sharding and horizontal sharding can further improve the performance and reliability of the database. . For example, an e-commerce website can store product information, user information, and order information in different databases, and disperse order tables into different databases based on user IDs. In PHP code, you can add, delete, modify, and query scattered data through multiple PDO connections.

Sample code:

// 根据用户ID查询订单信息
function queryOrder($userId) {
    $pdo1 = new PDO("mysql:host=localhost;dbname=db1", "user", "password");
    $pdo2 = new PDO("mysql:host=localhost;dbname=db2", "user", "password");
    $pdo3 = new PDO("mysql:host=localhost;dbname=db3", "user", "password");

    // 查询商品信息
    $pdo1->query("SELECT * FROM products");

    // 查询用户信息
    $pdo2->query("SELECT * FROM users WHERE id=$userId");

    // 根据用户ID查询订单信息
    $pdo3->query("SELECT * FROM orders WHERE user_id=$userId");
}

// 添加订单信息
function addOrder($userId, $product, $price) {
    $dbCount = 3; // 数据库数量
    $dbIndex = getDbIndex($userId, $dbCount);
    $dsn = "mysql:host=localhost;dbname=db" . ($dbIndex + 1);
    $pdo = new PDO($dsn, "user", "password");
    $pdo->query("INSERT INTO orders(user_id, product, price) VALUES($userId, '$product', $price)");
}

4. Summary

Vertical sharding and horizontal sharding are commonly used database optimization technologies in Internet businesses, which can effectively improve the read and write performance of the database. and reliability. In PHP programming, the addition, deletion, modification and query operations of scattered data can be realized through multiple PDO connections, which can further improve the performance and reliability of the system.

The above is the detailed content of Vertical sharding and horizontal sharding optimization practice in PHP programming. For more information, please follow other related articles on the PHP Chinese website!