PHP分库分表在Laravel中怎么实现

本文目录导读：

1. 基于 Sharding 扩展包
2. 手动实现分库分表
3. 使用监听器自动切换
4. 完整的配置示例
5. 最佳实践建议
6. 注意事项

在 Laravel 中实现分库分表，通常有几种主流方案，我会从简单到复杂为你介绍：

基于 Sharding 扩展包

使用 shard-manager 或 laravel-shard

composer require mabasic/laravel-shard

配置示例：

// config/shard.php
return [
    'connections' => [
        'shard1' => [
            'driver' => 'mysql',
            'host' => '192.168.1.1',
            'database' => 'db_shard_1',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8mb4',
        ],
        'shard2' => [
            'driver' => 'mysql',
            'host' => '192.168.1.2',
            'database' => 'db_shard_2',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8mb4',
        ],
    ],
    'shard_count' => 2,
];

模型使用：

use Mabasic\LaravelShard\Traits\Shardable;
class User extends Model {
    use Shardable;
    protected $shardKey = 'user_id'; // 分片键
}

手动实现分库分表

Model 基类方式

abstract class ShardModel extends Model {
    protected function getShardConnection($key) {
        // 根据key计算分库
        $database = 'shard_' . ($key % 2);
        return $database;
    }
    protected function getShardTable($key) {
        // 根据key计算分表
        $table = $this->table . '_' . ($key % 4);
        return $table;
    }
}
class Order extends ShardModel {
    protected $table = 'orders';
    public function scopeOnShard($query, $userId) {
        $connection = $this->getShardConnection($userId);
        $table = $this->getShardTable($userId);
        return $query->on($connection)
                     ->from($table);
    }
}

使用 Repository 模式

class OrderRepository {
    private $connections = ['mysql_shard1', 'mysql_shard2'];
    public function save(Order $order, $userId) {
        $shard = $userId % count($this->connections);
        $table = 'orders_' . ($userId % 4);
        $order->setConnection($this->connections[$shard]);
        $order->setTable($table);
        return $order->save();
    }
    public function findByUserId($userId) {
        $shard = $userId % count($this->connections);
        $table = 'orders_' . ($userId % 4);
        return Order::on($this->connections[$shard])
                   ->from($table)
                   ->where('user_id', $userId)
                   ->get();
    }
}

使用监听器自动切换

// App\Listeners\ShardListener.php
class ShardListener {
    public function handle($event) {
        // 假设请求头包含分片信息
        $shardId = request()->header('X-Shard-ID');
        if ($shardId) {
            $connection = 'shard_' . ($shardId % 2);
            DB::setDefaultConnection($connection);
        }
    }
}

完整的配置示例

数据库配置

// config/database.php
'connections' => [
    'shard_0' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST_0', '127.0.0.1'),
        'port' => env('DB_PORT', 3306),
        'database' => env('DB_DATABASE_0', 'shard_0'),
        // ... 其他配置
    ],
    'shard_1' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST_1', '127.0.0.1'),
        'port' => env('DB_PORT', 3306),
        'database' => env('DB_DATABASE_1', 'shard_1'),
        // ... 其他配置
    ],
],

分片中间件

class ShardMiddleware {
    public function handle($request, Closure $next) {
        $userId = $request->user()->id;
        $shardIndex = $userId % 2;
        // 动态设置连接
        config(['database.default' => 'shard_' . $shardIndex]);
        return $next($request);
    }
}

最佳实践建议

1. 选择合适的分片键：如用户ID、订单ID等
2. 合理设置分片数量：考虑未来扩展性
3. 跨分片查询处理：避免跨分片JOIN
4. 事务管理：分布式事务处理
5. 数据迁移工具：准备数据迁移方案

// 跨分片查询示例
class CrossShardQuery {
    public function getOrdersByDate($userId, $startDate, $endDate) {
        $orders = collect();
        // 如果不知道数据在哪个分片，需要查询所有分片
        foreach (['shard_0', 'shard_1'] as $connection) {
            $result = Order::on($connection)
                         ->where('user_id', $userId)
                         ->whereBetween('created_at', [$startDate, $endDate])
                         ->get();
            $orders = $orders->merge($result);
        }
        return $orders;
    }
}

注意事项

• 索引设计：分片键要有好的索引
• 数据一致性：跨库事务最好使用最终一致性方案
• 监控告警：监控各分片负载
• 备份恢复：按分片进行备份

选择哪种方案取决于你的具体业务需求、数据量和扩展计划，对于小型应用，建议从简单的模型层分库分表开始。