即使使用SQL_NO_CACHE，MySQL查询第二次运行也必须更快 [英] Mysql query run twice is must faster the second time even with SQL_NO_CACHE

问题描述

我正在尝试分析两个执行相同操作的不同查询，以找出哪个更快.为了进行测试，我将SQL_NO_CACHE放入两个查询中，以防止查询缓存弄乱时间.

I am trying to profile two different queries that do the same thing to find which one is faster. For testing, I have put SQL_NO_CACHE into both queries to prevent the query cache from messing up the timing.

查询A始终为50毫秒. 查询B第一次运行是100毫秒，如果我不久之后再次运行它，则是10毫秒.

Query A is consistently 50ms. Query B is 100ms the first time it is run and 10ms if I run it a second time shortly after.

为什么第二次查询B更快?查询缓存不应加快查询速度.可能是查询B的第一次运行将磁盘中的数据加载到内存中，从而使第二个查询在内存中运行并且速度更快?有办法测试吗?我尝试通过运行查询B之前从表中执行select *来测试自己，但是它仍然表现出相同的行为. SQL_NO_CACHE是否可能无法工作以禁用查询缓存?

Why is Query B faster the second time? The query cache should not be speeding up the queries. Could it be that the first run of query B loads the data from disk into memory so that the second query is running in memory and faster? Is there a way to test this? I tried to test this myself by doing select * from the table before I ran Query B, but it still exhibited the same behavior. Is SQL_NO_CACHE perhaps not working to disable the query cache?

查询B看起来像这样: SELECT SQL_NO_CACHE foo，bar FROM table1 LEFT JOIN table2 ON table1.foo = table2.foo WHERE bar = 1

Query B looks something like this: SELECT SQL_NO_CACHE foo,bar FROM table1 LEFT JOIN table2 ON table1.foo=table2.foo WHERE bar=1

推荐答案

是的，取决于您使用的存储引擎，是的，很有可能是从数据缓存而不是查询缓存加载的.

Depending on the storage engine you're using, yes it is most probably being loaded from a data cache and not a query cache.

MyISAM不为数据提供存储引擎级别的缓存，而仅缓存索引.但是，操作系统通常会从其自己的缓存中提供数据，这很可能会加快查询的执行速度.

MyISAM provides no storage engine level caching for data, and only caches indexes. However, the operating system often serves up data from its own caches which may well be speeding up your query execution.

您可以尝试在实际场景中对查询进行基准测试，只需在每次执行该查询(以及执行时间)时将该特定查询记录到数据库中即可.

You can try benchmarking the query in a real scenario, just log that specific query to the database every time its executed (along with its execution time).

这篇关于即使使用SQL_NO_CACHE，MySQL查询第二次运行也必须更快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！