首页 > 数据库 >MySQL query_cache

MySQL query_cache

时间:2023-11-09 11:33:38浏览次数:44  
标签:Qcache 缓存 buffer cache Innodb MySQL query pool


在服务器级别只提供了query cache,而在存储引擎级别,MyISAM和InnoDB分别引入了key cache和buffer pool

什么是query cache


Mysql没有shared_pool缓存执行计划,但是提供了query cache缓存sql执行结果和文本,如果在生命周期内完全相同的sql再次运行,则连sql解析都免去了;

所谓完全相同,包含如下条件:

Sql的大小写必须完全一样;

发起sql的客户端必须使用同样的字符集和通信协议;

sql查询同一数据库下的同一个表(不同数据库可能有同名表);

Sql查询结果必须确定,即不能带有now()等函数;

当查询表发生DML或DDL,其缓存即失效;

针对mysql/information_schema/performance_schema的查询不缓存;

使用临时表的sql也不能缓存;



开启缓存后,每个select先检查是否有可用缓存(必须对这些表有select权限),而每个写入操作先执行查询语句并使相关缓存失效;

5.5起可缓存基于视图的查询


缓存查看与设置


设置MySQL的查询缓存大小

SET GLOBAL QUERY_CACHE_SIZE=20000000;

显示MySQL的查询缓存

SHOW VARIABLES LIKE '%query_cache%';



Mysql维护一个hash表用来查找缓存,其key为sql text,数据库名以及客户端协议的版本等

相应参数

Have_query_cache:服务器是否支持查询缓存

Query_cache_type:0(OFF)不缓存;1(ON)缓存查询但不包括使用SQL_NO_CACHE的sql;2(DEMAND)只缓存使用SQL_CACHE的sql

Query_cache_size:字节为单位,即使query_cache_type=0也会为分配该内存,所以应该一并设置为0

Query_cache_limit:允许缓存的最大结果集,大于此的sql不予缓存

Query_cache_min_res_limit:用于限定块的最小尺寸,默认4K;



缓存的metadata占有40K内存,其可分为大小不等的多个子块,各块之间使用双向链表链接;根据其功能分别存储查询结果,基表和sql text等;

每个sql至少用到两个块:分别存储sql文本和查询结果,查询引用到的表各占一个块;

为了减少响应时间,每产生1行数据就发送给客户端;

数据库启动时调用malloc()分配查询缓存

 

查询缓存拥有一个全局锁,一旦有会话获取就会阻塞其他访问缓存的会话,因此当缓存大量sql时,缓存invalidation可能会消耗较长时间;

 

Innodb也可以使用查询缓存,每个表在数据字典中都有一个事务ID计数器,ID小于此值的事务不可使用缓存;表如果有锁(任何锁)则也不可使用查询缓存;

 

 状态变量

有关query cache的状态变量都以Qcache打头

mysql> SHOW STATUS LIKE 'Qcache%';

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

| Variable_name           | Value  |

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

| Qcache_free_blocks      | 36     |

| Qcache_free_memory      | 138488 |

| Qcache_hits             | 79570  |

| Qcache_inserts          | 27087  |

| Qcache_lowmem_prunes    | 3114   |

| Qcache_not_cached       | 22989  |

| Qcache_queries_in_cache | 415    |

| Qcache_total_blocks     | 912    |

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

Qcache_inserts—被加到缓存中query数目

Qcache_queries_in_cache—注册到缓存中的query数目

缓存每被命中一次,Qcache_hits就加1;

计算缓存query的平均大小=(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache

Com_select = Qcache_not_cached + Qcache_inserts + queries with errors found during the column-privileges check

Select = Qcache_hits + queries with errors found by parser


 


 


Buffer pool


innodb即缓存表又缓存索引,还有设置多个缓冲池以增加并发,很像oracle

采用LRU算法:

所有buffer块位于同一列表,其中后3/8为old,每当新读入一个数据块时,先从队尾移除同等块数然后插入到old子列的头部,如再次访问该块则将其移至new子列的头部

Innodb_buffer_pool_size:  buffer pool大小

Innodb_buffer_pool_instances: 子buffer pool数量,buffer pool至少为1G时才能生效

Innodb_old_blocks_pct: 范围5 – 95, 默认为37即3/8,指定old子列的比重

Innodb_old_blocks_time: 以ms为单位,新插入old子列的buffer块必须等待指定时间后才能移入new列,适用于one-time scan频繁的操作,以避免经常访问的数据块被剔出buffer pool

 

可通过状态变量获知当前buffer pool的运行信息

Innodb_buffer_pool_pages_total:缓存池总页数

Innodb_buffer_pool_bytes_data:当前buffer pool缓存的数据大小,包括脏数据

Innodb_buffer_pool_pages_data:缓存数据的页数量

Innodb_buffer_pool_bytes_dirty:缓存的脏数据大小

Innodb_buffer_pool_pages_diry:缓存脏数据页数量

Innodb_buffer_pool_pages_flush:刷新页请求数量

Innodb_buffer_pool_pages_free:空闲页数量

Innodb_buffer_pool_pages_latched:缓存中被latch的页数量,这些页此刻正在被读或写;然而计算此变量比较消耗资源,只有在UNIV_DEBUG被定义了才可用

相关源代码如下

#ifdef UNIV_DEBUG

  {"buffer_pool_pages_latched",

  (char*) &export_vars.innodb_buffer_pool_pages_latched,  SHOW_LONG},

#endif /* UNIV_DEBUG */

 

Innodb_buffer_pool_pages_misc:用于维护诸如行级锁或自适应hash索引的内存页=总页数-空闲页-使用的页数量

Innodb_buffer_pool_read_ahead:预读入缓存的页数量

Innodb_buffer_pool_read_ahead_evicted:预读入但是1次都没用就被剔出缓存的页

Innodb_buffer_pool_read_requests:InnoDB的逻辑读请求次数

Innodb_buffer_pool_reads:直接从磁盘读取数据的逻辑读次数

Innodb_buffer_pool_wait_free:缓存中没有空闲页满足当前请求,必须等待部分页回收或刷新,记录等待次数

Innodb_buffer_pool_write_requests:向缓存的写数量

 

 

可使用innodb standard monitor监控buffer pool的使用情况,主要有如下指标:

Old database pages: old子列中的页数

Pages made young, not young: 从old子列移到new子列的页数,old子列中没有被再次访问的页数

Youngs/s  non-youngs/s: 访问old并导致其移到new列的次数



 Key cache


5.5仅支持一个结构化变量,即key cache,其包含4个部件

Key_buffer_size

Key_cache_block_size:单个块大小,默认1k

Key_cache_division_limit:warm子列的百分比(默认100),key cache buffer列表的分隔点,用于分隔host和warm子列表

Key_cache_age_threshold:页在hot子列中的生命周期,值越小则越快的移至warm列表

 

MyISAM只缓存索引,

可创建多个key buffer—set global hot_cache.key_buffer_size=128*1024

索引指定key buffer—cache index t1 in hot_cache

可在数据库启动时load index into key_buffer提前加载缓存,也可通过配置文件自动把索引映射到key cache

 

key_buffer_size = 4G

hot_cache.key_buffer_size = 2G

cold_cache.key_buffer_size = 2G

init_file=/path/to/data-directory/mysqld_init.sql

mysqld_init.sql内容如下

CACHE INDEX db1.t1, db1.t2, db2.t3 IN hot_cache

CACHE INDEX db1.t4, db2.t5, db2.t6 IN cold_cache

 

默认采用LRU算法,也支持名为中间点插入机制midpoint insertion strategy

索引页刚读入key cache时,被放在warm列的尾部,被访问3次后则移到hot列尾并循环移动,如果在hot列头闲置连续N次都没访问到,则会被移到warm列头,成为被剔出cache的首选;

N= block no* key_cache_age_threshold/100

标签:Qcache,缓存,buffer,cache,Innodb,MySQL,query,pool
From: https://blog.51cto.com/u_809530/8274686

相关文章

  • Proxy下的Prepare透传,让GaussDB(for MySQL)更稳固,性能更卓越
     本文分享自华为云社区《Proxy下的Prepare透传,让GaussDB(forMySQL)更稳固,性能更卓越》,作者:GaussDB数据库。1.引言在很多业务场景下,数据库应用程序处理大量相同的SQL语句——只需更改SQL语句中的文字或变量值。例如:使用相同的SQL模板进行WHERE查询,SET 更新和VALUES 插入等操......
  • MySQL常用性能指标
    一些MySQL的常用性能指标,可以对此增加一些自定义指标到数据库的监控里,如zabbix或者prometheus,来更好的检测数据库的状态。MySQSL版本是5.7.19。因为是自己的测试环境,所以截图的一些指标很低,仅为大家展示查看参数的显示情况。mysql>selectversion();+-----------+|version()|......
  • mysql-utilities对比两个库数据一致性
    1.安装mysql-utilities首先yum源安装python,之后根据python版本下载安装mysql-connector-pythonyuminstallpythonpython--versionpython2.6.6下载地址:https://downloads.mysql.com/archives/c-python/rpm-ivhmysql-connector-python-2.1.6-1.el6.x86_64.rpmwhichpython之后......
  • MySQL 单表数据最大不要超过多少行?为什么?
    1背景作为在后端圈开车的多年老司机,是不是经常听到过,“mysql单表最好不要超过2000w”,“单表超过2000w就要考虑数据迁移了”,“你这个表数据都马上要到2000w了,难怪查询速度慢”这些名言民语就和“群里只讨论技术,不开车,开车速度不要超过120码,否则自动踢群”,只听过,没试过......
  • mysql 优化之开启慢查询并分析原因
      第一步.开启mysql慢查询方式一:修改配置文件my.ini增加几行:slow_query_log=on#开启慢查询日志slow_query_log_file=filename#指定日志文件保存路径,不指定的话默认在数据库文件目录下,名为hostname-slow.loglong_query_time=2#指定达到多少秒才算慢查询long-que......
  • mysql常见问题及排查步骤
    问题一:数据库连接串测试报错:Couldnotcreateconnectiontodatabaseserver.504错误遇到数据库问题的通用排查步骤确定数据库没有挂确定远程可以连接(前两个可以通过在本地连接验证)以上都OK,大概率服务器ip被限制访问,如果不是人为限制,在可以访问的服务器连上对应数据库,执行......
  • mysql8.x通过备份文件及binlog日志恢复数据
    问题简述记一次mysql数据库被误删(是整个库被删了)后的还原前提条件数据库版本为mysql8.x以上具有库被删除前的完整备份数据库开启binlog还原步骤第一步:通过完整备份还原被删的库注意事项:还原后切勿让其他用户连接,操作数据库。待使用binlog日志恢复数据后再对库进行操作,否......
  • MySQL到底是 join 性能好,还是in一下更快呢?
    事情是这样的,去年入职的新公司,之后在代码review的时候被提出说,不要写join,join耗性能还是慢来着,当时也是真的没有多想,那就写in好了,最近发现in的数据量过大的时候会导致sql慢,甚至sql太长,直接报错了。这次来浅究一下,到底是in好还是join好,仅目前认知探寻,有不对之处欢迎指正以下实验仅......
  • 记录一次报错,程序启动,MySql自动关闭
    关于初级程序员,对于安装mysql,以及配置可能会报几次错有时候虽然进行第二次安装成功,但是第一次的残留文件还在,可能引起报错在这里记录一次我的报错程序启动导致Mysql自动断开,需要手动打开1、打开任务管理器,打开详细信息,发现界面有两个mysqld.exe如果你是使用单程序连接,或者你......
  • 爬虫-mysql-工具
    MySQL数据库一、MySQL数据库的介绍1、发展史1996年,MySQL1.02008年1月16号Sun公司收购MySQL。2009年4月20,Oracle收购Sun公司。MySQL是一种开放源代码的关系型数据库管理系统(RDBMS),使用最常用的数据库管理语言--结构化查询语言(SQL)进行数据库管理。MySQL是开放源代码的,因此任......