Mysql面试整理大全

灰太狼 2023-06-06 10:35 57阅读 0赞

**1>事务的四大特性和事务并发引起的问题**  
事务的四大特性，即通常说的ACID属性，原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）

原子性：事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做。
    
    一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。
    
    隔离性：一个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
    
    持续性：也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操或故障不应该对其执行结果有任何影响。

在不考虑事务隔离级别的时候，事务并发会引发的问题有

第一类丢失更新
    撤销一个事务的时候，把其它事务已提交的更新数据覆盖了。这是完全没有事务隔离级别造成的。如果事务1被提交，另一个事务被撤销，那么会连同事务1所做的更新也被撤销。
    
    脏读（Dirty Read）
    如果一个事务对数据进行了更新，但事务还没有提交，另一个事务就可以“看到”该事务没有提交的更新结果。这样就造成的问题就是，如果第一个事务回滚，那么第二个事务在此之前所“看到”的数据就是一笔脏数据。
    
    不可重复读取（Non-Repeatable Read）
    不可重复读取是指同一个事务在整个事务过程中对同一笔数据进行读取，每次读取结果都不同。如果事务1在事务2的更新操作之前读取一次数据，在事务2的更新操作之后再读取同一笔数据一次，两次结果是不同的。所以Read Uncommitted也无法避免不可重复读取的问题。
    
    第二类丢失更新
    它和不可重复读本质上是同一类并发问题，通常将它看成不可重复读的特例。当两个或多个事务查询相同的记录，然后各自基于查询的结果更新记录时会造成第二类丢失更新问题。每个事务不知道其它事务的存在，最后一个事务对记录所做的更改将覆盖其它事务之前对该记录所做的更改。
    
    幻读（Phanton Read）
    幻读是指同样一个查询在整个事务过程中多次执行后，查询所得的结果集是不一样的。幻读针对的是多笔记录。在Read Uncommitted隔离级别下，不管事务2的插入操作是否提交，事务1在插入操作执行之前和之后执行相同的查询，取得的结果集是不同的，所以Read Uncommitted同样无法避免幻读。

**2>数据库事务隔离级别及实现**  
数据库事务的隔离级别有4种，由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable。（以下理解原理的表现需要首先掌握共享锁和排他锁的知识）  
①Read uncommitted  
原理：

1，事务对当前被读取的数据不加锁；
    2，事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级共享锁，直到事务结束才释放。

问题：脏读、不可重复和第二类丢失更新，幻读问题。  
表现：

1，事务1读取某行记录时，事务2也能对这行记录进行读取、更新；当事务2对该记录进行更新时，事务1再次读取该记录，能读到事务2对该记录的修改版本，即使该修改尚未被提交。
    2，事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。

②Read committed  
原理：

1，事务对当前被读取的数据加 行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；
    2，事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级排他锁，直到事务结束才释放。

问题：不可重复读、第二类丢失更新和幻读问题。  
表现：

1，事务1读取某行记录时，事务2也能对这行记录进行读取、更新；当事务2对该记录进行更新时，事务1再次读取该记录，读到的只能是事务2对其更新前的版本，要不就是事务2提交后的版本。
    2，事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。
    
    为什么解决了脏读问题？
    更新的时候加了排他锁，事务结束才释放，因此事务1在更新的时候，事务2是不会读到事务1还没提交的数据的

③Repeateable read  
原理：

1，事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加 行级共享锁，直到事务结束才释放；
    2，事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级排他锁，直到事务结束才释放。

问题： 幻读问题。  
表现：

1，事务1读取某行记录时，事务2也能对这行记录进行读取、更新；当事务2对该记录进行更新时，事务1再次读取该记录，读到的仍然是第一次读取的那个版本。
    2，事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。
    
    为什么没有不可重复读（包括第二类丢失更新）问题了？
    因为事务1读的时候加了共享锁，事务结束的时候才释放，共享锁的存在导致其他事务例如事务2在事务1结束前是无法竞争获取到排他锁的更新数据的，所以事务1事务过程中，数据不会被其他事务修改也就不会事务中两次读的数据不一致，从而也没有了第二类丢失更新问题。

④Serializable  
原理：

1，事务在读取数据时，必须先对其加 表级共享锁 ，直到事务结束才释放；
    2，事务在更新数据时，必须先对其加 表级排他锁 ，直到事务结束才释放。

问题：不存在问题  
表现：

1，事务1正在读取A表中的记录时，则事务2也能读取A表，但不能对A表做更新、新增、删除，直到事务1结束。
    2，事务1正在更新A表中的记录时，则事务2不能读取A表的任意记录，更不可能对A表做更新、新增、删除，直到事务1结束。
    
    为什么解决了幻读问题？
    直接加了表级锁，不存在增加删除行数

**3> innodb的事务隔离级别，讲一下GAP锁原理和MVCC**  
**[MVCC][]**

**4> XA协议，两阶段提交，2PC缺点和三阶段提交**  
**①xa协议：**XA是一个分布式事务协议，由Tuxedo提出。XA中大致分为两部分：事务管理器和本地资源管理器。其中本地资源管理器往往由数据库实现，比如Oracle、DB2这些商业数据库都实现了XA接口，而事务管理器作为全局的调度者，负责各个本地资源的提交和回滚。  
**②两阶段提交（2PC）：**两阶段提交又称2PC,2PC是一个非常经典的`强一致、中心化的原子提交协议`。分为两个阶段，第一阶段**投票阶段**，第二阶段**提交/执行阶段**。  
第一阶段：  
1）事务询问：协调者 向所有的 参与者 发送事务预处理请求，称之为Prepare，并开始等待各 参与者 的响应。  
2）执行本地事务：各个 参与者 节点执行本地事务操作,但在执行完成后并不会真正提交数据库本地事务，而是先向 协调者 报告说：“我这边可以处理了/我这边不能处理”。.  
3）各参与者向协调者反馈事务询问的响应：如果 参与者 成功执行了事务操作,那么就反馈给协调者 Yes 响应,表示事务可以执行,如果没有 参与者 成功执行事务,那么就反馈给协调者** **No 响应,表示事务不可以执行。  
第二阶段：  
1）执行事务提交：协调者 向** 所有参与者** 节点发出Commit请求  
2）事务提交：参与者 收到Commit请求之后,就会正式执行本地事务Commit操作,并在完成提交之后释放整个事务执行期间占用的事务资源。  
**③2PC的缺点**  
1）性能问题  
无论是在第一阶段的过程中,还是在第二阶段,所有的参与者资源和协调者资源都是被锁住的,只有当所有节点准备完毕，事务 协调者 才会通知进行全局提交，参与者 进行本地事务提交后才会释放资源。这样的过程会比较漫长，对性能影响比较大。  
2）单节点故障  
由于协调者的重要性，一旦 协调者 发生故障。参与者 会一直阻塞下去。尤其在第二阶段，协调者 发生故障，那么所有的 参与者 还都处于锁定事务资源的状态中，而无法继续完成事务操作。（虽然协调者挂掉，可以重新选举一个协调者，但是无法解决因为协调者宕机导致的参与者处于阻塞状态的问题）  
**④三阶段提交**  
相别二阶段提交，三阶段有两个改动

1、引入超时机制。同时在协调者和参与者中都引入超时机制。
    2、在第一阶段和第二阶段中插入一个准备阶段。保证了在最后提交阶段之前各参与节点的状态是一致的。

1）CanCommit阶段  
尝试获取数据库锁 如果可以，就返回Yes。   
2）PreCommit阶段  
在阶段一中，如果所有的参与者都返回Yes的话，那么就会进入PreCommit阶段进行事务预提交。这里的PreCommit阶段 跟上面的第一阶段是差不多的，只不过这里 协调者和参与者都引入了超时机制 （2PC中只有协调者可以超时，参与者没有超时机制）  
3）DoCommit阶段  
这里跟2pc的阶段二是差不多的。  
**⑤其他事务方案**  
TCC补偿事务：[TCC原理][TCC]

**5>Spring事务传播机制**  
**①，Propagation.REQUIRED**  
如果当前没有事务，就新建一个事务，如果已经存在一个事务中，加入到这个事务中。  
**②，Propagation.SUPPORTS**  
支持当前事务，如果当前没有事务，就以非事务方式执行。  
**③，Propagation.MANDATORY**  
使用当前的事务，如果当前没有事务，就抛出异常。  
**④，Propagation.REQUIRES\_NEW**  
新建事务，如果当前存在事务，把当前事务挂起。  
**⑤，Propagation.NOT\_SUPPORTED**  
以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。  
**⑥，Propagation.NEVER**  
以非事务方式执行，如果当前存在事务，则抛出异常。  
**⑦，Propagation.NESTED**  
如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION\_REQUIRED类似的操作。  
（这个和REQUIRED区别在于一个是加入到一个事务，一个是在嵌套事务运行）

**6>Mybatis防注入机制，预编译机制**  
**[Mybatis预编译机制详解][Mybatis]**

**7>了解几种数据库存储引擎以及各自优缺点**  
常见的有三种数据库InnoDB,MyISAM,MEMORY,博主实际还用过TokuDB。  
***① InnoDB***：**支持事务处理，支持外键，支持崩溃修复能力和并发控制**。如果需要对事务的完整性要求比较高（比如银行），**要**求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。   
**缺点：**读写效率较差雨MyISAM，占用的数据空间相对较大。

***② MyISAM***：**插入数据快，空间和内存使用比较低**。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。  
**缺点**：缺点是不支持事务的完整性和并发性。

***③ MEMORY***：**所有的数据都在内存中，数据的处理速度快，但是安全性不高**。如果需要**很快的读写速度**，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

**④ TokuDB：高压缩比**，默认使用zlib进行压缩，尤其是对字符串(varchar,text等)类型有非常高的压缩比，比较适合存储日志、原始数据等。**支持完整的ACID特性和事务机制，非常快的写入性能**，在线添加索引，不影响读写操作。适用于访问频率不高的数据或历史数据归档或者数据表非常大并且时不时还需要进行DDL操作。  
**缺点：不支持外键(foreign key)功能，不适大量读取的场景，因为压缩解压缩的原因，CPU占用高**。

**8>为什么用B+树做索引  
①B+树的磁盘读写代价更低：**B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。  
**②B+树的查询效率更加稳定：**由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。  
**③范围查找效率高：**对于范围查找来说，B+树只需遍历叶子节点链表即可，B树却需要重复地中序遍历,在项目中范围查找又很是常见的  
**④增删文件（节点）时效率更高：**因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率  
**9>左前缀匹配**  
①最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。  
②=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式  
[Mysql联合索引最左匹配原则][Mysql]  
**10>唯一索引和联合索引的区别**  
**[MySQL普通索引和唯一索引的深入讲解][MySQL]**  
**11>一条SQL查询/更新语句的执行过程**  
主体可以分为两部分，Server和存储引擎部分，更新过程需要先查询。  
![format_png][]  
**一、Server部分  
①连接器** 与客户端建立连接、获取权限、维持和管理连接  
**②查询缓存 **首先查询缓存，是否存在该记录，mysql8.0后删除此功能  
**③分析器 **词法分析和语法分析  
**④优化器 **决定索引，多表关联时候的连接数据  
**⑤执行器 **使用引擎提供的接口  
**二、存储引擎**  
①写undo log到log buffer （保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读）  
②执行事务，写redo log到log buffer（重做日志，物理日志，循环写），此时redo log是prepare状态  
③执行器生成这个操作的 binlog，并把 binlog 写入磁盘。  
③写redo log到log file并刷新落盘（innodb\_flush\_log\_at\_trx\_commit决定是否落盘）  
④提交事务，redo log 改成提交（commit）状态

**参考文档：**  
[Spring事物传播机制][Spring]  [B+树][B] [分布式事务 ][Link 1]

[MVCC]: https://www.jianshu.com/p/db334404d909
[TCC]: https://www.cnblogs.com/qdhxhz/p/11172585.html
[Mybatis]: https://blog.csdn.net/weixin_34452850/article/details/88991943
[Mysql]: https://blog.csdn.net/lizc_lizc/article/details/86573152
[MySQL]: https://www.jb51.net/article/155737.htm
[format_png]: /images/20230601/e5776d25e70748e0bdaf652f482d220f.png
[Spring]: https://www.jianshu.com/p/25c8e5a35ece
[B]: https://blog.csdn.net/a314774167/article/details/88111713
[Link 1]: https://www.cnblogs.com/qdhxhz/p/11167025.html