PHP PDO:准备好的mysql查询要缓存多长时间? [英] PHP PDO: How long are prepared mysql queries cached?

问题描述

如何利用准备好的陈述来提高绩效?我知道，如果将其放在循环中，这样的事情可能会有所帮助:

How to take advantage of prepared statements for performance? I understand that something like this might benefit if I put it in a loop:

SELECT `Name` FROM `Hobbits` WHERE `ID` = :ID;

我已经读过，用 准备好的语句循环比不使用没有准备的语句循环要快，但是其他情况下，准备好的语句会稍微降低性能.那么-这个循环有多大?

I've read that looping with prepared statements is faster than looping without, but otherwise prepared statements would slightly decrease performance. So - how big may that loop be?

如果我在代码的开头运行一个复杂的SQL查询，并在结尾使用一个不同的参数重复该查询-第二个查询的运行速度会更快吗? (对于每个页面加载，我们都使用一个连接).缓存查询是否有限制，所以最好立即重复查询?

If I run a complex SQL query at the beginning of my code and repeat it with one different parameter at the end - will the second query run faster? (We are using a single connection for each page load). Is there a limit on cached queries, so I better repeat my queries right away?

如何使用完全相同的参数(重新加载页面或2个用户)两次执行整个脚本?

What about executing the entire script twice with the exact same parameters (reload the page or 2 users)?

推荐答案

已准备好的查询将提供给SQL Server，该查询将对其进行分析并可能已经准备了执行计划.然后，您基本上会获得这些已分配资源的ID，并且只需填写语句中的空白即可执行此准备好的语句.您可以随意运行此语句，并且数据库不必重复进行解析和执行计划，这可能会提高速度.

A prepared query is given to the SQL server, which parses it and possibly already prepares an execution plan. You're then basically given an id for these allocated resources and can execute this prepared statement by just filling in the blanks in the statement. You can run this statement as often as you like and the database will not have to repeat the parsing and execution planning, which may bring a speed improvement.

只要您不丢弃该语句，就不会为该语句保持准备状态"而苦苦地超时.它不是缓存，而是SQL Server上的已分配资源.至少只要您的数据库驱动程序在SQL API中使用本机准备的语句即可.例如，除非您将PDO::ATTR_EMULATE_PREPARES设置为false，否则PDO默认情况下不这样做.

As long as you do not throw away the statement, there's no hard timeout for how long the statement will "stay prepared". It's not a cache, it's an allocated resource on the SQL server. At least as long as your database driver uses native prepared statements in the SQL API. PDO for example does not do so by default, unless you set PDO::ATTR_EMULATE_PREPARES to false.

尽管在脚本执行结束时，所有这些资源将始终被释放，它们不会在不同的页面加载中持久存在.除此之外，无论客户端脚本如何，SQL Server都可能会或可能不会缓存一段时间，并保留查询结果.

At the end of the script execution though, all those resources will always be deallocated, they do not persist across different page loads. Beyond that, the SQL server may or may not cache the query and its results for some time regardless of the client script.

这篇关于PHP PDO:准备好的mysql查询要缓存多长时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！