MySQL面经整理

一、MySQL存储引擎

1.1）MySQL支持哪些存储引擎？默认使用哪一个？

MySQL采用插件式存储结构，不同的存储引擎负责数据的存储和读取方式，因此他们在事务支持、锁机制、性能等方面不同。
通过show engines可以查看当前数据库支持的存储引擎。

Mysql5.5之后默认存储引擎就是 InnoDB。

1.2）MyISAM和InnoDB的区别

首先，InnoDB支持事务，能够保证数据一致性，MyISAM不支持事务。
其次，InnoDB支持行级锁和MVCC，而MyISAM只支持表级锁，因此在高并发写入场景下，InnoDB的性能更好。
第三，InnoDB支持外键约束，可以维护表之间的数据完成性，而MyISAM不支持外键。
第四，InnoDB 具有崩溃数据恢复能力，即使数据库异常宕机，也可以通过Redo Log恢复数据。而MyISAM不支持。
此外，InnoDB使用聚簇索引，数据存储在主键索引叶子节点中；MyISAM使用非聚簇索引，索引和数据分离存储。

二、MySQL 事务

2.1）事务的四大特性（ACID）

MySQL事务具有ACID四大特性

1. 原子性：事务中的操作是一个不可分割的整体，要么全部成功，要么全部失败，通过Undo Log实现。
2. 一致性：事务执行前后数据库始终保持合法状态。
3. 隔离性： 多个事务并发执行时互不干扰，主要通过锁机制和MVCC实现。
4. 持久性：事务提交后的数据永久保存，即使数据库宕机也不会丢失，通过Redo Log 实现。

2.2）事务并发带来哪些问题？不可重复读和幻读的区别？

事务并发执行时可能产生三类问题：

1. 脏读：读取到其他事务未提交的数据
2. 不可重复读：同一事务多次读取同一条记录，结果不一致
3. 幻读：同一事务中多次查询符合条件的记录，记录数量发生变化。
   MySQL通过事务隔离级别来解决这些问题。InnoDB默认采用RR(Repeatable Read，可重复读)隔离级别，结合MVCC和Next-key Lock 机制解决大部分并发一致性问题。

幻读是查询结果集的行数发生变化，不可重复读是同一行数据值被修改。

脏读

一个事务读取到了另一个事务未提交的数据，如果对方回滚，那么读取到的数据就是无效的。
比如：事务A修改了id = 1的一条数据，但是并未提交事务。此时，事务B读取的id =1 的这条数据，之后事务A回滚了，修改的数据从未真正存在过，却被事务B读取到了。

不可重复读

同一事务中，两次读取同一条数据，由于其他事务提交了更新操作，导致两次读取结果不同。
比如 ： 事务A开启第一次读取id =1的数据，此时事务B开启，修改了id = 1的这条数据，并提交事务。事务A再次读取id = 1的数据，两次结果不一样。

幻读

同一个事务中，两次按照相同条件查询数据，由于其他事务插入或删除了满足条件的记录，导致查询结果集的数量发生变化。
比如：
一张用户表

idage120221

事务A开启事务，查询age > 18的所有数据，结果有2条。
接着，事务B开启事务，插入一条数据（3，22）并提交事务。
此时，事务A再次查询 age > 18 的所有数据，结果有3条。

2.3）MySQL 事务隔离级别？默认是什么级别？

MySQL事务隔离级别有四种：读未提交（Read Uncommitted），读已提交（Read Committed）, 可重复读（Repeatable Read)，串行化（Serializable）。

RU最低的隔离级别，会产生脏读；
RC，解决了脏读，但是会出现不可重复读和幻读问题；
RR，解决了脏读和不可重读读的问题，MySQL InnoDB默认的隔离级别；
Serializable，隔离级别最高，但是性能最差；

另外需要注意，按照 SQL 标准，可重复读仍可能出现幻读，但 MySQL InnoDB 通过 MVCC 和 Next-Key Lock 机制，在大多数场景下避免了幻读。这样兼顾了数据一致性和并发性能。

RU

最低的隔离级别。一个事务可以读取到其他事务尚未提交的数据（脏读）。

RC

只能读取已经提交的的数据。解决了脏读问题。

RR

MySQL InnoDB默认的隔离级别。
保证同一事务内多次读取结果一致。
解决了脏读、不可重复读的问题。
SQL标准下，可重复读不能完全解决幻读；但MySQL InnoDB通过MVCC和临键锁机制，在很大程度上避免了幻读问题。

串行化

最高的隔离级别。所有事务排队执行。解决了脏读、不可重复读、幻读问题，但是性能最差。

2.4）MySQL的隔离级别是基于锁实现的么？

不完全是。InnoDB 的事务隔离级别主要通过 MVCC 和锁机制共同实现。
Read Committed 和 Repeatable Read 的一致性读主要依赖 MVCC，通过 Read View 读取数据快照；而为了解决幻读问题，Repeatable Read 在当前读场景下会结合 Next-Key Lock。最高级别 Serializable 则主要依赖锁机制实现事务串行执行。因此不能简单地说 MySQL 的隔离级别完全基于锁实现。

2.5） InnoDB对MVCC的具体实现

InnoDB聚簇索引记录中的隐藏列

1. trx_id 最后修改该记录的事务ID
2. roll_pointer 指向undo log的指针
3. row_id 隐藏主键（没有主键时生成）

Undo Log 保存历史版本

举个例子：
初始数据id = 1 name='jack; trx_id = 1，
事务2update user set name = 'tom' where id = 1
更新后，当前数据id = 1 , name = 'tom' trx_id = 2 roll_pointer --> undo1,undo1name ='jack' trx_id = 1
如果之后事务3再修改update user set name='Lucy' where id=1;,
当前数据name='Lucy' trx_id=3
undo log :undo2 → name='Jack' trx_id=101 ↓ undo1 → name='Tom' trx_id=100

Read View(一致性视图)

数据存在多个版本，但是当前事务应该看到哪个版本，Read View来解决这个问题。

其中Read View中比较重要的几个字段

1. m_ids :创建Read View 时，当前系统中活跃事务ID的集合。
2. min_trx_id:活跃事务中最小事务的ID。
3. max_trx_id:下一个将要分配的事务ID。
4. creator_id：创建Read View的事务ID。

判断规则

假设某个版本的trx_id =x

1. x < min_trx_id ,说明事务在生成Read View前已经提交。该版本可见
2. x >= max_trx_id ,

不同隔离级别下Read View的生成时机

• RC ：每一次执行select语句都会创建Read View
• RR ： 第一次快照读生成Read View，后续都复用这一份Read View

x

InnoDB 的 MVCC 是通过隐藏字段、Undo Log 和 Read View 实现的。每条记录都会保存一个 trx_id 和指向 undo log 的 roll_pointer。数据更新时不会覆盖旧数据，而是把旧版本保存到 undo log 中，通过 roll_pointer 形成版本链。

当执行快照读时，会生成一个 Read View，里面记录当前活跃事务集合 m_ids、最小事务 ID 和最大事务 ID。查询时根据记录版本的 trx_id 和 Read View 的可见性规则判断该版本是否可见，如果不可见，就沿着 undo log 继续查找旧版本，直到找到满足条件的版本。

在 Read Committed 隔离级别下，每次查询都会生成新的 Read View；在 Repeatable Read 下，只在第一次快照读时生成 Read View，因此能够保证可重复读。普通 select 属于快照读，会使用 MVCC；而 select for update、update、delete 等当前读则通过加锁机制保证并发安全。

三、MySQL索引

3.1）为什么索引能够提高查询效率

MySQL索引的本质是一种帮助数据库快速定位数据的数据结构。通过减少磁盘IO和需要扫描的数量量提高查询速率。

没有索引时，MySQL只能从第一条开始逐条查询，这种方式成为全表扫描。

建立索引后，MySQL会通过B+树结构查找。

MySQL 索引能够提升查询效率，本质上是因为它利用 B+ 树这种数据结构建立了键值和数据之间的映射关系，使数据库不需要进行全表扫描，而是能够快速定位到目标记录。由于 B+ 树是多叉平衡树，树高很低，百万甚至亿级数据通常只需要 3~4 次磁盘 I/O 就能完成查找，从而将查询复杂度由 O(n) 降低到 O(log n)。同时 B+ 树叶子节点有序并通过链表连接，因此还能高效支持范围查询和排序操作。

为什么选择B+树，优势在哪里？

3.1.1）红黑树

红黑树是一种二叉平衡树，每一个节点最多只有两个子节点。而B+树是多叉树，每一个节点可以存储上千个key。同等数据量下，红黑树的树高要远高于B+树的树高。

3.1.2） Hash

Hash索引通过哈希函数定位，等值查询很快O(1),但是不支持范围查询、排序。

3.1.3） B-树

B-树和B+树的最大区别： B-树非叶子节点存储key 和data.B+树的非叶子节点只存储Key，不存储data，所有数据都存储在叶子节点中，叶子节点通过双向链表连接。

存储效率：因为B+树的非叶子节点不存储数据，所以数据量相同的情况下，B+树的每一个节点可以存储更多的Key，比B-树更”矮胖“。

查询效率：

• 在单点查询中，B+树所有元素查询效率一致，必须查询到叶子节点。而B-树只要找到匹配元素即可。
• 在范围查询中，B-树需要通过复杂的中序遍历来确定左右边界的位置。因为B+树叶子节点及建立了双向链表，只需要找到左边界的位置，而后遍历链表即可。

因此，B+树的相较于B-树的优势：IO次数更少，查询效率更稳定，范围查询效率更高。

3.2）聚簇索引和非聚簇索引的区别？非聚簇索引一定回表查询么？

首先从定义入手，什么是聚簇索引，什么是非聚簇索引？

聚簇索引：索引和数据放在一起，叶子节点存储完整的数据，一张表只能由一个聚簇索引。
非聚簇索引： 索引和数据分开，叶子节点保存的是索引列值+主键值，一张表可以由多个非聚簇索引。

查询过程：

因为聚簇索引叶子节点保存的完整的数据，所以当找到叶子节点后即可返回。
而非聚簇索引得到的是主键值，则需要再次根据主键值通过聚簇索引查询数据，这个过程就是回表.

也有例外！覆盖索引。
当索引列就是要查询的列时，则不需要进行回表操作。

在MySQL InnoDB中，主键默认就是聚簇索引，而普通索引、唯一索引等都是非聚簇索引。

聚簇索引和非聚簇索引最大的区别在于叶子节点存储的内容不同。聚簇索引的叶子节点直接保存完整的数据行，因此索引和数据存储在一起，一张表只能有一个聚簇索引；而非聚簇索引的叶子节点保存的是索引列值和主键值，查询时通常需要先通过非聚簇索引找到主键，再根据主键到聚簇索引中查找完整数据，这个过程称为回表。InnoDB 中主键索引就是聚簇索引，普通索引、唯一索引等都属于非聚簇索引。如果查询的字段都包含在非聚簇索引中，则可以利用覆盖索引避免回表，提高查询效率。

3.2.1 ）为什么非聚簇索引存储主键值而不是数据地址?

1. 避免数据移动导致大量索引失效
   InnoDB的数据存储在聚簇索引中，数据的改动可能会导致数据存储的物理地址发生改变。如果存储物理地址，改动一条数据且引发物理地址改变，则所有个这条数据有关的二级索引都要 修改。
2. 保证事务和MVCC的一致性。
3. 统一访问方式。

3.3）最左匹配法则

定义：联合索引在使用的时，会从最左侧的字段开始匹配，只有连续匹配才能利用索引，中间不能跳过某一列。

失效场景：

1. 对索引列进行计算、函数操作
2. 隐士类型转换
3. 不符合最左匹配法则
4. 范围查询后面的列失效
5. like “%XXX”

怎么查看某条SQL语句是否使用了索引

可以通过 EXPLAIN 查看 SQL 的执行计划，判断 SQL 是否使用了索引。重点关注 key、type、rows 和 Extra 等字段。如果 key 不为空，说明实际使用了索引；如果 type 为 ALL，通常表示进行了全表扫描，没有利用索引。

key：实际使用索引
type:

• ALL : 全表扫描
• index : 全索引扫描
• range : 范围查询
• ref ： 等值查询
• eq_ref ： 唯一索引等值匹配
  possible_keys ： 优化器认为可能使用的索引