使用MySQL编写的语句缓存& JDBC [英] Prepared Statement Cache with MySQL & JDBC

问题描述

我读到MySQL不支持服务器端查询计划缓存。因此，如果我想使用PreparedStatements获得性能优势，我可以做的是在JDBC Connection中启用语句缓存。因此，根据文档，它将支持基于每个连接缓存预准备语句。

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.

与MySQL具有服务器端相比，JDBC连接的PreparedStatement缓存的性能提升是多少？查询计划缓存？
因此，如果确实在物理连接的缓存中找到了PreparedStatement，这是否意味着当它到达mysql服务器时，mysql不会对其运行查询优化并且能够直接执行它？

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 ?

在使用MySQL作为我的数据库时，我是否应该在JDBC连接级别使用Statement缓存？我正在使用Hikari数据库连接池和Mysql JDBC连接器。

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.

推荐答案

是的，如果你知道你是什么，缓存不会受到影响这样做。如果您按照预期重复使用预准备语句，那么将客户端缓存与服务器端缓存组合使用会带来好处（许多人忘记了最重要的部分：D）。只需设置 Connector / J属性属性正确：

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.

这篇关于使用MySQL编写的语句缓存& JDBC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！