数据库 杂记

悲观锁、乐观锁

悲观锁：这种借助数据库锁机制，在修改数据之前先锁定数据，再修改的方式被称之为悲观并发控制。悲观锁有共享锁和排他锁。

乐观锁：乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。乐观锁适用于读操作多的场景，这样可以提高程序的吞吐量。比如CAS。

第一/二/三范式

1NF 第一范式是指数据库的每一列都是不可分割的基本数据项，强调列的原子性，实例中某一属性不能拥有几个值。比如数据库的电话号码属性里面不可以有固定电话和移动电话值。
说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

2NF 第二范式建立在第一范式的基础上，即满足第二范式一定满足第一范式，第二范式要求数据表每一个实例或者行必须被唯一标识。除满足第一范式外还有两个条件，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。

举例来说：当数据表中是联合主键，但是有的列只依赖联合主键中的一个或一部分属性组成的联合主键，此时需要拆表才能复合第二范式。

3NF 若某一范式是第二范式，且每一个非主属性都不传递依赖于该范式的候选键，则称为第三范式，即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

举例来说：Employee(emp_id,emp_name,emp_age,dept_id,dept_name,dept_info)，当员工表中emp_id能够唯一确定员工员工信息，但是dept_name可由dept_id唯一确定，此时，该表不符合第三范式，此时可以删除除了dept_id之外的其他部门信息，把所有部门信息单独建立一张部门表。

转自：https://blog.csdn.net/u014458048/article/details/56678698

概括：

1NF：数据库表的每一列都是不可分割的基本数据项。

2NF：在1NF的基础上，每一个非主属性完全依赖于候选码。消除了非主属性对候选码的部分依赖。

3NF：在2NF的基础上，每一个非主属性不传递依赖于候选码。即表中的每一列只与候选码直接相关而不是间接相关。

BCNF：在1NF的基础上，对于任意非平凡依赖X->Y（非平凡依赖指Y不属于X），X必含有候选键。是“修正的3NF”。

4、

Select列名[[,列名]...]
From 表名1,表名2,...
Where检索条件;

多表联合检索结果与对多表的笛卡尔积进行检索相同。做了一些优化。

连接

Select 列名[[,列名]..]
From 表名1 [NATURAL]
		   [INNER |{LEFT | RIGHT FULL}[OUTER]] JOIN 表名2
	{ON 连接条件 | Using(Colname{,Colname...})}
[Where检索条件]...;

连接运算由两部分构成：连接类型和连接条件

连接类型（四选一）	连接条件（三选一）
inner join	natural
left outer join	on <连接条件>
right outer join	using(Col1, Col2, …, Coln)
full outer join

注：MySQL不支持全连接full outer join。

连接类型

Inner Join：即关系代数中的θ-连接运算。

Left Outer Join，Right Outer Join，Full Outer Join：即关系代数中的外连接运算
- 如"表1 Left Outer Join表2"，则连接后，表1的任何元组t会出现在结果表中，如表2中有满足连接条件的元组s，则t与s连接；否则t与空值元组连接；
- 如“表1 Right Outer Join表2"，则连接后，表2的任何元组s都会出现在结果表中，如表1中有满足连接条件的元组t，则t与s连接；否则s与空值元组连接；
- 如"表1 Full Outer Join表2"，是前两者的并。

连接条件

• 连接中使用natural
  出现在结果关系中的两个连接关系的元组在公共属性上取值相等，且公共属性只出现一次。
• 连接中使用on <连接条件>
  出现在结果关系中的两个连接关系的元组取值满足连接条件，且公共属性出现两次。
• 连接中使用 using(Col，Col2，…,Coln)
  (Col，Col2，…,Coln)是两个连接关系的公共属性的子集，元组在(Col，Col2，…,Coln)，取值相等，且(Col，Col2，…,Coln)只出现一次。
  
  

文件在磁盘上的存储方式

以FAT文件系统为例：

一个文件被保存在若干个磁盘块上。

文件系统有一个文件分配表FAT：记录该磁盘块存储的文件的后续磁盘块序号，类似链表。通过FAT将同一个文件的磁盘块串联。

目录中的文件条目记录其起始磁盘块序号。

索引类别

1. 稠密索引和稀疏索引

   • 稠密索引：对于主文件中每一个记录（形成的每一个索引字段值），都有一个索引项和它对应，指明该记录所在位置。
   • 稀疏索引：对于主文件中部分记录（形成的索引字段值），有索引项和它对应，这样的索引称非稠密索引或稀疏索引。
     

2. 主索引：通常是对每一存储块有一个索引项，索引项的总数和存储表所占的存储块数目相同，存储表的每一存储块的第一条记录，又称为锚记录（anchor record），或简称为块锚（block anchor）。属于稀疏索引
   

3. 辅助索引：是定义在主文件的任一或多个非排序字段上的辅助存储结构。属于稠密索引。
   

4. 聚簇索引和非聚簇索引

   • 聚簇索引：是指索引中邻近的记录在主文件中也是临近存储的；
   • 非聚簇索引：是指索引中邻近的记录在主文件中不一定是邻近存储的。
     

5. 倒排索引
   记录每个词条出现在哪些文档，及文档中的位置，可以根据词条快速定位到包含这个词条的文档及出现的位置。
   
   
   

Select查询原理

1. DBMS将查询语句转换成关系代数表达式（比如先做乘积，再做选择，最后做投影）；
2. 对关系代数表达式进行优化（例如改变操作次序），减少运行时间；（逻辑优化）
3. 为每一个关系代数操作选择优化的执行例行程序，形成物理查询计划：
   1. 获取数据库相关信息（定期统计）；
   2. 一个关系代数操作可能有多种例行程序，根据相关信息进行代价估算，选择代价最小的例程及参数；（物理优化）
   3. 形成查询计划。
4. 执行引擎：依物理查询计划调用相应的例行程序进行处理，并返回结果。
   
   

外排序

两阶段多路归并

1. 第一趟：划分子集并子集排序。
   将数据分为多个子集，每个子集大小小于内存大小。使用内存对每一个子集排序。
2. 第二趟：各子集间的归并排序。
   将每个子集的部分（比如一数据块）读进内存，多路归并。

前提：子集合数 * 子集合的块数 < 内存块数²
（子集合数和子集合块数分别小于内存块数）

更大规模数据集排序——多趟/多阶段

内存大小：内存块数3块
待排序数据：共占用数据块30块

基本策略：

1. 30块的数据集=>10个子集合，每个子集合3块，排序并存储。
2. 10个已排序子集合分成5个组：每个组2个子集合，分别进行二路归并，则可得到5个排好序的集合；
3. 5个集合再分成3个组：每个组2个子集，剩余一个单独1组，分别进行二路归并，可得3个排好序的集合；再分组，再归并得到2个排好序的集合；再归并便可完成最终的排序。
   
   

故障恢复

undo/redo（撤销/重做事务）是数据库故障恢复的手段。

undo/redo日志

系统故障可通过运行日志来恢复：
按照运行日志记录的事务操作顺序重做事务（当事务在发生故障时已正确结束）或撤消事务（当事务在发生故障时未结束）。

Undo日志记录某数据被修改前的值，可以用来在事务失败时进行rollback，保证了事务的原子性；

Redo日志记录某数据块被修改后的值，可以用来恢复未写入介质的已成功事务更新的数据，保证了事务的持久性。

检查点checkpoint

检查点是这样的时刻：在该时刻，DBMS强制使内存DB Buffer中的内容与介质DB（date file）中的内容保持一致，即将DB Buffer更新的所有内容写回DB中。

检查点被定时设置，以减少数据库故障恢复的长度。

• 检查点之前结束的事务不需要恢复（已经写回DB）；
• 检查点之后结束或发生的事务需要依据运行日志进行恢复（不能确定是否写回DB）：故障点前结束的重做，故障点时刻未结束的撤消。

隔离性

SQL标准的事务隔离级别包括：

• 读未提交 （read uncommitted） ：一个事务还没提交时，它做的变更就能被别的事务看到。
• 读提交 （read committed） ：一个事务提交之后，它做的变更才会被其他事务看到。
• 可重复读 （repeatable read） ：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据时一致的。当然可重复读隔离级别下，未提交变更对其他事务也是不可见的。
• 可串行化（serializable）： 对同一行记录，当出现不同事物的读写冲突时，是通过串行化的方式解决的， “写”会加“写锁”，“读”会加“读锁” ，后一个事务必须等前一个事务完成才能执行。

MySQL可以选择四种隔离级别，其中innodb使用多版本并发控制mvcc来实现 读提交/可重复读 两种隔离级别。

不同隔离级别能够解决不同的隔离性问题 ：

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

【注】 标准的可重复读隔离性级别，有幻读的可能性。但是，Innodb在可重复读级别下，使用了 next-key lock （行锁 + 间隙锁）解决了幻读问题。 间隙锁就是对值的间隙范围加锁，防止新的记录插入。 https://zhuanlan.zhihu.com/p/109129926

ACID

事务就是一组原子性的SQL查询。事务内的语句，要么全部执行成功，要么全部执行失败。

事务的四个标准特征（ACID） ：

• 原子性（atomicity）一个事务必须被视为一个不可分割的最小工作单元，整个事务中所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作；
• 一致性（consistency）数据库总是从一个一致性的状态转换到另外一个一致性的状态；
• 隔离性（isolation）并发执行的多个事务之间互相不受影响；
• 持久性（durability）一旦事务提交，则其所做的修改就会永久保存到数据库中 。

四个特征不是并非相互正交、没有交集。

WAL可以实现原子性（通过undo log）和持久性（通过redo log），进一步实现一致性。

适当的破坏一致性可以用来提升性能与并行度，例如：最终一致 ~= 读未提交。

快照

快照是数据存储的某一时刻的状态记录。

快照数据库里的数据是不变的。创建快照后，系统会对原数据库的所有数据页做个标识，如果数据页在创建快照后被修改，会复制一个数据页出来，没有修改的数据页则不会有快照(原数据库和快照数据库共用该数据页)。

数据库快照相当于一个新的数据库，保存快照创建时的数据库状态，与原数据库共享没有被修改的数据页。

布隆过滤器（Bloom Filter）

直观的说，bloom算法类似一个hash set，用来判断某个元素（key）是否在某个集合中。
和一般的hash set不同的是，这个算法无需存储key的值，对于每个key，只需要k个比特位，每个存储一个标志，用来判断key是否在集合中。

算法：

1. 首先需要k个hash函数，每个函数可以把key散列成为1个整数
2. 初始化时，需要一个长度为n比特的数组，每个比特位初始化为0
3. 某个key加入集合时，用k个hash函数计算出k个散列值，并把数组中对应的比特位置为1
4. 判断某个key是否在集合时，用k个hash函数计算出k个散列值，并查询数组中对应的比特位，如果所有的比特位都是1，认为在集合中。

优点：不需要存储key，节省空间

缺点：

1. 有可能误判（哈希冲突）
2. 无法删除

应用：
LSM中，可以利用布隆过滤器来快速判断某一SST中是否有某一key。

分表分库

分表：

• 垂直分表：可以把一个宽表的字段按访问频次、是否是大字段的原则拆分为多个表，这样既能使业务清晰，还能提升部分性能。拆分后，尽量从业务角度避免联查，否则性能方面将得不偿失。

• 水平分表：可以把一个表的数据(按数据行)分到多个同一个数据库的多张表中，每个表只有这个表的部分数据，这样做能小幅提升性能，它仅仅作为水平分库的一个补充优化。

分库：

• 垂直分库：可以把多个表按业务耦合松紧归类，分别存放在不同的库，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能，同时能提高整体架构的业务清晰度，不同的业务库可根据自身情况定制优化方案。但是它需要解决跨库带来的所有复杂问题。

• 水平分库：可以把一个表的数据(按数据行)分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题(数据路由问题后边介绍)。

https://blog.csdn.net/weixin_44062339/article/details/100491744