数据库连接池的使用

百世经纶『一页書』

已于 2022-08-03 16:43:01 修改

阅读量2.8k

点赞数 1

分类专栏： Java DATABASE 文章标签：数据库

于 2022-07-29 09:50:05 首次发布

本文链接：https://blog.csdn.net/weixin_43916074/article/details/126047939

版权

Java 同时被 2 个专栏收录

85 篇文章 1 订阅

订阅专栏

DATABASE

15 篇文章 0 订阅

订阅专栏

数据库连接池的使用

在这里插入图片描述

JDBC: https://www.educba.com/jdbc-interview-questions/.

1. Java连接数据库

数据库连接四步走：

注册驱动
获取链接
拿到statement，执行sql
处理结果

java连接数据库是通过JDBC技术，JDBC的全称是Java DataBase Connectivity,事实上，JDBC并不能直接访问数据库的，必须依赖于数据库厂商提供的驱动程序。

//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//获取链接
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/student", "root", "root");
//拿到statement
Statement state=conn.createStatement();
//执行sql，处理结果
ResultSet rs=st.executeQuery("select * from tb_stu");

1.1 长短连接

短连接

所谓短连接就是指应用程序和数据库通信完毕之后连接关闭。
频繁的建立/释放连接增加了系统负担
应用程序每次操作数据库的过程将会变得很卡
释放的连接不会马上被执行，服务器端口资源被消耗完，table lock，如果每秒几千次请求，就会连接不上数据库connection timeout

长连接:

即在建立连接后一直打开，直到应用程序关闭才释放。使用长连接的好处是减少每次创建连接带来的开销。
对于数据库服务器来说，过多的长连接则是灾难。

2. 连接池

Hikari是一款非常强大，高效，并且号称“史上最快连接池”。由于其性能方面比较好，并且在springboot2.0之后，采用的默认数据库连接池就是Hikari,在引用parents后不用专门再添加依赖。

2.1 performance

hikariCP > druid > tomcat-jdbc > dbcp > c3p0

2.2 application.yml

前面说了，springboot2.0以后默认使用Hikari，配置信息如下：

spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/ssm
spring.datasource.username = root
spring.datasource.password = root

Hikari git website: https://github.com/brettwooldridge/HikariCP.

最小空闲连接数量
spring.datasource.hikari.minimum-idle=5
空闲连接存活最大时间，默认600000（10分钟）
spring.datasource.hikari.idle-timeout=180000
连接池最大连接数，默认是10
spring.datasource.hikari.maximum-pool-size=10
连接池名称
spring.datasource.hikari.pool-name=MyHikariCP
池中连接的最长生命周期，值0表示无限生命周期，默认1800000即30分钟
spring.datasource.hikari.max-lifetime=1800000
数据库连接超时时间,默认30秒，即30000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.connection-test-query=SELECT 1

spring:
  datasource:
     #账号配置
     url: jdbc:mysql://127.0.0.1:3306/xxxxx
     username: xxxxx
     password: xxxxx
     driver-class-name: com.mysql.jdbc.Driver
     type: com.zaxxer.hikari.HikariDataSource
     
     #hikari数据库连接池
     hikari:
       pool-name: MyHikariCP
       minimum-idle: 5                  #最小空闲连接数量
       idle-timeout: 180000             #空闲连接存活最大时间，默认600000（10分钟）
       maximum-pool-size: 10            #连接池最大连接数，默认是10
       auto-commit: true                #此属性控制从池返回的连接的默认自动提交行为,默认值：true
       max-lifetime: 1800000            #此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认1800000即30分钟
       connection-timeout: 30000        #数据库连接超时时间,默认30秒，即30000
       connection-test-query: SELECT 1

3. Mysql

MYSQL 的最大连接数在5.7版本中默认是151，最大可以达到16384（2^14）

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 5050  |
+-----------------+-------+
1 row in set (0.00 sec)

设置最大连接数
set GLOBAL max_connections=100;
修改配置文件
vim /etc/my.cnf
max_connections=100
sudo systemctl start mysqld.service

4. Oracle

Oracle的连接数默认是150

查看当前连接数
select count(*) from v$process;
查询最大连接数
show parameter processes;
修改最大连接数(上面报警就执行下面的)
alter system set processes = 600 scope = both;
alter system set processes = 300 scope = spfile;

5. Sqlserver(mssql)

默认值为：0，即无限制。

查看当前连接数
select count(distinct(login_time)) from sys.sysprocesses ;
查询最大连接数
SELECT value_in_use FROM sys.configurations c WHERE c.name = ‘user connections’;
修改最大连接数
exec sp_configure ‘show advanced options’, 1
GO
exec sp_configure ‘user connections’, 300
GO
RECONFIGURE WITH OVERRIDE
GO

6. Hikari Source Code

在这里插入图片描述

6.1 DataSource

DataSource，面向用户的入口出口
通过HikariConfig使用fastPathPool，速度更快

public class HikariDataSource extends HikariConfig implements DataSource, Closeable
{
   private static final Logger LOGGER = LoggerFactory.getLogger(HikariDataSource.class);

   private final AtomicBoolean isShutdown = new AtomicBoolean();

   private final HikariPool fastPathPool;
   private volatile HikariPool pool;

   /**
    * Default constructor.  Setters are used to configure the pool.  Using
    * this constructor vs. {@link #HikariDataSource(HikariConfig)} will
    * result in {@link #getConnection()} performance that is slightly lower
    * due to lazy initialization checks.
    *
    * The first call to {@link #getConnection()} starts the pool.  Once the pool
    * is started, the configuration is "sealed" and no further configuration
    * changes are possible -- except via {@link HikariConfigMXBean} methods.
    */
   public HikariDataSource()
   {
      super();
      fastPathPool = null;
   }

   /**
    * Construct a HikariDataSource with the specified configuration.  The
    * {@link HikariConfig} is copied and the pool is started by invoking this
    * constructor.
    *
    * The {@link HikariConfig} can be modified without affecting the HikariDataSource
    * and used to initialize another HikariDataSource instance.
    *
    * @param configuration a HikariConfig instance
    */
   public HikariDataSource(HikariConfig configuration)
   {
      configuration.validate();
      configuration.copyStateTo(this);

      LOGGER.info("{} - Starting...", configuration.getPoolName());
      pool = fastPathPool = new HikariPool(this);
      LOGGER.info("{} - Start completed.", configuration.getPoolName());

      this.seal();
   }

6.2 线程池来管理连接池（Hikari Pool连接管理器）

closeConnectionExecutor

   /**
    * Permanently close the real (underlying) connection (eat any exception).
    *
    * @param poolEntry poolEntry having the connection to close
    * @param closureReason reason to close
    */
   void closeConnection(final PoolEntry poolEntry, final String closureReason)
   {
      if (connectionBag.remove(poolEntry)) {
         final Connection connection = poolEntry.close();
         closeConnectionExecutor.execute(() -> {
            quietlyCloseConnection(connection, closureReason);
            if (poolState == POOL_NORMAL) {
               fillPool();
            }
         });
      }
   }

addConnectionExecutor

   /**
    * Construct a HikariPool with the specified configuration.
    *
    * @param config a HikariConfig instance
    */
   public HikariPool(final HikariConfig config)
   {
      super(config);

      this.connectionBag = new ConcurrentBag<>(this);
      this.suspendResumeLock = config.isAllowPoolSuspension() ? new SuspendResumeLock() : SuspendResumeLock.FAUX_LOCK;

      this.houseKeepingExecutorService = initializeHouseKeepingExecutorService();

      checkFailFast();

      if (config.getMetricsTrackerFactory() != null) {
         setMetricsTrackerFactory(config.getMetricsTrackerFactory());
      }
      else {
         setMetricRegistry(config.getMetricRegistry());
      }

      setHealthCheckRegistry(config.getHealthCheckRegistry());

      handleMBeans(this, true);

      ThreadFactory threadFactory = config.getThreadFactory();

      final int maxPoolSize = config.getMaximumPoolSize();
      LinkedBlockingQueue<Runnable> addConnectionQueue = new LinkedBlockingQueue<>(maxPoolSize);
      this.addConnectionQueueReadOnlyView = unmodifiableCollection(addConnectionQueue);
      this.addConnectionExecutor = createThreadPoolExecutor(addConnectionQueue, poolName + " connection adder", threadFactory, new ThreadPoolExecutor.DiscardOldestPolicy());
      this.closeConnectionExecutor = createThreadPoolExecutor(maxPoolSize, poolName + " connection closer", threadFactory, new ThreadPoolExecutor.CallerRunsPolicy());

      this.leakTaskFactory = new ProxyLeakTaskFactory(config.getLeakDetectionThreshold(), houseKeepingExecutorService);

      this.houseKeeperTask = houseKeepingExecutorService.scheduleWithFixedDelay(new HouseKeeper(), 100L, housekeepingPeriodMs, MILLISECONDS);

      if (Boolean.getBoolean("com.zaxxer.hikari.blockUntilFilled") && config.getInitializationFailTimeout() > 1) {
         addConnectionExecutor.setMaximumPoolSize(Math.min(16, Runtime.getRuntime().availableProcessors()));
         addConnectionExecutor.setCorePoolSize(Math.min(16, Runtime.getRuntime().availableProcessors()));

         final long startTime = currentTime();
         while (elapsedMillis(startTime) < config.getInitializationFailTimeout() && getTotalConnections() < config.getMinimumIdle()) {
            quietlySleep(MILLISECONDS.toMillis(100));
         }

         addConnectionExecutor.setCorePoolSize(1);
         addConnectionExecutor.setMaximumPoolSize(1);
      }
   }

houseKeepingExecutorService

HouseKeeper：时间回拨，关闭空闲连接，保持最小连接数
MaxLifetimeTask：最大时间
KeepaliveTask：保持心跳

this.houseKeeperTask = houseKeepingExecutorService.scheduleWithFixedDelay(new HouseKeeper(),

   /**
    * Creating new poolEntry.  If maxLifetime is configured, create a future End-of-life task with 2.5% variance from
    * the maxLifetime time to ensure there is no massive die-off of Connections in the pool.
    */
   private PoolEntry createPoolEntry()
   {
      try {
         final PoolEntry poolEntry = newPoolEntry();

         final long maxLifetime = config.getMaxLifetime();
         if (maxLifetime > 0) {
            // variance up to 2.5% of the maxlifetime
            final long variance = maxLifetime > 10_000 ? ThreadLocalRandom.current().nextLong( maxLifetime / 40 ) : 0;
            final long lifetime = maxLifetime - variance;
            poolEntry.setFutureEol(houseKeepingExecutorService.schedule(new MaxLifetimeTask(poolEntry), lifetime, MILLISECONDS));
         }

         final long keepaliveTime = config.getKeepaliveTime();
         if (keepaliveTime > 0) {
            // variance up to 10% of the heartbeat time
            final long variance = ThreadLocalRandom.current().nextLong(keepaliveTime / 10);
            final long heartbeatTime = keepaliveTime - variance;
            poolEntry.setKeepalive(houseKeepingExecutorService.scheduleWithFixedDelay(new KeepaliveTask(poolEntry), heartbeatTime, heartbeatTime, MILLISECONDS));
         }

         return poolEntry;
      }
      catch (ConnectionSetupException e) {
         if (poolState == POOL_NORMAL) { // we check POOL_NORMAL to avoid a flood of messages if shutdown() is running concurrently
            logger.error("{} - Error thrown while acquiring connection from data source", poolName, e.getCause());
            lastConnectionFailure.set(e);
         }
      }
      catch (Exception e) {
         if (poolState == POOL_NORMAL) { // we check POOL_NORMAL to avoid a flood of messages if shutdown() is running concurrently
            logger.debug("{} - Cannot acquire connection from data source", poolName, e);
         }
      }

      return null;
   }

ProxyLeakTaskFactory ->ProxyLeakTask

this.leakTaskFactory = new ProxyLeakTaskFactory(config.getLeakDetectionThreshold(), houseKeepingExecutorService);


   public Connection getConnection(final long hardTimeout) throws SQLException
   {
      suspendResumeLock.acquire();
      final long startTime = currentTime();

      try {
         long timeout = hardTimeout;
         do {
            PoolEntry poolEntry = connectionBag.borrow(timeout, MILLISECONDS);
            if (poolEntry == null) {
               break; // We timed out... break and throw exception
            }

            final long now = currentTime();
            if (poolEntry.isMarkedEvicted() || (elapsedMillis(poolEntry.lastAccessed, now) > aliveBypassWindowMs && !isConnectionAlive(poolEntry.connection))) {
               closeConnection(poolEntry, poolEntry.isMarkedEvicted() ? EVICTED_CONNECTION_MESSAGE : DEAD_CONNECTION_MESSAGE);
               timeout = hardTimeout - elapsedMillis(startTime);
            }
            else {
               metricsTracker.recordBorrowStats(poolEntry, startTime);
               return poolEntry.createProxyConnection(leakTaskFactory.schedule(poolEntry), now);
            }
         } while (timeout > 0L);

         metricsTracker.recordBorrowTimeoutStats(startTime);
         throw createTimeoutException(startTime);
      }
      catch (InterruptedException e) {
         Thread.currentThread().interrupt();
         throw new SQLException(poolName + " - Interrupted during connection acquisition", e);
      }
      finally {
         suspendResumeLock.release();
      }
   }

class ProxyLeakTaskFactory
{
   private ScheduledExecutorService executorService;
   private long leakDetectionThreshold;

   ProxyLeakTaskFactory(final long leakDetectionThreshold, final ScheduledExecutorService executorService)
   {
      this.executorService = executorService;
      this.leakDetectionThreshold = leakDetectionThreshold;
   }

   ProxyLeakTask schedule(final PoolEntry poolEntry)
   {
      return (leakDetectionThreshold == 0) ? ProxyLeakTask.NO_LEAK : scheduleNewTask(poolEntry);
   }

   void updateLeakDetectionThreshold(final long leakDetectionThreshold)
   {
      this.leakDetectionThreshold = leakDetectionThreshold;
   }

   private ProxyLeakTask scheduleNewTask(PoolEntry poolEntry) {
      ProxyLeakTask task = new ProxyLeakTask(poolEntry);
      task.schedule(executorService, leakDetectionThreshold);

      return task;
   }
}

当应用启动时，连接池初始化最小连接数( MIN )；
当外部请求到达时，直接使用空闲连接即可。假如并发数达到最大( MAX )，则需要等待，直到超肘。
如果一直未拿到连接，就会抛出异常。

如果 MIN 过小，可能会出现过多请求排队等待获取连接
如果 MIN 过大，会造成资源浪费。
如果 MAX 过小，则峰值情况下仍有很多请求处于等待状态
如果 MAX 过大，可能导致数据库连接被占满，大量请求超时，进而影响其他应用，引发服务器连环雪崩。

7. Hikari配置多数据源

application.properties

#QAE
#spring.datasource.one.type=com.zaxxer.hikari.HikariDataSource
#spring.datasource.one.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
#spring.datasource.one.jdbc-url=jdbc:sqlserver://10.xxx3;DatabaseName=xxxx
#spring.datasource.one.username=xxx
#spring.datasource.one.password=xxx
#spring.datasource.one.minimum-idle=2
#spring.datasource.one.maximum-pool-size=3
#spring.datasource.one.idle-timeout=120000
#spring.datasource.one.connection-timeout=60000
#spring.datasource.one.connection-test-query=SELECT 1

#spring.datasource.two.type=com.zaxxer.hikari.HikariDataSource
#spring.datasource.two.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
#spring.datasource.two.jdbc-url=jdbc:sqlserver://10.xxx;DatabaseName=xxxx
#spring.datasource.two.username=xxx
#spring.datasource.two.password=xxx


#PRD
spring.datasource.one.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.one.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.one.jdbc-url=jdbc:sqlserver://10.xxxx;DatabaseName=xxxx
spring.datasource.one.username=xxxx
spring.datasource.one.password=xxxx
spring.datasource.one.minimum-idle=2
spring.datasource.one.maximum-pool-size=3
spring.datasource.one.idle-timeout=120000
spring.datasource.one.connection-timeout=60000
spring.datasource.one.connection-test-query=SELECT 1

spring.datasource.two.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.two.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.two.jdbc-url=jdbc:sqlserver://10.xxxx;DatabaseName=xxxx
spring.datasource.two.username=xxxx
spring.datasource.two.password=xxxx

DataSourceConfig

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

    @Bean("dsOne")
    @ConfigurationProperties(prefix = "spring.datasource.one")
    public DataSource dsOne(){
        return DataSourceBuilder.create().build();
    }

    @Bean("dsTwo")
    @ConfigurationProperties(prefix = "spring.datasource.two")
    public DataSource dsTwo(){
        return DataSourceBuilder.create().build();
    }
}

JdbcTemplateConfig

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class JdbcTemplateConfig {

    @Bean
    @Primary
    public JdbcTemplate jdbcTemplateOne(@Qualifier("dsOne") DataSource dsOne){
        return new JdbcTemplate(dsOne);
    }

    @Bean
    public JdbcTemplate jdbcTemplateTwo(@Qualifier("dsTwo")DataSource dsTwo){
        return new JdbcTemplate(dsTwo);
    }
}