【从入门到放弃-MySQL】数据库连接过程分析-客户端

前言

上文分析了【从入门到放弃-MySQL】数据库连接过程分析。本文我们一起来追一下客户端和服务端建联的过程。
这里客户端使用JDBC8.0,在SpringBoot2.1.3下验证。

请求流程

初始化

SpringBoot2.1.3默认使用的HickriCP连接池

应用启动时,会先注册spring.datasource.driver-class-name配置的驱动,这里我们使用com.mysql.cj.jdbc.Driver

启动后,我们直接通过一个查询操作的http请求来验证一次查询操作中,客户端与服务端连接的过程

首次请求

当使用到Dao请求时,开始建立连接
调用堆栈如下:

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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
checkErrorMessage:752, NativeProtocol (com.mysql.cj.protocol.a)
checkErrorMessage:741, NativeProtocol (com.mysql.cj.protocol.a)
checkErrorMessage:709, NativeProtocol (com.mysql.cj.protocol.a)
checkErrorMessage:132, NativeProtocol (com.mysql.cj.protocol.a)
proceedHandshakeWithPluggableAuthentication:540, NativeAuthenticationProvider (com.mysql.cj.protocol.a)
connect:202, NativeAuthenticationProvider (com.mysql.cj.protocol.a)
connect:1449, NativeProtocol (com.mysql.cj.protocol.a)
connect:165, NativeSession (com.mysql.cj)
connectOneTryOnly:955, ConnectionImpl (com.mysql.cj.jdbc)
createNewIO:825, ConnectionImpl (com.mysql.cj.jdbc)
<init>:455, ConnectionImpl (com.mysql.cj.jdbc)
getInstance:240, ConnectionImpl (com.mysql.cj.jdbc)
connect:199, NonRegisteringDriver (com.mysql.cj.jdbc)
getConnection:136, DriverDataSource (com.zaxxer.hikari.util)
newConnection:369, PoolBase (com.zaxxer.hikari.pool)
newPoolEntry:198, PoolBase (com.zaxxer.hikari.pool)
createPoolEntry:467, HikariPool (com.zaxxer.hikari.pool)
checkFailFast:541, HikariPool (com.zaxxer.hikari.pool)
<init>:115, HikariPool (com.zaxxer.hikari.pool)
getConnection:112, HikariDataSource (com.zaxxer.hikari)
fetchConnection:157, DataSourceUtils (org.springframework.jdbc.datasource)
doGetConnection:115, DataSourceUtils (org.springframework.jdbc.datasource)
getConnection:78, DataSourceUtils (org.springframework.jdbc.datasource)
openConnection:82, SpringManagedTransaction (org.mybatis.spring.transaction)
getConnection:68, SpringManagedTransaction (org.mybatis.spring.transaction)
getConnection:336, BaseExecutor (org.apache.ibatis.executor)
prepareStatement:85, SimpleExecutor (org.apache.ibatis.executor)
doQuery:62, SimpleExecutor (org.apache.ibatis.executor)
queryFromDatabase:324, BaseExecutor (org.apache.ibatis.executor)
query:156, BaseExecutor (org.apache.ibatis.executor)
query:109, CachingExecutor (org.apache.ibatis.executor)
query:83, CachingExecutor (org.apache.ibatis.executor)
selectList:148, DefaultSqlSession (org.apache.ibatis.session.defaults)
selectList:141, DefaultSqlSession (org.apache.ibatis.session.defaults)
invoke0:-1, NativeMethodAccessorImpl (sun.reflect)
invoke:62, NativeMethodAccessorImpl (sun.reflect)
invoke:43, DelegatingMethodAccessorImpl (sun.reflect)
invoke:498, Method (java.lang.reflect)
invoke:433, SqlSessionTemplate$SqlSessionInterceptor (org.mybatis.spring)
selectList:-1, $Proxy59 (com.sun.proxy)
selectList:230, SqlSessionTemplate (org.mybatis.spring)
executeForMany:144, MapperMethod (org.apache.ibatis.binding)
execute:77, MapperMethod (org.apache.ibatis.binding)
invoke:58, MapperProxy (org.apache.ibatis.binding)
selectByCondition:-1, $Proxy60 (com.sun.proxy)
getUserByCondition:40, UsersServiceImpl (com.springboot.demo.service.impl)
getUsersByCondition:74, IndexController (com.springboot.demo.controller)
invoke0:-1, NativeMethodAccessorImpl (sun.reflect)
invoke:62, NativeMethodAccessorImpl (sun.reflect)
invoke:43, DelegatingMethodAccessorImpl (sun.reflect)
invoke:498, Method (java.lang.reflect)
doInvoke:189, InvocableHandlerMethod (org.springframework.web.method.support)
invokeForRequest:138, InvocableHandlerMethod (org.springframework.web.method.support)
invokeAndHandle:102, ServletInvocableHandlerMethod (org.springframework.web.servlet.mvc.method.annotation)
invokeHandlerMethod:895, RequestMappingHandlerAdapter (org.springframework.web.servlet.mvc.method.annotation)
handleInternal:800, RequestMappingHandlerAdapter (org.springframework.web.servlet.mvc.method.annotation)
handle:87, AbstractHandlerMethodAdapter (org.springframework.web.servlet.mvc.method)
doDispatch:1038, DispatcherServlet (org.springframework.web.servlet)
doService:942, DispatcherServlet (org.springframework.web.servlet)
processRequest:1005, FrameworkServlet (org.springframework.web.servlet)
doGet:897, FrameworkServlet (org.springframework.web.servlet)
service:634, HttpServlet (javax.servlet.http)
service:882, FrameworkServlet (org.springframework.web.servlet)
service:741, HttpServlet (javax.servlet.http)
internalDoFilter:231, ApplicationFilterChain (org.apache.catalina.core)
doFilter:166, ApplicationFilterChain (org.apache.catalina.core)
doFilter:53, WsFilter (org.apache.tomcat.websocket.server)
internalDoFilter:193, ApplicationFilterChain (org.apache.catalina.core)
doFilter:166, ApplicationFilterChain (org.apache.catalina.core)
doFilterInternal:99, RequestContextFilter (org.springframework.web.filter)
doFilter:107, OncePerRequestFilter (org.springframework.web.filter)
internalDoFilter:193, ApplicationFilterChain (org.apache.catalina.core)
doFilter:166, ApplicationFilterChain (org.apache.catalina.core)
doFilterInternal:92, FormContentFilter (org.springframework.web.filter)
doFilter:107, OncePerRequestFilter (org.springframework.web.filter)
internalDoFilter:193, ApplicationFilterChain (org.apache.catalina.core)
doFilter:166, ApplicationFilterChain (org.apache.catalina.core)
doFilterInternal:93, HiddenHttpMethodFilter (org.springframework.web.filter)
doFilter:107, OncePerRequestFilter (org.springframework.web.filter)
internalDoFilter:193, ApplicationFilterChain (org.apache.catalina.core)
doFilter:166, ApplicationFilterChain (org.apache.catalina.core)
doFilterInternal:200, CharacterEncodingFilter (org.springframework.web.filter)
doFilter:107, OncePerRequestFilter (org.springframework.web.filter)
internalDoFilter:193, ApplicationFilterChain (org.apache.catalina.core)
doFilter:166, ApplicationFilterChain (org.apache.catalina.core)
invoke:200, StandardWrapperValve (org.apache.catalina.core)
invoke:96, StandardContextValve (org.apache.catalina.core)
invoke:490, AuthenticatorBase (org.apache.catalina.authenticator)
invoke:139, StandardHostValve (org.apache.catalina.core)
invoke:92, ErrorReportValve (org.apache.catalina.valves)
invoke:74, StandardEngineValve (org.apache.catalina.core)
service:343, CoyoteAdapter (org.apache.catalina.connector)
service:408, Http11Processor (org.apache.coyote.http11)
process:66, AbstractProcessorLight (org.apache.coyote)
process:834, AbstractProtocol$ConnectionHandler (org.apache.coyote)
doRun:1415, NioEndpoint$SocketProcessor (org.apache.tomcat.util.net)
run:49, SocketProcessorBase (org.apache.tomcat.util.net)
runWorker:1149, ThreadPoolExecutor (java.util.concurrent)
run:624, ThreadPoolExecutor$Worker (java.util.concurrent)
run:61, TaskThread$WrappingRunnable (org.apache.tomcat.util.threads)
run:748, Thread (java.lang)

再次请求

连接建立后,再次请求的调用栈

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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
createProxyConnection:97, PoolEntry (com.zaxxer.hikari.pool)
getConnection:185, HikariPool (com.zaxxer.hikari.pool)
getConnection:155, HikariPool (com.zaxxer.hikari.pool)
getConnection:128, HikariDataSource (com.zaxxer.hikari)
fetchConnection:157, DataSourceUtils (org.springframework.jdbc.datasource)
doGetConnection:115, DataSourceUtils (org.springframework.jdbc.datasource)
getConnection:78, DataSourceUtils (org.springframework.jdbc.datasource)
openConnection:82, SpringManagedTransaction (org.mybatis.spring.transaction)
getConnection:68, SpringManagedTransaction (org.mybatis.spring.transaction)
getConnection:336, BaseExecutor (org.apache.ibatis.executor)
prepareStatement:85, SimpleExecutor (org.apache.ibatis.executor)
doQuery:62, SimpleExecutor (org.apache.ibatis.executor)
queryFromDatabase:324, BaseExecutor (org.apache.ibatis.executor)
query:156, BaseExecutor (org.apache.ibatis.executor)
query:109, CachingExecutor (org.apache.ibatis.executor)
query:83, CachingExecutor (org.apache.ibatis.executor)
selectList:148, DefaultSqlSession (org.apache.ibatis.session.defaults)
selectList:141, DefaultSqlSession (org.apache.ibatis.session.defaults)
invoke0:-1, NativeMethodAccessorImpl (sun.reflect)
invoke:62, NativeMethodAccessorImpl (sun.reflect)
invoke:43, DelegatingMethodAccessorImpl (sun.reflect)
invoke:498, Method (java.lang.reflect)
invoke:433, SqlSessionTemplate$SqlSessionInterceptor (org.mybatis.spring)
selectList:-1, $Proxy59 (com.sun.proxy)
selectList:230, SqlSessionTemplate (org.mybatis.spring)
executeForMany:144, MapperMethod (org.apache.ibatis.binding)
execute:77, MapperMethod (org.apache.ibatis.binding)
invoke:58, MapperProxy (org.apache.ibatis.binding)
selectByCondition:-1, $Proxy60 (com.sun.proxy)
getUserByCondition:40, UsersServiceImpl (com.springboot.demo.service.impl)
getUsersByCondition:74, IndexController (com.springboot.demo.controller)
invoke0:-1, NativeMethodAccessorImpl (sun.reflect)
invoke:62, NativeMethodAccessorImpl (sun.reflect)
invoke:43, DelegatingMethodAccessorImpl (sun.reflect)
invoke:498, Method (java.lang.reflect)
doInvoke:189, InvocableHandlerMethod (org.springframework.web.method.support)
invokeForRequest:138, InvocableHandlerMethod (org.springframework.web.method.support)
invokeAndHandle:102, ServletInvocableHandlerMethod (org.springframework.web.servlet.mvc.method.annotation)
invokeHandlerMethod:895, RequestMappingHandlerAdapter (org.springframework.web.servlet.mvc.method.annotation)
handleInternal:800, RequestMappingHandlerAdapter (org.springframework.web.servlet.mvc.method.annotation)
handle:87, AbstractHandlerMethodAdapter (org.springframework.web.servlet.mvc.method)
doDispatch:1038, DispatcherServlet (org.springframework.web.servlet)
doService:942, DispatcherServlet (org.springframework.web.servlet)
processRequest:1005, FrameworkServlet (org.springframework.web.servlet)
doGet:897, FrameworkServlet (org.springframework.web.servlet)
service:634, HttpServlet (javax.servlet.http)
service:882, FrameworkServlet (org.springframework.web.servlet)
service:741, HttpServlet (javax.servlet.http)
internalDoFilter:231, ApplicationFilterChain (org.apache.catalina.core)
doFilter:166, ApplicationFilterChain (org.apache.catalina.core)
doFilter:53, WsFilter (org.apache.tomcat.websocket.server)
internalDoFilter:193, ApplicationFilterChain (org.apache.catalina.core)
doFilter:166, ApplicationFilterChain (org.apache.catalina.core)
doFilterInternal:99, RequestContextFilter (org.springframework.web.filter)
doFilter:107, OncePerRequestFilter (org.springframework.web.filter)
internalDoFilter:193, ApplicationFilterChain (org.apache.catalina.core)
doFilter:166, ApplicationFilterChain (org.apache.catalina.core)
doFilterInternal:92, FormContentFilter (org.springframework.web.filter)
doFilter:107, OncePerRequestFilter (org.springframework.web.filter)
internalDoFilter:193, ApplicationFilterChain (org.apache.catalina.core)
doFilter:166, ApplicationFilterChain (org.apache.catalina.core)
doFilterInternal:93, HiddenHttpMethodFilter (org.springframework.web.filter)
doFilter:107, OncePerRequestFilter (org.springframework.web.filter)
internalDoFilter:193, ApplicationFilterChain (org.apache.catalina.core)
doFilter:166, ApplicationFilterChain (org.apache.catalina.core)
doFilterInternal:200, CharacterEncodingFilter (org.springframework.web.filter)
doFilter:107, OncePerRequestFilter (org.springframework.web.filter)
internalDoFilter:193, ApplicationFilterChain (org.apache.catalina.core)
doFilter:166, ApplicationFilterChain (org.apache.catalina.core)
invoke:200, StandardWrapperValve (org.apache.catalina.core)
invoke:96, StandardContextValve (org.apache.catalina.core)
invoke:490, AuthenticatorBase (org.apache.catalina.authenticator)
invoke:139, StandardHostValve (org.apache.catalina.core)
invoke:92, ErrorReportValve (org.apache.catalina.valves)
invoke:74, StandardEngineValve (org.apache.catalina.core)
service:343, CoyoteAdapter (org.apache.catalina.connector)
service:408, Http11Processor (org.apache.coyote.http11)
process:66, AbstractProcessorLight (org.apache.coyote)
process:834, AbstractProtocol$ConnectionHandler (org.apache.coyote)
doRun:1415, NioEndpoint$SocketProcessor (org.apache.tomcat.util.net)
run:49, SocketProcessorBase (org.apache.tomcat.util.net)
runWorker:1149, ThreadPoolExecutor (java.util.concurrent)
run:624, ThreadPoolExecutor$Worker (java.util.concurrent)
run:61, TaskThread$WrappingRunnable (org.apache.tomcat.util.threads)
run:748, Thread (java.lang)

因为两次请求的调用栈都比较深且有很大一部分重合路径,我们使用Beyond Compare将两次调用栈对比来看。

Mybatis处理

如上图所示 selectByCondition 是请求中dao层的调用方法,这个方法调用之前是spring对http请求的处理动作,
处理的流程暂不分析。直接看selectByCondition之后处理流程。

  • MyBatis通过SqlSessionFactoryBuilder对mybatis-config.xml进行解析,从中构建出SqlSessionFactory,
  • 再创建出SqlSession实例,
  • SqlSession调用Executor生成StatementHandler对象。
  • 然后通过Spring框架的DataSourceUtils::getConnection方法获取连接。

连接

对比两次请求不同的地方,对比HikariDataSource源码。

  • 首次请求会调用112行,第二次会调用128行。

  • 可以看到Hikari连接池使用了双重检查锁的方式来实现单例,避免重复创建连接池。

  • 一次请求结束后,连接会放在连接池中,在连接池中,使用connectionBag控制一个连接“借出”、“归还”。详细信息可参考Hikari的线程池的生命周期

我们分析下首次调用建立连接的过程。

Hikari会先创建一个连接池,然后使用我们在启动时注册的驱动(com.mysql.cj.jdbc.Driver)创建连接。

可以从NonRegisteringDriver::connect一直追下去,可以看到com.mysql.cj.NativeSession::connect方法实现如下:

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
public void connect(HostInfo hi, String user, String password, String database, int loginTimeout, TransactionEventHandler transactionManager)
throws IOException {

this.hostInfo = hi;

// reset max-rows to default value
this.setSessionMaxRows(-1);

// TODO do we need different types of physical connections?
SocketConnection socketConnection = new NativeSocketConnection();
socketConnection.connect(this.hostInfo.getHost(), this.hostInfo.getPort(), this.propertySet, getExceptionInterceptor(), this.log, loginTimeout);

// we use physical connection to create a -> protocol
// this configuration places no knowledge of protocol or session on physical connection.
// physical connection is responsible *only* for I/O streams
if (this.protocol == null) {
this.protocol = NativeProtocol.getInstance(this, socketConnection, this.propertySet, this.log, transactionManager);
} else {
this.protocol.init(this, socketConnection, this.propertySet, transactionManager);
}

// use protocol to create a -> session
// protocol is responsible for building a session and authenticating (using AuthenticationProvider) internally
this.protocol.connect(user, password, database);

// error messages are returned according to character_set_results which, at this point, is set from the response packet
this.protocol.getServerSession().setErrorMessageEncoding(this.protocol.getAuthenticationProvider().getEncodingForHandshake());

this.isClosed = false;
}

  • 先创建一个socket与服务端建立连接
  • 通过NativeProtocol.getInstance初始化MySQL协议相关信息
  • 调用NativeProtocol::connect方法根据MySQL账号、密码、使用数据库等信息向服务端请求认证。
  • 使用proceedHandshakeWithPluggableAuthentication对返回的数据包根据MySQL协议进行解析。
  • 调用NativeProtocol::checkErrorMessage对解析后的内容做判断,如果没问题则正常连接,如果返回错误信息则抛出异常。
  • 连接建立后,通过Hikari连接池保存,下次使用直接用(如对比文件所示)。
    MySQL协议详解可参考:http://hutaow.com/blog/2013/11/06/mysql-protocol-analysis/

事务处理

org.springframework.jdbc.datasource.DataSourceTransactionManager会维护一个DataSourceTransactionObject。
里面存放事务请求的连接。保证事务里的所有请求都是同一个连接在执行。

总结

通过对数据库连接过程的分析,对数据库服务端、客户端的连接过程有了一个初步的认识,脑海中有个大概的体系,但还是不够深入,如MySQL协议的具体协议内容、连接鉴权的细节、Hikari连接池、Jdbc。。。都需要大量时间去深入研究,接下来要逐步去学习、沉淀下来。