Chuoer47's Blog

Appearance

MySQL高级二 ​

一、锁 ​

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

  • InnoDB按照锁的粒度分为以下三类：
    • 行级锁：每次操作锁住一条行数据
    • 表级锁：每次操作锁住整张表
    • 全局锁：锁定数据库中的所有表
  • MyISAM、Memory等引擎仅支持表级锁，且不支持事务，因此适用于只读或以读为主的场景

1、全局锁 ​

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

  • 其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

语法:

sql
-- 加锁
flush tables with read lock ;

-- 数据备份
mysqldump -uroot –p1234 itcast > itcast.sql

-- 释放锁
unlock tables ;

数据库中加全局锁，是一个比较重的操作，存在以下问题：

  • 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

  • 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。

sql
mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

2、表级锁 ​

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类：

  • 表锁

  • 元数据锁（meta data lock，MDL）

  • 意向锁

1)表锁 ​

对于表锁，分为两类：

  • 表共享读锁（read lock）

  • 表独占写锁（write lock）

语法：

  • 加锁：lock tables 表名... read/write;

  • 释放锁：unlock tables; / 客户端断开连接

读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

2)元数据锁 ​

元数据锁 meta data lock , 元数据锁，简写MDL。MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与 DDL冲突，保证读写的正确性。

这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)。

具体SQL语句的加锁如下：

对应SQL锁类型说明
lock tables xxx read /writeSHARED_READ_ONLY /SHARED_NO_READ_WRITE
select, select ... lock in share modeSHARED_READ与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
insert 、update、delete、select ... for updateSHARED_WRITE与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
alter table ...EXCLUSIVE与其他的MDL都互斥

查看当前数据库中的加锁情况：

sql
 select object_type, object_schema, object_name,lock_type, lock_duration from performance_schema.metadata_locks;

3)意向锁 ​

为了避免DML在执行时，加的行锁与表锁冲突造成性能降低，InnoDB中引入了意向锁 Intention Lock，使得表锁不用遍历每行记录是否加锁。引入意向锁后，如果一个客户端对某一行加上了行锁，那么系统会自动对其加上意向锁，当别的客户端试图对其加上表锁时，便会检查意向锁是否兼容，若是不兼容，便会阻塞直到意向锁释放。

分类

  • 意向共享锁（IS）：Intention Shared Lock，当事务准备在某条记录上加S锁时，需要先在表级别加IS锁
  • 意向排他锁（lX）：Intention Exclusive Lock，当事务准备在某条记录上加X锁时，需要先在表级别加IX锁

表锁兼容性

兼容性XIXSIS
X不兼容不兼容不兼容不兼容
IX不兼容兼容不兼容兼容
S不兼容不兼容兼容兼容
IS不兼容兼容兼容兼容

注：意向锁之间不会互斥。一旦事务提交，IS、IX 都会自动释放。

3、行级锁 ​

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

  • 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
  • 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
  • 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

1)行锁 ​

InnoDB实现了以下两种类型的行锁：

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。

  • 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

共享锁之间相互兼容，排它锁与任何锁都不兼容。

记录锁SQL语句

SQL记录锁类型说明
insert隐式锁一般情况不加记录锁
updateX锁自动加锁
deleteX锁自动加锁
select不加锁
SELECT ... LOCK IN SHARE MODES锁需手动在SELECT之后加LOCK IN SHARE MODE
SELECT ... FOR UPDATEX锁需手动在SELECT之后加FOR UPDATE

2)间隙锁 ​

Gap Locks（LOCK_GAP）间隙锁对索引记录间隙（不含该记录）加锁，确保索引记录间隙不变，防止其他事务在这个间隙插入新记录。

  • Gap锁唯一目的是为了防止产生幻读
  • Gap锁可以共存，不会限制其它事务在同一间隙上加任何行锁
  • 如果要对索引末尾加Gap锁，可以对Supremum伪记录加Gap锁

3)临键锁 ​

Next-Key Locks（LOCK_ORDINARY）临键锁是记录锁和间隙锁的组合，同时锁住数据和数据前面的间隙。

  • 在Repeatable Read隔离级别下支持
  • 默认情况下InnoDB使用临键锁进行搜索和索引扫描，以防止幻读。
  • 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
  • 索引上的等值查询（普通索引），向右遍历，最后一个值不满足查询需求时，临键锁退化为间隙锁。

此外，还有插入意向锁（Insert Intention Locks）用于向加了gap锁的间隙插入新记录时的等待状态，隐式锁依赖事务id保证插入记录的安全

二、InnoDB引擎 ​

1、逻辑存储结构 ​

InnoDB的逻辑存储结构如下图所示:

  • 表空间是InnoDB存储引擎逻辑结构的最高层， 如果用户启用了参数 innodb_file_per_table(在8.0版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。
  • 段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点， 索引段即为B+树的非叶子节点。段用来管理多个Extent（区）。
  • 区，表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。
  • 页，是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
  • 行，InnoDB 存储引擎数据是按行进行存放的。在行中，默认有两个隐藏字段：
    • Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
    • Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

2、InnoDB记录格式 ​

InnoDB设计了四种行格式：Compact, Redundant, Dynamic, Compressed，可以在创建或修改的表语句中指定ROW_FORMAT，默认Dynamic行格式。

1)Compact行格式 ​

一条完整的记录分为记录的额外信息和记录的真实数据两部分组成。

  • 记录的额外信息
    • 变长字段长度列表：非NULL的变长字段/可变字符集字段的实际长度，按列逆序存放，每列占1~2个字节
    • NULL值列表：允许NULL的列的标志位，1为NULL，0非NULL，按列逆序，高位用0补齐至整字节。
    • 记录头信息：固定的5个字节组成
  • 记录的真实数据：真实列数据，以及三个隐藏字段
    • row_id: 可选，没有主键/Unique字段的记录，引擎默认生成该隐藏列作为主键
    • transaction_id: 最后一次插入或更新该行的事务 id
    • roll_pointer: 回滚指针，指向该行的 undo log

2)Redundant行格式 ​

MySQL 5.0 之前使用的行格式

3)行溢出 ​

  • 对于定长字段，无论真实数据大小，都会占用固定大小的空间，用0/空格补齐
  • VARCHAR类型最多占用65535字节，但还依赖于字符集、列的限制等
  • 对于Compact和Redundant，如果某一列数据过大，只会存储该列前768个字节，然后用20个字节指向存储溢出数据的另一个页
  • MySQL规定一个页中至少存放两行记录，以及列的数量，都会影响行溢出的临界点

4)Dynamic 和 Compressed 行格式 ​

MySQL 8.0 默认行格式就是Dynamic，与Compact类似，区别在于行溢出时，真实数据处仅有一个指针，指针存储所有数据字节的其它页。而Compressed会采用压缩算法对页面进行压缩，以节省空间

3、InnoDB数据页 ​

InnoDB设计了多种类型的页，存放不同的信息，其中存放表中记录的是数据页，也称索引页（InnoDB中索引即数据）。

名称中文名占用空间大小描述
File Header文件头部38字节页的一些通用信息
Page Header页面头部56字节数据页专有的一些信息
Infimum + Supremum最小记录和最大记录26不确定两个虚拟行记录
User Records用户记录不确定实际存储的行记录内容
Free Space空闲空间不确定页中尚未使用的空间
Page Directory页面目录不确定页中某些记录的相对位置
File Trailer文件尾部8字节校验页是否完整

1)User Records ​

每插入一条记录，都会从Free Space分配一条记录的空间给User Records，Free Space分配完毕则该页使用完毕。若干个记录以及两个伪记录按主键大小串成一个单链表

其中每条记录的记录头部分：

  • delete_mask:
    • 标记当前记录是否被删除
    • 删除后仅修改标志位和链表指针，记录空间成为可重用空间
    • 插入新纪录时会复用可重用空间
  • min_rec_mask: B+树每层非叶子结点的最小记录
  • n_owned: 该记录组所含的记录数，见页目录部分
  • heap_no:
    • 当前记录在本页中的位置
    • 其中0、1对应最小(Infimum)和最大(Supremum)的伪记录
    • 完整记录之间根据主键大小排序。
  • record_type: 当前记录类型
    • 0 普通记录
    • 1 为B+树非叶节点记录
    • 2 最小记录(即Infimum伪记录)
    • 3 最大记录(即Supremum伪记录)。
  • next_record: 当前记录真实数据到下一条记录真实数据的地址偏移量 (按列逆序放置正是为了提高缓冲命中率)

2)Page Directory ​

对整个单链表查找记录性能低，因此采取分组索引策略：

  • 所有正常记录 (包括最小、最大记录，排除已删除记录) 划分为若干组
  • 每组最后一条记录（即最大记录）的头记录中的n_owned标记该组内记录数
  • 每组最后一条记录的地址偏移量抽取成索引，存放在Page Directory中构成页目录，其中每个偏移量称为槽

槽的更新：

  • 初始时仅Infimum和Supremum两个组
  • 每插入一条记录，从页目录中找到主键值大于本记录的最小槽，然后把该槽的n_owned加一，直到该组内记录达到8条
  • 一个组内的记录达到8条后，再次插入新记录时，将该组拆分为一个4条记录的组和一个5条记录的组，并新增对应的槽

因此，查找时:

  • 通过二分法确定该记录所在的槽，并借助前一个槽找到所在槽中主键值最小的记录
  • 通过记录的next_record遍历该槽查找记录

3)其它部分 ​

  • Page Header: 针对数据页，存储本页记录的状态信息，占56字节
  • File Header: 针对所有页，存储页的通用信息，如前后页指针构成双链表，占38字节
  • File Trailer: 针对所有页，用于同步正确性检验，占8字节，前4字节校验和，后四字节日志序列位置LSN

4、架构 ​

MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。

1)内存结构 ​

在左侧的内存结构中，主要分为这么四大块儿： Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。 接下来介绍一下这四个部分。

  • Buffer Pool

InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O。

在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等。

缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

  • free page：空闲page，未被使用。

  • clean page：被使用page，数据没有被修改过。

  • dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。

在专用服务器上，通常将多达80％的物理内存分配给缓冲池 。参数设置： show variables like 'innodb_buffer_pool_size';

  • Change Buffer

Change Buffer，更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。

INFO

与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO。

  • Adaptive Hash Index

自适应hash索引，用于优化对Buffer Pool数据的查询。MySQL的innoDB引擎中虽然没有直接支持hash索引，但是给我们提供了一个功能就是这个自适应hash索引。因为前面我们讲到过，hash索引在进行等值匹配时，一般性能是要高于B+树的，因为hash索引一般只需要一次IO即可，而B+树，可能需要几次匹配，所以hash索引的效率要高，但是hash索引又不适合做范围查询、模糊匹配等。

InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。

自适应哈希索引，无需人工干预，是系统根据情况自动完成。参数： adaptive_hash_index

  • Log Buffer

Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。

参数:

  • innodb_log_buffer_size：缓冲区大小

  • innodb_flush_log_at_trx_commit：日志刷新到磁盘时机，取值主要包含以下三个：

    • 1: 日志在每次事务提交时写入并刷新到磁盘，默认值。
    • 0: 每秒将日志写入并刷新到磁盘一次。
    • 2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次。

2)磁盘结构 ​

接下来，再来看看InnoDB体系结构的右边部分，也就是磁盘结构.

  • System Tablespace

系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)

参数：innodb_data_file_path

系统表空间默认的文件名叫 ibdata1。

  • File-Per-Table Tablespaces

    如果开启了innodb_file_per_table开关 ，则每个表的文件表空间包含单个InnoDB表的数据和索引 ，并存储在文件系统上的单个数据文件中。

开关参数：innodb_file_per_table ，该参数默认开启。

  • General Tablespaces

通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间。

sql
--  创建表空间
CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = engine_name;

-- 创建表时指定表空间
CREATE TABLE xxx ... TABLESPACE ts_name;
  • Undo Tablespaces

撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log日志。

  • Temporary Tablespaces

InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。

  • Doublewrite Buffer Files

双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。

  • Redo Log

重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时,发生错误时, 进行数据恢复使用。

以循环方式写入重做日志文件，涉及两个文件:ib_logfile0,ib_logfile1

3)后台线程 ​

前面我们介绍了InnoDB的内存结构，以及磁盘结构，那么内存中我们所更新的数据，又是如何到磁盘中的呢？ 此时，就涉及到一组后台线程，接下来，就来介绍一些InnoDB中涉及到的后台线程。

在InnoDB的后台线程中，分为4类，分别是：Master Thread 、IO Thread、Purge Thread、Page Cleaner Thread。

  • Master Thread

核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收

  • IO Thread

在InnoDB存储引擎中大量使用了AIO来处理IO请求, 这样可以极大地提高数据库的性能，而IO Thread主要负责这些IO请求的回调。

线程类型默认个数职责
Read thread4负责读操作
Wrie thread4负责写操作
Log thread1负责将日志缓冲区刷新到磁盘
Insert buffer thread1负责将写缓冲区内容刷新到磁盘

我们可以通过以下的这条指令，查看到InnoDB的状态信息，其中就包含IO Thread信息

sql
show engine innodb status \G;
  • Purge Thread

主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收。

  • Page Cleaner Thread

协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

三、Buffer Pool详解 ​

1、为什么要有 Buffer Pool？ ​

虽然说 MySQL 的数据是存储在磁盘里的，但是也不能每次都从磁盘里面读取数据，这样性能是极差的。要想提升查询性能，加个缓存就行了嘛。所以，当数据从磁盘中取出后，缓存内存中，下次查询同样的数据的时候，直接从内存中读取。

为此，Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

有了缓冲池后：

  • 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
  • 当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘。

缓冲池有多大?

Buffer Pool 是在 MySQL 启动的时候，向操作系统申请的一片连续的内存空间，默认配置下 Buffer Pool 只有 128MB 。可以通过调整 innodb_buffer_pool_size 参数来设置 Buffer Pool 的大小，一般建议设置成可用物理内存的 60%~80%。

2、Buffer Pool缓存什么 ​

InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的基本单位，一个页的默认大小为 16KB。因此，Buffer Pool 同样需要按「页」来划分。

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页，Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，接着将虚拟地址和物理地址建立映射关系。

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 undo 页，插入缓存、自适应哈希索引、锁信息等等。

为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等等。

控制块也是占有内存空间的，它是放在 Buffer Pool 的最前面，接着才是缓存页，如下图：

上图中控制块和缓存页之间灰色部分称为碎片空间。

为什么会有碎片空间呢？

每一个控制块都对应一个缓存页，那在分配足够多的控制块和缓存页后，可能剩余的那点儿空间不够一对控制块和缓存页的大小，自然就用不到喽，这个用不到的那点儿内存空间就被称为碎片了。

当然，如果你把 Buffer Pool 的大小设置的刚刚好的话，也可能不会产生碎片。

查询一条记录，就只需要缓冲一条记录吗？

不是的。当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，因为，通过索引只能定位到磁盘中的页，而不能定位到页中的一条记录。将页加载到 Buffer Pool 后，再通过页里的页目录去定位到某条具体的记录。

3、如何管理 Buffer Pool？ ​

1)如何管理空闲页？ ​

Buffer Pool 是一片连续的内存空间，当 MySQL 运行一段时间后，这片连续的内存空间中的缓存页既有空闲的，也有被使用的。

那当我们从磁盘读取数据的时候，总不能通过遍历这一片连续的内存空间来找到空闲的缓存页吧，这样效率太低了。

所以，为了能够快速找到空闲的缓存页，可以使用链表结构，将空闲缓存页的「控制块」作为链表的节点，这个链表称为 Free 链表（空闲链表）。

  • Free 链表上除了有控制块，还有一个头节点，该头节点包含链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。

  • Free 链表节点是一个一个的控制块，而每个控制块包含着对应缓存页的地址，所以相当于 Free 链表节点都对应一个空闲的缓存页。

  • 有了 Free 链表后，每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 Free 链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的控制块从 Free 链表中移除。

2)如何管理脏页？ ​

设计 Buffer Pool 除了能提高读性能，还能提高写性能，也就是更新数据的时候，不需要每次都要写入磁盘，而是将 Buffer Pool 对应的缓存页标记为脏页，然后再由后台线程将脏页写入到磁盘。

那为了能快速知道哪些缓存页是脏的，于是就设计出 Flush 链表，它跟 Free 链表类似的，链表的节点也是控制块，区别在于 Flush 链表的元素都是脏页。

有了 Flush 链表后，后台线程就可以遍历 Flush 链表，将脏页写入到磁盘。

3)如何提高缓存命中率？ ​

Buffer Pool 的大小是有限的，对于一些频繁访问的数据我们希望可以一直留在 Buffer Pool 中，而一些很少访问的数据希望可以在某些时机可以淘汰掉，从而保证 Buffer Pool 不会因为满了而导致无法再缓存新的数据，同时还能保证常用数据留在 Buffer Pool 中。

要实现这个，最容易想到的就是 LRU（Least recently used）算法。

该算法的思路是，链表头部的节点是最近使用的，而链表末尾的节点是最久没被使用的。那么，当空间不够了，就淘汰最久没被使用的节点，从而腾出空间。

简单的 LRU 算法的实现思路是这样的：

  • 当访问的页在 Buffer Pool 里，就直接把该页对应的 LRU 链表节点移动到链表的头部。
  • 当访问的页不在 Buffer Pool 里，除了要把页放入到 LRU 链表的头部，还要淘汰 LRU 链表末尾的节点。

图中：

  • Free Page（空闲页），表示此页未被使用，位于 Free 链表；
  • Clean Page（干净页），表示此页已被使用，但是页面未发生修改，位于 LRU 链表。
  • Dirty Page（脏页），表示此页「已被使用」且「已经被修改」，其数据和磁盘上的数据已经不一致。当脏页上的数据写入磁盘后，内存数据和磁盘数据一致，那么该页就变成了干净页。脏页同时存在于 LRU 链表和 Flush 链表。

简单的 LRU 算法并没有被 MySQL 使用，因为简单的 LRU 算法无法避免下面这两个问题：

  • 预读失效；

  • Buffer Pool 污染；

    (a)预读失效

什么是预读失效？

先来说说 MySQL 的预读机制。程序是有空间局部性的，靠近当前被访问数据的数据，在未来很大概率会被访问到。所以，MySQL 在加载数据页时，会提前把它相邻的数据页一并加载进来，目的是为了减少磁盘 IO。

但是可能这些被提前加载进来的数据页，并没有被访问，相当于这个预读是白做了，这个就是预读失效。

如果使用简单的 LRU 算法，就会把预读页放到 LRU 链表头部，而当 Buffer Pool 空间不够的时候，还需要把末尾的页淘汰掉。如果这些预读页如果一直不会被访问到，就会出现一个很奇怪的问题，不会被访问的预读页却占用了 LRU 链表前排的位置，而末尾淘汰的页，可能是频繁访问的页，这样就大大降低了缓存命中率。

怎么解决预读失效而导致缓存命中率降低的问题？

我们不能因为害怕预读失效，而将预读机制去掉，大部分情况下，局部性原理还是成立的。

要避免预读失效带来影响，最好就是让预读的页停留在 Buffer Pool 里的时间要尽可能的短，让真正被访问的页才移动到 LRU 链表的头部，从而保证真正被读取的热数据留在 Buffer Pool 里的时间尽可能长。

那到底怎么才能避免呢？

MySQL 是这样做的，它改进了 LRU 算法，将 LRU 划分了 2 个区域：old 区域 和 young 区域。young 区域在 LRU 链表的前半部分，old 区域则是在后半部分，如下图：

  • old 区域占整个 LRU 链表长度的比例可以通过 innodb_old_blocks_pct 参数来设置，默认是 37，代表整个 LRU 链表中 young 区域与 old 区域比例是 63:37。

划分这两个区域后，预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部。如果预读的页一直没有被访问，就会从 old 区域移除，这样就不会影响 young 区域中的热点数据。

(b) Buffer Pool污染

虽然通过划分 old 区域 和 young 区域避免了预读失效带来的影响，但是还有个问题无法解决，那就是 Buffer Pool 污染的问题。

什么是 Buffer Pool 污染？

当某一个 SQL 语句扫描了大量的数据时，在 Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了，等这些热数据又被再次访问的时候，由于缓存未命中，就会产生大量的磁盘 IO，MySQL 性能就会急剧下降，这个过程被称为 Buffer Pool 污染。

注意，Buffer Pool 污染并不只是查询语句查询出了大量的数据才出现的问题，即使查询出来的结果集很小，也会造成 Buffer Pool 污染。

比如，在一个数据量非常大的表，执行了这条语句：

sql
select * from t_user where name like "%xiaolin%";

可能这个查询出来的结果就几条记录，但是由于这条语句会发生索引失效，所以这个查询过程是全表扫描的，接着会发生如下的过程：

  • 从磁盘读到的页加入到 LRU 链表的 old 区域头部；
  • 当从页里读取行记录时，也就是页被访问的时候，就要将该页放到 young 区域头部；
  • 接下来拿行记录的 name 字段和字符串 xiaolin 进行模糊匹配，如果符合条件，就加入到结果集里；
  • 如此往复，直到扫描完表中的所有记录。

经过这一番折腾，原本 young 区域的热点数据都会被替换掉。

怎么解决出现 Buffer Pool 污染而导致缓存命中率下降的问题？

像前面这种全表扫描的查询，很多缓冲页其实只会被访问一次，但是它却只因为被访问了一次而进入到 young 区域，从而导致热点数据被替换了。LRU 链表中 young 区域就是热点数据，只要我们提高进入到 young 区域的门槛，就能有效地保证 young 区域里的热点数据不会被替换掉。

MySQL 是这样做的，进入到 young 区域条件增加了一个停留在 old 区域的时间判断。

具体是这样做的，在对某个处在 old 区域的缓存页进行第一次访问时，就在它对应的控制块中记录下来这个访问时间：

  • 如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该缓存页就不会被从 old 区域移动到 young 区域的头部；
  • 如果后续的访问时间与第一次访问的时间不在某个时间间隔内，那么该缓存页移动到 young 区域的头部；

这个间隔时间是由 innodb_old_blocks_time 控制的，默认是 1000 ms。

  • 也就说，只有同时满足「被访问」与「在 old 区域停留时间超过 1 秒」两个条件，才会被插入到 young 区域头部，这样就解决了 Buffer Pool 污染的问题。

  • 另外，MySQL 针对 young 区域其实做了一个优化，为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4 被访问了才会。

4)脏页什么时候会被刷入磁盘？ ​

引入了 Buffer Pool 后，当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，但是磁盘中还是原数据。因此，脏页需要被刷入磁盘，保证缓存和磁盘数据一致，但是若每次修改数据都刷入磁盘，则性能会很差，因此一般都会在一定时机进行批量刷盘。

可能大家担心，如果在脏页还没有来得及刷入到磁盘时，MySQL 宕机了，不就丢失数据了吗？

这个不用担心，InnoDB 的更新操作采用的是 Write Ahead Log 策略，即先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。

下面几种情况会触发脏页的刷新：

  • 当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
  • Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
  • MySQL 认为空闲时，后台线程回定期将适量的脏页刷入到磁盘；
  • MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

在我们开启了慢 SQL 监控后，如果你发现**「偶尔」会出现一些用时稍长的 SQL**，这可能是因为脏页在刷新到磁盘时可能会给数据库带来性能开销，导致数据库操作抖动。

如果间断出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。

::: important 总结

1、Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

2、Buffer Pool 以页为单位缓冲数据，可以通过 innodb_buffer_pool_size 参数调整缓冲池的大小，默认是 128 M。

3、Innodb 通过三种链表来管理缓页：

  • Free List（空闲页链表），管理空闲页；
  • Flush List（脏页链表），管理脏页；
  • LRU List，管理脏页 + 干净页，将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。

4、InnoDB 对 LRU 做了一些优化，我们熟悉的 LRU 算法通常是将最近查询的数据放到 LRU 链表的头部，而 InnoDB 做 2 点优化：

  • 将 LRU 链表 分为young 和 old 两个区域，加入缓冲池的页，优先插入 old 区域；页被访问时，才进入 young 区域，目的是为了解决预读失效的问题。
  • 当 「页被访问」且「old 区域停留时间超过 innodb_old_blocks_time 阈值（默认为 1 秒）」 时，才会将页插入到 young 区域，否则还是插入到 old 区域，目的是为了解决批量数据访问，大量热数据淘汰的问题。

5、可以通过调整 innodb_old_blocks_pct 参数，设置 young 区域和 old 区域比例。

:::

四、事务原理 ​

1、事务基础 ​

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务特性

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。

  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

我们研究事务的原理，就是研究MySQL的InnoDB引擎是如何保证事务的这四大特性的。

而对于这四大特性，实际上分为两个部分。

  • 原子性、一致性、持久性，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。
  • 隔离性是通过数据库的锁，加上MVCC来保证的。

1)redo log ​

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。

在事务提交时，会将redo log buffer中的数据刷新到redo log磁盘文件中。过一段时间之后，如果刷新缓冲区的脏页到磁盘时，发生错误，此时就可以借助于redo log进行数据恢复，这样就保证了事务的持久性。 而如果脏页成功刷新到磁盘 或 或者涉及到的数据已经落盘，此时redolog就没有作用了，就可以删除了，所以存在的两个redolog文件是循环写的。

那为什么每一次提交事务，要刷新redo log 到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘呢 ?

因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。 而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。 这种先写日志的方式，称之为 WAL（Write-Ahead Logging）。

2)undo log ​

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和 MVCC(多版本并发控制) 。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

  • Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

  • Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment回滚段中，内部包含1024个undo log segment。

2、MVCC基本概念 ​

1)当前读 ​

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

对于我们日常的操作，如：select ... lock in share mode(共享锁)，select ... for update、update、insert、delete(排他锁)都是一种当前读。

2)快照读 ​

简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

  • Read Committed：每次select，都生成一个快照读。

  • Repeatable Read：开启事务后第一个select语句才是快照读的地方。

  • Serializable：快照读会退化为当前读。

3)MVCC ​

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

接下来，我们再来介绍一下InnoDB引擎的表中涉及到的隐藏字段 、undolog 以及 readview，从而来介绍一下MVCC的原理。

3、隐藏字段 ​

创建表的时候InnoDB会自动的给我们添加三个隐藏字段及其含义分别是

隐藏字段含义
DB_TRX_ID最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
DB_ROLL_PTR回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本.
DB_ROW_ID隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

上述的前两个字段是肯定会添加的， 是否添加最后一个字段DB_ROW_ID，得看当前表有没有主键，如果有主键，则不会添加该隐藏字段。

4、undo log ​

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

版本链:

前面提到过，对记录的删改操作对应的Undo日志里有old roll pointer字段指向旧的Undo日志，从而构成一个记录的版本链。链表的头部就是当前记录的最新值。

5、readview ​

ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

ReadView中包含了四个核心字段：

字段含义
m_ids生成ReadView时系统中活跃的读写事务的id列表
min_trx_id生成ReadView时系统中活跃的读写事务最小id
max_trx_id生成ReadView时系统应分配给下一个事务的id值（最大活跃事务id+1）
creator_trx_id生成该ReadView的事务id（仅实际增删改时才分配，只读事务id默认0）

访问规则 有了ReadView后，查询过程会顺着版本链遍历，根据ReadView的访问规则，直到找到一条可访问的版本，或不含该记录。其中，trx_id为被访问版本的trx_id属性值：

条件是否可以访问说明
trx_id == creator_trx_id可以访问该版本成立，说明数据是当前事务自己更改的
trx_id < min_trx_id可以访问该版本成立，说明该版本在当前事务生成ReadView前已经提交
trx_id > max_trx_id不可以访问该版本成立，说明该版本在当前事务生成ReadView后才开启
min_trx_id <= trx_id <= max_trx_id如果trx_id在m_ids中，不可以访问；否则可以访问m_ids存在trx_id则说明该事务还是活跃的，不存在则说明该事务已提交

生成时机 对于READ_COMMITTED和REPEATABLE_READ，最大的区别在于它们生成ReadView的时机不同。也正因此，READ_COMMITTED无法禁止不可重复读问题。

  • READ_COMMITTED事务每次SELECT查询前都会生成独立的ReadView
  • REPEATABLE_READ事务只在第一次SELECT查询前生成一个共享的ReadView

另外，为了支持MVCC，insert undo日志在事务提交后即可释放，而update undo日志不能立即删除；删除时需要分两阶段，第一阶段仅做delete mark。

6、隔离级别 ​

  • RC隔离级别: RC隔离级别下，在事务中每一次执行快照读时生成ReadView。
  • RR隔离级别: RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView,那么既然ReadView都一样， ReadView的版本链匹配规则也一样， 那么最终快照读返回的结果也是一样的。

::: important 总结

所以呢，MVCC的实现原理就是通过 InnoDB表的隐藏字段、UndoLog 版本链、ReadView来实现的。而MVCC + 锁，则实现了事务的隔离性。 而一致性则是由redolog 与 undolog保证。

:::