深入解析MySQL innodb引擎

innodb是事物安全的MySQL存储引擎 是oltp应用中核心表的首选存储引擎

MySQL第一个支持事物的存储引擎是BDB

MySQL第一个完整支持ACID事物是innodb

innodb的特点 行锁设计 支持MVCC 支持外键 提供一致性非锁定读 同时被设计用来最有效的利用以及使用内存和cpu

innodb 体系架构

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

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

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

3 重做日志(redo log)缓冲

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

后台线程

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

1 Master Thread

MT 是一个非常核心的后台线程 住一套负责将缓冲池中的数据一部刷新到磁盘 保证数据的一致性 包括脏页的刷新 合并插入缓冲(insert buffer) undo页的回收

2 io thread

在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_file_io_threads参数 而是分别使用 innodb_read_io_threads和innodb_write_io_threads

可以通过命令show engine innodb status来观察innodb中的IO Thread

show engine innodb status\G;

3 Purge Thread

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

在innodb 1.1版本之前 purge操作仅在innodb存储引擎的master thread 中完成

而从innodb 1.1版本开始 purge操作可以独立到单独的线程中进行 以此来减轻master thread 的工作 从而提高cpu的使用率以及提升存储引擎的性能

用户可以再MySQL数据库的配置文件中添加如下命令来启用独立的Purge Thread

[mysql]

innodb_purge_threads=1

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

在innodb 1.2版本开始 innodb支持多个purge thread 这样做的目的是为了进一步加快undo页的回收这样也能更进一步利用磁盘的随机读取性能 用户可以设置4个purge thread

4 page cleaner thread

innodb 1.2.x 引入 page cleaner thread

作用:将之前版本中臧晔的刷新操作都放入到单独的线程中完成 减轻原master thread的工作对于用户查询线程的阻塞

内存

1 缓冲池

innodb存储引擎给予磁盘存储的 并将其中的激励按照页的方式进行管理 一次可将其视为给予磁盘的数据系统(disk-base database) 由于cpu跟磁盘速度之间的鸿沟 给予磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能

缓冲池就是一块内存区域 通过内存的速度来弥补磁盘的速度较慢对数据库性能的影响 在数据库中进行读取页的操作 首先将从磁盘读到页存放在缓冲池中 这个过程称为将页FIX 在缓冲池中 下一次在读相同的页时 首先判断页是否在缓冲池中 若在缓冲池中 该页在缓冲池中被命中 直接读取该页 否则读取 磁盘上的页

对于数据库中页的修改操作 则首先修改在缓冲池中的页 然后再以一定的频率刷新到磁盘上 这里需要注意的是 页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发 而是通过一种称为checkpoint的机制刷新回磁盘 同样 这也是为了提高数据库的整体性能

缓冲池的大小直接影响着数据库的整体性能

缓冲池缓存的数据页的类型有:索引页 数据也 undo页 插入缓冲(insert buffer) 自适应哈希索引(adaptive hash index) innodb存储锁信息(lock info) 数据字典信息(data dictionary)等 缓冲池不只是缓存索引页和数据页 他们只是占缓冲池很大的一部分而已

innodb 1.0.x以后 允许多个缓冲池实例 每页根据哈希值平均分配到不同缓冲池里 这样做的好处是减少数据库内部的资源竞争 增加数据库的并发处理能力

show variables likes 'innodb_buffer_pool_instances'\G;

将这个参数改为大于1 的值 就可以得到多个缓冲池实例 再通过命令show engine innodb status\G;观察修改后系统的缓冲池状态

MySQL 5.6 可以通过information_schema架构下的表 innodb_buffer_pool_status来观察缓冲的状态

2 LRU List Free List Flush List

数据库中的缓冲池是通过LRU(latest recent used 最近最少使用)算法来进行管理的 最频繁使用的页是在LRU列表的前端 而最少使用的页在LRU列表的尾端 即使用频发的页放在LRU列表的前端 而最少使用的页在LRU列表的尾端 当缓冲池不能存放新读取到的页时 将首先释放LRU列表中尾端的页

在innodb存储引擎中 缓冲池中页的大小默认为16KB 同样使用LRU算法对缓冲池进行管理 稍有不同的是innodb存储引起对传统LRU算法做了一些优化在innodb的存储引擎中 LRU列表中还加入了midpoint位置 即新读取的页 并不会直接放到LRU连的首部 而是放到midpoint位置 这个算法在innodb存储引擎下称为 midpoint insertion strategy 默认该位置在LRU列表长度的5/8处

参数:innodb_old_blocks_pct控制

show variables like 'innodb_old_blocks_pct'\G;

采用midpoint的原因

某些sql操作可能会使缓冲池的页被刷新出来 从而影响缓冲池的效率 常见的这类操作为索引或数据的扫描操作 这类操作需要访问表中的许多页甚至全部 而这些页通常来说又仅在这次查询操作中需要 并不是活跃的热点数据 如果页被放到LRU首部 那么非常可能将所需要的热点数据页从LRU列表一处 而在下一次需要读取该页时 innodb存储引擎需要再次访问磁盘

innodb_old_blocks_time

用于表示页读取到mid未支护需要等待多久才会被加入到LRU列表的热端 因此当需要执行上述所说的sql操作时 可以通过下面方法使LRU列表中热点数据不被刷出

set global innodb_old_blocks_time=1000;

如果用户预估自己活跃的热点数据不止百分之63 那么执行SQL语句前 可以修改innodb_old_blocks_pct 减少热点页被刷出来的概率

LRU列表管理已经读取的页 但数据库刚启动的时候 LRU列表是空的 即没有任何的页 这时页都存放在Free列表中 当需要从缓冲池中分页时 首先从Free列表中查找是否有可用的空闲页 若有则将该页从Free列表中删除 放入到LRU列表中 否则 根据LRU算法 淘汰LRU列表末尾的页将该内存空间分配给新的页 当页从LRU列表的old部分 加入到new部分时 称此时发生的操作为page made young 而因为innodb_old_blocks_time的设置而导致页没有从old部分移动到new部分的操作称为page noe made young可以通过命令 show engine innodb status来观察LRU列表及Free列表的使用情况和运行状态 这个状态显示的不是当前状态 而是过去某个时间范围内 innodb存储引擎的状态 per second averages calculated from the last 24 seconds 表示过去的24小时

page made young 显示了LRU列表中页移动到前端的次数 因为该服务器在运行阶段没有改变innodb_old_blocks_time的值 因此not young为0 youngs/s non-youngs表示美妙这两类操作的次数

buffer pool hit rate 表示缓冲池的命中率 值越接近1或等于1 说明缓冲池运行状态非常好 一般不应该小于百分之九五 若发生buffer pool hit rate的值小于百分之九五的情况 需要检查是否由于全表扫描引起的LRU列表被污染的问题

innodb_buffer_pool_stats:观察缓冲池的运行状态 information_schema中的表

innodb_buffer_page_lru:观察lru列表中每个页的具体信息 information_schema中的表

innodb1.0.x 开始支持压缩页的功能 可以讲16kb的页压缩为1kb 2kb 4kb 和8kb 但是由于压缩页的大小发生变化 lru列表也有了些许的改变 对于非16kb的页通过unzip_LRU列表进行管理 通过show engine innodbdb status可以观察

这里 lru len 包含unzip_lru len 列

对于不同压缩大小的页管理方式:在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列表中

同样可以通过information_schema架构下的表innodb_buffer_page_lru来观察unzip_LRU列表中的页

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

3 重做日志缓存

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

参数:innodb_log_buffer_size 默认8MB

将重做日志缓冲的内容刷新到外部磁盘的重做日志文件的情况:

1 master thread 每一秒将重做日志缓冲到重做日志文件

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

3 当重做日志缓冲池剩余空间小于二分之一时 重做日志缓冲刷新到重做日志文件

4 额外的内存池

在innodb存储引擎 对内存管理是通过一种称为内存堆(heap)的方式进行的

4 checkpoint技术

write ahead log 日志先写策略 避免数据丢失通过重做日志来完成数据的恢复

宕机通过重做日志恢复的条件

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

随着数据库的日积月累的增大 导致数据增到 内存不足以缓存所有数据 所以对生产环境应用中的数据库是很难保证的

2 重做日志可以无线增大

重做日志无线增大 会对成本要求太高 页不便于运维 而且 DBA 或SA 不知道什么时候重做日志是否已经接近于磁盘可使用的空间的阀值 而且让存储设备支持颗动态扩展页是需要一定的技巧和设备支持

3 宕机后的恢复时间 时间越久 恢复的代价就会越大

checkpoint的优点

1 缩短数据库的恢复时间

2 缓冲池不够用时 将脏页刷新到磁盘

3 重做日志不可用 刷新脏页

这样 数据库宕机的时候 就不需要所有的重做日志 因为checkpoint之前的页都已经刷新到磁盘 所以这时候只需要 checkpoint之后的重做日志进行恢复 这样就大大缩短了恢复时间

当缓冲池不够用的时候 就会根据 LRU算法会溢出最近最少使用的页 若此页为脏页 那么需要强制执行checkpoint 江脏页页就是页的新版本刷回磁盘

重做日志的循环使用

对于innodb存储引擎 是通过LSN（log sequence number）来标记版本 二LSN是8字节的数字 单位是字节 每个页都有lsn 重做日志页有LSN checkpoint页有LSN 可以根据 show engine innodb status来观察

checkpoint作用：将缓冲池中的脏页刷会磁盘 但是每次刷多少页到磁盘 从哪取脏页 什么时候触发checkpoint都不同

checkpoint类型

1 sharp checkpoint

作用：数据库关闭时将所有脏页都刷新到磁盘 这是默认工作方式

参数：innodb_fast_shutdown=1

2 fuzzy checkpoint

如果数据库正常运行的时候使用sharp checkpoint 那么数据库的可能性就会收到很大影响 所以在innodb存储引擎内部使用 fuzzy checkpoint进行页的刷新 （只刷新一部分脏页 而不是刷新所有的脏页回磁盘）

发生 fuzzy checkpoint情况

1 master threadcheckpoint

差不多以每秒或者每10秒的速度从缓冲池脏页列表刷新一定比例的页回磁盘 （异步 查询线程不会阻塞）

2 flush_lru_list checkpoint

innodb 1.1x版本前 需要检查LRU列表中是否有足够的（100）可用空间操作发生在用户查询线程中 会阻塞查询 没有 innodb存储引擎就会将lru列表尾端的页溢出 如果这些页里面有脏页 就需要checkpoint

innodb 1.2.x版本开始 这个检查被放在了一个单独的page cleaner 线程中进行 并且用户可以通过innod_lruscan depth控制列表中可用页的数量 默认为1024

3 async/sync flush checkpoint

当重做日志不可用的情况下 这时需要强制将一些页刷新会磁盘 而此时脏页是从脏页列表中选取 若将已经写入到重做日志的LSN 记为 redo_lsn 将已经刷新会磁盘最新页的lsn结尾checkpoint_lsn

checkpoint_age = redo_lsn - checkpoint_lsn

async_water_mark = 75% total_redo_log_file_size

async/sync flush checkpoint 是为了保证重做日志的循环使用的可用性

innodb 1.2x之前 async flush checkpoint会阻塞发现问题的用户查询线程 sync flush checkpoint会阻塞用户查询线程 并等待脏页的刷新完成

MySQL官方版本 并不能查看刷新也是从flush 列表还是LRU列表中进行checkpoint的 也不知道因为重做日志而产生的async/sync flush的次数 但是 innoSQL版本可以通过 show engine innodb status来观察

4 dirty page too much checkpoint

脏页太多导致innodb存储引擎强制进行checkpoint 其目的总的来说还是为了保证缓冲池中有足够多的页 主要是为了保证缓冲池中有足够可用的页

参数
innodb_max_dirty_pages_pct

5 master thread 工作方式

innodb 1.0.x 之前的master thread

master thread 具有最高的线程优先级别 其内部由多个循环(loop)组成 主循环 (loop) 后台循环(backgroup loop) 刷新循环(flush loop) 暂停循环(suspend loop) master thread就会在这些循环中切换

loop为主循环

loop循环通过thread sleep来实现 在负载很大的情况下可能会有延迟

每秒一次的操作包括

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

2 合并插入缓冲(可能)

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

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

再大的事务提交的时间很短的原因:innodb存储引擎仍然每秒会将重做日志缓冲中的内容刷新到重做日志文件

合并插入缓冲 也不是每秒都会发生的 innodb存储引擎会判断当前一秒内发生的io次数是否小于5次 如果小于5次 innodb人为当前io压力很小 可以执行合并插入缓冲的操作

buf_get_modified_ratio_pct跟百分之90的
innodb_max_dirty_pages_pct相比较 超过 才会人为需要进行磁盘同步操作 将100个脏页写入磁盘

每10秒的操作

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

2 合并之多5个插入缓冲(总是)

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

4 删除无用的undo页(总是)

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

过程

1 判断10秒之内的磁盘的IO操作是否小于200

2 小于 innodb认为有足够的IO能力 将100的脏页刷新到磁盘

3 innodb存储引擎会合并插入缓冲 这次合并插入缓冲总会在这个剪短进行

4 innodb存储引擎会再进行一次奖入职缓冲刷新到磁盘的操作 这和没秒一次发生的操作一样

5 innodb进一步执行full purge 即删除无用的undo页 对表update delete这类操作时操作时 原先的行被标记为删除 但是因为一致性读(consistent read)的关系 需要保留这些行版本的信息 但是在full purge过程中 innodb存储引擎会判断当前事物系统已被删除的行是否可以删除 比如有时候还有查询操作需要读取之前版本的undo信息 如果可以删除 innodb会立即将其删除

6
buf_get_modified_ratio_pct >70% 则刷新100个脏页到磁盘 <70%刷新10%的脏页到磁盘

background loop 当当前没有用户活跃(数据库空闲)或者数据库关闭(shutdown)

进行的操作

1 删除无用的undo(总是)

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

3 跳回到主循环(总是)

4 不断刷新100个页知道符合条件(可能 跳转到flush loop中完成)

跳转到flush loop 后 如果也没什么事情做了 innodb存储引擎会切换到suspend loop 将master thread挂起 等待事件的发生 若用户启用了(enable)innodb 却没有使用任何innodb的表 那么master thread 一直挂起状态

innodb 1.2.x之前的master thread

参数 innodb_io_capacity 用来表示磁盘io的吞吐量 默认是200 对于刷新到磁盘页的数量 按照innodb_io_capacity的百分比控制

1 在合并插入缓冲时 合并插入缓冲的数量为 innodb_io_capacity

2 在从缓冲区刷新脏页时 刷新脏页的数量为innodb_io_capacity

用来解决写入密集的应用程序中 产生大于20额插入缓冲的情况 master thread忙不来的问题

参数
innodb_max_dirty_pages_pct