PHP 實現 MySQL 分庫分表類：配置驅動的水平拆分方案

PHP 實現 MySQL 分庫分表類：配置驅動的水平拆分方案

當單表數據量過大導致性能瓶頸時，常見解決方案為分區表或分表。分表分為垂直拆分與水平拆分，分庫分表屬於水平拆分範疇，即按規則將數據分散至不同表甚至不同服務器的數據庫中，有效分散壓力。

核心實現：Config 類

根據配置文件計算目標庫與表名，生成對應 DSN 供上層連接使用。

namespace App\Model\Database;

class Config {
    public $dsn;
    public $user;
    public $password;
    public $dbname;
    public $table;

    private static $config;
    private static $configFile = 'mysql.php';

    public function __construct($dbname, $table, $id = 0) {
        if (is_null(static::$config)) {
            $config = include(static::$configFile);
            static::$config = $config;
        }
        $config = static::$config;

        if (isset($config['shared']) && isset($config['shared'][$dbname])) {
            $dbconfig = $config['shared'][$dbname];
            $id = is_numeric($id) ? (int)$id : crc32($id);
            
            // 計算庫編號與表編號
            $database_id = ($id / $dbconfig['database_split'][0]) % $dbconfig['database_split'][1];
            $table_id = ($id / $dbconfig['table_split'][0]) % $dbconfig['table_split'][1];

            // 匹配主機配置區間
            foreach ($dbconfig['host'] as $key => $conf) {
                list($from, $to) = explode('-', $key);
                if ($from <= $database_id && $database_id <= $to) {
                    $the_config = $conf;
                }
            }

            $this->dbname = $dbname . '_' . $database_id;
            $this->table = $table . '_' . $table_id;
        } else {
            // 不分庫分表
            $this->dbname = $dbname;
            $this->table = $table;
            $the_config = $config['db'][$dbname];
        }

        $c = $the_config;
        if (isset($c['unix_socket']) && $c['unix_socket']) {
            $this->dsn = sprintf('mysql:dbname=%s;unix_socket=%s', $this->dbname, $c['unix_socket']);
        } else {
            $this->dsn = sprintf('mysql:dbname=%s;host=%s;port=%s', $this->dbname, $c['host'], $c['port']);
        }
        $this->user = $c['user'];
        $this->password = $c['password'];
    }
}

配置文件結構 (mysql.php)

$default = [
    'unix_socket' => null,
    'host' => 'localhost',
    'port' => '3306',
    'user' => 'root',
    'password' => '',
];

$config = [
    // 普通庫
    'db' => [
        'my_site' => $default,
    ],
    // 分庫分表庫
    'shared' => [
        'user' => [
            'host' => [
                '0-10'   => $default,
                '11-28'  => $default,
                '29-99'  => $default,
            ],
            // 百庫百表示例
            'database_split' => [1, 100],  // 除數, 取模基數
            'table_split'    => [100, 100],
        ],
    ],
];

return $config;

規則解讀：假設 uid = 543234678
庫編號：(543234678 / 1) % 100 = 78 → user_78
表編號：(543234678 / 100) % 100 = 46 → userinfo_46

Model 基類整合示例

namespace App\Model;

use App\Model\Database\Config;
use PDO;

abstract class Model {
    public $config;
    public $connection;
    protected $dbnamePrefix;
    protected $tablePrefix;
    protected $table;

    public function __construct($id) {
        $this->config = new Config($this->dbnamePrefix, $this->tablePrefix, $id);
        $this->connection = new PDO($this->config->dsn, $this->config->user, $this->config->password);
        $this->table = $this->config->table;
    }

    public function update(array $data, array $where = []) {}
    public function select(array $where) {}
    public function insert(array $data) {}
    public function query($sql) { return $this->connection->query($sql); }
}

具體業務模型調用

require 'Config.php';
require 'Model.php';

use App\Model\Model;

class User extends Model {
    protected $dbnamePrefix = 'user';
    protected $tablePrefix = 'userinfo';
}

$user = new User(4455345345);
print_r($user);

此方案通過配置驅動實現分庫分表邏輯與業務代碼解耦，擴展性強，適合中大型 PHP 專案。