Boost PHP Database Performance: Persistent Connections, Connection Pools, and Caching

1. Use Persistent Connections

In PHP, persistent connections can speed up database connection by keeping the connection open across script executions, avoiding the overhead of establishing a new connection for each query.

To use persistent connections, employ the mysqli or PDO extensions and pass the MYSQLI_CLIENT_FOUND_ROWS or PDO::ATTR_PERSISTENT option when connecting.

Example using mysqli:

<?php
// 连接到数据库
$mysqli = new mysqli('localhost', 'username', 'password', 'database', null, null, MYSQLI_CLIENT_FOUND_ROWS);

// 检查连接是否成功
if ($mysqli->connect_errno) {
    die('连接数据库失败: ' . $mysqli->connect_error);
}

// 执行查询语句
$result = $mysqli->query('SELECT * FROM users');

// 处理结果集
while ($row = $result->fetch_assoc()) {
    echo $row['username'] . '<br>';
}

// 关闭数据库连接
$mysqli->close();
?>

2. Use Connection Pool

A connection pool maintains a set of pre‑created database connections that can be reused, reducing the time and resources needed to obtain a connection.

Using a pool improves the efficiency of database operations.

Example using the pdo_mysql extension to create a basic connection pool:

<?php
class ConnectionPool {
    private static $pool;
    private static $maxConnections = 10;
    private static $currentConnections = 0;

    private function __construct() {}

    public static function getConnection() {
        if (empty(self::$pool)) {
            self::$pool = new SplQueue();
        }
        if (self::$currentConnections < self::$maxConnections) {
            self::$currentConnections++;
            return self::createConnection();
        } else {
            if (!self::$pool->isEmpty()) {
                return self::$pool->dequeue();
            } else {
                return false;
            }
        }
    }

    public static function releaseConnection($connection) {
        self::$pool->enqueue($connection);
    }

    private static function createConnection() {
        // 创建数据库连接的代码
        $pdo = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');
        return $pdo;
    }
}
?>

When using the pool, call getConnection to obtain a connection and releaseConnection to return it to the pool.

3. Cache Query Results

If data changes infrequently, cache query results in memory to avoid repeated database access.

PHP can use Memcached or Redis for caching.

Example using Memcached:

<?php
// 连接到Memcached服务器
$memcached = new Memcached();
$memcached->addServer('localhost', 11211);

// 检查缓存中是否存在查询结果
$result = $memcached->get('users');

if (!$result) {
    // 查询数据库并将结果存入缓存
    $mysqli = new mysqli('localhost', 'username', 'password', 'database');
    $result = $mysqli->query('SELECT * FROM users')->fetch_all(MYSQLI_ASSOC);
    $memcached->set('users', $result, 3600); // 将结果缓存1小时
}

// 输出查询结果
foreach ($result as $row) {
    echo $row['username'] . '<br>';
}

// 关闭数据库连接
$mysqli->close();
?>

Caching reduces database queries and improves query speed.

Summary

Optimizing database connection speed can be achieved through persistent connections, connection pools, and caching query results. Persistent connections eliminate connection overhead, pools provide reusable connections, and caching reduces query frequency. Selecting the appropriate method based on system requirements can significantly improve database operation efficiency.