Prepared Statement Cache with MySQL & JDBC

问题:

I read that MySQL does not support server side query plan caching. So if i want to use PreparedStatements for performance benefits, what i can do is enable statement caching in JDBC Connection. So as per the docs it will enable caching of prepared statements on per connection basis.

What is the performance gain of PreparedStatement caching by JDBC connection compared to if MySQL had server side query plan caching ? So if a PreparedStatement is indeed found in the physical connection's cache, does it mean that when it gets to mysql server, mysql would not run query optimizaton on it and will be able to directly execute it ?

Should i even use Statement caching at JDBC connection level while using MySQL as my database ? I am using Hikari database connection pool with Mysql JDBC connector.

回答1:

Yes, caching won't hurt if you know what you are doing. Combining a client side cache with a server side cache wilds performance benefits if you reuse prepared statements as they are supposed to (a lot of people forget about the most important part :D). Just set Connector/J properties properties correctly:

cachePrepStmts=true&useServerPrepStmts=true

While I'm not a big fan of micro benchmarks, here is one to back my statements up (lame pun indeed). The interesting part about the benchmark is that it shows that enabling server side caching may actually slow things down without some client side caching, but with both layers of cache enabled as well as the proper reuse of prepared statements you may actually get a good speed up.

回答2:

One of the authors of HikariCP here. See the HikariCP wiki for how to properly configure MySQL for prepared statement caching. Prepared statement caching can dramatically speed up SQL. Additionally, it can avoid SQL-injection attacks on your code that would otherwise succeed if you concatenate user-provided input into SQL ordinary statements. Never compose a SQL string that contains user-provided input. Always use prepared statements, and set user-provided input as replacement values.

回答3:

Other makes and models of table servers will give you more performance benefits from JDBC prepared statements than will MySQL. Oracle, for example, can reuse execution plans.

But you should still use prepared statements in JDBC. There are plenty of reasons to use them, including the injection resistance that comes from binding your variables.