mysql pdo对象 缓存,PHP PDO：准备好的mysql查询要缓存多长时间？

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?

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?

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

解决方案

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.

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.

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.