书籍介绍：https://book.douban.com/subject/24708143/

体系结构

MySQL 被设计为一个可移植的数据库，几乎在当前所有系统上都能运行

• 如Linux, Solaris 、FreeBSD 、Mac 和Windows。

尽管各平台在底层（如线程）实现方面都各有不同

• 但是MySQL 基本上能保证在各平台上的物理体系结构的一致性

因此，用户应该能很好地理解MySQL 数据库在所有这些平台上是如何运作的

定义数据库和实例

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

• 注：当使用NDB 引擎时，数据库的文件可能不是操作系统上的文件
  • 而是存放于内存之中的文件，但是定义仍然不变

数据库实例：MySQL 数据库由后台线程以及一个共享内存区组成，共享内存可以被运行的后台线程所共享

• 注：与数据库的关系通常是一一对应的
  • 但是，在集群情况下可能存在一个数据库被多个数据实例使用的情况

MySQL 被设计为一个 单进程多线程 架构的数据库，MySQL 数据库实例在系统上的表现就是一个进程

• 类似于程序和进程的关系，当然这样类比也不太恰当

总结：数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合

数据库实例是程序，是位于用户与操作系统之间的一层数据管理软件，用户对数据库数据的任何操作

• 包括数据库定义、数据查询、数据维护、数据库运行控制等都是在数据库实例下进行的
  • 应用程序只有通过数据库实例才能和数据库打交道

MySQL体系结构

MySQL 数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎

MySQL 插件式的存储引擎架构提供了一系列标准的管理和服务支持

• 这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的

• 如SQL 分析器和优化器等，而存储引擎是底层物理结构的实现

  • 每个存储引擎开发者可以按照自己的意愿来进行开发

需要特别注意的是，存储引擎是基于表的，而不是数据库

MySQL存储引擎

存储引擎的好处是，每个存储引擎都有各自的特点，能够根据具体的应用建立不同存储引擎表

由于 MySQL 数据库开源特性

• 存储引擎可以分为 MySQL 官方存储引擎和第三方存储引擎（比如 InnoDB 存储引擎）

InnoDB存储引擎

InnoDB 存储引擎 支持事务，其设计目标主要面向 在线事务处理( OLTP) 的应用

其特点是 行锁设计、支持外键，并支持类似于 Oracle 的 非锁定读，即默认读取操作不会产生锁

• MySQL 数据库5.5.8 版本开始，InnoDB 存储引擎是默认的存储引擎
  • 之前默认的是 MyISAM 存储引擎

InnoDB 存储引擎将数据放在一个逻辑的表空间中

• 这个表空间就像黑盒一样由InnoDB 存储引擎自身进行管理

InnoDB 通过 使用多版本并发控制(MVCC) 来获得高并发性，并且实现了SQL标准的4 种隔离级别

• 默认为 REPEATABLE 级别

对于表中数据的存储，InnoDB 存储引擎采用了聚集(clustered) 的方式

• 因此每张表的存储都是按主键的顺序进行存放

如果没有显式地在表定义时指定主键， InnoDB 存储引擎会为每一行生成一个6 字节的ROWID, 并以此作为主键

InnoDB 存储引擎具备的高可用性、高性能以及高可扩展性

MylSAM存储引擎

MyISAM 存储引擎 不支持事务、表锁设计，支待全文索引

• 主要面向一些 OLAP 数据库应用

数据库系统与文件系统很大的一个不同之处在于对事务的支持，然而MyISAM 存储引擎是不支持事务的

• 毕竟在数据仓库中不是所有的操作都需要事务支持

最大的不同点：

• MyISAM 存储引擎的缓冲池只缓存(cache) 索引文件，而不缓冲数据文件

MylSAM 存储引擎表由MYD 和MYI 组成， MYD 用来存放数据文件， MYI 用来存放索引文件

注：在MySQL 5.0 版本之前，MyISAM 默认支持的表大小为4GB, 如果需要支持大于4GB 的MyISAM 表时

• 则需要制定 MAX_ROWS 和 AVG_ROW_LENGTH 属性

从MySQL5.0 版本开始， MyISAM 默认支持 256TB 的单表数据

对于MyISAM 存储引擎表， MySQL 数据库只缓存其索引文件，数据文件的缓存交由操作系统本身来完成

InnoDB存储引擎

InnoDB 是 事务安全 的MySQL 存储引擎

• 设计上采用了类似于 Oracle 数据库的架构

通常来说， InnoDB 存储引擎是OLTP应用中核心表的首选存储引擎

• 同时，也正是因为InnoDB 的存在，才使MySQL 数据库变得更有魅力

本章将详细介绍 InnoDB 存储引擎的体系架构及其不同于其他存储引擎的特性

InnoDB存储引擎概述

InnoDB 存储引擎是第一个完整支持 ACID 事务的MySQL 存储引擎

• 注：BDB 是第一个支持事务的MySQL 存储引擎。

特点：行锁设计、支持MVCC 、支持外键、提供一致性非锁定读

• 同时被设计用来最有效地利用以及使用内存和CPU

InnoDB存储引擎的版本

InnoDB 存储引擎被包含于所有MySQL 数据库的二进制发行版本中

• 早期其版本随着 MySQL 数据库的更新而更新

从 MySQL 5.1 版本时，MySQL 数据库允许存储引擎开发商 以动态方式加载引擎

• 这样存储引擎的更新可以不受MySQL 数据库版本的限制

注：InnoDB 1.0.x以及老版本，由于不支持多回滚段：

• InnoDB Plugin 支持的最大支持并发事务数量也被限制在1023

InnoDB体系架构

InnoDB 存储引擎，有多个内存块，可以认为这些内存块组成了一个大的内存池，负责如下工作：

• 维护所有进程／线程需要访问的多个内部数据结构

• 缓存磁盘上的数据，方便快速地读取，同时在对磁盘文件的数据修改之前在这里缓存

• 重做日志(redo log) 缓冲等

后台线程：

• 主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据

  • 此外将已修改的数据文件刷新到磁盘文件

• 同时保证在数据库发生异常的情况下 InnoDB 能恢复到正常运行状态

后台线程

InnoDB 存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务

Master Thread：

• 是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性
  • （包括脏页的刷新、合并插入缓冲(INSERT BUFFER) 、UNDO 页的回收等）

I/O Thread：

• 在 lnnoDB 存储引擎中大量使用了 AIO ( Async IO) 来处理写IO 请求，这样可以极大提高数据库的性能
  • 而 IO Thread 的工作主要是负责这些IO 请求的回调(call back)处理

注：InnoDB 1.0 版本之前共有 4 个IO Thread

• 分别是write 、read 、insert buffer 和log IO thread

在 Linux 平台下， IO Thread 的数量不能进行调整:

• 但是在Windows 平台下可以通过参数 innodb_file_ io threads 来增大IO Thread

从InnoDB 1.0.x 版本开始， read thread 和write thread 分别增大到了4 个，并且不再使用 innodb_ file _io_threads 参数

而是分别使用 innodb_read_ io threads 和innodb_write_ io _ threads 参数进行设置

• 可以通过命令SHOW ENGINE INNODB STATUS 来观察InnoDB 中的IO Thread: (注意：读线程的ID 总是小于写线程)

Purge Thread：

• 事务被提交后，其所使用的 undo log 可能不再需要，因此需要 PurgeThread 来回收已经使用并分配的 undo 页

• 注：在 InnoDB 1.1 版本之前， purge 操作仅在InnoDB 存储引擎的 Master Thread 中完成

而从 InnoDB 1.1 版本开始， purge 操作可以独立到单独的线程中进行，以此来减轻 Master Thread 的工作

• 从而提高 CPU 的使用率以及提升存储引擎的性能

在 InnoDB 1.1 版本中，即使将innodb_purge_ threads 设为大于1, InnoDB 存储引擎启动时也会将其设为1

• 从 InnoDB 1.2 版本开始， InnoDB 支持多个 Purge Thread, 这样做的目的是为了进一步加快 undo 页的回收

同时由于 Purge Thread 需要离散地读取undo 页，这样也能更进一步利用磁盘的随机读取性能。

Page Cleaner Thread：

• Page Cleaner Thread 是在InnoDB 1.2.x 版本中引入的

其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成

而其目的是为了减轻原 Master Thread 的工作及对于用户查询线程的阻塞，进一步提高 InnoDB 存储引擎的性能

缓冲池

InnoDB 存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理

• 因此可将其视为基于磁盘的数据库系统。在数据库系统中，由于CPU速度与磁盘速度之间的鸿沟
  • 基于磁盘的数据库系统通常使用 缓冲池技术 来提高数据库的整体性能

缓冲池简单来说就是一块内存区域，通过内存的速度来 弥补 磁盘速度较慢对数据库性能的影响

• 在数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中，这个过程称为将页FIX在缓冲池中

• 下一次再读相同的页时，首先判断该页是否在缓冲池中

若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页

对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上

• 通过一种称为 Checkpoint 的机制刷新回磁盘。同样，这也是为了提高数据库的整体性能

注：缓冲池的大小直接影响着数据库的整体性能，对于InnoDB 存储引擎而言

• 其缓冲池的配置通过参数innodb_buffer_pool_ size 来设置

InnoDB存储引擎中内存的结构

具体来看，缓冲池中缓存的数据页类型有：

索引页、数据页、undo 页、插入缓冲 、自适应哈希索引 、InnoDB 存储的锁信息 、数据字典信息等。

从 InnoDB 1.0.x 版本开始，允许有多个缓冲池实例。

每个页根据哈希值平均分配到不同缓冲池实例中。

这样做的好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。

可以通过参数 innodb_ buffer _pool_instances 来进行配置，该值默认为1。

从MySQL 5.6 版本开始，还可以通过 information_schema 架构下的表 INNODB_ BUFFER_POOL_STATS 来观察缓冲的状态。

LRU List 、Free List 和Flush List

InnoDB 存储引擎是怎么对这么大的内存区域进行管理的呢？

• （优化的LRU算法）

数据库中的缓冲池是通过LRU (Latest Recent Used, 最近最少使用）算法来进行管理的

• 即最频繁使用的页在LRU 列表的前端，而最少使用的页在 LRU 列表的尾端

当缓冲池不能存放新读取到的页时，将首先释放LRU 列表中尾端的页

• 在InnoDB 存储引擎中， 缓冲池中页的大小默认为 16KB, 同样使用LRU 算法对缓冲池进行管理

稍有不同的是 InnoDB 存储引擎对传统的LRU 算法做了一些优化

优化： 在 InnoDB 的存储引擎中， LRU 列表中还加入了 midpoint 位置

新读取到的页，虽然是最新访问的页，但并不是直接放入到LRU 列表的首部，而是放入到LRU 列表的 midpoint 位置

• 这个算法在InnoDB 存储引擎下称为 midpoint insertion strategy。

在默认配置下，该位置在LRU 列表长度的5/8 处。midpoint 位置可由参数 innodb_old_ blocks _pct 控制

在 InnoDB 存储引擎中，把 midpoint 之后的列表称为 old 列表，之前的列表称为 new 列表

• 可以简单地理解为new列表中的页都是最为活跃的热点数据

为什么不采用朴素的 LRU 算法，直接将读取的页放入到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

注：可以通过命令 SHOW ENGINE INNODB STATUS 来观察 LRU 列表及Free 列表的使用情况和运行状态

通过命令 SHOW ENGINE INNODB STATUS 可以看到：当前 Buffer pool size 共有 327 679 个页

• 即 327679*16K, 总共 5GB 的缓冲池

Free buffers 表示当前 Free 列表中页的数量，Database pages 表示 LRU 列表中页的数量

可能的情况是 Free buffers 与 Database pages 的数量之和不等于 Buffer pool size

• 说明Free列表和LRU列表基本包好了缓冲池中所有的页
• 因为缓冲池中的页还可能会被分配给自适应哈希索引、Lock 信息、Insert Buffer 等页
  • 这部分页不需要LRU 算法进行维护，因此不存在于 LRU 列表中

pages made young 显示了LRU 列表中页移动到前端的次数

• 因为该服务器在运行阶段没有改变 innodb_old_blocks_time 的值，因此 not young 为0

Buffer pool hit rate：

• 表示缓冲池的命中率，这个例子中为100%, 说明缓冲池运行状态非常良好

• 通常该值不应该小于95%

若发生Buffer pool hit rate 的值小于95% 这种情况 ，用户需要观察是否是由于全表扫描引起的LRU 列表被污染的问题

• 执行命令SHOW ENGINE INNODB STATUS 显示的不是当前的状态， 而是过去某个时间范围内InnoDB 存储引擎的状态

• 从上面的例子可以发现， Persecond averages calculated from the last 24 seconds 代表的信息为过去24 秒内的数据库状态

可以通过表 INNODB_BUFFER_PAGE_LRU 来观察每个LRU 列表中每个页的具体信息

• 例如通过下面的语句可以看到缓冲池 LRU 列表中 SPACE 为 1 的表的页类型

InnoDB 存储引擎从1.0.x 版本开始支持压缩页的功能，即将原本16KB 的页压缩为1KB 、2KB 、4KB 和8KB

而由于页的大小发生了变化， LRU 列表也有了些许的改变

• 对于非16KB 的页，是通过 unzip_LRU 列表进行管理的

可以看到 LRU 列表中一共有 1539 个页，而 unzip_LRU 列表中有 156 个页

• 这里需要注意的是， LRU 中的页包含了 unzip_LRU 列表中的页

unzip_LRU 是怎样从缓冲池中分配内存的呢？？

首先，在 unzip_LRU 列表中对不同压缩页大小的页进行分别管理

其次，通过 伙伴算法 进行内存的分配。例如对需要从缓冲池中申请页为4KB 的大小，其过程如下：

• 检查 4KB 的 unzip_LRU 列表，检查是否有可用的空闲页
• 若有，则直接使用
• 否则，检查 8KB 的 unzip_LRU 列表
• 若能够得到空闲页，将页分成 2 个 4KB 页，存放到 4KB 的 unzip_LRU 列表
• 若不能得到空闲页，从 LRU 列表中申请一个 16KB 的页，将页分为1 个8KB 的页、2 个4KB 的页
  • 分别存放到对应的 unzip_LRU 列表中

什么是脏页？？

在 LRU 列表中的页被修改后，称该页为 脏页(dirty page)

• 即缓冲池中的页和磁盘上的页的数据产生了不一致

这时数据库会通过 CHECKPOINT 机制 将脏页刷新回磁盘，而 Flush 列表 中的页即为 脏页列表

需要注意的是，脏页既存在于LRU 列表中，也存在于Flush 列表中

• LRU 列表用来管理缓冲池中页的可用性，Flush 列表用来管理将页刷新回磁盘， 二者互不影响

注：同 LRU 列表一样， Flush 列表也可以通过命令SHOW ENGINE INNODB STATUS 来查看

• 脏页同样存在于 LRU 列表中，故用户可以通过元数据表 INNODB_BUFFER_PAGE_LRU 来查看

重做日志缓冲

InnoDB 存储引擎的内存区域除了有缓冲池外，还有 重做日志缓冲(redo log buffer)

• InoDB 存储引擎 首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件

注：重做日志缓冲一般不需要设置得很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件

• 因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可

该值可由配置参数innodb_log_ buffer_ size 控制，默认为8MB

重做日志在下列三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中？

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

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

• 当重做日志缓冲池剩余空间小于1/2 时， 重做日志缓冲刷新到重做日志文件

额外的内存池

额外的内存池通常被DBA 忽略，他们认为该值并不十分重要，事实恰恰相反，该值同样十分重要

• 在InnoDB 存储引擎中，对内存的管理是通过一种称为 内存堆（heap）的方式 进行的

在对一些数据结构本身的内存进行分配时， 需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请

例如，分配了缓冲池(innodb_buffer_pool), 但是每个缓冲池中的帧缓冲(frame buffer)

• 还有对应的缓冲控制对象(buffer control block)
  • 这些对象记录了一些诸如LRU 、锁、等待等信息，而这个对象的内存需要从额外内存池中申请

因此，在申请了很大的InnoDB 缓冲池时，也应考虑相应地增加这个值

Checkpoint技术

InnoDB 是事务安全的MySQL 存储引擎，设计上采用了类似于 Oracle 数据库的架构

• 通常来说， InnoDB 存储引擎是OLTP应用中核心表的首选存储引擎

• 同时，也正是因为InnoDB 的存在，才使 MySQL 数据库变得更有魅力

本章将详细介绍InnoDB 存储引擎的体系架构及其不同于其他存储引擎的特性

缓冲池的设计目的为了协调 CPU 速度与磁盘速度的鸿沟

• 因此页的操作首先都是在缓冲池中完成的

如果一条DML 语句改变了页中的记录，那么此时页是脏的，即缓冲池中的页的版本要比磁盘的新

• 数据库需要将新版本的页从缓冲池刷新到磁盘

• 注：倘若每次一个页发生变化，就将新页的版本刷新到磁盘，那么这个开销是非常大的

同时，如果在从缓冲池将页的新版本刷新到磁盘时发生了 宕机，那么数据就不能恢复了

为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了 Write Ahead Log 策略

• 即当事务提交时，先写重做日志，再修改页

当由于发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复

• 这也是事务 ACID 中D (Durability 持久性）的要求

思考：如果重做日志可以无限地增大，同时缓冲池也足够大，那么是不需要将缓冲池中页的新版本刷新回磁盘

但是需要满足两个条件：

• 缓冲池可以缓存数据库中所有的数据

• 重做日志可以无限增大

如果条件满足带来的问题：

• 当数据库刚开始创建时，表中没有任何数据
  • 缓冲池的确可以缓存所有的数据库文件

然而随着市场的推广，用户的增加，产品越来越受到关注，使用晕也越来越大

• 这时负责后台存储的数据库的容量必定会不断增大

重做日志可以无限增大。也许是可以的，但是这对成本的要求太高，同时不便于运维

• DBA 或SA 不能知道什么时候重做日志是否已经接近于磁盘可使用空间的阈值

宕机后数据库的恢复时间会很长

问题的解决：Checkpoint (检查点）技术

• 缩短数据库的恢复时间

• 缓冲池不够用时，将脏页刷新到磁盘；

• 重做日志不可用时，刷新脏页

当数据库发生宕机时，数据库不需要重做所有的日志

• 因为 Checkpoint 之前的页都已经刷新回磁盘

故数据库只需对Checkpoint 后的重做日志进行恢复

• 这样就大大缩短了恢复的时间

注：重做日志出现不可用的情况是因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的

• 这从成本及管理上都是比较困难的

重做日志可以被重用的部分是指这些重做日志已经不再需要

• 即当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用

若此时重做日志还需要使用，那么必须强制产生 Checkpoint, 将缓冲池中的页至少刷新到当前重做日志的位置

对于 InnoDB 存储引擎而言，其是通过 LSN (Log Sequence Number) 来标记版本的

• 而LSN 是8 字节的数字，其单位是字节
• 每个页有LSN，重做日志中也有LSN，Checkpoint 也有LSN

在 InnoDB 存储引擎中， Checkpoint 发生的时间、条件及脏页的选择等都非常复杂

• 而 Checkpoint 所做的事情无外乎是将缓冲池中的脏页刷回到磁盘

不同之处在于 每次刷新多少页到磁盘，每次从哪里取脏页，以及什么时间触发 Checkpoint

在 InnoDB 存储引擎内部，有两种 Checkpoint，分别为：

• Sharp Checkpoint ssssa

• Fuzzy Checkpoint

Sharp Checkpoint 发生在数据库关闭时将所有的脏页都刷新回磁盘

• 这是默认的工作方式，即参数innodb_fast_shutdown= 1

但是若数据库在运行时也使用 Sharp Checkpoint, 那么数据库的可用性就会受到很大的影响

故在InnoDB 存储引擎内部使用 Fuzzy Checkpoint 进行页的刷新

• 即只刷新一部分脏页，而不是刷新所有的脏页回磁盘

在 InnoDB 存储引擎中可能发生如下 几种情况的 Fuzzy Checkpoint：

• Master Thread Checkpointsdsssa
• FLUSH_LRU_LIST Checkpoint
• Async/Sync Flush Checkpointssa
• Dirty Page too much Checkpoint

对于 Master Thread 中发生的 Checkpoint, 差不多 以每秒或每十秒的速度 从缓冲池的脏页列表中刷新一定比例的页回磁盘

这个过程是异步的，即此时 InnoDB 存储引擎可以进行其他的操作，用户查询线程不会阻塞

对于 FLUSH_ LRU_LIST Checkpoint 是因为 InnoDB 存储引擎需要保证 LRU 列表中需要有差不多 100 个空闲页可供使用

• 在InnoDB 1.1.x 版本之前，需要检查LRU 列表中是否有足够的可用空间操作发生在用户查询线程中
  • 显然这会阻塞用户的查询操作

倘若没有100 个可用空闲页，那么 InnoDB 存储引擎会将LRU 列表尾端的页移除

如果这些页中有脏页，那么需要进行Checkpoint, 而这些页是来自LRU 列表的

• 因此称为FLUSH_LRU_LIST Checkpoint

而从MySQL 5.6 版本，也就是InnoDB 1.2.x 版本开始，这个检查被放在了一个单独的Page Cleaner 线程中进行

• 并且用户可以通过参数innodb_lru scan_ depth 控制LRU 列表中可用页的数量，该值默认为1024）

Async/Sync Flush Checkpoint 指的是重做日志文件不可用的情况

• 这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的

若将巳经写入到重做日志的 LSN 记为 redo_lsn, 将已经刷新回磁盘最新页的LSN 记为 checkpoint_lsn, 则可定义：

checkpoint_age = redo_lsn - checkpoint_lsn

sync_water_mark = 75% * total_ redo_log_file_size （重做日志的大文件的总大小的 3/4）

sync_water_mark = 90% * total_ redo_ log_ file_ size （重做日志的大文件的总大小的 9/10）

checkpoint_age<async water_ mark，不需要刷新任何脏页到磁盘；

async_water_ mark<checkpoint_ age<sync_water_mark，触发Async Flush,

• 从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age<async water_mark:

checkpoint_ age>sync water—mark 这种情况一般很少发生，除非设置的重做日志文件太小，并且在进行类似LOAD DATA 的BULK INSERT 操作

此时触发Sync Flush 操作，从Flush 列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_ age<async water_ mark

Async/Sync Flush Checkpoint 是为了保证重做日志的循环使用的可用性

在 InnoDB 1.2.x 版本之前， Async Flush Checkpoint 会阻塞发现问题的用户查询线程

• 而 Sync Flush Checkpoint 会阻塞所有的用户查询线程，并且等待脏页刷新完成

从InnoDB 1.2.x 版本开始一一也就是MySQL 5.6 版本

• 这部分的刷新操作同样放入到了单独的 Page Cleaner Thread 中，故不会阻塞用户查询线程

MySQL 官方版本并不能查看刷新页是从Flush 列表中还是从LRU 列表中进行 Checkpoint 的，也不知道因为重做日志而产生的Async/Sync Flush 的次数

但是InnoSQL版本提供了方法，可以通过命令SHOW ENGINE INNODB STATUS 来观察

Dirty Page too much 即脏页的数量太多，导致 InnoDB 存储引擎强制进行 Checkpoint

锁机制

开发多用户、数据库驱动的应用时，最大的一个难点是：

• 一方面要最大程度地利用数据库的并发访问
• 另外一方面还要确保每个用户能以一致的方式读取和修改数据
  • 为此就有了锁Clocking的机制，同时这也是数据库系统区别于文件系统的一个关键特性

InnoDB 存储引擎较之 MySQL 数据库的其他存储引擎在这方面技高一筹

• 其实现方式非常类似于 Oracle 数据库

什么是锁

锁是数据库系统区别于文件系统的一个关键特性

• 锁机制用于管理对共享资源的并发访问，InnoDB 存储引擎会在行级别上对表数据上锁，这固然不错

不过InnoDB 存储引擎也会在数据库内部其他多个地方使用锁，从而允许对多种不同资源提供并发访问

• 注 1：数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性

对于锁，用户可能对某个特定的关系数据库系统的锁定模型有一定的经验，但这并不意味着知道其他数据库

• 也就是说，它们各自对于锁的实现完全不同

注 2：对于MyISAM 引擎，其锁是表锁设计。并发情况下的读没有问题，但是并发插入时的性能就要差一些了

• 若插入是在底部, MyISAM 存储引擎还是可以有一定的并发写入操作

注 3：对于 ===Microsoft SQL Server == 数据库，在Microsoft SQL Server 2005 版本之前其是 页锁 的

• 相对表锁的MyISAM 引擎来说并发性能有所提高

页锁容易实现，然而对于热点数据页的并发问题依然无能为力

到2005 版本， Microsoft SQL Server 开始支持乐观并发和悲观并发，在乐观并发下开始支持行级锁，但是其实现方式与InnoDB 存储引擎的实现方式完全不同

用户会发现在Microsoft SQL Server 下，锁是一种稀有的资源，锁越多开销就越大，因此它会有锁升级

• 在这种情况下，行 锁会升级到表锁，这时并发的性能又回到了以前

注 4：InnoDB 存储引擎锁的实现和Oracle 数据库非常类似，提供一致性的非锁定读、行级锁支持

• 行级锁没有相关额外的开销，并可以同时得到并发性和一致性

lock 与latch

在数据库中， lock 与latch 都可以被称为锁

但是两者有着截然不同的含义：

latch 一般称为 门锁（轻量级的锁），因为其要求锁定的时间必须非常短

• 若持续的时间长，则应用的性能会非常差

在InnoDB 存储引擎中， latch 又可以分为 mutex (互斥量）和rwlock (读写锁）

其目的是用来保证并发线程操作 临界资源 的正确性，并且通常没有死锁检测的机制

lock 的对象是 事务，用来锁定的是数据库中的对象

• 如表、页、行。并且一般lock的对象仅在事务commit 或rollback 后进行释放（不同事务隔离级别释放的时间可能不同）

此外， lock, 正如在大多数数据库中一样，是有 死锁 机制的

锁的类型

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

• 共享锁 (S Lock)：

  • 允许事务读一行数据

• 排他锁 (X Lock)：

  • 允许事务删除或更新一行数据

注 5：S 和X 锁都是行锁，兼容是指对同一记录 (row) 锁的兼容性情况

InnoDB 存储引擎支持多粒度(granular) 锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在

为了支持在不同粒度上进行加锁操作， InnoDB 存储引擎支持一种额外的锁方式，称之为意向锁 (lntention Lock)

意向锁是将锁定的对象分为多个层次：

• 意向锁意味着事务希望在更细粒度(fine granularity) 上进行加锁

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

设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。 其支持两种意向锁：

• 意向共享锁 (IS Lock) ：

  • 事务想要获得一张表中某几行的共享锁

• 意向排他锁 (IX Lock) ：

  • 事务想要获得一张表中某几行的排他锁

注 6：由于lnnoDB 存储引擎支持的是行级别的锁

• 因此 意向锁其实不会阻塞除全表扫以外的任何请求

故 表级意向锁与行级锁的兼容性如下：(只要有IX，和别的S,X都不兼容)

注 7：在InnoDB 1 . 0 版本之前，用户只能通过命令SHOW FULL PROCESSLIST

• SHOW ENGINE INNODB STATUS 等来查看当前数据库中锁的请求，然后再判断事务锁的情况

从InnoDBl.O 开始，在 INFORMATION_SCHEMA 架构下添加了表INNODB_TRX 、INNODB_LOCKS 、INNODB_LOCK_WAITS

通过这三张表，用户可以更简单地监控当前事务并分析可能存在的锁问题

自增长与锁

自增长 在数据库中是非常常见的一种属性，也是很多DBA 或开发人员首选的主键方式

• 在InnoDB 存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器 (auto-increment counter)

从MySQL 5.1.22 版本开始， InnoDB 存储引擎中提供了一种轻量级互斥量的自增长实现机制

• 这种机制大大提高了自增长值插入的性能

并且从该版本开始， InnoDB 存储引擎提供了一个参数innodb_ auto inc_ lock_ mode 来控制自增长的模式

• 该参数的默认值为1

一致性锁定读

在默认配置下，即事务的隔离级别为REPEATABLE READ 模式下

• InnoDB 存储引擎的SELECT 操作使用一致性非锁定读

但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性

• 而这要求数据库支待加锁语句，即使是对于SELECT 的只读操作

一致性非锁定读（造成不可重复度的本质原因）

一致性的非锁定读 ( consistent nonlocking read) 是指：

• InnoDB 存储引擎通过行多版本控制(multi versioning) 的方式来读取当前执行时间数据库中行的数据

如果读取的行正在执行DELETE 或UPDATE 操作，这时读取操作不会因此去等待行上锁的释放

• 相反地， InnoDB 存储引擎会去读取行的一个快照数据

外键和锁

外键主要用于引用完整性的约束检查

在InnoDB 存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB 存储引擎自动对其加一个索引

• 因为这样可以避免表锁——这比Oracle 数据库做得好， Oracle 数据库不会自动添加索引，用户必须自己手动添加
  • 这也导致了Oracle 数据库中可能产生死锁

对于外键值的插入或更新，首先需要查询父表中的记录，即SELECT 父表。

但是对于父表的SELECT 操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题

• 因此这时使用的是SELECT … LOCK IN SHARE MODE 方式，即主动对父表加一个S 锁

如果这时父表上已经这样加X 锁，子表上的操作会被阻塞

事务

事务(Transaction) 是数据库区别于文件系统的重要特性之一

• 在文件系统中，如果正在写文件，但是操作系统突然崩溃了，这个文件就很有可能被破坏

当然，有一些机制可以把文件恢复到某个时间点

• 不过，如果需要保证两个文件同步，这些文件系统可能就显得无能为力了

这正是数据库系统引入事务的主要目的：

• 事务会把数据库从一种一致状态转换为另一种一致状态

• 在数据库提交工作时，可以确保要么所有修改都已经保存了，要么所有修改都不保存

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

ACID 是以下4 个词的缩写：

• 原子性(atomicity)

• 一致性(consistency)

• 隔离性(isolation)

• 持久性 (durability)

概述

事务可由一条非常简单的SQL 语句组成，也可以由一组复杂的SQL 语旬组成

• 事务是访问并更新数据库中各种数据项的一个程序执行单元

在事务中的操作，要么都做修改，要么都不做，这就是事务的目的，也是事务模型区别与文件系统的重要特征之一

• 注：理论上说，事务有着极其严格的定义，它必须同时满足四个特性

但是数据库厂商出于各种目的，并没有严格去满足事务的ACID 标准

• 但是我们要了解ACID

原子性

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

• 事务中任何一个SQL 语句执行失败，已经执行成功的SQL 语句也必须撤销，数据库状态应该退回到执行事务前的状态

一致性

一致性指事务将数据库从一种状态转变为下一种一致的状态

在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏

• 事务是一致性的单位，如果事务中某个动作失败了，系统可以自动撤销事务一返回初始化的状态

隔离性

隔离性还有其他的称呼，如并发控制(concurrency control) 、可串行化(serializability) 、锁(locking) 等

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

当前数据库系统中都提供了一种粒度锁(granular lock) 的策略，允许事务仅锁住一个实体对象的子集

• 以此来提高事务之间的并发度

持久性

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

即使发生宕机等故障，数据库也能将数据恢复

• 需要注意的是，只能从事务本身的角度来保证结果的永久性

注：在事务提交后，所有的变化都是永久的

• 即使当数据库因为崩溃而需要恢复时，也能保证恢复后提交的数据都不会丢失

但若不是数据库本身发生故障，而是一些外部的原因，如RAID 卡损坏、自然灾害等原因导致数据库发生问题，那么所有提交的数据可能都会丢失

• 因此持久性保证事务系统的高可靠性(High Reliability), 而不是高可用性(High Availab山ty)

对于高可用性的实现，事务本身并不能保证，需要一些系统共同配合来完成

分类

从事务理论的角度来说，可以把 事务分为以下几种类型：

• 扁平事务( Flat Transactions)

• 带有保存点的扁平事务(Flat Transactions with Savepoints)

• 链事务(Chained Transactions)

• 嵌套事务(Nested Transactions)

• 分布式事务(Distributed Transactions)

扁平事务：是事务类型中最简单的一种，但在实际生产环境中，这可能是使用最为频繁的事务

在扁平事务中，所有操作都处于同一层次，其由BEGIN WORK 开始

• 由COMMIT WORK 或ROLLBACK WORK 结束，其间的操作是原子的，要么都执行，要么都回滚

• 因此扁平事务是应用程序成为原子操作的基本组成模块

注：扁平事务虽然简单，但在实际生产环境中使用最为频繁

• 正因为其简单，使用频繁，故每个数据库系统都实现了对扁平事务的支持

扁平事务的主要限制是不能提交或者回滚事务的某一部分，或分几个步骤提交

带有保存点的扁平事务：

• 除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态

这是因为某些事务可能在执行过程中出现的错误并不会导致所有的操作都无效，放弃整个事务不合乎要求，开销也太大

保存点（Savepoint) 用来通知系统应该记住事务当前的状态，以便当之后发生错误时

• 事务能回到保存点当时的状态

注 1：对于扁平的事务来说，其隐式地设置了一个保存点

然而在整个事务中，只有这一个保存点，因此，回滚只能回滚到事务开始时的状态

• 保存点用SAVE WORK 函数来建立，通知系统记录当前的处理状态

当出现问题时，保存点能用作内部的重启动点，根据应用逻辑，决定是回到最近一个保存点还是其他更早的保存点

注 2：保存点在事务内部是递增的，这意味着 ROLLBACK 不影响保存点的计数，并且单调递增的编号能保持事务执行的整个历史过程

• 包括在执行过程中想法的改变。如果想要完全回滚事务，还需要再执行命令ROLLBACK WORK

链事务：

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

注意，提交事务操作和开始下一个事务操作将合并为一个原子操作

这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中进行的一样

注 1 ：

可视为保存点模式的一种变种。带有保存点的扁平事务，当发生系统崩溃时，所有的保存点都将消失

• 因为其保存点是易失的(volatile),而非持久的(persistent)

这意味着当进行恢复时，事务需要从开始处重新执行，而不能从最近的一个保存点继续执行

注 2 ：

链事务与带有保存点的扁平事务不同的是，带有保存点的扁平事务能回滚到任意正确的保存点

• 而链事务中的回滚仅限于当前事务，即只能恢复到最近一个的保存点。对于锁的处理，两者也不相同

注 3 ：

链事务在执行COMMIT 后即释放了当前事务所待有的锁，而带有保存点的扁平事务不影响迄今为止所持有的锁

嵌套事务：

是一个层次结构框架。由一个顶层事务( topleveltransaction) 控制着各个层次的事务

• 顶层事务之下嵌套的事务被称为 子事务 (subtransaction), 其控制每一个局部的变换

注 1：

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

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

位于根节点的事务称为 顶层事务，其他事务称为 子事务

• 事务的前驱称 (predecessor) 为父事务(parent), 事务的下一层称为儿子事务(child)

子事务既可以提交也可以回滚

• 但是它的提交操作并不马上生效，除非其父事务巳经提交

因此可以推论出，任何子事物都在顶层事务提交后才真正的提交

树中的任意一个事务的回滚会引起它的所有子事务一同回滚，故子事务仅保留 A 、C 、I 特性，不具有D 的特性

注 2：

• 实际的工作是交由叶子节点来完成的，即只有叶子节点的事务才能访问数据库、发送消息、获取其他类型的资源

而高层的事务仅负责逻辑控制，决定何时调用相关的子事务

• 即使一个系统不支持嵌套事务，用户也可以通过保存点技术来模拟嵌套事务

在恢复时采用保存点技术比嵌套查询有更大的灵活性，如在完成Tk3 这事务时，可以回滚到保存点S2 的状态

• 而在嵌套查询的层次结构中，却不允许

嵌套事务的灵活性表现如下：

• 当通过保存点技术来模拟嵌套事务时，用户无法选择哪些锁需要被子事务继承，哪些需要被父事务保留

这就是说，无论有多少个保存点，所有被锁住的对象都可以被得到和访问

而在嵌套查询中，不同的子事务在数据库对象上持有的锁是不同的（说白了就是对于嵌套事务父事务的锁可以全部给它的子事务也可以就给其中的几个

• 如果子事务有父事务没有的锁，那么通过反向继承，也可以使父事务持有
  • 而对于保存点技术模拟的嵌套事务是不能的选择哪些锁被优化的）

如果系统支持在嵌套事务中并行地执行各个子事务，在这种情况下，采用保存点的扁平事务来模拟嵌套事务就不切实际了

• 这从另一个方面反映出，想要实现事务间的并行性， 需要真正支持的嵌套事务

分布式事务：

• 通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点（银行转账就是典型的分布式事务）

注：对于InnoDB 存储引擎来说，其支持扁平事务、带有保存点的事务、链事务、分布式事务

• 对于嵌套事务，其并不原生支持
  • 因此，对有并行事务需求的用户来说，MySQL 数据库或InnoDB 存储引擎就显得无能为力了

然而用户仍可以通过带有保存点的事务来模拟串行的嵌套事务

日志文件

事务(Transaction) 是数据库区别于文件系统的重要特性之一

• 在文件系统中，如果正在写文件，但是操作系统突然崩溃了，这个文件就很有可能被破坏

当然，有一些机制可以把文件恢复到某个时间点

• 不过，如果需要保证两个文件同步，这些文件系统可能就显得无能为力了

这正是数据库系统引入事务的主要目的：

• 事务会把数据库从一种一致状态转换为另一种一致状态

在数据库提交工作时，可以确保要么所有修改都已经保存了，要么所有修改都不保存

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

ACID 是以下4 个词的缩写：

• 原子性(atomicity)

• 一致性(consistency)

• 隔离性(isolation)

• 持久性 (durability)

事务的实现

事务隔离性 (I) 由锁来实现。原子性(A)、一致性(C.)、持久性(D) 通过数据库的redo log 和undo log 来完成

• redo log 称为重做日志，用来保证事务的原子性和持久性
• undo log 用来保证事务的一致性

注：有的DBA 或许会认为undo 是redo 的逆过程，其实不然

redo 和undo 的作用都可以视为是一种恢复操作， redo 恢复提交事务修改的页操作

• 而undo 回滚行记录到某个特定版本

因此两者记录的内容不同， redo 通常是物理日志，记录的是页的物理修改操作

• undo 是逻辑日志，根据每行记录进行记录

基本概念

重做日志用来实现事务的 持久性，即事务ACID 中的 D。其由两部分组成：

• 内存中的重做日志缓冲(redo log buffer), 其是 易失 的

• (磁盘)重做日志文件 (redo log file)，其是 持久 的

InnoDB 是事务的存储引擎，其通过 Force Log at Commit 机制实现事务的持久性

• 即当事务提交(COMMIT) 时，必须先将该事务的所有日志写入到重做日志文件进行持久化
  • 待事务的COMMIT 操作完成才算完成

这里的日志是指重做日志，在InnoDB 存储引擎中，由两部分组成，即redo log 和undo log

redo log 用来保证事务的持久性

undo log 用来帮助事务回滚及MVCC 的功能

redo log 基本上都是顺序写的，在数据库运行时不需要对redo log 的文件进行读取操作

• 而undo log 是需要进行 随机读写 的

注 1 ：为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后

• InnoDB 存储引擎都需要调用一次 fsync 操作

由于重做日志文件打开并没有使用O_DIRECT 选项，因此重做日志缓冲先写入文件系统缓存

• 为了确保重做日志写入磁盘，必须进行一次fsync 操作

由于fsync 的效率取决于磁盘的性能：

• 因此磁盘的性能决定了事务提交的性能，也就是数据库的性能

注 2 ：InnoDB 存储引擎允许用户手工设置非持久性的情况发生，以此提高数据库的性能

• 即当事务提交时，日志不写入重做日志文件，而是等待一个时间周期后再执行fsync 操作

由于并非强制在事务提交时进行一次fsync 操作，显然这可以显著提高数据库的性能

• 但是当数据库发生宥机时，由于部分日志未刷新到磁盘，因此会丢失最后一段时间的事务

注 3 ：参数innodb_ flush_log_at_trx_commit 用来控制重做日志刷新到磁盘的策略

该参数的默认值为 1 , 表示事务提交时必须调用一次fsync 操作

• 还可以设置该参数的值为0 和 2

0 表示事务提交时不进行写入重做日志操作，这个操作仅在master thread 中完成

• 而在master thread 中每1 秒会进行一次重做日志文件的fsync 操作
• 2表示事务提交时将重做日志写入重做日志文件， 但仅写入文件系统的缓存中，不进行fsync 操作

在这个设置下，当MySQL 数据库发生宕机而操作系统不发生宥机时，并不会导致事务的丢失

• 而当操作系统宥机时， 重启数据库后会丢失未从文件系统缓存刷新到重做日志文件那部分事务

虽然用户可以通过设置参数innodb_flush_log_at_trx_commit 为0 或2 来提高事务提交的性能，但是需要牢记的是：

• 这种设置方法丧失了事务的ACID 特性
• 而针对上述存储过程，为了提高事务的提交性能，应该在将多条记录插入表后进行一次的COMMIT 操作，而不是在每插入一条记录后进行一次COMMIT 操作
  • 这样做的好处是还可以使事务方法在回滚时回滚到事务最开始的确定状态

在 MySQL 数据库中还有 二进制日志(binlog) , 其用来进行 POINT-IN-TIME(PIT) 的恢复及 主从复制 (Replication) 环境的建立

从表面上看其和重做日志非常相似，都是记录了对于数据库操作的日志

然而也有很大的区别：

• 重做日志是在InnoDB 存储引擎层产生，而二进制日志是在MySQL 数据库的上层产生的，并且二进制日志不仅仅针对于InnoDB 存储引擎

  • MySQL 数据库中的任何存储引擎对于数据库的更改都会产生二进制日志

• 两种日志记录的内容形式不同

  • MySQL 数据库上层的二进制日志是一种逻辑日志，其记录的是对应的SQL 语句
  • 而InnoDB 存储引擎层面的重做日志是物理格式日志，其记录的是对于每个页的修改

• 两种日志记录写入磁盘的时间点不同，二进制日志只在事务提交完成后进行一次写入，即对于每一个事务，仅包含对应事务的一个日志

  • 而InnoDB 存储引擎的重做日志在事务进行中不断地被写入，这表现为日志并不是随事务提交的顺序进行写入的

因此每个事务对应多个日志条目，并且事务的重做日志写入是并发的

log block

在InnoDB 存储引擎中，重做日志都是以512 字节进行存储的

• 这意味着重做日志缓存、重做日志文件都是以块(block) 的方式进行保存的
  • 称之为重做日志块 (redolog block), 每块的大小为 512 字节

若一个页中产生的重做日志数最大于512 字节，那么需要分割为多个重做日志块进行存储

• 此外，由于重做日志块的大小和磁盘扇区大小一样，都是512 字节
  • 因此重做日志的写入可以保证原子性，不需要doublewrite 技术

重做日志块除了日志本身之外，还由 日志块头(log block header) 及 日志块尾(logblock tailer) 两部分组成

重做日志头一共占用12 字节，重做日志尾占用8 字节

• 故每个重做日志块实际可以存储的大小为 492 字节(512-12-8) ，如图：

注 ：

log buffer 是由 log block 组成，在内部 log buffer 就好似一个 数组

• 因此 LOG_BLOCK_HDR_NO 用来标记这个数组中的位置。其是 递增并且循环使用 的，占用 4个字节
  • 但是由于第一位用来判断是否是flush bit, 所以最大的值为 2G （4字节，32位，减去1位，也就是31位）

LOG_ BLOCK_ HOR_ DATA _LEN 占用2 字节，表示log block 所占用的大小

• 当logblock 被写满时，该值为Ox200, 表示使用全部log block 空间，即占用512 字节

LOG_BLOCK_FIRST_REC_GROUP占用2 个字节，表示 log block 中第一个日志所在的偏移量

• 如果该值的大小和LOG_BLOCK_OR_DATA_LEN 相同，则表示当前log block 不包含新的日志

如事务T1 的重做日志1 占用762 字节，事务T2 的重做日志占用100 字节

• 由于每个log block 实际只能保存492 个字节，因此其在log buffer 中的情况应如下图：

由于事务T1 的重做日志占用792 字节，因此需要占用两个log block

• 左侧的log block 中LOG_BLOCK_FIRST_ REC_GROUP 为12, 即log block中第一个日志的开始位置

在第二个log block 中，由于包含了之前事务 T1 的重做日志，事务 T2 的日志才是 log block 中第一个日志

• 因此该log block 的LOG_BLOCK_FIRST_REC_GROUP 为282 (270+12)

LOG_BLOCK_CHECKPOINT_NO 占用4 字节，表示该 log block 最后被写入时的检查点第4 字节的值

log block tailer 只由1 个部分组成，其值和LOG_BLOCK_HDR_NO 相同，并在函数log_block_init 中被初始化

索引

索引是应用程序设计和开发的一个重要方面

• 若索引太多，应用 程序的性能 可能会受到影响。而索引太少，对 查询性能 又会产生影响

要找到一个合适的平衡点，这对应用程序的性能至关重要

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

• B+ 树索引

• 全文索引

• 哈希索引

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

• B + 树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和最为有效的索引

• B+ 树索引的构造类似于 二叉树，根据键值( Key Value ) 快速找到数据

注1：B+ 树中的B 不是代表二叉, 而是代表 平衡, 因为 B + 树是从最早的平衡二叉树演化而未，但是 B + 树不是一个二叉树

注2：B + 树索引并不能找到一个给定键值的具体行

B+ 树索引能找到的只是被查找数据行所在的页。

• 然后数据库通过把页读入到内存，再在内存中进行查找， 最后得到要查找的数据

B+树索引

B+ 树索引的本质就是 B+ 树在数据库中的实现

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

因为当前一般的机械磁盘每秒至少可以做 100 次IO , 2 ~ 4 次的IO 意味着查询时间只需0.02 ~ 0.04 秒

数据库中的B+ 树索引可以分为 聚集索引 和 辅助索引, 但是不管是聚集还是辅助的索引，其内部都是B+ 树的

• 即 高度平衡 的，叶子节点存放着所有的数据

注：聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息

注2：B + 树索引并不能找到一个给定键值的具体行

• B+ 树索引能找到的只是被查找数据行所在的页。

然后数据库通过把页读入到内存，再在内存中进行查找， 最后得到要查找的数据

聚集索引

InnoDB 存储引擎表是索引组织表，即表中数据按照主键顺序存放。

• 而聚集索引就是按照每张表的主键构造一棵B+ 树，同时叶子节点中存放的即为整张表的行记录数据
  • 也将聚集索引的叶子节点称为 数据页

聚集索引的这个特性决定了索引组织表中数据也是索引的一部分

• 同B+ 树数据结构一样，每个数据页都通过一个 双向链表 来进行链接

注 1：由于实际的数据页只能按照一棵B+ 树进行排序，因此每张表只能拥有一个聚集索引(可以理解为主键)

• 在多数情况下，查询优化器倾向于采用聚集索引

因为聚集索引能够在B+ 树索引的叶子节点上直接找到数据

• 此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询

查询优化揣能够快速发现某一段范围的数据页需要扫描

数据库中的B+ 树索引可以分为== 聚集索引 和 辅助索引, 但是不管是聚集还是辅助的索引

• 其内部都是B+ 树，即 高度平衡 的，叶子节点存放着所有的数据

聚集索引的优点：

• 它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据

如用户需要查询一张注册用户的表，查询最后注册的10 位用户，由于B+ 树索引是双向链表的

• 用户可以快速找到最后一个数据页，并取出10 条记录

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

辅助索引

对于辅助索引(Secondary Index, 也称非聚集索引），叶子节点并不包含行记录的全部数据

• 叶子节点除了包含键值以外， 每个叶子节点中的索引行中还包含了一个 书签(bookmark)

该书签用来告诉InnoDB 存储引擎哪里可以找到与索引相对应的行数据

• 由于InnoDB 存储引擎表是索引组织表

如何通过辅助索引寻找数据？

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引

当通过辅助索引来寻找数据时， InnoDB 存储引擎会遍历辅助索引并通过叶子节点的指针获得指向主键索引的主键

• 然后再通过主键索引来找到一个完整的行记录

覆盖索引

InnoDB 存储引擎支持覆盖索引(covering index, 或称索引覆盖）

• 即从辅助索引中就可以得到查询的记录， 而不需要查询聚集索引中的记录

好处：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO 操作

注1：覆盖索引技术最早是在InnoDB Plugin 中完成并实现

这意味着对于InnoDB 版本小于1.0 的，或者MySQL 数据库版本为5.0 或以下的， InnoDB 存储引擎不支持覆盖索引特性

性能调优

InnoDB 存储引擎的性能问题：

选择合适的CPU

内存的重要性

硬盘对数据库性能的影响

合理地设置RAID

操作系统的选择也很重要

不同文件系统对数据库的影响

选择合适的基准测试工具

选择合适的CPU

当前数据库的应用类型一般分为两大类：OLTP、OLAP

OLAP 多用在数据仓库或数据集市中，一般需要执行复杂的SQL 语旬来进行查询

OLTP 多用在日常的事物处理应用中，如银行交易、在线商品交易、Blog 、网络游戏等应用

• 相对于OLAP,数据库的容量较小

InnoDB 存储引擎一般都应用于OLTP 的数据库应用：

• 用户操作的并发量大

• 事务处理的时间一般比较短

• 查询的语句较为简单，一般都走索引

• 复杂的查询较少

OLTP 的数据库应用本身对CPU 的要求并不是很高

• 因为复杂的查询可能需要执行比较、排序、连接等非常耗CPU 的操作，这些操作在OLTP 的数据库应用中较少发生

因此，可以说 OLAP 是CPU 密集型的操作，而OLTP 是IO 密集型的操作

• 建议在采购设备时，将更多的注意力放在提高IO 的配置上

注 1：从InnoDB 存储引擎的设计架构上来看，其主要的后台操作都是在一个单独的master thread 中完成的

• 因此并不能很好地支持多核的应用

当然，开源社区已经通过多种方法来改变这种局面，而InnoDB 1.0 版本在各种测试下已经显示出对多核CPU 的处理性能的支持有了极大的提高

• 而InnoDB 1.2 版本又支持多个purge 线程，以及将刷新操作从master thread 中分离出来

因此，若用户的CPU 支持多核， InnoDB 的版本应该选择1.1 或更高版本

另外，如果CPU 是多核的，可以通过修改参数 innodb_read_io_threads 和innodb_ write_io_threads 来增大IO 的线程

• 这样也能更充分有效地利用CPU的多核性能。

注 2：在当前的MySQL 数据库版本中， 一条SQL 查询语句只能在一个CPU 中工作，并不支持多CPU 的处理

• OLTP 的数据库应用操作一般都很简单，因此对OLTP 应用的影响并不是很大

但是，多个CPU 或多核CPU 对处理大并发最的请求还是会有帮助

内存的重要性

内存的大小是最能直接反映数据库的性能

InnoDB 存储引擎既缓存数据，又缓存索引，并且将它们缓存于一个很大的缓冲池中，即 InnoDB Buffer Pool

• 因此，内存的大小直接影响了数据库的性能

注 3 ：在上述测试中，数据和索引总大小为18GB,

• 然后将缓冲池的大小分别设为2GB 、4GB 、6GB 、8GB 、10GB 、12GB 、14GB 、16GB 、18GB 、20GB 、22GB, 再进行sysbench的测试

可以发现，随着缓冲池的增大，测试结果TPS (Transaction Per Second) 会线性增长

当缓冲池增大到20GB 和22GB 时，数据库的性能有了极大的提高

• 因为这时缓冲池的大小已经大于数据文件本身的大小，所有对数据文件的操作都可以在内存中进行

因此这时的性能应该是最优的，再调大缓冲池并不能再提高数据库的性能

如何判断当前数据库的内存是否已经达到瓶颈了呢？

• 可以通过查看当前服务器的状态，比较物理磁盘的读取和内存读取的比例来判断缓冲池的命中率
  • 通常InnoDB 存储引擎的缓冲池的命中率不应该小于99%

硬盘对数据库性能的影响

传统机械硬盘

• 当前大多数数据库使用的都是传统的机械硬盘

• 机械硬盘的技术目前巳非常成熟，在服务器领域一般使用SAS 或SATA 接口的硬盘

• 服务器机械硬盘开始向小型化转型，目前大部分使用2.5 寸的SAS 机械硬盘

机械硬盘有两个重要的指标：

• 一个是寻道时间，另一个是转速。当前服务器机械硬盘的寻道时间已经能够达到3ms, 转速为15 000RPM (rotate per minute)

• 传统机械硬盘最大的问题在于读写磁头，读写磁头的设计使硬盘可以不再像磁带一样，只能进行顺序访问，而是可以随机访问

• 但是，机械硬盘的访问需要耗费长时间的磁头旋转和定位来查找，因此顺序访问的速度要远高于随机访问

• 传统关系数据库的很多设计也都是在尽量充分地利用顺序访问的特性。

固态硬盘

固态硬盘，更准确地说是基于闪存的固态硬盘，是近几年出现的一种新的存储设备，其内部由闪存(Flash Memory) 组成

• 因为闪存的低延迟性、低功耗，以及防震性，闪存设备已在移动设备上得到了广泛的应用

企业级应用一般使用固态硬盘，通过并联多块闪存来进一步提高数据传输的吞吐量。

• 传统的存储服务提供商EMC 公司已经开始提供基于闪存的固态硬盘的TB 级别存储解决方案

数据库厂商Oracle 公司最近也开始提供绑定固态硬盘的Exadata 服务器