本文目录导读：
在 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);
注意事项
- 分表数量选择：建议选择2^n个表，方便扩展
- 索引优化：每个分表都要建立相应索引
- 事务处理：跨分表事务需使用分布式事务方案
- 数据迁移：提前规划好数据迁移方案
- 监控告警：对分表情况实时监控
水平拆分是数据库优化的利器，但也带来查询复杂度增加、事务处理困难等问题，建议在确实需要时才采用,并充分测试。
