MySql优化(三)详细解读InnoDB存储引擎
写在前面:
- 我热爱技术,热爱分享,热爱生活, 我始终相信:技术是开源的,知识是共享的!
- 博客里面的内容大部分均为原创,是自己日常的学习记录和总结,便于自己在后面的时间里回顾,当然也是希望可以分享自己的知识。目前的内容几乎是基础知识和技术入门,如果你觉得还可以的话不妨关注一下,我们共同进步!
- 个人除了分享博客之外,也喜欢看书,写一点日常杂文和心情分享,如果你感兴趣,也可以关注关注!
- 微信公众号:傲骄鹿先生
目录
InnoDB独立表空间,支持MVCC,行锁设计,提供一致性非锁定读,支持外键,插入缓冲,二次写,自适应哈希索引,预读使用聚集的方式存储数据,每张表的存储都是按主键顺序存放。
InnoDB是事物安全的MySQL存储引擎,设计上采用了类似Oracle数据库的架构。通常来说,InnoDB存储引擎是OLTP应用中核心表的首选存储引擎,也正是因为其存在,才使得MySQL数据库变得更加有魅力。
一、InnoDB体系架构
1、后台进程:
1.1 Master Thread:核心线程,负责缓冲池的数据异步入盘,包括脏页刷新、合并插入缓冲、undo页回收等。
1.2 IO Thread:包括read thread 和writer thread,使用show variables like '%innodb_%io_thread%';查看。
1.3 Purge Thread:回收事务提交后不再需要的undo log,通过show variables like '%innodb_purge_threads%'; 查看。
1.4 Page clear thread:脏页的刷新操作,从master thread分离出来。
2、InnoDB存储引擎有多个内存块,可以认为这些内存块组成了一个大的内存池,负责如下的工作:
- 维护所有进程/线程需要使用的多个内部数据结构
- 缓存磁盘上的数据,方便快速地读取,同时对磁盘文件数据修改之前在这里缓存
- 重做日志(redo log)缓存
InnoDB内存池主要有以下部分:
- 缓冲池
- 重做日志缓冲
- 额外内存池
以下主要从内存和线程的角度分析InnoDB的架构。
二、内存
2.1 缓冲池
从上图来看,主要包括数据页、索引页、undo页、insert buffer、adaptive hash index、数据字典等,其中索引页和数据页占用多数内存。
配置innodb_pool_buffer_instances将缓冲池分割为多个实例,减少内部竞争(比如锁)。
InnoDB是基于磁盘存储的,并将其中的记录按照页的方式进行管理。而缓冲池就是一块内存区域,主要缓冲数据页和索引页。
InnoDB中对页的读取操作,首先判断该页是否在缓冲池中,若在,直接读取该页,若不在则从磁盘读取页数据,并存放在缓冲池中。
对页的修改操作,首先修改在缓冲池中的页,再以一定的频率(Checkpoint机制)刷新到磁盘。参数:innodb_buffer_pool_size设置缓冲池大小
缓冲池通过LRU(Latest Recent Used,最近最少实用)算法进行管理。最频繁使用的页在LRU列表前端,最少使用的页在尾端,当缓冲池不能存放新读取的页时,首先释放LRU列表尾端的页(页数据刷新到磁盘,并从缓冲次中删除)。
InnoDB对于新读取的页,不是放到LRU列表最前端,而是放到midpoint位置(默认为5/8处)。这是因为一些SQL操作会访问大量的页(如全表扫描),读取大量非热点数据,如果直接放到首部,可能导致真正的热点数据被移除。
2.2 LRU list、free list、flush list
默认的缓冲页大小是16KB,使用LRU算法进行管理,新从磁盘加载的页默认加到LRU列表的midpoint处(尾端算起37%位置处)。通过show engine innodb status输出如下(部分):
Buffer pool size 512 【缓冲池内存512*16K】
Free buffers 256
Database pages 256 【LRU列表占用页】
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 255, created 40, written 67
0.16 reads/s, 0.06 creates/s, 0.37 writes/s
Buffer pool hit rate 943 / 1000 【缓冲池命中率大于95%则良好】, young-making rate 0 / 1000 not 0 / 1000
LRU len: 256, unzip_LRU len: 0 【LRU列表中的页可被压缩分为1K/2K/4K/8K之类的页】
LRU列表中的页被修改后变为dirty page,此时缓冲池中的页和磁盘不一致,通过checkpoint刷回磁盘,其中Flush list即为dirty page列表。
2.3 重做日志缓冲
重做日志先放到这个缓冲区,然后按一定频率刷新到重做日志文件。配置参数:innodb_log_buffer_size,默认是8MB,
刷新规则:
- Master Thread每秒将一部分重做日志缓冲刷新到重做日志文件
- 每一事务提交时会将重做日志刷新到重做日志文件(如果配置了)
- 重做日志缓冲区使用空间大于1/2
2.4 额外的内存池
内存堆,对InnoDB内部使用的数据结构对象进行管理。在对一些数据结构本身的内存进行分配时,需要从额外的内存池中申请,当该区域的内存不够时,会从缓冲池中申请。
三、线程
InnoDB存储引擎是多线程的模型,因此后台有多个不同的后台线程,负责处理不同的任务。
主要作用:
- 负责刷新内存池中的数据,保证缓冲池的内存缓冲的是最近的数据
- 已修改的数据文件刷新到磁盘文件
- 保证数据库发生异常的情况下InnoDB能恢复到正常状态。
InnoDB运行时主要有以下线程:
Master Thread
负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括脏页的刷新,合并插入缓冲(INSERT BUFFER),UNDO页的回收等。
IO Thread
负责AIO请求的回调处理。
参数:innodb_read_io_threads,innodb_write_io_threads
Purge Thread
事务提交后,undo log可能不再需要,由Purge Thread负责回收并重新分配的这些已经使用的undo页。
注意:Purge Thread需要离散地读取undo页。
Page Cleaner Thread
InnoDB 1.2.x引入,将Master Threader中刷新脏页的工作移至该线程,如上面说的FLUSH LRU LIST Checkpoint以及Async/Sync Flush Checkpoint。
四、checkpoint
当每次执行update、delete等语句更改记录时,缓冲池中的页与磁盘不一致,但是缓冲池的页不能频繁刷新到磁盘中(频率过大性能低),因此增加了write ahead log策略,当事务提交时先写重做日志,再修改内存页。当发生宕机时通过重做日志来恢复。checkpint解决以下问题:
(1)减少重做日志大小,缩减数据恢复时间。
(2)缓冲池不够用时将脏页刷回磁盘。
(3)重做日志不可用时将脏页刷回磁盘(如写满)。
show variables like 'innodb_max_dirty_pages_pct'; (默认75%)来控制inndodb强制进行checkpoint。
若每个重做日志大小为1G,定了了两个总共2G,则:
asyn_water_mark = 75 % * 重做日志总大小。
syn_water_mark = 90 % * 重做日志总大小。
(1)当checkpoint_age < asyn_water_mark时则不需要刷新脏页回盘。
(2)当syn_water_mark < checkpoint_age < syn_water_mark 时触发ASYNC FLUSH。
(3)当checkpoint_age>syn_water_mark触发sync flush,此情况很少发生,一般出现在大量load data或bulk insert时。
五、Master Thread工作方式
Master Thread具有最高的线程优先级别,内部由多个循环组成:主循环(loop),后台循环(backgroup loop),刷新循环(flush loop),暂停循环(suspend loop),Master Thread根据数据库运行状态在以上循环切换。
Master Thread主要流程伪代码如下:
void master_thread() {
goto loop;
// 主循环
loop:
for(int i = 0; i < 10; i++) {
// 每秒一次操作
thread_sleep(1)
// 日志缓冲刷新到磁盘,即使这个事务没提交
do log buffer flush to disk
// 合并插入缓冲(如果前一秒IO次数少于5次,InnoDB认为IO压力很小,执行该操作)
if(last_one_second_ios < 5)
do merge at most 5 insert buffer
// 至多刷新100个InnoDB的脏页到磁盘(脏页比例超过innodb_max_dirty_pages_pct)
if(buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct)
do buffer poll flush 100 dirty page
// 没有用户活动,跳转到 backgroupo loop
if(no user activity)
goto backgroupo loop
}
// 每10秒操作
// 刷新100个脏页到磁盘(过去10秒内IO操作小于200次)
if(last_ten_second_ios < 200)
do buffer pool flush 100 dirty page
// 合并最多5个插入缓冲
do merge at most 5 insert buffer
// 合并最多5个插入缓冲
do log buffer flush to disk
// 删除无用的Undo页(最多20个undo页)
do full purge
//脏页比例超过innodb_max_dirty_pages_pct,刷新100个脏页到磁盘,否则刷新10个脏页
if(buf_get_modified_ratio_pct > 70%)
do buffer pool flush 100 dirty page
else
do buffer pool flush 10 dirty page
goto loop
// 后台循环
backgroup loop:
// 删除无用的Undo
do full purge
// 合并20个插入缓冲
do merge 20 insert buffer
if not idel:
goto loop
else
goto flush loop
// 刷新循环
flush loop:
// 刷新100个脏页到磁盘,直到脏页比例小于innodb_max_dirty_pages_pct
do buffer pool flush 100 dirty page
if(buf_get_modified_ratio_pct>innodb_max_dirty_pages_pct)
goto flush loop
goto suspend loop
// 暂停循环
suspend loop:
// 暂停线程
suspend_thread()
// 等待事件
waiting event;
goto loop;
}
如上所示,主循环有两大操作,每秒操作和十秒操作。
InnoDB1.0.x优化:
在每秒操作中,Master Thread每次最多刷新100个脏页(脏页比例超过innodb_max_dirty_pages_pct),合并20个插入缓冲,如果在写入密集的应用,处理速度可能太慢了。
从InnoDB 1.0.x开始,提供了通过innodb_io_capacity参数
每秒操作中合并插入缓冲数量为innodb_io_capacity * 5%
刷新脏页数量为innodb_io_capacity
而默认innodb_max_dirty_pages_pct参数值从90调整为75
引入以下参数innodb_adaptive_flushing:自适应刷新
脏页比例小于innodb_max_dirty_pages_pct,也会刷新一定量的脏页(由InnoDB控制刷新策略和数量)
innodb_purge_batch_size:控制每次full purge回收Undo页,默认还是20
InnoDB1.2.x优化:
- InnoDB空闲时,执行原来的10秒一次操作,繁忙时,执行原来的每秒一次操作
- 刷新脏页操作,分离到单独Page Cleaner Thread
六、InnoDB关键特性
InnoDB关键特性包括:
- Insert buffer(插入缓冲)
- double write(两次写)
- adaptive hash index(自适应哈希索引)
- Async IO(异步IO)
- Flush neighbor page(刷新临近页)
Insert buffer
若插入按照聚集索引primary key插入,页中的行记录按照primary存放,一般情况下不需要读取另一个页记录,插入速度很快(如果使用UUID或者指定的ID插入而非自增类型则可能导致非连续插入导致性能下降,由B+树特性决定)。如果按照非聚集索引插入就很有可能存在大量的离散插入,insert buffer对于非聚集索引的插入和更新操作进行一定频率的合并操作,再merge到真正的索引页中。使用insert buffer需满足条件:
(1)索引为辅助索引。
(2)索引非唯一。(唯一索引需要从查找索引页中的唯一性,可能导致离散读取)
Double write
Doubel write保证了页的可靠性,Redo log是记录对页(16K)的物理操作,若innodb将页写回表时写了一部分(如4K)出现宕机,则物理页将会损坏无法通过redolog恢复。所以在apply重做日志前,将缓冲池中的脏页通过memcpy到doublewrite buffer中,再将doublewrite buffer页分两次每次1MB刷入共享表空间的磁盘文件中(磁盘连续,开销较小),完成doublewrite buffer的页写入后再写入各个表空间的表中。
当写入页时发生系统崩溃,恢复过程中,innodb从共享表空间的doublewrite找到该页的副本,并将其恢复到表空间文件中,再apply重做日志。
Adaptive hash index
Innodb根据访问频率对热点页建立哈希索引,AHI的要求是对页面的访问模式必须一样,如连续使用where a='xxx' 访问了100次。建立热点哈希后读取速度可能能提升两倍,辅助索引连接性能提升5倍。
通过show engine innodb status\G;查看hash searches/s, 表示使用自适应哈希,对于范围查找则不能使用。
Async IO
用户执行一次扫描如果需要查询多个索引页,可能会执行多个IO操作,AIO可同时发起多个IO请求,系统自动将这些IO请求合并(如请求数据页[1,2]、[2,3]则可合并为从1开始连续扫描3个页)提高读取性能。
刷新临近页
InnoDB提供刷新临近页功能:当刷新一脏页时,同时检测所在区(extent)的所有页,如果有脏页则一并刷新,好处则是通过AIO特性合并写IO请求,缺点则是有些页不怎么脏也好被刷新,而且频繁的更改那些不怎么脏的页又很快变成脏页,造成频繁刷新。对于固态磁盘则考虑关闭此功能(将innodb_flush_neighbors设置为0)。
七、InnoDB的启动、关闭与恢复
innodb_fast_shutdown
该值影响数据库正常关闭时的行为,取值可以为0/1/2(默认为1):
【为0时】:关闭过程中需要完成所有的full purge好merge insert buffer,并将所有的脏页刷新回磁盘,这个过程可能需要一定的时间,如果是升级InnoDB则必须将此参数调整为0再关闭数据库。
【为1时(默认)】:不需要full purge和merge insert buffer,但会将缓冲池中的脏页写回磁盘。
【为2时】:不需要full purge和merge insert buffer,也不会将缓冲池中的脏页写回磁盘,而是将日志写入日志文件中,后续启动时recovery。
innodb_force_recovery
参数innodb_force_recovery直接影响InnoDB的恢复情况。
默认值为0:进行所有的恢复操作,当不能进行有效恢复(如数据页corrupt)则将错误写入错误日志中。
某些情况下不需要完整的恢复造成,则可定制恢复策略,参数innodb_force_recovery还可以设置为6个非零值:1-6,大的数字表示包含了前面所有小数字表示的影响。有以下几种:
- 1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
- 2(SRV_FORCE_NO_BACKGROUND):阻止Master Thread线程运行,如果master thread需要进行full purge操作,这样会导致crash。
- 3(SRV_FORACE_NO_TRX_UNDO):不进行事务的回滚操作。
- 4(SRV_FORCE_NO_IBUF_MERGE):不进行插入缓冲区的合并操作。
- 5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看undo log,这样未提交的事务被视为已提交。
- 6(SRV_FORCE_NO_LOG_REDO):不进行redo操作。
在设置了innodb_force_recovery大于0后可对表进行select/create/drop操作,但不能进行insert update和delete等DML。如有大事务未提交,并且发生了宕机,恢复过程缓慢,不需要进行事务回滚则将参数设置为3以加快启动过程。