mysql 的on可以有多个条件吗

转载

mob64ca140234eb 2024-10-31 15:30:33

文章标签 mysql 的on可以有多个条件吗缓冲池重做日志存储引擎 文章分类 MySQL 数据库

一、前言

InnoDB存储引擎，在之前求职面试的时候，看过很多csdn类似的文章介绍，也知道几个基本特点，自己死记硬背也能在面试或者日常工作中说出个浅显的一二三来，但是真的说起到深层的原理，其实还是属于一知半解的程度，所以，还是需要通过书籍来系统补充一下自己缺失的知识。

二、概述

InnoDB是事务安全的MySQL存储引擎，通常来说，InnoDB存储引擎是OLTP应用中核心表的首选存储引擎。

它具有如下特点：
1 支持行锁设计（写操作时，锁行而不是锁表）
2 支持MVCC（Multi-Version Concurrency Control，多版本并发控制，支持对数据库的并发访问）
3 支持外键
4 提供一致性非锁定读

三、InnoDB体系架构

InnoDB存储引擎有多个内存块，可以认为这个鞋内存块组成了一个大的内存池，负责如下工作：

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

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

3 重做日志（redo log）缓冲

mysql 的on可以有多个条件吗_重做日志

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

1 后台线程

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

Master Thread
Master Thread 是一个非常核心的后台线程，主要负责缓冲池中数据的异步刷新到磁盘，保证数据一致性。包括脏页刷新，合并插入缓冲（INSERT BUFFER）、UNDO页的回收等。
IO Thread
InnoDB存储引擎中主要使用的是AIO（Async IO，异步IO）来处理写IO请求，这样做避免了不必要的等待时间，提高数据库性能。而IO Thread的工作主要是负责这些IO请求的回调处理。
在InnoDB 1.0版本之前，主要有4个IO Thread，分别是write、read、insert buffer和 log IO Thread。
Purge Thread
事务被提交之后，其所使用的undolog可能不再需要，因此需要用这个线程来回收已经使用并分配的undo页。
Page Cleaner Thread
顾名思义，其作用是将之前InnoDB旧版本中脏页的刷新操作，从Master Thread线程中剥离出来，放到这个单独的线程里进行，以减轻主线程的工作。

2 内存

缓冲池

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理，因此可以将其视为基于磁盘的数据库系统（Disk-base Database）。在数据库系统中，由于CPU速度和磁盘速度存在鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。

缓冲池，简单来说，就是一块儿内存区域，通过内存的速度来弥补磁盘速度较慢对数据库的性能影响。在数据库中进行读取页的操作，首先将从磁盘读到的页放在缓冲池中，下一次再读相同的页时，首先从缓冲池中读取，若未命中，则从磁盘读取。

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

具体来看，缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲（insert buffer），自适应哈希索引（adaptive hash index）、InnoDB存储的锁信息（lock info）、数据字典信息（data dictionary）等。不能简单认为，缓冲池紧急缓存索引页和数据页，这两者仅占缓冲池很大一部分而已。

mysql 的on可以有多个条件吗_重做日志_02

2. LRU List、Free List和Flush List

缓冲池介绍了页的存储结构，而InnoDB存储引擎需要使用LRU List、Free List和Flush List这三个队列对这个内存区域进行管理。

通常来说，数据库中的缓冲池是通过LRU（Lastest Recent Used，最近最少使用）算法来管理，也就是说最频繁使用的页放在队列前端，最少使用的页放在尾端。当缓冲池不能存放新读取的页时，首先释放LRU列表中尾端的页。

LRU列表用于管理已经读取的页，但当数据库刚启动时，LRU了列表是空的，这时页都存放在Free列表中。当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中，否则根据LRU算法，淘汰LRU队列末尾的页，将该内存空间分配给新的页。

在LRU列表中的页被修改后，该页被称为脏页（dirty page），即缓冲池中的页和磁盘上的页的数据产生了不一致，这时候数据库会通过CHECKPOINT机制将脏页重新刷回磁盘，而FLUSH列表的页即为脏页列表。需要注意的是，脏页既存在于LRU列表中，也存在与Flush列表中，LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响。

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

在通常情况下，8MB的重做日志缓冲池足以满足大部分应用，因为重做日志会在下列三种情况下降重做日志缓冲池中的内容刷新到外部磁盘的重做日志文件中。

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

额外的内存池

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

3 Checkpoint技术

在介绍完缓冲池的知识后，我们知道，缓冲池的设计目的在于协调CPU速度和磁盘速度的鸿沟，因此页的操作首先都是在缓冲池中完成的，如果一条DML语句，如Update或者Delete改变了页中的记录，那么此时页是脏页，即缓冲池中页的版本比磁盘的要新，数据库需要将新版本的页从缓冲池刷新到磁盘。

如果每一次一个页发生变化，就将其刷新回磁盘，那么这个开销将会非常大。若热点数据集中在某几个页中，那么数据库将疲于刷新页回磁盘的操作，从而导致整体性能会非常差。同时如果在从缓冲池将页的新版本刷新到磁盘时发生了宕机，那么数据将无法恢复。为了避免发生数据丢失问题，当前事务数据库系统普遍采用Write Ahead Log测了，即当食物提交时，先写重做日志，再修改页。当由于发生宕机而导致数据丢失时，通过重做日志来完成数据恢复，这也是事务ACID中D（Durability 持久性）的要求。

假如重做日志可以无限增大，同时缓冲池也足够大，能够缓冲所有数据库的数据，那么是不需要将缓冲池中页的新版本刷新回磁盘的，因为发送宕机时，完全可以通过重做日志来恢复整个数据库系统中的数据到宕机发生的时刻，但是这需要两个前提条件

1 缓冲池可以缓存数据库中所有的数据；
2 重做日志可以无限增大

对于第一个前提条件，当数据库刚开始创建时，表中无任何数据，缓冲池确实可以缓存所有数据库文件，然而随着市场推广，用户增加，产品越来越受到关注，数据库容量必然不断增大。当前3TB的MySQL数据库已不少见，但是3TB的内存却很少见，。因此第一个假设在生产环境中很难得到保证

对于第二个前提条件，重做日志可以无限增大，或许是可实现的，但是成本要求高，同时也不便于运维。DBA或者SA不知道施恩么时候重做日志是否接近于磁盘可使用空间的阈值，并且要让存储设备支持可动态扩展也需要一定的技巧和设备支持。

即便满足上述两个条件，还有一种情况需要考虑：当宕机数据库的恢复时间，当数据库运行了几个月甚至是几年时，这时发生宕机，重新应用重做日志的时间会非常久，恢复的代价也非常大。

因此，Checkpoint（检查点）技术的目的在于解决以下几个问题：

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

当数据库发生宕机时，数据库不需要重做所有日志，因为Checkpoint之前的页都已经刷新回磁盘，故数据库只需要对Checkpoint后的重做日志进行恢复，这样就大大缩短了恢复时间。

此外，当缓冲池不够用时，根据LRU算法会移除最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页刷新回磁盘。

重做日志出现不可用的情况是因为当前事务数据库对重做日志的设计都是循环使用的，并不是让其无限增大。重做日志可以被重用的部分是指这些重做日志已经不再被需要，即当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。若此时重做日志还需要使用，那么必须强制产生Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

对于InnoDB而言，其是通过LSN（Log Sequence Number）来标记版本的，而LSN是8字节的数字，其单位也是字节，每个页有LSN，重做日志也有LSN，Checkpoint也有LSN。

在InnoDB中存储引擎中，Checkpoint发送的时间、条件以及脏页的选择都十分复杂，而Checkpoint所做的事情无外乎都是将缓冲池中的脏页刷回到磁盘。不同之处在与每次刷新多少页到磁盘，每次从哪里取脏页，以及什么时间触发Checkpoint。在InnoDB存储引擎内部，有两种Checkpoint，分别为：

1 Sharp Checkpoint
2 Fuzzy Checkpoint

Sharp Checkpoint发生在数据库关闭时，将所有脏页都刷回磁盘，这是默认的工作方式。

但若数据库在运行时，也使用Sharp Checkpoint，那么数据库的可用性就会受到很大影响。所以在InnoDB存储引擎内部使用Fuzzy Checkpoint进行页的刷新，即只刷新一部分脏页，而不是全量刷新脏页回磁盘。

在InnoDB存储引擎中可能发生以下几种情况的Fuzzy Checkpoint：

1 Master Thread Checkpoint
2 FLUSH_LRU_LIST Checkpoint
3 Async/Sync Flush Checkpoint
4 Dirty Page too much Checkpoint

对于Master Thread中发生的Checkpoint，差不多以每秒或者每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，即此时InnDB存储引擎可以进行其他操作，用户查询线程不会阻塞。

FLUSH_LRU_LIST Checkpoint是因为InnoDB存储引擎需要保证LRU列表中需要差不多有100个空闲页可供使用。如果没有100个可用空闲页，那么InnoDB存储引擎会将LRU列表尾端的页移除，如果这些页中有脏页，则需要进行Checkpoint，而这些页是来自LRU列表的，因此称为FLUSH_LRU_LIST Checkpoint。

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

若每个重做日志的大小为1GB，并且定义了两个重做日志文件，则重做日志文件的总大小为2GB，则async_water_mark为1.5GB，sync_water_mark为1.8GB。则：

1 checkpoint_age < async_water_mark时，不需要刷新任何脏页到磁盘

2 sync_water_mark < checkpoint_age < async_water_mark时，触发Async Flush，从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age < async_water_mark

3 checkpoint_age > async_water_mark这种情况很少发生，除非设置的重做日志文件太小，并且在进行类似的LOAD DATA的BULK INSERT操作。此时触发Sync Flush操作，从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age <async_water_mark

最后一种Checkpoint的情况是因为Dirty Page too much，即脏页的数量太多，导致InnoDB存储引擎强制执行Checkpoint。其目的总的来说还是为了保证缓冲池有足够可用的页（其实这里很类似业务中的一些兜底策略）。

5 Master Thread工作方式

InnoDB存储引擎主要工作都是在一个单独的后台线程——Master Thread中完成的。且该现场具有线程最高的优先级别。内部由多个循环（loop）组成：主循环（Loop）、后台循环（background loop）、刷新循环（flush loop）、暂停循环（suspend loop）。Master Thread会根据数据库运行的状态在上述循环中进行切换。

Loop称为主循环，其中有两大部分的操作——每秒钟的操作和每10秒中的操作。

mysql 的on可以有多个条件吗_缓冲池_03

可以看到，loop循环通过Thread sleep来实现，这意味着所谓的每秒一次或者每10秒一次的操作是不精确的。在负载很大的情况下，可能会有延迟，只能说大概在这个频率下。

其中，每秒一次的操作包括：

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

即使某个事务还没有提交，InnoDB存储引擎仍然每秒会将重做日志缓冲池中的内容刷新到重做日志文件。这一点是必须要指定的，因为这可以很好地解释为什么再大的事务提交（commit）的时间也很短暂。

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

同样，刷新100个脏页也不是每秒都会发送。InnoDB通过判断当前缓冲池中脏页的利弊是否超过数据库配置文件中的预设参数（innode_max_dirty_pages_pct，默认90，代表90%），如果超过了这个阈值，才会执行此步骤。

运行代码大致如下所示。

mysql 的on可以有多个条件吗_存储引擎_04

接下来看每10秒操作

刷新100个脏页到磁盘（可能）
合并至多5个插入缓冲
将日志缓冲刷新到磁盘
删除无用的Undo页
刷新100个或10个脏页到磁盘

在以上过程中，InnoDB存储引擎会先判断过去的10秒之内，磁盘IO操作是否小于200次，如果是，InnoDB认为当前有足够的磁盘IO操作能力，因此将100个脏页刷新到磁盘，接着，InnoDB存储引擎会合并插入缓冲。不同于每秒一次操作时可能发生的合并插入缓冲操作，这次的插入缓冲操作总会在这个阶段进行，之后InnoDB存储引擎会再一次将日志缓冲刷新到磁盘。这和每秒一次发生的操作总是一样的。

接着InnoDB存储引擎会进行一次full purge操作，即删除无用的Undo页，对表进行update、delete这类操作，原先的行被标记为删除，但是因为一致性读的关系，需要保留这些行版本的信息。但是在full purge过程中，InnoDB存储引擎会判断当前事务系统中已被删除的行是否可以删除，比如这时候可能还有一些查询操作需要读取之前版本的undo信息，如果可以删除，InnoDB会立即删除。

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

所以，完整的主循环伪代码如下：

mysql 的on可以有多个条件吗_重做日志_05

接下来，看background loop，若当前没有用户活动（数据库空闲时）或者数据库关闭（shutdown），就会切换到这个循环。background loop会执行以下操作：

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

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

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

mysql 的on可以有多个条件吗_重做日志_06

mysql 的on可以有多个条件吗_重做日志_07

当然，这个是旧版本（1.0.x之前）的Master Thread的具体实现，在缓冲池向磁盘刷新时其实都做了一定的硬编码，当固态硬盘出现时（SSD)，这种规定很大程度上限制了InnoDB存储引擎对磁盘IO的性能，尤其是写入性能。

从前面伪代码来看，无论何时，InnoDB存储引擎最大只会刷新100个脏页到磁盘，合并20个缓冲。如果是在写入密集型的应用程序中，每秒可能会产生大于100个的脏页，如果是产生大于20个插入缓冲的情况，Master Thread似乎会忙不过来。即使磁盘能在1秒内处理多于100个页的写入和20个插入缓冲的合并。同时，当发生宕机需要恢复时，由于很多数据还没有刷新回磁盘，会导致恢复的时间需要很久，尤其是对于insert buffer来说。

当然，后续的版本已经提供了比较完善的解决方案：
新增参数innodb_io_capacity，用来表示磁盘IO的吞吐量，默认值为200。对于刷新到磁盘页的数量，会按照这个参数的百分比进行控制，规则如下：

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

若用户使用了SSD类的磁盘，或者将几块磁盘做了RAID，当存储设备拥有更高的IO速度时，完全可以将innodb_io_capacity调得再高一点，直到符合磁盘IO的吞吐量为止。

其实这只是改动的其中一个点，更多的优化，可以自己去社区看看。或者研究一下源码。

另外，这里很多知识只是介绍到了概念的层次，更细节的点，可以去看技术内幕这本书，最好可以自己动手实践一下，加深记忆。