MySQL 的慢查询日志中的“SELECT /*!N SQL_NO_CACHE */ * FROM `mytable`”是什么意思？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8282788/
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
What does "SELECT /*!N SQL_NO_CACHE */ * FROM `mytable`" mean in in MySQL's slow query log?
提问by Tom
I've just turned on slow query loggingon my MySQL database, adding the following to /etc/mysql/my.cnf:
我刚刚在我的 MySQL 数据库上打开了慢查询日志，将以下内容添加到/etc/mysql/my.cnf：
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
When I run mysqldumpslow, it outputs the following:
当我运行mysqldumpslow 时，它输出以下内容：
Reading mysql slow query log from mysql-slow.log
Count: 1 Time=199.23s (199s) Lock=0.00s (0s) Rows=32513.0 (32513), ...
SELECT /*!N SQL_NO_CACHE */ * FROM `mytable`
...
Looking at the original mysql-slow.log, the full query was:
查看原文mysql-slow.log，完整的查询是：
SELECT /*!40001 SQL_NO_CACHE */ * FROM `mytable`;
So mysqldumpslowjust replaced the number with N(to assist aggregating similar queries.)
因此，mysqldumpslow只需将数字替换为N（以帮助聚合类似的查询。）
So, the question is, where does that query come from and what does the /*!40001 SQL_NO_CACHE */bit mean?
所以，问题是，这个查询来自哪里，这个/*!40001 SQL_NO_CACHE */位是什么意思？
As best I can tell, it's probably from a mysqldumpcommand that was doing a backup (hence not wanting cached data), does that seem right? And if so, since it only read 32,000 rows, why did it take 199s?
据我所知，它可能来自mysqldump正在执行备份的命令（因此不需要缓存数据），这看起来对吗？如果是这样，既然它只读取了 32,000 行，为什么需要 199 行？
There are a bunch more similar queries on other tables taking 100s, 50s, down to a more reasonable 3s, most having about 10-20,000 rows, the biggest with 450,000 rows.
在其他表上有更多类似的查询需要 100 秒、50 秒，再到更合理的 3 秒，大多数有大约 10-20,000 行，最大的有 450,000 行。
采纳答案by Andrew
The query is probably 'slow' because the client (your backup system) is having to read every row in your table; which is taking apparently 199 seconds.
查询可能很“慢”，因为客户端（您的备份系统）必须读取表中的每一行；这显然需要 199 秒。
Note that if you did something like:
请注意，如果您执行了以下操作：
SELECT * FROM table LIMIT 100;
// read 50 rows
// sleep for 5 minutes
// read last 50 rows
The above query would appear in the slow log, because from when it first started, to when it could finish (by sending you the last requested row) it took 5 minutes.
上面的查询将出现在慢日志中，因为从它第一次启动到它可以完成（通过向您发送最后请求的行）花费了 5 分钟。
回答by Brian Fisher
The /*!40001 SQL_NO_CACHE */means that in versions of mysql >= 4.0.1 execute SELECT SQL_NO_CACHE * FROM mytableand in earlier versions execute the command without the SQL_NO_CACHE.
这/*!40001 SQL_NO_CACHE */意味着在 mysql >= 4.0.1 版本中执行SELECT SQL_NO_CACHE * FROM mytable和在早期版本中执行没有 SQL_NO_CACHE 的命令。
Also mysqldump does use the /*!40001 SQL_NO_CACHE */syntax.
mysqldump 也使用/*!40001 SQL_NO_CACHE */语法。
I'm not sure why your queries would be so slow.
我不知道为什么你的查询会这么慢。
回答by Wagner Bianchi
A matter of tuning may be considered by you to make this query run faster than it, btw, SQL_NO_CACHE means that this query will run without be eligible to be stored into query cache. For example, you can use this HINT, SQL_NO_CACHE, to avoid this query to be cached so as to test that execution time.
您可能会考虑调整问题以使此查询运行得比它快，顺便说一句，SQL_NO_CACHE 意味着此查询将运行而没有资格存储到查询缓存中。例如，您可以使用此 HINT SQL_NO_CACHE 来避免缓存此查询以测试该执行时间。
Check it out: http://dev.mysql.com/doc/refman/5.0/en/query-cache-in-select.html
检查一下：http: //dev.mysql.com/doc/refman/5.0/en/query-cache-in-select.html
More HINTS to use into queries: http://www.petefreitag.com/item/613.cfm
更多用于查询的提示：http: //www.petefreitag.com/item/613.cfm
Cheers, WB
干杯，WB
回答by Sumsun
From My Work, I also faced the same issue Then I tried in two ways to solve it.
从我的工作中，我也遇到了同样的问题然后我尝试了两种方法来解决它。
Firstly, I have increase the mysql_query_cachesize and increase the space in local Machine.
首先，我增加了mysql_query_cache本地机器的大小并增加了空间。
Secondly, I have checked the remote server mysql_query_cachesize and increase the temp directory space.
其次，我检查了远程服务器的mysql_query_cache大小并增加了临时目录空间。
Because, What I got from research that this system is working in both ways and giving us sense that we have to increase the space in the specific place.
因为，我从研究中得到的结果是，这个系统以两种方式工作，让我们感觉到我们必须增加特定地方的空间。
So, Be specific which mysqldump you are using and check out the version also for both system. As different version also behave slight different.
因此，请具体说明mysqldum您正在使用哪个p 并检查两个系统的版本。由于不同版本的行为也略有不同。
