数据版本设计_MySQL的事务、锁机制、库表设计

这篇文章内容总结结合上一篇的文章-这篇MySQL数据库总结，Java开发工程师当然要关注收藏

两篇相辅相成，总结的MySQL知识，应对面试、实际工作非常有效。

1、搞懂事务的隔离级别

事务有4大特性：ACID。

事务的概念，就是对一系列的数据库操作(比如插入多条数据)进行统一的提交或回滚操作。

• 原子性，英文是Atomicity

一个事务的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复到事务开始前的状态，也就是这个事务从来没有执行过。

• 一致性，英文是Consistency

一个事务在开始执行之前和执行结束，数据库的完整性没有被破坏。

• 隔离性，英文是Isolation

同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。

• 持久性，英文是Durability

事务处理结束后，对数据的修改就是永久的，即使系统出现故障也不会丢失。

重点来了，四种事务隔离级别

• 读未提交 read-uncommitted

事务执行完修改，没有提交，其他事务对此次修改也是可见的。

• 读提交 read-committed

事务从开始到提交之前，所做的任何修改对其他事务是不可见的。

• 可重复读 repeatable-read

一个事务按相同的查询条件读取以前检索过的数据，其他事务插入了满足其查询条件的新数据，产生幻行。

• 可串行化 serializable

强制事务串行执行。

事务隔离级别为串行化时，读写数据都会锁住整张表。事务安全，但性能很低。

MySQL命令行设置事务隔离级别

执行SQL如下：set session transaction isolation level read uncommitted;set session transaction isolation level repeatable read;

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

四种事务隔离级别，在并发环境下操作会导致的问题对比：脏读、不可重复读、幻读

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

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

幻读：系统管理员A将数据库中所有学生的成绩从具体分数修改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A修改结束后发现还有一条记录没有修改过来，就好像发生了幻觉一样，这就叫幻读。

四种事务隔离级别的会导致的问题

MySQL 默认的事务隔离级别为可重复读。

2、可重复读是默认存在幻读的情况的，怎么防止幻读呢？

• 在快照读情况下，MySQL通过MVCC来避免幻读。
• 在当前读情况下，MySQL通过next-key来避免幻读。

什么是快照读和当前读？

• 快照读：简单的select操作，属于快照读，不加锁。
• 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

什么是MVCC？

mvcc全称是multi version concurrent control(多版本并发控制)。MySQL把每个操作都定义成一个事务，每开启一个事务，系统的事务版本号自动递增。每行记录都有两个隐藏列：创建版本号和删除版本号

• select：事务每次只能读到创建版本号，小于等于此次系统版本号的记录，同时行的删除版本号，不存在或者大于当前事务的版本号。
• update：插入一条新记录，并把当前系统版本号，作为行记录的版本号，同时保存当前系统版本号到原有的行，作为删除版本号。
• delete：把当前系统版本号作为行记录的删除版本号。
• insert：把当前系统版本号作为行记录的版本号。

什么是next-key？

X锁+GAP锁(行锁+间隙锁)。

• 行锁：加在索引上的锁。
• 间隙锁：加在索引之间的锁。

3、MySQL的锁机制

从数据库的操作类型分为：读锁和写锁。

• 读锁：也叫共享锁，读取数据时加锁。针对同一条数据，多个读操作可以共同进行，互不影响。
• 写锁：也叫排它锁，修改数据时加锁。当前数据被执行修改操作，在没有完成前，这个排它锁会阻断其他并行的写或读。

从数据库的操作粒度分为：表锁和行锁。

• 表锁：开销小，锁定粒度大，发生死锁概率高，相对并发也低。
• 行锁：开销大，锁定粒度小，发生死锁概率低，相对并发也高。

两种常见的资源并发锁设计思路：悲观锁与乐观锁

• 悲观锁：特点是先获取锁，再进行业务操作。在整个数据执行读取或者修改删除操作过程中，数据一直处于锁定状态，其它事务无法读取或者修改删除这些数据，很保守。

共享锁和排它锁就是悲观锁设计的例子。

悲观锁的实现，只有数据库层提供的锁机制才能真正保证数据访问的排他性。

• 乐观锁：特点是先进行业务操作，不到万不得已不去拿锁。相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。每次自己操作数据的时候认为没有人会来修改它，所以不会去对数据进行加锁。但是在更新的时候会去判断在此期间数据有没有被修改，需要用户自己去实现乐观锁。乐观锁不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。

乐观锁的实现，不需要数据库提供特别的支持，一般做法是通过为需要上锁的数据增加一个版本标识解决的。在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

4、SELECT查询SQL语句的执行顺序是怎么样的？

以下是常见的SQL语句查询逻辑执行顺序，序号则为实际执行顺序：

(7)     SELECT (8)     DISTINCT (1)     FROM #(1)     FROM #(3)      JOIN (2)     ON (4)     WHERE (5)     GROUP BY (6)     HAVING (9)     ORDER BY (10)    LIMIT 

明白SQL语句的执行顺序，写SQL就会思路清晰很多。

５、库表设计

• 存储引擎的选择

MySQL常用的存储引擎包括 MyISAM、InnoDB 和 Memory，其中各自的特点如下：

(1)MyISAM: 全表锁，拥有较高的执行速度，一个写请求请阻塞另外相同表格的所有读写请求，并发性能差，占用空间相对较小，MySQL5.5 及以下仅 MyISAM支持全文索引，不支持事务。

(2)InnoDB ：行级锁(SQL 都走索引查询)，并发能力相对强，占用空间是 MyISAM的 2.5 倍，不支持全文索引(5.6 开始支持)，支持事务。

(3)Memory : 全表锁，存储在内存当中，速度快，但会占用和数据量成正比的内存空间且数据在 MySQL重启时会丢失。

基于以上特性，建议绝大部份都设置为InnoDB引擎，特殊的业务再考虑选用 MyISAM或 Memory ，如全文索引支持或极高的执行效率等。

• 分表

在数据库表使用过程中，为了减小数据库服务器的负担、缩短查询时间，常常会考虑做分表设计。分表分两种，一种是纵向分表(将本来可以在同一个表的内容，人为划分存储在为多个不同结构的表)和横向分表(把大的表结构，横向切割为同样结构的不同表)。

其中，纵向分表常见的方式有根据活跃度分表、根据重要性分表等。其主要解决问题如下：

(1)表与表之间资源争用问题；

(2)锁争用机率小；

(3)实现核心与非核心的分级存储，如UDB登陆库拆分成一级二级三级库；

(4)解决了数据库同步压力问题。

横向分表是指根据某些特定的规则来划分大数据量表，如根据时间分表。其主要解决问题如下：

(1)单表过大造成的性能问题；

(2)单表过大造成的单服务器空间问题。

• 索引

建立索引的目的是加快对表中记录的查找或排序，索引也并非越多越好。

这部分内容可以阅读上一篇的文章<>的第4条讲解。