mysql预编译原理_Mybatis之开启MySQL的预编译功能

最重要的参数，如下List-1:

List-1

useServerPrepStmts=true&&cachePrepStmts=true

首先来看不加List-1参数的情况，如下List-2:

List-2

url=jdbc:mysql://127.0.0.1:3306/mjduan?useUnicode=true&characterEncoding=utf-8&useSSL=false

服务器端MySQL的general日志里面内容如下List-3:

List-3 执行的三条语句被打印出来了

2018-06-12T23:37:54.142048Z 10 QuerySET NAMES utf8

2018-06-12T23:37:54.143931Z 10 QuerySET character_set_results = NULL

2018-06-12T23:37:54.145705Z 10 QuerySET autocommit=1

2018-06-12T23:37:54.210026Z 10 QuerySELECT id,name,age FROM student WHERE id = 1

2018-06-12T23:37:54.236635Z 10 QuerySELECT id,name,age FROM student WHERE id = 1

2018-06-12T23:37:54.239985Z 10 QuerySELECT id,name,age FROM student WHERE id = 1

JDBC连接的url中添加useServerPrepStmts=true，如下List-4:

List-4

url=jdbc:mysql://127.0.0.1:3306/mjduan?useUnicode=true&characterEncoding=utf-8&useSSL=false&&useServerPrepStmts=true

下面的List-5中，每个Ececute之前都有一个Prepare，说明执行SQL之前，都对该SQL进行解析、优化了，所以没有起到预编译的作用。

List-5

2018-06-12T23:29:34.175252Z 5 QuerySET NAMES utf8

2018-06-12T23:29:34.176954Z 5 QuerySET character_set_results = NULL

2018-06-12T23:29:34.178768Z 5 QuerySET autocommit=1

2018-06-12T23:29:34.214910Z 5 PrepareSELECT id,name,age FROM student WHERE id = ?

2018-06-12T23:29:34.244389Z 5 ExecuteSELECT id,name,age FROM student WHERE id = 1

2018-06-12T23:29:34.265083Z 5 Close stmt

2018-06-12T23:29:34.266680Z 5 PrepareINSERT INTO student(name,age) VALUES(?, ?)

2018-06-12T23:29:34.268304Z 5 Queryselect @@session.tx_read_only

2018-06-12T23:29:34.275772Z 5 ExecuteINSERT INTO student(name,age) VALUES('明明', 20)

2018-06-12T23:29:34.283289Z 5 Close stmt

2018-06-12T23:29:34.284142Z 5 PrepareSELECT id,name,age FROM student WHERE id = ?

2018-06-12T23:29:34.285013Z 5 ExecuteSELECT id,name,age FROM student WHERE id = 1

2018-06-12T23:29:34.287157Z 5 Close stmt

JDBC连接的url加上useServerPrepStmts=true&cachePrepStmts=true，如下List-6所示:

List-6

url=jdbc:mysql://127.0.0.1:3306/mjduan?useUnicode=true&characterEncoding=utf-8&useSSL=false&&useServerPrepStmts=true&cachePrepStmts=true

再去看MySQL的general日志，如List-7，这次生效了。

List-7 执行三条一样的语句，只有一次Prepare

2018-06-12T23:44:03.197228Z 11 QuerySET NAMES utf8

2018-06-12T23:44:03.202524Z 11 QuerySET character_set_results = NULL

2018-06-12T23:44:03.206439Z 11 QuerySET autocommit=1

2018-06-12T23:44:03.310079Z 11 PrepareSELECT id,name,age FROM student WHERE id = ?

2018-06-12T23:44:03.378740Z 11 ExecuteSELECT id,name,age FROM student WHERE id = 1

2018-06-12T23:44:03.427651Z 11 ExecuteSELECT id,name,age FROM student WHERE id = 1

2018-06-12T23:44:03.431207Z 11 ExecuteSELECT id,name,age FROM student WHERE id = 1

注意:

预编译功能只是在同一个Connection中生效，一个Connection无法获取另一个Connection中的预编译结果。

所以，使用Mybatis时，预编译功能只在同一个SqlSession中生效？这种说明不对，俩个SqlSession有可能拿到的是同一个Connection(因此DataSource)，这个时候这俩个SqlSession就能共享该Connection中的预编译结果。

预编译，其实有些人说预准备更恰当，不过现在几本都说预编译了。

参考：