blocks|key|1724524|text|尝试在查询中使用SQL_NO_CACHE+(MySQL+5.7)选项。(MySQL+5.6用户点击HERE+)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1724525|例如：|1724526|SELECT+SQL_NO_CACHE+*+FROM+TABLE|code-block|syntax|javascript|1724527|这将停止MySQL缓存结果，但请注意，其他操作系统和磁盘缓存也可能影响性能。这些都很难解决。|1724528|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.7/en/query-cache-in-select.html|1|https://dev.mysql.com/doc/refman/5.6/en/query-cache-in-select.html^0|8|C|0|1D|4|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]|$A|10|B|11|1|12]]|C|$]]|$1|D|3|E|5|6|7|13|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|14|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|15|8|@]|9|@]|C|$]]|$1|M|3|-4|5|6|7|16|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]|U|$5|P|Q|R|C|$S|V]]]]

Try using the <a href="https://dev.mysql.com/doc/refman/5.7/en/query-cache-in-select.html" rel="noreferrer">SQL_NO_CACHE</a> (MySQL 5.7) option in your query.
(MySQL 5.6 users click <a href="https://dev.mysql.com/doc/refman/5.6/en/query-cache-in-select.html" rel="noreferrer">HERE</a> )

eg.

<pre><code>SELECT SQL_NO_CACHE * FROM TABLE
</code></pre>

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.

blocks|key|1724867|text|另一个只影响当前连接的替代方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1724868|SET+SESSION+query_cache_type=0;|code-block|syntax|javascript|1724869|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Another alternative that only affects the current connection:

<pre><code>SET SESSION query_cache_type=0;
</code></pre>

blocks|key|1509695|text|对当前日期/时间的任何引用都将禁用该选择的查询缓存：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1509696|SELECT+*,NOW()+FROM+TABLE|code-block|syntax|javascript|1509697|请参见“使用MySQL查询高速缓存的前提条件和注意事项”@+http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html|offset|length|1509698|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html^0|0|0|U|1V|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Any reference to current date/time will disable the query cache for that selection:

<pre><code>SELECT *,NOW() FROM TABLE
</code></pre>

See "Prerequisites and Notes for MySQL Query Cache Use" @ <a href="http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html">http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html</a>

blocks|key|1509469|text|您还可以运行以下命令来重置查询缓存。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1509470|RESET+QUERY+CACHE|code-block|syntax|javascript|1509471|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can also run the follow command to reset the query cache.

<pre><code>RESET QUERY CACHE
</code></pre>

blocks|key|1509622|text|存在的一个问题是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1509623|SELECT+SQL_NO_CACHE+*+FROM+TABLE|code-block|syntax|javascript|1509624|方法的一个特点是，它似乎只阻止缓存查询的结果。但是，如果您正在查询的数据库正在与您想要测试的查询一起使用，那么其他客户端可能会缓存您的查询，从而影响您的结果。我正在继续研究解决这个问题的方法，如果我想出一个办法，我会编辑这篇文章。|1509625|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

One problem with the 

<pre><code>SELECT SQL_NO_CACHE * FROM TABLE
</code></pre>

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.

blocks|key|1509777|text|我会用到以下几点：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1509778|SHOW+VARIABLES+LIKE+'query_cache_type';
SET+SESSION+query_cache_type+=+OFF;
SHOW+VARIABLES+LIKE+'query_cache_type';|code-block|syntax|javascript|1509779|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I'd Use the following:

<pre><code>SHOW VARIABLES LIKE 'query_cache_type';
SET SESSION query_cache_type = OFF;
SHOW VARIABLES LIKE 'query_cache_type';
</code></pre>

blocks|key|1724906|text|在查询中使用用户定义的变量会使查询结果不可缓存。我发现它是一个比使用SQL_NO_CACHE更好的指标。但您应该将变量放在变量设置不会严重影响性能的位置：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1724907|SELECT+t.*
FROM+thetable+t,+(SELECT+@a:=NULL)+as+init;|code-block|syntax|javascript|1724908|entityMap^0|Y|C|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Using a user-defined variable within a query makes the query resuts uncacheable. I found it a much better indicator than using <code>SQL_NO_CACHE</code>. But you should put the variable in a place where the variable setting would not seriously affect the performance:

<pre><code>SELECT t.*
FROM thetable t, (SELECT @a:=NULL) as init;
</code></pre>

blocks|key|1724661|text|如果您想禁用查询缓存，请在mysql配置文件中将'query_cache_size‘设置为0。如果设置为0，mysql将不使用查询缓存。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1724662|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1509839|text|虽然一些答案是好的，但有一个主要的警告。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1509840|mysql查询可能会被阻止缓存，但它不会阻止底层操作系统将磁盘访问缓存到内存中。对于某些查询来说，这可能会大大减慢速度，特别是当它们需要从旋转的磁盘中提取数据时。|1509841|因此，虽然使用上面的方法很好，但每次我也会尝试使用一组不同的数据/范围进行测试，这些数据/范围可能不会从磁盘中拉到磁盘/内存缓存中。|1509842|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Whilst some of the answers are good, there is a major caveat.
The mysql queries may be prevented from being cached, but it won't prevent your underlying O.S caching disk accesses into memory. This can be a major slowdown for some queries especially if they need to pull data from spinning disks.
So whilst it's good to use the methods above, I would also try and test with a different set of data/range each time, that's likely not been pulled from disk into disk/memory cache.

blocks|key|1509880|text|您必须更改SQL字符串。因为SQL字符串是缓存键。例如，向SQL注释添加时间戳。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1509881|PHP函数：|1509882|function+db_RunSQL($SQL,+$NoCacheMode=false)
{
$SQL+=+(($NoCacheMode)+?+'/*'.time().'*/+'+:+'')+.+$SQL;
return+mysqli_query(db_SavedConnect(),+$SQL);
}|code-block|syntax|javascript|1509883|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You must change SQL string. Because SQL string is a cache key.
For example, add a timestamp to a SQL comment.
Function for PHP:
<pre><code>function db_RunSQL($SQL, $NoCacheMode=false)
{
$SQL = (($NoCacheMode) ? '/*'.time().'*/ ' : '') . $SQL;
return mysqli_query(db_SavedConnect(), $SQL);
}
</code></pre>

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.

Is there a way to disable caching for a query?

System: MySQL 4 on Linux webhosting, I have access to PHPMyAdmin.

Thanks

MySQL - force not to use cache for testing speed of query

虚拟主机

数据库

MySQL

Linux

我正在测试MySQL中一些查询的速度。数据库正在缓存这些查询，这使得我在测试这些查询的速度时很难获得可靠的结果。有没有办法禁用查询的缓存？系统: Linux上的MySQL 4虚拟主机，我可以访问PHPMyAdmin。谢谢

问MySQL -强制不使用缓存来测试查询速度
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL -强制不使用缓存来测试查询速度EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL -强制不使用缓存来测试查询速度
EN