The advantages and disadvantages of php encapsulating pdo instances and pdo long connections

This article brings you examples of PHP encapsulation of pdo in PHP, as well as relevant knowledge about pdo long connections. As the name suggests, long connections always maintain the connection. Compared with the usual short connections, the link will be re-created with each request. It is said that long connections can effectively reduce the creation process and better save performance. I hope everyone has to help!

##1. Foreword

Recently I need to write a script to realize the storage of crash logs. As expected, it is separated from the framework, so okay, We can only encapsulate database-related operations ourselves. The blogger here chose to encapsulate

pdo to operate the database.

2. Why choose pdo

As we all know,

php had mysql extension in the early days, but later it was missing because it was too old The new features of mysql have been introduced, so the primary key has declined.

Starting from

php5, it is recommended that you use the mysqli extension. This is an enhanced version of the mysql extension and is an object-orientedMySQL interface, easier to use. The disadvantage is that it can only operate mysql and is not powerful enough.

There is also the

pdo extension. This is the richest extension, supporting a variety of databases. The important thing is that it is stronger than the other two extensions in terms of security. By using prepared preprocessing can effectively prevent sql injection. Therefore, the blogger here chose to encapsulate pdo related operations.

3. PDO’s long connection

1. What is PDO’s long connection?

As the name suggests, a long connection means that the connection is always maintained. Compared with the usual short connection, each request In terms of re-creating links, long connections can effectively reduce the creation process and save performance.

In operation, when connecting to the database, add one more parameter:

$pdo = new PDO($dsn, $username, $passwd, [PDO::ATTR_PERSISTENT => true]);

The following

PDO::ATTR_PERSISTENT => true is the method to open a long connection.

2. Is long connection invalid for nginx? ##nginx

, is this true?

参考博文地址：https://www.cnblogs.com/wpjamer/articles/7106389.html

The general conclusion is: long connections are more targeted at apache, because

apache

maintains a process pool and enables apache mpmAfter the function, apache will maintain a process pool by default. mysqlThe connection after the long connection is not closed as a socet connection, but as a non-released one. Things are put into the process pool/thread pool. And for nginx, long connections are invalid. Will the resources be released when the script execution ends?

3. Long connection test under php-fpmThe seniors here have already tested it. We will give the seniors’ addresses. If you are interested, you can take a look

参考博文地址：https://hacpai.com/article/1526490593632

Conclusion:

Facts have proved that

php-fpm can achieve long connections, but if the process is idle, it will cause a waste of resources.
The configuration file of php-fpm can consider setting

pm.max_requests = 1000

, which represents the maximum number of service requests for each sub-process. If this value is exceeded, The child process will be automatically restarted. For example, if the parameter max_requests is set to a very large value, the child process will have to run many times before restarting. If an error or memory leak occurs in this request, then this value will be set to a very large value. Big is inappropriate. But if there is no problem with the request, if this value is set to a small value, it will restart frequently, which will also encounter a lot of

502

problems, so it is a matter of judgment and wisdom. Here is the initialization setting1000, if the test does not have memory leaks and other problems, it can be larger. 4. The impact of long connections on transactions<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">参考博文地址：https://www.zhihu.com/question/62603122</pre><div class="contentsignin">Copy after login</div></div>

Summary:

If the business concurrency is relatively large and there are transactions, it is not recommended to use long connections.

5. Summary During the constant search, the blogger found that in order to achieve the best performance of long connections, connection pooling cannot be avoided, and PHP is not very good. The implementation of connection pool is indeed a bit regretful.

Generally speaking, it is temporarily impossible to configure a perfect connection pool with

mysql

in

php

. In places where the business is more complex, it is better to try long connections with caution. Each connection is a thread, which will cause a lot of waste of resources. <p>      如果是某些业务需要持续的数据库操作，比如提交日志接口等，那么是可以考虑打开长连接的，记得设置<code>max_requests来定量关闭php-fpm连接，fpm关闭之后也会自动释放mysql的连接。

      还有pm.max_spare_servers设置服务器空闲时最大php-fpm进程数量。

例如： pm.max_spare_servers = 25 如果空闲时，会检查进程数，多于25个了，就会关闭几个，达到25个的状态。

      擅长swoole的同学，可以参考这篇文章：
基于swoole扩展实现真正的PHP数据库连接池

四、pdo部分demo的封装

      首先这部分博主是参考了一个网友的封装，github地址如下：

https://github.com/nadirvishun/php-pdo-class

      这个网友基本的增删改查都封装好了，而且都有参数预处理，安全性还是可以的。不过既然作为一个基准的类，还是缺少一些东西。

1、断线重连机制

例如重连函数：

    /**
     * @params:重连函数，上限3次
     * @date:2020/3/18
     * @time:17:03
     */
    public function customConnect()
    {
        try {
            $this->pdo = new PDO($this->config['dsn'], $this->config['username'], $this->config['password'], $this->config['params']);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //需要将错误处理模式变成异常模式
            return true;
        } catch (Exception $e) {
            if (stripos($e->getMessage(), 'MySQL server has gone away') !== false || stripos($e->getMessage(),' bytes failed with errno=10053') !==false) {
                $this->close();
                $this->tryNums++;
                if ($this->tryNums > 3) {
                    return false;
                }
                self::customConnect();
            } else {
                $this->throw_exception($e->getMessage());
                return false;
            }
        }
    }

2、转化php warnings为try…catch可捕获的错误

      这步原因是长连接会频繁的造成mysql gone away错误，而这个错误是php 的warnings级别错误，try..catch根本就捕获不到，所以博主这里自定义错误处理函数来处理。

      这部分是转化php的warnings错误为try..catch可以捕获的error错误，关于php的报错机制以及错误处理这块，咱们下篇再讨论。

//自定义warnings处理函数set_error_handler('customException');//拿到warnngs错误之后，转化为error错误抛出，这样就可以被try..catch捕获function customException( $error_no, $error_msg, $error_file, $error_line){
    throw new \Exception($error_msg,0,null);
    //throw new \Exception($error_msg);}

3、析构方法回收资源

    /**
     * destruct 关闭数据库连接
     */
    public function destruct()
    {
        $this->pdo = null;
    }

4、query的时候ping一下

  public function query($sql = null, $param = null)
    {
        //检测连接是否活跃
        $this->pdo_ping();
        //判断之前是否有结果集
        if (!empty($this->PDOStatement)) {
            $this->free();
        }
        xxxxxxxxxx        }

5、下载地址

      这四步完善之后，这个pdo的类还是可以用的，大家需要的话可以去百度云上下载。

链接: https://pan.baidu.com/s/1Siz_bKlhEIVNV99Y0zTzqw 提取码: ebqx

大家如果感兴趣的话，可以点击《PHP视频教程》进行更多关于PHP知识的学习。

The above is the detailed content of The advantages and disadvantages of php encapsulating pdo instances and pdo long connections. For more information, please follow other related articles on the PHP Chinese website!