MySQL（一）：架构、存储引擎与内存结构
导语：MySQL 面试的第一步是能讲清「一条 SQL 到底走了哪些环节」以及「InnoDB 凭什么快」。本篇覆盖 SQL 执行流程、逻辑架构、InnoDB 与 MyISAM 对比、内存结构（Buffer Pool / Change Buffer / 日志缓冲）、页与行结构、数据类型选型。其中架构与内存结构是原文缺失但面试必问的部分。共 12 题。
一、架构与执行流程
1. 一条 SQL 查询语句在 MySQL 中是如何执行的？
答： MySQL 分 Server 层（所有引擎共用）与存储引擎层，一条查询依次经过 5 个环节：
| 阶段 | 职责 | 关键点 |
|---|---|---|
| 1. 连接器 | 建立连接、身份认证、权限校验、连接管理 | 权限在连接建立时确定，中途改权限不影响已有连接；max_connections 默认 151；空闲超 wait_timeout 会被断开 |
| 2. 查询缓存 | 以 SQL 文本为 key 查缓存 | MySQL 8.0 已彻底移除——只要表有任意更新，该表所有缓存全部失效，命中率极低反而拖慢性能 |
| 3. 解析器 | 词法分析 + 语法分析，生成解析树 | 语法错误在此报 You have an error in your SQL syntax |
| 4. 优化器 | 决定用哪个索引、多表连接的顺序，生成执行计划 | 基于成本模型（cost）估算，这就是"优化器选错索引"发生的环节 |
| 5. 执行器 | 调用引擎接口逐行取数、过滤、返回 | 5.6 之前 Server 层还要再过滤一次；引入 ICP（索引条件下推） 后过滤可下推到引擎层 |
| 6. 存储引擎 | 真正读写数据页 | InnoDB 通过 Buffer Pool、B+Tree 索引、redo/undo 完成 |
更新语句会多一条日志链路：写 undo log → 更新 Buffer Pool 中的数据页（产生脏页）→ 写 redo log（prepare） → 写 binlog → redo log 置 commit。这条链路就是后面要讲的两阶段提交。
2. MySQL 的逻辑架构分为哪些层？
答： 自顶向下分三层：
| 层 | 组成 | 作用 |
|---|---|---|
| 连接层 | 连接池、线程管理、认证鉴权 | 处理客户端连接，每个连接一个线程 |
| Server 层 | 解析器、优化器、执行器、所有内置函数、跨引擎功能（视图、触发器、存储过程） | 与存储引擎无关的通用逻辑都在这层 |
| 存储引擎层 | InnoDB、MyISAM、Memory、Archive 等，通过统一的 API 对接 Server 层 | 负责数据的实际存取，插件式可替换 |
关键理解点：因为有了"存储引擎插件化"这层抽象，Server 层才能对所有引擎统一提供服务；也正因如此，事务、锁、MVCC、日志这些能力属于引擎层（所以 MyISAM 完全没有事务），而binlog 属于 Server 层（所有引擎通用）。
3. InnoDB 和 MyISAM 有什么区别？
答： 这是高频必考题（MySQL 5.5 起 InnoDB 成为默认引擎）：
| 维度 | InnoDB | MyISAM |
|---|---|---|
| 事务 | 支持（ACID） | 不支持 |
| 锁粒度 | 行级锁（默认）+ 表级锁 | 只有表级锁 |
| MVCC | 支持（读写不阻塞） | 不支持 |
| 外键 | 支持 | 不支持 |
| 崩溃恢复 | 支持（靠 redo log） | 不支持，崩溃后可能表损坏需 REPAIR TABLE |
| 索引结构 | B+Tree，聚簇索引（叶子存完整行） | B+Tree，非聚簇（叶子存数据文件地址） |
| 全文索引 | 5.6+ 支持 | 原生支持（早期优势） |
| COUNT(*) | 需遍历索引（较慢） | 内部维护行数计数器（极快） |
| 存储文件 | .ibd（表空间） | .MYD（数据）+ .MYI（索引） |
结论：绝大多数场景用 InnoDB；MyISAM 仅在"只读为主、不需要事务、追求 COUNT 极快"的历史场景才考虑，现已基本淘汰。
加分点：MyISAM 的
COUNT(*)快是因为它把行数存在表元数据里，但有 WHERE 条件时同样要扫描，并不总是快。
二、InnoDB 内存结构
4. InnoDB 的内存结构由哪些部分组成？
答： InnoDB 的内存主要有四块：
| 组件 | 作用 | 关键参数 |
|---|---|---|
| Buffer Pool（缓冲池） | 最核心。缓存数据页与索引页，读写都先在内存中进行 | innodb_buffer_pool_size，默认 128MB，生产建议设为物理内存的 50%~80% |
| Change Buffer（写缓冲） | 缓存非唯一二级索引的写操作，延迟合并，减少随机 IO | innodb_change_buffer_max_size（默认占 Buffer Pool 的 25%） |
| Adaptive Hash Index（自适应哈希索引） | InnoDB 自动为热点等值查询建哈希索引，把 B+Tree 查找降为 O(1) | innodb_adaptive_hash_index |
| Log Buffer（日志缓冲） | 缓存 redo log，再批量刷盘 | innodb_log_buffer_size；刷盘时机由 innodb_flush_log_at_trx_commit 控制 |
设计思想："所有读写在内存完成，脏页异步刷盘" —— 这就是 WAL（Write-Ahead Logging）策略，把随机写转成内存操作 + 顺序日志写。
innodb_flush_log_at_trx_commit三个取值的权衡（高频追问）：
1（默认）：每次提交都 fsync redo log，不丢数据但性能最低；0：每秒刷一次，实例崩溃可能丢 1 秒数据；2：每次提交写 OS 缓冲，操作系统崩溃才会丢数据。配合
sync_binlog=1才能保证 binlog 与 redo 都不丢（这就是常说的"双 1 配置"）。
5. 什么是 Buffer Pool？它的 LRU 为什么要改进？
答： Buffer Pool 是 InnoDB 缓存数据页与索引页的内存区域，读写都先经过它。它用 LRU（最近最少使用） 算法管理页的淘汰。
传统 LRU 的问题——缓冲池污染：若用简单的"新页放头部、淘汰尾部"，那么一次全表扫描或预读会把大量"只用一次"的页塞到 LRU 头部，把真正的热点页挤出缓冲池，导致缓存命中率骤降。
InnoDB 的改进（分代 LRU）：
- 把 LRU 链表分成 young 区（热数据，约 5/8） 与 old 区（冷数据，约 3/8）；
- 新读入的页先放在 old 区的头部（midpoint，中点插入法），而不是整个链表的头部；
- 只有满足"在 old 区停留超过
innodb_old_blocks_time（默认 1000ms）后再次被访问"，才会晋升到 young 区。
效果：全表扫描读进来的页只在 old 区转一圈就被淘汰，不会污染 young 区的热点数据；而真正被反复访问的页仍能晋升为热数据。
两个配套优化：① 预读（read-ahead）——顺序读时提前把后续页读入；② 脏页刷盘（checkpoint）——后台线程按 LRU 与 LSN 策略把脏页渐进刷盘，避免一次性刷盘造成抖动。
6. 什么是 Change Buffer？为什么它只对非唯一索引有效？
答： Change Buffer（写缓冲） 是 Buffer Pool 中的一块区域，用于缓存非唯一二级索引的 INSERT/UPDATE/DELETE 变更。
工作原理：当要修改的二级索引页不在 Buffer Pool 中时，InnoDB 不立即从磁盘读入该页，而是先把变更记录到 Change Buffer；等之后该页因其他查询被读入内存时，再把这些变更合并（merge）到页中。这样就把多次随机 IO 合并成一次，显著提升写性能。
为什么只对非唯一索引有效？ 这是本题的核心：
- 唯一索引插入/更新前，必须读一次索引页来校验"是否会违反唯一性约束"——这次读盘无论如何都躲不掉，所以"延迟合并"没有任何意义；
- 非唯一索引不需要做唯一性校验，才能放心地"先记账、后合并"。
适用场景：写多读少、且非唯一二级索引较多的表收益最大；若表上几乎都是唯一索引，Change Buffer 形同虚设。
与 Buffer Pool 的关系：Change Buffer 的容量上限由
innodb_change_buffer_max_size控制（默认 25%）。它也是 InnoDB 启动时"崩溃恢复慢"的原因之一——需要把 Change Buffer 里的变更合并回页。
三、页、行与数据类型
7. InnoDB 的数据页与行格式是怎样的？
答： InnoDB 以页（Page）为最小磁盘 IO 单位，默认 16KB（innodb_page_size 可设 4/8/16/32/64KB）。
页的内部结构（简化）：
| 区域 | 作用 |
|---|---|
| File Header | 页号、上一页/下一页指针（叶子页用它构成双向链表）、校验和 |
| Page Header | 页内记录数、空闲空间位置、页类型等 |
| Infimum / Supremum | 两条虚拟记录，分别表示页内的最小与最大值，充当边界哨兵 |
| User Records | 真正的行记录 |
| Free Space | 未使用的空间 |
| Page Directory | 稀疏槽位，支持页内二分查找（页内不用遍历） |
| File Trailer | 校验和，防止页写入不完整 |
行格式（Row Format）：REDUNDANT（老）、COMPACT、DYNAMIC（5.7+ 默认）、COMPRESSED。
行溢出（Off-page Overflow）：当一行数据过大（超过约半页）时，变长大字段会被放到独立的「溢出页」，行内只保留20 字节的指针；DYNAMIC 格式会完整移到溢出页，而老的 COMPACT 会在行内保留 768 字节前缀。
为什么是 16KB：与文件系统块大小（通常 4KB）成倍数关系、单次 IO 效率高，同时兼顾"一页能放下足够多的行"——是经验上的最优折中。若把页改小，树会变高（IO 次数增加）；改大则单次 IO 浪费严重。
8. 什么是页分裂与页合并？如何避免？
答：
页分裂（Page Split）：当一条新记录要插入的页已经满了，InnoDB 会新建一个页，把原页的一部分记录搬过去，并在上层索引节点维护新的指针。代价很高——一次插入可能引发多次写（写放大），还会造成页内空间碎片。
触发原因：插入位置在页中间（即无序插入）。典型场景就是用 UUID 做主键：新主键的大小是随机的，插入点随机落在各页中间，导致频繁分裂。
页合并（Page Merge）：当页内记录被大量删除，剩余空间超过 MERGE_THRESHOLD（默认 50%）时，InnoDB 会尝试把该页与相邻页合并，回收空间。
避免方式：
- 主键用自增（有序）：新记录永远追加到最右侧页，只在写满时顺序新开页，几乎不触发中间分裂；
- 避免频繁更新变长字段（如把
VARCHAR(10)改成很长的值），更新过长可能需要把记录移到新页； - 合理设置
innodb_fill_factor（预留一定空间给未来的插入）。
这也是「为什么推荐自增主键」最本质的原因——不只是"索引短"，更是"写入有序、避免页分裂"。
9. CHAR 与 VARCHAR 有什么区别？如何选择？
答：
| 维度 | CHAR | VARCHAR |
|---|---|---|
| 长度 | 定长，不足部分用空格补齐（读取时去掉尾部空格） | 变长，按实际长度存储 |
| 额外开销 | 无 | 需要 1~2 字节存长度（≤255 字节用 1 字节，>255 用 2 字节） |
| 最大长度 | 0~255 个字符 | 65535 字节（还受整行 65535 字节上限约束） |
| 空间利用率 | 固定，短值会浪费 | 无浪费 |
| 读写 | 无需解析长度，略快；更新不产生碎片 | 需读长度前缀；频繁变长更新会产生页内碎片 |
| 适用 | 长度固定或接近固定：MD5、UUID、手机号、性别、状态码 | 长度差异大：姓名、地址、备注、URL |
选择原则：
- 长度基本固定 →
CHAR（如CHAR(32)存 MD5）； - 长度差异大 →
VARCHAR； - 不要把
VARCHAR开得过小导致频繁截断，也不要都开成VARCHAR(255)（虽然变长存储，但过长的声明会影响某些临时表/索引的内存估算）。
坑点：
CHAR在存储时会去掉尾部空格，比较时也可能受影响（取决于PAD_CHAR_TO_FULL_LENGTH等设置），存需要保留尾部空格的字符串时要用VARCHAR。
10. utf8 与 utf8mb4 有什么区别？
答：
| 字符集 | 每字符最大字节 | 能存的字符 |
|---|---|---|
utf8（即 utf8mb3） | 3 字节 | 只能存基本多文种平面（BMP），无法存 emoji、部分生僻汉字 |
utf8mb4 | 4 字节 | 完整 UTF-8，包括 emoji、扩展汉字 |
结论：一律使用 utf8mb4。理由：
utf8是 MySQL 的历史遗留（为了省空间而限制 3 字节），不是完整的 UTF-8；- 存 emoji 会直接报
Incorrect string value，或用utf8存储时被截断/替换； - MySQL 8.0 已把默认字符集改为
utf8mb4（character_set_server），utf8也被明确标注为utf8mb3。
注意点：
- 排序规则（collation）推荐
utf8mb4_general_ci（不区分大小写、性能好）或utf8mb4_unicode_ci/utf8mb4_0900_ai_ci（更符合 Unicode 排序规则）； - 由
utf8mb4改回utf8会失败（若已有 4 字节字符），反向则安全； - 索引长度限制：
utf8mb4下单个索引列最大约 191 字符（767/4）——若表用COMPACT行格式且innodb_large_prefix关闭。MySQL 5.7+ 默认DYNAMIC行格式 + 开启大前缀，上限提升到 3072 字节，一般不再受限。
11. INT(11)、DECIMAL、FLOAT 这些类型该怎么理解？
答：
1）INT(11) 的 11 是显示宽度，不是存储长度
- 它只影响
ZEROFILL时的补零位数，与取值范围、占用字节完全无关（INT永远占 4 字节，范围-2^31 ~ 2^31-1）； - MySQL 8.0.17 起显示宽度已被废弃，写
INT即可。
2）金额绝不能使用 FLOAT/DOUBLE
FLOAT/DOUBLE是浮点数，存在二进制表示的精度误差（如0.1 + 0.2 != 0.3），累加后误差会放大；- 正确做法：
DECIMAL(M,D)定点数（精确小数，如DECIMAL(10,2)表示最多 10 位数、2 位小数），或用BIGINT存储"分"（避免小数运算）。
3）时间类型的选择
| 类型 | 字节 | 范围 | 特点 |
|---|---|---|---|
DATETIME | 8 | 1000-01-01 ~ 9999-12-31 | 与时区无关，存什么读什么 |
TIMESTAMP | 4 | 1970-01-01 ~ 2038-01-19 | 存 UTC，随会话时区变化；有 2038 问题 |
DATE | 3 | 日期 | 只存日期 |
结论：需要跨时区、要自动更新用
TIMESTAMP；需要长期存储、不受时区影响用DATETIME（这也是多数业务的选择）。
12. 自增主键用完了怎么办？
答：
先明确"用完"是什么：AUTO_INCREMENT 的上限取决于列类型——有符号 INT 的上限是 2147483647。达到上限后再插入会直接报错：Duplicate entry '2147483647' for key 'PRIMARY'（因为自增值不再增加，产生重复）。
解决方案：提前升级为 BIGINT
ALTER TABLE t MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;BIGINT UNSIGNED 的上限是 18446744073709551615，即使每秒插入 100 万条也能用约 58 万年，实际等同于"用不完"。
三个实践要点：
- 不要等用完才处理——
ALTER大表是有代价的，应在数据量到千万~亿级时就规划升级；线上大表要用pt-online-schema-change或gh-ost做在线 DDL，避免长时间锁表； - 自增值不会因为删数据而回退，但 MySQL 5.7 及之前重启后可能回退到
max(id)+1（若自增值未持久化），在删除过最大 id 的记录后重启，可能出现主键冲突；MySQL 8.0 已把自增值持久化到 redo log，重启不再回退； - 分布式场景下自增主键本身就不适用，需要全局唯一 ID（雪花、号段等），详见分库分表篇。