【MySQL高级】查询缓存、合并表、分区表

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 【MySQL高级】查询缓存、合并表、分区表

[1] 查询缓存


  MySQL的缓存机制简单地说就是缓存SQL语句和查询的结果,如果运行相同的SQL语句,服务器会直接从缓存中取到结果,而不需要再去解析和执行SQL语句。

  查询缓存会存储最新数据,而不会返回过期数据。当数据被修改后,在查询缓存中的任何相关数据均被清除。

  查询缓存会存储最新数据,而不会返回过期数据。当数据被修改后,在查询缓存中的任何相关数据均被清除。

1.1 重要变量

  输入以下命令进行查看:

show variables like '%query_cache%';

image.png

  · have_query_cache用来设置是否支持查询缓存区,“YES”表示支持查询缓存区。

  · query_cache_limit用来设置 MySQL 可以缓存的最大结果集,大于此值的结果集不会被缓存,该参数默认值是1MB.

  · query_cache_min_res_unit用来设置分配内存块的最小体积,每次给查询缓存结果分配内存的大小,默认分配4096个字节,如果此值较小,那么会节省内存,但是这样会使系统频繁分配内存块。

  · query_cache_size用来设置查询缓存使用的总内存字节数,必须是1024字节的倍数。

  · query_cache_type用来设置是否启用查询缓存。如果设置为OFF,表示不进行缓存;如果设置为ON,表示除了SOL NO_CACHE的查询以外,缓存所有的结果;如果设置为DEMAND,表示仅缓存SQL_CACHE的查询。

  · query_cache_type用来设置是否启用查询缓存。如果设置为OFF,表示不进行缓存;如果设置为ON,表示除了SOL NO_CACHE的查询以外,缓存所有的结果;如果设置为DEMAND,表示仅缓存SQL_CACHE的查询。

1.2 相关知识

 1、查询缓存不会缓存引用了用户自定义函数、存储函数、用户自定义变量、临时表的查询。

 2、查询缓存只是发生在服务器第一次接收到SOL查询语句,然后把查询结果缓存起来,对于查询中的子查询、视图查询和存储过程查询都不能缓存结果,对于预存储语句同样也不能使用缓存。

 3、使用查询缓存有利也有弊。一方面,查询缓存可以使查询变得更加高效,改善了MySQL服务器的性能;另一方面,查询缓存本身也需要消耗系统IO资源,所以说,查询缓存也增加了服务器额外的开销,主要体现以下几个方面:

 3.1、MySQL服务器在进行查询之前首先会检测查询缓存是否存在相同的查询条目。

 3.2、MySQL服务器在进行查询操作时,如果缓存中没有相同的查询条目,会将查询的结果缓存到查询缓存,这个过程也需要开销系统资源。

 3.3、如果数据库表发生增加操作,MySQL服务器查询缓存中相对应的查询结果将会无效,这时同样需要消耗系统资源.

 4、除了注意以上问题可以提高查询缓存的命中率外,还可以通过分区表提高缓存的命中率。通常我们会遇到这样的问题,对于某张表某个时间段内的数据更新比较频繁,其他时间段查询和更新比较多,一旦数据表数据执行更新操作,那么查询缓存中的信息将会清空,查询级存的命中率不会很高。此时,可以考虑采用分区表,把某个时间段的数据存放在一个单独的分区表中,这样可以提高服务器的查询缓存的命中率。

[2] 合并表、分区表


2.1 合并表

  合并表相当于一个容器,里面包含了多个真实表。

 合并表(merge table)是一种早期的、简单的分区实现,和分区表相比有一些不同的限制,并且缺乏优化。分区表严格来说是一个逻辑上的概念,用户无法访问底层的各个分区,对用户来说分区是透明的。但是合并表允许用户单独访问各个子表。分区表和优化器的结合更紧密,这也是未来发展的趋势,而合并表则是一种将被淘汰的技术,在未来的版本中可能被删除。

2.2 分区表

  对用户来讲,分区表示一个独立的逻辑表,但是底层由多个物理子表组成。在底层系统来看,每个分区表都有一个使用#分割命名的表文件。

 分区表的目的:让某些特定查询减少相应时间; 在下列的场景中,分区可以起到非常大的作用:

 1. 表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数据,其他均是历史数据。

 2. 分区表的数据更容易维护。例如,想批量删除大量数据可以使用清除整个分区的方式。另外,还可以对一个独立分区进行优化、检查、修复等操作。

 3. 分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备。

 4. 可以使用分区表来避免某些特殊的瓶颈。例如:InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等。

 5. 如需要,还可以备份和恢复独立的分区,这在非常大的数据集的场景下效果非常好。

 假设我们要在一个非常大的表中查出一段时间的记录,这个表含有很多年的历史数据,而我们要查询的仅仅是最近几个月的数据,这大约有10亿条记录。使用全表扫描,那么速度肯定会很慢,使用索引会在空间和维护上有很大消耗,最终一个查询会有产生成千上万的随机I/O,应用程序就会因此僵死。

 所以这里我们使用分区表来解决这个问题。为了保证大数据的可拓展性,一般有下面两个策略:

 全量扫描数据,不要任何索引。使用简单的分区方式存放表,不要任何索引。

 索引数据,并分离热点。如果数据有明显的‘热点’,而且除了这部分数据,其他数据很少被访问到,那么可以将这部分热点数据单独放在这个分区中,让这个分区的数据能够有机会都缓存在内存中。这样查询就可以只访问一个很小的分区表,能够使用索引,也能够有效地使用缓存。


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
20天前
|
SQL 缓存 关系型数据库
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴因未能系统梳理MySQL缓存机制而在美团面试中失利。为此,尼恩对MySQL的缓存机制进行了系统化梳理,包括一级缓存(InnoDB缓存)和二级缓存(查询缓存)。同时,他还将这些知识点整理进《尼恩Java面试宝典PDF》V175版本,帮助大家提升技术水平,顺利通过面试。更多技术资料请关注公号【技术自由圈】。
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
|
12天前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
44 9
|
14天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
41 3
|
18天前
|
缓存 NoSQL 关系型数据库
mysql和缓存一致性问题
本文介绍了五种常见的MySQL与Redis数据同步方法:1. 双写一致性,2. 延迟双删策略,3. 订阅发布模式(使用消息队列),4. 基于事件的缓存更新,5. 缓存预热。每种方法的实现步骤、优缺点均有详细说明。
|
19天前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(5)作者——LJS[含MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
|
18天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
85 1
|
24天前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版,无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖,然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理,最后关闭相关资源。
|
20天前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
39 1
|
1月前
|
存储 缓存 索引
从底层数据结构和CPU缓存两方面剖析LinkedList的查询效率为什么比ArrayList低
本文详细对比了ArrayList和LinkedList的查询效率,从底层数据结构和CPU缓存两个方面进行分析。ArrayList基于动态数组,支持随机访问,查询时间复杂度为O(1),且CPU缓存对其友好;而LinkedList基于双向链表,需要逐个节点遍历,查询时间复杂度为O(n),且CPU缓存对其帮助不大。文章还探讨了CPU缓存对数组增删操作的影响,指出缓存主要作用于读取而非修改。通过这些分析,加深了对这两种数据结构的理解。
36 2
|
19天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
47 0