MySQL体系架构¶

数据库是数据的集合，数据库管理系统（DBMS）是操作和管理数据库的应用程序。数据库应用主要有两类：OLAP（联机分析处理）和OLTP（联机事务处理）。

OLAP的主要特点是:

实时性要求不高
数据量大
并发量小

OLTP的主要特点是:

实时性要求高
数据量小
高并发
要求满足ACID

mysql是一种DBMS，其体系架构如下图所示：

1.0.1.MySQL架构图

mysql中集成的是插件式的存储引擎，InnoDB引擎是其中之一。存储引擎基于表而不是数据库：同一个数据库中根据不同表的访问操作需求可以选择不同的存储引擎。

InnoDB¶

InnoDB引擎主要面对OLTP类应用。

InnoDB引擎在mysql中处于文件和文件系统的上层，管理着对InnoDB引擎表的访问和更新。

InnoDB架构图¶

MySQL5.7版本中的架构图：

1.0.2.InnoDB详细架构图

MySQL8.0 版本中的架构图：

innodb-architecture8.0.png

图片来源与官网：

https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

InnoDB的一个多线程模型¶

1.0.3.InnoDB多线程模型

Master Thread - 核心线程¶

以下内容全部引自： InnoDB存储引擎——Master Thread工作方式

（当做备份）

Master Thread是InnoDB存储引擎非常核心的一个后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲、UNDO页的回收等。

InnoDB 1.0.x版本之前的Master Thread¶

Master Thread具有 最高的线程优先级别。

其内部由多个循环组成：主循环（loop）、后台循环（backgroup loop）、刷新循环（flush loop）、暂停循环（suspend loop）。

Master Thread会 根据数据库运行的状态 在loop、backgroup loop、flush loop和suspend loop中 进行切换。

loop是主循环，大多数的操作都在这个循环中，主要有两大部分的操作——每秒钟的操作和每10秒钟的操作。伪代码如下：

void master_thread()
{
    loop:
    for(int i = 0; i < 10; ++i){
        do thing once per second;
        sleep 1 second if necessary;
    }
    do things once per ten seconds;
    goto loop;
}

每秒一次的操作包括：

日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）；

即使某个事务还没有提交，InnoDB存储引擎仍然每秒会将重做日志缓冲中的内容刷新到重做日志文件。这也解释了为什么再大的事务提交的时间也是很短的。

合并插入缓冲（可能）；

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

至多刷新100个InnoDB的缓冲池中的脏页到磁盘（可能）；刷新100个脏页也不是每秒都会发生的，InnoDB存储引擎通过判断当前缓冲池中脏页的比例(buf_get_modified_ratio_pct)是否超过了配置文件中 innodb_max_dirty_pages_pct这个参数（默认是75，代表75%），如果超过了这个值，InnoDB存储引擎则认为需要做磁盘同步的操作，将100个脏页写入磁盘中。
如果当前没有用户活动，则切换到background loop(可能)；

综上所述，伪代码可以进一步具体化。

void master_thread()
{
    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 backgroud loop;
    }
    do things once per ten seconds;
    backgroud loop;
    do something;
    goto loop;
}

每10秒的操作主要是下面几个方面：

刷新100个脏页到磁盘（可能） InnoDB存储引擎会先判断过去10秒之内磁盘的IO操作是否小于200次，如果是，InnoDB存储引擎认为当前有足够的磁盘IO能力，因此将100个脏页刷新到磁盘。
合并至多5个插入缓冲（总是）
将日志缓冲刷新到磁盘（总是）
删除无用的Undo页（总是）
刷新100个或者10个脏页到磁盘（总是） InnoDB存储引擎会执行full purge操作，即删除无用的Undo页。对表进行update，delete这类的操作时，原先的行被标记为删除，但是因为一致性读的关系，需要保留这些行版本的信息。但是在full purge过程中，InnoDB存储引擎会判断当前事务系统中已被删除的行是否可以删除，比如有时候可能还有查询操作需要读取之前版本的undo信息，如果可以删除，InnoDB存储引擎会立即将其删除。从源代码中可以看出，InnoDB存储引擎在执行full purge 操作时，每次最多尝试回收20个undo页。

然后，InnoDB存储引擎会判断缓冲池中脏页的比例（buf_get_modified_ratio_pct）,如果有超过70%的脏页，则刷新100个脏页到磁盘，如果脏页的比例小于70%,则只需刷新10%的脏页到磁盘。

伪代码进一步细化:

void master_thread()
{
    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 backgroud 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;
    backgroud loop;
    do something;
    goto loop;
}

如果当前没有用户活动（数据库空闲）或者数据库关系，就会切换到backgroud loop这个循环。 backgroud loop会执行以下操作：

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

如果flush loop中也没有什么事情可以做了，InnoDB存储引擎会切换到suspend_loop，将Master Thread挂起，等待事件的发生。若用户启用了InnoDB存储引擎，却没有使用任何InnoDB存储引擎的表，那么Master Thread总是处于挂起的状态。

最后，Master Thread完整的伪代码如下：

void master_thread()
{
    loop:
    for(int i = 0; i < 10; ++i){
    thread_sleep(1);                            // 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) 
                                              // 如果缓冲池中的脏页比例大于innodb_max_dirty_pages_pct(默认是75时)
            do buffer pool flush 100 dirty page;    // 刷新100脏页到磁盘
        if(no user activity)
            goto backgroud loop;
    }
    if(last_ten_second_ios < 200)   // 如果过去10内磁盘IO次数小于设置的innodb_io_capacity的值（默认是200）
        do buffer pool flush 100 dirty page;

    do merge at most 5 insert buffer;   // 合并插入缓冲是innodb_io_capacity的5%（10）（总是）
    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;

    backgroud loop： // 后台循环
    do full purge   // 删除无用的undo页 （总是）
    do merge 20 insert buffer;  // 合并插入缓冲是innodb_io_capacity的5%（10）（总是）
    if not idle                 // 如果不空闲，就跳回主循环，如果空闲就跳入flush loop
        goto loop:              // 跳到主循环
    else
        goto flush loop

    flush loop:                 // 刷新循环
    do buffer pool flush 100 dirty page;
    if(buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct) 
                                // 如果缓冲池中的脏页比例大于innodb_max_dirty_pages_pct的值（默认75%）
        goto flush loop;        // 跳到刷新循环，不断刷新脏页，直到符合条件

        goto suspend loop;      // 完成刷新脏页的任务后，跳入suspend loop

    suspend loop:
    suspend_thread();           //master线程挂起，等待事件发生
    waiting event;
    goto loop;
}

InnoDB 1.2.x版本之前的Master Thread¶

1.0.x版本中，InnoDB存储引擎最多只会刷新100个脏页到磁盘，合并20个插入缓冲。如果是在写入密集的应用程序中，每秒可能会产生大于100个的脏页，如果是产生大于20个插入缓冲的情况，那么可能会来不及刷新所有的脏页以及合并插入缓冲。

后来，InnoDB存储引擎提供了参数innodb_io_capacity，用来表示磁盘IO的吞吐量，默认值为200。

mysql> show variables like 'innodb_io_capacity';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_io_capacity | 200   |
+--------------------+-------+
1 row in set (0.00 sec)

对于刷新到磁盘的页的数量，会按照innodb_io_capacity的百分比来进行控制。规则如下：

在合并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity值的5%;
在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity;

如果用户使用的是SSD类的磁盘，可以将innodb_io_capacity的值调高，直到符合磁盘IO的吞吐量为止；

另一个问题是参数innodb_max_dirty_pages_pct的默认值，在1.0.x版本之前，该值的默认值是90，意味着脏页占缓冲池的90%。InnoDB存储引擎在每秒刷新缓冲池和flush loop时会判断这个值，如果该值大于innodb_max_dirty_pages_pct,才会刷新100个脏页，如果有很大的内存，或者数据库服务器的压力很大，这时刷新脏页的速度反而会降低。后来将innodb_max_dirty_pages_pct的默认值改为了75。这样既可以加快刷新脏页的频率，又能够保证磁盘IO的负载。

mysql> show variables like 'innodb_max_dirty_pages_pct';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_max_dirty_pages_pct | 75    |
+----------------------------+-------+
1 row in set (0.00 sec)

还有一个新的参数是innodb_adaptive_flushing(自适应地刷新)，该值影响每秒刷新脏页的数量。原来的刷新规则是：脏页在缓冲池所占的比例小于innodb_max_dirty_pages_pct时，不刷新脏页；大于innodb_max_dirty_pages_pct时，刷新100个脏页。随着innodb_adaptive_flushing参数的引入，InnoDB通过一个名为buf_flush_get_desired_flush_rate的函数来判断需要刷新脏页最合适的数量。buf_flush_get_desired_flush_rate函数通过判断产生重做日志的速率来决定最合适的刷新脏页数量。

之前每次进行full purge 操作时，最多回收20个Undo页，从InnoDB 1.0.x版本开始引入了参数innodb_purge_batch_size,该参数可以控制每次full purge回收的Undo页的数量。该参数的默认值为20，并可以动态地对其进行修改。

mysql> show variables like 'innodb_purge_batch_size';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_purge_batch_size | 20    |
+-------------------------+-------+
1 row in set (0.00 sec)

Master Thread的伪代码变为了下面的形式：

void master_thread()
{
    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 backgroud 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
        do buffer pool flush 10%innodb_io_capacity dirty page;

    goto loop;
    backgroud 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_ratio_pct > innodb_max_dirty_pages_pct)
        goto flush loop;

    goto suspend loop;

    suspend loop:
    suspend_thread();
    waiting event;
    goto loop;
}

这个版本的性能得到了提高。

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
170312 20:14:04 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 38 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 1_second, 1 sleeps, 0 10_second, 1 background, 1 flush
srv_master_thread log flush and writes: 1

可以看到主循环进行了1次，每秒的操作进行了1次，10秒一次的操作进行了0次，backgound loop进行了1次，flush loop进行了1次。

InnoDB 1.2.x版本的Master Thread¶

1.2.x版本中再次对Master Thread进行了优化。 Master Thread的伪代码如下：

if InnoDB is idle
    srv_master_do_idle_tasks();
else
    srv_master_do_active_tasks();

其中srv_master_do_idle_tasks()就是之前版本中每10秒的操作，srv_master_do_active_tasks()处理的是之前每秒中的操作。同时，对于刷新脏页的操作，从Master Thread线程分离到一个单独的Page Cleaner Thread，从而减轻了Master Thread的工作，同时进一步提高了系统的并发性。

IO Thread¶

InnoDB中大量使用**AIO (Async IO)** 来处理IO请求。

IO Thread的作用，是负责这些 IO 请求的回调（call back）

可使用 show engine innodb status看到以下类型

insert buffer thread
log thread
read thread（4个）
write thread（4个）

Purge Thread¶

作用¶

事务被提交后，其所使用的undo log可能不在需要。因此，需要purge thread来回收已经使用并分配的undo页。

来历¶

以前Master Thread来完成释放undo log，InnoDB1.1独立出来，分担主线程压力

查看purge thread的数量¶

mysql> show variables like 'innodb_purge_threads';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_purge_threads | 4     |
+----------------------+-------+
1 row in set (0.02 sec)

Page Cleaner Thread¶

作用¶

**脏页**刷新到磁盘

来历¶

以前Master Thread来刷新脏页，InnoDB1.2独立出来，分担主线程压力