PHP大表怎么水平拆分

wen PHP项目 2026-08-11 72

本文目录导读：

PHP大表怎么水平拆分

水平拆分基础概念
常见的拆分策略
分表路由组件实现
分表操作示例
复杂查询处理
最佳实践建议
注意事项

在 PHP 中处理大表水平拆分是常见的数据库优化策略,我来详细说明实现方案：

水平拆分基础概念

// 单个大表
users (1亿条数据)
↓ 水平拆分
users_0, users_1, users_2, ..., users_15
// 根据某种规则将数据分散到多个表

常见的拆分策略

1 哈希取模方式

<?php
class UserTableShard {
    private $tableCount = 16; // 分表数量
    private $connections = [];
    // 根据用户ID计算表名
    public function getTableName($userId) {
        $tableIndex = $userId % $this->tableCount;
        return "users_{$tableIndex}";
    }
    // 写入数据
    public function insertUser($userId, $data) {
        $table = $this->getTableName($userId);
        $sql = "INSERT INTO {$table} SET user_id = ?, name = ?";
        // 使用PDO预处理
        $stmt = $this->getConnection($table)->prepare($sql);
        return $stmt->execute([$userId, $data['name']]);
    }
    // 查询数据
    public function getUser($userId) {
        $table = $this->getTableName($userId);
        $sql = "SELECT * FROM {$table} WHERE user_id = ?";
        $stmt = $this->getConnection($table)->prepare($sql);
        $stmt->execute([$userId]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    private function getConnection($table) {
        // 获取表索引
        $index = (int)substr($table, strrpos($table, '_') + 1);
        // 配置连接池
        if (!isset($this->connections[$index])) {
            // 每个分表可能在不同服务器
            $config = $this->getServerConfig($index);
            $dsn = "mysql:host={$config['host']};dbname={$config['db']}";
            $this->connections[$index] = new PDO($dsn, $config['user'], $config['pass']);
        }
        return $this->connections[$index];
    }
}

2 范围切分方式

<?php
class RangeShard {
    private $shardingRules = [
        0 => ['min' => 0, 'max' => 1000000, 'table' => 'users_range_0'],
        1 => ['min' => 1000000, 'max' => 2000000, 'table' => 'users_range_1'],
        2 => ['min' => 2000000, 'max' => 3000000, 'table' => 'users_range_2'],
    ];
    public function getTableName($userId) {
        foreach ($this->shardingRules as $rule) {
            if ($userId >= $rule['min'] && $userId < $rule['max']) {
                return $rule['table'];
            }
        }
        throw new Exception("No sharding rule for user ID: {$userId}");
    }
}

分表路由组件实现

<?php
class ShardingRouter {
    private $config;
    private $connections = [];
    public function __construct($config) {
        $this->config = $config;
    }
    /**
     * 获取分表信息
     */
    public function getTableInfo($key) {
        if ($this->config['strategy'] === 'hash') {
            $index = $this->hashSharding($key, $this->config['table_count']);
            $tableName = "{$this->config['table_prefix']}_{$index}";
            return [
                'table' => $tableName,
                'db'    => $this->getDatabaseConfig($index),
                'index' => $index
            ];
        }
        // 范围分表
        if ($this->config['strategy'] === 'range') {
            foreach ($this->config['range_rules'] as $rule) {
                if ($key >= $rule['min'] && $key < $rule['max']) {
                    return [
                        'table' => $rule['table'],
                        'db'    => $this->getDatabaseConfig($rule['index'])
                    ];
                }
            }
        }
        throw new Exception("Invalid sharding strategy");
    }
    /**
     * 哈希分表算法
     */
    private function hashSharding($key, $tableCount) {
        // 使用FNV1a算法保证分布均匀
        $hash = crc32($key); 
        return $hash % $tableCount;
    }
    /**
     * 获取数据库连接
     */
    private function getDatabaseConfig($index) {
        return [
            'host' => $this->config['servers'][$index % count($this->config['servers'])],
            'db'   => $this->config['database_prefix'] . $index
        ];
    }
}

分表操作示例

<?php
class UserService {
    private $router;
    private $connections = [];
    public function __construct($config) {
        $this->router = new ShardingRouter($config);
    }
    /**
     * 插入用户数据
     */
    public function createUser($userId, $userData) {
        try {
            $tableInfo = $this->router->getTableInfo($userId);
            $tableName = $tableInfo['table'];
            $sql = "INSERT INTO {$tableName} (user_id, name, email, created_at) 
                    VALUES (?, ?, ?, NOW())";
            $pdo = $this->getPdo($tableInfo['db']);
            $stmt = $pdo->prepare($sql);
            $stmt->execute([$userId, $userData['name'], $userData['email']]);
            return $pdo->lastInsertId();
        } catch (Exception $e) {
            // 日志记录
            error_log("Insert error: " . $e->getMessage());
            return false;
        }
    }
    /**
     * 查询用户数据
     */
    public function getUser($userId) {
        $tableInfo = $this->router->getTableInfo($userId);
        $tableName = $tableInfo['table'];
        $sql = "SELECT * FROM {$tableName} WHERE user_id = ?";
        $pdo = $this->getPdo($tableInfo['db']);
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$userId]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    /**
     * 更新用户数据
     */
    public function updateUser($userId, $data) {
        $tableInfo = $this->router->getTableInfo($userId);
        $tableName = $tableInfo['table'];
        $updates = [];
        $params = [];
        foreach ($data as $field => $value) {
            $updates[] = "{$field} = ?";
            $params[] = $value;
        }
        $params[] = $userId;
        $sql = "UPDATE {$tableName} SET " . implode(',', $updates) . " WHERE user_id = ?";
        $pdo = $this->getPdo($tableInfo['db']);
        $stmt = $pdo->prepare($sql);
        return $stmt->execute($params);
    }
    /**
     * 分页查询（跨所有分表）
     */
    public function getAllUsers($page, $limit) {
        $results = [];
        $offset = ($page - 1) * $limit;
        // 查询所有分表
        foreach ($this->config['servers'] as $index => $server) {
            $tableName = $this->config['table_prefix'] . '_' . $index;
            $sql = "SELECT * FROM {$tableName} LIMIT {$offset}, {$limit}";
            $pdo = $this->getPdo(['host' => $server, 'db' => $this->dbName]);
            $stmt = $pdo->query($sql);
            $results = array_merge($results, $stmt->fetchAll());
        }
        return $results;
    }
    private function getPdo($dbConfig) {
        $key = md5(json_encode($dbConfig));
        if (!isset($this->connections[$key])) {
            $dsn = "mysql:host={$dbConfig['host']};dbname={$dbConfig['db']};charset=utf8mb4";
            $this->connections[$key] = new PDO($dsn, 'user', 'password', [
                PDO::ATTR_PERSISTENT => true,
                PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
            ]);
        }
        return $this->connections[$key];
    }
}

复杂查询处理

<?php
class ShardingQueryBuilder {
    private $router;
    public function __construct($router) {
        $this->router = $router;
    }
    /**
     * 执行跨表条件查询
     */
    public function searchUsers($criteria, $page = 1, $limit = 20) {
        $results = [];
        // 遍历所有分表
        for ($i = 0; $i < $this->router->tableCount; $i++) {
            $tableName = "users_" . $i;
            $sql = "SELECT * FROM {$tableName} WHERE 1=1";
            $params = [];
            // 构建条件
            if (!empty($criteria['name'])) {
                $sql .= " AND name LIKE ?";
                $params[] = "%{$criteria['name']}%";
            }
            if (!empty($criteria['email'])) {
                $sql .= " AND email = ?";
                $params[] = $criteria['email'];
            }
            $sql .= " LIMIT " . $limit . " OFFSET " . (($page - 1) * $limit);
            // 执行查询
            $pdo = $this->router->getConnection($i);
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $results = array_merge($results, $stmt->fetchAll());
        }
        // 如果数据量太大，可以在应用层做分页和排序
        usort($results, function($a, $b) {
            return $a['created_at'] < $b['created_at'];
        });
        return array_slice($results, ($page - 1) * $limit, $limit);
    }
}

最佳实践建议

// 分表配置文件
$config = [
    'strategy' => 'hash',          // 分表策略: hash / range
    'table_prefix' => 'users',
    'table_count' => 16,           // 分表数量
    'servers' => [                  // 多服务器配置
        '192.168.1.1',
        '192.168.1.2',
    ],
    'database_prefix' => 'app_db',
    // 分表数量选择建议
    // - 每表不超过500万条记录
    // - 确保数据分布均匀
    // - 考虑未来数据增长
];
// 使用示例
$userService = new UserService($config);
// 写入
$userService->createUser(123456, [
    'name' => 'John Doe',
    'email' => 'john@example.com'
]);
// 读取
$user = $userService->getUser(123456);