首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

MySQL的内存结构与物理结构

作者：oz法卡山英雄营 | 来源：互联网 | 2024-10-13 19:18
从MySQL的物理结构和内存结构开始了解整个MySQL的运行机制，其中有几个特别重要的概念，也是平时工作中更加关注的地方，如：binlog、redolog、表空间、慢查询日志、My


“从MySQL的物理结构和内存结构开始了解MySQL的运行机制”



MySQL的数据存储结构主要分两个方面：物理存储结构与内存存储结构，作为数据库，所有的数据最后一定要落到磁盘上，才能完成持久化的存储。内存结构为了实现提升数据库整体性能，主要用于存储临时数据和日志的缓冲。本文主要讲MySQL的物理结构，以及MySQL的内存结构，对于存储引擎也主要以InnoDB为主。



01

—


MySQL的物理结构


上图的 On-Disk Structures 主要是InnoDB存储引擎的磁盘结构，对于MySQL数据库来说，还包括一些文件、日志、表结构存储结构等。
文件主要包括参数文件、日志文件、表结构文件、存储引擎文件等，存储引擎文件主要包括表空间文件、redo log等。
参数文件指的是MySQL实例启动时，会先去读取的参数配置文件，配置内容包含各种文件的位置，一些初始化参数，这些参数定义了某种内存结构的大小设置，还包括一些其他配置，如：主从配置等。
日志文件记录了MySQL数据库的各种类型活动，这些日志都是在Server层实现的，是各种存储引擎都会有的日志文件。包括错误日志、binlog、慢查询日志、查询日志：
错误日志主要用于查看MySQL出现错误时，用来排查问题使用，是DBA出问题时，首要关注的日志。
慢查询日志是用来记录低于阈值的SQL语句，这个阈值通过long_query_time设置，默认是10秒，通过查询慢查询日志，也可以得到一些关于数据库需要优化的信息，比如需要某个语句执行扫描了全表，没有走到索引。开发人员可以结合场景去优化SQL语句或者优化索引的设置等。
查询日志记录了所有对MySQL数据库请求的信息，不论这些请求是否得到了正确的执行。
binlog是server层维护的一种二进制日志，与后面要说的InnoDB存储引擎层的redo log不同，主要用来记录对MySQL数据更新或潜在发生更新的SQL语句，不包括Select和Show这类操作。binlog默认是不开启的，测试表明开启确实会影响MySQL的性能。不过通过binlog可以实现数据的备份同步和数据恢复，同这么强大的作用比起来，损失这点性能也是值得的，所以建议开启。
当使用支持事务的存储引擎时，未提交事务的binlog会存储到binlog_cache中，而提交的事务，要根据参数来确定从缓冲刷到磁盘的时间。
根据binlog_cache_size设置缓冲池大小：

show variables like 'binlog_cache_size';

根据sync_binlog设置刷盘的时间：

show variables like 'sync_binlog';

  • 如果设置为0，表示MySQL不控制binlog的刷盘，由操作系统的文件系统来控制它的属性；

  • 如果设置不为0的值，表示每【设置值】次事务，刷新binlog缓冲池到磁盘；

如果设置为1，当发生断电、系统崩溃时，最多丢失一个事务的更新。如果设置大于1，在系统故障时，则可能会有一部分已提交的但还未来得及刷盘的数据丢失。因此，sync_binlog 设置为1需要每次都刷盘，对性能有一定影响，同时也是最安全。现在高版本的MySQL默认设置 sync_binlog 为1，不过可以设置成100以内的值，牺牲一定一致性来获取更多的性能。
binlog记录的事件支持三种格式：

  • STATEMENT：基于SQL语句的复制（statement-based replication, SBR）

  • ROW：基于行的复制（row-based replication, RBR）

  • MIXED：混合模式复制（mixed-based replication, MBR）


InnoDB存储引擎将所有数据逻辑地存放在一个空间，我们称之为表空间（tablespace）。表空间由段（segment）、区（extent）、页（page）组成，大致存储结构如下图所示：

  • 段：常见的段有数据段（B+树页节点）、索引段（B+树非页节点，索引节点）、回滚段等。

  • 区：区是由64个连续的页组成的，每个页大小为16KB，即每个区大约为1MB。

  • 页：页是innodb磁盘管理最小的单位，innodb每个页的大小是16K，且不可更改。常见的页有数据页、Undo页、事务数据页、系统页等

InnoDB页类型为B-tree node类型，存放的实际就是行数据了，File Header用于记录Page的头信息，其中比较重要的就是Fil_PAGE_PREV和FIL_PAGE_NEXT字段，通过这两个字段可以找到该页的上一页和下一页，实际上通过这两个字段将形成一个双向链表。Page Header用来记录Page状态信息。接下来Infimum和Supremum是两个伪行记录，Infimum（下确界）记录比该页中任何主键值都要小的值，Supremum （上确界）记录比该页中任何主键值都要大的值，这个伪记录分别构成了页中记录的边界。UserRecords中存放的就是数据行记录。Free Space存放的是空闲空间，被删除的行会被记录成空闲空间。Page  Directory记录着与二叉查找相关的信息。File Trailer存储用于检测数据完整性的校验。

InnoDB存储引擎是按照行来存储数据的，目前版本的MySQL默认行记录格式为Compact，还包括Redundant。下图为Compact行记录格式，每行的数据除了下图的定义的列外，还包括两个隐藏的列，事务ID列和回滚指针列，如果没有定义主键，还会有一个默认的Rowid列。
表空间可以看成是InnoDB存储引擎逻辑结构的最高层，如果不开启 innodb_file_per_table 参数设置的话，存储引擎会有一个共享表空间，ibdata1，所有数据都会存在这个表空间内，如果开启了 innodb_file_per_table 参数配置，则会为每张表单独放到一个表空间内。这里需要注意的是，每个独立的表空间内存放的只是数据、索引、插入缓冲等，其他类的数据回滚信息、插入缓冲索引、double write buffer还是存放在共享表空间内。
查看 innodb_file_per_table 参数配置，是否启用每个表为单独放在一个表空间内：

show variables like '%per_table%';

通常MySQL默认为不启用，也就是使用共享表空间ibdata1文件，共享表空间的特点是可以指定多个文件来作为共享表空间文件。可以设置为autoextend，当表空间不足时，自动扩张。

show variables like 'innodb_data%'

共享表空间还有一个问题是，无法通过删除数据文件方式，来减少表空间大小，只能通过 mysqldump 工具备份所有数据，再删除所有表空间数据，再重新导入备份数据，这样的方式重构表空间来减少。独立表空间则没有这个问题，可以实现单表在不同数据库中移动，性能也会更高一些。File-Per-Table tablespace 每个表独立的表空间，一般会在MySQL根目录上，以库名为目录的文件夹内，包括表结构定义文件（.frm）和表数据文件（.ibd）。
重做日志文件也叫redo log，不同于binlog是MySQL server层实现的，redo log是InnoDB存储引擎实现的。redo log以日志文件组的形式存在，InnoDB以循环的方式先写ib-logfile1，写完再写ib-logfile2，一组文件都写完了，再回到ib-logfile1，也就会覆盖之前写的数据。

redo log是记录的都是关于每个页（Page）的更改的物理情况，InnoDB要读取或修改数据是从磁盘读取到内存中进行的，然后再通过一套完整的策略来刷回磁盘，这其中并不是每次都要刷回磁盘的，因为会产生大量的随机I/O，InnoDB会优化随机I/O为顺序I/O。不过存在一种情况，就是当刷盘之前，数据库出现问题，那数据页还没来得及刷盘，数据会丢失，redo log就是解决这个问题的。在数据库重启恢复时，用来恢复还未刷到磁盘的数据页。
redo log包含两个部分，一个是日志缓冲，一个是磁盘上的redo log文件。redo log都是先写到日志缓冲内（redo log buffer），然后在写入日志文件，根据 innodb_flush_log_at_trx_commit 参数可以设定缓冲写到磁盘的机制。

  • 设置为0时，每次事务提交都把redo log留在redo log buffer中，等待master thread每秒定时刷到redo log中；

  • 设置为1时，每次事务提交都将redo log持久化到磁盘；

  • 设置为2时，每次事务提交只是把redo log写到磁盘的page上，等待数据刷盘；

redo log文件组是环形的结构，设置其大小要综合参考脏页刷新与每次重启恢复数据的时长，设置过小，事务高峰期，可能会使部分没落盘的数据页的redo log被覆盖；设置过大，可能会导致重启之后，数据恢复时间过长，也浪费磁盘空间。对于redo log的介绍，后续文章将会继续深入讨论其机制和原理。
单独依靠redo log并不一定能恢复到故障之前的状况，当一个事务还未提交时，可能redo log已经写完了，但其实这部分数据应该需要回滚。所以，这就需要一种回滚机制，也就是undo log。undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。
在InnoDB存储引擎中，undo log用于实现回滚和多版本控制-MVCC，其原理大致为，当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。当用到行版本控制的时候，当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。



02

—


MySQL的内存结构



InnoDB存储引擎使用Buffer Pool在内存中缓存表数据和索引，处理数据时可以直接操作缓冲池的数据，提升InnoDB的处理速度。缓冲池的数据一般按照页格式，每个页包含多行数据，缓冲池可以看成是页面链表，并且使用LRU（last recent used）算法，来管理缓冲池的数据列表。当需要新空间将新页面加到缓冲池时，将会淘汰最近最少使用的数据。

MySQL提供了多个关于缓冲池的配置参数，

  • innodb_buffer_pool_instances 与 innodb_buffer_pool_size 配置缓冲池的实例和缓冲池大小：通过配置多个缓冲池可以减少不同线程的竞争，提升并发度。通常在专用服务器上，80%的物理内存会分配给Buffer Pool。

  • innodb_buffer_pool_chunk_size  配置缓冲池的块大小：当增加或减少innodb_buffer_pool_size时，操作以块形式执行，块大小由此参数决定，默认为128M。

  • innodb_max_dirty_pages_pct 配置脏页比例：根据设置的缓冲池中脏页比例，来触发将脏页刷盘的时机。另外，InnoDB也根据redo log的生成速度和刷新频率，来触发刷盘时机。

  • innodb_read_ahead_threshold 与 innodb_random_read_ahead 预读参数配置：预读是指一次I/O请求磁盘中某页中的数据时，会同时同步取出相邻页面的数据，缓存到缓冲池。因为，InnoDB认为这些页面的数据大概率也将会被读取，从而来提升I/O性能。包括线性预读和随机预读。

Change Buffer用来缓存不在缓冲池中的辅助索引页(非唯一索引)的变更。这些缓存的的变更，可能由INSERT、UPDATE或DELETE操作产生，当读操作将这些变更的页从磁盘载入缓冲池时，InnoDB引擎会将change buffer中缓存的变更跟载入的辅助索引页合并。
不像聚簇索引，辅助索引通常不是唯一的，并且辅助索引的插入顺序是相对随机的。若不用change buffer，那么每有一个页产生变更，都要进行I/O操作来合并变更。使用change buffer可以先将辅助索引页的变更缓存起来，当这些变更的页被其他操作载入缓冲池时再执行merge操作，这样可以减少大量的随机I/O。change buffer可能缓存了一个页内的多条记录的变更，这样可以将多次I/O操作减少至一次。
在内存中，change buffer占据缓冲池的一部分。在磁盘上，change buffer是系统表空间的一部分，以便数据库重启后缓存的索引变更可以继续被缓存
自适应哈希索引是InnoDB表通过在内存中构造一个哈希索引来加速查询的优化技术，此优化只针对使用 '=' 和 'IN' 运算符的查询。MySQL会监视InnoDB表的索引查找，若能通过构造哈希索引来提高效率，那么InnoDB会自动为经常访问的辅助索引页建立哈希索引。
这个哈希索引总是基于辅助索引(B+树结构)来构造。MySQL通过索引键的任意长度的前缀和索引的访问模式来构造哈希索引。InnoDB只为某些热点页构建哈希索引。
Log Buffer用来缓存要写入磁盘日志文件的内存缓冲区域，该区域大小由 innodb_log_buffer_size 参数定义，默认16MB。
Doublewrite Buffer是位于系统表空间中的存储区域，其工作原理是：在将缓冲池中的页写入磁盘上对应位置之前，先将缓冲池中的页copy到内存中的doublewrite buffer，之后再分两次，每次1M，顺序地将内存中doublewrite buffer中的页写入系统表空间中的doublewrite区域，然后立即调用系统fsync函数，同步数据到磁盘文件中，避免缓冲写带来的问题。在完成doublewrite页的写入之后，再将内存上doublewrite buffer中的页写入到自己的表空间文件。如果当页面写入磁盘时，发生了数据库宕机，会导致“写失效”，重启之后，可以通过Doublewrite Buffer来恢复故障前要写的Page数据。


InnoDB存储引擎支持事务、MVCC、故障恢复等特性，要理解InnoDB的存储结构，我总结我的理解关键点如下：
磁盘I/O，磁盘的读写是操作系统实现的，结合磁盘存取数据的过程，来看InnoDB的逻辑存储结构，在磁盘和内存中都以页（Page）为操作的基本单元，页内元素才是基本的行结构数据；
基于B+ Tree结构来组织数据的存储，叶节点和非叶节点存储数据和索引，并且通过主键构建整个树结构，便于通过主键索引遍历数据；
二阶段提交、undo log等机制完善InnoDB事务的特性；
缓冲池、索引等机制的引入提升MySQL性能；
由redo log、binlog，错误日志来实现数据库故障恢复、备份和异常情况的记录。



  1. https://dev.mysql.com/doc/refman/5.5/en/innodb-on-disk-structures.html

  2. https://dev.mysql.com/doc/refman/5.7/en/innodb-in-memory-structures.html

  3. https://dev.mysql.com/doc/refman/5.7/en/innodb-doublewrite-buffer.html

  4. 《MySQL技术内幕：InnoDB存储引擎》

  5. https://www.cnblogs.com/liuhao/p/3714012.html

  6. https://my.oschina.net/u/553773/blog/792144

  7. https://github.com/mysql/mysql-server/tree/5.7





推荐阅读
  • function

    解析SQL查询结果的排序问题及其解决方案

    解析SQL查询结果的排序问题及其解决方案
    本文探讨了为什么某些SQL查询返回的数据集未能按预期顺序排列，并提供了详细的解决方案，帮助开发者理解并解决这一常见问题。 ... [详细]
  • default

    构建个人博客站点：基于LAMP环境的WordPress部署指南

    本文详细介绍如何利用已搭建的LAMP（Linux、Apache、MySQL、PHP）环境，快速创建一个基于WordPress的内容管理系统（CMS）。WordPress是一款流行的开源博客平台，适用于个人或小型团队使用。 ... [详细]
  • default

    MySQL、SQL Server 和 Oracle 中的分组与排序技巧

    本文详细介绍了在 MySQL、SQL Server 和 Oracle 数据库中如何使用分组和排序功能。涵盖了聚集函数的应用、HAVING 子句的作用以及特定数据库中的独特方法，如 SQL Server 的 ROW_NUMBER() 函数和 Oracle 的相关特性。 ... [详细]
  • default

    MySQL 客户端执行 NOW() 函数时出现 8 小时时差问题及解决方案

    本文介绍了一种在 MySQL 客户端执行 NOW() 函数时出现时间偏差的问题，并详细描述了如何通过配置文件调整时区设置来解决该问题。演示场景中，假设当前北京时间为2023年2月17日19:31:37，而查询结果显示的时间比实际时间晚8小时。 ... [详细]
  • random

    使用RPM包在本地安装MySQL 5.6

    本文详细介绍了如何通过RPM包在Linux系统（如CentOS）上安装MySQL 5.6。涵盖了检查现有安装、下载和安装RPM包、配置MySQL以及设置远程访问和开机自启动等步骤。 ... [详细]
  • get

    C#中SQL Server与Access批量数据插入性能对比

    本文探讨了使用C#在SQL Server和Access数据库中批量插入多条数据的性能差异。通过具体代码示例，详细分析了两种数据库的执行效率，并提供了优化建议。 ... [详细]
  • get

    SQL基础入门与数据库体系概述

    本文介绍了数据库体系的基础知识，涵盖关系型数据库（如MySQL）和非关系型数据库（如MongoDB）的基本操作及高级功能。通过三个阶段的学习路径——基础、优化和部署，帮助读者全面掌握数据库的使用和管理。 ... [详细]
  • less

    深入解析网络存储技术

    本文详细介绍了网络存储技术的基本概念、分类及应用场景。通过分析直连式存储（DAS）、网络附加存储（NAS）和存储区域网络（SAN）的特点，帮助读者理解不同存储方式的优势与局限性。 ... [详细]
  • default

    为已有数据表添加主键：MySQL与SQL Server的最佳实践

    为已有数据表添加主键：MySQL与SQL Server的最佳实践
    本文介绍了在处理一个涉及数据交互的小项目时，如何为没有主键标识的老表添加主键。具体探讨了在SQL Server中为已有数据表添加自增主键或GUID主键的两种方法，并提供了详细的SQL语句及执行效果。 ... [详细]
  • default

    npm run dev后报错

    npm run dev后报错 ... [详细]
  • default

    CentOS 6.3 64位系统下搭建LAMP环境并安装PHP 5.5.7

    在成功安装和测试MySQL及Apache之后，接下来的步骤是安装PHP。为了确保安全性和配置的一致性，建议在安装PHP前先停止MySQL和Apache服务，并将MySQL集成到PHP中。 ... [详细]
  • default

    【48】在flask中使用ＳＱＬＡＬｃｈｅｍｙ连接ｓｑｌｉｔｅ数据库

    方法：１　配置数据库basediros.path.abspath(os.path.dirname(__file__))　　＃获取当前文件的绝对路径appFlask(__name__ ... [详细]
  • java

    递推算法与大数处理

    本问题探讨了在特定条件下排列儿童队伍的方法数量。题目要求计算满足条件的队伍排列总数，并使用递推算法和大数处理技术来解决这一问题。 ... [详细]
  • java

    Netflix利用Druid实现高效实时数据分析

    Netflix利用Druid实现高效实时数据分析
    本文探讨了全球领先的在线娱乐公司Netflix如何通过采用Apache Druid，实现了高效的数据采集、处理和实时分析，从而显著提升了用户体验和业务决策的准确性。文章详细介绍了Netflix在系统架构、数据摄取、管理和查询方面的实践，并展示了Druid在大规模数据处理中的卓越性能。 ... [详细]
  • nodejs

    Node.js 进程异常时自动发送邮件通知

    在项目部署后，Node.js 进程可能会遇到不可预见的错误并崩溃。为了及时通知开发人员进行问题排查，我们可以利用 nodemailer 插件来发送邮件提醒。本文将详细介绍如何配置和使用 nodemailer 实现这一功能。 ... [详细]
author-avatar
oz法卡山英雄营
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有