MySQl优化学习笔记（四）查询缓存

一、系统参数配置文件的物理位置：

windows系统：MySQl文件夹下的my.ini 

linux系统：/etc/my.cnf

系统参数配置文件中都是些键值对，存的是MySQl系统级参数。  

二、修改系统参数方式

修改系统参数有如下两种方式：

    1、修改系统参数配置文件。该文件内容修改之后永久生效，该配置文件在MySQL启动时加载。

    2、通过命令修改参数。用命令修改参数均为临时修改（内存中修改），重启服务后还原，重新加载系统配置文件。

查询缓存系统参数的命令为： show VARIABLES like '%query_cache%'; （查询系统参数关键词：SHOW VARIABLES。只要以SHOW VARIABLES 开头的，都表示查看系统参数。）

    执行此命令，查询结果及结果含义如下表：

其中，query_cache_type （是否开启查询缓存）参数为查询缓存最重要的参数，每个值的含义如下：

        0（OFF）关闭查询缓存        

        1（On） 开启查询缓存功能  但是当select 语句中使用SQL_NO_cache 提示后，将不查询缓存。

        2（DEMAND） 根据需求开启  当select语句中使用了SQL_CACHE后，才使用查询缓存。

该参数不能通过命令修改（set global query_cache_type=2;命令无效），只能修改系统配置文件，修改位置如下图：

设置查询缓存大小：set global query_cache_size=134217728;

查看查询缓存状态：show status like '%Qcache%';

（次数为方便标记用我自己的截图）

三、测试查询缓存

1、场景一：query_cache_type（是否开启查询缓存）设置为2（Demand根据需求开启）

      执行如下语句：select sql_cache id from test where id=1;（当查询缓存状态为Demand根据需求开启时，只有在查询中使用了sql_cache，才能使用查询缓存），之后查看查询缓存状态，此时Qcache_inserts（插入到缓存中的SQL总数）为1，Qcache_queries_in_cache（当前在缓存中的SQl数量）为1，Qcache_hits（查询缓存被访问的次数）为0，如下图：

 再次执行语句：select sql_cache id from test where id=1;，之后查看查询缓存状态，此时Qcache_hits （查询缓存被访问的次数）为1，如下图：

若在查询语句select sql_cache id from test where id=1;的查询项id前面加个空格，执行，之后查看查询缓存状态，Qcache_insert （插入到缓存中的SQL总数）变为2，Qcache_queries_in_cache（当前在缓存中的SQl数量）变为2，Qcache_hits （查询缓存被访问的次数）仍为1，证明加了空格的SQL没有在查询缓存中，如下图：

2、场景二：query_cache_type （是否开启查询缓存）设置为1（开启）

    在配置文件中将query_cache_type设置为1，并重启SQL服务。执行如下语句：select id from test where id=1;（当查询缓存状态为ON开启时，默认使用查询缓存），之后查看查询缓存状态，此时Qcache_inserts（插入到缓存中的SQL总数）为2，Qcache_queries_in_cache（当前在缓存中的SQl数量）为2，Qcache_hits（查询缓存被访问的次数）为0，如下图：

再次执行SQL语句select id from test where id=1;，Qcache_hits（查询缓存被访问的次数）变为2（增长了2）如下图，正常情况如果SQL在查询缓存里已经存在，再次查询该SQL，Qcache_hits（查询缓存被访问的次数）应该加1才对，这里为什么加2了呢？

原因是缓存中不仅能缓存SQL命令，还能缓存权限、索引等。当查询缓存状态设置为1(ON)时，若查询中包含约束（此SQL中id为主键），缓存中会缓存SQL命令和索引，因此再次查询之后，Qcache_hits（查询缓存被访问的次数）增长了2。

当执行的SQL改为select  title from test where id=1后，执行两遍（一遍为存入查询缓存，一遍为查询缓存），再次查看查询缓存状态，Qcache_hits（查询缓存被访问的次数）增长了1，因为仅包含SQL命令，不包含索引，如下图：

再次执行select  title from test where id=1，然后查看查询缓存状态，Qcache_hits（查询缓存被访问的次数）增长了1，变为4，如下图：

当query_cache_type设置为1（ON）时，若不想查询缓存 则语句为：select sql_no_cache title from test where id=1;，执行语句后查看查询缓存状态，Qcache_hits（查询缓存被访问的次数）不变，如下图：

3、清空缓存：reset query cache

    执行情况缓存语句：reset query cache后，再次查看查询缓存状态，Qcache_queries_in_cache（当前在缓存中的SQl数量）变为0。

再次运行select  title from test where id = 1;后查看查询缓存状态，此时Qcache_inserts（插入到缓存中的SQL总数）与Qcache_queries_in_cache（当前在缓存中的SQl数量）均增长了1，Qcache_inserts（插入到缓存中的SQL总数）包含清空缓存之前的查询缓存次数。

PS：query_cache_type 设置为1（开启），会增大查询效率，但同时会影响删除和修改的效率，因此适用于查询频繁，而很少做修改和删除操作的场景。