How to set up MySQL connection pool using PHP?

Using PHP to set up a MySQL connection pool can improve performance and scalability. The steps include: 1. Install the MySQLi extension; 2. Create a connection pool class; 3. Set the connection pool configuration; 4. Create a connection pool instance; 5. Obtain and release connections. With connection pooling, applications can avoid creating a new database connection for each request, thereby improving performance.

Setting up MySQL connection pool using PHP

Connection pooling is a mechanism for managing database connection resources, which can improve the performance and scalability of applications . A connection pool creates and maintains a predefined number of database connections that can be readily retrieved and used when needed.

To set up a MySQL connection pool using PHP, please follow these steps:

1. Install the MySQLi extension

MySQLi is a MySQL extension for PHP. It provides connection pooling functionality. Make sure the MySQLi extension is installed.

2. Create a connection pool class

Create a class to manage the connection pool. The class should contain methods such as connection pool creation, connection acquisition and connection release.

class ConnectionPool {

    private $pool;
    private $config;

    public function __construct(array $config) {
        $this->config = $config;
        $this->createPool();
    }

    private function createPool() {
        $this->pool = [];

        for ($i = 0; $i < $this->config['pool_size']; $i++) {
            $conn = new mysqli(
                $this->config['host'],
                $this->config['user'],
                $this->config['password'],
                $this->config['database']
            );

            $conn->autocommit(true);
            $this->pool[] = $conn;
        }
    }

    public function getConnection() {
        if (empty($this->pool)) {
            $this->createPool();
        }

        return array_pop($this->pool);
    }

    public function releaseConnection(mysqli $conn) {
        $this->pool[] = $conn;
    }

}

3. Set the connection pool configuration

Set the connection pool configuration in the connection pool class, including host, user name, password, database name and connection pool size.

$config = [
    'host' => 'localhost',
    'user' => 'root',
    'password' => 'password',
    'database' => 'test',
    'pool_size' => 10,
];

4. Create a connection pool instance

Instantiate the connection pool class and create a connection pool.

$pool = new ConnectionPool($config);

5. Get and release the connection

Use the getConnection() method to get the connection from the connection pool, and use releaseConnection() Method releases the connection.

$conn = $pool->getConnection();
// 使用连接...

$pool->releaseConnection($conn);

Practical case

The following code demonstrates how to use connection pooling to execute MySQL queries:

$pool = new ConnectionPool($config);
$conn = $pool->getConnection();

$result = $conn->query("SELECT * FROM users");

while ($row = $result->fetch_assoc()) {
    echo $row['name'] . "\n";
}

$pool->releaseConnection($conn);

By using connection pooling, you can improve your application Performance of your program because it avoids creating a new database connection for each request. This is especially useful in high concurrency situations.

The above is the detailed content of How to set up MySQL connection pool using PHP?. For more information, please follow other related articles on the PHP Chinese website!