数据库隔离级别、事务

1.主键 超键 候选键 外键

主键：唯一标识一条记录，不能有重复的，不允许为空。
超键：也是唯一标识，但是可以包含其他非主键字段，所以其包含主键。
候选键：也是唯一标识，但是是最小的超键，即不包含无关的属性。
外键：一个表中存在的另一个表的主键是这个表的外键。

如：表（学号、姓名、身份证）
学号：主键、超键
身份证：候选键、主键、超键
学号+姓名：超键
关系：超键>候选键>主键

---

学习B站up【沉默的反补】视频:【MySQL】事务与隔离级别（99%的人存在误区）_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV16J411m7qi?spm_id_from=333.999.0.0

1 事务

事务只是一个改变，是一些操作的集合；用专业的术语讲，它就是一个程序的执行单元；事务本身其实并不包含这4个特性。我们需要通过某些手段，尽可能地让这个执行单元满足这四个特性，那么，我们就可以它为一个事务，或者说一个正确地事务，完美的事务。

2 四特性（ACID）

• 原子性：满足原子操作的单元，对数据的操作，要么全部执行，要么全部失败。
• 一致性：事务开始和完成，数据都必须保持一致。
• 隔离性：事务之间是相互独立的，中间状态对外不可见。
• 持久性：数据的修改是永久的。

3 隔离级别

3.1 并发情况下事务引发的隔离级别

         | 一般情况下，多个单元操作并发执行，会出现这么几个问题

• 脏读：A事务还未提交，B事务就读到了A事务的结果。（破坏了隔离性）
• 不可重复读：A事务在本次事务中，对自己未操作过的数据，进行了多次读取，结果出现了不一致或记录不存在的情况。（破坏了一致性，update 和 delete）
• 幻读：A事务在本次事务中，对自己未操作过的数据，进行了多次读取，第一次读取时，记录不存在，第二次读取，记录出现了。（破坏了一致性，insert）

3.2 解决（制定标准）

为了权衡“隔离”和“并发”的矛盾，ISO定义了四个事务的隔离级别，每个级别的隔离程度不同，出现的副作用也不同。

• 未提交读（read-uncommitted）：最低级别，只能保证持久性；
• 已提交读（read-committed）：语句级别；
• 可重复读（repeatable-read）：事务级别；
• 串行化（serializable）：最高级别，事务与事务完全串行化执行，毫无并发可言，性能极低。

事务隔离级别	脏读	不可重复读	幻读
未提交读（read-uncommitted）	会	会	会
已提交读（read-committed）	-	会	会
可重复读（repeatable-read）	-	-	会
串行化（serializable）	-	-	-

注意： 这四个级别只是一个标准，各个数据库厂商，并不是完全按照这个标准来做的。

3.2 实现（InnoDB）

• 锁机制：阻止其他事务对数据进行操作，各个隔离级别主要体现在读取数据时加的锁和释放时机。

1. 未提交读RU：事务读取时，不加锁；
2. 已提交读RC: 事务读取时加 行级共享锁 (读到才加锁), 读完,立即释放(并不是事务结束）
3. 可重复读RR：事务读取时 加 行级共享锁，直到事务结束才会释放；
4. 串行化SE：事务读取时 加 表级共享锁。直到事务结束时，才会释放。

• MVCC机制

       | multiple version concurrent control 是一种控制并发的方法，主要用来提高数据库的并发性能

        实际上就是 CAS版本控制 和 读写分离 的思想；

        主要作用于RC和RR级别。

4 存储引擎（MyISAM, InnoDB）

存储引擎是修饰数据库【表】的。

	InnoDB	MyISAM
外键	支持	不支持
事务	支持	不支持
锁	支持 表锁 和 行锁	支持 表锁
可恢复性	根据事务日志进行恢复	无事务日志
表结构	数据和索引是集中存储的，.ibd和.frm	数据和索引是分开存储的，数据 .MYD, 索引 .MYI
索引	聚簇索引 支持全文索引	非聚簇索引 不支持全文索引
查询性能

5 三大范式

• 第一范式：确保每列保持原子性，数据表中的所有字段值都是不可分解的原子值。
• 第二范式：确保表中的每列都和主键相关
• 第三范式：确保每列都和主键列直接相关而不是间接相关

6 索引

索引是排好序的数据结构，使用索引可以快速访问数据表中的特定信息

优点：

• 提高数据检索的速度
• 降低数据排序的成本
• 加速表与表之间的连接
• 将随机I/O变成顺序I/O ？

缺点：

• 从空间角度考虑，建立索引需要占用物理空间
• 从时间角度 考虑，创建和维护索引都需要花费时间，例如对数据进行增删改的时候都需要维护索引。

6.1 索引数据结构：

  索引数据结构: BTree索引，Hash索引，full-text全文索引，R-Tree索引(空间数据索引)。

 InnoDB引擎的索引类型有B+树索引，哈希索引，全文索引，默认的索引类型为B+树索引。

6.11 hash索引

hash索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引通过哈希算法进行哈希计算得到哈希码，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值，一般多用于精确查找。

6.12 B+树

• 非叶子节点不存储data，只存储索引，可以存放更多的索引；
• 叶子节点包含所有索引字段；
• 叶子节点用指针连接，提高区间访问的性能；

        B+树具有有序性，并且所有的数据都存放在叶子节点，所以查找的效率非常高，并且支持排序和范围查找

6.13 Hash索引和B+树的区别

1. 哈希索引多用于精确的等值查找，B+索引多用于【除了精确的等值查找外】的其他查找；
2. 哈希表不支持范围查找；
3. 哈希表不支持排序；
4. 哈希索引不支持模糊查询 及 多列索引的最左前缀匹配；
5. 哈希表会存在哈希冲突，哈希索引的性能是不稳定的；B+索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

6.13 B树和B+树的区别

• B树中的内部节点和叶子节点均存放键和值；B+树的内部节点只有键，叶子节点存放所有键值
• B+树的叶子节点是相连在一起的，方便顺序检索

6.14 数据库为什么使用 B+树 而不是 B树

1. B树适用于随机检索，而B+树适用于随即检索和顺序检索；
2. B+树的空间利用率更高。B树每个节点要存储键和值，而B+树的内部节点只存储键，这样B+树的一个节点就可以存储更多的索引，从而使树的高度变低，减少了I/O次数，使得数据检索速度更快；
3. B+树的叶子节点都是连接在一起的，所以范围查找，顺序查找更加方便；
4. B+树的性能更稳定。

B树的内部节点可以存储值，所以可以吧一些频繁访问的值放在距离根节点比较近的地方，这样可以提高查询效率。

6.2索引分类

1. 主键索引：数据列不允许重复，不能为NULL，一个表只能有一个主键索引；
2. 组合索引：由多个列值组成的索引；
3. 唯一索引：数据列不允许重复，可以为NULL，索引列的值必须唯一的，如果是组合索引，则列值的组合必须唯一；
4. 全文索引：对文本的内容进行搜索；
5. 普通索引：基本的索引类型，可以为NULL

6.3 聚簇索引 和 非聚簇索引

最主要的区别： 数据和索引是否分开存储  ？

• 聚簇索引：将数据和索引放到一起存储，索引结构的叶子节点保留了数据行；
• 非聚簇索引：将数据和索引分开存储，索引叶子节点存储的是指向数据行的地址。

InnoDB存储引擎中：默认的索引是B+树，主键索引是聚簇索引，在其之上创建的索引是辅助索引，是非聚簇索引。（辅助索引中的叶子节点 存储的 是 主键）

MyISAM存储引擎中：默认的索引是B+树，但主键索引和辅助索引都是 非聚簇索引

聚簇索引可以直接找到数据；

非聚簇索引 先找到 聚簇索引，再通过 聚簇索引 找到 数据。（这个过程 也称为 回表查询）

        非聚簇索引 不一定会进行 回表查询， 索引覆盖 时 就可以避免 回表查询。

6.4 索引使用场景

• 哪些情况下需要创建索引：

       ① 对于中大型表建立索引非常有效，对于非常小的表，全部表扫描速度更快；

       ②  where中常用的列字段

       ③  字段多 且 字段值没有重复 时考虑 唯一索引

       ④  字段多 且 有重复 时考虑 普通索引

       ⑤  多个字段经常被查询的话 考虑 联合索引

• 哪些情况不需要：

        ① 若表的增删改非常多，查询较少，没有必要，维护索引也需要一定代价

        ②  对于超大型的表，建立和维护索引的代价也会变高，可以考虑 分区技术

6.5 联合索引及最左匹配原则

最左匹配原则: 从最左边为起点开始连续匹配, 遇到范围查询(<, >, between, like)会停止匹配

6.6 索引失效

---

MySQL 支持的锁 

| 意向锁 

• 是不存在的，是表级锁，当你要想获取读锁或者写锁的时候，必须获得先获得意向锁。
• 用法：减少锁的扫描，当你想往表上加锁的时候，你之前需要扫描一行行是否有行锁；当有了意向锁，你只需要看这个表是否有意向锁，没有直接加，有，就不能加锁

划分：

1.  从锁的粒度上分MySQL支持的锁为： 表级锁、行级锁（InnoDB）、页级锁（BDB）；        

            表级锁：开销小-加锁快; 不会出现死锁；锁定粒度大, 发生锁冲突概率最高, 并发度最低；

            行级锁：开销大-加锁慢; 会出现死锁; 锁定粒度最小, 发生锁冲突概率最低, 并发度也最高。

            页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之   间，并发度一般。                

      2. 从锁的操作上分： 读锁（共享锁） 和 写锁（排他锁）；

      3. 从实现方式上：乐观锁 和 悲观锁；

使用场景：

1.  修改数据库表结构会自动加 表级锁 - 元数据锁；
2.  更新数据未使用索引，行级锁会上升为表级锁；
3.  更行数据使用索引会使用行级锁；
4.  select ... for update 会使用行级锁。

数据库 InnoDB下锁的分类：

行锁&表锁

只有明确指定主键，才会执行行锁，否则执行表锁；

锁算法（机制）

 1. 行锁算法

• Record Lock（普通行锁 / 记录锁）： 单个行记录上的锁 。 ① 键值在条件范围内 ② 记录存在
• Gap Lock（间隙锁）：对于键值不存在条件范围内，叫做“间隙”（GAP），引擎就会对这个“间隙”加锁，这种机制就是Gap机制；GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。该锁只会在隔离级别是RR或者以上的级别内存在。间隙锁的目的是为了让其他事务无法在间隙中新增数据
• Next-Key Lock（行&间隙 / 临键锁）：它是记录锁和间隙锁的结合，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。next-key锁是InnoDB默认的锁. 

【MySQL】MySQL有几种锁_小七的博客-CSDN博客_mysql的锁有几种

———————————————————————————————————————————

MySQL高频面试题_资源分享_牛客网