前言
最近写golang发现go的sql包中默认实现的一个数据库连接池怪简单的，想着之前也没仔细看看java中的实现。顺带看了一下java的HikariCP初期版本的实现逻辑也差不多一致（后期就比较复杂了之后再看）。大致如下：
- 初始生产最小数量连接到一个线程安全的容器中
idleConnections - getConnection从
idleConnections获取连接，若有直接返回，没有看是否生成的连接小于最大连接数。若小于直接生成并返回。若等于就是阻塞直到idleConnections有空闲连接。 - 使用完成后放回
idleConnections
实现测试效果
测试出来比HikariCP效果还要好，不知道是不是哪里没配置好？
MySQL8.2 13600KF+32G
HikariCP 5.0.1
JDK 11
Thread NUM 512
QUERY COUNT 512*10000
HikariCP=======================================
size :5120000
total took:72732
min :0
max :442
avg :0.01420546875
5ms :5118653,percent:99.97369140625
20ms :722,percent:0.0141015625
50ms :113,percent:0.00220703125
100ms :65,percent:0.00126953125
===============================================
SimplePool=====================================
size :5120000
total took:16784
min :0
max :541
avg :0.003278125
5ms :5119943,percent:99.99888671875
20ms :36,percent:7.03125E-4
50ms :7,percent:1.3671874999999999E-4
100ms :0,percent:0.0
===============================================
SimplePool实现代码
package com.tsq.simple;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicInteger;
/**
* 一个简单的数据库连接池
*
* @author tangsq
* @date 2024/09/05
*/
public class SimplePool implements DbPool {
/**
* 空闲回收池
*/
private final LinkedBlockingQueue<Connection> idlePool;
/**
* 使用池，这个可以不要但可能不归还，Hirkia是使用的代理连接close时会归还
*/
private final LinkedBlockingQueue<Connection> busyPool;
/**
* 当前new的连接
*/
private final AtomicInteger activeSize = new AtomicInteger(0);
/**
* 配置
*/
private final SimpleConfig configuration;
private final DataSource dataSource;
SimplePool(SimpleConfig configuration) throws ClassNotFoundException, InstantiationException, IllegalAccessException, NoSuchMethodException, InvocationTargetException, SQLException {
this.configuration = configuration;
idlePool = new LinkedBlockingQueue<>();
busyPool = new LinkedBlockingQueue<>();
Class<?> clazz = Class.forName(configuration.getDriverClassName());
this.dataSource = (DataSource) clazz.getDeclaredConstructor().newInstance();
//设置属性
setPropertyToDataSource();
addConnections(this.configuration.getMinPoolSize());
}
public void setPropertyToDataSource() throws IllegalAccessException {
//反射设置属性值,这里标准的dataSource并没有定义,而且反射出的数据源由不同厂家提供可能有定制属性
//通过getDeclaredFields()方法获取对象类中的所有属性（含私有）
Field[] configFields = configuration.getClass().getDeclaredFields();
Map<String, Object> configFieldMap = new HashMap<>();
for (Field field : configFields) {
field.setAccessible(true);
configFieldMap.put(field.getName(), field.get(this.configuration));
}
// 获取当前类的父类
Class<?> parent = this.dataSource.getClass();
// 递归遍历父类
while (parent != null) {
Field[] dataSourceFields = parent.getDeclaredFields();
//遍历属性
for (Field field : dataSourceFields) {
//通过setAccessible()设置为true,允许通过反射访问私有变量
Object value = configFieldMap.get(field.getName());
if (value == null) {
continue;
}
field.setAccessible(true);
field.set(this.dataSource, value);
}
// 获取父类的父类
parent = parent.getSuperclass();
}
}
@Override
public void init() {
}
public void addConnections(int upper) throws SQLException {
for (int i = activeSize.get(); i < upper; i++) {
//可以增加测试联通sql
addConnection();
}
}
public Connection addConnection() throws SQLException {
Connection connection = this.dataSource.getConnection();
this.idlePool.add(connection);
activeSize.addAndGet(1);
return connection;
}
@Override
public Connection getConnection() throws SQLException, InterruptedException {
Connection conn = idlePool.poll();
if (conn != null) {
busyPool.offer(conn);
return conn;
}
if (activeSize.get() < configuration.getMaxPoolSize()) {
//新增连接
return addConnection();
}
// 如果空闲池中连接数达到maxSize， 则阻塞等待归还连接
conn = idlePool.poll(10000, TimeUnit.MILLISECONDS);// 阻塞获取连接，如果10秒内有其他连接释放，
if (conn == null) {
throw new RuntimeException("等待连接超时");
}
busyPool.offer(conn);
return conn;
}
@Override
public void release(Connection connection) {
if (connection != null) {
busyPool.remove(connection);
idlePool.offer(connection);
}
}
@Override
public void destroy() {
}
测试代码
public static void main(String[] args) throws ClassNotFoundException, InvocationTargetException, InstantiationException, IllegalAccessException, NoSuchMethodException, SQLException, InterruptedException {
System.out.println("HikariCP=======================================");
HikariTest();
System.out.println("===============================================");
System.out.println("SimplePool=====================================");
SimpleTest();
System.out.println("===============================================");
}
public static void SimpleTest() throws SQLException, ClassNotFoundException, InvocationTargetException, InstantiationException, IllegalAccessException, NoSuchMethodException, InterruptedException {
LinkedBlockingQueue<Long> time = new LinkedBlockingQueue<>();
SimpleConfig simpleConfig = new SimpleConfig();
simpleConfig.setIdleTimeout(1000);
simpleConfig.setConnectionTimeout(1000);
simpleConfig.setJdbcUrl("jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=UTF-8");
simpleConfig.setMinPoolSize(10);
simpleConfig.setMaxPoolSize(100);
simpleConfig.setDatabaseName("rank");
simpleConfig.setDriverClassName("com.mysql.cj.jdbc.MysqlDataSource");
simpleConfig.setUser("root");
simpleConfig.setPassword("root");
SimplePool simplePool = new SimplePool(simpleConfig);
int THREAD_COUNT = 512;
ExecutorService executor = Executors.newFixedThreadPool(THREAD_COUNT);
for (int k = 0; k < 10000; k++) {
CountDownLatch countDownLatch1 = new CountDownLatch(THREAD_COUNT);
for (int i = 0; i < THREAD_COUNT; i++) {
executor.submit(() -> {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
long start1 = System.currentTimeMillis();
connection = simplePool.getConnection();
long start2 = System.currentTimeMillis();
time.add(start2 - start1);
preparedStatement = connection.prepareStatement("select * from post1 limit 1");
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String id = resultSet.getString(1);
}
} catch (Exception e) {
System.out.println(e);
} finally {
try {
if (resultSet != null) {
resultSet.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
} catch (SQLException e) {
System.out.println(e);
}
simplePool.release(connection);
countDownLatch1.countDown();
}
});
}
countDownLatch1.await();
}
int size = time.size();
long max = 0;
long min = 0;
long total = 0;
long ms5 = 0;
long ms20 = 0;
long ms50 = 0;
long ms100 = 0;
for (long t : time) {
if (t > max) {
max = t;
}
if (t < min) {
min = t;
}
if (t <= 5) {
ms5++;
}
if (5 < t && t <= 20) {
ms20++;
}
if (20 < t && t <= 50) {
ms50++;
}
if (50 < t && t <= 100) {
ms100++;
}
total += t;
}
System.out.println("size :" + size);
System.out.println("total took:" + total);
System.out.println("min :" + min);
System.out.println("max :" + max);
System.out.println("avg :" + total * 1.0 / size);
System.out.println("5ms :" + ms5 + ",percent:" + ms5 * 1.0 / size * 100);
System.out.println("20ms :" + ms20 + ",percent:" + ms20 * 1.0 / size * 100);
System.out.println("50ms :" + ms50 + ",percent:" + ms50 * 1.0 / size * 100);
System.out.println("100ms :" + ms100 + ",percent:" + ms100 * 1.0 / size * 100);
}
public static void HikariTest() throws SQLException, ClassNotFoundException, InvocationTargetException, InstantiationException, IllegalAccessException, NoSuchMethodException, InterruptedException {
LinkedBlockingQueue<Long> time = new LinkedBlockingQueue<>();
HikariConfig config = new HikariConfig();
// jdbc properties
config.setDriverClassName("com.mysql.cj.jdbc.MysqlDataSource");
config.setJdbcUrl("jdbc:mysql://localhost:3306/rank?useUnicode=true&characterEncoding=UTF-8");
config.setUsername("root");
config.setPassword("root");
config.setMinimumIdle(10);
config.setMaximumPoolSize(100);
config.setConnectionTimeout(30000);
config.setMaxLifetime(0);
config.setIdleTimeout(0);
HikariDataSource hikariDataSource = new HikariDataSource(config);
int THREAD_COUNT = 512;
ExecutorService executor = Executors.newFixedThreadPool(THREAD_COUNT);
for (int k = 0; k < 10000; k++) {
CountDownLatch countDownLatch1 = new CountDownLatch(THREAD_COUNT);
for (int i = 0; i < THREAD_COUNT; i++) {
executor.submit(() -> {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
long start1 = System.currentTimeMillis();
connection = hikariDataSource.getConnection();
long start2 = System.currentTimeMillis();
time.add(start2 - start1);
preparedStatement = connection.prepareStatement("select * from post1 limit 1");
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String id = resultSet.getString(1);
}
} catch (Exception e) {
System.out.println(e);
} finally {
try {
if (resultSet != null) {
resultSet.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
} catch (SQLException e) {
System.out.println(e);
}
try {
assert connection != null;
connection.close();
} catch (SQLException e) {
System.out.println(e);
}
countDownLatch1.countDown();
}
});
}
countDownLatch1.await();
}
int size = time.size();
long max = 0;
long min = 0;
long total = 0;
long ms5 = 0;
long ms20 = 0;
long ms50 = 0;
long ms100 = 0;
for (long t : time) {
if (t > max) {
max = t;
}
if (t < min) {
min = t;
}
if (t <= 5) {
ms5++;
}
if (5 < t && t <= 20) {
ms20++;
}
if (20 < t && t <= 50) {
ms50++;
}
if (50 < t && t <= 100) {
ms100++;
}
total += t;
}
System.out.println("size :" + size);
System.out.println("total took:" + total);
System.out.println("min :" + min);
System.out.println("max :" + max);
System.out.println("avg :" + total * 1.0 / size);
System.out.println("5ms :" + ms5 + ",percent:" + ms5 * 1.0 / size * 100);
System.out.println("20ms :" + ms20 + ",percent:" + ms20 * 1.0 / size * 100);
System.out.println("50ms :" + ms50 + ",percent:" + ms50 * 1.0 / size * 100);
System.out.println("100ms :" + ms100 + ",percent:" + ms100 * 1.0 / size * 100);
}
