MySQL - 强制不使用缓存来测试查询速度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/181894/
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
MySQL - force not to use cache for testing speed of query
提问by Jarod Elliott
I'm testing the speed of some queries in MySQL. The database is caching these queries making it difficult for me to get reliable results when testing how fast these queries are.
我正在测试 MySQL 中某些查询的速度。数据库正在缓存这些查询，这使我在测试这些查询的速度时很难获得可靠的结果。
Is there a way to disable caching for a query?
有没有办法禁用查询的缓存？
System: MySQL 4 on Linux webhosting, I have access to PHPMyAdmin.
系统：Linux 虚拟主机上的 MySQL 4，我可以访问 PHPMyAdmin。
Thanks
谢谢
回答by Jarod Elliott
Try using the SQL_NO_CACHE(MySQL 5.7) option in your query. (MySQL 5.6 users click HERE)
尝试在查询中使用SQL_NO_CACHE(MySQL 5.7) 选项。（MySQL 5.6 用户点击这里）
eg.
例如。
SELECT SQL_NO_CACHE * FROM TABLE
This will stop MySQL caching the results, however be aware that other OS and disk caches may also impact performance. These are harder to get around.
这将停止 MySQL 缓存结果，但请注意其他操作系统和磁盘缓存也可能影响性能。这些更难解决。
回答by John Carter
Another alternative that only affects the current connection:
另一种仅影响当前连接的替代方法：
SET SESSION query_cache_type=0;
回答by mediobit
Any reference to current date/time will disable the query cache for that selection:
对当前日期/时间的任何引用都将禁用该选择的查询缓存：
SELECT *,NOW() FROM TABLE
See "Prerequisites and Notes for MySQL Query Cache Use" @ http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html
请参阅“MySQL 查询缓存使用的先决条件和注意事项”@http: //dev.mysql.com/tech-resources/articles/mysql-query-cache.html
回答by barbushin
There is also configuration option: query_cache_size=0
还有配置选项：query_cache_size=0
To disable the query cache at server startup, set the query_cache_size system variable to 0. By disabling the query cache code, there is no noticeable overhead. If you build MySQL from source, query cache capabilities can be excluded from the server entirely by invoking configure with the --without-query-cache option.
要在服务器启动时禁用查询缓存，请将 query_cache_size 系统变量设置为 0。通过禁用查询缓存代码，没有明显的开销。如果您从源代码构建 MySQL，则可以通过使用 --without-query-cache 选项调用 configure 来完全从服务器中排除查询缓存功能。
回答by djt
You can also run the follow command to reset the query cache.
您还可以运行以下命令来重置查询缓存。
RESET QUERY CACHE
回答by wbharding
One problem with the
一个问题
SELECT SQL_NO_CACHE * FROM TABLE
method is that it seems to only prevent the result of your query from being cached. However, if you're querying a database that is actively being used with the query you want to test, then other clients may cache your query, affecting your results. I am continuing to research ways around this, will edit this post if I figure one out.
方法是它似乎只会阻止您的查询结果被缓存。但是，如果您查询的数据库正与您要测试的查询一起使用，则其他客户端可能会缓存您的查询，从而影响您的结果。我正在继续研究解决这个问题的方法，如果我想出一个办法，我会编辑这篇文章。
回答by Sergio Costa
I'd Use the following:
我会使用以下内容：
SHOW VARIABLES LIKE 'query_cache_type';
SET SESSION query_cache_type = OFF;
SHOW VARIABLES LIKE 'query_cache_type';
回答by newtover
Using a user-defined variable within a query makes the query resuts uncacheable. I found it a much better indicator than using SQL_NO_CACHE. But you should put the variable in a place where the variable setting would not seriously affect the performance:
在查询中使用用户定义的变量会使查询结果无法缓存。我发现它比使用SQL_NO_CACHE. 但是你应该把变量放在变量设置不会严重影响性能的地方：
SELECT t.*
FROM thetable t, (SELECT @a:=NULL) as init;
回答by Jinesh
If you want to disable the Query cache set the 'query_cache_size' to 0 in your mysql configuration file . If its set 0 mysql wont use the query cache.
如果要禁用查询缓存，请在 mysql 配置文件中将 'query_cache_size' 设置为 0。如果它的 set 0 mysql 不会使用查询缓存。
