MySQL技术内幕（一） InnoDB存储引擎

1. InnoDB 体系架构

InnoDB 存储引擎有多个内存块，可以认为这些内存块组成一个大的内存池。

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

简单来说，就是相当于一个小型的操作系统，后台线程相当于CPU，内存池相当于内存，文件就是磁盘。

1.1 后台线程

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

• Master Thread：

  • Master Thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性、合并插人缓冲(INSERT BUFFER)等。

• IO Thread：

  • 在InnoDB 存储引擎中大量使用了AIO来处理写IO请求，极大提高了数据库的性能。可以通过show engine InnoDB status 命令查看
  • 

• Purge Thread：

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

• Page Cleaner Thread：

  • 进行刷新脏页的操作。

1.2 内存

① 缓冲池

• InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。为了解决CPU速度与磁盘速度不匹配的问题，通常使用缓冲池技术来提高数据库的整体性能。
• 在读取页的时候，首先从磁盘读到的页放在缓冲池中，下一次读取相同的页就不需要在进行磁盘IO，如果没有命中就要去磁盘读取。
• 修改操作的时候，则先修改缓冲池中的页，再以一定得频率刷新到磁盘上。（刷新回磁盘的操作是通过Checkpoint机制刷新回磁盘，下面会介绍）。
• 具体来看，缓冲池中缓存的数据页类型有:索引页、数据页、undo页、插入缓冲( insert buffer)、自适应哈希索引(adaptive hash index)、InnoDB存储的锁信息（lockinfo)、数据字典信息(data dictionary〉等。

• 在InnoDB 1.x之后，允许有多个缓冲池实例，每个页根据哈希值平均分配到不同的缓冲池中，可以减少数据库内部竞争，提高并发处理能力。

② LRU List、Free List 和 Flush List

InnoDB 存储引擎主要通过这三个链表对缓冲池的内存区域进行管理。

• LRU List：在InnoDB 存储引擎中，缓冲池中页的大小默认为16KB，使用LRU算法进行管理，但是在LRU的基础上进行了一些改进。
  • 新读取到的页并不是直接放在LRU列表的首部，而是放在midpoint位置（默认5/8处）。
  • 因为一些索引或数据扫描操作，可能需要访问非常多的页，而这些页可能只是在此次使用，为了避免这些页将真正的热点数据移除，而采用这种形式。
  • InnoDB 又引入了一个innodb_old_blocks_time参数，如果读取到的页经过多少时间，才会被放到LRU列表的热端。
  • 另外 InnoDB 1.0.x版本之后支持压缩页功能。
• Free List：LRU列表管理已经读取的页，Free列表管理空闲的页，例如当数据库启动的时候LRU列表是空的，读取进入页之后先向Free列表请求页，然后删除Free列表的页，加入LRU列表中，当Free列表没有页了再进行页面置换。
• Flush List：在LRU列表中的页诶修改之后，就称之为脏页，即缓冲池中的页和磁盘上的页数据产生了不一致，Flush 列表的页就是脏页的列表。

③ 重做日志缓冲

• InnoDB存储引擎首先将重做日志信息先放入到这个重做日志缓冲区，然后按一定频率将其刷新到重做日志文件。默认大小为8MB。
• 以下三种情况会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中：
  • Master Thread每一秒将重做日志缓冲刷新到重做日志文件;
  • 每个事务提交时会将重做日志缓冲刷新到重做日志文件;
  • 当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件。

④ 额外的内存池

• 在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆(heap)的方式进行的。
• 在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。

1.3 Checkpoint 技术

Checkpoint（检查点） 技术的目的主要就是解决以下问题

• 缩短数据库的恢复时间：
  • 数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新到磁盘，只需要重做Checkpoint之后的日志，缩短了恢复时间。
• 缓冲池不够用时，将脏页刷新到磁盘
  • 如果缓冲池不够用的情况下，LRU需要进行页面置换，如果被置换的页是脏页，则强制执行Checkpoint，将改页写回磁盘。
• 重做日志不可用时，刷新脏页
  • 因为重做日志基本都是重复使用，如果要被覆盖的重做日志的内容还没有被写回磁盘，则强制执行Checkpoint，将那些页刷新到磁盘。

1.5 Master Thread 工作方式

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

lnnoDB 1.0.x版本之前的Master Thread

Master Thread 具有最高的线程优先级别。其内部由多个循环组成。

循环中每秒一次的操作包括：

• 日志缓冲刷新到磁盘，即时这个事务还没有提交（总是）。
  • 即使事务没有提交，每秒一次的刷新操作也会将重做日志缓冲中的内容刷新到磁盘，所以很大的事务提交时间也很短。
• 合并插入缓存（可能）
  • 存储引擎会价差当前一秒发生的IO次数是否小于五次，小于则认为IO压力小，可以进行插入缓冲操作。
• 至多可以刷新100个缓冲池中的脏页到磁盘。
• 如果当前没有用户活动，则切换到后台循环，节省资源。

每十秒进行一次的操作：

• 刷新100个脏页到磁盘（可能）。
  • 存储引擎会检查过去十秒的IO操作次数，小于两百则由足够的IO能力，则可以将100个脏页刷新回磁盘。
  • 另外存储引擎会判断脏页的比例如果大于70% 则回收100个，如果小于，则需要刷新10%（一定会发生）。
• 合并至多5个插入缓冲（总是）。
• 将日志缓冲刷新到磁盘（与每一秒做的操作一致）。
• 删除无用的 Undo 页，会判断哪些 Undo 页可以删除，最多回收20个。

后台线程循环（background loop）：若当前没有用户活动（数据库空闲时）或者数据库关闭( shutdown)，就会切换到这个循环。background loop会执行以下操作：

• 删除无用的Undo页
• 合并20个插入缓冲
• 跳回到主循环。
• 跳转到flush loop 刷新100个页直到符合条件。

如果flush loop 也无事可做，可能会切换到suspend_loop 暂停循环，将主线程挂起。

lnnoDB 1.2.x版本之前的Master Thread

增加了一些关于脏页，插入缓冲以及 Undo页的设置选项，在设备允许的情况下，可以进行自定义，增强刷新的页数等。

lnnoDB 1.2.x版本将的Master Thread

将刷新脏页的线程分离到了一个单独的 Page Cleaner Thread。

1.6 InnoDB 关键特性

InnoDB存储引擎的关键特性包括:

• 插入缓冲（Insert Buffer)
• 两次写( Double Write)
• 自适应哈希索引(Adaptive Hash Index)
• 异步IO (Async IO)
• 刷新邻接页(Flush Neighbor Page)

1.6.1 插入缓冲（Insert Buffer）

1. Insert Buffer

主键是行唯一的标识符，通常应用程序中行记录的插入顺序是按照主键主键递增的顺序进行插入的。但是对于非聚集索引叶子节点的插入不再是顺序的了，这时就需要离散地访问非聚集索引页。

所以InnoDB 设计了插入缓冲：

• 对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中。
• 如果在，直接插入，不在则先放入到 Insert Buffer 中。
• 然后再以一定的频率以及情况进行 Insert Buffer 和辅助索引叶子节点的合并操作。
• 这时通常能将多个插入操作合并到一个操作，因为存在于同一个索引页，从而提供性能。

Insert Buffer 的使用需要同时满足以下两个条件:

• 索引是辅助索引( secondary index);
• 索引不是唯一(unique）的。

2. Change Buffer

InnoDB 从1.0.x版本开始引入了Change Buffer，可将其视为Insert Buffer 的升级。从这个版本开始，InnoDB存储引擎可以对DML 操作 ----- INSERT、DELETE、UPDATE都进行缓冲，他们分别是:Insert Buffer、Delete Buffer、Purge buffer。

3. Insert Buffer 的内部实现

Insert Buffer 的数据结构是一棵B+树，由叶子节点和非叶子节点组成。

非叶子节点 存放的是查询的search key （一共占用9个字节）。也就是数据表到具体物理位置的映射。

• space：表示待插入记录所在表的表空间id，每个表有一个唯一的 space id 占用4字节；
• marker占用1字节，用来兼容老版本的 Insert Buffer；
• offset 表示页所在的偏移量，占用4字节；

当一个辅助索引要插入页时，如果该页不在缓冲池中，则会根据以上规则构造一个 search key ，然后查询该B+树，将记录插入到对应的叶子节点。

叶子节点记录如下：前三个字段和search key 的字段一样，metadata 字段记录一些标志，从第五个字段开始存储实际插入记录的各个字段。

为了方便合并插入缓冲区的一些操作，还需要维护一个特殊的页来记录每个辅助索引页的一些信息。称为 Insert Buffer Bitmap

4. Merge Insert Buffer

Insert Buffer 中的记录何时合并到真正的辅助索引中：

• 辅助索引页被读取到缓冲池时；
  • 如果辅助索引页被读取到缓冲池，例如通过SELECT 查询读取，则检查Insert Buffer Bitmap ，检查该索引页是否有记录存在于Insert Buffer 中，如果有，则插入该记录。
• Insert Buffer Bitmap页追踪到该辅助索引页已无可用空间时;
• Master Thread 每一秒或者十秒一次的合并操作。

1.6.2 两次写

InnoDB存储引擎正在写入某个页到表中，而这个页只写了一部分，数据库就宕机了，这种情况称为部分写失效。

这时会想到的是使用redo日志进行操作，但是redo日志记录的是对页的物理操作，如果页本身已经发生损坏，则是没有意义的，所以采用在使用redo重做日志之前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做。这就称为两次写（double write）。

具体实现步骤为：

• 对数据缓冲池中的脏页进行刷新时，并不直接写磁盘。
• 而是会通过memcpy函数将脏页先复制到内存中的double write buffer。
• 之后通过double write buffer再分两次、每次1MB顺序写入共享表空间的物理磁盘上。
  • 因为该过程写入磁盘的空间是连续的，所以，IO的效率很高。
• 然后马上调用fsync函数，同步脏页进磁盘上。

1.6.3 自适应哈希索引

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index，AHI)。也就是说存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

建立哈希索引的要求如下：

• 使用同样的查询条件访问了 100 次。
• 或者是页通过该查询条件访问了 N 次，N = 页中记录 / 16；

1.6.4 异步IO

为了提高磁盘操作性能，当前的数据库系统都采用异步IO (Asynchronous IO,AIO）的方式来处理磁盘操作。

所谓异步IO就是，应用进程给操作系统发送一个请求后立刻返回，告诉操作系统需要什么数据，放在哪里，操作系统将数据准备好，并且从内核缓冲区直接映射到内存中，并给应用进程发送一个完成信号，则数据就可以使用。

数据库使用异步IO主要有两点优势：

• 在需要访问多个索引页的情况下，如果是同步IO，则需要发送一个IO请求，阻塞读取之后再发送一个IO请求，而异步IO则可以以例如流水线的方式，发出多个IO请求，等待IO操作的处理。
• 使用AIO连续发送多个IO请求的情况下，如果有连续的页面，则AIO底层可以优化为一个IO请求。

1.6.5 刷新邻接页

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

1.7 启动、关闭与恢复

在InnoDB 存储引擎关闭以及恢复的时候，可以通过一些参数，来设置一些策略。

参数 innodb_fast_ shutdown 影响着关闭行为：值可以为0、1 或者 2

• 0 表示完成所有 full purge （无用Undo 页的删除）和 merge insert buffer （插入缓冲的合并），以及将脏页写回磁盘。
• 1 表示只需要将脏页写回磁盘，是默认设置。
• 2 表示所有都不做，只是将内容写入日志。

参数 innodb_force_recovery 可以设置InnoDB引擎的恢复策略，因为有时通过事务回滚等操作，可能恢复的时间更长。

• 1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的 corrupt 页。
• 2(SRV_FORCE_NO_BACKGROUND):阻止 Master Thread线程的运行。
• 3(SRV_FORCE_NO_TRX_UNDO):不进行事务的回滚操作。
• 4(SRV_FORCE_NO_IBUF_MERGE):不进行插入缓冲的合并操作。
• 5(SRV_FORCE_NO_UNDO_LoG_SCAN):不查看撤销日志（Undo Log)，InnoDB存储引擎会将未提交的事务视为已提交。
• 6(SRv_FORCE_NO_LOG_REDO):不进行前滚的操作。

2. 文件

2.1 参数文件

参数文件用于 MySQL 启动时的初始化参数，这些参数设置了相关的一些特性。

参数分为两类：

• 动态参数：指在运行期间可以修改的参数。
• 静态参数：指在运行期间不可修改的参数。

2.2 日志文件

日志文件记录了影响 MySQL 数据库的各种类型活动，常见的包括以下几类：

• 错误日志（error log）
• 二进制日志（binlog）
• 慢查询日志（slow query log）
• 查询日志（log）

2.2.1 错误日志

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录一些警告信息或正确的信息。

可通过该命令查看错误文件的位置信息：

2.2.2 慢查询日志

慢查询日志（slow log）可以定位存在问题的SQL语句，从而进行SQL 层面的优化，例如设置一个阈值，将查询时间超过该值的SQL语句都记录到慢查询日志文件中。

对于慢查询日志，主要先要了解以下参数：

• slow_query_log：是否开启慢查询日志，默认OFF，开启则设置为 ON。
• slow_query_log_file：慢查询日志文件存储位置。
• log_queries_not_using_indexes ：是否把没有使用到索引的SQL记录到日志中，默认OFF，开启则设置为 ON。
• long_query_time：超过多少微秒的查询才会记录到日志中，5.1版本之后，单位采用微妙。
• log_output：慢查询日志输出位置，默认为FILE 文件的形式，可以改成输出到 mysql 库下的slow_log表，修改该参数为 TABLE。

开启慢查询日志的方法也主要分为两种：

• 执行语句设置（这个方法重启MySQL后会失效）

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '文件路径（绝对路径）';
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'TABLE';

• 写入配置文件（重启MySQL 不会失效，修改后需要重启）

slow_query_log="ON"
slow_query_log_file="文件路径（绝对路径）"
log_queries_not_using_indexes="ON"
long_query_time=1

例如使用执行一条SQL没有用到索引：

则可以在slow_log 表中显示：

2.2.3 查询日志

查询日志记录了所有对数据库的请求的信息，无论这些请求是否得到了正确的执行。

通过设置 general_log 参数开启，并且通过 log_output 参数可以设置在 mysql库下的 general_log 表中显示。

2.2.4 二进制日志

二进制日志(binary log）记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT 和SHOW这类操作，因为这类操作对数据本身并没有修改。

二进制文件主要作用：

• 恢复（recovery）：某些数据的恢复需要二进制文件。
• 复制（replication）：原理和恢复类似，通过复制和执行二进制日志使一台远程的MySQL 数据库（从）和一台主MySQL 数据库进行同步。
• 审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

二进制日志通过配置 log-bin 参数为ON则开启

以下配置文件参数影响二进制日志记录的信息和行为：

• max_binlog _size：单个二进制文件的最大值，超过则生成下一个文件。
• binlog_cache_size：在事务中未提交的二进制日志会被记录到二进制日志缓存中，提交后会写入到真的二进制文件。
• sync_binlog：该参数定义了什么时候将二进制日志写入磁盘的时机。
• binlog-do-db：表示需要写入哪些库的日志。
• binlog-ignore-db：表示需要忽略哪些库的日志。
• log-slave-update：一般情况下，如果某个数据库复制的是一个从机，则不会从他那取得二进制日志并写入到自己的日志中，如果需要写入，则配置该参数。
• binlog _format：规定了二进制日志的存储格式。主要有三种：
  • STATEMENT：存储SQL 语句，存储空间小，但是对于类似rand()等确定操作，会造成主从数据不一致。
  • ROW：存储表的行更改情况，空间较大，不可读取。
  • MIXED：两种混用。

二进制日志通过 MySQL 提供的 mysqlbinlog 查看。

还有一些与存储引擎无关的文件，包括 ① 套接字文件：参数UNIX系统下本地连接MySQL 的套接字方式；② pid 文件：存储了进程ID；③ 表结构定义文件：以frm为结尾，记录了表的结构定义。

2.4 InnoDB 存储引擎文件

2.4.1 表空间文件

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

设置 innodb_data_file_path 参数后，所有基于InnoDB存储引擎的表的数据都会记录到该共享表空间中。

设置了参数 innodb_file_per_table，则用户可以将每个基于InnoDB存储引擎的表产生一个独立表空间。后缀为ibd。

2.4.2 重做日志文件

重做日志（redo log）记录了对于InnoDB存储引擎的事务日志。

每个InnoDB存储引擎至少有一个重做日志文件组，每个组下又至少有两个重做日志文件。以循环的方式写入，例如先写文件1，文件1写满之后，写文件2，文件2满了之后，又重新写文件1，因为文件1的内容会被覆盖，所以会有一个 capacity 阈值，检测 check point 是否已经将该部分内容写回到磁盘，如果没有，则从脏页列表中选择部分脏页写回磁盘。

redo log 和 bin log 的区别？

1. 服务范围不同：binlog 会记录各种存储引擎的事务日志，而 redolog 只关乎 InnoDB 引擎本身。
2. 记录内容不同：binlog 有STATEMENT 和 ROW 格式，分别记录的是SQL语句和行的修改情况。而 redolog 则记录的是对于页更改的物理情况。
3. 写入时间不同：对于一个事务，binlog 会在提交时一次写完，而 redolog，在事务进行时就不断写入。

重做日志结构：

• redo_log_type：占用Ⅰ字节，表示重做日志的类型；
• space：表示表空间的ID，但采用压缩的方式，因此占用的空间可能小于4字节；
• page_no：表示页的偏移量，同样采用压缩的方式
• redo_log_body：表示每个重做日志的数据部分，恢复时需要调用相应的函数进行解析

从重做日志缓冲往磁盘写入时，是按512个字节，也就是一个扇区的大小进行写人。因为扇区是写入的最小单位，因此可以保证写入必定是成功的。

从重做日志缓冲写入磁盘的条件一般包括两个：

• Master Thread 的定时循环操作。
• 由参数 innodb_flush _log _at_trx_commit 控制，表示在提交(commit）操作时，对缓冲进行处理。
  • 有三个可用参数，0、1 和 2；
  • 0 表示事务提交不处理缓冲区，而等待每秒一次的主线程循环。
  • 1 表示事务提交就同步的将重做日志缓冲区写到磁盘。为了保证事务的持久性，就必须采用这种策略。
  • 2 表示事务提交通过异步的方式将重做日志缓冲区写到磁盘。并不能保证该事件一定发生。

3. InnoDB 逻辑存储结构

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

3.1 表空间

表空间是InnoDB 存储引擎逻辑结构的最高层，所有数据都存放在表空间中。

如果启用了innodb_file_per_table 的参数，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲 Bitmap页，其他类的数据，如回滚(undo）信息，插人缓冲索引页、系统事务信息，二次写缓冲(Double write buffer）等还是存放在原来的共享表空间内。

3.2 段

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

3.3 区

区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证页的连续性，InnoDB存储引擎每次从磁盘一次申请4-5个区。

在默认情况下，InnoDB存储引擎页的大小为16KB，即一个区中一-共有64个连续的页。

3.4 页

同大多数数据库一样，InnoDB有页(Page）的概念（也可以称为块)，页是InnoDB磁盘管理的最小单位。

在InnoDB存储引擎中，默认每个页的大小为16KB。

从InnoDB 1.2.x版本开始，可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。

常见的页包括：

• 数据页（B-tree Node)
• undo页(undo Log Page)
• 系统页(System Page)
• 事务数据页(Transaction system Page）
• 插入缓冲位图页(Insert Buffer Bitmap）
• 插入缓冲空闲列表页（Insert Buffer Free List)
• 未压缩的二进制大对象页(Uncompressed BLOB Page)

3.5 行

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