MySQL 概述与架构

MySQL 是互联网行业使用最广泛的开源关系型数据库，由瑞典 MySQL AB 公司开发，后被 Sun 收购，最终随 Sun 并入 Oracle。MySQL 凭借其高性能、高可靠性和易用性，成为 Web 应用的首选数据库。

1. MySQL 发展历史与版本演进

1.1 发展历程

时间	里程碑
1995 年	MySQL AB 成立，发布第一个内部版本
2000 年	MySQL 3.23 发布，支持 MyISAM/InnoDB 引擎
2003 年	MySQL 4.0 发布，支持 UNION、多表 DELETE
2005 年	MySQL 5.0 发布，引入存储过程、视图、触发器、游标
2008 年	Sun 收购 MySQL AB
2010 年	Oracle 收购 Sun，MySQL 5.5 发布，InnoDB 成为默认引擎
2013 年	MySQL 5.6 发布，引入 GTID 复制、全文索引 InnoDB 支持
2015 年	MySQL 5.7 发布，引入 JSON 支持、多源复制、性能大幅提升
2018 年	MySQL 8.0 发布，引入窗口函数、CTE、原子 DDL、降序索引、不可见索引

1.2 MySQL 5.7 与 8.0 关键差异

特性	MySQL 5.7	MySQL 8.0
事务数据字典	基于 frm 文件	基于 InnoDB 表（原子 DDL）
字符集默认值	latin1	utf8mb4
排序规则默认值	latin1_swedish_ci	utf8mb4_0900_ai_ci
窗口函数	不支持	支持（ROW_NUMBER、RANK、DENSE_RANK 等）
公共表表达式 CTE	不支持	支持（WITH 递归）
降序索引	不支持	支持
不可见索引	不支持	支持
直方图统计	无	支持（ANALYZE TABLE ... UPDATE HISTOGRAM）
Hash Join	不支持	支持
角色管理	不支持	支持
持久化全局变量	不支持	支持（SET PERSIST）
自增主键持久化	重启后重置为 MAX(id)+1	持久化到 redo log
JSON 函数	基础支持	增强（JSON_TABLE、JSON_ARRAYAGG 等）
资源组	不支持	支持 Resource Group
密码策略	mysql_native_password	caching_sha2_password（默认）

2. MySQL 架构分层

MySQL 整体采用分层架构设计，从上到下分为四个层次：

┌─────────────────────────────────────────────────────────────────────┐
│                        客户端 / 连接器                               │
│              JDBC / ODBC / .NET / PHP / Python / CLI                │
├─────────────────────────────────────────────────────────────────────┤
│                        连接层（Connection Layer）                    │
│        连接处理、身份验证、线程管理、连接池、SSL/TLS 加密              │
├─────────────────────────────────────────────────────────────────────┤
│                        服务层（Service Layer）                       │
│   ┌───────────┐  ┌──────────┐  ┌──────────┐  ┌──────────────────┐  │
│   │ SQL 接口   │  │  解析器   │  │  优化器   │  │   缓存与缓冲      │  │
│   │ (Parser)  │  │ (Parser) │  │(Optimizer)│  │ (Caches & Buffer)│  │
│   └───────────┘  └──────────┘  └──────────┘  └──────────────────┘  │
├─────────────────────────────────────────────────────────────────────┤
│                        引擎层（Engine Layer）                        │
│       InnoDB │ MyISAM │ Memory │ Archive │ CSV │ NDB │ ...          │
├─────────────────────────────────────────────────────────────────────┤
│                        存储层（Storage Layer）                       │
│           文件系统（NTFS / ext4 / XFS）、SAN、NAS、SSD               │
└─────────────────────────────────────────────────────────────────────┘

2.1 连接层（Connection Layer）

职责：处理客户端连接、身份验证、线程管理。

连接处理：每个客户端连接在 MySQL 中对应一个线程（或线程池中的线程），MySQL 5.5 之前是一个线程服务一个连接，5.5 之后支持线程池插件。
身份验证：支持多种认证插件，包括 mysql_native_password、caching_sha2_password（8.0 默认）、sha256_password、LDAP 认证等。
SSL/TLS：支持传输层加密，确保客户端与服务器之间的通信安全。
连接限制：通过 max_connections 参数控制最大连接数，通过 wait_timeout 和 interactive_timeout 控制空闲连接超时。

关键参数：

sql

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'wait_timeout';

2.2 服务层（Service Layer）

服务层是 MySQL 的核心，负责 SQL 语句的解析、优化和执行。

SQL 接口（SQL Interface）：

接收 SQL 语句，返回结果集。
支持 DDL、DML、DCL、TCL、存储过程、视图、触发器。

解析器（Parser）：

词法分析：将 SQL 语句分解为 Token（关键字、标识符、操作符等）。
语法分析：根据语法规则构建解析树（Parse Tree）。
语义分析：检查表名、列名是否存在，权限是否满足。

优化器（Optimizer）：

基于代价的优化器（CBO, Cost-Based Optimizer），选择最优执行计划。
统计信息：表行数、索引基数、数据分布（直方图 8.0+）。
优化策略：索引选择、JOIN 顺序、子查询优化、条件下推等。

缓存与缓冲（Caches & Buffer）：

MySQL 8.0 移除了查询缓存（Query Cache），因为它在高并发场景下成为瓶颈。
保留了表定义缓存、主机名缓存、权限缓存等。

2.3 引擎层（Engine Layer）

存储引擎层是 MySQL 区别于其他数据库的关键特性 —— 插件式存储引擎架构。

不同的存储引擎提供不同的数据存储机制、索引技术和锁定水平。
最常用的是 InnoDB（默认），其次是 MyISAM、Memory 等。
每个表可以指定不同的存储引擎，通过 ENGINE=xxx 指定。

sql

-- 查看支持的存储引擎
SHOW ENGINES;

-- 指定存储引擎建表
CREATE TABLE t (id INT) ENGINE=InnoDB;

2.4 存储层（Storage Layer）

存储层负责将数据持久化到磁盘。不同存储引擎的文件格式不同：

InnoDB：.ibd（独立表空间）、ibdata（共享表空间）、redo log、undo log。
MyISAM：.frm（表定义）、.MYD（数据文件）、.MYI（索引文件）。
文件系统选择：推荐 XFS 或 ext4，对于 SSD 建议使用 noatime、nodiratime 挂载选项。

3. InnoDB 架构详解

InnoDB 是 MySQL 默认的存储引擎，其架构设计精妙，兼顾了事务处理和高并发性能。

┌─────────────────────────────────────────────────────────────────────┐
│                          InnoDB 架构                                 │
│                                                                     │
│  ┌───────────────────────────────────────────────────────────────┐  │
│  │                     内存结构（In-Memory）                       │  │
│  │                                                                │  │
│  │  ┌──────────────────┐  ┌──────────────────┐                   │  │
│  │  │   Buffer Pool    │  │   Change Buffer  │                   │  │
│  │  │   (缓冲池)        │  │   (变更缓冲)      │                   │  │
│  │  │                  │  │                  │                   │  │
│  │  │ · 数据页          │  │ · 缓冲二级索引    │                   │  │
│  │  │ · 索引页          │  │   的变更操作      │                   │  │
│  │  │ · 插入缓冲        │  │                  │                   │  │
│  │  │ · 自适应哈希索引   │  │                  │                   │  │
│  │  │ · 锁信息          │  │                  │                   │  │
│  │  └──────────────────┘  └──────────────────┘                   │  │
│  │                                                                │  │
│  │  ┌──────────────────┐  ┌──────────────────┐                   │  │
│  │  │  Log Buffer      │  │ Adaptive Hash    │                   │  │
│  │  │  (日志缓冲)       │  │ Index (自适应哈希) │                   │  │
│  │  └──────────────────┘  └──────────────────┘                   │  │
│  └───────────────────────────────────────────────────────────────┘  │
│                              │                                      │
│                              ▼                                      │
│  ┌───────────────────────────────────────────────────────────────┐  │
│  │                     磁盘结构（On-Disk）                        │  │
│  │                                                                │  │
│  │  ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────────────┐  │  │
│  │  │ 共享表空间 │ │ 独立表空间│ │ Redo Log │ │  Undo 表空间     │  │  │
│  │  │(ibdata1) │ │ (.ibd)   │ │          │ │                  │  │  │
│  │  └──────────┘ └──────────┘ └──────────┘ └──────────────────┘  │  │
│  │  ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────────────┐  │  │
│  │  │ 通用表空间 │ │  临时表空间│ │ 双写缓冲  │ │ 系统表空间        │  │  │
│  │  │          │ │          │ │(Doublewrite│ │(mysql.ibd)      │  │  │
│  │  │          │ │          │ │  Buffer)  │ │                  │  │  │
│  │  └──────────┘ └──────────┘ └──────────┘ └──────────────────┘  │  │
│  └───────────────────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────────────────────┘

3.1 Buffer Pool（缓冲池）

Buffer Pool 是 InnoDB 最重要的内存区域，用于缓存数据页和索引页，减少磁盘 I/O。

核心特性：

大小：通常设置为物理内存的 50%-80%，通过 innodb_buffer_pool_size 配置。
实例化：通过 innodb_buffer_pool_instances 配置多个实例，减少并发竞争。建议 CPU 核心数 <= 实例数。
页管理：以页（Page，默认 16KB）为单位，使用 LRU（Least Recently Used）变体算法管理。

LRU 算法优化：

InnoDB 使用改进的 LRU 算法，将链表分为 young 区（热数据，5/8）和 old 区（冷数据，3/8）。
新读取的页放入 old 区头部，避免全表扫描污染热数据。
通过 innodb_old_blocks_pct 控制 old 区比例，innodb_old_blocks_time 控制页在 old 区存活时间。

Buffer Pool 包含的内容：

数据页（Data Page）
索引页（Index Page）
插入缓冲页（Insert Buffer Page）
自适应哈希索引页（Adaptive Hash Index Page）
锁信息（Lock Info）
数据字典信息（Data Dictionary）

关键参数：

sql

-- 查看 Buffer Pool 状态
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 查看 Buffer Pool 大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 预热 Buffer Pool（MySQL 5.6+）
-- 关闭时自动 dump，启动时自动 load
SHOW VARIABLES LIKE 'innodb_buffer_pool_dump%';

3.2 Change Buffer（变更缓冲）

Change Buffer 是 Insert Buffer 的升级版（MySQL 5.5+），用于缓冲对二级索引（非唯一索引）的 DML 操作（INSERT、UPDATE、DELETE）。

工作原理：

当要修改的二级索引页不在 Buffer Pool 中时，先将修改操作缓存到 Change Buffer。
当该页被读取到 Buffer Pool 时，再将 Change Buffer 中的操作合并（Merge）到该页。
后台线程也会定期 Merge。

优势：

减少随机 I/O：将多次随机 I/O 合并为一次顺序 I/O。
适用于写多读少的场景（如日志表、流水表）。

关键参数：

sql

SHOW VARIABLES LIKE 'innodb_change_buffering';
-- 可选值：none, inserts, deletes, changes, purges, all
SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';
-- 默认 25，表示 Change Buffer 最大占 Buffer Pool 的 25%

3.3 Adaptive Hash Index（自适应哈希索引）

InnoDB 自动监测某些索引页的访问模式，如果发现某些索引值被频繁访问，会在内存中为这些热点页构建哈希索引，加速查询。

特点：

自动创建，无需人工干预。
仅对等值查询（= 和 IN）有效，对范围查询无效。
可能出现锁竞争，高并发下可考虑关闭。

sql

SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
SHOW STATUS LIKE 'Innodb_adaptive_hash%';

3.4 Log Buffer（日志缓冲）

Log Buffer 是 redo log 的内存缓冲区，事务产生的 redo log 先写入 Log Buffer，再按策略刷入磁盘。

大小：默认 16MB（innodb_log_buffer_size），大事务可能需要调大。

刷盘策略：

sql

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

值	含义	性能	安全性
0	每秒刷盘一次	最高	可能丢失 1 秒数据
1	每次提交刷盘（默认）	最低	最安全，不丢数据
2	每次提交写入 OS 缓存，每秒刷盘	中等	丢失 1 秒数据（MySQL 宕机不丢，系统宕机可能丢）

4. InnoDB 进程结构（后台线程）

InnoDB 使用多线程架构，主要后台线程包括：

4.1 Master Thread

Master Thread 是 InnoDB 的核心后台线程，负责将缓冲池中的数据异步刷新到磁盘。

主要工作：

每秒执行：刷新脏页到磁盘、合并 Change Buffer、切 redo log 文件。
每 10 秒执行：刷新脏页、删除无用的 undo 页、合并 Change Buffer。
根据系统负载动态调整操作频率。

4.2 IO Thread

InnoDB 大量使用 AIO（异步 I/O）来处理 I/O 请求，IO Thread 负责这些操作的回调。

类型：

Read Thread：处理读请求（默认 4 个，innodb_read_io_threads）。
Write Thread：处理写请求（默认 4 个，innodb_write_io_threads）。
Insert Buffer Thread：处理 Change Buffer 的合并。
Log Thread：处理 redo log 的写入。

sql

SHOW VARIABLES LIKE 'innodb_%io_threads';
SHOW ENGINE INNODB STATUS\G

4.3 Purge Thread

Purge Thread 负责清理那些被标记为删除但实际上还没有被删除的数据（即 undo log 中标记为 delete-mark 的记录）。

当事务提交后，undo log 不再需要用于回滚，但 Purge Thread 需要真正删除标记的记录。
在 MySQL 5.6 之前，Purge 操作由 Master Thread 执行。
从 MySQL 5.6 开始可以独立配置：innodb_purge_threads（默认 4）。

4.4 Page Cleaner Thread

Page Cleaner Thread 负责将 Buffer Pool 中的脏页刷新到磁盘。

从 MySQL 5.6 开始引入，将脏页刷新从 Master Thread 中分离出来。
通过 innodb_page_cleaners 配置线程数（默认 4）。
与 innodb_max_dirty_pages_pct（默认 75%）配合，控制 Buffer Pool 中脏页比例。

5. 内存结构与磁盘结构

5.1 内存结构总结

结构	作用	关键参数
Buffer Pool	缓存数据页和索引页	innodb_buffer_pool_size
Change Buffer	缓冲二级索引修改	innodb_change_buffer_max_size
Adaptive Hash Index	热点页哈希索引	innodb_adaptive_hash_index
Log Buffer	redo log 缓冲	innodb_log_buffer_size

5.2 磁盘结构总结

结构	文件	作用
共享表空间	ibdata1	存储数据字典、undo log（5.5/5.6）、Change Buffer、双写缓冲
独立表空间	.ibd	每张表的数据和索引（5.6+ 默认）
Redo Log	ib_logfile0、ib_logfile1	重做日志，保证持久性
Undo 表空间	undo_001、undo_002	回滚日志，支持 MVCC（5.6+ 可独立）
临时表空间	ibtmp1	存储临时表数据
系统表空间	mysql.ibd	MySQL 8.0 数据字典（替代 .frm 文件）
双写缓冲	位于共享表空间	防止部分写失效

6. MySQL 关键特性

6.1 插件式存储引擎

MySQL 的存储引擎以插件形式加载，用户可以根据业务需求选择合适的引擎，甚至开发自定义引擎。

优势：

灵活选择：OLTP 用 InnoDB，归档用 Archive，临时数据用 Memory。
在线切换：ALTER TABLE t ENGINE=InnoDB;
独立演进：各引擎独立开发、独立优化。

6.2 基于代价的优化器（CBO）

MySQL 优化器基于代价模型选择最优执行计划：

统计信息：innodb_stats_persistent（持久化统计信息，默认开启）、innodb_stats_auto_recalc。
代价估算：CPU 代价 + I/O 代价，选择总代价最小的执行计划。
直方图（MySQL 8.0+）：ANALYZE TABLE t UPDATE HISTOGRAM ON col1, col2;

sql

-- 查看统计信息
SELECT * FROM mysql.innodb_table_stats;
SELECT * FROM mysql.innodb_index_stats;
-- 更新统计信息
ANALYZE TABLE t;

6.3 半同步复制（Semi-Sync Replication）

半同步复制是介于异步复制和全同步复制之间的一种方案，保证至少有一个从库接收到 binlog 后才返回客户端成功。

工作原理：

主库提交事务后，等待至少一个从库确认收到 binlog。
从库收到 binlog 后写入 relay log，回复 ACK。
主库收到 ACK 后返回客户端成功。

参数配置：

sql

-- 主库安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = ON;
SET GLOBAL rpl_semi_sync_master_timeout = 1000; -- 超时后降级为异步

-- 从库安装插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = ON;

权衡：

优点：提高数据一致性，减少主从切换时的数据丢失风险。
缺点：增加主库提交延迟，如果从库响应慢会影响主库吞吐量。

7. 生产环境最佳实践

7.1 内存配置建议

ini

# my.cnf
innodb_buffer_pool_size = 80%_of_physical_memory  # 单实例不要超过
innodb_buffer_pool_instances = 8                   # 与 CPU 核心数匹配
innodb_log_buffer_size = 64M                       # 大事务场景适当增大
innodb_change_buffer_max_size = 25                 # 写多读少可增大

7.2 安全配置

sql

-- 生产环境必须设置
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_doublewrite = ON

7.3 性能监控

sql

-- 查看 InnoDB 整体状态
SHOW ENGINE INNODB STATUS\G

-- 查看 Buffer Pool 命中率
-- 命中率 = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

8. 总结

MySQL 采用四层架构：连接层、服务层、引擎层、存储层。
InnoDB 是默认引擎，其核心内存结构是 Buffer Pool，磁盘结构包括表空间、redo log、undo log。
后台线程包括 Master Thread、IO Thread、Purge Thread、Page Cleaner Thread。
8.0 版本相比 5.7 有大量增强，包括原子 DDL、窗口函数、CTE、降序索引等。
生产环境应合理配置内存参数，确保数据安全（双 1 配置）。

MySQL 概述与架构 ​

1. MySQL 发展历史与版本演进 ​

1.1 发展历程 ​

1.2 MySQL 5.7 与 8.0 关键差异 ​

2. MySQL 架构分层 ​

2.1 连接层（Connection Layer） ​

2.2 服务层（Service Layer） ​

2.3 引擎层（Engine Layer） ​

2.4 存储层（Storage Layer） ​

3. InnoDB 架构详解 ​

3.1 Buffer Pool（缓冲池） ​

3.2 Change Buffer（变更缓冲） ​

3.3 Adaptive Hash Index（自适应哈希索引） ​

3.4 Log Buffer（日志缓冲） ​

4. InnoDB 进程结构（后台线程） ​

4.1 Master Thread ​

4.2 IO Thread ​

4.3 Purge Thread ​

4.4 Page Cleaner Thread ​

5. 内存结构与磁盘结构 ​

5.1 内存结构总结 ​

5.2 磁盘结构总结 ​

6. MySQL 关键特性 ​

6.1 插件式存储引擎 ​

6.2 基于代价的优化器（CBO） ​

6.3 半同步复制（Semi-Sync Replication） ​

7. 生产环境最佳实践 ​

7.1 内存配置建议 ​

7.2 安全配置 ​

7.3 性能监控 ​

8. 总结 ​

MySQL 概述与架构

1. MySQL 发展历史与版本演进

1.1 发展历程

1.2 MySQL 5.7 与 8.0 关键差异

2. MySQL 架构分层

2.1 连接层（Connection Layer）

2.2 服务层（Service Layer）

2.3 引擎层（Engine Layer）

2.4 存储层（Storage Layer）

3. InnoDB 架构详解

3.1 Buffer Pool（缓冲池）

3.2 Change Buffer（变更缓冲）

3.3 Adaptive Hash Index（自适应哈希索引）

3.4 Log Buffer（日志缓冲）

4. InnoDB 进程结构（后台线程）

4.1 Master Thread

4.2 IO Thread

4.3 Purge Thread

4.4 Page Cleaner Thread

5. 内存结构与磁盘结构

5.1 内存结构总结

5.2 磁盘结构总结

6. MySQL 关键特性

6.1 插件式存储引擎

6.2 基于代价的优化器（CBO）

6.3 半同步复制（Semi-Sync Replication）

7. 生产环境最佳实践

7.1 内存配置建议

7.2 安全配置

7.3 性能监控

8. 总结