Detailed introduction to database connection persistence in PHP

This article will give you a detailed introduction to database connection persistence in PHP. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

Database optimization is our top priority in web development, and in many cases we are actually programming for databases. Of course, all user operations and behaviors are saved in the form of data.

Among these, is there anything that can be optimized in the database connection creation process? The answer is of course yes. Languages ​​such as Java have connection pool settings, but PHP does not have such a thing as a connection pool in ordinary development. The connection pool technology is often used when multi-threading is involved, so PHP A new connection will be created every time it is run, so in this case, how do we optimize the data connection?

What is database connection persistence

Let’s first look at the definition of database connection persistence.

A persistent database connection refers to a connection that is not closed when the script ends running. When a persistent connection request is received. PHP will check whether there is already an identical persistent connection (that was opened previously).

If it exists, this connection will be used directly; if it does not exist, a new connection will be established. The so-called "same" connection refers to a connection to the same host using the same username and password.

Readers who do not fully understand the work of a web server and distributed load may misunderstand the role of persistent connections. In particular, persistent connections do not provide the ability to establish a "user session" on the same connection, nor do they provide the ability to effectively establish a transaction. In fact, strictly speaking, persistent connections do not provide any special functionality that non-persistent connections cannot provide.

This is connection persistence in PHP, but it also points out that persistent connections do not provide any special features that non-persistent connections cannot provide. This is very confusing. Isn't it said that this solution can improve performance?

What is the use of connection persistence?

Yes, judging from the special functions pointed out in the above definition, persistent connections do not bring new or more advanced functions, but its greatest use is to improve efficiency, and also It means performance will improve.

When the connection cost (Overhead) created by the Web Server to the SQL server is high (for example, it takes a long time and consumes more temporary memory), the persistent connection will be more efficient.

That is to say, when the connection cost is high, the cost of creating a database connection will be greater, and of course the time will be longer. After using persistent connections, each child process only performs a connection operation once in its life cycle, instead of making a connection request to the SQL server every time it processes a page. This means that each child process will establish its own independent persistent connection to the server.

For example, if 20 different child processes run a script to establish a persistent SQL server persistent connection, then 20 different persistent connections are actually established to the SQL server, one for each process.

Efficiency comparison

Without further ado, let’s compare directly through the code. First, we define a statistical function to return the current millisecond time. In addition, we also need to prepare the data connection parameters.

function getmicrotime()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float) $usec + (float) $sec);
}

$db = [
    'server' => 'localhost:3306',
    'user' => 'root',
    'password' => '',
    'database' => 'blog_test',
];

Next, we first use ordinary mysqli for testing.

$startTime = getmicrotime();
for ($i = 0; $i < 1000; $i++) {
    $mysqli = new mysqli($db["server"], $db["user"], $db["password"], $db["database"]); //持久连接
    $mysqli->close();
}
echo bcsub(getmicrotime(), $startTime, 10), PHP_EOL;
// 6.5814000000

In the process of creating a database connection through 1000 cycles, we spent more than 6 seconds. Next, we use persistent connections to create these 1,000 database connections. Just add a p: before the $host parameter of mysqli.

$startTime = getmicrotime();
for ($i = 0; $i < 1000; $i++) {
    $mysqli = new mysqli(&#39;p:&#39; . $db["server"], $db["user"], $db["password"], $db["database"]); //持久连接
    $mysqli->close();
}
echo bcsub(getmicrotime(), $startTime, 10), PHP_EOL;
// 0.0965000000

From the perspective of mysqli connection, the efficiency improvement is very obvious. Of course, the PDO database connection also provides the property of establishing a persistent connection.

$startTime = getmicrotime();
for ($i = 0; $i < 1000; $i++) {
    $pdo = new PDO("mysql:dbname={$db[&#39;database&#39;]};host={$db[&#39;server&#39;]}", $db[&#39;user&#39;], $db[&#39;password&#39;]);
}
echo bcsub(getmicrotime(), $startTime, 10), PHP_EOL;
// 6.6171000000

$startTime = getmicrotime();
for ($i = 0; $i < 1000; $i++) {
    $pdo = new PDO("mysql:dbname={$db[&#39;database&#39;]};host={$db[&#39;server&#39;]}", $db[&#39;user&#39;], $db[&#39;password&#39;], [PDO::ATTR_PERSISTENT => true]); //持久连接
}
echo bcsub(getmicrotime(), $startTime, 10), PHP_EOL;
// 0.0398000000

When connecting in PDO mode, you need to give a PDO::ATTR_PERSISTENT parameter and set it to true. In this way, the connection established by PDO also becomes a persistent connection.

Note

Since the persistent connection of the database is so powerful, why not default to this persistent connection form, and do we need to manually add parameters to achieve it? PHP developers certainly still have concerns.

If the number of child processes with persistent connections exceeds the set limit on the number of database connections, the system will cause some problems. If the database has a limit of 16 simultaneous connections, and in the case of a busy session, 17 threads try to connect, then one thread will fail to connect. If at this time, an error occurs in the script that prevents the connection from being closed (such as an infinite loop), the 16 connections to the database will be quickly affected.

At the same time, table locks and transactions also need attention.

When using a data table lock in a persistent connection, if the script cannot release the data table lock for any reason, subsequent scripts using the same connection will be permanently blocked, requiring the httpd service or database to be restarted. Serve

在使用事务处理时，如果脚本在事务阻塞产生前结束，则该阻塞也会影响到使用相同连接的下一个脚本

所以，在使用表锁及事务的情况下，最好还是不要使用持久化的数据库连接。不过好在持久连接和普通连接是可以在任何时候互换的，我们定义两种连接形式，在不同的情况下使用不同的连接即可解决类似的问题。

总结

事物总有两面性，持久连接一方面带来了效率的提升，但另一方面也可能带来一些业务逻辑上的问题，而且这种问题如果在不了解持久连接的机制的情况下会非常难排查。因此，在日常开发中我们一定要在了解相关功能特性的情况下再选择适合的方式来完成所需要的功能开发。

测试代码：

https://github.com/zhangyue0503/dev-blog/blob/master/php/202004/source/PHP%E4%B8%AD%E7%9A%84%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%8C%81%E4%B9%85%E5%8C%96.php

推荐学习：php视频教程

The above is the detailed content of Detailed introduction to database connection persistence in PHP. For more information, please follow other related articles on the PHP Chinese website!