How to perform database horizontal sub-database operation in ThinkPHP6?

With the expansion of business scale, the amount of data that the database needs to process is also increasing, causing a single database to face pressure. At this time, we need to perform database level sub-database operations to disperse the data into different databases, thereby improving the performance and scalability of the system. This article will introduce how to perform database horizontal sharding operations in ThinkPHP6.

1. What is database horizontal sub-database?

Database horizontal sharding is the process of dispersing data in one database into multiple databases. We can divide the data into different databases according to certain rules (such as according to user ID or time period), thereby reducing the load pressure on a single database. At the same time, when the amount of data is large, horizontal sharding can also improve query efficiency and enhance data security.

2. Implementation of horizontal sub-library in ThinkPHP6

In ThinkPHP6, we can implement horizontal sub-library by using database middleware. Place the database middleware in the MySQL connection of ThinkPHP6 to control the sub-database.

1. Install Thinkswoole

In ThinkPHP6, Thinkswoole is used as the database middleware. We need to install Thinkswoole in the project.

Add the ThinkSwoole version information to the composer.json file, and then use composer to install it.

2. Modify the database configuration

First find the config/database.php file and replace the MySQL connection with the Swoole connection. Comment out the original MySQL connection information:

// 'mysql' => [
    //     // 默认数据连接标识
    //     'default' => env('database.driver', 'mysql'),
    //     // 数据库连接信息
    //     'connections' => [
    //         'mysql' => [
    //             // 数据库类型
    //             'type' => 'mysql',
    //             // 主机地址
    //             'host' => env('database.hostname', '127.0.0.1'),
    //             // 数据库名
    //             'database' => env('database.database', ''),
    //             // 用户名
    //             'username' => env('database.username', 'root'),
    //             // 密码
    //             'password' => env('database.password', ''),
    //             // 端口
    //             'hostport' => env('database.hostport', '3306'),
    //             // 数据库连接参数
    //             'params' => [],
    //             // 数据库编码默认采用utf8
    //             'charset' => 'utf8',
    //             // 数据库表前缀
    //             'prefix' => env('database.prefix', ''),
    //             // 数据库调试模式
    //             'debug' => env('database.debug', true),
    //             // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
    //             'deploy' => 0,
    //             // 数据库读写是否分离 主从式有效
    //             'rw_separate' => false,
    //             // 读写分离后 主服务器数量
    //             'master_num' => 1,
    //             // 指定从服务器序号
    //             'slave_no' => '',
    //             // 是否严格检查字段是否存在
    //             'fields_strict' => true,
    //             // 数据集返回类型
    //             'resultset_type' => 'array',
    //             // 自动写入时间戳字段
    //             'auto_timestamp' => false,
    //             // 时间字段取出后的默认时间格式
    //             'datetime_format' => false,
    //             // Builder类
    //             'builder' => '',
    //             // Query类
    //             'query' => '\think\db\Query',
    //             // 是否需要进行SQL性能分析
    //             'sql_explain' => false,
    //         ],
    //     ],
    // ],

Add Swoole connection information:

 // swoole
    'swoole' => [
        // 默认数据连接标识
        'default' => 'swoole',
        // 数据库连接信息
        'connections' => [
            'swoole' => [
                // 数据库类型
                'type' => 'mysql',
                // 服务器地址
                'hostname' => [
                    '127.0.0.1:3305',
                    '127.0.0.1:3306',
                ],
                // 数据库名
                'database' => 'test',
                // 用户名
                'username' => 'root',
                // 密码
                'password' => '',
                // 端口
                'hostport' => '',
                // 数据库连接参数
                'params' => [],
                // 数据库编码默认采用utf8mb4
                'charset' => 'utf8mb4',
                // 数据库表前缀
                'prefix' => '',
                // 数据库调试模式
                'debug' => true,
                // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
                'deploy' => 0,
                // 数据库读写是否分离 主从式有效
                'rw_separate' => false,
                // 读写分离后 主服务器数量
                'master_num' => 1,
                // 指定从服务器序号
                'slave_no' => '',
                // 自动写入时间戳字段
                'auto_timestamp' => false,
                // 时间字段取出后的默认时间格式
                'datetime_format' => 'Y-m-d H:i:s',
                // Builder类
                'builder' => '',
                // Query类
                'query' => '\think\db\Query',
                // 是否需要进行SQL性能分析
                'sql_explain' => false,
            ],
        ],
    ],

In the above code, we defined two server addresses (127.0.0.1:3305 and 127.0.0.1:3306 ), this is to implement sub-library of multiple data nodes. Database name, user name, password and other information remain unchanged.

3. Create database middleware

Create the database middleware of Db.php in the app/middleware directory and add the following code:

<?php
namespace appmiddleware;

use thinkRequest;
use thinkContainer;

class Db
{
    public function handle(Request $request, Closure $next)
    {
        $serverIds = $this->getServerIds($request);
        //定义一个连接池
        $conns = [];
        foreach($serverIds as $sid) {
            $sid = $request->$sid;
            if(empty($conns[$sid])) {
                $conns[$sid] = Container::getInstance()
                                         ->make('db')->connect($sid);
            }
        }
        Container::getInstance()->bind('db', function() use ($conns) {
            return $conns;
        });
        return $next($request);
    }

    protected function getServerIds(Request $request)
    {
        return ['uid'];
    }
}

Create here A middleware called Db is created. In the handle method, first obtain the server ID array of the current request. Then compare these server addresses with the existing addresses in the connection pool $cons. If they do not exist, add them to the connection pool. Finally, bind the connection pool $conns to the container instance. In the getServerIds method, we can set the name of the server ID, which defaults to uid.

4. Register middleware

Add the following code to config/middleware.php:

return [
    ...
    appmiddlewareDb::class,
];

This code is used to register middleware. Added our Db middleware to the list of middleware execution activities.

5. Implement the sub-library operation

Next, we will implement the horizontal sub-library operation in the model. Taking the user table as an example, the user ID is divided into 100,000 and 100,000 as the limit of a database, which means that data with user IDs between 0 and 100,000 are stored in a database, and so on, until the user ID is in Data between 900,000 and 1 million is stored in the 10th database.

<?php
namespace appmodel;

use thinkModel;

class User extends Model
{
    protected $connection = [
        1 => 'user_1',
        2 => 'user_2',
        3 => 'user_3',
        4 => 'user_4',
        5 => 'user_5',
        6 => 'user_6',
        7 => 'user_7',
        8 => 'user_8',
        9 => 'user_9',
        10 => 'user_10',
    ];

    protected $pk = 'uid';

    public function getTableName(): string
    {
        $id = ceil($this->id / 100000);
        return $this->connection[$id] . '.' . $this->table;
    }
}

Here we define 10 database connections, each connection represents a database shard, achieving the purpose of horizontal sharding. Then we define the getTableName method to obtain the data table name corresponding to the current model. Calculate the database connection that needs to be accessed based on the primary key ID value in the model, and return the combination of database connection and data table name.

Summary:

This article introduces the horizontal sub-library operation in ThinkPHP6. As business continues to expand and data scale increases, horizontal sharding can improve system performance and scalability, as well as enhance data security. In ThinkPHP6, you can use Thinkswoole middleware and other methods to implement horizontal sub-library operations.

The above is the detailed content of How to perform database horizontal sub-database operation in ThinkPHP6?. For more information, please follow other related articles on the PHP Chinese website!