mysql 预编译_MySql启动预编译功能

小灰灰 2023-01-11 01:16 175阅读 0赞

在Java编程中，应用代码绝大多数使用了PreparedStatement,无论你是直接使用JDBC还是使用框架。

在Java编程中，绝大多数使用了使用了PreparedStatement连接MySQL的应用代码没有启用预编译,无论你是直接使用JDBC还是使用框架。

在我所能见到的项目中，几乎没有见过启用MySQL预编译功能的。网上更有文章说MySQL不支持预编译，实在是害人不浅。

要想知道你的应用是否真正的使用了预编译，请执行：show global status like '%prepare%';看看曾经编译过几条，当前Prepared\_stmt\_count 是多少。大多数是0吧？

这篇文章分以下几个方面：

一.MySQL是支持预编译的

打开MySQL日志功能，启动MySQL，然后 tail -f mysql.log.path(默认：/var/log/mysql/mysql.log).

create table axman\_test (ID int(4) auto\_increment primary key, name varchar(20),age int(4));

insert into axman\_test (name,age) values ('axman',1000);

prepare myPreparedStmt from 'select \* from axman\_test where name = ?';

set @name='axman';

execute myPreparedStmt using @name ;

控制台可以正确地输出：

mysql> execute myPreparedStmt using @name ;

\+----+-------+------+

| ID | name  | age  |

\+----+-------+------+

|  1 | axman | 1000 |

\+----+-------+------+

1 row in set (0.00 sec)

而log文件中也忠实地记录如下：

111028  9:25:06       51 Query    prepare myPreparedStmt from 'select \* from axman\_test where name = ?'

51 Prepare    select \* from axman\_test where name = ?

51 Query    set @name='axman'

111028  9:25:08       51 Query    execute myPreparedStmt using @name

51 Execute    select \* from axman\_test where name = 'axman'

二.通过JDBC本身是可以预编译的，这个不用多说。相当于我们把控制台输入的命令直接通过JDBC语句来执行：

Class.forName("org.gjt.mm.mysql.Driver");

String url = "jdbc:mysql://localhost:3306/mysql";

Connection conn = null;

try \{

conn = DriverManager.getConnection(url, "root", "12345678");

Statement stmt = conn.createStatement();

/\*以下忽略返回值处理\*/

stmt.executeUpdate("prepare mystmt from 'select \* from axman\_test where name = ?'");

stmt.execute("set @name='axman'");

stmt.executeQuery("execute mystmt using @name");

stmt.close();

\} finally \{

if (conn != null) \{

conn.close();

看日志输出：

111028  9:30:19       52 Connect    root@localhost on mysql

52 Query    /\* @MYSQL\_CJ\_FULL\_PROD\_NAME@ ( Revision: @MYSQL\_CJ\_REVISION@ ) \*/SHOW VARIABLES WHERE Variable\_name ='language' OR Variable\_name = 'net\_write\_timeout' OR Variable\_name = 'interactive\_timeout' OR Variable\_name = 'wait\_timeout' OR Variable\_name = 'character\_set\_client' OR Variable\_name = 'character\_set\_connection' OR Variable\_name = 'character\_set' OR Variable\_name = 'character\_set\_server' OR Variable\_name = 'tx\_isolation' OR Variable\_name = 'transaction\_isolation' OR Variable\_name = 'character\_set\_results' OR Variable\_name = 'timezone' OR Variable\_name = 'time\_zone' OR Variable\_name = 'system\_time\_zone' OR Variable\_name = 'lower\_case\_table\_names' OR Variable\_name = 'max\_allowed\_packet' OR Variable\_name = 'net\_buffer\_length' OR Variable\_name = 'sql\_mode' OR Variable\_name = 'query\_cache\_type' OR Variable\_name = 'query\_cache\_size' OR Variable\_name = 'init\_connect'

52 Query    /\* @MYSQL\_CJ\_FULL\_PROD\_NAME@ ( Revision: @MYSQL\_CJ\_REVISION@ ) \*/SELECT @@session.auto\_increment\_increment

52 Query    SHOW COLLATION

52 Query    SET NAMES latin1

52 Query    SET character\_set\_results = NULL

52 Query    SET autocommit=1

52 Query    SET sql\_mode='STRICT\_TRANS\_TABLES'

52 Query    prepare mystmt from 'select \* from axman\_test where name = ?'

52 Prepare    select \* from axman\_test where name = ?

52 Query    set @name='axman'

52 Query    execute mystmt using @name

52 Execute    select \* from axman\_test where name = 'axman'

52 Quit

三.默认的PrearedStatement不能开启MySQL预编译功能：

虽然第二节中我们通过JDBC手工指定MySQL进行预编译，但是PrearedStatement却并不自动帮我们做这件事。

Class.forName("org.gjt.mm.mysql.Driver");

String url = "jdbc:mysql://localhost:3306/mysql";

Connection conn = null;

try \{

conn = DriverManager.getConnection(url, "root", "12345678");

PreparedStatement ps = conn.prepareStatement("select \* from axman\_test where name = ?");

ps.setString(1, "axman' or 1==1");

ResultSet rs = ps.executeQuery();

if (rs.next()) \{

System.out.println(rs.getString(1));

Thread.sleep(1000);

rs.close();

ps.clearParameters();

ps.setString(1, "axman");

rs = ps.executeQuery();

if (rs.next()) \{

System.out.println(rs.getString(1));

rs.close();

ps.close();

\} finally \{

if (conn != null) \{

conn.close();

废话少说，直接看日志：

111028  9:54:03       53 Connect    root@localhost on mysql

53 Query    /\* @MYSQL\_CJ\_FULL\_PROD\_NAME@ ( Revision: @MYSQL\_CJ\_REVISION@ ) \*/SHOW VARIABLES WHERE Variable\_name ='language' OR Variable\_name = 'net\_write\_timeout' OR Variable\_name = 'interactive\_timeout' OR Variable\_name = 'wait\_timeout' OR Variable\_name = 'character\_set\_client' OR Variable\_name = 'character\_set\_connection' OR Variable\_name = 'character\_set' OR Variable\_name = 'character\_set\_server' OR Variable\_name = 'tx\_isolation' OR Variable\_name = 'transaction\_isolation' OR Variable\_name = 'character\_set\_results' OR Variable\_name = 'timezone' OR Variable\_name = 'time\_zone' OR Variable\_name = 'system\_time\_zone' OR Variable\_name = 'lower\_case\_table\_names' OR Variable\_name = 'max\_allowed\_packet' OR Variable\_name = 'net\_buffer\_length' OR Variable\_name = 'sql\_mode' OR Variable\_name = 'query\_cache\_type' OR Variable\_name = 'query\_cache\_size' OR Variable\_name = 'init\_connect'

53 Query    /\* @MYSQL\_CJ\_FULL\_PROD\_NAME@ ( Revision: @MYSQL\_CJ\_REVISION@ ) \*/SELECT @@session.auto\_increment\_increment

53 Query    SHOW COLLATION

53 Query    SET NAMES latin1

53 Query    SET character\_set\_results = NULL

53 Query    SET autocommit=1

53 Query    SET sql\_mode='STRICT\_TRANS\_TABLES'

53 Query    select \* from axman\_test where name = 'axman\\' or 1==1'

111028  9:54:04       53 Query    select \* from axman\_test where name = 'axman'

53 Quit

两条语句都是直接执行，而没有预编译。注意我的第一条语句select \* from axman\_test where name = 'axman\\' or 1==1'，下面还会说到它。

接着我们改变一下jdbc.url的选项：

String url = "jdbc:mysql://localhost:3306/mysql?cachePrepStmts=true&prepStmtCacheSize=25&prepStmtCacheSqlLimit=256";

执行上面的代码还是没有开启Mysql的预编译。

四.只有使用了useServerPrepStmts=true才能开启Mysql的预编译。

上面的代码其它不变，只修改String url = "jdbc:mysql://localhost:3306/mysql?useServerPrepStmts=true";

查看日志：

111028 10:04:52       54 Connect    root@localhost on mysql

54 Query    /\* @MYSQL\_CJ\_FULL\_PROD\_NAME@ ( Revision: @MYSQL\_CJ\_REVISION@ ) \*/SHOW VARIABLES WHERE Variable\_name ='language' OR Variable\_name = 'net\_write\_timeout' OR Variable\_name = 'interactive\_timeout' OR Variable\_name = 'wait\_timeout' OR Variable\_name = 'character\_set\_client' OR Variable\_name = 'character\_set\_connection' OR Variable\_name = 'character\_set' OR Variable\_name = 'character\_set\_server' OR Variable\_name = 'tx\_isolation' OR Variable\_name = 'transaction\_isolation' OR Variable\_name = 'character\_set\_results' OR Variable\_name = 'timezone' OR Variable\_name = 'time\_zone' OR Variable\_name = 'system\_time\_zone' OR Variable\_name = 'lower\_case\_table\_names' OR Variable\_name = 'max\_allowed\_packet' OR Variable\_name = 'net\_buffer\_length' OR Variable\_name = 'sql\_mode' OR Variable\_name = 'query\_cache\_type' OR Variable\_name = 'query\_cache\_size' OR Variable\_name = 'init\_connect'

54 Query    /\* @MYSQL\_CJ\_FULL\_PROD\_NAME@ ( Revision: @MYSQL\_CJ\_REVISION@ ) \*/SELECT @@session.auto\_increment\_increment

54 Query    SHOW COLLATION

54 Query    SET NAMES latin1

54 Query    SET character\_set\_results = NULL

54 Query    SET autocommit=1

54 Query    SET sql\_mode='STRICT\_TRANS\_TABLES'

54 Prepare    select \* from axman\_test where name = ?

54 Execute    select \* from axman\_test where name = 'axman\\' or 1==1'

111028 10:04:53       54 Execute    select \* from axman\_test where name = 'axman'

54 Close stmt

54 Quit

如果useServerPrepStmts=true，ConneciontImpl在prepareStatement时会产生一个 ServerPreparedStatement.在这个ServerPreparedStatement对象构造时首先会把当前SQL语句发送给 MySQL进行预编译，然后将返回的结果缓存起来，其中包含预编译的名称(我们可以看成是当前SQL语句编译后的函数名)，签名(参数列表)，然后执行的 时候就会直接把参数传给这个函数请求MySQL执行这个函数。否则返回的是客户端预编译语句，它仅做参数化工作，见第五节。

ServerPreparedStatement在请求预编译和执行预编译后的SQL 函数时，虽然和我们上面手工预编译工作相同，但它与MySQL交互使用的是压缩格式，如prepare指令码是22，这样可以减少交互时传输的数据量。

注意上面的代码中，两次执行使用的是同一个PreparedStatement句柄.如果使用个不同的PreparedStatement句柄，把代码改成：

Class.forName("org.gjt.mm.mysql.Driver");

String url = "jdbc:mysql://localhost:3306/mysql?useServerPrepStmts=true";

Connection conn = null;

try \{

conn = DriverManager.getConnection(url, "root", "12345678");

PreparedStatement ps = conn.prepareStatement("select \* from axman\_test where name = ?");

ps.setString(1, "axman' or 1==1");

ResultSet rs = ps.executeQuery();

if (rs.next()) \{

System.out.println(rs.getString(1));

Thread.sleep(1000);

rs.close();

ps.close();

ps = conn.prepareStatement("select \* from axman\_test where name = ?");

ps.setString(1, "axman");

rs = ps.executeQuery();

if (rs.next()) \{

System.out.println(rs.getString(1));

rs.close();

ps.close();

\} finally \{

if (conn != null) \{

conn.close();

再看日志输出：

Connect    root@localhost on mysql

55 Query    /\* @MYSQL\_CJ\_FULL\_PROD\_NAME@ ( Revision: @MYSQL\_CJ\_REVISION@ ) \*/SHOW VARIABLES WHERE Variable\_name ='language' OR Variable\_name = 'net\_write\_timeout' OR Variable\_name = 'interactive\_timeout' OR Variable\_name = 'wait\_timeout' OR Variable\_name = 'character\_set\_client' OR Variable\_name = 'character\_set\_connection' OR Variable\_name = 'character\_set' OR Variable\_name = 'character\_set\_server' OR Variable\_name = 'tx\_isolation' OR Variable\_name = 'transaction\_isolation' OR Variable\_name = 'character\_set\_results' OR Variable\_name = 'timezone' OR Variable\_name = 'time\_zone' OR Variable\_name = 'system\_time\_zone' OR Variable\_name = 'lower\_case\_table\_names' OR Variable\_name = 'max\_allowed\_packet' OR Variable\_name = 'net\_buffer\_length' OR Variable\_name = 'sql\_mode' OR Variable\_name = 'query\_cache\_type' OR Variable\_name = 'query\_cache\_size' OR Variable\_name = 'init\_connect'

55 Query    /\* @MYSQL\_CJ\_FULL\_PROD\_NAME@ ( Revision: @MYSQL\_CJ\_REVISION@ ) \*/SELECT @@session.auto\_increment\_increment

55 Query    SHOW COLLATION

55 Query    SET NAMES latin1

55 Query    SET character\_set\_results = NULL

55 Query    SET autocommit=1

55 Query    SET sql\_mode='STRICT\_TRANS\_TABLES'

55 Prepare    select \* from axman\_test where name = ?

55 Execute    select \* from axman\_test where name = 'axman\\' or 1==1'

111028 10:10:24       55 Close stmt

55 Prepare    select \* from axman\_test where name = ?

55 Execute    select \* from axman\_test where name = 'axman'

55 Close stmt

55 Quit

同一个SQL语句发生了两次预编译。这不是我们想要的效果，要想对同一SQL语句多次执行不是每次都预编译，就要使用 cachePrepStmts=true，这个选项可以让JVM端缓存每个SQL语句的预编译结果，说白了就是以SQL语句为key, 将预编译结果缓存起来，下次遇到相同的SQL语句时作为key去get一下看看有没有这个SQL语句的预编译结果，有就直接合出来用。我们还是以事实来说 明：

上面的代码只修改String url = "jdbc:mysql://localhost:3306/mysql?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=25&prepStmtCacheSqlLimit=256";

这行代码中有其它参数自己去读文档，我不多啰嗦，执行的结果：

111028 10:27:23       58 Connect    root@localhost on mysql

58 Query    /\* mysql-connector-java-5.1.18 ( Revision: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq ) \*/SHOW VARIABLES WHERE Variable\_name ='language' OR Variable\_name = 'net\_write\_timeout' OR Variable\_name = 'interactive\_timeout' OR Variable\_name = 'wait\_timeout' OR Variable\_name = 'character\_set\_client' OR Variable\_name = 'character\_set\_connection' OR Variable\_name = 'character\_set' OR Variable\_name = 'character\_set\_server' OR Variable\_name = 'tx\_isolation' OR Variable\_name = 'transaction\_isolation' OR Variable\_name = 'character\_set\_results' OR Variable\_name = 'timezone' OR Variable\_name = 'time\_zone' OR Variable\_name = 'system\_time\_zone' OR Variable\_name = 'lower\_case\_table\_names' OR Variable\_name = 'max\_allowed\_packet' OR Variable\_name = 'net\_buffer\_length' OR Variable\_name = 'sql\_mode' OR Variable\_name = 'query\_cache\_type' OR Variable\_name = 'query\_cache\_size' OR Variable\_name = 'init\_connect'

58 Query    /\* mysql-connector-java-5.1.18 ( Revision: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq ) \*/SELECT @@session.auto\_increment\_increment

58 Query    SHOW COLLATION

58 Query    SET NAMES latin1

58 Query    SET character\_set\_results = NULL

58 Query    SET autocommit=1

58 Query    SET sql\_mode='STRICT\_TRANS\_TABLES'

58 Prepare    select \* from axman\_test where name = ?

58 Execute    select \* from axman\_test where name = 'axman\\' or 1==1'

111028 10:27:24       58 Execute    select \* from axman\_test where name = 'axman'

58 Quit

注意仅发生一次预编译，尽管代码本身在第一次执行后关闭了ps.close();但因为使用了cachePrepStmts=true,底层并没有真实关闭。

千万注意，同一条SQL语句尽量在一个全局的地方定义，然后在不同地方引用，这样做一是为了DBA方便地对SQL做统一检查和优化，就象IBatis把 SQL语句定义在XML文件中一样。二是同一语句不同写法，即使空格不同，大小写不同也会重新预编译，因为JVM端缓存是直接以SQL本身为key而不会 对SQL格式化以后再做为key。

我们来看下面的输出：

35 Prepare    select \* from axman\_test where name = ?

35 Execute    select \* from axman\_test where name = 'axman\\' or 1==1'

111029  9:54:31       35 Prepare    select \* FROM axman\_test where name = ?

35 Execute    select \* FROM axman\_test where name = 'axman'

第一条语句和第二条语句的差别是FROM在第二条语句中被大写了，这样还是发生了两次预编译。

37 Prepare    select \* from axman\_test where name = ?

37 Execute    select \* from axman\_test where name = 'axman\\' or 1==1'

111029  9:59:00       37 Prepare    select \* from    axman\_test where name = ?

37 Execute    select \* from    axman\_test where name = 'axman'

这里两条语句只是第二条的from后面多了个空格，因为你现在看到是HTML格式，如果不加转义符，两个空格也显示一个空格，所以你能可看不到区别，但你可以在自己的机器上试一下。

五.即使没有开启MySQL的预编译，坚持使用PreparedStatement仍然非常必要。

在第三节的最后我说到"注意我的第一条语句select \* from axman\_test where name = 'axman\\' or 1==1'，下面还会说到它。"，现在我们回过头来看，即使没有开启MySQL端的预编译，我们仍然要坚持使用PreparedStatement，因为 JVM端对PreparedStatement的SQL语句进行了参数化，即用占位符替换参数，以后任何内容输入都是字符串或其它类型的值，而不会和原始 的SQL语句拚接产生SQL注入，对字符串中的任何字符都会做检查，如果可能是SQL语句使用的标识符，会进行转义。然后发送一个合法的安全的SQL语句 给数据库执行。