MYSQL是怎么运行的

一. 初识MySQL

1.1 MySQL的架构

是由服务器程序和客户端程序两部分组成。例如在Linux操作系统下通过执行安装目录下的bin文件夹内的mysql.server启动脚本启动服务端程序，不过通常我们会讲这个目录直接加入到系统环境变量里，这样不用指定具体目录就能执行到bin下的可执行命令。通过运行mysql运行客户端程序，例如通过mysql -uroot -p 默认连接本机的mysql服务器。

1.2 客户端和服务端连接过程

服务端和客户端的连接过程，实际上是服务端进程和客户端进程通信的过程。MYSQL支持以下三种进程间通信

TCP/IP

通过IP+端口号的方式，监听网络上客户端的连接。默认监听的3306端口

命名管道和共享内存

windows下，如果客户端和服务器端在同一台主机中，可通过启动服务器端程序和客户端程序时添加参数支持

UNIX套接字

如果服务器端和客户端都运行在为类UNIX的主机上，可以使用UNIX域套接字进行通信

1.3 服务器处理客户端请求

处理连接：通过客户端发来的身份信息，如主机信息，用户名，密码等信息鉴权，另外可以使用TLS协议进行通讯间加密

解析与优化：

查询缓存是将刚刚处理过的查询请求和结果进行缓存，由于维护缓存带来的花销，不推荐使用查询缓存，且8.0版本已经删除。

语法解析作用是分析语法是否正确，讲要操作的表对象，数据对象，条件等提取出来放入到数据结构里，供下一步使用。

查询优化的结果就是生成一个执行计划，这个执行计划表明了查询走哪些索引，表之间的连接顺序。EXPLAIN命令

1.4 存储引擎

存储引擎以前叫做表处理器，这样叫是不是就老土很多。实际上，数据的存储和提取操作都封装到存储引擎模块。不同的存储引擎有不同的存储结构，存储算法支持。常用的就俩

InnoDB

MyISAM

由于MyISAM不支持事务，所以大部分场景都是用的InnoDB引擎

二. 字符集和比较规则

由于计算机中只能存储二进制数据，所以字符串的存储就是建立字符和二进制数据的映射关系。

ASCII字符集，收录了128个字符，包括空格，标点符号，数字，字母等

UTF-8字符集几乎收录了世界所有国家使用的字符，使用1-4字节编码

由于常用的一些字符使用1-3个字节就可以表示了，为了系统的存储和性能，所以MySQL针对字符集设计了utf8mb3(utf8)和utf8mb4两个概念.在MySQL8中，作者优化了utf8mb4字符集，作为了默认的字符集

比较规则是指字符的比较规则，MySQL对每种字符集都有默认的比较规则，utf8字符集默认的比较规则就是utf8_general_ci.

要保证客户端和服务器端的编解码的字符集一致，不然就会产生乱码

三. InnoDB记录存储结构

InnoDB记录存储结构是指对于InnoDB存储引擎，当往里存入一行数据时，规定了数据存储到哪，什么格式存储。

InnoDB的数据会被存储到磁盘上，读取的时候为了加速数据读取的速度，采用页的方式为基本单位进行内存和磁盘交互的基本单位。一页默认为16KB。

1. InnoDB行格式

即InnoDB引擎按照什么格式去存储每一行的数据

1.1 Compact行格式

由记录的额外信息和记录的真实数据部分

1.1.1 变长字段长度列表

由于MySQL支持的字段类型有例如VARCHAR，TEXT，BLOB等变长类型，所以在实际存储的时候会变长字段长度列表里按照变长字段逆序存放长度值。在变长编码的字符集时，该定长列的字节数也会加入到变长字段长度列表中，如果没有可变长度字段，则该部分自然就不存在

1.1.2 NULL值列表

为了将记录中的值为NULL的列统一管理起来。该列表将每一个允许为NULL的列对应一个二进制位，将这些二进制位逆序排列。二进制值为1表示该列为NULL

1.1.3 记录头信息

为了描述该条记录的一些属性信息，由固定的5字节组成

5字节即5*8,40位二进制

预留位1,2未使用

delete_flag 标记该记录是否被删除

min_rec_flag 表示该记录是否为B+树的每层非叶子节点中最小的目录项记录

n_owned 一个页中记录会分组，每组中有一个记录为组长，组长的n_owned代表该组的记录数。其他记录的n_owned为0

heap_no 表示当前记录在页面堆中的位置

record_type表示记录的类型

0表示普通记录
1表示B+树中非叶子节点的目录项记录
2表示Infimun记录
3表示Supremum记录

next_record 表示下一条记录的相对位置

1.1.4 隐藏列

在列数据，会自动为每一条记录都添加一些列

row_id 行id，唯一标识一条记录

trx_id 事务id

roll_pointer 回滚指针

📗

InnoDB表的主键生成策略，优先使用用户自定义的主键，其次为选取一个不能存NULL的UNIQUE键作为主键，其次是增加的row_id为主键

1.1.5 溢出列

由于前面提到过的按页存储，一页16KB，对于某列的数据如果过大，一页存不下，COMPACT行格式会只在该页存储该列一部分数据，剩余数据分散存放在几个其他页里，在记录的真实数据里用20字节存储指向这些页的地址

1.2 DYNAMIC行格式

MySQL5.7默认的行格式就是DYNAMIC，与COMPACT大部分都是类似，只不过在处理溢出列的时候不同，不会存储部分真实列数据，而是将列全部数据存储到溢出页，只存储指向溢出页的地址

📗

COMPRESSED格式与DYNAMIC类似，但是COMPRESSED格式会使用压缩算法对页面进行压缩。REDUNDANT比较原始

四. InnoDB数据页结构

页是InnoDB管理存储空间的基本单位，为了不同的目的设计了不同类型的页，如存放表空间头部信息的页，存放Change Buffer的页，存放INODE的页，存放undo日志信息的页

1. 数据页结构

File Header 页的一些通用信息

Page Header 页的专用信息

Infimum + Supremum 表示页面中的最小记录和最大记录

User Records 用户存储的记录内容空间

Free Space 页中未使用的空间

Page Directory 页目录，表示页中某些记录的相对位置

File Trailer 文件尾部，用于校验页是否完整

2. 记录在页中的存储

当我们通过insert向数据库里插入一条数据时，这条数据会按照行格式存储到页里，通过从Free Space里申请空间，并将这个空间划分到User Records部分。

数据页中Infimum和Supremum是两行虚拟的记录，Infimum表示页中最小的记录，Supremum表示页中最大的记录，用户记录都比Infimum大，比Supremum小。通过数据行中的next_record将虚拟记录和用户记录通过链表的方式连接起来。

存储用户记录的地方叫做堆区，每一条记录都有一个heap_no编号，排列在后方记录的heap_no比前一个大1，其中排列顺序是按照主键大小排序的。

📙

需要注意的是，next_record指向的是下一条记录真实数据部分，而不是整个下一条记录的头部。目的是向左读取就能读取到记录头信息，向右读取就能读取倒真实数据。也就是前文提到的记录头里变长字段长度列表，NULL值列表是逆序排列的原因。

删除这条记录只会将该记录的delete_flag置为1。并调整next_record指针指向关系。物理删除并不在此时进行，所以无论如何操作，InnoDB内部都会维持着一个主键从小倒大的顺序记录链表。而当我们下一次再次插入一个主键id为2的记录时，会重新复用这个删除的记录空间。

3. Page Directory

如果一个数据页里的数据较多，当查找一条数据时，线性遍历速度堪忧。为了解决这个问题，设计了页目录,其实就是给页内所有的数据加了索引。按照主键的值，通过索引二分法加速访问。

通过对数据进行分组，将每组的最后一个记录的值写入到页目录里，即构成二级索引。每组的最后一个记录的记录头里n_owned表示该组的记录个数。

4. Page Header

记录数据页记录的状态信息。比如数据页存储了多少个记录，Free Space在页内偏移量，页目录项的数量。

5. File Header

前面提到的page header是针对数据页记录的状态信息，前面提到过页有多种类型。File Header通用于各种类型的页，比如页的编号，下一页和上一页的位置等等

FIL_PAGE_SPACE_OR_CHKSUM 表示页的校验和

FIL_PAGE_OFFSET 页号

FIL_PAGE_PREV,FIL_PAGE_NEXT 上一页和下一页。用于将所有页连接，构成双向链表。

FIL_PAGE_TYPE 页类型

FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID 该页属于哪个表空间

FIL_PAGE_FILE_FLUSH_LSN

FIL_PAGE_LSN

6. File Trailer

页是MySQL操作内存和磁盘交互的基本单位。如果内存修改了，而磁盘没有刷新，或者只刷新了一部分，所以通过File Trailer进行校验。该部分在页的最后位置。页尾部的校验和和File Header的校验和相对应来保证数据的完整性。

五. B+树索引

1. 索引原理

结合前面页目录的经验，我们很容易想到以下的结构来加速页的访问

其实当数据量很多的时候，页有很多，则页目录会有很多，则装页目录的页也会有很多。而且当我们增删改查操作时，如果删除28页所有记录，则目录项项2没有必要存在，就需要删除目录项2，就得把目录项2后面的目录项都向前移动以下。所以InnoDB设计了一种更灵活管理所有目录项的方式，其实和页里存储页记录项相似。

将存放目录项记录的数据页的record_type设为1，存放用户数据的数据页的record_type设为0。在目录项的数据页里，像管理用户记录一样管理主键和页号对应的目录项数据。

真正存储用户记录的页都在B+树最底层的节点上，这些节点称为叶子节点或叶节点，其余用来存放目录项记录的节点称为非叶子节点或内节点。其中B+树最上边的节点称为根节点。

假设主键为int，大小就是4Byte，假设指针大小为6Byte，一共占10KByte，当然前面说到的数据页结构里的File Header，PageHeader部分大小可以忽略不计，也就上百字节。一页大小为16KB，则可以存储大约1000个目录项。对于叶子节点，假设一行数据的大小为1KByte，则可以存储大约16行记录。

三层节点可以存储的数据量为： 1000 * 1000 * 16 = 16000000 条，约一千多万条

四层节点: 1000 * 1000 * 1000 * 16 = 16000000000 条，约100亿条

通过这样，只需要进行四个页内的查找

聚簇索引

向上面图所示，使用记录主键值的大小进行记录和页的排序，B+树的叶子节点存储的是完整的用户记录，就称为聚簇索引。

二级索引

当查询条件里没有主键，只是其他列作为搜索条件，可以建立别的列c2的索引树。这种索引树的特点是页目录项里的键是c2的值，值为页号。叶子节点的数据页里，键为c2的值，值为该记录的主键值。也就是说二级索引的叶子节点并不存储完整的用户记录，而只是记录的c2对于的主键值。再通过获得到的主键值通过聚簇索引回表得到完整的用户记录。

📙

其实二级索引的非叶子节点除了存储索引值，页号，还存储了主键值。因为二级索引的索引不一定是唯一的，也就会导致比如两个数据页A，B的索引值相同，此时再来新增一个索引值相同的记录，往A插入还是B插入呢？所以就没有唯一确定的位置。所以一般通过索引值排序后，再通过主键进行排序，因为主键必然是唯一的，所以必然能唯一确认一个位置。是在索引列有序的情况下，使得主键有序。没有主键？不可能，忘了之前提到过的隐藏列嘛！

联合索引

以多个列的大小作为排序规则，也就是多个列建立索引。比如将c1,c2,c3作为联合索引，则索引会先按照c1排序，再在c1值相同的情况下对c2排序，直到c3有序。联合索引只会维护一颗树。

MyISAM中的索引方案

前面介绍的InnoDB索引即数据，也就是聚簇索引已经包含所有的用户记录。而MyISAM却不同，将索引和数据分开存储。

首先将所有的数据存储到一个单独文件里，并没有排序，称为数据文件。还有个索引文件，索引里没有完整的用户记录，而是主键值和数据文件里行号的对应关系。通过行号去数据文件里查找。MyISAM需要进行回表操作。

2. 索引的代价

索引在时间和空间上都会拖后腿。

空间上，每建立一个索引都会建立一颗B+树，B+树的存储是占空间的

时间上，增删改操作时，都需要维护B+树索引。这些操作都可能会使得节点和记录的排序造成破坏。插入或者删除可能会引起页的分裂和页的回收，以维护节点和记录的排序a

六. InnoDB表空间

系统表空间对应着文件系统的一个或者多个实际文件，独立表空间对应着文件系统的”表名.ibd”实际文件。表空间就是被切分为许多个页的池子。

表空间中的页都有一个唯一的页号，这个页号代表着该页在表空间的相对位置，可以通过页号快速定位到该页。这个页号由4字节组成，也就是32位，一页默认16KB，则一个表空间最大能存储的数据大小是64TB。

1. 独立表空间结构

1.1 区

独立表空间被分为许多连续的区，每个区由64个页组成，每个区1MB空间，每256个区划分为一个组，每个组前几个页的类型是固定的。

第一个组前三个页的类型是固定的

FSP_HDR 用来记录整个表空间的一些整体属性，和本组所有区的属性

IBUF_BITMAP 存储关于Change Buffer的一些信息

INODE 存储INODE Entry

其余各组前两个页类型是固定的

XDES 用来记录本组256个区的属性

IBUF_BITMAP

为什么要提出区这个概念?

如果只靠页这个概念，页可以是在物理上无连续的空间，也就是说相邻页号在物理位置可能距离很远，而当我们查询时，涉及到需要跨多个页的查询，可能因为在磁盘上位置的差异导致随机IO，而磁盘的随机IO是很慢的，因为需要伴随磁头的物理移动。所以尽量让链表中相邻的页的物理位置也相邻。

1.2 段

为了不区分叶子节点和非叶子节点，都放到刚申请的区里，效果必然大打折扣。而为了区别叶子节点和非叶子节点，提出了段的概念。叶子节点有自己独立的区，非叶子节点也有自己独立的区。存放叶子节点或者非叶子节点的集合就是一个段。

默认下，InnoDB就一个聚簇索引，一个索引两个段，段是以区为单位申请空间的，一个区默认1MB，也就是说一个很少记录的表也会占据2MB空间嘛？为了这种浪费，提出了碎片区的概念。碎片区中的有的页可能属于段A，可能有的属于段B。碎片区属于表空间。

当开始向表中插入数据时，段是从碎片区申请单个页面为单位来分配存储空间的，当某个段的占用超过32个碎片区页面后，会以完整的区为单位来分配存储空间。

1.3 区的分类

空闲的区 FREE

有剩余空闲页面的碎片区 FREE_FRAG

没有空闲页面的碎片区 FULL_FRAG

附属于某个段的区 FSEG

为了管理这些区设计了XDES Entry结构，每个区都对应一个XDES Entry结构。

Segment ID 段ID，表示区分配给某个段的ID

List Node 用来构成XDES Entry的链表

State 前文介绍的区的四个分类。

Page State Bitmap 一共128位，一个区默认64页，每页占两个位，第一位用来表示页是否空闲，第二位还没有用到

1.4 XDES Entry链表

当插入数据时，数据量较少时，此时段的空间是从碎片区里申请的，插入数据时需要知道哪些区的状态是FREE，哪些区的状态是FREE_FRAG。如果遍历这些区对于的XDES Entry结构，数据量很大时，区很多，每次都遍历这么多的Entry，速度很慢。所以将XDES Entry中List Node部分构建链表。

将Free状态的区对于的Entry构成一个链表，称为Free链表

将Free_Frag状态构成Free_Frag链表

FULL_FRAG链表

想找一个FREE_FRAG区时，将FREE_FRAG链表头节点拿出来，从这个区里选个零散页来插入数据。当这个区数据满时，从Free Frag链表移除，设置State为Full Frag，加入到Full Frag链表。

前面提到的三个链表是属于整个表空间的。一个索引对应两个段，一个段是非叶子节点的段，一个是叶子节点的段。

当我们要查找属于某个段的区时，表空间的三个链表其实并不难展现。所以每个段又维护了三个链表

Free链表一个段内，所有页都是空闲页面的区对于的XDES Entry构成这个链表。

NOT_FULL链表，一个段内，有空间页面的区对应的XDES Entry构成这个链表

FULL 链表

为了方便找到这些链表，提出了List Base Node结构。包含了节点个数信息，和链表的头节点和尾节点的指针。

段都定义了INODE Entry结构，用来记录段中的属性

Segment ID 段的ID

NOT_FULL_N_USED 表示NOT_FULL链表使用的页面数量

List Base Node 三个链表

Fragment Array Entry 零散页面

1.5 各类型页面

属于表空间的三个链表，以及属于表空间的INODE Entry又存在哪里呢？前面提到过的256个区为一个组，每组起始页面的类型是固定的

FSP_HDR类型

除了页面通用的部分，File Header，File Trailer，该类型页面有

File Space Header 存储表空间整体的属性信息

XDES Entry 存储本组256个区对应的属性信息

Empty Space 页结构的填充，无作用

其中File Space Header存储的属性信息，比较重要的有

Space ID 表空间ID

Size 表空间拥有的页面数

FREE Limit 尚未被初始化的最小页号，大于或者等于该页号的XDES Entry都没被加入到FREE链表

FRAG_N_USED FREE_FRAG链表中已经使用的页面数量

FREE List,FREE_FRAG,FULL_FRAG 链表的基节点

Next Unused Segment ID 下一个未被使用的段ID

只有第一个组的第一页为该类型

XDES类型

前面提到的每一个XDES Entry结构对应一个区的属性信息。当表空间所需要的区很多时，一个页面可能存放不下所有的Entry结构。所以把表空间的区分成若干个组，每个组开头的一个页面记录着该组内所有的Entry结构。

IBUF_BITMAP类型

每个分组的第二个页面的类型为该类型。存储的是Change Buffer信息。是将尚未加载到磁盘引起的数据改动信息记录到这里，等数据库空闲或者该页面加载到磁盘了再进行修改合并。

INODE类型

为了存储INODE Entry结构的。

2 系统表空间

整个MYSQL进程只有一个系统表空间，该空间记录一些与整个系统相关的信息。

六. Buffer Pool

为了调节内存和磁盘速度不一致，提出了缓存页面的Buffer Pool.在MySQL服务器启动时就向操作系统申请了一片连续的内存。

控制块

Buffer Pool里也是按照页存储，称为缓冲页。将内存里的页和磁盘里的页对应起来。而为了记录这些对应关系，和这些缓冲页的一些基本信息，例如缓冲页所在的表空间编号，页号，在buffer pool中的地址等信息，提出了控制块。一个控制块对应一个缓冲页面。所以在Buffer Pool里，前面一段空间是连续的控制块，后面是缓冲页。

Free链表

为了查找哪些缓冲页用上了，哪些没用上。在服务刚启动时，所有页面都没有使用，此时将所有控制块加入到Free链表。需要将页面进行缓冲时，直接从free链表头节点取就行。

哈希表

而为了在查询时，怎么知道这个页面是否被缓存了，提出了哈希表的方式，通过键为表空间号+页号作为key，缓冲页控制块的地址作为value就能加速访问。

flush链表

当修改了缓冲页的数据时，与磁盘的页不一致，称为脏页。总不能一次把所有缓冲页都刷新到磁盘里吧，如果buffer pool足够大，那要刷新的页太多了。所以把修改过的缓冲页的控制块链接到flush链表里。

LRU链表

如果buffer pool中的页面都被占用了，有需要缓冲新页的需求怎么办。提出了LRU淘汰策略，将最近最少使用的页面淘汰掉。每次访问一个页面，就把该页面放到链表头部，这样当buffer pool中的页面都被占用，链表尾部就被淘汰，置换新的页面。

但是InnoDB一种预读机制，就是预先读取某些页面到buffer pool。

线性预读： 如果顺序访问某个区的页面超过innodb_read_ahead_threshold的值，则会触发将下一个区的全部页面异步加载到buffer pool里。

随机预读: 某个区13个连续页面加载到buffer pool里，则异步将全区所有其他页面加载到buffer pool里。该功能默认是关闭的。

全表扫描: 全表扫描的语句需要访问的页面特别多，所以会使得LRU链表失效

针对以上问题，提出了冷热分区的LRU链表。将LRU链表37%页面划分为冷数据去，剩下的分为热数据区。页面第一次被加载访问时只会被加载到LRU的冷数据区，那第二次访问时放到热数据区嘛？当然不可行，因为一个页面有多条数据，访问其中一条数据就算访问页面一次，对于全表扫描，一个页面必然要被访问多次。所以在第一次访问的时候记录访问时间，如果再次访问的时候在1s内则不会放到热数据区，超过1s则放到热数据区

进一步优化：并不是热数据区的页面一被访问就移动到链表头部，而是如果访问的时候，页面在链表的1/4后面时才会移动到头部。

刷新脏页到磁盘

从LRU链表的冷数据中刷新一部分页面到磁盘

从flush链表刷新一部分页面到磁盘

可以通过配置多个buffer pool实例个数来提高并发处理能力。

七. 事务

现实世界的业务场景需要映射到数据库，现实世界的一次状态转换要满足

原子性

A向B转账，从A账户扣钱，B账户加钱，必须都成功，或者都失败，不存在中间状态

隔离性

A向B转两次帐，每笔之间隔离，不相互影响

一致性

数据库中的数据满足现实世界的约束

持久性

一次状态转换后，结果永久保留

八. redo日志

由于前面所提到的buffer pool，在对数据进行修改时，其实是对读入到内存的缓冲页进行修改，如果系统崩溃，那就会导致数据丢失，不满足持久性。如果一次数据的修改就将缓冲页刷到磁盘，效率是低下的，比如一条记录的插入可能会引起页分裂等情况，需要对多个页进行修改，而这两个页可能不在一个区，导致实际在磁盘位置间隔较远，这种随机IO效率也是很低下的。所以我们让对数据的修改的内容记录下来，系统重启后按照这个记录对数据进行重新更新，这种记录就是redo日志。redo日志是在磁盘上连续存储的，也避免了随机IO。

1. redo日志格式

type 日志类型

space ID 表空间id

page number 页号

data 日志具体内容

在执行一条语句时，可能会修改非常多的页面，包括系统数据页面和用户数据页面。比如可能会修改系统数据max row id的值。可能会更新非叶子结点和叶子结点。插入数据时，可能会更新页目录信息，还会更新上一条记录的next record指向。

InnoDB将其分成了多个不可分割的组。比如更新MAX ROW ID属性产生的redo日志为一组，想聚簇索引B+树的页面插入一条记录时产生的redo日志时一组。当插入数据需要页分裂时，会产生多组日志。而在记录redo日志时系统崩溃，重启后按照部分redo日志恢复出来的肯定是错误的状态。所以规定以组的方式记录redo日志，恢复时，针对某个组中的redo日志要么全部恢复，要么一条也不恢复。

2. MTR

将对底层页面进行一次原子访问的过程称为一个MTR，mini-transaction.

一个事务可以包含若干条语句，一条语句包含多个MTR，一个MTR包含若干条redo日志

3. redo日志缓冲区

为了解决磁盘速度过慢引入的buffer pool，同样写入redo日志时也不能直接写到磁盘中，所以在服务器启动时就申请了一大片称为redo log buffer的连续内存空间。又将buffer空间划分为多个连续的redo log block。这个block就像前面提到的buffer page类似。

4. redo log刷盘时机

log buffer空间不足，默认占了50%就刷盘

事务提交时，为了保证持久性

将某个脏页刷新到磁盘，必须先保证页面修改时对应的redo log刷新到磁盘

后台的线程，每秒一次将log buffer中的redo日志刷新到磁盘

正常关闭服务器时

做checkpoint时

5. lsn

log sequence number用来表示总共写入的redo日志量。初始lsn为8704

6. checkpoint

redo日志文件组中的容量有限，所以得循环使用。只要对应脏页刷新到磁盘了，则对应的redo日志就可以被覆盖了。设计了checkpoint_lsn用来表示可以被覆盖的日志总量。初始值也是8704

7. innodb_flush_log_at_trx_commit

为了保证持久性，要让所有的redo日志都刷新到磁盘里，会明显降低数据库性能，如果对持久性要求不这么强烈，可以修改该系统变量。

0 ：事务提交时不立刻向磁盘同步redo日志，而是后台线程去处理。会加速处理请求，但是不能保证严格的持久性

1: 事务提交时将redo日志同步磁盘，默认为该值

2: 事务提交时redo日志写入到操作系统缓冲区。这种情况数据库挂了，操作系统没挂可以保证持久性。