MySQL缓存机制详解(一)

时间:2023-02-05 21:36:17


本文章拿来学习用||参考资料:http://www.2cto.com/database/201308/236361.html

对MySql查询缓存及SQL Server过程缓存的理解及总结   一、MySql的Query Cache   1、Query Cache   MySQL Query Cache是用来缓存我们所执行的SELECT语句以及该语句的结果集。MySql在实现Query Cache的具体技术细节上类似典型的KV存储,就是将SELECT语句和该查询语句的结果集做了一个HASH映射并保存在一定的内存区域中。当客户端发起SQL查询时,Query Cache的查找逻辑是,先对SQL进行相应的权限验证,接着就通过Query Cache来查找结果。它不需要经过Optimizer模块进行执行计划的分析优化,更不需要发生同任何存储引擎的交互,减少了大量的磁盘IO和CPU运算,所以有时候效率非常高。   2、Query Cache设置参数   可以通过调整 MySQL的参数打开并设置它的Query Cache功能,主要有以下5个参数:   (1)、query_cache_limit:允许缓存的单条查询结果集的最大容量,默认是1MB,超过此参数设置的查询结果集将不会被缓存; (2)、query_cache_min_res_unit:设置查询缓存Query Cache每次分配内存的最小空间大小,即每个查询的缓存最小占用的内存空间大小; (3)、query_cache_size:设置 Query Cache 所使用的内存大小,默认值为0,大小必须是1024的整数倍,如果不是整数倍,MySQL 会自动调整降低最小量以达到1024的倍数; (4)、query_cache_type:控制 Query Cache 功能的开关,可以设置为0、1、2三种,意义分别如下:   a、0(OFF):关闭 Query Cache 功能,任何情况下都不会使用 Query Cache;   b、1(ON):开启 Query Cache 功能,但是当SELECT语句中使用SQL_NO_CACHE提示后,将不使用Query Cache;   c、2(DEMAND):开启Query Cache 功能,但是只有当SELECT语句中使用了SQL_CACHE 提示后,才使用Query Cache。 (5)、query_cache_wlock_invalidate:控制当有写锁定发生在表上的时刻是否先失效该表相关的Query Cache,如果设置为 1(TRUE),则在写锁定的同时将失效该表相关的所有Query Cache,如果设置为0(FALSE)则在锁定时刻仍然允许读取该表相关的Query Cache。   3、Query Cache和性能   任何事情过犹不及,尤其对于某些写频繁的系统,开启Query Cache功能可能并不能让系统性能有提升,有时反而会有下降。原因是MySql为了保证Query Cache缓存的内容和实际数据绝对一致,当某个数据表发生了更新、删除及插入操作,MySql都会强制使所有引用到该表的查询SQL的Query Cache失效。对于密集写操作,启用查询缓存后很可能造成频繁的缓存失效,间接引发内存激增及CPU飙升,对已经非常忙碌的数据库系统这是一种极大的负担。   4、其他   Query Cache因MySql的存储引擎不同而实现略有差异,比如MyISAM,缓存的结果集存储在OS Cache中,而最流行的InnoDB则放在Buffer Pool中。       二、SQL Server的Procedure Cache   SQL Server没有类似MySql的Query Cache机制,但是它有自己的缓存机制。SQL Server不会简单直接地缓存SQL查询结果集,而是缓存它所读取过的查询数据页(数据缓存Data Buffer),同时它还缓存执行计划(过程缓存Procedure Cache),下面就谈谈我们所熟知的过程缓存。   1、SQL执行过程   SQL语句在执行前首先需要被编译,接着需要通过SQL Server查询引擎进行优化,然后得到优化后的执行计划,最后SQL按照执行计划被执行。   2、过程缓存(Procedure Cache)   创建执行计划会占用CPU资源,当执行计划被创建后,SQL Server查询引擎默认会自动缓存执行计划。   对于整体相似,仅仅是参数不同的SQL语句,SQL Server可以重用缓存的执行计划。   但对于不同的SQL语句,SQL Server并不能重复使用以前的执行计划,而需要重新编译出一个新的执行计划,因为SQL Server查询引擎会自动缓存执行计划,每一个新的执行计划都会占用SQL Server的内存。   在SQL Server可用内存足够使用的情况下,查询引擎并不主动清除以前保存的查询计划。所以,某些情况下,一条相似的SQL语句,仅仅因为写法不同,而凭空多出了很多执行计划,对于相似的SQL,这些多余的执行计划白白地占据着内存,大大影响SQL Server中缓存的查询计划数目。   对于上面这种情况,如果限定了SQL Server最大可用内存,它将导致SQL Server可用内存减少,从而在执行查询时尤其是大的数据查询时与磁盘发生更多的内存页交换;如果没有设置最大可用内存,则SQL Server由于缓存了太多执行计划,从而使内存占用过大。   3、如何减少过程缓存 对于减少过程缓存的占用,主要是可以通过使用参数化查询。   参数化查询的关键是查询优化器将创建一个可以重用的缓存计划(SQL Server查询优化器将查询重新编写为一个参数化SQL语句),这个可重用的缓存计划消除了对这些类似SQL语句的每一次执行都创建一个缓存计划的需求。通过创建一个可重用计划,SQL Server就减少了存放类似的执行计划所需的内存使用。   对于开发人员,我们一般可以通过下面两种方式实现参数化查询:   (1)、使用存储过程执行SQL语句;   (2)、使用sp_executesql 方式执行SQL语句。   关于使用存储过程执行SQL,再说句题外话:对于存储过程一直以来有颇多争议,比如ORM派认为存储过程是完全面向过程的不易扩展不易维护的等等等等。根据我个人的开发经验,简单的几乎没有逻辑的存储过程我建议多用,但是复杂的存储过程一直以来都是BUG*,而且后期维护成本奇高(听我司架构师讲过,某重要业务系统的数据库有个八千多行的存储过程,两百多个变量,没有人敢动),逻辑最好通过应对剧烈变化的业务逻辑层来写。现在我们有了成熟的ORM,还有分层,开发中要绝对避免写过长且逻辑复杂的存储过程,否则面对变化,日积月累再出现几个八千行的存储过程也不是没有可能。

对MySql查询缓存及SQL Server过程缓存的理解及总结   一、MySql的Query Cache   1、Query Cache   MySQL Query Cache是用来缓存我们所执行的SELECT语句以及该语句的结果集。MySql在实现Query Cache的具体技术细节上类似典型的KV存储,就是将SELECT语句和该查询语句的结果集做了一个HASH映射并保存在一定的内存区域中。当客户端发起SQL查询时,Query Cache的查找逻辑是,先对SQL进行相应的权限验证,接着就通过Query Cache来查找结果。它不需要经过Optimizer模块进行执行计划的分析优化,更不需要发生同任何存储引擎的交互,减少了大量的磁盘IO和CPU运算,所以有时候效率非常高。   2、Query Cache设置参数   可以通过调整 MySQL的参数打开并设置它的Query Cache功能,主要有以下5个参数:   (1)、query_cache_limit:允许缓存的单条查询结果集的最大容量,默认是1MB,超过此参数设置的查询结果集将不会被缓存; (2)、query_cache_min_res_unit:设置查询缓存Query Cache每次分配内存的最小空间大小,即每个查询的缓存最小占用的内存空间大小; (3)、query_cache_size:设置 Query Cache 所使用的内存大小,默认值为0,大小必须是1024的整数倍,如果不是整数倍,MySQL 会自动调整降低最小量以达到1024的倍数; (4)、query_cache_type:控制 Query Cache 功能的开关,可以设置为0、1、2三种,意义分别如下:   a、0(OFF):关闭 Query Cache 功能,任何情况下都不会使用 Query Cache;   b、1(ON):开启 Query Cache 功能,但是当SELECT语句中使用SQL_NO_CACHE提示后,将不使用Query Cache;   c、2(DEMAND):开启Query Cache 功能,但是只有当SELECT语句中使用了SQL_CACHE 提示后,才使用Query Cache。 (5)、query_cache_wlock_invalidate:控制当有写锁定发生在表上的时刻是否先失效该表相关的Query Cache,如果设置为 1(TRUE),则在写锁定的同时将失效该表相关的所有Query Cache,如果设置为0(FALSE)则在锁定时刻仍然允许读取该表相关的Query Cache。   3、Query Cache和性能   任何事情过犹不及,尤其对于某些写频繁的系统,开启Query Cache功能可能并不能让系统性能有提升,有时反而会有下降。原因是MySql为了保证Query Cache缓存的内容和实际数据绝对一致,当某个数据表发生了更新、删除及插入操作,MySql都会强制使所有引用到该表的查询SQL的Query Cache失效。对于密集写操作,启用查询缓存后很可能造成频繁的缓存失效,间接引发内存激增及CPU飙升,对已经非常忙碌的数据库系统这是一种极大的负担。   4、其他   Query Cache因MySql的存储引擎不同而实现略有差异,比如MyISAM,缓存的结果集存储在OS Cache中,而最流行的InnoDB则放在Buffer Pool中。       二、SQL Server的Procedure Cache   SQL Server没有类似MySql的Query Cache机制,但是它有自己的缓存机制。SQL Server不会简单直接地缓存SQL查询结果集,而是缓存它所读取过的查询数据页(数据缓存Data Buffer),同时它还缓存执行计划(过程缓存Procedure Cache),下面就谈谈我们所熟知的过程缓存。   1、SQL执行过程   SQL语句在执行前首先需要被编译,接着需要通过SQL Server查询引擎进行优化,然后得到优化后的执行计划,最后SQL按照执行计划被执行。   2、过程缓存(Procedure Cache)   创建执行计划会占用CPU资源,当执行计划被创建后,SQL Server查询引擎默认会自动缓存执行计划。   对于整体相似,仅仅是参数不同的SQL语句,SQL Server可以重用缓存的执行计划。   但对于不同的SQL语句,SQL Server并不能重复使用以前的执行计划,而需要重新编译出一个新的执行计划,因为SQL Server查询引擎会自动缓存执行计划,每一个新的执行计划都会占用SQL Server的内存。   在SQL Server可用内存足够使用的情况下,查询引擎并不主动清除以前保存的查询计划。所以,某些情况下,一条相似的SQL语句,仅仅因为写法不同,而凭空多出了很多执行计划,对于相似的SQL,这些多余的执行计划白白地占据着内存,大大影响SQL Server中缓存的查询计划数目。   对于上面这种情况,如果限定了SQL Server最大可用内存,它将导致SQL Server可用内存减少,从而在执行查询时尤其是大的数据查询时与磁盘发生更多的内存页交换;如果没有设置最大可用内存,则SQL Server由于缓存了太多执行计划,从而使内存占用过大。   3、如何减少过程缓存 对于减少过程缓存的占用,主要是可以通过使用参数化查询。   参数化查询的关键是查询优化器将创建一个可以重用的缓存计划(SQL Server查询优化器将查询重新编写为一个参数化SQL语句),这个可重用的缓存计划消除了对这些类似SQL语句的每一次执行都创建一个缓存计划的需求。通过创建一个可重用计划,SQL Server就减少了存放类似的执行计划所需的内存使用。   对于开发人员,我们一般可以通过下面两种方式实现参数化查询:   (1)、使用存储过程执行SQL语句;   (2)、使用sp_executesql 方式执行SQL语句。   关于使用存储过程执行SQL,再说句题外话:对于存储过程一直以来有颇多争议,比如ORM派认为存储过程是完全面向过程的不易扩展不易维护的等等等等。根据我个人的开发经验,简单的几乎没有逻辑的存储过程我建议多用,但是复杂的存储过程一直以来都是BUG*,而且后期维护成本奇高(听我司架构师讲过,某重要业务系统的数据库有个八千多行的存储过程,两百多个变量,没有人敢动),逻辑最好通过应对剧烈变化的业务逻辑层来写。现在我们有了成熟的ORM,还有分层,开发中要绝对避免写过长且逻辑复杂的存储过程,否则面对变化,日积月累再出现几个八千行的存储过程也不是没有可能。

作者介绍:半路学IT,做开发3年,先就职在一家共享单车公司,做后台开发!

我开了一个公众号,欢迎各位有志同道合朋友,关注!不定期分享干活,和我得故事!

MySQL缓存机制详解(一)

MySQL缓存机制详解(一)的更多相关文章

  1. 浏览器 HTTP 协议缓存机制详解

    最近在准备优化日志请求时遇到了一些令人疑惑的问题,比如为什么响应头里出现了两个 cache control.为什么明明设置了 no cache 却还是发请求,为什么多次访问时有时请求里带了 etag, ...

  2. nginx平台初识(二) 浏览器 HTTP 协议缓存机制详解

    1.缓存的分类 缓存分为服务端侧(server side,比如 Nginx.Apache)和客户端侧(client side,比如 web browser). 服务端缓存又分为 代理服务器缓存 和 反 ...

  3. PHP缓存机制详解

    一,PHP缓存机制详解 我们可以使用PHP自带的缓存机制来完成页面静态化,但是仅靠PHP自身的缓存机制并不能完美的解决页面静态化,往往需要和其他静态化技术(通常是伪静态技术)结合使用. output ...

  4. 二,PHP缓存机制详解

    一,PHP缓存机制详解 我们可以使用PHP自带的缓存机制来完成页面静态化,但是仅靠PHP自身的缓存机制并不能完美的解决页面静态化,往往需要和其他静态化技术(通常是伪静态技术)结合使用. output ...

  5. hibernate缓存机制详解

    hiberante面试题—hibernate缓存机制详解   这是面试中经常问到的一个问题,可以按照我的思路回答,准你回答得很完美.首先说下Hibernate缓存的作用(即为什么要用缓存机制),然后再 ...

  6. 浏览器 HTTP 协议缓存机制详解--网络缓存决策机制流程图

    1.缓存的分类 2.浏览器缓存机制详解 2.1 HTML Meta标签控制缓存 2.2 HTTP头信息控制缓存 2.2.1 浏览器请求流程 2.2.2 几个重要概念解释 3.用户行为与缓存 4.Ref ...

  7. mysql锁机制详解

    前言 大概几个月之前项目中用到事务,需要保证数据的强一致性,期间也用到了mysql的锁,但当时对mysql的锁机制只是管中窥豹,所以本文打算总结一下mysql的锁机制. 本文主要论述关于mysql锁机 ...

  8. RecyclerView 缓存机制详解

    一 前言 RecyclerView据官方的介绍,该控件用于在有限的窗口中展示大量数据集,其实这样功能的控件我们并不陌生,例如:ListView.GridView.RecyclerView可以用来代替传 ...

  9. 5分钟看懂系列:HTTP缓存机制详解

    原创文章首发于公众号:「码农富哥」,欢迎收藏和关注,如转载请注明出处! 什么是HTTP缓存 HTTP 缓存可以说是HTTP性能优化中简单高效的一种优化方式了,缓存是一种保存资源副本并在下次请求时直接使 ...

随机推荐

  1. 51nod 最小周长

    1283 最小周长 题目来源: Codility 基准时间限制:1 秒 空间限制:131072 KB 分值: 5 难度:1级算法题  收藏  关注 一个矩形的面积为S,已知该矩形的边长都是整数,求所有 ...

  2. 安卓中的Model-View-Presenter模式介绍

    转载自:http://www.jcodecraeer.com/a/anzhuokaifa/androidkaifa/2015/0425/2782.html 英文原文:Introduction to M ...

  3. C4.5(决策树)

    C4.5是一系列用在机器学习和数据挖掘的分类问题中的算法.它的目标是监督学习:给定一个数据集,其中的每一个元组都能用一组属性值来描述,每一个元组属于一个互斥的类别中的某一类.C4.5的目标是通过学习, ...

  4. 【代码笔记】iOS-多张图片合成一张

    代码: RootViewController.m #import "RootViewController.h" @interface RootViewController () @ ...

  5. JQGrid 参数、属性API

    JQGrid是一个在jquery基础上做的一个表格控件,以ajax的方式和服务器端通信. JQGrid Demo 是一个在线的演示项目.在这里,可以知道jqgrid可以做什么事情. 下面是转自其他人b ...

  6. Python:面向对象

    面向过程:根据业务逻辑从上到下写垒代码 面向对象:对函数进行分类和封装 函数式:将某功能代码封装到函数中,日后便无需重复编写,仅调用函数即可 类:用来描述具有相同属性和方法的对象的集合,定义了该集合中 ...

  7. MD5加密解密

    方法一 首先,先简单介绍一下MD5 MD5的全称是message-digest algorithm 5(信息-摘要算法,在90年代初由mit laboratory for computer scien ...

  8. HDU 5616 Jam's balance

    背包.dp[i]=1表示i这种差值能被组合出来,差值有负数,所以用sum表示0,0表示-sum,2*sum表示sum. 询问X的时候,只需看dp[sum+X]或者dp[sum-X]是否有一个为1,注意 ...

  9. Cocoa编程之IBAction和IBOutlet含义

    IBAction / IBOutlet / IBOutletCollection 时间 2014-05-05 15:00:00  NSHipster原文  http://nshipster.com/i ...

  10. nginx 添加代理

    1 确认安装路径 ps aux | grep nginx 2.进入配置目录 3.使用vi编辑配置文件 如果是新增,可以参考其他配置,5yy复制相应行,p粘贴,然后修改内容后:wq保存退出 4.验证配置 ...