【Java面试八股文】数据库篇

导航： 

【黑马Java笔记+踩坑汇总】JavaSE+JavaWeb+SSM+SpringBoot+瑞吉外卖+SpringCloud+黑马旅游+谷粒商城+学成在线+MySQL高级篇+设计模式+牛客面试题

目录

请你说说MySQL索引,以及它们的好处和坏处

请你说说MySQL的索引是什么结构,为什么不用哈希表

请你说说数据库索引的底层数据结构

请你讲讲B树和B+树

数据库为什么不用红黑树而用B+树？

请你说说索引怎么实现的B+树,为什么选这个数据结构？

请你说说聚簇索引和非聚簇索引

请你说说数据库引擎有哪些,各自有什么区别

请你说说InnoDB的MVCC

请你说说乐观锁和悲观锁

请你介绍一下数据库的ACID

请你说说MySQL的事务隔离级别

MySQL主从同步是如何实现的？

MySQL调优

---

请你说说MySQL索引,以及它们的好处和坏处

得分点

检索效率、磁盘资源、维护索引对性能的影响、最左前缀原则、三种索引介绍和区别

索引是一种用于快速查询的排好序的数据结构，索引是存储引擎实现的，不同索引的存储引擎不一定相同。

优点：

• 查询效率高，磁盘I/O次数低；
• 每行数据索引唯一；
• 联合、分组、排序查询效率高；

缺点：

• 创建索引耗费时间；
• 维护索引耗费时间，每次增删改时要维护索引；
• 索引占用磁盘空间，索引文件可能比数据文件更占空间。例如innoDB的1个聚簇索引和多个非聚簇索引加起来肯定比原数据文件占的内存多；
• 联合索引查询时，没有遵循最左前缀原则将会导致索引不起作用，从而出现严重的性能问题。

innoDB索引按存储方式可以分为：聚集索引、二级索引、联合索引（严格说是二级索引）。

聚集索引：索引即数据（因为记录就存在B+树叶节点），数据即索引。 

• 数据访问更快（因为索引和数据保存在同一个B+树）；
• 排序查找和范围查找很快（因为叶节点之间由双向链表链接）；
• 分段加载，节省内存；
• 建议主键短、自增、不可更新，从而提高效率；

非聚簇索引（辅助、二级索引）：B+树叶节点只存查询字段的值和主键的值，通过主键值回表查聚集索引。

联合索引：同时为多个列建立非聚簇索引。先按c2字段排序，c2字段相同时按c3字段排序。

聚簇索引和非聚簇索引区别

• 叶节点：聚簇索引叶子节点存记录，非聚簇索引叶子节点存数据位置 。非聚簇索引不会影响数据表的物理存储顺序。
• 数量：一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。
• 效率：使用聚簇索引的时候，数据的查询效率高 ，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。因为聚簇索引存的完整记录，移动起来慢。

标准回答

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

索引就像指向表行的指针，是一种允许查询操作快速确定哪些行符合WHERE子句中的条件，并检索到这些行的其他列值的数据结构；

索引主要有普通索引、唯一索引、主键索引、外键索引、全文索引、复合索引几种；

在大数据量的查询中，合理使用索引的优点非常明显，不仅能大幅提高匹配where条件的检索效率，还能用于排序和分组操作的加速。

当时索引如果使用不当也有比较大的坏处：比如索引必定会增加存储资源的消耗；同时也增大了插入、更新和删除操作的维护成本，因为每个增删改操作后相应列的索引都必须被更新。

加分回答-只要创建了索引，就一定会走索引吗？

不一定。比如，在使用组合索引的时候，如果没有遵从“最左前缀”的原则进行搜索，则索引是不起作用的。

最左前缀原则：指的是由多个列组成的联合索引，在查询时只会对最左边使用的几个列进行索引查询。具体来说，如果索引键包含A、B、C三个列，而查询语句仅使用了A和B列，则最左前缀的原则会指导MySQL只使用A、B两列进行索引搜索，而不会使用C列。

当使用“最左前缀”原则时，指定的列的顺序非常重要。如果指定的列顺序不正确，就无法利用索引进行查询，会产生全表扫描的情况，这会造成非常严重的性能问题。

举例，假设在id、name、age字段上已经成功建立了一个名为MultiIdx的组合索引。索引行中按id、name、age的顺序存放，索引可以搜索id、（id，name）、（id，name，age）字段组合。如果列不构成索引最左面的前缀，那么MySQL不能使用局部索引，如（age）或者（name，age）组合则不能使用该索引查询。

请你说说MySQL的索引是什么结构,为什么不用哈希表

得分点

B+树、自适应的哈希、内存耗费

MySQL中的索引是B+树结构，可以分段加载节点数据，省内存，查询效率也高。

自适应的哈希：InnoDB底层索引是B+树，但他支持自适应的哈希索引。对于经常被访问的数据，MySQL会把经常被访问数据的数据页地址存到哈希表里，这样下次再查到这个热点数据就会直接从哈希表找，提高查询效率；

不用哈希表的原因：哈希表耗费内存，要把所有数据载入内存。

标准回答

MySQL中的索引B+树实现的；

哈希表的查询效率的确最高，时间复杂度O(1)，但是它要求将所有数据载入内存，而数据库存储的数据量级可能会非常大，全部载入内存基本上是不可能实现的；

B+树可以分段加载需要的节点数据，可以在内存资源有限的前提下，极大提高查询效率。

分段加载：将整个B+树划分成若干个段，每个段包含多个数据页。在查询过程中，只需要将当前需要查询的节点所在的段加载到内存中，而不必将整棵树都加载到内存中，从而减少内存的占用和I/O操作次数。

请你说说数据库索引的底层数据结构

得分点

介绍并比较优缺点、二叉树、红黑树、哈希表、B树、B+树

数据库（不单指MySQL）索引可选的底层数据机构包括：

- 二叉树：某些场景下会退化成链表，查询慢；

- 红黑树：红黑树是二叉树，索引树层数会很高，查询次数和IO不如B+树；

- Hash表：增删改查时间复杂度O（1），但①太占空间，②难以排序、范围查询，③无法对单独一个字段索引，④重复值多时处理冲突耗费性能和IO；

- B-Tree：多路平衡查找树，非叶节点也存数据，左小右大，一般层数比B+树深，查询速度和IO次数也就不如B+树；可以分段加载节点数据。

- B+Tree：多路平衡查找树，非叶节点存目录，叶节点存记录。查询效率更高（比B树矮胖，层数很难超过4层），IO次数也少（很难超过3次），更稳定，范围查询效率高（因为叶节点之间由双向链表链接）。可以分段加载节点数据。

标准答案

数据库（不单指MySQL）索引可选的底层数据机构包括：

- 二叉树

- 红黑树

- Hash表

- B-Tree

但mysql索引的底层用的并不是二叉树和红黑树。因为二叉树和红黑树在某些场景下都会暴露出一些缺陷。

首先，二叉树在某些场景下会退化成链表，而链表的查找需要从头部开始遍历，而这就失去了加索引的意义。

不使用红黑树的原因是：红黑树作为底层数据结构在面对在些表数据动辄数百万数千万的场景时，会导致索引树的层数很高。索引从根节点开始查找，而如果我们需要查找的数据在底层的叶子节点上，那么树的高度是多少，就要进行多少次查找，数据存在磁盘上，访问需要进行磁盘IO，这会导致效率过低；

而B+树由B树和索引顺序访问方法演化而来，它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进行链接。

B+树索引在数据库中的一个特点就是高扇出性，例如在InnoDB存储引擎中，每个页的大小为16KB。在数据库中，B+树的高度一般都在2～4层，这意味着查找某一键值最多只需要2到4次IO操作，这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作，2～4次的IO操作意味着查询时间只需0.02～0.04秒。

为什么B+树的高度一般不会超过4层：

真实一个数据页可以存100条记录， 一个目录页能存1000条数据；4层B+树能存一千万条数据，我们用到的B+树都不会超过4层，每个页内部可以用二分查找更快查找。

InnoDB非聚簇索引情况：

InnoDB 页的大小为 16KB，一般表的主键类型为 INT(占用4个字节)或 BIGINT (占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页 (B+Tree 中的一个节点)中大概存储16KB/(8B+8B)=1K个键值 (因为是估值，为方便计算，这里的K取值为 10^3。也就是说一个深度为3的B+Tree 索引可以维护 10^3*10^3*10^3= 1 亿条记录(这里假定一个数据页也存储10A3条行记录数据了)

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在 2~4 层。MySQL的lnnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 2~4  次磁盘IO操作。

数据页大小：

MyISAM 的数据页大小是固定的，是 1KB，也就是说，MyISAM 存储引擎的数据都是以 1KB 的块进行管理的。

InnoDB 存储引擎的数据页大小是可调的，默认是 16KB。在 MySQL 5.7 版本之前，InnoDB 的数据页大小默认是 8KB。可以通过参数 innodb_page_size 来设置 InnoDB 的数据页大小，取值范围是 4KB、8KB、16KB 和 32KB。

请你讲讲B树和B+树

得分点

平衡多路查找树、记录存储位置、非叶节点存储内容、叶节点关系、查询性能和IO次数

1. 非叶节点的值数量和孩子数量：k阶树，B+树非叶节点有k个值、k个孩子，B树非叶节点有k-1个值、k个孩子；

2. 记录存储位置：B+树记录全存在叶节点，B树记录存在所有节点；

3. 非叶节点存储内容：B+树非叶节点存索引，B树非叶节点存记录；

4. 叶子结点之间关系：B+树所有叶子节点构成一个有序双向链表；B树叶节点之间没指针，只是有序；

查询性能：B+树查询效率更高（比B树矮胖，层数很难超过4层），IO次数也少（很难超过3次），更稳定，范围查询效率高（因为叶节点之间由双向链表链接）。

B树和B+树都可以分段加载节点数据。

标准回答

它们都是平衡多路查找树，是在二叉查找树基础上的改进数据结构。在二叉查找树上查找一个数据时，最坏情况的查找次数为树的深度，当数据量很大时，查询次数可能还是很大，造成大量的磁盘IO，从而影响查询效率；

为了减少磁盘IO的次数，必须降低树的深度，因此在二叉查找树基础上将树改成了多叉加上一些限制条件，就形成了B树；

B+树是B树的变种，区别主要是：对于k阶的B树，每个中间节点只存k-1个值、k个指针（是那k-1个值的范围），而B+树存k个值和k个指针；B树中所有节点中值的总集是全部关键字集合，而B+树中所有叶子节点值的总集就是全部关键字集合；B+树为所有叶子节点增加了链接，从而实现了快速的范围查找；

加分回答-B+树优点

B+树由B树和索引顺序访问方法演化而来，它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进行链接。

B+树索引在数据库中的一个特点就是高扇出性，例如在InnoDB存储引擎中，每个页的大小为16KB。在数据库中，B+树的高度一般都在2～4层，这意味着查找某一键值最多只需要1到3次IO操作（（根节点被缓存，不算IO次数））。因为现在一般的磁盘每秒至少可以做100次IO操作，2～4次的IO操作意味着查询时间只需0.02～0.04秒。

数据库为什么不用红黑树而用B+树？

得分点

介绍红黑树和B+树、叉数、高度、查询性能、磁盘IO次数、范围查询、分段加载数据

因为B+树是多叉，红黑树是二叉，B+树更矮胖，查询性能更高，IO次数更少。

B+树是多路平衡查找树，非叶节点存目录，叶节点存记录。查询效率更高（比B树矮胖，层数很难超过4层），IO次数也少（很难超过3次），更稳定，范围查询效率高（因为叶节点之间由双向链表链接）。 可以分段加载节点数据。  

m阶B+树：

• 每个非叶节点关键字个数和孩子个数相等；
• 根结点关键字个数2到m，非跟结点关键字个数⌈m/2⌉到m；
• 所有叶子在同一层；

红黑树： 近似平衡二叉树，左右子树高差有可能大于 1，查找效率略低于平衡二叉树，但增删效率高于平衡二叉树，适合频繁插入删除。

• 结点非黑即红；
• 根结点是黑色，叶节点是黑色空节点（常省略）；
• 任何相邻节点不能同时为红色；
• 从任一结点到其每个叶子的所有路径都包含相同数目的黑色结点；
• 查询性能稳定O(logN)，高度最高2log(n+1)；

标准回答

首先，红黑树是一种近似平衡二叉树（不完全平衡），结点非黑即红的树，它的树高最高不会超过 2*log(n)，因此查找的时间复杂度为 O(log(n))，无论是增删改查，它的性能都十分稳定；

但是，红黑树本质还是二叉树，在数据量非常大时，需要访问+判断的节点数还是会比较多，同时数据是存在磁盘上的，访问需要进行磁盘IO，导致效率较低；

而B+树是多叉的，可以有效减少磁盘IO次数；同时B+树增加了叶子结点间的连接，能保证范围查询时找到起点和终点后快速取出需要的数据。

加分回答-红黑树做索引底层数据结构的缺陷

试想一下，以红黑树作为底层数据结构在面对在些表数据动辄数百万数千万的场景时，创建的索引它的树高得有多高？

索引从根节点开始查找，而如果我们需要查找的数据在底层的叶子节点上，那么树的高度是多少，就要进行多少次查找，数据存在磁盘上，访问需要进行磁盘IO，这会导致效率过低；

那么红黑树作为索引数据结构的弊端即是：树的高度过高导致查询效率变慢。

请你说说索引怎么实现的B+树,为什么选这个数据结构？

得分点

B+树、查询效率、IO次数、范围排序查询、分段加载数据页

B+树：

B+树是多路平衡查找树，非叶节点存目录，叶节点存记录。

为什么选B+树？

查询效率更高（层数很难超过4层），IO次数也少（很难超过3次），更稳定，范围、排序查询效率高（因为叶节点之间由双向链表链接）。可以分段加载节点数据。 

m阶B+树：

• 每个非叶节点关键字个数和孩子个数相等；
• 根结点关键字个数2到m，非跟结点关键字个数⌈m/2⌉到m；
• 所有叶子在同一层；

 

标准回答

索引本质上就是通过预排序+树型结构来加快检索的效率，而MySQL中使用InnoDB和MyISAM引擎时都使用了B+树实现索引。

它是一棵平衡多路查找树，是在二叉查找树基础上的改进数据结构。在二叉查找树上查找一个数据时，最坏情况的查找次数为树的深度，当数据量很大时，查询次数可能还是很大，造成大量的磁盘IO，从而影响查询效率；

为了减少磁盘IO的次数，必须降低树的深度，因此在二叉查找树基础上将树改成了多叉加上一些限制条件，就形成了B树；

B+树中所有叶子节点值的总集就是全部关键字集合；B+树为所有叶子节点增加了链接，从而实现了快速的范围查找；

在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。在数据库中，B+树的高度一般都在2～4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO。这很不错，因为当前一般的机械磁盘每秒至少可以做100次IO，2～4次的IO意味着查询时间只需0.02～0.04秒。

在数据库中，B+树索引还可以分为聚集索引和辅助索引，但不管是聚集索引还是辅助索引，其内部都是B+树的，即高度平衡的，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

请你说说聚簇索引和非聚簇索引

得分点

介绍、区别、叶节点、数量、效率、回表

innoDB索引按存储方式可以分为：聚集索引、二级索引、联合索引（严格说是二级索引）。

聚集索引：索引即数据（因为记录就存在B+树叶节点），数据即索引。 

• 数据访问更快（因为索引和数据保存在同一个B+树）；
• 排序查找和范围查找很快（因为叶节点之间由双向链表链接）；
• 分段加载，节省内存；
• 建议主键短、自增、不可更新，从而提高效率；

非聚簇索引（辅助、二级索引）：B+树叶节点只存查询字段的值和主键的值，通过主键值回表查聚集索引。

联合索引：同时为多个列建立非聚簇索引。先按c2字段排序，c2字段相同时按c3字段排序。

聚簇索引和非聚簇索引区别

• 叶节点：聚簇索引叶子节点存记录，非聚簇索引叶子节点存数据位置 。非聚簇索引不会影响数据表的物理存储顺序。
• 数量：一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。
• 效率：使用聚簇索引的时候，数据的查询效率高 ，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。因为聚簇索引存的完整记录，移动起来慢。

标准回答

两者主要区别是数据和索引是否分离。聚簇索引是将数据与索引存储到一起，找到索引也就找到了数据；而非聚簇索引是将数据和索引存储分离开，索引树的叶子节点存储了数据行的地址。

在InnoDB中，一个表有且仅有一个聚簇索引（因为原始数据只留一份，而数据和聚簇索引在一起），并且该索引是建立在主键上的，即使没有指定主键，也会特殊处理生成一个聚簇索引；其他索引都是辅助索引，使用辅助索引访问索引外的其他字段时都需要进行二次查找。

而在MyISAM中，所有索引都是非聚簇索引，叶子节点存储着数据的地址，对于主键索引和普通索引在存储上没有区别。

加分回答

在InnoDB存储引擎中，可以将B+树索引分为聚簇索引和辅助索引（非聚簇索引）。无论是何种索引，每个页的大小都为16KB，且不能更改。

聚簇索引是根据主键创建的一棵B+树，聚簇索引的叶子节点存放了表中的所有记录。辅助索引是根据索引键创建的一棵B+树，与聚簇索引不同的是，其叶子节点仅存放索引键值，以及该索引键值指向的主键。也就是说，如果通过辅助索引来查找数据，那么当找到辅助索引的叶子节点后，很有可能还需要根据主键值查找聚簇索引来得到数据，这种查找方式又被称为书签查找。因为辅助索引不包含行记录的所有数据，这就意味着每页可以存放更多的键值，因此其高度一般都要小于聚簇索引。

请你说说数据库引擎有哪些,各自有什么区别

得分点

InnoDB、MyISAM，外键、事务、高并发、锁级别、内存要求、磁盘耗费、查询性能、增删改性能

InnoDB：支持外键和事务，行锁适合高并发，缓存索引和数据，内存要求高（因为要缓存索引和记录），适合存大数据量，增删改性能更优（行级锁高并发），耗费磁盘（因为有多个非聚簇索引，索引可能比记录空间还大）。

MyISAM：不支持外键和事务，表锁不适合高并发，缓存索引和数据地址，内存要求低（因为不用缓存记录），查询性能更优（因为查询时InnoDB要维护MVCC一致，而且多缓存了记录），节省磁盘（因为磁盘不存完整记录）。

标准回答

InnoDB 引擎是 MySQL 的事务安全（ACID 兼容）存储引擎，具有提交、回滚和崩溃恢复功能来保护用户数据；行级锁定读取增加了多用户并发性和性能；将用户数据存储在聚集索引中，以减少基于主键的常见查询的 I/O；还支持 FOREIGN KEY 维护数据完整性。

MyISAM引擎的表占用空间较小，表级锁定限制了读/写工作负载的性能，因此它通常用于只读或以读取为主的场景。

Memory引擎是将所有数据存储在 RAM 中，以便在需要快速查找非关键数据的环境中进行快速访问，以前被称为 HEAP 引擎。

Archive引擎非常适合存储大量的独立的，作为历史记录的数据，因为它们不经常被读取。它 拥有高效的插入速度，但其对查询的支持相对较差。

Cluster/NDB是高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用。

Federated引擎提供连接单独的 MySQL 服务器，从多个物理服务器创建一个逻辑数据库的能力，非常适合分布式或数据集市环境。

标准回答2

InnoDB是具有事务、回滚和崩溃修复能力的事务安全型引擎，它可以实现行级锁来保证高性能的大量数据中的并发操作；MyISAM是具有默认支持全文索引、压缩功能及较高查询性能的非事务性引擎。具体来说，可以在以下角度上形成对比：

事务：InnoDB支持事务；MyISAM不支持。

数据锁：InnoDB支持行级锁；MyISAM只支持表级锁。

读写性能：InnoDB增删改性能更优（行级锁高并发）；MyISAM查询性能更优。

全文索引：InnoDB不支持（但可通过插件等方式支持）；MyISAM默认支持。

外键：InnoDB支持外键；MyISAM不支持。

存储结构：InnoDB在磁盘存储为一个文件；MyISAM在磁盘上存储成三个文件（表定义、数据、索引）。

存储空间：InnoDB需要更多的内存和存储；MyISAM支持支持三种不同的存储格式：静态表(默认)、动态表、压缩表。

移植：InnoDB在数据量小时可通过拷贝数据文件、备份binlog、mysqldump工具移植，数据量大时比较麻烦；可单独对某个表通过拷贝表文件移植。

崩溃恢复：InnoDB有崩溃恢复机制；MyISAM没有。

默认推荐：InnoDB是MySQL5.5之后的默认引擎。

加分回答-InnoDB中行级锁是怎么实现的？

InnoDB行级锁是通过给索引上的索引项加锁来实现的。只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

当表中锁定其中的某几行时，不同的事务可以使用不同的索引锁定不同的行。另外，不论使用主键索引、唯一索引还是普通索引，InnoDB都会使用行锁来对数据加锁。

请你说说InnoDB的MVCC

得分点

概念、三剑客、流程、读提交和可重复读MVCC原理

MVCC：多版本并发控制，通过管理记录的多个版本，实现了数据库事务并发时的一致性读，即当前事务读取正在被其他事务更新的行时，能读到该记录被更新之前的版本。解决了读写冲突。

MVCC三剑客：隐藏字段、Undo Log、Read View。

隐藏字段：innoDB聚簇索引叶节点记录有两个隐藏字段，trx_id和roll_pointer。

• trx_id：事务id。事务修改记录时会将事务id写在trx_id列。
• roll_pointer：回滚指针。指向回滚日志里该记录版本链的最近结点。用于找到回滚日志里该记录被事务修改前的版本。

Undo Log回滚日志：存储各记录的版本链，用于回滚和存档。记录的一个版本就是版本链的一个结点，每个结点的数据域是整个记录，指针域指向上个版本的结点。首结点是最近记录。

Read View：事务快照读时生成的数据系统读视图，记录并维护当前活跃事务的id列表。事务id是按创建时间递增的。

• 快照读：不加锁的查询语句，可能读到旧版本数据。
• 当前读：加锁的查询语句，读到的一定是最新版本数据

 Read View的规则，即可见性算法：

通过读视图，可以判断当前查询中，记录的某个版本是否可见。

判断方法是比较各版本的trx_id和读视图里的活跃事务id，如果某版本trx_id小于读视图的最小事务id，则代表那个版本是生成读视图之前的已提交版本，当前查询就可以访问那个版本。

MVCC流程：

查询，生成读视图，用读视图的活跃事务id依次对比各版本的事务id，找到符合规则的数据。

应用： 事务隔离级别里的读提交和可重复读底层是由MVCC实现的。并且MySQL InnoDB 引擎的可重复读解决了幻读问题，快照读由MVCC解决，当前读通过 next-key lock解决。

读提交的MVCC原理：事务每次读到的都是最新已提交的数据。每次读取数据前都生成一个ReadView。快照读生成Read View，不断对比版本链各版本的trx_id，直到发现某版本trx_id比Read View的活跃事务列表里最小trx_id还小，该版本则是快照读前最新已提交的数据。

可重复读的MVCC原理：只在第一次查询时生成ReadView，之后查询用第一次快照读时生成的ReadView。

标准回答

全称 Multi-Version Concurrency Control ，即多版本并发控制，逻辑是维持一个数据的多个版本，使得读写操作没有冲突。MVCC主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

它是一种用来解决读-写冲突的无锁并发控制机制。在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能，还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题。

加分回答-三剑客

InnoDB默认的隔离级别是RR（REPEATABLE READ），RR解决脏读、不可重复读、幻读等问题，使用的是MVCC。MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议。它最大的优点是读不加锁，因此读写不冲突，并发性能好。InnoDB实现MVCC，多个版本的数据可以共存，主要基于以下技术及数据结构：

1. 隐藏列：InnoDB中每行数据都有隐藏列，隐藏列中包含了本行数据的事务id、指向undo log的指针等。

2. 基于undo log的版本链：每行数据的隐藏列中包含了指向undo log的指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链。

3. ReadView：通过隐藏列和版本链，MySQL可以将数据恢复到指定版本。但是具体要恢复到哪个版本，则需要根据ReadView来确定。所谓ReadView，是指事务（记做事务A）在某一时刻给整个事务系统（trx_sys）打快照，之后再进行读操作时，会将读取到的数据中的事务id与trx_sys快照比较，从而判断数据对该ReadView是否可见，即对事务A是否可见。

 

请你说说乐观锁和悲观锁

得分点

定义、乐观锁三种实现方式、使用场景

MySQL数据库的锁机制主要有两种：乐观锁和悲观锁。 

乐观锁：每次拿数据时候乐观地认为别人不会改这个数据，更新时再判断锁。因为读的时候不判断锁，所以读效率高；因为更新时才判断锁所以也不会死锁。

实现方式：版本号机制、时间戳机制、CAS机制。

版本号机制：表中有一个version字段， 读时取version字段，更新时如果发现版本号没变就给版本号加一并更新，如果发现版本号变了就拒绝更新。

时间戳机制：表中有一个时间戳字段，更新时对比时间戳得知是否被更改。

CAS机制：使用像compare and swap这样的原子操作来不断尝试修改记录，如果数据没有被其他线程修改则会更新成功，否则需重试。

应用场景： 

git等版本控制工具是悲观锁，在pull到push期间如果别人已经push过，那么我会push失败。

库存多、高并发的秒杀场景适合用乐观锁，为防止主从同步出错要强制从主机查数据。在提交订单业务的最后一步查库存余量。

悲观锁：每次拿数据时候悲观地认为别人正在改这个数据，拿数据时候就判断锁，拿到锁后阻塞其他线程。因为阻塞所以并发差（特别是长事务）、可能死锁。因为阻塞所以有排他性，精度高。

库存少、低并发、高价格、高精度的秒杀场景适合用悲观锁。订单业务刚开始查库存的时候就加锁，然后生成订单，减库存，释放锁。

标准回答

乐观锁：乐观锁总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量（单位时间内完成的任务、处理的数据量、传输的信息量等），像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。

UPDATE ... SET version=version+1 WHERE version=${version};

悲观锁：悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

加分回答-两种锁的使用场景

乐观锁：

GIT，SVN，CVS等代码版本控制管理器，就是一个乐观锁使用很好的场景，例如：A、B程序员，同时从SVN服务器上下载了code.html文件，当A完成提交后，此时B再提交，那么会报版本冲突，此时需要B进行版本处理合并后，再提交到服务器。这其实就是乐观锁的实现全过程。如果此时使用的是悲观锁，那么意味者所有程序员都必须一个一个等待操作提交完，才能访问文件，这是难以接受的。

悲观锁：

悲观锁的好处在于可以减少并发，但是当并发量非常大的时候，由于锁消耗资源、锁定时间过长等原因，很容易导致系统性能下降，资源消耗严重。因此一般我们可以在并发量不是很大，并且出现并发情况导致的异常用户和系统都很难以接受的情况下，会选择悲观锁进行。

请你介绍一下数据库的ACID

得分点

原子性、一致性、隔离性、持久性

数据库的ACID是指事务四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

原子性：事务的所有操作，要么全部成功，要么全部失败。 

一致性：事务前后，数据库的约束没有被破坏，保持前后一致。

隔离性：操作同一资源的并发事务之间相互隔离，不会互相干扰。

持久性：事务的结果最终一定会持久化到数据库，宕机等故障也无法影响。

标准回答

事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。在事务中的操作，要么都执行修改，要么都不执行，这就是事务的目的，也是事务模型区别于文件系统的重要特征之一。

事务需遵循ACID四个特性：

- A（atomicity），原子性。原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，整个事务的执行才算成功。事务中任何一个SQL语句执行失败，那么已经执行成功的SQL语句也必须撤销，数据库状态应该退回到执行事务前的状态。

- C（consistency），一致性。一致性指事务将数据库从一种状态转变为另一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

- I（isolation），隔离性。事务的隔离性要求每个读写事务的对象与其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见，这通常使用锁来实现。

- D（durability） ，持久性。事务一旦提交，其结果就是永久性的，即使发生宕机等故障，数据库也能将数据恢复。持久性保证的是事务系统的高可靠性，而不是高可用性。

加分回答-事务分类

事务可以分为以下几种类型：

- 扁平事务：是事务类型中最简单的一种，而在实际生产环境中，这可能是使用最为频繁的事务。在扁平事务中，所有操作都处于同一层次，其由BEGIN WORK开始，由COMMIT WORK或ROLLBACK WORK结束。处于之间的操作是原子的，要么都执行，要么都回滚。

- 带有保存点的扁平事务：除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态，这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效，放弃整个事务不合乎要求，开销也太大。保存点（savepoint）用来通知系统应该记住事务当前的状态，以便以后发生错误时，事务能回到该状态。

- 链事务：可视为保存点模式的一个变种。链事务的思想是：在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务。注意，提交事务操作和开始下一个事务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中进行的。

- 嵌套事务：是一个层次结构框架。有一个顶层事务（top-level transaction）控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务（subtransaction），其控制每一个局部的变换。

- 分布式事务：通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。对于分布式事务，同样需要满足ACID特性，要么都发生，要么都失效。

对于MySQL的InnoDB存储引擎来说，它支持扁平事务、带有保存点的扁平事务、链事务、分布式事务。对于嵌套事务，MySQL数据库并不是原生的，因此对于有并行事务需求的用户来说MySQL就无能为力了，但是用户可以通过带有保存点的事务来模拟串行的嵌套事务。

请你说说MySQL的事务隔离级别

得分点

隔离级别概念、三种读问题、四种隔离级别的特点、底层、解决读问题

事务隔离级别是指操作同一资源的并发事务之间的隔离度。隔离级别越高，事务之间的相互干扰就越小，安全性就越高。

读问题：

• 脏读：读到了脏数据。当前事务读到另一个未提交事务刚改的数据。只有读未提交会脏读。
• 不可重复读：前后重复读的数据不一样。前后两次读同数据，这期间数据被其他事务改了，导致前后读取的数据不同。
• 幻读：前后读的数据是一样，但多了几行或少了几行，像幻觉一样。事务前后读的数据集合不同，导致出现“幻像”行。仅串行化能解决幻读问题。

事务隔离级别

• 读未提交：事务能读到所有未提交事务的数据。压根不加锁、没隔离，性能最高。
• 读提交：事务能读到已提交事务的数据。底层由MVVC实现，每次快照读都生成读视图 。解决脏读问题。
• 可重复读（默认）：前后读的数据相同。底层由MVVC实现，仅第一次快照读生成读视图。解决脏读、不可重复读问题。MySQL InnoDB 引擎的可重复读解决了幻读问题。快照读由MVCC解决，当前读通过 next-key lock解决。
• 串行化：事务一拿到锁阻塞其他事务，直到释放锁。读时共享锁，写时排它锁。阻塞导致性能最差。解决脏读、不可重复读、幻读问题。 

MVVC：多版本并发控制。MVCC三剑客：隐藏字段、Undo Log、Read View。

共享锁（读锁）：在共享锁下，多个线程可以同时读取数据，但只有一个线程能够修改数据。当一个线程在修改数据时，必须获得独占锁，以便其他线程不能访问数据。

排它锁（写锁）：在排它锁下，只有一个线程可以修改数据，其他线程不允许访问数据。

标准回答 

SQL 标准定义了四种隔离级别，这四种隔离级别分别是：

- 读未提交（READ UNCOMMITTED）

- 读提交 （READ COMMITTED）；

- 可重复读 （REPEATABLE READ）；

- 串行化 （SERIALIZABLE）。

下面是四种隔离级别在解决脏读、不可重复读、幻读问题方面的情况：

隔离级别	脏读	不可重复读	幻读
读未提交	存在	存在	存在
读已提交	不存在	存在	存在
可重复读	不存在	不存在	存在
串行化	不存在	不存在	不存在

脏读（Dirty Read）：指一个事务读取了另一个未提交的事务所写入的数据，如果隔离级别越高，则越不容易出现脏读问题。

不可重复读（Non-Repeatable Read）：指一个事务在读取同一数据时，由于另外一个事务的修改或删除，导致两次读取的数据不同。如果隔离级别越高，则越不容易出现不可重复读问题。

幻读（Phantom Read）：指一个事务多次执行同一个查询，但每次返回的数据集合都不同，导致出现“幻像”行。如果隔离级别越高，则越不容易出现幻读问题。

加分回答-锁

READ UNCOMMITTED读未提交：

它是性能最好、也最野蛮的方式，因为它压根儿就不加锁，所以根本谈不上什么隔离效果，可以理解为没有隔离。

REPEATABLE READ & READ COMMITTED：

为了解决不可重复读，MySQL 采用了 MVVC (多版本并发控制) 的方式。

我们在数据库表中看到的一行记录可能实际上有多个版本，每个版本的记录除了有数据本身外，还要有一个表示版本的字段，记为 row trx_id，而这个字段就是使其产生的事务的 id，事务 ID 记为 transaction id，它在事务开始的时候向事务系统申请，按时间先后顺序递增。

SERIALIZABLE：

读的时候加共享锁，其他事务可以并发读，但是不能写。写的时候加排它锁，其他事务不能并发写也不能并发读。

MySQL主从同步是如何实现的？

步骤、延时问题、原理（三个线程）、主从库数量

主从同步实现步骤： 

1. 主服务器把数据更改记录到二进制日志（binlog，记录改不记录读，用于数据复制和数据恢复）中；
2. 从服务器异步近似实时地把主服务器的二进制日志复制到自己的中继日志（relay log）中；
3. 从服务器重做中继日志中的操作，把更改应用到自己的数据库上，以达到数据的最终一致性。

复制最大的问题是主服务器压力大导致的复制延时问题。

复制原理： 

• 主库二进制日志转储线程：负责将二进制日志发给从库。强制从主库读取数据的时，会给二进制日志加锁 ，读完解锁。
• 从库I/O 线程：负责连接主库，并向主库发送请求和复制二进制日志到中继日志。
• 从库SQL 线程：负责读取并执行中继日志中的更新语句，实现主从同步。 

主从库数量： 

• 每个 Master 可以有多个 Slave
• 每个 Slave 只能有一个唯一的服务器ID，只有一个 Master。

 

标准答案

复制（replication）是MySQL数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用。总体来说，replication的工作原理分为以下3个步骤：

1.主服务器（master）把数据更改记录到二进制日志（binlog）中。

2.从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。

3.从服务器重做中继日志中的事件，把更改应用到自己的数据库上，以达到数据的最终一致性。

复制的工作原理并不复杂，其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是，复制不是完全实时地进行同步，而是异步实时。这中间存在主从服务器之间的执行延时，如果主服务器的压力很大，则可能导致主从服务器延时较大。

MySQL调优

得分点：观察、调优

关键字：观察、 查看状态、慢查询日志、定位慢sql、执行成本、执行计划。调优、缓存、mysql参数、定时清理数据、存储引擎、索引、表结构、分表

观察：查看状态、开启并分析慢查询日志、定位慢sql语句，查询sql执行成本、分析sql执行计划

1. 查看状态，如sql查询成本、连接次数、慢查询次数等性能参数；

   show status like '参数';

2. 开启慢查询日志，修改慢查询阈值，mysqldumpslow分析慢查询日志里查询慢的语句；

   set slow_query_log='ON';    #开启慢查询日志
   set long_query_time = 1;     #设置慢查询阈值

   #命令行，按照查询时间排序，查看前五条 慢查询SQL 语句
   mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log    

3. 定位慢sql语句，查询sql执行成本。开启并使用show profiles和show profile查询sql语句列表和指定sql语句执行成本（持续时间、cpu、io情况）；
4. explan分析sql执行计划（访问类型、记录条数、索引长度等）；

调优：缓存、mysql参数、定时清理数据、存储引擎、索引、表结构、分表

1. 缓存优化。mysql调整缓冲池大小等参数；引入redis。InnoDB使用缓冲池缓存记录和索引
2. 配置MySQL参数。关闭不必要的服务和日志（调优结束关闭慢查询日志），调整最大连接数。
3. 定时清理数据。对于不再使用的表、数据、日志、缓存等，应该及时清理，避免占用过多的MySQL资源，从而提高MySQL的性能。
4. 使用合适的存储引擎。MyISAM比较适合读取频繁，写入较少的场景（因为表级锁、B+树叶存地址），而InnoDB比较适合并发写入的场景（因为行级锁、B+树叶存记录）。
5. 索引优化。在频繁查询（特别是分组、范围查询）的列、唯一特性的列上建立索引，避免创建过多索引、联合索引把频繁查询的列放左边。
6. 表结构优化。遵循第一范式（每个属性不可再分），第二范式（表必须有一个主键），第三范式（非主键列必须直接依赖于主键），避免无限制的增加列，尽量使用枚举类型。
7. 分表。将大表分成较小的表，局限范围，在大量查询时优化性能。

MySQL的缓冲池被分为多个不同的缓存池，其中包括：

• 查询缓存：用来缓存查询结果。
• InnoDB缓存池：用来缓存热点表和索引数据页。
• MyISAM缓存池：用来缓存表数据块。

MySQL的缓冲池使用的是LRU（最近最少使用）淘汰策略，它会优先缓存最近使用的数据。当缓冲池的空间不足时，MySQL会将最不常用的数据从缓冲池中替换出去，以腾出空间缓存新的数据。

MySQL的缓存设置包括多个参数，其中比较常见的缓存参数包括以下几个：

1. key_buffer_size：该参数用来设置MyISAM索引的缓存大小。如果应用程序中涉及到大量的索引查询，可以适当提高该值。一般来说，key_buffer_size占用总内存的1/4到1/3比较合适。

2. innodb_buffer_pool_size：该参数用来设置InnoDB缓冲池的大小。InnoDB存储引擎使用缓冲池来缓存数据和索引文件。如果InnoDB表的读写频次较高，建议将该值设置为物理内存的70%到80%。

3. sort_buffer_size：该参数用来设置排序缓冲区大小。如果查询中涉及到ORDER BY或GROUP BY操作，可以适当提高该值。一般来说，sort_buffer_size占用总内存的1/4到1/3比较合适。

4. read_buffer_size和read_rnd_buffer_size：这两个参数是用来设置读取缓冲区大小的，默认值为128 KB。如果应用程序中经常进行大文件的读取操作，可以适当提高这两个参数。

5. binlog_cache_size：该参数是用来设置二进制日志的缓存大小。如果应用程序中需要持久化一些数据，可以开启二进制日志，并适当调整该参数。

总的来说，MySQL的缓存设置需要根据应用程序的实际情况进行调整，例如根据读写频次调整InnoDB缓冲池大小，根据查询特点调整sort_buffer_size大小等。合理的缓存设置可以大大提高MySQL的性能和稳定性。

配置MySQL参数

• max_connections是MySQL的一个系统参数，它用来限制同一时刻可以与MySQL服务器建立的最大连接数。默认情况下，max_connections的值是100，可以通过设置my.cnf文件或动态修改来调整该值。如果应用程序需要处理大量的请求，可以适当提高max_connections的值。但是，需要注意的是，随着max_connections的增加，MySQL服务器需要消耗更多的内存、CPU资源以及线程资源。如果过多的连接数，不仅会降低系统的响应速度，还可能导致系统崩溃或者出现僵尸进程。
• innodb_flush_log_at_trx_commit是MySQL InnoDB存储引擎的一个重要参数，用来控制事务提交时的日志写入行为。该参数的默认值为1，表示每次提交事务都会立即将事务日志写入磁盘。在这种模式下，可以保证事务的持久化和一致性，但同时会降低系统性能，特别是在高并发写入的场景下。