跳至主要內容

MySQL高级二

RuyiWei...大约 40 分钟数据库mysql

MySQL高级二

一、锁

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

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

1、全局锁

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

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

语法:

-- 加锁
flush tables with read lock ;

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

-- 释放锁
unlock tables ;

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

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

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

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

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都互斥

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

 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中，再将合并后的数据刷新到磁盘中。

相关信息

与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘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 语法创建通用表空间，在创建表时，可以指定该表空间。

--  创建表空间
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信息

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 污染。

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

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 日志的大小。

总结

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的版本链匹配规则也一样， 那么最终快照读返回的结果也是一样的。

总结

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

0  字
评论
  • 按正序
  • 按倒序
  • 按热度
Powered by Waline v3.2.0
默认页脚
Copyright © 2024 RuyiWei