使用PHP是否可以在多个页面加载的服务器端缓存准备好的语句? [英] Are prepared statements cached server-side across multiple page loads with PHP?
问题描述
我在制作启用JDBC的Java应用程序时了解了预备语句，并且我的应用程序使用连接池层，这确保了预备语句在服务器端缓存，这会带来性能上的好处.
I learnt about prepared statements when making a JDBC-enabled Java application, and my app uses a connection pooling layer that assures me that prepared statements are cached server-side and this gives a performance benefit.
但是，对于PHP，我所读的所有内容都表明，它们仅在页面加载期间被缓存.通常，我不会多次重复同一查询，而是在给定的页面加载中运行多个不同的查询，但是会在多个页面加载中重复这些查询.
However, with PHP everything I've read says that they are only cached for the life of the page load. Generally I don't repeat the same query many times, but run several different queries, on a given page load, but will repeat them across multiple page loads.
由于我的PHP进程是持久性的(即使用PHP-FPM，它们将在整个生命周期中服务数百个页面，而不是一个页面)，我想知道它们是否将重用数据库连接，而不是产生并杀死它们每次点击.
As my PHP processes are persistent (i.e. they will serve hundreds of pages in their lifetime instead of just one, using PHP-FPM), I was wondering if they will re-use database connections, rather than spawning and killing them off for each hit.
- 将PHP-FPM与mysqli或PDO结合使用会使连接的时间长于单个页面加载的时间吗?
- 如果没有，我能做到吗?
- 如果这样做，或者我执行了#2，这将使准备好的语句的缓存持续的时间长于一页加载的时间吗?
请澄清一下，我不是在谈论查询缓存(完全是另一种野兽)，也不是在缓存查询输出.我想在服务器端缓存已编译的准备好的语句及其执行计划.
Just to clarify, I'm not talking about the query cache, which is another beast entirely, or caching the output of queries. I want to cache the compiled prepared statement and its execution plan server-side.
推荐答案
当服务请求php时，它将清除"实例并释放资源和其他变量.这是分几个步骤完成的.由于fastcgi在请求后使进程保持活动状态，因此不会执行所有步骤，也不会释放所有内存.有例如EG(persistent_list)，由 mysql_pconnect()，
要直接(重新)使用准备好的语句，您需要该语句(和该连接)的标识符.使用(php-)postgresql时，这只是一个(连接方式)唯一的字符串，您传递给 pg_execute()，因此您的脚本可以毫无问题地访问另一个实例先前创建的语句(使用相同的连接).
使用 mysqli 或
您所希望的就是MySQL的服务器端查询缓存.在最新版本中(请参阅链接)，在使用准备好的语句时可能会识别该语句.但是即使那样，它也不会重新使用实际的准备好的语句:
When a request is served php "cleans" the instance and frees resources and other variables. This is done in several steps. Since fastcgi keeps the process alive after a request not all steps are executed and not all memory is freed. There is e.g. EG(persistent_list) which is used by mysql_pconnect(), pg_pconnect(), ... This list isn't emptied between requests as long as the process keeps alive (could be, depending on the actual implementation, but that would defy the purpose of EG(persistent_list)). If you use persistent connections your script might get a "re-used" connection established during a previous request.
To (re-)use a prepared statement directly you need the identifier for that statement (and that connection). When using (php-)postgresql this is simply a (connection-wise) unique string you pass to pg_execute(), so your script has no problem to gain access to the statement previously prepared by another instance (using the same connection).
Using mysqli or PDO-mysql you need a resource/object as statement identifier. That's kind of a problem since neither the mysqli nor the pdo extension seem to offer a way of storing the resource in EG(persist_list) between requests and you can't recreate it either. Unless php-fpm offers such a "service" it's seems impossible to re-use a mysql prepared statement directly.
All you can hope for is MySQL's server-side query cache. In recent versions (see link) it may recognize the statement when using prepared statements. But even then it doesn't re-use the actual prepared statement:
对于通过二进制协议执行的准备好的语句，与查询缓存中的语句进行比较是基于扩展?后的语句文本.参数标记.该语句仅与通过二进制协议执行的其他缓存语句进行比较.也就是说，出于查询缓存的目的，通过二进制协议发布的语句不同于通过文本协议发布的语句.
For a prepared statement executed via the binary protocol, comparison with statements in the query cache is based on the text of the statement after expansion of ? parameter markers. The statement is compared only with other cached statements that were executed via the binary protocol. That is, for query cache purposes, statements issued via the binary protocol are distinct from statements issued via the text protocol.
因此，如果我没记错的话，当前您不能在php中使用上一个请求期间重新使用mysql语句.
So, if I'm not mistaken, currently you can't re-use a mysql statement prepared during a previous request in php.
这篇关于使用PHP是否可以在多个页面加载的服务器端缓存准备好的语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
