1.全面理解Mysql架构

1. 一条SQL查询语句是如何执行的？

比如查询语句：select * from user where id = 10;

✅Select执行流程

下面是 MySQL 的基本架构示意图，从中我们可以清楚地看到 SQL 语句在 MySQL的各个功能模块中的执行过程。

**客户端：**连接工具（Navacat、SQLyog、JDBC）都归纳为MySQL客户端(Client)，主要用于发送执行sql语句的请求。

服务端：分为Server 层和存储引擎层两部分。

• Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
• **存储引擎层负责数据的存储和检索。**其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL5.5.5 版本开始成为了默认存储引擎。也就是说，你执行 create table 建表的时候，如果不指定引擎类型，默认使用的就是InnoDB。不过，你也可以通过指定存储引擎的类型来选择别的引擎，比如在 create table语句中使用 engine=memory, 来指定使用内存引擎创建表。

server层和存储层的职责如下：

Server 层

1. 负责处理 SQL 语句、解析、优化、缓存等。
2. 负责权限管理、用户认证等。
3. 提供了各种 SQL 函数和存储过程。
4. 提供了复制、备份、恢复等高级功能。
5. Server 层有自己的日志系统，称为 binlog（归档日志）。binlog 记录了所有修改数据库数据的 SQL 语句（如 INSERT、UPDATE、DELETE 等）的信息，但不包括 SELECT 和 SHOW 这类查询语句。binlog 主要用于复制和恢复操作。

存储引擎层

1. 负责数据的存储和检索。
2. MySQL 支持多种存储引擎，如 InnoDB、MyISAM、Memory 等，每种引擎都有其特点和适用场景。
3. InnoDB 是 MySQL 的默认存储引擎，它支持事务、行级锁定和外键约束。InnoDB 有自己的日志系统，称为 redo log（重做日志） 和 undo log（撤销日志）。redo log 用于保证事务的持久性，在数据库崩溃后可以用来恢复数据；undo log 用于支持事务的原子性和多版本并发控制（MVCC）。

✅连接器

作用：建立连接 → 获取权限 → 维持和管理连接

mysql -h$ip-P$port-u$user-p# TCP握手后认证身份

认证流程：

1. 用户名或密码错误 →Access denied for user，程序结束
2. 认证通过 → 从权限表读取权限，后续这个连接的权限判断都基于此次读取

# 查看数据库的连接状态showprocesslist;# 查看当前的wait_timeout参数值SHOWVARIABLESLIKE'wait_timeout';

连接管理：

**注意：建立连接的过程通常是比较复杂的，建议在使用中要尽量减少建立连接的动作，尽量使用长连接。**为了提升数据库并发性，可以建立一个数据库连接池。

OOM 问题与解决：

全部使用长连接后，有时候 MySQL 占用内存涨得特别快，因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的，这些资源会在连接断开的时候才释放，所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM）。从现象看就是 MySQL 异常重启了。

1. 定期断开长连接，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2. MySQL 5.7+：在每次执行一个比较大的操作后，通过执行mysql_reset_connection重置连接资源（无需重连、不重新鉴权），但是会将连接恢复到刚刚创建完时的状态。

在Java 中与 MySQL 数据库交互通常使用 JDBC (Java Database Connectivity) API，它提供了自己的连接管理和错误处理机制。请注意，频繁地创建和关闭连接可能会对性能产生负面影响，特别是在高负载的情况下。因此，**在生产环境中，通常会使用连接池来管理数据库连接，**这样可以复用现有的连接而不是频繁地创建和销毁它们。

✅查询缓存

作用：以 key-value 形式缓存已执行过的 SQL 语句及结果，命中时直接返回，跳过后续所有步骤。

在MySQL5.7版本，连接完成后就会直接查询缓存。MySQL 8.0 已将查询缓存功能整体移除。

为什么不用？弊大于利。更新频繁时缓存命中率极低，只有静态表（如系统配置表）才适合。

相关参数：

按需使用（5.7适用）：

SETGLOBALquery_cache_type=DEMAND;-- 或写入 my.cnf 后重启mysql>selectSQL_CACHE*fromuserwhereid=1;-- 仅此条走缓存

参考：MySQL 为什么在 8.0 版本中移除了查询缓存功能

查询缓存常见的问题：

但是大多数情况下不建议使用查询缓存，为什么呢？因为查询缓存往往弊大于利。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

解决方案：

MySQL 提供了按需使用的方式。可以将参数 query_cache_type 设置成 DEMAND，对于默认的 SQL 语句都将不使用查询缓存。

你可以通过在 MySQL 命令行界面（CLI）中执行以下命令来设置 query_cache_type 为 DEMAND：

SETGLOBALquery_cache_type=DEMAND;

或者，你可以在 MySQL 的配置文件（通常是 my.cnf 或 my.ini）中设置：

[mysqld] query_cache_type = DEMAND

修改配置文件后，你需要重启 MySQL 服务来使更改生效。

而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，如下：

# 只有这条带有 SQL_CACHE 提示的查询会被缓存。mysql>selectSQL_CACHE*fromuserwhereid=1;

✅分析器

作用：词法分析 → 语法分析，判断 SQL 是否合法。

若查询缓存未命中，则会执行分析器，来分析查询语句是否合法。

两步走：

**词法分析：**析查询语句是否合法。

1. 主要负责从 SQL 语句中提取关键字，比如：查询的表，字段名，查询条件等等。

2. 词法分析仅负责分词，不涉及 information_schema 或任何元数据查询。

**语法分析：**根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

1. 判断输入的SQL 语句是否满足 MySQL 语法
2. 如果 SQL 语句不对，就会返回 You have an error in your SQL syntax 的错误提醒，一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接"use near"的内容。

✅优化器

作用：在多个可行方案中选出效率最高的执行方案。

核心任务：多索引时选索引、多表 JOIN 时定连接顺序。

示例：select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20

优化器会选择先扫小表，减少大表扫描量。

✅执行器

作用：根据上一步优化器的执行方案调用引擎接口，逐行遍历并返回结果集。

执行步骤：

1. 权限检查：判断你对这个表是否有执行权限，无权限则报错，这是一种安全机制，确保只有被授权的用户才能访问和操作数据。
2. 调用引擎接口： 逐行取数据，满足条件则放入结果集
3. 返回结果集：遍历完成后返回给客户端

注意：

1. 如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。
2. 在语法分析过程中，解析器会进行一些初步的权限检查 precheck，例如验证用户是否有权访问指定的数据库和表。
3. 有些时候，SQL语句要操作的表不只是SQL字面上那些。SQL执行过程中可能会有触发器这种在运行时才能确定的过程，precheck是不能对这种运行时涉及到的表进行权限校验的，所以需要在执行器阶段进行权限检查。

打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口：

1. 调用引擎接口取这个表的第一行，判断是否满足条件，如果不是则跳过，如果是则将这行存在结果集中
2. 调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

至此，这个语句就执行完成了。

✅思考题：

如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报"不存在这个列"的错误：“Unknown column ‘k’ in ‘where clause’”。请问是在哪个阶段报出的错误？

答案：分析器

2. 一条SQL更新语句是如何执行的？

✅更新与查询的差异

更新语句同样走完查询流程的五步（连接器→查询缓存→分析器→优化器→执行器），额外涉及两个核心日志模块：redo log 和 binlog。

例如下面更新语句：

updateTsetc=c+1whereID=2;-- 假设原来 c=0

✅redo log（重做日志）- InnoDB 特有

类比：酒店掌柜在人流多的时候，先用粉板（redo log）记临时账→ 打烊后整理记到账本（磁盘）

核心机制 — WAL（Write-Ahead Logging）：先写日志，再写磁盘

关键特性：

循环写关键概念：

redo log buffer：内存缓冲区，先写 buffer，commit 时才写入磁盘ib_logfile文件。

案例：

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。如果今天赊账的不多，掌柜可以等打烊后再整理。

但如果某天赊账的特别多，粉板写满了，又怎么办呢？这个时候掌柜只好放下手中的活儿，把粉板中的一部分赊账记录更新到账本中，然后把这些记录从粉板上擦掉，为记新账腾出空间。

与此类似，InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块"粉板"总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是"粉板"上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示"粉板"满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

✅binlog（归档日志）- Server 层

1. binlog（归档日志）是Server 层特有的日志。

   为什么会有两份日志？

   MySQL 最初只有 MyISAM 引擎，自带的 binlog 只能归档，没有 crash-safe 能力。后来 InnoDB 以插件形式引入，为了弥补这一缺陷，自建了 redo log。所以两份日志是历史产物：binlog 负责归档，redo log 负责崩溃恢复。

   三种本质区别：

   维度	redo log	binlog
   归属	InnoDB 引擎特有	Server 层，所有引擎通用
   内容	物理日志：“在数据页 xxx 偏移量处写入 yyy”	逻辑日志：“给 ID=2 的 c 字段加 1”
   写入	循环写，固定空间，写满覆盖	追加写，文件写满后切新文件，不覆盖旧日志

   核心参数：

   -- 两个参数都设为 1，才能保证 crash 后数据 + binlog 都不丢失innodb_flush_log_at_trx_commit=1-- 每次事务 redo log 刷盘sync_binlog=1-- 每次事务 binlog 刷盘

   数据恢复实战：

   假设某天 12:00 误删了一张表，如何找回？

   昨晚全量备份 12:00 误删 |-----------------------------------|------→ 时间线 |<---- binlog 逐条重放 ----->| 步骤： ① 找到最近全量备份（如昨晚），恢复到临时库 ② 从备份时间点开始，取出 binlog 逐条重放到 11:59 ③ 临时库 = 误删前的线上库 → 从临时库取回表数据

数据恢复实战：

假设某天 12:00 误删了一张表，如何找回？

昨晚全量备份 12:00 误删 |-----------------------------------|------→ 时间线 |<---- binlog 逐条重放 ----->| 步骤： ① 找到最近全量备份（如昨晚），恢复到临时库 ② 从备份时间点开始，取出 binlog 逐条重放到 11:59 ③ 临时库 = 误删前的线上库 → 从临时库取回表数据

✅Update 执行流程（两阶段提交）

updateTsetc=c+1whereID=2;-- 假设原来 c=0

橙色=执行器执行，绿色=InnoDB 引擎执行

✅两阶段提交（内部XA）

一句话：redo log 和 binlog 是两个独立逻辑，两阶段提交保证二者一致，否则崩溃恢复后数据对不上。

不用两阶段提交的后果（以update T set c=c+1 where ID=2，c 原值=0 为例，假设写完第一个日志后 crash）：

场景1：先写 redo log，后写 binlog

假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。 但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。 然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

redo log 写完 ，c 由0 -> 1 ✓ → crash → binlog 未写成功，c还是0，此时MySQL 进程异常重启 ───────────────────────────────────────── 崩溃恢复后：原库 c=1（redo log 恢复了数据） binlog 恢复后：临时库 c=0（binlog 缺了这条语句） → 主库和备份库数据不一致！

场景2：先写 binlog，后写 redo log

如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了"把 c 从 0 改成 1"这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

binlog 写完 ，c 由0 -> 1 ✓ → crash → redo log 未写成功，c还是0✗，此时MySQL 进程异常重启 ───────────────────────────────────────── 崩溃恢复后：原库 c=0（redo log 没记录，事务无效） binlog 恢复后：临时库 c=1（binlog 多了一个事务） → 同样是主备不一致！

可以看到，如果不使用"两阶段提交"，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

两阶段提交流程图：

redo log binlog │ │ ③ 写 prepare ────────────────────────│ │ │ │ ④ 写 binlog ──────┤ │ │ ⑤ 改 commit ────────────────────────→ │ │ 事务完成 归档完成

思考题：

binlog 写完，redo log 还没 commit前发生 crash，那崩溃恢复的时候 MySQL 会怎么处理？

我们先来看一下崩溃恢复时的判断规则。

1. 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
2. 如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：
   • a. 如果是，则提交事务；
   • b. 否则，回滚事务。

所以，binlog 写完，redo log 还没 commit前发生 crash， 对应的就是 2(a) 的情况，崩溃恢复过程中事务会被提交。

追问1：

redo log 和 binlog 是怎么关联起来的?

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

1. 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
2. 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

redo log 和 binlog 通过XID（事务ID）关联：

崩溃恢复时： ① 顺序扫描 redo log ② 遇到只有 prepare 的事务 → 取出 XID ③ 拿 XID 去 binlog 里查找对应事务 ④ 找到 → 提交 / 找不到 → 回滚

追问2：

redo log buffer 是什么？是先修改内存，还是先写 redo log文件？

在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：

begin;insertintot1...insertintot2...commit;

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里。

所以，redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。

但是，真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit语句的时候做的。