MySQL 的基础架构-优快云博客

本文链接：https://blog.youkuaiyun.com/csdn_tom_168/article/details/149152844

MySQL 的基础架构是一个经典的 分层、模块化设计，核心目标是处理 SQL 请求、管理数据存储、保证数据安全性和一致性。其核心架构通常被划分为以下几个主要层次：

+----------------------------------------------------------------------------------------------------+
|                                            Client Applications                                     |
|  (mysql CLI, JDBC, Python Connector, PHP PDO, ORM Frameworks, etc.)                               |
+----------------------------------------------------------------------------------------------------+
                                      ↓  (Connection/TCP)
+-----------------------------+       ↓  (Authentication)        +-----------------------------------+
|       【连接层】             | ←------+                         |        【系统数据库】              |
|    Connection Pool          |        |                         |  ┌─────────────────────────────┐  |
|  - 监听端口 (3306)          |        | (Thread per Connection) |  │  mysql (用户/权限/元数据)    │  |
|  - 线程管理/线程池          |        |                         |  ├─────────────────────────────┤  |
|  - 身份验证 (用户名/密码)   | ←------+                         |  │  information_schema (元数据) │  |
|  - 维持连接状态             |        |                         |  ├─────────────────────────────┤  |
+-----------------------------+        |                         |  │  performance_schema (性能指标)│  |
                                      ↓                         |  ├─────────────────────────────┤  |
+----------------------------------------------------------------+  │  sys (友好性能视图)         │  |
|                      【服务层 (SQL Layer)】                      |  └─────────────────────────────┘  |
|  +-------------------+  +-------------------+  +----------------+  +-----------------------------------+
|  |   解析器 (Parser)  |→|   优化器 (Optimizer)|→|   执行器 (Executor)|                                      |
|  | - 词法/语法分析    |  | - 生成执行计划     |  | - 调用存储引擎  |                                      |
|  | - 构建解析树       |  | - 成本估算         |  | - 权限最终检查  |                                      |
|  +-------------------+  +-------------------+  | - 结果集处理    |                                      |
|                                                +----------------+                                      |
|                                                                                                       |
|  【日志系统 (关键)】                                                                                  |
|  +----------------------+                                                                             |
|  |   Binlog (二进制日志) |←───────┤ 用于主从复制 & 时间点恢复 (PITR)                                   |
|  |   - 逻辑日志          |        | (服务层写入，引擎层协同)                                          |
|  +----------------------+        |                                                                   |
|                                  ↓ (Write)                                                           |
+-------------------------------------------------------------------------------------------------------+
                                      ↓  (Storage Engine API)
+-------------------------------------------------------------------------------------------------------+
|                     【存储引擎层 (Pluggable Storage Engines)】                                        |
|                                                                                                       |
|  +------------------------------------+  +-------------------------+  +----------------------------+  |
|  |         InnoDB (默认引擎)           |  |        MyISAM           |  |  其他引擎 (Memory, CSV...) |  |
|  |  ┌──────────────────────────────┐  |  |  - 不支持事务           |  |                            |  |
|  |  │  缓冲池 (Buffer Pool)         │  |  |  - 表锁                 |  |                            |  |
|  |  │   - 缓存数据页/索引页         │  |  |  - 不支持外键           |  |                            |  |
|  |  └──────────────┬───────────────┘  |  +-------------------------+  +----------------------------+  |
|  |                 │ (Read/Modify)    |                                                               |
|  |  ┌──────────────▼───────────────┐  |                                                               |
|  |  │  重做日志 (Redo Log)          │←─┤ 保证事务持久性 (WAL)                                          |
|  |  │   - 物理逻辑日志              │  |                                                               |
|  |  └──────────────┬───────────────┘  |                                                               |
|  |                 │ (Flush on Commit)|                                                               |
|  |  ┌──────────────▼───────────────┐  |                                                               |
|  |  │  回滚日志 (Undo Log)          │  | 支持事务回滚 & MVCC (多版本并发控制)                           |
|  |  │   - 存储旧数据版本            │  |                                                               |
|  |  └──────────────────────────────┘  |                                                               |
|  |                                    |                                                               |
|  |  表空间文件 (.ibd)                 | 数据/索引的持久化存储                                          |
|  +------------------------------------+                                                               |
+-------------------------------------------------------------------------------------------------------+
                                      ↓ (Disk I/O)
+-------------------------------------------------------------------------------------------------------+
|                                    物理存储 (磁盘/SSD)                                                |
|   - 数据文件 (*.ibd, *.myd)                                                                            |
|   - 日志文件 (ib_logfile*, binlog.*)                                                                   |
+-------------------------------------------------------------------------------------------------------+

(示意图：展示了 SQL 请求从客户端到存储引擎的流程及各核心组件)

连接层 (Connectors / Connection Pool)
- 职责：处理客户端连接、授权认证、线程管理。
- 关键组件/概念：
  - 连接器 (Connection Handler)：监听网络端口（默认 3306），接受来自各种客户端（mysql CLI, JDBC, ODBC, Python Connector, PHP PDO 等）的连接请求。
  - 线程池 (Thread Pooling - 可选/常见)：现代 MySQL 通常使用线程池（尤其在商业版或 Percona Server 中）来高效管理客户端连接。为每个连接分配一个线程（或从线程池复用），处理该连接上的所有请求。避免为每个新连接创建销毁线程的开销。
  - 身份认证 (Authentication)：验证连接请求的用户名、密码以及来源主机是否具有连接权限（基于 mysql.user 系统表）。
  - 连接管理：管理连接的生命周期（建立、维持、断开），维护连接状态（如当前数据库 USE database）。
- 作用：为上层提供一个可靠的、经过认证的通信通道。
服务层 (Server Layer / SQL Layer)
- 职责：解析 SQL、优化查询、执行核心内置函数、管理缓存等。这层是 MySQL 的大脑，与存储引擎无关。
- 关键组件/概念：
  - 查询缓存 (Query Cache - 已弃用): 注意：在 MySQL 8.0 中已被完全移除。 在早期版本中，它缓存 SELECT 语句及其结果集。如果后续完全相同的查询到来，直接返回缓存结果。但因其维护开销大、命中率低、对表更新敏感等问题，最终被移除。
  - 解析器 (Parser)：
    - 词法分析 (Lexical Analysis)：将 SQL 文本字符串拆分成一个个有意义的“词”（Token），如关键字（SELECT, FROM）、标识符（表名、列名）、常量等。
    - 语法分析 (Syntax Analysis)：根据 MySQL 的语法规则，检查 SQL 语句的结构是否正确，生成一个 解析树 (Parse Tree) 或 抽象语法树 (AST)。比如检查括号是否匹配、关键字顺序是否正确。
  - 预处理器 (Preprocessor) / 解析器扩展：在解析树基础上进行进一步语义检查。例如：
    - 检查表和列是否存在。
    - 解析名字和别名，消除歧义。
    - 权限检查（初步，执行时还有更细检查）。
    - 展开 * 为所有列名。
    - 常量表达式求值（如 WHERE id = 10 * 2 + 5 简化为 WHERE id = 25）。
  - 优化器 (Optimizer)：核心中的核心！ 基于解析树/预处理结果、表统计信息、索引信息、配置参数等，生成一个或多个可能的执行计划 (Execution Plan)，并估算每个计划的成本（CPU, I/O, Memory 等）。最终选择一个它认为成本最低的执行计划。
    - 优化类型：选择使用哪个索引（或全表扫描）、多表连接的顺序 (JOIN)、子查询优化、覆盖索引 (Covering Index) 判断、GROUP BY/ORDER BY 优化（利用索引排序）、LIMIT 优化等。
    - 依赖统计信息：InnoDB 会定期计算表的统计信息（如索引基数 - Cardinality）供优化器使用。不准确的统计信息可能导致优化器选择次优计划。
  - 执行器 (Executor)：负责根据优化器选定的执行计划，调用底层存储引擎提供的接口，一步步执行计划。
    - 在执行前，会进行最终权限检查（检查用户是否有权执行该操作）。
    - 打开表，获取表的元数据。
    - 调用存储引擎接口进行数据读写操作（如 read_first_row, read_next_row, insert_row, update_row 等）。
    - 处理 WHERE 条件过滤、计算表达式、执行聚合函数 (SUM, COUNT, AVG)、排序、分组等操作（如果不能在存储引擎层完成）。
    - 将最终结果集返回给客户端（或缓存在服务层内部结构）。
存储引擎层 (Storage Engine Layer)
- 职责：真正负责数据的存储和提取。MySQL 采用 可插拔存储引擎架构，这意味着你可以根据不同的需求（事务、性能、特性）为不同的表选择不同的存储引擎。
- 核心概念：
  - 插件式设计：服务层通过定义好的 存储引擎 API 与引擎交互。引擎负责实现这些接口（如 handler 类的方法）。
  - 数据存储：引擎管理数据如何在磁盘（或内存）上组织（文件格式：如 .ibd for InnoDB, .MYD/.MYI for MyISAM）。
  - 索引实现：引擎决定支持哪些索引类型（B-Tree, Hash, R-Tree, Full-Text）以及如何实现它们。
  - 事务支持：引擎是否支持事务（ACID 特性）。InnoDB 支持，MyISAM 不支持。
  - 锁机制：引擎实现行级锁、表级锁等并发控制机制。
  - 缓存/缓冲：引擎通常有自己的内存缓冲池（如 InnoDB Buffer Pool）来缓存数据和索引页，减少磁盘 I/O。
- 主要引擎：
  - InnoDB (默认且最常用)：
    - 支持事务（ACID），提供提交、回滚、崩溃恢复能力。
    - 支持行级锁，并发写性能好。
    - 支持外键约束。
    - 使用 聚集索引 (Clustered Index)：表数据按主键顺序物理存储。
    - 关键结构：Buffer Pool (缓冲池), Redo Log (重做日志 - 保证持久性), Undo Log (回滚日志 - 支持事务回滚和 MVCC)。
    - 文件：.ibd (表空间文件，存储表结构、数据、索引)。
  - MyISAM (历史遗留)：
    - 不支持事务，不支持行级锁（只有表锁），不支持外键。
    - 读性能在某些简单场景可能更快（尤其全表扫描），写并发差（表锁）。
    - 支持 FULLTEXT 索引（早期优势）。
    - 文件：.MYD (数据文件), .MYI (索引文件), .frm (表定义文件 - 8.0 后数据字典统一管理，此文件消失)。
  - 其他引擎 (较少使用)：Memory (数据存内存，重启丢失), Archive (高压缩比，只写), CSV (数据存 CSV 文件), Blackhole (接收数据但不存储)，Federated (访问远程表 - 已弃用) 等。
日志系统 (Logging Subsystem)
- 职责：保证数据的持久性 (Durability)、一致性 (Consistency)，支持主从复制 (Replication) 和 崩溃恢复 (Crash Recovery)。贯穿服务层和存储引擎层。
- 关键日志：
  - Binlog (Binary Log) - 服务层日志：
    - 逻辑日志：记录所有更改数据的 SQL 语句（statement 格式）或其引起的行数据变更（row 格式，默认推荐）或混合（mixed）。
    - 主要用途：主从复制 (Replication)（主库写 binlog，从库读 binlog 重放）和 数据恢复 (Point-in-Time Recovery - PITR)（结合 mysqldump + mysqlbinlog）。
    - sync_binlog 参数控制刷盘时机，影响数据安全性和性能。
  - Redo Log (重做日志) - InnoDB 引擎层日志：
    - 物理逻辑日志：记录物理页的修改（属于哪个表空间、哪个页、偏移量、修改内容）。
    - 目的：保证事务的持久性 (Durability)。事务提交时，只需保证 redo log 落盘（WAL - Write-Ahead Logging 原则），即使数据页还没写回磁盘，崩溃后也能通过 redo log 重放恢复已提交事务的修改。
    - 循环写入：通常由两个文件 (ib_logfile0, ib_logfile1) 组成，循环使用。
    - innodb_flush_log_at_trx_commit 参数控制刷盘策略，是性能与安全的关键权衡点（1-最安全，0/2-性能更好但有丢数据风险）。
  - Undo Log (回滚日志) - InnoDB 引擎层日志：
    - 记录事务修改前的旧版本数据。
    - 目的：
      - 支持事务回滚 (Rollback)：回滚时，用 undo log 将数据恢复到修改前的状态。
      - 实现 MVCC (Multi-Versioning Concurrency Control)：提供数据行的历史版本，供其他并发事务读取（实现 READ COMMITTED, REPEATABLE READ 隔离级别）。
    - 存储在 Undo Tablespaces 中。
  - Error Log (错误日志)：记录 MySQL 启动、运行、停止过程中的诊断信息、警告和错误。排查问题的首要位置。
  - Slow Query Log (慢查询日志)：记录执行时间超过 long_query_time 阈值的 SQL 语句。用于性能分析和优化。
  - General Query Log (通用查询日志)：记录所有客户端连接和执行的语句。调试用，对性能影响大，生产环境通常关闭。
管理工具与服务 (Management & Utilities)
- MySQL Server 进程 (mysqld)：承载上述所有核心功能。
- 各种命令行工具：位于 bin 目录（如前所述），如 mysql, mysqladmin, mysqldump, mysqlbinlog, mysqlcheck 等，用于管理、维护、备份恢复。
- 系统数据库：
  - mysql：存储用户账户、权限、存储过程/函数/触发器定义、时区、事件等信息。
  - information_schema：提供数据库元数据的只读视图（标准 SQL 方式访问），包含库、表、列、索引、权限、进程等信息。
  - performance_schema：收集 MySQL 服务器运行时的性能指标，用于监控、分析和故障诊断（如锁、I/O、内存、线程状态）。
  - sys (MySQL 5.7+)：基于 performance_schema 和 information_schema 构建的视图、存储过程和函数库，提供更易读、更常用的性能诊断报告。

核心工作流程简述 (以 SELECT 为例)：

连接：客户端通过连接器建立连接，完成认证。
查询请求：客户端发送 SELECT 语句。
解析与优化：
- 解析器进行词法、语法分析，生成解析树。
- 预处理器进行语义检查、权限初步检查、展开 * 等。
- 优化器分析各种执行路径，生成最优执行计划。
执行：
- 执行器根据计划，调用存储引擎 API。
- 存储引擎（如 InnoDB）根据执行计划（是否走索引、走哪个索引）从磁盘（或 Buffer Pool）读取数据页。
- 执行器在服务层处理 WHERE 过滤、排序、分组等（如果引擎层未完成）。
返回结果：执行器将处理好的结果集返回给连接器，再由连接器发送回客户端。

核心工作流程简述 (以 UPDATE 为例)：

连接、解析、优化、执行：前几步类似 SELECT。
引擎操作：
- 执行器调用存储引擎的 read_row 或 index_read 接口定位要修改的行（可能涉及锁）。
- 存储引擎将修改前的数据写入 Undo Log（用于回滚和 MVCC）。
- 在内存（Buffer Pool）中修改数据行。
- 将修改操作记录到 Redo Log Buffer。
提交：
- 客户端发送 COMMIT。
- 执行器通知存储引擎准备提交。
- 存储引擎将 Redo Log Buffer 内容刷到 Redo Log 文件（根据 innodb_flush_log_at_trx_commit 设置，确保刷盘）。
- 存储引擎写入 Binlog（如果配置了 sync_binlog，也确保刷盘 - 两阶段提交 (2PC) 的关键点，保证 redo log 和 binlog 逻辑一致）。
- 存储引擎在内存中标记事务提交，释放锁。
后台操作：
- InnoDB 的后台线程 (Checkpoint) 会定期将 Buffer Pool 中脏页（已修改但未写回磁盘的数据页）异步刷回磁盘。
- Purge 线程清理不再需要的旧 Undo Log 版本。

总结：

MySQL 的基础架构清晰分层（连接层、服务层、存储引擎层），并通过强大的日志系统（Binlog, Redo Log, Undo Log）保障核心特性（ACID、复制、恢复）。其可插拔存储引擎设计提供了极大的灵活性，而 InnoDB 作为默认引擎，凭借其事务支持、行级锁、崩溃恢复能力成为绝大多数场景的首选。理解这个架构对于进行性能调优、故障排查、高可用设计至关重要。