MySQL事务

1.事务的概念

在现行软件中,多用户,多程序,多线程已经是普遍现象,那么避免不了一张数据表多用户一起在操作。为保持这些用户的数据一致性,提出了事务的概念。

例如:用户A给用户B通过账户进行转钱,用户A转走了,余额-1000,B余额就需要+1000。所以最稳妥的方法是这两个Update作为一个整体来执行。否则用户A-1000,B的+1000,没有出现或者出现晚了都是一种非常棘手的情况。

2.事务的特性

事务存在四大特性

• 持久性:也称永久性,指一个事务一旦提交,它对数据库中数据的改变就是永久。接下来的其它操作或故障不会对其有任何影响。持久性不能完全通过数据库自己解决。
• 隔离性:一个事务的操作是不会被其它事务干扰的。一个事务内部操作及数据对其它事务是隔离的。
• 原子性:一个事务要么都做要么都不做。 比如:A用户给B用户打钱,A用户账户-500,和B账户+500必须是原子的,要不就成功,要不两个账户一个不-,一个不+。
• 一致性:事务必须从一个一致的状态到另一个一致的状态。数据要保持一致。
  比如:A用户给B用户转500块钱,A用户余额-500,B用户的余额不能因为数据库的阻塞就成为+1000,这样数据不一致。

3.事务的隔离级别

• 未提交读:在事务未提交的情况下(存在回滚的可能),其它事务就可以进行读取。可能会发生脏读
• 已提交读:在事务提交后,其它事务才可以进行读取到此次事务更新的数据,可能会发生不可重复读
• 可重复读:无论事务提交前还是提交后,其它事务读取的数据不会改变。比如:事务A修改将400修改为450,事务B在事务A提交前,查询的是400,在事务A提交后,查询的依旧是400。InnoDB引擎默认的隔离级别
• 可串行化:调度是一个或多个事务的重要操作按时间排序的一个序列。如果一个调度的动作首先是一个事务的所有动作，然后是另一个事务的所有动作，以此类推，而没有动作的混合，那么我们说这一调度是串行的。如果一并行调度的结果等价于某一串行调度的结果，那么这个并行调度成为可串行化的

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

听完概念后,估计还是有点似懂非懂,那么我们来实际演示下

脏读:

我们把这种情况定义为会话2读入了脏数据,也就是脏读。

幻读:
会话1,在事务中返回更新后的查询语句是4条

会话2也进行插入,事务返回更新后的也是4条

这时候两个用户都以为自己插入后,数据是4条,但是事务提交后,用户在进行查询,发现忽然多了条不认识的数据,变成了5条,这样的现象像幻听一样,所以命名幻读。

可串行化的隔离级别是可串行化的时候是可以解决幻读问题的,因为当一个会话的事务对一个表进行操作的时候,另一个任何读写操作都是等待的。

总结:

4.事务设计

事务设计分为

• 逻辑设计

  • 范式设计
  • 反范式设计

• 物理设计

  • 命名规范
  • 储存引擎选择
  • 数据类型选择

1.范式

第一范式

• (1)数据表中所有的字段都只具有单一属性
• (2)单一属性的列由基本属性构成
• (3)设计出来的表都是二维表

这张表不符合第一范式,因为字段name-age不是表示单一属性。

第二范式

• 要求表中只具有一个业务主键,符合第二范式的表,所有非主键列必须依赖于主键列

不符合第二范式,因为产品ID更多是用于独立的分别产品,并不依赖于订单表ID。

第三范式

• 在第二范式的基础上,加了非主键列与非主键列间的限制,不允许非主键列与非主键列之间存在依赖的关系

客户姓名与客户编号两个非主键列形成了依赖关系

常见表设计

首先不符合第二范式,分类名称,和商品ID没有绝对依赖关系。并且也不符合第三范式,商品名称和分类名称有关联关系。

订单金额和订单商品数量也存在了依赖关系,所以不符合第三范式。

2.反范式

但是在某些情况下,符合范式设计的数据表,SQL查询效率并不高例如如下,表的过于分离,也使表查询的性能大大降低,所以出现了反范式的设计。

反范式设计:为了性能和读取效率适当对范式设计进行违反,允许存在少量的亢余,用空间去换取时间

例如如下表,分类名称,是常查项,我可以把其放入商品信息,分类描述不常查,所以可以把其另分一张表

改变成如下

所以逻辑设计是MySQL优化的最关键一步,我们不能完全按照范式设计,考虑以后的设计,和数据如何存取更加重要。

3.命名规范

数据库、表、字段的命名要遵守可读性原则,可以驼峰命名法或者_来隔离,一大段小写字母可读性差,不能直观理解其意思。
数据库、表、字段的命名要遵守表意性原则,例如对于表,表的名称应该能够直观反映其中储存的数据。
数据库、表、字段尽可能使用长名原则,对于数据库来说,为了满足其可读性和表意性,所以名字自然不会短,语义必须明确,且最好用_进行隔离。

4.储存引擎的选择
常用的就是MyISAM和InnoDB。支持事务必然选InnoDB,查询业务多选MyISAM。

5.字段数据类型
当一个列可以选择多种数据类型的时候,使用优先考虑数字类型,其次是日期和时间类型,最后才考虑字符类型。对于相同级别的数据类型,应该优先选择占用空间小的数据类型。

如果涉及到和财务相关的,尽可能选择DECIMAL,因为FLOAT和DOUBLE会存在数据的丢失。

还有容易让人困惑的是timestamp和datatime区别优先选timestamp,因为其本质是采用int类型来存取。插入和删除都比较快。