Laravel 13 数据库连接池指南

Laravel 13 数据库连接池是提升数据库性能的重要技术，本文介绍连接池的原理和实现。

连接池概述

为什么需要连接池

<?php

namespace App\Services\Database;

class ConnectionPoolReason
{
    public static function withoutPool(): array
    {
        return [
            '每次请求都创建新连接',
            'TCP三次握手开销',
            '认证过程耗时',
            '连接数不可控',
            '资源浪费严重',
        ];
    }
    
    public static function withPool(): array
    {
        return [
            '连接复用，减少创建开销',
            '控制最大连接数',
            '连接预热',
            '健康检查',
            '负载均衡',
        ];
    }
}

连接池实现

基础连接池

<?php

namespace App\Services\Database;

use Illuminate\Database\Connection;
use Illuminate\Support\Facades\DB;
use RuntimeException;

class ConnectionPool
{
    protected string $connectionName;
    protected int $minConnections;
    protected int $maxConnections;
    protected int $idleTimeout;
    protected array $available = [];
    protected array $inUse = [];
    protected int $totalConnections = 0;
    
    public function __construct(
        string $connectionName = 'mysql',
        int $minConnections = 5,
        int $maxConnections = 20,
        int $idleTimeout = 300
    ) {
        $this->connectionName = $connectionName;
        $this->minConnections = $minConnections;
        $this->maxConnections = $maxConnections;
        $this->idleTimeout = $idleTimeout;
        
        $this->initialize();
    }
    
    protected function initialize(): void
    {
        for ($i = 0; $i < $this->minConnections; $i++) {
            $this->available[] = $this->createConnection();
        }
        
        $this->totalConnections = $this->minConnections;
    }
    
    public function getConnection(): PooledConnection
    {
        if (!empty($this->available)) {
            $connection = array_pop($this->available);
            $connection->setLastUsed(time());
            $this->inUse[$connection->getId()] = $connection;
            
            return $connection;
        }
        
        if ($this->totalConnections < $this->maxConnections) {
            $connection = $this->createConnection();
            $this->inUse[$connection->getId()] = $connection;
            $this->totalConnections++;
            
            return $connection;
        }
        
        throw new RuntimeException('Connection pool exhausted');
    }
    
    public function releaseConnection(PooledConnection $connection): void
    {
        $id = $connection->getId();
        
        if (isset($this->inUse[$id])) {
            unset($this->inUse[$id]);
            
            if ($connection->isHealthy()) {
                $connection->setLastUsed(time());
                $this->available[] = $connection;
            } else {
                $this->totalConnections--;
            }
        }
    }
    
    protected function createConnection(): PooledConnection
    {
        $pdo = DB::connection($this->connectionName)->getPdo();
        
        return new PooledConnection(
            id: uniqid('conn_'),
            pdo: $pdo,
            connectionName: $this->connectionName,
            createdAt: time(),
            lastUsedAt: time()
        );
    }
    
    public function cleanup(): int
    {
        $now = time();
        $cleaned = 0;
        
        $this->available = array_filter($this->available, function ($conn) use ($now, &$cleaned) {
            if ($now - $conn->getLastUsed() > $this->idleTimeout && 
                $this->totalConnections > $this->minConnections) {
                $this->totalConnections--;
                $cleaned++;
                return false;
            }
            return true;
        });
        
        return $cleaned;
    }
    
    public function getStats(): array
    {
        return [
            'total' => $this->totalConnections,
            'available' => count($this->available),
            'in_use' => count($this->inUse),
            'min_connections' => $this->minConnections,
            'max_connections' => $this->maxConnections,
        ];
    }
}

池化连接封装

<?php

namespace App\Services\Database;

use PDO;

class PooledConnection
{
    protected string $id;
    protected PDO $pdo;
    protected string $connectionName;
    protected int $createdAt;
    protected int $lastUsedAt;
    protected int $queryCount = 0;
    
    public function __construct(
        string $id,
        PDO $pdo,
        string $connectionName,
        int $createdAt,
        int $lastUsedAt
    ) {
        $this->id = $id;
        $this->pdo = $pdo;
        $this->connectionName = $connectionName;
        $this->createdAt = $createdAt;
        $this->lastUsedAt = $lastUsedAt;
    }
    
    public function getId(): string
    {
        return $this->id;
    }
    
    public function getPdo(): PDO
    {
        return $this->pdo;
    }
    
    public function getLastUsed(): int
    {
        return $this->lastUsedAt;
    }
    
    public function setLastUsed(int $time): void
    {
        $this->lastUsedAt = $time;
    }
    
    public function getQueryCount(): int
    {
        return $this->queryCount;
    }
    
    public function isHealthy(): bool
    {
        try {
            $this->pdo->query('SELECT 1');
            return true;
        } catch (\Throwable $e) {
            return false;
        }
    }
    
    public function getAge(): int
    {
        return time() - $this->createdAt;
    }
    
    public function getIdleTime(): int
    {
        return time() - $this->lastUsedAt;
    }
    
    public function query(string $sql, array $bindings = []): mixed
    {
        $this->queryCount++;
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($bindings);
        
        return $stmt;
    }
}

高级连接池

带健康检查的连接池

<?php

namespace App\Services\Database;

use Illuminate\Support\Facades\Log;

class HealthCheckedConnectionPool extends ConnectionPool
{
    protected int $healthCheckInterval = 60;
    protected int $maxConnectionAge = 3600;
    
    public function getConnection(): PooledConnection
    {
        $connection = parent::getConnection();
        
        if ($this->needsHealthCheck($connection)) {
            if (!$connection->isHealthy()) {
                $this->handleUnhealthyConnection($connection);
                return $this->getConnection();
            }
        }
        
        return $connection;
    }
    
    protected function needsHealthCheck(PooledConnection $connection): bool
    {
        return $connection->getIdleTime() > $this->healthCheckInterval ||
               $connection->getAge() > $this->maxConnectionAge;
    }
    
    protected function handleUnhealthyConnection(PooledConnection $connection): void
    {
        Log::warning('Unhealthy connection detected', [
            'connection_id' => $connection->getId(),
            'age' => $connection->getAge(),
        ]);
        
        $this->totalConnections--;
    }
    
    public function performHealthChecks(): array
    {
        $results = [
            'checked' => 0,
            'healthy' => 0,
            'unhealthy' => 0,
        ];
        
        foreach ($this->available as $key => $connection) {
            $results['checked']++;
            
            if ($connection->isHealthy()) {
                $results['healthy']++;
            } else {
                $results['unhealthy']++;
                unset($this->available[$key]);
                $this->totalConnections--;
            }
        }
        
        return $results;
    }
}

负载均衡连接池

<?php

namespace App\Services\Database;

use Illuminate\Support\Collection;

class LoadBalancedConnectionPool
{
    protected Collection $pools;
    protected string $strategy;
    
    public function __construct(array $connections, string $strategy = 'round_robin')
    {
        $this->pools = collect();
        $this->strategy = $strategy;
        
        foreach ($connections as $name => $config) {
            $this->pools->put($name, new ConnectionPool(
                $name,
                $config['min'] ?? 5,
                $config['max'] ?? 20
            ));
        }
    }
    
    public function getConnection(): PooledConnection
    {
        return match ($this->strategy) {
            'round_robin' => $this->roundRobin(),
            'least_connections' => $this->leastConnections(),
            'random' => $this->random(),
            default => $this->roundRobin(),
        };
    }
    
    protected function roundRobin(): PooledConnection
    {
        static $index = 0;
        $pool = $this->pools->values()[$index % $this->pools->count()];
        $index++;
        
        return $pool->getConnection();
    }
    
    protected function leastConnections(): PooledConnection
    {
        $pool = $this->pools->sortBy(fn($p) => $p->getStats()['in_use'])->first();
        
        return $pool->getConnection();
    }
    
    protected function random(): PooledConnection
    {
        return $this->pools->random()->getConnection();
    }
    
    public function releaseConnection(PooledConnection $connection, string $poolName): void
    {
        if ($pool = $this->pools->get($poolName)) {
            $pool->releaseConnection($connection);
        }
    }
    
    public function getStats(): array
    {
        return $this->pools->map(fn($pool, $name) => [
            'name' => $name,
            'stats' => $pool->getStats(),
        ])->toArray();
    }
}

连接池管理器

<?php

namespace App\Services\Database;

use Illuminate\Support\Manager;

class ConnectionPoolManager extends Manager
{
    protected array $pools = [];
    
    public function getPool(string $connection = null): ConnectionPool
    {
        $connection = $connection ?? $this->getDefaultDriver();
        
        if (!isset($this->pools[$connection])) {
            $this->pools[$connection] = $this->createPool($connection);
        }
        
        return $this->pools[$connection];
    }
    
    protected function createPool(string $connection): ConnectionPool
    {
        $config = config("database.connections.{$connection}", []);
        
        return new ConnectionPool(
            connectionName: $connection,
            minConnections: $config['pool']['min'] ?? 5,
            maxConnections: $config['pool']['max'] ?? 20,
            idleTimeout: $config['pool']['idle_timeout'] ?? 300
        );
    }
    
    public function getDefaultDriver(): string
    {
        return config('database.default', 'mysql');
    }
    
    public function cleanupAll(): array
    {
        $results = [];
        
        foreach ($this->pools as $name => $pool) {
            $results[$name] = $pool->cleanup();
        }
        
        return $results;
    }
    
    public function getStats(): array
    {
        $stats = [];
        
        foreach ($this->pools as $name => $pool) {
            $stats[$name] = $pool->getStats();
        }
        
        return $stats;
    }
}

连接池中间件

<?php

namespace App\Http\Middleware;

use App\Services\Database\ConnectionPoolManager;
use Closure;
use Illuminate\Http\Request;

class ConnectionPoolMiddleware
{
    protected ConnectionPoolManager $poolManager;
    
    public function __construct(ConnectionPoolManager $poolManager)
    {
        $this->poolManager = $poolManager;
    }
    
    public function handle(Request $request, Closure $next)
    {
        $response = $next($request);
        
        $this->poolManager->cleanupAll();
        
        return $response;
    }
}

连接池配置

<?php

return [
    'default' => env('DB_CONNECTION', 'mysql'),
    
    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
            
            'pool' => [
                'enabled' => env('DB_POOL_ENABLED', true),
                'min' => env('DB_POOL_MIN', 5),
                'max' => env('DB_POOL_MAX', 20),
                'idle_timeout' => env('DB_POOL_IDLE_TIMEOUT', 300),
                'max_age' => env('DB_POOL_MAX_AGE', 3600),
            ],
        ],
        
        'mysql_read' => [
            'driver' => 'mysql',
            'host' => env('DB_READ_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
            
            'pool' => [
                'enabled' => true,
                'min' => 3,
                'max' => 10,
            ],
        ],
        
        'mysql_write' => [
            'driver' => 'mysql',
            'host' => env('DB_WRITE_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
            
            'pool' => [
                'enabled' => true,
                'min' => 2,
                'max' => 10,
            ],
        ],
    ],
];

连接池监控

<?php

namespace App\Services\Database;

use Illuminate\Support\Facades\Log;

class ConnectionPoolMonitor
{
    protected ConnectionPoolManager $poolManager;
    
    public function __construct(ConnectionPoolManager $poolManager)
    {
        $this->poolManager = $poolManager;
    }
    
    public function collect(): array
    {
        $stats = $this->poolManager->getStats();
        
        foreach ($stats as $name => $poolStats) {
            $this->checkThresholds($name, $poolStats);
        }
        
        return $stats;
    }
    
    protected function checkThresholds(string $name, array $stats): void
    {
        $utilization = $stats['total'] > 0 
            ? $stats['in_use'] / $stats['total'] 
            : 0;
        
        if ($utilization > 0.9) {
            Log::warning('Connection pool near capacity', [
                'pool' => $name,
                'utilization' => round($utilization * 100, 2) . '%',
                'stats' => $stats,
            ]);
        }
        
        if ($stats['total'] >= $stats['max_connections']) {
            Log::critical('Connection pool at maximum capacity', [
                'pool' => $name,
                'stats' => $stats,
            ]);
        }
    }
    
    public function getHealth(): array
    {
        $stats = $this->poolManager->getStats();
        $health = [];
        
        foreach ($stats as $name => $poolStats) {
            $utilization = $poolStats['total'] > 0 
                ? $poolStats['in_use'] / $poolStats['total'] 
                : 0;
            
            $health[$name] = [
                'status' => $utilization > 0.9 ? 'critical' : ($utilization > 0.7 ? 'warning' : 'healthy'),
                'utilization' => round($utilization * 100, 2) . '%',
                'available' => $poolStats['available'],
                'in_use' => $poolStats['in_use'],
            ];
        }
        
        return $health;
    }
}

连接池命令

<?php

namespace App\Console\Commands;

use App\Services\Database\ConnectionPoolManager;
use Illuminate\Console\Command;

class ConnectionPoolCommand extends Command
{
    protected $signature = 'db:pool {action : Action to perform (stats|cleanup|health)}';
    protected $description = 'Manage database connection pools';
    
    public function handle(ConnectionPoolManager $poolManager): int
    {
        $action = $this->argument('action');
        
        return match ($action) {
            'stats' => $this->showStats($poolManager),
            'cleanup' => $this->cleanup($poolManager),
            'health' => $this->showHealth($poolManager),
            default => $this->invalidAction(),
        };
    }
    
    protected function showStats(ConnectionPoolManager $poolManager): int
    {
        $stats = $poolManager->getStats();
        
        foreach ($stats as $name => $poolStats) {
            $this->info("Pool: {$name}");
            $this->table(
                ['Metric', 'Value'],
                [
                    ['Total Connections', $poolStats['total']],
                    ['Available', $poolStats['available']],
                    ['In Use', $poolStats['in_use']],
                    ['Min Connections', $poolStats['min_connections']],
                    ['Max Connections', $poolStats['max_connections']],
                ]
            );
            $this->newLine();
        }
        
        return self::SUCCESS;
    }
    
    protected function cleanup(ConnectionPoolManager $poolManager): int
    {
        $results = $poolManager->cleanupAll();
        
        foreach ($results as $name => $cleaned) {
            $this->info("Pool {$name}: Cleaned {$cleaned} idle connections");
        }
        
        return self::SUCCESS;
    }
    
    protected function showHealth(ConnectionPoolManager $poolManager): int
    {
        $monitor = new \App\Services\Database\ConnectionPoolMonitor($poolManager);
        $health = $monitor->getHealth();
        
        foreach ($health as $name => $status) {
            $icon = match ($status['status']) {
                'healthy' => '<info>✓</info>',
                'warning' => '<comment>⚠</comment>',
                'critical' => '<error>✗</error>',
                default => '?',
            };
            
            $this->line("{$icon} {$name}: {$status['status']} (Utilization: {$status['utilization']})");
        }
        
        return self::SUCCESS;
    }
    
    protected function invalidAction(): int
    {
        $this->error('Invalid action. Use: stats, cleanup, or health');
        return self::FAILURE;
    }
}

总结

Laravel 13 的数据库连接池通过复用连接、控制连接数、健康检查等机制，可以显著提升数据库性能。合理配置连接池参数，并结合监控告警，可以确保数据库连接的高效稳定运行。