当前位置：首页 > news >正文

mysql总结

news 2025/2/22 16:50:13

系列文章目录

暂无

前言

mysql面试题的总结以及部分原理，部分图片为网上资源，如侵权请告知删除。

一、MySQL 执行流程

1.连接器：建立连接，管理连接、校验用户身份；

2.查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行(MySQL 8.0 已删除该模块)；

3.解析 SQL：通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；

4.执行 SQL：执行 SQL 共有三个阶段：

预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列；

优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划；

执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

二、myql一行数据怎么存储的

我们每创建一个 database（数据库）都会在 /var/lib/mysql/ 目录里面创建一个以 database 为名的目录，然后保存表结构和表数据的文件都会存放在这个目录里：
1）db.opt，用来存储当前数据库的默认字符集和字符校验规则。
2）t_order.frm，t_order 的表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
3） t_order.ibd，t_order 的表数据会保存在这个文件。表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）。这个行为是由参数 innodb_file_per_table 控制的，若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后， MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。

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

表空间由段（segment）、区（extent）、页（page）、行（row）组成，InnoDB存储引擎的逻辑存储结构大致如下图：

InnoDB 的数据是按「页」为单位来读写的，默认每个页的大小为 16KB，每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区。

2. InnoDB 行格式有哪些？

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。

3. COMPACT 行格式长什么样？

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

3.1 记录的额外信息

记录的额外信息包含 3 个部分：变长字段长度列表、NULL 值列表、记录头信息。

3.2 记录的真实数据

3.2.1 row_id

如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。

InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

1）如果有主键，默认会使用主键作为聚簇索引的索引键；

2）如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；

3）在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；

3.2.2 trx_id

事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。

3.3.3 roll_pointer

这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。用于MVCC。

4. 问题汇总

4.1 MySQL 的 NULL 值是怎么存放的？

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

NULL 值列表的空间不是固定 1 字节的。当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。

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

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

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

一行记录最大能存储 65535 字节的数据，但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。

所以，我们在算 varchar(n) 中 n 最大值时，需要减去这两个列表所占用的字节数。要保证所有字段的长度 + 变长字段字节数列表所占用的字节数（每一个varchar长度小于255是1字节，超过255长度是2字节） + NULL值列表所占用的字节数(默认1字节，超过9列为null是2字节) <= 65535。

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

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

三、索引

1. 索引分类

我们可以按照四个角度来分类索引。

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。

按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。

按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。

按「字段个数」分类：单列索引、联合索引。

2. 为什么mysql采用B+树作为索引

2.1 B+树 VS B树

B 树和 B+ 都是通过多叉树的方式，会将树的高度变矮，所以这两个数据结构非常适合检索存于磁盘中的数据。但是 MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构，原因有：

1）B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

2）B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；

3）B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

2.2 B+树 VS 二叉树

随着数据量的增加，二叉树的树高会越来越高，磁盘I/O次数会变多，B+树在千万级别的数据量下，高度依旧维持在3~4层，也就是说一次查询操作只需要3~4次磁盘I/O操作。

2.3 B+树 VS Hash

虽然hash等值查询快，但是无法做范围查找。

3. 聚簇索引和二级索引

如果叶子节点存储的是实际数据的就是聚簇索引，一个表只能有一个聚簇索引；

如果叶子节点存储的不是实际数据，而是主键值则就是二级索引，一个表中可以有多个二级索引。
在使用二级索引进行查找数据时，如果查询的数据能在二级索引找到，那么就是「索引覆盖」操作，如果查询的数据不在二级索引里，就需要先在二级索引找到主键值，需要去聚簇索引中获得数据行，这个过程就叫作「回表」。

下图为聚簇索引图：

4. B+ 树是如何进行查询的？

以上图为例子，B+ 树如何实现快速查找主键为 6 的记录：

1）从根节点开始，通过二分法快速定位到符合页内范围包含查询值的页，因为查询的主键值为 6，在[1, 7)范围之间，所以到页 30 中查找更详细的目录项；

2）在非叶子节点（页30）中，继续通过二分法快速定位到符合页内范围包含查询值的页，主键值大于 5，所以就到叶子节点（页16）查找记录；

3）接着，在叶子节点（页16）中，通过槽查找记录时(参考本章4.2 数据页内查找)，使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到主键为 6 的记录。

5. 数据页是怎么存储数据的

为了高效查询记录所在的数据页，InnoDB 采用 b+ 树作为索引，每个节点都是一个数据页。

5.1 数据页结构

InnoDB 的数据是按「数据页」为单位来读写的，默认数据页大小为 16 KB。每个数据页之间通过双向链表的形式组织起来，物理上不连续，但是逻辑上连续。

5.2 数据页内查找

数据页内包含用户记录，每个记录之间用单向链表的方式组织起来，为了加快在数据页内高效查询记录，设计了一个页目录，页目录存储各个槽（分组），且主键值是有序的，于是可以通过二分查找法的方式进行检索从而提高效率。下图是一个数据页的存储

以上面那张图举个例子，5 个槽的编号分别为 0，1，2，3，4，我想查找主键为 11 的用户记录：

1）先二分得出槽中间位是 (0+4)/2=2 ，2号槽里最大的记录为 8。因为 11 > 8，所以需要从 2 号槽后继续搜索记录；

2）再使用二分搜索出 2 号和 4 槽的中间位是 (2+4)/2= 3，3 号槽里最大的记录为 12。因为 11 < 12，所以主键为 11 的记录在 3 号槽里；

3）这里有个问题，「槽对应的值都是这个组的主键最大的记录，如何找到组里最小的记录」？比如槽 3 对应最大主键是 12 的记录，那如何找到最小记录 9。解决办法是：通过槽 3 找到槽 2 对应的记录，也就是主键为 8 的记录。主键为 8 的记录的下一条记录就是槽 3 当中主键最小的 9 记录，然后开始向下搜索 2 次，定位到主键为 11 的记录，取出该条记录的信息即为我们想要查找的内容；

6. 索引失效

6.1 对索引使用左或者左右模糊匹配

因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。
反向索引：比如name字段为“梅花”，查找条件 name like '%梅'；我们可以在表里新增一个字段reverse_name为“花梅”，使用 reverse_name like '梅%'; 查找

限制范围：增加范围限制条件，避免全表扫描，比如查找当天，当月等

索引覆盖：select查询的字段尽量在叶子节点上

第三方组件：es等，但涉及到异构同步等问题

6.2 对索引使用函数

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。

不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。例如：对 length(name) 的计算结果建立一个名为 idx_name_length 的索引

alter table t_user add key idx_name_length ((length(name)));

6.3 对索引进行表达式计算

如：where id + 1 = 10;

6.4 对索引隐式类型转换

MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。

6.4.1 字段是字符串，查询条件是数字

phone 字段，是二级索引，类型是 varchar。

select * from t_user where phone = 1300000001;

相当于

select * from t_user where CAST(phone AS signed int) = 1300000001;

CAST 函数是作用在了 phone 字段，而 phone 字段是索引，也就是对索引使用了函数！对索引使用函数是会导致索引失效的。

6.4.2 字段是数字，查询条件是字符串

select * from t_user where id = "1";

相当于

select * from t_user where id = CAST("1" AS signed int);

索引字段并没有用任何函数，CAST 函数是用在了输入参数，因此是可以走索引扫描的。

6.5 联合索引非最左匹配

在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。

也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。

6.6 WHERE 子句中的 OR

因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

7. explain

7.1 type字段

system > const > eq_ref > ref > range > index > all

1）system：MySQL对查询的某部分进行优化并把其转化成一个常量（可以通过show warnings命令查看结果）。const是system的一个特例，表示表里只有一条元组匹配时为const。

2）eq_ref：主键或唯一键索引被连接使用，最多只会返回一条符合条件的记录。简单的select查询不会出现这种type。

3）ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引和某个值比较，会找到多个符合条件的行。

4）range：通常出现在范围查询中，比如in、between、大于、小于等。使用索引来检索给定范围的行。

5）index：扫描全索引拿到结果，一般是扫描某个二级索引，二级索引一般比较少，所以通常比ALL快一点。

6）ALL：全表扫描，扫描聚簇索引的所有叶子节点。

7.2 extra字段(部分)

1）Using filesort：mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。如：建立了col1、col2、col3三个字段的复合索引，排序也是按照索引的顺序。排序时没有按照建立复合索引字段col1、col2、col3的顺序进行，因此产生了外部的索引排序。效率低。

2）Using temporary：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

3）Using index：使用覆盖索引

4）Using where：where筛选条件不是索引列，或where筛选条件是索引列之一但不是前导列

5) Using index condition：查询的列不完全被索引覆盖，where条件中是一个查询的范围。（<、between）

6) Using join buffer：表明使用了连接缓存

8. 问题汇总

8.1 mysql单表不要超过多少行

假设

1）非叶子节点内指向其他页的数量为 x

2）叶子节点内能容纳的数据行数为 y

3）B+树的层数为 z

如下图中所示，Total =x^(z-1) *y 也就是说总数会等于 x 的 z-1 次方与y的乘积：

8.1.1 叶子节点内指向其他页的数量为 x

在本章5.1数据页结构章节介绍了数据页的结构，都会有 File Header (38 byte)、Page Header (56 Byte)、Infimum + Supermum（26 byte）、File Trailer（8byte）, 再加上页目录，大概 1k 左右。我们就当做它就是 1K, 那整个页的大小是 16K, 剩下 15k 用于存数据，在索引页中主要记录的是主键与页号，主键我们假设主页是 Bigint (8 byte), 而页号也是固定的（4Byte）, 那么索引页中的一条数据也就是 12byte。所以 x=15*1024/12≈1280 行。

8.1.2 叶子节点内能容纳的数据行数为 y

叶子节点中存放的是真正的行数据，这个影响的因素就会多很多，比如，字段的类型，字段的数量。每行数据占用空间越大，页中所放的行数量就会越少。

1）这边我们暂时按一条行数据 1k 来算，那一页就能存下 15 条，Y = 15*1024/1000 ≈15。

根据上述的公式，Total =x^(z-1) *y，已知 x=1280，y=15：

假设 B+ 树是两层，那就是 z = 2， Total = （1280 ^1 ）*15 = 19200

假设 B+ 树是三层，那就是 z = 3， Total = （1280 ^2） *15 = 24576000 （约 2.45kw）

2）比如我实际当行的数据占用空间不是 1K , 而是 5K, 那么单个数据页最多只能放下 3 条数据。同样，还是按照 z = 3 的值来计算，那 Total = （1280 ^2） *3 = 4915200 （近 500w）

所以，在保持相同的层级（相似查询性能）的情况下，在行数据大小不同的情况下，其实这个最大建议值也是不同的，而且影响查询性能的还有很多其他因素，比如，数据库版本，服务器配置，sql 的编写等等。

8.2 唯一索引和普通索引的区别以及什么时候使用唯一索引

8.2.1 区别

8.2.2 唯一索引适用场景

1）唯一性要求字段，如：用户邮箱、手机号、身份证号、订单流水号等业务唯一标识字段。

2）替代部分主键约束，当主键是自增ID，但需要其他字段组合唯一时（如多租户系统的 tenant_id + resource_id）。

3）避免重复插入，通过数据库层直接拦截重复数据（比应用层校验更可靠）。

8.3 sql优化

待定

8.4 索引优化例子

待定

四、事务

1.事务有哪些特性？

1）原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成。原子性是通过 undo log（回滚日志） 来保证的；

2）一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。一致性则是通过持久性+原子性+隔离性来保证；

3）隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力。隔离性是通过 MVCC（多版本并发控制）或锁机制来保证的；

4）持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。持久性是通过 redo log （重做日志）来保证的；

2.并行事务会引发什么问题？

在同时处理多个事务的时候，可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题。

1）脏读：如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。(读到其他事务未提交的数据)

2）不可重复读：在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。(前后读取同一数据不一致)

3）幻读：在一个事务内多次查询某个符合查询条件的「记录数量」或「统计值」，如果出现前后两次查询到的记录数量或统计值不一样的情况，就意味着发生了「幻读」现象。（前后读取的记录数量或统计值不一致）

3.事务的隔离级别有哪些？

1）读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；

2）读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；

3）可重复读（repeatable read），（mysql默认）指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；

4）串行化（serializable ），会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

针对不同的隔离级别，并发事务时可能发生的现象也会不同：

4.可重复读是如何工作的？

可重复读隔离级别，它很大程度上避免幻读现象：

1）针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。

2）针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

4.1 MVCC(多版本并发控制)

对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。

「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，

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

4.1.1 Read View

Read View 有四个重要的字段：
1）m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。

2）min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。

3）max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；

4）creator_trx_id ：指的是创建该 Read View 的事务的事务 id。

4.1.2 过程

在第二章节3.2中介绍过数据行除了真是数据列外，还有额外的三个字段。

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

1）如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。

2）如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。

3）如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：

3.1）如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。

3.2）如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

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

4.2 例子

假设事务 A （事务 id 为51）启动后，紧接着事务 B （事务 id 为52）也启动了，那这两个事务创建的 Read View 如下：

事务 A 和事务 B 的 Read View 具体内容如下：

1）在事务 A 的 Read View 中，它的事务 id 是 51，由于它是第一个启动的事务，所以此时活跃事务的事务 id 列表就只有 51，活跃事务的事务 id 列表中最小的事务 id 是事务 A 本身，下一个事务 id 则是 52。

2）在事务 B 的 Read View 中，它的事务 id 是 52，由于事务 A 是活跃的，所以此时活跃事务的事务 id 列表是 51 和 52，活跃的事务 id 中最小的事务 id 是事务 A，下一个事务 id 应该是 53。

接着，在可重复读隔离级别下，事务 A 和事务 B 按顺序执行了以下操作：

1）事务 B 读取小林的账户余额记录，读到余额是 100 万；

2）事务 A 将小林的账户余额记录修改成 200 万，并没有提交事务；

3）事务 B 读取小林的账户余额记录，读到余额还是 100 万；

4）事务 A 提交事务；

5）事务 B 读取小林的账户余额记录，读到余额依然还是 100 万；

接下来，跟大家具体分析下。

1）事务 B 第一次读小林的账户余额记录，在找到记录后，它会先看这条记录的 trx_id，此时发现 trx_id 为 50，比事务 B 的 Read View 中的 min_trx_id 值（51）还小，这意味着修改这条记录的事务早就在事务 B 启动前提交过了，所以该版本的记录对事务 B 可见的，也就是事务 B 可以获取到这条记录。

2）接着，事务 A 通过 update 语句将这条记录修改了（还未提交事务），将小林的余额改成 200 万，这时 MySQL 会记录相应的 undo log，并以链表的方式串联起来，形成版本链，如下图：

你可以在上图的「记录的字段」看到，由于事务 A 修改了该记录，以前的记录就变成旧版本记录了，于是最新记录和旧版本记录通过链表的方式串起来，而且最新记录的 trx_id 是事务 A 的事务 id（trx_id = 51）。

3）然后事务 B 第二次去读取该记录，发现这条记录的 trx_id 值为 51，在事务 B 的 Read View 的 min_trx_id 和 max_trx_id 之间，则需要判断 trx_id 值是否在 m_ids 范围内，判断的结果是在的，那么说明这条记录是被还未提交的事务修改的，这时事务 B 并不会读取这个版本的记录。而是沿着 undo log 链条往下找旧版本的记录，直到找到 trx_id 「小于」事务 B 的 Read View 中的 min_trx_id 值的第一条记录，所以事务 B 能读取到的是 trx_id 为 50 的记录，也就是小林余额是 100 万的这条记录

4）最后，当事物 A 提交事务后，由于隔离级别时「可重复读」，所以事务 B 再次读取记录时，还是基于启动事务时创建的 Read View 来判断当前版本的记录是否可见。所以，即使事物 A 将小林余额修改为 200 万并提交了事务，事务 B 第三次读取记录时，还会按照3）的逻辑读取数据，读到的记录都是小林余额是 100 万的这条记录。

5. 鲜为人知的幻读

MySQL InnoDB默认的可重复读隔离级别加上next-key lock一定程度上解决了幻读问题，但依然存在特殊的情况下产生幻读问题。

1）第一种情况，先启动的事务A使用快照读，后启动的事务B插入新的数据行并提交，然后事务A再更新，其后A的查询能查到事务B新增的数据行。

按MVCC的原理，第⑧处事务A查询结果不应该返回id=5的记录，但因为有update在先，所以该记录查询了出来。快照读不会加锁，导致事务B可以insert成功，而update语句又是当前读，能够更新id=5的数据，所以，当执行⑧时，快照读也就能够查询出来id=5的记录了。

2）第二种情况，如果事务A开始没有使用当前读，当其他事务B插入数据并提交后，事务A再使用当前读就会发生幻读现象。

这种情况是因为快照读不生成next-key lock导致，其他事务可以插入本事务查询范围内的记录行，所以，当其他事务插入数据后再执行当前读（for update），就能查到新的记录，从而产生幻读问题。

五、锁

1. MySQL 有哪些锁？

在 MySQL 里，根据加锁的范围，可以分为全局锁、表级锁和行锁三类

1.1 全局锁

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

1.2 表级锁

1.2.1 表锁

表锁住，无法操作

1.2.2 元数据锁（MDL）

当我们对数据库表进行操作时，会自动给这个表加上 MDL：

对一张表进行 CRUD 操作时，加的是 MDL 读锁；

对一张表做结构变更操作的时候，加的是 MDL 写锁；

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

1.2.3 意向锁

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。所以，意向锁的目的是为了快速判断表里是否有记录被加锁。

1.2.4 AUTO-INC 锁(自增锁)

数据库自增主键就是通过AUTO-INC 锁实现，通过innodb_autoinc_lock_mode设置。

1）当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；

2）当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。

3）当 innodb_autoinc_lock_mode = 1，

3.1）普通 insert 语句，自增锁在申请之后就马上释放；

3.2）类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

1.3 行级锁

行级锁的类型主要有三类：

1）Record Lock：记录锁，也就是仅仅把一条记录锁上；

2）Gap Lock：间隙锁，锁定一个范围，但是不包含记录本身；

间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁，共享（S型）和排他（X型）的间隙锁是没有区别的，他们相互不冲突，且功能相同。

插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作；尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一区间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。所以，插入意向锁和间隙锁之间是冲突的。

3）Next-Key Lock：临建锁，Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

2. MySQL 是怎么加行级锁的？

加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁。

2.1 唯一索引等值查询

1）当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。

2）当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。

2.2 非唯一索引等值查询

1）当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。

2）当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

2.3 非唯一索引和主键索引的范围查询的加锁规则不同之处

1）唯一索引在满足一些条件的时候，索引的 next-key lock 退化为间隙锁或者记录锁。

2）非唯一索引范围查询，索引的 next-key lock 不会退化为间隙锁和记录锁。

3.死锁例子

1）Time 1：此时事务 A 在主键索引（INDEX_NAME : PRIMARY）上加的是间隙锁，锁范围是(20, 30)。

2）Time 2：此时事务 B 在主键索引（INDEX_NAME : PRIMARY）上加的是间隙锁，锁范围是(20, 30)。

3）Time3：事务 A 的状态为等待状态（LOCK_STATUS: WAITING），因为事务 A向事务 B 生成的间隙锁（范围 (20, 30)）中插入了一条记录（会生成插入意向锁，LOCK_MODE:INSERT_INTENTION），而插入意向锁和间隙锁是冲突（参考本章1.2.3意向锁），所以事务A需等待。

4）Time4：事务 B 在生成插入意向锁时而导致被阻塞，这是因为事务 B 向事务 A 生成的范围为 (20, 30) 的间隙锁插入了一条记录（会生成插入意向锁），而插入意向锁和间隙锁是冲突的，所以事务 B 在获取插入意向锁时就陷入了等待状态。

六：日志

1）undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。

2）redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；

3）binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

1. undo log

1.1 什么是undo log

undo log 是一种用于撤销回退的日志。在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚

1.2 undo log作用

1）实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。

2）实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

1.3 undo log 是如何刷盘（持久化到磁盘）的？

undo log 和数据页的刷盘策略是一样的，都需要通过 redo log 保证持久化。 buffer pool 中有 undo 页，对 undo 页的修改也都会记录到 redo log。redo log 会每秒刷盘，提交事务时也会刷盘，数据页和 undo 页都是靠这个机制保证持久化的。

2. redo log

2.1 什么是redo log

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

2.2 redo log作用

1）实现事务的持久性，让 MySQL 有 crash-safe （崩溃恢复）的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；

2）将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

2.3 产生的 redo log 是直接写入磁盘的吗？

不是的。

实际上，执行一个事务的过程中，产生的 redo log 也不是直接写入磁盘的，因为这样会产生大量的 I/O 操作，而且磁盘的运行速度远慢于内存。所以，redo log 也有自己的缓存—— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer，后续在持久化到磁盘如下图：

2.4 redo log 什么时候刷盘？

1）MySQL 正常关闭时；

2）当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；

3）InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。

4）每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这个策略可由 innodb_flush_log_at_trx_commit 参数控制：

4.1）当设置该参数为 0 时，表示每次事务提交时，还是将 redo log 留在 redo log buffer 中，该模式下在事务提交时不会主动触发写入磁盘的操作。

4.2）当设置该参数为 1 时(默认)，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失。

4.3）当设置该参数为 2 时，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，Page Cache 是专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存

2.5 redo log 文件写满了怎么办？

默认情况下， InnoDB 存储引擎有 1 个重做日志文件组( redo log Group），「重做日志文件组」由有 2 个 redo log 文件组成，这两个 redo 日志的文件名叫：ib_logfile0 和 ib_logfile1 。（每个最大1G）

重做日志文件组是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。所以 InnoDB 存储引擎会先写 ib_logfile0 文件，当 ib_logfile0 文件被写满的时候，会切换至 ib_logfile1 文件，当 ib_logfile1 文件也被写满时，会切换回 ib_logfile0 文件。

3.binlog

前面介绍的 undo log 和 redo log 这两个日志都是 Innodb 存储引擎生成的。

MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写入 binlog 文件。

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

3.1.redo log 和 binlog 有什么区别？

1）适用对象不同：

1.1）binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；

1.2）redo log 是 Innodb 存储引擎实现的日志；

2）文件格式不同：

2.1）binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：

2.1.1）STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；

2.1.2）ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；

2.1.3）MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；

2.2）redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；

3）写入方式不同：

3.1）binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。

3.2）redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

4）用途不同：

4.1）binlog 用于备份恢复、主从复制；

4.2）redo log 用于掉电等故障恢复。

3.2 主从复制是怎么实现？

MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。

这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。

3.2.1 MySQL 主从复制还有哪些模型？

3.2.1.1 同步复制

MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：

一是性能很差，因为要复制到所有节点才返回响应；

二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。

3.2.1.2 异步复制（默认模型）

MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。

3.2.1.3 半同步复制

MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。

这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

3.3.binlog 什么时候刷盘？

事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。

3.3.1 binlog cache

MySQL 给每个线程分配了一片内存用于缓冲 binlog ，该内存叫 binlog cache，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。

如果超过了这个参数规定的大小，就要暂存到磁盘。

虽然每个线程有自己 binlog cache，但是最终都写到同一个 binlog 文件：

1）图中的 write，指的就是指把日志写入到 binlog 文件，但是并没有把数据持久化到磁盘，因为数据还缓存在文件系统的 page cache 里，write 的写入速度还是比较快的，因为不涉及磁盘 I/O。

2）图中的 fsync，才是将数据持久化到磁盘的操作，这里就会涉及磁盘 I/O，所以频繁的 fsync 会导致磁盘的 I/O 升高。

3.3.2 sync_binlog

3.2.1.1 sync_binlog = 0 的时候（默认），表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；

3.2.1.2 sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；

3.2.1.3 sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

4. 问题汇总

4.1 一条sql更新语句的执行过程

具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下:

1）执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录： 1.1）如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；

1.2）如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。

2）执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：

2.1）如果一样的话就不进行后续更新流程；

2.2）如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；

3）开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。

4）InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。

为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘，这就是 WAL 技术。MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。

5）至此，一条记录更新完了。

6）在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。

7）事务提交（为了方便说明，这里不说组提交的过程，只说两阶段提交）

7.1）prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；

7.2）commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；

8）至此，一条更新语句执行完成。

七、内存

1.Buffer Pool 缓存什么？

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool(默认128M) 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。

此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，接着将虚拟地址和物理地址建立映射关系。

Innodb 通过三种链表来管理缓页：

1）Free List （空闲页链表），管理空闲页；

2）Flush List （脏页链表），管理脏页；

3）LRU List，管理脏页+干净页，将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。

2.LRU算法

2.1 问题

简单的 LRU 算法并没有被 MySQL 使用，因为简单的 LRU 算法无法避免下面这两个问题

2.1.1 预读失效

MySQL 在加载数据页时，会提前把它相邻的数据页一并加载进来，目的是为了减少磁盘 IO。

但是可能这些被提前加载进来的数据页，并没有被访问，相当于这个预读是白做了，这个就是预读失效。

如果使用简单的 LRU 算法，就会把预读页放到 LRU 链表头部，而当 Buffer Pool空间不够的时候，还需要把末尾的页淘汰掉。

如果这些预读页如果一直不会被访问到，就会出现一个很奇怪的问题，不会被访问的预读页却占用了 LRU 链表前排的位置，而末尾淘汰的页，可能是频繁访问的页，这样就大大降低了缓存命中率。

2.1.2 Buffer Pool 污染

当某一个 SQL 语句扫描了大量的数据时，在 Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了，等这些热数据又被再次访问的时候，由于缓存未命中，就会产生大量的磁盘 IO，MySQL 性能就会急剧下降，这个过程被称为 Buffer Pool 污染。