跳至主要內容

MySQL-小林coding

ErenJaegerKing大约 35 分钟数据库MySQL

基础篇

MySQL 执行一条 select 查询语句，在 MySQL 中期间发生了什么？

连接器：建立连接，管理连接，校验用户身份

查询缓存：8.0已删除

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

执行SQL:有三个阶段

  • 预处理阶段:检查表或字段是否存在；将select* 中的*符号扩展为表上的所有列
  • 优化阶段：选择查询成本最小的执行计划
  • 执行阶段：根据执行计划执行SQL查询语句，从存储引擎读取记录，返回给客户端

MySQL 执行流程是怎样的？

mysql的内部架构分为几层？

server层负责干嘛？

存储引擎负责干嘛？

第一步：连接器

先连接MySQL，然后才能执行SQL语句

连接的过程需要进行三次挥手，因为MySQL是基于TCP协议进行传输的

如何查看MySQL服务被多少个客户端连接了？

show processlist

空闲连接会一直占用吗？

不会，MySQL定义了空闲连接的最大空闲时长，由wait_timeout参数控制，默认值是8个小时（28880秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。

show variables like 'wait_timeout';

我们可以手动断开空闲连接

kill connection +id;

MySQL的连接数有限制吗？

MySQL的最大连接数由max_connections控制，比如mysql的服务默认是151个，超过这个值，系统就会拒绝接下来的连接请求，并报错提示"Too many connections"

MySQL的连接和HTTP一样，有短连接和长连接的概念

// 短连接
连接mysql服务（TCP三次挥手）
执行sql
断开mysql服务（TCP四次挥手）

// 长连接
连接mysql服务（TCP三次挥手）
执行sql
执行sql
执行sql
...
断开mysql服务（TCP四次挥手）

使用长连接的好处就是可以减少建立连接和断开连接的过程

但是长连接会导致占用内存增多，会发生MySQL服务异常重启的现象

怎么解决长连接占用内存的问题？

定期断开长连接

客户端主动重置连接，MySQL5.7实现了mysql_reset_connection()函数的接口，当执行一个很大的操作后，可以在代码里面调用这个函数来重置连接

总结：连接器的工作,与客户端进行TCP三次握手建立连接，校验客户端的用户名和密码，如果对了，就会读取该用户的权限，然后再后面的权限逻辑判断都会基于此时读取到的权限

第二步：查询缓存

很鸡肋的功能，在MySQL8.0开始就不会走查询缓存这个阶段了，也就是server层的查询缓存被移除了

第三步：解析SQL

在正式执行SQL查询语句之前，MySQL会先对SQL语句做解析，这个工作由解析器来完成

第一件事情：词法分析

分析后分为 关键字 和 非关键字

第二件事情：语法分析

语法解析器会根据语法规则，判断你输入的SQL语句是否满足MySQL语法，如果没问题就会构建出SQL语法树（方便后面获取SQL类型、表名、字段名、where条件）

第四步：执行SQL

正式执行SQL语句，进入三个阶段

  1. prepare 预处理阶段
  • 检查SQL查询语句中的表或字段是否存在
  • 将select * 中 * 符号，扩展为表上的所有列
  1. optimize 优化阶段

优化器主要负责将SQL查询语句的执行方案确定下来，比如表里面有多个索引，优化器会基于查询成本的考虑，来决定选择使用哪个索引

explain + 语句命令可以知道选择器选择了哪个索引

  1. execute 执行阶段

执行器会与存储引擎交互，以记录为单位

read_first_record

索引篇

索引常见面试题

什么是索引？

索引是数据的目录

存储引擎，就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现

索引的分类

按数据结构分类

常见的索引B+Tree、HASH索引、Full-Text索引

InnoDB会在不同的创景下面选择不同的列作为聚簇索引：会有三种情况

其他索引都属于辅助索引，也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是B+Tree索引

B+Tree索引在存储数据中的具体实现是怎么样的？

存储在B+Tree索引时是长什么样子的？

B+Tree是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。

B+树存储千万级的数据只需要3-4层高度就可以满足，这意味这从千万级的表查询目标数据最多需要3-4此磁盘I/O，所以B+Tree相比于B树和二叉树来说，最大的优势在于查询效率很高，因为即便在数据量大的情况，查询一个数据的磁盘I/O依然维持在3-4次。

回表：查两个B+Tree才能查到数据

这种在二级索引的B+Tree就能查询到结果的过程就叫做覆盖索引，也就是只需要查一个B+Tree就能找到数据

为什么MySQL InnoDB选择B+Tree作为索引的数据结构

小林coding关于为什么选择做出的回答

  1. B+Tree vs B Tree
  2. B+Tree vs 二叉树
  3. B+Tree vs Hash

按物理存储分类

索引分为 聚簇索引（主键索引）、二级索引（辅助索引）

就算数据存放在哪里的，

按字段特性分类

  1. 主键索引

最多只有一个索引，索引列的值不允许有空值

  1. 唯一索引

索引列的值必须唯一，但是允许有空值

  1. 普通索引

  2. 前缀索引

指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为char、varchar、binart、varbinary的列上

使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

按字段个数分类

单列索引、联合索引

联合索引：通过将多个字段组合成一个索引，该索引就被称为联合索引

使用联合索引时，存在最左匹配原则，如果不遵守最左匹配原则，联合索引会失效

就是（a，b，c）联合索引，是先按a排序，所以b和c是全局无序的，局部相对有序

利用索引的前提是索引里的key是有序的

联合索引范围查询

什么时候需要/不需要创建索引？

索引最大的好处是提高查询速度，但是索引有缺点：3个 物理空间、创建和维护、降低表的增删改效率

什么时候适合索引？2个 唯一 、WHERE GROUP BY/ORDER BY

什么时候不需要创建索引？4个 WHERE GROUP BY/ORDER BY 、 重复字段、 数据少、 频繁进行更新

有什么优化索引的方法？

4种常见的优化方法

  1. 前缀索引优化 减小索引字段大小 局限性
  2. 覆盖索引优化 避免回标 减少了大量I/O操作
  3. 主键索引最好是自增的 好处是什么 什么情况下会出现页分裂
  4. 索引最好设置为NOT NULL 会导致优化器做索引选择的时候更加难count会省略值为NULL的行 会占用至少1字节空间存储NULL值列表
  5. 防止索引失效 小林coding更详细的了解索引失效 执行效率从低到高的顺序为:?

小林coding关于该索引原理的总结

从数据页的角度看B+树

B+树的节点里存放的是什么呢？查询数据的过程又是怎么样的？

InnoDB是如何存储数据的？

InnoDB的数据是按数据页为单位来读写的，数据I/O操作的最小单位是页，InnoDB数据页的默认大小是16KB

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

InnoDB采用了B+树作为索引

B+树的特点 每个节点都是一个数据页

  • 叶子节点存放数据，非叶子节点仅存放目录项作为索引
  • 非叶子节点分为不同层次
  • 按照索引键大小排序，构成双向链表，便于范围查询

聚簇索引和二级索引

区别：聚簇叶子存放数据，二级叶子存放主键值

什么是回表？什么是索引覆盖

  • 如果某个查询语句使用了二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后，需要去聚簇索引中获取数据行，这个过程就叫做回表，也就是说要查两个B+树才能吃到数据
  • 当查询的数据是主键值时，只有二级索引就能查询到，不用聚簇索引查，这个过程就叫做索引覆盖，也就是只需要查询一个B+树就能找到数据

为什么MySQL采用B+树作为索引

因为 MySQL 的数据是存储在磁盘中的嘛，MySQL 的数据是持久化的，数据（索引+记录）是保存到磁盘上的

怎样的索引的数据结构是好的？

内存的访问速度是纳秒级别，磁盘访问的速度是毫秒级别，读取同样大小的数据，磁盘中读取的速度比从内存中读取的速度要慢上万倍，甚至几十万倍

磁盘读写的最小单位是扇区，扇区的大小只有 512B 大小，操作系统一次会读写多个扇区，所以操作系统的最小读写单位是块（Block）。Linux 中的块大小为 4KB，也就是一次磁盘 I/O 操作会直接读写 8 个扇区。

设计一个适合MySQL索引的数据结构，至少满足一下要求:

  • 能在尽可能少的磁盘的 I/O 操作中完成查询工作；
  • 要能高效地查询某一个记录，也要能高效地执行范围查找；

什么是二分查找？

索引数据最好能按顺序排列，这样可以使用「二分查找法」高效定位数据

时间复杂度就降到了O(logn)

什么是二分查找树？

用数组来实现线性排序的数据虽然简单好用，但是插入新元素的时候性能太低。因为插入一个元素，需要将这个元素之后的所有元素后移一位

二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点

当每次插入的元素都是二叉查找树中最大的元素，二叉查找树就会退化成了一条链表，查找数据的时间复杂度变成了 O(n)，会导致什么：树的高度就等于每次查询数据时磁盘 IO 操作的次数

二叉查找树由于存在退化成链表的可能性，会使得查询操作的时间复杂度从 O(logn) 升为 O(n)

什么是自平衡二叉树？

为了解决二叉查找树会在极端情况下退化成链表的问题，后面就有人提出平衡二叉查找树（AVL 树）

主要是在二叉查找树的基础上增加了一些条件约束：每个节点的左子树和右子树的高度差不能超过 1

不管平衡二叉查找树还是红黑树，都会随着插入的元素增多，而导致树的高度变高，这就意味着磁盘 I/O 操作次数多，会影响整体数据查询的效率。

当树的节点越多的时候，并且树的分叉数 M 越大的时候，M 叉树的高度会远小于二叉树的高度

什么是 B 树

为了解决降低树的高度的问题，后面就出来了 B 树，它不再限制一个节点就只能有 2 个子节点，而是允许 M 个子节点 (M>2)，从而降低树的高度

但是 B 树的每个节点都包含数据（索引+记录），而用户的记录数据的大小很有可能远远超过了索引数据，这就需要花费更多的磁盘 I/O 操作次数来读到「有用的索引数据」。

另外，如果使用 B 树来做范围查询的话，需要使用中序遍历，这会涉及多个节点的磁盘 I/O 问题，从而导致整体速度下降。

什么是 B+ 树？

B+ 树与 B 树差异的点:

  • 叶子 非叶子
  • 所有索引都会在叶子节点出现，构成一个有序链表
  • 非叶子节点中又多少个子节点，就有多少个索引
  • 非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）

比较下 B+ 和 B 树的性能区别

1、单点查询

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

2、插入和删除效率

B+ 树的插入和删除效率更高

3、范围查询

B+ 树所有叶子节点间还有一个链表进行连接，这种设计对范围查找非常有帮助

因此，存在大量范围检索的场景，适合使用 B+树，比如数据库。而对于大量的单个索引查询的场景，可以考虑 B 树，比如 nosql 的MongoDB。

MySQL 中的 B+ 树

  • B+ 树的叶子节点之间是用「双向链表」进行连接，这样的好处是既能向右遍历，也能向左遍历。
  • B+ 树点节点内容是数据页，数据页里存放了用户的记录以及各种信息，每个数据页默认大小是 16 KB。

总结

MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构，原因有：

  • 叶子节点 和 非叶子节点
  • 有大量冗余节点
  • 双向链表

MySQL 单表不要超过 2000W 行，靠谱吗？

索引结构不会影响单表最大行数，2000W 也只是推荐值，超过了这个值可能会导致 B + 树层级更高，影响查询性能。

索引失效有哪些？

索引存储结构长什么样？

InnoDB 存储引擎根据索引类型不同，分为聚簇索引（上图就是聚簇索引）和二级索引。它们区别在于，聚簇索引的叶子节点存放的是实际数据，所有完整的用户数据都存放在聚簇索引的叶子节点，而二级索引的叶子节点存放的是主键值，而不是实际数据。

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

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。

为什么 like 关键字左或者左右模糊匹配无法走索引呢？

因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。

对索引使用函数

为什么对索引使用函数，就无法走索引了呢？

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

对索引进行表达式计算

在查询条件中对索引进行表达式计算，也是无法走索引的。

为什么对索引进行表达式计算，就无法走索引了呢？

原因跟对索引使用函数差不多。

对索引隐式类型转换

如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，你会在执行计划的结果发现这条语句会走全表扫描。

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

联合索引非最左匹配

需要遵循最左匹配原则

为什么联合索引不遵循最左匹配原则就会失效？

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

WHERE 子句中的 OR

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

总结

6种

MySQL 使用 like “%x“，索引一定会失效吗？

不一定

如果数据库表中的字段只有主键+二级索引，那么即使使用了左模糊匹配，也不会走全表扫描（type=all），而是走全扫描二级索引树(type=index)。

再说一个相似，我们都知道联合索引要遵循最左匹配才能走索引，但是如果数据库表中的字段都是索引的话，即使查询过程中，没有遵循最左匹配原则，也是走全扫描二级索引树(type=index)

count(*) 和 count(1) 有什么区别？哪个性能最好？

哪种 count 性能最好？

count(*) = count(1) > count(主键字段) > count(字段)

count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。

所以，如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。

再来，就是不要使用 count(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数，建议给这个字段建立一个二级索引。

事务篇

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

事务有哪些特性？

  • 原子性（atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成。undo log
  • 一致性（consistency）：事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态 。通过原子性 + 隔离性 + 持久性来保证
  • 隔离性（isolation）：允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致 mvcc（多版本并发控制）或者锁机制
  • 持久性（durability）：事务结束后，对数据的修改就是永久的，即便系统故障也不会消失 redo log

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

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

脏读

一个事务读到了另一个未提交事务修改过的数据，就意味着发生了脏读现象。

不可重复读

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

幻读

在一个事务内多次查询某个符合查询条件的记录数量，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了幻读现象

事务的隔离级别有哪些？

  • 脏读：读到其他事务未提交的数据；
  • 不可重复读：前后读取的数据不一致；
  • 幻读：前后读取的记录数量不一致。

按严重性排序：脏读 > 不可重复读 > 幻读

有四种隔离级别

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

按隔离水平高低排序：串行化 > 可重复读 > 读已提交 > 读未提交

可重复读的隔离级别下，解决幻读的方案：

  • 快照读（普通select语句），是通过MVCC方式解决了幻读。因为这个事务启动前后所看到的数据是一致的，即便中途有其他事务插入了一条数据，是查询不出来这条数据的，所以很好避免幻读的问题
  • 当前读（select ... for update等语句），是通过next-key lock（记录锁 + 间隙锁）方式解决了幻读

这四种隔离级别具体是如何实现的呢？

  • 「读未提交」,直接读取最新的数据
  • 「串行化」，通过加读写锁的方式来避免并发访问
  • 「读提交」「可重复读」，「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

Read View 在 MVCC 里如何工作的？

Read View 有四个重要的字段：

  • creator_trx_id
  • m_ids
  • min_trx_id
  • max_trx_id

聚簇索引记录中都包含下面两个隐藏列：

  • trx_id:把该事务的事务id记录在trx_id隐藏列里
  • roll_pointer：这个隐藏列是个指针，指向每一个旧版本记录 => undo日志

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

可重复读是如何工作的？

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

读提交是如何工作的？

读提交隔离级别是在每次读取数据时，都会生成一个新的 Read View。

总结

事务的四大特性?

三种并发问题？

四种隔离级别？

InnoDB 可重复读隔离级别下 是怎么解决幻读的？

  • 快照读 => 普通select => MVCC
  • 当前读 => select for update => next-key lock锁

对于读提交和可重复读隔离级别的事务是通过什么来实现的，区别在于什么不同

上面这两个隔离级别是通过什么和什么的比对，来控制并发事务访问同一个记录的行为，这就叫MVCC（多版本并发控制）

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

什么是幻读？

当某一个事务多次查询某个符合查询条件的记录数量，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了幻读现象

快照读是如何避免幻读的？

可重复隔离级别是由MVCC（多版本并发控制）实现的。实现的方式是：开始事务后 => 执行第一个查询语句 => 创建一个Read View => 后续查询条件利用这个Read View,通过这个Read View就可以在undo log版本链找到事务开始时的数据 => 事务过程中每次查询的数据都是一致的

当前读是如何避免幻读的？

MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。

InnoDB引擎为了解决可重复读隔离级别使用当前读而造成的幻读问题，就引出了间隙锁

幻读被完全解决了吗？

可重复读隔离级别下虽然很大程度上避免了幻读，但是还是没有能完全解决幻读。

锁篇

MySQL 有哪些锁？

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

全局锁

全局锁是怎么用的？

命令

全局锁应用场景是什么？

全库逻辑备份

加全局锁又会带来什么缺点呢？

业务停滞

既然备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？

开启事务 MVCC

表级锁

MySQL 表级锁有哪些？具体怎么用的。 4种

表锁

尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

元数据锁（MDL）

MDL读锁 MDL写锁 事务

意向锁

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。

意向锁的目的是为了快速判断表里是否有记录被加锁

AUTO-INC 锁

不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

行级锁

Record Lock

Record Lock 称为记录锁，锁住的是一条记录。

Gap Lock

Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

什么是幻读？

Next-Key Lock

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

MySQL 是怎么加锁的？

什么 SQL 语句会加行级锁？

锁定读语句会加行级锁之外，update 和 delete 操作都会加行级锁，且锁的类型都是独占锁(X型锁)

行级锁有哪些种类？

不同隔离级别下，行级锁的种类是不同的。

读已提交隔离级别 ===> 记录锁 可重复读隔离级别 ===> 记录锁、间隙锁

Record Lock

记录锁

Gap Lock

可重复读隔离级别 ===> 间隙锁 ===> 幻读

Next-Key Lock

临键锁

MySQL 是怎么加行级锁的？

加锁的对象是？

加锁的基本单位是？

那到底是什么场景呢？

唯一索引等值查询

待看

唯一索引范围查询

待看

非唯一索引等值查询

待看

非唯一索引范围查询

待看

没有加索引的查询

待看

update 没加索引会锁全表？

为什么会发生这种的事故？

锁全表带来的隐患

InnoDB存储引擎的默认事务隔离级别是？

幻读是指在同一个事务下，连续执行两次同样的查询语句，第二次的查询语句可能会返回之前不存在的行。

怎么避免幻读现象？ 通过next-key锁来锁住记录本身和记录之间的"间隙"，防止其他事务在这个记录之间插入新的记录，从而避免了幻读现象

update语句中where条件没有使用索引，就会全表扫描，于是对所有记录加上next-key锁，相当于把整个表锁住了

update 不带索引就是全表扫扫描，也就是表里的索引项都加锁，相当于锁了整张表，所以大家误以为加了表锁。

如何避免这种事故的发生？

开启安全更新模式？

总结

当我们要执行 update 语句的时候，确保 where 条件中带上了索引列，并且在测试机确认该语句是否走的是索引扫描，防止因为扫描全表，而对表中的所有记录加上锁。

我们可以打开 MySQL sql_safe_updates 参数，这样可以预防 update 操作时 where 条件没有带上索引列。

如果发现即使在 where 条件中带上了列索引列，优化器走的还是全标扫描，这时我们就要使用 force index([index_name]) 可以告诉优化器使用哪个索引。

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

可以

什么是幻读？

MySQL 文档是怎么定义幻读（Phantom Read） 当同一个查询在不同的时间产生不同的结果集时，事务中就会出现所谓的幻读问题。例如，如果 SELECT 执行了两次，但第二次返回了第一次没有返回的行，则该行是“幻像”行。同一个事务之间。

MySQL是怎么解决幻读的？

InnoDB引擎的默认隔离级别是可重复读，但是它很大程度上避免幻读现象（并不是完全解决了）

  • 快照读（普通select语句），是通过MVCC方式解决了幻读。怎么避免的？就是事务启动前后所看到的数据的是一致的
  • 当前读（select...for update等语句），是通过next-key lock（记录锁 + 间隙锁）方式解决了幻读。就算这个拥有next-key lock锁的时候，其他事务就会在这个插入语句中就会被阻塞

总结

在 MySQL 的可重复读隔离级别下，针对当前读的语句会对索引加记录锁+间隙锁，这样可以避免其他事务执行增、删、改时导致幻读的问题。

有一点要注意的是，在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

MySQL 死锁了，怎么办？

死锁的发生

Innodb 可重复读（RR）

为什么会产生死锁？

Innodb 引擎为了解决「可重复读」隔离级别下的幻读问题，就引出了 next-key 锁，它是记录锁和间隙锁的组合。

  • Record Lock，记录锁，锁的是记录本身；
  • Gap Lock，间隙锁，锁的就是两个值之间的空隙，以防止其他事务在这个空隙间插入新的数据，从而避免幻读现象。

Insert 语句是怎么加行级锁的？

Insert 语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。

什么是隐式锁？ 当事务需要加锁的时，如果这个锁不可能发生冲突，InnoDB会跳过加锁环节，这种机制称为隐式锁。隐式锁是 InnoDB 实现的一种延迟加锁机制，其特点是只有在可能发生冲突时才加锁，从而减少了锁的数量，提高了系统整体性能。

1、记录之间加有间隙锁

待看

2、遇到唯一键冲突

待看

如何避免死锁？

死锁的四个条件：互斥、占有且等待、不可强占用、循环等待

  • 设置事务等待锁的超时时间
  • 开启主动死锁检测

幂等性校验的目的是为了保证不会出现重复的

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

事务 A 和 事务 B 的间隙锁范围都是一样的，为什么不会冲突？

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

两个事务的间隙锁之间是相互兼容的，不会产生冲突。

插入意向锁是什么？ 插入意向锁名字里虽然有意向锁这三个字，但是它并不是意向锁，它属于行级锁，是一种特殊的间隙锁。

为什么会发生死锁？

事务 A 和事务 B 在执行完后 update 语句后都持有范围为(20, 30）的间隙锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁。

总结

两个事务即使生成的间隙锁的范围是一样的，也不会发生冲突，因为间隙锁目的是为了防止其他事务插入数据，因此间隙锁与间隙锁之间是相互兼容的。

在执行插入语句时，如果插入的记录在其他事务持有间隙锁范围内，插入语句就会被阻塞，因为插入语句在碰到间隙锁时，会生成一个插入意向锁，然后插入意向锁和间隙锁之间是互斥的关系。

如果两个事务分别向对方持有的间隙锁范围内插入一条记录，而插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁。

日志篇

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

更新语句流程会涉及三种日志：

  • undo log（回滚日志） 原子性 事务回滚和MVCC
  • redo log（重做日志） 持久性 用于掉电等故障恢复
  • binlog（归档日志） 用于数据备份和主从复制

为什么需要 undo log？

  • 回滚日志，保证事务ACID特性中的原子性（atomicity）

  • 通过ReadView + undo log 实现MVCC（多版本并发控制）

  • 读提交隔离级别在每一个 select都会生成一个新的Read View，也就意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致

  • 可重复读隔离级别是启动事务时生成一个Read View，然后整个事务期间都在用这个Read View，保证事务期间读到的数据都是事务启动前的记录

undo log是如何持久化到磁盘的？

undo log和数据页的刷盘策略是一样的，都需要通过redo log保证持久化

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

为什么需要 Buffer Pool？

Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

  • 读取，有，在Buffer Pool中取
  • 修改，有，在Buffer Pook中直接修改，后续由后台线程选择一个合适的时机将脏页写入磁盘

Buffer Pool 缓存什么？

InnoDB将数据划分未若干页，以页作为磁盘和内存交互的基本单位，一个页的默认大小为16KB

InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。

Undo 页是记录什么？ 开始事务 => 更新记录前，InnoDB层记录相应的undo log / 更新操作的话，需要把被更新的列的旧值记下来 => 生成一条undo log => 会写入Buffer Pool中的Undo页面

查询一条记录，就只需要缓冲一条记录吗？ 不，查询一条记录时，InnoDB将整个页的数据加载到Buffer Pool中，通过页里的页目录去定位到某条具体的记录

为什么需要 redo log ？

WAL（Write-Ahead Logging）技术：MySQL的写操作并不是立刻写到磁盘上，而上先写日志，然后在合适的时间再写到磁盘上

什么是 redo log？

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

事务提交后，redo log就会被持久化到磁盘中，可以不需要将缓存在Buffer Pool里的脏页数据持久化到磁盘

MySQL重启后，可以根据redo log的内容，将所有数据恢复到最新状态

被修改 Undo 页面，需要记录对应 redo log 吗？

需要的。

在内存修改Undo页面后，需要记录对应的redo log

  • 开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log
  • 如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

redo log 和 undo log 区别在哪？

  • redo log记录了此次事务 完成后 的数据状态，记录的是更新之后的值
  • undo log记录了此次事务 开始前 的数据状态，记录的是更新之前的值

事务提交前后所需要的日志

  • 事务提交之前的崩溃，重启后通过undo log回滚事务
  • 事务提交之后的崩溃，重启后会通过redo log恢复事务

crash-safe（崩溃恢复）：redo log + WAL =>保证已提交的记录不会丢失。redo log保证了事务四大特性中的持久性。

redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？

  • redo log使用了追加操作，所以磁盘操作是顺序写
  • 写入数据需要找到写入位置，找到写入位置，然后才写到磁盘，所以磁盘操作是随机写

磁盘的顺序写比随机写高效的多，因此redo log写入磁盘的开销更小

WAL技术的另外一个优点：MySQL的写操作从磁盘随机写变成了顺序写

为什么需要redo log？

  • 实现事务的持久新，让MySQL有crash-safe的能力
  • 将写操作从随机写变成了顺序写，提升MySQL写入磁盘的性能

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

不是，redo log也有自己的缓存redo log buffer

每产生一条redo log时，就会先写入到redo log buffer，然后再持久化到磁盘

redo log 什么时候刷盘？

待看

redo log 文件写满了怎么办？

待看

为什么需要 binlog ？

四个区别

  1. 使用对象不同
  • binlog是MySQL的Server层实现的日志，所有存储引擎都可以使用
  • redo log是InnoDB存储引擎实现的日志
  1. 文件格式不同 binlog
  • STATEMENT（默认格式）：每一条修改的SQL都会被记录到binlog中
  • ROW：记录行数据最终被修改成什么样子了
  • MIXED ：statement + row redolog是物理日志，记录的是在某个数据页做了什么修改，比如对XXX表空间中的YYY数据页ZZZ偏移量的地方做了AAA更新
  1. 写入方式不同
  • binlog是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志
  • redo log是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志
  1. 用途不同
  • binlog 用于备份恢复、主从复制
  • redo log 用于掉电等故障恢复

如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？

上次编辑于:
贡献者: Jaeger Eren
Copyright © 2025 ErenJaegerKing