MySQL是怎样运行的

api调用工程师
895 阅读1小时+

本文档是对《MySQL是怎样运行的》（作者：小孩子4919）的梳理总结，针对书中的重点进行列举提纲

1、初识MySQL

本章主要是对MySQL概览介绍和安装使用的介绍

1.1、MySQL启动时的参数

一般在命令行启动MySQL客户端（不是启动MySQL服务）时，需要制定参数：

mysql -u root -p

这里的-u和-p分别代表的是用户名和密码，注意这里的参数是分大小写的，除次之外还有其他的启动参数

  • -h：代表请求的MySQl服务锁在计算机的域名或IP，在本机可以默认为-hlocalhost，可以忽略
  • -P：大写P代表的是端口号

1.2、客户端和服务器的连接处理过程

主要是通过三种方式：TCP/IP、命名管道和共享内存、UNIX套接字

一条SQL语句的处理过程主要是：

  1. 连接器处理连接
  2. 查询缓存看是否命中
  3. 分析器分析语法
  4. 优化器决定具体如何执行
  5. 执行器调用存储引擎的API执行，返回执行结果

1.3、存储引擎

常见的有InnoDB、MyISAM等，常用InnoDB

存储引擎是表级别的，存储引擎的创建、查看

1.4、总结

  1. MySQL是客户端/服务器(CS)架构，用户通过客户端发送增删改查语句，服务器收到后处理并且把结果返回给客户端
  2. MySQL在bin目录下有很多可执行文件，有一些是客户端的一些是服务器的
  3. MySQL常用启动语法
  4. 客户端进程和服务器进程在通信时采用的模式
  5. 服务器执行SQL的大体流程
  6. 关于存储引擎的SQL语句

2、MySQL的调控按钮：启动选项和系统变量

本章主要介绍了MySQL的一些配置启动选项和它们的作用范围

2.1、启动选项

主要指的是在启动MySQL服务时或者客户端连接启动的时候指定的设置项，这些设置项可以在命令行上使用，也可以在配置文件中修改

在命令行上启动使用

如下这些都是我们在启动客户端时带的启动项，它们是一种简单化表示（短形式）

mysql -uroot -p123456 -hlocalhost -P3306

他们的长形式是：

mysql --user=root --password=Aa10969taka

即格式是--启动选项1[=值1] --启动选项2[=值2]

在配置文件中使用

首先要知道配置文件的默认路径，在``Windows和类UNIX`不一样，这些配置文件优先级是以最后一个定义的为准，同一个配置文件中优先级以最后出现的为准

然后知道配置文件的内容和格式：

[server]：代表的是某个组，改组下面直接定义即可，不同的启动程序对应的组不一样

[server]					# server组
option1						# 选项1不需要值
option2 = value2  # 选项2需要值

[mysqld]					# mysqld组
option1						# 选项1不需要值
option2 = value2  # 选项2需要值

# ...

如果同一个启动选项既出现在命令行中又出现在配置文件中，那么以命令行为准

2.2、系统变量

系统变量是MySQL在运行过程中会用到的许多影响程序行为的变量，比如说允许同时连入的客户端数量:max_connections

表的默认存储引擎:default_storage_engine, 查询缓存的大小:query_cache_size

可以使用show variables查看所有系统变量

大部分系统变量可以通过启动选项的方式设置：即在命令行启动或者修改配置文件，所不同的是系统变量有作用范围一说：

  • GLOBAL：全局范围，影响服务器的整体操作
  • SESSION：回话范围，影响某个客户端的操作

服务器在启动时，会将每个全局变量初始化为默认值，通过启动选项启动服务器设置的全局变量作用范围都是GLOBAL的

查看和修改不同作用范围的全局变量：P30

注意：

  • 并不是所有系统变量都有GLOBAL和SESSION的作用范围

  • 有些变量是只读的，不能设置值

  • 启动选项和系统变量的区别：

    • 启动选项是在程序启动的时候由用户传入的参数，系统变量是影响服务器运行行为的变量

    • 大部分的系统变量都可以当启动选项传入，有些启动选项也不是系统变量

    • 有些系统变量是在程序运行过程中自动生成的，不可以当作启动选项设置

2.3、状态变量

MySQL服务器程序中维护了很多关于程序运行状态的变量，它们被称为状态变量。比如：

Thread_connected: 表示当前有多少客户端与服务器建立了连接

Innodb_rows_updated: 表示更新了多少条以InnoDB为存储引擎的记录

这种变量只能读不能修改，同样也有GLOBAL和SESSION两种作用范围

2.4、总结

  1. 启动选项可以调整服务器和客户端启动后的一些行为，有两种设置方式
  2. 系统变量是服务器程序中维护的一些变量，这些变量影响着服务器的行为，作用范围有GLOBAL和SESSIOn,修改方式有两种:
    • 在服务器启动时通过添加相应的启动选项进行修改
    • 在运行期间通过SET语句修改

3、字符集和比较规则

本章主要介绍MySQL的字符集相关知识

3.1、字符集和比较规则简介

字符集是什么？有哪些？: 字符和二进制数字的映射关系 ASCII、GBK、UTF-8...

对于同一个字符集为什么有不同的比较规则？大小写

UTF-8和Unicode关系：UTF-8是Unicode字符集的一种，除此之外还有UTF-16、UFT-32

  • UTF-8使用1～4个字节表示一个字符
  • UTF-16使用2或4个字节表示一个字符
  • UTF-16使用4个字节表示一个字符

3.2、MySQL中的字符集和比较规则

MySQL中UTF-8和定义稍有不同：

  • utf8mb3: 阉割的UTF-8，使用1～3个字节表示字符，MySQL中的utf8代表utf8mb3
  • utf8mb4: 正宗的UTF-8，使用1～4个字节表示

MySQL中的字符集和比较规则的查看：P36

3.3、字符集和比较规则的应用

MySQL有4个级别的字符集（同时对应四个比较规则），分别是：服务器级别、数据库级别、表级别、列级别

查看语句：P39

字符集和比较规则的关系：

  • 只修改字符集，比较规则跟着变成修改后的字符集的默认比较规则，反之相同

4种比较规则的联系

  • 如果创建或修改列没有制定比较规则，就用表的，向上递进

3.4、总结

  1. 字符集和比较规则是什么意思
  2. MySQL中的utf8的特殊
  3. MySQL的四种字符集和比较规则，它们的关系

4、InnoDB记录存储结构

本章主要说明MySQL中InnoDB存储引擎记录的不同行的格式和物理存储规则

4.1、以COMPACT为代表的行格式

InnoDB存储引擎对于行的格式一共有4种：COMPACT、REDUNDANT、DYNAMIC、COMPRESSED，虽然是行格式，但是其作用范围为表级别的，默认为DYNAMIC，但是差别不大

COMPACT的行格式总览：P57

  • 记录的额外信息：
    • 变长字段长度列表
    • NULL值列表
    • 记录头信息
  • 记录的真实数据：
    • 自定义列
    • 隐藏列

**记录的额外信息：**服务器为了更好的管理记录而不得不添加的一些信息

  1. 变长列表：MySQL中的变长数据类型，如varchar中存储的具体字节数，以数组的形式反向被存储到该空间
  2. NULL值列表：对可以为NULL值的数据以1代表NULL，0代表不是NULL的规则，按位反向（补字节）记录在该空间
  3. 记录头：固定5字节，用于描述一些属性: P61

记录的真实数据：

  1. 自定义列的数据：从左到右正序排列记录
  2. 隐藏列：
    • row_id: 如果没有主键，自动生成的占用6字节的，作为主键用的id
    • try_id: 6字节的事务id
    • roll_pointer: 回滚指针

innoDB主键生成策略：

  1. 优先使用用户自定义的键为主键（NOT NULL UNIQUE）
  2. 如果没有自定义，选取一个(NOT NULL UNQUE)的键作为主键
  3. 自动生成row_id作为主键

4.2、REDUNDANT行格式

依葫芦画瓢

REDUNTDANT行格式总览：

  • 记录的额外信息:
    • 字段长度偏移列表
    • 记录头信息
  • 记录的真实信息:
    • 自定义列
    • 隐藏列

记录的额外信息：

  1. 字段长度偏移列表：将该记录的所有列（包括隐藏列）的信息反向存储到偏移列表中，记录的是偏移值
  2. 记录头信息：固定6字节（比COMPACT多一字节）：P65：比起COMPACT的区别：
    • 多了n_field和1_byte_offs_flag
    • 少了record_type

NULL值处理： REDUNDANT格式没了NULL值列表，怎么处理NULL呢？

  • 将列对应的偏移量值的第一位作为是否为NULL的依据

4.3、溢出列

InnoDB是以页存储记录的，默认一页为16KB，如果一个记录超过了页怎么办？

在COMPACT和REDUNDANT中，对于占用特别大的记录，在记录的真实数据处只会处理该列的一部分数据（768字节），将剩余的数据分散在其他几个页中，在记录的真实数据处用20字节记录指向这些页的地址，这些只记录溢出数据的页称为溢出页，把这个列成为溢出列

一个列在存储多少字节才会变为溢出列？，MySQL规定一个页中至少存放两条记录，如果存不下就要将该列变为溢出列

4.4、Dynamic和COMPRESSED

Dynamic是默认的行记录格式。和COMPACT很像，唯一的区别是在处理溢出列的数据时：

  • 不会在记录真实数据处存储该溢出列的真实数据的前768字节，而是将该列的全部真实数据都存到溢出页中，只记录20字节的地址

COMPRESSED不同与DYNAMIC的一点在于：

  • COMPRESSED行格式会采用压缩算法对页面进行压缩，用来节省空间

REDUNDANT是一种比较原始的行格式，它是非紧凑的，而COMPACT、DYNAMIC、COMPRESSED是较新的格式，它们是紧凑的

也就是说前者占用空间较大，后者占用空间较小

4.5、总结

  1. 页是InnoDB中磁盘和内存交换的基本单位，也是InnoDB管理存储空间的基本单位，默认大小16KB
  2. InnoDB的四种行格式、修改的语句、区别

5、InnoDB数据页面结构：存放记录

本章是研究InnoDB的数据页的结构以及记录在数据页的组织结构

5.1、数据页结构总揽

一块儿16KB的数据页可以分为多个部分，如下是它的物理结构: P72

  • File Header：文件头部，记录页的通用信息，占据38字节
  • Page Header：页面头部，记录数据页的专有信息，占据56字节
  • Infimum + Supremum：页面中最大和最小记录，是虚拟记录，占据26字节
  • User Record：用户记录，用于存储用户记录的内容，不确定
  • Free Space：空闲空间，页中尚未使用的空间，不确定
  • Page Dictionary：页目录，页中某些记录的相对位置，不确定
  • File Trailer：文件尾部，校验页是否完整，8字节

5.2、页的类型

如上用来存放记录的页是FILE_PAGE_INDEX，也就是索引页，除此之外还有：日志页、溢出页等

详细见P87

5.3、User Record、Free Space、Page Dictionary

我们自己的存储记录会存到User Record中，但是页面一开始并没有User Record部分，每插入一条记录都会从Free Space部分申请一个记录大小的空间，并将该部分变为User Record部分，当满了就没有Free Space部分了，需要去申请新的页

User Record的组织方式以及和记录头的关系：P74（重点）

Page Dictionary：P80（重点）

每个记录头信息中都有一个next record属性，从而可以使页中所有记录串联成一个单项链表

InnoDB会把页中的记录分为若干组，每个组的最后一个记录地址偏移量作为一个槽（slot），存放在Page Dictionary中，一个slot占用两字节，在一个页中根据主键查找记录是非常快的：

  1. 通过二分法确定该记录所在分组对应的slot，并找到该slot所在分组中主键值最小的记录
  2. 通过记录的next_record属性遍历该slot所在组的各个记录

5.4、Page Header

主要存储在数据页中的记录的状态信息，占据固定56字节，如：

存储数据页中已经存了多少条记录、Free Space在页面的地址偏移量、Page Dictionary中存了几个槽

Page Header的结构和描述：P85

5.5、File Header和File Trailer

File Header

文件头部，占据固定38字节，通用于任何类型的页，它描述了一些通用于任何页的信息：详见P86，如：

这个页的编号、校验和、上一个页和下一个页、当前页的类型、

FIle Trailer

文件尾部，占据固定8字节，通用于任何类型的页，

  • 前四字节：页的校验和，和File Header中的相对应，二者不同说明刷新期间发生了错误

  • 页面最后被修改对应的LSN的后4字节

5.6、总结

  1. InnoDB页面的7部分结构和各自的作用
  2. 用户记录在页面中的组织方式

6、快速查询的秘籍——B+树索引

本章主要是介绍InnoDB存储引擎的索引机制，即以页为单位的InnoDB是如何组织这些页的

6.1、索引

对于一个SQL语句SELECT * FROM user WHERE id = 3;，结合前面所学知识，MySQL如何查找这一条数据

  1. 定位到所需要的页（本章内容，需要知道页是如何组织的）
  2. 从所需要的页中查找相应的记录（见第五章）

InnoDB的数据是按照B+树组织的，每个树节点对应的物理实现是一个页，满足一个数据页中用户记录的主键值大于上一个页中用户记录的主键值，这个索引也叫做居主键索引

其中叶子结点记录的是数据项，非叶子结点记录的是对叶子结点的索引，包括：

  • 页的用户记录的最小主键值
  • 页号

一层层向上递进，顶层只有一个根结点，每次根据索引查找时，都会在一个非叶子结点内使用二分法找到下一个页面，当到达最终数据页时，也是使用二分法找到该数据

InnoDB是如何区分叶子结点和非叶子结点？使用记录头信息中的record type属性：

  • 0：代表普通用户记录
  • 1：代表目录项记录
  • 2：Infimun记录
  • 3：Supremun记录

6.2、索引注意事项

首先要明确这些概念：二级索引、回表、联合索引：P103

  1. 根结点从一开始是数据页，当满了会升级成为目录页
  2. 二级索引中除了记录索引项、页面号，还会记录主键值，也就是说对name加索引实际上是加的(name., id)
  3. 一个页面上最少要存放两条记录

6.3、MyISAM中的索引

在InnoDB中, 数据本身就是一个主键索引，但是在MyISAM中不是这样：是将数据和索引分开，也就是说所有索引都是二级索引

MyISAM也会生成主键索引，在索引的叶子结点中，存储的不是id，而是id和行号的组合，是以行号来记录的

6.4、总结

  1. InnoDB中索引结构
  2. 聚簇索引、二级索引、联合索引等概念
  3. 创建删除索引的SQL
  4. MyISAM的索引

7、B+树索引的使用

本章主要介绍索引在使用时候的原理，以及索引的一些高级知识

7.1、索引的代价

主要分为空间和时间上的代价

  • 空间：每个索引都是一棵B+树，每个结点都占据16KB

  • 时间：

    优化器决定使用哪个索引所进行的比较操作

    使用查询语句的回表操作

    执行增删操作时，不可避免的对索引的结点执行页分裂、页回收等操作，都会额外消耗时间

即，索引不是没有缺点的，索引不是越多越好

回表代价

需要知道的是，执行回表操作越多，使用二级索引的效率也就越低，某些查询宁愿使用全表扫描，比如在二级索引中查找的记录占全部记录的99%，那么这么多记录回表显然性能更浪费，这些操作都是优化器的事儿

7.2、索引的应用

7.3.1、扫描区间和边界条件

明确全表扫描的概念，索引之所以会快就是因为避免了全表扫描，让我们执行语句时对小范围的数据进行判别，如：

SELECT * FROM user WHERE id >= 2 AND id <= 100;

这条SQL明显使用聚簇索引，找到id =2的记录，然后顺着链表往后找直到超过id = 100

这个要扫描的区间称为扫描区间，id = 2 AND id <= 100称为边界条件（明确单点扫描区间的概念）

需要注意的是，

  1. 并不是所有搜索条件都可以称为边界条件
  2. 用不同的索引的扫描区间可能不同
  3. 对于字符串索引和联合索引来说，满足最左前缀原则
7.3.2、索引用于排序

由于索引本身就是排好序的，所以我们在使用的时候直接使用就可以了，需要注意的是：

  1. 根据最左前缀原则，ORDER BY后面的顺序必须满足联合索引的顺序
  2. 存在ASC，DESC混用等根据索引的原理不可用于排序的情况
7.3.3、索引用于分组

根据索引的排序原理，索引可以用在分组语句上，但也要满足最左前缀原则才可以使用

7.3.4、索引下推功能

索引下推：like 'hello%’and age > 10 检索，MySQL5.6 版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度

7.4、更好创建和使用索引

  1. 如果一个索引仅仅出现在查询中，没有在Where后，没必要因为覆盖索引而在创建索引时多加一个
  2. 索引列的类型占据空间尽量小
  3. 最左前缀原则
  4. 覆盖索引原则
  5. WHERE id + 1 = 3这样是没办法使用索引的，索引列在等号左侧单独出现
  6. 由于聚簇索引的页分裂和移动记录性能开销，最好定义主键、且最好是AUTO_INCREMENT的

7.5、总结

  1. 索引的代价
  2. 索引的应用场景
  3. 创建和使用索引的技巧

8、MySQL的数据目录

本章主要介绍MySQL数据是如何以物理文件的形式存储在计算机中

8.1、MySQL的数据目录

查看MySQL的数据目录存储位置

SHOW VARIABLES LIKE 'datadir'; 
# user/local/var/mysql

数据库的存储

创建的以数据库以同名文件夹的形式存储在数据目录中，并在文件夹内创建一个db.opt文件用于存储一些属性

表的存储

表有两部分组成：表结构的定义、表存储的数据。其中前者是在创建表时以表名.frm的形式存储在数据库子目录中

表数据因为存储引擎的不同而有所不同：

  1. InnoDB

    以前时放在一块儿的叫做系统表空间（5.6之前）

    现在是在所属数据库对应的子目录下，为每一个表创建一个独立表空间表名.ibd

    • 表名.frm 表结构定义
    • 表名.idb 数据文件（索引即数据）

  2. MyISAM

    • 表名.frm：表结构的定义

    • 表名.MYD 数据文件

    • 表名.MYI 索引文件

8.2、文件系统对数据库的影响

MySQL中的数据以文件系统的形式被存放在不同os的计算机中，自然会收到不同os文件系统对数据库的限制

  1. 数据库名称和表名称长度收到制约
  2. 特殊字符问题
  3. 文件大小受到制约

8.3、自带库简介

自带的几个存储的信息都是什么？

  1. mysql库：用户账户和权限信息，存储过程定义、一些日志等
  2. information_schema：所有数据库的信息， 有哪些表，视图，索引等，这些信息是描述性信息
  3. performance_schema：MySQL服务器运行过程的状态信息，相当于性能监控
  4. sys：通过视图的形式将infomation_schema和performance_schema组织起来，方便开发人员查看

8.4、总结

  1. MySQL如何组织到文件系统上
  2. 自带库

9、InnoDB表空间：存放页面

本章和第4章、第五章相对应，介绍存储页面的表空间组织结构，以及实现索引等细节，极其复杂

9.1、回忆

浏览本章之前回忆一遍第四章、第五章

9.2、独立表空间1

9.2.1、区的概念

如第8章所说，一个独立表空间存储的内容是一张表的内容

由于表中的页是在太多，所以有必要对它们进行更高一级的组织，所以我们引入了区、组概念

  • 区（extent）：连续64个页面就是一个区（64 * 16KB = 1MB）
  • 组：256个区被划分为一个组（256MB）

这些组的头几个页面是类似的，也就是说，这些组的第一个区的头几个页面有共通性：

组1区1 前三个页：

FSP_HDR 页、IBUF_BITMAP页、INODE页

其他组 区1 前两个页：

XDES页、IBUF_BITMAP页

下面分别介绍这些作用：

  • FSH_HDR页：登记整个表空间的一些整体属性以及本组的所有区（256个）的属性
  • IBUF_BITMAP页：存储关于Change Buffer的一些信息
  • INODE页：存储许多称为INODE Entry的数据结构
  • XDES页：extent descriptor，登记256个区的属性
9.2.2、段的概念

引入区的概念除了上述所说还有一点，再B+树索引中，我们以页为节点，同一层的叶节点形成一个双向链表，但是它们之间的实际物理磁盘距离可能会隔得很远，这样会产生很多随机IO操作，为此我们引入了区，一个区实在物理上连续的64个页

如果在使用B+树执行查询的时候，为了区分叶子节点和非叶子节点，InnoDB对叶子节点和非叶子节点进行了物理上的区别对待，也就是说，将非叶子节点和叶子节点放在了不同区，存放非叶子节点的区的集合是一个段，存放叶子节点的区的集合也是一个段，也就是说：一个索引会生成两个段，一个叶子节点段和一个非叶子节点段

对于存储比较少的表来说，一个聚簇索引远远用不到两个段（2MB）的空间，也就引入了碎片区的概念，在一个碎片区中并不是所有页都是为了存储同一个段中的内容

数据在插入时：

  1. 从某个碎片区以单个页面为单位分配空间
  2. 当某个占用32个碎片区后，以后的数据以完整的区来分配空间
9.2.3、区的分类

也可以说是区的四种状态

状态名含义
FREE空闲的区
FREE_FRAG有剩余空闲页面的碎片区
FULL_FRAG没有剩余空闲页面的碎片区
FSEG附属于某个段的区

上面三种状态的区不属于任何一个段

XDES Entry：每一个区都对应着一个这样的结构（40 Byte），记录了区的一些特性，分别为：

  • Segment ID (8 Byte)：段编号，存在的前提是该区已经是FSEG状态了
  • List Node(4 + 2 + 4 + 2 = 12 Byte)：用于连接若干个List Node
    • Prev Node Page Number 和 Prev Node ：前一个XDES Entry指针
    • Next Node Page Number 和 Next Node Offset：后一个XDES Entry指针
  • State(4 Byte)：如上的状态
  • Page State Bitmap(16 Byte)：16 * 8 = 128bit，每两个bit管理一个页（因为一个区64个页），这两个bit第一位用来表示页是否空闲，第二个bit没用到

可以看出XDES Entry是一个双向链表，那么这些区可以组成什么链表呢？

  • 对于没有附属于段的区（附属于表空间的区）来说：

    1. FREE链表：将状态为FREE的区结构连接成一个链表
    2. FREE_FRAG链表：将状态为FREE_FRAG的结构连接成一个链表
    3. FULL_FRAG链表：将状态为FULL_FRAG的结构连接成一个链表

  • 对于附属于段的区来说(FSEG状态)

    1. FREE链表：同一个段中所有页面都是空闲页面的区
    2. NOT_NULL链表：有空闲空间的区
    3. FULL链表：满了的区

也就是说，每一个索引都对应着两个段，一个段有3个链表，独立的表空间页有三个链表

为什么这么分？

初心是减少随机IO，并且不至于让数据量非常少的表浪费空间，具体看P147

9.2.4、插入数据申请页面过程
  1. 段数据较少，且看FREE_FRAG链表没有了
  2. 去FREE链表（附属于表空间的）申请一个FREE状态的区，变为FREE_FRAG状态并插入FREE_FRAG链表中
  3. 当这个区空间用完了，将之该变状态插入FULL_FRAG链表
  4. 当页面满32个了，再申请就直接一个区
  5. 再插入数据时，先看该段下的NOT_NULL链表有没有区
  6. 没有就从该段下的FREE链表申请一个改变状态并插入NOT_NULL链表
  7. 当该区满了，就将之改变状态插入FULL链表中
9.2.5、链表基节点

List Base Node数据结构，所有链表都有这样的结构，这个结构包含了链表的头节点和尾节点指针以及链表中包含了多少个节点的信息，一共16(4 + 4 + 2 + 4 + 2)字节：

  • List Length：该链表的节点数量
  • First Node Page Number 和 First Node Offset：指向头结点的指针
  • Last Node Page Numebr 和 Last Node Offset：指向尾节点的指针

将该节点放在表空间的固定位置就容易找到了

9.2.6、段的结构

像每个区都有XDES Entry结构一样，每个段都有INODE Entry结构，用来组织段

一共192字节（8 + 4 + 16 * 3 + 4 + 32 * 4 = 192 Byte），分别为：

  1. Setment Id：段编号
  2. NOT_FULL_N_USED：在NOT_NULL链表中已经使用了多少页面
  3. 3个List Base Node：对应一个段的三个链表的基节点
  4. Magic Number：标识这个INODE Entry是否已经初始化，初始化后这个值是97937874
  5. Fragment Array Entry：每个Fragment Array Entry结构都对应着一个零散的页面（碎片区），表示一个零散的页号（32个）

9.3、独立表空间2

上面说了各个结构，现在将他们在表空间的具体位置进行说明，按照组的第一个区的头几个页面不同来说明

9.3.1、FSP_HDR类型页

登记整个表空间的一些整体属性以及本组的所有区（256个）的属性

表空间中第一个组的第一个区的第一个页面：

  • File Header：文件头部，记录通用信息
  • File Space Header：表空间的一些整体属性信息，112Byte
  • XDES Entry：一个组有256个区，一个对应一个，一共有256个，占据 256* 40 Byte = 10KB
  • Empty Space：尚未使用空间，用于填充
  • File Trailer：文件尾部，校验用

File Space Header拿出来看看：具体参考P152

  • Space Id（4 Byte）：表空间ID

  • Not Used（4 Byte）：填充用

  • Size（4 Byte）：当前表空间拥有的页数

  • FREE Limit（4 Byte）：尚未被初始化的最小页号，大于等于这个页号的区的XDES Entry都还没有被加入FREE链表

  • Space Flags（4 Byte）：一些占用比较小的属性

  • FRAG_N_USED（4 Byte）：直属于表空间的FREE_FRAG链表中已使用的页面数量

  • List Base Node for FREE List （16 Byte）：直属于表空间的FREE链表的链表基节点

  • List Base Node for FREE_FRAG List （16 Byte）：直属于表空间的FREE_FRAG的链表基节点

  • List Base Node for FULL_FRAG List （16 Byte）：直属于表空间的FULL_FRAG的链表基节点

  • Next Unused Segment ID（8 Byte）：当前表空间中下一个未使用的Segment Id，创建索引用

  • List Base Node for SEG_INODES_FULL List（16 Byte）：SEG_INODES_FULL 链表的基节点

  • List Base Node for SEG_INODES_FREE List（16 Byte）：SEG_INODES_FREE 链表的基节点

9.3.2、XDES类型页

extent descriptor，登记256个区的属性

阉割版的FSP_HDR，主要需要记录改组的XDES，具体参考P154

9.3.3、IBUF_BITMAP类型页

存储关于Change Buffer的一些信息

在增删改记录时，我们需要先更新聚簇索引，再更新二级索引，这些页面在表空间随机分布，会产生随机IO，严重影响性能

InnoDB设计者设计出了Change Buffer结构，在修改非唯一二级索引页面时，如果该页面尚未加载到内存（处于磁盘中），就先将该修改暂时缓存到Change Buffer中，待服务器空闲或者该页面被加载到内存后，再将修改合并到对应页面

9.3.4、INODE类型页

存储许多称为INODE Entry的数据结构

表空间中第一个组的第一个区的第三个页面：

  • File Header：文件头部
  • List Node for INODE Page List：存储上一个INODE页面和下一个INDOE页面的指针
    • Prev Node Page Number 和 Prev Node Offset
    • Next Node Page Number 和 Next Node Offset
  • INODE列表：具体的INODE Entry 结构
  • Empty Space：尚未使用空间，无特殊意义
  • File Trailer：校验用

在一个表空间中，如果段超过85个，那么一个INDOE类型的页面不足以存储所有的段对应的INODE Entry结构，所以需要额外的INODE类型页面来存储这些结构，同样是为了方便管理这些INODE类型的页面，InnoDB将这些页面分为放在两个不同的链表：

  1. SEG_INODES_FULL链表：该链表中的INODE页面已经没有空闲空间来存储额外的INDOE Entry
  2. SEG_INODES_FREE链表：该链表的页面中还有空间来存储额外的INODE Entry结构

对应FSH_HDR的File Space Header中的两个信息，它们就是用来存储这两个链表的基节点的：

  • List Base Node for SEG_INODES_FULL List（16 Byte）：SEG_INODES_FULL 链表的基节点
  • List Base Node for SEG_INODES_FREE List（16 Byte）：SEG_INODES_FREE 链表的基节点
9.3.5、存储INODE Entry过程
  1. 先看看SEG_INODES_FREE是不是空的
  2. 如果不是空的就从该链表中获取一个节点，也就是获取到一个仍有空闲空间INODE类型的页面，然后将INDOE Entry放入到该页面中
  3. 如果该页面放完之后满了，就放入SEG_INODES_FULL链表中
  4. 如果此时再来一个INODE Entry，并且SEG_INODES_FREE是空的
  5. 从表空间FREE_FRAG链表中申请一个页面，将此链表的类型修改为INDOE，将该页面放入SEG_INODES_FREE中，将该INDOE Entry放入该页面
9.3.6、Segment Header结构的使用

一个索引产生两个段，分别是非叶子节点段和叶子节点段，每个段都有一个INODE Entry结构，如何知道某个段对应哪个INODE Entry？ 这种关系被记录在了索引页的Page Header中：

  • PAGE_BTR_ASEG_LEAT：B+树叶子节点段的头部信息，仅在B+树的根页中定义
  • PAGE_BTR_SEG_TOP：B+树非叶子节段的头部信息，仅在B+树的根页中定义

都占用10 Byte，它们其实对应的是Segment Header结构：

  • Space ID of the INODE Entry：INODE Entry结构所在的表空间ID
  • Page Number of the INODE Entry：INODE Entry结构所在页面页号
  • Byte Offset of the INODE Entry：INODE Entry结构所在页面中偏移量

PAGE_BTR_SEG_LEAT记录着叶子节点段的INODE Entry结构的地址是哪个表空间哪个页面的哪个位置，

PAGE_BTR_SEG_TOP记录着非叶子节点段的INODE Entry结构对应着哪个表空间中的哪个页面的哪个位置，

这样索引和对应的段的关系就建立起来了，注意一点：一个索引对应两个段，所以只需要在索引的根页面中记录这两个结构即可

9.4、系统表空间

和独立表空间对比，系统表空间的整体结构：

组1区1 前8个页：

除了FSP_HDR 页、IBUF_BITMAP页、INODE页这些和独立表空间相同的页之外还有：

  • SYS:insert buffer header页：存储Change Buffer的头部信息
  • INDEX:insert buffer root页：存储Change Buffer根页面
  • TRX_SYS页：事务系统相关信息
  • SYS: first rollback segment页：第一个回滚段的信息
  • SYS:data directory header页：数据字典头部信息

其他组 区1 前2个页：

XDES页、IBUF_BITMAP页，和独立表一样

除了这些不一样，系统表空间的extent 1和extent 2这两个区，这128个页面（2MB）被称为

Doublewrite Buffer双写缓冲区

数据字典

我们使用INSERT语句向MySQL插入数据时，MySQL肯定要做的是需要验证INSERT中存不存在该表，存不存在该列，除此之外还要保存很多类似的信息：

  • 某个表属于哪个表空间，表里面有多少列？列的类型？
  • 该表有多少个索引？每个索引具体信息？
  • ...

这些数据也被称为元数据，为了更好保存，InnoDB特意定义了一些列系统内部表internal system table来保存，这些系统表也被称为数据字典，系统表空间的第七个页面记录了数据字典的头部信息

9.5、总结

  1. 表空间的划分，区、组、段的概念
  2. 它们分别对应的数据结构，如XDES Entry、INODE Entry，组成部分
  3. 几个链表、它们的作用、它们在独立表空间的位置

10、单表访问方法和索引合并

本章主要介绍MySQL是执行单表查询的访问方法和一些索引的优化

10.1、访问方法

MySQL设计者将MySQL执行查询语句的方法称为访问方法（Access Method）或访问类型，同一个查询语句可以使用多种不同的访问方法来执行，常见的访问方法有：

  • const
  • ref
  • ref_or_null
  • range
  • index
  • all
  • index_merge

下面分别介绍下

10.1.1、const

通过聚簇索引或者唯一二级索引定位一条记录。如：

SELECT * FROM single_table WHERE id = 12;

由于这种查询的代价是常数级别的，所以我们称为const，但是这种查询只能是等值查询，且对于唯一二级索引来说

查询NULL值的时候，不是使用const访问方法

10.1.2、ref

搜索条件为二级索引列与常数进行等值比较，形成的扫描区间为单点扫描区间，采用二级索引来执行的访问方法称为ref

# key1上有普通索引
SELECT * FROM single_table WHERE key1 = 'abc';

由于不是唯一索引，所以查询结果可能有多个，从二级索引查询完成后在执行回表操作

注意：二级索引每获取到一条二级索引记录，就执行回表操作，而不是将主键值搜集起来一起回表

10.1.3、ref_or_null

相比于上面的ref，这种访问方法只是多扫描了一些NULL值的二级索引记录

SELECT * FROM single_table WHERE key1 = 'abc' OR key1 is NULL;

注意：值为NULL的记录会被放在索引的最左端

10.1.4、range

使用索引查询时，对应的扫描区间为若干个单点扫描区间或者范围扫描区间的访问方法称为range

注意：仅仅一个单点扫描期间区间不可以

10.1.5、index

全部扫描二级索引记录的访问方法称为index（不执行回表操作）

# key(key_part1, key_part2, key_part3)
SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

这种方法也是一种全表扫描，但是因为二级索引比起聚簇索引较小，所以性能好一点

10.1.6、all

全表扫描的访问方法叫做all

10.2、优化器和MRR

对于这样一条SQL：

# key(key1)
# key(key2)
SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

优化器会使用哪个索引呢？答案是：优化器会通过访问表中的少量数据收集的一些根据统计信息，在通过一定的算法来计算使用这两个索引的成本（哪个扫描区间更小成本越低）来决定。

一般来说，使用key1列上索引的方法是ref，key2列上索引是range，前者区间更小些

MRR：Disk-Sweep Multi-Range Read 多范围读取

MySQL的一种优化机制，对于读出来的二级索引中的主键id，将他们排序后再统一回表，但是条件要求比较苛刻

10.3、索引合并

一般来说MySQ只会为单个索引生成扫描区间，但是有时候也会为多个索引生成，这就叫做 index merge索引合并

10.3.1、Intersection（交集）索引合并

对于这样的查询SQL：

# key idx_key1 (key1)
# key idx_key3 (key3)
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

一般来说可以使用idx_key1或idx_key3两个索引。这是两个ref访问方法

也可以同时用两个索引查找，然后对id相同的记录回表，这就是索引合并的一种。但是它要求从任何一个索引中获取到的记录id必须是递增的，原因有二：

  1. 从两个有序序列中获取交集算法上较容易
  2. 或者到的交集也是递增的，回表时减少随机IO
10.3.2、Union（并集）索引合并

对于这样的查询SQL：

# key idx_key1 (key1)
# key idx_key3 (key3)
SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b';

我们使用任何一个索引的扫描区间都是全部，所以我们可以使用index merge索引合并，同时查找各自条件然后取并集，这种方式也要求每个索引查询出来的id是有序的，原因和上面相同

10.3.3、Sort-Union索引合并

Union index merge 使用条件比较苛刻，如下的SQL就不能使用：

# key idx_key1 (key1)
# key idx_key3 (key3)
SELECT * FROM single_table WHERE key1 < 'a' AND key3 > 'z';

我们可以用index merge，想将每个查询出来的根据id排序，然后在执行Union的操作

注意：有Sort-Union，没有Sort-Intersection

10.4、总结

  1. 访问方法是什么？分别解释？
  2. 索引合并方法？分别解释？

11、连接的原理

本章主要介绍MySQL中多表连接的操作和原理，以及优化

11.1、连接简介

首先创建两个表，并插入数据

CREATE TABLE t1(m1 int, n1 char(1));
CREATE TABLE t2(m2 int, n2 char(1));
INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

从本质上来说，连接就是将各个表中的记录取出来按照规则匹配，即将t1表和t2表记录连接起来组成一个更大的记录

如果不加限制条件匹配规则，就是笛卡尔积，即记录数等于两表记录数相乘，其SQL为：

SELECT * FROM t1, t2;

我们有必要定义连接规则即过滤条件，用来满足我们的业务需求，一般来说过滤条件有两种：

  • 涉及单表的条件：比如t1.m1 > 1或者t2.m2 > 2
  • 涉及多表的条件：如ti.m1 = t2.m2或者t1.n1 > t2.n2
11.1.1、连接过程

一条连接语句SQL：

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

上述SQL的过滤条件是：

  • t1.m1 > 1：单表条件
  • t1.m1 = t2.m2：多表条件
  • t2.n2 < 'd'：单表条件

这个连接执行过程如下：

  1. 确定第一个需要查询的表，这个表称为驱动表，假设为t1
  2. 从驱动表每获取到一条记录，都从t2表中查找匹配的记录（符合过滤条件的记录）

如上述的过程需要查询一次t1表，两次t2表（两条记录）

需要注意的是：每次从驱动表获取到的记录直接去被驱动表中查询，而不是搜集完成以后再查询

11.1.2、内连接和外连接

内连接：针对驱动表过滤后的某条记录，在被驱动表中没有找到与之匹配的记录，该纪录不会加入到最后结果集

外连接：针对驱动表过滤后的某条记录，在被驱动表中没有找到与之匹配的记录，该纪录也会加入到最后结果集，根据选取的驱动表不同可以分为左外连接（左表是驱动表）和右外连接（右表是驱动表）

对于外连接来说，有时候我们也不想把驱动表的全部记录加入到最后结果集，我们分了两个语句:

  • WHERE：凡是不符合WHERE条件的都记录都不会加入到最后的结果集
  • ON：无法在被驱动表中找到匹配记录时仍然会加入到最后的结果集，对应的被驱动表各个字段用NULL表示

需要注意的是：对于内连接来说，用WHERE和ON是等价的

写法：

# 内连接
SELECT * FROM t1, t2;
SELECT * FROM t1 INNER JOIN t2;

# 左外连接和右外连接
SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.m1 = t2.m2;
SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.m1 = t2.m2;

11.2、连接优化

如上所示，“驱动表只访问一次，但被驱动表却可能访问多次，且访问次数取决于对驱动表的执行单表查询后的结构集中有多少记录”这样的连接执行方式成为循环嵌套连接，这是最简单的一种连接方式，下面我们来看一下可以对它做什么优化

11.2.1、使用索引加快速度

再嵌套循环连接中要多次访问被驱动表，所以如果访问被驱动表的时候是利用了索引，可以提升性能

假设有多个索引可以选择，我们会选择代价最低的一个

11.2.2、基于块的循环嵌套连接

为了尽量减少被驱动表的访问次数，我们可以在把被驱动表的记录加载到内存时，一次性和驱动表中的多条记录进行匹配，这样就可以减少从磁盘上加载被驱动表的代价

Join Buffer：连接缓冲区，在执行连接前申请的一块固定大小的空间用来存放驱动表的结果集，在被驱动表扫描时一次性和多条驱动表记录进行匹配

这种基于Join Buffer的嵌套循环连接算法成为基于块的嵌套循环连接算法

11.3、总结

  1. 连接的原理、分类
  2. 连接的优化

12、基于成本的优化

主要深入介绍MySQL执行SQL语句成本的具体计算方式

12.1、什么是成本

成本可以理解成为代价，性能，消耗等。主要分为两个方面：

  • IO成本：将数据从磁盘加载到内存的时间成为IO成本
  • CPU成本：读取记录，检测记录是否满足搜索条件，对结果集排序这些操作所花费的CPU时间

值得了解的是：InnoDB将读取一个页面花费的成本默认为1.0，读取以及检测一条记录是否符合搜索条件的成本默认为0.2，这些数字没有单位，称为成本常数

12.2、单表查询的成本

在执行一条单表查询时，优化器的工作流程主要是：

  1. 根据搜索条件，找出所有可以使用的索引
  2. 计算全表扫描的代价
  3. 计算使用不同索引执行查询的代价
  4. 对比各种执行方案，找出成本最低的那个方案

下面以如下一条SQL语句，分别具体介绍一下每一步的操作：

# PRIMARY KEY (`id`),
# UNIQUE KEY `uk_key2` (`key2`),
# KEY `idx_key1` (`key1`),
# KEY `idx_key3` (`key3`),
# KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)

SELECT * FROM single_table WHERE
	key1 IN ('a', 'b', 'c') AND
	key2 > 10 AND key2 < 1000 AND
	key3 > key2 AND
	key_part1 LIKE '%hello%' AND
	common_field = '123';
12.2.1、找出所有可以使用的索引

索引使用的条件：只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=、<>或者LIKE操作符（前缀匹配即可使用）连接起来，就会产生一个扫描区间，就有可能使用到索引，分析上面几个搜索条件：

  • key1 IN ('a', 'b', 'c') ：可以使用idx_key1
  • key2 > 10 AND key2 < 1000：可以使用uk_key2
  • key3 > key2：不能使用
  • key_part1 LIKE '%hello%'：通配符开头不能使用
  • common_field = '123'：没有索引，不能使用

我们将一个查询中可能使用到的索引称为possible keys，本例中的possible keys是idx_key1和 uk_key2

12.2.2、计算全表扫描的代价

全部扫描就是将聚簇索引全部和搜索条件对比，查询成本 = IO成本 + CPU成本

计算全表扫描代价需要两个信息：

  • IO成本：聚簇索引占用页面数 * 1.0
  • CPU成本：该表中的记录数 * 0.2

我们可以通过以下SQL查询到相关信息

SHOW TABLE STATUS LIKE 'single_table'\G;
12.2.3、计算使用不同索引执行查询的代价

值得注意的是：使用二级索引 + 回表的计算需要两个方面的数据：扫描区间数量和回表的次数

一般来说优化器先看UNIQUE索引，所以我们先看uk_key2

uk_key2：key2 > 10 AND key2 < 1000 AND

  • 扫描区间的数量：优化器认为读取二级索引的一个扫描区间IO成本和读取一个页面的IO成本相同
  • 需要回表的次数：就是计算索引在扫描区间内包含多少条满足条件的索引记录
  • 根据这些记录的主键值到聚簇索引中执行回表操作：优化器认为每次回表操作相当于访问一个页面
  • 回表操作后得到的完整用户记录再检测其他条件是否成立

idx_key1：key1 IN ('a', 'b', 'c')

  • 扫描区间的数量：三个扫描区间，相当于加载三个页面成本
  • 需要回表次数：每个扫描区间和上面类似
  • 根据记录的主键值到聚簇索引中执行回表：和上面类似，相当于加载三个页面
  • 回表操作后得到的完整用户记录再检测其他条件是否成立
12.2.4、找出成本最低的方案

根据上面的计算，找出最低的方案即可

12.2.5、计算索引在扫描区间的记录数

InnoDB的做法是：先获取索引对应的B+树的区间最左记录和区间最右记录，然后计算中间有多少条记录，这种方式称为index dive

对于如下的SQL：

SELECT * FROM single_table WHERE key1 IN ('a', 'b', 'c', 'd', ...);

IN中有几个值就要执行几次index dive

需要注意的是：如果IN中值太多，这些扫描的成本可能就比全表扫描成本大，基于此InnoDB提供了一个变量eq_range_index_dive_limit，默认值为200。

如果超过了该值就不能使用index dive了，而是使用索引统计数据index statistics来计算

具体可以参阅：P200，简单说就是可以根据一个SQL查看某个索引中重复的数量有多少，比上全表的数量就可以得出这个索引中每个值出现的记录数，进而根据扫描区间去估计总共的记录数

12.3、连接查询的成本

连接查询的总成本 = 单次访问驱动表的成本 + 多次查询被驱动表的成本

12.3.1、条件过滤

我们将查询驱动表后得到的记录条数称为驱动表的扇出（fanout），很明显fanout越小成本越低

所以优化器首先会计算fanout，但是计算fanout并不简单，有时候需要靠"猜测"：

  • 如果使用全表扫描执行单表查询，那么计算驱动表fanout需猜测满足全部搜索条件的记录有多少条
  • 如果使用索引来执行单表查询，那么计算驱动表fanout需要猜测除了满足形成索引的扫描区间的搜索条件外，还满足其他搜索条件的记录有多少条

这个猜测过程称为条件过滤（Condition Filtering），这个过程较为复杂。可能用到索引，可能用到统计数据

12.3.2、成本分析

连接查询的总成本 = 单词访问驱动表的成本 + 驱动表扇出 * 单次访问被驱动表的成本

对于左右外连接查询，他们驱动表是固定的，但是对于内连接来说可以选择分别以不同的驱动表计算成本，进而得出最优的结果

12.3.3、多表连接的查询分析

对于两个表的内连接，有两种情况，但是对于多个表的连接就会很多(n!)

MySQL如果对每种情况都计算成本显然得不偿失，所以MySQL有很多办法来减少因计算不同连接顺序下的查询成本带来的性能消耗

  1. 提前结束某种连接顺序的成本评估

    在计算各种连接顺序之前去维护一个全局变量，表示当前最小的连接成本，如果在分析某个连接属性的过程中超过了该成本就停止分析

  2. 系统变量optimizer_search_depth

    如果连接表个数小于这个值，就全部分析，否则只针对这么多表进行分析

  3. 根据一些启发式规则

    根据经验指定的一些规则，如果不满足这些规则就不分析

12.4、调节成本常数

值得了解的是：InnoDB将读取一个页面花费的成本默认为1.0，读取以及检测一条记录是否符合搜索条件的成本默认为0.2，这些数字没有单位，称为成本常数

除了这两个还有很多成本常数，他们存储在mysql数据库的两个表：

SHOW TABLES FROM mysql LIKE '%cost%';
# server_cost：
# engine_cost

server_cost表中记录的是在server层进行的操作：连接管理、查询缓存、语法解析、查询优化

engine_cost表中记录的是在engine层进行的操作：具体数据存取

我们可以通过更新这两个表中的记录来精确控制在生成执行计划时的成本计算过程

12.5、总结

  1. 成本概念和计算方式
  2. 单表查询计算步骤及细节
  3. 多表连接查询的总体思路

14、基于规则的优化（子查询优化二三事）

本章主要介绍的是一些MySQL自动查询重写规则

14.1、条件化简

MySQL优化器会简化我们写的一些表达式，如：

  • 移除不必要的括号
  • 常量传递
  • 移除没用的条件
  • 表达式计算
  • HAVING子句和WHERE子句的合并
  • 常量表检测

14.2、外连接消除

简单来说，就是在外连接查询时，指定的WHERE子句中包含被驱动表的列不为NULL值的条件称为空值拒绝(reject-NULL)，在被驱动表的WHERE子句符合空值拒绝条件后，外连接和内连接可以相互转化，从而让优化器可以通过评估不同的连接顺序成本选择成本最低的连接顺序来执行

14.3、子查询

14.3.1、子查询及其分类

在一个查询语句中的某个位置也可以有另一个查询语句，这个出现在某个查询的某个位置中的查询就称为子查询，子查询可以在任何位置出现

  • 在SELECT子句中

    SELECT (SELECT m1 FROM t1 LIMIT 1);

  • 在FROM子句中

    SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t

  • 在WHERE或ON子句的表达式：

    SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);

  • 出现在ORDER BY 和GROUP BY子句中，无意义

按照返回结果区分：

  • 标量子查询：只返回一个单一值的子查询
  • 行子查询：返回一条记录的子查询，包含多个列
  • 列子查询：查询出一个列的数据，多条记录
  • 表子查询，子查询结果中既包含很多记录，又包含多个列

相关子查询 / 不相关子查询：如果子查询的执需要依赖外层查询的值，我们就把这个子查询称为相关子查询，否则就称为不相关子查询

# n1是t1的列，n2是t2的列
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);
14.3.2、子查询执行方式

不想关的标量子查询、行子查询的执行方式：

SELECT * FROM s1
	WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1);
  1. 单独执行外层子查询
  2. 将子查询得到结果当作外层查询参数，执行外层子查询

相关的标量子查询、行子查询的执行方式：

SELECT * FROM s1 WHERE
	key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);
  1. 先从外层查询获取一条记录
  2. 从这条记录中找出子查询涉及到的值
  3. 根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立就加入结果集
  4. 跳到步骤1，知道外层查询中获取不到记录
14.3.3、IN子查询的优化
SELECT * FROM s1
	WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

物化表

对于IN子句中的参数较多时，不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表中，这个表称为物化表或者临时表，将子查询结果集中的记录保存到临时表的过程叫做物化，这个表有两点注意项：

  1. 该临时表中的列就是子查询结果集的列
  2. 写入到该表的记录会被去重

物化表转连接

将子查询物化后，假设物化表为materialized_table，该物化表的子查询结果集列为m_val

上面的查询相当于表s1和子查询物化表materialized_table进行内连接

SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

基于此我们就可以让优化器通过不同的连接方式来计算成本从而执行查询

子查询转化半连接

能不能不进行物化操作，直接把子查询转化为连接？对于上面这条SQL:

SELECT * FROM s1
	WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

可以理解为：对于s1中的某条记录，如果能在s2表中符合条件的记录中找到一条或多条记录，这些记录的common_field值等于s1表中key1的值，那么该条s1表中的记录就会被加入到最终的结果集：

SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key1 = s2.common_field WHERE s2.key3 = 'a'

这条SQL和上条不一样在于：我们不能保证对于s1表中的某条记录在s2表中有多少条记录满足s1.key1 = s2.common_field，我们关心的只是有没有还不是有几条

基于此，我们引入了半连接的概念：对于s1表中的某条记录来说，我们只关心s2表中是否存在与之匹配的记录，而不关心有多少条与之匹配，最终结果集只保留s1表中的记录

如何实现半连接？具体：P237

  • Table pullout：子查询表上拉
  • Duplicate Weedout：重复值消除
  • LooseScan：松散扫描
  • Semi-Join Materialization：半连接物化，前文说的物化本质上就是一种半连接方案
  • FirstMatch：首次匹配

14.4、总结

  1. MySQL对用户编写的查询SQL的优化
  2. 子查询的分类
  3. IN子查询的优化、物化表、半连接

15、EXPLAIN详解

本章主要深入介绍EXPLAIN关键字，对优化器优化后产生的执行计划进行分析

查看查询执行计划的方法：

EXPLAIN (语句)

# 结果转JSON
[
  {
    "id": 1,				 # 在一个大查询语句中 每个SELECT关键字对应一个唯一id
    "select_type": "SIMPLE", # SELECT关键字对应查询的类型 
    "table": null,			 # 表名
    "partitions": null,		 # 匹配的分区信息 
    "type": null,			 # 针对单表的访问方法
    "possible_keys": null, 	 # 可能用到的索引 
    "key": null,		 	 # 实际用到的索引
    "key_len": null,		 # 实际使用的索引长度 
    "ref": null,			 # 当使用索引列等值查询时，和索引列进行等值匹配的对象信息 
    "rows": null,			 # 预估需要读取的记录条数 
    "filtered": null,		 # 针对预估的需要读取的记录，经过搜索条件过滤后剩余条数的百分比
    "Extra": "No tables used"# 额外信息
   }
]

# 查看执行计划的成本
EXPLAIN FORMAT=JSON (语句)

15.1、table

EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表该表的表名

如果一个查询中涉及多个表就对应多条记录

EXPLAIN SELECT * FROM s1 INNER JOIN s2;

15.2、id

每一张表都对应着执行计划输出的一条记录，但是如果是在同一个``SELECT中它们的id是相同的，也就是说，id用来区分不同的SELECT`语句，记录用来区分不同的表

SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2);

SELECT * FROM s1 UNION SELECT * FROM s2;

需要注意的是：

  1. 对于连接查询的执行计划，参与连接到两表分别对应一条记录，但是id相同，前后分别代表驱动表和被驱动表
  2. 查询优化器可能对涉及子查询的查询语句进行重写，从而转化为连接查询
  3. 对于包含UNION查询语句来说，可能会多一条记录，这个记录代表的是用于去重的临时表

15.3、select_type

每一个SELECT关键字代表的小查询都对应select_type属性，也就说，id对应select_type

可取的值：

  • SIMPLE
  • PRIMARY
  • ... （详见: P249）

15.4、type

表明了这个表的访问方法，除了前面10.1节讲过的单表访问方法，还有：

  • system
  • eq_ref
  • fulltext
  • ... （详见: P251）

15.5、possible keys 、 key 和 key_len

possible keys 和 key : 可能用到的索引 和 实际用到的索引

key_len 表示用到的索引列的长度:

  • 该列中每个实际数据最多占用的存储空间的长度，如INT就是4，对于变长类型的列：如utf8 varchar(100)

    存储长度 = 在该字符集下表示一个字符最多占用的字节数 * 最多的字符数

  • 如果该列允许NULL值，key_len 加上1字节

  • 如果是变长类型，key_len加上2字节

key_len的意义在于让我们在使用联合索引执行查询时，能知道优化器具体使用了涉及多少个列的搜索条件来充当形成扫描区间的边界条件

15.6、ref

当访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery中其中的一个时，ref列就是展示与索引列进行等值匹配的条件是什么， 比如是一个常数或者某个列或者是一个函数

15.7、rows

查询优化器决定使用全表扫描方式对某个表进行查询时，rows代表的就是该表的估计行数。

如果使用索引来执行查询，rows代表的预计扫描的索引记录行数

15.8、filtered

回顾12.3.1的 Conditon Filtering概念

对于单表查询来说这个列无意义，主要是在连接查询中使用

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 
	WHERE s1.common_field = 'a';

详见:P259

15.9、Extra

说明一些额外信息，我们可以通过额外信息来更准确理解MySQL是如何执行给定的查询语句，具体见P260

索引下推概念

15.10、optimizer trace

optimizer_trace是一个系统变量。有的时候用户想不明白为什么通过EXPLAIN看到的方案是这样的，optimizer trace就是用来查看优化器生成执行计划的整个过程。 查看和开启的SQL:

SHOW VARIABLES LIKE 'optimizer_trace';
SET optimizer_trace = "enabled=on";

开启后输入自己的查询语句，然后就可以从OPTIMIZER_TRACE表中查看上一个查询的优化过程：

SELECT * FROM information_schema.OPTIMIZER_TRACE;

优化过程主要有3个阶段：

  • prepare阶段
  • optimizer阶段
  • execute阶段

15.10、总结

  1. EXPLAIN语句的作用和各个字段的详细作用
  2. optimizer trace查看方法和作用

16、InnoDB的Buffer Pool

本章主要介绍Buffer Pool的概念以及原理

16.1、什么是Buffer Pool

为了缓存磁盘中的页，InnoDB在MySQL服务器启动时向操作系统申请了一块连续的内存空间，起名为Buffer Pool。默认情况下有128MB

它对应的一篇内存空间被划分为连续的若干个页面，我们将这些页面称之为缓冲页。

Buffer Pool的内部组成：

  • 控制块：每个缓冲页都有的控制信息

  • 碎片：用不到的内存空间

  • 缓冲页：每个控制块对应的缓冲页

16.2、Free 链表的管理

MySQL服务器在启动时就会申请Buffer Pool，但是此时还没有页面加载进来。

我们把所有空闲的缓冲页对应的控制块作为一个节点放在一个链表中，这个链表也成为free链表。有基节点

刚开始所有控制块都被加入了该链表中，每当需要从磁盘加载一个页面到Buffer Pool中时，就从free链表取出一个空闲的缓冲页，并把该缓冲页对应的控制块的信息填上，然后把该缓冲页对应的控制块从free链表中移除

缓冲页的Hash处理

如何知道某个页面有没有在缓冲页中?

根据表空间 + 页号作为key，用缓冲页控制块的地址作为value来创建一个hash表，在需要访问某个数据页的数据时，先从该hash表中查看有没有

16.3、Flush 链表的管理

如果我们修改了Buffer Pool中某个缓存页的数据，它就变得和磁盘上的不一样了，这样的缓存页称为脏页（dirty page）。我们当然可以每当修改完某个缓存页就立即刷新到磁盘上，但是这样会降低性能。

所以我们又创建了一个存储脏页的链表，凡是被修改过的缓存页对应的控制块都会作为一个节点被放入到这个链表中，这个链表称为flush链表，它的结构和free链表差不多。有基节点

16.4、LRU链表的管理

对于小容量的Buffer Pool，如果需要缓存的页面占用内存大小超过了Buffer Pool的大小，也就是free链表中没有多余的空闲缓冲页时，应该删除一些旧的页面。

由此我们根据最近最少使用的原则创建了LRU链表 ，用来淘汰最久未使用的缓冲页，当需要访问某个页时：

  • 如果该页不在Buffer Pool中，则从磁盘中读取加载到Buffer Pool中 ，将该缓冲页对应的控制块作为节点放入LRU链表的头部
  • 如果该页已经加载到Buffer Pool了，就直接将该页对应的控制块移动到LRU链表头部

LRU链表的存在问题

  • 预读（read-ahead）问题

    预读就是InnoDB认为执行当前请求时，可能会在后面读取某些页面，于是就预先把这些可能读取到的页面加载到Buffer Pool，预读也分为线性预读和随机预读（详见P283）

    这些预读的页面如果命中不大，会极大更新LRU链表导致整体效率降低

  • 全表扫描问题

    当使用全表扫描时，整个LRU链表都会被刷新一次，这会严重影响Buffer Pool的使用，降低命中率

解决方法

InnoDB将LRU链表按照一定比例（可调，默认old 37%）分为了两部分：

  • young 区：存储使用频率非常高的缓冲页，这一部分链表也叫热数据
  • old 区：存储频率不是很高的缓冲页，这一部分链表也叫冷数据

针对预读：

当磁盘某个页面在初次加载到Buffer Pool中的某个缓冲页时，该缓冲页对应控制块会被放入old区，这样一来预读到Buffer Pool去不进行后续访问的页面会逐渐从old区域淘汰

针对全表扫描：

对于首次加载到old区的数据页，每次进行访问时会把该页放到young区的头部。但是仍然会更新young区，所以多了一个时间间隔（innodb_old_blocks_time，默认1s），在对某个处于old区的缓冲页第一次进行访问时记录当时访问时间，如果后续的访问时间与第一次访问时间在某个时间间隔内，该页面就不会移动。这是我们全表扫描时由于第一次和最后一次访问同一个页面时间不超过1s，就不会移动old区

更进一步优化：只有被访问的缓冲页位于young区长度1/4后面时，才会被移动到LRU链表头部，处在前1/4的热带数据轻易不更新

16.5、脏页刷新

后台有线程专门负责每隔一段时间就将脏页刷新回磁盘，刷新方式有两种：

  • 从LRU链表的冷数据中刷新一部分页面到磁盘

    从链表尾部扫描，发现是脏页就刷新会内存，这种方式叫BUF_FLUSH_LRU

  • 从flush链表中刷新一部分到磁盘

    BUF_FLUSH_LIST，这个刷新的速率取决于系统的繁忙程度

除了这些，有时候因为线程刷新脏页慢，还会直接释放LRU链表尾部的未修改缓冲页，如果没有则不得已将LRU链表尾部一个脏页同步刷新到磁盘，这种方式叫BUF_FLUSH_SINGLE_PAGE

16.6、Buffer Pool其他空间管理

16.6.1、并发管理

由于多线程的Buffer Pool需要加锁处理，所以单一的Buffer Pool可能会影响请求的处理速度。

所以在Buffer Pool特别大时可以将他们拆成若干个小的Buffer Pool，每一个都成为一个实例

从而提高了并发能力

16.6.2、chunk

MySQL支持在运行过程中调整Buffer Pool的大小。由于重新申请空间大小要移动原来的数据，所以MySQL不再一次性为某个Buffer Pool实例向操作系统申请一大片连续的内存空间，而是以一个chunk为单位向操作系统申请。也就是说一个Buffer Pool由若干个chunk组成，一个chunk就代表一片连续的内存空间，里面包含了若干个缓冲页和控制块

16.7、配置Buffer Pool的注意事项

  • innodb_buffer_pool_size =

    innodb_buffer_pool_chunk_size * innodb_buffer_pool_instance

    想保证每个实例中包含的chunk数量相同

  • 启动时如果chunk_size * instance大于size，服务器会自动设置chunk_size = size / instances

查看Buffer Pool状态信息（详见P291）：

SHOW ENGINE INNODB STATUS\G;

## 
...
======================
BUFFER POOL AND MEMORY
======================
xxx

16.8、总结

  1. Buffer Pool作用
  2. free链表、flush链表、LRU链表
  3. 脏页刷新空间管理

17、事务

本章主要介绍MySQL中InnoDB的事务初步

17.1、什么是事务

transcation事务，指的是一组不可分割的原子操作，这组操作要么全部执行要么全部不执行

最经典的例子就是account表中两个人转账的操作: , 此处不在赘述

# 下面转账操作要么全部执行要么全部不执行，如果只执行一个就会出现错误
UPDATE account SET balance = balance - 10 WHERE id = 1;
UPDATE account SET balance = balance + 10 WHERE id = 2;

17.2、事务的ACID特性

17.2.1、原子性Atomicity

如上所说，将这种**"要么全部执行，要么全不做"的规则称为事务的原子性，指的是这组事务不可分割**

17.2.2、隔离性Isolation

隔离性主要指的是两个事务之间（并发），保证另一个事务的转换状态不会影响到本事务的转换状态，这个规则称为隔离性，此时要求存储引对两个并发事务操作顺序有一定规律

17.2.3、一致性Consistency

一致性主要指的是现实生活的一些约束条件，这些约束条件要反映到数据库上，如果数据库中的数据全部符合现实世界的约束，我们就说这些数据是一致的，一致体现在操作前后。比如说转账后的余额之和和转账之前的余额之和应该是一样的，并且不能出现负数等约束，保证一致性的手段有两种

  • 数据库本身为我们解决一部分，如触发器，check语句
  • 业务逻辑保障

注意：原子性和隔离性都会对一致性产生影响，但不绝对。

17.2.4、持久性Durability

指的是一个事务的操作成功之后，这个结果应当被持久保留。反映到数据库上指的是这个操作结果应该被写在磁盘上或者其他方式，不管发生任何事故，这次事务造成的影响都不应当被丢失

17.3、事务的状态

详见P299

事务是一个抽象的概念，他对应着一个或多个数据库操作，根据这些操作所执行的不同阶段吧事务大致划分成了下面几个状态：

  • 活动的(acive)：
  • 部分提交的(partially committed)
  • 失败的(failed)
  • 中止的(aborted)
  • 提交的(committed)

17.4、事务的语法

17.4.1、开启事务

可以使用以下两种语句：

# 第一种
BEGIN;
xxxxx

# 第二种
START TRANSCATION;
xxx

相较于第一种，第二种可以添加修饰符：

# 当前事务是一个只读事务，不可修改
START TRANSCATION READ ONLY;
xxx

# 当前事务是一个读写事务
START TRANSCATION READ WRITE;
xxx

# 启动一致性读
START TRANSCATION WITH CONSISTENT SNAPSHOT;
xxx

也可以添加多个修饰符，用逗号隔开

17.4.2、提交或回滚事务

提交事务：

BEGIN;
UPDATE account SET balance = balance - 10 WHERE id = 1;
UPDATE account SET balance = balance + 10 WHERE id = 2;
COMMIT;

回滚事务：

BEGIN;
UPDATE account SET balance = balance - 10 WHERE id = 1;
UPDATE account SET balance = balance + 10 WHERE id = 2;
ROLLBACK;

17.5、支持事务的存储引擎

不是所有存储引擎都支持事务，InnoDB可以支持

MySQL中有一个系统变量autocommit，用来自动提交事务，默认on

也就是说，如果不显式使用上面的事务语句，每一条语句都默认算一个事务

如果想关闭这种默认提交：

  • 可以使用上面的事务语句
  • 将autocommit设置为off

17.6、隐式提交

当如上关闭了默认提交后，有些语句会默认帮我们提交一个事务（效果等同于输入commit）

  • DDL语言，建立数据库、表、视图、存储过程等
  • 修改mysql数据中的表，使用ALTER USER、CREATE USER、DROP USER等
  • 在一个事务没有提交或者回滚时开启另一个事务，自动提交上一个事务
  • 加载数据的语句，LOAD DATA
  • 关于复制的一些语句，START SLAVE、STOP SLAVE等

17.7、保存点

在事务对应的数据库语句中打几个点，在调用rollback语句可以指定回滚到哪个点，而不是回滚到最初的原点：

BEGIN;
UPDATE account SET balance = balance - 10 WHERE id = 1;
SAVEPOINT s1; # 保存点
UPDATE account SET balance = balance + 10 WHERE id = 2;
ROLLBACK to s1; # 回滚保存点到s1

17.8、总结

  1. 事务的含义
  2. ACID
  3. 事务的语法、隐式提交和保存点

18、redo log

本章主要介绍redo log日志

18.1、什么是 redo log

redo log 叫做重做日志，指的是在一个事务执行完毕时，因为直接将结果刷新回磁盘的代价太高，并且事务所涉及的页面不连续等，所以我们在每次提交事务时，只需要把修改的内容记录一下，这个记录的日志就叫做redo log，这样做的好处是：

  • 占用空间小
  • redo log是顺序写入磁盘，使用顺序IO

18.2、redo log格式

redo log本质上记录的是事务对数据库进行了哪些修改。InnoDB对不同的场景定义了多种类型的redo log，绝大部分都有下面的通用结构：

  • type：redo log的类型
  • space ID：表空间ID
  • page number：页号
  • data：这条redo log具体内容

18.3、简单的redo log类型

回顾前面第四章记录的存储结构，如果一个表没有显式定义主键，并且没有NOU NULL UNIQUE的列，MySQL会自动添加一个row_id作为主键。

row_id的赋值方法：P310。

InnoDB将这种及其简单的redo log叫做物理日志，这种日志只需要记录一下在某个页面的某个偏移量处修改了几个字节的值，具体修改后的内容是什么，InnoDB根据在页面中写入数据的多少划分几种不同的redo log：

  • MLOG_1BYTE：表示在页面的某个便宜量处写入了1个字节的redo log类型
  • MLOG_2BYTE：表示在页面的某个便宜量处写入了2个字节的redo log类型
  • MLOG_4BYTE：表示在页面的某个便宜量处写入了4个字节的redo log类型
  • MLOG_8BYTE：表示在页面的某个便宜量处写入了8个字节的redo log类型

上文提到的MAX ROW ID就是8字节的值，这种简单的物理日志的格式是：

  • type：1/2/4/8
  • space ID
  • page number
  • offset
  • len：1 / 2 / 4 / 8
  • 具体数据

18.4、复杂的redo log类型

在执行一条INSERT语句时，需要修改的地方很多，如：

  • 修改页面中Page Direction中的槽信息
  • 修改Page Header中各种统计信息
  • 修改上一条记录的next record属性

如果用上面的简单日志格式，可能需要很多条，所以这里提出了一些新的redo log类型：

  • MLOG_REC_INSERT
  • MLOG_COMP_REC_INSERT
  • MLOG_COMP_PAGE_CREATE
  • MLOG_COMP_REC_DELETE
  • MLOG_COMP_LIST_START_DELETE
  • MLOG_COMP_LIST_END_DELETE
  • MLOG_ZIP_PAGE_COMPRESS

这些类型的redo log既包含物理层面的意思，也包含逻辑层面的意思：

  • 从物理层面看：这些日志都指明了对哪个表空间的那个页进行修改
  • 从逻辑层面看：在系统崩溃后重启时，并不能直接根据这些日志中的记载在页面的某个偏移量处恢复某个数据，而是需要调用一些函数，在执行完这些函数后才能将页面恢复成之前的样子

18.5、Mini-Transcation

18.5.1、以组的形式写入redo log

语句在执行的过程中可能修改若干个页面，由于对这些页面的修改都发生在Buffer Pool中，所以在修改完页面后需要记录相应的redo log，在执行语句的过程中产生的redo log，被InnoDB划分成若干个不可分割的组

比如说我们执行一条INSERT语句：

  • 如果插入的数据页剩余空间充足，则记录一条MLOG_COMP_REC_INSERT的日志（可能是多条）即可，这种插入称为乐观插入
  • 如果剩余空间不足，需要执行页分裂操作，还要将原来数据页一部分记录复制到新的页中，更新段、区的统计信息等，这种插入称为悲观插入，需要几十条redo log

InnoDB规定一条插入操作的一系列的redo log是原子的，即在恢复时要么全部恢复，要么全部不恢复

如何做到原子呢？

  • 如果是多条redo log，则会在该组的最后面加上一条特殊类型的redo log，该类型为MLOG_MULTI_REC_END，结构只有一个type字段代表结束
  • 如果是一条，则该redo log的type字段的第一个bit为1
18.5.2、Mini-Transcation概念

InnoDB把对底层页面进行一次原子访问的过程称为一个Mini-Transcation（MTR），如修改一次MAX ROW ID，或者像某个索引对应的B+树插入一条数据

一个事务可以包含若干个语句，一条语句又包含若干个MTR，一个MRT包含若干条redo log

18.6、redo log的写入过程

18.6.1、redo log block

InnoDB将通过MTR生成的日志放入512字节的页中，这中页称为redo log block

结构详见P320

  • log block header
    • LOG_BLOCK_HDR_NO
    • LOG_BLOCK_HDR_DATA_LEN
    • LOG_BLOCK_FIRST_REC_GROUP
    • LOG_BLOCK_CHECKPOINT_NO
  • log block body
  • log block trailer
    • LOG_BLOCK_CHECHSUM
18.6.2、redo log缓冲区

如同Buffer Pool一样，写入redo log也不是直接写在磁盘上，而是在服务器启动时和Buffer Pool一样会申请一片连续空间，这被称为redo log buffer,简称log buffer，这片连续空间被划分若干个redo log block，默认大小16MB

18.6.3、redo log写入log buffer

向log buffer写入redo log是顺序写入的，如何知道应该写在哪个bolck的那个偏移量处？

InnoDB提供了buf_free，该变量指明了后续写入的redo log应该在那个位置

前面说到每个MTR产生的日志是一个组，一个组的日志必须先暂存到一个地方，当MTR结束后将这个过程中产生的一组日志全部复制到log buffer

注意：并发事务的MTR会交替插入

18.7、redo log 文件

18.7.1、redo log刷盘时机

MTR产生的一组redo log在MTR结束后复制到log buffer，什么时候会被刷新到磁盘？

  • log buffer空间不足时
  • 事务提交时
  • 后台线程每1s都会刷一次
  • 做checkpoint时
18.7.2、redo log 文件组

MySQL的数据目录下有ib_logfile0和ib_logfile1两个文件，log buffer默认就是刷到这里面

默认是2，大小每个48MB，可以调整，整体是一个循环链表结果，从0开始写，到尾部写完继续写0文件

18.7.3、redo log文件格式

和log buffer对应，redo log文件其实也是由若干个512字节的block组成

每个文件的组成：

  • 前四个bolck（2048字节）存储管理信息
  • 后面存储log buffer中的block镜像

管理信息的组成 （4个block）详见P325：

  • log file header
  • checkpoint1
  • 没用
  • checkpoint2

18.8、log sequence number

简称 lsn，是一个用来记录当前总共已经写入的redo log量，初始值是8704

lsn的计算方法见P327

每一组由MTR生成的redo log都有一个唯一的lsn值和它对应，lsn值越小说明redo log越早

18.8.1、flushed_to_disk_lsn

redo log是先写到log buffer中，之后才会被刷到磁盘的中，所以全局变量buf_next_to_write是用来标记当前log buffer中已经有哪些被刷新到磁盘上了

lsn是表示当前系统中写入的redo log总量，这包括了写到log buffer但没有刷新到磁盘的redo log，相应地，InnoDB提出了一个表示刷新到磁盘中的redo log量的全局变量——flushed_to_disk_lsn，该值在开始和lsn相同都是8704，随着系统运行，每当有block被刷新到磁盘，就会更新flushed_to_disk_lsn的值

如果两者相同，就代表log buffer中所有的日志都被刷新到磁盘当中了

18.8.2、flush链表中的lsn

一个MTR代表的是一组redo log，它除了将这一组redo log写入log buffer，还会在MTR执行过程中修改过的页面加入到Buffer Pool中的flush链表中

flush链表中的脏页是按照第一次修改的时间进行排序的，在这个过程中会啊在缓冲页对应的控制块记录两个关于页面何时修改的值:

  • oldest_modification：第一次修改Buffer Pool中的某个缓冲页就将修改页面的MTR开始时对应的lsn写入这个属性
  • newest_modification：每修改一次页面都会将修改该页面的MTR结束后对应的lsn值写入这个属性。也就是这个属性代表最近一次修改后对应的lsn值

18.9、checkpoint

由于redo log文件是有限的，我们不得不循环使用redo log文件组中的文件。判断某些redo log是否可以覆盖就是看它所对应的脏页是否已经被刷新到了磁盘

即使MTR生成的redo log已经写入到磁盘，但是他们修改的脏页仍然运行在Buffer Pool中，所以它们的redo log也是不能被覆盖的。

InnoDB有一个全局变量checkpoint_lsn，用来表示当前系统中可以被覆盖的redo log日志总量有多少，初始值也是8704

页面a被刷新到了磁盘，mtr1生成的redo log就可以被覆盖了，所以可以进行一个checkpoint_lsn的增加操作，这个过程称为执行一次checkpoint，主要分为两个步骤：

  1. 计算当前系统可以被覆盖的redo log对应的lsn最大值是多少
  2. 将checkpoint_lsn和对应的redo log文件组偏移量以及此次checkpoint编号写到日志文件的管理信息

查看系统lsn信息

SHOW VARIABLES INNODB STATUS\G;

18.10、崩溃恢复

服务器如果不会挂掉，redo log一点用都没有，只有在出现问题需要恢复时，redo log才会派上用场

18.10.1、确定恢复的起点和终点

需要从对应的lsn值为checkpoint_lsn的redo log开始恢复

选取logfile文件中最近的一次checkpoint对应的checkpoint_lsn值以及它在redo log文件组的偏移量checkpoint_offset，这就是恢复的起点

普通block的log block header中有一个LOG_BLOCK_HDE_LEN属性用来记录当前的block使用了多少字节的空间，对于没有填满的bock，该值小于512，他就是此次恢复中最后一个需要扫描的block，也就是终点

18.10.2、如何恢复

详见P338

  • 使用hash表
  • 跳过已经刷新到磁盘中的页面

18.11、总结

  1. redo log格式，种类
  2. log buffer 结构、redo log文件组
  3. lsn相关变量作用
  4. 恢复流程

19、undo log

本章主要介绍undo log日志系统

19.1、事务Id

19.1.1、分配时机

一个事务可以是一个只读事务，也可以是一个读写事务

START TRANSCATION READ ONLY;	# 只读
START TRANSCATION READ WRITE;   # 读写

如果某个事务在执行的过程中对某个表进行了增删改查操作，那么InnoDB存储引擎就会给它分配一个独一无二的事务id：

  • 对于只读事务，只有在它第一次对某个用户创建的临时表执行增删改操作时，才会分配事务id
  • 对于读写事务，只有在它第一次对某个表执行增删改操作时才会分配事务id

注意：如果不为某个事务分配事务id，则它的事务id值默认为0

19.1.2、事务id如何生成

事务id是一个数字，它的分配策略和前面提到的row_id分配策略相似：

  • 服务器在内存中维护一个全局变量，当需要某个事务分配时就把变量的值当作事务id分配给该事务，并且将该遍历自增1
  • 每当这个变量的值为256倍数时，就会将该变量的值刷新到系统表空间中页号为5的页面中一个Max Trx Id的属性中，这个属性占据8个字节
  • 当系统下一次重启时，将这个Max Trx Id属性加载到内存中，将该值加上256之后进行分配

这样就可以保证事务id是一个递增的数字，先分配的较小后分配的较大

前面章节说到过InnoDB的行格式中有一个trx_id的隐藏列，这就是对这个聚簇索引记录进行改动的语句所在的事务对应的事务id

19.2、undo log格式

19.2.1、INSERT操作对应的undo log

如果回滚插入操作，只需要记住插入的主键信息，所以在写对应的undo log只要把这条记录主键信息记录上就好了，InnoDB设计了TRX_UNDO_INSERT_REC的undo log:

  • end of record：本条日志结束后，下条开始时的地址
  • undo type：本条日志类型，这里是TRX_UNDO_INSERT_REC
  • undo no：本条日志对应的编号，在一个事务中从0递增
  • table id：本条日志对应的记录所在表的table id
  • 主键信息：主键每个列占用存储空间大小和真实值
  • start of record：上一条日志结束后，本条开始时在页面的地址
INSERT INTO 
	undo_demo(id, key1. col) VALUES(1, 'AWM', '狙击枪'), (2, 'M416', 步枪);

意味着向聚簇索引和二级索引idx_key1中插入了2条记录，不过我们只需要针对聚簇索引来记录undo log，聚簇索引记录存放到类型为FIL_PAGE_INDEX的页面中，undo log存放到类型为FIL_PAGE_UNDO_LOG页面中

roll_pointer：本质上就是一个指针，指向记录对应的undo log

19.2.2、DELETE操作对应的undo log

InnoDB删除操作执行：

  1. delete mark：将delete_flag标识位设为1
  2. purge：事务提交后，把该记录从正常记录链表中删除，加入到垃圾链表中

在执行一条删除语句时，在删除语句所在的事务提交之前只会执行delete mark过程，只需要考虑对删除操作在阶段1所做的影响进行回滚即可，InnoDB设计了TRX_UNDO_DEL_MARK_REC：

具体看P350

  • end of record：
  • undo type：
  • undo no：
  • table id：
  • info bits：
  • trx_id：
  • roll pointer：
  • 主键各列信息：
  • len of index_col_info：
  • 索引各列信息：
  • start of record：
19.2.3、UPDATE操作对应的undo log

执行UPDATE时，更新主键与否有着截然不同的处理方案

不更新主键

又可以分为被更新的列占用的存储空间是否发生变化

  • 就地更新：更新后的列和更新前的列占据的存储空间一样大，就可以直接在原记录的基础上修改对应列的值
  • 先删除旧记录，再插入新记录：如果有任何一个被更新的列在更新前后存储空间大小发生变化，就需要先把这条旧记录删除(delete mark + purge)，然后在插入新的记录

针对不更新主键的UPDATE，InnoDB设计了TRX_UNDO_UPD_EXIST_REC的undo log，详见P355

更新主键

因为在聚簇索引中记录根据主键值排序为单链表，所以如果主键发生变化，意味着该记录需要移动位置，InnoDB分为两步处理

  1. 将旧记录进行delete mark操作（不会purge）
  2. 根据更新后的各列值创建新的纪录，将其插入到聚簇索引中
19.2.4、增删改对二级索引的影响

一个表可以有一个聚簇索引和多个二级索引，对于二级索引来说，INSERT和DELETE操作和在聚簇索引中相似，但是UPDATE操作稍微有点儿不同：

  • 如果UPDATE语句中没有涉及二级索引的列，那就不需要对二级索引执行任何操作
  • 如果UPDATE语句中涉及到了二级索引，就要进行操作：
    1. 对旧的二级索引记录执行delete mark操作
    2. 根据更新后的值创建一条新的二级索引记录，然后在二级索引对应B+树中重新定位到它的位置并插进去

19.3、通用链表结构

前面说了为什么需要undo log，以及INSERT、DELETE、UPDATE这些用来改动数据的语句都会产生什么类型的undo log，不同undo log格式差别。下面说一下这些undo log会被具体写到什么地方，在写入过程中需要注意的问题

链表基结点示意图， 具体见P358：

  • List Length
  • First Node Page Number + First Node Offset
  • Last Node Page Number + Last Node Offset

19.4、FIL_PAGE_UNDO_LOG页面

FIL_PAGE_UNDO_LOG页面是专门用来存放undo log的页面。这是它的结构：

  • File Header
  • Undo Page Header
  • undo日志
  • File Trailer

其中Undo Page Header是undo log特有的：

  • TRX_UNDO _PAGE_TYPE：本页面存储什么类型的undo log
  • TRX_UNDO_PAGE_START：本页面第一条undo log的偏移量
  • TRX_UNDO_PAGE_FREE：本页面存储最后一条undo log的偏移量
  • TRX_UNDO_PAGE_NODE：链表结点

19.5、Undo 页面链表

一个事务中多条语句，所以一个事务会生成很多undo log，这些日志在一个页面中放不下，需要放在多个页面中，这些页面通过TRX_UNDO_PAGE_NODE属性连成了链表，其中第一个结点页面称为first undo page, 其余的页面称为normal undo page。

一个事务产生的多个undo log类型可能是不一样的，所以会根据undo log的类型分为两个链表

  • insert链表
  • update链表

另外，在对普通表和临时表的记录改动时所产生的undo log要分别记录，也就是说一个事务最多有4个以Undo页面为结点组成的链表。并且这些链表是按需分配，什么时候需要什么时候分配

19.6、undo log的写入过程

复习第九章段的概念

InnoDB规定，每一个Undo 页面链表都对应着一个段，称为Undo Log Segment，也就是说链表中的页面都是从这个段中申请的，所以他们在链表的第一个页面first undo page上设计了一个Undo Log Segment Header结构，包含了该链表对应的段的Segment Header信息：

详见P364

  • TRX_UNDO_STATE
  • TRX_UNDO_LAST_LOG
  • TRX_UNDO_FSEG_HEADER
  • TRX_UNDO_PAGE_LIST

InnoDB规定，同一个事务向一个Undo 页面链表写入的undo log算一个组，在每写一组undo log时，都会在这组undo log前记录一下关于这个组的一些属性，将存这些属性地方称之为Undo Log Header

详见P366

19.7、重用Undo页面

xxx

20、事务隔离级别和MVCC

本章主要介绍事务的隔离级别和MVVC

20.1、事务的隔离级别

20.1.1、事务并发执行中遇到的一些问题
  1. 脏写：一个事务修改了另一个事务未提交修改的数据
  2. 脏读：一个事务读取了另一个事务未提交修改的数据
  3. 不可重复读：一个事务修改了另一个未提交事务读取的数据
  4. 幻读：一个事务先根据某些搜索条件查询出一些记录，在该事务未提交时，另一个事务写入了一些符合那些搜索条件的记录，第一个事务再进行相同的搜索发现和之前的结果出现了变化（幻影行）

严重性：脏写 > 脏读 > 不可重复读 > 幻读

20.1.2、SQL标准的4种隔离级别

  1. 未提交读（READ UNCOMMITTED）

  2. 提交读（READ COMMITTED）

  3. 可重复读（REPEATABLE READ）

  4. 可串行化（SERIALIZABLE）

    ​ SQL标准中规定的并发事务执行过程中可以发生的现象

隔离级别脏读不可重复读幻读
READ UNCOMMITTED👌👌👌
READ COMMITTED👌👌
REPEATABLE READ👌
SERIALIZABLE

注意：脏写都不会发生

20.1.3、MySQL中的四种隔离级别

MySQL可以支持四种隔离级别。默认隔离级别是REPEAtABLE READ，并且可以很大程度上禁止幻读

SQL设置隔离级别，详见P386

20.2、MVCC原理

20.2.1、版本链

对于InnoDB存储引擎的表来说，它的聚簇索引记录中都包含下面两个必要的隐藏列：

  • try_id：一个事务每次对某条聚簇索引改动时，都会把该事务的事务id赋值给trx_id列
  • roll_pointer：每次对某条聚簇索引进行改动时，都会把旧的版本写入到undo log中，这个隐藏列指向了历史版本，可以通过它找到该记录修改之前的版本

每对记录进行一次改动时，都会记录一条undo log，每条undo log也都有一个roll pointer，通过这个属性可以将这些undo log串成一个链表，这就是版本链。版本链的头结点就是当前记录的最新值，每个版本中还有生成该版本的事务id。我们会利用这个记录的版本链来控制并发事务访问相同记录的行为，这种机制称之为多版本并发控制（MVCC, Multi-Version Concurrency Control）

20.2.2、ReadView

对于使用READ UNCOMITTED隔离级别的事务来说，由于可以读到未提交事务的修改内容，所以直接读取最新的版本就可以了，对于SERIALIZABLE隔离级别的事务来说，InnoDB会使用锁来保证同一时刻不会出现竞争。

对于READ COMMITTED和REPEATABLE READ来说，都必须保证读到的是已经提交的事务，所以核心问题是：

需要判断版本链中那个版本才是当前事务可见的？

为此， InnoDB提出了ReadView概念，主要包括4部分内容：

  • m_ids：在生成的ReadView时，当前系统中活跃的事务id列表
  • min_trx_id：生成RV时，m_ids最小的也就是最早的事务id
  • max_trx_id：系统应该分配的下一个事务id值
  • creator_trx_id：生成该RV的事务的事务id

有了ReadView，再访问某条记录时，只需要按照以下步骤判断版本即可：详细见P391

READ COMMITTED和REPEATABLE READ的实验见P391

  • READ COMMITTED——在事务每次读取数据前都生成一个RV
  • REPEATABLE READ——在事务第一次读数据时生成一个RV，以后复用
20.2.3、二级索引和MVCC

只有在聚簇索引中才有trx_id和roll_pointer隐藏列，在二级索引中执行查询呢如何执行MVCC？

  1. 二级索引页面的Page Header有一个PAGE_MAX_TRX_ID，每当对该页面的记录执行增删改时，都会以执行操作事务id的最大值更新改值。当SELECT访问二级索引首先会看RV对应的min_trx_id是否大于改值，如果大于说明页面所有记录都对该RV代表的事务可见
  2. 如果不大于，就执行回表，到聚簇索引中去执行即可
20.2.4、MVCC小结

所谓MVCC指的就是在使用READ COMMITTED和REPEATABLE READ这两种隔离级别的事务执行普通的SELECT时，访问记录的版本链的过程

两种隔离级别的不同在于生成RV的时机的不同

20.3、总结

  1. 四种隔离级别以及解决的问题
  2. 版本链、RV、两种隔离级别下MVCC执行过程

21、锁

本章主要介绍MySQL中锁机制

21.1、解决并发事务问题的基本方式

22.1.1、写-写情况

由于写写情况如果不限制会出现脏写现象，所以我们有必要进行对并发写的记录进行加锁操作来实现事务的先后执行避免脏写。

锁本质上就是一个内存中的结构，当一个事务想对记录进行改动时，首先会看该记录有没有关联内存中的锁，没有的话就生成一个与之关联，代表加锁成功。如果这时另一个事务也要对记录进行修改，发现已经有锁关联了，这个事务也会生成一个锁结构与记录关联，不过锁结构的is_waiting为true

前面事务提交后，就会把它生成的锁结构释放，并且检测到还有锁处于等待，就将等待事务的锁结构is_waiting设为false，然后唤醒它

22.1.2、读-写或者写-读情况

读-写情况下会出现脏读、不可重复读、幻读等情况（MySQL会在第三隔离级别时解决大部分幻读）

如何避免这些情况发生？

  • 方案1：读操作使用多版本并发控制（MVCC），写操作加锁
  • 方案2：都加锁

其中事务利用MVCC进行的读操作也称为一致性读（Consistent Read），或者一致性无锁读

22.2、锁的分类

事务用加锁的方式解决并发问题问题时，既要允许读-读不受影响，又要让写-写、读-写相互阻塞，有必要对锁进行分类：

  • 共享锁：读锁、S锁，事务在读一条记录时需要先获取记录的S锁
  • 排他锁：写锁、X锁，事务在改动记录时需要先获得记录的X锁
# 也可以手动控制加什么锁
SELECT * FROM user IN SHARE MODE;	# S
SELECT * FROM user FOR UPDATE;		# X

写操作获取锁情况：P405

锁的粒度

表锁和行锁

**意向共享锁（IS）和意向排他锁（IX）**都是属于表级锁，它们的提出仅仅是为了在之后加真正的表级锁（S或者X）时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录

22.3、MySQL中InnoDB的行锁和表锁

22.3.1、InnoDB的表锁

表级别的XS锁：在对某个表中记录进行增删改查时，InnoDB不会为这个表添加表级别的S锁或者X锁。但是会加上IX或者IS意向锁。但是对表进行增删改查和对该表执行DDL语句会互现阻塞，这个是由于Server层的元数据锁。所以InnoDB的表锁有点鸡肋。

AUTO_INC锁：在使用AUTO_INCREMENT修饰列后插入记录时，如果不指定系统就会自动赋给它递增的值

这个操作可以有两种方式实现：

  • 方案1：采用AUTO_INC锁，这样一个事务在持有AUTO_INC锁时，其他事务的插入语句都会被阻塞
  • 方案2：采用一个轻量级锁，在插入时获取轻量级锁，然后再生成本次的值后就释放掉

这两种方案可以使用其中一个，也可以混用，一般在插入确定数量时使用轻量级锁，性能高

22.3.2、InnoDB的行锁

Record Lock：普通的记录锁

Gap Lock：间隙锁

Next-Key Lock：前两种混合

Insert Intention Lock：一个事务在插入一条记录时，需要判断插入位置是否已经被其他事务加了gap Lock或者Next-Key Lock，有的话就需要阻塞等待，这个事务也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是目前是等待状态。这种类型的锁叫做插入意向锁(Insert Intention Lock)。这种结构并不会阻止别的事务继续获取该记录上的任何类型的锁

隐式锁：Innodb采用乐观插入的方式，所以在做insert操作时不会进行显示加锁，也就是不会生成lock_t锁结构，这样可以极大的减少锁开销，提升整体的性能。如果没有显示的行锁，该如何保证事务插入的正确性呢？

在事务1提交之前，事务2是不会报错主键冲突的，会陷入等待的状态，那么这种阻塞是如何形成的？就是隐式锁。具体P416

22.4、InnoDB锁的内存结构

如果执行这样的SQL：

SELECT * FROM hero LOCK IN SHARE MODE;

InnoDB存储引擎的锁结构：详见P417

  • 锁所在的事务信息：一个锁对应一个事务，这里记载该锁对应的事务信息
  • 索引信息：对于行级锁，需要记录加锁的记录属于哪个索引
  • 表锁 / 行锁信息：表锁记录的是对哪个表加的锁，行锁记录的是下面三个信息：
    • Space Id：记录所在的表空间
    • Page Number：记录所在的页号
    • n_bits：行级锁一条记录对应着一个比特，一个页面包含多条记录，用不同的比特却分到底是对哪条记录加了锁，为此在行级锁结构末尾放置了一堆比特，这个属性用来说明有多少比特
  • type_mode：32比特的数，划分为如下三部分：
    • lock_mode：锁模式（IX、IS、X、S、AUTO_INC）
    • lock_type：锁类型（表、行）
    • rec_lock_type：行锁具体类型（普通锁、Next-Key、gap、插入意向）
  • 其他信息：哈希表和链表来管理锁结构
  • 一堆比特位：行级锁独有的，一个bit对应一个记录

22.5、语句加锁分析

22.5.1、普通的SELECT语句

在不同的隔离级别下，普通SELECT语句具有不同表现：

  • READ UNCOMMITTED：不加锁，直接读最新版本
  • READ COMMITTED：不加锁，每次执行语句前都生成一个RV
  • REPEATABLE READ：不加锁，只在第一次执行语句时生成RV
  • SERIALIZABLE：在这种情况下分情况：
    • 系统变量autocommit = 0，即禁用自动提交，普通的SELECT也会转为SELECT... IN SHARE MODE，也就是读取记录前先获得S锁
    • autocommit = 1，启动自动提交，普通SELECT不加锁，只是利用MVCC生成一个RV来读取记录。因为启动自动提交意味着一个事务只有一条语句，不会出现不可重复读、幻读

注意：InnoDB不能完全禁止幻读现象：P424

22.5.2、锁定读
SELECT ... LOCK IN SHARE MODE;
SELECT ... FOR UPDATE;
UPDATE ...;
DELETE ...;

前两种语句时规定的锁定读语法格式，后两种由于在执行过程中首先需要定位到被改动的记录并给记录加锁，也可以认为是一钟锁定读

匹配模式和唯一性搜索概念：P425

语句在执行过程中可能需要访问多个扫描区间的记录，在为这些记录加锁时也会受到很多条件制约：

  • 事务隔离级别
  • 语句执行时使用的索引类型
  • 是否是精确匹配
  • 是否是唯一性搜索
  • 具体执行的语句类型

需要注意的是：事务在执行过程中所获取的锁一般在事务提交或回滚时才会释放，但是在前两个隔离级别下，某些情况也会提前将不符合搜索条件记录上的锁释放掉。

主要步骤如下：

  1. 快速定位到B+树该扫描区间的第一条记录，把该记录作为当前记录
  2. 为当前记录加锁
  3. 判断索引条件下推是否成立（二级索引），判断当前记录是否符合扫描区间的边界条件
  4. 执行回表操作（二级索引）
  5. 判断边界条件是否成立
  6. server层判断其他条件是否成立
  7. 获取当前记录所在单向链表的下一条记录，将其作为新的当前记录，返回第二步
avatar
文章
阅读
粉丝