MySQL数据库存储引擎及事务处理

   插件式存储引擎是MySQL数据库最重要的特性之一，用户可以根据需要选择如何使用存储和索引数据等。MySQL支持多种存储引擎，本文主要介绍三种存储引擎InnoDB，MyISAM以及MEMORY，然后再介绍MySQL的事务处理相关内容。

1、MySQL存储引擎

   MySQL默认的存储引擎是InnoDB，可以根据不同需求进行更改。

1.1 存储引擎设置

查看MYSQL支持的存储引擎命令：show engines;

创建表时设置存储引擎：create table table_name() engine = innodb;

修改存储引擎：alter table table_name engine = myisam;

修改配置文件来达到全部表的存储引擎修改：Windows 下my.ini文件 ，Linux下my.cnf文件

[mysqld]

default-storage-engine=INNODB

1.2 存储引擎特点

InnoDB

该存储引擎提供具有提交，回滚、崩溃恢复能力事务安全机制、支持外键、自动增长列等功能，索引采用的是聚集索引,索引和数据存放在同一个文件中，所以INNODB存储引擎的数据存在磁盘上就两个文件。文件名和表名是相同的，扩展名分别为：.frm(存储表的定义)          .ibd(存储的是数据和索引)

MyISAM

该存储引擎不支持事务、也不支持外键、索引采用非聚集索引，对数据完整性没有要求或者以select、insert为主的应用基本上采用MYISAM存储引擎，索引和数据分开存储，MYISAM在磁盘上存储的数据有三个文件，文件名和表名是相同的，扩展名分别为：.frm(存储表的定义)   .MYD(MYData 存储数据)    .MYI(MYIndex 存储索引)

MEMORY

该存储引擎使用内存来存储数据，每个memory表实际存在一个文件，格式.frm(存储表的定义)，memory类型存储引擎表的访问是很快的，因为数据都存放在内存中，默认的索引hash索引（不支持区间查找），假如服务一旦关闭，表中的数据就不存在了。

1.3 三种存储引擎的比较

锁机制：在事务并发操作时，并发操作粒度；

B-树索引 | hash索引：提高访问速度；

外键:子表的字段依赖于父表的键，设置两张表的依赖关系；

事务：多个SQL语句执行，要么全部成功，要么全部失败，不存在部分成功；

索引缓存 | 数据缓存：在查询时，对源数据未做修改，重复查询是否可以减少磁盘IO；

2、事务处理

2.1 什么是事务？

事务就是由一个或多个SQL语句组成的不可分割的单元，只有所有的操作都正常完成，事务会提交给数据库，若部分操作未完成，事务就会退回初始状态。因此，事务要么成功，要么失败。

2.2 事务处理相关命令

操作	对应的命令
查看存储引擎	SHOW CREATE TABLE 表名；
更改引擎	ALTER TABLE 表名 ENGINE=新引擎名；
回滚	ROLLBACK;
声明事务开始	BEGIN;
事务提交	COMMIT;
查询自动提交功  能状态	SELECT @@AUTOCOMMIT;
设置自动提交功能	SET AUTOCOMMIT=0或1;（0为手动）
设置分离水平	SET SESSION TRANSACTION ISOLATION LEVEL 分离水平;

使用保留点 SAVEPOINT

savepoint 是在数据库事务处理中实现“子事务”（subtransaction），也称为嵌套事务的方法。事务可以回滚到 savepoint 而不影响 savepoint 创建前的变化, 不需要放弃整个事务。

ROLLBACK 回滚的用法可以设置保留点 SAVEPOINT，执行多条操作时，回滚到想要的那条语句之前。

使用 SAVEPOINT

SAVEPOINT savepoint_name;    // 声明一个 savepoint
ROLLBACK TO savepoint_name;  // 回滚到savepoint

删除 SAVEPOINT

保留点再事务处理完成（执行一条 ROLLBACK 或 COMMIT）后自动释放。

MySQL5 以来，可以用:

RELEASE SAVEPOINT savepoint_name;  // 删除指定保留点

2.3 事务的ACID特性

       一般来说，事务必须具备四个特性，简称为ACID特性：

原子性（Atomic）

   事务是一个不可分割的整体，事务必须具有原子性。即事务包含的所有操作要么全部成功，要么全部失败回滚。

一致性（Consistency）

          事务执行前后，数据库的数据状态必须保持一致性。 数据库的一致性状态需要用户来负责，由并发控制机制实现。举例来说，网上购物来说，将商品加入购物车，商品的库存介绍、到商品加入购物车是一个完整性的事务。

        由于并发操作带来的数据不一致性问题包括数据脏读，不可重复读，幻读，相关内容将在下一节进行介绍。

隔离性（Isolation）

       当两个事务或多个事务并发操作时，为了保证数据的安全性，将一个事务的内部操作和其他事务隔离起来，不被其他正在执行的事务看到。举例说明，假如事务t1和事务t2并发操作时，对t1而言，要么是在t2执行完成之后执行，那么是可以看到t2执行的结果，要是在t2之前执行完成，那么t2能够看到t1执行的结果。

持久性（Durability）

       事务完成之后，数据库中的数据永久性的保存下来，即使数据库出现故障，也能恢复数据。

2.4 事务未隔离问题

        当多个线程都开启事务操作数据库中数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性。如果没有隔离，会发生的几种问题：

脏读（Dirty Read）

      一个事务读取了另一个事务未提交的数据。举例来说，事务A和事务B并发执行时，事务A更新后，事务B查询到事务A尚未提交的数据，此时，事务A回滚，则事务B读取到的数据就是脏数据（事务B读取事务A未移交的数据）

不可重复读（NonRepeatable Read）

         一个事务的操作导致另一个事务前后两次读取的数据不同。举例来说，事务A和事务B并发执行，当事务B查询读取数据后，事务A更新了事务读物的数据，此时事务B再次读取数据是，发现前后两次的数据不一样（事务B读取了事务A已提交的事务）。

幻读（虚读）(Phantom Read）

        一个事务的操作导致另一个事务前后两次查询结果的数据数量不同。举例来说，事务A和事务B并发执行，当事务B查询读取数据后，此时事务A增加了数据或者是删除了数据。事务B查询，发现查询到前一次不存在的数据，或者发现前一次查询的记录不见了（事务B读取了事务A新增加的或者读取到事务A删除的数据）。

2.5 事务的隔离级别

      在JDBC编程中，Condition对象定义了5种事务隔离级别：

1、Transation_None:表示不支持事务

2、Transation_Read_Uncommited:未提交读。

说明在提交前一个事务可以看到另一个事务的变化。这样：脏读、不可重复读，幻读都是存在的。

3、Transation_Read_Commited:已提交读。

读取未提交的数据是不被允许的。这个级别任然允许不可重复读和幻读。

4、Transation_Repeatable_Read:可重复读。

事务保证能够读取相同的数据而不会失败。允许幻读

5、Transation_Serializable:可序列化/串行化。

最高的事务级别，防止脏读、可重复读、幻读

注意：事务的隔离级别越高，为避免冲突所环花费的性能也就越高

以下为不同隔离级别，对于三种问题的处理：

隔离级别	脏读	不可重复读	幻读
未提交读	可以	可以	可以
已提交读	不可以	可以	可以
可重复读	不可以	不可以	可以
串行化	不可以	不可以	不可以

设置隔离级别

查看当前事务的隔离级别：select @@tx_isolation;

修改事务的隔离级别

>set  [global | session]  transaction isolation level 隔离级别名称;
>set tx_isolation=’隔离级别名称;’

隔离级别：Serializable | Repeatable read | Read committed |
Read uncommitted
注意：
设置默认级别是指当前session的下一个事务
设置session级别是指当前session以后的所有事务
设置global级别是指对之后的所有session，不包括当前session

2.6  事务控制语句

• BEGIN 或 START TRANSACTION 显式地开启一个事务；

• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

• ROLLBACK TO identifier 把事务回滚到标记点；

• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ U NCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

2.7  MYSQL 事务处理主要有两种方法

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

2.8  MySQL中的锁机制

     数据库为了维护ACID，尤其是一致性和隔离性，一般使用加锁的方式。同时由于数据库是个高并发的应用，同一时间有大量的并发访问，如果加锁过度，会极大的降低并发处理的能力。所以对于加锁的处理，是数据库对于事务处理的精髓所在

锁方案

一次封锁

因为有大量的并发访问，为了预防死锁，一般应用中采用的是一次封锁的方案：就是在方法的开始阶段，已经预先知道需要用到那些数据，然后全部锁住，在方法执行之后，再全部解锁。
这种方案可以有效避免死锁发生，当时由于数据库操作在事务开始阶段并不知道具体会用到哪些数据，所以该方案不合适在数据库中使用。

两段锁

两段锁协议将事务分成两个阶段：加锁阶段和解锁阶段

• 加锁阶段：在该阶段可以进行加锁操作。读数据前需要申请获取S锁（共享锁：其他事务可以继续加共享锁，但不能加排他锁）；写数据前需要申请获取X锁（排他锁：其他事务不能获取任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。

• 解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能进行加锁操作。

   

  

   

  备注：这个方案无法避免死锁，但是可以保证事务调度的串行化（串行化在数据库恢复和备份时候很重要）。

MySQL的锁类型

表锁

对一整张表加锁，并发能力低下（即使有分读锁、写锁），一般在DDL处理时使用

行锁

只锁住特定行的数据，并发能力强，MySQL一般都是用行锁来处理并发事务。

如果用到无索引的字段，那么MySQL会在存储引擎层面将所有的记录加锁，然后由MySQL Server过滤，如果不满足会调用unlock_row把不满足条件的记录释放锁（这里违背了二段锁协议）。

这种情况同样适用于MySQL的默认隔离级别RR。所以对一个数据量很大的表做批量修改的时候，如果无法使用相应的索引，MySQL Server过滤数据的的时候特别慢，就会出现虽然没有修改某些行的数据，但是它们还是被锁住了的现象。

GAP锁（间隙锁）

是MySQL使用索引对行锁两边的区间进行加锁，避免其他事务在这两个区间insert的一种锁。

 

如图所示：数据库中存在值5,30。那么数据库会将数据段切分以下几个区间：
(negative infinity, 5],
(5,30],
(30,positive infinity)
当对值为30这一行加行锁的时候，会同时对(5,30]和(30,positive infinity)加GAP锁。这样其他事务如果想在这两个区间进行insert操作的时候，需要等待本次事务完成。

如果对不存在的数据进行更新，比如更新20(不存在)对应数据行，那么数据库也会对其存在的区间(5,30]加GAP锁。这样，如果有其他事务想插入值为10的数据，需要等待20这个事务完成。

如果使用的是没有索引的字段，那么会给全表加入GAP锁。

Next-Key锁

Next-Key锁是行锁和GAP锁的合并（MySQL使用它来避免幻读）

MVVC(多版本并发控制）

Innodb中的乐观锁实现。通过它提高MySQL的读取操作的性能。并能解决MySQL的重复读问题。
MVVC在每一行记录的后面加两个隐含列（记录创建版本号和删除版本号）。这里的版本号指的是事务的版本号（每个事务启动的时候，都有一个递增的版本号）。

• 比如插入一条记录（事务id为1）

id	name	create_version	delete_version
1	bird	1	-

• 如果把这条记录name更新为dog

id	name	create_version	delete_version
1	dog	2	-
1	bird	1	2

在更新操作的时候，采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。

• 删除这条记录时

id	name	create_version	delete_version
1	dog	2	3
1	bird	1	2

删除操作的时候，就把事务版本号作为删除版本号

• 执行查询操作需要符合如下规则才能被查出来

1. 删除版本号 大于 当前事务版本号，就是说删除操作是在当前事务启动之后做的。
2. 创建版本号 小于或者等于 当前事务版本号 ，就是说记录创建是在事务中（等于的情况）或者事务启动之前。

在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数据！这在一些对于数据的时效特别敏感的业务中，就很可能出问题。
对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库当前版本数据的方式，叫当前读 (current read)。很显然，在MVCC中：
快照读：就是select
>select * from table ....;
当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。
>select * from table where ? lock in share mode;
>select * from table where ? for update;
>insert;
>update ;
>delete;

Innodb中事务隔离级别和锁的关系

Innodb通过使用不同的锁来实现事务隔离

避免脏读

通过对数据加行锁或则表锁，使对同一数据进行操作的事务处于等待状态，来避免同时操作

事务A	事务B
begin;	begin;
update class_teacher set class_name='初三二班' where teacher_id=1;	update class_teacher set class_name='初三三班' where teacher_id=1;
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
commit;

避免不可重复读

通过MVVC实现事务的可重复读

避免幻读

通过Next-Key锁避免产生幻读现象
MySQL在RC和RR中都避免了幻读现象。

参考

https://www.jianshu.com/p/bcc614524024

https://blog.csdn.net/moxigandashu/article/details/62046030

http://www.runoob.com/mysql/mysql-transaction.html

四种隔离事务总结： http://blog.chinaunix.net/uid-14010457-id-3956842.html