MySQL查询缓存优化示例详析

目录
  • 一、概述
  • 二、查询优化内容
    • 1、查询缓存的原理
    • 2、查询缓存的优缺点
    • 3、不能应用查询缓存的内容
    • 4、查询缓存相关的服务器变量
    • 5、SELECT语句的缓存控制
    • 6、查询缓存相关的状态变量
    • 7、查询的优化的检查路线
    • 8、命中率和内存使用率估算
    • 9、版本差异
  • 三、总结

一、概述

在日常使用数据库中，80%的数据请求都是查询，而余下的20%是更新或者增加数据。如何提升查询性能，便是提高数据库处理能力的关键。

二、查询优化内容

1、查询缓存的原理

查询的路线图：

缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写。

2、查询缓存的优缺点

优点

不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结果，提高查询性能

缺点

查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低效率查询缓存的使用，会增加检查和清理Query Cache中记录集的开销

3、不能应用查询缓存的内容

  • 查询语句中加了SQL_NO_CACHE参数
  • 查询语句中含有获得值的函数，包含:自定义函数，如：NOW() ，CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等
  • 对系统数据库的查询：mysql、information_schema 查询语句中使用SESSION级别变量或存储过程中的局部变量
  • 查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句，查询语句中类似SELECT …INTO 导出数据的语句
  • 对临时表的查询操作
  • 存在警告信息的查询语句
  • 不涉及任何表或视图的查询语句
  • 某用户只有列级别权限的查询语句
  • 事务隔离级别为Serializable时，所有查询语句都不能缓存

4、查询缓存相关的服务器变量

  • query_cache_min_res_unit：查询缓存中内存块的最小分配单位，默认4k，较小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足
  • query_cache_limit：单个查询结果能缓存的最大值，单位字节，默认为1M，对于查询结果过大而无法缓存的语句，建议使用SQL_NO_CACHE
  • query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报
  • query_cache_wlock_invalidate：如果某表被其它的会话锁定，是否仍然可以从查询缓存中返回结果，默认值为OFF，表示可以在表被其它会话锁定的场景中继续从缓存返回数据；ON则表示不允许
  • query_cache_type：是否开启缓存功能，取值为ON, OFF, DEMAND

5、SELECT语句的缓存控制

  • SQL_CACHE：显式指定存储查询结果于缓存之中
  • SQL_NO_CACHE：显式查询结果不予缓存
  • query_cache_type参数变量
  • query_cache_type的值为OFF或0时，查询缓存功能关闭
  • query_cache_type的值为ON或1时，查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存，此为默认值
  • query_cache_type的值为DEMAND或2时，查询缓存功能按需进行，显式指定SQL_CACHE的SELECT语句才会缓存；其它均不予缓存

6、查询缓存相关的状态变量

show gloable status like 'Qcache%' ;

7、查询的优化的检查路线

8、命中率和内存使用率估算

查询缓存中内存块的最小分配单位query_cache_min_res_unit ：

(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

查询缓存命中率 ：

Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%

查询缓存内存使用率：

(query_cache_size – qcache_free_memory) / query_cache_size * 100%

9、版本差异

在早期版本mysql均支持缓存，但是随着redis等内存型高性能的缓存技术兴起，mysql已经抛弃自己的缓存功能，mysql8.0以后不再支持缓存功能。

三、总结

MYSQL的缓存优化在早期版本可以起到一定的优化作用，最新的版本不再支持，缓存的功能而由其他的缓存服务来承担。

到此这篇关于MySQL查询缓存优化的文章就介绍到这了,更多相关MySQL查询缓存优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL查询缓存的小知识

    MySQL查询缓存的小知识

    前言 我们知道,缓存的设计思想在RDBMS数据库中无处不在,就拿号称2500w行代码,bug堆积如山的Oracle数据库来说,SQL的执行计划可以缓存在library cache中避免再次执行相同SQL发生硬解析(语法分析->语义分析->生成执行计划),SQL执行结果缓存在RESULT CACHE内存组件中,有效的将物理IO转化成逻辑IO,提高SQL执行效率. MySQL的QueryCache跟Oracle类似,缓存的是SQL语句文本以及对应的结果集,看起来是一个很棒的Idea,那为什么从My

  • 详解mysql查询缓存简单使用

    详解mysql查询缓存简单使用

    MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品.MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一. 当我们开启Mysql的查询缓存,当执行完全相同的SQL语句的时候,服务器就会直接从缓存中读取结果.当数据被修改, 之前的缓存会失效,所以修改比较频繁的表不适合做查询缓存. 一.查询

  • MySQL的查询缓存机制基本学习教程

    MySQL的查询缓存机制基本学习教程

    MySQL缓存机制简单的说就是缓存sql文本及查询结果,如果运行相同的sql,服务器直接从缓存中取到结果,而不需要再去解析和执行sql.如果表更改 了,那么使用这个表的所有缓冲查询将不再有效,查询缓存值的相关条目被清空.更改指的是表中任何数据或是结构的改变,包括INSERT.UPDATE. DELETE.TRUNCATE.ALTER TABLE.DROP TABLE或DROP DATABASE等,也包括那些映射到改变了的表的使用MERGE表的查询.显然,这对于频繁更新的表,查询缓存是不适合的,而

  • 清空mysql 查询缓存的可行方法

    对一条sql进行优化时,发现原本很慢的一条sql(将近1分钟) 在第二次运行时, 瞬间就完成了(0.00sec) 这是因为mysql对同一条sql进行了缓存,服务器直接从上次的查询结果缓存中读取数据,而不是重新分析.执行sql. 可通过如下方法清空查询缓存 reset query cache;

  • mysql 设置查询缓存

    可将如下语句 query_cache_size = 268435456 query_cache_type = 1 query_cache_limit = 1048576 存放到/etc/my.cnf文件的[mysqld]下 然后重启mysql数据库 service mysqld restart 就会启动mysql的缓存机制Query Cache. 在使用中,查询缓存会存储一个 SELECT 查询的文本与被传送到客户端的相应结果. 如果之后接收到一个同样的查询,服务器将从查询缓存中检索结果,而不是

  • mysql的查询缓存说明

    对mysql查询缓存从五个角度进行详细的分析:Query Cache的工作原理.如何配置.如何维护.如何判断查询缓存的性能.适合的业务场景分析. 工作原理 查询缓存的工作原理,基本上可以概括为:缓存SELECT操作或预处理查询(注释:5.1.17开始支持)的结果集和SQL语句:新的SELECT语句或预处理查询语句,先去查询缓存,判断是否存在可用的记录集,判断标准:与缓存的SQL语句,是否完全一样,区分大小写: 查询缓存对什么样的查询语句,无法缓存其记录集,大致有以下几类:查询语句中加了SQL_N

  • MySQL缓存的查询和清除命令使用详解

    Mysql 查询缓存 查询缓存的作用就是当查询接收到一个和之前同样的查询,服务器将会从查询缓存种检索结果,而不是再次分析和执行上次的查询.这样就大大提高了性能,节省时间. 1.配置查询缓存 修改配置文件,修改[mysqld]下的query_cache_size和query_cache_type(如果没有则添加).其中query_cache_size表示缓存的大小,而query_cache_type有3个值,表示缓存那种类  型的select结果集,query_cache_type各个值如下: 0

  • MySQL查询缓存优化示例详析

    MySQL查询缓存优化示例详析

    目录 一.概述 二.查询优化内容 1.查询缓存的原理 2.查询缓存的优缺点 3.不能应用查询缓存的内容 4.查询缓存相关的服务器变量 5.SELECT语句的缓存控制 6.查询缓存相关的状态变量 7.查询的优化的检查路线 8.命中率和内存使用率估算 9.版本差异 三.总结 一.概述 在日常使用数据库中,80%的数据请求都是查询,而余下的20%是更新或者增加数据.如何提升查询性能,便是提高数据库处理能力的关键. 二.查询优化内容 1.查询缓存的原理 查询的路线图: 缓存SELECT操作或预处理查询的

  • MySQL排序原理和案例详析

    MySQL排序原理和案例详析

    前言 排序是数据库中的一个基本功能,MySQL也不例外.用户通过Order by语句即能达到将指定的结果集排序的目的,其实不仅仅是Order by语句,Group by语句,Distinct语句都会隐含使用排序.本文首先会简单介绍SQL如何利用索引避免排序代价,然后会介绍MySQL实现排序的内部原理,并介绍与排序相关的参数,最后会给出几个"奇怪"排序例子,来谈谈排序一致性问题,并说明产生现象的本质原因. 1.排序优化与索引使用 为了优化SQL语句的排序性能,最好的情况是避免排序,合理利

  • MySQL六种约束的示例详解(全网最全)

    MySQL六种约束的示例详解(全网最全)

    目录 一.概述 二.约束演示 三.外键约束 1. 什么是外键约束 2. 不使用外键有什么影响 3. 添加外键的语法 4. 删除/更新行为 5. 演示删除/更新行为 四.主键id到底用自增好还是uuid好 五.实际开发尽量少用外键 一.概述 概念: 约束是作用于表中字段上的规则,用于限制存储在表中的数据. 目的: 保证数据库中数据的正确.有效性和完整性. 分类: 注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束. 二.约束演示 上面我们介绍了数据库中常见的约束,以及约束涉及到的关

  • CI框架数据库查询缓存优化的方法

    本文实例讲述了CI框架数据库查询缓存优化的方法.分享给大家供大家参考,具体如下: CI框架中有个比较好的查询优化,就是数据库缓存优化 1.开启缓存 //在application/config.php中开启 $db['default']['cache_on'] = TRUE; //在application/config.php中开启 $db['default']['cachedir'] = './cache'; //并在对应的目录中加一个可写缓存目录cache 2. 在对应的查询中开启缓存语句 /

  • Spring 缓存抽象示例详解

    Spring缓存抽象概述 Spring框架自身并没有实现缓存解决方案,但是从3.1开始定义了org.springframework.cache.Cache和org.springframework.cache.CacheManager接口,提供对缓存功能的声明,能够与多种流行的缓存实现集成. Cache接口为缓存的组件规范定义,包含缓存的各种操作集合: Cache接口下Spring提供了各种xxxCache的实现:如RedisCache,EhCacheCache , ConcurrentMapCa

  • MySQL系列之九 mysql查询缓存及索引

    MySQL系列之九 mysql查询缓存及索引

    目录 一.MySQL的架构 二.查询缓存(Query Cache) 哪些查询可能不会被缓存: 查询缓存相关的服务器变量: 查询缓存相关的状态变量: 三.索引 1.索引类型: 2.高性能索引策略: 3.索引的优化建议 4.索引的创建与删除 四.EXPLAIN命令 五.SQL语句性能优化 一.MySQL的架构 连接器 连接池,安全认证.线程池.连接限制.检查内存.缓存 SQL接口 DML.DDL SQL解析器,对SQL语句的权限检查.解析为二进制程序 优化器,优化访问路径 缓存cache,buffe

  • MySQL查询性能优化武器之链路追踪

    MySQL查询性能优化武器之链路追踪

    目录 前言 1. 查看optimizer trace配置 2. 开启optimizer trace 3. 线上问题复现 3. 使用optimizer trace 前言 MySQL优化器可以生成Explain执行计划,我们可以通过执行计划查看是否使用了索引,使用了哪种索引? 但是到底为什么会使用这个索引,我们却无从得知. 好在MySQL提供了一个好用的分析工具 — optimizer trace(优化器追踪),可以帮助我们查看优化器生成执行计划的整个过程,以及做出的各种决策,包括访问表的方法.各种

  • MySQL查询性能优化索引下推

    MySQL查询性能优化索引下推

    目录 前言 1. 索引下推的作用 2. 案例实践 3. 索引下推配置 4. 索引下推原理剖析 5. 索引下推应用范围 前言 前面已经讲了MySQL的其他查询性能优化方式,没看过可以去了解一下: MySQL查询性能优化七种方式索引潜水 MySQL查询性能优化武器之链路追踪 今天要讲的是MySQL的另一种查询性能优化方式 — 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本增加的特性. 1. 索引下推的作用 主要作用有两个: 减少回表查询的次数 减少存

  • MySQL数据操作管理示例详解

    目录 一.查看表和查看表的定义 二.删除表 三.创建表 四.表类型 五.修改表 六.完整性 1.数据完整性 2.使用约束实现数据完整性 3.在数据表上添加约束 4.删除约束 七.添加数据 八.修改数据 九.删除数据 十.SQL语句中的运算符 算数运算符 比较运算符 逻辑运算符 一.查看表和查看表的定义 SHOW TABLE 表名 DESC 表名; DESCRIBE 表名 二.删除表 DROP TABLE [IF EXISTS ] 表名; 如果表USER存在,删除表USER DROP TABLE

随机推荐