当您只执行一项选择时,使用准备好的 Select 语句是否更好?

发布于 2024-07-15 07:02:17 字数 250 浏览 8 评论 0原文

我目前正在使用 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(6

为你拒绝所有暧昧 2024-07-22 07:02:17

MySQLPerformanceBlog.com 在一篇关于“准备语句。” 彼得·扎伊采夫写道:

我做了一个简单的基准测试(使用
SysBench)简单地查看性能
查询(单行点选择)使用
标准声明、准备声明
并从查询缓存中提供服务。
准备好的报表给出 2290
每秒查询次数显着
优于标准2000
声明,但仍远低于
当结果为 4470 次查询/秒
从查询缓存提供服务。

这似乎表明,使用准备好的语句的“开销”是它们比使用直接查询执行快 14.5%,至少在这个简单的测试中是这样。 随着更复杂的查询或更大的结果集,相对差异可能会减小。

考虑到到服务器的两次往返和其他因素,准备好的查询会更快,这似乎违反直觉。 Peter 的基准缺乏细节。 无论如何,您应该运行自己的测试,因为您运行的查询类型以及您的环境和硬件绝对是重要因素。

至于查询缓存,过去确实是准备好的语句与缓存查询结果不兼容,但这种情况已经改变了。 请参阅“查询缓存如何运行” MySQL 文档:

MySQL 5.1.17之前,准备
语句不使用查询缓存。
从5.1.17开始,准备
语句使用下面的查询缓存
某些条件有所不同
取决于制备方法:...

文档继续描述这些条件。 去读吧。

我确实建议对 SELECT 查询使用准备好的语句。 如果您始终如一地这样做,则在将变量插入 SQL 语句时引用变量可能会很有效。 但即使引用也可能存在一些微妙的安全漏洞,例如使用多字节字符集(请参阅 MySQL bug #8378)。 在这些情况下,以安全的方式使用准备好的查询会更容易。

MySQLPerformanceBlog.com did some benchmarks in an article about "Prepared Statements." Peter Zaitsev wrote:

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.

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:

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.

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.

私藏温柔 2024-07-22 07:02:17

是的,使用准备好的语句。 我严重怀疑您会遇到性能问题,准备好的语句的运行速度比常规文字查询慢得多。 然而,在 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.

芸娘子的小脾气 2024-07-22 07:02:17

这是我的理解,经以下讨论证实:此处

普通查询被视为单个
字符串,已解析,已执行,以及
回。 故事结局。 一个准备好的
声明被视为模板
字符串,解析并缓存。 那么它
几乎有变量传入其中
就像函数调用一样。

缓存查询一次往往会花费
不仅仅是执行它
直的。 节省下来的钱稍后就会出现
当你跳过编译时调用
步。 您可以根据重复查询保存
编译量。

因此,简而言之,在 MySQL 上,如果您执行一次查询,准备它只会增加不必要的额外处理量。

This is my understanding, as confirmed by discussion from: here

A normal query is taken as a single
string, parsed, executed, and
returned. End of story. A prepared
statement is taken as a template
string, parsed, and cached. It then
has variables passed into it, almost
like a function call.

Caching the query once tends to cost a
little bit more than just executing it
straight. The savings come in later
calls, when you skip the compilation
step. You save per repeated query the
amount of the compilation.

So, in short, on MySQL, if you're executing a query once, preparing it just adds a needless extra amount of processing.

心碎无痕… 2024-07-22 07:02:17

准备好的陈述通常被认为是更好的做法。

我建议阅读关于准备好的语句的 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.

楠木可依 2024-07-22 07:02:17

您是否只在应用程序生命周期中执行“一次”选择,或者每次调用该函数“一次”?

因为如果是后者,无论如何您仍然应该从准备好的语句中的缓存中受益。

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.

浮世清欢 2024-07-22 07:02:17

只是提醒一下 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).

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文