mysql基础之查询缓存、存储引擎

Wesley13
• 阅读 421

一、查询缓存

  “查询缓存”,就是将查询的结果缓存下载,如果查询语句完全相同,则直接返回缓存中的结果。

  如果应用程序在某个场景中,需要经常执行大量的相同的查询,而且查询出的数据不会经常被更新,那么,使用查询缓存会有一定的性能提升。

查看当前服务是否开启了查询缓存功能:

mysql基础之查询缓存、存储引擎

MariaDB [ren]> 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             | 1048576 |
| query_cache_strip_comments   | OFF     |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
7 rows in set (0.00 sec)

mysql基础之查询缓存、存储引擎

query_cache_type的值设置了OFF,表示目前没有开启查询缓存功能

query_cache_type的值可以设置为:ON、OFF、DEMAND,分别表示已启用、已禁用、按需缓存,设置在配置文件/etc/my.cnf.d/server.cnf中即可。

have_query_cache的值为YES,表示当前数据库支持缓存功能

query_cache_limit表示单条查询缓存的最大值,如果查询结果超过此值的大小,即时指定缓存当前结果,结果也不会被缓存,默认值为1M。

query_cache_min_res_unit表示缓存存储于内存的最小单元,默认为4K,也就是说,即时查询结果只有1K,也会占用4K内存,所以,如果此值设置的过大,会造成内存空间的浪费,如果此值设置的过小,则会频繁的分配内存单元或者频繁的回收内存单元。

query_cache_size表示查询缓存的总大小,也就是说,内存中用于查询缓存的空间大小,如果其值为0,即时开启了查询缓存,也无法缓存。

query_cache_wlock_invalidate表示查询语句所查询的表如果被写锁锁定,是否仍然使用缓存返回结果。也就是“查询缓存遭遇写锁时是否失效”,设置为OFF表示“不失效”;设置为ON表示“失效”。当此值设置为ON,如果表被施加了写锁,那么当写锁释放时,数据可能发生了改变,所以在表被施加写锁期间,即时此时有查询语句命中了查询缓存,也不能从缓存获取结果。(此值设置为OFF时,性能更好,并发能力更好,此值设置为ON时,更加安全,保证了数据的一致性)(写锁时独立的,排他的)

查询语句完全相同时,缓存才能够被命中,完全相同表示大小写也相同。

一般在数据变化不频繁,且又需要重复执行相同查询的场景中使用缓存。

二、使用查询缓存

使用方式:

开启缓存:query_cache_type=ON后,指定对应的查询语句不适用缓存:select sql_no_cache name from stu;按需使用缓存:query_cache_type=DEMAND,指定对应的查询语句使用缓存
select sql_cache name from stu;

第一种是默认符合缓存条件的都缓存,只有使用sql_no_cache指定的语句不缓存

第二种是默认所有查询语句的结果都不缓存,只有使用sql_cache指定的语句才会缓存

例子:

mysql基础之查询缓存、存储引擎

[root@ren7 ~]# vim /etc/my.cnf.d/server.cnf 
#############################################[server]
query_cache_type=DEMAND
query_cache_size=100M#############################################
[root@ren7 ~]# systemctl restart mariadb

mysql基础之查询缓存、存储引擎

查看查询缓存相关的参数:

mysql基础之查询缓存、存储引擎

MariaDB [ren]> show variables like '%query%';
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| expensive_subquery_limit     | 100           |
| ft_query_expansion_limit     | 20            |
| have_query_cache             | YES           |
| long_query_time              | 10.000000     |
| query_alloc_block_size       | 16384         |
| query_cache_limit            | 1048576       |
| query_cache_min_res_unit     | 4096          |
| query_cache_size             | 104857600     |
| query_cache_strip_comments   | OFF           |
| query_cache_type             | DEMAND        |
| query_cache_wlock_invalidate | OFF           |
| query_prealloc_size          | 24576         |
| slow_query_log               | OFF           |
| slow_query_log_file          | ren7-slow.log |
+------------------------------+---------------+
14 rows in set (0.00 sec)

mysql基础之查询缓存、存储引擎

mysql基础之查询缓存、存储引擎

执行三次查询语句:MariaDB [ren]> select sql_cache * from students where id=8;
+----+--------------+------+------+--------+--------+-----------+
| id | name         | age  | high | gender | cls_id | is_delete |
+----+--------------+------+------+--------+--------+-----------+
|  8 | 周杰伦儿     |   34 | NULL | 男     |      1 |           |
+----+--------------+------+------+--------+--------+-----------+
1 row in set (0.00 sec)

mysql基础之查询缓存、存储引擎

查看缓存命中的情况:

mysql基础之查询缓存、存储引擎

MariaDB [ren]> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 104838240 |
| Qcache_hits             | 2         |
| Qcache_inserts          | 1         |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 0         |
| Qcache_queries_in_cache | 1         |
| Qcache_total_blocks     | 4         |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql基础之查询缓存、存储引擎

Qcache_free_blocks表示已分配的内存中空闲块的数量;

Qcache_free_memory表示查询缓存的空闲总量大小;

Qcache_hits表示以被缓存的条目的命中次数;

Qcache_inserts表示在未命中缓存时,将查询结果写入缓存的次数;

Qcache_lowmem_prunes表示用于查询缓存的内存区域的修剪次数(当用于缓存的内存被占满时,mysql会使用LRU算法清除命中率低的缓存项,从而空余出部分内存空间,用于缓存新的“查询缓存”);

Qcache_not_cached表示没有被缓存的查询语句的数量;

Qcache_queries_in_cache表示已经缓存的SQL语句的数量;

Qcache_total_blocks表示当前查询缓存占用的内存的block数量。

mysql基础之查询缓存、存储引擎

查询缓存的碎片率 = (Qcache_free_blocks / Qcache_total_blocks)* 100%

查询缓存利用率 = (Qcache_cache_size - Qcache_free_memory) / query_cache_size * 100%

query_cache_min_res_unit的预估值参考计算公式:(query_cache_size - Qcache_free_memory)/ Qcache_queries_in_cache

查询缓存命中率 = (Qcache_hits / Com_select)* 100%

mysql基础之查询缓存、存储引擎

Com_select表示查询语句的执行次数:

mysql基础之查询缓存、存储引擎

MariaDB [ren]> show status like 'Com_select%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 3     |
+---------------+-------+
1 row in set (0.00 sec)

mysql基础之查询缓存、存储引擎

flush query cache;可以清理查询缓存碎片,但并不会从缓存中移除任何缓存;

reset query cache;会从查询缓存中移除所有查询结果的缓存。

三、存储引擎

  mysql中,存储引擎是插件式的,同一个数据库中的不同的表可以使用不同的存储引擎,所以,存储引擎是表级别的概念,存储引擎也被称为“表类型”,每张表可以使用不同的存储引擎类型。

  mysql中最常用的存储引擎是innodb与myisam。

MYISAM:支持表级锁,不支持行级锁,不支持事务,不支持外键约束,支出全文索引,表空间文件相对小;

INNODB:支持表级锁,行级锁,支持事务,支持外键,不支持全文索引,表空间文件相对较大。

 1、查看表类型,查看存储引擎

mysql基础之查询缓存、存储引擎

MariaDB [ren]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

mysql基础之查询缓存、存储引擎

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
2年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Stella981 Stella981
2年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Wesley13 Wesley13
2年前
MySQL总结(十一)子查询
!(https://oscimg.oschina.net/oscnet/upa344f41e81d3568e3310b5da00c57ced8ea.png)子查询1\.什么是子查询需求:查询开发部中有哪些员工selectfromemp;通
Easter79 Easter79
2年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
京东云开发者 京东云开发者
5个月前
浅谈SQL优化小技巧 | 京东云技术团队
回顾MySQL的执行过程,帮助介绍如何进行sql优化。(1)客户端发送一条查询语句到服务器;(2)服务器先查询缓存,如果命中缓存,则立即返回存储在缓存中的数据;(3)未命中缓存后,MySQL通过关键字将SQL语句进行解析,并生成一颗对应的解析树,MySQL
Python进阶者 Python进阶者
4个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这