Java|第1节 MySQL 架构篇 2021-12-24 mysql|架构|数据库|组件使用

Java组件总目录
MySQL 架构篇

Java组件总目录
一、mysql文件结构
- 1. 日志文件（顺序IO）
- - 1）错误日志（errorlog）
  - 2）二进制日志（bin log）
  - 3）通用查询日志（general query log）
  - 4）慢查询日志（slow query log）
- 2. 数据文件（随机IO）
- - redolog文件：
  - InnoDB数据文件：
  - MyIsam数据文件
二、mysq的架构
三、MySqlServer层对象
- Sql语句执行流程
四、InnoDB存储引擎
- 1 InnoDB架构图
- 2 InnoDB磁盘文件（ON-Disk Structures）
- - 1 系统表空间（System Tablespaces）
  - 2 用户表空间 (File-Per-Table Tablespaces)
  - - Redo log 详细介绍
- 3 InnoDB逻辑存储结构
- - - 页
- 4 InnoDB内存结构
- - 1 Buffer Pool缓冲池
  - 2 额外内存池（Addtional memory pool）
  - 3 Redo log Buffer重做日志缓冲
  - - 修改数据的流程
  - 4 内存数据落盘
  - - 4.1 脏页落盘
    - 4.2 重做日志落盘
    - - redo log 写入的时机
- 5 CheckPoint检查点机制
- - - 1.简介
    - 2.Checkpoint分类
    - - 1、Master Thread Checkpoint
      - 2、FLUSH_LRU_LIST Checkpoint
      - 3、Async/Sync Flush Checkpoint
      - 4、Dirty Page too much
- 6 Double Write双写
InnoDB 数据落盘流程

一、mysql文件结构

MySQL是通过文件系统对数据和索引进行存储的。
MySQL从物理结构上可以分为日志文件和数据索引文件。

MySQL在Linux中的数据索引文件和日志文件通常放在/var/lib/mysql目录下。
1. 日志文件（顺序IO） 1）错误日志（errorlog）
默认是开启的，而且从5.5.7以后无法关闭错误日志，错误日志记录了运行过程中遇到的所有严重的错误信息,以及 MySQL每次启动和关闭的详细信息。

默认的错误日志名称：hostname.err。错误日志所记录的信息是可以通过log-error和log-warnings来定义的，其中log-err是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。

#可以直接定义为文件路径，也可以为ON|OFF log_error=/var/log/mysqld.log #只能使用1|0来定义开关启动，默认是启动的 log_warings=1

2）二进制日志（bin log）
默认是关闭的，需要通过以下配置进行开启。

log-bin=mysql-bin

其中mysql-bin是binlog日志文件的basename，binlog日志文件的完整名称：mysql-bin-000001.log
binlog记录了数据库所有的ddl语句和dml语句，但不包括select语句内容，语句以事件的形式保存，描述了数据的变更顺序，binlog还包括了每个更新语句的执行时间信息。如果是DDL语句，则直接记录到binlog日志，而DML语句，必须通过事务提交才能记录到binlog日志中。
binlog主要用于实现mysql主从复制、数据备份、数据恢复。
binlog/relaylog（二进制日志）主从复制时使用，后面详细讲解。
3）通用查询日志（general query log）
默认情况下通用查询日志是关闭的。
由于通用查询日志会记录用户的所有操作，其中还包含增删查改等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响mysql的性能的。如若不是为了调试数据库的目的建议不要开启查询日志。
4）慢查询日志（slow query log）
默认是关闭的。需要通过以下设置进行开启：

#开启慢查询日志 slow_query_log=ON #慢查询的阈值 long_query_time=10 #日志记录文件如果没有给出file_name值，默认为主机名，后缀为-slow.log。 # 如果给出了文件名，但不是绝对路径名，文件则写入数据目录。 slow_query_log_file= file_name

记录执行时间超过long_query_time秒的所有查询，便于收集查询时间比较长的SQL语句；
查询多少SQL超过了慢查询时间的阈值： SHOW GLOBAL STATUS LIKE ‘%Slow_queries%’;
2. 数据文件（随机IO） redolog文件：

ib_logfile0、ib_logfile1

InnoDB数据文件：
.frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
.ibd：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件。
ibdata文件：使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件。系统表空间
MyIsam数据文件
.frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
.myd文件：主要用来存储表数据信息。
.myi文件：主要用来存储表数据文件中任何索引的数据树。
二、mysq的架构

文章图片

1.Connectors
连接器，指的是不同语言中与SQL的交互，
2.Management Serveices & Utilities
系统管理和控制工具
3.Connection Pool: 连接池

管理用户连接，等待处理连接请求。
负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。
而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，接受客户端的命令请求，传递Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的cache 等。

4.SQL Interface: SQL接口
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
5.Parser: 解析器
SQL命令传递到解析器的时候会被解析器验证和解析。
主要功能：

a . 将SQL语句进行词法分析和语法分析，解析成语法树，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
b. 如果在分解过程中遇到错误，那么就说明这个sql语句是不合理的。

6.Optimizer: 查询优化器
SQL语句在查询之前会使用查询优化器对查询进行优化。explain语句查看的SQL语句执行计划，就是由查询优化器生成的。
7.Cache和Buffer：查询缓存
mysql server层的一个组件，在mysql5.7及以前的版本中都包含。在mysql8中已经移除此组件。缓存默认是关闭状态。
他的主要功能是将客户端提交给MySQL的 select请求的返回结果集 cache 到内存中，与该 query 的一个hash 值做一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
8.Pluggable Storage Engines：存储引擎
与其他数据库例如Oracle 和SQL Server等数据库中只有一种存储引擎不同的是，MySQL有一个被称为“Pluggable Storage Engine Architecture”(可插拔的存储引擎架构)的特性，也就意味着MySQL数据库提供了多种存储引擎。
而且存储引擎是针对表的，用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。也就是说，同一数据库不同的表可以选择不同的存储引擎
简而言之，存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
三、MySqlServer层对象 Sql语句执行流程

文章图片

四、InnoDB存储引擎 1 InnoDB架构图详细显示了InnoDB存储引擎的体系架构，从图中可见，InnoDB存储引擎由内存池，后台线程和磁盘文件三大部分组成。接下来我们就来简单了解一下内存相关的概念和原理。

文章图片

2 InnoDB磁盘文件（ON-Disk Structures） InnoDB的主要的磁盘文件主要分为三大块：一是系统表空间，二是用户表空间，三是redo日志文件和归档文件。二进制文件(binlog)等文件是MySQL Server层维护的文件，所以未列入InnoDB的磁盘文件中。
1 系统表空间（System Tablespaces）

ibdata1其中包含: 数据字典双写缓冲区（Doublewrite ）修改缓冲区（插入缓冲区）：辅助索引的修改操作的缓冲 Change Buffer; 回滚日志（undo logs）：事务时详细讲解默认保存在系统表空间中，可以独立文件存储，生成回滚表空间中（Undo Tablespaces）。

2 用户表空间 (File-Per-Table Tablespaces)

默认情况下每个表对应一个表空间文件，存储表中的数据和索引信息。

mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name| Value | +-----------------------+-------+ | innodb_file_per_table | ON| +-----------------------+-------+ 1 row in set (0.00 sec) 如果此参数为OFF，所有的数据都保存到系统表空间中 3)通用表空间 General tablespace 使用create tablespace语句创建的表空间就是通用表空间。 4）临时表空间当使用临时表时，在临时表空间中完成。例如排序、分组、union时。 5）回滚表空间默认在系统表空间中 6）Redo log 重做日志，非常重要。保证数据不丢失的一个重要环节。由一组文件组成： ib_logfile0 ib_logfile1 循环写入的一组文件，默认两个文件每个文件50m。控制文件大小的参数： mysql> show variables like 'innodb_log_file_size'; +----------------------+----------+ | Variable_name| Value| +----------------------+----------+ | innodb_log_file_size | 50331648 | +----------------------+----------+ 1 row in set (0.00 sec)

Redo log 详细介绍

重做日志文件组是如何写入数据的？
每个InnoDB存储引擎至少有1个重做日志文件组(group)，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0和ib_logfile1。
在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行。
InnoDB存储引擎先写入重做日志文件1，当文件被写满时，会切换到重做日志文件2，再当重做日志文件2也被写满时，再切换到重做日志文件1。
如何设置重做日志文件大小？
用户可以使用innodb_log_file_size来设置重做日志文件的大小，这对InnoDB存储引擎的性能有着非常大的影响。
如果重做日志文件设置的太大，数据丢失时，恢复时可能需要很长的时间；
另一方面，如果设置的太小，重做日志文件太小会导致依据checkpoint的检查需要频繁刷新脏页到磁盘中，导致性能的抖动。

3 InnoDB逻辑存储结构用户表空间的逻辑存储结构，真正存储数据的文件。
表空间文件->段->区（64页1m）->页（默认单位16k）->行
行的大小取决于表结构的定义，由字段的数据类型和长度计算得来。一行占用内存小，一页的行数比较多。
如果行特别大超过16k需要使用多个页存储一行数据。每个数据页之间由指针连接的。（效率低，拆表优化）
数据页是默认的存取单位，无论是数据还是索引都是以页为单位。

文章图片

页 InnoDB 每个页默认大小时是 16KB，页是 InnoDB管理磁盘的最小单位，也InnoDB中磁盘和内存交互的最小单位。
操作系统管理磁盘的最小单位也是页，是操作系统读写磁盘最小单位，Linux中页一般是4K，可以通过命令查看。

文章图片

4 InnoDB内存结构

文章图片

1 Buffer Pool缓冲池

1.数据页：数据内容 2.索引页：索引内容一个数据页和索引页都是16k。 3.修改缓冲区（change Buffer 新版）辅助索引的修改操作的缓冲，最早版本只是update, 后面对增删改。 4、自适应hash索引（adaptive hash index）完全由InnoDB引擎维护的索引，无法人工干预。

2 额外内存池（Addtional memory pool）
额外内存池是InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间, 知道即可。
3 Redo log Buffer重做日志缓冲

重做日志缓冲区。保存的是重做日志，定时保存到磁盘+commit时写入磁盘。

文章图片

修改数据的流程

1、找到要修改的数据所在的数据页 2、先到缓冲池中找数据页，如果没有查询磁盘。 3、把查询到数据页放到buffer pool中。 4、修改数据页之前先写redo log buffer，redo log中记录对数据库修改的每个动作。 5、修改内存中的数据页，**内存中的数据页和磁盘上的数据页不一致，产生了脏页**。 6、提交修改，把redo log buffer中的内容刷新到redo log 文件中。 7、如果刷新成功commit成功。 8、如果刷新失败，提交失败，需要回滚操作。

一旦把redo log buffer中的数据写入redo log 文件中，相当于持久化成功，数据就不会丢失了。
一旦数据库崩溃可以根据redo log 恢复数据。redo log 中记录的内容是对应的数据页上修改了哪些内容。
为什么先写Redo Log，不直接写入磁盘？
随机写：需要不断移动磁头进行数据的写入操作。性能比较差。
顺序写：WAL（write ahead log）不需要移动磁头的写入，写入性能好于随机写。
4 内存数据落盘
InnoDB内存缓冲池中的数据page要完成持久化的话，是通过两个流程来完成的，一个是脏页落盘；一个是预写redo log日志。
当缓冲池中的页的版本比磁盘要新时，数据库需要将新版本的页从缓冲池刷新到磁盘。但是如果每次一个页发送变化，就进行刷新，那么性能开发是非常大的，于是InnoDB采用了Write AheadLog（WAL）策略和Force Log at Commit机制实现事务级别下数据的持久性。
WAL要求数据的变更写入到磁盘前，首先必须将内存中的日志写入到磁盘；
Force-log-at-commit要求当一个事务提交时，所有产生的日志都必须刷新到磁盘上，如果日志刷新成功后，缓冲池中的数据刷新到磁盘前数据库发生了宕机，那么重启时，数据库可以从日志中恢复数据。
为了确保每次日志都写入到重做日志文件，在每次将重做日志缓冲写入重做日志后，必须调用一次fsync操作，将缓冲文件从文件系统缓存中真正写入磁盘。
4.1 脏页落盘在数据库中进行读取操作，将从磁盘中读到的页放在缓冲池中，下次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。
对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为CheckPoint的机制刷新回磁盘。
4.2 重做日志落盘 redo log 写入的时机

1、redo log buffer定时每秒钟写入一次。 2、commit时写入由【innodb_flush_log_at_trx_commit】参数控制。 0：commit时不写入文件，等待主线程每秒的写入动作。有可能会丢失数据。性能是最好的。 1：默认值。commit时把数据写入操作系统缓存，并且调用fsync函数，真正的把**数据刷新到磁盘** redo log。（推荐） 2：commit时把数据写入操作系统缓存，不调用fsync函数，由操作系统控制写入。也存在丢失数据的可能。

用户程序写入数据到磁盘文件时，需要调用操作系统的接口，操作系统本身是有缓冲区的，之后依赖操作系统机制不时的将缓存中刷新到磁盘文件中。
只有设置为1是最安全但是性能消耗的方式，可以真正地保证事务的持久性，但是由于MySQL执行刷新操作 fsync() 是阻塞的，直到完成后才会返回，我们知道写磁盘的速度是很慢的，因此MySQL 的性能会明显地下降。
0和2的性能最好的模式，综合安全性和性能的考虑，在业务中经常使用的2这种模式，在MySQL异常重启时不会丢失数据，只有在服务器意外宕机时才会丢失1秒的数据，这种情况几率是很低的，相对于性能来说，这时可以容忍的。
5 CheckPoint检查点机制 1.简介思考一下这个场景：如果重做日志可以无限地增大，同时缓冲池也足够大，那么是不需要将缓冲池中页的新版本刷新回磁盘。因为当发生宕机时，完全可以通过重做日志来恢复整个数据库系统中的数据到宕机发生的时刻。
但是这需要两个前提条件：

缓冲池可以缓存数据库中所有的数据；
重做日志可以无限增大

因此Checkpoint（检查点）技术就诞生了，目的是解决以下几个问题：

1、缩短数据库的恢复时间；
2、缓冲池不够用时，将脏页刷新到磁盘；
3、重做日志不可用时，刷新脏页。
1 当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。数据库只需对Checkpoint后的重做日志进行恢复，这样就大大缩短了恢复的时间。
2 当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷回磁盘。
3 当重做日志出现不可用时，因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的。重做日志可以被重用的部分是指这些重做日志已经不再需要，当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。如果重做日志还需要使用，那么必须强制Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

Checkpoint发生的时间、条件及脏页的选择等都非常复杂。而Checkpoint所做的事情无外乎是将缓冲池中的脏页刷回到磁盘，不同之处在于每次刷新多少页到磁盘，每次从哪里取脏页，以及什么时间触发Checkpoint。
2.Checkpoint分类在InnoDB存储引擎内部，有两种Checkpoint，分别为：Sharp Checkpoint、Fuzzy Checkpoint.

sharp checkpoint：在关闭数据库的时候，将buffer pool中的脏页全部刷新到磁盘中。
fuzzy checkpoint：数据库正常运行时，在不同的时机，将部分脏页写入磁盘。仅刷新部分脏页到磁盘，也是为了避免一次刷新全部的脏页造成的性能问题。

Fuzzy Checkpoint：

1、Master Thread Checkpoint；
2、FLUSH_LRU_LIST Checkpoint；
3、Async/Sync Flush Checkpoint；
4、Dirty Page too much Checkpoint

1、Master Thread Checkpoint 在Master Thread中，会以每秒或者每10秒一次的频率，将部分脏页从内存中刷新到磁盘，这个过程是异步的。正常的用户线程对数据的操作不会被阻塞。
2、FLUSH_LRU_LIST Checkpoint 当这个空间页面数量不足的时候，发生FLUSH_LRU_LIST checkpoint。
FLUSH_LRU_LIST checkpoint是在单独的page cleaner线程中执行的。
MySQL对缓存的管理是通过buffer pool中的LRU列表实现的，LRU 空闲列表中要保留一定数量的空闲页面，来保证buffer pool中有足够的空闲页面来相应外界对数据库的请求。
空闲页的数量由innodb_lru_scan_depth参数表来控制的，因此在空闲列表页面数量少于配置的值的时候，会发生checkpoint，剔除部分LRU列表尾端的页面。
3、Async/Sync Flush Checkpoint Async/Sync Flush checkpoint 发生在重做日志不可用的时候，将buffer pool中的一部分脏页刷新到磁盘中，在脏页写入磁盘之后，事务对应的重做日志也就可以释放了。
【Java|第1节 MySQL 架构篇 2021-12-24】Async/Sync Flush checkpoint是在单独的page cleaner线程中执行的。
关于redo_log文件的的大小，可以通过 innodb_log_file_size 来配置。
对于是执行Async Flush checkpoint还是Sync Flush checkpoint，由 checkpoint_age 以及async_water_mark 和 sync_water_mark 来决定。

# 即checkpoint_age等于最新的lsn减去已经刷新到磁盘的lsn的值 checkpoint_age = redo_lsn - checkpoint_lsn async_water_mark = 75% * innodb_log_file_size sync_water_mark = 90% *innodb_log_file_size

async：异步落盘，不会阻塞写操作。当redolog的使用量超过文件的75%未达到90%时，执行异步落盘。直到redo log 的使用量小于75%后停止 sync：同步落盘，会阻塞写操作。 redolog文件的使用量达到90%，执行同步落盘操作，同时阻塞写操作。直到redo log 的使用量小于75%后停止。

4、Dirty Page too much Dirty Page too much Checkpoint是在Master Thread 线程中每秒一次的频率实现的。
Dirty Page too much 意味着buffer pool中的脏页过多，执行checkpoint脏页刷入磁盘，保证buffer pool中有足够的可用页面。
6 Double Write双写如果说Insert Buffer给InnoDB存储引擎带来了性能上的提升，那么Double Write带给InnoDB存储引擎的是数据页的可靠性。

文章图片

如上图所示，Double Write由两部分组成，一部分是内存中的double write buffer，大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也为2MB。
在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先复制到内存中的double write buffer区域，之后通过double write buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免操作系统缓冲写带来的问题。在完成double write页的写入后，再讲double wirite buffer中的页写入各个表空间文件中。
如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间中的double write中找到该页的一个副本，将其复制到表空间文件中，再应用重做日志。
InnoDB 数据落盘流程