【MySQL】事务、存储引擎和三范式

事务（Transaction）

定义

Mysql逻辑上的一组操作，这些操作要么全做要么全不做，是一个不可分割的工作单位。事务只和DML语句（insert、delete、update）有关。

特性（ACID）

• 原子性（atomicity）：事务是一个不可分割的工作单位，不允许分隔，其中的操作要么全做要么全不做。

• 一致性（consistency）：一个事务执行之前和执行之后都必须处于一次性状态。

• 隔离性（isolation）：多个用户并发访问数据库，如操作同一张表时，数据库为每个用户开启的事务，内部操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

• 持久性（durability）：事务一旦提交，对数据库中数据的改变就是永久性的，接下来其他操作或故障都不会对其有任何影响。

并发操作带来的问题

• 脏读：一个事务修改了数据未提交，另一个事务读取了未提交的数据。

• 丢失修改：两个事务都修改了数据，第一个事务的修改会被丢失。

• 不可重复读（修改）：一个事务正在对数据进行多次修改的同时，另一个事务读取数据，可能会发生两次读取的数据不一致的情况。

• 幻读（增删）：一个事务多次插入或删除数据，另一个事务读取时会发现多了或少了数据。

事务的隔离级别（由低到高）

• read uncommitted（未提交读）：最低的隔离级别，允许读取未提交的事务，会导致脏读、幻读、不可重复读。

• read committed（提交读）：一个事务要等另一个事务提交后才可以读取数据，可避免脏读，会导致幻读、不可重复读。

• repeatable read（重复读）：mysql默认隔离级别，在读取数据（事务开始）时，不再允许修改数据，可避免脏读、不可重复读，会导致幻读。

• serializable（序列化）：最高的隔离级别，完全按照ACID，事务以串行化顺序执行，可避免脏读、幻读、不可重复读。但效率低下，一般不用。

三范式

• 第一范式：数据库表的每一列都是不可分割的基本数据项，强调原子性。

• 第二范式：在满足第一范式的基础上，实体的每个非主键属性完全依赖于主键属性。（消除部分依赖：当主键由两个或两个以上的字段构成，表中某些信息通过主键的一个字段就能唯一确定，称为部分依赖）

• 第三范式：在满足第二范式的基础上，实体中不存在非主键属性传递依赖于主键属性。（传递依赖：A依赖于B，B依赖于C => A传递依赖于C）

• 反三范式：没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，提高读性能，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。

存储引擎

存储引擎指表的类型及表在计算机上的存储方式。

常见存储引擎

1. MyISAM

   占用空间小，有较高的插入、查询速度，不支持事务、也不支持外键。对事务完整性没有要求或以select、insert为主可使用该存储引擎。只支持表级锁，用户的select、update、delete、insert操作都会为表加锁。

   MyISAM的表存储成3个文件，文件名与表名相同。存储表结构的扩展名为.frm，存储表数据的扩展名为.MYD，存储表索引的扩展名为.MYI。

   支持3种不同的存储格式：

• 静态表：表中字段长度不可变，每个记录都是固定长度的。优点是存储迅速，容易缓存，出现故障易恢复；缺点是由于存储时会按列的宽度的定义补足空格，占的空间通常比动态表多。在取数据时默认会把字段后面的空格去掉，如果不注意会把字段本身带的空格也去掉。
• 动态表：记录是不固定长度的。优点是占用空间相对较少；缺点是频繁地更新、删除数据容易产生碎片。
• 压缩表：每个记录被单独压缩，只有非常小的访问开支。

2. InnoDB

   遵循ACID模式设计，提供了具有提交、回滚和崩溃恢复能力的事务安全。但相比MYISAM，写的效率会差一些，并且会占用更多磁盘空间以保存数据和索引。

   特点：支持自动增长列（AUTO-INCREMENT）属性；为了维护数据完整性，还支持外键完整性约束；支持事务；支持行级锁，大大提高多用户并发操作的性能。

   创建表的表结构存储在.frm中，数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中。

3. MEMORY

   使用存在内存中的内容来建表，而且数据全部放在内存中。

   数据全部放在内存中，而且默认使用HASH索引（还有BTREE索引），访问速度非常快。要求服务器有足够的内存来维持MEMORY存储引擎的表的使用，不需要了可以释放内存或删除表。

   MEMORY引擎使用很少，常用于内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。对存储引擎为MEMORY的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

4. Archive

   用于数据归档，压缩比非常高。支持insert、replace和select，不支持update、delete，不支持事务，其设计目的只是为了提供高速的插入和压缩功能。

   用来存储历史数据，如记录日志信息。

5. MERGE

   是一组MyISAM表的组合，这些MyISAM表结构必须完全相同。MERGE本身没有数据，对MERGE类型表的insert、update和delete，实际上是对内部的MyISAM表进行的。
   

InnoDB实现事务的方式

• 原子性：回滚日志（Undo log），用于记录数据修改前的状态。
• 一致性：重作日志（Redo log），用于记录数据修改后的状态。
• 隔离性：锁，用于资源隔离，分为共享锁（S）和排它锁（X）（查询加共享锁，修改加排它锁）。
• 持久性：重作日志（Redo log）和回滚日志（Undo log）。

InnoDB日志

Redo log：在页修改的时候，先写到 redo log buffer 里面， 然后写到 redo log 的文件系统缓存里面(fwrite)，然后再同步到磁盘文件（ fsync）。

Undo log：在 MySQL5.5 之前， undo 只能存放在 ibdata文件里面， 5.6 之后，可以通过设置 innodb_undo_tablespaces 参数把 undo log 存放在 ibdata之外。

事务在修改页时，要先记 undo，在记 undo 之前要记 undo 的 redo， 然后修改数据页，再记数据页修改的 redo。 Redo（里面包括 undo 的修改） 一定要比数据页先持久化到磁盘。 当事务需要回滚时，因为有 undo，可以把数据页回滚到前镜像的 状态，崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo把该事务的修改回滚到事务开始之前。 如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。

参考

http://www.360doc.com/content/18/0422/08/11935121_747709917.shtml
https://blog.csdn.net/qq_34988624/article/details/85838638