MySQL的内存结构与物理结构

从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

原文发布于微信公众号 - BanzClub(banz-club)

原文发表时间:2019-08-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券