当您只执行一项选择时,使用准备好的 Select 语句是否更好?
我目前正在使用 PDO 在 PHP 中编写 CRUD 类。
我喜欢预准备语句提供的安全性,但我听说它们也会阻止像 mysql 这样的数据库使用 queryCache。
当您一次只执行一项选择时,使用准备好的 Select 语句是否更好? 或者仅仅 $pdo->quote() 就足以满足安全性的观点(或者有任何其他优点,例如缓存?)。
我所有的更新、删除和插入都是使用准备好的语句完成的。 我只是对选择感到好奇。
I am currently writing a CRUD class in PHP using PDO.
I like the security that prepared statements provide, but I have heard that they also prevent databases like mysql from using the queryCache.
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
MySQLPerformanceBlog.com 在一篇关于“准备语句。” 彼得·扎伊采夫写道:
这似乎表明,使用准备好的语句的“开销”是它们比使用直接查询执行快 14.5%,至少在这个简单的测试中是这样。 随着更复杂的查询或更大的结果集,相对差异可能会减小。
考虑到到服务器的两次往返和其他因素,准备好的查询会更快,这似乎违反直觉。 Peter 的基准缺乏细节。 无论如何,您应该运行自己的测试,因为您运行的查询类型以及您的环境和硬件绝对是重要因素。
至于查询缓存,过去确实是准备好的语句与缓存查询结果不兼容,但这种情况已经改变了。 请参阅“查询缓存如何运行” MySQL 文档:
文档继续描述这些条件。 去读吧。
我确实建议对
SELECT
查询使用准备好的语句。 如果您始终如一地这样做,则在将变量插入 SQL 语句时引用变量可能会很有效。 但即使引用也可能存在一些微妙的安全漏洞,例如使用多字节字符集(请参阅 MySQL bug #8378)。 在这些情况下,以安全的方式使用准备好的查询会更容易。MySQLPerformanceBlog.com did some benchmarks in an article about "Prepared Statements." Peter Zaitsev wrote:
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.
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:
The documentation goes on to describe these conditions. Go read it.
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.是的,使用准备好的语句。 我严重怀疑您会遇到性能问题,准备好的语句的运行速度比常规文字查询慢得多。 然而,在 mysql 上,你似乎是正确的。 尽管如此,我还是会选择准备好的陈述。
这是一个参考:
http://www.mysqlperformanceblog.com/2006/08/02 /mysql-prepared-statements/
不过,如果您担心缓存,您可能需要查看诸如 memcached。
Yes, use prepared statements. I seriously doubt you will run into performance problems with prepared statements running much slower than just a regular literal query. However, on mysql, you appear to be correct. I would opt for prepared statements nevertheless.
Here is one reference:
http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/
Although, if you are worried about caching, you might want to look at things like memcached.
这是我的理解,经以下讨论证实:此处
因此,简而言之,在 MySQL 上,如果您执行一次查询,准备它只会增加不必要的额外处理量。
This is my understanding, as confirmed by discussion from: here
So, in short, on MySQL, if you're executing a query once, preparing it just adds a needless extra amount of processing.
准备好的陈述通常被认为是更好的做法。
我建议阅读关于准备好的语句的 MySql 文章以及它们相对于传统普通插值字符串查询的实用性和优势。
Prepared statements are generally considered to be better practice.
I would suggest reading the MySql article on prepared statements and their practicalities and advantages over conventional plain-vanilla interpolated stringy queries.
您是否只在应用程序生命周期中执行“一次”选择,或者每次调用该函数“一次”?
因为如果是后者,无论如何您仍然应该从准备好的语句中的缓存中受益。
Are you only doing a select "once" in the application lifetime, or "once" per call to the function?
Because if the latter, you should still benefit from the caching in the prepared statement anyway.
只是提醒一下 MySQL > 5.1.17 确实使用查询缓存来准备声明。
从代码 POV 来看,我相信准备好的语句在很大程度上是可读性、可维护性等方面的最佳选择……
不使用它们的原因之一是昂贵的查询,这些查询会以一定的频率被调用。 (需要花费大量时间运行的查询,并且在查询缓存上有真正的好处)。
Just a reminder that MySQL > 5.1.17 does use the query cache for prepared statements.
From the code POV, i believe prepared statements are, for the most part, the way to go in terms of readability, maintainability, etc...
The one reason not to use them would be expensive queries that get called with some frequency. (queries that take a lot of time to run and have a real benefit on being on the query cache).