MySQL技术内幕2InnoDB存储引擎

InnoDB

通常来说，InnoDB存储引擎是OLTP应用中核心表的首选存储引擎
OLTP应用：On-Line Transaction Processing联机事务处理过程(OLTP)，也称为面向交易的处理过程，其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果，是对用户操作快速响应的方式之一。

2.1InnoDB存储引擎概述

该存储引擎是第一个完整支持ACID事务的MySQL存储引擎，其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，同时被设计用来最有效的利用以及使用内存和cpu

2.2InnoDB存储引擎版本

MySQL5.1:允许存储引擎以动态方式加载引擎；两个引擎，静态引擎，动态加载引擎(InnoDBPlugin)InnoDB1.0.x,增加compress和dynamic页格式
MySQL5.5:支持InnoDB1.1.x，增加了Linux AIO多回滚段
MySQL5.6:InnoDB1.2.x，增加了全文索引支持，在线索引添加

2.3InnoDB体系架构

后台线程
内存池
磁盘文件

2.3.1后台线程

Master Thread
主要负责讲缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲(INSET BUFFER)、UNDO页的回收等
IO Thread
主要负责IO请求的回调处理。
InnoDB1.0之前共有四个IOThread，分别是write eadinsert bufferlog io thread
从InnoDB1.0.x，read和write thread分别增大到了4个。分别可以使用innodb_read_io_threads和innodb_write_io_threads来调整数目
可以通过SHOW ENGINE INNODB STATUSG来观察io Thread
Purge Thread
事务被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经被分配使用的undo页
用户可以在MySQL的数据库配置文件中添加如下命令来启动独立的PurgeThread:

[mysqld]
innodb_purge_thread=1

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

2.3.2 内存

1. 缓冲池

缓冲池的默认页面大小为16k
缓冲池的配置通过innodb_buffer_pool_size来设置
查看：SHOW VARIABLES LIKE 'innodb_buffer_pool_size'G;
缓冲池中的数据页类型有：索引页、数据页、undo页、插入缓冲页、自适应哈希索引、InnoDB存储的锁信息、数据字典索引等
从1.0.x允许有多个缓冲池实例，该属性可以通过innodb_buffer_pool_instances来配置，默认值为１

2. LRU List、Free List 和Flash List

LRU优化算法midpoint insertion strategy，midpoint可以通过innodb_old_blocks_pct来配置（表示到尾端的百分比）:默认配置下将最近读到的页放入lru列表长度的5/8处。
midpoint之前的成为new列表，之后的称为old列表
innodb_old_blocks_time:用于表示列表读取到mid位置后需要等待多久会被加入到LRU的热端

LRU用来管理已经读取的页，最初为空，所有的页都放在Free List中
LRUold部分的页加入new端的操作被称为page made young，而因为innodb_old_blocks_time的设置没有加入new成功的操作被称为page not made young
可以通过SHOW ENGINE INNODB STATUS 来查看LRU 和 free 列表的状态
一个重要的检测变量 Buffer pool hit rate 如果小于95%，用户需要观察是否由于全表扫描引起的LRU列表被污染的问题

从1.2版本开始可以使用表INNODB_BUFFER_POOL_STATUS来观察缓冲池的运行状态

SELECT 
POOL_ID,HIT_RATE,PAGES_MADE_YOUNG,PAGES_NOT_MAGE_YOUNG
FROM INNODB_BUFFER_POOL_STATUSG;

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

SELECT 
TABLE_NAME,SPACE,PAGE_NUMBER,PAGE_TYPE
FROM INNODB_BUFFER_PAGE_LRU
WHERE SPACE=1;

Innodb从1.0.x开始支持压缩页功能，将原来的16k变为1k2k4k8k，非16K的页通过unzip_LRU列表进行管理
可以通过information_schema架构下的表INNODB_BUFFER_PAGE_LRU来观察unzip_LRU中的页

SELECT 
TABLE_NAME,SPACE,PAGE_NUMBER,COMPRESSED_SIZE
FROM INNODB_BUFFER_PAGE_LRU
WHERE COMPRESSED_SIZE<>0;

flush 列表即需要刷新回磁盘的脏页，可以通过两种方式来查看：
a. SHOW ENGINE INNODB STATUS查看Modified db pages
b. 通过INNODB_BUFFER_PAGE_LRU

SELECT
TABLE_NAME,SPACE,PAGE_NUMBER,PAGE_TYPE
FROM INNODB_BUFFER_PAGE_LRU
WHERE OLDEST_MODIFICATION>0;

3.重做日志缓冲

重做日志缓冲只需要保证每秒钟产生的事务量在这个缓冲大小之内即可。该值可由参数innodb_log_buffer_size配置，默认为8MB
重做日志缓冲在以下三种情况下刷新到重做日志中：

Mater Thread 每一秒将重做日志缓冲刷新到重做日志文件
每个事务提交时将重做日志文件缓冲刷新到重做日志文件
当重做日志缓冲剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件

4. 额外的内存池

2.4Checkpoint技术

write ahead log策略：当事务提交时，先写重做日志，再修改页
checkpoint技术解决一下几个问题:

缩短数据库的恢复时间
缓冲池不够用时，可将脏页刷新到磁盘
重做日志不可用时，刷新脏页

Innodb使用LSN(Log Sequence Number)来标记版本，是个8字节的数字，单位字节。可以通过SHOW ENGINE INNODB STATUS来观察

在innodb中具有两种checkpoint:

Sharp Checkpoint
Fuzzy Checkpoint
sharp checkpoint　发生在数据库关闭时，将所有的脏页都刷新回磁盘，即默认的工作方式，即参数innodb_fast_shutdown=1
fuzzy checkpoint 发生在数据库运行时，只刷新一部分脏页，innodb中的Fuzzy checkpoint:
Master Thread Checkpoint:每秒或者每十秒异步刷新一部分比例脏页到磁盘，不会阻塞用户查询线程
FLUSH_LRU_LIST Checkpoint: 在1.1.x版本之前，需要检查LRU列表中是否有足够的空间操作发生在用户查询线程中，会阻塞用户查询操作；从1.2.x之后被单独放在PageCleaner线程中进行，通过innodb_lru_scan_depth来控制lru可用页数量，默认1024
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
sync_water_mark=90%total_redo_log_file_size
当,
- checkpoint_age<async_water_mark时不需要刷新任何脏页到磁盘
- async_water_mark<checkpoint_age<sync_water_mark时，触发Async_Flush，从列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age<aync_water_mark
- checkpoint_age>sync_water_mark这种情况一般很少发生，除非设置的重做日志文件太小，并且在进行类似LOAD DATA 的BULK INSERT操作。此时触发Sync Flush操作，从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age<async_water_mark
  可以通过show engine innodb status，来确定刷新是从flush列表还是 LRU列表发起的checkpoint
Dirty Page too much Checkpoint :即脏页数量太多，可由innodb_max_dirty_pct控制

2.5 Master Thread工作方式

2.5.1 InnoDB 1.0.x之前的工作方式

内部由以下循环构成，会在其中进行切换:主循环（Loop），后台循环（background loop），刷新循环（flush loop），暂停循环（suspend loop）

Loop

包含每秒钟的操作和每十秒的操作

每秒的操作

日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）；
合并插入缓冲（可能）根据当前一秒的io次数
至多刷新100个InnoDB的缓冲池中的脏页到磁盘（可能），根据缓冲池中脏页的比例innodb_max_dirty_page_pct
如果当前没有用户活动，则切换到background loop（可能）

每十秒的操作

刷新100个脏页到磁盘（可能），根据前面10秒的io次数是否小于200
合并至多5个插入缓冲（总是）
将日志缓冲刷新到磁盘（总是）
删除无用的Undo页（总是）
刷新100个或者10个脏页到磁盘（总是），根据脏页的比例

Background Loop

删除无用的Undo页（总是）
合并20个插入缓冲（总是）
跳回到主循环（总是）
不断刷新100页直到符合条件（可能，跳转到flush Loop中完成）

循环的伪代码如下

void master_thread(){
    goto loop;
loop:
    for(int i=0;i<10;i++){
        thread_sleep(1);
        do log buffer flush to disk;
        if(last_one_second_ios < 5){
            do merge at most 5 insert buffer;
        }
        if(buf_get_modified_ratio_pct>innodb_max_dirty_pages_pct){
            do buffer pool flush 100 dirty page
        }
        if(no user activity){
            goto background loop;
        }
    }
    if (last_ten_second_ios<200)
        do buffer pool flush 100 dirty page
    do merge at most 5 insert buffer
    do log buffer flush to disk
    do full purge
    if (buf_get_modified_ratio_pct>70%){
        do buffer pool flush 100 dirty page 
    }
    else{
        buffer pool flush 10 dirty page
    }
    goto loop;
    
    background loop:
     do full purge;
    do merge 20 insert buffer;
    if not idle:
        goto loop;
    else:
        goto flush loop;
    flush loop:
        do buffer pool flush 100 dirty page;
        if (buf_get_modified_radio_pct>innodb_max_dirty_page_pct)
        {
            goto flush loop;
        }
        goto suspend loop;
    suspend loop:
        suspend_thread();
    waiting_event;
    goto loop;
}

2.5.2innoDB1.2.x版本之前的工作方式（从innodb1.0.x开始）

几个改变：

引入innodb_io_capacity表示io吞吐量，用来控制io刷新到磁盘的
innodb_max_dirty_pages_pct默认值调为75
innodb_adaptive_flushing该值影响每秒刷新的数量
innodb_purge_batch_size控制undo页数量，并可以动态修改

循环的伪代码如下：

void master_thread(){
    goto loop;
loop:
    for(int i=0;i<10;i++){
        thread_sleep(1);
        do log buffer flush to disk;
         if(last_one_second_ios < 5% innodb_io_capacity){
            do merge 5% innodb_io_capacity insert buffer;
        }
        if(buf_get_modified_ratio_pct>innodb_max_dirty_pages_pct){
            do buffer pool flush 100% innodb_io_capacity dirty page;
        }
        else if enable adaptive flush
            do buffer pool flush desired amount dirty page;
        if(no user activity){
            goto background loop;
        }
    }
    if (last_ten_second_ios<innodb_io_capacity)
        do buffer pool flush 100% innodb_io_capacity dirty page
    do merge 5% innodb_io_capacity insert buffer
    do log buffer flush to disk
    do full purge
    if (buf_get_modified_ratio_pct>70%){
       do buffer pool flush 100% innodb_io_capacity dirty page
    }
    else{
       buffer pool flush 10% innodb_io_capacity dirty page
    }
    goto loop;
    
    background loop:
     do full purge;
    do merge 100% innodb_io_capacity insert buffer;
    if not idle:
        goto loop;
    else:
        goto flush loop;
    flush loop:
        do buffer pool flush 100% innodb_io_capacity dirty page;
        if (buf_get_modified_radio_pct>innodb_max_dirty_page_pct)
        {
            goto flush loop;
        }
        goto suspend loop;
    suspend loop:
        suspend_thread();
    waiting_event;
    goto loop;
}

2.5.3 InnoDB1.2.x

if InnoDB is idle
    srv_master_do_idle_tasks();//之前每十秒的操作
else
    srv_master_do_active_tasks();

2.6 InnoDB的关键特性

插入缓冲
两次写
自适应哈希索引
异步IO
刷新邻接页

2.6.1 插入缓冲

InsertBuffer

索引是辅助索引
索引不是唯一的

SHOW ENGINE INNODB STATUSG;
seg size 显示insert Buffer，inserts代表了插入的记录数，merged recs代表了合并的插入记录数量，merges代表合并次数，
插入缓冲内存大小可以通过IBUF_POOL_SIZE_PER_MAX_SIZE

Change Buffer
1.0.x版本后引入Changebuffer，开启各种buffer：InsertBuffer,DeleteBuffer,PurgeBuffer
对一条记录的删除可以分为两个过程:

将记录标记为删除（delete)
真正地讲记录删除（purge)
innoDB提供了参数Innodb_change_buffering用来开启各种buffer的选项:inserts,deletes,purges,changes,all,none
1.2.x版本后可以通过innodb_change_buffer_max_size来控制ChangeBuffer最大使用内存的数量，默认为25，表示最多使用1/4的内存空间，该参数最大有效值为50

SHOW ENGINE INNODB STATUSG;可以查看
merged operations:insert 表示insertBuffer ,delete mark 表示delete buffer，delete 表示 purge buffer;
discarded operations:表示当change Buffer 发生merge时，表已经被删除，此时已经无需再将记录合并到辅助索引中

Insert Buffer的内部实现
InsertBuffer是一颗B+树，放在ibdata1中。因此，试图通过独立表空间ibd文件恢复表中数据时，往往会CHECK TABLE失败。这是因为表的辅助索引中的数据可能还在Insert Buffer中，也就是共享表空间中，所以通过ibd文件进行恢复中，还需要REPAIR TABLE来重建表上所有的辅助索引：

非叶节点：

叶节点：

metadata:

IBUF_REC_OFFSET_COUNT:用来排序每个记录进入Insert Buffer的顺序

Insert Buffer Bitmap页用来追踪16384个辅助索引页，也就是256个区Extent

每个辅助索引页在bitmap中占4bit

4.Merge Insert Buffer
Merge Insert Buffer操作可能发生在以下几种情况下：

辅助索引页被读取到缓冲池中
Insert Buffer Bitmap页追踪到该辅助索引页已无空间时：可用空间小于1/32页，会强制进行一个合并操作
Master Thread

2.6.2两次写

本文来自博客园，作者：千里之行_始于足下，转载请注明原文链接：https://www.cnblogs.com/zhouyu0-0/p/11766597.html