MySQL查询缓存 --《高性能MySQL》读书笔记-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

MySQL查询缓存 --《高性能MySQL》读书笔记

简介: <div style="font-family:微软雅黑; font-size:14px; line-height:21px">        Query Cache(QC)</div> <div style="font-family:微软雅黑; font-size:14px; line-height:21px">        缓存完整的Select结果,当查询命中该缓存,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_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更佳)

如何 分析和配置 查询缓存



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个值 :
  a、0或off,代表关闭 
 b、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,可以读取已锁定的表的缓存数据。


缓存功效实践:
①测试数据:MySQL自带数据库sakila.rental
②测试SQL:
a)SELECT  COUNT(*)FROM sakila.rental;
b)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】
Note:
  • 关闭缓存后重启MySQL:net stop/start mysql57
  • 一定要先关闭缓存,不能在运行时设置参数关闭。
  • C:\ProgramData\MySQL\MySQL Server 5.7\my.ini文件:query_cache_type=0,query_cache_size=0
总结:
  • 开启缓存是要消耗资源的;
  • 多次执行相同查询,开启缓存效果更佳;
疑问:
  • 关闭缓存后,首次执行虽比开启缓存节省时间,但依旧比多次平均执行耗时。
  • 执行a语句时,黑屏总比navicat耗时?

关闭缓存后,第一次查询很慢,后面很快?
        ①缓存禁用了,第一次查时数据从硬盘加载到内存,再连续查速度变快。 由于内存有限,一会儿后数据从内存清除,当然再查就慢了。
        ②禁用缓存,仅是禁止了SQL语句重新分析和数据读取,但如果有些表,索引已经打开或者加载到内存中,则在内存无其它冲突请求时仍然有效。 因此会速度快于第一次。


常用命令:
  • 碎片整理:flush query cache【将所有查询缓存重新排序,并将所有空闲空间聚集起来】
  • 清空缓存:reset query cache【加锁访问所有查询缓存,此期间其他连接无法访问查询缓存,从而导致服务器僵死,so尽量使查询缓存空间较小,控制服务器僵死在非常短的时间内】


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章