Java后端面试学习知识总结——数据库：MySQL

2020-08-03 10:01:41 浏览数 (1)

文章目录

  • 关系型数据库的主要考点
  • 架构
    • 1.怎么设计一个关系型数据库？
      • 1.1什么是关系型数据库？
      • 1.2关系型数据数据库的组成。
      • 1.3如何设计一个关系型数据库？
      • 1.4索引模块
  • 索引
    • 1.运用二分搜索树来创建索引。
    • 2.运用AVL树和红黑树来创建索引。
    • 3.运用B-Tree来创建索引。
    • 4.运用B 树来创建索引（MySQL的索引结构）。
    • 5.运用Hash来创建索引
    • 6.SQL和NoSQL数据库的索引。
    • 7.密集索引和稀疏索引的区别。
    • 8.MySQL中的索引结构。
    • 9.最左匹配原则
    • 10.如何调优SQL
  • 锁
    • 1.锁的分类。
    • 2.MyISAM与InnoDB在锁上的区别。
    • 3.数据库事务的四大特性。
    • 4.数据库事务的隔离级别以及各级别下并发访问的问题。
    • 5.当前读和快照读，RR级别避免幻读的表象——MVCC。
    • 6.RR级别如何真正避免幻读？（间隙锁）
  • 其他常考点
    • 1.MyISAM和InnoDB分别适用的场景
    • 2.数据库三大范式

关系型数据库的主要考点

  1. 架构
  2. 索引
  3. 锁

架构

1.怎么设计一个关系型数据库？

1.1什么是关系型数据库？

  关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。

1.2关系型数据数据库的组成。

  一个关系型数据库是由两部分组成，一部分是RDBMS，另一部分是存储系统。   RDBMS的全拼是Relational Database Management System，从字面上可以理解为关系数据库管理系统。可以简单理解为为是关系型数据库中的管理系统，也可以认为是一个程序实例，用来跟用户进行交互和数据的操作，将物理存储映射成逻辑结构。   存储系统即文件系统，即将数据以文件的形式持久化存储进硬盘（SSD，机械硬盘）中。

1.3如何设计一个关系型数据库？

  设计一个关系型数据库，其实主要是设计RDBMS。可以将RDBMS拆分成以下几个子模块。   1.存储管理模块：对数据的格式和文件分割进行管理，把物理数据通过逻辑结构标示出来。在这一模块中，还需要对存储效能进行优化，因为磁盘的IO速度很低，不能在磁盘上做数据的处理，需要将数据读取到内存当中进行处理，为了提高效率就需要尽量减少磁盘的IO次数。在进行磁盘IO的时候，读取一行数据和多行数据所消耗的时间是差不多的，所以行就失去了意义。在关系型数据库中，一般将页和块作为逻辑存储单位，块和页中存放多行数据，每次IO加载多个块到内存当中来提高效率。   2.缓存模块：为了更好更快地对数据文件进行操作，需要引入缓存机制，将取出的数据块放入缓存中，下次程序再次使用就可以直接从内存中返回而不是对磁盘进行IO。缓存管理有许多种算法， 有一种思想是一旦某行数据被访问了，那么它附近的数据也极有可能在最近的访问中被访问到。   3.SQL解析模块：在外界对数据库进行操作时，是需要使用SQL语句的，SQL解析模块负责将SQL语句解析编译成机器能识别的语言，并对数据进行操作。如果想要提升SQL效率，可以将SQL语句编译之后存到缓存当中，方便下次使用的时候直接解析即可。   4.日志管理模块：对数据库的操作需要记录下来，方便做数据库的主从同步和数据恢复。   5.权限划分模块：需要给用户数据管理的私密空间，按照权限划分，给不同用户不同的操作级别。   6.容灾机制模块：数据库挂了需要恢复，怎么恢复，恢复到什么程度，需要设计。   7.索引模块：为了提升查询数据的速度，需要引入索引模块。   8.锁管理模块：为了让数据库支持并发，需要引入锁管理模块。

1.4索引模块

  1.4.1 为什么要使用索引？   如果说没有索引的话，我们从数据库中查询数据需要对数据库进行全表扫描来查询所需的数据，将整张表的数据全部或者分批次加载到内存当中，找到我们需要的数据并返回。这种方式非常的慢，数据量小还可以，数据量大之后就不能用了，所以我们需要避免全表扫描的情况，引入索引机制。   索引的灵感来自于字典，在字典的检索目录中，我们将文字的关键信息组织起来，比如偏旁和部首，查询的时候根据偏旁或者部首找到对应的页码，就快速找到了我们想要的数据。数据库也一样，将关键信息放在索引中，快速找到数据所在的内存地址来获取数据。   1.4.2 什么样的信息适合作为索引？   能把该记录限定到一定范围内的字段，就适合用来作为索引，比如主键、唯一建和其他普通键都可以。索引的设置也需要进行相应的考虑，不是所有的字段作为索引都很高效。   1.4.3 索引的数据结构选择   有了关键字还不足以成为一个索引系统，关键是利用什么样的逻辑结构将关键字组织起来，让我们的检索变得高效。这就需要选择一种合适的数据结构了。具体的数据结构论述将在章节2中进行阐述。

索引

1.运用二分搜索树来创建索引。

  二分搜索树是一种常用的树状数据结构，又称为BST。在树形态比较平衡的情况下，BST的搜索效率可以达到O(logN)级别，搜索的时间和树的层数成正比。所以我们可以利用BST来为数据库建立索引模块。   具体的实现就是，可以将索引的关键字信息挂到BST上，根据大小关系在BST中进行搜索，BST的每个节点存储着关键字对应数据的物理内存地址，搜索到所需关键字之后，根据指针去内存中拿到整个数据。也可以将数据和索引一起挂在BST的节点上，直接返回即可。   BST虽然简单，查询效率也高，但是有一个致命的问题是BST有可能会退化成一条链表，所有的节点都挂在根结点的右子树或者全部挂在左子树上，此时查询效率就会退化成O(N)级别，和全表扫描一样了。所以我们需要更稳定的索引结构，此时AVL树和红黑树就跳进了我们的脑海。

2.运用AVL树和红黑树来创建索引。

  AVL树是一种平衡二叉搜索树，通过旋转的操作来保持BST的左右子树高度差不超过1，不会出现极端的链表化的情况。红黑树也是对BST的另外一种变种数据结构，在节点上增加了颜色限制，同时也是靠旋转来调整结构。   虽然这两种数据结构都避免了BST的链表化，保证了稳定性，但是还存在着一个巨大的问题，那就是AVL和红黑树的旋转是对整棵树的操作，需要将树整体加载到内存当中，部分加载的情况是无法旋转的，所以如果进行大规模数据存储的时候，内存是装不下整颗树的，此时就会造成频繁的磁盘IO。所以这就是为什么数据库最后没有选择这两种所数据结构的原因。

3.运用B-Tree来创建索引。

  B-Tree有两种写法：B Tree和B-Tree，所以你平时看到的B-树和B树其实是一个东西。B树即平衡多路查找树，其结构如下图：

  在B树中，叶子节点和非叶子节点都存储着关键字信息和数据，查询过程是靠大小比较来进行。比如我们要在树中查数字0029，首先进入根结点发现0029大于根结点的0017，就进入右边的指针指向的节点，发现右子节点的范围是0028-0048，而0029正好在其中，就进入范围中间的指针所指向的节点，找到0029并返回。   一棵 m 阶的 B 树，需要满足下列条件：

  1. 每个节点最多拥有m个子节点且m>=2，空树除外。
  2. 除根节点外每个节点的关键字数量大于等于ceil(m/2)-1，小于等于m-1，非根节点关键字数必须>=2。
  3. 所有叶子节点均在同一层、叶子节点除了包含了关键字和关键字记录的指针外也有指向其子节点的指针只不过其指针地址都为null对应下图最后一层节点的空格子。
  4. 如果一个非叶节点有n个子节点，则该节点的关键字数等于n-1。
  5. 所有节点关键字是按递增次序排列，并遵循左小右大原则。

  在AVL或者红黑树中，插入或者删除后不满足条件需要对树进行旋转。而B树维护自己的平衡状态是依靠分裂以及合并的操作，这种操作并不需要将整棵树一次性加载进内存当中，B树的查找过程是一个顺指针查找节点和节点中查找关键字的交叉过程。   即使B树的查找很方便和稳定，但是有一个缺陷是如果我们需要遍历数据，就需要从根结点开始往下跨层进行遍历，需要不断地在内外存进行数据交互，磁盘的IO次数也会增加，这是比较影响性能的，所以就引出了B树的变种——B 树。

4.运用B 树来创建索引（MySQL的索引结构）。

  B 树是B树的一个升级版，相对于B树来说B 树更充分的利用了节点的空间，让查询速度更加稳定，其速度完全接近于二分法查找。

  1. B 跟B树不同B 树的非叶子节点不保存关键字记录的指针，只进行数据索引，这样使得B 树每个非叶子节点所能保存的关键字大大增加。
  2. B 树叶子节点保存了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样。
  3. B 树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。
  4. 非叶子节点的子节点数=关键字数（来源百度百科）（根据各种资料这里有两种算法的实现方式，另一种为非叶节点的关键字数=子节点数-1（来源维基百科)，虽然他们数据排列结构不一样，但其原理还是一样的Mysql的B 树是用第一种方式实现）。

  B 树的百度百科示意图如下：

  在B树的基础上B 树将所有的数据都放在了叶子节点中，并使用链表进行连接。有以下几个提升：

  1. 由于非叶子节点不存储数据只存储索引，使得B 树的非叶子节点可以保存更多的索引关键字，而在磁盘IO中，为了满足局部性原理，会给每个节点分配一页的存储容量，这就保证了单词IO中可以有更多的检索信息，减少了查找时的磁盘IO次数。
  2. B 树必须查找到叶子节点才能拿到命中数据，而B树在非叶子节点也可能命中数据，所以B树的查询效率没有B 树稳定，而B 树的高度一般都比B树低。
  3. B 树的叶子节点使用链表链接，这样如果我们需要进行遍历或者全表扫描的时候可以直接对叶子节点遍历即可，B树则需要从根结点进行层层遍历。
  4. B 树可以进行区间查找，B树则无法进行区间查找。

5.运用Hash来创建索引

  和HashMap快速获取数据的思想一样，我们可以利用Hash来创建数据库的索引模块，可以将索引关键字进行Hash运算，放进不同的桶中，在桶的后面挂上该索引的数据。

  此时，我们需要查询某个索引关键字，直接找到所在的桶取出即可，其思想和HashMap类似，但是这种索引有很大的局限性：

  1. 仅仅能满足单条数据的查询，比如“=”，“in”，无法进行范围查询。
  2. 无法避免数据的排序操作。
  3. 如果一些关键字无法进行一致的哈希运算，就不能用来当做索引。

6.SQL和NoSQL数据库的索引。

  SQL数据库主要指关系型数据库，比如MySQL，SQL Server等。NoSQL数据库泛指非关系型数据库，比如MongoDB，Redis等。   SQL数据存在特定结构的表中，而NoSQL则更加灵活和可扩展，存储方式可以省是JSON文档、哈希表或者其他方式。这就导致了SQL经常需要进行遍历操作，而NoSQL可以将索引和结果数据聚合在一起。比如在MySQL中，我们有两个表，一个存储学生信息，一个表存储班级，我们想要获取1班中有几个学生，就需要先在班级表中查到1班的id，然后去学生表中遍历班级id为1班的数量。而MongoDB中，可以在一个集合中，将1班的所有同学聚合在班级表中，一次查询直接返回所有学生数即可。   根据之前介绍的B数和B 树的区别，不难想到，NoSQL数据库由于索引和数据聚合在一起，而且一般都是单一查询，所以使用B树作为存储结构更为合适，因为B树就是索引和数据聚合在一起的结构。

7.密集索引和稀疏索引的区别。

  密集索引如下图所示：

  • 密集索引的叶子节点不仅存储了索引值，还包含了该索引其他列的内容。
  • 一个表只能创建一个密集索引，因为物理排列顺序只能有一种，密集索引的索引项和物理地址是顺序对应的。

  稀疏索引如下图所示：

  • 稀疏索引的叶子节点仅保存了索引键位信息以及物理地址。

8.MySQL中的索引结构。

  MySQL中的索引结构和存储引擎有关

  • MyISAM引擎的索引结构。
    • MyISAM引擎中，不管是主键索引还是普通索引都是稀疏索引。
    • MyISAM中，表数据和索引是分开存储的。B 树的叶子结点存储了索引信息和数据地址信息。
  • InnoDB引擎的索引结构。
    • InnoDB引擎有且仅有一个密集索引。
    • InnoDB中主键索引和数据是存储在同一页文件中的，加载叶子节点进入内存时，数据也被加载了进去。
    • InnoDB在进行稀疏索引查询时，首先需要在附注索引中定位到主键信息，再去主键索引中查询该条信息。
  • 二者的索引表分布如下图所示，InnoDB是一种聚簇分布，MyISAM是一种非聚簇分布：
  • InnoDB额外的知识：
    • 若一个主键被定义，该主键则作为密集索引。
    • 若没有主键被定义，该表的第一个唯一非空索引作为密集索引。
    • 若不满足上述条件，InnoDB内部会生成一个隐藏主键作为密集索引。
    • 非主键索引存储相关键位和数据对应的主键值，需要进行两次查找。

9.最左匹配原则

  最左匹配原则都是针对联合索引来说的，所以首先要了解一下联合索引。如下图所示，假设有一个表，有三列：col1, col2, col3，然后创建(col3, col2)联合索引。其表结构和索引结构如下图所示：

  • 可以看出，联合索引其实是根据索引的最左字段来创建索引树的，在这里就是col3，也就是姓名列，而在姓名的基础上，联合索引会根据后续的索引值，对其进行排序。
  • MySQL建立联合索引去查询时，第一位索引是绝对有序的，比如上图，联合索引col3,col2，查询时，B 树会按照col3进行查询，排序，然后查到的内容中再按照col2去排序，所以没有col2可以走联合索引，没有col3就没办法走联合索引。

最左匹配原则：MySQL会从最左索引开始，一直向右匹配索引，知道遇到范围查询（>, <, between, like）就停止匹配。

  1. 比如建立联合索引(a, b, c, d)，那么必须要有a索引，才可能匹配该联合索引，如 a = 3 and b = 4 and c = 5 and d = 6 是完全匹配上该联合索引的，如果没有a索引，如b = 4 and c = 5 and d = 6 是无法使用该联合索引的。这一点从联合索引的介绍中可以发现，建立联合索引去查询时，第一位索引是绝对有序的，如果不提供第一索引位信息，是无法进行后续查询的。
  2. 建立联合索引(a, b, c, d)，如果使用 a = 3 and b = 4 and c > 5 and d = 6 ，其中c索引位使用了范围查询，那么索引匹配只到c为止，d是是用不上索引的，此时索引位为(a, b, c)。其原理也很容易理解，由于d索引是在c索引确定后在其基础上进行排序的，如果c索引是一个范围，那么d索引无法利用索引排序进行查询。
  3. 其中，in和=是可以乱序的，比如b = 3 and a = 4 and c = 5 and d = 6可以匹配联合索引(a, b, c, d)，这是因为MySQL的查询优化器会自动优化成索引可以识别的形式，也就是a = 3 and b = 4 and c = 5 and d = 6 。

10.如何调优SQL

  这个问题是一种经验问题，具体场景需要具体分析，这里可以抽象出大致的优化思路：

  1. 根据慢日志定位查询SQL语句： 1.1 使用set global slow_query_log = on;语句打开慢日志查询。 1.2 使用set global long_query_time = ?;语句设定定义为慢查询SQL的时间，其中?为自定义数字，单位为秒。 1.3 使用show variables like '%quer%';和show status like '%slow_queries%';来查询慢查询的关键信息
  1. 定位到慢查询语句之后，利用explain关键字对其进行分析

2.1 explain的SQL语句并不会真正的运行，只是分析 2.2 explain的结果中，比较关键的两个字段，一个是type，一个是extra。

  1. 分析出SQL语句的问题之后，对SQL语句进行修改与优化，最好使其走索引。

锁

1.锁的分类。

  数据库中锁的分类按照不同的划分方式，可以分为以下几种：

  • 按照锁的粒度划分：可分为表级锁、行级锁和页级锁。
    • 表级锁是锁住整张表，行级锁是锁住影响的数据行，页级锁介于行级锁和表级锁之间，一次锁定相邻的一组记录。
  • 按照锁的级别划分：可分为共享锁、排它锁。
    • 共享锁有些地方也称为读锁，因为数据库读事务会给行或者表上共享锁，此时别的读事务也可以给上了共享锁的数据继续上共享锁，也就是共享锁和共享锁兼容。
    • 排它锁也被称为写锁，因为数据库修改事务会给行或者表上排它锁，此时别的事务无法再操作上了排它锁的数据，因为排它锁和其他的锁互斥，所以也叫互斥锁。
  • 按照加锁方式划分：可分为自动锁、显示锁。
    • 自动锁就是自动上锁，比如在数据库中执行select语句的时候，会自动上共享锁，不需要显示调用。
    • 显示锁就是手动上锁，比如在数据库中执行select语句的时候，可以在语句最后显示加上lock in share mode，或者for update，会分别给数据上共享锁或者排它锁。这是一种显示的上锁方式。
  • 按照操作划分：可分为DML锁、DDL锁。
  • 按照使用方式划分：可分为乐观锁、悲观锁。
    • 乐观锁和Java多线程中的乐观锁思想其实是一样的，在数据库表中建立一个version字段，每次对数据进行修改时，为了防止发生冲突，先对version字段进行检查，再修改，修改成功version 1。这就是乐观锁的实现。
    • 悲观锁其实就是利用数据库内置的排它锁。

2.MyISAM与InnoDB在锁上的区别。

  • MyISAM引擎默认使用的是表级锁，不支持行级锁。
    • MyISAM引擎在对表进行select查询时，会直接给表上一把读锁，也就是共享锁；在对表进行增删改的时候会给表上一把写锁，也就是互斥锁。
    • 当表被读锁锁住的时候，另外的操作允许上读锁，但是上写锁会被block（阻塞）住，直到所有的锁释放为止。
    • 当表被写锁锁住时，不允许另外的操作上任何锁，所有操作都会被阻塞，需要等锁的释放。
    • select操作也可以通过显示的for update后缀操作上写锁。
  • InnoDB引擎默认使用的是行级锁，也支持表级锁。
    • 同MyISAM引擎一样，InnoDB读操作也是上读锁，增删改操作是上写锁。
    • InnoDB中的锁，默认只锁住操作的数据行，未操作的数据行可以被另外的操作上锁。
    • 除了用Id、主键索引以外的其他索引建，InnoDB会对涉及到的所有行上锁。
    • 如果不走索引，InnoDB的锁会变成表级锁，锁住整张表。

3.数据库事务的四大特性。

数据库的四大特性为ACID，分别如下：

  • 原子性（Atomic）：指数据库事务作为一个整体被执行，要么全部执行，要么全部不执行。
  • 一致性（Consistency）：保证数据库状态从一个一致状态转变为另一个一致状态。
  • 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应该影响其他事物的执行。
  • 持久性（Durability）：一个事务一但提交，对数据库的修改应当永久保存。

4.数据库事务的隔离级别以及各级别下并发访问的问题。

MySQL数据库事务的四大隔离级别如下：

  • 读未提交（Read Uncommitted）：该隔离级别下，如果一个事务已经开始写数据，则不允许其他事务同时进行写操作，但允许其他事务读取正在写操作的数据。
    • 读未提交隔离级别下，可能会出现脏读的问题。脏读是指事务A读取了事务B已修改未提交的数据，而事务B随后回滚，就导致事务A读取到了错误的数据，成为脏读。
  • 读已提交（Read Committed）：该隔离级别下，读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其它事务访问正在操作行数据。
    • 读已提交隔离级别下，不会出现脏读问题，但是会出现不可重复读问题。不可重复读是由于读取数据的事务允许其他事务继续访问该行数据，一个事务A在自己没有更新数据库数据的情况下，同一种查询操作执行两次或者多次获取到的结果不一致，因为别的事务B、C…在A查询的同时一直在对数据进行更新，并提交了事务。
  • 可重复读（Repeatable Read）：该隔离级别下，读取数据的事务将会禁止写事务，但允许读事务，写事务则禁止其它任何事务。
    • 可重复读隔离级别下，不会出现脏读和不可重复读问题，但是会出现幻读问题。幻读有很多种解读说法，按照MySQL官方文档中的法，幻读是指读会读到上一次没有返回的数据，看起来像是幻影一般。也就是说事务A读取到了一个结果集，然后事务B向表中新增了一条数据，事务A再次读取结果集就发现数量增加了一条，这就是幻读。
  • 序列化（Serializable）：该隔离级别下，提供严格的事务隔离，将禁止并发执行，要求事务序列化执行，必须一个接一个执行。
    • 序列化隔离级别下，不会出现脏读、不可重复读和幻读问题，因为严格串行执行，虽然可靠性高，但是会降低并发操作的性能。
  • MySQL默认的事务隔离级别是可重复读。

5.当前读和快照读，RR级别避免幻读的表象——MVCC。

上一节介绍中，在RR（可重复读）隔离级别下，会出现幻读的情况，MySQL也考虑到了这种情况并且给出了解决办法来尽量避免幻读现象的产生，那么到底是怎么解决的呢？首先是通过MVCC机制，利用快照读来一定程度上避免幻读。

  • MVCC：多版本控制（Multiversion Concurrency Control），是一种提高并发的技术。MVCC解决幻读的方法就是利用undo log日志保存数据的多个版本，然后利用事务的id来创建快照，同一个事务中，事务只能看到其它事务在创建快照之前已经提交的修改（不管查询几次）和该事物本身提交的修改。
  • 当前读：指读出的数据是数据库中的最新数据，加了锁的增删改查都是当前读，如update，delete，insert和显式加锁的select（select ... lock in share mode，select ... for update）。
    • 为什么update，delete，insert也是当前读呢？明明是写操作啊。以update为例，在其更新前，实际上会有一个当前读的操作，将数据的最新版本读取出来并进行修改，修改后再更新回数据库中。
  • 快照读：指读出的数据是创建快照前的最新数据，普通的查询语句为快照读：select。
  • 快照读的原理
    • 首先，InnoDB存储引擎在数据库的每行数据后面添加了三个隐藏字段：
      • DB_TRX_ID：记录最近一次对本行数据进行修改（写操作）的事务ID。
      • DB_ROLL_PTR：一个回滚指针，指向上一次当前数据行的修改undo log信息。
      • DB_ROW_ID：随着新的数据行插入而单调递增的一个ID信息。（当InnoDB引擎没有指定主键并且没有唯一非空键的时候，就会调用这个ID信息作为自增聚簇索引键，这个字段和快照读的关系不大。）
    • 当数据库进行了修改后，都会被记录在undo日志当中，这是一种老版本日志，包含两种，一种insert undo日志，记录事务的inset操作，只在回滚的时候需要；另外一种是update undo日志，不仅在回滚时需要，快照读也需要用到。
      • RR隔离级别下，事务在开启后，第一次执行快照读，会创建一个快照（Read View），将当前系统中其他活跃的事务记录起来，以备快照读使用，此后该事物之后使用的都是此快照，直到事务结束。
      • RC隔离级别下，事务在开启后，每次执行快照读，快照（Read View）都会被重置，即创建一个新的快照（Read View）。
      • 当事务进行更新的时候，会将操作行的三个隐藏字段进行填充后记录到undo日志当中，如果进行了快照读，那么InnoDB会将该记录行的DB_TRX_ID与Read View中的一些变量进行比较，然后只展现满足条件的数据。
      • 比如事务A开始后，执行普通select语句，创建了快照；之后事务B执行insert语句；然后事务A再执行普通select语句，得到的还是之前B没有insert过的数据，因为这时候A读的数据是符合快照可见性条件的数据。这就防止了部分幻读，此时事务A是快照读。
  • 事务更新undo日志的过程：
    • 第一次修改：
    • 再次修改：
  • 虽然快照读利用MVCC和历史数据部分避免了幻读在一定程度上解决了幻读的问题，但还是不能完全保证不出现幻读，比如A事务在开启快照读之后，B事务进行了写操作插入了一个新行，然后A事务进行了update操作，此时A事务的update操作覆盖到了B事务刚插入的新行，那么A事务再次快照读就会读到这个新行，产生了幻读。

6.RR级别如何真正避免幻读？（间隙锁）

MySQL在RR级别下彻底解决幻读的方法其实还是加锁，RR级别下的当前读，利用了next-key锁，即行锁 gap锁来避免了幻读，加next-key锁需要显示进行调用，即使用select ... lock in share mode，select ... for update语法进行上锁。

  • 行锁在之前已经介绍过了，就是锁住正在操作的数据行。
  • gap锁，又称为间隙锁，即锁住数据行之间的间隙，被锁住的gap无法被插入新的数据，这就避免了幻读。gap锁会用在非唯一索引或者不走索引的当前读中，如下图所示：

在上图所示的表中，我们通过select from tb1 where id = 9 for update这个语句进行当前读的时候，由于id是非唯一键，为了防止幻读就会对如图所示的行间隙加gap锁。在当前事务未结束之前，其他事务不允许向间隙中添加数据。

  • 如果对主键索引或者唯一键索引会使用gap锁么？如果where条件全部命中，就不会使用gap锁，只会加行锁，如下图所示：

此时当我们where id = 3 and id = 4完全命中时，只会加行锁，因为是唯一索引，其他事务无法添加id = 3或者id = 4的新纪录，所以不会出现幻读情况，就没有必要使用gap锁。

  • 如果不走索引，那么Gap锁会锁住表的所有缝隙，这种情况比直接加表锁更降低性能。

其他常考点

1.MyISAM和InnoDB分别适用的场景

  • MyISAM
    • MyISAM引擎会使用一个变量保存表的行数，所以适合频繁执行全表count语句的场景。
    • MyISAM引擎由于是表级锁，所以适用于查询非常频繁，增删改数据频率不高的场景。
    • MyISAM不支持事务，所以适用于对事务无要求的场景。
  • InnoDB
    • InnoDB引擎不会保存表的行数，每次查询全表count都会要去扫描遍历表，虽然全表count效率不如MyISAM，但增删改效率比较高，所以适用于增删改查都相当频繁的场景。
    • InnoDB支持事务，适用于可靠性要求比较高的场景。

2.数据库三大范式

  1. 第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。比如下图的表结构就不满足第一范式，因为列中的数据还可以再分隔：

修改成下表就满足了第一范式：

  1. 第二范式（2NF）：第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。如下图表结构所示，由于一个订单号中可能包含几种产品号，所以主键为订单号和产品号联合组成，但是发现表中的订单金额和订单时间仅和订单号相关，与产品号无关，这就违反了第二范式。

此时需要对表进行拆分，拆分成下面两张表结构就满足了第二范式：

  1. 第三范式（3NF）：在2NF基础上，确保数据表中的每一列数据都和主键直接相关，而不能间接相关。比如下表结构，虽然满足第二范式，所有属性都依赖于学号，但是其中的“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”这一列，此时就违反了第三范式，需要对表进行拆分：

拆分成下面两张表结构，就满足了第三范式：

参考：深入理解索引和AVL树、B-树、B 树的关系 平衡二叉树、B树、B 树、B*树 理解其中一种你就都明白了

数据结构 nosql 数据库 sql 云数据库SQLServer

0 人点赞