2.1.4. 性能优化之数据库连接池原理详解

服务系统 -> 从DB获取数据，每次都需要创建通道链接，用完之后需要关闭，每次的开销比较大，所以就出现了连接池。

2.1.4.1. 大体思路

• 初始化一部分链接
• 获取链接
  • 判断物理链接是否有效
  • 扩容连接池
• 创建链接
• 关闭链接（把池连接返回连接池）

总结：就是把物理链接获取来放入缓存，提供外部获取到有效的缓存中的物理链接的过程。

这里的代码就是一个骨架。不过思路也是学到了。

2.1.4.2. 实现代码

/**
 * 池链接
 * @author : zhuqiang
 * @version : V1.0
 * @date : 2017/2/12 16:02
 */
public class PooledConnection {
    // 真正的物理链接对象
    private Connection connection;
    // 是否被占用
    private boolean isBusy = false;

    public PooledConnection(Connection connection) {
        this.connection = connection;
    }

    public void close() {
        this.isBusy = false;
    }

    /**
     * 测试链接方法
     * @param sql
     * @return
     */
    public ResultSet queryBySqlTest(String sql) {
        Statement sm = null;
        ResultSet rs = null;
        try {
            sm = connection.createStatement();
            rs = sm.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public boolean isBusy() {
        return isBusy;
    }

    public void setBusy(boolean busy) {
        isBusy = busy;
    }
}


/**
 * 连接池
 * @author : zhuqiang
 * @version : V1.0
 * @date : 2017/2/12 15:59
 */
public interface IMyPool {
    PooledConnection getConnection();

    /**
     * 创建连接池
     * @param count 指定个数
     */
    void createConnection(int count);
}
/**
 * @author : zhuqiang
 * @version : V1.0
 * @date : 2017/2/12 16:03
 */
public class MyPoolImpl implements IMyPool {
    private Logger log = LoggerFactory.getLogger(getClass());
    private String jdbcDriver;
    private String jdbcUrl;
    private String userName;
    private String password;
    private int initCount; // 初始化数量
    private int stepSize; //步进数量，连接池最小一次性创建多少
    private int maxSize; //最大运行存在多少链接

    private Vector<PooledConnection> poolConnects = new Vector<>();

    public MyPoolImpl() {
        init();
    }

    // 初始化信息
    private void init() {
        InputStream is = this.getClass().getResourceAsStream("/jdbc.properties");
        Properties pro = new Properties();
        try {
            pro.load(is);
            this.jdbcDriver = pro.getProperty("jdbcDriver");
            this.jdbcUrl = pro.getProperty("jdbcUrl");
            this.userName = pro.getProperty("userName");
            this.password = pro.getProperty("password");
            this.initCount = Integer.valueOf(pro.getProperty("initCount", "1"));
            this.stepSize = Integer.valueOf(pro.getProperty("stepSize", "2"));
            this.maxSize = Integer.valueOf(pro.getProperty("maxSize", "5"));

            // 获取数据库驱动对象
            Driver driver = (Driver) Class.forName(this.jdbcDriver).newInstance();
            DriverManager.registerDriver(driver);
            createConnection(this.initCount);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public PooledConnection getConnection() {
        if (poolConnects.size() <= 0) {
            log.error("pools is no init");
            throw new RuntimeException("pools is no init");
        }
        // 获取一个有效的链接
        PooledConnection realConnection = getRealConnection();
        if (realConnection != null) {
            return realConnection;
        }

        while (realConnection == null) {
            // 需要扩容
            createConnection(stepSize);
            realConnection = getRealConnection();
            try {
                TimeUnit.MILLISECONDS.sleep(300);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
        return realConnection;
    }

    /**
     * 获取一个有效的链接
     * @return
     */
    private synchronized PooledConnection getRealConnection() {
        // 非占用，有效
        for (PooledConnection poolConnect : poolConnects) {
            if (poolConnect.isBusy()) {
                continue;
            }
            // 判断是否合法的
            try {
                // isValid 源码是去查询数据库，如果不能正常返回则说明无效
                if (!poolConnect.getConnection().isValid(2000)) {
                    Connection validConnection = DriverManager.getConnection(this.jdbcUrl, this.userName, this.password);
                    poolConnect.setConnection(validConnection);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            poolConnect.setBusy(true);
            return poolConnect;
        }
        return null;
    }

    @Override
    public void createConnection(int count) {
        int size = poolConnects.size();
        if (count > 0 && (size + count) < maxSize) {
            for (int i = 0; i < count; i++) {
                try {
                    Connection connection = DriverManager.getConnection(this.jdbcUrl, this.userName, this.password);
                    PooledConnection pooledConnection = new PooledConnection(connection);
                    poolConnects.add(pooledConnection);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } else {
            log.error("创建池链接失败：currentSize={},after create sie={},maxSize={},超过上限", size, size + count, maxSize);
            throw new RuntimeException("创建池链接失败：大于 maxSize");
        }
    }
}

2.1.4.3. 测试用例

public class MyPoolImplTest {
    private Logger log = LoggerFactory.getLogger(getClass());
    private IMyPool pool = new MyPoolImpl();

    private synchronized void selectData() throws SQLException {
        PooledConnection connection = pool.getConnection();
        ResultSet resultSet = connection.queryBySqlTest("SELECT * FROM sys_config");
        while (resultSet.next()) {
            log.info(resultSet.getString("SKEY"));
        }
        resultSet.close();
        // 这里一定要记得关闭
        connection.close();
    }

    @Test
    public void singleTest() throws SQLException, InterruptedException {
        for (int i = 0; i < 101; i++) {
            new Thread(new Runnable() {
                @Override
                public void run() {
                    try {
                        selectData();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }, i + "").start();
        }
        synchronized (this){
            wait();
        }
    }
}

2.1.4.4. 更多的测试信息

mysql 的最大链接数量在：MySQL\MySQL Server 5.5\my.ini 中max_connections=100，修改该数量可以有效的测试连接池。