• 数据库连接池的使用

    在这里插入图片描述

    数据库连接池的使用

    在这里插入图片描述

    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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    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

    • 1
    • 2
    • 3
    • 4
    • 5

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 设置最大连接数
      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. Source code

    DataSource，面向用户的入口出口

    6.1 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();
   }

    • 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
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46

    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();
            }
         });
      }
   }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    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);
      }
   }

    • 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
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52

    houseKeepingExecutorService

    • HouseKeeper：时间回拨，关闭空闲连接，保持最小连接数
    • MaxLifetimeTask： 最大时间
    • KeepaliveTask：保持心跳
    this.houseKeeperTask = houseKeepingExecutorService.scheduleWithFixedDelay(new HouseKeeper(),

    • 1
       /**
    * 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;
   }

    • 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
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41

    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();
      }
   }

    • 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
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    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;
   }
}


    • 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
    • 当应用启动时，连接池初始化最小连接数( 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.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.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

    • 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
    • 31
    • 32
    • 33
    • 34

    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();
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    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);
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
  • 相关阅读:
    深度学习常用激活函数总结
    vue一些知识补充
    数据库范式
    什么是软件工程？
    计算机视觉 Project 1：Image Filtering and Hybrid Images
    将基站搬到天上，物联网迎来一场“升维”竞争
    【Linux】Docker部署sock shop
    宠物信息服务预约小程序的效果如何
    关于如何设置好记且复杂度高的密码
    勒索病毒趋势分析
  • 原文地址：https://blog.csdn.net/weixin_43916074/article/details/126047939