查询缓存:
缓存的数据:k/v,即键值对;
key:查询语句的hash值;
value:查询语句的查询结果;
判断缓存是否命中标准:
通过整个查询语句的hash值的比较,完全相同则命中;
有些查询结果是不能被缓存的:
要查询的数据库中可能包含敏感信息
在查询语句中包含有用户自定义的函数(UDF);
存储函数;
用户自定义变量;
对于临时表发起的查询请求;
包含列级别授权的查询;
有着不确定结果值的mysql的内建函数:
如:NOW(), CURRENT_DATE(), CURRENT_TIME(), CURRENT_USER(),...
在mysql数据库查询缓存相关的服务器变量:
MariaDB [(none)]> show global variables like 'query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_strip_comments | OFF | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 6 rows in set (0.00 sec)
query_cache_limit:能够缓存的查询结果的最大字节数上限
query_cache_min_res_unit:查询缓存中内存块的最小分配单元;
query_cache_size:查询缓存申请的在内存中的总可用空间;
query_cache_strip_comments:用于控制是否去掉SQL查询语句中的注释部分之后在作为key存入查询缓存中。
query_cache_type:缓存功能开启与否的开关:
query_cache_wlock_invalidate:如果某个连接会话对某表施加了写锁,是否依然可以从缓存中查询并返回查询结果;
想要查看缓存服务器状态参数,就要设置查询缓存申请的内存空间的大小,必须是1024的整数倍;
MariaDB [(none)]> set @@global.query_cache_size=134217728; Query OK, 0 rows affected (0.00 sec) MariaDB [hellodb]> show global status like 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 134198384 | | Qcache_hits | 1 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+-----------+ 8 rows in set (0.00 sec)
Qcache_free_blocks:表示查询缓存中目前还剩余多少个blocks;
Qcache_free_memory:查询缓存中还空闲的内存空间;
Qcache_hits:表示查询缓存中查询语句的命中次数。数字越大,缓存效果越理想;
Qcache_lowmem_prunes:该参数记录了有多少条查询请求是因为内存空间不足而基于LRU算法被移出缓存的;如果该数值较大,则表示为查询缓存分配的内存空间太小;
Qcache_not_cached:取决于query_cache_type变量的设置的作用下,没有被缓存的查询请求的数量;
Qcache_queries_in_cache:当前查询缓存中缓存的查询请求的结果的数量;
Qcache_total_blocks:当前查询缓存中总计分配了多少个block;
缓存命中率的计算:
Qcache_hits/(Qcache_hits + Qcache_inserts)
通过查看服务器参数区别,来判断是否命中缓存;
首先,查看表内容,然后查看参数,在查看表内容,在查看参数;
MariaDB [hellodb]> select * from coc; +----+---------+----------+ | ID | ClassID | CourseID | +----+---------+----------+ | 1 | 1 | 2 | | 2 | 1 | 5 | | 3 | 2 | 2 | | 4 | 2 | 6 | | 5 | 3 | 1 | | 6 | 3 | 7 | | 7 | 4 | 5 | | 8 | 4 | 2 | | 9 | 5 | 1 | | 10 | 5 | 9 | | 11 | 6 | 3 | | 12 | 6 | 4 | | 13 | 7 | 4 | | 14 | 7 | 3 | +----+---------+----------+ 14 rows in set (0.00 sec) MariaDB [hellodb]> show global status like 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 134198384 | | Qcache_hits | 0 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+-----------+ 8 rows in set (0.00 sec) MariaDB [hellodb]> select * from coc; +----+---------+----------+ | ID | ClassID | CourseID | +----+---------+----------+ | 1 | 1 | 2 | | 2 | 1 | 5 | | 3 | 2 | 2 | | 4 | 2 | 6 | | 5 | 3 | 1 | | 6 | 3 | 7 | | 7 | 4 | 5 | | 8 | 4 | 2 | | 9 | 5 | 1 | | 10 | 5 | 9 | | 11 | 6 | 3 | | 12 | 6 | 4 | | 13 | 7 | 4 | | 14 | 7 | 3 | +----+---------+----------+ 14 rows in set (0.00 sec) MariaDB [hellodb]> show global status like 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 134198384 | | Qcache_hits | 1 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+-----------+ 8 rows in set (0.00 sec)
通过Qcache_hits参数比较,可以发现,通过Qcache_inserts添加缓存之后,再次输入查询语句,是从缓存中提取的结果;
需要注意,如果对表进行了写操作(增、删、改),则MySQL会自动将查询缓存中与该表相关的所有缓存项全部清除;因此,与此表相关的查询请求必须重新构建缓存内容;