php 何时使用 SQL_NO_CACHE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18777975/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
When to use SQL_NO_CACHE
提问by
I am in the process of going over my queries, and I have been reading articles about how you should use SQL_NO_CACHEin SELECTqueries. This has confused me because every article in the end has a different conclusion on when to use this. One blog I have read said you should use this if you have identical queries, and are unique. On another blog I read that you should use it when you have to extract information that never changes.
我正在检查我的查询，并且我一直在阅读有关如何SQL_NO_CACHE在SELECT查询中使用的文章。这让我很困惑，因为最后每篇文章对何时使用它都有不同的结论。我读过的一篇博客说，如果您有相同的查询并且是唯一的，则应该使用它。在另一个博客上，我读到当您必须提取永不改变的信息时应该使用它。
Can someone explain to when is a good practice to use this? I know it's been asked before but reading a lot of articles didn't help, especially when people are saying to use this method in different situations. I made some theoretical situations, can someone tell me if it's beneficial to use SQL_NO_CACHE. Thank you and I do apologize for a repeated question. I'm just really confused.
有人可以解释一下什么时候使用它的好习惯吗？我知道之前有人问过这个问题，但阅读了很多文章并没有帮助，尤其是当人们说要在不同情况下使用这种方法时。我做了一些理论情况，有人可以告诉我使用SQL_NO_CACHE. 谢谢你，我为重复的问题道歉。我真的很困惑。
Say a website store its configuration (i.e. site name, site description, keywords), and on every page a query request is made to extract this information as it's required on every page.
You select a
userIDduring a log in check, the query only runs during the log in check process.You select some data from table
ain order to update a field in tableb, should you useSQL_NO_CACHEon the select for tablea?
假设一个网站存储其配置（即站点名称、站点描述、关键字），并且在每个页面上都会发出查询请求以提取每个页面上需要的信息。
您
userID在登录检查期间选择一个，查询仅在登录检查过程中运行。您从表
a中选择一些数据以更新表中的字段b，您应该SQL_NO_CACHE在选择表上使用a吗？
Thank you.
谢谢你。
回答by internals-in
SQL_NO_CACHE
SQL_NO_CACHE
Simply add SQL_NO_CACHE after the SELECT part of the SELECT statement and before the fields list. The first query below will use the query cache if it is enabled and the query is cached:
只需在 SELECT 语句的 SELECT 部分之后和字段列表之前添加 SQL_NO_CACHE。如果启用了查询缓存并且查询被缓存，下面的第一个查询将使用查询缓存：
SELECT * FROM table WHERE search= 'keyword'; //lets take 1ms
The second query below will not use the query cache:
下面的第二个查询不会使用查询缓存：
SELECT SQL_NO_CACHE * FROM table WHERE search= 'keyword'; //lets take ~0.2ms at 2nd time
This is particularly useful when benchmarking a query; if the query cache is enabled although the first query may take some time the second and subsequent queries are almost instant. With the use of SQL_NO_CACHE you can be assured the query cache is not used and can safely compare result times. The SQL_NO_CACHE hint turns off MySQL's builtin query caching mechanism for a particular query. You can help MySQL make the query cache more efficent by using this hint on queries that are highly dynamic (such as a keyword search, or a report that only runs nightly). Make sure query caching is turned on otherwise there is no need for this command.
这在对查询进行基准测试时特别有用；如果启用了查询缓存，尽管第一个查询可能需要一些时间，第二个和后续查询几乎是即时的。通过使用 SQL_NO_CACHE，您可以确保不使用查询缓存，并且可以安全地比较结果时间。SQL_NO_CACHE 提示为特定查询关闭 MySQL 的内置查询缓存机制。通过对高度动态的查询（例如关键字搜索或仅每晚运行的报告）使用此提示，您可以帮助 MySQL 提高查询缓存的效率。确保查询缓存已打开，否则不需要此命令。
what SQL_CACHE and SQL_NO_CACHE ?
什么 SQL_CACHE 和 SQL_NO_CACHE ？
The SQL_CACHE and SQL_NO_CACHE options affect caching of query results in the query cache. SQL_CACHE tells MySQL to store the result in the query cache if it is cacheable and the value of the query_cache_type system variable is 2 or DEMAND. With SQL_NO_CACHE, the server does not use the query cache. It neither checks the query cache to see whether the result is already cached, nor does it cache the query result. (Due to a limitation in the parser, a space character must precede and follow the SQL_NO_CACHE keyword; a nonspace such as a newline causes the server to check the query cache to see whether the result is already cached.)
SQL_CACHE 和 SQL_NO_CACHE 选项影响查询缓存中查询结果的缓存。SQL_CACHE 告诉 MySQL 将结果存储在查询缓存中，如果它是可缓存的，并且 query_cache_type 系统变量的值为 2 或 DEMAND。使用 SQL_NO_CACHE，服务器不使用查询缓存。它既不检查查询缓存以查看结果是否已经缓存，也不缓存查询结果。（由于解析器的限制，空格字符必须在 SQL_NO_CACHE 关键字之前和之后；非空格如换行符会导致服务器检查查询缓存以查看结果是否已被缓存。）
NO_CACHE according to my opinion can be used if 'CACHE' is enabled and the data in the db is dynamically updated , ie db data cache can't be relied upon , eg: storing user password hash we cant rely on CACHE since there is frequent possibility of a change of data
根据我的意见，如果启用了“CACHE”并且数据库中的数据是动态更新的，则可以使用 NO_CACHE，即不能依赖数据库数据缓存，例如：存储用户密码哈希我们不能依赖 CACHE，因为有频繁数据更改的可能性
Updates of useful scenarios
有用场景的更新
1) force not to use cache for testing speed of query
1) 强制不使用缓存来测试查询速度
回答by symcbean
The most obvious time I would choose to suppress caching is when I don't want the query results in the cache (obviously?).
我选择抑制缓存的最明显的时间是我不希望缓存中的查询结果（显然？）。
If I will only be running a query once then there's no point in displacing data currently in the cache to make room for data which will never be retreived from the cache.
如果我只运行一次查询，那么替换当前缓存中的数据来为永远不会从缓存中检索的数据腾出空间是没有意义的。
The second scenario is where I want to run a low priority query - e.g. generating a report or a partial backup - where the performance of my query is not important, but it is important that it is minimaly disruptive for the other stuff happening in the database (there are some issues around contention for cache access).
第二种情况是我想运行低优先级查询 - 例如生成报告或部分备份 - 我的查询性能并不重要，但重要的是它对数据库中发生的其他事情的破坏最小（关于缓存访问争用存在一些问题）。
A third scenario is when there are lotsof simple, single table select queries returning very small datasets (e.g. using trivial ORM). In this scenario, using query caching will likely be slower than bypassing it altogether - the DBMS will spend more time managing the query cache than reading the data from buffer pool / system cache.
第三种情况是当有很多简单的单表选择查询返回非常小的数据集时（例如使用平凡的 ORM）。在这种情况下，使用查询缓存可能比完全绕过它更慢——与从缓冲池/系统缓存读取数据相比，DBMS 将花费更多时间管理查询缓存。
As Tot says, the cache will skew any profiling / benchmarking - but the query cache is not the only place where the data is bufferred/cached.
正如 Tot 所说，缓存会影响任何分析/基准测试——但查询缓存并不是唯一缓冲/缓存数据的地方。
You select some data from table a in order to update a field in table b, should you use SQL_NO_CACHE on the select for table a?
您从表 a 中选择一些数据以更新表 b 中的字段，是否应该在表 a 的选择上使用 SQL_NO_CACHE？
No. The cache is automatically invalidated when the underlying data is modified (which is where much of the cost for simple queries comes from).
不会。当底层数据被修改时，缓存会自动失效（这是简单查询的大部分成本的来源）。
回答by Bjoern
In order to answer you question you first have to understand how the MySQL query cache works. A very good article about this can be found here. On of the key aspects is that the cache is synchronized with the data:
为了回答您的问题，您首先必须了解 MySQL 查询缓存的工作原理。可以在这里找到一篇关于此的非常好的文章。一个关键方面是缓存与数据同步：
The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.
查询缓存不会返回陈旧数据。当表被修改时，查询缓存中的任何相关条目都会被刷新。
In your usecases I don't find any real purpose for not using the query cache with SQL_NO_CACHE. There are reasons like profiling or avoiding writing big data into a limited query cache for using this option, but I don't see this to be the case here.
在您的用例中，我没有发现不将查询缓存与SQL_NO_CACHE. 使用此选项有一些原因，例如分析或避免将大数据写入有限的查询缓存，但我不认为这里是这种情况。
回答by Toto
The only time I used it was when profiling queries (EXPLAIN extended...).
我唯一一次使用它是在分析查询时 ( EXPLAIN extended...)。
When running a query several times (a very import step to "warm up" the server), it will be cached therefore the profiler will output wrong results.
当多次运行查询时（“预热”服务器的一个非常重要的步骤），它将被缓存，因此分析器将输出错误的结果。
Depending of the situation I also use:
根据情况我也使用：
SET SESSION query_cache_type = OFF
SET SESSION query_cache_type = OFF
