I am trying to do some benchmarking of different queries on different columns but MySQL just won't let me do it. After the first execution of a query, I can never get the same execution time for that query again. For example if the query executes in 0.062 secs the first time, I can never get the same execution time for the 2nd, 3rd etc runs. It becomes either 0 seconds or something like 0.015.
我试图对不同列上的不同查询进行一些基准测试，但MySQL不会让我这样做。在第一次执行查询后，我再也无法获得该查询的相同执行时间。例如，如果查询在第一次执行0.062秒时，我永远无法获得第二次，第三次等运行的相同执行时间。它变为0秒或类似0.015的东西。
I have read many posts on disabling and clearing MySQL query cache but none of them has been of any help for me.
我已经阅读了很多关于禁用和清除MySQL查询缓存的帖子，但是没有一个对我有任何帮助。
喜欢这个SO帖子在这里
No matter what I do, MySQL seems to insist on using cached results.
无论我做什么，MySQL似乎都坚持使用缓存结果。
I restart the MySQL Workbench then I run;
我重新启动MySQL Workbench然后运行;
set global query_cache_type=0;
set global query_cache_size=0;
flush query cache;
reset query cache;
The execution time keeps showing 0 secs.
执行时间持续显示0秒。
Only server variable that I haven't been able to change is "have_query_cache". Its value is "yes" and when I try to set it to "no" Workbench says it is read-only.
只有我无法更改的服务器变量是“have_query_cache”。它的值是“是”，当我尝试将其设置为“no”时，Workbench表示它是只读的。
I also do;
我也这样做;
set profiling=1;
run my select query
show profile for query 2;
Profiling result shows this:
分析结果显示：
'starting', '0.000077'
'checking permissions', '0.000007'
'Opening tables', '0.000016'
'init', '0.000035'
'System lock', '0.000009'
'optimizing', '0.000013'
'statistics', '0.000094'
'preparing', '0.000008'
'executing', '0.000002'
'Sending data', '0.000016'
'end', '0.000002'
'query end', '0.000003'
'closing tables', '0.000005'
'freeing items', '0.000139'
'cleaning up', '0.000009'
If I am not wrong this shows that no caches are being used right? But I stil see 0 secs. for the execution time.
如果我没有错，这表明没有使用缓存吗？但我仍然看到0秒。执行时间。
Edit: The query I am running is a SELECT query using "SQL_NO_CACHE" like so:
编辑：我正在运行的查询是使用“SQL_NO_CACHE”的SELECT查询，如下所示：
SELECT SQL_NO_CACHE col1,now() from mytable where col2="some_value"
(I added now() function to help me prevent query caching)
（我添加了now（）函数来帮助我防止查询缓存）
Edit2: I am using innoDB, MySQL 5.6.10
Edit2：我使用的是innoDB，MySQL 5.6.10
Could someone please help me cause I can't see what is going on here.
有人可以帮助我，因为我看不到这里发生了什么。
Thanks a lot!
非常感谢！
5 个解决方案
#1
9
Running the query for the first time makes InnoDB buffer pool populate with your tables' relevant blocks.
第一次运行查询使InnoDB缓冲池填充了表的相关块。
Since re-running the query requires exactly same blocks, it spares the query from the need to read them from disk when it's re-run, making it significantly faster.
由于重新运行查询需要完全相同的块，因此它会使查询从重新运行时从磁盘中读取它们的需要，从而使查询速度明显加快。
#2
20
This could be due to the cache of the data itself, and not the query cache.
这可能是由于数据本身的缓存，而不是查询缓存。
To make sure, you can disable the query cache for a single statement by adding SQL_NO_CACHE after your SELECT statement.
要确保，可以通过在SELECT语句后添加SQL_NO_CACHE来禁用单个语句的查询缓存。
Ex:
例如：
SELECT SQL_NO_CACHE field FROM table.
SELECT SQL_NO_CACHE字段FROM表。
#3
7
You can verify the difference between buffer pool page reads versus page reads that had to go to disk to get the page:
您可以验证缓冲池页面读取与必须转到磁盘以获取页面的页面读取之间的区别：
mysql> SHOW SESSION STATUS LIKE 'Innodb_buffer_pool_read%';
mysql> ...run a query...
mysql> SHOW SESSION STATUS LIKE 'Innodb_buffer_pool_read%';
Compare these values from the report, and note how much they grow:
比较报告中的这些值，并注意它们增长了多少：
+---------------------------------------+----------+
| Variable_name | Value |
+---------------------------------------+----------+
| Innodb_buffer_pool_read_requests | 10327490 |
| Innodb_buffer_pool_reads | 1133 |
+---------------------------------------+----------+
The read_requests are logical page reads, which may read from pages already in the buffer pool. If this number grows after your query, but reads doesn't grow, then your query got its result solely from the buffer pool.
read_requests是逻辑页读取，可以从缓冲池中已有的页读取。如果在查询之后此数字增长，但读取不会增长，那么您的查询仅从缓冲池获得其结果。
The reads are the number of page reads that had to go out to disk and incur the cost of I/O to copy pages into the buffer pool. If this number grows after your query, then it had to load data from disk.
读取是必须转出到磁盘的页面读取次数，并且会导致将页面复制到缓冲池的I / O成本。如果在查询后此数字增长，则必须从磁盘加载数据。
For more on the difference between the performance of memory access versus disk access, read http://everythingisdata.wordpress.com/2009/10/17/numbers-everyone-should-know/
有关内存访问性能与磁盘访问性能之间差异的更多信息，请参阅http://everythingisdata.wordpress.com/2009/10/17/numbers-everyone-should-know/
Re your comment:
你的评论：
The "Opening tables" is related to an in-memory cache of InnoDB tables, kind of like a data dictionary. Tables are opened the first time they are referenced after a restart. A given table's entry in the in-memory data dictionary stays in memory indefinitely, so that memory usage can grow pretty large if you have thousands of tables. In MySQL 5.6, they have some ne tuning variables to put a cap on that memory usage, and evict infrequently-used tables. But this whole mechanism is separate from the buffer pool, which stores pages of data and indexes.
“打开表”与InnoDB表的内存缓存有关，有点像数据字典。重新启动后第一次引用表时会打开表。内存数据字典中的给定表条目无限期地保留在内存中，因此如果您有数千个表，则内存使用量会增长很多。在MySQL 5.6中，他们有一些调整变量来限制内存使用量，并逐出不常用的表。但是这整个机制与缓冲池是分开的，缓冲池存储数据和索引的页面。
The "statistics" is also separate from the buffer pool. InnoDB maintains in-memory statistics about data and indexes, which it uses to guide the query optimizer. Statistics are refreshed automatically when you first access an InnoDB table, when you run SHOW TABLE STATUS or run certain queries against the INFORMATION_SCHEMA, when you run ANALYZE TABLE, and also when the size of the table changes significantly. InnoDB generates these statistics reading a fixed number of pages from the table at random, and this is likely to hit the disk if you have a cold buffer pool.
“统计”也与缓冲池分开。 InnoDB维护有关数据和索引的内存中统计信息，用于指导查询优化器。当您第一次访问InnoDB表，运行SHOW TABLE STATUS或对INFORMATION_SCHEMA运行某些查询，运行ANALYZE TABLE时，以及表的大小发生显着变化时，统计信息会自动刷新。 InnoDB生成这些统计信息，从表中随机读取固定数量的页面，如果你有一个冷缓冲池，这很可能会打到磁盘上。
#4
3
OK. So I have been diggin' on this innoDB buffer pool issue. Thank you @Quassnoi, @TheVedge for mentioning that. It has been very useful for the following work.
好。所以我一直在关于这个innoDB缓冲池问题。谢谢@Quassnoi，@ TheVedge提到这一点。它对以下工作非常有用。
I was trying to find a way to disable or reset innoDB buffer pool but I haven't been able to find a way other than restarting MySQL server. (To restart the server on a windows box you can either use services.msc or command prompt.)
我试图找到一种方法来禁用或重置innoDB缓冲池，但我还没有找到一种方法，而不是重新启动MySQL服务器。 （要在Windows框中重新启动服务器，可以使用services.msc或命令提示符。）
I guess all the configuration variable settings and the use of "SQL_NO_CACHE" was more than enough to avoid cache use, and also checking the profiling results I was not seeing any cache reference neither (check the original post).
我想所有配置变量设置和“SQL_NO_CACHE”的使用都足以避免缓存使用，并且还检查分析结果我没有看到任何缓存引用（检查原始帖子）。
Then I decided to check the profiling for the same query's 1st and 2nd runs after restarting MySQL server. This way I would be able to see what was going on during the query execution step by step.
然后我决定在重新启动MySQL服务器之后检查同一查询的第一次和第二次运行的分析。通过这种方式，我可以逐步查看查询执行过程中发生了什么。
1st run after server restart yielded the following steps and timings:
服务器重启后第一次运行产生以下步骤和时间：
'starting', '0.000078'
'checking permissions', '0.000005'
'Opening tables', '0.120329'
'init', '0.000107'
'System lock', '0.000012'
'optimizing', '0.000024'
'statistics', '0.065317'
'preparing', '0.000026'
'executing', '0.000003'
'Sending data', '0.000038'
'end', '0.000005'
'query end', '0.000006'
'closing tables', '0.000014'
'freeing items', '0.000463'
'cleaning up', '0.000039'
And these are the results of the 2nd run:
这些是第二轮的结果：
'starting', '0.000068'
'checking permissions', '0.000006'
'Opening tables', '0.000019'
'init', '0.000046'
'System lock', '0.000007'
'optimizing', '0.000010'
'statistics', '0.000073'
'preparing', '0.000009'
'executing', '0.000002'
'Sending data', '0.000035'
'end', '0.000003'
'query end', '0.000003'
'closing tables', '0.000006'
'freeing items', '0.000181'
'cleaning up', '0.000015'
As you can see there is a huge difference for the durations of "opening tables" and "statistics" actions between the two executions and none of the executions reference any cache.
正如您所看到的，两个执行之间的“打开表”和“统计”操作的持续时间存在巨大差异，并且没有任何执行引用任何缓存。
The two actions seem to be the most time consuming parts for the 1st execution (I believe as a part of the creation of innoDB buffer pool), and once the pool is generated these two actions not performed as heavily hence the much shorter execution time for the query after the buffer pool creation.
这两个动作似乎是第一次执行时最耗时的部分（我相信作为创建innoDB缓冲池的一部分），并且一旦生成池，这两个动作的执行时间不会那么大，因此执行时间要短得多。创建缓冲池后的查询。
When you sum up the execution times of the individual steps for 2nd run we get 0.000483 seconds and I think MySQL workbench shows this result as 0.000 secs which might also trick you to think that the second run was result of cache using but actually it seems like it is not. This is just a display precision issue for the execution durations of queries.
当你总结第二次运行的各个步骤的执行时间时，我们得到0.000483秒，我认为MySQL工作台将此结果显示为0.000秒，这也可能会让你觉得第二次运行是缓存使用的结果，但实际上看起来像不是这样。这只是查询执行持续时间的显示精度问题。
As a result what I have figured out so far is I actually have been able to disable the use of caches and it is the completion of innoDB buffer pool that speeds up the second query so much. This also means that I can find a record in 2 million rows in 0.000483 seconds.
因此，到目前为止我已经想到的是我实际上已经能够禁用缓存的使用，它是innoDB缓冲池的完成，它加速了第二个查询。这也意味着我可以在0.000483秒内找到200万行的记录。
Would you agree?
你同意吗？
#5
1
In your .ini/.cfg in [mysqld] section, add a line have_query_cache=0 # to prevent using resources from default of yes ccyy/mm/dd your initials. With your other changes, close/restart MySQL. You should NOT observe any QC activity in profile research.
在[mysqld]部分的.ini / .cfg中，添加一行has_query_cache = 0＃以防止使用默认值为ccyy / mm / dd的资源作为首字母。通过其他更改，关闭/重启MySQL。您不应该在个人资料研究中观察任何QC活动。
#1
9
Running the query for the first time makes InnoDB buffer pool populate with your tables' relevant blocks.
第一次运行查询使InnoDB缓冲池填充了表的相关块。
Since re-running the query requires exactly same blocks, it spares the query from the need to read them from disk when it's re-run, making it significantly faster.
由于重新运行查询需要完全相同的块，因此它会使查询从重新运行时从磁盘中读取它们的需要，从而使查询速度明显加快。
#2
20
This could be due to the cache of the data itself, and not the query cache.
这可能是由于数据本身的缓存，而不是查询缓存。
To make sure, you can disable the query cache for a single statement by adding SQL_NO_CACHE after your SELECT statement.
要确保，可以通过在SELECT语句后添加SQL_NO_CACHE来禁用单个语句的查询缓存。
Ex:
例如：
SELECT SQL_NO_CACHE field FROM table.
SELECT SQL_NO_CACHE字段FROM表。
#3
7
You can verify the difference between buffer pool page reads versus page reads that had to go to disk to get the page:
您可以验证缓冲池页面读取与必须转到磁盘以获取页面的页面读取之间的区别：
mysql> SHOW SESSION STATUS LIKE 'Innodb_buffer_pool_read%';
mysql> ...run a query...
mysql> SHOW SESSION STATUS LIKE 'Innodb_buffer_pool_read%';
Compare these values from the report, and note how much they grow:
比较报告中的这些值，并注意它们增长了多少：
+---------------------------------------+----------+
| Variable_name | Value |
+---------------------------------------+----------+
| Innodb_buffer_pool_read_requests | 10327490 |
| Innodb_buffer_pool_reads | 1133 |
+---------------------------------------+----------+
The read_requests are logical page reads, which may read from pages already in the buffer pool. If this number grows after your query, but reads doesn't grow, then your query got its result solely from the buffer pool.
read_requests是逻辑页读取，可以从缓冲池中已有的页读取。如果在查询之后此数字增长，但读取不会增长，那么您的查询仅从缓冲池获得其结果。
The reads are the number of page reads that had to go out to disk and incur the cost of I/O to copy pages into the buffer pool. If this number grows after your query, then it had to load data from disk.
读取是必须转出到磁盘的页面读取次数，并且会导致将页面复制到缓冲池的I / O成本。如果在查询后此数字增长，则必须从磁盘加载数据。
For more on the difference between the performance of memory access versus disk access, read http://everythingisdata.wordpress.com/2009/10/17/numbers-everyone-should-know/
有关内存访问性能与磁盘访问性能之间差异的更多信息，请参阅http://everythingisdata.wordpress.com/2009/10/17/numbers-everyone-should-know/
Re your comment:
你的评论：
The "Opening tables" is related to an in-memory cache of InnoDB tables, kind of like a data dictionary. Tables are opened the first time they are referenced after a restart. A given table's entry in the in-memory data dictionary stays in memory indefinitely, so that memory usage can grow pretty large if you have thousands of tables. In MySQL 5.6, they have some ne tuning variables to put a cap on that memory usage, and evict infrequently-used tables. But this whole mechanism is separate from the buffer pool, which stores pages of data and indexes.
“打开表”与InnoDB表的内存缓存有关，有点像数据字典。重新启动后第一次引用表时会打开表。内存数据字典中的给定表条目无限期地保留在内存中，因此如果您有数千个表，则内存使用量会增长很多。在MySQL 5.6中，他们有一些调整变量来限制内存使用量，并逐出不常用的表。但是这整个机制与缓冲池是分开的，缓冲池存储数据和索引的页面。
The "statistics" is also separate from the buffer pool. InnoDB maintains in-memory statistics about data and indexes, which it uses to guide the query optimizer. Statistics are refreshed automatically when you first access an InnoDB table, when you run SHOW TABLE STATUS or run certain queries against the INFORMATION_SCHEMA, when you run ANALYZE TABLE, and also when the size of the table changes significantly. InnoDB generates these statistics reading a fixed number of pages from the table at random, and this is likely to hit the disk if you have a cold buffer pool.
“统计”也与缓冲池分开。 InnoDB维护有关数据和索引的内存中统计信息，用于指导查询优化器。当您第一次访问InnoDB表，运行SHOW TABLE STATUS或对INFORMATION_SCHEMA运行某些查询，运行ANALYZE TABLE时，以及表的大小发生显着变化时，统计信息会自动刷新。 InnoDB生成这些统计信息，从表中随机读取固定数量的页面，如果你有一个冷缓冲池，这很可能会打到磁盘上。
#4
3
OK. So I have been diggin' on this innoDB buffer pool issue. Thank you @Quassnoi, @TheVedge for mentioning that. It has been very useful for the following work.
好。所以我一直在关于这个innoDB缓冲池问题。谢谢@Quassnoi，@ TheVedge提到这一点。它对以下工作非常有用。
I was trying to find a way to disable or reset innoDB buffer pool but I haven't been able to find a way other than restarting MySQL server. (To restart the server on a windows box you can either use services.msc or command prompt.)
我试图找到一种方法来禁用或重置innoDB缓冲池，但我还没有找到一种方法，而不是重新启动MySQL服务器。 （要在Windows框中重新启动服务器，可以使用services.msc或命令提示符。）
I guess all the configuration variable settings and the use of "SQL_NO_CACHE" was more than enough to avoid cache use, and also checking the profiling results I was not seeing any cache reference neither (check the original post).
我想所有配置变量设置和“SQL_NO_CACHE”的使用都足以避免缓存使用，并且还检查分析结果我没有看到任何缓存引用（检查原始帖子）。
Then I decided to check the profiling for the same query's 1st and 2nd runs after restarting MySQL server. This way I would be able to see what was going on during the query execution step by step.
然后我决定在重新启动MySQL服务器之后检查同一查询的第一次和第二次运行的分析。通过这种方式，我可以逐步查看查询执行过程中发生了什么。
1st run after server restart yielded the following steps and timings:
服务器重启后第一次运行产生以下步骤和时间：
'starting', '0.000078'
'checking permissions', '0.000005'
'Opening tables', '0.120329'
'init', '0.000107'
'System lock', '0.000012'
'optimizing', '0.000024'
'statistics', '0.065317'
'preparing', '0.000026'
'executing', '0.000003'
'Sending data', '0.000038'
'end', '0.000005'
'query end', '0.000006'
'closing tables', '0.000014'
'freeing items', '0.000463'
'cleaning up', '0.000039'
And these are the results of the 2nd run:
这些是第二轮的结果：
'starting', '0.000068'
'checking permissions', '0.000006'
'Opening tables', '0.000019'
'init', '0.000046'
'System lock', '0.000007'
'optimizing', '0.000010'
'statistics', '0.000073'
'preparing', '0.000009'
'executing', '0.000002'
'Sending data', '0.000035'
'end', '0.000003'
'query end', '0.000003'
'closing tables', '0.000006'
'freeing items', '0.000181'
'cleaning up', '0.000015'
As you can see there is a huge difference for the durations of "opening tables" and "statistics" actions between the two executions and none of the executions reference any cache.
正如您所看到的，两个执行之间的“打开表”和“统计”操作的持续时间存在巨大差异，并且没有任何执行引用任何缓存。
The two actions seem to be the most time consuming parts for the 1st execution (I believe as a part of the creation of innoDB buffer pool), and once the pool is generated these two actions not performed as heavily hence the much shorter execution time for the query after the buffer pool creation.
这两个动作似乎是第一次执行时最耗时的部分（我相信作为创建innoDB缓冲池的一部分），并且一旦生成池，这两个动作的执行时间不会那么大，因此执行时间要短得多。创建缓冲池后的查询。
When you sum up the execution times of the individual steps for 2nd run we get 0.000483 seconds and I think MySQL workbench shows this result as 0.000 secs which might also trick you to think that the second run was result of cache using but actually it seems like it is not. This is just a display precision issue for the execution durations of queries.
当你总结第二次运行的各个步骤的执行时间时，我们得到0.000483秒，我认为MySQL工作台将此结果显示为0.000秒，这也可能会让你觉得第二次运行是缓存使用的结果，但实际上看起来像不是这样。这只是查询执行持续时间的显示精度问题。
As a result what I have figured out so far is I actually have been able to disable the use of caches and it is the completion of innoDB buffer pool that speeds up the second query so much. This also means that I can find a record in 2 million rows in 0.000483 seconds.
因此，到目前为止我已经想到的是我实际上已经能够禁用缓存的使用，它是innoDB缓冲池的完成，它加速了第二个查询。这也意味着我可以在0.000483秒内找到200万行的记录。
Would you agree?
你同意吗？
#5
1
In your .ini/.cfg in [mysqld] section, add a line have_query_cache=0 # to prevent using resources from default of yes ccyy/mm/dd your initials. With your other changes, close/restart MySQL. You should NOT observe any QC activity in profile research.
在[mysqld]部分的.ini / .cfg中，添加一行has_query_cache = 0＃以防止使用默认值为ccyy / mm / dd的资源作为首字母。通过其他更改，关闭/重启MySQL。您不应该在个人资料研究中观察任何QC活动。