时间戳

首页 > 技术文章 > 深入了解MySQL

深入了解MySQL

changtong1819 2021-09-19 23:15 原文

MySQL

架构

日常使用 MySQL 的情景一般是这样的：

  1. 启动 MySQL 服务器程序。
  2. 启动 MySQL 客户端程序并连接到服务器程序。
  3. 在客户端程序中输入一些命令语句作为请求发送到服务器程序，服务器程序收到这些请求后，会根据请求的内容来操作具体的数据并向客户端返回操作结果。

运行着的服务器程序和客户端程序本质上都是计算机上的一个进程，所以客户端进程向服务器进程发送请求并得到回复的过程本质上是一个进程间通信的过程

服务器请求处理流程如下：

image-20210809135845009

截止到服务器程序完成了查询优化为止，还没有真正的去访问真实的数据表， MySQL 服务器把数据的存储和提取操作都封装到了一个叫 存储引擎 的模块里。我们知道 表 是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是 存储引擎 负责的事情。为了实现不同的功能， MySQL 提供了各式各样的 存储引擎 ，不同 存储引擎 管理的表具体的存储结构可能不同，采用的存取算法也可能不同。

字符集

MySQL 中的 UTF-8：

  • utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。
  • utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

注意：在 MySQL 中 utf8 是 utf8mb3 的别名，所以之后在 MySQL 中提到 utf8 就意味着使用1~3个字节来表示一个字符，因此在中文使用场景下，我们一般都是使用 utf8mb4，但是一个字符所用的最大字节长度会影响数据库的存储和性能，所以具体使用结合具体场景分析

字符集和比较规则：

  • 字符集：字符与二进制数据的映射，也就是 编码规则

  • 比较规则：比较规则 的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中，所以有时候也称为 排序规则

每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则

MySQL 有4个级别的字符集和比较规则，分别是：

  • 服务器级别：读取配置文件中的参数设置

  • 数据库级别：创建和修改数据库的时候可以指定该数据库的字符集和比较规则

    创建数据库时如果不指定字符集和比较规则，将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则

    CREATE DATABASE 数据库名
 	 [[DEFAULT] CHARACTER SET 字符集名称]
	 [[DEFAULT] COLLATE 比较规则名称];
ALTER DATABASE 数据库名
	 [[DEFAULT] CHARACTER SET 字符集名称]
	 [[DEFAULT] COLLATE 比较规则名称];

  • 表级别：在创建和修改表的时候指定表的字符集和比较规则

    如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则

    CREATE TABLE 表名 (列的信息)
	 [[DEFAULT] CHARACTER SET 字符集名称]
	 [COLLATE 比较规则名称]]
ALTER TABLE 表名
	 [[DEFAULT] CHARACTER SET 字符集名称]
	 [COLLATE 比较规则名称]

  • 列级别：同一个表中的不同的列也可以有不同的字符集和比较规则

    如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规
    则作为该列的字符集和比较规则

    CREATE TABLE 表名(
	 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
	 其他列...
);
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

  • 总而言之就是从上到下，不指定就默认用上一级的字符集和比较规则

数据接收到返回过程的字符集转化：

image-20210809130448091

由于上面发生了多次字符集转换，因此容易导致字符串转换异常，所以我们通常都把 character_set_client 、character_set_connection、character_set_results 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换
为了方便我们设置， MySQL 提供了一条非常简便的语句：
SET NAMES 字符集名;
这一条语句产生的效果和我们执行这3条的效果是一样的：
SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;

InnoDB 记录结构

数据记录是指对应于数据源中一行信息的一组完整的相关信息

InnoDB 页

InnoDB 将数据存储在磁盘中，但是对于数据的各种操作是在内存中进行的，涉及到频繁的读写内存、磁盘的操作，所以 InnoDB 将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB 中页的大小一般为 16KB 也就是说，在这种情况下，一次最少把内存中的 16KB 内容刷新到磁盘中

InnoDB 行

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为 行格式 或者 记录格式

我们可以在创建或修改表的语句中指定 行格式 ：

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称

InnoDB 目前有四种 行格式：

  • Compact

    image-20210809143026399

    Compact行格式中，一条完整记录分为 额外信息 和 真实数据 两部分

    • 额外信息

      1. 变长字段长度列表：我们常使用一些变长数据类型，例如 VARCHAR ，这就需要使用该列表来记录这些变长数据类型真正占用的字节数

        所以说这种行格式下 MySQL 的变长数据类型存储时占用的空间还包括这一块

        此外需要注意：

        • 变长字段长度列表中只存储值为 非NULL 的列内容占用的长度，值为 NULL 的列的长度是不储存的
        • 字长列表是按数据列逆序存放的
        • 对于 CHAR(M) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表

      2. NULL 值列表：把值为 NULL 的列统一管理起来，存储到 NULL 值列表，如果没有允许为 NULL 的列，那么该列表也不存在

        列表中存储一个二进制位，1 和 0 分别对应 NULL 和 非NULL

        依旧是按照列顺序的逆序排序

        列表长度为字节数的整数倍，即 8*n ，高位补零

      3. 记录头信息：由固定五字节组成

        image-20210809144837611

        都是一些标志信息，详细描述如下：

        名称 大小 描述
        delete_mask 1 标记该记录是否被删除
        min_rec_mask 1 B+树的每层非叶子节点中的最小记录都会添加该标记
        n_owned 4 表示当前记录拥有的记录数
        heap_no 13 表示当前记录在记录堆的位置信息
        record_type 3 表示当前记录的类型， 0 表示普通记录， 1 表示B+树非叶子节点记录， 2 表示最小记录， 3表示最大记录
        next_record 16 表示下一条记录的相对位置

    • 真实数据：记录的真实信息处理包含我们的列数据外，还会添加一些默认列（隐藏列）

      这里就不得不提到 MySQL 的主键生成策略了（准确说是 InnoDB）：

      如果用户没有定义主键，则选取一个 Unique 键作为主键，如果表中连 Unique 键都没有定义的话，则 InnoDB 会为表默认添加一个名为 row_id 的隐藏列作为主键，当然，设置了主键的话就不会创建这个隐藏列了

  • Redundant

    这个比较老了，和 Compact 差不多，略过

    image-20210809150044957

    注意：对于 Compact 和 Reduntant 行格式来说，如果某一列中的数据非常多的话（比如在某条数据的某一列存个8KB的数据，那肯定会溢出，一般一页也就16KB），在本记录的真实数据处只会存储该列的前 768 个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做 行溢出 ，存储超出 768 字节的那些页面也被称为 溢出页

  • Dynamic MySQL5.7 的默认行格式

  • Compressed 这两个行格式依旧和 Compact相似，不同之处在于这俩格式处理行溢出时不会再分开存数据了，而是把溢出字段的全部数据都放新一页中，Compressed 会对页面进行压缩

InnoDB 数据页结构

此处的数据页是指存放我们表中记录的那种类型的页，官方称这种存放记录的页为索引（ INDEX ）页

数据页结构

数据页代表的这块存储空间可以被划分为多个部分，不同部分有不同的功能，各个部分如图所示：

image-20210809170134756

从上面的结构图可以看出，一个 InnoDB 数据页被划分成了 7 个部分

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

记录在页的存储

页空间并不是一开始就划分好的，我们需要存储的数据是要按照对应的行结构成为一条记录然后存放在 User Records中，这部分空间一开始是没有的的，因此我们存放一行记录就占用一行空闲空间，以此不断占用 Free Space ，存放了数据的空间最终就成为 User Records

在行结构时提到过 行溢出 以及 溢出页 概念就是发生在 用户记录 这部分空间

User Records 中记录的表示：

image-20210809233014703

通过上面的图示我们就可以再来了解 行结构 中的额外信息都有啥作用了：

  • delete_mask 标记当前记录是否被删除，也就是说记录被删除时并不会立即抹去空间，而是改变这个标记，新数据添加时直接覆盖

  • min_rec_mask B+树的每层非叶子节点中的最小记录都会添加该标记

  • n_owned

  • heap_no 表示当前记录在本 页 中的位置

    InnoDB 自动给每个 页 里加入了两个记录（伪记录），分别是 最小记录 最大记录，这两条记录的 heap_no 字段的值分别为 0 和 1 ，也就是说位于最前面，由于这两条记录不是我们自己定义的记录，所以它们并不存放在 页 的 User Records 部分，他们被单独放在一个称为 Infimum + Supremum 的部分

    那么记录之间是怎么比较大小的呢？对于一条完整的记录来说，比较记录的大小就是比较 主键 的大小

  • record_type 表示当前记录的类型，一共有 4 种类型的记录， 0 表示普通记录， 1 表示B+树非叶节点记录， 2 表示最小记录， 3 表示最大记录

  • next_record 表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量

    比方说第一条记录的 next_record 值为 32 ，意味着从第一条记录的真实数据的地址处向后找 32 个字节便是下一条记录的真实数据，下一条记录 指得并不是按照我们插入顺序的下一条记录，而是按照 主键值 由小到大的顺序的下一条记录。而且规定 Infimum 记录（也就是最小记录） 的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum 记录（也就是最大记录）

因此，我们的记录在页中按照主键从小到大的顺序形成了一个单链表

image-20210810005311492

既然是链表形式，那么记录的插入删除就很好理解了，节点之间通过 next_record 指针连接起来，删除记录就是将其前一个节点的 next_record 指向其后一个节点，注意，指针指向的是下一个记录的真实数据的起始位置，那么有意思的来了，前面我们提到的行头信息的列表都是倒序放置的，而真实数据是正序的，指针指向了中间，那么我们向左或向右都能按正序读取到数据

页目录

为了方便查找数据，InnoDB 将记录进行分组并设计了页目录：

  1. 将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组
  2. 每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的 n_owned 属性表示该记录拥有多少条记录，也就是该组内共有几条记录
  3. 将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近 页 的尾部的地方，这个地方就是所谓的 Page Directory ，也就是 页目录

image-20210810011240709

所以在一个数据页中查找指定主键值的记录的过程分为两步：

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

页面头部

页面头部用来存放 数据页 的一些状态信息

image-20210810011835691

  • PAGE_DIRECTION
    假如新插入的一条记录的主键值比上一条记录的主键值大，我们说这条记录的插入方向是右边，反之则是左边。用来表示最后一条记录插入方向的状态就是 PAGE_DIRECTION
  • PAGE_N_DIRECTION
    假设连续几次插入新记录的方向都是一致的， InnoDB 会把沿着同一个方向插入记录的条数记下来，这个条数就用 PAGE_N_DIRECTION 这个状态表示。当然，如果最后一条记录的插入方向改变了的话，这个状态的值会被清零重新统计。

页通用属性

File Header 与 File Trailer
页通用属性，例如页的校验和、页号、类型、属于哪个表空间等等
通过页号信息（上一页、下一页的页号将不同的页组成双向链表）
大致了解即可，详细信息略过！

B+树索引

前面我们了解了 数据页 和 行结构 ，我们可以有以下认知：

  • 各个数据页可以组成一个 双向链表
  • 每个数据页中的记录会按照主键值从小到大的顺序组成一个 单向链表
  • 每个数据页都会为存储在它里边儿的记录生成一个 页目录
  • 通过主键查找某条记录的时候可以在 页目录 中使用二分法快速定位到对应的槽
  • 遍历该槽对应分组中的记录即可快速找到指定的记录

可以看出，通过页目录我们还是能比较方便地根据主键来查询记录的，通过二分法快速定位到槽，再遍历找出目的记录，可是非主键查找呢？还有这里的页目录是定位页内数据，那么怎么找到这个页呢？如果都是遍历所有页和所有记录，那效率会很低，因此，索引 出现了！

索引

引言：
前面我们的页目录将数据根据主键划分为 槽 进行分组，然后我们通过二分法能够快速找到页中的数据，但是我们只有页空间是连续的，我们想要定位到页只靠前后页码将页串起来形成的双向链表肯定是不够的，因此我们又可以借助 页目录 这种形式给每个页定义一个目录单独存放，这样我们查找页之前先找目录，至于目录的形式，我们可以将每一个目录项定义为对应页码和其最小主键值的组合，这样又可以根据二分法查找出记录到底存在于哪一页了！那么对于这样一个页的目录，我们就可以给它取个名字，叫做 索引，当然，这只是个假设，并非数据库中真正运用的索引方式。

正题：
InnoDB 复用了存储用户记录的数据页来存储目录项，我们称其为 目录项记录 ，使用了 record_type 属性来区分 用户记录 和 目录项记录

record_type 属性取值

  • 0：普通的用户记录
  • 1：目录项记录
  • 2：最小记录
  • 3：最大记录

所以我们的数据页变成了下面这种样式：

image-20210810180508686

  • 目录项记录 的 record_type 值是1，而普通用户记录的 record_type 值是0。
  • 目录项记录 只有主键值和页的编号两个列，而普通的 用户记录 的列是用户自己定义的，可能包含很多列，另外还有 InnoDB 自己添加的 隐藏列

那么问题又来了，我的 目录项记录 也太多了，一页装不下怎么办？没办法，我目录页也用多个，然后，我们又要想办法快速找到我们的目录页，因此，又要给目录页再来个级别更高的目录页来定位它，因此，俄罗斯套娃开始了······现在，我们的数据页变成下面这样了

image-20210810181219299

而上面这样的数据结构，我们就称其为 B+ 树

不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到 B+ 树这个数据结构中了，所以我们也称这些数据页为 节点 。从图中可以看出来，我们的实际用户记录其实都存放在 B+ 树的最底层的 节点 上，这些节点也被称为 叶子节点 或 叶节点 ，其余用来存放 目录项 的节点称为 非叶子节点 或者 内节点 ，其中 B+ 树最上边的那个节点也称为 根节点 。

一般情况下，我们用到的 B+ 树都不会超过4层，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），又因为在每个页面内有所谓的 Page Directory （页目录），所以在页面内也可以通过二分法实现快速定位记录。

聚簇索引

对于上面的原意是用来存储数据的 B+ 树来说，其本身就是一个 目录 或者是 索引
他有两个特点：

  1. 使用 记录 的主键值的大小进行 记录 和 页 的排序

    • 页内的 记录 是按照主键的大小顺序排成一个单向链表。
    • 各个存放用户 记录 的 页 也是根据页中用户记录的主键大小顺序排成一个 双向链表。
    • 存放 目录项记录 的 页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

  2. B+ 树的叶子节点存储的是完整的 用户记录

    所谓完整的 用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

具有这两种特性的 B+ 树称为 聚簇索引 ，所有完整的用户记录都存放在这个 聚簇索引 的 叶子节点 处

InnoDB 存储引擎会自动的为我们创建 聚簇索引。需要注意的是：在 InnoDB 存储引擎中， 聚簇索引 就是数据的存储方式（所有的用户记录都存储在了 叶子节点 ），也就是所谓的索引即数据，数据即索引。

在 InnoDB 中，我们的表的存在形式就是以聚簇索引的方式存在的，这也是为什么一定要主键（哪怕没有主键存储引擎都会自动创建一个隐藏列作为主键列）。

二级索引

聚簇索引 只能是在查询主键值时才能发挥出索引的作用，但是通常我们需要其它字段进行查询，那么我们就需要给这个字段另外再建立索引了

与 聚簇索引 不同的是，我们新的 B+ 树索引在叶子节点的页空间中的存放记录不会再包含所有的列了（没必要所有的索引都存放全表数据，浪费空间），只会存放 索引字段列 和 主键列，并且 目录项记录 中不再是 主键+页号 的搭配，而是变成了 索引字段列+页号

回顾一下，目录项中的记录格式大概为 100页 + 100，即第一百页的对应字段最小值为100

所以，现在我们通过非主键的索引字段来进行数据查询过程又变了：

  1. 确定 目录项记录 页

    根据 根页面 也就是最高的一级目录，可以快速定位到 目录项记录 所在的 页

  2. 通过 目录项记录页 确定到数据真实存在的 数据页

  3. 根据 页 中的页目录又迅速定位到包含索引字段和主键的 记录

  4. 根据对应的主键去 聚簇索引 查询出完整记录（回表）

由于上述过程，我们称这样的索引为 二级索引

联合索引

我们可以同时为多个列建立索引，称为 联合索引

例如我们为 c2 和 c3 列建立的索引如图所示：
image-20210811213818110

可以看出，叶子节点存放的记录由 c2、c3 和主键 c1 组成，其本质还是个二级索引，不过排序规则变成了以 c2 和 c3 的大小，注意这与分别为 c2 c3 建立索引是不一样的

扩展-MyISAM 索引

我们知道 InnoDB 中索引即数据，也就是聚簇索引的那棵 B+ 树的叶子节点中已经把所有完整的用户记录都包含了，而 MyISAM 的索引方案虽然也使用树形结构，但是却将索引和数据分开存储

创建/删除索引

InnoDB 和 MyISAM 会自动为主键或者声明为 UNIQUE 的列去自动建立 B+ 树索引，但是如果我们想为其他的列建立索引就需要我们显式的去指明

为啥不自动为每个列都建立个索引呢？
每建立一个索引都会建立一棵 B+ 树，每插入一条记录都要维护各个记录、数据页的排序关系，这是很费性能和存储空间的

创建索引

-- 建表时创建索引
CREATE TALBE 表名 (
    各种列的信息 ··· ,
    [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)

-- 修改表结构添加索引
ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列)

-- 删除索引
ALTER TABLE 表名 DROP [INDEX|KEY] 索引名

B+树索引使用

回顾 B+ 树索引：

  • 每个索引都对应一棵 B+ 树， B+ 树分为好多层，最下边一层是叶子节点，其余的是内节点。所有 用户记录 都存储在 B+ 树的叶子节点，所有 目录项记录 都存储在内节点
  • InnoDB 存储引擎会自动为主键（如果没有它会自动帮我们添加）建立 聚簇索引 ，聚簇索引 的叶子节点包含完整的 用户记录
  • 可以对需要的列建立 二级索引 ， 二级索引 的叶子节点包含的用户记录由 索引列 + 主键 组成，所以如果想通过 二级索引 来查找完整的用户记录的话，需要通过 回表 操作，也就是在通过 二级索引 找到主键值之后再到 聚簇索引 中查找完整的用户记录。

索引的代价

索引是个好东西，我们业务中的一切操作如果不能走索引的话会非常慢，但是索引也是有代价的：

  • 空间上的代价
    每建立一个索引都要为它建立一棵 B+ 树，每一棵 B+ 树的每一个节点都是一个 数据页，一个页默认会占用 16KB 的存储空间，一棵很大的 B+ 树由许多数据页组成，占用空间大
  • 时间上的代价
    每次对表中的数据进行增、删、改操作时，都需要去修改各个 B+ 树索引，消耗性能

所以说，一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。因此我们要尽可能利用好索引，避免索引失效，不建立非必要的索引。

适用条件

全值匹配

如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配

建立一个包含 name birthday phone 三个字段的索引，那么当我们执行 SQL ：select * from table where name = xx and birthday = xx and phone = xx 时查询过程为：

  • 因为 B+ 树的数据页和记录先是按照 name 列的值进行排序的，所以先可以很快定位 name 列的值是 xx 的记录位置
  • 在 name 列相同的记录里又是按照 birthday 列的值进行排序的，所以在 name 列的值是 xx 的记录里又可以快速定位 birthday 列的值是 xx 的记录
  • 如果很不幸， name 和 birthday 列的值都是相同的，那再来按 phone 列查找

那么问题又来了，既然索引的存放形式还有个先后顺序，那我查询的时候字段顺序反了会走索引吗？
当然会，MySQL 有一个叫 查询优化器 ，会分析这些搜索条件并且按照可以使用的索引中列的顺序来决定先使用哪个搜索条件，后使用哪个搜索条件

匹配左边的列

我们的查询数据不一定非要有全部字段才会走索引，前面我们也讲了多个索引字段时的查询过程，可以看出，查询时按照最左匹配原则依旧会走索引

最左匹配原则：如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列

匹配列前缀

对于字符串字段，我们用到模糊查询，这也依旧遵循最左匹配原则，因此 abc123 数据使用 like abc% 是会走索引的，但是 like %123 就不会走索引,因为我们说了，索引是按照字段大小排列的，而字符串的大小比较又是一个一个字符来比较的，所以匹配最左前缀依旧能够使用这个索引顺序

匹配范围值

这个因为我们本身索引就是按某个字段的顺序进行排列的，所以范围查询理所应当

如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到 B+ 树索引

SELECT * FROM tableWHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

对于联合索引 idx_name_birthday_phone_number 来说，只能用到 name 列的部分，而用不到 birthday 列的部分，因为只有 name 值相同的情况下才能用 birthday 列的值进行排序，而这个查询中通过 name 进行范围查找的记录中可能并不是按照 birthday 列进行排序的，所以在搜索条件中继续以 birthday 列进行查找时是用不到这个 B+ 树索引的。

这里个前面的全值匹配或匹配最前列不同，那个是先根据第一个字段找出一部分记录，再根据第二个字段从前面的记录中又精确找出记录，而这里是要取范围的，第二个索引无法取出范围（前面说过，二字段索引有序的前提是一字段确定或相等）

精确匹配某一列并范围匹配另外一列

对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找

具体原因和前面的的匹配规则差不多

用于排序

一般来说，我们要对数据进行排序的话，需要先把所有数据加载进内存，再利用各种算法来进行排序操作，这显然是非常耗时的，但是我们也知道 索引 本身就是自带排序的，所以，利用 索引 的结构，我们可以尝试让我们的排序操作去走索引

SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

我们的 name\birthday\phone 字段本身就是按这个顺序建立的索引，那么结合 B+ 树索引在多字段索引情况下的结构，我们可以知道，其本身就是先按照 name 再根据 birthday phone 字段来顺序排列的，因此，我们的顺序也要注意和索引顺序对应才会走索引。什么？你问我为什么这次优化器不帮忙优化？拜托，我们排序的时候字段顺序不同时作用不同结果也不相同，怎么优化？前面的 where xx = xx and yy = yy and zz = zz是因为顺序不改变结果

索引失效的排序场景

  1. 联合索引的各个排序列的排序不一致导致索引失效

    即有升序有降序的情况下走不了索引

  2. WHERE子句中出现非排序使用到的索引列

    SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10;

    尽管你说两个字段都定义了索引，但是排序时不会走索引的，当然如果这俩在联合索引里，那还是阔以滴

  3. 排序列中的列不是包含在同一个索引中

  4. 排序列使用了复杂的表达式

    即索引列不要使用数据库提供的一些函数之类的

用于分组

所谓分组，其实和排序差不多，当我们的数据按顺序排列的时候，分出来的同一组一定是相邻数据，也就是说，前面排序的索引规则到分组这里还是适用，依旧是多个列也要对应联合索引的顺序

回表

前面我们提到了 回表，因为我们的完整记录都存在于 聚簇索引 上的，因此我们使用 二级索引 查询出对应记录的主键值后还要到 聚簇索引 上查询完整记录，又因为 InnoDB 中，表本身就是 聚簇索引，因此这个操作就称之为 回表

需要回表的记录越多，使用二级索引的性能就越低

比如我在二级索引中找出的记录数占了全表的 90% 以上，这就意味着我们还要再去表中查询这么多的数据，就像多线程我们还要考虑线程切换带来的资源消耗一样，这里我们还不如直接一开始就全表扫描，至于究竟语句执行时走的是全表扫描还是二级索引加回表，这就由我们的优化器来判断了

覆盖索引

我们前面说了，回表还是个比较麻烦的操作，那么有没有可能咱直接就从 二级索引 里查询出需要的数据呢？我们说了，二级索引里查询出来的数据包含有 索引列+主键 ，然后根据主键回表查询出完整记录，但是当我们需要的数据字段已经全部包含在二级索引里了，我们不就不用回表了？没错，这就是 覆盖索引

挑选索引

1.只为用于搜索、排序或分组的列创建索引

也就是说，我们只为 where join on order by group by 中的列建立索引，其它只作为查询结果的列不要建立索引，一是占空间二是影响我们删改数据的效率

2.考虑列的基数

列的基数 指的是某一列中不重复数据的个数，比方说某个列包含值 2, 5, 8, 2, 5, 8, 2, 5, 8 ，虽然有 9 条记录，但该列的基数却是 3 。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中。而且如果某个建立了二级索引的列的重复值特别多，那么使用这个二级索引查出的记录还可能要做回表操作，这样性能损耗就更大了，所以性别字段加索引没用。

你想一想，我们一直在提索引多一个就会影响删改效率，并且回表的话效率也低，于是你给性别来个索引，我们通过索引也就只能过滤一般数据，还要回表查询，这还不如一开始就全表查询

所以结论就是：最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好

索引列类型尽可能小

我们这里所说的类型大小指的就是该类型表示的数据范围的大小。之所以要选择小类型数据是因为：

  • 数据类型越小，在查询时进行的比较操作越快
  • 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I/O 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

索引字符串前缀

我们知道一个字符串其实是由若干个字符组成，通常我们的字符串又挺长的，那么整个字段都作为索引就会耗时耗空间，因此我们可以只对字符串字段的前几个字符建立索引，这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值

CREATE TABLE person_info(
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

这里的name(10)就是只对 name 列的前10个字符进行索引
这种只索引字符串值的前缀的策略是值得推荐的，尤其是在字符串类型能存储的字符比较多的时候

当然，有得有失，使用了字符串前缀会导致无法支持使用索引排序

让索引列在比较表达式中单独出现

如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的

  1. WHERE my_col * 2 < 4 会遍历所有记录再来计算，不走索引
  2. WHERE my_col < 4/2 会走索引找出小于 2 的记录

让主键自增

我们的聚簇索引是按主键大小排的，如果我们插入的主键忽大忽小，会导致页分裂，影响性能，所以建议使用上AUTO INCREMENT

避免重复索引

例如我们给某一列定义一个主键了还给他定义一个索引，这就会导致索引重复，新加的索引没用还影响性能

单表访问方法

当我们想要执行一条查询 SQL 时，MySQL 会通过不同的方式来执行这个数据查询的操作，而这个执行查询语句的方式称之为 访问方法 或者 访问类型 ，所以我们优化查询 SQL 时就是要让 MySQL 选择最佳的访问方法

我们先建一个表

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

我们可以通过 EXPLAIN 来查看我们即将执行的 SQL 究竟会通过哪种 访问方法 执行

EXPLAINSELECT * FROM `user`

image-20210815185503966

这里的 type 对应的就是我们的访问方法了

类型

const

有的时候我们可以通过主键列来定位一条记录，比方说这个查询：
SELECT * FROM single_table WHERE id = 1438;
MySQL 会直接利用主键值在聚簇索引中定位对应的用户记录

MySQL 认为通过主键或者唯一二级索引列与常数的等值比较来定位一条记录非常快的，所以把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为： const ，意思是常数级别的，代价是可以忽略不计的。

不过这种 const 访问方法只能在主键列或者唯一二级索引列（UNIQUE KEY）和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个 const 访问方法才有效

ref

有时候我们对某个普通的二级索引列与常数进行等值比较，比如这样：
SELECT * FROM single_table WHERE key1 = 'abc';

由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少，则回表的代价还是比较低的， MySQL 可能选择使用索引而不是全表扫描的方式来执行查询。 MySQL 把这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为： ref 。

ref_or_null

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为 NULL 的记录也找出来，就像下边这个查询：
SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;
当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为 ref_or_null

range

之前介绍的几种访问方法都是在对索引列与某一个常数进行等值比较的时候才可能使用到但是有时候我们面对的搜索条件更复杂

比如下边这个查询：
SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

MySQL 把这种利用索引进行范围匹配的访问方法称之为： range，当然这里进行范围匹配中的 索引 可以是聚簇索引，也可以是二级索引

index

我们有 key_part1, key_part2, key_part3 建立的索引，当我们执行以下查询：

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

key_part2 并不是联合索引 idx_key_part 的最左索引列，按理说达到了我们前面说的索引失效条件，的确我们无法使用 ref 或者 range 访问方法来执行这个语句，但是我们可以直接遍历整个二级索引来比较出 key_part2 = 'abc' 的记录

MySQL 把这种采用遍历二级索引记录的执行方式称之为： index

all

最直接的查询执行方式就是我们已经提了无数遍的全表扫描，对于 InnoDB 表来说也就是直接扫描聚簇索引，MySQL 把这种使用全表扫描执行查询的方式称之为： all

注意

  1. 一般情况下我们只能利用单个二级索引执行查询
    因此当我们的查询条件是多个并且有不包含在索引里的条件时，一般先是根据前面的索引列回表查出数据再按照后面的条件找出最终结果
  2. 在一般情况下执行一个查询时最多只会用到单个二级索引，但不是还有特殊情况么，在一些特殊情况下也可能在一个查询中使用到多个二级索引
    例如分别使用两个索引列查询出记录再取交集，这样就利用到了两个索引

表连接原理

JOIN 按照功能大致分为如下三类：

  • INNER JOIN（内连接，或等值连接）：获取两个表中字段匹配关系的记录。
  • LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
  • RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录
SELECT * FROM xxxa INNER JOIN xxxb ON yy=zz

表连接过程

image-20210815001320262

对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左（外）连接的驱动表就是左边的那个表，右（外）连接的驱动表就是右边的那个表

嵌套循环连接

驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为 嵌套循环连接

索引加快连接

我们说过，连接操作会对被驱动表多次访问，如果每次都是全表扫描效率会非常低，因此我们可以为被驱动表的连接字段添加上索引，当然如果连接字段就是主键的话就更好了，连回表都省了

执行计划

一条查询语句在经过 MySQL 查询优化器的各种基于成本和规则的优化会后生成一个所谓的 执行计划 ，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。 MySQL 提供了 EXPLAIN 语句来帮助我们查看某个查询语句的具体执行计划，只需要在我们将要执行的 SQL 前添加上该语句即可输出详细的 执行计划

image-20210816002756320

  1. table

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

  2. id

    查询语句中每出现一个 SELECT 关键字，MySQL 就会为它分配一个唯一的 id 值。这个 id 值就是 EXPLAIN 语句的第一个列

  3. select_type

    MySQL 的大叔为每一个 SELECT 关键字代表的小查询都定义了一个称之为 select_type 的属性，意思是我们只要知道了某个小查询的 select_type 属性，就知道了这个小查询在整个大查询中扮演了一个什么角色

    • SIMPLE

      查询语句中不包含 UNION 或者子查询的查询都算作是 SIMPLE 类型

    • PRIMARY

      对于包含 UNION 、 UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的 select_type 值就是 PRIMARY

    • UNION

      对于包含 UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的 select_type 值就是 UNION

    • UNION RESULT

      MySQL 选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION RESULT

  4. type

    就是我们前面说的访问方法

  5. possible_keys 和 key

    possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些， key 列表示实际用到的索引有哪些

    possible_keys 列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引

  6. key_len

    key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度

  7. rows

    如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数

Buffer Pool

不管是用于存储用户数据的索引（包括聚簇索引和二级索引），还是各种系统数据，都是以 页 的形式存放在 表空间 中的，而所谓的 表空间 只不过是 InnoDB 对文件系统上一个或几个实际文件的抽象，也就是说我们的数据说到底还是存储在磁盘上的。但是在磁盘上处理数据是很慢的，所以 InnoDB 存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，在进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其 缓存 起来，这样将来有请求再次访问该页面时，就可以省去磁盘 IO 的开销了。

Buffer Pool构造

在 MySQL 服务器启动的时候就向操作系统申请了一片连续的内存，这片内存叫做 Buffer Pool （中文名是 缓冲池 ）

Buffer Pool 中默认的缓存页大小和在磁盘上默认的页大小是一样的，都是 16KB

为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一些所谓的 控制信息 ，这些控制信息包括该页所属的表空间编号、页号、缓存页在 Buffer Pool 中的地址、链表节点信息、一些锁信息以及 LSN 信息等
每个缓存页对应的控制信息占用的内存大小是相同的，把每个页对应的控制信息占用的一块内存称为一个 控制块 ，控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中：

image-20210817120229608

缓存页的哈希表

当我们需要访问某个页中的数据时，就会把该页从磁盘加载到 Buffer Pool 中，如果该页已经在 Buffer Pool 中的话直接使用就可以了。那么问题也就来了，我们怎么知道该页在不在 Buffer Pool 中呢？

我们其实是根据 表空间号 + 页号 来定位一个页的，也就相当于 表空间号 + 页号 是一个 key ， 缓存页 就是对应的 value ，这就构成了一个哈希表！在需要访问某个页的数据时，先从哈希表中根据 表空间号 + 页号 看看有没有对应的缓存页，如果有，直接使用该缓存页就好，没有就加载

链表

free 链表

从磁盘上读取一个页到 Buffer Pool 中的时候该放到哪个缓存页的位置呢？或者说怎么区分 Buffer Pool 中哪些缓存页是空闲的，哪些已经被使用了呢？

我们最好在某个地方记录一下Buffer Pool中哪些缓存页是可用的，这个时候缓存页对应的 控制块 就派上大用场了，我们可以把所有空闲的缓存页对应的控制块作为一个节点放到一个链表中，这个链表也可以被称作 free链表 （或者说空闲链表）。

flush 链表

如果我们修改了 Buffer Pool 中某个缓存页的数据，那它就和磁盘上的页不一致了，这样的缓存页也被称为 脏页 。每次修改缓存页后，我们并不着急立即把修改同步到磁盘上，而是在未来的某个时间点进行同步，所以我们需要记录哪些页是脏页

因此创建一个存储脏页的链表，凡是修改过的缓存页对应的控制块都会作为一个节点加入到一个链表中，因为这个链表节点对应的缓存页都是需要被刷新到磁盘上的，所以也叫 flush 链表

LRU 链表

缓存毕竟不是用来长期存数据的，空间有限，所以我们希望在缓存中的数据都是最常使用的，使用频率少的逐渐清理掉，因此就又想到了我们的 LRU 算法（最近最少使用淘汰）

只要我们使用到某个缓存页，就把该缓存页调整到 LRU 链表 的头部，这样 LRU 链表 尾部就是最近最少使用的缓存页，当 Buffer Pool 中的空闲缓存页使用完时，到 LRU链表 的尾部淘汰缓存页

仅仅只是上面这种链表规则，缓存命中率还是会不高，因为：

  • 加载到 Buffer Pool 中的页不一定被用到。
  • 如果非常多的使用频率偏低的页被同时加载到 Buffer Pool 时，可能会把那些使用频率非常高的页从 Buffer Pool 中淘汰掉

因为有这两种情况的存在，所以 InnoDB 把这个 LRU链表 按照一定比例分成两截，分别是：

  • 一部分存储使用频率非常高的缓存页，所以这一部分链表也叫做 热数据 ，或者称 young区域 。
  • 另一部分存储使用频率不是很高的缓存页，所以这一部分链表也叫做 冷数据 ，或者称 old区域 。

image-20210817164415744

其他的一些链表
为了更好的管理 Buffer Pool 中的缓存页，除了我们上边提到的一些措施，InnoDB 还引进了其他的一些链表 ，比如 unzip LRU链表 用于管理解压页， zip clean链表 用于管理没有被解压的压缩页， zip free数组 中每一个元素都代表一个链表，它们组成所谓的 伙伴系统 来为压缩页提供内存空间等等

刷新脏页到磁盘

后台有专门的线程每隔一段时间负责把脏页刷新到磁盘，这样可以不影响用户线程处理正常的请求。主要有两种刷新路径：

  • 从 LRU链表 的冷数据中刷新一部分页面到磁盘。
    后台线程会定时从 LRU链表 尾部开始扫描一些页面，扫描的页面数量可以通过系统变量 innodb_lru_scan_depth 来指定，如果从里边发现脏页，会把它们刷新到磁盘。这种刷新页面的方式被称之为 BUF_FLUSH_LRU 。
  • 从 flush链表 中刷新一部分页面到磁盘。
    后台线程也会定时从 flush链表 中刷新一部分页面到磁盘，刷新的速率取决于当时系统是不是很繁忙。这种刷新页面的方式被称之为 BUF_FLUSH_LIST

事务

概念

定义：事务就是一个对数据库操作的序列，是一个不可分割的工作单位，要不这个序列里面的操作全部执行，要不全部不执行。

特性：ACID

  • 原子性（Atomicity）

    一个事务是一个不可分割的工作单位，要不全部执行，要不全部不执行

  • 一致性（Consistency）

    事务开始之前和事务结束以后，数据库的完整性约束没有被破坏

    比如我们设置了某列只能是数字且不能为空，那么无论事务怎么执行，最终这个约束不会变。数据库某些操作的原子性和隔离性都是保证一致性的一种手段，在操作执行完成后保证符合所有既定的约束则是一种结果

  • 隔离性（Isolation）

    多个事务并发执行时，相互之间无影响

  • 持久性（Durability）

    当一个事提交后，对数据库的改变是永久性的，不会被回滚

    当把现实世界的状态转换映射到数据库世界时， 持久性 意味着该转换对应的数据库操作所修改的数据都应该在磁盘上保留下来，不论之后发生了什么事故，本次转换造成的影响都不应该被丢失掉

事务的状态

image-20210817212026082

从图中可以看出了，只有当事务处于提交的或者中止的状态时，一个事务的生命周期才算是结束了。对于已经提交的事务来说，该事务对数据库所做的修改将永久生效，对于处于中止状态的事务，该事务对数据库所做的所有修改都会被回滚到没执行该事务之前的状态。

语法

开启事务：

  • BEGIN

  • START TRANSACTION

    START TRANSACTION 语句和 BEGIN 语句有着相同的功效，都标志着开启一个事务，但是在 START TRANSACTION 语句后可跟随几个 修饰符

    • READ ONLY只读事务，只能读数据，不能修改
    • READ WRITE读写事务，可读可写
    • WITH CONSISTENT SNAPSHOT一致性读

如果我们不显式指定事务的访问模式，那么该事务的访问模式就是 读写 模式

提交事务：

开启事务之后就可以继续写需要放到该事务中的语句了，当最后一条语句写完了之后，我们就可以提交该事务了，提交的语句：
COMMIT

终止事务：

语句：ROLLBACK 回滚

MySQL 中并不是所有存储引擎都支持事务的功能，目前只有 InnoDB 和 NDB 存储引擎支持

默认情况下，如果我们不显式的使用 START TRANSACTION 或者 BEGIN 语句开启一个事务，那么每一条语句都算是一个独立的事务，这种特性称之为事务的 自动提交

保存点：

MySQL 提出了一个 保存点 的概念，就是在事务对应的数据库语句中打几个点，我们在调用 ROLLBACK 语句时可以指定会滚到哪个点，而不是回到最初的原点。定义保存点的语法如下：
SAVEPOINT 保存点名称;
我们可以指定回滚到指定的保存点：
ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;

日志

通用日志 binlog

概念：

binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog 是 mysql 的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。

逻辑日志：可以简单理解为记录的就是 sql 语句

在实际应用中，binlog的主要使用场景有两个，分别是主从复制和数据恢复。

  1. 主从复制：在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。
  2. 数据恢复：通过使用mysqlbinlog工具来恢复数据。

刷盘时机：

对于InnoDB存储引擎而言，只有在事务提交时才会记录binlog，此时记录还在内存中，那么binlog是什么时候刷到磁盘中的呢？mysql通过sync_binlog参数控制binlog的刷盘时机，取值范围是0-N：

  • 0：不去强制要求，由系统自行判断何时写入磁盘；
  • 1：每次commit的时候都要将binlog写入磁盘；
  • N：每N个事务，才会将binlog写入磁盘。

从上面可以看出，sync_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值。

日志格式：

binlog 日志有三种格式，分别为 STATMENT、ROW 和 MIXED。

在 MySQL 5.7.7之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW。日志格式通过binlog-format指定。

  • STATMENT ：基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql 语句会记录到binlog中。 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 从而提高了性能； 缺点：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等。
  • ROW ：基于行的复制(row-based replication, RBR)，不记录每条 sql 语句的上下文信息，仅需记录哪条数据被修改了。 优点：不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题； 缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨
  • MIXED ：基于STATMENT和ROW两种模式的混合复制(mixed-based replication, MBR)，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

事务日志redolog

前面我们说过 Buffer Pool 这个页缓存空间，那么，既然是缓存，还是由硬盘到内存的缓存，持久性问题是跑不了的，我们知道 Redis 的持久化方案主要有两种： AOF RDB，即一个利用日志，一个利用备份，这里我们的 MySQL 就使用上了日志，即我们没有必要在每次事务提交时就把该事务在内存中修改过的全部页面刷新到磁盘，只需要把修改了哪些东西记录一下就好

日志格式

redo 日志本质上只是记录了一下事务对数据库做了哪些修改，MySQL 针对事务对数据库的不同修改场景定义了多种类型的 redo 日志，但是绝大部分类型的 redo 日志都有下边这种通用的结构：

image-20210817233623910

各个部分的详细释义如下：

  • type ：该条 redo 日志的类型。
  • space ID ：表空间ID。
  • page number ：页号。
  • data ：该条 redo 日志的具体内容。

所以，redo日志会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统奔溃重启后可以把事务所做的任何修改都恢复出来

事务日志undolog

概念

我们说过 事务 需要保证 原子性 ，也就是事务中的操作要么全部完成，要么什么也不做。但是偏偏有时候事务执行到一半会出现一些情况，比如：

  • 情况一：事务执行过程中可能遇到各种错误，比如服务器本身的错误，操作系统错误，甚至是突然断电导致的错误。
  • 情况二：程序员可以在事务执行过程中手动输入 ROLLBACK 语句结束当前的事务的执行。

这两种情况都会导致事务执行到一半就结束，但是事务执行过程中可能已经修改了很多东西，为了保证事务的原子性，我们需要把东西改回原先的样子，这个过程就称之为 回滚 （英文名： rollback ），这样就可以造成一个假象：这个事务看起来什么都没做，所以符合 原子性 要求。

每当我们要对一条记录做改动时（这里的改动可以指 INSERT 、 DELETE 、 UPDATE ），都需要留一手 —— 把回滚时所需的东西都给记下来，MySQL 把这些为了回滚而记录的这些东西称之为撤销日志，英文名为 undo log 。这里需要注意的一点是，由于查询操作并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的 undo日志 。

事务id

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

  • 对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增、删、改操作时才会为这个事务分配一个事务id ，否则的话是不分配事务id的。
  • 对于读写事务来说，只有在它第一次对某个表（包括用户创建的临时表）执行增、删、改操作时才会为这个事务分配一个事务id，否则的话也是不分配事务id的。

日志格式

image-20210818014350047

事务隔离级别

事务简介的章节中我们说过事务有一个称之为 隔离性 的特性，理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样子的话对性能影响太大，
我们既想保持事务的 隔离性 ，又想让服务器在处理访问同一数据的多个事务时性能尽量高些，鱼和熊掌不可得兼，舍一部分 隔离性 来保证性能。

事务并发问题

  • 脏写

    如果一个事务修改了另一个未提交事务修改过的数据，那就意味着发生了 脏写

  • 脏读

    如果一个事务读到了另一个未提交事务修改过的数据，那就意味着发生了 脏读

  • 不可重复读

    如果当前事务先进行了一次数据读取，然后再执行过程中再次读取数据却读到的是被其它事务修改过的数据，导致两次读取到的数据不匹配，那就意味着发生了 不可重复读

  • 幻读

    如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，那就意味着发生了 幻读

隔离级别

  • READ UNCOMMITTED ：读未提交
  • READ COMMITTED ：读已提交
  • REPEATABLE READ ：可重复读 InnoDB 默认级别，重点是 MVCC 和锁
  • SERIALIZABLE ：可串行化 每次读都需要获得表级共享锁，读写相互都会阻塞
隔离级别 脏读 不可重复读 幻读
读未提交 T T T
读已提交 F T T
可重复读 F F T
可串行化 F F F

脏写这个问题太严重了，不论是哪种隔离级别，都不允许脏写的情况发生。

MySQL在 可重复读REPEATABLE READ 隔离级别下，是可以禁止幻读问题的发生的
MySQL 的默认隔离级别为 REPEATABLE READ ，我们可以手动修改一下事务的隔离级别

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

读-解决方案

当前读：需要保证读取到的数据在自己事务提交或改动前一直是最新版本，因此需要加锁

快照读：就是普通的 SELECT 语句，可能读到的数据不是最新版本，但一定是当前事务可见的最新版本

解决快照读的幻读、不可重复读问题使用 MVCC 方案（可以当做乐观锁）

MVCC 解决不了当前读的幻读问题，但是能够解决快照读的

解决当前读的幻读就需要使用 LBCC （间隙锁和记录锁组成的临建锁）

注意为什么我们的重点在读上面？因为本身我们的写操作肯定是需要加锁的，而区别在于使用上 MVCC 这一策略可以让我们尽管写操作加了锁我们还是可以不被阻塞住去读取可见版本的数据。

MVCC

MVCC （Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用 READ COMMITTD 、 REPEATABLE READ 这两种隔离级别的事务在执行普通的 SELECT 操作时访问记录的版本链的过程，这样子可以使不同事务的 读-写 、 写-读 操作并发执行，从而提升系统性能。

核心思想：只能查找事务 ID 小于等于当前事务 ID 的行；只能查找删除时间大于等于当前事务 ID 的行，或未删除的行。保证读不加锁，读写不冲突

版本链

每次对记录进行改动，都会记录一条 undo日志 ，每条 undo日志 也都有一个 roll_pointer 属性（ INSERT 操作对应的 undo日志 没有该属性，因为该记录并没有更早的版本），可以将这些 undo日志 都连起来，串成一个链表，所以现在的情况就像下图一样：

image-20210818184011500

对该记录每次更新后，都会将旧值放到一条 undo日志 中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被 roll_pointer 属性连接成一个链表，我们把这个链表称之为 版本链 ，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的 事务id

ReadView

对于使用 READ UNCOMMITTED 隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了，对于使用 SERIALIZABLE 隔离级别的事务来说，InnoDB 规定使用加锁的方式来访问记录

对于使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的

所以，InnoDB 提出了 ReadView 的概念：

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

有了这个 ReadView ，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：

  • 如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以可见
  • 如果被访问版本的 trx_id 属性值小于 ReadView 中的 min_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 前已经提交，所以可见
  • 如果被访问版本的 trx_id 属性值大于 ReadView 中的 max_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以不可见
  • 如果被访问版本的 trx_id 属性值在 ReadView 的 min_trx_id 和 max_trx_id 之间，那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，不可见；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，可见

ReadView

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录

在 MySQL 中， READ COMMITTED 和 REPEATABLE READ 隔离级别的一个非常大的区别就是它们生成 ReadView 的时机不同

READ COMMITTED —— 每次读取数据前都生成一个 ReadView

REPEATABLE READ —— 在第一次读取数据时生成一个 ReadView

锁

锁结构

事务并发执行时，写-写 场景我们是需要防止脏写问题的，所以在多个未提交事务相继对一条记录做改动时，需要让它们排队执行，这个排队的过程其实是通过 锁 来实现的。

当一个事务想对某条记录做改动时，首先会看看内存中有没有与这条记录关联的 锁结构 ，当没有的时候就会在内存中生成一个 锁结构 与之关联。比方说事务 T1 要对这条记录做改动，就需要生成一个 锁结构 与之关联：

image-20210818233156945

其实在 锁结构 里有很多信息，不过为了简化理解，我们现在只把两个比较重要的属性拿了出来：

  • trx信息 ：代表这个锁结构是哪个事务生成的
  • is_waiting ：代表当前事务是否在等待

如图所示，当事务 T1 改动了这条记录后，就生成了一个 锁结构 与该记录关联，因为之前没有别的事务为这条记录加锁，所以 is_waiting 属性就是 false ，我们把这个场景就称之为获取锁成功，或者加锁成功，然后就可以继续执行操作了

这看起来很像 synchronized 的轻量锁的锁记录

在事务 T1 提交之前，另一个事务 T2 也想对该记录做改动，那么先去看看有没有 锁结构 与这条记录关联，发现有一个 锁结构 与之关联后，然后也生成了一个 锁结构 与这条记录关联，不过 锁结构 的
is_waiting 属性值为 true ，表示当前事务需要等待，我们把这个场景就称之为获取锁失败，或者加锁失败

所以，解决 脏读 、 不可重复读 、 幻读 有两种方案：

  1. 读操作利用多版本并发控制（ MVCC ），写操作进行 加锁
  2. 读、写操作都采用 加锁 的方式

一致性读

事务利用 MVCC 进行的读取操作称之为 一致性读 ，或者 一致性无锁读 ，有的地方也称之为 快照读 。所有普通的 SELECT 语句在 READ COMMITTED 、 REPEATABLE READ 隔离级别下都算是 一致性读

一致性读 并不会对表中的任何记录做 加锁 操作，其他事务可以自由的对表中的记录做改动。

锁

共享锁和独占锁

我们前边说过，并发事务的 读-读 情况并不会引起什么问题，不过对于 写-写 、 读-写 或 写-读 这些情况可能会引起一些问题，需要使用 MVCC 或者 加锁 的方式来解决它们。在使用 加锁 的方式解决问题时，由于既要允许 读-读 情况不受影响，又要使 写-写 、 读-写 或 写-读 情况中的操作相互阻塞，所以对锁分了类：

  • 读锁（共享锁） ，简称 S 锁 。在事务要读取一条记录时，需要先获取该记录的 S锁
  • 写锁（独占锁） ，也常称 排他锁 ，简称 X锁 。在事务要改动一条记录时，需要先获取该记录的 X锁

只有大家都获取共享锁才能都获取到，其它任何情况都是不兼容的，即互斥的

对于上面提到的锁都是针对记录的，也可以被称之为 行级锁 或者 行锁

乐观锁和悲观锁

悲观锁是一副“总有刁民想害朕”的态度，指的是对数据被外界修改持保守态度，数据修改包括本系统当前的其他事务，以及来自外部系统的事务处理。因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制，比如上面说的共享排它锁。

乐观锁是很佛系的态度，总认为应该没什么问题。乐观锁，大多是基于数据版本机制实现的，即为数据增加一个版本标识。在基于数据库表的版本解决方案中，一般是通过为数据库表人为增加一个 version 字段来实现。

锁粒度

前面说的行级锁对一条记录加锁影响的也只是这条记录而已，我们就说这个锁的粒度比较细；其实一个事务也可以在表级别进行加锁，自然就被称之为 表级锁 或者 表锁 ，对一个表加锁影响整个表中的记录，我们就说这个锁的粒度比较粗。给表加的锁也可以分为共享锁和独占锁

  • 给表加 S锁 ：

    如果一个事务给表加了 S锁 ，那么：

    • 别的事务可以继续获得该表的 S锁
    • 别的事务可以继续获得该表中的某些记录的 S锁
    • 别的事务不可以继续获得该表的 X锁
    • 别的事务不可以继续获得该表中的某些记录的 X锁

  • 给表加 X锁 ：

    如果一个事务给表加了 X锁 ，那么：

    • 别的事务不可以继续获得该表的 S锁
    • 别的事务不可以继续获得该表中的某些记录的 S锁
    • 别的事务不可以继续获得该表的 X锁
    • 别的事务不可以继续获得该表中的某些记录的 X锁

此外，我们学过 Java 的锁就知道 synchronized 为了应对各种情况设计了偏向锁、轻量锁、重量锁，我们数据库有时也面临着表锁和行锁的切换，那么我们加表锁之前怎么判断表里是否有记录添加上了行锁呢？不能一个一个遍历吧？因此引入了 意向锁 的概念：

  • 意向共享锁，简称 IS锁 。当事务准备在某条记录上加 S锁 时，需要先在表级别加一个 IS锁 。
  • 意向独占锁，简称 IX锁 。当事务准备在某条记录上加 X锁 时，需
    要先在表级别加一个 IX锁 。

IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录，也就是说其实IS锁和IX锁是兼容的，IX锁和IX锁是兼容的，其实就是个标记的作用

InnoDB 锁实现

前面说的概念都是一些通用的理论知识，具体到某个数据库或者存储引擎其底层实现与支持还是不一样的
对于 MyISAM MEMORY MERGE 这些存储引擎来说，它们只支持表级锁，而且这些引擎并不支持事务，所以我们的重点还是在 InnoDB 上

InnoDB 中的表级锁

其实 InnoDB 存储引擎提供的表级 S锁 或者 X锁 是相当鸡肋，只会在一些特殊情况下，比方说崩溃恢复过程中用到

当我们在对使用 InnoDB 存储引擎的表的某些记录加 S锁 之前，那就需要先在表级别加一个 IS锁 ，当我们在对使用 InnoDB 存储引擎的表的某些记录加 X锁 之前，那就需要先在表级别加一个 IX锁

InnoDB 中的行级锁

行锁 ，顾名思义就是在记录上加的锁，我们知道 InnoDB 的表其实就是聚簇索引，即索引和记录是一体的，而 InnoDB 的行级锁是通过给索引上的索引项进行加锁实现的，这就意味着如果不利用索引检索数据，则会对所有记录加锁，相当于表锁

什么是 对索引项加锁，也就是对该索引项对应的所有记录加锁？ 比如一个学生表中有两个学生同名了，都叫 张三，相当于表中 name 字段有重复，那么该字段建有索引时，对应 张三 一个索引段上锁时，两条 张三 记录都会上锁。

常用的 行锁类型：

  • Record Locks

    记录锁，对索引项加锁，也就是对该索引项对应的所有记录加锁

  • Gap Locks

    MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用 加锁 方案解决。由于在事务第一次执行读取时，那些幻影记录并不存在，我们无法在上面添加记录锁，因此提出了 Gap Locks 间隙锁

    gap锁 的提出仅仅是为了防止插入幻影记录而提出的，我们对一条记录加上 gap锁 之后，其它事务想要往这条记录之前的间隙添加记录就会被阻塞住，但是对于往这条记录之后的间隙插入记录的事务又怎么防止呢？我们前面讲数据页结构说过其会记录最小记录以及最大记录，那么我们在页面最大记录上加一个 gap锁 就可以阻塞想要在记录后面添加记录的事务了

  • Next-Key Locks

    有时候我们既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录，那么 Next-Key Locks 就出来了，其本身就是一个 记录锁 和 gap锁 的合体，它既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前边的间隙

  • Insert Intention Locks

    我们说一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了所谓的 gap锁 ，如果有的话，插入操作需要等待，直到拥有 gap锁 的那个事务提交。但是 InnoDB 规定事务在等待的时候也需要在内存中生成一个 锁结构 ，表明有事务想在某个间隙中插入新记录，但是现在在等待。把这种类型的锁命名为 Insert Intention Locks ，我们也可以称为 插入意向锁 。

死锁

所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。

发生死锁，InnoDB一般会检测到，并让锁住记录少的那个事务回滚让出锁。如果不能检测到死锁，也可以通过超时参数 innodb_lock_wait_timeout 来防止锁等待太久。

InnoDB 锁结构

对一条记录加锁的本质就是在内存中创建一个 锁结构 与之关联，如果每一条记录都对其加锁，未免太浪费空间了，所以如果符合下边这些条件：

  • 在同一个事务中进行加锁操作
  • 被加锁的记录在同一个页面中
  • 加锁的类型是一样的
  • 等待状态是一样的

那么这些记录的锁就可以被放到一个 锁结构 中

image-20210819123826747

高性能数据库

表设计

三大范式

第一范式

无重复的列，表中的每一列都是拆分的基本数据项，即列不能够再拆分成其他几列，强调的是列的原子性.。

如果在实际场景中，一个联系人有家庭电话和公司电话，那么以“姓名、性别、电话”为表头的表结构就没有达到 1NF。要符合 1NF 只需把电话列拆分，让表头变为姓名、性别、家庭电话、公司电话即可。

第二范式

属性完全依赖于主键，首先要满足它符合 1NF，另外还需要包含两部分内容：

  • 表必须有一个主键；
  • 没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。即要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。

第三范式

属性不传递依赖于其他非主属性，首先需要满足 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

第二范式和第三范式的区别

  • 第二范式：非主键列是否依赖主键（包括一列通过某一列间接依赖主键），要是有依赖关系就是第二范式；
  • 第三范式：非主键列是否直接依赖主键，不能是那种通过传递关系的依赖。要是符合这种依赖关系就是第三范式。

范式优点：

  • 避免数据冗余，减少维护数据完整性的麻烦；
  • 减少数据库的空间；
  • 数据变更速度快。

范式缺点：

  • 按照范式的规范设计的表，等级越高的范式设计出来的表数量越多。
  • 获取数据时，表关联过多，性能较差。

反范式

范式是普适的规则，满足大多数的业务场景的需求。对于一些特殊的业务场景，范式设计的表，无法满足性能的需求。此时，就需要根据业务场景，在范式的基础之上进行灵活设计，也就是反范式设计。

反范式设计主要从三方面考虑：

  • 业务场景；
  • 相应时间；
  • 字段冗余。

字段设计

  1. 禁用 ENUM、SET 类型
  2. 避免使用 TEXT、BLOB 数据类型
  3. 禁用 NULL 列
    • MySQL 难以优化 NULL 列；
    • NULL 列加索引，需要额外空间；
    • 含 NULL 复合索引无效。
  4. 禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统中，数据库中存储路径
  5. 短数据使用 TINYINT 或 SMALLINT，例如年龄、邮编
  6. 使用 UNSIGNED 存储非负数值，扩大正数的范围
  7. int(3) int(5) 区别：
    • 实际占用空间一样
    • 3 和 5 仅是最小显示宽度而已
  8. 对货币等对精度敏感的数据，应该使用定点数
  9. Char 与 Varchar 类型
    • 存储字符串长度相同的全部使用 Char 类型；字符长度不相同的使用 Varchar 类型
    • char(n) 无论存储的字符串多长都是占用 nbytes，即短了补空格，长了不存
    • varchar(n) 在没达到最长的长度时存的字符串多长就占用对应的空间（实际空间加 1byte ，因为要存长度信息）

索引设计

  1. 限制每张表上的索引数量
    • 索引可以增加查询效率，但同样也会降低插入和更新的效率
  2. 禁止给表中的每一列都建立单独的索引
    • 可以使用联合索引
  3. 每个 InnoDB 表必须有个主键
    • 因为聚簇索引是按照主键排序的
  4. 常见索引列：
    • 出现在SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
    • 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
    • 多表 join 的关联列
  5. 索引列顺序：
    • 区分度最高的放在联合索引的最左侧
    • 尽量把字段长度小的列放在联合索引的最左侧
    • 使用最频繁的列放到联合索引的左侧
  6. 避免建立冗余索引和重复索引
    • 索引多了会增加查询优化器生成执行计划的时间

推荐阅读