罗罗编程学习记录
本页内容 展开查看

05.2.1.1 MySQL 架构与执行流程

所属:全栈开发面试题库 / 05 数据库与存储 / 05.2 MySQL / 05.2.1 架构、InnoDB 与事务并发

覆盖范围

  • MySQL 分层架构:Server 层与存储引擎层
  • 一条 SELECT / UPDATE / DDL 的完整执行链路
  • 连接器、解析器、优化器、执行器职责边界
  • 后台线程、内部临时表、统计信息与连接管理
  • MySQL 8.0 关键变化,与 PostgreSQL 进程模型对照

高频题速览

编号 题目 难度 频率
05.2.1.1-001 MySQL 整体架构分哪几层 基础 ★★★★★
05.2.1.1-002 一条 SELECT 的执行流程 进阶 ★★★★★
05.2.1.1-003 一条 UPDATE 的完整执行流程 高级 ★★★★★
05.2.1.1-006 InnoDB 后台线程模型 高级 ★★★★☆
05.2.1.1-007 查询缓存为什么在 8.0 被移除 基础 ★★★★☆

架构主线

客户端连接
  → 连接器(权限 / 线程)
    → 解析器(词法 / 语法 / 语义)
      → 优化器(索引选择 / 连接顺序 / 代价)
        → 执行器(调存储引擎接口)
          → 存储引擎层(InnoDB:缓冲池 / 日志 / 页)

05.2.1.1-001 MySQL 的整体架构分为哪几层?

频率 ★★★★★ | 难度 基础

一句话结论

MySQL 分两层:上层是与存储引擎无关的 Server 层(连接器、解析器、优化器、执行器、缓存与通用函数),下层是存储引擎层(InnoDB、MyISAM 等,通过插件接口被执行器调用)。

概念解释

  • Server 层:负责连接管理、SQL 解析、查询优化、执行调度与跨引擎通用能力,所有引擎共享。
  • 存储引擎层:负责数据的存取、事务、锁与索引实现,插件式可插拔;同一实例可同时挂载多种引擎。
  • 分层意义:解析优化在 Server 层完成,物理存取交给引擎,换引擎不影响上层 SQL 处理逻辑。

原理流程

  1. 客户端连接先到 Server 层连接器,校验账号权限并绑定线程。
  2. SQL 经解析器生成解析树,优化器选出执行计划。
  3. 执行器按计划调用引擎接口读写数据,引擎返回结果行给 Server 层再过滤、聚合后回传。

代码 / 命令

-- 查看当前实例挂载的存储引擎
SHOW ENGINES;
-- 查看某表使用的引擎
SHOW TABLE STATUS LIKE 'orders'\G

常见误区

  • 误区:索引、事务、锁都在 Server 层实现。正解:这些是存储引擎能力,InnoDB 与 MyISAM 行为差异正源于此。
  • 误区:一个实例只能用一种引擎。正解:每张表可指定不同引擎,引擎按表区分。

追问

  • 问:Server 层与引擎层以什么接口交互?答:执行器通过引擎抽象接口(handler 接口)调用下层方法,如读行、写行、索引查找。
  • 问:SQL 优化在哪一层?答:在 Server 层的优化器,与具体引擎无关,但优化器要参考引擎提供的成本与统计信息。

05.2.1.1-002 一条 SELECT 语句的执行流程是什么?

频率 ★★★★★ | 难度 进阶

一句话结论

SELECT 链路为:连接器校验 →(8.0 无查询缓存)解析器词法语法解析 → 预处理器语义检查 → 优化器生成执行计划 → 执行器按计划调引擎取数 → Server 层做过滤、排序、聚合后返回结果集。

概念解释

  • 词法/语法解析:把 SQL 文本切成词元并校验语法,生成解析树。
  • 语义检查:检查表、列是否存在,权限是否足够,名称是否消歧。
  • 优化器:基于统计信息估算各访问路径代价,选索引、定连接顺序。
  • 执行器:按计划逐行调用引擎接口,在 Server 层完成剩余过滤与聚合。

原理流程

  1. 连接器校验账号与连接,分配线程。
  2. 解析器生成解析树,预处理阶段校验对象与权限。
  3. 优化器确定访问路径与连接顺序,输出执行计划。
  4. 执行器调引擎读取记录,引擎从缓冲池或磁盘取页;Server 层按 WHERE、ORDER BY、GROUP BY 处理后返回。

代码 / 命令

-- 查看优化器最终选出的计划
EXPLAIN SELECT o.id, u.name
FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID'
ORDER BY o.created_at DESC LIMIT 20;

常见误区

  • 误区:SELECT 也会写 redo/undo。正解:纯快照读不改数据,不产生 undo;当前读(锁定读)才会加锁与写日志。
  • 误区:优化器选出的索引一定最优。正解:基于统计估算,统计过期或数据倾斜时可能选错,详见 05.2.2.2。

追问

  • 问:解析器和预处理器分工是什么?答:解析器做词法语法,预处理器做语义(对象存在性、权限、列名消歧)。
  • 问:执行器什么时候才真正读磁盘?答:缓冲池未命中对应页时,由引擎发起磁盘 IO 把页读入缓冲池。

05.2.1.1-003 一条 UPDATE 语句的完整执行流程是什么?

频率 ★★★★★ | 难度 高级

一句话结论

UPDATE 先经 Server 层解析优化,执行器调引擎定位并锁定目标行;引擎在缓冲池修改页,写 redo log(准备/提交)与 undo log,Server 层写 binlog,经两阶段提交保证 redo 与 binlog 一致,脏页由后台刷盘。

概念解释

  • 当前读:UPDATE 读到的是最新已提交版本,并对目标行加写锁,区别于快照读。
  • undo log:记录修改前旧版本,用于回滚与 MVCC 版本链。
  • redo log:记录页的物理修改,保证崩溃后已提交事务可重做。
  • binlog:Server 层逻辑日志,用于主从复制与备份恢复。

原理流程

  1. 连接器与解析优化同上;执行器按计划定位行(无索引会扫描并加锁,见 05.2.2.4)。
  2. 引擎加行锁,读入缓冲池页,生成 undo 旧版本,修改数据页为脏页。
  3. 写 redo log(prepare 阶段),提交时 Server 写 binlog,再将 redo 置为 commit,完成两阶段提交。
  4. 脏页后续由 checkpoint 刷盘;事务回滚时用 undo 恢复旧版本。

代码 / 命令

-- 查看正在执行的更新与锁等待
SELECT * FROM performance_schema.data_locks;
SELECT * FROM sys.innodb_lock_waits;

UPDATE orders SET status = 'PAID' WHERE id = 1001;

常见误区

  • 误区:UPDATE 立刻把脏页刷到磁盘。正解:先改缓冲池页,刷盘由 checkpoint 异步完成;持久性由 redo log 保证。
  • 误区:binlog 由 InnoDB 直接写。正解:binlog 是 Server 层日志,redo/undo 才是引擎层日志。

追问

  • 问:崩溃时如何判断该事务是否提交?答:靠 redo prepare 状态与 binlog 是否落盘判定,详见 05.2.1.3。
  • 问:UPDATE 没命中索引会发生什么?答:扫描范围行会被加锁,间隙锁/临键锁可能扩大到全表,详见 05.2.2.4。

05.2.1.1-004 连接器、查询缓存、解析器、优化器、执行器各做什么?

频率 ★★★★☆ | 难度 进阶

一句话结论

连接器管连接与权限;解析器做词法语法解析;优化器选访问路径;执行器按计划调用引擎;查询缓存是 8.0 之前的结果级缓存,8.0 已整体移除。

概念解释

  • 连接器:握手认证、维护连接生命周期、校验每次语句的权限。
  • 解析器:词法分析拆分标识符,语法分析生成解析树。
  • 优化器:基于代价选索引、定连接顺序与改写方式。
  • 执行器:打开表、按计划调用引擎接口,逐行过滤返回。

原理流程

  • 连接建立后线程复用;语句依次流经解析、优化、执行。
  • 8.0 之前若查询缓存命中,SQL 文本哈希一致可直接返回结果,绕过解析与优化。

代码 / 命令

-- 查看连接与线程状态
SHOW PROCESSLIST;
SELECT * FROM performance_schema.threads;

常见误区

  • 误区:连接器在每次执行语句时重新做账号密码校验。正解:认证在连接握手完成,后续语句做权限校验即可。
  • 误区:查询缓存适合高并发读场景值得自己补回。正解:写频繁即失效,且缓存击穿与一致性问题多,8.0 移除后应由应用层/Redis 承担缓存。

追问

  • 问:执行器如何知道走哪个索引?答:索引选择是优化器决定的,执行器只按计划执行。
  • 问:权限校验在哪一步?答:连接器连接时校验账号,解析后预处理器再校验对象权限。

05.2.1.1-005 Server 层与存储引擎层如何解耦?

频率 ★★★★☆ | 难度 进阶

一句话结论

Server 层通过统一的引擎 handler 接口向下调用,引擎向上暴露读行、写行、索引查找等能力;上层不感知引擎内部存储格式,切换引擎或新增引擎不需要改动 SQL 处理代码。

概念解释

  • handler 抽象:执行器面向接口编程,不直接操作某引擎的文件结构。
  • 引擎能力差异:事务、外键、全文索引、锁语义由各引擎自行实现,Server 层只调用其暴露的能力。
  • 插件式架构:引擎以插件形式注册,SHOW ENGINES 可见。

原理流程

  1. 执行器根据表定义找到对应引擎实例。
  2. 通过接口发起表扫描或索引定位请求。
  3. 引擎返回记录句柄,执行器继续上层处理。

代码 / 命令

CREATE TABLE t_innodb (id INT PRIMARY KEY) ENGINE = InnoDB;
CREATE TABLE t_memory (id INT PRIMARY KEY) ENGINE = Memory;

常见误区

  • 误区:Server 层也实现了行锁。正解:行锁是引擎能力,MyISAM 只有表锁。
  • 误区:换引擎后 SQL 写法完全不变、行为也不变。正解:语法兼容,但事务隔离、锁、索引行为可能不同。

追问

  • 问:为什么外键约束只在部分引擎支持?答:约束的检查与级联需要引擎在写入路径上实现,Server 层不强制。
  • 问:一张表能在运行时换引擎吗?答:可以用 ALTER TABLE ... ENGINE=InnoDB,但会重建表,大表需走在线 DDL。

05.2.1.1-006 InnoDB 有哪些后台线程?

频率 ★★★★☆ | 难度 高级

一句话结论

InnoDB 后台线程包括主线程、IO 线程(读/写/日志/插入缓冲)、刷脏页线程、purge 线程、重做日志线程等,分别负责 IO 处理、脏页刷盘、版本回收与日志推进。

概念解释

  • 主线程:调度脏页刷新、合并插入缓冲、purge 等后台工作。
  • IO 线程:处理异步 IO 请求,分读、写、log、insert buffer 四类。
  • purge 线程:回收不再被引用的 undo 版本与删除标记。
  • 刷脏页线程:按 checkpoint 策略把缓冲池脏页写回磁盘。

原理流程

  1. 后台线程并行处理 IO 与回收工作,不阻塞前台事务主路径。
  2. purge 线程跟随 undo 版本链清理旧版本;脏页线程根据 redo 与 LSN 推进刷盘。
  3. 线程数与行为受相关参数控制,具体默认值随版本变化,待确认时以官方手册为准。

代码 / 命令

-- 查看 InnoDB 后台状态与信号量
SHOW ENGINE INNODB STATUS\G

常见误区

  • 误区:purge 由提交事务的线程同步完成。正解:长事务场景下 purge 异步进行,回收滞后会导致 undo 膨胀。
  • 误区:后台线程越多越好。正解:线程过多会增加上下文切换与 IO 争用,需按 IO 能力调整。

追问

  • 问:purge 不及时会有什么后果?答:旧版本无法清理,undo 与表空间膨胀,详见 05.2.1.5。
  • 问:哪些线程直接影响写入性能?答:日志线程与刷脏页线程,刷盘跟不上会导致事务提交被拖住。

05.2.1.1-007 查询缓存为什么在 MySQL 8.0 被移除?

频率 ★★★★☆ | 难度 基础

一句话结论

查询缓存把完整 SQL 文本与结果做哈希映射,但只要表发生写操作,该表所有缓存条目全部失效;写密集场景命中率低、校验开销大、并发锁竞争明显,8.0 正式移除。

概念解释

  • 缓存键:SQL 文本逐字节匹配,大小写、空格、参数不同即视为不同查询。
  • 失效粒度:表级失效,任意写操作清空相关表的全部缓存结果。
  • 维护成本:每次查询先查缓存哈希,写时批量清理,CPU 与锁开销不可忽略。

原理流程

  1. 旧版流程:连接器后先查缓存,命中直接返回;未命中再解析优化执行。
  2. 表有写事务提交后,该表关联的缓存条目被标记失效并清理。
  3. 8.0 起该模块删除,结果缓存由应用层或 Redis 等外部方案承担。

代码 / 命令

-- 5.7 可查看查询缓存状态;8.0 无 query_cache 参数
SHOW STATUS LIKE 'Qcache%';

常见误区

  • 误区:查询缓存是 Key-Value 缓存,能和 Redis 一样按 key 精细失效。正解:它按 SQL 文本缓存结果集,失效粒度是表。
  • 误区:读多写少就一定该开查询缓存。正解:即使读多,键命中率与失效成本仍需实测,8.0 已不提供该能力。

追问

  • 问:8.0 之后等价能力放哪里?答:应用层缓存、Redis 或代理层结果缓存,按需设计失效策略。
  • 问:查询缓存命中判断包含哪些要素?答:SQL 文本、当前数据库、协议与结果权限上下文都需一致。

05.2.1.1-008 MySQL 8.0 有哪些关键新特性?

频率 ★★★★☆ | 难度 进阶

一句话结论

8.0 代表性变化包括:原生数据字典、隐藏索引与函数索引、CTE 与窗口函数、降序索引、不可见索引、原子 DDL、MGR 成熟化;具体版本号与默认行为以官方手册为准,不确定处标待确认。

概念解释

  • 数据字典:元数据由 InnoDB 内部表集中管理,不再依赖散落的 .frm 文件。
  • 原子 DDL:DDL 要么完整成功要么回滚,避免中途崩溃留下不一致元数据。
  • 窗口函数/CTE:SQL 表达能力增强,减少应用层二次处理。

原理流程

  • 新特性在解析优化层扩展语法,在引擎层补充数据结构与约束支持。
  • 升级到 8.0 前需核对 5.7 依赖的已废弃行为与参数变化。

代码 / 命令

-- 窗口函数示例
SELECT user_id, amount,
       RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rk
FROM orders;

常见误区

  • 误区:8.0 只是性能提升,语法不变。正解:新增窗口函数、CTE、函数索引等语法能力。
  • 误区:所有 5.7 参数在 8.0 同名同义。正解:部分参数废弃或默认值调整,迁移前需逐项核对。

追问

  • 问:原子 DDL 解决什么问题?答:DDL 崩溃后不再出现表与元数据半成状态,可回滚到变更前。
  • 问:函数索引解决什么场景?答:对表达式结果建索引,避免表达式运算导致普通索引失效。

05.2.1.1-009 内部临时表什么时候会出现?

频率 ★★★☆☆ | 难度 进阶

一句话结论

当查询包含 GROUP BY、ORDER BY 无法用索引完成、UNION、子查询物化、派生表或部分聚合时,Server 层或引擎会使用内部临时表;内存临时表放不下会转为磁盘临时表。

概念解释

  • 内存临时表:基于内存引擎在会话内构建,速度快但受 tmp_table_size 限制。
  • 磁盘临时表:超过内存上限后落盘,IO 代价显著上升。
  • 触发场景:排序无索引、分组聚合、去重、集合运算、派生表物化等。

原理流程

  1. 优化器判断当前访问路径无法直接产出有序/聚合结果。
  2. 先生成内存临时表;估算或实际超过阈值后转换为磁盘临时表。
  3. 结果从临时表输出给后续节点。

代码 / 命令

-- Extra 中出现 Using temporary 说明用到内部临时表
EXPLAIN SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;
SHOW STATUS LIKE 'Created_tmp%tables';

常见误区

  • 误区:出现临时表一定是坏计划。正解:无索引支撑分组排序时临时表不可避免,关键是判断是否落盘。
  • 误区:临时表会被其他会话看到。正解:内部临时表会话私有,结束即释放。

追问

  • 问:如何判断临时表落盘了?答:看 Created_tmp_disk_tables 增长,或计划 Extra 提示磁盘临时表。
  • 问:怎么减少临时表?答:让分组排序列走索引,缩小参与聚合的结果集。

05.2.1.1-010 InnoDB 的统计信息是怎么收集的?

频率 ★★★☆☆ | 难度 进阶

一句话结论

InnoDB 通过采样数据页估算表的行数、索引基数与分布,统计信息可持久化存储或实时计算;优化器依赖这些估算选择索引与连接顺序,采样不足会导致估算偏差。

概念解释

  • 采样页:按配置采样若干索引/数据页,统计记录数与键分布。
  • 持久化统计:统计信息落库保存,避免每次打开表重新采样。
  • 基数(cardinality):索引列唯一值估算数量,是判断索引选择性的关键。

原理流程

  1. 建表、更新超过比例阈值或手动 ANALYZE TABLE 时触发采样。
  2. 采样结果写入统计信息,供优化器估算 rows 与选择率。
  3. 数据倾斜或采样偏小时,估算与实际行数出现偏差。

代码 / 命令

ANALYZE TABLE orders;
SHOW INDEX FROM orders;      -- 查看 Cardinality
SELECT * FROM mysql.innodb_table_stats;

常见误区

  • 误区:SHOW INDEX 的 Cardinality 是精确值。正解:它是采样估算值,与真实唯一值可能有差距。
  • 误区:统计信息越新计划一定越好。正解:刚大批量导入后统计滞后,需手动分析;过度频繁分析也有开销。

追问

  • 问:统计信息不准会引发什么?答:优化器低估行数,选错索引或连接顺序,详见 05.2.2.2。
  • 问:直方图解决什么问题?答:补充列值分布信息,缓解等值/范围选择率估算偏差。

05.2.1.1-011 字符集、排序规则与连接字符集是什么关系?

频率 ★★★☆☆ | 难度 基础

一句话结论

字符集(charset)规定字符如何编码,排序规则(collation)规定比较与排序次序;库、表、列、连接各有层级字符集,连接字符集决定客户端与服务端之间编码如何转换。

概念解释

  • 字符集:如 utf8mb4,决定编码字节长度与可表示字符范围。
  • 排序规则:如 utf8mb4_general_ci / utf8mb4_0900_ai_ci,决定大小写重音是否敏感与排序顺序。
  • 连接字符集:客户端发送字节被解释成什么字符集,由 character_set_client 等参数决定。

原理流程

  1. 客户端按连接字符集发送 SQL 字节,服务端转成内部字符集存储。
  2. 比较排序按列/表上的 collation 执行。
  3. 字符集不一致时发生隐式转换,可能导致索引失效。

代码 / 命令

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
ALTER TABLE t MODIFY c VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

常见误区

  • 误区:utf8 就是完整 UTF-8。正解:MySQL 旧 utf8 是 3 字节子集,emoji 需用 utf8mb4。
  • 误区:排序规则只影响显示。正解:_ci/_bin 影响等值比较与索引匹配,大小写敏感差异会导致查询结果不同。

追问

  • 问:连接字符集不对会出现什么?答:中文/emoji 乱码,或比较时隐式转换使索引失效。
  • 问:库表列字符集不一致以谁为准?答:建列时取列定义,未指定继承表,表未指定继承库。

05.2.1.1-012 实例、数据库、schema 之间是什么关系?

频率 ★★★☆☆ | 难度 基础

一句话结论

实例是后台进程与内存的运行实体;一个实例可管理多个数据库(schema 在 MySQL 中与 database 基本同义),库内再组织表、视图、索引等对象。

概念解释

  • 实例(instance):mysqld 进程及其内存结构、线程与后台服务。
  • 数据库(database/schema):对象的命名空间,CREATE DATABASE 与 CREATE SCHEMA 在 MySQL 中等价。
  • 对象隔离:不同库的表通过 库名.表名 跨库访问,权限按库/表粒度授予。

原理流程

  1. 启动 mysqld 形成实例,加载数据目录下各库。
  2. 连接后默认进入一个库,跨库对象用限定名访问。
  3. 备份复制通常按实例或库粒度设计。

代码 / 命令

SHOW DATABASES;
SELECT table_schema, COUNT(*) FROM information_schema.tables GROUP BY table_schema;

常见误区

  • 误区:实例就是数据库。正解:实例是运行实体,数据库是实例内的对象命名空间。
  • 误区:MySQL 的 schema 与 PostgreSQL schema 概念相同。正解:PG 的 schema 是库内二级命名空间,MySQL schema 基本等价 database。

追问

  • 问:一个实例能否同时服务多个业务库?答:可以,但需评估资源与备份隔离,业务隔离更推荐独立实例。
  • 问:跨库 JOIN 有什么限制?答:同实例可跨库 JOIN,跨实例需借助复制、异构同步或应用层拼装。

05.2.1.1-013 连接管理:线程复用、长连接与连接数上限怎么治理?

频率 ★★★★☆ | 难度 进阶

一句话结论

MySQL 每连接对应一个服务线程;长连接复用线程减少握手开销,但空闲连接占内存;连接数受 max_connections 限制,超过后新连接被拒绝,生产上通常配合连接池治理。

概念解释

  • 线程复用:连接断开后线程可被新连接复用,避免反复创建销毁。
  • 长连接:连接长期保持,避免每次请求重新握手与认证。
  • 连接池:应用侧或中间件侧维持固定连接数,控制对数据库的连接压力。

原理流程

  1. 新连接到达,连接器检查连接数是否超限,分配或复用线程。
  2. 空闲长连接保留内存与会话状态;长时间闲置可由超时参数断开。
  3. 应用侧连接池控制并发连接上限,配合数据库 max_connections。

代码 / 命令

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'wait_timeout';

常见误区

  • 误区:连接越多吞吐越高。正解:连接数超过 CPU/IO 承载后上下文切换与争用反而拖慢整体。
  • 误区:长连接永不释放最省资源。正解:长连接累积会话内存与临时对象,需有空闲回收策略。

追问

  • 问:连接池大小怎么定?答:与核数、IO 模型和事务时长相关,过大造成排队争抢,需压测确定。
  • 问:Too many connections 怎么应急?答:先释放空闲连接、排查泄漏,再评估上调上限并上连接池。

05.2.1.1-014 一条 DDL 语句的执行链路是怎样的?

频率 ★★★★☆ | 难度 高级

一句话结论

DDL 先在 Server 层解析并申请 MDL 元数据锁,再由引擎执行表结构变更;8.0 原子 DDL 把元数据变更写入事务日志,中途崩溃可回滚;大表 DDL 需评估锁与重建开销。

概念解释

  • MDL 锁:访问表元数据时加元数据锁,防止 DDL 与 DML 并发造成结构不一致。
  • 表重建 vs 原地变更:部分 DDL 需复制全表重建,部分只改元数据或原地调整。
  • 原子 DDL:8.0 将 DDL 纳入可恢复事务,失败回滚不残留半成品结构。

原理流程

  1. 解析优化后申请 MDL 锁,校验权限与表状态。
  2. 引擎按变更类型选择原地修改或重建表,过程中处理数据拷贝。
  3. 提交时更新数据字典与元数据;崩溃由 redo/数据字典事务回滚。

代码 / 命令

-- 查看当前被 MDL 阻塞的会话
SELECT * FROM performance_schema.metadata_locks;
-- 大表变更优先评估 Online DDL / 第三方工具(见 05.2.2.5)

常见误区

  • 误区:加列只是改元数据,秒级完成。正解:带默认值或类型变更在不同版本可能触发重建,需以手册与实测为准。
  • 误区:DDL 不会阻塞业务查询。正解:MDL 与重建阶段可能阻塞 DML,长事务持有 MDL 会放大影响。

追问

  • 问:什么是 Online DDL?答:变更过程尽量不阻塞读写,仍可能在准备/提交阶段短暂加锁,详见 05.2.2.5。
  • 问:如何查看 DDL 是否在重建表?答:观察计划/状态输出与临时表、拷贝行数变化。

05.2.1.1-015 预编译语句与执行计划复用是怎么回事?

频率 ★★★☆☆ | 难度 进阶

一句话结论

预编译语句先把 SQL 模板解析成执行计划,后续用不同参数多次执行,复用解析与优化结果;绑定参数还能减少注入风险,但计划复用在数据分布不均时可能选到不适用的通用计划。

概念解释

  • 预处理(prepare):文本解析与计划生成一次完成。
  • 参数绑定:占位符在执行时传入具体值,计划不随参数文本变化。
  • 计划复用权衡:通用计划对大部分参数可行,极端值场景可能不如逐次优化。

原理流程

  1. 客户端发送带占位符的模板,服务端生成计划句柄。
  2. 多次 execute 绑定参数执行,跳过解析优化。
  3. 计划随连接释放或被失效条件清除。

代码 / 命令

PREPARE stmt FROM 'SELECT * FROM orders WHERE user_id = ?';
SET @uid = 1001;
EXECUTE stmt USING @uid;
DEALLOCATE PREPARE stmt;

常见误区

  • 误区:预编译一定提升性能。正解:只执行一次的语句,预处理反而多一次往返;复用次数多才划算。
  • 误区:绑定参数能完全避免注入。正解:参数作为值传递不可拼接 SQL 片段,标识符(表名列名)仍不能靠参数绑定。

追问

  • 问:预编译计划何时失效?答:相关表结构变更、统计更新或连接关闭等条件下重新优化。
  • 问:与 PG 预编译计划选择有何不同?答:PG 对 prepared statement 会在自定义计划与通用计划间权衡,详见 05.3.2.2。

05.2.1.1-016 MySQL 8.0 与 5.7 有哪些默认行为差异需要注意?

频率 ★★★☆☆ | 难度 进阶

一句话结论

主要差异集中在:默认字符集、排序规则、统计信息、sql_mode、优化器行为与废弃参数;具体默认值随小版本调整,迁移前必须以目标版本官方手册逐项核对,不确定处标待确认。

概念解释

  • 字符集默认值:8.0 默认 utf8mb4 与新排序规则,5.7 默认 latin1/utf8mb3 的历史库需检查。
  • sql_mode:严格模式与不合法值处理在版本间有调整。
  • 优化器/参数:部分旧参数废弃、默认代价系数变化,可能导致计划不同。

原理流程

  1. 升级前导出参数、sql_mode 与字符集配置对比。
  2. 在测试实例按目标版本跑核心 SQL 计划对比。
  3. 记录依赖废弃行为的语句并改造后再切流。

代码 / 命令

SELECT @@sql_mode;
SHOW VARIABLES LIKE 'character_set_server';
-- 待确认:目标小版本的参数默认值以 SHOW VARIABLES 与官方手册为准

常见误区

  • 误区:同一条 SQL 跨版本计划必然一致。正解:统计与代价模型变化后计划可能改变,需回归验证。
  • 误区:升级只替换二进制即可。正解:参数、字符集、废弃函数与兼容模式都要逐项梳理。

追问

  • 问:字符集默认变化会影响存量数据吗?答:新建库表按新默认,存量库表仍保留原定义,迁移时需显式核对。
  • 问:如何做版本兼容性回归?答:核心慢 SQL 在新旧版本分别出计划与结果集对比。

05.2.1.1-017 MySQL 与 PostgreSQL 在进程模型上有什么不同?

频率 ★★★★☆ | 难度 进阶

一句话结论

MySQL 是单进程多线程模型,连接复用线程;PostgreSQL 是单进程(postmaster)为每个连接 fork 一个独立后端进程,进程间通过共享内存协作,因此 PG 对连接数更敏感,通常必须配连接池。

概念解释

  • MySQL 线程模型:线程切换成本低,但连接内存与会话状态在线程间复用。
  • PG 进程模型:每连接一进程,隔离性强,但进程创建与内存开销高,高并发连接数易拖垮实例。
  • 共享内存:两者都用共享内存缓冲数据,PG 的后端进程直接访问共享缓冲区。

原理流程

  1. MySQL:连接器分配服务线程处理该连接语句。
  2. PG:postmaster 接收连接后 fork 后端进程,进程处理该连接直至断开。
  3. PG 连接数上限受进程资源约束,PgBouncer 在应用与实例之间复用连接。

代码 / 命令

-- MySQL
SHOW STATUS LIKE 'Threads_connected';
-- PostgreSQL
SELECT count(*) FROM pg_stat_activity;

常见误区

  • 误区:MySQL 每连接也是独立进程。正解:MySQL 是线程模型,pg 的每连接进程模型才是 fork。
  • 误区:PG 可以像 MySQL 一样放开大连接数。正解:进程模型下连接过多内存与调度代价高,必须上连接池,详见 05.3.1.1。

追问

  • 问:PG 为什么常配 PgBouncer?答:把大量应用连接复用到少量后端进程连接上,降低进程与内存开销。
  • 问:线程模型相对进程模型的短板是什么?答:单线程故障/阻塞可能影响共享资源,隔离性弱于独立进程。

05.2.1.1-018 实例、表空间、表、页这些概念的边界怎么划分?

频率 ★★★☆☆ | 难度 基础

一句话结论

实例运行在数据目录之上,表空间是引擎层的存储组织单位,表由若干索引/数据段组成,段再划分为区与页;SQL 语句面对的是表与索引,物理页与表空间属于引擎内部组织。

概念解释

  • 数据目录:实例启动指定的根目录,存放各库文件与日志。
  • 表空间:InnoDB 中表与索引数据的物理容器,分系统表空间、独立表空间等。
  • 段/区/页:段是索引或表的逻辑单元,区是连续页组,页是最小 IO 单位。

原理流程

  1. 实例启动打开数据目录与各表空间。
  2. 表数据按聚簇索引组织成段,段分配区与页。
  3. 读写以页为单位在缓冲池与磁盘间搬运。

代码 / 命令

SHOW VARIABLES LIKE 'datadir';
SHOW VARIABLES LIKE 'innodb_data_file_path';

常见误区

  • 误区:表空间就是一个文件。正解:表空间可由多个文件组成,独立表空间默认每表一个 .ibd。
  • 误区:页是引擎对外暴露的概念。正解:页是内部存储单位,SQL 层只感知表与行。

追问

  • 问:独立表空间与共享表空间区别?答:独立表空间每表一文件便于回收迁移,共享表空间集中存放,详见 05.2.1.4。
  • 问:数据目录损坏会怎样?答:实例无法正常启动或部分库表不可访问,备份与 binlog 归档是恢复基础。