下文均基于mysql-connector-java-5.1.43籍琳， mysql server version 5.6版本進(jìn)行分析菲宴。
從剛開始接觸JDBC開始，就學(xué)到使用PrepareStatement對(duì)sql進(jìn)行預(yù)編譯趋急，不用每次語(yǔ)句都進(jìn)行一次重新sql解析和編譯喝峦，相較于使用Statement能夠提高程序的性能，那么到底是用PrepareStatement對(duì)性能的提升有多大呢宣谈？
通過(guò)示例代碼：
import java.sql.*;
/**
* Created by ZHUKE on 2017/8/18.
*/
public class Main {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/test", "root", "root");
String prepareSql = "select * from user_info where firstName = ?";
PreparedStatement preparedStatement = conn.prepareStatement(prepareSql);
Statement statement = conn.createStatement();
String statementSql = "select * from user_info where firstName= 'zhuke'";
long nowTime = System.currentTimeMillis();
int count = 100000;
for (int i = 0; i < count; i++) {
preparedStatement.setString(1, "zhuke");
preparedStatement.execute();
}
long nowTime1 = System.currentTimeMillis();
System.out.println("preparedStatement execute " + count + " times consume " + (nowTime1 - nowTime) + " ms");
long nowTime2 = System.currentTimeMillis();
for (int i = 0; i < count; i++) {
statement.execute(statementSql);
}
long nowTime3 = System.currentTimeMillis();
System.out.println("statement execute " + count + " times consume " + (nowTime3 - nowTime2) + " ms");
}
}
執(zhí)行同樣的語(yǔ)句100000次愈犹，得到的結(jié)果如下：
14588 ： 14477，這就是我一直深信的性能提升？漩怎？勋颖？
一定是哪里出了問題，通過(guò)查找資料知道勋锤，PrepareStatement會(huì)將帶有參數(shù)占位符饭玲？的sql語(yǔ)句提交到mysql服務(wù)器，服務(wù)器會(huì)對(duì)sql語(yǔ)句進(jìn)行解析和編譯叁执，將編譯后的sql id返回給客戶端茄厘，客戶端下次值需要將參數(shù)值和sql id發(fā)送到服務(wù)器即可。以此節(jié)省了服務(wù)器多次重復(fù)編譯同一sql語(yǔ)句的開銷谈宛，而且因?yàn)椴挥妹看味及l(fā)送完整sql內(nèi)容次哈，也一定程度上節(jié)省了網(wǎng)絡(luò)開銷。
那么為什么以上代碼中吆录，PrepareStatement沒有實(shí)現(xiàn)性能提升呢窑滞？
通過(guò)開啟mysql的詳細(xì)日志，對(duì)PrepareStatement的執(zhí)行來(lái)一探究竟恢筝。
preparedStatement.setString(1, "zhuke");
preparedStatement.execute();
mysql日志如下：
通過(guò)mysql日志我們可以看到哀卫，通過(guò)PrepareStatement的方式，每次執(zhí)行發(fā)送給mysql服務(wù)器的依然是完整的參數(shù)拼接完成后的sql語(yǔ)句撬槽，并沒有利用到上述的服務(wù)器預(yù)編譯的特性此改。
通過(guò)mysql-connector-java(5.1.43版本)連接驅(qū)動(dòng)的源碼來(lái)查找原因。
public java.sql.PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
synchronized (getConnectionMutex()) {
……
if (this.useServerPreparedStmts && getEmulateUnsupportedPstmts()) {
canServerPrepare = canHandleAsServerPreparedStatement(nativeSql);
}
//如果useServerPreparedStmts配置為true侄柔，且服務(wù)器支持sql預(yù)編譯優(yōu)化共啃，則執(zhí)行服務(wù)器sql優(yōu)化
if (this.useServerPreparedStmts && canServerPrepare) {
if (this.getCachePreparedStatements()) {
synchronized (this.serverSideStatementCache) {
……
} else {//否則執(zhí)行本地預(yù)編譯
……
}
return pStmt;
}
}
服務(wù)器支持預(yù)編譯的情況下，那么就只由useServerPreparedStmts 控制是否進(jìn)行服務(wù)器預(yù)編譯了暂题。而從源碼中又知道其默認(rèn)值為false勋磕。那么如果不顯式配置useServerPreparedStmts =true，就不會(huì)進(jìn)行服務(wù)器預(yù)編譯敢靡，而只執(zhí)行本地預(yù)編譯挂滓。
Important change: Due to a number of issues with the use of server-side prepared statements, Connector/J 5.0.5 has disabled their use by default. The disabling of server-side prepared statements does not affect the operation of the connector in any way.
To enable server-side prepared statements, add the following configuration property to your connector string:
useServerPrepStmts=true
The default value of this property is false (that is, Connector/J does not use server-side prepared statements).
通過(guò)查找MySQL官網(wǎng)發(fā)現(xiàn)，驅(qū)動(dòng)文件在版本 5.0.5后將設(shè)為了false啸胧，所以需要手動(dòng)指定和開啟服務(wù)器預(yù)編譯功能赶站。
https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-0-5.html
通過(guò)在url鏈接中添加參數(shù)useServerPreparedStmts =true開啟服務(wù)器預(yù)編譯。
現(xiàn)在我們看到mysql日志信息如下：
此時(shí)我們看到纺念，開啟了服務(wù)器預(yù)編譯后贝椿，mysql服務(wù)器會(huì)首先prepare
預(yù)編譯
select * from user_info where firstName = ?
語(yǔ)句。
再次實(shí)驗(yàn)以上代碼陷谱，看看性能提升了多少：
13312 ： 14535烙博，性能提升了8.4%.
與之對(duì)應(yīng)的還有一個(gè)參數(shù)：cachePrepStmts表示服務(wù)器是否需要緩存prepare預(yù)編譯對(duì)象瑟蜈。
// 關(guān)閉cachePrepStmts時(shí)新建兩個(gè)preparedStatement
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/test?useServerPrepStmts=true", "root", "root");
String prepareSql = "select * from user_info where firstName = ?";
PreparedStatement preparedStatement = conn.prepareStatement(prepareSql);
preparedStatement.setString(1, "zhuke");
preparedStatement.execute();
preparedStatement.close();
preparedStatement = conn.prepareStatement(prepareSql);
preparedStatement.setString(1, "zhuke1");
preparedStatement.execute();
preparedStatement.close();
可以看到此時(shí)，針對(duì)完全相同的sql語(yǔ)句渣窜，服務(wù)器進(jìn)行了兩次預(yù)編譯過(guò)程铺根。
那么當(dāng)我們開啟cachePrepStmts的時(shí)候呢？
// 關(guān)閉cachePrepStmts時(shí)新建兩個(gè)preparedStatement
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/test?useServerPrepStmts=true&cachePrepStmts=true", "root", "root");
String prepareSql = "select * from user_info where firstName = ?";
PreparedStatement preparedStatement = conn.prepareStatement(prepareSql);
preparedStatement.setString(1, "zhuke");
preparedStatement.execute();
preparedStatement.close();
preparedStatement = conn.prepareStatement(prepareSql);
preparedStatement.setString(1, "zhuke1");
preparedStatement.execute();
preparedStatement.close();
可以看到乔宿，開啟cachePrepStmts時(shí)位迂，mysql服務(wù)器只進(jìn)行了一次預(yù)編譯過(guò)程。
通過(guò)閱讀源碼發(fā)現(xiàn)详瑞，當(dāng)開啟cachePrepStmts時(shí)掂林，客戶端會(huì)以sql語(yǔ)句作為鍵，預(yù)編譯完成后的對(duì)象PrepareStatement作為值坝橡，保存在Map中泻帮，以便下次可以重復(fù)利用和緩存。
//prepareStatement關(guān)閉時(shí)计寇，將對(duì)象存入緩存中
public void close() throws SQLException {
MySQLConnection locallyScopedConn = this.connection;
if (locallyScopedConn == null) {
return; // already closed
}
synchronized (locallyScopedConn.getConnectionMutex()) {
if (this.isCached && isPoolable() && !this.isClosed) {
clearParameters();
this.isClosed = true;
//緩存預(yù)編譯對(duì)象
this.connection.recachePreparedStatement(this);
return;
}
realClose(true, true);
}
}
public void recachePreparedStatement(ServerPreparedStatement pstmt) throws SQLException {
synchronized (getConnectionMutex()) {
if (getCachePreparedStatements() && pstmt.isPoolable()) {
synchronized (this.serverSideStatementCache) {
Object oldServerPrepStmt = this.serverSideStatementCache.put(makePreparedStatementCacheKey(pstmt.currentCatalog, pstmt.originalSql), pstmt);
if (oldServerPrepStmt != null) {
((ServerPreparedStatement) oldServerPrepStmt).isCached = false;
((ServerPreparedStatement) oldServerPrepStmt).realClose(true, true);
}
}
}
}
}
結(jié)論
使用mysql的預(yù)編譯對(duì)象PrepateStatement時(shí)刑顺，一定需要設(shè)置useServerPrepStmts=true開啟服務(wù)器預(yù)編譯功能，設(shè)置cachePrepStmts=true開啟客戶端對(duì)預(yù)編譯對(duì)象的緩存饲常。
參考資料：
https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
http://www.cnblogs.com/justfortaste/p/3920140.html
