仅执行一次选择时，使用准备好的Select语句会更好吗? [英] Is it better to use a prepared Select statement when you are only doing one select?
问题描述
我目前正在使用PDO在PHP中编写CRUD类.
I am currently writing a CRUD class in PHP using PDO.
我喜欢预准备语句提供的安全性，但是我听说它们还阻止了像mysql这样的数据库使用queryCache.
I like the security that prepared statements provide, but I have heard that they also prevent databases like mysql from using the queryCache.
一次只进行一次选择时，使用准备好的Select语句是否更好?还是$ pdo-> quote()足以满足安全性的观点(或具有缓存等其他优势?).
Is it better to use a prepared Select statement when you are only doing one select at a time? or would just $pdo->quote() suffice the security standpoint (or have any other advantages like caching?).
我所有的更新，删除和插入操作都已经使用准备好的语句完成了.我只是对选择感到好奇.
All my update, delete and inserts are done using prepared statements already. I am just curious about the selects.
推荐答案
MySQLPerformanceBlog.com did some benchmarks in an article about "Prepared Statements." Peter Zaitsev wrote:
我做了一个简单的基准测试(使用 SysBench)看性能简单 查询(单行点选择)使用 标准陈述，准备陈述 并将其从查询缓存中投放. 准备好的语句给出2290 查询/秒，这是很明显的 优于2000标准 陈述，但仍远低于 结果为4470次/秒 从查询缓存中投放.
I’ve done a simple benchmark (using SysBench) to see performance of simple query (single row point select) using standard statement, prepared statement and have it served from query cache. Prepared statements give 2290 queries/sec which is significantly better than 2000 with standard statements but it is still well below 4470 queries/sec when results are served from query cache.
这似乎表明，使用准备好的语句的开销"在于，与使用直接查询执行相比，使用准备好的语句的时间快 14.5％，至少在此简单测试中如此.如果查询更为复杂或结果集更大，则相对差异可能会减小.
This seems to say that the "overhead" of using prepared statements is that they are 14.5% faster than using a straight query execution, at least in this simple test. The relative difference probably diminishes with a more complex query or a larger result set.
考虑到与服务器的双向往返和其他因素，准备好的查询会更快，这似乎是违反直觉的.彼得的基准缺乏细节.无论如何，您应该运行自己的测试，因为运行的查询的类型以及环境和硬件绝对是重要的因素.
It seems counter-intuitive that prepared queries would be faster, given the double round-trip to the server and other factors. Peter's benchmark lacks details. Anyway, you should run your own tests, because the type of query you run, and your environment and hardware, are definitely important factors.
对于查询缓存，过去的确有事实，即准备好的语句与缓存查询结果不兼容，但是这已经改变了.请参阅中的"查询缓存的运行方式". MySQL文档:
As for Query Cache, it was true in the past that prepared statements were incompatible with caching query results, but this was changed. See "How the Query Cache Operates" in the MySQL documentation:
在MySQL 5.1.17之前，已准备好 语句不使用查询缓存. 从5.1.17开始，准备 语句使用下的查询缓存 某些条件有所不同 取决于制备方法:...
Before MySQL 5.1.17, prepared statements do not use the query cache. Beginning with 5.1.17, prepared statements use the query cache under certain conditions, which differ depending on the preparation method: ...
文档将继续描述这些情况.去读吧.
The documentation goes on to describe these conditions. Go read it.
我建议对SELECT查询使用准备好的语句.如果始终执行变量，则在将变量插入SQL语句时对变量进行报价可能是有效的.但即使是报价也可能会有一些细微的安全漏洞，例如带有多字节字符集的代码(请参见MySQL错误# 8378 ).在这种情况下，以安全的方式使用准备好的查询会更容易.
I do recommend using prepared statements for SELECT queries. Quoting variables as you interpolate them into SQL statements can be effective if you do it consistently. But even quoting may have some subtle security vulnerabilities, e.g. with multi-byte character sets (see MySQL bug #8378). It's easier to use prepared queries in a secure way in these cases.
这篇关于仅执行一次选择时，使用准备好的Select语句会更好吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
