登录/注册
fuyao-w
23
0
0
浏览量
粉丝
关注
数据库.markdown
fuyao-w
2020-11-26 15:34:53
17
0

数据库三范式

第一范式(1NF) 强调的是列的原子性,即列不能够再分成其他几列。 考虑这样一个表:【联系人】(姓名,性别,电话) 如果在实际场景中,一个联系人有家庭电话和公司电话,那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列(电话)拆分,即:【联系人】(姓名,性别,家庭电话,公司电话)。1NF 很好辨别,但是 2NF 和 3NF 就容易搞混淆。

说明:在任何一个关系数据库中,第一范式(1NF)是对关系模式的设计基本要求,一般设计中都必须满足第一范式(1NF)。不过有些关系模型中突破了1NF的限制,这种称为非1NF的关系模型。换句话说,是否必须满足1NF的最低要求,主要依赖于所使用的关系模型。

第二范式(2NF) 首先是 1NF,另外包含两部分内容,一是表必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的一部分。

考虑一个订单明细表:【OrderDetail】(OrderID,ProductID,UnitPrice,Discount,Quantity,ProductName)。 因为我们知道在一个订单中可以订购多种产品,所以单单一个 OrderID 是不足以成为主键的,主键应该是(OrderID,ProductID)。显而易见 Discount(折扣),Quantity(数量)完全依赖(取决)于主键(OderID,ProductID),而 UnitPrice,ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。 可以把【OrderDetail】表拆分为【OrderDetail】(OrderID,ProductID,Discount,Quantity)和【Product】(ProductID,UnitPrice,ProductName)来消除原订单表中UnitPrice,ProductName多次重复的情况。

第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的唯一标识。简而言之,第二范式就是在第一范式的基础上属性完全依赖于主键。

第三范式(3NF) 在1NF基础上,任何非主属性不依赖于其它非主属性[在2NF基础上消除传递依赖]。

第三范式(3NF)是第二范式(2NF)的一个子集,即满足第三范式(3NF)必须满足第二范式(2NF)。

首先是 2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况。 考虑一个订单表【Order】(OrderID,OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity)主键是(OrderID)。

其中 OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity 等非主键列都完全依赖于主键(OrderID),所以符合 2NF。不过问题是 CustomerName,CustomerAddr,CustomerCity 直接依赖的是 CustomerID(非主键列),而不是直接依赖于主键,它是通过传递才依赖于主键,所以不符合 3NF。 通过拆分【Order】为【Order】(OrderID,OrderDate,CustomerID)和【Customer】(CustomerID,CustomerName,CustomerAddr,CustomerCity)从而达到 3NF。

第二范式(2NF)和第三范式(3NF)的概念很容易混淆,区分它们的关键点在于,2NF:非主键列是否完全依赖于主键,还是依赖于主键的一部分;3NF:非主键列是直接依赖于主键,还是直接依赖于非主键列。

读写锁

为了解决并发控制问题,数据库通过共享锁(读锁)和排它锁(写锁)来控制。

锁粒度

MySQL提供了多种选择。每种存储引擎都可以实现自己的锁策略和锁粒度。下面介绍两种最重要的锁策略:

表锁:

表锁是MySQL 中最基本的锁策略,并且是开销最小的策略。他会锁住整张表。一个用户在对表进行写操作的时候必须要获得写锁,这回阻塞其他用户对该表的所有读写操作。只在没有写锁的时候,其他读取的用户才能获得读锁,读锁之间是不相互阻塞的。

在特定的场景中,表锁也能有良好的性能。例如,READ LOCAL 表锁支持某些类型的并发写操作。另外,写锁也比读锁具有更高的优先级,因此,一个写锁请求可能插入到读锁请求队列的前面(写锁可以插入到读锁前面,但是读锁不能插入到写锁前面)

尽管存储引擎可以自己管理自己的锁,MySQL 本身还是会提供不同的表锁来实现不同的目的。例如:服务器会为诸如 ALERT TABLE 之类的语句提供表锁,而忽略存储引擎的锁机制。

行级锁:

行级锁可以最大程度的支持并发处理(同时带来的最大的锁开销)。在InnoDB和XtraDB,以及一些其他存储引擎中实现了行级锁。行级锁只在存储引擎层中实现,而MySQL 服务器层没有实现。服务器层完全不了解存储引擎中锁的实现。

事物:

数组库必须的四个特性

  1. 原子性(atomicity):事务包含的所有操作要么全部成功,要么全部失败回滚;成功必须要完全应用到数据库,失败则不能对数据库产生影响
  2. 一致性 (consistency): 事务执行前和执行后必须处于一致性状态 例:用户A和用户B的前加起来一共是5000; 无论AB用户之间是如何相互转换的,事务结束后两个用户的钱加起来还是5000,这就是事务的一致性。
  3. 隔离性 (isolation): 当多个用户并发访问数据库时,数据库为每一个用户开启的事务,不被其他事务的操作所干扰,多个并发事务之间要相互隔离;
  4. 持久性 (durability): 一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便在数据库系统遇到故障的情况下也不会丢失事物的操作。

隔离性:当多个线程都开启事务来操作数据库中的数据时,数据库系统要进行隔离操作,以保证各个线程获取数据的准确性。 不考虑事务的隔离性,会产生的几种问题:

01:脏读

A 在一个事物中,正在访问数据,并且对数据进行了修改,而这种修改还没有提交。但是这期间B读取并使用了这个未提交的数据

例:用户A向用户B转账100元,A通知B查看账户,B发现前确实已到账,而之后无论第二条SQL是否执行,只要该事务不提交,则所有操作都将回滚,当B再次查看账户时就会发现前其实并没有到账。

02:不可重复读

A在一个事物中多次读取数据的过程中,由于B也读取并修改了这个数据,导致了A在同一个事物中读取到的数据不同。

不可重复读是指在一个事务内,多次读取同一个数据,在这个事务还没有结束 ,另一个事务也访问该同一数据,但是由于第二个事务的修改,那么第一个事务两次读取的数据可能不一样,因此称为不可重复读;即同一个事务中原始数据读取不可重复。 注:不可重复读和脏读的区别,脏读是某一个事务读取另一个事务未提交的脏数据; 不可重复读则是同一个事物中多次读取。

03:幻读:

A 对表中的一个结果集进行修改,同时B向A中的结果集添加了一些数据,A提交后就会发现还有没处理的数据。

当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行修改,这种数据涉及到表中的全部数据行,同时,第二个事务也对这个表数据进行修改,这个修改是对表中新增一条数据,那么操作第一个事务的用户发现表中的数据还没有修改的数据行,就好像发生了幻觉一样,这就是发生了幻读。 注:幻读和不可重复读都读取另一条已经提交的事务,所不同的是不可重复读查询的都是同一数据项,而幻读针对的是一批数据整体。

隔离级别 脏读(Dirty Read) 不可重复读(NonRepeatable Read) 幻读(Phantom Read)
未提交读(Read uncommitted) 可能 可能 可能
已提交读(Read committed) 不可能 可能 可能
可重复读(Repeatable read) 不可能 不可能 可能
可串行化(Serializable ) 不可能 不可能 不可能

·读未提交(Read Uncommitted):允许脏读,A 事物能读取到B未提交的事物。

·提已交读(Read Committed):A只能读取到B已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

·可重复读(Repeated Read):可重复读。A在同一事物中读取到的结果相同,不受其他事物的影响。在同一个事务内的查询都是事务开始时刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读

·串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁(Lock in share mode),读写相互都会阻塞。(InnoDB通过间隙锁解决幻读的问题)

死锁:

死锁是指两个或者多个事物在同一资源上相互占用,并请求锁定对方占用的资源,从而导致的恶性循环的现象。当多个事物以不同的顺序锁定资源时,就可能会产生死锁。

为了解决这种问题,数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统,比如InnoDB 存储引擎,越能检测到死锁的循环依赖,并立即返回一个错误。还有一种方式是,当查询的时间超过锁等待的超时时间后放弃锁请求,这种方式通常来说不太好。InnoDB 目前处理死锁的方法是,将持有最少行级排他锁的事物进行回滚(这是相对比较简单的死锁回滚算法)。

锁的行为和顺序是和存储引擎相关的。以同样的顺序执行语句,有些存储引擎会产生死锁,有些则不会。死锁的产生有双重原因:有些是因为真正的数据冲突,这种情况通常很难避免,但有些则是完全由于存储引擎的实现导致的。

死锁发生后之后部分或者完全回滚其中一个事物,才能打破死锁。对于事物型系统这是无法避免的。

显示和隐式锁定

InnoDB 采用的是两阶段锁定协议。在事物过程中,随时都可以执行锁定,锁只有执行COMMIT或者ROLLBACK的时候,才会释放并且所有的锁是在同一时刻被释放。前面的锁定都是隐式锁定,InnoDB 会根据隔离级别在需要的时候自动加锁。

另外,InnoDB也支持通过特定的语句进行显示锁定,这些语句不属于SQL规范:

SELECT ... LOCK IN SHARE MODE

SELECT ... FOR UPDATE

MySQL 也支持LOCK TABLE 和 UNLOCK TABLE 语句,这是在服务器层中实现的,和存储引擎无关。他们也有自己的用途,但并不能替代事物处理。如果需要处理事物,还是需要选择事物型存储引擎。

多版本并发控制

MySQL的大多数事务型的存储引擎实现的都不是简单的行级锁。基于提升并发性的考虑,他们一般都同时实现了多版本并发控制(MVCC)。

可以认为MVCC是行级锁的一个变种,但是他在很多情况下避免的加锁操作,因此开销更低。虽然不同存储引擎的实现可能不同,但大多都实现了非阻塞读等操作,写操作也只锁定必要的行。

MVCC的实现,是通过保存数据在某个节点上的快照来实现的。也就是说,不管需要执行多长时间,每个事物看到的数据都是一致的。根据事物开始的时间不同,每个数据对同一张表,同一个时刻看到的数据可能是不一样的。

前面说到不同的存储引擎的MVCC的实现是不同的,典型的有乐观并发控制和悲观并发控制。下面通过InnoDB的简化版行为来说明MVCC是如何工作的。

InnoDB是一个 多版本的存储引擎:它保存有关已更改行的旧版本的信息,以支持并发和回滚等事务功能 。此信息存储在表空间中称为 回滚段的数据结构中(在Oracle中的类似数据结构之后)。InnoDB 使用回滚段中的信息来执行事务回滚中所需的撤消操作。它还使用该信息构建行的早期版本以进行 一致读取

在内部,InnoDB为数据库中存储的每一行添加三个字段。6字节DB_TRX_ID字段指示插入或更新行的最后一个事务的事务标识符。此外,删除在内部被视为更新,其中行中的特殊位被设置为将其标记为已删除。每行还包含一个DB_ROLL_PTR称为滚动指针的7字节 字段。roll指针指向写入回滚段的撤消日志记录。如果更新了行,则撤消日志记录包含在更新行之前重建行内容所需的信息。一个6字节的DB_ROW_ID字段包含一个行ID,当插入新行时,该行ID会单调增加。如果 InnoDB自动生成聚簇索引,索引包含行ID值。否则,该 DB_ROW_ID列不会出现在任何索引中。

简答的理解 InnoDB的MVCC,是通过在每行记录后面保存两个隐藏列来实现的。这两个列一个保存了行的创建时间,一个保存行的过期时间(或删除时间)。当然存储的不是实际的时间,而是系统版本号。没开始一个新的事物。事物开始时刻的系统版本号会作为事物的版本号,用来和查询到的每行记录的版本号进行比较。下面在 REPEATABLE READ 隔离级别下,MVCC 具体是如何操作的。

SELECT

​ InnoDB 会根据一下两个条件检查每行记录:

a.InnoDB只查找版本早于当前版本的数据行(也就是行系统版本号小于等于事物的系统版本号),这样可以保证事物读取的行,要么是在事物开始前就已经存在的,要么是事物自身插入或者修改过的。

b. 行的删除版本要么未定义,要么大于当前事物版本。这样可以确保事物读取到的行,在事物开始之前未被删除。

​ 只有符合上述两个条件的记录,才能返回作为查询结果。

INSERT

InnoDB 为当前插入的每一行保存当前系统版本号作为行版本号。

DELETE

InnoDB 为删除的每一行保存当前系统版本号作为删除标识。

UPDATE

InnoDB 插入一条新的记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为删除标识。

保存这两个额外的系统版本号,使大多数读操作可以不加锁。这样设计使得读数据操作简单,性能很好,并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间,需要做更多的行检查工作,以及一些额外的维护工作。

MVCC 只在 REPEATALE READ 和 READ COMMITED 两个隔离界别下工作。其他两个级别都和MVCC 不兼容,因为 READ COMMITED 总是读取到最新的数据行,而不是符合当前事物版本的数据行。而SERIALZABLE 则会对所有读取的行都加锁。

暂无评论