PHP项目如何优化数据库连接池？

本文目录导读：

1. 使用持久连接（Persistent Connections）
2. 使用连接池中间件
3. 框架级连接池优化
4. 自定义连接池实现
5. 优化策略总结
6. 推荐方案

在PHP项目中优化数据库连接池,需要根据项目架构（是否使用框架、PHP运行模式等）采取不同策略，以下是几种常见的优化方法和最佳实践：

使用持久连接（Persistent Connections）

对于传统PHP-FPM模式，可以使用持久连接避免重复创建连接：

// PDO 持久连接
$options = [
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass', $options);
// mysqli 持久连接
$mysqli = new mysqli('p:localhost', 'user', 'pass', 'test');

注意事项：

• 持久连接在FPM进程间重用,但需确保事务和锁的正确处理
• 可能遇到 MySQL has gone away 错误，需配合自动重连

使用连接池中间件

对于高并发场景,引入专门的连接池中间件：

1 ProxySQL

# 安装 ProxySQL
apt-get install proxysql
# 配置连接池
mysql -h127.0.0.1 -P6032 -uadmin -padmin
# 设置连接池大小
UPDATE mysql_servers SET max_connections=200 WHERE hostgroup_id=0;
LOAD MYSQL SERVERS TO RUNTIME;

2 Swoole 连接池（推荐）

// Swoole 协程连接池
class DbPool {
    private static $pool;
    public static function getPool() {
        if (empty(self::$pool)) {
            self::$pool = new Swoole\ConnectionPool(
                function () {
                    $config = config('database');
                    return new PDO(
                        "mysql:host={$config['host']};dbname={$config['dbname']}",
                        $config['username'],
                        $config['password']
                    );
                },
                $config['pool_size'] ?? 10
            );
        }
        return self::$pool;
    }
}
// 使用连接
$pool = DbPool::getPool();
$pdo = $pool->get();
try {
    $result = $pdo->query('SELECT * FROM users');
    // 业务逻辑
} finally {
    $pool->put($pdo); // 归还连接
}

框架级连接池优化

1 Laravel 数据库配置优化

// config/database.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', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => [
                PDO::ATTR_PERSISTENT => true,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
            ],
        ],
    ],
    // 连接池配置（需第三方包）
    'pool' => [
        'min' => 5,
        'max' => 20,
        'wait_timeout' => 3,
        'idle_timeout' => 60,
    ],
];

2 ThinkPHP 连接池配置

// config/database.php
return [
    'type' => 'mysql',
    'hostname' => '127.0.0.1',
    'database' => 'test',
    'username' => 'root',
    'password' => '',
    'hostport' => '3306',
    'params' => [
        PDO::ATTR_PERSISTENT => true,
        PDO::ATTR_CASE => PDO::CASE_NATURAL,
    ],
    // 连接池（需安装 think-orm-pool）
    'pool' => [
        'min_connections' => 5,
        'max_connections' => 100,
        'connect_timeout' => 10,
        'wait_timeout' => 3,
        'heartbeat' => -1,
        'max_idle_time' => 60,
    ],
];

自定义连接池实现

class DatabaseConnectionPool {
    private $pool = [];
    private $config;
    private $maxConnections;
    private $currentConnections = 0;
    public function __construct($config) {
        $this->config = $config;
        $this->maxConnections = $config['pool_size'] ?? 10;
    }
    public function getConnection() {
        // 检查空闲连接
        if (!empty($this->pool)) {
            return array_pop($this->pool);
        }
        // 创建新连接
        if ($this->currentConnections < $this->maxConnections) {
            $this->currentConnections++;
            return $this->createConnection();
        }
        // 等待可用连接
        throw new RuntimeException('All connections are busy');
    }
    public function releaseConnection($connection) {
        $this->pool[] = $connection;
    }
    private function createConnection() {
        $dsn = "mysql:host={$this->config['host']};dbname={$this->config['database']}";
        return new PDO($dsn, $this->config['username'], $this->config['password']);
    }
}
// 使用示例
$pool = new DatabaseConnectionPool($dbConfig);
$conn = $pool->getConnection();
try {
    $stmt = $conn->query('SELECT * FROM users');
    $users = $stmt->fetchAll();
} finally {
    $pool->releaseConnection($conn);
}

优化策略总结

1 参数优化

# MySQL 配置
max_connections = 500
wait_timeout = 60
interactive_timeout = 60
thread_cache_size = 32
# PHP-FPM 配置
pm.max_children = 50
pm.start_servers = 10
pm.min_spare_servers = 5
pm.max_spare_servers = 20

2 连接检查与健康检测

// 实现连接健康检查
function checkConnection($pdo) {
    try {
        $pdo->query('SELECT 1');
        return true;
    } catch (Exception $e) {
        return false;
    }
}
// 自动重连机制
function getConnection($pdo, $maxRetries = 3) {
    for ($i = 0; $i < $maxRetries; $i++) {
        if (checkConnection($pdo)) {
            return $pdo;
        }
        // 重新连接
        $pdo = reconnect();
    }
    throw new Exception('Cannot connect to database');
}

3 监控与告警

// 连接池使用率监控
class PoolMonitor {
    public static function recordUse($pool) {
        $stats = [
            'used' => $pool->getUsedConnections(),
            'idle' => $pool->getIdleConnections(),
            'total' => $pool->getMaxConnections(),
            'usage_rate' => $pool->getUsedConnections() / $pool->getMaxConnections(),
        ];
        // 记录到日志或监控系统
        Log::info('Connection pool stats', $stats);
        // 阈值告警
        if ($stats['usage_rate'] > 0.8) {
            Alert::warn('Database connection pool usage above 80%');
        }
    }
}

推荐方案

根据项目规模选择合适的方案：

关键建议：

1. 连接池大小建议设为 CPU核心数 * 2 + 硬盘数
2. 使用连接池时开启 SET NAMES utf8mb4 确保字符集一致
3. 定期运行 OPTIMIZE TABLE 避免连接泄露
4. 使用 EXPLAIN 分析慢查询，减少连接占用时间

选择哪种优化方式取决于你的项目架构、并发量和技术栈，对于新项目，推荐使用 Swoole 或 Hyperf 等协程框架，它们内置了成熟的连接池实现。