5.14上午数据库学习笔记

数据库设计和事务

数据库三大范式（设计规则）

范式：Normal Format，符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度。一个数据库表之间的所有字段之间的联系的合理性。

学生表，班级表

• 范式是离散数学里的概念

• 范式目标是在满足组织和存储的前提下使数据结构冗余最小化

• 范式级别越高，表的级别就越标准

目前数据库应用到的范式有以下几层

• 第一范式：1NF

• 第二范式：2NF

• 第三范式：3NF

除此之外还有BCNF范式,4NF,5NF

一个数据库表设计的是否合理，要从增删改查的角度去考虑，操作是否方便

第一范式(1NF)：确保表中每一列数据的原子性，不可再分！

 

第二范式(2NF)：在满足第一范式的基础上，确保列数据要跟主键关联，不能出现部分依赖。

 

第三范式设计表：在满足第二范式的基础上，保证每一列数据都要跟主键直接关联，不能出现传递依赖。

当数据库到达第三范式的时候，基本上有关数据冗余，数据插入、删除、更新的异常问题得到了解决，这也是一个”合法的”数据库最基本的要求，但是*效率问题*就另当别论了，因为表越多，连接操作就越多，但是连接是一个比较耗资源的操作。

 

 

 在设计数据库的时候，要在满足自己需求的前提下，尽可能的满足三大范式。

表间的关系

需求分析：

• 一对一关系： 一个学号对一个姓名

在设计数据库时如果是一对一关系，直接设计成一张表。（如果在字段非常多的情况下，可以做合理的分表）

• 一对多（多对一）关系：一个老师多个班级 ，一个班级对多个学生，

一个订单包含多个商品。多个商品属于一个订单。

设计时主要是通过外键关联。

• 多对多关系： 学生对课程 ,用户购买商品

设计数据库时，多对多关系，需要一个中间表进行关联！

事务管理

事务是指逻辑上的一组操作，组成这组操作的各个单元，要不全成功要不全失败。即一组sql中哪怕有一条失败也会失败

例子: 转账操作,一个账户减钱，一个账户加钱

默认情况下，mysql的事务是自动提交的，也就是执行了增删改语句之后，数据直接持久化到磁盘上，不能撤销。

但是如果改为手动事务之后，更新过的数据，再没有使用commit提交时，可以通过rollback进行撤回。

数据库事务的原理

如果不写start transaction/begin;commit; 此时事务默认开启，自动提交；

在数据库中 ，事务都是自动提交的。事务的自动提交就是 执行语句完成之后 就立刻持久化到数据库中。

start transaction/begin;开始事务

rollback;回滚事务

commit;提交事务

当我们添加了start transaction/begin;和commit；后 事务的提交就从自动变成手动。因为中途出错，所以导致commit;不执行，也就是说缓冲区中的数据没有持久化的数据库中。

什么是数据库事务

从开启到提交为一个事务。

由此可见，一个事务对应一组业务。一个事务中间可以有一条sql,多条sql。

所以 一个业务开始之前 开启事务 一个业务结束之后 提交事务。

我们这个转账案例：需要几个事务？

可以写成两个事务，但是不合适。因为我们的需求 让张三减的同时让李四加钱。只能写成一个事务。

事务的特征ACID 面试题

一、事务的基本要素（ACID）

​ 事务（Transaction），一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如start transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

在事务开始和结束之间做的所有的事情叫完整事务。

在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。

1、原子性（Atomicity）：

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2、一致性（Consistency）：

​ 事务开始前和结束后，数据库的数据完整性约束没有被破坏，事务前后操作数据是一致的 。比如A向B转账，不可能A扣了钱，B却没收到。

能量守恒。

3、隔离性（Isolation）：

​ 一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

​ 两个事务之间是有隔离级别，隔离级别的不同会导致出现不同的问题。此时产生三种问题：脏读 幻读 不可重复读。

4、持久性（Durability）：

​ 持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

事务的并发问题

数据库事务无非就两种：查询数据的事务（select）、更新数据的事务（update,insert）。

在没有事务隔离控制的时候，多个事务在同一时刻对同一数据的操作可能就会影响到结果，通常有四种情况：

• 两个更新事务同时修改一条数据时，会出现数据丢失，绝对不允许出现

• 一个更新事务更新一条数据时，另一个读取事务读取了还没提交的更新，这种情况下会出现读取到脏数据

• 一个读取事务读取一条数据时，另一个更新事务修改了这条数据，这时就会出现不可重现的读取

• 一个读取事务读取时，另一个插入事务（注意此处时插入）插入了一条新数据，这样就可能多读出一条数据，出现幻读

注意：前三种是对同一条数据的并发操作，对程序的结果可能产生致命影响，尤其是金融等实时性，准确性要求极高的系统，绝不容许这三中情况的出现。

相比第四种情况不会影响数据的真实性，在很多情况下是允许的，如社交论坛等实时性要求不高的系统！

事务的并发问题：

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致

3、A事务查询了某个Id的记录，发现为空，准备插入该编号的记录。此时B事务先做了插入，并做了提交，此时A事务插入同编号的记录时触发了主键重复的异常，对于A事务而言，第一次读的结果就像发生了幻觉。

注意：不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

MySQL事务隔离级别(用于解决事务并发问题)

 对于MySQL的Innodb的默认事务隔离级别是重复读（repeatable read）

Read Uncommitted(未提交读):

在该隔离级别下，并发事务可以读取未提交的数据，可能导致 脏读  不可重复读   幻读 等问题。

• 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读

Read Committed（提交读）：

在该隔离级别下，并发事务只能读取提交过的数据，可以避免脏读问题，但是可能导致 不可重复读   幻读 等问题。

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。

• 这种隔离级别 也导致所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果

Repeatable Read（可重复读）:

在该隔离级别下，在同一事务中多次查询数据都能保证一致，可以避免不可重复读问题，但是可能导致 幻读等问题。

• 这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）

Serializable（可串行化）:

• 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

案例说明

脏读：

事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

场景：公司发工资了，领导把5000元打到Tom的账号上，但是该事务并未提交，而Tom正好去查看账户，发现工资已经到账，账户多了5000元，非常高兴，可是不幸的是，领导发现发给Tom的工资金额不对，是2000元，于是迅速回滚了事务，修改金额后，将事务提交，Tom再次查看账户时发现账户只多了2000元，Tom空欢喜一场，从此郁郁寡欢，走上了不归路…...

不可重复读：

事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

场景：Tom拿着工资卡去消费，酒足饭饱后在收银台买单，服务员告诉他本次消费1000元，Tom将银行卡给服务员，服务员将银行卡插入POS机，POS机读到卡里余额为3000元，就在Tom磨磨蹭蹭输入密码时，他老婆以迅雷不及掩耳盗铃之势把Tom工资卡的3000元转到自己账户并提交了事务，当Tom输完密码并点击“确认”按钮后，POS机检查到Tom的工资卡已经没有钱，扣款失败，Tom十分纳闷，明明卡里有钱，于是怀疑POS有鬼，和收银小姐姐大打出手，300回合之后终因伤势过重而住进ICU，Tom从此郁郁寡欢，从此走上了不归路......

分析：上述情况即为不可重复读，两个并发的事务，“事务A：POS机扣款”、“事务B：Tom的老婆网上转账”，事务A事先读取了数据，事务B紧接了更新数据并提交了事务，而事务A再次读取该数据扣款

幻读：

已知有两个事务A和B，A从一个表中读取了数据，然后B在该表中插入了一些新数据，导致A再次读取同一个表, 就会多出几行，简单地说，一个事务中先后读取一个范围的记录，但每次读取的纪录数不同，称之为幻象读

场景：Tom跟朋友聚餐完毕后开启事务付账，先付了80元烟钱，Tom的老婆正好在家里查账发现Tom仅仅有80元的消费记录，非常吃惊，心想“老公真是太节俭了，嫁给他真好！”，而Tom此时再次支付饭钱1000元，即新增了一条1000元的消费记录并提交了事务，沉浸在幸福中的老婆不敢相信自己的眼睛，又查询了Tom当天工资卡消费明细，一探究竟，可查出的结果竟然发现又多了一笔1000元的消费，Tom的老婆瞬间怒气冲天，外卖订购了一个大号的榴莲，傍晚降临，Tom生活在了水深火热之中，只感到膝盖针扎的痛......

　　小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表