PHP数据库Connection与Statement池化

PHP数据库Connection与Statement池化

数据库连接池和Statement池化可以显著提升数据库密集型应用的性能。连接池复用TCP连接，Statement池化复用SQL执行计划。今天说说PHP中的池化技术。

连接池的核心是预先创建一批连接，使用时获取，使用后归还。

```php
class PdoConnectionPool
{
private array $pool = [];
private int $maxSize;
private int $currentSize = 0;
private string $dsn;
private string $user;
private string $pass;
private array $options;

public function __construct(string $dsn, string $user, string $pass, int $maxSize = 10, array $options = [])
{
$this->dsn = $dsn;
$this->user = $user;
$this->pass = $pass;
$this->maxSize = $maxSize;
$this->options = $options;
}

public function get(): PDO
{
// 从池中获取可用连接
foreach ($this->pool as $key => $connection) {
if (!$connection['in_use']) {
if ($this->isHealthy($connection['pdo'])) {
$this->pool[$key]['in_use'] = true;
$this->pool[$key]['last_used'] = time();
return $connection['pdo'];
}
unset($this->pool[$key]);
$this->currentSize--;
}
}

// 创建新连接
if ($this->currentSize < $this->maxSize) {
$pdo = $this->createConnection();
$this->pool[] = [
'pdo' => $pdo,
'in_use' => true,
'created_at' => time(),
'last_used' => time(),
];
$this->currentSize++;
return $pdo;
}

// 等待连接释放
$waitTime = 0;
while ($waitTime < 5000) {
foreach ($this->pool as $key => $connection) {
if (!$connection['in_use']) {
if ($this->isHealthy($connection['pdo'])) {
$this->pool[$key]['in_use'] = true;
$this->pool[$key]['last_used'] = time();
return $connection['pdo'];
}
unset($this->pool[$key]);
$this->currentSize--;
}
}
usleep(200);
$waitTime += 200;
}

throw new \RuntimeException("连接池已满，等待超时");
}

public function release(PDO $pdo): void
{
foreach ($this->pool as $key => $connection) {
if ($connection['pdo'] === $pdo) {
$this->pool[$key]['in_use'] = false;
return;
}
}
}

public function closeAll(): void
{
foreach ($this->pool as $connection) {
$connection['pdo'] = null;
}
$this->pool = [];
$this->currentSize = 0;
}

public function status(): array
{
$inUse = 0;
foreach ($this->pool as $conn) {
if ($conn['in_use']) $inUse++;
}

return [
'total' => $this->currentSize,
'in_use' => $inUse,
'idle' => $this->currentSize - $inUse,
'max' => $this->maxSize,
];
}

private function createConnection(): PDO
{
return new PDO($this->dsn, $this->user, $this->pass, $this->options + [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
}

private function isHealthy(PDO $pdo): bool
{
try {
$pdo->query('SELECT 1');
return true;
} catch (\Exception $e) {
return false;
}
}

public function __destruct()
{
$this->closeAll();
}
}

class PooledDatabase
{
private PdoConnectionPool $pool;

public function __construct(PdoConnectionPool $pool)
{
$this->pool = $pool;
}

public function query(string $sql, array $params = []): array
{
$pdo = $this->pool->get();
try {
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
} finally {
$this->pool->release($pdo);
}
}

public function execute(string $sql, array $params = []): int
{
$pdo = $this->pool->get();
try {
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return $stmt->rowCount();
} finally {
$this->pool->release($pdo);
}
}
}
?>

Statement池化可以避免重复解析SQL：

```php
class StatementPool
{
private array $statements = [];
private PdoConnectionPool $pool;

public function __construct(PdoConnectionPool $pool)
{
$this->pool = $pool;
}

public function execute(string $sql, array $params = []): array
{
$hash = md5($sql);

if (!isset($this->statements[$hash])) {
$pdo = $this->pool->get();
try {
$this->statements[$hash] = [
'statement' => $pdo->prepare($sql),
'pdo' => $pdo,
];
} finally {
$this->pool->release($pdo);
}
}

$this->statements[$hash]['statement']->execute($params);
return $this->statements[$hash]['statement']->fetchAll();
}
}
?>

池化技术的关键是资源复用和健康检查。连接池减少TCP握手的开销，Statement池化减少SQL解析的次数。在Swoole等常驻内存环境中，池化的效果更明显。但在PHP-FPM中，每个请求结束后连接就释放了，池化的意义不大