辟谣SQL_NO_CACHE

最新推荐文章于 2023-09-22 16:49:09 发布

weixin_33704591

最新推荐文章于 2023-09-22 16:49:09 发布

阅读量515

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/monkeyzhu/blog/1503848

版权

2019独角兽企业重金招聘Python工程师标准>>>

query cache工作原理

官方文档对query cache做了说明。其中指出query cache为所有session共享。非频繁的表更新情况下使用，当表被修改所有相关的缓存条目都会被清除。缓存严格区分sql语句，mysql提供query_cache_strip_comments可以忽略带有注释的语句。当前还不支持分区表。核心参数：query_cache_type和query_cache_size 。关闭query cache需要在mysql启动时执行。

如何正确设置query cache大小

show status like "%Qcache%";
#当前cache状态
show variables like "%cache%";
#系统设置值

一开始可以参考官方文档几十M，观察当前cache使用情况，然后按需增加或减少。

是否开启query cache强烈参考下面：

Qc不适用的场景：

1、子查询或者外层查询；
2、存储过程、存储函数、触发器、event中调用的SQL，或者引用到这些结果的；
3、包含一些特殊函数时，例如：BENCHMARK()、CURDATE()、CURRENT_TIMESTAMP()、NOW()、RAND()、UUID()等等；
4、读取mysql、INFORMATION_SCHEMA、performance_schema 库数据的；
5、类似SELECT…LOCK IN SHARE MODE、SELECT…FOR UPDATE、SELECT..INTO OUTFILE/DUMPFILE、SELECT..WHRE…IS NULL等语句；
6、SELECT执行计划用到临时表（TEMPORARY TABLE）；
7、未引用任何表的查询，例如 SELECT 1+1 这种；
8、产生了 warnings 的查询；
9、SELECT语句里加了 SQL_NO_CACHE 关键字；

SQL_NO_CACHE

这个参数字面即表达了含义，官方文档在mysql5.6的文档中注释了一句，SQL_NO_CACHE关键词前面必须要有空格，否则依然会检查是否缓存。

让我们看些列子：

mysql> show variables like "%query_cache%";     
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
开启 query_cache

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                            |
+----------+------------+--------------------------------------------------------------------------------------------------+
|        1 | 0.00099850 | select SQL_NO_CACHE count(*) from info_collect_keywordresult where updated_at>"2016-04-05 00:00" |
|        2 | 0.00093250 | select SQL_NO_CACHE count(*) from info_collect_keywordresult where updated_at>"2016-04-05 00:00" |
|        3 | 0.00095800 | select
SQL_NO_CACHE count(*) from info_collect_keywordresult where updated_at>"2016-04-05 00:00" |
|        4 | 0.00019600 | show profile for query                                                                           |
|        5 | 0.00103800 | select  count(*) from info_collect_keywordresult where updated_at>"2016-04-05 00:00"             |
|        6 | 0.00015675 | select  count(*) from info_collect_keywordresult where updated_at>"2016-04-05 00:00"             |
|        7 | 0.00099475 | select SQL_NO_CACHE count(*) from info_collect_keywordresult where updated_at>"2016-04-05 00:00" |
|        8 | 0.00095950 | select
SQL_NO_CACHE count(*) from info_collect_keywordresult where updated_at>"2016-04-05 00:00" |
+----------+------------+--------------------------------------------------------------------------------------------------+
一些语句执行profile

mysql> show profile for query 6;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000022 |
| Waiting for query cache lock   | 0.000005 |
| init                           | 0.000004 |
| checking query cache for query | 0.000008 |
| checking privileges on cached  | 0.000005 |
| checking permissions           | 0.000012 |
| sending cached result to clien | 0.000094 |
| cleaning up                    | 0.000009 |
+--------------------------------+----------+
8 rows in set, 1 warning (0.00 sec)
已缓存的语句profile

mysql> show profile for query 7;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000072 |
| checking permissions | 0.000011 |
| Opening tables       | 0.000025 |
| init                 | 0.000034 |
| System lock          | 0.000011 |
| optimizing           | 0.000013 |
| statistics           | 0.000020 |
| preparing            | 0.000017 |
| executing            | 0.000004 |
| Sending data         | 0.000642 |
| end                  | 0.000012 |
| query end            | 0.000008 |
| closing tables       | 0.000013 |
| freeing items        | 0.000099 |
| cleaning up          | 0.000016 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
使用sql_no_cache参数后的执行profile看看到和官方文档说明无擦别。既不检查使用cache，也不写入cache
同时该参数也是实际在5.1的mysql版本上测试过。通过profile计划看出并不像某些blog所写。在有cache时，即便使用SQL_NO_CACHE也会使用缓存。
我想这其中可能题主在执行sql查询时只注意到时间变化，而产生使用cache的结论，而没有真正查看sql执行的profile，如果数据第一次加载到内存然后执行sql，必然比第二次数据已经在内存中要执行的慢。
所以sql的profile会告诉我们具体执行的过程。


mysql> show profile for query 8;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000026 |
| Waiting for query cache lock   | 0.000005 |
| init                           | 0.000005 |
| checking query cache for query | 0.000059 |
| checking permissions           | 0.000010 |
| Opening tables                 | 0.000024 |
| init                           | 0.000033 |
| System lock                    | 0.000018 |
| optimizing                     | 0.000013 |
| statistics                     | 0.000021 |
| preparing                      | 0.000018 |
| executing                      | 0.000004 |
| Sending data                   | 0.000567 |
| end                            | 0.000009 |
| query end                      | 0.000009 |
| closing tables                 | 0.000012 |
| freeing items                  | 0.000112 |
| cleaning up                    | 0.000018 |
+--------------------------------+----------+
18 rows in set, 1 warning (0.01 sec)

这里的mysql版本为5.6.24官方文档对此版本说明SQL_NO_CACHE关键词前面必须要有空格，否则依然会检查是否缓存
看到确实检查了缓存但并不会使用缓存，仅仅是检查。

转载于:https://my.oschina.net/monkeyzhu/blog/1503848