由浅入深带你快速理解Mysql内部机制！(带面试题补充)

基础

MySQL的执行流程是怎么样子的

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

索引

为什么MySQL使用B+树作为索引？

1. 高效磁盘读写：

• B+树节点大小通常等于磁盘页大小，减少磁盘I/O次数。
• 非叶子节点仅存索引，叶子节点存数据，提高存储密度。

2. 范围查询性能高：

• 叶子节点双向链表****相连，顺序遍历高效，适合范围查询（如BETWEEN语句）。

3. 结构稳定且自平衡：

• B+树自动平衡树高，查找、插入、删除的时间复杂度为 **O(**log **n)**。
• 更新操作简单，维护成本低。

4. 数据有序存储：

• 叶节点按关键字有序排列，便于排序操作。

5. 更适合聚簇索引：

• InnoDB 引擎使用 B+树作为聚簇索引，数据和索引在叶节点一起存放，查找效率更高。

聚簇索引和非聚簇索引有什么区别

聚簇索引：

• 索引叶子节点存储的是数据行，可以直接访问完整数据
• 每个表只能有一个聚簇索引，通常为主键索引

非聚簇索引：

• 索引叶子节点存储的都是数据行的主键和对应的索引列，需通过主键才能访问完整的数据行。

MySQL索引的最左前缀匹配原则是什么？

MSGL 索引的最左前缀匹配原则指的是在使用联合家引时，查询条件必须从索引的最左侧开始匹配。如果一个联合索引包含多个列，查询条件必须包含第一个列的条件，然后是第二个列，以此类推。

底层原理：因为联合索引在B+树中的排列方式遵循”从左到右”的顺序

事务

事务有哪些特性

事务的特性是ACID，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。例如，A向B转账500元，这个操作要么都成功，要么都失败，体现了原子性。转账过程中数据要保持一致，A扣除了500元，B必须增加500元。隔离性体现在A向B转账时，不受其他事务干扰。持久性体现在事务提交后，数据要被持久化存储。

如何保证:

• 持久性：通过redo log(重做日志)
• 原子性：通过undo log(回滚日志)
• 隔离性：通过MVCC(多版本并发控制)

并发事务会导致什么问题？

mysql是允许多个客户端连接的，意味着会出现同时处理多个事务的情况。

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

脏读

一个事务读取到了另一个事务****未提交事务修改过的数据

事务A对某个数据进行了修改，但是事务A并未结束，这时有一个事务B来读取数据，如果这时候事务A发生了回滚，那么事务B读取到的就是过期的数据，这就是脏读。

不可重复读

在一个事务内多次读取一个数据，****前后两次读取到的数据不一致。

两次读取数据的中间有其他事务修改了值并已经提交。

幻读

在一个事务内多次查询某个符合查询条件的记录数量，如果前后两次查询到的数据数量不一样的情况。

事务的隔离级别

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

• 读未提交(read uncommitted)：指一个事务还没提交时，它做的变更就能被其他事务看到
  • 实现：直接读取最新发数据
• 读已提交(read committed)：指一个事务提交之后，它做的变更就能被其他事务看到
• 可重复读(repeated read)(默认)：指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的.
• 串行化(serializable)：会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务完成，才能继续执行。
  • 实现：通过读写锁来避免并行访问

读已提交和可重复读的实现：他们是通过Read View 来实现的，它们的区别在于创建Read View 的时机不同，读已提交是在每个语句执行之前都会重新生成一个 Read View，而可重复读是在启动事务前生成，然后整个事务期间都使用这个 Read View。

MySQL 的 InnoDB的默认隔离级别虽然是可重复读，但是它在很大程度上避免了幻读现象，解决的方法用两种：

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

MVCC（多版本并发控制）

MVCC 是 MySQL 中用于在高并发环境下保持数据一致性的一种机制。它通过版本管理和判断规则，在读已提交（Read Committed）和可重复读（Repeatable Read）隔离级别下生效。

1. MVCC的原理

MVCC 主要通过以下机制来实现数据一致性：

• Undo Log****：
  • 逻辑日志，存储数据修改前的值，结构类似链表，每个事务数据指向上一个事务。
  • 每次修改操作会生成一条 Undo Log，形成多个历史版本。
• 隐藏列： InnoDB 为每条数据维护了三个隐藏列：
  • DB_TRX_ID： 当前这条数据的事务 ID。
  • DB_ROLL_PTR（回滚指针）： 指向上一版本 Undo Log 的指针。
  • DB_ROW_ID： 若无显式主键，InnoDB 自动生成的主键。

1. Read View 机制

在 MVCC 中，Read View 负责判断数据的可见性，不同隔离级别下 Read View 的生成策略不同：

• 读已提交（Read Committed****）： 每个语句执行之前，都会重新生成一个新的 Read View。
• 可重复读（Repeatable Read****）： 事务启动时生成 Read View，整个事务期间保持不变。

Read View 包含以下信息：

• 当前事务 ID： 正在执行的事务。
• 未提交事务 ID 列表 (m_ids)： 当前存在的所有未提交事务。
• 最小事务 ID (min_trx_id)： 活跃事务中最小的 ID。
• 最大事务 ID (max_trx_id)： 系统中已经分配但未提交的最大事务 ID。

1. 数据可见性判断规则

在读取数据时，事务首先获取 Read View，然后依据以下规则判断是否可见：

1. DB_TRX_ID < min_trx_id：
   1. 该数据在 Read View 创建前已提交，事务可见。
2. DB_TRX_ID > max_trx_id：
   1. 该数据在 Read View 创建后提交，事务不可见。
3. min_trx_id ≤ DB_TRX_ID ≤ max_trx_id：
   1. 若 DB_TRX_ID 不在 m_ids 列表中，数据可见。
   2. 若 DB_TRX_ID 在 m_ids 列表中，数据不可见（未提交）。

总结： MVCC 通过维护多个版本和使用 Read View，在高并发下实现数据一致性。其核心在于合理管理数据版本，避免读写冲突。其实在我看来，MVCC可以算是乐观锁的一种实现，它为了提高效率，在读已提交和可重复读隔离级别下，避免使用锁，而是通过维护undolog和ReadView，巧妙的让读写操作互不干扰，让每个事务都能看到专属的数据快照。

锁

MySQL中的锁根据性质可以分为：共享锁和排他锁

• 共享锁（S锁/读锁）
• 排他锁（X锁/写锁）

MySQL中的锁根据粒度大小，分为三种：

• 全局锁：所i顶数据库中所有的表
• 表级锁：
  • 表锁：锁住整张表，粒度较大
  • 元数据锁：防止DML和DDL中途，隐式加的锁。
  • 意向锁：避免加表锁时一行一行的去查看行锁和加锁情况，解决上述抵消而引入的，隐式加的锁。
  • AUTO-INC 锁：
• 行级锁：
  • 行锁(Record Lock)：对单个记录加锁。RC和RR下都支持。
  • 间隙锁(Gap Lock)：锁的是记录间间隙，RR下才有。
  • 临建锁(Next-Key Lock)：锁的是当前记录+记录前的间隙。

锁分类

表级锁

表锁

lock tables t_student read;
lock tables t_student write;
unlock tables；

元数据锁（MDL）

我们不需要显示的加MDL锁，当我们对一张表进行读写操作的时候，会自动的给这张表加上MDL锁

• 当我们对一张表执行CRUD操作时，加的是MDL读锁。
• 对一张表做结构变更操作的时候，加的是MDL写锁。

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

MDL不需要显示调用，那么他是在什么时候释放的？

MDL是在事务提交之后才会释放，意味着在事务执行期间，MDL是一直持有的。

意向锁

在InnoDB中：

• 对表里的某些记录加上共享锁之前，需要先在表级别加上一个意向共享锁
• 独占锁同理

需要注意的是，意向锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会起冲突，实惠和共享表锁(lock tables … read)和独占表锁(lock tables … write)发生冲突。

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

AUTO-INC锁（自增锁）

AUTO-INCC是InnoDB中特殊的一种表级锁，同于处理带有AUTO+INCREMENT 列的操作。主要是为了保证在并发情况下，自增列的值能够正确、连续的分配。

如果一个事务正在插入数据到有自增列的表时，会先获取自增锁，拿不到就可能会被阻塞住。这里的阻塞行为只是自增锁行为的其中一种，可以理解为自增锁就是一个接口，其具体的实现有多种。

innodb_autoinc_lock_mode	介绍
0	传统模式
1	连续模式（MySQL 8.0 之前默认）
2	交错模式(MySQL 8.0 之后默认)

行级锁

普通的select语句是不会对记录加锁的，因为它是属于快照都。如果要在查询时对记录加锁，可以使用下面的这两个方式，这种拆线呢会加锁的方式称为锁定读

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁
select ... for update;

上面这两条语句必须在一个事务中，因为事务提交了，锁就会释放，所以在使用这两条语句的时候，要加上begin 墩号start transaction 或者 set autocommit = 0。

Record Lock

行锁也分为S锁和X锁。

Gap Lock

Gap Lock 称为间隙锁，只存在于可重复读级别，目的是为了解决可重复读隔离级别下的幻读现象。仅防插入****：其他事务不能在这个范围内插入新记录，但可以修改已有的记录

假设有一个范围id为(3,5)间隙锁，你妹其他事务就无法插入id=4 的这条事务了，这样就能有效防止幻读现象的发生。

间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的锁，并不存在互斥关系，因为间隙的无敌是防止插入幻影记录而提出的。

Next-Key Lock

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

假如表中有一个范围id 为(3,5]的next-key lock ，你妹其他事务即不能插入id=4的记录，也不能修改id = 5的这条记录。

那么这里id=4的记录上的是Gap Lock，id =5 的记录上的是 Record Lock锁

日志

MySQL中的日志有:

• undo log (回滚日志)：是Innodb存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和****MVCC。
• redo log (重做日志)：是Innodb存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等事务故障回复。
• binlog (归档日志) ：是server层生成的日志，主要用于数据备份和主从复制。

binlog（Sever层）

作用：

• 数据库宕机后的恢复
• mysql实现主从复制的原理。（从节点基于主节点的binlog变更达到数据同步）
• 缓存一致性问题解决方案中（canal组件监听binlog实现的解决方案）

知识点

• binlog的几种格式？
  • statement
  • sql
  • mixed

redo log（引擎层，InnoDB独有）

作用

• 数据库的宕机恢复
• mysql的bufferpool中脏页刷新到磁盘出错时，需要依赖redo log恢复

Buffer Pool提高了读写效率，但是buffer 是基于内存的，所以在断电时还没有落盘的脏页数据就会丢失，所有当有一条记录需要更新的时候，InnoDB就会先更新内存，标记为脏页的同时将本次的修改已 redo log 的形式记录下来。

之后InnoDB会在适当的时候，由后台线程将缓存在 Buffer Pool的脏页数据刷新到磁盘里，这就是 WAL (Write-Ahead Logging) 技术。

WAL：MySQL的写操作并不是立刻写在磁盘上，而是先写日志，然后再合适的时间再写到磁盘上。

主要保证的是持久性

• 实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失;
• 将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

undo log（引擎层，InnoDB独有）

作用

• MVCC时的undo log版本链
• 保证事务的原子性，回滚时基于undo log回滚。

Buffer pool

缓存池，为的是提高数据库的读写性能。

两阶段提交

事务提交之后，redo log 和 binlog 都需要持久化到磁盘，但是这是两个独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致，造成主从环境的数据不一致。

所以MySQL为了避免出现两份日志之间逻辑不一致的问题，使用了两阶段提交来解决。两阶段提交其实是分布式事务与i执行协议，它可以保证多个逻辑操作的原子性。

两阶段提交把单个事务的提交拆分为了2个阶段，分别是准备和提交，每个阶段都有协调者和参与者共同完成。

准备阶段：MySQL 会将事务操作记录到redolog中，并标记为 prepare 状态

提交阶段：事务提交的时候，MySQL会将事务操作记录到binlog中。然后把redolog中的日志设置为提交状态.

面试补充

MySQL 中的排序是如何实现的

• 如果排序命中索引：使用索引排序（效率高，因为索引有序）
• 如果没有命中索引：使用文件排序

文件排序中，如果数据量小则在内存中排序，具体是使用单路排序或者双路排序。

如果数据量大则利用磁盘文件完成进行外部排序

内存排序

当MySQL执行 ORDER BY 或者 GROUP BY 查询时，如果无法使用索引来进行排序时，每个排序线程都会分配一个用于排序的内存缓存区 sort_buffer，用来存放缓存数据。

双路排序：

如果select的列的长度超过了 max_length_sort_data参数，默认是4096字节，MySQL为了节省排序占用的空间，sort_buffur 中只会存放主键/行号和排序字段（也就是order by 之后的字段），完成排序后还需要有次回表的操作来返回数据。

单路排序：

相比双路排序就是将所有select的字段都放置到了sort_buffer中，减少了一次回表的操作。

MySQL中的存储引擎有哪些，它们有什么区别?

InnoDB

是MySQL的默认引擎，以高可靠性和事务支持著称。

提供高并发性能，适用于高负载的OLTP应用

• 事务支持： 提供 ACID 特性（原子性、一致性、隔离性、持久性）。
• 崩溃恢复： 通过重做日志（Redo Log****）和回滚日志（Undo Log），在崩溃后能自动恢复。
• 外键****支持： 保证数据完整性和一致性。
• 行级锁： 并发性能较好，避免表级锁的争用。
• 聚簇索引****： 主键和数据存储在一起，二级索引指向主键。
• MVCC（多版本并发控制****）： 提高读写并发性。

MyISAM

专注于读性能

• 不支持事务： 数据操作不能回滚。
• 不支持外键： 表间关联性差。
• 表级锁： 更新操作会锁住整个表，写入时并发性能差。
• 压缩表： 可以节省存储空间。

MySQL 中的隔离级别有哪些？

• 读未提交：事务可以读到其他事务未提交的数据
• 读以提交：事务只能读取其他已提交事务的数据。
• 可重复读：事务在整个过程中，读取的结果一致。
• 串行化：最高级别的隔离，事务串行执行，完全避免脏读、不可重复读、幻读。

可重复读是如何实现的？

在 InnoDB 中，可重复读（REPEATABLE READ****） 通过 MVCC（多版本并发控制****） 来避免脏读和不可重复读，并且通过 Next-Key Lock 机制在当前读情况下，避免一定程度上的幻读。

• 不可重复读：指在同一事务中，多次读取同一行数据，结果却不一致。
• 幻读：指在同一事务中，前后两次读取的结果集数量不一致。

先说明一下MVCC：

在可重复读的情况下，事务一开始的时候会创建一个 Read View，并且在创建之后保持不变，在Read View中存储了当前的事务id，所有未提交的事务id，和未开始的事务id。InnoDB会根据这三个变量和判断规则去undo log中寻找对应能看到的数据快照，从而避免了脏读、可重复读和幻读。

再说一下临键锁 Next-Key Lock，通过对已存在的数据加记录锁和对记录之间加间隙锁从而避免了在当前读情况下的幻读。

什么是聚簇索引？

在 MySQL 中，聚簇索引是一种将数据存储和索引组织在一起的索引类型，主要特性是数据和索引共存。

就是主键索引

间隙锁的工作原理是什么？

间隙锁是专门在可重复读的隔离机制下防止幻读的一种锁机制。

它锁的是两个索引之间的间隙，而不是具体的记录。

MySQL 的 undo log 和redo log 是做什么用的？有什么区别？

undo log

作用：

• 实现事务回滚，保障事务的原子性
• 实现MVCC（配合 ReadView ）

Redo log

作用：

• 实现事务的持久性，让MySQL有 crash-saafe 的能力，能够保证MySQL在任何时候突然崩溃，重启后之前已提交的记录都不会丢失
• 将读写操作从随机写变成了顺序写，提高MySQL的磁盘写入功能

区别：

• redo log 记录了此次事务修改后的数据状态，记录的是更新之后的值，主要用于事务崩溃恢复，保证事务的持久性。
• undo log 记录了此次事务修改前的数据状态，记录的是更新之前的值，主要用于事务回滚，保证事务的原子性。

binlog是做什么用的？

binlog 是记录所偶遇对数据库执行更改操作的日志，以二进制你格式保存在磁盘上。

作用：

1. 数据恢复（回复到某一时刻）
2. 主从复制（主库将binlog复制给从库，从库按照binlog重放操作，实现主从一致）

在索引使用的时候，知道哪些索引使用时的一些优化方法，以及说在哪些情况下索引会失效？

索引的优化技巧

• 选择性高的列键索引：值越分散，索引效果越好
• 使用覆盖索引：避免回表，提高查询效率
• 使用复合索引时遵循最左前缀原则：
• 合理运用范围查询(因为最左前缀原则)；
• 合理利用排序（ORDE BY），避免排序操作

  • -- index(age, name)
    SELECT * FROM user WHERE age = 30 ORDER BY name; ✅ 命中索引
    

索引失效

• 对所以使用左或者左右模糊匹配

  • 也就是说like %xx 或者 like%xx%这两种方式都会造成索引失效

  • 因为 索引 B+树是按照索引值有序排列的，智能进行前缀比较。

• 使用了函数或表达式
• 类型不一致，隐式转换导致失效

• OR 条件未全部使用索引字段