图解MySQL-小林code笔记

前言

学习MySQL，拼命地学习MySQL的原理。

一、基础篇

执行一条select语句，期间发生了什么？

• 连接器：建立连接，管理连接、校验用户身份；
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL8.0已删除该模块；
• 解析SQL：通过解析器对SQL查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
• 执行SQL：执行SQL公共分为三个阶段：
  • 预处理阶段：检查表或字段是否存在；将select *中的*符号扩展为表上的所有列；
  • 优化阶段：基于查询成本的考虑，选择查询成本最低的执行计划；
  • 执行阶段：根据执行计划执行SQL查询语句，从存储引擎读取记录，返回给客户端。

MySQL一行记录是怎么存储的？

MySQL的数据存放在哪个文件？

我们创建一个名为my_test的database，该database里有一张名为t_order的数据库表。

MySQL存储的行为是由存储引I擎实现的，MySQL支持多种存储引擎，不同的存储引擎保存的文件自然也不同。
InnoDB每创建一个database就会在/var/lib/mysql/目录里面创建一个以database为名的目录，然后保存表结构和表数据的文件都会存放在这个目录里。对应里面有三个文件，分别代表着：

• db.opt，用来存储当前数据库的默认字符集和字符校验规则。
• t_order.frm，t_order的表结构会保存在这个文件。
• t_order.ibd，t_order的表数据会保存在这个文件。

表空间文件的结构是怎么样的？

表空间由段（segment）、区（extent）、页（page）、行（row）组成。

1. 行（row）：数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。
2. 页（page）：记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取(也就是一次I/O操作)
   只能处理一行数据，效率会非常低。因此，InnoDB的数据是按「页」为单位来读写的，默认每个页的大小为16KB。
   页是InnoDB存储引起磁盘管理的最小单元，意味着数据库每次读写都是以16KB为单位的。页的类型由很多，常见的有数据页、undo日志页、溢出页等等。数据表中的行记录是用用「数据页」来管理的。
3. 区（extent）：在表中数据量大的时候，为某个索引分配空间的时候不再按照页的单位分配了，而是按照区（extent）为单位分配。每个区的大小为1MB，对于16KB的页来说，连续的64个页会被划为1个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序I/O了。
4. 段（segment）：表空间是由各个段组成的，段是由多个区组成的。段一般分为数据段、索引段和回滚段。

• 索引段：存放B+树的非叶子节点的区的集合；
• 数据段：存放B+树的叶子节点的区的集合；
• 回滚段：存放的是回滚数据的区的集合。

InnoDB 行格式有哪些？

行格式就是一条记录的存储结构。
InnDB提供了4种行格式，分别是Redundant、Compact、Dynamic和Compressed行格式。

• Redundant是很古老的行格式了，且非紧凑的格式，MySQL5.0版本之前用的行格式，现在基本没人用了。
• MySQL5.0之后引l入了Compact行记录存储方式，Compact是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从MySQL5.1版本之后，行格式默认设置成Compact。
• Dynamic和Compressed两个都是紧凑的行格式，它们的行格式都和Compact差不多，因为都是基于Compact改进一点东西。从MySQL5.7版本之后，默认使用Dynamic行格式。

COMPACT 行格式长什么样？

可以看出，一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分。

记录的额外信息

记录的额外信息包含3个部分：变长字段长度列表、NULL值列表、记录头信息。
1、变长字段长度列表
varchar这种变长类型在存储数据的时候，也要把数据占用的大小存起来，存到「变长字段长度列表」里面，读取数据的时候才能根据这个「变长字段长度列表」去读取对应长度的数据。其他TEXT、BLOB等变长字段也是这么实现的。
「变长字段长度列表」中的信息之所以要逆序存放，是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个CPU Cache Line中，这样就可以提高CPU Cache的命中率。
当数据表没有变长字段的时候，比如全部都是int类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了。因为没必要，不如去掉节省空间。

2、NULL值列表
表中的某些列可能会存储NULL值，如果把这些NULL值都放到记录的真实数据中会比较浪费空间，所以Compact行格式把这些值为 NULL的列存储到 NULL值列表中。
如果存在允许NULL 值的列，则每个列对应一个二进制位（bit)，二进制位按照列的顺序逆序排列。

• 二进制位的值为1时，代表该列的值为NULL。
• 二进制位的值为0时，代表该列的值不为NULL。

另外，NULL值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补0。

当数据表的字段都定义成NOT NULL的时候，这时候表里的行格式就不会有NULL值列表了。

3、记录头信息
记录头信息中包含的内容很多，这里说几个比较重要的：

• delete_mask：标识此条数据是否被删除。从这里可以知道，我们执行delete删除记录的时候，并不会真正的删除记录，知识将这个记录的delete_mask标记为1。
• next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。指向的是下一条记录的「记录头信息」和口「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
• record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录。

记录的真实数据

记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为row_id、trx_id、roll_pointer：

• row_id：如果我们建表的时候指定了5或者唯一约束列，那么就没有row_id隐藏字段了。如果既没有指定主键，又没有唯一约束，那么InnoDB就会为记录添加row_id隐藏字段。row_id不是必需的，占用6个字节。
• trx_id：事务id，表示这个数据是有哪个事务生成的。trx_id是必需的，占用6个字节。
• roll_pointer：这条记录上一个版本的指针。roll_pointer是必须的，占用7个字节。

varchar(n)中n最大取值为什么？

MySQL规定除了TEXT、BLOB这种大对象类型之外，其他所有的列（不包含隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。也就是说，一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节。

varchar(n)字段类型的n代表的是最多存储的字符数量，并不是字节大小，要算varchar(n)最大能允许存储的字节数，还要看数据库表的字符集，因为字符集代表着，1个字符要占用多少字节，比如ascii字符集，1个字符占用1字节，那么varchar(100)意味着最大能允许存储100字
节的数据。

CREATE TABLE test (
	`name` VARCHAR(65535) NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT  = COMPACT;

单字段的情况

storange overhead：「变长字段长度列表」和「NULL值列表」
一行数据的最大字节数是655335（不包含TEXT、BLOB这种大对象类型），其实是包含「变长字段长度列表」和「NULL值列表」所占用的字符数的。
我们存储字段类型为varchar(n)的数据时，其实分成了三个部分来存储：

• 真实数据
• 真实数据占用的字节数
• NULL标识，如果不允许为NULL，这部分不需要

变长字段长度列表所占的字节数 = 所有「变长字段长度列表」占用的字节数只和。

多字段的情况

如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

行溢出后，MySQL是怎么处理的？

MySQL中磁盘和内存交互的基本单位是页，一个页的大小一般是16KB，也就是16384字节，而一个varchar(n)类型的列最多可以存储65532字节，一些大对象如TEXT、BLOB可能存储更多的数据，这时候一个页可能就存不了一条记录。这个时候就会发生页溢出，多的数据就会存到另外的「溢出页」中。

Compact行格式在发生行溢出的后的处理，在记录的真实数据出只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用20字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。

Compressed和Dynamic这两个行格式和Compact非常类型，但记录的真实数据处不会存储该行的一部分数据，值存储20个字节指针来指向溢出页。而实际的数据都存储在溢出页中，如下图：

总结

MySQL的NULL值是怎么存放的？

MySQL的Compact行格式中会用「NULL值列表」来标记值为NULL的列，NULL值并不会存储在行格式中的真实数据部分。
NULL值列表会占用1字节空间，当表中所有字段都定义成NOT NULL，行格式就不会有NULL值列表，这样就可节省1字节的空间。

MySQL怎么知道varchar(n)实际占用数据的大小？

MySQL的Compact行格式中会用「变长字段长度列表」存储变长字段实际占用的数据大小。

varchar(n)中n最大取值为多少？

一行记录最大能存储65535字节的数据，但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以，我们在算varchar(n)中的n的最大值时，需要减去这两个列表所占用的字节数。
如果一张表只有一个varchar(n)字段，且允许为NULL,，字符集为ascii，varchar(n)中n的最大取值为65532。
计算公式：65535-变长字段字节数列表所占用的字节数-NULL值列表所占用的字节数=65535-2-1=65532。
如果有多个字段的话，要保证所有字段的长度+变长字段字节数列表所占用的字节数+NULL值列表所占用的字节数<=65535。

行溢出后，MySQL是怎么处理的？

如果一个数据页存不了一条记录，InnoDB存储引擎会自动将溢出的数据存放到「溢出页」中。
Compact行格式针对行溢出的处理是这样的：当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用20字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。
Compressed和Dynamic这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储20个字节的指针来指向溢出页，而实际的数据都存储在溢出页中。

三、事务篇

事务隔离级别是怎么实现的？

事务：保证操作的原子性。

事务有哪些特性？

事务必要要遵守4个特性：

• 原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
• 一致性：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
• 持久性：事务处理结束后，对数据的修改就是永久的，即使系统故障也不会丢失。

InnoDB引擎通过什么技术来保证事务的这四个特性：

• 原子性是通过undo log（回滚日志）来保证的；（undo属于MySQL概念，不属于InnoDB）;
• 隔离性是通过MVCC（多版本并发控制）或锁机制来保证的；
• 持久性是通过redo log（重做日志）来保证的；
• 一致性是通过原子性+隔离性+持久性来保证的。

并发事务会引起什么问题？

MySQL服务端是允许多个客户端连接的，这意味着MySQL会出现同时处理多个事务的情况。
在同时处理多个事务的时候，就可能出现脏读、幻读、不可重复读等问题。

• 脏读：如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。
• 幻读：在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。
• 不可重复读：在一个事务多次读取同一个数据，如果出现前后两次读到的数据不一致的情况，就意味着发生了「不可重复读」现象。

这三种现象的严重性排序如下：脏读 > 不可重复读 > 幻读

事务的隔离级别有哪些？

SQL 标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，这四个隔离级别如下：

• 读未提交：指一个事务还没提交时，它做的变更就能被其他事务看到；
• 读已提交：指一个事务提交之后，它的变更才能被其他事务看到；
• 可重复读：指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，
• 串行化：会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

MySQL在「可重复读」的隔离级别下，可以很大程度上避免幻读现象的发生（注意是很大程度避免，并不是彻底避免），所以MySQL并不会使用「串行化」隔离级别来避免幻读现象的发生，因为使用「串行化」隔离级别会影响性能。
MySQL InnoDB引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：

• 针对快照读（普通select语句），是通过MVCC方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（select…for update等语句），是通过next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行select…for update语句的时候，会加上next-key lock，如果有其他事务在next-key lock锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好地避免幻读问题。

Read View在MVCC里如何工作的?

背景知识：

• Read View中四个字段作用；
• 聚簇索引记录中两个跟事务有关的隐藏列。

Read View有四个重要的字段：

• m_ids：指的是在创建Read View时，当前数据库中「活跃事务」的事务id列表，注意是一个列表，“活跃事务”指的是启动了但还没提交的事务。
• min_trx_id：指的是在创建Read_View时，当前数据库中「活跃事务」中事务id最小的事务，也就是m_ids的最小值。
• max_trx_id：这个并不是m_ids的最大值，而是创建Read View时当前数据库中应该给下一个事务的id值，也就是全局事务中最大的事务的id值+1；
• creator_trx_id：指的是创建该Read View的事务的事务。

聚簇索引记录中的两个隐藏列：

• trx_id：当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务id记录在trx_id隐藏列里；
• roll_pointer：每次对某条聚簇索引记录进行改动时，就会把旧版本的记录写入到undo日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

在创建Read View后，我们可以将记录中的trx_id划分这三种情况：

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还是这几种情况：

• 如果记录的trx_id值小于Read View中的min_trx_id值，表示这个版本的记录是在创建Read View前已经提交的事务生成的，所以该版本的记录对当前事务可见。
• 如果记录的trx_id值大于等于Read View中的max_trx_id值，表示这个版本的记录是在创建Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
• 如果记录的trx_id在Read View的min_trrx_id和max_trx_id之间，需要判断trx_id是否在m_ids列表中：
  • 如果记录的trx_idf在m_ids列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录当前事务不可见。
  • 如果记录的trx_id不在m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫MVCC（多版本并发控制）。

可重复度是如何工作的？

可重复读隔离级别是启动事务时生成一个Read View，然后整个事务期间都在用这个Read View。

读提交是如何工作的？

MySQL可重复度隔离级别，完全解决幻读了吗？

四、锁篇

MySQL有哪些锁？

MySQL是怎么加锁的？

update没加索引会锁全表？

MySQL记录锁+间隙锁可以防止删除操作而导致的幻读吗？

MySQL死锁了，怎么办？

字节面试：加了什么锁，导致死锁的？

五、日志篇

MySQL日志：undo log、redo log、binlog有什么用？

六、内存篇

揭开Buffer Pool的面纱