【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
相关文章
|
9天前
|
SQL 缓存 关系型数据库
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴因未能系统梳理MySQL缓存机制而在美团面试中失利。为此,尼恩对MySQL的缓存机制进行了系统化梳理,包括一级缓存(InnoDB缓存)和二级缓存(查询缓存)。同时,他还将这些知识点整理进《尼恩Java面试宝典PDF》V175版本,帮助大家提升技术水平,顺利通过面试。更多技术资料请关注公号【技术自由圈】。
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
|
6天前
|
缓存 NoSQL 关系型数据库
mysql和缓存一致性问题
本文介绍了五种常见的MySQL与Redis数据同步方法:1. 双写一致性,2. 延迟双删策略,3. 订阅发布模式(使用消息队列),4. 基于事件的缓存更新,5. 缓存预热。每种方法的实现步骤、优缺点均有详细说明。
|
24天前
|
存储 缓存 索引
从底层数据结构和CPU缓存两方面剖析LinkedList的查询效率为什么比ArrayList低
本文详细对比了ArrayList和LinkedList的查询效率,从底层数据结构和CPU缓存两个方面进行分析。ArrayList基于动态数组,支持随机访问,查询时间复杂度为O(1),且CPU缓存对其友好;而LinkedList基于双向链表,需要逐个节点遍历,查询时间复杂度为O(n),且CPU缓存对其帮助不大。文章还探讨了CPU缓存对数组增删操作的影响,指出缓存主要作用于读取而非修改。通过这些分析,加深了对这两种数据结构的理解。
32 2
|
2月前
|
缓存 NoSQL 关系型数据库
MySQL与Redis缓存一致性的实现与挑战
在现代软件开发中,MySQL作为关系型数据库管理系统,广泛应用于数据存储;而Redis则以其高性能的内存数据结构存储特性,常被用作缓存层来提升数据访问速度。然而,当MySQL与Redis结合使用时,确保两者之间的数据一致性成为了一个重要且复杂的挑战。本文将从技术角度分享MySQL与Redis缓存一致性的实现方法及其面临的挑战。
138 2
|
3月前
|
存储 缓存 关系型数据库
查询缓存效果
【8月更文挑战第14天】
34 2
|
3月前
|
缓存 NoSQL Redis
一天五道Java面试题----第九天(简述MySQL中索引类型对数据库的性能的影响--------->缓存雪崩、缓存穿透、缓存击穿)
这篇文章是关于Java面试中可能会遇到的五个问题,包括MySQL索引类型及其对数据库性能的影响、Redis的RDB和AOF持久化机制、Redis的过期键删除策略、Redis的单线程模型为何高效,以及缓存雪崩、缓存穿透和缓存击穿的概念及其解决方案。
|
3月前
|
存储 缓存 NoSQL
微服务复杂查询之缓存策略
微服务复杂查询之缓存策略
|
3月前
|
缓存 关系型数据库 MySQL
【缓存大对决】Memcached VS MySQL查询缓存,谁才是真正的性能之王?
【8月更文挑战第24天】在现代Web应用中,缓存技术对于提升性能与响应速度至关重要。本文对比分析了Memcached与MySQL查询缓存这两种常用方案。Memcached是一款高性能分布式内存对象缓存系统,支持跨服务器共享缓存,具备灵活性与容错性,但受限于内存大小且不支持数据持久化。MySQL查询缓存内置在MySQL服务器中,简化了缓存管理,特别适用于重复查询,但功能较为单一且扩展性有限。两者各有所长,实际应用中可根据需求单独或结合使用,实现最佳性能优化。
106 0
|
3月前
|
缓存 Java 数据库连接
Hibernate 中的查询缓存是什么?
【8月更文挑战第21天】
35 0
|
23天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
56 3
Mysql(4)—数据库索引