PHP数据库Connection与Statement池化

PHP数据库Connection与Statement池化数据库连接池和Statement池化可以显著提升数据库密集型应用的性能。连接池复用TCP连接Statement池化复用SQL执行计划。今天说说PHP中的池化技术。连接池的核心是预先创建一批连接使用时获取使用后归还。phpclass 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池化可以避免重复解析SQLphpclass 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中每个请求结束后连接就释放了池化的意义不大