Java后端高频知识点学习笔记6---MySQL

Java后端高频知识点学习笔记6---MySQL

参考地址：牛_客_网
https://www.nowcoder.com/discuss/819308

1、MyISAM和InnoDB的区别

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，⽽且提供了⼤量的特性，包括全⽂索引、压缩、空间函数等，但MyISAM不⽀持事务和⾏级锁，⽽且最⼤的缺陷就是崩溃后⽆法安全恢复。不过，5.5版本之后，MySQL引⼊了 InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为 InnoDB

⼤多数时候我们使⽤的都是 InnoDB 存储引擎，但是在某些情况下使⽤ MyISAM 也是合适的，例如：读密集的情况下。（如果你不介意 MyISAM 崩溃恢复问题的话）

两者的对比：

① 是否支持行级锁：

• MyISAM只有表级锁（table-level locking）
• InnoDB支持行级锁（row-level locking）和表级锁，默认为行级锁

② 是否支持事务和崩溃后的安全恢复：

• MyISAM强调的是性能，每次查询具有原子性，其执行速度比InnoDB类型更快，但是不提供事务支持；
• InnoDB提供事务支持，外部键等高级数据库功能，具有事务（commit）、回滚（rollback）和崩溃修复能力（crash recovery capabilities）

③ 是否支持外键：

• MyISAM不⽀持
• InnoDB⽀持

④ 是否支持MVCC(Multiversion Concurrency Control，多版本并发控制)：

• 仅InnoDB支持；应对高并发事务，MVCC比单纯的加锁更高效；MVCC只在READ COMMITTED 和 REPEATABLE READ两个隔离级别下工作；MVCC可以使用乐观锁(optimistic)和悲观锁(pessimistic)来实现；各数据库中MVCC实现并不统一

2、索引

InnoDB存储引擎支持B+树索引、全文索引、哈希索引

MySQL索引使⽤的 数据结构 主要有 B+树索引 和 哈希索引；对于哈希索引来说，底层的数据结构就是哈希表，因此在需要为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余⼤部分场景，建议选择B+树索引

MySQL的B树索引使⽤的是B树中的B+Tree（B+树），但对于主要的两种存储引擎的实现⽅式是不同的

• MyISAM: B+树叶节点的data域存放的是数据记录的地址；在索引检索的时候，⾸先按照B+树搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录；这被称为“⾮聚簇索引”

• InnoDB：其数据⽂件本身就是索引⽂件；相⽐MyISAM，索引⽂件和数据⽂件是分离的，其表数据⽂件本身就是按B+Tree组织的⼀个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据⽂件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。⽽其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值⽽不是地址，这也是和MyISAM不同的地⽅。在根据主索引搜索时，直接找到 key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再⾛⼀遍主索引；因此，在设计表的时候，不建议使⽤过⻓的字段作为主键，也不建议使⽤⾮单调的字段作为主键，这样会造成主索引频繁分裂

3、B树、B+树、红黑树

B-Tree（B树）：B树是一棵多路平衡查找树，B树的每个节点都存储索引和数据（key和data）

对于一个m阶的B树：

1、每个节点最多有m-1个关键字（可以存有的键值对），根节点最少可以只有1个关键字，非根节点最少有m/2个关键字

关键字范围：根节点[1,m-1] 非根节点[m/2,m-1]

2、每个节点中的关键字都按照从小到大的顺序排列

3、所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同

4、每个节点都存有索引和数据，也就是对应的key和value

B+树：B+树也是一棵多路平衡查找树；查询的时间复杂度O(log n)

只有叶子节点存储data，叶子节点包含了这棵树的所有数据，所有的叶子节点使用链表相连，便于区间查找和遍历，所有的非叶子节点起到了索引作用

对于一个 m 阶的 B+ 树：

1、关键字范围：根节点[1,m-1] 非根节点[m/2,m-1]

2、B+树中非叶子节点不存储数据，只存储索引，数据都存储在叶子节点中

3、对于非叶子节点中key都按照从小到大的顺序排列，非叶子节点中的每一个key，都会出现在子节点中，是子节点中最大或最小元素

叶子节点中的记录也按照key从小到大排列

4、叶子节点依据关键字的大小从小到大顺序链接，形成一个有序链表

5、每个结点至多有m个子女；除根结点外，每个结点至少有[m/2]个子女，根结点至少有两个子女；有k个子女的结点必有k个关键字

6、所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同

红黑树：

简介：一种二叉查找树；但在每个节点增加一个存储位表示节点的颜色，可以是red或black；通过对任何一条从根到叶子的路径上各个节点着色的方式的限制，红黑树确保没有一条路径会比其它路径长出两倍；它是一种弱平衡二叉树（由于是若平衡，可以推出，相同的节点情况下，AVL树的高度低于红黑树），相对于要求严格的AVL树来说，它的旋转次数变少，所以对于搜索，插入，删除操作多的情况下，我们就用红黑树

性质

• 每个节点非红即黑
• 根节点是黑的
• 每个叶节点(叶节点即树尾端NUL指针或NULL节点)都是黑的
• 如果一个节点是红的,那么它的两儿子都是黑的
• 对于任意节点而言,其到叶子点树NIL指针的每条路径都包含相同数目的黑节点

4、MySQL为什么使用B+树作为索引

① B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了

② B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路；所有关键字查询的路径长度相同，导致每一个数据的查询效率相当

③ B+树更便于遍历：由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引

B+叶子节点依据关键字的大小从小到大顺序链接，形成一个有序链表

B+树更适合基于范围的查询：B树在提高了IO性能的同时并没有解决元素遍历时效率低下的问题，正是为了解决这个问题，B+树应运而生；B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低

5、聚簇索引和非聚簇索引的区别

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

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

6、什么是联合索引

联合索引是指对表上的多个列进行索引，联合索引的创建方法与单个索引创建的方法一样，不同之处仅在于有多个索引列。从本质上来说，联合索引还是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2，参考下图；另外，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，所以使用联合索引时遵循最左前缀集合

7、MySQL索引有什么优缺点

索引的优点
1、创建唯一性索引，保证数据库表中每一行数据的唯一性
2、大大加快数据的检索速度，这也是创建索引的最主要的原因
3、加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
4、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
5、通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能

索引的缺点
1、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
2、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
3、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

8、什么是事务

数据库事务(transaction)是数据库中的一组操作，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位

9、事务的四大特性（ACID）

① 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。

② 一致性（Consistency）：事务将数据库从一种状态转变为下一种一致性状态。在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏。

③ 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。

④ 持久性（Durability）：一个事务被提交之后；它对数据库中数据的改变是永久性的，即使数据库发生故障也不应该对其又任何影响

10、脏读、不可重复读、幻读

① 脏读（Dirty read）：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个被修改后的数据；因为这个数据是还没有提交的数据，那么另外⼀个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的

② 不可重复读（Unrepeatableread）: 指在⼀个事务内多次读同⼀数据；在这个事务还没有结束时，另⼀个事务也访问该数据；那么，在第⼀个事务中的两次读数据之间，由于第⼆个事务的修改导致第⼀个事务两次读取的数据可能不太⼀样；这就发⽣了在⼀个事务内两次读到的同一数据是不⼀样的情况，因此称为不可重复读

③ 幻读 （Phantom read）:幻读与不可重复读类似。它发⽣在⼀个事务（T1）读取了⼏⾏数据，接着另⼀个并发事务（T2）插⼊了⼀些数据时；在随后的查询中，第⼀个事务（T1）就会发现多了⼀些原本不存在的记录，就好像发⽣了幻觉⼀样，所以称为幻读

11、事务的隔离级别

① READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读

② READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生

③ REPEATABLE-READ(可重复读)：（默认）对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生

④ SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别；所有的事务依此逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读

MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重复读）

12、当前读和快照读

当前读：读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录

• 例如，假设要update一条记录，但是另一个事务已经delete这条数据并且commit了，如果不加锁就会产生冲突；所以update的时候肯定要是当前读，得到最新的信息并且锁定相应的记录

快照读：读取的是记录数据的可见版本（可能是过期的数据），不用加锁

快照读：普通的select
当前读：update、insert、delete及 加锁的select

快照读是基于 MVCC 和 undo log 来实现的，适用于简单 select 语句

当前读是基于Next-key Lock（行锁 + 间歇锁）来实现的，适用于 insert，update，delete， select ... for update， select ... lock in share mode 语句，以及加锁了的 select 语句

对于更新数据不能使用快照读，因为更新数据时如果使用快照读会可能会覆盖其他事务的更改

13、MySQL中的锁

行级锁

• 共享锁（S锁）：又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁(共享锁)，而不能加X锁(排它锁)，直到事务T释放A上的S锁；这保证了其他事务可以读A，但在事务T释放A上的S锁(共享锁)之前不能对A做任何修改

• 排它锁（X锁）：又称写锁。若事务T对数据对象A加上X锁(排它锁)，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的X锁(排它锁)；这保证了其他事务在T释放A上的X锁(排它锁)之前不能再读取和修改A

• • MySql InnoDB行锁的加锁机制
    1、InnoDB表是索引组织的表，主键是聚集索引，非主键索引都包含主键信息
    2、InnoDB默认是行级锁
    3、InnoDB行级锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表级锁

表级锁

• 意向锁设计目的主要是为了在一个事务中揭示下一行将被请求的锁的类型
  意向共享锁：(IS锁) 表明事务准备给数据行添加一个共享锁，一个数据行在被添加共享锁之前首先需要获取当前表的意向共享锁
  意向排它锁：(IX锁) 表明事务准备给数据行添加一个排它锁，一个数据行在被添加排它锁之前首先需要获取当前表的意向排它锁
  意向共享锁和意向排它锁是数据库主动加的，不需要我们手动处理

总结：
InnoDB有三种行锁的算法：行锁（Record Lock）、间隙锁（Gap Lock）、临键锁（Next-Key Lock）
1、行锁（Record Lock）：单个行记录上的锁
2、间隙锁（Gap Lock）：锁定一个范围，防止新增行插入；GAP锁(间隙锁)的目的，是为了防止同一事务的两次当前读，出现幻读的情况
3、临键锁（Next-Key Lock）：行锁 + 间隙锁，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题

14、bin log（二进制日志）、redo log（重做日志）、undo log（撤消日志）

1、bin log
bin log：二进制日志文件；bin log记录了MySQL所有修改数据库的操作，然后以二进制的形式记录在日志文件中，其中还包括每条语句所执行的时间和所消耗的资源，以及相关的事务信息

2、redo log
redo log：重做日志；redo log用来实现事务的持久性，即事务的ACID中的D；它由两部分组成：一是内存中的重做日志缓冲（redo log buffer），它是易失的；二是重做日志文件（redo log file），它是持久的

• mysql每执行一条 DML 语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file；这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术
  InnoDB是事务的存储引擎，它通过Force Log at Commit机制实现事务的持久性，即当事务提交（COMMIT）时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。这里的日志是指重做日志，在InnoDB存储引擎中，由两部分组成，即redo log和undo log。

redo log用来保证事务的持久性，undo log用来帮助事务回滚及MVCC的功能；redo log基本上都是顺序写的，在数据库运行时不需要对redo log的文件进行读取操作；而undo log是需要进行随机读写的

3、undo log

undo log，撤消日志；用来帮助事务回滚及MVCC的功能；原子性底层就是通过undo log实现的

redo log记录了事务的行为，可以很好地通过其对页进行“重做”操作；但是事务有时还需要进行回滚操作，这时就需要undo log（撤销日志）；因此在对数据库进行修改时，InnoDB存储引擎不但会产生redo log，还会产生一定量的undo log。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用undo log的信息将数据回滚到修改之前的样子

redo log存放在重做日志文件中；undo log存放在数据库内部的一个特殊段（segment）中，这个段称为undo段（undo segment），undo段位于共享表空间内

除了“回滚”操作，undo的另一个作用是MVCC，即在InnnoDB存储引擎中MVCC的实现是通过undo来完成；当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo log(撤销日志)读取之前的行版本信息，以此实现非锁定读取

undo log会产生redo log，也就是undo log的产生会伴随着redo log的产生，因为undo log也需要持久性的保护

15、数据库三大范式

1、第一范式（1NF）：列不可再分
2、第二范式（2NF）：非主属性完全依赖于主键
3、第三范式（3NF）：属性不依赖于其它非主属性；属性直接依赖于主键，不是间接