您现在的位置是：网站首页> 编程资料编程资料
MySQL查询缓存的小知识_Mysql_
2023-05-27
350人已围观
简介 MySQL查询缓存的小知识_Mysql_
前言
我们知道，缓存的设计思想在RDBMS数据库中无处不在，就拿号称2500w行代码，bug堆积如山的Oracle数据库来说，SQL的执行计划可以缓存在library cache中避免再次执行相同SQL发生硬解析（语法分析->语义分析->生成执行计划），SQL执行结果缓存在RESULT CACHE内存组件中，有效的将物理IO转化成逻辑IO，提高SQL执行效率。
MySQL的QueryCache跟Oracle类似，缓存的是SQL语句文本以及对应的结果集，看起来是一个很棒的Idea，那为什么从MySQL 4.0推出之后，5.6中默认禁用，5.7中被deprecated（废弃）以及8.0版本被Removed，今天就聊聊MySQL QueryCache的前世今生。
QueryCache介绍
MySQL查询缓（QC：QueryCache）在MySQL 4.0.1中引入，查询缓存存储SELECT语句的文本以及发送给客户机的结果集，如果再次执行相同的SQL，Server端将从查询缓存中检索结果返回给客户端，而不是再次解析执行SQL，查询缓存在session之间共享，因此，一个客户端生成的缓存结果集，可以响应另一个客户端执行同样的SQL。
回到开头的问题，如何判断SQL是否共享？
通过SQL文本是否完全一致来判断，包括大小写，空格等所有字符完全一模一样才可以共享，共享好处是可以避免硬解析，直接从QC获取结果返回给客户端，下面的两个SQL是不共享滴，因为一个是from，另一个是From。
--SQL 1 select id, balance from account where id = 121; --SQL 2 select id, balance From account where id = 121;
下面是Oracle数据库通过SQL_TEXT生成sql_id的算法，如果sql_id不一样说明就不是同一个SQL，就不共享，就会发生硬解析。
#!/usr/bin/perl -w use Digest::MD5 qw(md5 md5_hex md5_base64); use Math::BigInt; my $stmt = "select id, balance from account where id = 121\0"; my $hash = md5 $stmt; my($a,$b,$msb,$lsb) = unpack("V*",$hash); my $sqln = $msb*(2**32)+$lsb; my $stop = log($sqln) / log(32) + 1; my $sqlid = ''; my $charbase32 = '0123456789abcdfghjkmnpqrstuvwxyz'; my @chars = split '', $charbase32; for($i=0; $i < $stop-1; $i++){ my $x = Math::BigInt->new($sqln); my $seq = $x->bdiv(32**$i)->bmod(32); $sqlid = $chars[$seq].$sqlid; } print "SQL is:\n $stmt \nSQL_ID is\n $sqlid\n"; 大家可以发现SQL 1和SQL 2通过代码生成的sql_id值是不一样，所以不共享。
SQL is: select id, balance from account where id = 121 SQL_ID is dm5c6ck1g7bds SQL is: select id, balance From account where id = 121 SQL_ID is 6xb8gvs5cmc9b
如果让你比较两个Java代码文件的内容的有何差异，只需要将这段代码理解透了，就可以改造实现自己的业务逻辑。
QueryCache配置
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 |
| Variable_name | Description |
|---|---|
| have_query_cache | 查询缓存是否可用，YES-可用；NO-不可用，如果用标准二进制MySQL，值总是YES。 |
| query_cache_limit | 控制单个查询结果集的最大尺寸，默认是1MB。 |
| query_cache_min_res_unit | 查询缓存分片数据块的大小，默认是4KB，可以满足大部分业务场景。 |
| query_cache_size | 查询缓存大小，单位Bytes，设置为0是禁用QueryCache，注意：不要将缓存的大小设置得太大，由于在更新过程中需要线程锁定QueryCache，因此对于非常大的缓存，您可能会看到锁争用问题。 |
| query_cache_type | 当query_cache_size>0；该变量影响qc如何工作，有三个取值0，1，2，0：禁止缓存或检索缓存结果；1：启用缓存，SELECT SQL_NO_CACHE的语句除外；2：只缓存以SELECT SQL_CACHE开头的语句。 |
query_cache_min_res_unit说明
默认大小是4KB，如果有很多查询结果很小，那么默认数据块大小可能会导致内存碎片，由于内存不足，碎片可能会强制查询缓存从缓存中删除查询。
在这种情况下，可以减小query_cache_min_res_unit的值，由于修剪而删除的空闲块和查询的数量由Qcache_free_blocks和Qcache_lowmem_prunes状态变量的值给出，如果大量的查询有较大的结果集，可以增大该参数的值来提高性能。
通常开启QueryCache方式
# 修改MySQL配置文件/etc/my.cnf，添加如下配置，重启MySQL server即可。 [mysqld] query_cache_size = 32M query_cache_type = 1
QueryCache使用
先搞点测试数据，分别对禁用和开启QueryCache下的场景进行测试。
--创建一个用户表users，并且插入100w数据。 CREATE TABLE `users` ( `id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名', `age` tinyint NOT NULL DEFAULT '0' COMMENT 'age', `gender` char(1) NOT NULL DEFAULT 'M' COMMENT '性别', `phone` varchar(16) NOT NULL DEFAULT '' COMMENT '手机号', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表'; select count(*) from users; +----------+ | count(*) | +----------+ | 1000000 |
禁用queryCache场景
在不使用QueryCache的时候，每次执行相同的查询语句，都要发生一次硬解析，消耗大量的资源。
#禁用QueryCache的配置 query_cache_size = 0 query_cache_type = 0
重复执行下面查询，观察执行时间。
--第一次执行查询语句 mysql> select * from users order by create_time desc limit 10; +---------+------------+-----+--------+-------------+---------------------+---------------------+ | id | name | age | gender | phone | create_time | update_time | +---------+------------+-----+--------+-------------+---------------------+---------------------+ | 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 | ....... 10 rows in set (0.89 sec) --第二次执行同样的查询语句 mysql> select * from users order by create_time desc limit 10; +---------+------------+-----+--------+-------------+---------------------+---------------------+ | id | name | age | gender | phone | create_time | update_time | +---------+------------+-----+--------+-------------+---------------------+---------------------+ | 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 | ....... 10 rows in set (0.90 sec) -- profile跟踪情况 mysql> show profile cpu,block io for query 1; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | preparing | 0.000022 | 0.000017 | 0.000004 | 0 | 0 | | Sorting result | 0.000014 | 0.000009 | 0.000005 | 0 | 0 | | executing | 0.000011 | 0.000007 | 0.000004 | 0 | 0 | | Sending data | 0.000021 | 0.000016 | 0.000004 | 0 | 0 | | Creating sort index | 0.906290 | 0.826584 | 0.000000 | 0 | 0 |
可以看到，多次执行同样的SQL查询语句，执行时间都是0.89s左右，几乎没有差别，同时时间主要消耗在Creating sort index阶段。
开启queryCache场景
开启查询缓存时，查询语句第一次被执行时会将SQL文本及查询结果缓存在QC中，下一次执行同样的SQL执行从QC中获取数据返回给客户端即可。
#禁用QueryCache的配置 query_cache_size = 32M query_cache_type = 1
--第一次执行查询语句 mysql> select * from users order by create_time desc limit 10; +---------+------------+-----+--------+-------------+---------------------+---------------------+ | id | name | age | gender | phone | create_time | update_time | +---------+------------+-----+--------+-------------+---------------------+---------------------+ | 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 | ....... 10 rows in set (0.89 sec) --第二次执行查询语句 mysql> select * from users order by create_time desc limit 10; +---------+------------+-----+--------+-------------+---------------------+---------------------+ | id | name | age | gender | phone | create_time | update_time | +---------+------------+-----+--------+-------------+---------------------+---------------------+ | 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 | ....... 10 rows in set (0.00 sec) -- profile跟踪数据 mysql> show profile cpu,block io for query 3; +--------------------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+----------+----------+------------+--------------+---------------+ | Waiting for query cache lock | 0.000016 | 0.000015 | 0.000001 | 0 | 0 | | checking query cache for query | 0.000007 | 0.000007 | 0.000000 | 0 | 0 | | checking privileges on cached | 0.000004 | 0.000003 | 0.000000 | 0 | 0 | | checking permissions | 0.000034 | 0.000033 | 0.000001 | 0 | 0 | | sending cached result to clien | 0.000018 | 0.000017 | 0.000001 | 0 | 0 |
可以看到，第一次执行QueryCache里没有缓存SQL文本及数据，执行时间0.89s，由于开启了QC，SQL文本及执行结果被缓存在QC中，第二次执行执行同样的SQL查询语句，直接命中QC且返回数据，不需要发生硬解析，所以执行时间降低为0s，从profile里看到sending cached result to client直接发送QC中的数据返回给客户端。
查询缓存命中率
查询缓存相关的status变量
mysql>SHOW GLOBAL STATUS LIKE 'QCache\_%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ |
点击排行
本栏推荐
