PrepareStatement的功与过

PrepareStatement的功与过

背景

最近同事说遇到一个问题："使用JDBC驱动进行PrepareStatemet查询时，同一个SQL，当传递某些参数时执行特别慢（用psql命令行单独执行又特别快）。初步分析：JDBC里的执行计划选择不正确,当表的数据分布不均匀，且SQL传递参数不一样时，PrepareStatement仍然用的原来的执行计划”。最后的解决方案和结论是：

  • 方案是通过在jdbcURL上设置参数"prepareThreshold=0"解决（项目使用的PostgreSQL数据库）
  • 结论是prepareThreshold参数默认是5，客户端会编译并缓存preparestatement的执行计划，设置为0就能禁用客户端缓存

疑问

  • 首先，对解决方案本身没有疑问，因为设置这个参数后，日志里打印出这个SQL确实快了，问题确实解决了。
  • 主要的疑问产生在“客户端会编译并缓存preparestatement的执行计划，设置为0就能禁用客户端缓存”在这个结论上。感觉这个结论是错误的，主要因为生成执行计划需要很多数据库的元信息和统计值等，jdbc驱动很难自己生成执行计划。

PrepareStatement的功与过

PrepareStatement的主要作用是什么呢？有一些经验的Java程序员都知道：

  • 通过参数化可以防止SQL注入
  • 可以提高性能，数据库服务不用每次都硬解析SQL生成执行计划

PrepareStatement会引入哪些问题呢？

  • 遇到最多的是因为SQL只编译解析一次，执行计划的重用导致会忽略实际传入的参数对执行计划的影响。

prepareThreshold 参数的含义

那么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: 5

  • SQL的解析过程

    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
-------------------
 on

    java 测试程序

     public static void main(String args[]) {
		try {
			Class.forName("org.postgresql.Driver").newInstance();
			String url = "jdbc:postgresql://xx.xx.14.173:6362/abase?prepareThreshold=3";
			Connection conn = DriverManager.getConnection(url, "sa", "123456");
			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，当执行第四遍的时候已经忽略了解析的过程，会直接绑定执行计划

结论

  • prepareThreshold 是一个是否开启server prepare开关
    • 当为0时，禁用server prepare。相当于每次SQL都是硬解析。
    • 当为1时，开启server prepare。相当于每次都走服务端的缓存。复用已有的SQL解析结果和执行计划，不进行SQL解析。
    • 当n大于1时，执行第n次时开启server prepare。相当于从第N次开始复用SQL解析结果和执行计划，不进行SQL解析。
  • 要进行preparestatement时，需要客户端(jdbc驱动)和服务端(db server)相互配合，都需要编译和进行缓存，都需要占用内存空间。
    • jdbc驱动主要将SQL解析成native SQL format,不是向后台传送的原生的full sql text
    • jdbc驱动要缓存一些结果的类型和结果的元数据信息但不缓存执行计划
  • 同时也有一些其他的功能和例外情况需要注意，具体参见官方文档，比如
    • select * 后，做ddl 会对驱动已经缓存的元数据信息有影响
    • set search_path、每次参数传递时类型不一致等也会对服务器缓存的执行计划有影响，导致选择错误
    • 将 preferQueryMode设置为extendedCacheEverything时，可以将常规的statement 也设置成类似preparestatemt的行为，避免过渡的SQL解析
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/wangzhen3798/p/12206811.html