mysql-JDBC源码解析

来源：互联网发布：手机怎么激活淘宝店铺编辑：程序博客网时间：2024/05/17 07:05

一、背景

MySQL是一个中小型关系型数据库管理系统，目前我们淘宝也使用的也非常广泛。为了对开发中间DAO持久层的问题能有更深的理解以及最近在使用的phoenix on Hbase的SQL也是实现的JDBC规范，在遇到问题的时候能够有更多的思路，于是研究了一下MySQL_JDBC驱动的源码，大家都知道JDBC是Java访问数据库的一套规范，具体访问数据库的细节有各个数据库厂商自己实现，看驱动实现也有助有我们更好的理解JDBC规范，并且在这过程中也发现了一直以来对于PreparedStatement常识理解上的错误，与大家分享（MySQl版本5.1.39，JDBC驱动版本5.1.7，JDK版本1.6）。

二、JDBC典型应用
下面是个最简单的使用JDBC取得数据的应用。主要能分成几个步骤，分别是①加载数据库驱动，②获取数据库连接，③创建PreparedStatement，并且设置参数 ④ 执行查询，来分步分析这个过程。基本上每个步骤的源码分析我都画了时序图，如果不想看文字的话，可以对着时序图看。最后我还会分析关于PreparedStatement预编译的话题，有兴趣的同学可以仔细看下。

Java代码

1. public class PreparedStatement_Select {

2. private Connection conn = null;

3. private PreparedStatement pstmt = null;

4. private ResultSet rs = null;

5. private String sql = "SELECT * FROM user WHERE id = ?";

7. public void selectStudent(int id) {

8. try {

9. // step1：加载数据库厂商提供的驱动程序

10. Class.forName(“ com.mysql.jdbc.Driver ”);

11. } catch (ClassNotFoundException e) {

12. e.printStackTrace();

13. }

15. String url = "jdbc:mysql://localhost:3306/studb";

16. try {

17. // step2：提供数据库连接的URL,通过DriverManager获得数据库的一个连接对象

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

19. } catch (SQLException e) {

20. e.printStackTrace();

21. }

23. try {

24. // step3：创建Statement(SQL的执行环境)

25. pstmt = conn.prepareStatement(sql);

26. pstmt.setInt(1, id);

28. // step4: 执行SQL语句

29. rs = pstmt.executeQuery();

31. // step5: 处理结果

32. while (rs.next()) {

33. int i = 1;

34. System.out.print(" 学员编号: " + rs.getInt(i++));

35. System.out.print(", 学员用户名: " + rs.getString(i++));

36. System.out.print(", 学员密码: " + rs.getString(i++));

37. System.out.println(", 学员年龄: " + rs.getInt(i++));

38. }

39. } catch (SQLException e) {

40. e.printStackTrace();

41. } finally {

42. // step6: 关闭数据库连接

43. DbClose.close(rs, pstmt, conn);

44. }

45. }

46.}

三、JDBC驱动源码解析

Java数据库连接（JDBC）由一组用 Java 编程语言编写的类和接口组成。JDBC 为工具/数据库开发人员提供了一个标准的 API，使他们能够用纯Java API 来编写数据库应用程序。说白了一套Java访问数据库的统一规范，如下图，具体与数据库交互的还是由驱动实现，JDBC规范之于驱动的关系，也类似于Servlet规范与Servlet容器(Tomcat)的关系，本质就是一套接口和一套实现的关系。如下类图所示，我们平时开发JDBC时熟悉的Connection接口在Mysql驱动中的实现类是com.mysql.jdbc.JDBC4Connection类，PreparedStatement接口在Mysql驱动中的实现类是com.mysql.jdbc.JDBC4Connection, ResultSet接口在Mysql驱动中的实现类是 com.mysql.jdbc.JDBC4ResultSet，下面的源码解析也是通过这几个类展开。

1：加载数据库厂商提供的驱动程序

首先我们通过Class.forName("com.mysql.jdbc.Driver")来加载mysql的jdbc驱动。 Mysql的com.mysql.jdbc.Driver类实现了java.sql.Driver接口，任何数据库提供商的驱动类都必须实现这个接口。在DriverManager类中使用的都是接口Driver类型的驱动，也就是说驱动的使用不依赖于具体的实现，这无疑给我们的使用带来很大的方便。如果需要换用其他的数据库的话，只需要把Class.forName()中的参数换掉就可以了，可以说是非常方便的，com.mysql.jdbc.Driver类也是驱动实现JDBC规范的第一步。

Java代码

1. public class Driver extends NonRegisteringDriver implements java.sql.Driver {

2. static {

3. try {

4. //往DriverManager中注册自身驱动

5. java.sql.DriverManager.registerDriver(new Driver());

6. } catch (SQLException E) {

7. throw new RuntimeException("Can't register driver!");

8. }

9. }

10. public Driver() throws SQLException {

11. }

12. }

在com.mysql.jdbc.Driver类的静态初始化块中会向java.sql.DriverManager注册它自己 ,注册驱动首先就是初始化，然后把驱动的信息封装一下放进一个叫做DriverInfo的驱动信息类中，最后放入一个驱动的集合中, 到此Mysql的驱动类com.mysql.jdbc.Driver也就已经注册到DriverManager中了。

Java代码

1. public static synchronized void registerDriver(java.sql.Driver driver) throws SQLException {

2. if (!initialized) {

3. initialize();

4. }

6. DriverInfo di = new DriverInfo();

8. //把driver的信息封装一下，组成一个DriverInfo对象

9. di.driver = driver;

10. di.driverClass = driver.getClass();

11. di.driverClassName = di.driverClass.getName();

13. writeDrivers.addElement(di);

14. println("registerDriver: " + di);

16. readDrivers = (java.util.Vector) writeDrivers.clone();

17. }

注册驱动的具体过程序列图如下：

2.获取数据库连接

数据库连接的本质其实就是客户端维持了一个和远程MySQL服务器的一个TCP长连接，并且在此连接上维护了一些信息。

通过 DriverManager.getConnection(url, "root", "root")获取数据库连接对象时，由于之前已经在 DriverManager中注册了驱动类，所有会找到那个驱动类来连接数据库com.mysql.jdbc.Driver.connect

Java代码

1. private static Connection getConnection(

2. String url, java.util.Properties info, ClassLoader callerCL) throws SQLException {

3. java.util.Vector drivers = null;

5. if (!initialized) {

6. initialize();

7. }

8. //取得连接使用的driver从readDrivers中取

9. synchronized (DriverManager.class){

10. drivers = readDrivers;

11. }

13. SQLException reason = null;

14. for (int i = 0; i < drivers.size(); i++) {

15. DriverInfo di = (DriverInfo)drivers.elementAt(i);

17. if ( getCallerClass(callerCL, di.driverClassName ) != di.driverClass ) {

18. continue;

19. }

20. try {

21. // 找到可供使用的驱动，连接数据库server

22. Connection result = di.driver.connect(url, info);

23. if (result != null) {

24. return (result);

25. }

26. } catch (SQLException ex) {

27. if (reason == null) {

28. reason = ex;

29. }
}

接着看com.mysql.jdbc.Driver.connect是如何建立连接返回数据库连接对象的，写法很简洁，就是创建了一个MySQL的数据库连接对象，传入host, port, database等连接信息，在com.mysql.jdbc.Connection的构造方法里面有个createNewIO()方法，主要会做两件事情，一、建立和MysqlServer的Socket连接，二、连接成功后，进行登录校验, 发送用户名、密码、当前数据库连接默认选择的数据库名。

Java代码

1. public java.sql.Connection connect(String url, Properties info)

2. throws SQLException {

3. Properties props = null;

4. try {

5. // 传入host,port,database等连接信息创建数据库连接对象

6. Connection newConn = new com.mysql.jdbc.ConnectionImpl(host(props),

7. port(props), props, database(props), url);

9. return newConn;

10. } catch (SQLException sqlEx) {

11. throw sqlEx;

12. } catch (Exception ex) {

13. throw SQLError.createSQLException(...);

14. }

15. }

继续往下看ConnectionImpl构造器中的实现，会调用 createNewIO()方法来创建一个MysqlIO对象，维护在Connection中。

Java代码

1. protected ConnectionImpl(String hostToConnectTo, int portToConnectTo, Properties info,

2. String databaseToConnectTo, String url)

3. throws SQLException {

4. try {

5. this.dbmd = getMetaData(false, false);

6. //创建MysqlIO对象，建立和MySql服务端的连接，并且进行登录校验工作

7. createNewIO(false);

8. initializeStatementInterceptors();

9. this.io.setStatementInterceptors(this.statementInterceptors);

10. } catch (SQLException ex) {

11. cleanup(ex);

13. throw ex;

14. }

15. }

16. }

紧接着createNewIO()会建了一个com.mysql.jdbc.MysqlIO，利用com.mysql.jdbc.StandardSocketFactory来创建一个Socket建立与MySQL服务器的连接，然后就由这个mySqlIO来与MySql服务器进行握手(doHandshake())，这个doHandshake主要用来初始化与MySQL server的连接，负责登陆服务器和处理连接错误。在其中会分析所连接的mysql server的版本，根据不同的版本以及是否使用SSL加密数据都有不同的处理方式，并把要传输给数据库server的数据都放在一个叫做packet的buffer中，调用send()方法往outputStream中写入要发送的数据。

Java代码

1. protected void createNewIO(boolean isForReconnect)

2. throws SQLException {

4. // 创建一个MysqlIO对象，建立与Mysql服务器的Socket连接

5. this.io = new MysqlIO(newHost, newPort, mergedProps,

6. getSocketFactoryClassName(), this,

7. getSocketTimeout(),

8. this.largeRowSizeThreshold.getValueAsInt());

10. // 登录校验MySql Server, 发送用户名、密码、当前数据库连接默认选择的数据库名

11. this.io.doHandshake(this.user, this.password,

12. this.database);

14. // 获取MySql数据库连接的连接ID

15. this.connectionId = this.io.getThreadId();

16. this.isClosed = false;

17. }

具体的跟Mysql Server建立连接的代码如下：

Java代码

1. public MysqlIO(String host, int port, Properties props,

2. String socketFactoryClassName, ConnectionImpl conn,

3. int socketTimeout, int useBufferRowSizeThreshold) throws IOException, SQLException {

4. this.connection = conn;

6. try {

7. // 创建Socket对象,和MySql服务器建立连接

8. this.mysqlConnection = this.socketFactory.connect(this.host,

9. this.port, props);

11. // 获取Socket对象

12. this.mysqlConnection = this.socketFactory.beforeHandshake();

14. //封装SocketInputStream输入流

15. if (this.connection.getUseReadAheadInput()) {

16. this.mysqlInput = new ReadAheadInputStream(this.mysqlConnection.getInputStream(), 16384,

17. this.connection.getTraceProtocol(),

18. this.connection.getLog());

19. } else {

20. this.mysqlInput = new BufferedInputStream(this.mysqlConnection.getInputStream(),

21. 16384);

22. }

23. //封装ScoketOutputStream输出流

24. this.mysqlOutput = new BufferedOutputStream(this.mysqlConnection.getOutputStream(),

25. 16384);

26. }

具体的跟MySQL Server交互登录校验的代码如下：

Java代码

1. void secureAuth411(Buffer packet, int packLength, String user,

2. String password, String database, boolean writeClientParams)

3. throws SQLException {

5. // 设置用户名

6. packet.writeString(user, "utf-8", this.connection);

8. if (password.length() != 0) {

9. packet.writeByte((byte) 0x14);

10. try {

11. // 设置密码

12. packet.writeBytesNoNull(Security.scramble411(password, this.seed, this.connection));

13. } catch (NoSuchAlgorithmException nse) {

14. }

16. if (this.useConnectWithDb) {

17. // 设置连接数据库名

18. packet.writeString(database, "utf-8", this.connection);

19. }

21. // 向Mysql服务器发送登录信息包(用户名、密码、此Socket连接默认选择的数据库)

22. send(packet, packet.getPosition());

24. byte savePacketSequence = this.packetSequence++;

26. // 读取Mysql服务器登录检验后发送的状态信息，如果成功就返回，如果登录失败则抛出异常

27. Buffer reply = checkErrorPacket();

28. }

最终由SocketOutputStream经过一次RPC发送给MySQLServer进行验证。

Java代码

1. private final void send(Buffer packet, int packetLen)

2. throws SQLException {

3. try {

4. //把登录信息的字节流发送给MySQL Server

5. this.mysqlOutput.write(packetToSend.getByteBuffer(), 0,

6. packetLen);

7. this.mysqlOutput.flush();

8. }

9. } catch (IOException ioEx) {

10. throw SQLError.createCommunicationsException(this.connection,

11. this.lastPacketSentTimeMs, this.lastPacketReceivedTimeMs, ioEx);

12. }

13. }

具体的获取数据库连接的序列图如下:

3.创建PreparedStatement，并设置参数

当创建完数据库连接之后，就可以通过conn.prepareStatement(sql) 来获取SQL执行环境PreparedStatement了，获取PreparedStatement的逻辑非常简单，会根据需要编译的SQL语句和Connection连接对象来创建一个JDBC4PreparedStatement对象，也就是相应SQL的执行环境了，具体代码如下：

Java代码

1. public java.sql.PreparedStatement prepareStatement(String sql,

2. int resultSetType, int resultSetConcurrency) throws SQLException {

3. checkClosed();

4. PreparedStatement pStmt = null;

6. //需要预编译的SQL语句

7. String nativeSql = getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql): sql;

9. if (this.useServerPreparedStmts && getEmulateUnsupportedPstmts()) {

10. canServerPrepare = canHandleAsServerPreparedStatement(nativeSql);

11. }

12. // 创建JDBC4PreapareStatement对象,这个SQL环境中持有预编译SQL语句及对应的数据库连接对象

13. pStmt = com.mysql.jdbc.PreparedStatement.getInstance(this, nativeSql,

14. this.database);

15. return pStmt;

16. }

当创建完SQL执行环境PreparedStatement对象之后，就可以设置一些自定义的参数了，最终会把参数值保存在JDBC4PreapareStatement的parameterValues字段，参数类型保存在parameterTypes中，如下代码：

Java代码

1. public void setInt(int parameterIndex, int x) throws SQLException {

2. int parameterIndexOffset = getParameterIndexOffset();

4. checkBounds(paramIndex, parameterIndexOffset);

5. byte[] parameterAsBytes = StringUtils.getBytes(String.value(x), this.charConverter,

6. this.charEncoding, this.connection

7. .getServerCharacterEncoding(), this.connection

8. .parserKnowsUnicode());

9. this.parameterStreams[paramIndex - 1 + parameterIndexOffset] = null;

10. //设置参数值

11. this.parameterValues[paramIndex - 1 + parameterIndexOffset] = parameterAsBytes;

12. //设置参数类型

13. this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = Types.INTEGER;

14. }

具体的创建PreparedStatement的序列图如下：

3.执行查询

创建完PreparedStatement之后，就一切准备就绪了，就可以通过 pstmt.executeQuery()来执行查询了。主要思路是根据SQL模板和设置的参数，解析成一条完整的SQL语句，最后根据MySQL协议，序列化成字节流，RPC发送给MySQL服务端。主要的处理过程如下：

Java代码

1. public java.sql.ResultSet executeQuery() throws SQLException {

2. checkClosed();

3. ConnectionImpl locallyScopedConn = this.connection;

4. CachedResultSetMetaData cachedMetadata = null;

5. synchronized (locallyScopedConn.getMutex()) {

6. if (doStreaming

7. && this.connection.getNetTimeoutForStreamingResults() > 0) {

8. locallyScopedConn.execSQL(this, "SET net_write_timeout="

9. + this.connection.getNetTimeoutForStreamingResults(),

10. -1, null, ResultSet.TYPE_FORWARD_ONLY,

11. ResultSet.CONCUR_READ_ONLY, false, this.currentCatalog,

12. null, false);

13. }

14. //解析封装需要发送的sql语句,序列化成MySQL协议对应的字节流

15. Buffer sendPacket = fillSendPacket();

17. if (locallyScopedConn.getCacheResultSetMetadata()) {

18. cachedMetadata = locallyScopedConn.getCachedMetaData(this.originalSql);

19. }

21. Field[] metadataFromCache = null;

23. // 执行sql语句,并获取MySQL发送过来的结果字节流，根据MySQL协议反序列化成ResultSet

24. this.results = executeInternal(-1, sendPacket,

25. doStreaming, true,

26. metadataFromCache, false);

27.

28. if (oldCatalog != null) {

29. locallyScopedConn.setCatalog(oldCatalog);

30. }

32. }

33. this.lastInsertId = this.results.getUpdateID();

34. return this.results;

35. }

接下来看下 fillSendPacket() 方法怎么来序列化成二进制字节流的，请看下面的代码分析

Java代码

1. protected Buffer fillSendPacket(byte[][] batchedParameterStrings,

2. InputStream[] batchedParameterStreams, boolean[] batchedIsStream,

3. int[] batchedStreamLengths) throws SQLException {

4. // 从connection的IO中得到发送数据包，首先清空其中的数据

5. Buffer sendPacket = this.connection.getIO().getSharedSendPacket();

6. sendPacket.clear();

8. //数据包的第一位为一个操作标识符（MysqlDefs.QUERY），表示驱动向服务器发送的连接的操作信号，包括有QUERY, PING, RELOAD, SHUTDOWN, PROCESS_INFO, QUIT, SLEEP等等，这个操作信号并不是针对sql语句操作而言的CRUD操作，从提供的几种参数来看，这个操作是针对服务器的一个操作。一般而言，使用到的都是MysqlDefs.QUERY，表示发送的是要执行sql语句的操作。

9. sendPacket.writeByte((byte) MysqlDefs.QUERY);

11. boolean useStreamLengths = this.connection

12. .getUseStreamLengthsInPrepStmts();

14. int ensurePacketSize = 0;

15. for (int i = 0; i < batchedParameterStrings.length; i++) {

16. if (batchedIsStream[i] && useStreamLengths) {

17. ensurePacketSize += batchedStreamLengths[i];

18. }

19. }

21. // 判断这个sendPacket的byte buffer够不够大，不够大的话，按照1.25倍来扩充buffer

22. if (ensurePacketSize != 0) {

23. sendPacket.ensureCapacity(ensurePacketSize);

24. }

26. //遍历所有的参数。在prepareStatement阶段的new ParseInfo()中，驱动曾经对sql语句进行过分割，如果含有以问号标识的参数占位符的话，就记录下这个占位符的位置，依据这个位置把sql分割成多段，放在了一个名为staticSql的字符串中。这里就开始把sql语句进行拼装，把staticSql和parameterValues进行组合，放在操作符的后面

27. for (int i = 0; i < batchedParameterStrings.length; i++) {

28. if ((batchedParameterStrings[i] == null)

29. && (batchedParameterStreams[i] == null)) {

30. throw SQLError.createSQLException(Messages

31. .getString("PreparedStatement.40") //$NON-NLS-1$

32. + (i + 1), SQLError.SQL_STATE_WRONG_NO_OF_PARAMETERS);

33. }

35. //在sendPacket中加入staticSql数组中的元素，就是分割出来的没有”?”的sql语句，并把字符串转换成byte

36. sendPacket.writeBytesNoNull(this.staticSqlStrings[i]);

38. if (batchedIsStream[i]) {

39. streamToBytes(sendPacket, batchedParameterStreams[i], true,

40. batchedStreamLengths[i], useStreamLengths);

41. } else {

43. //用batchedParameterStrings，也就是parameterValues来填充参数位置。在循环中，这个操作是跟在staticSql后面的，因此就把第i个参数加到了第i个staticSql段中。参考前面的staticSql的例子，发现当循环结束的时候，原始sql语句最后一个”?”之前的sql语句就拼成了正确的语句了

44. sendPacket.writeBytesNoNull(batchedParameterStrings[i]);

45. }

46. }

48. // 由于在原始的包含问号的sql语句中，在最后一个”?”后面可能还有order by等语句，因此staticSql数组中的元素个数一定比参数的个数多1，所以这里把staticSqlString中最后一段sql语句放sendPacket中

49. sendPacket.writeBytesNoNull(this.staticSqlStrings[batchedParameterStrings.length]);

50. return sendPacket;

51. }

准备好需要发送的MySQL协议的字节流后，就可以一路通过ConnectionImpl.execSQL--> MysqlIO.sqlQueryDirect --> MysqlIO.send -- >OutPutStram.write把字节流数据通过Socket发送给MySQL服务器，然后线程阻塞等待服务端返回结果数据，拿到数据后再根据MySQL协议反序列化成我们熟悉的ResultSet对象。

具体执行SQL的序列图如下：

四、探究MyQL预编译

一．背景：

现在我们淘宝持久化大多数是采用iBatis+MySQL做开发的，大家都知道，iBatis内置参数，形如#xxx#的，均采用了sql预编译的形式，举例如下：

Xml代码

1. <select id=”queryUserById” returnType=”userResult”>

2. SELECT * FROM user WHERE id =#id#

3. </select>

查看日志后，会发现这个sql执行时被记录如下，SELECT * FROM user WHERE id = ?

看过iBatis源码发现底层使用的就是JDBC的PreparedStatement，过程是先将带有占位符（即”?”）的sql模板发送至mysql服务器，由服务器对此无参数的sql进行编译后，将编译结果缓存，然后直接执行带有真实参数的sql。查询了相关文档及资料后, 基本结论都是，使用预编译，可以提高sql的执行效率，并且有效地防止了sql注入。但是一直没有亲自去测试下，趁着最近看MySQL_JDBC的源码的契机，好好研究测试了下。测试结果出乎意料，发现原来一直以来我对PreparedStatement的理解是有误的。我们平时使用的不管是JDBC还是ORM框架iBatis默认都没有真正开启预编译，形如PreparedStatement( SELECT * FROMuser WHERE id = ? )，每次都是驱动拼好完整带参数的SQL( SELECT * FROM user WHERE id = 5 )，然后再发送给MySQL服务端，压根就没用到如PreparedStatement名字的功能。咨询了淘宝相关DBA 和相关TDDL同学，确认了现在我们线上使用的TDDL（JDBC）默认都是没有打开预编译的，但是经过测试确实预编译会快一点，DBA那边之后会详细测试并推广到线上。

接下来我会把探究过程跟大家分享并记录下。

二．问题：

我的疑问有两点：1.MySQL是否默认开启了预编译功能？若没有，将如何开启？ 2.预编译是否能有效提升执行SQL的性能？

三．探究一：MySQL是否默认开启了预编译？

首先针对第一个问题。我的电脑上已经安装了MySQL，版本是5.1.9，打开配置文件my.ini，在"port=3306" 这一行下面加了配置：log=d:/logs/mysql_log.txt，这样就开启了MySQL日志功能，该日志主要记录MySQL执行sql的过程。重启MySQL，并建立一个库studb，在该库下建一个叫user的表，有id(主键)和username和password三个字段。

接着，我建立了一个简单的Java工程，引入JDBC驱动包mysql-connector-java-5.0.3-bin.jar。然后写了如下的代码：

Java代码

1. public static void main(String[] args) throws Exception{

2. String sql = "select * from userwhere id= ?";

3. Class.forName("com.mysql.jdbc.Driver");

4. Connection conn = null;

5. try{

6. conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?user=root&password=root");

7. PreparedStatement stmt = conn.prepareStatement(sql);

8. stmt.setString(1,5);

9. ResultSet rs = stmt.executeQuery();

10. rs.close();

11. stmt.close();

12. }catch(Exception e){

13. e.printStackTrace();

14. }finally{

15. conn.close();

16. }

17. }

执行这些代码后，打开刚才配置的mysql日志文件mysql_log.txt，日志记录如下：

1 Query SET NAMES utf8

1 Query SET character_set_results = NULL

1 Query SHOW VARIABLES

1 Query SHOW WARNINGS

1 Query SHOW COLLATION

1 Query SET autocommit=1

1 Prepare select *from user where id = ?

1 Execute select * from user where id= 5

1 Close stmt

1 Quit

从MySQL日志可以清晰看到，server端执行了一次预编译Prepare及执行了一次Execute，预编译sql模板为“select * from user where id= ?”，说明MySQL5.1.19+ mysql-connector-java-5.0.3是默认开启预编译的。但还是有很多疑惑，为什么之前查阅资料,都说开启预编译是跟 useServerPrepStmts 参数有关的，于是将刚才代码里的JDBC连接修改如下：

Java代码

1. DriverManager.getConnection("jdbc:mysql://localhost:3306/prepare_stmt_test?user=root&password=root&useServerPrepStmts=false")

执行代码后，再次查看mysql日志：

1Query SET NAMES utf8

1 Query SET character_set_results = NULL

1 Query SHOW VARIABLES

1 Query SHOW WARNINGS

1 Query SHOW COLLATION

1 Query SET autocommit=1

1 Query select * from user where id= 5

1Quit

果然，日志没有了prepare这一行，说明MySQL没有进行预编译。这意味着useServerPrepStmts这个参数是起效的，且默认值为true。

最后意识到useServerPrepStmts这个参数是JDBC的连接参数，这说明此问题与JDBC驱动程序可能有关系。打开MySQL官网，发现在线的官方文档很强大，支持全文检索，于是我将“useServerPrepStmts”做为关键字，搜索出了一些信息，原文如下：

Important change: Due to a number ofissues with the use of server-side prepared statements, Connector/J5.0.5 has disabled their use by default. The disabling of server-sideprepared statements does not affect the operation of the connector in any way.

To enable server-sideprepared statements, add the following configuration property to your connectorstring:

useServerPrepStmts=true

The default value of thisproperty is false (that is,Connector/J does not use server-side prepared statements)

这段文字说，Connector/J在5.0.5以后的版本，默认useServerPrepStmts参数为false，Connector/J就是我们熟知的JDBC驱动程序。看来，如果我们的驱动程序为5.0.5或之后的版本，想启用mysql预编译，就必须设置useServerPrepStmts=true。我的JDBC驱动用的是5.0.3，这个版本的useServerPrepStmts参数默认值是true。于是我将Java工程中的JDBC驱动程序替换为5.0.8的版本，去掉代码里JDBC连接中的useServerPrepStmts参数，再执行，发现mysql_log.txt的日志打印如下：

2 Query SHOW SESSIONVARIABLES

2 Query SHOW WARNINGS

2 Query SHOW COLLATION

2 Query SET NAMES utf8

2 Query SET character_set_results = NULL

2 Query SET autocommit=1

2 Query select * from user where id= 5

2 Quit

果然，在mysql_log.txt日志里，prepare关键字没有了,说明 useServerPrepStmts 参数确实跟JDBC驱动版本有关。另外还查阅了相关MySQL的官方文档后，发现MySQL服务端是在4.1版本才开始支持预编译的，之后的版本都默认支持预编译。

第一个问题解决了，结论就是：要打开预编译功能跟MySQL版本及 MySQL Connector/J（JDBC驱动）版本都有关，首先MySQL服务端是在4.1版本之后才开始支持预编译的，之后的版本都默认支持预编译，并且预编译还与 MySQL Connector/J（JDBC驱动）的版本有关， Connector/J 5.0.5之前的版本默认支持预编译， Connector/J 5.0.5之后的版本默认不支持预编译，所以我们用的Connector/J 5.0.5驱动以后版本的话默认都是没有打开预编译的（如果需要打开预编译，需要配置 useServerPrepStmts 参数）

四．探究二：预编译是否能有效提升执行SQL的性能？

首先，我们要明白MySQL执行一个sql语句的过程。查了一些资料后，我得知，mysql执行脚本的大致过程如下：prepare（准备）-> optimize（优化）-> exec（物理执行），其中，prepare也就是我们所说的编译。前面已经说过，对于同一个sql模板，如果能将prepare的结果缓存，以后如果再执行相同模板而参数不同的sql，就可以节省掉prepare（准备）的环节，从而节省sql执行的成本。明白这一点后，我写了如下测试程序：

Java代码

1. public static void main(String []a) throws Exception{

2. String sql = "select * from user whereid = ?";

3. Class.forName("com.mysql.jdbc.Driver");

4. Connection conn = null;

5. try{

6. conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?user=root&password=root&useServerPrepStmts=true");

7. PreparedStatement stmt = conn.prepareStatement(sql);

8. stmt.setString(1,5);

9. ResultSet rs1 = stmt.executeQuery(); //第一次执行

10. s1.close();

11. stmt.setString(1,9);

12. ResultSet rs2 = stmt.executeQuery(); //第二次执行

13. rs2.close();

14. stmt.close();

15. }catch(Exception e){

16. e.printStackTrace();

17. }finally{

18. conn.close();

19. }

20. }

执行该程序后，查看mysql日志：

1Query SHOW SESSION VARIABLES

1 Query SHOW WARNINGS

1 Query SHOW COLLATION

1 Query SET NAMES utf8

1 Query SET character_set_results = NULL

1 Query SET autocommit=1

1 Prepare select * from userwhere id = ?

1 Execute select * from user where id = 5

1 Execute select * from user where id = 9

1 Close stmt

1 Quit

按照日志看来，PreparedStatement重新设置sql参数后，并没有重新prepare，看来预编译起到了效果。但刚才我们使用的是同一个stmt，如果将stmt关闭，重新获取一个stmt呢？

Java代码

1. public static void main(String []a) throws Exception{

2. String sql = "select * from userwhere id = ?";

3. Class.forName("com.mysql.jdbc.Driver");

4. Connection conn = null;

5. try{

6. conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?user=root&password=root&useServerPrepStmts=true");

7. PreparedStatement stmt = conn.prepareStatement(sql);

8. stmt.setString(1,5);

9. ResultSet rs1 = stmt.executeQuery(); //第一次执行

10. rs1.close();

11. stmt.close();

12. stmt = conn.prepareStatement(sql); //重新获取一个statement

13. stmt.setString(1,9);

14. ResultSet rs2 = stmt.executeQuery(); //第二次执行

15. rs2.close();

16. stmt.close();

17. }catch(Exception e){

18. e.printStackTrace();

19. }finally{

20. conn.close();

21. }

22. }

mysql日志打印如下：

1Query SHOW SESSION VARIABLES

1 Query SHOW WARNINGS

1 Query SHOW COLLATION

1 Query SET NAMES utf8

1 Query SET character_set_results = NULL

1 Query SET autocommit=1

1 Prepare select * from user where id=?

1 Execute select * from user where id= 5

1 Close stmt

1 Prepare select *from user where id = ?

1 Execute select * from user where id = 9

1 Close stmt

1 Quit

很明显，关闭stmt后再执行第二个sql，mysql就重新进行了一次预编译，这样是无法提高sql执行效率的。而在实际的应用场景中，我们不可能保持同一个statement。那么，mysql如何缓存预编译结果呢？

搜索一些资料后得知，JDBC连接参数中有另外一个重要的参数：cachePrepStmts ，设置为true后可以缓存预编译结果。于是我将测试代码中JDBC连接串改为了这样：

Java代码

1. conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/prepare_stmt_test?user=root&password=root&useServerPrepStmts=true&cachePrepStmts=true");

再执行代码后，发现mysql日志记录又变成了这样：

1 Prepare select * from user where id = ?

1 Execute select * from user where id = 5

1 Execute select * from user where id = 9

OK，现在我们才正式开启了预编译，并开启了缓存预编译的功能。那么接下来我们对预编译语句("select * from userwhere id = ?")进行性能测试，测试数据如下：

当不开启预编译功能时（String url ="jdbc:mysql://localhost:3306/studb"），做10次测试，100000个select总时间为（单位毫秒）
      12321，12173，12159，12132，12604，12349，12621，12356，12899，12287
   （每次查询一个RPC，每一个查询，都会在mysql server端做一次编译及一次执行）
   Mysql协议：xx xx xx xx QUERY .. .. .. .. .. ..
   Mysql协议：xx xx xx xx QUERY .. .. .. .. .. ..

开启预编译，但不开启预编译缓存时（String url= "jdbc:mysql://localhost:3306/studb?useServerPrepStmts=true"），做10次测试，100000个select总时间为（单位毫秒）
      21349，22860，27237，26848，27772，28100，23114，22897，20010，23211
   （每次查询需要两个RPC，第一个RPC是编译，第二个RPC是执行，进测试数据可以看到这种其实与不打开预编译相比居然还慢，因为多了一次RPC，网络开销在那里）
   Mysql协议：xx xx xx xx PREPARE .. .. .. .. .. ..
   Mysql协议：xx xx xx xx EXECUTE PS_ID ..
   Mysql协议：xx xx xx xx PREPARE .. .. .. .. .. ..
   Mysql协议：xx xx xx xx EXECUTE PS_ID ..

开启预编译，并开启预编译缓存时（String url ="jdbc:mysql://localhost:3306/studb?useServerPrepStmts=true&cachePrepStmts=true"），做10次测试，100000个select总时间为
   8732，8655，8678，9693，8624，9874，8444，9660，8607，8780
  （第一次两个RPC，之后都是一个RPC，第一次会因为编译sql模板走一次RPC，后面都只需要执行一次RPC，在mysql server端不需要编译，只需要执行）
   Mysql协议：xx xx xx xx PREPARE .. .. .. .. .. ..
   Mysql协议：xx xx xx xx EXECUTE PS_ID ..
   Mysql协议：xx xx xx xx EXECUTE PS_ID ..

从测试结果看来，若开启预编译，但不开启预编译缓存，查询效率会有明显下降，因为需要走多次RPC，且每个查询都需要编译及执行；开启预编译并且打开预编译缓存的明显比不打开预编译的查询性能好30%左右（这个是本机测试，还需要更多验证）。

结论：对于Connector/J5.0.5以后的版本，若使用useServerPrepStmts=true开启预编译，则一定需要同时使用cachePrepStmts=true 开启预编译缓存，否则性能会下降，只有二者都开启，才算是真正开启了预编译功能，性能会比不开启预编译提升30%左右（这个可能是我测试程序的原因，有待进一步研究）

五．预编译JDBC驱动源码剖析

首先对于打开预编译的URL（String url ="jdbc:mysql://localhost:3306/studb?useServerPrepStmts=true&cachePrepStmts=true"）获取数据库连接之后，本质是获取预编译语句pstmt = conn.prepareStatement(sql)时会向MySQL服务端发送一个RPC，发送一个预编译的SQL模板（驱动会拼接mysql预编译语句prepare s1 from 'select * fromuser where id = ?'），然会MySQL服务端会编译好收到的SQL模板，再会为此预编译模板语句分配一个serverStatementId发送给JDBC驱动，这样以后PreparedStatement就会持有当前预编译语句的服务端的serverStatementId,并且会把此 PreparedStatement缓存在当前数据库连接中，以后对于相同SQL模板的操作pstmt.executeUpdate()，都用相同的PreparedStatement，执行SQL时只需要发送serverStatementId和参数，节省一次SQL编译, 直接执行。并且对于每一个连接(驱动端及Mysql服务端)都有自己的preparecache,具体的源码实现是在com.mysql.jdbc.ServerPreparedStatement中实现。

阅读全文

0 0