MySQL体系架构

MySQL体系架构

MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。

• Connectors：与MySQL服务器建立连接。目前几乎支持所有主流的服务端编程技术，例如常见的 Java、C、Python、.NET等，它们通过各自API技术与MySQL建立连接

• MySQL Server：服务层是MySQL的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分。

• Pluggable Storage Engines：存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现在有很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB。在绝大多数情况下，推荐使用InnoDB

• File System：该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

MySQL Server

• 连接池（Connection Pool）：负责存储和管理客户端与数据库的连接。

• 系统管理和控制工具（Management Services & Utilities）：如备份恢复、安全管理、集群管理等

• SQL接口（SQL Interface）：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。

• 解析器（Parser）：负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法。

• 查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。

• 缓存（Cache&Buffer）： 缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

File System

• pid 文件: pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件，和许多其他 Unix/Linux 服务端程序一样，它存放着自己的进程 id

• socket 文件: socket 文件也是在 Unix/Linux 环境下才有的，用户在 Unix/Linux 环境下客户端连接可以不通过TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL。

• 配置文件: 用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等。

• 数据文件

  • db.opt 文件：记录这个库的默认使用的字符集和校验规则。
  • frm 文件：存储与表相关的元数据（meta）信息，包括表结构的定义信息等，每一张表都会有一个frm 文件。
  • MYD 文件：MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)，每一张表都会有一个.MYD 文件。
  • MYI 文件：MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息，每一张 MyISAM 表对应一个 .MYI 文件。
  • ibd文件和 IBDATA 文件：存放 InnoDB 的数据文件（包括索引）。InnoDB 存储引擎有两种表空间方式：独享表空间和共享表空间。独享表空间使用 .ibd 文件来存放数据，且每一张InnoDB 表对应一个 .ibd 文件。共享表空间使用 .ibdata 文件，所有表共同使用一个（或多个，自行配置）.ibdata 文件。
  • ibdata1 文件：系统表空间数据文件，存储表元数据、Undo日志等 。
  • ib_logfile0、ib_logfile1 文件：Redo log 日志文件。

• 日志文件：二进制日志（Binary Log）、慢查询日志（Slow query log）、通用查询日志（General query log）和错误日志（Error log）等。

Innodb存储引擎

从MySQL 5.5版本开始默认使用InnoDB作为引擎，它擅长处理事务，具有自动崩溃恢复的特性，在日常开发中使用非常广泛。

Architecture

下面是官方的InnoDB引擎架构图(MySQL5.7)，MySQL8在磁盘结构方面有所不同。

Innodb存储结构主要分为内存结构和磁盘结构两大部分

• 内存结构主要包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer

• InnoDB磁盘主要包含Tablespaces，InnoDB Data Dictionary，Doublewrite Buffer、Redo Log和Undo Logs

Buffer Pool

Buffer Pool：缓冲池，简称BP。BP以Page页为单位，BP的底层采用链表数据结构管理Page。访问表记录和索引时会在Page页中缓存，以后使用可以减少磁盘IO操作，提升效率

show variables like 'innodb_buffer_pool_size';  # 推荐设置为物理内存的 70% 到 80%

set global innodb_buffer_pool_size = 256 * 1024 * 1024;  # MySQL5.7开始可以在线动态调整

show variables like '%innodb_page_size%'; # 查看page页大小

加入元素时并不是从表头插入，而是从中间midpoint位置插入，如果数据很快被访问，那么page就会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。

每当有新的page数据读取到buffer pool时，InnoDb引擎会判断是否有空闲页，是否足够，如果有就将free page从free list列表删除，放入到LRU列表中。没有空闲页，就会根据LRU算法淘汰LRU链表默认的页，将内存空间释放分配给新的页。

Change Buffer

Change Buffer：写缓冲区。在进行DML操作时，如果BP没有其相应的Page数据，并不会立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，再将数据合并恢复到BP中。

ChangeBuffer占用BufferPool空间，默认占25%，最大允许占50%，可以根据读写业务量来进行调整。参数innodb_change_buffer_max_size;

InnoDB文件存储结构

InnoDB文件存储格式

Tablespaces

Select查询流程

Select执行流程: 一条SQL查询语句是如何执行的？

查询执行引擎负责执行 SQL 语句，查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。

若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有相同的 SQL 语句执行则直接返回结果。

• 如果开启了查询缓存，先将查询结果做缓存操作
• 返回结果过多，采用增量模式返回

通讯机制与线程

通过客户端/服务器通信协议与MySQL建立连接（Connectors&Connection Pool）。MySQL 客户端与服务端的通信方式是 ==半双工==。

# 查看用户正在运行的线程信息，root用户能查看所有线程，其他用户只能看自己的
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  |  139 | Waiting on empty queue | NULL             |
|  9 | root            | localhost | NULL | Query   |    0 | init                   | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set, 1 warning (0.00 sec)

• id：线程ID，可以使用 kill xx;
• user：启动这个线程的用户
• Host：发送请求的客户端的IP和端口号
• db：当前命令在哪个库执行
• Command：该线程正在执行的操作命令
  • Create DB：正在创建库操作
  • Drop DB：正在删除库操作
  • Execute：正在执行一个PreparedStatement
  • Close Stmt：正在关闭一个PreparedStatement
  • Query：正在执行一个语句
  • Sleep：正在等待客户端发送语句
  • Quit：正在退出
  • Shutdown：正在关闭服务器
• Time：表示该线程处于当前状态的时间，单位是秒
• State：线程状态
  • Updating：正在搜索匹配记录，进行修改
  • Sleeping：正在等待客户端发送新请求
  • Starting：正在执行请求处理
  • Checking table：正在检查数据表
  • Closing table : 正在将表中数据刷新到磁盘中
  • Locked：被其他查询锁住了记录
  • Sending Data：正在处理Select查询，同时将结果发送给客户端
• Info：一般记录线程执行的语句，默认显示前100个字符。想查看完整的使用show full processlist;

更多信息参照：MySQL8 show-processlist

查询缓存

如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成“解析树”。

show variables like '%query_cache%';  # 查看查询缓存是否启用，空间大小，限制等
show status like 'Qcache%';           # 查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等

SET GLOBAL query_cache_type = DEMAND; # 不建议使用查询缓存

或者可以在 MySQL 的配置文件（通常是 my.cnf 或 my.ini）中设置：

[mysqld]  
query_cache_type = DEMAND

解析器和优化器

解析器（Parser） 将客户端发送的SQL进行语法解析，生成 “解析树”。 ==预处理器==根据一些MySQL规则进一步检查“解析树”是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后生成==新的“解析树”==。

查询优化器（Optimizer） 根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。

• 等价变换策略
  • 5=5 and a>5 改成 a > 5
  • a < b and a=5 改成b>5 and a=5
  • 基于联合索引，调整条件位置等
• 优化count、min、max等函数
  • InnoDB引擎min函数只需要找索引最左边
  • InnoDB引擎max函数只需要找索引最右边
  • MyISAM引擎count(*)，不需要计算，直接返回
• 提前终止查询: 使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据
• in的优化: MySQL对in查询，会先进行排序，再采用二分法查找数据。比如where id in (2,1,3)，变成 in (1,2,3)

Update流程与日志机制

WAL技术

缓冲池（Buffer Pool）: 是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据(若缓冲池没有数据，则从磁盘加载并缓存)，然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

如果缓存的内容更新好了，但磁盘的内容还是旧的，何时更新到磁盘呢？如何保证数据一致性呢？

• InnoDB是以页为单位来进行磁盘IO的, 一个微小的更新就刷新一个完整的数据页到磁盘太浪费了
• 随机IO刷起来比较慢, Redo Log 的顺序写减少了随机 I/O，对传统机械硬盘性能提升显著。

Update更新流程

更新语句不但需要先查询，且在更新执行流程中，日志的写入和两阶段提交机制至关重要，它们确保了事务的持久性和一致性。

update table_name set a=a+1 where id=2

1. 加载数据页到缓冲池（Buffer Pool）： 根据更新条件查找目标记录。

   • 如果目标记录所在的数据页不在缓冲池中，从磁盘加载到缓冲池。
   • 如果已经在缓冲池中，则直接操作。
   • 目标记录所在的数据页被加锁（如行锁或间隙锁，具体取决于事务隔离级别）。

2. 记录 Undo Log（用于回滚和 MVCC）： 在对记录进行更新之前，生成 Undo Log 并存储在回滚段中，记录被修改前的旧值。Undo Log 用于支持 事务回滚（回退到原始状态） 和 多版本并发控制（MVCC）的一致性读。

3. 更新缓冲池中的数据： 修改缓冲池中的目标数据页，将数据标记为“脏页”（Dirty Page）。 数据尚未同步到磁盘，只是更新了内存中的缓存。

4. 记录 Redo Log（日志缓冲区）： 将更新操作写入 Redo Log Buffer（内存中的日志缓冲区），以物理日志的形式记录“对哪些数据页进行了哪些更改”。Redo Log Buffer 不会立即写入磁盘，而是由以下规则触发写入：

   • 当事务进入 Prepare 阶段。
   • 当 Redo Log Buffer 的使用量达到阈值（由 innodb_log_buffer_size 控制）。
   • 每秒的后台刷新（默认由 innodb_flush_log_at_trx_commit 参数控制）。

5. Redo Log 的 Prepare 阶段： Redo Log Buffer 的内容被顺序写入磁盘（Redo Log 文件），并标记为“Prepare”状态。 此阶段的关键点：

   • 确保数据的持久性：一旦系统崩溃，Redo Log 中的记录可以用来恢复到 Prepare 阶段的状态。
   • 事务尚未提交，对外仍不可见。

6. 记录 Binlog（逻辑日志）： Server 层生成 Binlog 日志（逻辑日志），记录 SQL 语句的操作。 Binlog 被写入 Binlog Buffer（日志缓冲区），然后按照事务提交时的策略（sync_binlog 参数）决定是否同步到磁盘。

   • sync_binlog=1：每次事务提交时将 Binlog 从缓冲区刷到磁盘，确保持久性。
   • sync_binlog>1：延迟同步，可能丢失部分 Binlog。

7. Redo Log 的 Commit 阶段： 事务进入 Commit 阶段，Redo Log 文件中写入一个Commit 标记。Commit 标记表示事务提交完成，数据对外可见。Redo Log 的 Commit 标记同步到磁盘后，事务才算真正完成。

8. 数据最终写入磁盘（刷脏页）： 更新操作只修改缓冲池中的数据页，实际数据页的写入由后台线程完成（异步操作）。 脏页刷盘的触发条件：

   • 缓冲池使用率达到阈值（由 innodb_max_dirty_pages_pct 控制）。
   • Redo Log 的空间不足。
   • 手动触发（如执行 FLUSH 操作）。

两阶段提交(内部XA)

两阶段提交（2PC）在 Redo Log 和 Binlog 的协调中起到核心作用，确保两者一致性。

1. Prepare 阶段： Redo Log 的 Prepare 状态在 Binlog 写入之前完成。 即使系统崩溃，只要有 Redo Log 的 Prepare 日志，数据仍可以恢复到一致性状态。

   🔔 XA事务--extra disk flush for transaction preparation

   MySQL官方文档明确指出了在 XA 事务的两阶段提交过程中，prepare 阶段也会涉及到磁盘刷新操作。详见：innodb_support_xa

   禁用 innodb_support_xa 可能会导致复制不安全，因此MySQL禁止了与二进制日志组提交相关的性能提升，这也是为什么从 MySQL 5.7.10 版本开始，这个功能始终是开启的，不允许再禁用。

2. Binlog 写入： Binlog 写入成功后，再进入 Redo Log 的 Commit 阶段。 确保 Binlog 和 Redo Log 的位置（LSN）一致。

3. Commit 阶段： Redo Log 写入 Commit 标记，表示事务完成。 若系统崩溃后只找到 Prepare 状态的 Redo Log，事务会被回滚（保证一致性）。

1. innodb_flush_log_at_trx_commit： 控制 Redo Log 的刷盘策略：

   • 1：每次事务提交时刷盘，提供最高的数据安全性。
   • 0：不实时刷盘，性能高但可能丢失数据。
   • 2：事务提交时写入 OS 缓冲区，定时刷盘。

2. sync_binlog： 控制 Binlog 的刷盘策略：

   • 1：每次事务提交时同步到磁盘，确保 Binlog 的持久性。
   • 较大的值（如 100）可以提高性能，但可能丢失最近的事务日志。

3. innodb_log_buffer_size： 增大日志缓冲区可以减少磁盘 I/O，但过大可能浪费内存。

Redo Log

Redo Log 是物理增量日志，核心在于持久性、性能优化和崩溃恢复。

• 持久性保证（Durability）： 确保事务的修改在提交前已写入日志，即使系统崩溃也能恢复。
• 崩溃恢复： 万一断电或者数据库挂了，在重启时根据redo日志中的记录就可以将数据自动恢复。

show variables like '%innodb_log_group_home_dir%';  # ./ 数据目录
SHOW VARIABLES LIKE 'datadir';                      #  datadir  /var/lib/mysql/

相关参数及作用

show variables like '%innodb_log_file_size%';
show variables like '%innodb_log_files_in_group%';

Redo Log刷盘时机由innodb_flush_log_at_trx_commit参数控制，详见：innodb-parameters

这里的==刷盘==指的是Redo Buffer -> OS cache -> flush cache to disk，fsync() 的作用就是强制将内核缓冲区中的数据写入到磁盘

• 1（default）: 每次事务提交都执行刷盘，最安全，性能最差的方式
• 0：每秒执行一次刷盘，可能丢失一秒内的事务数据
• 2：每次事务提交执行 Redo Buffer -> OS cache，由后台Master线程每隔1秒调用fsync()

Binlog

Binary log（二进制日志），简称Binlog（MySQL Server自己的日志）。Binlog是记录所有数据库==表结构变更==以及==表数据修改==的二进制日志，不会记录SELECT和SHOW这类操作。Binlog日志是以事件形式记录，还包含语句所执行的消耗时间。开启Binlog日志有以下两个最重要的使用场景。

• 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。
• 数据恢复：通过mysqlbinlog工具来恢复数据。

show variables like 'log_bin';      # Binlog状态查看
show binary logs;                   # Binlog开启后可以查看有多少binlog文件

MySQL5.7 版本中Binlog默认是关闭的，8.0版本默认是开启的。开启Binlog需要修改my.cnf或my.ini配置文件，然后重启MySQL服务。

[mysqld]
log_bin=/var/lib/mysql/mysql-binlog
binlog-format=ROW
server-id=1
expire_logs_days =30

开启 Binlog 后，默认可以在数据目录查看到具体的 Binlog 文件：ls /var/lib/mysql/

topple@Ubuntu22:~$ ls /docker/mysql/mysql5.7/data/
auto.cnf         client-key.pem  ib_logfile1  mysql-bin.000001  mysql-binlog.000001  private_key.pem  sys
ca-key.pem       demo            ibdata1      mysql-bin.000002  mysql-binlog.index   public_key.pem
ca.pem           ib_buffer_pool  ibtmp1       mysql-bin.000003  mysql.sock           server-cert.pem
client-cert.pem  ib_logfile0     mysql        mysql-bin.index   performance_schema   server-key.pem

发生以下任何事件时, binlog日志文件会重新生成：

• 服务器启动或重新启动
• 服务器刷新日志，执行命令flush logs
• 日志文件大小达到 max_binlog_size 值，默认值为 1GB

MySQL日志管理

MySQL 中的日志是数据库管理和故障恢复的重要组成部分。包括二进制日志（Binary Log）、重做日志（Redo Log）、回滚日志（Undo Log）等。

• 二进制日志（Binary Log）：记录所有更改操作，用于数据恢复和主从复制。
• 重做日志（Redo Log）：记录事务的更改，用于事务持久性和崩溃恢复。
• 回滚日志（Undo Log）：记录事务开始时的数据快照，用于事务回滚和MVCC(多版本并发控制)
• 错误日志（Error Log）：记录服务器的错误和警告信息。
• 慢查询日志（Slow Query Log）：记录执行时间超过阈值的查询语句。
• 查询日志（General Query Log）：记录所有客户端发送的查询语句。

[mysqld]
log_bin = /path/to/binlog/mysql-bin.log
server_id = 1

PURGE BINARY LOGS TO 'mysql-bin.000005';
PURGE BINARY LOGS BEFORE '2023-10-01 00:00:00';

[mysqld]
innodb_log_file_size = 512M
innodb_log_files_in_group = 2

systemctl stop mysql
rm -f /var/lib/mysql/ib_logfile*
systemctl start mysql

[mysqld]
innodb_undo_tablespaces = 2
innodb_undo_logs = 128

[mysqld]
log_error = /path/to/error.log

[mysqld]
slow_query_log = 1
slow_query_log_file = /path/to/slow-query.log
long_query_time = 2

[mysqld]
general_log = 1
general_log_file = /path/to/general-query.log