MySQL高级进阶

MySQL高级进阶

一、基本概念

• 数据库：物理操作系统文件或其他形式文件类型的集合。

• 数据库实例：MySQL数据库由后台进程以及一个共享内存区组成。

  数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合；数据库实例是程序，是位于用户和操作系统之间的一层数据管理软件，用户对数据库数据的任何操作，包括数据库定义、数据查询、数据维护、数据库运行控制等都是在数据实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。

       MySQL被设计为一个单进程多线程架构的数据库，MySQL数据库实例在系统上的表现就是一个进程。MySQL数据库是按照/etc/my.cnf → /etc/mysql/my.cnf → /usr/local/mysql/etc/my.cnf → ~/.my.cnf的顺序读取配置文件的。在Linux环境下，配置文件一般放在/etc./my.cnf下。配置文件中有一个配置参数datadir，该参数指定了数据库所在的路径。在Linux操作系统下默认datadir为/usr/local/mysql/data。

连接MySQL操作是一个连接进程和MySQL数据库实例进行通信。常用的进程通信方式有管道、命名管道、命名字、TCP/IP套接字、UNIX域套接字。TCP/IP套接字方式是MySQL数据库在任何平台下都提供的连接方式，也是网络中使用得最多的一种方式。

二、MySQL组成

• 连接池组件

• 管理服务和工具组件

• SQL接口组件

• 查询分析器组件

• 优化器组件

• 缓冲组件

• 插件式存储引擎

• 物理文件

三、MySQL存储引擎

存储引擎是基于表的，而不是数据库。

InnoDB存储引擎

InnoDB存储引擎支持事务，其设计目标主要是面向在线事务处理(OLTP, Online Transaction Processing)的应用。其特点是行锁设计、支持外键，并支持类似于Oracle的非锁定读，即默认读取操作不会产生锁。

InnoDB通过使用多版本并发控制(MVCC, Multi-Version Concurrency Control)来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为Repeatable级别。同时使用一种被称为next-key locking的策略来避免幻读(phantom)现象的产生。InnoDB存储引擎还提供了插入缓存(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead)等高性能和高可用的功能。

对于表中数据的存储，InnoDB存储引擎采用了聚集(clustered)的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显示地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。

实践证明，InnoDB存储引擎具备高可用性、高性能以及高可扩展性。

MyISAM存储引擎

MyISAM存储引擎不支持事务、表锁设计，支持全文索引，主要面向一些OLAP(On-line Analytical Processing)数据库应用。MyISAM的缓冲池只缓存索引文件，而不缓存数据文件，这和绝大多数数据库都非常的不同。MyISAM存储引擎由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。

Memory存储引擎

Memory存储引擎(之前称HEAP存储引擎)将表中的数据存放在内存中，如果数据库重启或发生故障崩溃，表中的数据将会消失。它非常适合用于存储临时数据的临时表，以及数据仓库中的维度表。Memory存储引擎默认使用哈希索引，而不是我们熟悉的B+树索引。

Memory存储引擎非常快，但它只支持表锁，并发性能较差，并且不支持TEXT和BLOB。最重要的是，存储变长字段(varchar)时是按照定长字段(char)的方式进行的，因此会浪费内存，不过eBay工程师已解决此问题。

MySQL数据库使用Memory存储引擎作为临时表来存+间结果含有TEXT或BLOB列类型字段，则MySQL数据库会把其转换到MyISAM存储引擎表而存放到磁盘中。

Archive存储引擎

Archive存储引擎只支持insert和select操作，从MySQL 5.1开始支持索引。Archive存储引擎使用zlib算法将数据行(row)进行压缩后存储，压缩比一般可以达1:10。Archive存储引擎使用行锁来实现高并发的插入操作，但是其本身并不是事务安全的存储引擎，其设计目标主要是提供高速的插入和压缩功能。

Federated存储引擎

Federated存储引擎并不存放数据，它只是指向一台远程MySQL数据库服务器上的表。目前只支持MySQL数据库表，不支持异构数据库表。

Maria存储引擎

Maria存储引擎是由MySQL创始人Michael Widenius新开发的引擎，设计目标主要是用来取代原有的MyISAM存储引擎，从而成为MySQL的默认存储引擎。Maria支持缓存数据和索引文件，应用了行锁设计，提供了MVCC功能，支持事务和非事务安全的选项，以及更好的BLOB字符类型的处理性能。

四、InnoDB存储引擎详解

InnoDB是事务安全的MySQL存储引擎，设计上采用了类似于Oracle数据库的架构。通常来说，InnoDB存储引擎是OLTP应用中核心表的首选存储引擎。其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，同时被设计用来最有效地使用内存和CPU。

• 后台线程

InnoDB存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务。Master Thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲(insert buffer)、UNDO页的回收等。在InnoDB存储引擎中大量使用了AIO(Async IO)来处理写IO请求，这样可以极大提高数据库的性能，而IO Thread的工作主要是负责这些IO请求的回调(call back)处理。IO Thread分别有write、read、insert buffer和log IO Thread。

事务被提交后，其所使用的undolog可能不再需要，因此需要Purge Thread来回收已经使用并分配的undo页。在InnoDB 1.1版本之前，purge操作仅在InnoDB存储引擎的Master Thread中完成。而从InnoDB 1.1版本开始，purge操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作，从而提高CPU的使用率以及提升存储引擎的性能。从InnoDB 1.2开始，InnoDB支持多个Purge Thread，这样做的目的是为了进一步加快undo页的回收。同时由于Purge Thread需要离散地读取undo页，这样也能更进一步利用磁盘的随机读取性能。

Purge Cleaner Thread是在InnoDB 1.2.x版本引入的。其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

• 内存

       InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。为了协调CPU速度和磁盘速度的鸿沟，基于磁盘的的数据库系统通常使用缓冲池技术来提高数据库的性能。

对于数据库中页的修改操作，首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为checkpoint的机制刷新回磁盘。

缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲(insert buffer)、自适应哈希索引(adaptive hash index)、InnoDB存储的锁信息(lock info)、数据字典信息(data dictionary)等，不能简单地认为，缓冲池只是缓存索引页和数据页，它们只是占缓冲池很大的一部分而已。

• LRU List、Free List、Flush List

       数据库的缓冲池是通过LRU(Latest Recent Used, 最近最少使用)算法来进行管理的。即最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。在InnoDB存储引擎中，缓冲池中页的大小默认为16KB，同样使用LRU算法对缓冲池进行管理。并且InnoDB存储引擎对LRU算法进行了一些改进，LRU列表中还加入了midpoint位置。新读取到的页，虽然是最新访问的页，但并不是直接插入到LRU列表的首部，而是放入到LRU列表的midpoint位置。这个算法在InnoDB存储引擎下称为midpoint insertion strategy。默认配置下，该位置在LRU列表长度的 5/8处。把midpoint之后的列表称为old列表，之前的表称为new列表。可以简单地理解为new列表中的页都是最为活跃的热点数据。

改进之后的LRU算法的优点：若直接读取到的页放入到LRU的首部，那么某些SQL操作可能会使缓冲池中的页被刷新出，从而影响缓冲池的效率。常见的这类操作为索引或数据的扫描操作。这类操作需要访问表中的许多页，甚至是全部的页，而这些页通常来说又仅仅在这次查询操作中需要，并不是活跃的热点数据。如果页被放入LRU列表的首部，那么非常可能将所需要的热点数据页从LRU列表中移除，而在下一次需要读取该页时，InnoDB存储引擎需要再次访问磁盘。为了解决这个问题，InnoDB存储引擎引入了另一个参数来进一步管理LRU列表，这个参数是innodb_old_blocks_time，用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。

LRU列表用来管理已经读取的页，但当数据库刚启动时，LRU列表是空的，即没有任何的页。这时页都存放在Free列表中。当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中。否则根据LRU算法，淘汰LRU列表末端的页，将该内存空间分配给新的页。当页从LRU列表的old部分加入到new部分时，称此时发生的操作为page made young，而因为innodb_old_blocks_time的设置而导致页没有从old部分移动到new部分的操作称为page not made young。

InnoDB从1.0.x开始支持压缩页的功能，即将原本16KB的页压缩为1KB、2BK、4KB、8KB。对于非16KB的页，是通过unzip_LRU列表进行管理的，通过伙伴算法进行内存的分配。例如从缓冲池中申请大小为4KB的页：

1. 检查4KB的unzip_LRU列表，检查是否有可用的空闲页；

2. 若有，则直接使用；

3. 否则，检查8KB的unzip_LRU列表；

4. 若能够得到空闲页，将页分为2个4KB页，存放到4KB的unzip_LRU列表；

5. 若不能得到空闲页，从LRU列表中申请一个16KB的页，将页分为1个8KB的页、2个4KB的页，分别存放到对应的unzip_LRU列表中。

       在LRU列表中的页被修改后，称该页为脏页(dirty page)，即缓冲池中的页和磁盘上的页的数据产生不一致。数据库通过checkpoint机制将脏页刷新回磁盘。Flush列表中的页即为脏页列表。脏页既存在于LRU列表中，也存在于Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响。

重做日志缓冲(redo log buffer)存放着InnoDB存储引擎的重做日志信息，它按照一定的频率将重做日志刷新到重做日志文件。默认8MB的重做日志缓冲池足以满足绝大多数的应用。重做日志在以下三种情况下会将重做日志缓冲区中的内容刷新到外部磁盘的重做日志文件中。

1. Master Thread每一秒将重做日志缓冲刷新到重做日志文件；

2. 每个事务提交时会将重做日志缓冲刷新到重做日志文件；

3. 当重做日志缓冲池剩余空间小于12时，重做日志缓冲刷新到重做日志文件。

• Checkpoint技术

       为了避免发生数据丢失的问题，当前事务数据库系统普遍采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。

Checkpoint(检查点)可以缩短数据库的恢复时间；缓冲池不够用时，可将脏页刷新到磁盘；重做日志不可用时，刷新脏页。

对于InnoDB存储引擎而言，其是通过LSN(Log Sequence Number)来标记版本的。LSN是8字节的数字，其单位是字节。每个页有LSN，重做日志中也有LSN，Checkpoint也有LSN。InnoDB内部有两种Checkpoint，Sharp Checkpoint和Fuzzy Checkpoint。Sharp Checkpoint发生在数据库关闭时将所有的脏页都刷新回磁盘，也是默认的工作方式，即innodb_fast_shutdown=1。若数据库在运行时也使用Sharp Checkpoint，那么数据库的可用性就会受到很大的影响。故在InnoDB存储引擎内部使用Fuzzy Checkpoint进行页的刷新，即只刷新一部分脏页，而不是刷新所有的脏页回磁盘。

       InnoDB存储引擎的主要工作都是在一个单独的后台线程Master Thread中完成的。Master Thread具有最高的线程优先级别。其内部由多个循环(loop)组成：主循环(loop)、后台循环(backgroup loop)、刷新循环(flush loop)、暂停循环(suspend loop)。Master Thread会根据数据库运行的状态在loop、background loop、flush loop和suspend loop中进行切换。

      Loop被称为主循环，因为大多数的操作是在这个循环中，其中有两大部分的操作——每秒的操作和每10秒的操作。

• 每秒一次的操作

• 日志缓冲刷新到磁盘，即使这个事务还没有提交(总是)；

• 合并插入缓冲(可能)；

• 至多刷新100个InnoDB的缓冲池中的脏页到磁盘(可能)；

• 如果当前没有用户活动，则切换到background loop(可能)。

       即使某个事务还没有提交，InnoDB存储引擎仍然每秒会将重做日志缓冲中的内容刷新到重做日志文件，正因为此，大事务提交的时间也是很短的。

       合并插入缓冲(Insert Buffer)并不是每秒都会发生的。InnoDB存储引擎会判断当前一秒发生的IO次数是否小于5次，如果小于5次，InnoDB认为当前的IO压力很小，可以执行合并插入缓冲的操作。

       刷新100个脏页也不是每秒都会发生的。InnoDB存储引擎通过判断当前缓冲池中脏页的比例(buf_get_modified_ratio_pct)是否超过了配置文件中innodb_max_dirty_pages_pct这个参数(默认为90，代表90%)，如果超过了这个阈值，InnoDB存储引擎认为需要做磁盘同步的操作，将100个脏页写入磁盘中。

• 每10秒的操作

• 刷新100个脏页到磁盘(可能)；

• 合并至多5个插入缓冲(总是)；

• 将日志缓冲刷新到磁盘(总是)；

• 删除无用的Undo页(总是)；

• 刷新100个或者10个脏页到磁盘(总是)。

       在以上的过程中，InnoDB存储引擎会先判断过去10秒之内磁盘的IO操作是否小于200次，如果是，InnoDB存储引擎认为当前有足够的磁盘IO操作能力，因此将100个脏页刷新到磁盘。接着，InnoDB存储引擎会合并插入缓冲。不同于每秒一次操作时可能发生的合并插入缓冲操作，这次的合并插入操作总会在这个阶段进行。之后，InnoDB存储引擎会再进行一次将日志缓冲刷新到磁盘的操作。这和每秒一次时发生的操作是一样的。

       接着，InnoDB存储引擎会进行一步执行full purge操作，即删除无用的Undo页。对表进行update、delete这类操作时，原先的行被标记为删除，但是因为一致性读(consistent read)的关系，需要保留这些行的版本信息。但是在full purge过程中，InnoDB存储引擎会判断当前事务系统中已被删除的行是否可以删除，如果有时候可能还有查询操作需要读取之前版本的undo信息，如果乐意删除，InnoDB会立即将其删除。InnoDB存储引擎在执行full purge操作时，每次最多尝试回收20个undo页。

InnoDB存储引擎会判断缓冲池脏页的比例(buf_get_modified_ratio_pct)，如果有超过70%的脏页，则刷新100个脏页到磁盘，如果脏页的比例小于70%，则刷新10%的脏页到磁盘。

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

• background loop执行的操作

• 删除无用的Undo页(总是)；

• 合并20个插入缓冲(总是)；

• 跳回到主循环(总是)；

• 不断刷新100个页直到符合条件(可能，跳转到flush loop中完成)。

       若flush loop中也没有什么事情可做了，InnoDB存储引擎会切换到suspend loop，将Master Thread挂起，等待事件的发生。若用户启用了InnoDB存储引擎，却没有任何InnoDB存储引擎的表，那么Master Thread总是处于挂起的状态。

• InnoDB存储引擎关键特性

• 插入缓冲(Insert Buffer)

• 两次写(Double Write)

• 自适应哈希索引(Adaptive Hash Index)

• 异步IO(Async IO)

• 刷新邻接页(Flush Neighbor Page)

  
  

• 插入缓冲(Insert Buffer)

       InnoDB存储引擎开创性地设计了Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放到一个Insert Buffer对象中。数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge操作，这时通常能够将多个插入合并到一个操作中(因为在一个索引页中)，这就大大提高了对于非聚集索引插入的性能。Insert Buffer的使用需要同时满足两个条件：索引是辅助索引(secondary index)、索引不是唯一的。

InnoDB从1.0.x版本开始引入了Change Buffer，可将其视为Insert Buffer的升级。InnoDB存储引擎可以对DML(insert、delete、update)都进行缓冲，分别对应于Insert Buffer、Delete Buffer、Purge Buffer。Change Buffer适用的对象依然是非唯一的辅助索引。

Insert Buffer的数据结构是一棵B+树，在MySQL4.1之前的版本中每张表有一棵Insert Buffer B+树。而在现在的版本中，全局只有一棵Insert Buffer B+树，负责对所有的辅助索引进行Insert Buffer。而这棵B+树存放在共享表空间中，默认也就是ibdata1中。

• 两次写(Double Write)

       如果说Insert Buffer带给InnoDB存储引擎的是性能上的提升，那么doublewrite(两次写)带给InnoDB存储引擎的是数据页的可靠性。

在应用重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是doublewrite。doublewrite由两部分组成，一部分是内存中的doublewrite buffer，大小为2MB，另一部分是物理磁盘上共享表空间中连续的128个页，即2个区(extent)，大小同样为2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次、每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成doublewrite页的写入后，再将doublewrite buffer中的页写入各个表空间文件中，此时的写入则是离散的。

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

• 自适应哈希索引(Adaptive Hash Index)

       哈希(hash)是一种非常快的查找方法，在一般情况下这种查找的时间复杂度为O(1)，即一般仅需要一次查找就能定位数据。而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般为3~4层，故需要3~4次的查询。

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index, AHI)。AHI是通过缓冲池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表结构建立哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。AHI有一个要求，即对这个页的连续访问模式必须是一样的。启用AHI后，读取和写入的速度可以提高2倍，辅助索引的连续操作性能可以提高5倍。

• 异步IO (Async IO)

      为了提高磁盘操作性能，当前的数据库系统都采用异步IO (Asynchronous IO, AIO)的方式来处理磁盘操作。InnoDB存储引擎亦是如此。用户可以在发出一个IO请求后立即再发出另一个IO请求，当全部IO请求发送完毕后，等待所有IO操作的完成，这就是AIO，这样可以提高IOPS的性能。InnoDB 1.1.x开始，提供了内核级别AIO的支持，称为Native AIO。

• 刷新邻接页(Flush Neighbor Page)

      InnoDB存储引擎提供了Flush Neighbor Page(刷新邻接页)的特性：当刷新一个脏页时，InnoDB存储引擎会检测该页所在区(extend)的所有页，如果是脏页，那么一起进行刷新。好处显而易见，通过AIO可以将多个IO写入操作合并为一个IO操作，故该工作机制在传统机械硬盘下有着显著的优势。

五、文件

• 参数文件

MySql数据库的参数分为两类，动态参数和静态参数。动态参数意味着可以在MySQL实例运行中进行更改，静态参数说明在整个实例声明周期内都不得进行更改，就好像是只读的。可以通过set命令对动态参数进行修改。

• 日志文件

日志文件包括错误日志、慢查询日志、二进制日志和查询日志。

错误日志文件对MySQL的启动、运行和关闭过程进行了记录。

慢查询日志，在MySQL启动时有一个阈值，会将运行时超过该阈值的所有sql语句都记录到慢查询日志文件中。该阈值可以通过参数long_query_time来设置，默认值为10，代表10秒。还可以通过额外的参数long_query_io将超过指定逻辑IO次数的SQL记录到slow log中，该值默认为100，即表示对逻辑读取次数大于100的sql语句，记录到slow log中。

二进制日志(binary log)，记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。二进制日志文件在默认情况下并没有启动，需要手动指定参数来启动。

• 表结构定义文件

不论表采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义。

• InnoDB存储引擎文件

InnoDB存储引擎文件包括重做日志文件、表空间文件。

InnoDB采用将存储的数据按表空间进行存放的设计。在默认配置下会有一个初始大小为10MB，名为ibdata1的文件，该文件就是默认的表空间文件。

在默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件，这就是重做日志文件，它们记录了InnoDB存储引擎的事务日志。每个InnoDB存储引擎至少有1个重做日志文件组(group)，每个文件组下至少有两个重做日志文件，如默认的ib_logfile0和ib_logfile1。参数innodb_log_file_size指定每个重做日志文件的大小。参数innodb_log_files_in_group指定了日志文件组中重做日志文件的数量，默认为2。一方面重做日志不能设置的太大，如果设置的很大，在恢复时可能需要很长的时间。另一方面又不能设置的很小，否则可能导致一个事务的日志需要很多次切换重做日志文件，重做日志文件太小还会导致频繁地发生async checkpoint，导致性能的抖动。

首先，二进制日志会记录所有与MySQL数据库有关日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志。而InnoDB存储引擎的重做日志只记录有关该存储引擎本身的事务日志。其次，记录的内容不同，无论用户将二进制日志文件记录的格式设为statement还是row，又或者是mixed，其记录的都是关于一个事务的具体操作内容，即该日志是逻辑日志。而InnoDB存储引擎的重做日志文件记录的是关于每个页(page)的更改的物理情况。

写入重做日志文件的操作不是直接写，而是先写入一个重做日志缓冲(redo log buffer)中，然后按照一定的条件顺序地写入日志文件。从重做日志缓冲往磁盘写入时，是按512个字节，也就是一个扇区的大小进行写入。因为扇区是写入的最小单位，因此可以保证写入必定是成功的。因此在重做日志的写入过程中不需要有doublewrite。

六、表

表就是关于特定实体的数据集合，这也是关系型数据库的核心。在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table)。在InnoDB存储引擎表中，每张表都有一个主键，如果在创建表时没有显式地定义主键，MySQL数据库会首先判断表中是否有非空的唯一索引，如果有则该列即为主键，如果没有InnoDB存储引擎会自动创建一个6字节大小的指针。当表中有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键。

InnoDB逻辑存储结构

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

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。可以存放在默认表空间，也可以启用参数innodb_file_per_table，则每张表内的数据都单独放到一个表空间内。

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

区是由连续的页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4~5个区。在默认情况下，InnoDB存储引擎页的大小为16KB，即一个区中一共有64个连续的页。InnoDB1.0.x版本开始引入压缩页，即每个页的大小可以通过参数KEY_BLOCK_SIZE设置为2K、4K、8K，因此每个区对应页的数量就应该为512、256、128。InnoDB1.2.x版本新增了参数innodb_page_size，通过该参数可以将默认页的大小设置为4K、8K，但是页中的数据库不是压缩的。这时区中页的数量同样为256、128。总之，无论页的大小如何变化，区的大小总是1M。

InnoDB有页(有时也称为块)的概念，页是InnoDB磁盘管理的最小单位。在InnoDB存储引擎中，默认每个页的大小为16KB。可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。若设置完成，则所有表中页的大小都为innodb_page_size，不可以对其再次进行修改，除非通过mysqldump导入和导出操作来产生新的库。

InnoDB存储引擎是面向列的(row-oriented)，也就是说数据是按照行进行存放的。每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200行的记录，即7992行记录。

InnoDB行记录格式

InnoDB存储引擎提供了Compact和Redundant两种格式来存放行记录数据。Redundant格式是为了兼容之前版本而保留的。

Compact行记录是在MySQL5.0中引入的，其设计目的是高效地存储数据。简单来说，一个页中存放的行数据越多，其性能就越高。Compact行记录格式的首部是一个非NULL变长字段长度列表，并且其是按照列的顺序逆序存放的，其长度为：若列的长度小于255字节，用1字节表示；若大于255个字节，用2字节表示。

变长字段的长度最大不能超过2字节，这是因在MySQL数据中Varchar类型的最大长度限制为65535。变长字段之后的第二部分是NULL标志位，该位指示了该行数据中是否有NULL值，有则用1表示。该部分所占的字节应该为1字节。接下来的部分是记录头信息(record header)，固定占用5字节(40位)。其含义如下：

最后的部分就是实际存储每个列的数据。不管是char还是varchar，NULL不占用该部分任何空间，即NULL除了占有NULL标志位，实际存储不占有任何空间。每行数据除了用户定义的列外，还有两个隐藏列，事务ID列和回滚指针列，分别为6字节和7字节的大小。若InnoDB表没有定义主键，每行还会增加一个6字节的rowid列。InnoDB存储引擎在页内部是通过一种链表的结构来串连各个行记录的。

Redundant行记录格式是MySQL5.0之前InnoDB的行记录存储方式，其记录格式如下：

不同于Compact行记录格式，Redundant行记录格式的首部是一个字段长度偏移列表，同样是按照列的顺序逆序放置的。若列的长度小于255字节，用1字节表示；若大于255字节，用2字节表示。第二部分分为记录头(record header)，不同于Compact行记录格式，Redundant行记录格式的记录头占用6字节(48位)，其每一个位的含义如下：

InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。一般认为BLOB、LOB这类的大对象列类型的存储会把数据存放在数据页面之外。另外，即便是varchar列类型数据，仍然有可能被存放为行溢出数据。MySQL8验证，varchar类型的最大长度为65532。varchar(n)中的n指的是字符的长度，而官方文档说明varchar类型最大支持65535，单位是字节。

当发生行溢出时，数据存放在页类型为Uncompress BLOB页中。

InnoDB 1.0.x版本开始引入了新的文件格式，以前支持的Compact和Redundant格式称之为Antelope文件格式，新的文件格式称为Barracuda文件格式。Barracuda文件格式下拥有两种新的行记录格式：Compressed和Dynamic。新的两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式，在数据页中只存放20个字节的指针，实际的数据都存放在Off Page中，而之前的Compact和Redundant两种格式会存放768个前缀字节。

Compressed行记录格式的另一个功能就是，存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行非常有效的存储。

通常理解VARCHAR是存储变长长度的字符类型，CHAR是存储固定长度的字符类型。从MySQL4.1开始，CHAR(N)中的N指的是字符的长度，而不是之前版本的字节长度。也就是说，在不同的字符集下，CHAR类型列内部存储的可能不是定长的数据。对于多字节字符编码的CHAR数据类型的存储，InnoDB存储引擎在内部将其视为变长字符类型，因此在多字节字符集的情况下，CHAR和VARCHAR的实际行存储基本是没有区别的。

InnoDB数据页结构

页是InnoDB存储引擎管理数据库的最小磁盘单位。类型为B-tree Node的页存放的即是表中行的实际数据。InnoDB数据页由7个部分组成，包括头文件(File Header)、页头(Page Header)、Infimum和Supremum Records、User Records(用户记录，即行记录)、Free Space(空闲空间)、Page Directory(页目录)、File Trailer(文件结尾信息)。其中File Header、Page Header、File Trailer的大小是固定的，分别为 38、56、8字节，这些空间用来标记该页的一些信息，如checksum，数据页所在B+树索引的层数等。User Records、Free Space、Page Directory这部分为实际的行记录存储空间，因此大小是动态的。

File Header用来记录页的一些头信息，共占用38字节，其组成如下：

Page Header该部分用来记录数据页的状态信息，由14个部分组成，共占用56字节。

在InnoDB存储引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。Infimum记录是比该页中任何主键值都要小的记录，Supremum指比任何可能大的值还要大的值。这两个值在页创建时被建立，并且在任何情况下不会被删除。在Compact行格式和Redundant行格式下，两者占用的的字节数各不相同。

User Records即实际存储行记录的内容。

Free Space指的是空闲空间，同样也是个链表数据结构。在一条记录被删除后，该空间会被加入到空闲链表中。

Page Directory(页目录)中存放了记录的相对位置(这里存放的是页相对位置，而不是偏移量)，有些时候这些记录指针称为Slots(槽)。在InnoDB中并不是每个记录拥有一个槽，InnoDB存储引擎的槽是一个稀疏目录(sparse directory)，即一个槽中可能包含多个记录。伪记录Infimum的n_owned的值总是1，记录Supremum的n_owned的取值范围为[1，8]，其他用户记录的n_owned的取值范围为[4，8]。当记录被插入或删除时需要对槽进行分裂或平衡的维护操作。由于Page Directory是稀疏目录，二叉查找的结果只是一个粗略的结果，因此InnoDB必须通过record header的next_record来继续顺序查找相关记录。

File Trailer是为了检测页是否完整地写入磁盘。File Trailer只有FIL_PAGE_END_LSN属性，占8个字节，前4个字节表示该页的checksum，后4个字节和File Header里的FIL_PAGE_LSN相同。将这两个值与File Header里的FIL_PAGE_SPACE_OR_CHKSUM和FIL_PAGE_LSN进行比较(checksum的比较是需要经过函数计算的)，以此来保证页的完整性。默认配置下，InnoDB每次从磁盘读取一个页就会检测该页的完整性。

约束

InnoDB存储引擎的约束有：Primary Key、Unique Key、Foreign Key、Default、NOT NULL、ENUM和SET约束、触发器约束

MySQL数据库不支持传统的CHECK约束，但是通过ENUM和SET类型可以解决部分这样的约束需求。

视图

在MySQL数据库中，视图是一个命名的虚表，它由一个sql查询来定义，可以当做表来使用。与持久表不同的是，视图中的数据没有实际的物理存储。MySQL不支持物化视图，所有的视图都是虚拟的。

####分区表

分区的过程是将一个表或索引分解为多个更小、更可管理的部分。MySQL数据库支持的分区类型为水平分区，并不支持垂直分区。MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。MySQL有以下几种分区：

• RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区。

  CREATE TABLE T (
  ID INT
  )ENGINE = INNODB
  PARTITION BY RANGE (ID)(
  PARTITION P0 VALUES LESS THAN (100),
  PARTITION P1 VALUES LESS THAN (200)
  );

• LIST分区：和RANGE分区类似，只是LIST分区面向的是离散的值。

  CREATE TABLE Q (
  ID INT
  )ENGINE = INNODB
  PARTITION BY LIST (ID)(
  PARTITION Q0 VALUES IN (1,3,5,7,9),
  PARTITION Q1 VALUES IN (2,4,6,8,10)
  );

• HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各个分区的数据数量大致都是一样的。在RANGE和 LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MYSQL自动完成这些工作，用户所要做的只是基于将要被散列的列值指定一个列值或者表达式，以及指定被分区的表将要被分割成的分区数量。要使用HAHS分区来分割一个表，要在CREATE TABLE语句上添加一个PARTITION BY HASH(expr)子句，其中“expr”是返回一个整数的表达式。expr可以仅仅是字段类型为整型的列名。此外，用户很可能需要在后面再添加一个PARTITITIONS num子句，其中num是非负的整数，它表示将要被分割成分区的数量。如果没有包含一个PARTITIONS子句，分区数量默认为1。

  CREATE TABLE T_HASH ( 
  A INT, 
  B DATETIME 
  ) ENGINE = INNODB
  PARTITION BY HASH(YEAR(B)) 
  PARTITIONS 4;

  MYSQL还支持一种LINEAR HASH分区，它使用一个更加复杂的算法来确定新行插入到已经分区的表中的位置。

  CREATE TABLE T_LINEAR_HASH ( 
  A INT, 
  B DATETIME 
  ) ENGINE = INNODB
  PARTITION BY LINEAR HASH(YEAR(B)) 
  PARTITIONS 4;

  LINEAR HASH分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有大量数据的表。缺点在于，与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡。

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

  CREATE TABLE T_KEY ( 
  A INT, 
  B DATETIME 
  )ENGINE = INNODB
  PARTITION BY KEY(B) 
  PARTITIONS 4;

• RANGE、LIST、HASH和KEY分区的条件是数据必须是整型，如果不是整型需要通过函数将其转化为整型。COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。

  CREATE TABLE T_COLUMN ( 
  A INT, 
  B DATETIME
  ) ENGINE = INNODB
  PARTITION BY RANGE COLUMNS(B)(
  PARTITION P0 VALUES LESS THAN ('2021-06-23'),
  PARTITION P1 VALUES LESS THAN ('2022-06-23')
  );

不论哪种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。唯一索引可以是允许NULL值的，并且分区列只要是唯一索引的一个组成部分，不需要整个唯一索引列都是分区列。如果建表时没有指定主键，唯一索引，可以指定任何一个列为分区列。

子分区其实是对每个分区表的每个分区进行再次分隔，目前只有RANGE和LIST分区的表可以再进行子分区，子分区只能是HASH或者KEY分区。子分区可以将原本的数据进行再次的分区划分。

CREATE TABLE tb_sub (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

七、索引与算法

InnoDB常见索引：

• B+树索引

• 全文索引

• 哈希索引

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

B+树索引是传统意义上的索引，B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页，然后数据库通过把页读入到内存，再去内存中进行查找，最后得到要查找的数据。

B+树是为磁盘或其他直接读取辅助设备设计的一种平衡查找树。在B+树种，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。B+树索引在数据库中最大的特点就是高扇出性，因此在数据库中，B+树的高度一般都在2~4层，也就是说查找某一键值的行记录时最多只需要2~4次IO操作。

数据库中的B+树索引分为聚集索引(clustered index)和辅助索引(secondary index)，二者内部实现都是B+树，即高度平衡点。聚集索引和辅助索引最大的不同是，聚集索引叶子节点存放着实际的数据，而辅助索引叶子节点存放的是聚集索引键，即聚集索引的指针。

聚集索引

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。

聚集索引的存储并不是严格意义上物理上连续的，而是逻辑上连续的。页通过双向链表链接，页按照主键的顺序排序；每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

聚集索引的一个好处是，它对于主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。

辅助索引(非聚集索引)

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

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

索引管理

用户可以设定对整个列进行索引，也可以只索引一个列的开头部分数据。

Cardinality，表示索引中唯一值的数据的估计值，它应该尽可能接近1，如果非常小，可以考虑删除该索引。

InnoDB存储引擎从1.0.x开始支持一种称为Fast Index Creation的索引创建方式，简称FIC。对于辅助索引的创建，InnoDB存储引擎会对创建的表加一个S锁。由于FIC在索引的创建过程中对表加上了S锁，因此在创建的过程中只能对该表进行读操作，若有大量的事务需要对目标表进行写操作，那么数据库的服务同样不可用。FIC方式只限于辅助索引，对于聚集索引的创建和删除同样需要重建一张表。

MySQL5.6开始支持Online DDL操作，其允许辅助索引创建的同时，还允许其他诸如Insert、Update、Delete这类DML操作，这样极大地提高了MySQL的并发性能。在alter table增加辅助索引时，可以指定algorithm为none、share、exclusive、default的任何一种。none：执行索引创建或者删除操作时，对目标表不增加任何的锁，即事务仍然可以进行读写操作，不会受到阻塞，因此这种模式可以获得最大的并发度。share：执行索引创建或删除时，对目标增加一个S锁。exclusive：执行索引创建或删除时，对目标表增加一个X锁，读写事务都不能进行，因此会阻塞所有的线程。default：此模式下首先会判断当前操作是否可以用none模式，若不能，则判断是否可以用share模式，最后判断是否可以使用exclusive模式，也就是说default会通过判断事务的最大并发性来判断执行DDL的模式。

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

Cardinality值

并不是所有的查询条件中出现的列都需要添加索引。要不要添加索引，可以参考Cardinality的值，它表示索引中不重复记录数据的预估值。在实际应用中，Cardinality/n_rows_in_table应尽可能地接近1。如果非常小，那么用户需要考虑是否有必要创建这个索引。因为建立索引的前提是高选择性的，这样对数据库来说才具有实际意义。

对Cardinality值的统计是放在存储引擎层进行的，数据库通过采样的方法对Cardinality值进行统计，在InnoDB存储引擎中，Cardinality统计信息的更新发生在两个操作中：Insert和Update。默认InnoDB存储引擎对8个叶子节点进行采样。

联合索引

联合索引是指对表上的多个列进行索引。联合索引的创建方法与单个索引创建的方法一样，不同之处仅在于有多个索引列。

覆盖索引

InnoDB存储引擎支持覆盖索引(Covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

索引提示(Index Hint)

MySQL数据库支持索引提示(Index Hint)，显示地告诉优化器使用哪个索引。

Multi-Range Read优化

MySQL5.6开始支持Multi-Range Read(MRR)优化。Multi-Range Read优化的目的是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来极大的性能提升。Multi-Range Read优化可适用于Range、ref、eq_ref类型的查询。MRR优化的好处：

• MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键的顺序进行书签查找。

• 减少缓冲池中页被替换的次数。

• 批量处理对键值的查询操作。

Index Condition Pushdown(ICP)优化

MySQL5.6开始支持Index Condition Pushdown(ICP)优化，MySQL数据库会在取出索引的同时，判断是否可以进行where条件的过滤，也就是将where的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层SQL对记录的索取(fetch)，从而提高数据库的整体性能。Index Condition Pushdown(ICP)优化支持range、ref、eq_ref、ref_or_null类型的查询，当前支持MyISAM和InnoDB存储引擎。

哈希索引

InnoDB存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式。对于缓存池中的page页都有一个chain指针，它指向相同哈希函数值的页。而对于除法散列，m的取值为略大于2倍的缓冲池页数量的质数。哈希索引最大的缺点是只能用来搜索等值的查询。

全文检索

全文检索(Full-Text Search)是将存储于数据库中的整本书或整篇文章中的任意内容检索出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。从InnoDB1.2.x开始支持全文检索。

全文检索通常使用倒排索引(inverted index)来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表(auxiliary table)中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式：

• inverted file index，其表现形式为{单词，单词所在文档的ID}

• full inverted index，其表现形式为{单词，(单词所在文档ID，在具体文档中的位置)}

在InnoDB存储引擎中，将(DocumentID，Position)视为一个ilist。因此在全文检索的表中，有两个列，一个是word字段，另一个是ilist字段，并且在word字段上设有索引。倒排索引需要将word存放到一张表中，这个表称为辅助表(Auxiliary Table)。在InnoDB存储引擎中，为了提高全文索引的并行性能，共有6张辅助表，目前每张表根据word的Latin编码进行分区。辅助表是持久的表，存放于磁盘上。

FTS Index Cache(全文检索索引缓存)是一个红黑树，其根据(word，ilist)进行排序。这意味着插入的数据已经更新了对应的表，但是对全文索引的更新可能在分词操作后还在FTS Index Cache中，Auxiliary Table可能还没有更新。InnoDB存储引擎会批量对Auxiliary Table进行更新，而不是每次插入后更新一次Auxiliary Table。当对全文检索进行查询时，Auxiliary Table首先会将FTS Index Cache中对应的word字段合并到Auxiliary Table中，然后再进行查询。由于其根据红黑树排序后进行批量插入，其产生的Auxiliary Table相对较小。对于InnoDB存储引擎，其总是在事务提交时将分词写入到FTS Index Cache，然后再通过批量更新写入磁盘。

InnoDB存储引擎对于全文检索的限制：

• 每张表只能有一个全文检索的索引。

• 由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则。

• 不支持没有单词界定符的语言，例如中文、日文、韩文等。

MySQL数据库通过MATCH()...AGAINST()语法支持全文检索的查询，MATCH指定了需要被查询的列，AGAINST指定了使用何种方法去进行查询。

全文检索通过MATCH函数进行查询，默认采用了Natural Language模式，其表示查询带有指定word的文档。

select 
            * 
from
            t
where 
            match(body)against ('Jason' in natural language mode);

八、锁

InnoDB存储引擎默认在行级别上对表数据上锁，数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

在数据库中，lock和latch都可以理解为锁，但二者有着截然不同的含义。

latch一般称为闩锁(轻量级的锁)，因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差。在InnoDB存储引擎中，latch又可以分为mutex(互斥量)和rwlock(读写锁)。其目的是用来保证并发线程操作临街资源的正确性，并且通常没有死锁检测机制。

lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放。lock有死锁检测机制。

共享锁和排它锁

InnoDB存储引擎实现了两种标准级别的行级锁：

• 共享锁(S Lock)，允许事务读一行数据。

• 排它锁(X Lock)，允许事务删除或更新一行数据。

X锁与任何锁都不兼容，S锁仅与S锁兼容。S锁和X锁都是行锁，兼容是指针对同一行记录锁的兼容情况。

意向锁

InnoDB存储引擎支持多粒度锁定，这种锁定允许事务在行级别上的锁和表级别上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称为意向锁(Intention Lock)。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度(fine granularity)上进行加锁。若将上锁的对象看成一棵树，那么对最下层的对象上锁，也就是对最细粒度的对象进行上锁，那么首先需要对粗粒度的对象上锁。InnoDB存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁：

• 意向共享锁(IS Lock)，事务想要获得一张表中某几行的共享锁。

• 意向排它锁(IX Lock)，事务想要获得一张表中某几行的排它锁。

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实也不会阻塞除全表扫以外的任何请求。

一致性非锁定读

一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行Delete或Update操作，这时也不会因此去等待行上锁的释放。相反地，InnoDB存储引擎会去读行的一个快照数据。之所以称其为非锁定读，因为不需要等待访问的行上X锁的释放。快照数据是指该行的之前版本的数据，该实现是通过undo段来实现的。而undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。

非锁定读机制极大地提高了数据库的并发性。在InnoDB存储引擎的默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。但是在不同的事务隔离级别下，读取的方式不同，并不是在每个事务隔离级别下都是采用非锁定的一致性读。

快照数据其实就是当前行数据之前的历史版本，每行记录可能有多个版本。一个行记录可能有不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制(Multi Version Concurrency Control，MVCC)。

在事务隔离级别READ COMMITED和REPREATABLE READ下，InnoDB存储引擎使用非锁定的一致性读。然而，对于快照数据的定义却不相同。在READ COMMITED事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。而在REPREATABLE READ事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

一致性锁定读

在默认配置下，即事务的隔离级别为REPREATABLE READ模式下，InnoDB存储引擎的select操作使用一致性非锁定读。但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于select的只读操作。InnoDB存储引擎对于select语句支持两种一致性的锁定读操作：

• select ... for update

• select ... lock in share mode

select ... for update对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。select ... lock in share mode对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被锁定。

自增长锁

自增长在数据库中是非常常见的一种属性，对每个含有自增长值的表都有一个自增长计数器，当对含有自增长计数器的表进行插入操作时，这个计数器会被初始化。插入操作会依据这个自增长的计数器加1赋予自增长列。这个实现方式称为Auto-Inc Locking。这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是一个事物完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

虽然Auto-Inc Locking从一定程度上提高了并发插入的效率，但还是存在一些性能上的问题。首先，对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成。其次，对于Insert ... select的大量数据的插入会影响插入的性能，因为另一个事务中的插入会被阻塞。

从MySQL5.1.22开始，InnoDB存储引擎提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。InnoDB存储引擎还提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，该值的默认为1。

在InnoDB存储引擎中，自增长的列必须是索引，同时必须是索引的第一个列。

外键和锁

在InnoDB存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB存储引擎自动对其加一个索引，因为这样可以避免表锁。这比Oracle数据库做得好，Oracle数据库不会自动添加索引，用户必须自己手动添加，这也导致了Oracle数据库中可能产生死锁。

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

锁的算法

InnoDB存储引擎有3种行锁的算法：

• Record Lock：单个行记录上的锁。

• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。

• Next-Key Lock：Gap Lock+ Record Lock，锁定一个范围，并且锁定记录本身。

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

Next-Key Lock是Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB存储引擎对于行的查询都是采用这种锁定算法。

采用Next-Key Lock的锁定技术称为Next-Key Locking。其设计目的是为了解决Phantom Problem。利用这种锁定技术，锁定的不是单个值，而是一个范围，是谓词锁(predict lock)的一种改进。除了Next-Key Locking，还有Previous-Key Locking技术，差别就是锁定范围的开闭区间，仅此而已。

然而，当查询的索引含有唯一属性时，InnoDB存储引擎会对Next-Key Lock进行优化，将其降为Record Lock，即仅锁住索引本身，而不是范围。Next-Key Lock降级为Record Lock仅在查询的列是唯一索引的情况下。若是辅助索引，其上加的是Next-Key Lock，InnoDB存储引擎还会对辅助索引下一个键值加上一个Gap Lock。

Gap Lock的作用是为了阻止多个事务将记录插入到同一个范围内，而这会导致Phantom Problem问题的产生。用户可以通过下面两种方式来显式地关闭Gap Lock：

• 将事务的隔离级别设置为READ COMMITED

• 将参数innodb_locks_unsafe_for_binlog设置为1

在上述配置下，除了外键约束和唯一性检查依然需要的Gap Lock，其余情况仅使用Record Lock进行锁定。

在InnoDB存储引擎中，对于Insert的操作，其会检查插入记录的下一条记录是否被锁定，若已经被锁定，则不允许查询。

对于唯一键值的锁定，Next-Key Lock降级为Record Lock仅存在于查询所有的唯一索引列。若唯一索引由多个列组成，而查询仅是查找多个唯一索引列中的其中一个，那么查询其实是range类型查询，而不是point类型查询，故InnoDB存储引擎依然使用Next-Key Lock进行锁定。

在默认的事务隔离级别下，即REPEATABLE READ下，InnoDB存储引擎采用Next-Key Locking机制来避免Phantom Problem。Phantom Problem是指在同一事务下，连续执行两次相同的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。

锁问题

脏读

脏页指的是在缓冲池中已经被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中的页和磁盘中的页的数据是不一致的，当然在刷新到磁盘之前，日志都已经被写入到了重做日志文件中。而所谓的脏数据是指事务对缓冲池中行记录的修改，并且还没有被提交。

对于脏页的读取，是非常正常的。脏页是因为数据库实例内存和磁盘异步造成的，这并不影响数据的一致性，或者说两者最终会达到一致性，即脏页都刷回磁盘。并且因为脏页的刷新是异步的，不影响数据库的可用性，因此可以带来性能的提高。

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

脏读指的是在不同事务下，当前事务可以读到另外事务未提交的数据。脏读现象在生产环境中并不常发生，脏读发生的条件是需要事务的隔离级别为READ UMCOMMITTED，而目前绝大多数数据库至少设置成READ COMMITTED。InnoDB存储引擎默认事务隔离级别为READ REPEATABLE，Microsoft SQL Server数据库为READ COMMITTED，Oracle数据库同样为READ COMMITTED。

不可重复读

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

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

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

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

丢失更新

一个事务的更新操作会被另一个事务的更新操作覆盖，从而导致数据的不一致。在当前数据库的任何隔离级别下，都不会导致数据库理论意义上的丢失更新问题。因为即使是READ UMCOMMITTED的事务隔离级别，对于行的DML操作，需要对行或其他粗粒度级别的对象加锁。

死锁

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。

解决死锁问题的最简单的一个方法是超时，即两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。在InnoDB存储引擎中，参数innodb_lock_wait_timeout用来设置超时的时间。

超时机制虽然简单，但是其仅通过超时后对事务进行回滚的方式来处理，或者说其实根据FIFO的顺序选择回滚对象。但若超时的事务所占权重比较大，如事务操作更新了很多行，占用了较多的undo log，这时采用FIFO的方式，就显得不合适了，因为回滚这个事务的时间相对于另一个事务所占用的时间可能会很多。

除了超时机制，当前数据库还都普遍采用了wait-for graph的方式来进行死锁检测。较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用这种方式。wait-for graph要求数据库保存以下两种信息：

• 锁的信息链表

• 事务等待链表

通过上述链表可以构造出一张图，而在这张图中若存在回路，就代表存在死锁，因此资源间相互发生等待。

wait-for graph是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说InnoDB存储引擎选择回滚undo量最小的事务。

wait-for graph的死锁检测机制通常采用深度优先的算法实现，在InnoDB1.2之前，都是采用递归方式实现。从1.2版本之后，对wait-for graph的死锁检测进行了优化，将递归用非递归的方式实现，从而进一步提了了InnoDB存储引擎的性能。

锁升级

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

InnoDB存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式，因此不管一个事务锁住页中一个记录还是多个记录，其开销通常都是一致的。

九、事务

InnoDB存储引擎中的事务完全符合ACID特性：

• 原子性(Atomicity)

• 一致性(Consistency)

• 隔离性(Isolation)

• 持久性(Durability)

原子性(Atomicity)指整个数据库事务是不可分割的工作单位，事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

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

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

持久性(Durability)事务一旦提交，其结果是永久性的，即便系统故障也不会丢失。

事务的分类：

• 扁平事务

• 带有保存点的扁平事务

• 链事务

• 嵌套事务

• 分布式事务

在扁平事务中，所有操作都处于同一层次，其由BEGIN WORK开始，由COMMIT WORK或ROLLBACK WORK结束，其间的操作都是原子操作，要么都执行，要么都回滚。因此扁平事务是应用程序成为原子操作的基本组成模块。

带保存点的扁平事务，除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态。这是因为某些事务可能在执行过程中出现的错误并不会导致所有的操作都无效，放弃整个事务不合乎要求，开销也太大。保存点用来通知系统应该记住事务当前的状态，以便当之后发生错误时，事务能回到保存点时的状态。保存点用SAVE WORK函数来创建。

链事务可视为保存点模式的一种变种。带有保存点的扁平事务，当系统发生宕机时，所有的保存点都消失，因为保存点是易失的而非持久的。这意味着当进行恢复时，事务需要从开始处重新执行，而不能从最近的一个保存点继续执行。链事务的设计思想是，在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务。提交事务和开始下一个事务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中进行的一样。

链事务与带有保存点的扁平事务不同的是，带有保存点的扁平事务能回滚到任意正确的保存点。而链事务中的回滚仅限于当前事务，即只能恢复到最近一个保存点。链事务在执行COMMIT后即释放了当前事务所持有的锁，而带有保存点的扁平事务不影响迄今为止所持有的锁。

嵌套事务是一个层次结构框架。有一个顶层事务控制着各个层次的事务。顶层事务之下嵌套的事务称为子事务，其控制着每一个局部的变换。

• 嵌套事务是由若干个事务组成的一棵树，子树既可以是嵌套事务，也可以是扁平事务。

• 处在叶子节点的事务是扁平事务。但是每个子事务从根节点到叶节点的距离可以是不同的。

• 处于根节点的事务称为顶层事务，其他事务称为子事务。事务的前驱称为父事务，事务的下一层称为儿子事务。

• 子事务既可以提交也可以回滚。但它的提交操作并不马上生效，除非其父事务已经提交。任何子事务都在顶层事务提交后才真正生效。

• 树中的任意一个事务的回滚会引起它的所有子事务一同回滚，故子事务仅保留ACI特性，不具备D特性。

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

对于InnoDB存储引擎来说，其支持扁平事务、带有保存点的扁平事务、链事务、分布式事务。对于嵌套事务，其并不原生支持。

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

redo log恢复提交事务修改的页操作，而undo log回滚行记录到某个特定版本。redo通常是物理日志，记录的是页的物理修改操作。undo是逻辑日志，根据每行记录进行记录。

redo

重做日志用来实现事务的持久性，即事务的ACID中的D。其由两部分组成：一是内存中的重做日志缓存(redo log buffer)，其是易失性的；二是重做日志文件(redo log file)，其是持久的。

InnoDB存储引擎是事务的存储引擎，其通过Force Log at Commit机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的commit操作完成才算完成。这里的日志是指重做日志，在InnoDB存储引擎中由两部分组成，即redo log和undo log。redo log用来保证事务的持久性，undo log用来帮助事务回滚及MVCC的功能。redo log基本上都是顺序写的，在数据库运行时不需要redo log的文件进行读取操作。而undo log是需要进行随机读写的。

为了确保每次日志都 写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB存储引擎都需要调用一次fsync操作。由于重做日志文件打开并没有使用O_DIRECT选项，因此重做日志缓冲先写入文件系统缓存。为了确保重做日志写入磁盘，必须进行一次fsync操作。由于fsync的效率取决于磁盘的性能，因此磁盘的性能决定了事务提交的性能，也就是数据库的性能。

在MySQL数据库中还有一种二级制日志(binlog)，其用来进行POINT-IN-TIME(PIT)的恢复及主从复制环境的建立。从表面上看其和重做日志非常相似，都是记录了对于数据库操作的日志。然而，从本质上来看，两者有着非常大的不同。首先，重组日志是在InnoDB存储引擎层产生的，并且二进制日志不仅仅针对InnoDB存储引擎，MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。其次，两种日志记录的内容形式不同。MySQL数据库上层的二进制日志是一种逻辑日志，其记录的是对应的SQL语句。而InnoDB存储引擎层面的重做日志是物理格式日志，其记录的是对每个页的修改。此外，两种日志记录写入磁盘的时间点不同。二级制日志只在事务提交完成后进行一次写入。而InnoDB存储引擎的重做日志在事务进行中不断地被写入，这表现为日志并不是随事务提交的顺序进行写入的。

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

若一个页中产生的重做日志数量大于512字节，那么需要分割为多个重做日志块进行存储。此外，由于重做日志块的大小和磁盘扇区的大小一样，都是512字节，因此重做日志的写入可以保证原子性，不需要doublewrite技术。

重做日志块除了日志本身之外，还由日志块头(log block header)及日志块尾(log block tailer)两部分组成。重做日志头一共占用12字节，重做日志尾占用8字节。故每个重做日志块实际可以存储的大小为492字节。

log group为重做日志组，其中有多个重做日志文件。虽然源码中已支持log group的镜像功能，但是在ha_innobase.cc文件中禁止了该功能，因此InnoDB存储引擎实际只有一个log group。

log group是一个逻辑上的概念，并没有一个实际存储的物理文件来表示log group信息。log group由多个重做日志文件组成，每个log group中的日志文件大小是相同的，且在InnoDB1.2版本之前，重做日志文件的总大小要小于4GB。从InnoDB1.2开始重做日志总大小的限制提高为512GB。

重做日志文件中存储的就是之前在log buffer中保存的log block，因此其也是根据块的方式进行物理存储的管理，每个块的大小与log block一样，同样为512字节。在InnoDB存储引擎运行过程中，log buffer根据一定的规则将内存中的log block刷新到磁盘。

由于InnoDB存储引擎的存储管理是基于页的，故其重做日志格式也是基于页的。

LSN是Log Sequence Number的缩写，其代表的是日志序列号。在InnoDB存储引擎中，LSN占用8字节，并且单调递增。LSN表示的含义有：

• 重做日志写入的总量

• checkpoint的位置

• 页的版本

假如页P1的LSN为10000，而数据库启动时，InnoDB检测到写入重做日志中的LSN为13000，并且事务已经提交，那么数据库需要进行恢复操作，将重做日志应用到P1页中。

InnoDB存储引擎在启动时不管上次数据库运行时是否正常关闭，都会尝试进行恢复操作。因为重做日志记录的是物理日志，因此恢复的速度比逻辑日志，如二进制日志，要快很多。与此同时，InnoDB存储引擎自身也对恢复进行了一定程度的优化，如顺序读取及并行应用重做日志，这样可以进一步提高数据库恢复的速度。

由于checkpoint表示已经刷新到磁盘页上的LSN，因此在恢复过程中仅需恢复checkpoint开始的日志部分。

undo

重做日志记录了事务的行为，可以很好地通过其对页进行重做操作。但是事务有时还需要进行回滚操作，这时就需要undo。因此在对数据库进行修改时，InnoDB存储引擎不但会产生redo，还会产生一定量的undo。如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。

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

当用户执行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也需要持久性的保护。

InnoDB存储引擎对undo的管理同样采用段的方式。首先InnoDB存储引擎有rollback segment，每个回滚段记录了1024个undo log segment，而在每个undo log segment段中进行undo页的申请。共享表空间偏移量为5的页(0,5)记录了所有rollback segment header所在的页，这个页的类型为FIL_PAGE_TYPE_SYS。

事务在undo log segment分配页并写入undo log的这个过程同样需要写入重做日志。当事务提交时，InnoDB存储引擎会做以下两件事情：

• 将undo log放入列表中，以供之后的purge操作

• 判断undo log所在的页是否可以重用，若可以分配给下个事务使用

事务提交后并不能马上删除undo log及undo log所在的页。因为可能还有其他事务需要通过undo log来得到行记录之前的版本。故事务提交时将undo log放入一个链表中，是否可以最终删除undo log及undo log所在的页由purge线程判断。

若为每一个事务分配一个单独的undo页会非常浪费存储空间，特别是对于OLTP类型的应用。在InnoDB存储引擎的设计中对undo页可以进行重用。当事务提交时，首先将undo log放入链表中，然后判断undo页的使用空间是否小于3/4，若是则表示undo页可以被重用，之后新的undo log记录在当前undo log的后面。由于存放undo log的列表是以记录进行组织的，而undo页可能存放着不同事务的undo log，因此purge操作需要涉及磁盘的离散读取操作，是一个比较缓慢的过程。

在InnoDB存储引擎中，undo log分为：

• insert undo log

• update undo log

insert undo log是指在insert操作中产生的undo log。因为insert操作的记录，只对事务本身可见，对其他事务不可见(这是事务的隔离性要求)，故该undo log可以在事务提交后直接删除。不需要进行purge操作。

update undo log记录的是对Delete和update操作产生的undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。update undo log相对于之前介绍的insert undo log，记录的内容更多，所需占用的空间也更大。

purge

Delete和update操作可能并不直接删除原有的数据。purge用于最终完成Delete和update操作。因为InnoDB存储引擎支持MVCC，所以记录不能在事务提交时立即进行处理，这时其他事务可能正在引用这行，故InnoDB存储引擎需要保存记录之前的版本。而是否可以删除这条记录通过purge来进行判断。若该行记录已不被任何其他事务引用，那么就可以进行真正的Delete操作。可见，purge操作时清理之前的Delete和update操作，将上述操作最终完成。而实际执行的操作为Delete操作，清理之前行记录的版本。

为了节省存储空间，InnoDB存储引擎的undo log设计是这样的：一个页上允许多个事务的undo log存在。虽然这并不代表事务在全局过程中提交的顺序，但是后面的事务产生的undo log总在后面。此外，InnoDB存储引擎还有一个history列表，它根据事务提交的顺序，将undo log进行链接。history list表示按照事务提交的顺序将undo log进行组织。在InnoDB存储引擎的设计中，先提交的事务总在尾端。undo page存放了undo log，由于可以重用，因此一个undo page中可能存放了多个不同事务的undo log。

InnoDB存储引擎这种先从 history list中找undo log，然后再从undo page中找undo log的设计模式是为了避免大量的随机读取操作，从而提高效率。

若事务为非只读事务，则每次事务提交时需要进行一次fsync操作，以此保证重做日志已经写入磁盘。当数据库发生宕机时，可以通过重做日志进行恢复。为了提高磁盘fsync的效率，当前数据库都提供了group commit的功能，即一次fsync可以刷新确保多个事务日志被写入文件。对于InnoDB存储引擎来说，事务提交时会进行两个阶段的操作：

• 修改内存中事务对应的信息，并且将日志写入重做日志缓冲

• 调用fsync将确保日志都从重做日志缓冲写入磁盘

可以将多个事务的重做日志通过一次fsync刷新到磁盘，这样大大地减少了磁盘的压力，从而提高了数据库的整体性能。对于写入或更新较为频繁的操作，group commit的效果尤为明显。

分布式事务

在使用分布式事务时，InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE。XA事务允许不同数据库之间的分布式事务，如一台服务器是MySQL数据库，另一台是Oracle数据库的，又可能还有一台服务器是SQL Server数据库，只要参与在全局事务中的每个节点都支持XA事务。

XA事务由一个或多个资源管理器(Resource Managers)、一个事务管理器(Transaction Manager)以及一个应用程序(Application Program)组成。

• 资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器。

• 事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器通信。

• 应用程序：定义事务的边界，指定全局事务中的操作。

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

最为常见的内部XA事务存在于binlog于InnoDB存储引擎直接。由于复制的需要，因此目前绝大多数的数据库都开启了binlog功能。在事务提交时，先写二进制日志，再写InnoDB存储引擎的重做日志。

十、备份

MySQL数据本身提供的备份：mysqldump、ibbackup、replication。

第三方工具：xtrabacup、LVM快照备份。

根据备份的方法不同，可以将备份分为：

• Hot Backup(热备)

• Cold Backup(冷备)

• Warm Backup(温备)

Hot Backup是指数据库运行中直接备份，对正在运行的数据库操作没有任何的影响，也就是Online Backup。Cold Backup是指备份操作是在数据库停止的情况下，这种备份最为简单，一般只需要复制相关的数据库物理文件即可，即Offline Backup。Warm Backup备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，如加一个全局读锁以保证备份数据的一致性。

根据备份数据库的内容，备份文件又可以分为：

• 完全备份

• 增量备份

• 日志备份

完全备份是指对数据库进行一个完整的备份。增量备份是指在上次完全备份的基础上，对于更改的数据进行备份。日志备份是指对MySQL数据库二进制日志的备份，通过对一个完全备份进行二进制日志的重做来完成数据库的point-in-time的恢复工作。MySQL数据库复制的原理就是异步实时地将二进制日志重做传送并应用到从数据库。

对于InnoDB存储引擎来说，因为其支持MVCC功能，因此实现一致的备份比较简单。用户可以先开启一个事务，然后导出一组相关的表，最后提交。当然用户的事务隔离级别必须设置为REPEATABLE READ，这样的做法就可以给出一个完美的一致性备份。

对于mysqldump备份工具来说，可以通过添加--single-transaction选项获得InnoDB存储引擎的一致性备份。

对于InnoDB存储引擎的冷备，只需要备份MySQL数据库的frm文件，共享表空间文件，独立表空间文件(*.ibd)，重做日志文件。另外建议定期备份MySQL数据库的配置文件my.cnf，这样有利于恢复的操作。

原文始发于微信公众号（勤奋的凯尔森同学）：MySQL高级进阶