MySQL学习（四）——事务与存储引擎

1. 事务

1.1 概念

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

就比如: 张三给李四转账1000块钱，张三银行账户的钱减少1000，而李四银行账户的钱要增加1000。 这一组操作就必须在一个事务的范围内，要么都成功，要么都失败。

正常情况: 转账这个操作, 需要分为以下这么三步来完成 , 三步完成之后, 张三减少1000, 而李四增加1000, 转账成功 ；

异常情况: 转账这个操作, 也是分为以下这么三步来完成 , 在执行第三步是报错了, 这样就导致张三减少1000块钱, 而李四的金额没变, 这样就造成了数据的不一致, 就出现问题了。

为了解决上述的问题，就需要通过数据的事务来完成，我们只需要在业务逻辑执行之前开启事务，执行完毕后提交事务。如果执行过程中报错，则回滚事务，把数据恢复到事务开始之前的状态。

注意： 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐式的提交事务。

1.2 事务操作

为了体现事务的特性，我们重新建立一张账户管理的表，数据如下：

drop table if exists account;
create table account(id int primary key AUTO_INCREMENT comment 'ID',name varchar(10) comment '姓名',money double(10,2) comment '余额'
) comment '账户表';
insert into account(name, money) VALUES ('张三',2000), ('李四',2000);

1.2.1 未设置事务

首先我们最开始张三和李四的金额都是2000，然后我们一次性执行下面的语句：

-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
你干嘛~...
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

上述语句中的第5行不符合语法规范，执行肯定会报错，这个时候我们再查看数据库中的数据，查询如下：

可以看到，张三的钱减少了，但是李四的前也没有增加，这就是一个错误的操作。

1.2.2 控制事务

控制事务的语句如下：

• 查看/设置事务提交方式

  SELECT @@autocommit ;
  SET @@autocommit = 0 ;
  

• 开始事务

  START TRANSACTION 或 BEGIN ;
  

• 提交事务

  COMMIT;
  

• 回滚事务

  ROLLBACK
  

注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。

上面转账的语句加上了事务控制后如下所示：

-- 开启事务
start transaction
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;

1.3 事务四大特性

事务具有四大特性：

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

上述就是事务的四大特性，简称ACID

1.4 并发事务问题

1. 脏读

   指一个事务读到另外一个事务还没有提交的数据。

   

   比如上面的B读取到了A未提交的数据。

2. 不可重复读

   指一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
   

   事务A两次读取同一条记录，但是读取到的数据却是不一样的。

3. 幻读

   指一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”。

   

1.5 事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：

隔离级别	脏读	不可重复读	幻读
Read uncommited	$✓$	$✓$	$✓$
Read commited	×	$✓$	$✓$
Repeatable Read(默认)	×	×	$✓$
Serializable	×	×	×

• 查看事务隔离级别

  SELECT @@TRANSACTION_ISOLATION;
  

• 设置事务隔离级别

  SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
  

注意：事务隔离级别越高，数据越安全，但是性能越低。

2. 存储引擎

2.1 MySQL体系结构

MySQL的体系结构可以分为客户层、服务层、存储引擎层、物理层四个方面，其结构如下：

1. 客户层

   最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2. 服务层

   第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3. 存储引擎层

   存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。

4. 物理层

   主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。

2.2 存储引擎

存储引擎是mysql数据库的核心，存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

• 建表时指定存储引擎

  CREATE TABLE 表名(字段1 字段1类型 [ COMMENT 字段1注释 ] ,......字段n 字段n类型 [COMMENT 字段n注释 ]
  ) ENGINE = INNODB [ COMMENT 表注释 ] ;
  

  只需要在建立的表的后面添加一个 ENGINE= 的字段即可。

• 查询当前数据库支持的存储引擎

  show engines;
  

  查询结果如下：

  

  可以看到，上面支持的数据库引擎其 Support 一列标注为 YES ，不支持的标注为 NO ，InnoDB 数据库引擎设置为是默认引擎。

• 创建其他引擎

  以创建 MEEMORY 引擎为例，创建的语句如下：

  create table my_memory(id int,name varchar(10)
  ) engine = Memory ;
  

2.3 存储引擎的特点

这里我们主要介绍三种引擎 InnoDB, MyISAM, Memory 的特点。

2.3.1 InnoDB

1. 简单介绍

   InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。

   其特点如下：

   • DML操作遵循ACID模型，支持事务；
   • 行级锁，提高并发访问性能；
   • 支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

2. 文件存储

   InnoDB 引擎的每张表都会对应一个 ibd 表空间文件，存储该表的表结构、数据和索引。

   我们直接打开MySQL安装时的数据存放目录，这个目录下有很多文件夹，不同的文件夹代表不同的数据库，我们直接打开study文件夹，可以看到其中的文件目录如下：

   

   可以看到里面有很多的ibd文件，每一个ibd文件就对应一张表，比如：我们有一张表 account，就有这样的一个account.ibd文件，而在这个ibd文件中不仅存放表结构、数据，还会存放该表对应的索引信息。 而该文件是基于二进制存储的，不能直接基于记事本打开，我们可以使用mysql提供的一个指令 ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表的表结构。

   

3. 逻辑存储结构

   InnoDB引擎中的逻辑结构最高层时表空间，一个表空间中含有若干个段，一个段中含有若干的区，一个区包含若干个页，一个页包含若干个行，其关系如下：

   

   各个结构如下：

   • 表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。
   • 段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
   • 区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。
   • 页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
   • 行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。

2.3.2 MyISAM

MyISAM是MySQL早期的默认存储引擎。

其特点为：

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 访问速度快

MyISAM的表分为三个文件进行存储：

• .sdi：存储表结构信息
• .MYD: 存储数据
• .MYI: 存储索引

2.3.3 Memory

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

其存储采用的时hash索引。

存放的表的文件为 .sdi ，存储表结构信息。

2.3.4 区别和比较

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	不支持	不支持
锁机制	行锁	表锁	表锁
B+树索引	支持	支持	支持
Hash索引	不支持	不支持	支持
全文索引	支持	支持	不支持
空间使用	高	低
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	不支持	不支持