I am in the process of going over my queries, and I have been reading articles about how you should use SQL_NO_CACHE in SELECT queries. 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.
我正在检查查询，并且一直在阅读关于如何在SELECT查询中使用SQL_NO_CACHE的文章。这让我很困惑，因为每一篇文章最后都有不同的结论来说明什么时候使用它。我读过的一个博客说，如果你有相同的查询，你应该使用它，而且是独一无二的。在另一个博客上，我读到，当你必须提取从未改变的信息时，你应该使用它。
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.您在登录检查过程中选择一个userID，查询仅在登录检查过程中运行。
-
You select some data from table
ain order to update a field in tableb, should you useSQL_NO_CACHEon the select for tablea?为了更新表b中的字段，应该在表a的select上使用SQL_NO_CACHE吗?
Thank you.
谢谢你！
4 个解决方案
#1
8
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关键字之前和之后加上空格字符;诸如newline之类的非空间会导致服务器检查查询缓存，以查看结果是否已经被缓存。
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
NO_CACHE据我看来可以使用如果启用了“缓存”和数据库中的数据动态更新,即数据库数据缓存不能依赖,如:存储用户密码散列我们不能依赖于缓存由于频繁变化的数据的可能性
Updates of useful scenarios
更新的有用的场景
1) force not to use cache for testing speed of query
1)强制不使用缓存来测试查询速度
#2
4
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.
在您的usecases中，我没有发现不使用SQL_NO_CACHE查询缓存的真正目的。使用此选项有一些原因，如分析或避免将大数据写入有限的查询缓存中，但我认为这里不是这样的。
#3
3
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 lots of 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?
为了更新表b中的字段，应该在表a的select上使用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).
不。当基础数据被修改时，缓存将自动失效(这正是简单查询的大部分成本来自于此)。
#4
1
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
设置会话query_cache_type = OFF
#1
8
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关键字之前和之后加上空格字符;诸如newline之类的非空间会导致服务器检查查询缓存，以查看结果是否已经被缓存。
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
NO_CACHE据我看来可以使用如果启用了“缓存”和数据库中的数据动态更新,即数据库数据缓存不能依赖,如:存储用户密码散列我们不能依赖于缓存由于频繁变化的数据的可能性
Updates of useful scenarios
更新的有用的场景
1) force not to use cache for testing speed of query
1)强制不使用缓存来测试查询速度
#2
4
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.
在您的usecases中，我没有发现不使用SQL_NO_CACHE查询缓存的真正目的。使用此选项有一些原因，如分析或避免将大数据写入有限的查询缓存中，但我认为这里不是这样的。
#3
3
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 lots of 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?
为了更新表b中的字段，应该在表a的select上使用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).
不。当基础数据被修改时，缓存将自动失效(这正是简单查询的大部分成本来自于此)。
#4
1
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
设置会话query_cache_type = OFF