PHP怎么实现数据分库 数据分库的3种架构方案解析

<?php

class ShardingDatabase
{
    private $databases;
    private $shardingKey;

    public function __construct(array $databases, string $shardingKey)
    {
        $this->databases = $databases;
        $this->shardingKey = $shardingKey;
    }

    private function getDatabase(int $shardingValue): PDO
    {
        $dbCount = count($this->databases);
        $dbIndex = $shardingValue % $dbCount;
        return $this->databases[$dbIndex]; // 假设 databases 数组存储的是 PDO 连接
    }

    public function query(string $sql, array $params, int $shardingValue)
    {
        $db = $this->getDatabase($shardingValue);
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function insert(string $sql, array $params, int $shardingValue): bool
    {
        $db = $this->getDatabase($shardingValue);
        $stmt = $db->prepare($sql);
        return $stmt->execute($params);
    }
}

// 示例用法
$databases = [
    new PDO("mysql:host=db1;dbname=users", "user", "password"),
    new PDO("mysql:host=db2;dbname=users", "user", "password"),
];

$shardingDb = new ShardingDatabase($databases, 'user_id');

// 插入数据，根据 user_id 分库
$userId = 123;
$sql = "INSERT INTO users (user_id, name) VALUES (:user_id, :name)";
$params = [':user_id' => $userId, ':name' => 'Alice'];
$shardingDb->insert($sql, $params, $userId);

// 查询数据，同样根据 user_id
$sql = "SELECT * FROM users WHERE user_id = :user_id";
$params = [':user_id' => $userId];
$result = $shardingDb->query($sql, $params, $userId);

print_r($result);

?>

<?php

class DatabaseConfig
{
    public static function getUserDbConfig(): array
    {
        return [
            'host' => 'user_db_host',
            'dbname' => 'user_db',
            'user' => 'user',
            'password' => 'password',
        ];
    }

    public static function getOrderDbConfig(): array
    {
        return [
            'host' => 'order_db_host',
            'dbname' => 'order_db',
            'user' => 'user',
            'password' => 'password',
        ];
    }
}

class UserDatabase
{
    private $db;

    public function __construct()
    {
        $config = DatabaseConfig::getUserDbConfig();
        $dsn = "mysql:host={$config['host']};dbname={$config['dbname']}";
        $this->db = new PDO($dsn, $config['user'], $config['password']);
    }

    public function getUserById(int $userId): array
    {
        $stmt = $this->db->prepare("SELECT * FROM users WHERE id = :id");
        $stmt->execute([':id' => $userId]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
}

class OrderDatabase
{
    private $db;

    public function __construct()
    {
        $config = DatabaseConfig::getOrderDbConfig();
        $dsn = "mysql:host={$config['host']};dbname={$config['dbname']}";
        $this->db = new PDO($dsn, $config['user'], $config['password']);
    }

    public function getOrdersByUserId(int $userId): array
    {
        $stmt = $this->db->prepare("SELECT * FROM orders WHERE user_id = :user_id");
        $stmt->execute([':user_id' => $userId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

// 示例用法
$userDb = new UserDatabase();
$orderDb = new OrderDatabase();

$user = $userDb->getUserById(1);
$orders = $orderDb->getOrdersByUserId(1);

print_r($user);
print_r($orders);

?>

<?php

class ReadWriteDatabase
{
    private $masterDb;
    private $slaveDbs;

    public function __construct(PDO $masterDb, array $slaveDbs)
    {
        $this->masterDb = $masterDb;
        $this->slaveDbs = $slaveDbs;
    }

    private function getSlaveDb(): PDO
    {
        $slaveCount = count($this->slaveDbs);
        $slaveIndex = rand(0, $slaveCount - 1); // 随机选择一个从库
        return $this->slaveDbs[$slaveIndex];
    }

    public function query(string $sql, array $params, bool $isWrite = false)
    {
        $db = $isWrite ? $this->masterDb : $this->getSlaveDb();
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function insert(string $sql, array $params): bool
    {
        return $this->query($sql, $params, true); // 强制写主库
    }

    public function update(string $sql, array $params): bool
    {
        return $this->query($sql, $params, true); // 强制写主库
    }
}

// 示例用法
$masterDb = new PDO("mysql:host=master_db;dbname=mydb", "user", "password");
$slaveDbs = [
    new PDO("mysql:host=slave_db1;dbname=mydb", "user", "password"),
    new PDO("mysql:host=slave_db2;dbname=mydb", "user", "password"),
];

$readWriteDb = new ReadWriteDatabase($masterDb, $slaveDbs);

// 插入数据，写主库
$sql = "INSERT INTO users (name) VALUES (:name)";
$params = [':name' => 'Bob'];
$readWriteDb->insert($sql, $params);

// 查询数据，读从库
$sql = "SELECT * FROM users WHERE name = :name";
$params = [':name' => 'Bob'];
$result = $readWriteDb->query($sql, $params);

print_r($result);

?>