何时使用SQL_NO_CACHE [英] When to use SQL_NO_CACHE
问题描述
我正在检查我的查询，并且我一直在阅读有关如何在SELECT查询中使用SQL_NO_CACHE的文章.这让我感到困惑，因为最后的每篇文章都对何时使用它有不同的结论.我读过的一个博客说，如果您有相同的查询并且是唯一的，则应该使用此博客.在另一个博客上，我读到必须提取永远不变的信息时应该使用它.
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.
有人可以解释何时使用此方法是一种好习惯吗?我知道有人问过它，但是阅读很多文章并没有帮助，尤其是当人们说在不同情况下使用此方法时.我做了一些理论上的假设，有人可以告诉我使用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.
-
说一个网站存储其配置(即网站名称，网站描述，关键字)，并在每个页面上发出查询请求以提取每个页面所需的此信息.
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.
您在登录检查期间选择userID，查询仅在登录检查过程中运行.
You select a userID during a log in check, the query only runs during the log in check process.
您要从表a中选择一些数据以更新表b中的字段，是否应在表a的选择上使用SQL_NO_CACHE?
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?
谢谢.
推荐答案
SQL_NO_CACHE
只需在SELECT语句的SELECT部分之后和字段列表之前添加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 * 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
这在对查询进行基准测试时特别有用；如果启用了查询缓存，尽管第一个查询可能要花一些时间，但第二个和后续查询几乎是即时的.使用SQL_NO_CACHE可以确保不使用查询缓存，并且可以安全地比较结果时间. SQL_NO_CACHE提示针对特定查询关闭MySQL的内置查询缓存机制.通过对高度动态的查询(例如关键字搜索或仅每晚运行的报告)使用此提示，可以帮助MySQL提高查询缓存的效率. 确保查询缓存已打开，否则不需要此命令.
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_CACHE和SQL_NO_CACHE?
SQL_CACHE和SQL_NO_CACHE选项影响查询结果在查询缓存中的缓存. SQL_CACHE告诉MySQL将结果存储在查询缓存中(如果它是可缓存的)并且query_cache_type系统变量的值为2或DEMAND.使用SQL_NO_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.)
如果启用了'CACHE'并且数据库中的数据是动态更新的，则可以使用NO_CACHE，即不能依赖db数据缓存，例如:存储用户密码哈希，我们不能依赖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
有用情况的更新
1)强制不使用缓存来测试查询速度
1) force not to use cache for testing speed of query
这篇关于何时使用SQL_NO_CACHE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
