innodb学习笔记

总结问题

数据库引擎结构和区别

数据库是由一个个文件组成的，存储引擎是底层物理结构的实现。使用存储引擎使得数据库能够对事务进行支持（不同于文件系统的地方）
存储引擎是基于表的，不是基于数据库的。

主要对比Innodb和MyISAM

• Innodb支持事务，myisam不支持（因为设计它的用处不需要事务，看第二点）；
• Innodb适合OLTP在线事务处理应用，which is 频繁修改和安全性要求高的应用（网游）
• myisam适合OLAP在线分析处理，主要是查询为主的应用（数据仓库）查询次数大于更新次数
• innodb行锁设计、支持外键，myisam是表锁设计，不支持外键；

索引层结构、插入数据索引结构的变化

B+树。

innodb的主索引文件本身就是数据文件（聚集索引），which means B+树叶子节点的数据域就保存了实际的数据。
当然innodb也有辅助索引（非聚集索引），其中数据域记录的是相应记录主键的值，也就是说用辅助索引查询数据时，找到了主键的值，还要到聚集索引里面再把实际的数据通过主键值取出来。
所以不建议过长的字段作为主键。

因为有聚集索引，所以innodb必须要有主键，如果没有指定主键的话，会用一个可以唯一标识数据的列作为主键，如果还没有这样的列，就自动生成一个长6字节的隐含字段作为主键。

innodb中，数据是按行存储的（在页中）。

B+树索引本身并不能找到具体的一条记录，能找到只是该记录所在的页。数据库把页载入到内存，然后通过Page Directory再进行二叉查找。

索引的优化方法、优化底层

使用explain来显示数据库如何使用索引的。当进行范围查找或join时，或者查询语句使用了过多的索引时，就会直接进行全表扫描，这样可能更快。

索引设置建议：

• 最好的索引是每一行数据都有唯一标识，也就是唯一索引；
• 索引列的不同值越多越好，比如，性别这一列就不能做索引，因为性别只有“男”“女”。
• 主键索引长度尽量短，因为辅助索引要用到;
• 频繁更新的列不适合做索引；
• 一次查询不要用过多的索引；
• 利用最左前缀原则，比如建立了一个联合索引(a,b,c)，那么其实我们可利用的索引就有(a), (a,b), (a,b,c)
  建立联合索引时顺序是很重要的，必须在建索引前考虑清楚。
• like “xxxx%” 是可以用到索引的，like “%xxxx” 则不行(like “%xxx%” 同理)，因为varchar是保存了字段前面一部分作为索引。

数据库的几种日志，如何刷新和区别

MySQL数据库中常见的日志文件

• 错误日志

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。该文件不仅记录了所有的错误信息，也记录一些警告信息或正确的信息。

• 慢查询日志

慢查询日志（slowlog）可帮助DBA定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。慢查询日志（slowlog）可帮助DBA定位可能存在问题的SQL语句，从而进行SQL语句层面的优化

• 查询日志

查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。默认文件名为：主机名.log。

• 二进制日志

二进制日志（binary log）记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改.
恢复（recovery）：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

• 表结构定义文件

因为MySQL插件式存储引擎的体系结构的关系，MySQL数据的存储是根据表进行的，每个表都会有与之对应的文件。但不论表采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义。

innodb存储引擎文件

包括表空间文件和重做日志文件。表空间文件是用来管理InnoDB存储引擎的存储，分为共享表空间和独立表空间。重做日志非常的重要，用来记录InnoDB存储引擎的事务日志，也因为重做日志的存在，才使得InnoDB存储引擎可以提供可靠的事务。

• 表空间文件

InnoDB采用将存储的数据按表空间（tablespace）进行存放的设计。在默认配置下会有一个初始大小为10MB，名为ibdata1的文件。当配置了相关参数，会将为每一个表创建独立表空间，命名：表名.ibd

• 重做日志文件

ib_logfile0和ib_logfile1

它们记录了对于InnoDB存储引擎的事务日志。

当实例或介质失败（media failure）时，重做日志文件就能派上用场。例如，数据库由于所在主机掉电导致实例失败，InnoDB存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。

事务

原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log称为重做日志，用来保证事务的原子性和持久性。undolog用来保证事务的一致性（write ahead log），在数据真正持久化之前先把变更写入 log 的方式就叫做 WAL（Write Ahead Logging）,除了带来事务一致性的保证，由于只需要把操作写到 WAL 里就可以认为操作完成而无需等待持久化真正的数据库变更完成就可以返回，数据库操作的效率也得到了一些提升。

索引组织表

在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table）。在InnoDB存储引擎表中，每张表都有个主键（Primary Key），如果在创建表时没有显式地定义主键，则InnoDB存储引擎会按如下方式选择或创建主键：

• 首先判断表中是否有非空的唯一索引（Unique NOT NULL），如果有，则该列即为主键。

• 如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。

innodb逻辑存储结构

从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间（tablespace）。表空间又由段（segment）、区（extent）、页（page）组成。页在一些文档中有时也称为块（block），InnoDB存储引擎的逻辑存储结构大致如图4-1所示

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。

InnoDB存储引擎表是索引组织的（index organized），因此数据即索引，索引即数据。那么数据段即为B+树的叶子节点（图4-1的Leaf nodesegment），索引段即为B+树的非索引节点（图4-1的Non-leaf node segment）

区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。InnoDB存储引擎页的大小为16KB，即一个区中一共有64个连续的页。

InnoDB存储引擎是面向列的（row-oriented），也就说数据是按行进行存放的。

页是InnoDB存储引擎管理数据库的最小磁盘单位。页类型为B-tree Node的页存放的即是表中行的实际数据了。
InnoDB数据页由以下7个部分组成，如图4-6所示。

需要牢记的是，B+树索引本身并不能找到具体的一条记录，能找到只是该记录所在的页。数据库把页载入到内存，然后通过Page Directory再进行二叉查找。只不过二叉查找的时间复杂度很低，同时在内存中的查找很快，因此通常忽略这部分查找所用的时间。

约束

数据完整性

关系型数据库系统和文件系统的一个不同点是，关系数据库本身能保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制

数据完整性3种形式：

• 实体完整性

  保证表中有一个主键。在InnoDB存储引擎表中，用户可以通过定义Primary Key或Unique Key约束来保证实体的完整性。用户还可以通过编写一个触发器来保证数据完整性。

• 域完整性

  保证数据每列的值满足特定的条件。在InnoDB存储引擎表中，域完整性可以通过以下几种途径来保证：

  • 选择合适的数据类型确保一个数据值满足特定条件。

  • 外键（Foreign Key）约束。

  • 编写触发器。

  • 还可以考虑用DEFAULT约束作为强制域完整性的一个方面

• 参照完整性

  保证两张表之间的关系。InnoDB存储引擎支持外键，因此允许用户定义外键以强制参照完整性，也可以通过编写触发器以强制执行。

对于InnoDB存储引擎本身而言，提供了以下几种约束：

• Primary Key
• Unique Key
• Foreign Key
• Default
• NOT NULL

创建约束

• create表的时候定义主键等约束

• 通过ALTER TABLE来创建约束

alter table test add unique key id_num(id_num)

ENUM和SET约束

例如表上有一个性别类型，规定域的范围只能是male或female，在这种情况下用户可以通过ENUM类型来进行约束。

create table a (id int, sex eum('female', 'male'));

触发器与约束

触发器的作用是在执行INSERT、DELETE和UPDATE命令之前或之后自动调用SQL命令或存储过程。
最多可以为一个表建立6个触发器，即分别为INSERT、UPDATE、DELETE的BEFORE和AFTER各定义一个。

例如创建对于异常数据插入检测的触发器，当要插入数据库的数据异常时，触发器会将异常信息记录下来，并防止正确的数据被修改。

外键约束

外键用来保证参照完整性，两个表之间的约束。学生的班级号必须是班级表里面存在的班级号。

InnoDB存储引擎在外键建立时会自动地对该列加一个索引。

MySQL数据库的外键约束都是即时检查（immediate check）

视图

在MySQL数据库中，视图（View）是一个命名的虚表，它由一个SQL查询来定义，可以当做表使用。与持久表（permanent table）不同的是，视图中的数据没有实际的物理存储。
视图是基于基表的一个虚拟表，但是用户可以对某些视图进行更新操作，其本质就是通过视图的定义来更新基本表。

物化视图

物化视图的数据存储在非易失的存储设备上。物化视图可以用于预先计算并保存多表的链接（JOIN）或聚集（GROUP BY）等耗时较多的SQL操作结果。

MySQL数据库本身并不支持物化视图，换句话说，MySQL数据库中的视图总是虚拟的。

通过触发器，在MySQL数据库中实现了类似物化视图的功能。

分区

分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

MySQL数据库支持的分区类型为水平分（不同行的数据分在不同的区），并不支持垂直分（不同列）。

create table t1 (
col1 int not null
col2 int not null
col3 int not null
unique key (col1, col2)
)
partition by hash(col1)//分区列必须是unique key的一部分
partitions 4;

如果建表时没有指定主键，唯一索引，可以指定任何一个列为分区列。

分区类型

• RANGE分区

create table t1 (
id int 
)engine = innodb
partition by range(id)
partition p0 values less than (10)
partition p1 values less than (20)

当插入一个不在分区中定义的值时，MySQL数据库会抛出一个异常。如，我们向表t中插入30这个值.

解决：可以对分区添加一个MAXVALUE值的分区。

分区使用：通过EXPLAIN PARTITION命令我们可以发现，在上述语句中，SQL优化器只需要去搜索p2008这个分区，而不会去搜索所有的分区。在查询语句前加explain partition，同时查询条件要符合要求。

• LIST分区

LIST分区和RANGE分区非常相似，只是分区列的值是离散的，而非连续的。

不同于RANGE分区中定义的VALUES LESS THAN语句，LIST分区使用VALUES IN。因为每个分区的值是离散的，因此只能定义值。

• HASH分区

HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致都是一样的。用户所要做的只是基于将要进行哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

create table t1(
a int,
b date 
)engine = innodb
partition by hash(year(b))
partitions 4;

• KEY分区

KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用MySQL数据库提供的函数进行分区。

• COLUMNS分区

MySQL5.5版本开始支持COLUMNS分区，可视为RANGE分区和LIST分区的一种进化。COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。此外，RANGE COLUMNS分区可以对多个列的值进行分区。

分区与性能

数据库的应用分为两类：一类是OLTP（在线事务处理），如Blog、电子商务、网络游戏等；另一类是OLAP（在线分析处理），如数据仓库、数据集市。在一个实际的应用环境中，可能既有OLTP的应用，也有OLAP的应用。如网络游戏中，玩家操作的游戏数据库应用就是OLTP的，但是游戏厂商可能需要对游戏产生的日志进行分析，通过分析得到的结果来更好地服务于游戏，预测玩家的行为等，而这却是OLAP的应用

对于OLAP的应用，分区的确是可以很好地提高查询的性能，因为OLAP应用大多数查询需要频繁地扫描一张很大的表。

然而对于OLTP的应用，分区应该非常小心。在这种应用下，通常不可能会获取一张大表中10%的数据，大部分都是通过索引返回几条记录即可。

innodb索引

InnoDB存储引擎支持以下几种常见的索引：

• B+树索引
• 全文索引
• 哈希索引

InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。

数据结构与算法

• 二分查找

每页Page Directory中的槽是按照主键的顺序存放的，对于某一条具体记录的查询是通过对Page Directory进行二分查找得到的。

• 平衡二叉树

平衡二叉树的定义如下：首先符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度最大差为1。

• B+树

平衡查找树+顺序索引（链表）

B+树索引

B+树索引的本质就是B+树在数据库中的实现。但是B+索引在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般都在2～4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO。

数据库中的B+树索引可以分为聚集索引（clustered inex）和辅助索引（secondaryindex）。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

聚集索引

聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

每张表只能拥有一个聚集索引。聚集索引能够在B+树索引的叶子节点上直接找到数据。

聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是前面说过的页通过双向链表链接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

聚集索引的另一个好处是，它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据。如用户需要查询一张注册用户的表，查询最后注册的10位用户，由于B+树索引是双向链表的，用户可以快速找到最后一个数据页，并取出10条记录。

辅助索引 - 数据域存储的是相应记录主键的值

对于辅助索引（Secondary Index，也称非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据.

每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。举例来说，如果在一棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问以得到最终的一个数据页。

辅助索引远小于聚集索引，选择辅助索引可以减少IO操作。

B+树索引的管理

索引的创建和删除可以通过两种方法，一种是ALTER TABLE，另一种是CREATE/DROP INDEX。

InnoDB存储引擎实现Online DDL的原理是在执行创建或者删除操作的同时，将INSERT、UPDATE、DELETE这类DML操作日志写入到一个缓存中。待完成索引创建后再将重做应用到表上，以此达到数据的一致性。

对于什么时候添加B+树索引，一般的经验是，在访问表中很少一部分时使用B+树索引才有意义.
例如，按性别进行查询时，可取值的范围一般只有’M’、’F’。因此上述SQL语句得到的结果可能是该表50%的数据（假设男女比例1∶1），这时添加B+树索引是完全没有必要的。

联合索引

联合索引是指对表上的多个列进行索引。第一个排序，再第二列排序。。。。

优化器选择不使用索引的情况

在某些情况下，当执行EXPLAIN命令进行SQL语句的分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN链接操作等情况下。

哈希索引

innodb自己控制。哈希索引只能用来搜索等值的查询，而对于其他查找类型，如范围查找，是不能使用哈希索引的。

全文检索

全文检索（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。

例如，搜索引擎需要根据用户输入的关键字进行全文查找，电子商务网站需要根据用户的查询条件，在可能需要在商品的详细介绍中进行查找，这些都不是B+树索引所能很好地完成的工作。

InnoDB存储引擎从1.2.x版本开始支持全文检索的技术。在InnoDB存储引擎中，将（DocumentId，Position）视为一个“ilist”。因此在全文检索的表中，有两个列，一个是word字段，另一个是ilist字段，并且在word字段上有设有索引。

锁

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。InnoDB存储引擎会在行级别上对表数据上锁。

lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同）。

innodb锁的类型

• 共享锁（S Lock），允许事务读一行数据。
• 排他锁（X Lock），允许事务删除或更新一行数据。

如果一个事务T1已经获得了行r的共享锁，那么另外的事务T2可以立即获得行r的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容（Lock Compatible）。但若有其他的事务T3想获得行r的排他锁，则其必须等待事务T1、T2释放行r上的共享锁——这种情况称为锁不兼容。

X锁与任何的锁都不兼容，而S锁仅和S锁兼容。需要特别注意的是，S和X锁都是行锁，兼容是指对同一记录（row）锁的兼容性情况。

意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度（fine granularity）上进行加锁。

若将上锁的对象看成一棵树，那么对最下层的对象上锁，也就是对最细粒度的对象进行上锁，那么首先需要对粗粒度的对象上锁。

InnoDB存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。

1）意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁2）意向排他锁（IX Lock），事务想要获得一张表中某几行的排他锁

一致性非锁定读

在默认配置下，即事务的隔离级别为REPEATABLE READ模式下，InnoDB存储引擎的SELECT操作使用一致性非锁定读。

一致性的非锁定读（consistent nonlocking read）是指InnoDB存储引擎通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB存储引擎会去读取行的一个快照数据。

一致性锁定读

用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于SELECT的只读操作。

SELECT…FOR UPDATE对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。SELECT…LOCK IN SHARE MODE对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。

解决Phantom Problem幻读

Phantom Problem是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行

行锁的算法

InnoDB存储引擎有3种行锁的算法，其分别是：

• Record Lock：单个行记录上的锁
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身□
• Next-Key Lock∶Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

锁问题

脏读 - 读到未提交的数据 - READ UNCOMMITTED

脏数据是指事务对缓冲池中行记录的修改，并且还没有被提交（commit）

脏数据是指未提交的数据，如果读到了脏数据，即一个事务可以读到另外一个事务中未提交的数据，则显然违反了数据库的隔离性。

脏读指的就是在不同的事务下，当前事务可以读到另外事务未提交的数据，简单来说就是可以读到脏数据。

脏读发生的条件是需要事务的隔离级别为READ UNCOMMITTED，而目前绝大部分的数据库都至少设置成READ COMMITTED。InnoDB存储引擎默认的事务隔离级别为READ REPEATABLE。

不可重复读（幻读） - 读到已提交的数据 - READ COMMITTED

不可重复读是指在一个事务内多次读取同一数据集合。在这个事务还没有结束时，另外一个事务也访问该同一数据集合，并做了一些DML操作。因此，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的情况，这种情况称为不可重复读。

不可重复读和脏读的区别是：脏读是读到未提交的数据，而不可重复读读到的却是已经提交的数据，但是其违反了数据库事务一致性的要求。

很多数据库厂商（如Oracle、Microsoft SQL Server）将其数据库事务的默认隔离级别设置为READ COMMITTED，在这种隔离级别下允许不可重复读的现象。

在InnoDB存储引擎中，通过使用Next-Key Lock算法来避免不可重复读的问题。

InnoDB存储引擎的默认事务隔离级别是READ REPEATABLE，采用Next-Key Lock算法，避免了不可重复读的现象。

丢失更新 - 更新操作被覆盖

丢失更新是另一个锁导致的问题，简单来说其就是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。

但是，在当前数据库的任何隔离级别下，都不会导致数据库理论意义上的丢失更新问题。事务T2并不能对行记录r进行更新操作，其会被阻塞，直到事务T1提交。

但是逻辑上可能会出现。要避免丢失更新发生，需要让事务在这种情况下的操作变成串行化，而不是并行的操作。

事务

数据库系统引入事务的主要目的：事务会把数据库从一种一致状态转换为另一种一致状态。在数据库提交工作时，可以确保要么所有修改都已经保存了，要么所有修改都不保存。

ACID

• 原子性（atomicity）

原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，才算整个事务成功。

事务中的一系列操作，要么都做，要么都不做。

• 一致性（consistency）

一致性指事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

• 隔离性（isolation）- 锁来实现

事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见，通常这使用锁来实现。

• 持久性（durability）

事务一旦提交，其结果就是永久性的。

对于Oracle数据库来说，其默认的事务隔离级别为READ COMMITTED，不满足I的要求，即隔离性的要求。

对于InnoDB存储引擎而言，其默认的事务隔离级别为READ REPEATABLE，完全遵循和满足事务的ACID特性。

事务分类

• 扁平事务（Flat Transactions）

在扁平事务中，所有操作都处于同一层次，其由BEGINWORK开始，由COMMIT WORK或ROLLBACK WORK结束，其间的操作是原子的，要么都执行，要么都回滚。因此扁平事务是应用程序成为原子操作的基本组成模块。
扁平事务的主要限制是不能提交或者回滚事务的某一部分，或分几个步骤提交。由此出现了下一种。

• 带有保存点的扁平事务（Flat Transactions with Savepoints）

除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态。保存点（Savepoint）用来通知系统应该记住事务当前的状态，以便当之后发生错误时，事务能回到保存点当时的状态。

带有保存点的扁平事务，当发生系统崩溃时，所有的保存点都将消失，因为其保存点是易失的（volatile），而非持久的（persistent）。

• 链事务（Chained Transactions）

在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务。

• 嵌套事务（Nested Transactions）

是一个层次结构框架。由一个顶层事务（top-leveltransaction）控制着各个层次的事务。

• 分布式事务（Distributed Transactions）

通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。

事务的实现

事务隔离性由锁来实现。
原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log称为重做日志，用来保证事务的原子性和持久性。undolog用来保证事务的一致性。

redo恢复提交事务修改的页操作，而undo回滚行记录到某个特定版本。

redo通常是物理日志，记录的是页的物理修改操作。undo是逻辑日志，根据每行记录进行记录。

undo像游戏返回存档点，redo是从存档点返回现在的地点（不像再从存档点重新走一遍了）

redo重做日志 - 记录的是页的物理修改操作

其由两部分组成：一是内存中的重做日志缓冲（redo log buffer），其是易失的；二是重做日志文件（redo log file），其是持久的。

InnoDB是事务的存储引擎，其通过Force Log at Commit机制实现事务的持久性，即当事务提交（COMMIT）时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。

在InnoDB存储引擎中，重做日志都是以512字节进行存储的。这意味着重做日志缓存、重做日志文件都是以块（block）的方式进行保存的，称之为重做日志块（redo logblock），每块的大小为512字节。

InnoDB存储引擎运行过程中，log buffer重做日志缓存根据一定的规则将内存中的log block刷新到磁盘（重做日志文件）中。

• 事务提交时
• 当log buffer中有一半的内存空间已经被使用时
• log checkpoint时

undo - 事务的回滚

如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。

redo存放在重做日志文件中，与redo不同，undo存放在数据库内部的一个特殊段（segment）中，这个段称为undo段（undo segment）。

undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。例如，用户执行了一个INSERT 10W条记录的事务，这个事务会导致分配一个新的段，即表空间会增大。在用户执行ROLLBACK时，会将插入的事务进行回滚，但是表空间的大小并不会因此而收缩。

当InnoDB存储引擎回滚时，它实际上做的是与先前相反的工作。对于每个INSERT，InnoDB存储引擎会完成一个DELETE；对于每个DELETE，InnoDB存储引擎会执行一个INSERT；对于每个UPDATE，InnoDB存储引擎会执行一个相反的UPDATE，将修改前的行放回去。

undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC（多版本一致性控制）的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

undo log会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护

undo log分为：

• insert undo log

insert undo log是指在insert操作中产生的undo log。记录了主键的列和值，在回滚时就有依据了。

• update undo log

记录的是对delete和update操作产生的undo log。

事务控制语句

start transaction|begin:显示开启一个事务

commit:提交事务

rollback:回滚

savepoint name : 在事务中创建一个保存点

rollback to name: 回滚值某保存点。没有真正结束事务。

set transaction:设置事务隔离级别

READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE

事务隔离级别

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

InnoDB存储引擎默认支持的隔离级别是REPEATABLE READ。使用Next-Key Lock锁的算法，因此避免幻读的产生。

分布式事务

分布式事务指的是允许多个独立的事务资源（transactional resources）（不同数据库里的数据）参与到一个全局的事务中。

全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的ACID要求又有了提高。

分布式事务可能在银行系统的转账中比较常见。

分布式事务使用两段式提交（two-phase commit）的方式。在第一阶段，所有参与全局事务的节点都开始准备（PREPARE），告诉事务管理器它们准备好提交了。在第二阶段，事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。可见与本地事务不同的是，分布式事务需要多一次的PREPARE操作，待收到所有节点的同意信息后，再进行COMMIT或是ROLLBACK操作。

当前Java的JTA（Java Transaction API）可以很好地支持MySQL的分布式事务，需要使用分布式事务应该认真参考其API。

备份

分类

根据备份的方法不同

• 热备Hot Backup是指数据库运行中直接备份，对正在运行的数据库操作没有任何的影响。

• 冷备Cold Backup是指备份操作是在数据库停止的情况下，这种备份最为简单，一般只需要复制相关的数据库物理文件即可。

• WarmBackup备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，如加一个全局读锁以保证备份数据的一致性。

按照备份后文件的内容

• 逻辑备份是指备份出的文件内容是可读的，一般是文本文件。内容一般是由一条条SQL语句，或者是表内实际数据组成

• 裸文件备份是指复制数据库的物理文件。

按照备份数据库的内容来分

完全备份是指对数据库进行一个完整的备份。增量备份是指在上次完全备份的基础上，对于更改的数据进行备份。日志备份主要是指对MySQL数据库二进制日志的备份，通过对一个完全备份进行二进制日志的重做（replay）来完成数据库的point-in-time的恢复工作。

复制

复制（replication）是MySQL数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用

步骤 - 异步实时

• 主服务器（master）把数据更改记录到二进制日志（binlog）中

• 从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。

• 从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。