Windows mysql打开查询缓存 mysql查询缓存是否开启

转载

doscommand 2024-06-09 08:05:42

文章标签 Windows mysql打开查询缓存数据库数据结构与算法大数据缓存 文章分类 MySQL 数据库

Query Cache（QC）

缓存完整的Select结果，当查询命中该缓存，MySQL会立刻返回结果，跳过解析、优化和执行阶段。

1、如何判断缓存命中

缓存存放在一个引用表中，通过哈希值引用。哈希值包括查询本身、待查数据库、客户端协议版本等可能影响返回结果的信息。

注：

当表被lock tables锁住时，仍可以通过查询缓存返回数据。
任何字符不同（包括空格、注释）都会导致缓存的不命中。
不会被缓存：①查询语句包含不确定数据（如函数now()、current_date()）；②不同用户返回不同结果current_user()、connection_id()）；③包含自定义函数、存储函数、用户变量、临时表、mysql中的系统表、包含列级别权限的表等等。

a）~~“如果查询中包含一个不确定的函数，MySQL则不会检查查询缓存”？~~

错误。MySQL检查查询缓存时，仅仅检测SQL语句是否以sel（大小写不敏感）开头，还没有解析SQL语句。

b） “如果查询语句中包含任何不确定函数，则查询缓存中是找不到缓存结果的”

正解。即使执行了，结果也不会放在查询缓存中。

MySQL只要发现不能被缓存的部分，将禁止此查询被缓存。

如果查询中带有current_date，最好直接写死'2016-01-25'，

缺点：

打开查询缓存会对读写操作带来额外的消耗；

读查询开始前必须检查是否命中；
如果读查询可被缓存但还未被缓存，会将其结果存入查询缓存（额外消耗）；
写数据时，对应表所有缓存都失效，如果查询缓存非常大或碎片很多，将带来大量系统消耗。
查询缓存加锁排他。

2、查询缓存如何使用内存

查询缓存完全存储于内存中，MySQL自行管理这块内存。

服务器启动；
初始化查询缓存所需内存；（此内存池是一个完整的空闲块，大小为配置的查询缓存大小减去维护查询缓存数据结构所需空间<约40K>）
查询结果若需要缓存，MySQL从缓存池申请一个数据块用于存储，该块大于参数query_cache_min_res_unit，即使查询结果远小于query_cache_min_res_unit。【查询开始返回结果就分配空间，无法预估查询结果的大小，So无法为每个查询结果精确分配大小合适的缓存空间】
缓存时，MySQL优先选择尽可能小的数据块，若该块空间不够，将申请新的尽可能小的数据块；（亦可能选择较大的，此不深究）；若该块有剩余，MySQL将其释放，并放入空闲部分。

Note：

分配数据块需先锁住空间块，再找到合适大小，so相对耗时，MySQL尽量避免。
碎片：若平均查询结果非常小，服务器并发地向两个连接返回结果。返回结果后MySQL回收剩余数据块时，发现回收的块小于query_cache_min_res_unit，不能直接在后续的内存块中分配使用，即产生碎片。

如何减少碎片呢？

实际消耗（query_cache_size - Qcache_free_memory）除以Qcache_queries_in_cache计算单各查询的平均缓存大小。最糟糕时，任何两个数据块间都有一个非常小的空闲块，此时Qcache_free_blocks恰好达到Qcache_total_blocks / 2，碎片问题很严重。

可通过 query_cache_limit限制可缓存的最大查询结果以便减少大查询结果的缓存，从而减少碎片。

3、何时应该使用查询缓存

缓存和失效都会带来额外的消耗，So当缓存带来的资源节约大于其本身的资源消耗才推荐使用。

命中率：Qcache_hits / (Qcache_hits+Com_select)（核心，但难判断且不直观）

消耗大量资源的查询；（如汇总计算count等；多表join再排序分页，查询消耗巨大但返回结果集小）
相关表update、delete、insert 比 select少很多；
数据的访问频率非常高，或者访问频率不高，但是它的生存周期很长。
命中和写入比率：Qcache_hits / Qcache_inserts（通常3:1即可，10:1更佳）

如何分析和配置查询缓存

Windows mysql打开查询缓存 mysql查询缓存是否开启_Windows 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             | 599040  | 
  
   

    | query_cache_type             | ON      | 
  
   

    | query_cache_wlock_invalidate | OFF     | 
  
   

    +------------------------------+---------+

（1） query_cache_type有3个值：

0或off，代表关闭

1或on，代表开启

在on模式下，如果你不想使用缓存，需要通过sql_no_cache关键词来显示的指明，

如select sql_no_cache id,name from tableName;

c、2 或demand，按需要是否开启缓存。

当sql语句中有SQL_CACHE关键词时才缓存，

如：select SQL_CACHE user_name from users where user_id = '100';

无论哪种模式下，当sql中使用了mysql函数时，都不会缓存。

（2） query_cache_size表示分配给查询缓存的总内存大小，该值并非越大越好，需要结合实际情况设置。

（3） query_cache_limit 单次查询结果大于这个值，则不再缓存。该值默认是1048576，即1M。

（4） query_cache_min_res_unit 分配的最小缓存块大小，默认是4KB，设置该值大，对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。

（5） query_cache_wlock_invaliate 默认为OFF，可以读取已锁定的表的缓存数据。

缓存功效实践：

sakila.rental

②测试SQL：

a） SELECT COUNT(*) FROM sakila . rental ;

SELECT rental_date FROM sakila . rental WHERE rental_date > '2005-08-20 21:35:58' ;

时间对比如下：

耗时分别为：navicat和【黑屏】，黑屏时间保留两位小数（0.01 s）

耗时ms	开启缓存 (首次执行)	开启缓存 (多次平均)	关闭缓存 (首次执行)	关闭缓存 (多次平均)
a	36【10】	0【0】	8【30】	3【10】
b	9【10】	1【0】	9【10】	2【0】