2022-04-02 15:14 已编辑乐信_后台开发工程师

关注

（必看）java面试重难点之数据库专题-MySQL

未完待续，持续更新，如果有用，收藏关注。

1、Mysql中四种隔离级别分别是什么？

2、ACID靠什么保证的

2.1：Mysql怎么保证原子性

2.2：mysql怎么保证一致性

2.3：mysql怎么保证隔离性

2.4：mysql怎么保证持久性

3、什么是MVCC？

4、四大范式

5、mysql工作原理

------------索引相关-----------

6、什么是索引

7、索引种类

8、Mysql索引类型有哪几种

9、什么是聚簇索引和非聚簇索引

10、使用聚簇索引的优势

11、InnoDB 和 MyISAM 的区别

12、B树与B+树的区别

13、为什么采用innodb作为默认引擎

14、为什么采用B+树索引

15、B+树的叶子节点链表是单向还是双向?

16、什么是覆盖索引和回表

17、间隙锁及其危害

18、主键和唯一索引的区别

19、锁的类型有哪些

20、说一下 MySQL 的行锁和表锁

21、行锁升级为表锁

22、哪些情况下适合建索引

23、哪些情况下不适合建索引

24、explain是什么

25、什么情况下索引会失效

如何优化Mysql语句？（重要）

1、Mysql中四种隔离级别分别是什么？

读未提交（READ UNCOMMITTED）：未提交读隔离级别也叫读脏，就是事务可以读取其它事务未提交的数据。

读已提交（READ COMMITTED）：在其它数据库系统比如 SQL Server 默认的隔离级别就是提交读，已提交读

隔离级别就是在事务未提交之前所做的修改其它事务是不可见的。

可重复读（REPEATABLE READ）：保证同一个事务中的多次相同的查询的结果是一致的，比如一个事务一开始

查询了一条记录然后过了几秒钟又执行了相同的查询，保证两次查询的结果是相同的，可重复读也是 mysql 的默认隔离级别。

可串行化（SERIALIZABLE）：可串行化就是保证读取的范围内没有新的数据插入，比如事务第一次查询得到某个

范围的数据，第二次查询也同样得到了相同范围的数据，中间没有新的数据插入到该范围中。

案例图

2、ACID靠什么保证的

A：原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

C：一致性由其他三大特性保证、程序代码要保证业务上的一致性

I：隔离性由MVCC来保证

D：持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复

2.1：Mysql怎么保证原子性

利用Innodb的undo log，undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。

例如:
(1)当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
(2)当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
(3)当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操
undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

2.2：mysql怎么保证一致性

1.final不可变

作用于类、方法、成员变量、局部变量。初始化完成后的不可变对象，其它线程可见。常量不会改变不会因为其它线程产生影响。Final修饰的引用类型的地址不变，同时需要保证引用类型各个成员和操作的线程安全问题。因为引用类型成员可能是可变的。

2.synchronized同步

作用域代码块、方法上。通过线程互斥，同一时间的同样操作只允许一个线程操作。通过字节码指令实现。

3.Volatile

多线程的内存模型：main memory（主存）、working memory（线程栈），在处理数据时，线程会把值从主存load到本地栈，完成操作后再save回去(volatile关键词的作用：每次针对该变量的操作都激发一次load and save)。

（1）volatile 修饰的变量的变化保证对其它线程立即可见。

volatile变量的写，先发生于读。每次使用volatile修饰的变量个线程都会刷新保证变量一致性。但同步之前各线程可能仍有操作。（如：各个根据volatile变量初始值分别进行一些列操作，然后再同步写赋值。每个线程的操作有先后，当一个最早的线程给线程赋值时，其它线程同步。但这时其它线程可能根据初始值做了改变，同步的结果导致其它线程工作结果丢失。

因此，根据volatile的语意使用条件：运算结果不依赖变量的当前值。

（2）volatile禁止指令重排优化。

2.3：mysql怎么保证隔离性

通过MVCC。

2.4：mysql怎么保证持久性

利用了redo log,Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。

redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；

二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。也就是说提交了两个日志文件。

3、什么是MVCC？

MVCC叫做多版本并发控制，实际上就是保存了数据在某个时间节点的快照。

我们每行数据实际上隐藏了两列，创建时间版本号，过期(删除)时间版本号，每开始一个新的事务，版本号都会自动递增。

拿user表举例子，假设我们插入两条数据，他们实际上应该长这样。

这时候假设小明去执行查询，此时current_version=3

select * from user where id<=3;

同时，小红在这时候开启事务去修改id=1的记录，current_version=4

update user set name='张三三' where id=1;

执行成功后的结果是这样的

如果这时候还有小黑在删除id=2的数据，current_version=5，执行后结果是这样的。

由于MVCC的原理是查找创建版本小于或等于当前事务版本，删除版本为空或者大于当前事务版本，小明的真实的查询应该是这样

select * from user where id<=3 and create_version<=3 and (delete_version>3 or delete_version is null);

所以小明最后查询到的id=1的名字还是'张三'，并且id=2的记录也能查询到。这样做是为了保证事务读取的数据是在事务开始前就已经存在的，要么是事务自己插入或者修改的。

4、四大范式

1、第一范式（1NF）

即：列不可再分

1.每一列属性都是不可再分的属性值，确保每一列的原子性

2.两列的属性相近或相似或一样，尽量合并属性一样的***保不产生冗余数据

例如:顾客表(姓名、编号、地址、……)其中"地址"列还可以细分为国家、省、市、区等。

2、第二范式（2NF）

即：属性完全依赖于主键

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主键

3、第三范式（3NF）

即：属性不依赖于其它非主属性属性直接依赖于主键

数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。像：a-->b-->c 属性之间含有这样的关系，是不符合第三范式的。

比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）

这样一个表结构，就存在上述关系。学号--> 所在院校 --> (院校地址，院校电话)

这样的表结构，我们应该拆开来，如下。

（学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）

4、BC范式（BCNF）

即：候选键存在多个属性时，多个主属性直接要消除传递依赖关系

（1）所有非主属性对每一个码都是完全函数依赖；（2）所有的主属性对于每一个不包含它的码，也是完全函数依赖；（3）没有任何属性完全函数依赖于非码的任意一个组合。 R属于3NF，不一定属于BCNF，如果R属于BCNF，一定属于3NF。 ··· 就是对于候选码中包含多个属性时，里面的关键字段互相没有依赖。假设仓库管理关系表(仓库号，存储物品号，管理员号，数量)，满足一个管理员只在一个仓库工作；一个仓库可以存储多种物品，则存在如下关系：

(仓库号，存储物品号)——>(管理员号，数量) (管理员号，存储物品号)——>(仓库号，数量) 所以，(仓库号，存储物品号)和(管理员号，存储物品号)都是仓库管理关系表的候选码，表中唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系： (仓库号)——>(管理员号) (管理员号)——>(仓库号) ··· 即存在关键字段决定关键字段的情况，因此其不符合BCNF。把仓库管理关系表分解为两个关系表仓库管理表(仓库号，管理员号)和仓库表(仓库号，存储物品号，数量)，这样这个数据库表是符合BCNF的，并消除了删除异常、插入异常和更新异常。

5、mysql工作原理

客户端向MySQL服务器发送一条查询请求
服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
MySQL根据执行计划，调用存储引擎的API来执行查询
将结果返回给客户端，同时缓存查询结果

6、什么是索引

官方定义: 一种帮助mysql提高查询效率的数据结构
索引的优点:
1、大大加快数据查询速度
索引的缺点:
1、维护索引需要耗费数据库资源
2、索引需要占用磁盘空间
3、当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

7、索引种类

a.主键索引
设定为主键后数据库会自动建立索引，innodb为聚簇索引
b.单值索引
即一个索引只包含单个列，一个表可以有多个单列索引
c.唯一索引
索引列的值必须唯一，但允许有空值
d.复合索引
即一个索引包含多个列
e.Full Text 全文索引 (My5.7版本之前只能由于MYISAM引擎)
全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、 TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文索引

8、Mysql索引类型有哪几种

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。
FULLTEXT：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
HASH：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。
HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
BTREE：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
RTREE：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
相对于BTREE，RTREE的优势在于范围查找。：

9、什么是聚簇索引和非聚簇索引

聚簇索引就是按照每张表的主键构造一颗B+树，叶子节点中存放索引值与数据
非聚簇索引就是按照每张表的辅助键构造一颗树，叶子节点中只存放索引值

在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找

InnoDB中

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）
聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键（类似oracle中的RowId）来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。
2、MYISAM中

MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

10、使用聚簇索引的优势

1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

- 2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

11、InnoDB 和 MyISAM 的区别？

事务：MyISAM不支持事务，InnoDB支持事务；
全文
索引：MyISAM 支持全文索引，InnoDB 5.6 之前不支持全文索引；
关于 count()：MyISAM会直接存储总行数，InnoDB 则不会，需要按行扫描。意思就是对于 select count() from table; 如果数据量大，MyISAM 会瞬间返回，而 InnoDB 则会一行行扫描；
外键：MyISAM 不支持外键，InnoDB 支持外键；
锁：MyISAM 只支持表锁，InnoDB 可以支持行锁。

12、B树与B+树的区别

（1）B树的每个结点都存储了key和data，B+树的data存储在叶子节点上。节点不存储data，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。

（2）树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

13、为什么采用innodb作为默认引擎

因为innodb采用的是聚簇索引，叶子节点存放索引列与值

1、B+树是低高度的树，因为非叶子节点只存放索引列，这样子能存放的索引列就更多了

2、减少了IO次数，B+树的值只存放在叶子节点里，先通过索引值找到叶子结点，再通过IO操作取出数据，而B树在非叶子节点上也需取出数据做比较，IO次数更多

14、为什么采用B+树索引

1、减少了IO次数，B+树的值只存放在叶子节点里，先通过索引值找到叶子结点，再通过IO操作取出数据，而B树在非叶子节点上也需取出数据做比较，IO次数更多

2、非叶子节点能存放更多的索引列，树的节点更低

3、对比哈希索引，哈希索引适合与单列数据的操作，时间复杂度是O(1)，但不适合于对一个范围内的数据进行操作。

15、B+树的叶子节点链表是单向还是双向?

Page是Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位，与数据库相关的所有内容都存储在Page结构里。Page分为几种类型：数据页（B-Tree Node），Undo页（Undo Log Page），系统页（System Page），事务数据页（Transaction System Page）等；每个数据页的大小为16kb，每个Page使用一个32位（一位表示的就是0或1）的int值来表示，正好对应Innodb最大64TB的存储容量(16kb * 2^32=64tib) 一个Page的基本结构如下：