mysql 查看并开启查询缓存

如何查看并开启查询缓存

查看是否开启查询缓存：

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             | OFF       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)

query_cache_type:是否开启查询缓存，0 表示不开启查询缓存，1 表示始终开启查询缓存 (不要缓 存使用 sql_no_cache)， 2 表示按需开启查询缓存 (需要缓存使 用 sql_cache)
query_cache_size:给缓存分配的最大内存空间

FLUSH QUERY CACHE : 清理查询缓存内存碎片 (不会清理查询缓存)
RESET QUERY CACHE : 从查询缓存中移出所有查询
FLUSH TABLES : 关闭所有打开的表，同时该操作将会清空查询缓存中的内容

开启查询缓存：需要修改配置文件,linux my.cnf

[root@localhost www]# find / -name my.cnf
/etc/my.cnf

[root@localhost www]# vim /etc/my.cnf

添加query_cache_type=1

[root@localhost www]# systemctl restart mysqld

查询结果：

SELECT * from purchase_order WHERE order_sn like "ry2bhxtAu4%"
> OK
> 时间: 2.674s

SELECT * from purchase_order WHERE order_sn like "ry2bhxtAu4%"
> OK
> 时间: 0.002s

以上是查看和开启查询缓存的demo，下面记录一下查询缓存的概念、优缺点及应用注意点：

了解什么是查询缓存，需要先了解MySQL的执行流程，这里文字描述
1.客户端向MySQL服务器发送一条查询请求
2.服务器先查询查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段
3.服务器进行SQL解析，预处理、再由优化器生成对应的执行计划
4.MySQL根据执行计划，调用存储引擎的API来执行查询
5.将结果返回给客户端，同时缓存查询结果

可以了解到 查询缓存 相当于一个hash结构，哈希值索引，这个哈希值通过查询本身、当前要查询的 数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同 (例如 : 空格、注释)，都会导致缓存不会命中。
简单理解为：开启查询缓存后，在满足缓存条件下，可以快速返回结果集。比如demo中从2s到0.01s的查询返回。

那么具备命中缓存的条件是什么呢，哪些不会缓存呢？

  • 查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果 都不会被缓存。比如函数 NOW() 或者 CURRENT_DATE() 会因为不同的查询时间，返回不同的查询结果，再 比如包含 CURRENT_USER 或者 CONNECION_ID() 的查询语句会因为不同的用户而返回不同的结果，将这样 的查询结果缓存起来没有任何的意义

  • MySQL 查询缓存系统会跟踪查询中涉及的每个表，如果这些表 (数据或结构) 发生变化，那么和这张表相关 的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失 效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿

查询缓存对系统的额外消耗不仅仅在写，读也会有消耗：

  1. 任何的查询语句在开始之前都必须经过检查，即使这条 SQL语句 永远不会命中缓存
  2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

基于此，并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，特别是写密集型应用，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。可以尝试打开查 询缓存，并在数据库设计上做一些优化 :

  1. 用多个小表代替一个大表，注意不要过度设计
  2. 批量插入代替循环单条插入
  3. 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
  4. 可以通过 SQL_CACHE 和 SQL_NO_CACHE 来控制某个查询语句是否需要进行缓存
    注 : SQL_NO_CACHE 是禁止缓存查询结果，是从设置后开始的，如果之前有缓存的话，满足命中条件还是可以作为缓存结果返回的。

补充：

mysql服务端分层：
连接层：主要是线程管理，对程序连接的管理
sql层：解析sql，优化sql
存储引擎层：innodb，myisam存储引擎

连接层：1.连接后的用户密码的校验。2.校验后的连接进行线程分配管理。3.对用户校验后的数据库表的操作权限的校验
mysql最大连接数：show variables like "%max_connections%";
当前用户的连接：show processlist;
sql层：sql语句是由连接层传递过来的
如：select * from user where id > 10 and (age >11 or sex = 0 );

  1. 先判断sql语句的类型
    （query(select)）,dml(insert, update, delete), ddl(alter), status(show status)等

  2. 假设query
    mysql8之前是先判断查询缓存是否开启，如果开启查询缓存，看是否命中，如果命中，那么直接返回结果，反之继续执行。
    mysql8.0之后，执行流程是解析器，sql解析器，语法解析器。
    sql解析器：根据查询的sql语句将sql划分为小token
    将上面的sql语句分成了 select,*,from,where,id,>,10。。。
    得到前面分解的token，根据token去进行排列组合（关键字and or）成解析树
    是根据where条件中的关键词进行组合。

优化器做了什么？
优化器：根据解析树，选择合适的执行计划（这个计划不一定最优）
1.获取表结构信息（字段信息，字段类型，存储位置，索引信息）获取的信息是查询的表的信息。如果是join那么就是获取两张表的信息。
2. 根据解析树进行条件过滤，主要是一些没有意义的查询 1=1
3.索引信息 来确定、判断执行计划
4.执行这个计划，在索引以及条件等来过滤

posted @ 2020-11-10 16:21  蝶墨轩冕  阅读(2091)  评论(0)  编辑  收藏  举报