《MySQL技术内幕：InnoDB存储引擎》读书笔记

一、Mysql体系结构和存储引擎

1. 概念：             

　　数据库：物理操作系统文件或其他形式文件类型的集合。（是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合。）

　　数据库实例：由数据库后台进程/线程以及一个共享内存区域组成。（是应用程序，是位于用户与操作系统之间的一层数据管理软件，用户对数据库的任何操作都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。）

  数据库是由一个一个文件组成的，如果要对这些文件执行诸如SELECT、INSERT、UPDATE和DELETE之类的操作，不能通过简单的操作文件来更改数据库的额内容，需要通过数据库实例来完成对数据库的操作。

2. 体系结构:         

　　组成部分:

• 连接池组件
• 管理服务和工具组件
• SQL接口组件
• 查询分析器组件
• 优化器组件
• cache组件
• 插件式存储引擎 (最重要的特色)
• 物理文件

 　　MySQL区别于其他数据库的最重要的特点是插件式的表存储引擎。（存储引擎是底层物理结构的实现，它是基于表的，而不是数据库。）

 3. Mysql表存储引擎：

　　InnoDB：

• InnoDB存储引擎支持事务,主要面向在线事务处理（OLTO）方面的应用。
• 特点：行锁设计，支持外键，并支持非锁定读（即默认情况下读取操作不会产生锁）。
• MSQL在windoes版本下的InnoDB是默认的存储引擎。
• InnoDB将数据放在一个逻辑的表空间中，这个表空间就想黑盒一样由InnoDB自身进行管理。从MySQL4.1版本开始，它可以将每个InnoDB存储引擎的表单独放在一个独立的ibd文件中。
• InnoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了SQL标准中的4种隔离级别，默认为REPEATABLE级别，同时使用一种被称为next-key locking的策略来避免幻读。  除此之外,InnoDB还提供了插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用的功能。
• 对于表中数据的存储,InnoDB存储引擎采用了聚集的方式，这种方式类似于Oracle的索引聚集表。每张表的存储都按主键的顺序存放，如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。

　　MyISAM：

• 特点：不支持事务、表锁和全文索引，对于一些OLAP（在线分析处理）操作速度快。除windows版本外，是所有其他版本默认的存储引擎。
• MyISAM存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。
• 对于MyISAM存储引擎表，Mysql数据库只缓存其索引文件，数据文件的缓存交由操作系统本身来完成，这与其他使用LRU算法缓存数据的大部分数据库大不相同。

　　其他存储引擎：NDB，Archive，Memory，Federated，Maria

【注】虽然MySQL有许多存储引擎，但是它们之间不存在优劣性的差异，我们应根据不同的应用选择适合自己的存储引擎。

二、InnoDB存储引擎

1. InnoDB体系架构：

　　InnoDB有多个内存块,可以认为这些内存块组成了一个大的内存池,负责如下工作:

• 维护所有进程/线程需要访问的多个内部数据结构。
• 缓存磁盘上的数据，方便快速地读取，并且对磁盘文件的数据进行修改之前在这里缓存。
• 重做日志缓冲。
• ....

　　后台线程的主要作用是负责刷新内存池中的数据，保证缓存池中的内存缓存的是最近的数据。除此之外，将已修改的数据文件刷新到磁盘文件，同时保证数据库发生异常情况下InnoDB能恢复到正常运行状态。

默认情况下，InnoDB的后台线程有7个：IO thread（4个），master thread（1个），锁监控线程（1个），错误监控线程（1个）。

2. 内存        

 　　InnoDB存储引擎内存由以下几个部分组成：缓冲池、重做日志缓冲池以及额外的内存池。

　　缓冲池是占最大块内存的部分，用来存放各种数据的缓存。因为InnoDB的存储引擎的工作方式总是将数据库文件按页（每页16K）读取到缓冲池，然后按最近最少使用(LRU)算法来保留在缓冲池中的缓存数据。如果数据库文件需要修改，总是首先修改在缓存池中的页(发生修改后，该页即为脏页)，然后再按照一定的频率将缓冲池的脏页刷新到文件。

　　缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲、自适应哈希索引、InnoDB存储的锁信息、数据字典信息等。不能简单的认为缓冲池只是缓存索引页和数据页，他们只是占缓冲池很大的一部分而已。

 3. master thread        

　　InnoDB存储引擎的主要工作都是在一个单独的后台线程master thread中完成的。

　　master thread的线程优先级别最高，其内部由几个循环组成：主循环、后台循环、刷新循环、暂停循环。 master thread会根据数据库的运行状态在loop、background loop、flush loop和suspend loop中进行切换。

　　loop：主循环，大多数的操作都在这个循环中。其中有两大部分操作：每秒的操作和每10秒的操作。

　　每秒操作包括：

1. 日志缓冲刷新到磁盘，即使这个事务还没有提交(总是)。 
   • 即使某个事务还没有提交，Inno存储引擎仍然会每秒将重做日志缓冲中的内容刷新到重做日志文件，因此再大的事务commit的时间也很快。
2. 合并插入缓冲(可能)。
   • InnoDB存储引擎会判断当前1秒内发生的IO次数是否小于5次，如果小于5次，InnoDB认为当前的IO压力很小，可以执行合并插入缓冲的操作。
3. 至多刷新100个InnoDB的缓冲池中的脏页到磁盘(可能)。
   • 当前缓冲池中脏页的比例如果超过配置的阈值，则将100个脏页写入磁盘。
4. 如果当期没有用户活动，切换到background loop(可能)。

　　每10秒的操作：

1. 刷新100个脏页到磁盘(可能)。
   • InnoDB存储引擎先判断过去10秒内磁盘的IO操作是否小于200次，如果是，才将100个脏页刷新到磁盘。
2. 合并至多5个插入缓冲(总是)。
3. 将日志缓冲刷新到磁盘(总是)。
4. 删除无用的undo页(总是)。
5. 刷新100个或10个脏页到磁盘(总是)。
6. 产生一个检查点(总是)。

 　　background loop：后台循环。若当前没有用户活动(数据库空闲时)或者数据库关闭时，就会切换到这个循环。

　　包含操作:

1. 删除无用的undo页(总是)
2. 合并20个插入缓冲(总是)
3. 跳回到主循环(总是)
4. 不断刷新100个页,直到符合条件(可能，跳到flush loop中完成)

　　如果flush loop中也没有事情可做了，InnoDB存储引擎会切换到suspend loop，将master thread挂起，等待事件的发生。

　　master thread潜在问题：

　　　　随着固态硬盘的普及，IO速度提高，硬编码中InnoDB最多刷新100个脏页、合并20个缓冲会限制性能。

• 新增了innodb_io_capacit，,按其值百分比刷新对应数量的页。
• innodb_max_dirty_pages_pct：脏页占缓冲池的比例，75到80较好。

 4. 关键特性：插入缓冲、两次写、自适应哈希索引

 　　（1）插入缓冲

　　　　Insert buffer（插入缓冲）并不是缓冲池的一个部分，它和数据页一样，是物理页的一个组成部分。

　　我们知道,主键是行唯一的标识符，在应用程序中记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。因此,这样的情况下，插入操作一般很快就能完成。但是，不可能每张表上只有一个聚集索引，在更多情况下，一张表上有多个非聚集的辅助索引。

　　比如以name作为查找字段，并且name不唯一，这样的情况下产生了一个非聚集的并且不是唯一的索引。在进行插入操作时，数据页的存放还是按主键id的执行顺序存放，但是对于非聚集索引，叶子结点的插入不再是顺序的了。这时就需要离散的访问非聚集索引页，插入性能在这里变低了。然而这并不是name字段上索引的错误，因为B+树的特性决定了非聚集索引插入的离散型。

　　插入缓冲对于非聚集索引的插入和更新操作，不是每一次直接插入索引页中，而是先判断插入的非聚集索引页是否在缓存池中。如果在，则直接插入；如果不在，则先放入一个插入缓冲区中，好似欺骗数据库这个非聚集的索引已经插入到叶子结点了，然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对非聚集索引执行插入和修改操作的性能。

　　插入缓冲的使用要满足两个条件：

• 索引是辅助索引
• 索引不是唯一的
  • 辅助索引不能是唯一的，因为在把它插入到插入缓冲时，我们并不去查找索引页的情况。如果去查找肯定又会出现离散读的情况，插入缓冲就失去了意义。

　　存在的问题：

　　　　在写密集的情况下，插入缓冲会过多的占用缓冲池内存，默认情况下最大可以占用1/2的缓冲池内存。

 　　（2）两次写

　　插入缓冲带给InnoDB存储引擎性能，两次写带给InnoDB数据的可靠性。

　　当数据库宕机时，可能发生数据库正在写一个页面，而这个页只写了一部分的情况，我们称之为部分写失效。

可能会想，如果发生写失效，可以通过重做日志进行恢复。但是需要知道，重做日志中记录的是对页的物理操作，如果这个页本身已经损坏，在对其进行重做是没有意义的。这就是说，在应用重做日志前，我们需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再重做日志，这就是两次写。

　　doublewrite由两部分组成：一部分是内存中的doublewrite buffer，大小为2MB；另一部分是物理磁盘上共享表空间中连续的128个页，即两个区，大小同样为2MB。

　　当缓冲区的脏页刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer。之后通过doublewrite buffer再分两次，每次写入1MB到共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成doublewrite页的写入后，再将doublewrite buffer中的页写入到各个表空间文件中，此时的写入则是离散的。

　　如果操作系统在将页写入磁盘的过程中崩溃了，在恢复过程中，InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，将其拷贝到表空间文件，再应用重做日志。

 　　（3）自适应哈希索引

　　InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称为自适应的。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快，而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

　　需要注意的是，哈希索引只能用来搜索等值的查询，而对于其他类型，如范围查找，是不能使用的。

三. 表

1. InnoDB存储引擎表类型 ：

　　在InnoDB存储引擎表中，每张表都有个主键，如果在创建表时没有显式地定义主键，则InnoDB存储引擎会按如下方式选择或创建主键：

• 首先表中是否有非空的唯一索引，如果有，则该列即为主键。
• 不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。

2. InnoDB逻辑存储结构：

 　　所有数据都被逻辑地存放在一个空间中，我们称之为表空间。表空间又由段(segment)、区(extent)、页(page)组成，页有时也称为块。

　　表空间：

• 表空间可看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都是存放在表空间中。默认情况下InnoDB有一个共享表空间ibdata1，即所有数据都放在这个表空间内，如果启用参数innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内。
• 在启用参数后，需要注意的是：每章表的表空间内存放的只是数据、索引和插入缓冲，其他类的数据，如撤销信息、系统事务信息、二次写缓冲等还是放在原来的共享表空间内。

　　段：

• 表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。前面介绍过InnoDB存储引擎表是由索引组织的，因此数据即索引，索引即数据。那么数据段即为B+树的页节点（图4-1中的leaf node segment），索引段即为B+树的非索引节点（none-leaf node segment）。
• 注意并不是每个对象都有段。因此更准确的说，表空间是由分散的页和段组成。

　　区：

• 区是由64个连续的页组成的，每个页大小为16KB，即每个区大小为1MB。对于大的数据段，InnoDB存储引擎最多每次可以申请4个区，以此来保证数据的顺序性能。
• 在每个段开始时，先有32个页大小的碎片页来存放数据，当这些页使用完之后才是64个连续页的申请。

　　页：

• 页是InnoDB磁盘管理的最小单位。
• 常见页类型有：数据页、Undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页。

　　行：

• InnoDB存储引擎是面向行的，也就是说数据的存放按行进行存放。每个页最多存放7992行记录。 

 3. InnoDB物理存储结构 ：

　　物理意义上，InnoDB表由共享表空间、日志文件组和表结构定义文件组成。

　　InnoDB行记录格式：

　　　　Compact行记录格式：（MySQL5.0开始被引入的）

　　　　　　设计目标是能高效存放数据。简单来说，如果一个页中存放的行数据越多，其性能就越高。

　　　　　　

4. 约束                         

 （1）数据完整性

　　关系型数据库与文件系统的一个不同点是，关系数据库本身能保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制。几乎所有的关系型数据库都提供了约束机制，约束提供了一条强大而简易的途径来保证数据库中的数据完整性，数据完整性有三种形式：

• 实体完整性
  • 保证表中有一个主键。在innodb存储引擎表中，我们可以通过定义Primary key或Unique key约束来保证实体的完整性。或者我们还可以通过编写触发器来保证数据完整性。
• 域完整性
  • 保证数据的值满足特定的条件。在innodb中，域完整性可通过以下途径来保证：选择合适的数据类型可以确保一个数据值满足特定条件，外键约束，编写触发器。还可以考虑用default约束作为强制域完整性的一个方面。
• 参照完整性
  • 保证两张表之间的关系。InnoDB支持外键，因此允许用户定义外键以强制参照完整性，也可以编写触发器强制执行。

　　对于InnoDB，提供了4种约束：

• Primary Key（主键）
• Unique key
• Foreign Key（外键）
• Default
• Not null

 （2）约束和索引的区别：

　　当你创建了一个唯一索引，就创建了一个唯一的约束。但是约束和索引的概念还是有所不同的，约束更是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，有逻辑上的概念，在数据库中更是一个物理存储的方式。

（3）触发器与约束：

　　触发器的作用是在insert、delete和update命令之前或之后自动调用sql命令或者存储过程。　　

　　创建触发器命令：create trigger

　　最多可以为一个表建立6个触发器，分别为insert、update、delete的before和after各定义一个。(before和after代表触发器发生的时间，表示是在每行操作的之前发生还是之后发生)

（4）视图：

　　视图（View）是一个命名的虚表，它由一个查询来定义，可以当作表使用。与持久表不同的是，视图中的数据没有物理表现形式。

　　视图的主要用途之一是被用作一个抽象装置。程序本身不用关心基表的结构，只需要按照视图定义来获取数据或更新数据。因此,视图同时在一定程度上起到一个安全层的作用。

　　create view v_t as select * from t where id<10;

四、索引与算法（重要）

　　数据库索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

1.简单来说，数据库索引就是数据库的数据结构！进一步说则是该数据结构中存储了一张表中某一列的所有值，也就是说索引是基于数据表中的某一列创建的。总而言之：一个索引是由表中某一列上的数据组成，并且这些数据存储在某个数据结构中。

2.索引的作用。举个例子，假设有一张数据表Emplyee，该表有三列：Employee_name，Employee_age，Employee_address

表中有几万条记录。现在要执行下面这条查询语句，查找出所有名字叫“Jesus”的员工的详细信息：Select * from Employee where Employee_name='Jesus'

3.如果没有数据库索引功能，数据库系统会逐行的遍历整张表，对于每一行都要检查其Employee_Name字段是否等于“Jesus”。因为我们要查找所有名字为“Jesus”的员工，所以当我们发现了一条名字是“Jesus”的记录后，并不能停止继续查找，因为可能有其他员工也叫“Jesus”。这就意味着，对于表中的几万条记录，数据库每一条都要检查。这就是所谓的“全表扫描”（ full table scan）

4.而数据库索引功能索引的最大作用就是加快查询速度，它能从根本上减少需要扫表的记录/行的数量。

5.如何创建数据库索引。可以基于Employee表的两列创建索引即可：CREAT INDEX name_index ON Employee(Employee_name，Employee_age) 

　　如果索引太多，应用的性能可能会收到影响，如果索引太少，对查询性能又会产生影响，要找到一个合适的平衡点，这对应用的性能至关重要。

4.1 InnoDB存储引擎索引概述

　　InnoDB存储引擎支持两种常见的索引：一种是B+树索引，另一种是hash索引。

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

• B+树索引就是传统意义上的索引，这是目前关系型数据库系统中最常用、最有效的索引。B+树索引的构造类似于二叉树，根据键值快速找到数据，需要注意：B+树的B不是代表二叉树，而是代表平衡，B+树从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。

　　【注】：B+树索引并不能找到一个给定键值的具体行。B+树能找到的只是被查找数据行所在的页，然后数据库通过把页读入内存，再在内存中进行查找。

B+树是通过二叉查找树，再由平衡二叉树、B树演化而来。即 二叉查找树-->平衡二叉树--->B树--->B+树

4.2 平衡二叉树

（1）二叉查找树：左子树的键值总小于根节点的键值，右子树的键值总大于根节点的键值。因此可以通过中序遍历得到键值的排序输出。

中序遍历输出：2、3、5、6、7、8

　　对上面这棵二叉树进行查找，如查键值为8的记录：先找到根，其值为6，6<8，因此再找6的右子树，找到7，7<8，再找右子树-->8。一共找了3次。

　　如果是下面这种构造的二叉树，则查询效率不高：

 平均查找次数为(1+2+3+4+5+5)/6=3.16次，和顺序查找差不多。

　　显然图5-3 二叉查找树的效率就低了。因此若想最大性能地构建一个二叉查找树，需要这颗二叉查找树是平衡的，因此引出了新的定义——平衡二叉树，或称为AVL树。

（2）平衡二叉树（AVL树）

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

　　平衡二叉树对于查找的性能是比较高的，但不是最高的，只是接近最高性能。要达到最好的性能，需要建立一颗最优二叉树，但是最优二叉树的建立和维护需要大量操作，因此我们一般只需建立一颗平衡二叉树即可。

　　平衡二叉树对于查询的速度的确很快，但是维护一个平衡二叉树的代价非常大，通常需要一次或多次左旋和右旋来得到插入或更新后树的平衡性。

4.3 B+树

　　B+树由B树和索引顺序访问方法(ISAM)演化而来。

　　B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶节点中，各叶结点指针进行连接。

 　　看一个B+树的例子，下面这个B+树高度为2，每页可存放4条记录，扇出（fan out）为5。

　　上图，所有记录都在叶节点，并且是顺序存放的，如果我们从最左边的叶节点开始遍历，可以得到所有键值的顺序排序：5、10、15、20、25、30、50、55、60、65、75、80、85、90。

 看这个：深入浅出分析MySQL索引设计背后的数据结构

4.4 B+树索引

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

　　数据库中的B+索引可以分为聚集索引和辅助聚集索引。不管是聚集索引还是非聚集的索引，其内部都是B+树的，即高度平衡的，叶节点存放着所有的数据，聚集索引与非聚集索引不同的是，叶节点存放的是否是一整行的信息。

4.4.1 聚集索引

 　　前面提到，InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。 聚集索引就是按照每张表的主键构造一颗B+树，并且叶节点中存放着整张表的行记录数据，因此也让聚集索引的叶节点成为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树结构一样，每个数据页都通过一个双向链表来进行链接。

　　由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。在许多情况下，查询优化器非常倾向于采用聚集索引，因为聚集索引能够让我们在索引的叶节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够非常快的访问针对范围内的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。

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

　　如果要查找主键某一范围内的数据，通过叶节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。

 4.4.2 辅助索引（也称非聚集索引）

 　　对于辅助索引(非聚集索引)，叶级别不包含行的全部数据。叶节点除了包含键值以外，每个叶级别中的索引行中还包含了一个书签，该书签用来告诉InnoDB存储引擎，哪里可以找到与索引相对应的行数据。因为InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

　　辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。但通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

　　例：如果在一颗高度为3的辅助索引树中查找数据，那么需要对这颗辅助索引遍历三次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引进行3次查找，才能最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO来访问最终的一个数据页。

 4.4.3 B+树索引的管理  

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

　　查看表中索引的信息可以用SHOW INDEX语句。如我们来分析表t，之前先加一个联合索引：

　　

　　表t上有3个索引：一个主键索引、c列上的索引、b列前100个字节构成的索引。

　　每个列的含义：

• Table              ：索引所在的表名
• Non_unique   ：非唯一的索引。可以看到primary key是0，因为必须是唯一的。
• Key_name     ：索引的名称。可以通过这个名称来DROP INDEX。
• Seq_in_index ：索引中该列的位置。上面的联合索引idx_a_b就比较直观。
• Column_name：索引的列。
• Collection       ：列以什么方式存储在索引中。可以是'A'或NULL。B+树索引总是A，即排序的。如果使用了Heap存储引擎，并且建立了Hash索引，就会显示NULL，因为Hash根据Hash桶来存放索引数据，而不是对数据进行排序。
• Cardinality      ：非常关键的值，表示索引中唯一值的数目的估计值。Cadibality/表的行数应尽可能接近1，如果非常小，那么需要考虑是否还需要建这个索引。
• Sub_part          ：是否是列的部分被索引。如果看idx_b这个索引，这里显示100，表示我们只索引b列的前100个字符。如果索引整个列，则该字段为NULL。
• Packed             ：关键字如何被压缩。如果没有被压缩，则为NULL。
• Null                   ：是否索引的列含有NULL值。如果有则是Yes。
• Index_type        ：索引的类型。InnoDB存储引擎只支持B+树索引，所以这里显示的都是BTREE。
• Comment          ：注释。

　　cardinality值非常关键，优化器会根据这个值来判断是否使用这个索引。但这个值并非实时更新的，因此并不准确。

　　如果需要更新索引Cardinality的信息，可以使用analyze table命令。

　　建议在一个非高峰时间，对应用程序下的几张核心表做analyze table操作，这能使优化器和索引更好的为你工作。

 4.5 B+树索引的使用

4.5.1 什么时候使用B+树索引？

　　（高选择、取出表中少部分的数据）

　　访问表中很少一部分行时，使用B+树索引才有意义。对于性别、地区、类型字段，他们可取值的范围很小，即低选择性，这时添加B+树索引是完全没有必要的。相反如果某个字段的取值范围很广，几乎没有重复，即高选择性，则此时使用B+树索引是最适合的，例如姓名。

　　因此，当访问高选择性字段并从表中取出很少一部分行时，对这个字段添加B+树索引是非常有必要的。但是如果出现了访问字段是高选择性的，但是取出的行数据占表中大部分的数据时，这是MySql就不会使用B+树索引。

　　 

　　MySQL数据库的优化器会预估查询可能得到的行，如果大于某一个值，则B+树会选择全表的扫描。这个值一般在20%（即当取出的数据量超过表中数据的20%，优化器就不会使用索引）。但是预估的返回行数是不准确的，因此有时优化器的判断是不正确的，应相信自己的判断强制使用索引。

 4.5.2  顺序读、随机读和预读取

• 顺序读：是指顺序的读取磁盘上的块。

• 随机读：是指访问的块不是连续的，需要磁盘的磁头不断移动。当前传统磁盘的瓶颈之一就是随机读取的速度较低。

在数据库中顺序读与随机读的概念：

• 顺序读：是指根据索引的叶节点数据就能顺序地读取所需的行数据。这个顺序只是逻辑地顺序读，在物理磁盘上可能还是随机读取。但是相对来说，物理磁盘上的数据还是比较顺序的，因为是根据区来管理的，区是64个连续页。如根据主键进行读取，或许通过辅助索引的叶节点就能读取到数据。
• 随机读：一般是指访问辅助索引叶节点不能完全得到结果的，需要根据辅助索引叶节点中的主键去找实际的行数据。因为一般来说，辅助索引和主键所在的数据段不同，因此访问是随机的方式。

 　　　　为了提高读取的性能，InnoDB存储引擎引入了预读取技术。预读取是指通过一次IO请求将多个页读取到缓冲池中，并且估计预读取的多个页马上会被访问。传统的IO请求每次只读取1个页，在传统机械硬盘较低的IOPS下，预读技术可以大大提高读取的性能。

 4.5.3 辅助索引的优化使用

 　　辅助索引的叶节点包含有主键，但是辅助索引的叶并不包含完整的行信息。因此，InnoDB存储引擎总是会先从辅助索引的叶节点判断是否能得到所需的数据。   

　　看一个例子：

creat table t (a int not null, b varchar(20), primary key(a), key(b));
insert into t select 1, 'kangaroo';
insert into t select 2, 'dolphin';
insert into t select 3, 'dragon';
insert into t select 2, 'antelope';

　　

 　　因为辅助索引中包含了主键a的值，因此访问b列上的辅助索引就能得到a的值，那这样就可以得到表中所有的数据。并且在通常情况下，一个辅助索引页中能存放的数据比主键页上存放的数据多，因此优化器优先选择了辅助索引。如果我们解释这条SQL语句，可得到如下结果：

　　

　　可以看到，优化器最终选择的索引是b，如果想得到对列a排序的结果，需要对其进行order by操作，这样优化器会直接走主键，避免对a列的排序操作。

 　　

或者强制使用主键来得到结果：

　　InnoDB的所有辅助索引都引用主键作为data域.

　　将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id = 13”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

4.5.4 联合索引

 　　联合索引是指对表上的多个列做索引。（前面我们讨论的情况都是只对表上的一个列进行了索引）

　　联合索引的创建方法和之前介绍的一样：

alter table t add key idx_a_b(a,b);

　　从本质上来说，联合索引还是一颗B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。

　　讨论两个整型列组成的联合索引，假定两个键值的名称分别为a，b，如下图所示：

　　

　　从上图可以看到多个键值的B+树情况，其实和之前讨论的单个键值没什么不同，键值都是排序的，通过叶节点可以逻辑上顺序地读出所有数据。就上面例子来说即：(1,1)，(1,2)，(2,1)，(2,4)，(2,4)，(3,1)，(3,2)。数据就按(a,b)的顺序进行了存放。

　　因此，对于查询 SELECT * FROM TABLE WHERE a=xxx and b=xxx，显然可以使用(a,b)这个联合索引。对于单个的a列查询 SELECT * FROM TABLE WHERE a=xxx 也是可以使用(a,b)索引。但对于b列的查询 SELECT * FROM TABLE WHERE b=xxx 不可以使用这颗B+树索引。因为叶节点上的b值为1,2,1,4,1,2，显然不是排序的，因此对于b列的查询使用不到(a,b)的索引。

 　　联合索引的第二个好处是，可以对第二个键值进行排序。例如，在很多情况下我们都需要查询某个用户的购物情况，并按照时间排序，去除最近3次的购买记录，这是使用联合索引可以避免多一次的排序操作，因为索引本身在叶节点已经排序了。

　　【注】：对于相同的第一个键值的数据，第二个键值是排好序的。

　　　　　　对于单个列a的查询往往使用单个键的索引，因为其叶节点包含单个键值，能存放的记录更多。

 4.6  哈希算法

 　　InnoDB存储引擎中自适应哈希索引使用的是散列表（Hash Table）的数据结构。但是散列表不只存在于自适应哈希中，在每个数据库中都存在。设想一个问题，当前我的内存为128G，我怎么得到内存中的某一个被缓存的页呢？内存中查询速度很快，但是也不可能遍历所有内存。这时对于字典操作，O(1)的散列技术就能有很好的用武之地。

 4.6.1 哈希表        

 　　哈希表也成散列表，由直接寻址表改进而来，所以我们先来看直接寻址表。当关键字的全域U比较小时，直接寻址是一种简单而有效的技术。假设某引用要用到一个动态集合，其中每个元素都有一个取自U的关键字，同时假设没有两个元素具有相同的关键字。

　　用一个数组(即直接寻址表)T[0...m-1]表示动态集合，其中每个位置(槽)对应全域U中的一个关键字。如下图，槽k指向集合中一个关键字为k的元素，如果该集合中没有关键字为k的元素，则T[k]=null。

 　　

　　直接寻址技术存在一个很明显的问题：如果全域U很大，由于计算机的可用容量限制，难以在机器中存储大小为U的表T。如果实际需要的关键字集合K相对于U来说很小，分配给T的大部分空间都要浪费掉。

　　因此哈希表出现了，该元素处于h(k)中，亦即利用哈希函数h，根据关键字k计算出槽的位置。函数h将关键字域U映射到哈希表T[0...m-1]的槽位上，如下图：

　　

　　哈希表技术很好地解决了直接寻址遇到的问题，但存在问题：两个关键字可能映射到同一个槽上，这种情况称之为碰撞，数据库中一般采用最简单的碰撞解决技术，称之为链接法。

　　在链接法中，把散列到同一槽中的所有元素都放到一个链表中，如下图所示。槽j中有一个指针，它指向由所有散列到j的元素构成的链表的头；如果不存在这样的元素，则j中为NULL。

　　

　　哈希函数：数据库中一般使用除法散列，通过取k除以m的余数，来将关键字k映射到m个槽的某一个去。h(k)=k mod m

 4.6.2 自适应哈希索引

 　　自适应哈希索引是数据库自己创建并使用的，DBA本身并不能对其进行干预。当在配置文件中启用了参数innodb_adaptive_hash_index后，数据库会自动创建槽数为innodb_buffer_pool_size/256个的哈希表。

　　例如，设置innodb_buffer_pool_size为10MB，则启动InnoDB存储引擎会创建一个10M/156个槽的自适应哈希表。

　　自适应哈希索引经哈希函数映射到一个哈希表中，因此自适应哈希索引对于字典类型的查找非常快速，但是只能适用于等值查询，对于范围查找无能为力。

五、锁（重要）

　　锁机制用于管理对共享资源的并发访问。

5.1 InnoDB存储引擎中的锁

5.1.1 锁的类型   

　　InnoDB实现了两种标准的行级锁：

• 共享锁：允许事务读一行数据。
• 排他锁：允许事务删除或者更新一行数据。

　　当一个事务已经获得了行r的共享锁，那么另外的事务可以立即获得行r的共享锁，因为读取并没有改变行r的数据，我们称这种情况为锁兼容。但如果有事务想获得行r的排他锁，则它必须等待事务释放行r上的共享锁——这种情况我们成为锁不兼容。

　　InnoDB支持多粒度锁定，这种锁定允许在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上的加锁操作，InnoDB支持一种额外的锁方式，我们称之为意向锁。意向锁是表级别的锁。其设计目的是为了在一个事务中解释下一行将被请求的锁的类型。

　　InnoDB支持两种意向锁：

• 意向共享锁：事务想要获得一个表中某几行的共享锁。
• 意向排他锁：事务想要获得一个表中某几行的排他锁。

　　因为InnoDB至此的是行级别的锁，所以意向锁其实不会阻塞除全表扫描以外的任何请求。

 5.1.2 一致性的非锁定读操作  

 一致性的非锁定行读是指InnoDB通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行删除和更新操作，这时读取操作不会因此而等待行上锁的释放，相反，InnoDB会去读取行的一个快照数据。

 　　

　　之所以称为非锁定读，是因为不需要等待访问的行上X锁的释放。快照数据是指该行之前版本的数据，该实现是通过Undo段来实现。而Undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有必要对历史的数据进行修改。

 　　非锁定读的机制大大提高了数据读取的并发性，在InnoDB默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。但是在不同事物隔离级别下，读取的方式不同，并不是每个事务级别下读取的都是一致性读。同样，即使都是使用一致性读，但是对于快照数据的定义也不同。

　　通过上图可以知道，快照数据其实就是当前行数据之前的历史版本，可能有多个版本。一个行可能有不止一个快照数据，我们称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（MVCC）。

　　在Read Commited和Repeatable Read(InnoDB默认)下，InnoDB使用非锁定的一致性读。然而，对于快照数据的定义却不相同。Read commited下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。在Repeatable事务隔离级别下和Repeatable read级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

　　对于Read commited级别，这违反了事务ACID的隔离性。（隔离性：一个事务的影响在该事务提交前对其他事务都不可见）

5.1.3 对读取加锁   

 　　InnoDB存储引擎对于select持2种加锁操作：

• select...for update ：对读取的行记录加一个排它锁（X锁）。其他事务想在这些行上加任何锁都会被阻塞
• select...lock in share mode：对读取的行记录加一个共享锁（S锁）。其他事务可以向被锁定的记录加S锁，但如果加X锁，则被阻塞。

对于一致性非锁定读，即使读取的行被使用select...for update，也是可以进行读取的。另外，select...for update、select...lock in share mode必须在一个事务中，当事务提交了锁也就释放了。禁止自动提交，在使用上述两句selset锁定语句时，务必加上begin、start transaction或者set autocommit=0。

 5.1.4 外键和锁     

　　在InnoDB存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB存储引擎自动对其加一个索引，因为这样可以避免表锁。（但Oracle不会自动添加索引，用户必须自己手动添加，这也是导致很多死锁问题产生的原因。）

　　对于外键值的插入或者更新，首先需要查询父表中的记录，即select父表。但是对于父表的select操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题，因此这时使用的是select...lock in share mode方式，主动对父表加一个S锁，如果这时父表已经加X锁，那么子表上的操作会被阻塞。例如：

　　

5.1.5 锁的算法     

 　　InnoDb有3中行锁的算法设计：

• Record Lock  ：单个行记录上的锁。
• Gap Lock       ：间隙锁，锁定一个范围，但不包含记录本身。
• Next-key Lock：上面两种锁的合体，锁定一个范围，并且锁定记录本身。

　　Record Lock总是会去锁住索引记录。如果InnoDb存储引擎表建立的时候没有设置任何一个索引，这时InnoDB会使用隐式的主键来进行锁定。

　　Next-key Lock为默认算法，InnoDB会自己选一个最小算法模型。

 5.1.6 锁问题                             

 　　通过锁可以实现事务隔离性的要求，使得事务可以并发地工作。因为事务隔离性的要求，锁只会带来3种问题：丢失更新、脏读、不可重复读。如果可以防止这三种情况的发生，那将不会产生并发异常。

（1）丢失更新

 　　出现下面的情况时，就会发生丢失更新：

1. 1. 事务T1查询一行数据，放入本地内存，并显示给一个终端用户User1。
   2. 事务T2也查询该行数据，并将取得的数据显示给终端用户User2。
   3. User1修改这行记录，更新数据库并提交。
   4. User2修改这行记录，更新数据库并提交。

 　　显然，这个过程中用户User1的修改更新操作“丢失”了。（例如如果银行转账丢失了更新操作，后果将很严重。）

　　要避免丢失更新发生，其实需要让这种情况下的事务变成串行操作，而不是并发的操作。即在上述4种的第1种情况下，对用户读取的记录加上一个排他锁，同样，发生第2种情况下的操作时，用户也需要加上一个排他锁。这种情况下，第2步就必须等待第1、3步完成，最后完成第4步。

（2）脏读

 　　脏数据指在缓冲池中被修改的数据，并且还没有被提交。

　　（脏数据和脏页有所不同，脏页：指在缓冲池中已被修改的页，但还没有刷新到磁盘，即数据库实例内存中的页和磁盘的页中的数据是不一致的。）

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

　　（而对于脏页的读取是非常正常的。脏页是因为数据库实例内存和磁盘的异步同步造成的，这并不影响数据的一致性。）

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

　　脏读很少发生，因为脏读发生的条件是需要需要隔离级别为read uncommited，而一般数据库隔离级别至少设置为read commited。

 （3）不可重复读

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

　　一般来说不可重复度的问题是可以接受的，因为其读到的是已经提交的数据，本身并不会带来很大的问题。因此，很多数据库厂商将数据库事务的默认隔离级别设置成READ COMMITTED，在这种隔离级别下允许不可重复读的现象。

　　InnoDB存储引擎中，通过使用Next-Key Lock算法来避免不可重复读的问题。在Next-Key Lock算法下，对于索引的扫描，不仅仅是锁住扫描到的索引，而且还能锁住这些索引覆盖的范围。因此对于这个范围内的插入都是不允许的，这样就避免了另外的事务在这个范围内插入数据导致的不可重复读的问题。因此，InnoDB存储引擎的默认事务隔离级别是READ REPEATABLE，采用Next-Key Lock算法，就避免了不可重复读的现象。

　　参考数据库事务隔离级别（脏读、幻读、不可重复读）

　　数据库通过锁机制解决并发访问的问题。根据锁定对象不同：分为行级锁和表级锁；根据并发事务锁定的关系上看：分为共享锁定和独占锁定，共享锁定会防止独占锁定但允许其他的共享锁定。而独占锁定既防止共享锁定也防止其他独占锁定。为了更改数据，数据库必须在进行更改的行上施加行独占锁定，insert、update、delete和selsct for update语句都会隐式采用必要的行锁定。

　　但是直接使用锁机制管理是很复杂的，基于锁机制，数据库给用户提供了不同的事务隔离级别，只要设置了事务隔离级别，数据库就会分析事务中的sql语句然后自动选择合适的锁。 
　　不同的隔离级别对并发问题的解决情况如图：

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

 　　不可重复读与幻读的区别：不可重复读的重点是修改： 同样的条件，你读取过的数据，再次读取出来发现值不一样了。幻读的重点在于新增或者删除：同样的条件，第 1 次和第 2 次读出来的记录数不一样。

1、丢失更新：指一个事务正在访问修改数据，与此同时另一个事务也在访问修改此数据，两个事务互相不知道对方的存在。假如在是事务A修改数据前事务B已经修改过1次数据，那么事务A最终只能查询到假数据，丢失了更新操作。

2、脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

例如：

张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。

与此同时，

事务B正在读取张三的工资，读取到张三的工资为8000。

随后，

事务A发生异常，而回滚了事务。张三的工资又回滚为5000。

最后，

事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

3、不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

例如：

在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
与此同时，
事务B把张三的工资改为8000，并提交了事务。
随后，
在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

4、幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

例如：

目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。
此时，
事务B插入一条工资也为5000的记录。
这时，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。

参考mysql锁（七）丢失更新，脏读，不可重复读，幻读

 5.2 阻塞

 　　因为不同锁之间的兼容性关系，所以在有些时刻，一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源。在InnoDB存储引擎的源代码中，用Mutex数据结构来实现锁，在访问资源前需要用mutex_enter函数进行申请，在资源访问或修改完毕后立即执行mutex_exit函数。当一个资源已被一个事务占有时，另一个事务执行mutex_enter函数会发生等待，这就是阻塞。阻塞并不是意见坏事，阻塞是为了保证事务可以并发并且正常运行。

5.3 死锁

 　　如果程序是串行的，那么不可能发生死锁。死锁只发生于并发的情况，数据库就是一个并发进行着的程序，因此可能会发生死锁。

　　（死锁的一个经典案例：A等待B，B在等待A。）

　　InnoDB存储引擎有一个后台的锁监控线程，该线程负责查看可能的死锁问题，并自动告知用户。 InnoDB存储引擎并不会回滚大部分的错误异常，但是死锁除外。发现死锁后，InnoDB存储引擎会马上回滚一个事务。

 5.4 锁升级

　　锁升级是指将当前锁的粒度降低。例如，数据库可以把一个表的1000个行锁升级成一个页锁，或者将页锁升级为表锁。如果数据库的设计中认为锁是一种稀有资源，而且想避免锁的开销，那数据库中会频繁出现锁升级现象。

　　SQL Server数据库可能发生锁升级；InnoDB存储引擎不存在锁升级的问题。在InnoDB存储引擎中，1个锁的开销与1000000个锁是一样的，都没有开销。

六、事务（重要）

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

　　ACID：原子性，一致性，隔离性，持久性

　　原子性：原子性是指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作执行都成功，才算整个事务成功。如果事务中任何一个sql语句执行失败，那么已经执行成功的sql语句也必须撤销，数据库状态应该退回到执行事务前的状态。

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

　　隔离性：一个事务的影响在该事务提交前对其他事务都不可见----这通过锁来实现

　　持久性：事务一旦提交,其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。

6.1 事务的实现

　　隔离性由锁来实现；原子性、一致性和持久性通过数据库的redo和undo来完成。

　（1）redo（重做）

　　InnoDB中，事务日志通过重做（redo）日志文件和InnoDB存储引擎的日志缓冲来实现。当开始一个事务时,会记录该事务的一个LSN（日志序列号），当事务执行时，会往InnoDB的日志缓冲里插入事务日志，当事务提交时，必须将innoDB存储引擎的日志缓冲写入磁盘。也就是在写数据前，需要先写日志。这种方式称为预写日志方式。

　　InnoDB通过预写日志的方式来保证事务的完整性。这意味着磁盘上存储的数据页和内存缓冲池中的数据页是不同步的，对于内存缓冲池中页的修改，先是写入重做日志文件，然后再写入磁盘，因此是一种异步的方式。

　（2）undo（撤销）

　　重做日志记录了事务的行为，可以很好地通过其进行“重做”，但是事务有时还需要撤销，这就需要undo。

　　undo与redo正好相反，对数据库进行修改时，数据库不但会产生redo，而且会产生一定量的undo，即使你执行的事务或语句由于某种原因失败了，或者如果你用一条rollback语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。

　　与redo不同的是，redo存放在重做日志文件中，undo存放在数据库内部的一个特殊段(segment)中，称为undo段，undo段位于共享表空间内。

　　我们通常对undo有这样的误解：undo用于将数据库物理地恢复到执行语句或事务之前的样子——但事实并非如此。数据库只是逻辑地恢复到原来的样子，所有修改都被逻辑地取消，但是数据结构本身在回滚之后可能大不相同，因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对于数据记录的并发访问。如一个事务在修改当前页中某几条记录，但同时还有别的事务在对同一页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

　　例如：我们的事务执行了一个INSERT 10万条记录的SQL语句，这条语句可能会导致分配一个新的段，即表空间会增大。如果我们执行ROLLBACK时，会将插入的事务进行回滚，但是表空间的大小并不会因此而收缩。因此，当InnoDB存储引擎回滚时，它实际上做的是与先前相反的工作。对于每个INSERT，InnoDB存储引擎会完成一个DELETE；对于每个DELETE，InnoDB存储引擎会执行一个INSERT；对于每个UNDATE，InnoDB存储引擎会执行一个相反的UPDATE，将修改前的行放回去。

6.2 事务控制语句

　　在MySQL命令的默认设置下，事务都是自动提交的，即执行SL语句后就会马上执行COMMIT操作。因此开始一个事务，必须使用BEGIN、START TRANSACTION，或者执行SET AUTOCOMMIT=0，以禁止当前会话的自动提交。

• START TRANSACTION | BEGIN：显示地开启一个事务。
  • START TRANSACTION、BEGIN语句都可以在mysql命令行下显式地开启一个事务。但是在存储过程中，MySQL分析会自动将BEGIN识别为BEGIN...END。因此在存储过程中，只能使用START TRANSACTION语句来开启一个事务。      
• COMMIT：COMMIT会提交你的事务，并使得已对数据库做的所有修改称为永久性的。
• ROLLBACK：回滚会结束你的事务，并撤销正在进行的所有未提交的修改。
• SAVEPOINT identifier：SAVEPOINT允许你在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT。
• RELEASE SAVEPOINT identifier：删除一个事务的保存点，当没有一个保存点执行这条语句时，会抛出一个异常。
• ROLLBACK TO [SAVEPOINT] identifier：这个语句与SAVEPOINT命令一起使用。可以把事务回滚到标记点，而不回滚在此标记点之前的任何工作。
• SET TRANSACTION：这个语句用来设置事务的隔离级别。InnoDB存储引擎提供的事务隔离级别有：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。

6.3 事务的隔离级别

　　四个隔离级别：

• read uncommitted
• read commited
• repeatable read
• serializable

　　InnoDB的默认隔离级别是repeatable read，但是与标准sql不同的是，innodb在repeatable read级别下，使用next-key lock锁的算法，因此避免幻读的产生。所以,innodb在repeatable read的事务隔离级别下已经能完全保证事务的隔离性要求，即达到sql标准的serializable隔离级别。

　　隔离级别越低，事务请求的锁越少，或者保持锁的时间就越短。这也是为什么大多数数据库系统默认的事务隔离级别是read committed。

　　在serializable的事务隔离级别，InnoDB存储引擎会对每个select语句后自动加上lock in share mode，即给每个读取操作加一个共享锁。因此这个事务隔离级别下，读占用锁了，一致性的非锁定读不再予以支持。因为InnoDB存储引擎在repeatable read隔离级别下就可以达到3°的隔离，所以一般不在本地事务中使用serialiable的隔离级别，serialiable的隔离级别主要用于InnoDB存储引擎的分布式事务。

6.4 分布式事务

　　Innodb支持XA事务，通过XA事务可以来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源参与一个全局的事务中。事务资源通常是关系型数据库系统，但也可以使其他类型的资源。全局事务要求在其中所有参与的事务要么都提交，要么都回滚，这对于事务原有的ACID要求又有了提高，另外，在使用分布式事务时，innoDB的事务隔离级别必须设置为serializable。

　　分布式事务由一个或者多个资源管理器、一个事务管理器以及一个应用程序组成。

• 资源管理器：提供访问事务资源的方法.通常一个数据库就是一个资源管理器。
• 事务管理器：协调参与全局事务中的各个事务.需要和参与全局事务中的所有资源管理器进行通信。
• 应用程序：定义事务的边界,指定全局事务中的操作。