MySQL(三)SQL优化、Buffer pool、Change buffer

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL(三)SQL优化、Buffer pool、Change buffer



SQL优化

MySQL可以通过B+树来减少索引查处时的IO磁盘次数,但是每次查找、新增都去做磁盘IO的话,如果频繁操作还是会遇到瓶颈。因此就有Buffer pool和Change buffer的出现。

Buffer pool

目的:buffer pool是为了减少磁盘IO的读写次数。

假如没有buffer pool,则每次查询都会从磁盘中读取,进行IO操作。

因此会在内存中专门取一大块区域用作Buffer pool用来保存一些已经读过的页和周围的页数据(空间局部性),这样的的话当下次查询数据时会在Buffer pool查询是否存在需要的页,减少读写IO次数。

当修改数据时,同样会先修改buffer pool中的数据,被修改的页称为脏页,一般会采用redo log持久化机制,将脏页统一写入到磁盘中。

buffer pool结构

buffer pool涉及到三个链表:

free list 组织 buffer pool 中未使用的缓存页;

flush list 组织buffer pool 中脏页,也就是待刷盘的页;

lru list 组织 buffer pool 中冷热数据,当 buffer pool 没有空闲页,将从 lru list 中最久未使用的数据进行淘汰;

Buffer pool&LRU算法

buffer pool 优化了LRU方法。因为MySQL有预读机制,每次将缓存页加载进Buffer pool时,会将目标缓存页附近的数据页也加进来。就是磁盘读取的空间局部性原理。

这样会导致一个问题,可能被预读加进来的数据页,再之后可能长时间就没访问过了。

优化一、冷热数据

Buffer pool中5/8的区域为new sublist,3/8的区域为old sublist。

  • 因此数据加入pool缓冲池时,优先进入old sublist,页被访问时会进入new sublist,在free list
    不足时,优先刷入那些old sublist区域的进入磁盘,再清空pool区域。
  • 所以那些被预读加进来的数据页,如果没被访问,就会一直呆在old区域,等到空间不足时,会被提前清空。这样就能解决预读失效的问题。

优化二、时间阈值

页被访问,且在old sublist停留时间超过配置阈值的,才进入new sublist,以解决批量数据访问,大量热数据淘汰的问题。通常阈值设置为1s。

为什么是1s?

因为预读机制将加载进来的数据页通常是会在1s之内就访问,通常1s之内就会访问这些加载进来的数据页,可能1s之后就不会再被访问了。

因此如果这时将这些访问了的缓存页就加进new区域也不太好,因此通过配置时间阈值。在1s之后在被访问的数据页才进入new区域,并放入new区域的头部,说明后续可能还会被访问到。而1s之前被访问的数据页就不变。

详细内容参考:https://www.jianshu.com/p/7cb6d7d59064

Change buffer

假如一个需要修改的页数据没有在buffer pool中,我们需要怎么操作:

  1. 将数据页调入到buffer pool中, 一次随机磁盘IO
  2. 更新buffer pool中的数据 , 一次内存IO
  3. 将修改写入redo log,一次磁盘顺序写IO

看起来操作还行,但是如果在写多读少的场景下的话,我们有更好的方法,也就是innodb的change buffer。


Change buffer工作原理

Change buffer 缓存非唯一索引的数据变更(DML 操作,只记录操作,不记录结果),当访问这个数据页或者定期时间到达Change buffer 中的数据将会异步 merge 到磁盘当中;

因此对于刚刚的需要修改的页数据没有在buffer pool中,使用change buffer的操作:

  1. 将修改的操作写入到change buffer中 ,一次内存IO
  2. 写入日志到redolog中,等待触发merge,一次磁盘顺序写IO

可以看到使用change buffer的话在写多读少的场景下会节省最耗时的磁盘IO读写的次数。

change buffer不适用的场景

  • 唯一索引的场景:唯一索引需要判断是否冲突,也就是是否唯一,这个判断需要全局扫描,需要从磁盘读数据,change buffer也就没什么用了。
  • 写少读多的场景:因为修改数据页后读取该页会触发merge,而我们的目的就是为了把多次数据页的修改通过一次merge更新到磁盘中,如果读数据的场景多了,那么merge的次数多,也不会减少IO操作次数了。

总结

当数据不在buffer pool中,修改页数据后然后读取数据的步骤,理解一下buffer pool怎么和change buffer工作的:

修改操作:

在buffer poo匹配不到页数据;

在change buffer中记录该数据的修改操作;

查询操作:

在buffer pool 中匹配不到页数据;

change buffer中读取做merge操作,放回buffer pool中;

在磁盘中读取页数据;

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
2月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
3月前
|
SQL 关系型数据库 MySQL
Go语言数据库编程:使用 `database/sql` 与 MySQL/PostgreSQL
Go语言通过`database/sql`标准库提供统一数据库操作接口,支持MySQL、PostgreSQL等多种数据库。本文介绍了驱动安装、连接数据库、基本增删改查操作、预处理语句、事务处理及错误管理等内容,涵盖实际开发中常用的技巧与注意事项,适合快速掌握Go语言数据库编程基础。
209 62
|
4月前
|
SQL 数据采集 关系型数据库
实现MySQL与SQL Server之间数据迁移的有效方法
总的来说,从MySQL到SQL Server的数据迁移是一个涉及到很多步骤的过程,可能会遇到各种问题和挑战。但只要精心规划、仔细执行,这个任务是完全可以完成的。
268 18
|
5月前
|
SQL 关系型数据库 MySQL
【MySQL】SQL分析的几种方法
以上就是SQL分析的几种方法。需要注意的是,这些方法并不是孤立的,而是相互关联的。在实际的SQL分析中,我们通常需要结合使用这些方法,才能找出最佳的优化策略。同时,SQL分析也需要对数据库管理系统,数据,业务需求有深入的理解,这需要时间和经验的积累。
149 12
|
5月前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
|
5月前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
6月前
|
SQL Oracle 关系型数据库
【YashanDB知识库】如何将mysql含有group by的SQL转换成崖山支持的SQL
本文探讨了在YashanDB(崖山数据库)中执行某些SQL语句时出现的报错问题,对比了MySQL的成功执行结果。问题源于SQL-92标准对非聚合列的严格限制,要求这些列必须出现在GROUP BY子句中,而SQL:1999及更高版本允许非聚合列直接出现在选择列中。YashanDB和Oracle遵循SQL-92标准,因此会报错。文章提供了两种解决方法:使用聚合函数处理非聚合列,或将GROUP BY与ORDER BY拆分为两层查询。最后总结指出,SQL-92标准更为严谨合理,建议开发者遵循此规范以避免潜在问题。
|
3月前
|
人工智能 运维 关系型数据库
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
585 1
|
4月前
|
负载均衡 算法 关系型数据库
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
|
2月前
|
SQL 存储 关系型数据库
MySQL功能模块探秘:数据库世界的奇妙之旅
]带你轻松愉快地探索MySQL 8.4.5的核心功能模块,从SQL引擎到存储引擎,从复制机制到插件系统,让你在欢声笑语中掌握数据库的精髓!

推荐镜像

更多