MySQL(InnoDB剖析):05---InnoDB体系架构（后台线程、内存池、文件）

①Master Thread（主线程）

• Master Thread是一个非常 核心的后台线程 ，主要负责将缓冲池中的数据 异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERT BUFFER）、UNDO页的回收 等
• 在后面会有专门的一篇文章介绍Master Thread的工作方式

②IO Thread（IO线程）

• 在InnoDB中 大量使用了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_threds参数 ，而是分别使用innodb_read_io_threads和innodb_write_io_threads参数进行设置，如：

-- 查看InnoDB存储引擎的版本
show variables like 'innodb_version'\G

-- 查看innodb_read_io_threads和innodb_write_io_threads的数量
show variables like 'innodb_%io_threads'\G

• 通过下面的命令查看InnoDB中的IO Thread，可以看到：
  • IO Thread 0为insert buffer thread
  • IO Thread 1为log thread
  • 之后都是read thread和write thread，并且多线程的ID总是小于写线程

show engine innodb status\G;

③Purge Thread（清洗线程）

• 事务被提交后，其所使用的undolog可能不再需要，因此需要Purge Thread 来回收已经使用并分配的undo页
• 在InnoDB 1.1版本之前，purge操作仅在InnoDB存储引擎的Master Thread中来完成
• 从InnoDB 1.1版本之后， purge操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作 ，从而提高CPU的利用率以及提升存储引擎的性能
• 用户可以在数据库的 配置文件中添加如下命令来启用独立的Purge Thread：

[mysqld]
innodb_purge_threads=1

• 在InnoDB 1.1版本中 ，即使将innodb_purge_threads设为大于1，InnoDB存储引擎启动时也会将其设为1，并在错误文件中出现如下类似的提示：

• 从InnoDB 1.2版本开始，InnoDB支持多个Purge Thread，这样做的 目的是为了进一步加快undo页的回收 。同时由于Purge Thread需要离散地读取undo页，这样也能 更进一步利用磁盘的随机读取性能
• 下面可以 查看purge_threads的数量 ，由于配置文件没有设置，所以显示为0：

-- 查看系统及版本
select version()\G;

-- 查看purge_threads的数量
show variables like 'innodb_purge_threads'\G;

④Page Cleaner Thread

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

①缓冲池

• InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因此可将其视为基于磁盘的数据库系统。在数据库系统中， 由于CPU速度和磁盘之间的鸿沟 ，基于磁盘的数据库系统通常 使用缓冲池技术来提高数据库的整体性能
• 缓冲池的工作原理： 缓冲池简单来说就是一块内存区域，通过内存的速度来 弥补磁盘速度较慢 对数据库性能的影响。
  • ①读取操作： 在数据库中进行 读取页 的操作， 首先将从磁盘读到的页存放在缓冲池中 ，这个过程称为将页“FIX”在缓冲池中。下一次再读相同的页时， 首先判断该页是否在缓冲池中 。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页；否则，读取磁盘上的页
  • ②写入/修改操作： 对于数据库中页的修改操作，则 首先修改在缓冲池中的页 ，然后用再以一定的频率刷新到磁盘上。这里需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是 通过一种称为“Checkpoint”的机制刷新回磁盘 。同样，这也是为了提高数据库的整体性能
• 缓冲池的大小： 缓冲池的大小直接影响着数据库的整体性能。由于32位操作系统的限制，在该系统下最多将该值设置为3G。此外用户可以打开操作系统的PAE选项来获得32位操作系统下最大64GB内存的支持。随着内存技术的不断成熟，其成本也在不断下降。单条8G的内存变得非常普遍，而PC服务器已经能支持512GB的内存。因此为了让数据库使用更多的内存，强烈建议数据库服务器都采用64位操作系统
• 设置/查看缓冲池的大小：
  • 对于InnoDB存储引擎，其缓冲池的配置通过参数“innodb_buffer_pool_size”来设置
  • 也可以通过命令查看参数“innodb_buffer_pool_size”的大小

show variables like 'innodb_buffer_pool_size'\G;

• 缓冲池中的数据页类型：
  • 缓冲池的数据页类型有： 索引页、数据页、undo页、插入缓冲（insert buffer）、自适应哈希索引（adaptive hash index）、InnoDB存储的锁信息、数据字典信息 等

• 多个缓冲池实例：
  • 从InnoDB 1.0.x版本开始， 允许有多个缓冲池实例 。每个页根据哈希值平均分配到不同缓冲池实例中。这样做的好处是减少数据库内部的资源竞争，增加数据库的并发处理能力
  • 可以通过参数“innodb_buffer_pool_instances”来进行设置，该值默认为1。当然可以在配置文件中修改该参数来得到多个缓冲池实例

show variables like 'innodb_buffer_pool_instances'\G;

• 查看缓冲池实例的详细信息 （因为此数据库中只有一个缓冲池，所以下面显示的是单个缓冲池的信息） ：

show engine innodb status\G;

• 通过“information_schema”数据库中的“INNODB_BUFFER_POLL_STATS”来 查看缓冲的状态 （同理，下面只显示了一个缓冲池的信息，pool_id为0） ：

select pool_id,pool_size,free_buffers,database_pages
from information_schema.innodb_buffer_pool_stats\G;

②LRU List、Free List、Flush List

• 缓冲池是一个很大的内存区域，其中存放各种类型的页，MySQL通过这3中List对页进行管理

2.1 LRU list

• LRU List用来 管理缓冲池中已经读取/使用到的页
• 数据库中的缓冲池是 通过LRU（最近最少使用）算法 来进行管理。即最频繁使用的页放在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将释放LRU列表中尾端的页
• midpoint位置：
  • 在InnoDB存储引擎中，缓冲池中页的大小默认为16KB，同样使用LRU算法对缓冲池进行管理，稍有不同的是 InnoDB存储引擎对传统的LRU算法做了一些优化 。在InnoDB的存储引擎中，LRU列表中加入了“midpoint”位置。新读取到的页，虽然是最新访问的页，但 并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint位置
  • 这个算法在InnoDB存储引擎下成为“midpoint insertion strategy”。在 默认配置下，该位置在LRU列表长度的5/8处 。midpoint位置可 由参数“innodb_old_blocks_pct”控制
  • 把midpoint 之后的列表成为old列表 ， 之前的列表称为new列表 。可以简单地理解为new列表中的页都是最为活跃的热点数据
  • 如通过下面命令查看到innodb_old_blocks_pct的值为37，表示新读取的页插入到LRU列表尾端的37%地位置（差不多3/8的位置）

show variables like 'innodb_old_blocks_pct'\G;

• 为什么不使用传统的LRU算法： 不使用传统的LRU算法把读取的页放入到LRU列表的首部，是因为若直接将读取到的页放入LRU的首部，那么 某些SQL操作可能会使缓冲池中的页被刷新出去 ，从而影响缓冲池的效率。常见的这类操作为索引或数据的操作操作。这类操作需要访问表中的许多页，甚至是全部的页，而这些页通常来说又仅在这次查询操作中需要，并不是活跃的热点数据。如果页被放入LRU列表的首部，那么非常可能将所需要的热点数据页从LRU列表中移除，而在下一次需要读取该页时，InnoDB存储引擎需要再次访问磁盘
• “innodb_old_blocks_time”参数： InnoDB存储引擎引入了此参数来进一步管理LRU列表，该参数用于表示 页读取到mid位置后需要等待多久才会被加入到LRU列表的热端 。因此当需要执行上述所说的SQL操作时，可以通过下面的方法尽可能使用LRU列表中热点数据不被刷出

show variables like 'innodb_old_blocks_time'\G;

-- 例如，设置该参数的值为1000
set global innodb_old_blocks_time=1000;

• 通过“innodb_buffer_page_lru”表 查看每个LRU列表中每个页的具体信息 。只需要查看LRU列表中SPACE为1的表的页类型：

select table_name,space,page_number,page_type
from information_schema.innodb_buffer_page_lru where space=1;

压缩页的功能（unzip_LRU列表）

• InnoDB从 1.0.x版本开始 支持压缩页 的功能，即将原本16KB的页压缩为1KB、2KB、4KB、8KB。由于页的大小发生了变化，LRU列表也有了些许的改变。对于非16KB的页，是 通过unzip_LRU列表进 行管理的
• 通过下面的命令可以观察到：
  • LRU列表一共有302个页
  • 而unzip_LRU列表中有0个页
  • 这里需要注意的是，LRU中的页包含了unzip_LRU列表中的页

show engine innodb status\G;

• unzip_LRU如何分配内存： 对于压缩页的表，每个表的压缩比率可能各不相同。可能存在有的表页大小为8KB，有的表页大小为2KB的情况。 以从缓冲池中申请页为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列表中
• 查看unzip_LRU列表中的页： 也是通过innodb_buffer_page_lru表

select table_name,space,page_number,compressed_size
from information_schema.innodb_buffer_page_lru where compressed_size<>0;

2.2 Free List

• 上面介绍的“LRU List”用来管理已经读取的页，但 当数据库刚启动时 ，LRU列表是空的，没有使用任何页，这时 空闲页都存放在Free列表中
• 工作原理： 当需要从缓冲池中分页时，首先从Free列表查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中；否则根据LRU算法，淘汰LRU列表末尾的页，将释放的该内存空间分配给新的页
• 当 页从LRU列表的old部分加入到new部分时 ，称此时发生的操作为“page made young”，而因为“innodb_old_blocks_time”的设置而 导致页没有从old部分移动到new部分的操作 称为“page not made young”
• 通过以下命令 来观察LRU列表以及Free列表的使用情况和运行状态：
  • Buffer pool size： 缓冲池中共有8192个页，即缓冲池大小为8192*16K
  • Free buffers： 表示Free List中页的数量
  • Database pages： 表示LRU列表中页的数量
  • Pages made young： 表示LRU列表中页移动到前端的次数，因为该服务器在运行阶段没有改变innodb_old_blocks_time的值，因此not young为0。youngs/s和non-youngs/s分别表示每秒这两类操作的次数
  • Buffer pool hit rate： 表示缓冲池的命中率。此处为98.3%。通常该值不应该小于95%，若该值小于95%，用户需要观察是否是由于全表扫描引起的LRU列表被污染的问题

show engine innodb status\G;

• 从InnoDB 1.2版本开始，还可以通过表INNODB_BUFFER_POOL_STATS 来观察缓冲池的运行状态，如：

select pool_id,hit_rate,pages_made_young,pages_not_made_young
from information_schema.innodb_buffer_pool_stats\G;

2.3 Flush List

• Flush List的作用： 在LRU列表中的页被修改后，称该页为脏页（dirty page），即缓冲池中的页和磁盘上的数据产生了不一致。这时数据库会通过“CHECKPOINT”机制 将脏页刷新回磁盘 ，而 Flush列表中的页即为脏页列表
• 需要注意的是， 脏页既存在于LRU列表中，也存在与Flush列表中 。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘， 两者互不影响
• 查看Flush列表： 与上面一样也是通过下面的命令查看，箭头所指的为Flush List中页的数量为0

show engine innodb status\G;

• 通过表格查看Flush List的信息： 因为Flush List也存在与LRU列表中，所以也可以通过查看innpdb_buffer_page_lru表来查看，但是需要加上“OLDEST_MODIFICATION大于0”的查询条件

select table_name,space,page_number,page_type
from information_schema.innodb_buffer_page_lru
where oldest_modification>0;

③重做日志缓冲

• InnoDB的内存区域还有重做日志缓冲（redo log buffer）。InnoDB存储引擎首 先将重做日志信息放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件
• 重做日志缓冲大小：
  • 重做日志缓冲一般不需要设置很大，因为一般情况下每一秒会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可
  • 该值可由参数innodb_log_buffer_size控制，默认为8MB

show variables like 'innodb_log_buffer_size'\G;

• 重做日志缓冲自动刷新的三种情景： 通常情况下，8MB的重做日志缓冲池足以满足决大部分的应用，因为重做日志在下列三种情况会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中
  • 1.Master Thread每一秒将重做日志缓冲刷新到重做日志文件
  • 2.每个事务提交后会将重做日志缓冲刷新到重做日志文件
  • 3.当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件

④额外的内存池

• 额外的内存池通常被DBA忽略，它们认为该值并不十分重要，事实恰恰相反，该值同样十分重要
• 在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆（heap）的方式进行的
• 在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。例如，分配了缓冲池，但是每个缓冲池中的帧缓冲还有对应的缓冲控制对象，这些对象记录了一些诸如LRU、锁、等待等信息，而这个对象的内存需要从额外内存池中申请。因此，在申请了很大的InnoDB缓冲池时，也应该考虑相应地增加这个值