尽量不废话直接上干货...

一、MySQL架构设计

客户端依据通信协议请求服务端，而MySQL这个服务器执行SQL语句命令并给出反馈，整体架构如下：

可以粗略的把MySQL服务器分为两层，上面的为Server层，主要包括连接器、查询缓存、分析器【分析器+预处理器】、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

下面的一层为存储引擎，存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL5.5版本开始成为了默认存储引擎。这两层共同构建了MySQL。

1.1 连接器

身份认证和权限相关(登录 MySQL 的时候)，我们在数据库层执行SQL语句时，应用程序会连接到相应的数据库服务器，把SQL语句发送给服务器处理，实际上也就是通信。数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

1.2 查询缓存

执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除）。在接收到查询请求后，并不会直接去数据库查询，而是在数据库的查询缓存中找是否有相对应的查询数据（某条给定的查询语句在第一次执行时，服务器会缓存这条查询语句和他返回的结果）。

如果存在，那么在返回查询结果之前，MySQL会检查一次用户权限。如果权限没有问题，key 是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个value就会被则直接从缓存中拿到结果返回给客户端。查询不会被解析，不用生成执行计划，不会被执行。判断是否命中缓存是将此查询语句和缓存中的查询语句进行比对，如果完全相同，那就认为它们是相同的，就认为命中缓存了。如果不存在，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。1.3 分析器

没有命中缓存的话，SQL 语句就会经过分析器，Mysql通过将SQL语句进行解析，并生成一棵对应的解析树。MySQL解析器将使用MySQL语法分析(语法规则验证)和解析查询，如将验证是否使用错误的关键字，或者关键字的顺序是否正确。

1.4 预处理器

预处理器根据一些MySQL规则进一步检查解析树是否合法，如数据表和数据列是否存在，解析列名和别名，是否有歧义。接下来预处理器会验证用户权限(precheck)。查看用户是否有相应的操作权限

1.5 优化器

按照 MySQL 认为最优的方案去执行。例如表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序，将SQL语句转化成执行计划，一条查询可以有很多种执行方式，最后都返回相同的结果，最后找到其中最好的执行计划（Mysql使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划的成本，选择其中成本最小的一个）

1.6 执行器

Mysql根据执行计划给出的指令逐步执行。开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有就会返回没有权限的错误。在此过程中，有大量的操作需要通过调用存储引擎实现的接口完成，这些接口即为“handler API”接口。查询中的每一个表由一个handler的实例表示。（实际上，在优化阶段Mysql就为每一个表创建了一个handelr实例，优化器可以根据这些实例的接口获取表的相关信息，如表的所有列名、索引统计信息等）

1.7注意事项

这两层共同构建了MySQL，以上各个环节中包含如下三个注意事项：

1.7.1连接的权限时效

一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

1.7.2长连接使用

因为创建连接比较复杂，所以建议使用长连接，但是长连接容易OOM。

定期断开长连接，使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。如果用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。1.7.3不建议查询缓存

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，8.0已经将该功能彻底移除了

总结而言，连接设置修改只影响后续连接，长连接虽好，但还是定期断开，否则容易OOM，不建议使用查询缓存。

二、硬盘基础知识2.1 机械硬盘

我们知道机械硬盘是将数据写入扇区，一个扇区 512b，那么写入数据都会有一个寻址的过程（就是寻找扇区）机械硬盘写入数据时需要经过：1、定位到磁道；2、等待旋转到对应扇区；3、开始读写。步骤 1和2 机械定位，速度慢。

2.2 固态硬盘

固态硬盘将数据写入闪存芯片，使用电子定位，所以比机械硬盘快很多。对于拷贝数据，是连续的扇区写入数据，一次性申请连续的空间，固态硬盘比机械硬盘快 3 到 5 倍。对于随机读写，固态硬盘比机械硬盘快 100-300 倍，而我们大多数的场景都是随机读写。

2.3 文件系统

我们一般不会直接与硬盘打交道，都是与文件系统打交道。文件系统将硬盘划分为一个一个的 block，1block = 8扇区 = 4kb，一个block 是由多个连续的扇区组成，这样就大大减少了扇区寻址的时间，但是这样会存在 block 写不满的情况，这就是磁盘碎片。文件系统磁盘读写模式。

顺序读写，分配的是连续的 block，所以读写性能高，但是需要满足两个条件：文件大小固定；预分配磁盘空间随机读写，分配的 block 不是连续的，磁头需要频繁切换，读写性能比较低。2.4 文件系统磁盘读写规则

文件系统实际不是直接将数据读写到磁盘中，它会现将数据写入 buffer ，buffer 按照策略将数据刷新到磁盘，读取数据时先读 cache，cache读不到再去读磁盘。如果系统宕机、断电 使用 Linux 提供的 fsync 可以直接将数据刷新到磁盘。

Innodb 以页为单位进行磁盘交互，一页大小 16 KB，一页中至少保存两条数据，也就是说当你更新某个页中的一条数据中的一个字段，它都需要整页更新，而且如果事务修改了很多页，页之间的block不是连续的，磁盘还要不停的寻址。

三 InnoDB储存引擎

我们知道InnoDB的记录结构、数据页结构、数据目录、表空间很复杂，所以本文抛砖引玉，先入门基础，之后的文章按重点和大家感兴趣点知识点来续写。

3.1 InnoDB储存引擎特点

InnoDB是一个健壮的事务型存储引擎，InnoDB就是作为默认的存储引擎。

InnoDB还引入了行级锁定和外键约束。Innodb的索引和数据是紧密捆绑的，没有使用压缩从而会造成 Innodb 比 MyISAM 数据文件体积庞大很多。InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度。InnoDB 表的select count(*) 比MyISAM慢很多；

当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量，需要注意的是，当count(*) 语句包含 where 条件时，两种表的操作是一样的，当count(*)语句包含where条件时MyISAM也需要扫描整个表。

DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除在以下场合下，使用InnoDB是最理想的选择：更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。默认的事务隔离级别为可重复读（REPEATABLE-READ），通过MVCC（并发版本控制）来实现，使用的锁粒度默认为行级锁，可以支持更高的并发；当然，也支持表锁，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了支持行级锁，InnoDB的行级锁是有条件的。在where条件没有使用主键时，照样会锁全表（没有使用其他索引）灾备。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。外键约束。MySQL支持外键的存储引擎只有InnoDB。支持自动增加列AUTO_INCREMENT属性。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。大数据量下用innodb，因为支持事务，行级锁。对于InnoDB来说，最大的优势在于支持事务，当然这是以牺牲效率为代价的

3.2 InnoDB储存引擎整体概括：3.3 InnoDB内存结构

Buffer Pool 主要分为以下几个部分：Buffer Pool，change buffer，Adaptive hash Index，Redo log buffer。

3.3.1 BufferPool

缓存的是页面信息，包括数据也和索引页。默认大小是128M，可以调整。如果写满了，则使用LRU算法来替换。

3.3.3 Change Buffer 写缓冲

Change buffer是Buffer Pool的一部分。作用是减少随机磁盘访问，提升更新性能，如果这个数据也不是唯一索引，不存在数据重复的情况（更新记录的数据页不在内存中），就不需要从磁盘加载索引页判断数据是否重复（唯一性检查）。这种情况下先把修改记录在内存的缓冲区中，从而提升更新语句的执行速度。这块区域就是Change Buffer。

最后把Change Buffer记录到数据页的操作叫做merge。什么时候发生merge？有几种情况：

在访问这个数据页的时候通过后台线程数据库shut downredo log写满时触发。

如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会再写数据后立即读取，就可以使用CB。可以调大这个值来支持写多读少的情况。也就是CB占BP的的比例，默认是25%。

3.3.4 Adaptive hash index

索引应放在磁盘的，为什么要专门把一种hash的索引放到内存？在索引讲解再讨论。

3.3.5 Redo log Buffer

Redo log也不是每一次都直接写入磁盘，在BP里面有一块内存区域（Log Buffer）专门用来保存即将要写入日志文件的数据，默认16M。

redo log的内容主要用于崩溃恢复。磁盘的数据文件，数据来自BP。redo log写入到系统，而不写入数据文件。

3.4 InnoDB磁盘结构

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存在表空间里。InnoDB的表空间分为5类：

3.4.1系统表空间 systemtablespace

在默认情况下InnoDB存储引擎有一个共享表空间，也叫系统表空间，包含InnoDB数据字典和双写缓冲区，change Buffer和Undo log。

Undo log

单独介绍。

数据字典

由内部系统表组成，存储表和索引的元数据

双写缓冲区

InnoDb的页和OS的页大小不一致，InnoDB的是16K，OS的是4K，因此InnodB的页写入时，一个页需要分4次写。如果存储引擎正在写时发生宕机，可能出现页只写了一部分的情况，这叫做部分写失效。由于此时某个页已经破坏了，无法通过redo log来修复，此时只能通过一个页的副本来进行，这就是双写技术。

跟redo log一样，double wire 由两部分组成，一部分是内存的double write，一部分是磁盘的double write。因为double write是顺序写入的，不会带来太大的开销。DWB由128个页构成，容量只有2M。

具体执行过程为：

页数据先memcopy到DWB的内存里；DWB的内存里，会先刷到DWB的磁盘上；DWB的内存里，再刷到数据磁盘存储上；

步骤2和步骤3要写2次磁盘，这就是“Double Write”的由来。

能够通过DWB保证页数据的完整性，但毕竟DWB要写两次磁盘，会不会导致数据库性能急剧降低呢？

分析DWB执行的三个步骤：

页数据memcopy到DWB的内存，速度很快；DWB的内存fsync刷到DWB的磁盘，属于顺序追加写，速度也很快；刷磁盘，随机写，本来就需要进行，不属于额外操作；

128页（每页16K）2M的DWB，会分两次刷入磁盘，每次最多64页，即1M的数据，执行也是非常之快的。

根据第三方测评，该方式损失大约10%的性能。

【扩展】

DW ：data warehouse数据仓，DW数据分层，由下到上为 DWD,DWB,DWS

DWD：data warehouse detail 细节数据层，有的也称为 ODS层，是业务层与数据仓库的隔离。DWB：data warehouse base 基础数据层，存储的是客观数据，一般用作中间层，可以认为是大量指标的数据层。DWS：data warehouse service 服务数据层，基于DWB上的基础数据，整合汇总成分析某一个主题域的服务数据，一般是宽表。3.4.2 独占表空间 file-pre-table tablespace

开启后，为每个表单独占一个表空间。但是其他的数据，例如回滚信息，插冲索引页，系统事务信息，双写信息等都还是放在原来的共享表空间里的。

3.4.3 通用表空间

通用表空间也是一种共享的表空间，看相关说明，功能存储不同数据库的表，还能自定义数据路径和文件，其他没看出来有啥用。

3.4.4 临时表空间

存储临时表的数据，包括用户创建的临时表，服务器重启时会被删除重建。

3.4.5 undo log 空间

undo log默认在系统表空间ibdata1文件中，因为共享表空间不会自动收缩，也可以单独创建一个undo表空间。

3.5 后台线程

后台线程的主要作用是负责刷新内存池和把修改的数据页刷新到磁盘上，后台线程有master thread，IO thread，purge thread，page cleaner thread几种。

master thread 负责刷新缓存数据到磁盘并协调调度其他后台进程。IOthread 分为insert buffer，log，read，write 线程，分别用来处理insert buffer，重做日志和读写请求的IO回调。purge thread 用来回收 undo 页。page cleaner thread 用来刷新脏页。四、InnoDB是如何运行的4.1 InnoDB执行流程

DML数据操作语句（更新、删除、插入）这些在执行的时候肯定要记录日志，MySQL 自带的日志模块 binlog（归档日志） ，所有的存储引擎都可以使用，常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），假如我们要更新ID为2的这条数据当前值自增1，其中ID为主键，加了索引：

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

我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

连接器： 连接数据库User，并通过输入账号密码通过连接认证， 查询缓存不执行，因为在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空分析器: 进行语法分析，提取关键字：use 、go、update 、set 、where ，判断关键字是否满足MySQL的语法， 预处理器：进一步获取UserInfo表名、列名：name、age，判断这些元素是否都存在，如果都存在则验证权限，如果权限存在继续向下优化器： 定位到要更新的数据，查询tml这一条数据，然后把age改为18，生成一个执行计划执行器: 调用handler查询相关接口写入这一行数据执行器先找存储引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回执行器拿到引擎给的行数据，把这个值设置为18，得到新的一行数据，再调用引擎接口写入这行新数据。存储引擎将这行新数据更新到内存中前，会先把原来的值写入到 undo log 文件中，用于提交失败后回滚；引擎判断该记录所在的数据页是否可以写入 change buffer；将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态，然后告知执行器执行完成了，随时可以提交事务执行器收到通知后记录 binlog，并把 binlog 写入磁盘执行器调用引擎接口，提交 redo log 为提交状态。

图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的

以上就是在更新数据时的一些操作。

4.2两阶段提交4.2.1为什么日志需要“两阶段提交”？

是为了让两份日志之间的逻辑一致。

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。看看这两种方式会有什么问题。

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

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

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

4.2.2 “两阶段提交”保证的效果

binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。如果 DBA 承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，可以这么做：

首先，找到最近的一次全量备份，如果运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。这样临时库就跟误删之前的线上库一样了，然后把表数据从临时库取出来，按需要恢复到线上库去。

当然不只是误操作后需要用这个过程来恢复数据。当需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，常见的做法也是用全量备份加上应用 binlog 来实现的，这个两个日志的“不一致”就会导致线上出现主从数据库不一致的情况。一定程度上，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

因为InnoDB的Buffer Pool即是重点又是难点，所以下篇文章“详细”分析…

#程序员##学习#