数据库之MySQL第一篇概念

表设计的3大范式？

• 1NF：每个属性都是不可分割的原子项
• 2NF：非主属性必须完全依赖于主属性
• 3NF：属性不依赖于其他非主属性（非主属性不存在传递依赖）

数据在MySQL中是如何存储的？

以页为单位存储数据

• 虽然数据保存在磁盘中，但其处理是在内存中进行的。为了减少磁盘随机读取次数，InnoDB采用页而不是行的粒度来保存数据，即数据被分成若干页，以页为单位保存在磁盘中。InnoDB的页大小，一般是16KB。
• 各个数据页组成一个双向链表
• 每个数据页中的记录按照主键顺序组成单向链表；每一个数据页中有一个页目录，方便按照主键查询记录。

数据页结构

页目录通过槽把记录分成不同的小组，每个小组有若干条记录。如图所示，记录中最前面的小方块中的数字，代表的是当前分组的记录条数，最小和最大的槽指向2个特殊的伪记录。有了槽之后，我们按照主键搜索页中记录时，就可以采用二分法快速搜索，无需从最小记录开始遍历整个页中的记录链表。

如果要搜索主键（PK）=15的记录：先二分得出槽中间位是(0+6)/2=3，看到其指向的记录是12＜15，所以需要从#3槽后继续搜索记录；再使用二分搜索出#3槽和#6槽的中间位是(3+6)/2=4.5取整4，#4槽对应的记录是16＞15，所以记录一定在#4槽中；再从#3槽指向的12号记录开始向下搜索3次，定位到15号记录。

SQL语句执行流程

一条查询语句的执行流程

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心
服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功
能都在这一层实现，比如存储过程、触发器、视图等。
而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、
Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始
成为了默认存储引擎。
也就是说，你执行 create table 建表的时候，如果不指定引擎类型，默认使用的就是
InnoDB。不过，你也可以通过指定存储引擎的类型来选择别的引擎，比如在 create table 语
句中使用 engine=memory, 来指定使用内存引擎创建表。不同存储引擎的表数据存取方式不
同，支持的功能也不同。
从图中不难看出，不同的存储引擎共用一个 Server 层，也就是从连接器到执行器的部分。你
可以先对每个组件的名字有个印象，接下来我会结合开头提到的那条 SQL 语句，带你走一遍
整个执行流程，依次看下每个组件的作用。

连接器

第一步，你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立
连接、获取权限、维持和管理连接。连接命令一般是这么写的：
输完命令之后，你就需要在交互对话里面输入密码。虽然密码也可以直接跟在 -p 后面写在命
令行中，但这样可能会导致你的密码泄露。如果你连的是生产服务器，强烈建议你不要这么
做。
连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成经典的 TCP 握手后，连
接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

mysql -h ip -P port -u user -p

如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程
序结束执行。
如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面
的权限判断逻辑，都将依赖于此时读到的权限。
这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也
不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。
连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist
命令中看到它。文本中这个图是 show processlist 的结果，其中的 Command 列显示为
“Sleep”的这一行，就表示现在系统里面有一个空闲连接。
客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控
制的，默认值是 8 小时。
如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost
connection to MySQL server during query。这时候如果你要继续，就需要重连，然后再执
行请求了。
数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连
接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就
是尽量使用长连接。
但是全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为
MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时
候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），
从现象看就是 MySQL 异常重启了。
怎么解决这个问题呢？你可以考虑以下两种方案。
1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开
连接，之后要查询再重连。
2. 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行
mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验
证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存【鸡肋】

连接建立完成后，你就可以执行 select 语句了。执行逻辑就会来到第二步：查询缓存。
MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行
过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，
value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被
直接返回给客户端。
如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓
存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返
回结果，这个效率会很高。
但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因
此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数
据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新
一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。
好在 MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置成
DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语
句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

mysql> select SQL_CACHE * from T where ID=10；

需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底 没有这个功能了。

分析器

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。 分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。 MySQL 从你输的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T” 识别成“表名 T”，把字符串“ID”识别成“列 ID”。 做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规 则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。 如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，比如 下面这个语句 select 少打了开头的字母“s”。一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容。

优化器

经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。 优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联 （join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的 join：

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里 面 d 的值是否等于 20。 也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。 优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。如果你还有一 些疑问，比如优化器是怎么选择索引的，有没有可能选择错等等，没关系，我会在后面的文章中单独展开说明优化器的内容。

执行器

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶 段，开始执行语句。 开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没 有权限的错误，如下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时 候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个 引擎提供的接口。 比如我们这个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：

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

至此，这个语句就执行完成了。 对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口， 之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。 你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。 在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

一条更新语句的执行流程

mysql> update T set c=c+1 where ID=2;

分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后，执行器负责具体执行，找到这一行，然后更新。 与查询流程不一样的是，更新流程还涉及两个重要的日志模块，它们正是我们今天要讨论的主 角：redo log（重做日志）和 binlog（归档日志）。

redo log

不知道你还记不记得《孔乙己》这篇文章，酒店掌柜有一个粉板，专门用来记录客人的赊账记 录。如果赊账的人不多，那么他可以把顾客名和账目写在板上。但如果赊账的人多了，粉板总会有记不下的时候，这个时候掌柜一定还有一个专门记录赊账的账本。 如果有人要赊账或者还账的话，掌柜一般有两种做法：

一种做法是直接把账本翻出来，把这次赊的账加上去或者扣除掉；

另一种做法是先在粉板上记下这次的账，等打烊以后再把账本翻出来核算。在生意红火柜台很忙时，掌柜一定会选择后者，因为前者操作实在是太麻烦了。首先，你得找 到这个人的赊账总额那条记录。你想想，密密麻麻几十页，掌柜要找到那个名字，可能还得带 上老花镜慢慢找，找到之后再拿出算盘计算，最后再将结果写回到账本上。

同样，在 MySQL 里也有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找 到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题， MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。

而粉板和账本配合的整个过程，其实就是 MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。 具体来说，当有一条记录需要更新的时候，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。 要理解 crash-safe 这个概念，可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

bin log

前面我们讲过，MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们聊到的粉板 redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。 我想你肯定会问，为什么会有两份日志呢？ 因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。 这两种日志有以下三点不同。

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志， 记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

有了对这两个日志的概念性理解，我们再来看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。 

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内 存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的 一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状 态，更新完成。

这里我给出这个 update 语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色 框表示是在执行器中执行的。

你可能注意到了，最后三步看上去有点“绕”，将 redo log 的写入拆成了两个步骤： prepare 和 commit，这就是"两阶段提交"。

两阶段提交

为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。要说明这个问题，我 们得从文章开头的那个问题说起：怎样让数据库恢复到半个月内任意一秒的状态？ 前面我们说过了，binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的 DBA 承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一 周一备。 当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备 份恢复到临时库；

然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。

这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。 好了，说完了数据恢复过程，我们回来说说，为什么日志需要“两阶段提交”。这里不妨用反 证法来进行解释。 由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。

仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

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

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的 库的状态不一致。 你可能会说，这个概率是不是很低，平时也没有什么动不动就需要恢复临时库的场景呀？ 其实不是的，不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候，也就是需要 再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。 简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状 态保持逻辑上的一致。

思考

当redo log的prepare阶段成功并且bin log写入也成功，但是redo log的commit阶段失败，此时如何保证一致性呢？

当MySQL重启恢复时，发现redo log虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。

注意事项

redo log

1. redo log记录的内容，不是记录数据页“更新之后的状态”，而是记录这个页 “做了什么改动”。
2. redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样 可以保证 MySQL 异常重启之后数据不丢失。

bin log

1. bin log记录的内容，有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。
2. sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数 我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。