php持久化连接数据库

php的持久化数据库连接已经不是一个新鲜的名词了,由于php语言生命周期的问题,如果每次连接数据库都重新打开一个连接会很低效,所以引入了长连接机制(应该是实现在sapi部分,例如cli sapi就不支持db长连接),本文探究apache2handler sapi和 fpm sapi是否支持db长连接.使用方法以pdo为例在实例化pdo对象时传入:

PDO::ATTR_PERSISTENT => true

即可实现长连接,但是隐约在哪里听说过在apache下由于是以线程的方式执行,所以连接在线程关闭时也会释放掉.本着实践是检验真理的唯一标准态度,遂决定做个实验测试下.

测试环境:
mysql-5.1.50
php-5.4.19
apache && nginx

php测试代码:

<?php
$pdo = new PDO(
    sprintf('mysql:dbname=%s;host=%s;port=%d', 'test', 'localhost', '3306'),
    'test',
    'test',
    array(
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
        //PDO::ATTR_PERSISTENT => true #pdo默认为false
    )
);

var_dump($pdo->query('select now()')->fetchColumn());

测试php-fpm sapi(nginx):
首先查看数据库连接:

mysql> show full processlist;
+----+------+-----------------+------+---------+------+-------+-----------------------+
| Id | User | Host            | db   | Command | Time | State | Info                  |
+----+------+-----------------+------+---------+------+-------+-----------------------+
|  1 | root | localhost:61513 | test | Query   |    0 | NULL  | show full processlist |
+----+------+-----------------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

访问php测试脚本后查看数据库连接:

mysql> show full processlist;
+----+------+-----------------+------+---------+------+-------+-----------------------+
| Id | User | Host            | db   | Command | Time | State | Info                  |
+----+------+-----------------+------+---------+------+-------+-----------------------+
|  1 | root | localhost:61513 | test | Query   |    0 | NULL  | show full processlist |
+----+------+-----------------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

将测试脚本中PDO::ATTR_PERSISTENT => true的注释取消掉后执行脚本,再查看数据库连接:

mysql> show full processlist;
+----+------+-----------------+------+---------+------+-------+-----------------------+
| Id | User | Host            | db   | Command | Time | State | Info                  |
+----+------+-----------------+------+---------+------+-------+-----------------------+
|  1 | root | localhost:61513 | test | Query   |    0 | NULL  | show full processlist |
|  3 | test | localhost:61525 | test | Sleep   |    2 |       | NULL                  |
+----+------+-----------------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)

结论1:fpm sapi支持db长连接

测试apache2handler sapi(apache):
首先关闭掉长连接会话:

mysql> kill 3;
Query OK, 0 rows affected (0.00 sec)

查看数据库会话列表:

mysql> show full processlist;
+----+------+-----------------+------+---------+------+-------+-----------------------+
| Id | User | Host            | db   | Command | Time | State | Info                  |
+----+------+-----------------+------+---------+------+-------+-----------------------+
|  1 | root | localhost:61513 | test | Query   |    0 | NULL  | show full processlist |
+----+------+-----------------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

执行测试脚本(注释掉PDO::ATTR_PERSISTENT => true,即未设置长连接的脚本)并查看数据库会话:

mysql> show full processlist;
+----+------+-----------------+------+---------+------+-------+-----------------------+
| Id | User | Host            | db   | Command | Time | State | Info                  |
+----+------+-----------------+------+---------+------+-------+-----------------------+
|  1 | root | localhost:61513 | test | Query   |    0 | NULL  | show full processlist |
+----+------+-----------------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

然后执行设置好长连接的脚本并查看数据库会话:

mysql> show full processlist;
+----+------+-----------------+------+---------+------+-------+-----------------------+
| Id | User | Host            | db   | Command | Time | State | Info                  |
+----+------+-----------------+------+---------+------+-------+-----------------------+
|  1 | root | localhost:61513 | test | Query   |    0 | NULL  | show full processlist |
|  5 | test | localhost:61569 | test | Sleep   |    2 |       | NULL                  |
+----+------+-----------------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)

结论2:apache2handler sapi也是支持db长连接的