跳至主要內容

MySQL 体系架构

HSXISAWD2026/3/20大约 12 分钟数据库MySQL架构原理

MySQL 体系架构

一、MySQL 整体架构概览

MySQL 采用分层架构设计，主要分为以下几层：

二、连接层详解

2.1 连接管理

MySQL 连接器负责跟客户端建立连接、获取权限、维持和管理连接。

-- 查看当前连接
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 查看连接状态
SHOW STATUS LIKE 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 2     |  -- 缓存中的线程数
| Threads_connected | 5     |  -- 当前连接数
| Threads_created   | 100   |  -- 创建过的线程数
| Threads_running   | 1     |  -- 正在运行的线程数
+-------------------+-------+

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

2.2 连接池配置

[mysqld]
# 最大连接数
max_connections = 500

# 最大用户连接数
max_user_connections = 400

# 连接超时时间（秒）
wait_timeout = 28800
interactive_timeout = 28800

# 连接错误计数
max_connect_errors = 100

# 线程缓存
thread_cache_size = 50

2.3 连接状态详解

状态说明
Sleep等待客户端发送新请求
Query正在执行查询
Locked等待表锁
Copying to tmp table执行查询并复制结果到临时表
Sending data正在发送数据给客户端
Sorting result正在排序结果集

2.4 长连接与短连接

长连接问题：MySQL 执行过程中临时使用的内存是管理在连接对象里的，长期累积可能导致内存占用过大。

解决方案：

-- 方案1：定期断开长连接
-- 方案2：MySQL 5.7+ 执行 mysql_reset_connection 重置连接资源

-- 查看连接内存使用
SELECT
    thread_id,
    user,
    current_allocated
FROM performance_schema.memory_summary_by_thread_by_event_name
WHERE event_name = 'memory/sql/THD::main_mem_root';

三、服务层详解

3.1 SQL 接口

SQL 接口接收用户的 SQL 命令，并返回用户需要查询的结果。

支持的操作：

  • DDL：数据定义语言（CREATE、ALTER、DROP）
  • DML：数据操作语言（INSERT、UPDATE、DELETE）
  • DQL：数据查询语言（SELECT）
  • DCL：数据控制语言（GRANT、REVOKE）
  • TCL：事务控制语言（COMMIT、ROLLBACK）

3.2 解析器（Parser）

解析器对 SQL 语句进行语法分析、语义分析。

词法分析

将 SQL 语句拆分成一个个 "单词"：

SELECT id, name FROM users WHERE status = 1

词法分析结果：
Token: SELECT  (关键字)
Token: id      (标识符)
Token: ,       (分隔符)
Token: name    (标识符)
Token: FROM    (关键字)
Token: users   (标识符)
Token: WHERE   (关键字)
Token: status  (标识符)
Token: =       (操作符)
Token: 1       (数值)

语法分析

根据语法规则构建语法树（AST）：

                   SELECT
                  /   |   \
             columns  FROM  WHERE
              /  \      |      \
            id   name  users   =
                              / \
                         status  1

3.3 优化器（Optimizer）

优化器在表里有多个索引时决定使用哪个索引，在多表关联时决定表的连接顺序。

优化器类型

查看执行计划

-- 基本执行计划
EXPLAIN SELECT * FROM users WHERE status = 1;

-- 详细格式
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE status = 1;
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE status = 1;

-- 实际执行分析（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM users WHERE status = 1;

优化器开关

-- 查看优化器开关
SHOW VARIABLES LIKE 'optimizer_switch';

-- 临时关闭某个优化
SET optimizer_switch = 'index_merge=off';

-- 使用优化器提示（Hints）
SELECT /*+ NO_INDEX_MERGE(users) */ * FROM users WHERE a = 1 OR b = 2;
SELECT /*+ JOIN_ORDER(t1, t2, t3) */ * FROM t1 JOIN t2 JOIN t3;

3.4 执行器（Executor）

执行器调用存储引擎接口执行 SQL。

-- 查看执行器扫描行数
SHOW STATUS LIKE 'Handler%';

-- 重要指标
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_read_first         | 5     |  -- 读取索引第一项次数
| Handler_read_key           | 100   |  -- 通过索引读取行的次数
| Handler_read_next          | 500   |  -- 按顺序读取下一行的次数
| Handler_read_rnd           | 10    |  -- 随机读取行的次数
| Handler_read_rnd_next      | 1000  |  -- 全表扫描读取下一行的次数
+----------------------------+-------+

四、查询缓存（已废弃）

⚠️ 注意：MySQL 8.0 已完全移除查询缓存功能

4.1 为什么移除

  1. 命中率低：只要表有任何更新，该表的所有查询缓存都会失效
  2. 维护成本高：需要维护缓存一致性，对写操作有额外开销
  3. 锁竞争：查询缓存使用全局锁，高并发下成为瓶颈
  4. 内存管理复杂：缓存碎片问题

4.2 替代方案

五、一条 SQL 的完整执行流程

以查询语句为例：

SELECT id, name, age FROM users WHERE status = 1 AND age > 18 ORDER BY age LIMIT 10;

5.1 连接阶段

5.2 解析阶段

语法分析：构建语法树，验证语法正确性

语义分析：验证表和列是否存在

5.3 优化阶段

5.4 执行阶段

5.5 执行流程图

六、存储引擎层

6.1 存储引擎概述

MySQL 支持插件式存储引擎架构，不同的存储引擎有不同的特性。

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

+--------------------+---------+----------------------------------------------------------------+
| Engine             | Support | Comment                                                        |
+--------------------+---------+----------------------------------------------------------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys    |
| MyISAM             | YES     | Non-transactional engine with good full-text search support   |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables     |
| CSV                | YES     | CSV storage engine                                             |
| ARCHIVE            | YES     | Archive storage engine                                         |
| BLACKHOLE          | YES     | /dev/null storage engine                                       |
| FEDERATED          | NO      | Federated MySQL storage engine                                 |
+--------------------+---------+----------------------------------------------------------------+

-- 查看默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine';

-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'users';

6.2 存储引擎对比

特性InnoDBMyISAMMemory
事务支持是否否
行级锁是否（表锁）否（表锁）
外键是否否
崩溃恢复是否否（数据丢失）
MVCC是否否
全文索引是（5.6+）是否
存储限制64TB256TB受限于内存
缓存数据和索引仅索引N/A

6.3 InnoDB 架构详解

七、日志系统

7.1 日志类型概览

日志类型作用存储位置
Redo Log崩溃恢复，保证持久性ib_logfile0/1
Undo Log事务回滚，MVCC系统表空间/Undo 表空间
Binary Log主从复制，数据恢复mysql-bin.xxxxxx
Error Log错误和警告信息hostname.err
Slow Query Log慢查询分析hostname-slow.log
General Log所有 SQL 语句一般不开启

7.2 Redo Log（重做日志）

InnoDB 特有的日志，用于保证事务的持久性。

# Redo Log 配置
[mysqld]
# Redo Log 大小（每个文件）
innodb_log_file_size = 256M

# Redo Log 文件数量
innodb_log_files_in_group = 2

# Redo Log Buffer 大小
innodb_log_buffer_size = 64M

# 刷盘策略
# 0 = 每秒刷盘（可能丢失1秒数据）
# 1 = 每次提交都刷盘（默认，最安全）
# 2 = 每次提交写到OS缓存，每秒刷盘
innodb_flush_log_at_trx_commit = 1

7.3 Binary Log（二进制日志）

Server 层日志，用于主从复制和数据恢复。

-- 查看 binlog 配置
SHOW VARIABLES LIKE '%log_bin%';

-- 查看 binlog 列表
SHOW BINARY LOGS;

-- 查看 binlog 事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- 使用 mysqlbinlog 解析
mysqlbinlog --no-defaults mysql-bin.000001
mysqlbinlog --no-defaults --start-datetime="2024-01-01 00:00:00" mysql-bin.000001

# Binary Log 配置
[mysqld]
# 开启 binlog
log_bin = /var/log/mysql/mysql-bin

# binlog 格式
# STATEMENT = 记录SQL语句
# ROW = 记录行变化（推荐）
# MIXED = 混合模式
binlog_format = ROW

# binlog 过期时间（天）
expire_logs_days = 7
# MySQL 8.0+
binlog_expire_logs_seconds = 604800

# 单个 binlog 最大大小
max_binlog_size = 256M

# 刷盘策略
sync_binlog = 1  # 每次提交刷盘

7.4 两阶段提交

为保证 Redo Log 和 Binlog 的一致性，MySQL 使用两阶段提交：

7.5 Update 语句执行流程

UPDATE users SET name = 'test' WHERE id = 1;

1. 执行器找存储引擎取 id=1 这一行
2. 如果 id=1 这一行所在的数据页在 Buffer Pool 中，直接返回
3. 如果不在，从磁盘读入 Buffer Pool，再返回
4. 执行器拿到这一行，修改 name 值，得到新行
5. 调用存储引擎写入新行
6. 存储引擎将新行更新到 Buffer Pool
7. 同时写入 Redo Log，此时 Redo Log 处于 prepare 状态
8. 执行器生成这个操作的 Binlog，写入磁盘
9. 执行器调用存储引擎的提交事务接口
10. 存储引擎把刚刚写入的 Redo Log 改成 commit 状态
11. 更新完成

八、Buffer Pool 详解

8.1 Buffer Pool 结构

8.2 LRU 算法优化

MySQL 使用改进的 LRU 算法，将 LRU 列表分为两部分：

这样设计可以避免一次全表扫描将热点数据从 Buffer Pool 中淘汰。

8.3 Buffer Pool 配置

[mysqld]
# Buffer Pool 总大小（建议设置为物理内存的 60-80%）
innodb_buffer_pool_size = 8G

# Buffer Pool 实例数（建议设置为 CPU 核心数）
innodb_buffer_pool_instances = 8

# 每个实例的大小 = 总大小 / 实例数
# 8G / 8 = 1G per instance

# 预热：重启时加载之前的热数据
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

-- 查看 Buffer Pool 状态
SHOW ENGINE INNODB STATUS\G

-- 关键指标
SHOW STATUS LIKE 'Innodb_buffer_pool%';

+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_pages_total        | 524288      |  -- 总页数
| Innodb_buffer_pool_pages_free         | 100000      |  -- 空闲页数
| Innodb_buffer_pool_pages_data         | 400000      |  -- 数据页数
| Innodb_buffer_pool_pages_dirty        | 1000        |  -- 脏页数
| Innodb_buffer_pool_read_requests      | 100000000   |  -- 读请求数
| Innodb_buffer_pool_reads              | 10000       |  -- 磁盘读次数
+---------------------------------------+-------------+

-- 计算命中率
命中率 = (read_requests - reads) / read_requests * 100%
       = (100000000 - 10000) / 100000000 * 100%
       = 99.99%
Default footer
Copyright © 2026 HSXISAWD