对比分析PHP和Java数据库连接查询,记录给Java项目添加数据库连接池的笔记

梦康 2015-08-30 00:00:00 854

轻量级的数据库连接池封装实际项目:https://github.com/zhoumengkang/netty-restful-server/tree/master/src/main/java/net/mengkang/nettyrest/mysql

使用 demo:https://github.com/zhoumengkang/netty-restful-server/blob/master/src/main/java/net/mengkang/demo/dao/UserDao.java

在项目开发中发现,很多 sql 查询0.01ms 的时间都不到,为什么在查询密集的情况下 api 的响应时间会很长呢?我并没有有使用Mybatis之类的第三方工具包,而是自己从最基础的封装逐步实现,也就是在每次查询都是查完了就关闭连接,没有对这个连接做复用。这样导致如果查询比较密集的情况下,即使本身查询语句没有问题,但是主要时间都消耗在了数据库的连接和断开上了。

在 PHP 中使用数据库时,我们一般的做法都是当前进程使用一个连接,不管是多少次查询,每次查询完毕之后,释放结果集,但是该连接不断,留着该进程中后面的查询继续使用。其实也是连接池的思想,只不过因为我们常规情况下都是单个进程,单个线程,代码都是顺序执行,该连接池中只有一个连接,并且生命周期也和该进程的生命周期同步。

为此我做了一个小测试,使用 php 做 api ,在该 api 中执行30次数据库查询。

for ($i=0; $i < 30; $i++) { 
    $sql = "select 1 from user limit where id=".$i;
    $res = $db->query($sql);
    echo "查询了".$i."次\n";
}
zhoumengkang$ mysql -uroot -pzmkzmk -e "show global status"|grep "Connections"
Warning: Using a password on the command line interface can be insecure.
Connections    30198
zhoumengkang$ php 2.php
查询了1次
查询了2次
查询了3次
查询了4次
查询了5次
查询了6次
查询了7次
查询了8次
查询了9次
查询了10次
查询了11次
查询了12次
查询了13次
查询了14次
查询了15次
查询了16次
查询了17次
查询了18次
查询了19次
查询了20次
查询了21次
查询了22次
查询了23次
查询了24次
查询了25次
查询了26次
查询了27次
查询了28次
查询了29次
查询了30次
数据库连接关闭了
zhoumengkang$ mysql -uroot -pzmkzmk -e "show global status"|grep "Connections"
Warning: Using a password on the command line interface can be insecure.
Connections    30200

在上面的例子里,我在析构函数里释放数据库连接,并且输出数据库连接关闭了,所以整个过程只和数据库建立了一次连接。

通过查询 mysql 的 Connections ,发现前后就多了2个,一次数我的脚本连接,一次是终端命令。

 

下面是对一段 Java 代码的压测,每次查询完都关闭数据库连接,我用 ab 压测了下

zhoumengkang$ ab -c100 -n1000 "http://localhost:8081/?method=test"
Requests per second:    17.24 [#/sec] (mean)
Time per request:       5800.506 [ms] (mean)

连接数直接增加了3万。耗时58秒。使用了连接池后,

Requests per second:    158.87 [#/sec] (mean)
Time per request:       629.462 [ms] (mean)

使用连接池候,我设置的最小连接数为20,查看 mysql 的 Connections 发现在项目启动之后,连接数增加了20,之后就没有变化。响应也减少了到6秒。

下面记录下我封装的连接池的笔记

0.别忘了配置 mysql 驱动

否则会在数据库连接池初始化时出现异常,提示找不到 mysql 驱动

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.18</version>
</dependency>

1.首先在 maven 里配置两个连接池依赖库

<dependency>
	<groupId>commons-dbcp</groupId>
	<artifactId>commons-dbcp</artifactId>
	<version>1.4</version>
</dependency>
<dependency>
	<groupId>commons-pool</groupId>
	<artifactId>commons-pool</artifactId>
	<version>1.6</version>
</dependency>

2.然后新建一个数据库连接池管理类

package me.topit.site.util.mysql;

import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

/**
 * Created by zhoumengkang on 25/8/15.
 */
public class JdbcPool {

    /**
     * 在java中,编写数据库连接池需实现java.sql.DataSource接口,每一种数据库连接池都是DataSource接口的实现
     * DBCP连接池就是java.sql.DataSource接口的一个具体实现
     */
    private static DataSource writeDataSource = null;
    private static DataSource readDataSource  = null;
    //在静态代码块中创建数据库连接池
    static{
        try{
            Properties writeProp = new Properties();
            writeProp.load(new InputStreamReader(JdbcPool.class.getResourceAsStream("/write.db.properties"),"UTF-8"));
            writeDataSource = BasicDataSourceFactory.createDataSource(writeProp);

            Properties readProp = new Properties();
            readProp.load(new InputStreamReader(JdbcPool.class.getResourceAsStream("/read.db.properties"),"UTF-8"));
            readDataSource = BasicDataSourceFactory.createDataSource(readProp);

        }catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    public static Connection getWriteConnection() throws SQLException{
        //从数据源中获取数据库连接
        return writeDataSource.getConnection();
    }

    public static Connection getReadConnection() throws SQLException{
        return readDataSource.getConnection();
    }

    public static void release(Connection conn,Statement st,ResultSet rs){
        if(rs!=null){
            try{
                //关闭存储查询结果的ResultSet对象
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if(st!=null){
            try{
                //关闭负责执行SQL命令的Statement对象
                st.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }

        if(conn!=null){
            try{
                //将Connection连接对象还给数据库连接池
                conn.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

}

3.并做了从库和主库的配置

主从配置的差不多,注意从库设置为只读

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/hujia
username=root
password=zmkzmk

#<!-- 初始化连接 -->
initialSize=20

#最大连接数量
maxActive=500

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000


#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property;]
#注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读(read-only)状态。
#如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix)
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

然后使用数据库连接池

//读
Connection conn = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
    conn = JdbcPool.getReadConnection();
    statement = conn.prepareStatement(sql);

    //...statement.setxxx()

    rs = statement.executeQuery();
}catch (SQLException e){
    e.printStackTrace();
}finally {
    JdbcPool.release(conn,statement,rs);
}
//写
Connection conn = null;
PreparedStatement statement = null;
ResultSet rs = null;
int id = 0;

try {
    conn = JdbcPool.getWriteConnection();
    statement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

    //...statement.setxxx()

    statement.executeUpdate();
    rs = statement.getGeneratedKeys();
    if (rs.next()){
        id = rs.getInt(1);
    }
}catch (SQLException e){
    e.printStackTrace();
}finally {
    JdbcPool.release(conn, statement, rs);
}