最近一个同事说遇到一个问题，“使用JDBC驱动进行prepareStatemet查询时，jdbc里的执行计划是错误的，有一些SQL特别慢。初步分析原因是数据分布不均匀，传递参数不一样时，prepareStatement仍然用的原来的执行计划。”最后的解决方案和结论是:
首先，对解决方案本身没有疑问，因为设置这个参数后，日志里打印出这个SQL确实快了，问题确实解决了。主要的疑问产生在“客户端会编译并缓存preparestatement的执行计划，设置为0就能禁用客户端缓存”在这个结论上。我认为这个结论是错误的，主要因为生成执行计划需要很多数据库的元信息和统计值等，jdbc驱动很难自己生成执行计划。
PrepareStatement的主要作用是什么呢？有一些经验的Java程序员都知道：
PrepareStatement会引入哪些问题呢？
那么JDBC 驱动到底能不能编译SQL并生成执行计划么？prepareThreshold=0的含义是什么呢？
参阅了几遍官方文档和一篇博客及查看了PostgreSQL JDBC驱动源码后总算明白了，起码算是逻辑自洽了。
首先，prepareThreshold 是一个开关，开始server prepare的开关，即开启重用SQL的开关。
public static void main(String args[]) throws Exception
{
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://xx.xx.14.173:6362/abase";
Connection conn = DriverManager.getConnection(url,"sa","123456");
PreparedStatement pstmt = conn.prepareStatement("SELECT ?");
// cast to the pg extension interface
org.postgresql.PGStatement pgstmt = pstmt.unwrap(org.postgresql.PGStatement.class);
// on the third execution start using server side statements
pgstmt.setPrepareThreshold(0);
for (int i=1; i<=5; i++)
{
pstmt.setInt(1,i);
boolean usingServerPrepare = pgstmt.isUseServerPrepare();
ResultSet rs = pstmt.executeQuery();
rs.next();
System.out.println("Execution: "+i+", Used server side: " + usingServerPrepare + ", Result: "+rs.getInt(1));
rs.close();
}
pstmt.close();
conn.close();
}当pgstmt.setPrepareThreshold(0);时，结果为:
Execution: 1, Used server side: false, Result: 1
Execution: 2, Used server side: false, Result: 2
Execution: 3, Used server side: false, Result: 3
Execution: 4, Used server side: false, Result: 4
Execution: 5, Used server side: false, Result: 5
当pgstmt.setPrepareThreshold(1);时，结果为:
Execution: 1, Used server side: true, Result: 1
Execution: 2, Used server side: true, Result: 2
Execution: 3, Used server side: true, Result: 3
Execution: 4, Used server side: true, Result: 4
Execution: 5, Used server side: true, Result: 5当pgstmt.setPrepareThreshold(2);时，结果为:
Execution: 1, Used server side: false, Result: 1
Execution: 2, Used server side: true, Result: 2
Execution: 3, Used server side: true, Result: 3
Execution: 4, Used server side: true, Result: 4
Execution: 5, Used server side: true, Result: 5SQL的解析过程
postgres=# create table test as select 111 a; /*测试表*/
postgres=> show log_parser_stats ; /*log_parser_stats参数为开启状态*/
log_parser_stats
------------------
on
postgres=# show log_planner_stats ; /*log_planner_stats参数为开启状态*/
log_planner_stats
-------------------
onjava 测试程序
public static void main(String args[]) {
try {
Class.forName("org.postgresql.Driver").newInstance();
String url = "jdbc:postgresql://192.168.88.128:5432/postgres?prepareThreshold=3";
Connection conn = DriverManager.getConnection(url, "postgres", "12345678");
int foovalue = 111;
PreparedStatement st = conn.prepareStatement("SELECT * FROM test WHERE a = ?");
st.setInt(1, foovalue);
ResultSet rs = st.executeQuery();
ResultSet rs1 = st.executeQuery();
ResultSet rs2 = st.executeQuery();
ResultSet rs3 = st.executeQuery();
while (rs3.next()) {
System.out.println(rs3.getString(1));
}
rs.close();
rs1.close();
rs2.close();
rs3.close();
st.close();
} catch (Exception ee) {
System.out.print(ee.getMessage());
}
}通过表查看数据库csv日志：
postgres=# select command_tag,message from postgres_log ;
command_tag | message
PARSE | PARSER STATISTICS
PARSE | PARSE ANALYSIS STATISTICS
PARSE | REWRITER STATISTICS
BIND | PLANNER STATISTICS
SELECT | execute : SELECT * FROM test WHERE a = $1
PARSE | PARSER STATISTICS
PARSE | PARSE ANALYSIS STATISTICS
PARSE | REWRITER STATISTICS
BIND | PLANNER STATISTICS
SELECT | execute : SELECT * FROM test WHERE a = $1
PARSE | PARSER STATISTICS
PARSE | PARSE ANALYSIS STATISTICS
PARSE | REWRITER STATISTICS
BIND | PLANNER STATISTICS
SELECT | execute S_1: SELECT * FROM test WHERE a = $1
BIND | PLANNER STATISTICS
SELECT | execute S_1: SELECT * FROM test WHERE a = $1结果:POSTGRESQL解析日志中记录了三步PARSER STATISTICS->PARSE ANALYSIS STATISTICS ->REWRITER STATISTICS -> BIND PLANNER STATISTICS，当执行第四遍的时候已经忽略了解析的过程，之后会直接生成执行计划
原文：https://www.cnblogs.com/wangzhen3798/p/12206811.html