Detailed explanation of HikariCP database connection pool

Detailed explanation of HikariCP database connection pool

1. Overview of HikariCP

1.1 What is a database connection pool

Connection pooling is a commonly used technology, why do we need connection pooling?
This needs to start with TCP. If our server and the database are not deployed on the same machine, then each time the server queries the database, it must first establish a connection, usually a TCP connection. The establishment of a connection requires a three-way handshake. Assume that the background service has a one-way access to the database. It takes 10ms, so it takes 30ms just to establish a connection, and TCP also has a slow start mechanism. In fact, a query may be more than one TCP round trip, and the query efficiency will be greatly reduced.

1.2 Why do you need a connection pool

In order to solve the above problems, we need to maintain some long links so that we don't have to establish a connection every time. After all, establishing a connection requires some other system resources in addition to time. Another advantage is that the connection pool makes it easier for us to manage. On the one hand, it can prevent database resources from being occupied by certain APIs, and on the other hand, it can also avoid resource leakage.

1.3 What is Hikari

HikariCP is a database connection pool component open sourced by Japanese programmers. The code is very lightweight and very fast. According to the official data, in the case of i7, 32 threads and 32 connections are opened, random database read and write operations are performed. The speed of HikariCP is hundreds of times that of the commonly used C3P0 database connection pool. In SpringBoot2.0, HikariCP is also officially recommended

Insert picture description here

1.4 What is HikariCP

That’s why Hikari, why is it so fast

1. 字节码精简 :优化代码(HikariCP利用了一个第三方的Java字节码修改类库Javassist来生成委托实现动态代理,动态代理的实现在ProxyFactory类),直到编译后的字节码最少,这样,CPU缓存可以加载更多的程序代码;

2. 优化代理和拦截器:减少代码,例如HikariCP的Statement proxy只有100行代码,只有BoneCP的十分之一;

3. 自定义数组类型(FastStatementList)代替ArrayList:避免每次get()调用都要进行range check,避免调用remove()时的从头到尾的扫描,相对与ArrayList极大地提升了性能,而其中的区别是,ArrayList在每次执行get(Index)方法时,都需要对List的范围进行检查,而FastStatementList不需要,在能确保范围的合法性的情况下,可以省去范围检查的开销。
自定义集合类型(ConcurrentBag):支持快速插入和删除,特别是在同一线程既添加又删除项时,提高并发读写的效率;

4. 针对CPU的时间片算法进行优化:尽可能在一个时间片里面完成各种操作(具体机制比较模糊)。

5. 针对连接中断的情况:比其他CP响应时间上有了极好的优化,响应时间为5S,会抛出SqlException异常,并且后续的getConnection()可以正常进行

6. 关于Connection的操作:另外在Java代码中,很多都是在使用完之后直接关闭连接,以前都是从头到尾遍历,来关闭对应的Connection,而HikariCP则是从尾部对Connection集合进行扫描,整体上来说,从尾部开始的性能更好一些。

2. Detailed explanation of HikariCP configuration parameters

2.1 Basic parameters

maximum-pool-size   池中最大连接数(包括空闲和正在使用的连接)
minimum-idle   池中最小空闲连接数量。默认值10
pool-name   连接池的名字
auto-commit   是否自动提交池中返回的连接。默认值为true。
idle-timeout    空闲时间。仅在minimum-idle小于maximum-poop-size的时候才会起作用。默认值10分钟。
max-lifetime   连接池中连接的最大生命周期。当连接一致处于闲置状态时,数据库可能会主动断开连接。
connection-timeout   连接超时时间。默认值为30s,可以接收的最小超时时间为250ms。但是连接池请求也可以自定义超时时间

2.2 Configuration case

Spring Boot2.+连接池就是Hikaricp,不需要再另外导入包和配置.启动项目可以看到控制台

说明Spring Boot 2.+默认使用的就是连接池HikariCP.
server:
  port: 8080

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/category?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    driver-class-name: com.mysql.jdbc.Driver

    hikari:
      pool-name: Retail_HikariCP #连接池名称
      minimum-idle: 10 #最小空闲连接数量
      idle-timeout: 120000 #空闲连接存活最大时间,默认600000(10分钟)
      maximum-pool-size: 20 #连接池最大连接数,默认是10
      auto-commit: true  #此属性控制从池返回的连接的默认自动提交行为,默认值:true
      max-lifetime: 1800000 #此属性控制池中连接的最长生命周期,值0表示无限生命周期,默认1800000即30分钟
      connection-timeout: 30000 #数据库连接超时时间,默认30秒,即30000
      connection-test-query: SELECT 1

mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml
  type-aliases-package: com.example.category

#showSql
logging:
  level:
    com:
      example:
        mapper : debug