数据库基本知识

Reference:
数据库系统原理

关系型数据库和非关系型数据库

简述关系型数据库和非关系型数据库
Oracle，Microsoft SQL Server，MySQL，PostgreSQL，DB2，Microsoft Access， SQLite

关系型数据库：
关系型数据库：指采用了关系模型来组织数据的数据库。
关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。

关系：一张二维表，每个关系都具有一个关系名，也就是表名
元组：二维表中的一行，在数据库中被称为记录
属性：二维表中的一列，在数据库中被称为字段
域：属性的取值范围，也就是数据库中某一列的取值限制
关键字：一组可以唯一标识元组的属性，数据库中常称为主键，由一个或多个列组成
关系模式：指对关系的描述。其格式为：关系名(属性1，属性2， … … ，属性N)，在数据库中成为表结构

优点：
1、易于维护：都是使用表结构，格式一致；
2、使用方便：SQL语言通用，可用于复杂查询；
3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。
缺点：
1、读写性能比较差，尤其是海量数据的高效率读写；
2、固定的表结构，灵活度稍欠；
3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

---

非关系型数据库：
Redis， Amazon DynamoDB， Memcached，Microsoft Azure Cosmos DB和Hazelcast

非关系型数据库：指非关系型的，分布式的，且一般不保证遵循ACID原则的数据存储系统。

优点：
1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式、列式数据库，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
2、速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
3、高扩展性；
4、成本低：nosql数据库部署简单，基本都是开源软件。

缺点：
1、不提供sql支持，学习和使用成本较高；
2、无事务处理；
3、数据结构相对复杂，复杂查询方面稍欠。

---

数据库设计原则

ACID原则

ACID是关系型数据库系统采纳的原则：

1. 原子性（Atomicity）：是指一个事务要么全部执行，要么完全不执行。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

2. 一致性(Consistency): 事务在开始和结束时，应该始终满足一致性约束。所有事务对同一个数据的读取结果都是相同的。
   比如系统要求A+B=100,那么事务如果改变了A的数值，则B的数值也要相应修改来满足这样一致性要求；与CAP中的C代表的含义是不同的。

3. 事务独立（Isolation）：如果有多个事务同时执行，彼此之间不需要知晓对方的存在，而且执行时互不影响，事务之间需要序列化执行，有时间顺序。数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed） 、可重复读（repeatable read）和串行化（Serializable）。

4. 持久性(Durability)：事务的持久性是指事务运行成功以后，对系统状态的更新是永久的，不会无缘无故回滚撤销，即便系统故障也不会丢失。
   

• 只有满足一致性，事务的执行结果才是正确的。
• 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
• 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
• 事务满足持久化是为了能应对系统崩溃的情况。

---

CAP原则

在一个分布式系统中， Consistency（一致性）、 Availability（可用性）、Partition tolerance（分区容错性），三者不可得兼。CAP原则的精髓就是要么AP，要么CP，要么AC，但是不存在CAP。如果在某个分布式系统中数据无副本， 那么系统必然满足强一致性条件， 因为只有独一数据，不会出现数据不一致的情况，此时C和P两要素具备，但是如果系统发生了网络分区状况或者宕机，必然导致某些数据不可以访问，此时可用性条件就不能被满足，即在此情况下获得了CP系统，但是CAP不可同时满足 。

1. 强一致性（Consistency）：即在分布式系统中的同一数据多副本情形下，对于数据的更新操作体现出的效果与只有单份数据是一样的
2. 可用性（Avalilability）：客户端在任何时刻对大规模数据系统的读/写操作都应该保证在限定延时内完成
3. 分区容忍性（ Partition Tolerance）：在大规模分布式数据系统中，网络分区现象，即分区间的机器无法进行网络通信的情况是必然发生的，所以系统应该能够在这种情况下仍然继续工作

---

数据库三大范式

关系型数据库

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

   比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式
   

2. 属性完全依赖于主键
   第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主键

   确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
   
   同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，

   但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，这样就不满足第二范式的要求，调整如下，需分成两个表：
   
   

3. 确保每列都和主键列直接相关,而不是间接相关
   每一列数据都和主键直接相关，而不能间接相关。

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

   比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）
   这样一个表结构，就存在上述关系。 学号–> 所在院校 --> (院校地址，院校电话)
   这样的表结构，我们应该拆开来，如下：
   （学号，姓名，年龄，性别，所在院校）–（所在院校，院校地址，院校电话）

上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，而不是主键“学号”，所以需做如下调整：

---

并发一致性问题

丢失修改
指一个事务的更新操作被另外一个事务的更新操作覆盖。
例如：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改并提交生效，T2 随后修改，T2 的修改覆盖了 T1 的修改。

脏读
指在不同的事务下，当前事务可以读到另外事务未提交的数据。
例如：T1 修改一个数据但未提交，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

不可重复读
在一个事务内多次读取同一数据集合。在这一事务还未结束前，另一事务也访问了该同一数据集合并做了修改，由于第二个事务的修改，第一次事务的两次读取的数据可能不一致。
例如：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

幻影读
T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

产生并发不一致性问题的主要原因是破坏了事务的隔离性 --> 用户手动加锁 OR 数据库管理系统的事务隔离级别

---

封锁

MySQL 中提供了两种封锁粒度：行级锁以及表级锁。
锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。
封锁粒度越小，系统开销就越大。

1. 读写锁

• 互斥锁：X 写锁
  一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁
• 共享锁：S 读锁
  一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁

2. 意向锁
意向锁（Intention Locks）可以更容易地支持多粒度封锁。

意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。

• 一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
• 一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

---

存储引擎

https://www.jianshu.com/p/4bb9f78b4f6d

• InnoDB存储引擎
  InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎。

• MyISAM存储引擎
  它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。

• MEMORY存储引擎
  MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

如何选择不同引擎
InnoDB：如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制
MyISAM：如果数据表主要用来插入和查询记录
MEMORY：临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果

---

事务的隔离级别

• 未提交读（READ UNCOMMITTED）
  事务中的修改，即使没有提交，对其它事务也是可见的。

  读到了另一个事务还未提交的数据 --> 脏读

• 提交读（READ COMMITTED）–> 脏读
  一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。

  事务A对数据的提交影响了事务B的查询结果，一个事务中的不同时刻的相同查询产生了不同结果。每个 select 语句都有自己的一份快照，而不是一个事务一份，所以在不同的时刻，查询出来的数据可能是不一致的。 --> 不可重复读

• 可重复读（REPEATABLE READ） --> 脏读，不可重复读
  保证在同一个事务中多次读取同一数据的结果是一样的。之后读的结果等于第一次读的结果

  由于可重复读保留的是B事务一开始读取的数据值，所以会导致B读到的一开始的数据值覆盖了A在过程中修改的数据值。

• 可串行化（SERIALIZABLE） --> 脏读，不可重复读，幻影读
  强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行。

  它将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束。

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

MySQL事务隔离

• 在 MySQL 中只有使用了Innodb 数据库引擎 的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

https://zhuanlan.zhihu.com/p/117476959

默认隔离级别：可重复读

设置全局隔离级别为读提交级别：

mysql> set global transaction isolation level read committed;

set [作用域] transaction isolation level [事务隔离级别]
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

事务开始于 begin 命令之后的第一条语句执行的时候:

begin;
select * from xxx; 
commit; -- 或者 rollback;

Oracle事务隔离

默认隔离级别：提交读

1. 未提交读
   性能最好，最野蛮的方式，不加锁，所以根本谈不上什么隔离效果，可以理解为没有隔离。
2. 串行化
   读的时候加共享锁，也就是其他事务可以并发读，但是不能写。
   写的时候加排它锁，其他事务不能并发写也不能并发读。

3. MVCC --> 可重复读 & 提交读

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。

版本号
在 MVCC 中事务的修改操作（DELETE、INSERT、UPDATE）会为数据行新增一个版本快照。

数据库表中的一行记录有多个版本，有一个字段来表示版本：trx_id。
- 系统版本号 SYS_ID：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
- 事务版本号 TRX_ID ：事务开始时的系统版本号。

可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读提交则是每次执行语句时候都重新生成一次快照。MVCC 规定只能读取已经提交的快照。

Undo 日志
MVCC 的多版本指的是多个版本的快照，快照存储在 Undo 日志中，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。

ReadView
MVCC 维护了一个 ReadView 结构，主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, …}，还有该列表的最小值 TRX_ID_MIN 和 TRX_ID_MAX。

SELECT 操作时，根据数据行快照的 TRX_ID 与 TRX_ID_MIN 和 TRX_ID_MAX 之间的关系，从而判断数据行快照是否可以使用：

• TRX_ID < TRX_ID_MIN:
  该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。
• TRX_ID > TRX_ID_MAX:
  该数据行快照是在事务启动之后被更改的，因此不可使用。
• TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行判断：
  1. 提交读:
     如果 TRX_ID 在 TRX_IDs 列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。
  2. 可重复读：
     都不可以使用。如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。

快照读与当前读

• 快照读
  MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。
• 当前读
  （INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

4. Next-Key Locks --> 幻影读

Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现。

Next-Key Locks = Record Locks + Gap Locks

• Record Locks
  锁定一个记录上的索引，而不是记录本身。
• Gap Locks
  锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。
  例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)