MySQL面试题

基础篇

InnoDB和MyISAM有什么区别？

InnoDB 支持事务、外键和行锁

MyISAM 不支持事务、外键和行锁，仅支持表锁;

InnoDB 不仅缓存索引还缓存真实的数据，

MyISAM 仅缓存索引，不缓存真实数据;

InnoDB 中有聚簇索引和非聚簇索引，

MyISAM 中仅存在非聚簇索引;

InnoDB 的索引和数据存储在同一个文件中

MyISAM 的索引和数据是分开存储的

什么是数据库范式，为什么要反范式？

数据库范式是设计数据库表结构的一个规则，为了减少数据冗余，提高数据库的性能。有时为了性能考虑可以进行反范式设计， 将经常一起查询的数据放到一个表中，或者在需要频繁查询的字段上创建冗余存储，能够减少联表查询的操作来提高查询速度。例如，一个电商的项目，查看订单详情，按照三范式的规则，订单详情需要关联用户信息表，再关联商品表，这种联表查询效率并不高，这样可以在订单表里加上所需要的字段，来避免联表查询。

第一范式：表中的字段都是不可再分的数据

第二范式：非主键字段要依赖主键字段（满足一的基础上）

第三范式：非主键字段和主键之间是直接依赖，不存在传递依赖（满足二的基础上）

使用JSON数据类型存储数据，就属于反范式设计。

说一下事务的隔离级别有哪些？ 它们分别解决了什么问题？

• 读未提交（RU）：无法解决任何并发问题，能够读取到其他事务未提交的数据。
• 读已提交（RC）：能够解决脏读问题，只能够读取到已提交事务的数据。
• 可重复读（RR）：能够解决读取不一致（不可重复读）的问题，并且通过mvcc能够解决快照读语义下的幻读问题。
• 序列化（SE）：能够防止所有并发问题，通过加读写锁的方式 确保在事务完成之前，其他事务无法修改这些数据。但是管理等待的事务需要消耗额外的资源会增加服务器负担。

什么是脏读、脏写、幻读、不可重复读？

脏读：一个事务内读取到了其他事务里还没提交的数据。

脏写（任何隔离级别都不允许发生）：一个事务内覆盖掉了其他事务内未提交的数据。事务B修改了事务A还未提交的数据，导致的数据不一致。

幻读：一个事务内多次以相同条件查询时，由于其他事务的插入或删除操作造成查询的记录数不一样。

不可重复读：同一个事务内，两次读取同一行数据时，由于其他事务的更新或删除操作，导致出现的结果不一样。

脏写和丢失更新的区别：

脏写：一个事务在对数据进行修改后，但尚未提交时，另一个事务也对同一数据进行了修改。

丢失更新：两事务T1T2同时读取某一数据并修改时，在T1的结果提交后，T2提交了修改结果，使得T1的修改无效。

MVCC

什么是MVCC ？为什么要有MVCC 解决什么问题？

MVCC是多版本并发控制， 用来管理并发操作的数据库机制。通过维护数据的多个版本来允许多个事务同时进行读写操作，从而提高并发性能并减少锁的竞争。 能够解决快照读语义下的幻读问题。

什么是Read View？什么是Undolog？ Read View存储哪些数据代表什么含义？ Undolog日志的意义是什么？

Read View：事务开始时产生的数据快照，这个快照包括该事务开始前已提交的数据版本，不同隔离级别下产生的数据快照的时机不同。RC在每条语句执行前都生成一个Read View，RR在事务启动时生成一个Read View然后整个事务期间使用这个Read View。

Undo log（回滚日志）：当执行一条写入类型的SQL（增删改）就会记录在undo log日志中，事务操作失败需要回滚，根据undo log中记录的版本进行逆向操作让事务回到最开始的状态。

Read View 包含4个重要的字段：

• • m_ids：当前活跃事务的id列表，确定创建read view时未提交的事务
  • min_trx_id：活跃事务中最小的事务id，确定数据版本生成的最早时间点
  • max_trx_id：下一个事务的id，
  • creator_trx_id：创建该事务的id

创建事务的id小于Read view的最小事务id，该版本是事务开始前提交的，所以可见

创建事务的id大于Read view的最大事务id，该本版本是当前事务开始后产生的，不可见

创建的事务id在Read view的最小事务id和最大事务id之间，判断该事务id在不在活跃事务id中。

• • 在：该事务在当前事务开始时未提交所以不可见。
  • 不在：该事务已经提交，所以可见。

Undolog 什么时候生成新数据？

每条写入型的SQL执行时都会记录在Undolog中。

• 插入操作：如果插入之前这条数据都不存在，就不会产生Undo记录，因为没有旧数据需要保存用来进行回滚。
• 更新操作：更新时会生成新的Undo log来记录旧值。
• 删除操作：删除时生成新的Undo log来记录被删除行的完整内容。

readview 什么时候产生什么时候消失？他的生命周期和不同事务隔离级别有关系吗？

在不同隔离级别下read view产生和消失的时间不同。read view的生命周和不同事务的隔离级别有密切的关系。

在RC隔离级别下，每次查询时都会生成一个Read View，当查询结束read view也随着消失。

在RR隔离级别下，事务执行第一个快照读操作后会生成一个read view，当事务结束后（提交或回滚）时消失。

mysql是如何解决脏读问题和幻读问题的？

脏读指的是一个事务读取到了另一个未提交事务的修改，从而造成的数据不一致。可以通过设置事务隔离级别来避免幻读。RC、RR、SE隔离级别都能避免幻读问题。

幻读是在一个事务内，第一次查询后因为其他事务进行插入或删除操作，导致了两次查询返回了不同的数据集。通过SE和RR隔离级别来避免幻读的问题，本质上是通过MVCC和锁的机制解决的。

快照读：因为在事务中执行了查询操作后就会产生快照（read view），这个事务期间使用的数据都是这个read view，所以避免了幻读。

当前读：进行当前读操作，每次都会产生一个read view，查询的都是最新数据从而产生幻读问题。MySQL会在进行当前读时，加上next-key lock，这时其他事务在锁的范围内进行插入或删除操作就会被阻塞，从而避免幻读问题。（RR事务结束锁释放，RC读完就释放）

SE隔离级别：本质也是通过锁的机制来避免幻读，该隔离级别下事务执行时会对所有涉及到的数据加锁，其他事务无法进行插入或删除操作，从而避免幻读。

什么是快照读 什么是当前读？分别解释概念和举出对应sql例子？

快照读：读取数据时不加锁，通过MVCC来读取数据的快照，每次读取的数据是事务开始时的一个快照，不是当前最新的数据版本。允许多个事务并发读取相同的数据时不会相互阻塞。

SELECT*FROM account WHERE balance >100;

当前读：读取数据时需要加锁，读取到的是数据的最新版本，通常用于更新、删除和插入操作，确保读取到的数据是最新的并且没有被其他事务修改。

SELECT*FROM account WHERE id =1FORUPDATE;

请详细解释一下mvcc根据read view和undolog匹配数据的流程？

undo log中的trx_id（创建该数据版本的事务ID）和read view中的current_trx_id（创建该事务的ID）相等，说明该数据版本就是当前事务自身创建的，所以可读。

trx_id小于min_ids：数据版本由当前事务开始前就已经提交的事务创建的所以可读。

trx_id大于max_ids：数据版本在当前事务开始后创建的，所以不可读

trx_id在min_ids和max_ids之间，需要判断trx_id 是否在活跃事务列表中

• 不在列表中：事务还未提交不可读
• 在列表中：事务已经提交，可读