MySQL高级学习笔记

学习时间：2023年6月20日

学习来源：尚硅谷

1 逻辑架构

1.1 逻辑架构剖析

略，详见[[高性能MySQL学习笔记#1.1 逻辑架构]]

1.2 MySQL执行流程

image-20230621140628447

1.2.1 查询缓存

Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。

1.2.2 解析器

在解析器中对 SQL 语句进行语法分析、语义分析。

1.2.3 优化器

在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引检索等。

1.2.4 执行器

截止到现在，还没有真正去读写真实的表，仅仅只是产出了一个执行计划。于是就进入了执行器阶段 。

执行之前需要判断该用户是否具备权限。如果没有，就会返回权限错误。如果具备权限，就执行 SQL查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

例如：

1
select * from test where id=1;

比如：表 test 中，ID 字段没有索引，那么执行器的执行流程是这样的：

1
2
3
调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是1，如果不是则跳过，如果是则将这行存在结果集中；
调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。对于有索引的表，执行的逻辑也差不多。

小结：

image-20230621141244826

1.3 数据库缓冲池

InnoDB存储引擎是以页（位于磁盘）为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面（包括读页面、写页面、创建新页面等操作）。而磁盘 I/O 需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请占用内存来作为数据缓冲池，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。

这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行I/O的时间。要知道，这种策略对提升 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。

1.3.1 缓冲池与查询缓存

缓冲池

在 InnoDB 存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种数据的缓存，如下图所示：

image-20230621142057182

缓存数据更新策略：

image-20230621142904204

查询缓存

查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。因为命中条件苛刻，而且只要数据表发生变化，查询缓存就会失效，因此命中率低。

1.3.2 读取缓冲池数据

缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取。

image-20230621142340464

1.3.3 查看/设置缓冲池的大小

命令：

1
show variables like 'innodb_buffer_pool_size';
1
2
3
4
5
6
7
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.03 sec)

InnoDB 的缓冲池大小只有 134217728/1024/1024=128MB。我们可以修改缓冲池大小，比如改为256MB，方法如下：

1
set global innodb_buffer_pool_size = 268435456;

2 存储引擎

2.1 查看存储引擎

1
show engines;

image-20230621143808269

2.2 设置表的存储引擎

存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为不同的表设置不同的存储引擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

2.2.1 创建表

我们之前创建表的语句都没有指定表的存储引擎，那就会使用默认的存储引擎 InnoDB。

语法格式：

1
2
3
CREATE TABLE 表名(
  建表语句; 
) ENGINE = 存储引擎名称;

2.2.2 修改表

如果表已经建好了，我们也可以使用下边这个语句来修改表的存储引擎：

1
ALTER TABLE 表名 ENGINE = 存储引擎名称;

例如：

1
2
3
mysql> ALTER TABLE engine_demo_table ENGINE = InnoDB;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

2.3 引擎介绍

略，详见[[高性能MySQL学习笔记#1.5 存储引擎]]

3 索引的数据结构

3.1 索引的作用

image-20230621161048031

假如给数据使用二叉树这样的数据结构进行存储，如下图所示：

image-20230621161118747

3.2 索引及其优缺点

MySQL索引是存储在磁盘上的数据结构，它包含有关表中的数据的快速访问路径。但是它不是完全存储在磁盘上的。更确切地说，索引的一部分存储在磁盘上，一部分存储在内存中。

当在MySQL表上创建索引时，索引会被存储在磁盘上。这包括B-树，B+树和哈希索引。当我们需要查询数据时，MySQL会加载这些索引到内存中，并在内存中使用这些索引来查找数据。在查询期间，MySQL使用缓存来存储最频繁使用的索引和查询结果，以便下次访问时可以更快地返回结果。

3.2.1 概念

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

索引的本质：索引是数据结构。可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现高级查找算法。

3.2.2 优点

  • 提高数据检索的效率，降低数据库的IO成本，这也是创建索引最主要的原因。
  • 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
  • 在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
  • 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

3.2.3 缺点

  • 创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
  • 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

3.3 磁盘数据页的存储结构

3.3.1 概述

MySQL 中的页（Page）是一种逻辑概念，它是用来组织和管理存储在表空间中的数据的最小单位。MySQL 使用页来将表数据和索引数据分割为固定大小的块，以便更有效地管理和访问数据。通常情况下，每个页的大小为 16KB即$2^{14}$字节。

以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。也就是说，在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页，数据库I/O操作的最小单位是页。一个页中可以存储多个行记录。

页a、页b、页c…页n这些页可以不在物理结构上相连，只要通过双向链表相关联即可。

img

一个数据页内部会存储一行一行的数据，也就是平时在一个表里插入的一行一行的数据就会存储在数据页里，然后数据页里的每一行数据都会按照主键大小进行排序存储，同时每一行数据都有指针指向下一行数据的位置，组成单向链表，如下图。

img

又如：

image-20231028153922564

每个数据页里都会有一个页目录，里面根据数据行的主键存放了一个目录，同时数据行是被分散存储到不同的槽位里去的，所以实际上每个数据页的目录里，就是这个页里每个主键跟所在槽位的映射关系，如下图所示。

img

在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽（注意，数据页中的行记录通过主键进行了排序，因此可以使用二分法来查找），然后再遍历该槽对应的分组中的记录即可快速找到指定的记录。

3.3.2 InnoDB的页结构

img

  • File Header：文件头，存储页的通用信息，页跟页之间的双向关联，指针等
  • Page Header：页头，存储数据页专有的信息
  • Infimum+Supremum：页中的最小和最大记录，表中的数据都会比最小记录大，比最大记录小
  • User Records：用户记录，存放用户记录
  • Free Space：空闲空间，与User Records是互斥的关系，主要用来记录当前页的剩余空间大小
  • Page Directory：目录，存储记录在页中的相对位置，为了加快查找，能够查询到记录所在的组
  • File Trailer：文件尾，校验页是否完整

3.3.3 页中存储数据的流程

img

  • 开始的时候是没有User Records的，当插入数据的时候，会向Free Space申请空间
  • 插入数据，此时User Records会逐渐变大，而Free Space会逐渐变小
  • 当数据正好满了的时候，Free Space会变为0，不过一般情况下，不会这么凑巧，比如，新插入记录大小为30B，而Free Space只有10B，此时会重新申请一个页存储该条记录，这个10B的Free Space就会剩下在当前页中。

3.3.4 User Records结构

在MySQL中最常用的行存储格式是compact（紧凑）行格式。

img

记录的真实数据：

  • 主键列的值：存放主键的值
  • 两个隐藏列：
    • trx_id列的值：事务id
    • roll_pointer列的值：回滚的指针，指向回滚的内容
  • a列/b列的值：除主键外依次插入值

记录的额外信息：

  • 变长字段长度列表：变长字段指varchar等字符串类型，在存储的过程中，int，number都有固定大小的存储，字符串类型传入值多大就会存储多少，这样的目的是节省空间
  • NULL值列表：储存空值列表
  • 记录头信息：
    • 预留位1：无需关心
    • 预留位2：无需关心
    • deleted_flag：删除标志，0未删除，1已删除
    • min_rec_flag：目录项，只会在B+树的非叶子节点最小的目录项中添加这个标记
    • n_owned：记录当前组里的数量，此位置只会在主键最大的记录中维护
    • heap_no：堆号，在页面堆（innodb中数据连续排列，这种结构称为heap）中，记录的相对位置，根据heap_no可以直接找到该条记录所在的位置，序号从2开始排列，heap_no为0的是Infimum，为1的是Supremum，分配之后该值就不会发生变化（特别是删除中）。
    • record_type：记录类型，4种类型：
      • 0- 普通记录(用户插入的记录)
      • 1- B+树中非叶子节点的目录项记录(根据目录项可快速找到对应的记录)
      • 2- Infimum
      • 3- Supremum
    • next_record：记录的是当前记录的next_record与下一条记录的next_record之间的偏移量 。指向下一个记录，记录下一个记录的信息，此处需要注意的是，这里指向的是记录的真实数据的主键列的值，而不是变长字段长度列表，因为这样方便查找，想查下一条记录的额外信息只需要指向下一条记录再往前查询即可，查询真实信息，往后查询即可

3.4 InnoDB的索引

3.4.1 索引之前的查找

1
SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

在一个页中的查找

假设目前表中的记录比较少，所有的记录都可以被存放在一个页中，在查找记录的时候可以根据搜索条件的不同分为两种情况：

  • 以主键为搜索条件（二分法查找）：可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
  • 以其他列作为搜索条件（只能顺序查找）：因为在数据页中并没有对非主键建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。

在很多页中查找

大部分情况下我们表中存放的记录都是非常多的，需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤：

  1. 定位到记录所在的页。
  2. 从所在的页内查找相应的记录。

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们上面的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的。

3.4.2 设计索引

创建一张表：

1
2
3
4
5
6
CREATE TABLE index_demo(
  c1 INT,
  c2 INT,
  c3 CHAR(1),
  PRIMARY KEY(c1)
) ROW_FORMAT = Compact;

这个新建的index_demo表中有2个INT类型的列，1个CHAR(1)类型的列，而且我们规定了c1列为主键，这个表使用Compact行格式来实际存储记录的。这里我们简化了index_demo表的行格式示意图：

image-20230621170526421

把一些记录放到页里的示意图就是：

image-20230621170615305

① 一个简单的索引设计方案

我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢？因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以不得不依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页中该怎么办？我们可以为快速定位记录所在的数据页而建立一个目录，建这个目录必须完成下边这些事：

  • 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。
  • 给所有的页建立一个目录项。

image-20230621170756565

以页28为例，它对应目录项2，这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值5。我们只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键值快速查找某条记录的功能了。比如：查找主键值为20的记录，具体查找过程分两步：

  1. 先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中（因为 12 < 20 < 209 ），它对应的页是页9。

  2. 再根据前边说的在页中查找记录的方式去页9中定位具体的记录。

至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为索引。

② InnoDB中的索引方案

迭代1次：目录项记录的页

我们把前边使用到的目录项也放到数据页中的样子就是这样：

image-20230621171504388

从图中可以看出来，我们新分配了一个编号为30的页来专门存储目录项记录。这里再次强调目录项记录和普通的用户记录的不同点：

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

相同点：两者用的是一样的数据页，都会为主键值生成Page Directory（页目录），从而在按照主键值进行查找时可以使用二分法来加快查询速度。

现在以查找主键为20的记录为例，根据某个主键值去查找记录的步骤就可以大致拆分成下边两步：

  1. 先到存储目录项记录的页30，然后在页30中通过二分法快速定位到对应目录项，因为 12 < 20 < 209 ，所以定位到对应的记录所在的页就是页9。

  2. 再到存储用户记录的页9中根据二分法快速定位到主键值为20的用户记录。

迭代2次：多个目录项纪录的页

image-20230621171752030

从图中可以看出，我们插入了一条主键值为320的用户记录之后需要两个新的数据页：

  • 为存储该用户记录而新生成了页31。
  • 因为原先存储目录项记录的页30的容量已满（我们前边假设每个页只能存储4条目录项记录），所以不得不需要一个新的页32来存放页31对应的目录项。

现在因为存储目录项记录的页不止一个，所以如果我们想根据主键值查找一条用户记录大致需要3个步骤，以查找主键值为20的记录为例：

  1. 确定目录项记录页，我们现在的存储目录项记录的页有两个，即页30和页32，又因为页30表示的目录项的主键值的范围是 [1, 320) ，页32表示的目录项的主键值不小于 320 ，所以主键值为20的记录对应的目录项记录在页30中。

  2. 通过目录项记录页确定用户记录真实所在的页。在一个存储目录项记录的页中通过主键值定位一条目录项记录的方式说过了。

  3. 在真实存储用户记录的页中定位到具体的记录。

迭代3次：目录项记录页的目录页

image-20230621172020046

如图，我们生成了一个存储更高级目录项的页33，这个页中的两条记录分别代表页30和页32，如果用户记录的主键值在[1, 320)之间，则到页30中查找更详细的目录项记录，如果主键值不小于320的话，就到页32中查找更详细的目录项记录。

我们可以用下边这个图来描述它：

image-20230621172045585

这个数据结构的名称是B+树。

B树和B+树的介绍详见[[数据结构#3.1.5 B树和B+树]]

③ B+树

一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第0层，之后依次往上加。之前我们做了一个非常极端的假设：存放用户记录的页最多存放3条记录，存放目录项记录的页最多存放4条记录。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录，那么：

  • 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放100条记录。
  • 如果B+树有2层，最多能存放1000×100=10,0000条记录。
  • 如果B+树有3层，最多能存放1000×1000×100=1,0000,0000条记录。
  • 如果B+树有4层，最多能存放1000×1000×1000×100=1000,0000,0000条记录。相当多的记录！！！

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

3.4.3 常见的索引概念

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。

① 聚簇索引（主键索引）

一般情况下，聚簇索引等同于主键索引，但这里有一个前提条件，那就是这张表需要有主键，只有有了主键，它才能有主键索引，有主键索引才能等于聚簇索引。

主键索引是InnoDB自动生成的。如果没有设置主键，则InnoDB会默认在表中生成一个隐藏的主键DB_ROW_ID。

image-20230731170825856

特点：

  1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

    • 页内的记录是按照主键的大小顺序排成一个单向链表。

    • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。

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

  2. B+树的叶子节点存储的是完整的用户记录。所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。索引即数据，数据即索引。

  3. 如果使用InnoDB引擎，创建表后会产生两个文件：

    1. 表名.frm：保存表的定义
    2. 表名.ibd：保存表的数据和索引

image-20230731170708198

优点：

  • 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快；
  • 聚簇索引对于主键的排序查找和范围查找速度非常快；
  • 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

缺点：

  • 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增ID列为主键。
  • 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
② 二级索引（辅助索引、非聚簇索引、普通索引）

如果执行下面的SQL语句：

1
select * from table where name="Hongyi";

为了加快搜索，我们在name字段上建立一个索引：

image-20231028154921879

但是存在一个问题，即相同的数据被记录了两次，一个是主键索引，一个是name索引。

现在改造name索引，叶子结点不存放完整的字段，而只存放name列和主键列，每个记录由name列进行排序：

image-20231028155544733

现在执行上面的sql语句时，首先根据name索引快速查找到用户记录（叶子结点）的主键，然后拿到主键再在主键索引中快速查找到完整的用户记录，这个过程称之为回表。如果name列不存在唯一性约束，即存在相同的name，则会查找到多个符合条件的主键，则需要进行多次回表。

image-20231028155926249

非聚集索引：非聚集索引是索引结构和数据分开存放的索引。

如果使用MyISAM引擎，创建表后会产生三个文件：

image-20230731171045989

  1. 表名.frm：保存表的定义
  2. 表名.myi：保存表的索引
  3. 表名.myd：保存表的数据

image-20230731170936442

image-20230621172610973

概念：回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到2棵B+树！

③ 联合索引

联合索引是对两个及以上的普通字段建立的索引。例如执行：

1
select * from user where name="Hongyi" and phone="123";

现在为加快查找速度，我们在name列和phone列上建立联合索引。现在用户记录先按照name列排序，如果name列相同，则按照phone列排序。

image-20231028160328885

联合索引需要遵守最左匹配原则。如果执行下面的sql：

1
select * from user where phone="123";

此时phone列在索引中是乱序的，索引失效。

3.4.4 注意事项

根页面位置万年不动

  • 每当为某个表创建一个B+树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
  • 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
  • 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

这个过程特别注意的是：一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

3.5 MyISAM的索引

3.5.1 索引原理

B树索引适用存储引擎如表所示：

索引/存储引擎 MyISAM InnoDB Memory
B-Tree索引 支持 支持 支持

即使多个存储引擎支持同一种类型的索引，但是他们的实现原理也是不同的。Innodb和MyISAM默认的索引是Btree索引；而Memory默认的索引是Hash索引。

MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址。

image-20230621173946740

如果我们在Col2上建立一个二级索引，则此索引的结构如下图所示：

image-20230621174018753

3.5.2 与InnoDB对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。

小结两种引擎中索引的区别：

① 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引。

② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

③ InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。

④ MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

⑤ InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

image-20230621173217351

3.6 索引的代价

索引是个好东西，但不能乱建，它在空间和时间上都会有消耗：

  • 空间上的代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。

  • 时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

4 索引的创建与设计

4.1 索引的声明与使用

4.1.1 索引分类

  • 从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
  • 按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。
  • 按照作用字段个数进行划分，分成单列索引和联合索引。

4.1.2 创建索引

语法：

1
2
CREATE TABLE table_name [col_name data_type] 
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
  • UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；
  • INDEX与KEY为同义词，两者的作用相同，用来指定创建索引；
  • index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认列名为索引名；
  • col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
  • length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
  • ASC或DESC指定升序或者降序的索引值存储。

4.2 索引的设计原则

4.2.1 适合建立索引的场景

  • 字段有唯一性约束：如果某个字段是唯一的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录。
  • 频繁作为 WHERE 查询条件的字段：某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
  • 经常 GROUP BY 和 ORDER BY 的列：索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。
  • 使用最频繁的列放到联合索引的左侧
  • 限制索引的数目：在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。
    • 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
    • 索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
    • 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。
  • 在多个字段都要创建索引的情况下，联合索引优于单值索引

4.2.2 不适合建立索引的场景

  • 在where中使用不到的字段，不要设置索引
  • 数据量小的表最好不要使用索引
  • 有大量重复数据的列上不要建立索引：造成很多次回表
  • 避免对经常更新的表创建过多的索引：表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
  • 删除不再使用或者很少使用的索引

5 性能分析工具

暂略

6 索引与查询优化

6.1 索引失效

  • 使用联合索引时违背最左匹配原则

最左匹配原则：必须按照索引建立时的顺序，依次满足。

  1. 一旦跳过某个字段，索引后面的字段都无法被使用（部分失效）。
  2. 如果查询条件中没有使用这些字段中第1个字段时，多列（或联合）索引不会被使用（全部失效）。
  3. 当遇到范围查询(>、<、between、like)就会停止匹配

例如：某表现有索引(a,b,c)

1
2
3
4
5
6
7
8
9
10
11
12
13
select * from t where a=1 and b=1 and c=1; #这样可以利用到定义的索引（a,b,c）,用上a,b,c

select * from t where a=1 and b=1; #这样可以利用到定义的索引（a,b,c）,用上a,b

select * from t where b=1 and a=1; #这样可以利用到定义的索引（a,b,c）,用上a,b（mysql有查询优化器）

select * from t where a=1; #这样也可以利用到定义的索引（a,b,c）,用上a
 
select * from t where b=1 and c=1; #这样不可以利用到定义的索引（a,b,c）
 
select * from t where a=1 and c=1; #这样可以利用到定义的索引（a,b,c），但只用上a索引，b,c索引用不到

select * from t where a=1 and b>1 and c=1; #这样a,b可以用到（a,b,c），c索引用不到
  • 索引列上参与计算会导致索引失效
1
select * from student where id-1 = 1
  • OR 前后存在非索引的列，索引失效

如果birthday列不存在索引，则下面的语句会导致索引失效

1
SELECT * FROM student where id =1 or birthday = "2021-12-23"
  • like以通配符%开头索引失效
1
select * from student where name like "%三"
  • is null可以使用索引，is not null无法使用索引
  • 不等于(!= 或者<>)索引失效
1
select * from student where id!=1
  • 列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引
1
select * from student where name=张三

6.2 子查询优化

子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。原因：

  • 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
  • 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
  • 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

因此尽量不要使用子查询，而是使用连接join来替代。

6.3 分页查询优化

  • 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

比如查询2000001~2000010的数据：

1
2
3
SELECT * 
FROM student AS t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) AS a
WHERE t.id = a.id;
  • 该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。
1
SELECT * FROM student WHERE id > 2000000 LIMIT 10;
  • 使用覆盖索引

索引列+主键包含SELECT到FROM之间查询的列。

6.4 其他优化策略

暂略

7 调优策略

7.1 调优步骤

7.1.1 选择合适的DBMS

例如选用MySQL，Oracle或者其他DBMS。

7.1.2 优化表设计

  • 根据不同的需求选择不同的存储引擎（事务、锁等）
  • 表结构遵从三范式设计（属性不可分割-消除部分依赖-消除传递依赖），减少冗余字段
  • 数据类型的选择

7.1.3 优化逻辑查询

  • 子查询优化（小表驱动大表）
  • 等价谓词重写
  • 视图重写
  • 条件简化
  • 连接消除

7.1.4 优化物理查询

主要是索引的优化。

7.1.5 使用中间件缓存

因为数据都是存放到数据库中，我们需要从数据库层中取出数据放到内存中进行业务逻辑的操作，当用 户量增大的时候，如果频繁地进行数据查询，会消耗数据库的很多资源。如果我们将常用的数据直接放到内存中，就会大幅提升查询的效率。

常用中间件缓存：Redis，Memcached等

7.1.6 库级优化

控制库中数据表的数量，采用多个库而非单个库等。

  • 读写分离

采用读写分离，降低主数据库的负载，例如使用主库完成写操作，从库完成读操作。

image-20240504185136387

  • 数据分片

对数据库进行分库分表。将一个数据库切分成多份，放在不同的数据库服务器上。

image-20240504185312366

7.2 优化数据库结构

7.2.1 冷热数据分离

拆分表的思路是，把1个包含很多字段的表拆分成2个或者多个相对较小的表。这样做的原因是，这些表中某些字段的操作频率很高(热数据)，经常要进行查询或者更新操作，而另外一些字段的使用频率却很低(冷数据)，冷热数据分离，可以减小表的宽度。如果放在一个表里面，每次查询都要读取大记录，会消耗较多的资源。

目的：

  • 减少磁盘IO，保证热数据的内存缓存命中率。
  • 更有效的利用缓存，避免读入无用的冷数据。

7.2.2 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表把需要经常联合查询的数据插入中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

7.2.3 增加冗余字段

设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。但是，合理地加入冗余字段可以提高查询速度。

表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。尤其在数据量大，而且需要频繁进行连接的时候，为了提升效率，我们也可以考虑增加冗余字段来减少连接。

7.2.4 优化数据类型

  • 对整数类型数据进行优化

遇到整数类型的字段可以用 INT 型 。这样做的理由是，INT 型数据有足够大的取值范围，不用担心数据超出取值范围的问题。

对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型 UNSIGNED 来存储。因为无符号相对于有符号，同样的字节数，存储的数值范围更大。

  • 既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型。

跟文本类型数据相比，大整数往往占用更少的存储空间 ，因此，在存取和比对的时候，可以占用更少的内存空间。所以，在二者皆可用的情况下，尽量使用整数类型，这样可以提高查询的效率。如：将IP地址转换成整型数据。

  • 使用TIMESTAMP存储时间

  • 用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

8 事务

在 MySQL 中，只有 InnoDB 是支持事务的。

8.1 数据库事务概述

8.1.1 基本概念

事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态。

事务处理的原则：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的操作都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。

8.1.2 ACID

  • 原子性（atomicity）：

原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。

  • 一致性（consistency）：

一致性是指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态。这种状态是语义上的而不是语法上的，跟具体的业务有关。

  • 隔离性（isolation）：

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

  • 持久性（durability）：

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志。当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

事务机制的实现：

  • 隔离性：锁
  • 原子性、一致性、持久性：redo日志和undo日志
    • REDO LOG 称为 重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。
    • UNDO LOG 称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。

8.1.3 事务的状态

  • 活动的（active）

事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态。

  • 部分提交的（partially committed）

当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在部分提交的状态。

  • 失败的（failed）

当事务处在活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。

  • 中止的（aborted）

如果事务执行了一部分而变为失败的状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态。换句话说，就是要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称之为回滚。当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了中止的状态。

  • 提交的（committed）

当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处在了提交的状态。

image-20230621180537153

8.2 事务的使用

使用事务有两种方式，分别为显式事务和隐式事务。

8.2.1 显式事务

步骤1： START TRANSACTION或者BEGIN，作用是显式开启一个事务。

1
2
3
mysql> BEGIN; 
#或者 
mysql> START TRANSACTION;

START TRANSACTION语句相较于BEGIN特别之处在于，后边能跟随几个修饰符：

  • READ ONLY：标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
  • READ WRITE：标识当前事务是一个读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
  • WITH CONSISTENT SNAPSHOT：启动一致性读。

步骤2：一系列事务中的操作（主要是DML，不含DDL）

步骤3：提交事务或中止事务（即回滚事务）

1
2
# 提交事务。当提交事务后，对数据库的修改是永久性的。
mysql> COMMIT;
1
2
3
4
5
# 回滚事务。即撤销正在进行的所有没有提交的修改 
mysql> ROLLBACK; 

# 将事务回滚到某个保存点。 
mysql> ROLLBACK TO [SAVEPOINT]

其中关于SAVEPOINT相关操作有：

1
2
# 在事务中创建保存点，方便后续针对保存点进行回滚。一个事物中可以存在多个保存点。
SAVEPOINT 保存点名称;
1
2
# 删除某个保存点
RELEASE SAVEPOINT 保存点名称;

8.2.2 隐式事务

MySQL中有一个系统变量 autocommit，默认为开启：

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    |   ON  |
+---------------+-------+
1 row in set (0.01 sec)

自动提交的作用：

  • 情况一：在开启autocommit的情况下，对于所有的SQL命令，都将自动提交到MySQL数据库中，即在命令执行完毕后，MySQL会自动执行COMMIT。

例如，A执行了下面的语句：

1
insert into test values (a);

此时B查表，可以看到a，因为A执行该语句后，mysql隐式地执行了：

1
commit;
  • 情况二：关闭autocommit的情况下，则需要使用COMMIT来手动提交事务到数据库中。

例如，A执行了下面的语句：

1
insert into test values (a);

此时B查表，没有a的数据；A再执行：

1
commit;

B再查表，可以看到a。

  • 情况三：当autocommit为ON的情况下，并且又手动开启了事务，那么mysql会把start transaction与commit之间的语句当做一次事务来处理，默认并不会帮用户提交需要手动提交，如果用户不提交便退出了，那么事务将回滚。

例如，A执行了下面的语句：

1
2
begin;
insert into test values (a);

此时B查表，没有a的数据；A再执行：

1
commit;

B再查表，可以看到a。

如果A没有commit，直接退出，则事务回滚，a的数据将丢失。

如果我们想关闭这种自动提交的功能，可以使用下边两种方法之一：

  • 显式地使用START TRANSACTION或者BEGIN语句开启一个事务（情况三）。这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。
  • 把系统变量autocommit的值设置为OFF（情况二）
1
2
3
SET autocommit = OFF;
#或
SET autocommit = 0;
  • 当我们在一个事务还没提交或者回滚时就又使用START TRANSACTION或者BEGIN语句开启了另一个事务时，会隐式的提交上一个事务。
  • 当前的autocommit系统变量的值为OFF，我们手动把它调为ON时，也会隐式的提交前边语句所属的事务。
  • 使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句也会隐式的提交前边语句所属的事务。

8.2.3 示例

在默认状态下（打开自动提交）。

示例1

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE user(name varchar(20), PRIMARY KEY (name)) ENGINE=InnoDB;

BEGIN;
INSERT INTO user VALUES '张三';
COMMIT;

BEGIN;
INSERT INTO user VALUES '李四';
INSERT INTO user VALUES '李四';
ROLLBACK;

SELECT * FROM user;

执行结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> COMMIT;
Query OK, 0 rows affected (0.00 秒)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 秒)
mysql> INSERT INTO user VALUES '李四';
Query OK, 1 rows affected (0.00 秒)
mysql> INSERT INTO user VALUES '李四';
Duplicate entry '李四' for key 'user.PRIMARY'
mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 秒)
mysql> SELECT * FROM user;
+--------+
|  name  |
+--------+
|   张三 |
+--------+
1 行于数据集 (0.01 秒)

说明：张三的数据正常提交，而事务回滚，李四没有添加进表。

示例2

1
2
3
4
5
6
7
8
9
CREATE TABLE user (name varchar(20), PRIMARY KEY (name)) ENGINE=InnoDB;
  
BEGIN;
INSERT INTO user VALUES '张三';
COMMIT;

INSERT INTO user VALUES '李四';
INSERT INTO user VALUES '李四';
ROLLBACK;

执行结果：

1
2
3
4
5
6
7
8
mysql> SELECT * FROM user;
+--------+
| name   |
+--------+
| 张三 |
| 李四 |
+--------+
2 行于数据集 (0.01 秒)

说明：张三正常提交，对于李四，第一条插入语句后面隐式地跟上了commit，因此正常提交，而第二条插入回滚。

8.3 事务的隔离级别

MySQL是一个客户端/服务器架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称为一个会话（ Session ）。每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。事务有隔离性的特性，理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样对性能影响太大，我们既想保持事务的隔离性，又想让服务器在处理访问同一数据的多个事务时性能尽量高些 ，那就看二者如何权衡取舍了。

8.3.1 数据并发问题

  • 脏写

对于两个事务 Session T1、Session T2，如果事务Session T2修改了另一个未提交事务Session T1修改过的数据，那就意味着发生了脏写。

image-20231030201739234

  • 脏读

Session T2读取了已经被 Session T1更新但还没有被提交的字段。之后若 Session T1回滚，Session T2读取的内容就是临时且无效的。

image-20231030201847658

  • 不可重复读

Session T2读取了一个字段，然后 Session T1更新了该字段。 之后Session T2再次读取同一个字段，值就不同了。那就意味着发生了不可重复读。

image-20231030202015580

  • 幻读

Session T1从一个表中读取了一个字段，然后 Session T2 在该表中插入了一些新的行。 之后，如果 Session T1再次读取同一个表，就会多出几行。那就意味着发生了幻读。

image-20231030202126995

问题严重性：

1
脏写 > 脏读 > 不可重复读 > 幻读

8.3.2 SQL的隔离级别

隔离级别 脏读 不可重复读 幻影读
未提交读 √ √ √
提交读 × √ √
可重复读 × × √
可串行化 × × ×

我们愿意舍弃一部分隔离性来换取一部分性能在这里就体现在：设立一些隔离级别，隔离级别越低，并发问题发生的就越多。 SQL标准 中设立了4个 隔离级别 ：

  • READ UNCOMMITTED：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。
  • READ COMMITTED：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可重复读、幻读问题仍然存在。
  • REPEATABLE READ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。
  • SERIALIZABLE：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读。

隔离级别与并发性能的关系：

image-20230625171358916

8.3.3 MySQL的隔离级别

MySQL的默认隔离级别为REPEATABLE READ：

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

通过下面的语句修改事务的隔离级别：

1
2
3
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;
# 或者
SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别';
  • 使用 GLOBAL 关键字（在全局范围影响）：
    • 当前已经存在的会话无效
    • 只对执行完该语句之后产生的会话起作用
  • 使用 SESSION 关键字（在会话范围影响）：
    • 对当前会话的所有后续的事务有效
    • 如果在事务之间执行，则对后续的事务有效
    • 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务

8.4 事务日志

8.4.1 REDO日志

① 问题概述

InnoDB存储引擎是以页为单位来管理存储空间的，进行的增删改查操作其实本质上都是在访问页面（包括读页面、写页面、创建新页面等操作）。前边学习Buffer Pool的时候说过（[[MySQL高级学习笔记#1.3 数据库缓冲池]]），在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。但是在学习事务的时候又强调过一个称之为持久性的特性，就是说对于一个已经提交的事务，在事务提交后即使系统发生了崩溃，这个事务对数据库中所做的更改也不能丢失。但是如果我们只在内存的Buffer Pool中修改了页面，假设在事务提交后突然发生了某个故障，导致内存中的数据都失效了，那么这个已经提交了的事务对数据库中所做的更改也就跟着丢失了，这是我们所不能忍受的。那么如何保证这个持久性呢？

  • 解决方法1：在事务提交时，把该事务修改的所有页面都刷新到磁盘。（内存->硬盘）

缺点：

刷新一个完整的数据页太浪费了：虽然我们只修改了一条记录，但是会将这条记录所在的页（16KB）都刷新到磁盘上，会造成大量磁盘I/O的浪费。

随机I/O刷新起来比较慢：一个事务可能包含很多语句，即使是一条语句也可能修改许多页面，并且该事务修改的这些页面可能并不相邻。这就意味着将某个事务修改的Buffer Pool中的页面刷新到磁盘时，需要进行很多的随机I/O。而随机I/O要比顺序I/O慢，尤其是机械硬盘。

  • 解决方法2：在事务提交时，只需要把修改的内容记录一下就好了。

例如记录：“将第0号表空间第100号页面中偏移量为1000处的值更新为2。”，这就是redo日志。

优点：

  • redo日志占用的空间非常小；
  • redo日志是顺序写入磁盘的：在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序I/O。

InnoDB引擎的事务采用了WAL技术 (Write-Ahead Logging)，这种技术的思想就是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这里的日志就是redo log。当发生宕机且数据未刷到磁盘的时候，可以通过redo log来恢复，保证ACID中的D，这就是redo log的作用。

② redo日志流程

redo日志本质上只是记录了事务对数据库做了哪些修改。

以一个更新事务为例，redo log 流转过程，如下图所示：

image-20240504193446686

  1. 先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝
  2. 生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值（先写日志）
  3. 当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式
  4. 定期将内存中修改的数据刷新到磁盘中（后写磁盘）
③ 刷盘策略

redo log的写入并不是直接写入磁盘的，InnoDB引擎会在写redo log的时候先写redo log buffer（内存中），之后以一定的频率刷入到真正的redo log file中（硬盘）。这里的一定频率怎么看待呢？这就是我们要说的刷盘策略。

image-20240504193737594

InnoDB给出 innodb_flush_log_at_trx_commit 参数，该参数控制commit提交事务时，如何将 redo log buffer 中的日志刷新到 redo log file 中。它支持三种策略：

  • 设置为0 ：表示每次事务提交时不进行刷盘操作，但系统默认master thread每隔1s进行一次重做日志的同步。

image-20240504194050650

  • 设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）

image-20240504194000137

  • 设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件。

image-20240504194026756

8.4.2 UNDO日志

undo log是事务原子性的保证。在事务中 更新数据 的 前置操作 其实是要先写入一个 undo log 。

① 概念

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

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

以上情况出现，我们需要把数据改回原先的样子，这个过程称之为 回滚 ，这样就可以造成一个假象：这个事务看起来什么都没做，所以符合 原子性 要求。

  • 插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉即可
  • 删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中即可
  • 修改了一条记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值即可
② 过程

image-20240504194845310

9 锁

事务的隔离性由锁来实现。

9.1 概述

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。为保证数据的一致性，需要对并发操作进行控制，因此产生了锁。同时锁机制也为实现MySQL的各个隔离级别提供了保证。 锁冲突也是影响数据库并发访问性能的一个重要因素。所以锁对数据库而言显得尤其重要，也更加复杂。

9.2 并发访问

下面分析两个并发事务

  • 读-读

读-读情况，即并发事务相继读取相同的记录。读取操作本身不会对记录有任何影响，并不会引起什么问题，所以允许这种情况的发生。

  • 写-写

写-写情况，即并发事务相继对相同的记录做出改动。

在这种情况下会发生脏写的问题，任何一种隔离级别都不允许这种问题的发生。所以在多个未提交事务相继对一条记录做改动时，需要让它们排队执行，这个排队的过程其实是通过锁来实现的。

这个所谓的锁其实是一个内存中的结构，在事务执行前本来是没有锁的。

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

比如，事务 T1 要对这条记录做改动，就需要生成一个锁结构与之关联。

T1获取锁：

image-20230625172811344

T2尝试获取锁，失败：

image-20230625172909811

T1提交后，释放锁，T2获得锁：

image-20230625172934459

  • 读-写 或 写-读

读-写或写-读，即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生脏读、不可重复读、幻读的问题。

解决方案

  • 方案一：读操作利用多版本并发控制（MVCC，详见第10大节），写操作进行加锁。

  • 方案二：读、写操作都采用加锁的方式。

对比发现：

  • 采用MVCC方式的话，读-写操作彼此并不冲突，性能更高。
  • 采用加锁方式的话，读-写操作彼此需要排队执行，影响性能。

一般情况下我们当然愿意采用MVCC来解决读-写操作并发执行的问题，但是业务在某些特殊情况下，要求必须采用加锁的方式执行。

MySQL中锁的分类

image-20230625173239445

9.3 从数据操作的类型划分

  • 读锁：也称为共享锁、英文用S表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。
  • 写锁：也称为排他锁、英文用X表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

需要注意的是对于 InnoDB 引擎来说，读锁和写锁可以加在表上，也可以加在行上。MyISAM引擎不支持行级锁。

9.4 从锁的粒度划分

9.4.1 表锁

该锁会锁定整张表，它是MySQL中最基本的锁策略，并不依赖于存储引擎，并且表锁是开销最少的策略。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁的问题。当然，锁的粒度大所带来最大的负面影响就是出现锁资源争用的概率也会最高，导致并发率大打折扣。

① 表级别的读写锁

后略

10 多版本并发控制

11 日志

11.1 日志类型

MySQL支持的日志类型如下：

  • 慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。

  • 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。

  • 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。

  • 二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。

  • 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。

  • 数据定义语句日志：记录数据定义语句执行的元数据操作。

除二进制日志外，其他日志都是文本文件。默认情况下，所有日志创建于MySQL数据目录中。

11.2 通用查询日志

通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助我们准确定位问题。

11.3 错误日志

暂略

11.4 二进制日志

binlog可以说是MySQL中比较重要的日志了，在日常开发及运维过程中，经常会遇到。

binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的 DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。

主要应用场景：

  • 数据恢复
  • 数据复制

image-20231029095456801

11.4.1 查看默认情况

在MySQL8中默认情况下，二进制文件是开启的。

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| log_bin                         | ON                               |
| log_bin_basename                | /var/lib/mysql/binlog            |
| log_bin_index                   | /var/lib/mysql/binlog.index      |
| log_bin_trust_function_creators | OFF                              |
| log_bin_use_v1_row_events       | OFF                              |
| sql_log_bin                     | ON                               |
+---------------------------------+----------------------------------+
6 rows in set (0.00 sec)