嘘~ 正在从服务器偷取页面 . . .

数据库 八股文

Redis MySQL
八股文
发布日期:   2022-04-26
更新日期:   2023-03-17
文章字数:   40.4k
阅读时长:   145 分
阅读次数:  

MySQL

1. InnoDB

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎。

5.5 版本之后，MySQL 引入了 InnoDB（事务性数据库引擎），作为默认引擎。

InnoDB 的优势：

  • MyISAM 只有表级锁（table-level locking），而 InnoDB 支持行级锁（row-level locking）和表级锁，默认为行级锁；
  • InnoDB 实现了四个标准的隔离级别。其默认级别是 REPEATABLE READ，并通过间隙锁策略防止幻读，间隙锁使 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定防止幻行的插入；
  • InnoDB 提供事务支持，具有提交（commit）和回滚（rollback）事务的能力，InnoDB 支持外键；
  • 使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态；
  • InnoDB 支持 MVCC（Multi-Version Concurrency Control，即多版本并发控制）。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

在正常的开发中，选择默认的 InnoDB 没有什么问题。

1.1 数据结构

在 MySQL 中，数据存储在物理磁盘上，数据操作在内存中执行，所以不能对磁盘反复读写。

InnoDB 将数据划分为若干页，以页作为磁盘与内存交互的基本单位，一般页的大小为 16KB。一次至少读取 1 页数据到内存中或者将 1 页数据写入磁盘，减少内存与磁盘的交互次数。这是一种典型的缓存设计思想。

时间维度：如果一条数据正在在被使用，那么在接下来一段时间内大概率还会再被使用。可以认为热点数据缓存都属于这种思路的实现；

空间维度：如果一条数据正在在被使用，那么存储在它附近的数据大概率也会很快被使用。InnoDB 的数据页和操作系统的页缓存则是这种思路的体现。

1.2 InnoDB 行格式

MySQL 以记录(一行数据)为单位向数据表中插入数据，这些记录在磁盘上的存放方式称为行格式。

MySQL 支持4种不同类型的行格式：Compact、Redundant、Dynamic、Compressed。 我们可以在创建或修改表的语句中指定行格式。

Redundant 是比较老的数据格式，Compressed 不能应用在System data；所以 Compact 和 Dynamic 应用较广泛。Version 5.6 默认使用 Compact，Version 5.7 默认使用 Dynamic。

Compact 行格式示意图

从上图可以看出，一条完整的记录包含记录的额外信息和记录的真实数据两部分。

1.2.1 额外信息

# 示例数据库结构
MySQL> CREATE TABLE record_format_demo (
    ->     c1 VARCHAR(10),
    ->     c2 VARCHAR(10) NOT NULL,
    ->     c3 CHAR(10),
    ->     c4 VARCHAR(10)
    -> ) CHARSET=ascii ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.03 sec)

# 示例数据
MySQL> SELECT * FROM record_format_demo;
+------+-----+------+------+
| c1   | c2  | c3   | c4   |
+------+-----+------+------+
| aaaa | bbb | cc   | d    |
| eeee | fff | NULL | NULL |
+------+-----+------+------+
2 rows in set (0.00 sec)

存储内部结构示例图

变长字段长度列表

MySQL 中支持一些变长数据类型（比如VARCHAR(M)、TEXT等）（CHAR为固定字长数据类型），它们存储数据占用的存储空间不是固定的，而是会随着存储内容的变化而变化。

变长字段占用的存储空间要包含：

  1. 真正的数据内容；
  2. 占用的字节数。

在 Compact 行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放（只储存非 NULL 列内容的长度）。

NULL 值列表

对于可为 NULL 的列，为了节约存储空间，不会将 NULL 值保存在记录的真实数据部分。而是会将其保存在 记录的额外信息 里面的 NULL值列表 中（所以在设计数据库中，尽量让列都不可为 NULL）。

先统计表中允许存储 NULL 值的列，然后将每个允许存储 NULL 值的列对应一个二进制位（1：值为 NULL，0：值不为 NULL）用来表示是否存储NULL值，并按照逆序排列。规定 NULL 值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。

记录头信息

记录头信息是由固定的5个字节(40位)组成, 不同的位代表不同的含义。

记录头示例图

名称 大小 bit 描述
预留位1 1 没有使用
预留位2 1 没有使用
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 表示下一条记录的相对位置

  • delete_mask：标记着当前记录是否被删除，0表示未删除，1表示删除。未删除的记录不会立即从磁盘上移除，而是先打上删除标记，所有被删除的记录会组成一个垃圾链表。之后新插入的记录可能会重用垃圾链表占用的空间，因此垃圾链表占用的存储空间也被称为可重用空间；

  • heap_no：表示当前记录在本页中的位置，InnoDB 自动为每页加上两条虚拟记录，一条是最小记录，另一条是最大记录。这由5字节大小的记录头信息和8字节大小的固定部分(其实内容是 infimum 或者 supremum)组成的。这两条记录被单独放在Infimum + Supremum的部分。

  • next_record：表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。可以简单理解为是一个单向链表，最小记录的下一个是第一条记录，最后一条记录的下一个是最大记录；

地址偏移指向示意图

在删除记录的时候，也可以类比成将一个节点从链表中删除。

record1 指向了 record3

1.2.2 记录的真实数据

除了本身的数据之外，还有隐藏列的数据：

列名 是否必须 占用空间 描述
DB_ROW_ID 否 6字节 行ID，唯一标识一条记录（在没有指定主键的时候生效）
DB_TRX_ID 是 6字节 事务ID
DB_ROLL_PTR 是 7字节 回滚指针

对于 CHAR(M) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。 还需要注意，变长字符集的CHAR(M)类型的列要求至少占用M个字节，而VARCHAR(M)却没有这个要求。比如对于使用utf8字符集的CHAR(10)的列来说，该列存储的数据字节长度的范围是10～30个字节，即使我们向该列中存储一个空字符串也会占用10个字节。

1.2.3 行溢出

MySQL 对一条记录占用的最大存储空间有限制，除了BLOB或者TEXT类型的列，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。可以不严谨的认为，一行记录占用的存储空间不能超过65535个字节。

这65535个字节除了列本身的数据之外，还包括其他的数据（storage overhead），比如存储一个 VARCHAR(M) 类型的列，需要占用3部分存储空间：

  1. 真实数据；
  2. 真实数据占用字节的长度；
  3. NULL值标识，如果该列有NOT NULL属性则可以没有这部分存储空间。
记录数据过多

MySQL 中磁盘与内存交互的基本单位是页，一般为16KB，16384个字节，而一行记录最大可以占用65535个字节，这就造成了一页存不下一行数据的情况。在 Compact 和 Redundant 行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址，从而可以找到剩余数据所在的页。

分页储存示例

其他行格式

MySQL中默认的行格式就是Dynamic。Dynamic和Compressed行格式和Compact行格式很像，只是在处理行溢出数据上有差异。Dynamic和Compressed行格式不会在记录的真实数据出存放前768个字节，而是将所有字节都存储在其它页面中。Compressed行格式会采用压缩算法对页面进行压缩，以节省空间。

1.3 InnoDB 数据页结构

数据页在结构上可以划分为多个部分：

数据页结构

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

用户自己的存储的数据会按照对应的行格式存在User Records中。实际上，新生成的页面是没有User Records的，只有当我们第一次插入数据时，才会从Free Space划一个记录大小的空间给User Records。当Free Space用完之后，就意味着当前的数据页也使用完了。

1.3.1 Page Directory

MySQL 使用了Page Directory（页目录）来解决查询需要遍历链表的问题（数据通过链表存储）。

大致原理如下：

  1. 将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组；

  2. 每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的 n_owned 属性表示该组内共有几条记录；

  3. 将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页尾部的地方，这个地方就是所谓的Page Directory。

分组示例图

查找指定主键值的记录的过程分为两步：

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

1.3.2 Page Header

Page Header专门用来存储数据页相关的各种状态信息，比如本页中已经存储了多少条记录，第一条记录的地址是什么，页目录中存储了多少个槽等等。固定占用56个字节。

1.3.3 File Header

File Header用来描述各种页都适用的一些通用信息。

我们重点关注其中几个属性：

  1. FIL_PAGE_SPACE_OR_CHKSUM：当前页面的校验和（checksum）。对于很长的字节串，我们可以通过某种算法来计算一个比较短的值来代表这个很长的字节串，这个值被称为校验和。通过校验和可以大幅度提升字符串等值比较的效率；
  2. FIL_PAGE_OFFSET：每一个页都有一个唯一的页号，InnoDB通过页号来可以定位一个页；
  3. FIL_PAGE_PREV和FIL_PAGE_NEXT：表示本页的上一个和下一个页的页号，形成一个双向链表。

数据页

1.3.4 File Trailer

MySQL 中内存和磁盘的基本交互单位是页。如果内存中页被修改，那么某个时刻一定会将内存页同步到磁盘中。如果在同步的过程中，系统出现问题，就可能导致磁盘中的页数据没能完全同步，也就是发生了脏页的情况。为了避免这种问题，每个页的尾部都加上了File Trailer来校验页的完整性。由8个字节组成：

  1. 前4个字节代表页的校验和，这个部分和 File Header 中的校验和相对应。简单理解，就是File Header和File Trailer都有校验和，如果两者一致则表示数据页是完整的。否则，则表示数据页是脏页；
  2. 后4个字节代表页面被最后修改时对应的日志序列位置（LSN）这个部分也是为了校验页的完整性的。

1.4 区、段和碎片区

1.4.1 区

B+ 树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间，双向链表相邻的两个页之间的物理位置可能离得非常远。B+ 树索引的使用场景中，范围查询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了。而如果链表中相邻的两个页物理位置离得非常远，就会发生随机 I/O。随机 I/O 是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用顺序 I/O。

所以引入区的概念，一个区就是物理位置上连续的 64 个页。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页的单位分配了，而是按照区为单位分配，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费（数据不足以填充满整个区），但是从性能角度看，可以消除很多的随机 I/O。

1.4.2 段

对于范围查询，是对 B+ 树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣了。所以 InnoDB 对 B+ 树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的区的集合就算是一个段（segment），存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成 2个段，一个叶子节点段，一个非叶子节点段。

除了索引的叶子节点段和非叶子节点段之外，InnoDB 中还有为存储一些特殊的数据而定义的段，比如回滚段。所以，常见的段有数据段、索引段、回滚段。数据段即为 B+ 树的叶子节点，索引段即为 B+ 树的非叶子节点。

在 InnoDB 存储引擎中，对段的管理都是由引擎自身所完成，DBA 不能也没有必要对其进行控制。这从一定程度上简化了 DBA 对于段的管理。

段其实不对应表空间中的某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成。

1.4.3 碎片区

默认情况下，一个使用 InnoDB 存储引擎的表只有一个聚簇索引，一个索引会生成 2个段，而段是以区为单位申请存储空间的，一个区默认占用 1M（64*16KB=1024KB）存储空间，所以默认情况下一个只存在几条记录的小表也需要 2M 的存储空间么？以后每次添加一个索引都要多申请 2M 的存储空间么？这对于存储记录比较少的表简直是天大的浪费。这个问题的症结在于到现在为止我们介绍的区都是非常纯粹的，也就是一个区被整个分配给某一个段，或者说区中的所有页面都是为了存储同一个段的数据而存在的，即使段的数据填不满区中所有的页面，那余下的页面也不能挪作他用。

为了考虑以完整的区为单位分配给某个段对于数据量较小的表太浪费存储空间的这种情况，InnoDB 提出了一个碎片（fragment）区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页面用于段 A，有些页面用于段 B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。

所以此后为某个段分配存储空间的策略是这样的：

  • 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的；
  • 当某个段已经占用了 32个碎片区页面之后，就会申请以完整的区为单位来分配存储空间。

所以现在段不能仅定义为是某些区的集合，更精确的应该是某些零散的页面以及一些完整的区的集合。

1.4.4 区的分类

区大体上可以分为 4种类型：

  • 空闲的区 (FREE)：现在还没有用到这个区中的任何页面；
  • 有剩余空间的碎片区 (FREE_FRAG)：表示碎片区中还有可用的页面；
  • 没有剩余空间的碎片区 (FULL_FRAG)：表示碎片区中的所有页面都被使用，没有空闲页面；
  • 附属于某个段的区 (FSEG)：每一索引都可以分为叶子节点段和非叶子节点段。

处于 FREE、FREE_FRAG 以及 FULL_FRAG 这三种状态的区都是独立的，直属于表空间。而处于 FSEG 状态的区是附属于某个段的。

1.5 其他引擎

MySQL 5.5 及之前，MyISAM 是默认存储引擎（访问速度比较快）。MyISAM 不支持事务，MyISAM 支持表级锁，不支持行级锁，表不支持外键，该存储引擎存有表的行数，count 运算会更快。适合查询频繁，不适合对于增删改要求高的情况。

Memory 存储引擎将所有数据都保存在内存，不需要磁盘 IO。支持哈希索引，因此查找速度极快。Memory 表使用表级锁，因此并发写入的性能较低。

2. 三大日志

2.1 binlog

binlog 是 MySQL sever 层维护的一种二进制日志，主要是记录对数据更新或潜在发生更新的 SQL 语句，并以”事务”的形式保存在磁盘中。

主要使用途径：

  1. 主从复制：replication 在 master 端开启 binlog，master 把它的二进制日志传递给 slaves 来达到 master-slave 数据一致的目的（即从服务器执行主服务器 binlog 日志内容）；

  2. 数据恢复：通过 binlog 工具来恢复数据。

2.1.1 设置 binlog

Binlog 日志功能默认是开启的，线上情况下 Binlog 日志的增长速度是很快的，在 MySQL 的配置文件 my.cnf 中提供一些参数来对 Binlog 进行设置。

设置此参数表示启用 binlog 功能，并制定二进制日志的存储目录
log-bin=/home/MySQL/binlog/

#MySQL-bin.* 日志文件最大字节（单位：字节）
#设置最大 100MB
max_binlog_size=104857600

#设置了只保留7天 BINLOG（单位：天）
expire_logs_days = 7

#binlog 日志只记录指定库的更新
#binlog-do-db=db_name

#binlog 日志不记录指定库的更新
#binlog-ignore-db=db_name

#写缓冲多少次，刷一次磁盘，默认0
sync_binlog=0

max_binlog_size ：Binlog 最大和默认值是 1G，该设置并不能严格控制 Binlog 的大小，尤其是 Binlog 比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性不可能做切换日志的动作，只能将该事务的所有 SQL 都记录进当前日志直到事务结束。所以真实文件有时候会大于 max_binlog_size 设定值；

expire_logs_days ：Binlog 过期删除不是服务定时执行，是需要借助事件触发才执行，事件包括：

  • 服务器重启；
  • 服务器被更新；
  • 日志达到了最大日志长度 max_binlog_size；
  • 日志被刷新。

二进制日志由配置文件的 log-bin 选项负责启用，MySQL 服务器将在数据根目录创建两个新文件 MySQL-bin.000001 和 MySQL-bin.index，若配置选项没有给出文件名，MySQL 将使用主机名称命名这两个文件，其中 .index 文件包含一份全体日志文件的清单；

sync_binlog：这个参数决定了 Binlog 日志的更新频率。默认 0 ，表示该操作由操作系统根据自身负载自行决定多久写一次磁盘。sync_binlog = 1 表示每一条事务提交都会立刻写盘。sync_binlog = n 表示 n 个事务提交才会写盘。

根据 MySQL 文档，写 Binlog 的时机是：SQL transaction 执行完，但任何相关的 Locks 还未释放或事务还未最终 commit 前。这样保证了 Binlog 记录的操作时序与数据库实际的数据变更顺序一致。

MySQL 会把用户对所有数据库的内容和结构的修改情况记入 MySQL-bin.n 文件，而不会记录 SELECT 和没有实际更新的 UPDATE 语句。

# 检查 Binlog 文件是否已开启
MySQL> show variables like '%log_bin%';
+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | ON                                 |
| log_bin_basename                | /usr/local/MySQL/data/binlog       |
| log_bin_index                   | /usr/local/MySQL/data/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)

show binary logs; #查看binlog列表
show master status; #查看最新的binlog

MySQL> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| MySQL-bin.000001 |       179 | No        |
| MySQL-bin.000002 |       156 | No        |
+------------------+-----------+-----------+
2 rows in set (0.00 sec)

# 查询二进制 Binlog 文件
MySQLbinlog MySQL-bin.000002 | more

2.1.2 记录格式

binlog 日志有三种格式，可以通过 binlog_format 参数指定。

statement

指定 statement，记录的内容是 SQL 语句原文，比如执行一条 update T set update_time=now() where id=1。

优点：在同步数据的时候，会直接执行对应的 SQL 语句。只需要记录执行语句的细节和上下文环境，避免了记录每一行的变化，在一些修改记录较多的情况下相比 ROW level 能大大减少 binlog 日志量，节约 IO，提高性能；还可以用于实时的还原；同时主从版本可以不一样，从服务器版本可以比主服务器版本高。

缺点：主从复制时，存在部分函数（如 sleep）及存储过程在 slave 上会出现与 master 结果不一致的情况。以上面的语句为例，在执行的过程中会出现时间 now() 的问题，在同步的时获取的时间不是当时的时间。并且为了保证 SQL 语句能在 slave 上正确执行，必须记录上下文信息。

row

row 格式记录的内容看不到详细信息，要通过 MySQL binlog 工具解析出来。

为了保证数据的一致性，通常使用 row 格式记录。但是这种格式，需要更大的容量来记录，比较占用空间，恢复与同步时会更消耗 IO 资源，影响执行速度。

所有的执行的语句在日志中都将以每行记录的修改细节来记录，因此，可能会产生大量的日志内容，干扰内容也较多。特别是当执行 alter table 之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中，实际等于重建了表。

statement 存储的记录

row 存储的记录

mixed

新版本的 MySQL 对 row level 做了优化，并不是所有的修改都会以 row level 记录，遇到表结构变更的时候会以 statement 模式来记录；如果 SQL 语句是 update 或 delete 等修改数据的语句，那还是会记录所有行的变更。因此，一般使用 row level 即可。

2.1.3 写入时机

事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

一个事务的 binlog 不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为 binlog cache。通过 binlog_cache_size 参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。

通过 write 和 fsync 分别将 binlog 写入 page cache 缓存，将缓存持久化到磁盘。

write 和 fsync 的时机，可以由参数 sync_binlog 控制，默认是0。为0的时候，表示每次提交事务都只 write，由系统自行判断什么时候执行 fsync。

为了安全起见，可以设置为1，表示每次提交事务都会执行 fsync，就如同 redo log 日志刷盘流程 一样。

还有一种折中方式，可以设置为 N(N>1)，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

binlog 写入时机

2.2 redo log

在系统宕机时，MySQL 会读取 redo log 中的内容恢复数据。每条 redo 记录由“表空间号 + 数据页号 + 偏移量 + 修改数据长度 + 具体修改的数据”组成。

2.2.1 刷盘时间

redo log 原理图示

（注意：缓冲池和查询缓冲不是一个东西）

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略：

  • 0 ：设置为 0 的时候，表示每次事务提交时不进行刷盘操作；
  • 1 ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）；
  • 2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache。

innodb_flush_log_at_trx_commit 参数默认为 1 ，也就是说当事务提交时会调用 fsync 对 redo log 进行刷盘

另外，InnoDB 存储引擎有一个后台线程，每隔1秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。一个没有提交事务的 redo log 记录，也可能会刷盘。

还有一种情况，当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。

2.2.2 日志文件组

硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。

可以配置为一组4个文件，每个文件的大小是 1GB，整个 redo log 日志文件组可以记录4G的内容。采用环形数组形式，从头开始写，写到末尾又回到头循环写。

redo log 写入图示

日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

  • write pos：当前记录的位置，一边写一边后移；
  • checkpoint：当前要擦除的位置，也是往后推移。

每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新。

write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log 记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。

原理图示

2.2.3 为什么用 redo log

可能有人会疑惑，为什么需要使用 redo log 来修改数据，为什么不修改的同时直接刷盘进数据页。

数据页大小是 16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，没有必要对数据页进行刷盘。而且数据页刷盘是随机写，因为一个数据页对应的位置可能在硬盘文件的随机位置，所以性能很差。

如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移量、更新值，再加上是顺序写，所以刷盘速度很快。

用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。

2.3 两阶段提交

redo log（重做日志）让 InnoDB 存储引擎拥有了崩溃恢复能力。binlog（归档日志）保证了 MySQL 集群架构的数据一致性。

这样就会出现问题，redo log 是在事务执行的过程中写入，binlog 在事务执行结束才会写入，这样会产生不一致的问题。

为了解决两份日志之间的逻辑一致问题，InnoDB 存储引擎使用两阶段提交方案。将 redo log 的写入拆成了两个步骤 prepare 和 commit，这就是两阶段提交。

使用两阶段提交后，写入 binlog 时发生异常也不会有影响，因为 MySQL 根据 redo log 日志恢复数据时，发现 redo log 还处于 prepare 阶段，并且没有对应 binlog 日志，就会回滚该事务。

日志回滚图示

2.4 undo log

在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。

如果执行过程中遇到异常的话，利用 回滚日志 中的信息将数据回滚到修改之前的样子！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性，使用 undo log + redo log 保证一致性。

3. 锁机制

MySQL 三类锁级别：全局锁、表级锁、行级锁。

  • 表级锁： MySQL 中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
  • 行级锁： MySQL 中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

3.1 并发情况

读 - 读并发

不会对数据有影响，不需要处理。

写 - 写并发

会发生脏写问题，SQL 规范的任何一个隔离级别都用锁解决了这个问题。

读 - 写并发

可能发生脏读、不可重复读、幻读的问题。

读 - 写并发问题的解决方案

  • 读写都加锁（读写操作彼此需要排队执行，性能低，但是读出来的数据始终是最新版本）；
  • 读操作采用 MVCC，写操作加锁（读写操作不冲突，性能高，无法保证读出来的数据最新）。

3.2 锁分类

3.2.1 从数据的操作类型划分

  • 共享锁/读锁/S 锁：读锁之间不互斥，多个事务的读操作可以同时进行，不会相互影响也不会相互阻塞。
  • 排他锁/写锁/X 锁：读写锁之间、写锁之间是互斥的，当前的写操作没有完成前，会阻塞其他写锁和读锁，确保在一段时间内，只有一个事务能执行写入，并且防止其他事务读取正在写入的资源。同理，在当前读操作执行时，也会阻塞其他的写操作。

在 InnoDB 中，共享锁和排他锁既可以作用在表上，也可以作用在行上。读操作可以加共享锁和排他锁，写操作一般只加排他锁。

共享锁 排他锁
共享锁 兼容 不兼容
排他锁 不兼容 不兼容
读锁
  • 给读操作加共享锁
SELECT ... LOCK IN SHARE MODE;

SELECT ... FOR SHARE; # (8.0新增语法)
# 加的是表锁
  • 给读操作加排他锁
SELECT ... LOCK IN UPDATE MODE;

SELECT ... FOR UPDATE; # (8.0新增语法)
# 加的是表锁

MySQL 8.0 新特性：

在 MySQL 5.7 及之前的版本，执行 SELECT ... FOR UPDATE ，如果获取不到锁，会一直等待，直到超时（innodb_lock_wait_timeout 变量）。在 MySQL 8.0 版本后，添加 NOWAIT、SKIP LOCKED 语法，跳过锁等待，或者跳过锁定。（SELECT ... FOR UPDATE NOWAIT）

  • NOWAIT：如果查询的行已经加锁，会立即报错返回；
  • SKIP LOCKED：如果查询的行已经加锁，只返回结果中不包含被锁定的行。
写锁
  • 给写操作加排他锁
DELETE/INSERT/UPDATE ... LOCK IN UPDATE MODE;

SELECT ... FOR UPDATE; # (8.0新增语法)
# 加的是表锁

写操作和锁的具体执行过程：

  • DELETE：对一条记录做 DELETE 操作的过程其实是先在 B+ 树中定位到这条记录的位置，然后获取这条记录的 X 锁，再执行 delete mark 操作；
  • UPDATE：在对一条记录做 UPDATE 操作时分为三种情况：
    • 情况 1：未修改该记录的键值，并且被更新的列占用的存储空间在修改前后未发生变化。则先在 B+ 树中定位到这条记录的位置，然后再获取一下记录的 X 锁，最后在原记录的位置进行修改操作；
    • 情况 2：未修改该记录的键值，并且至少有一个被更新的列占用的存储空间在修改前后发生变化。则先在 B+ 树中定位到这条记录的位置，然后获取一下记录的 X 锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。新插入的记录由 INSERT 操作提供的隐式锁进行保护；
    • 情况 3：修改该记录的键值，则相当于在原记录上做 DELECT 操作之后再来一次 INSERT 操作。
  • INSERT：一般情况下，新插入一条记录的操作并不加锁，通过一种称之为隐式锁的结构来保护这条新插入的记录在本事务提交前不被别的事务访问。

3.2.2 从锁的粒度划分

  • 表锁：锁定整张表，是 MySQL 的基本锁策略，不依赖于存储引擎。锁的粒度最大，冲突概率高，并发度低，开销低，不容易死锁；
  • 行锁：锁定某一行，依赖于存储引擎实现。如果表存在索引，那么记录锁是锁在索引上的，如果表没有索引，InnoDB 会创建一个隐藏的聚簇索引加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。锁的粒度最小，冲突概率低，并发度高，开销大，容易出现死锁；
  • 页锁：页级锁是 MySQL 中介于行级和表级之间的一种锁。它一次性地将相邻的一组记录进行加锁。

每个层级的锁的数量是有限制的。由于锁会占用内存，锁空间的大小也有限制。当某个层级的锁的数量超过这个层级的上限时，就会进行锁升级，即用大粒度的锁取代小粒度的锁，从而降低锁空间的内存占用，但是会降低并发度。不同粒度的锁之间不能共存。

表锁分类
  • 共享锁、排他锁 InnoDB 对某个表执行 DML 语句时（CRUD），不会自动添加表级的 S 锁和 X 锁；执行 ALTER TABLE 之类的 DDL 语句时，阻塞其他的 DML 语句；同理，执行 DML 语句也会阻塞 DDL 语句。而 MyISAM 在执行查询语句前，会给涉及的所有表加读锁，在执行增删改操作前，会给涉及的表加写锁。 手动给表加锁：lock tables t read/write （一般不用） 查看加锁的表：show open tables where in_use > 0 手动解锁所有表：unlock tables
  • 意向锁（intention lock） 意向锁是一种特殊的表锁，它可以和行锁共存。意向锁的作用是让粒度更高的锁知道其中是否上过粒度小的锁（因此意向锁相互之间都是兼容的）。如果没有意向锁，当一个事务想要给一张表加表锁时，需要遍历该表的所有行，查看其中是否有行锁。 当我们给某一行数据加上行锁时，会自动给更高粒度的空间（页、表）上一个意向锁。这样当其他事务需要给这个空间上更高粒度的锁时，就不用再遍历了。 意向锁也分为意向共享锁，意向排他锁，自动根据行锁的类型进行选择。
  • 自增锁（auto inc） 当表中有自增字段（auto increment）时，为了确保自增字段是连续自增的，就需要自增锁来实现。当执行插入时，就会自动添加一个表级的自增锁，执行完毕后再释放。由于每条插入语句都需要参与自增锁的竞争，并发度很低，所以可以通过 innodb_autoinc_lock_mode 变量来改变锁定机制。

MySQL 的插入分成三种：简单插入、批量插入、混合插入。

简单插入是指可以预先知道插入的行数的语句，例如没有嵌套子查询的 insert；

批量插入是指不能预先知道插入的行数的语句，例如嵌套子查询的 insert；

混合插入与简单插入类似，但是部分数据手动指定了自动递增字段的值。

  • innodb_autoinc_lock_mode = 0：传统的模式，每个插入语句都添加一个表级自增锁。；

  • innodb_autoinc_lock_mode = 1：MySQL 8.0 之前的默认值。在这种情况下，批量插入仍然使用自增锁，但是简单插入则使用 mutex （轻量级锁，只在分配过程中保持）来获取所需数量的自动低增值；

  • innodb_autoinc_lock_mode = 2：MySQL 8.0 后的默认值。在这种情况下，所有的插入语句都不会使用自增锁，但是执行批量插入时，生成的自增字段的值可能不连续（在回滚时，自增主键增长值不会滚回）；

  • 元数据锁（DML） 元数据锁的作用是保证读写的正确性不被表结构影响。 当对表做 CRUD 操作时，自动加元数据读锁；当对表结构做变更操作时，自动加元数据写锁。 读锁与读锁兼容，读锁与写锁、写锁与写锁不兼容。

行锁分类

  • 记录锁（record locks） 字面意思，给一条行记录加锁，也是最常用的锁。记录锁也分为读锁和写锁，规则与表级的相同；

  • 间隙锁（gap locks） 间隙锁用于解决幻读问题（也可以用 MVCC 解决）。 插入间隙锁后，不允许其他事务在两条记录之间插入新数据；
    image-20220724012351313

  • 临键锁（Next-key locks） 相当于是记录锁和间隙锁的结合体，是 InnoDB 的默认锁；
    临键锁

  • 插入意向锁（insert intention locks）。

    插入意向锁是一种间隙锁形式的意向锁，在真正执行 INSERT 操作之前设置。当执行插入操作时，总会检查当前插入操作的下一条记录（已存在的主索引节点）上是否存在锁对象，判断是否锁住了 gap，如果锁住了，则判定和插入意向锁冲突，当前插入操作就需要等待，也就是配合上面的间隙锁或者临键锁一起防止了幻读操作。

    因为插入意向锁是一种意向锁，意向锁只是表示一种意向，所以插入意向锁之间不会互相冲突，多个插入操作同时插入同一个 gap 时，无需互相等待，比如当前索引上有记录 4 和 8，两个并发 session 同时插入记录 6，7。他们会分别为(4,8)加上 GAP 锁，但相互之间并不冲突。

    INSERT 语句在执行插入之前，会先在 gap 中加入插入意向锁，如果是唯一索引，还会进行 Duplicate Key 判断，如果存在相同 Key 且该 Key 被加了互斥锁，则还会加共享锁，然后等待（因为这个相同的 Key 之后有可能会回滚删除，这里非常容易死锁）。等到成功插入后，会在这条记录上加排他记录锁。

3.2.3 从对待锁的态度划分

  • 悲观锁：总是假设最坏的情况，每次拿数据时都会加锁（每次操作前都进行枷锁）。例如行锁、表锁、读锁、写锁等（适合写多读少）；
  • 乐观锁：认为并发操作是小概率事件，不对操作加锁，而是在更新时判断在此期间数据有没有被改动。可以通过版本号或 CAS 机制实现。（JUC 的 atomic 就是通过 CAS 实现的）（适合读多写少）。

悲观锁和乐观锁是锁的设计思想，而不是具体的某个锁。

在表中设计一个 version 字段，对行数据的更新操作执行都执行以下步骤：

  1. 读取行数据和 version 的值；
  2. 在内存中对行数据进行操作；
  3. 再次读取 version 的值；
  4. 将 version 在步骤 3 中的值与步骤 1 中的值进行比较，如果相同则将行数据更新到磁盘，并且把磁盘中的 version 值 + 1；如果不同则从步骤 1 重新开始。

3.2.4 从加锁的方式划分

显式锁（Explicit Lock）是指用户在 SQL 语句中显式地加上的锁。例如，在 MySQL 中，可以使用 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 语句来显式地对查询结果集中的行加上排它锁或共享锁。

隐式锁（Implicit Lock）是指数据库系统在执行 SQL 语句时自动加上的锁。例如，在 MySQL 的 InnoDB 存储引擎中，当执行一个更新操作时，InnoDB 会自动对被更新的行加上排它锁；当执行一个查询操作时，InnoDB 会根据事务的隔离级别和查询类型来决定是否需要对查询结果集中的行加上共享锁。

  • 显式锁：指用户在 SQL 语句中显式地加上的锁；
  • 隐式锁：指数据库系统在执行 SQL 语句时自动加上的锁。

3.2.5 全局锁

对整个数据库进行加锁，让整个库处于只读状态。使用场景：全库逻辑备份。

以前有一种做法，是通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态。

让整库都只读：

  • 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
  • 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

官方自带的逻辑备份工具是 MySQLdump。当 MySQLdump 使用参数 –single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。由于 MVCC 的支持，这个过程中数据是可以正常更新的。single-transaction 方法只适用于所有的表使用事务引擎的库。

3.2.6 死锁

两个事务互相持有对方需要的锁，并且等待对方释放，双方都不会释放自己的锁。通过 show engine innodb status; 查看死锁的事务具体情况。

产生死锁的必要条件
  1. 两个或以上的事务；
  2. 每个事务都已经持有锁并且申请新的锁；
  3. 锁资源同时只能被同一个事务持有或者不兼容；
  4. 事务之间因为持有锁和申请锁导致彼此循环等待。

死锁的关键在于每个事务加锁的顺序不一致。如果一致，不会形成死锁。

解决死锁的方法
  1. 等待，直到超时。两个事务相互等待时，当一个事务等待事件超过阈值时，就将其回滚，从而释放锁，让另一个事务继续执行。通过 innodb_lock_wait_timeout 设置等待时间，默认 50s （缺点：等待时间不好设置，太长影响业务正常执行，太短容易误伤正常事务的的等待）；
  2. 使用死锁检测进行处理。使用 Wait-for graph 算法检测死锁。innodb_deadlock_detect 开启或关闭（缺点：算法本身需要耗费时间，如果同时并发的事务太多，会影响性能)（解决方法：用其他中间件对更新相同行的操作进行排队，比如可以使用消息队列获取更新操作并执行，但是这样做可能会导致系统性能下降，需要权衡利弊) 。
Wait-for graph

Wait-for graph 算法原理是把事务作为节点，事务之间的锁等待关系，用有向边表示。例如事务 A 等待事务 B 的锁，就从节点 A 画一条有向边到节点 B。这样如果 A、B、C、D 构成的有向图形成了环，则判断为死锁。

构建出以事务为点，锁为边的有向图，如果图中存在环，则存在死锁。innobb 引擎就会选择回滚 undo 量最小的事务，让其他事务继续执行。

通过事务等待链表和锁的信息链表获取锁占用情况

图出现环，出现死锁情况

避免死锁
  • 合理设计索引，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争；
  • 调整业务 SQL 执行顺序，避免 update/delete 等长时间持有锁的 SQL 在事务前面；
  • 避免大事务，尽量拆分成多个小事务处理；
  • 降低隔离级别；
  • 在并发高的场景下不要在事务中手动加锁。

4. 优化 MySQL

4.1 命名规范

  • 所有数据库对象名称必须使用小写字母并用下划线分割；
  • 所有数据库对象名称禁止使用 MySQL 保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）；
  • 数据库对象的命名要能做到见名识意，并且最好不要超过 32 个字符；
  • 临时库表必须以 tmp_ 为前缀并以日期为后缀，备份表必须以 bak_ 为前缀并以日期 (时间戳) 为后缀；
  • 所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）。

4.2 数据规范

4.2.1 尽量控制单表数据大小

建议将数据量控制在500万以内，过大会造成修改表结构，备份，恢复都会有很大的问题。

可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小。

4.2.2 禁止存储文件/图片

通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机 IO 操作，文件很大时，IO 操作很耗时。

通常存储于文件服务器，数据库只存储文件地址信息。

4.2.3 避免使用 TEXT/BLOB

MySQL 内存临时表不支持 TEXT、BLOB 这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。对于这种数据，MySQL 还是要进行二次查询，会使 SQL 性能变得很差。

建议把 BLOB 或是 TEXT 列分离到单独的扩展表中，查询时一定不要使用select *而只需要取出必要的列，不需要 TEXT 列的数据时不要对该列进行查询。

MySQL 对索引字段长度是有限制的，所以 TEXT 类型只能使用前缀索引，并且 TEXT 列上是不能有默认值的。

4.2.4 TIMESTAMP & DATETIME

TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07，TIMESTAMP 占用 4 字节和 INT 相同，但比 INT 可读性高。超出 TIMESTAMP 取值范围的使用 DATETIME 类型存储。

经常会有人用字符串存储日期型的数据（不正确的做法）

  • 缺点 1：无法用日期函数进行计算和比较；
  • 缺点 2：用字符串存储日期要占用更多的空间。

4.3 慢查询

MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过long_query_time 值的 SQL，则会被记录到慢查询日志中。long_query_time 的默认值为10，意思是运行10s以上的语句。

默认情况，MySQL 数据库并不启动慢查询日志，需要手动设置这个参数。如果不是调优需要的话（建议在测试环境中开启），一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

4.3.1 慢查询日志

MySQL 的慢查询日志，用来记录在 MySQL 中响应时间超过阈值（即 long_query_time ，默认为 10s）的语句。

除了 long_query_time 外，还有一个系统变量 min_examined_row_limit，表示查询扫描过的最小记录数。

当一条查询扫描的记录数大于 min_examined_row_limit 同时查询的执行事件超过 long_query_time ，那么这个查询就会被记录到慢查询日志中。

这个值默认是 0 ，可以在 my.ini/my.cnf 中修改，也可用 SET 命令修改。

# 查看慢查询是否开启
SHOW VARIABLES LIKE 'slow_query_log';

# 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

# 开启慢查询
SET GLOBAL slow_query_log='ON';

# 查看慢查询阈值
show variables like '%long_query_time%';

# 查看慢查询数量
SHOW GLOBAL STATUS LIKE '%Slow_queries%';

# 重置慢查询日志
MySQLadmin -uroot -p flush-logs slow

4.3.2 优化慢查询

  1. 通过慢日志定位执行较慢的 SQL 语句；
  2. 利用 explain 对这些关键字段进行分析；
  3. 根据分析结果进行优化。

对于慢查询，可以通过几种策略进行优化：

优化数据库结构
  1. 将字段很多的表分解成多个表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢；
  2. 对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。
分解关联查询

很多高性能的应用都会对关联查询进行分解，可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效。

SELECT * FROM tag 
        JOIN tag_post ON tag_id = tag.id
        JOIN post ON tag_post.post_id = post.id
        WHERE tag.tag = 'MySQL';
# 分解为：
SELECT * FROM tag WHERE tag = 'MySQL';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567);
优化 limit 分页

对于分页查询，在数据量大的时候，会导致前面的大量数据被浪费。

SELECT
	id,
	title 
FROM
	collect 
	LIMIT 90000,
	10;
# 在查找需要数据的同时，需要查找90010的数据，前面的数据将会被浪费

方法一：先查找出主键 id 值

用小结果集驱动大结果集。

SELECT id, title FROM collect WHERE id >=( SELECT id FROM collect ORDER BY id LIMIT 90000, 1 ) 
LIMIT 10;

方法二：“关延迟联”

“关延迟联”让 MySQL 扫描尽可能少的页面，获取需要的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用在优化关联查询中的 limit。

SELECT
	news.id,
	news.description 
FROM
	news
	INNER JOIN ( SELECT id FROM news ORDER BY title LIMIT 50000, 5 ) AS myNew USING ( id );

方法三：建立复合索引 acct_id 和 create_time

select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0,10

4.4 性能分析工具

4.4.1 explain

使用 EXPLAIN 对特定 SQL 语句进行分析，结果会出现供我们分析的信息列。

# 数据库结构和数据
-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '对于这本书的描述/简介',
  `number` int(0) NULL DEFAULT NULL COMMENT '这本书剩余的数量',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `book`(`id`, `name`, `description`, `number`) VALUES (1, '红楼梦', '神瑛侍者和一株❀的故事', 48);
INSERT INTO `book`(`id`, `name`, `description`, `number`) VALUES (2, '西游记', '四个人向西天进发', 10);
INSERT INTO `book`(`id`, `name`, `description`, `number`) VALUES (3, '鲁迅全集', '还没有补充', 0);

# EXPLAIN 执行语句
EXPLAIN SELECT * FROM book WHERE `name` = '红楼梦';

explain 执行结果

type 字段

type 表示访问字段数据的方式，从性能好到差的排序为：

  • system，表中只有一行数据（系统表），这是 const 类型的特殊情况；
  • const，最多返回一条匹配的数据，在查询的最开始读取；
  • eq_ref，对于前面的每一行，从该表中读取一行数据；
  • ref，对于前面的每一行，从该表中读取匹配索引值的所有数据行；
  • fulltext，通过 FULLTEXT 索引查找数据；
  • ref_or_null，与 ref 类似，额外加上 NULL 值查找；
  • index_merge，使用索引合并优化技术，此时 key 列显示使用的所有索引；
  • unique_subquery，替代以下情况时的 eq_ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)；
  • index_subquery，与 unique_subquery 类似，用于子查询中的非唯一索引：value IN (SELECT key_column FROM
  • single_table WHERE some_expr)；
  • range，使用索引查找范围值；
  • index，与 ALL 类型相同，只不过扫描的是索引；
  • ALL，全表扫描，通常表示存在性能问题。
Extra 字段

extra 通常包含额外信息，可以帮助我们处理性能问题：

  • Using where，表示将经过 WHERE 条件过滤后的数据传递给下个数据表或者返回客户端。如果访问类型为 ALL 或者 index，而 Extra 字段不是 Using where，意味着查询语句可能存在问题（除非就是想要获取全部数据）；
  • Using index condition，表示通过索引访问表之前，基于查询条件中的索引字段进行一次过滤，只返回必要的索引项。这也就是索引条件下推优化；
  • Using index，表示直接通过索引即可返回所需的字段信息（index-only scan），不需要访问表。对于 InnoDB，如果通过主键获取数据，不会显示 Using index，但是仍然是 index-only scan。此时，访问类型为 index，key 字段显示为 PRIMARY；
  • Using filesort，意味着需要执行额外的排序操作，通常需要占用大量的内存或者磁盘；
  • Using temporary，意味着需要创建临时表保存中间结果。
其他字段
列名 作用
id 语句中 SELECT 的序号
如果是 UNION 操作的结果，显示为 NULL；此时 table 列显示为
select_type SELECT 的类型，包括：
- SIMPLE，不涉及 UNION 或者子查询的简单查询；
- PRIMARY，最外层 SELECT；
- UNION，UNION 中第二个或之后的 SELECT；
- DEPENDENT UNION，UNION 中第二个或之后的 SELECT，该 SELECT 依赖于外部查询；
- UNION RESULT，UNION 操作的结果；
- SUBQUERY，子查询中的第一个 SELECT；
- DEPENDENT SUBQUERY，子查询中的第一个 SELECT，该 SELECT 依赖于外部查询；
- DERIVED，派生表，即 FROM 中的子查询；
- DEPENDENT DERIVED，依赖于其他表的派生表；
- MATERIALIZED，物化子查询；
- UNCACHEABLE SUBQUERY，无法缓存结果的子查询，对于外部表中的每一行都需要重新查询；
- UNION 中第二个或之后的 SELECT，该 UNION属于 UNCACHEABLE SUBQUERY。
partitions 对于分区表而言，表示数据行所在的分区；普通表显示为 NULL
possible_keys 可能用到的索引，实际上不一定使用
key 实际使用的索引
key_len 实际使用的索引的长度
ref 用于和 key 中的索引进行比较的字段或者常量，从而判断是否返回数据行
rows 执行查询需要检查的行数，对于 InnoDB 是一个估计值。
filtered 根据查询条件过滤之后行数百分比，rows × filtered 表示进入下一步处理的行数。
explain 输出格式

EXPLAIN FORMAT=<xxx> ...

  • 传统格式；
  • JSON 格式（最详细）；
  • TREE 格式（8.0.16+）；
  • 可视化输出（MySQL workbench）。

Tree 格式

4.4.2 查看系统性能参数

SHOW STATUS LIKE '参数'，常用的性能参数：

Connections：连接 MySQL 服务器的次数
Uptime：MySQL 服务器上线事件
Slow_queries：慢查询次数
Innodb_rows_read：已 select 的行数
Innodb_rows_inserted：已 inserted 的行数 Innodb_rows_updated：已 updated 的行数 Innodb_rows_deleted：已 deleted 的行数
Com_select：查询操作的次数
Com_insert：插入操作的次数
Com_update：更新操作的次数
Com_delete：删除操作的次数

4.4.3 查看上一条 SQL 语句的查询成本

SHOW STATUS LIKE 'last_query_cost';

SQL 查询是一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论：

  1. 位置决定效率。如果页在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多；
  2. 批量决定效率。如果从磁盘中对单一页进行随机读取，那么效率是很低的（差不多 10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

所以说，遇到 I/O 并不用担心，方法找对了，效率还是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

4.4.4 trace

# 开启
SET optimizer_trace = "enabled=on", end_markers_in_json = ON;

# 设置大小
SET optimizer_trace_max_mem_size = 1000000;

SELECT * FROM account a JOIN result b ON a.`uid` = b.`uid` LIMIT 10;

SELECT * FROM information_schema.OPTIMIZER_TRACE;

4.4.5 监控分析视图 sys schema

索引情况：

# 1. 查询冗余索引 
select * from sys.schema_redundant_indexes; 
# 2. 查询未使用过的索引 
select * from sys.schema_unused_indexes; 
# 3. 查询索引的使用情况 
select index_name, rows_selected, rows_inserted, rows_updated, rows_deleted from sys.schema_index_statistics where table_schema='dbname';

表相关：

# 1. 查询表的访问量 
select table_schema, table_name, sum(io_read_requests + io_write_requests) as io from sys.schema_table_statistics group by table_schema, table_name order by io desc; 
# 2. 查询占用 bufferpool 较多的表 
select object_schema, object_name, allocated, data from sys.innodb_buffer_stats_by_table order by allocated limit 10; 
# 3. 查看表的全表扫描情况 
select * from sys.statements_with_full_table_scans where db='dbname';

语句相关：

# 1. 监控 SQL 执行的频率 
select db, exec_count, query from sys.statement_analysis order by exec_count desc; 
# 2. 监控使用了排序的 SQL 
select db, exec_count, first_seen, last_seen, query from sys.statements_with_sorting limit 1; 
# 3. 监控使用了临时表或者磁盘临时表的 SQL 
select db, exec_count, tmp_tables, tmp_disk_tables, query from sys.statement_analysis where tmp_tables > 0 or tmp_disk_tables > 0 order by (tmp_tables + tmp_disk_tables) desc;

I/O 相关：

# 1. 查看消耗磁盘 I/O 的文件 
select file, avg_read, avg_write, avg_read + avg_write as avg_io from sys.io_global_by_file_by_bytes order by avg_read limit 10;

InnoDB 相关：

# 1. 行锁阻塞情况 
select * from sys.innodb_lock_waits;

4.5 面试

MySQL 性能优化分为四大部分：

  • 硬件和操作系统层面的优化；
  • 架构设计层面的优化；
  • MySQL 程序配置优化；
  • SQL 优化。

4.5.1 硬件和操作系统层面的优化

从硬件层面来说，影响 MySQL 性能的因素有，CPU、可用内存大小、磁盘读写速度、网络带宽。

从操作系层面来说，应用文件句柄数、操作系统网络的配置都会影响到 MySQL 性能。这部分的优化一般由 DBA 或者运维工程师去完成。
在硬件基础资源的优化中，我们重点应该关注服务本身承载的体量，然后提出合理的指标要求，避免出现资源浪费。

4.5.2 架构设计层面的优化

  1. 搭建 MySQL 主从集群，单个 MySQL 服务容易单点故障，一旦服务器宕机，将会导致依赖 MySQL 数据库的应用全部无法响应。主从集群或者主主集群可以保证服务的高可用性；
  2. 读写分离设计，在读多写少的场景中，通过读写分离的方案，可以避免读写冲突导致的性能影响；
  3. 引入分库分表机制，通过分库可以降低单个服务器节点的 IO 压力，通过分表的方式可以降低单表数据量，从而提升 SQL 查询的效率；
  4. 针对热点数据，可以引入更为高效的分布式数据库，比如 Redis、MongoDB 等，他们可以很好的缓解 MySQL 的访问压力，同时还能提升数据检索性能。

4.5.3 MySQL 程序配置优化

对于 MySQL 数据库本身的优化，一般是通过 MySQL 中的配置文件 my.cnf 来完成的。

配置的作用域，分为会话级别和全局。全局参数的设定对于已经存在的会话无法生效。会话参数的设定随着会话的销毁而失效。全局类的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效。

4.5.4 SQL 优化

  1. 通过慢查询日志和慢查询日志分析工具得到有问题的SQL 列表；
  2. 针对慢 SQL，我们可以使用关键字 explain 来查看当前 SQL 的执行计划。可以重点关注type key rows filterd 等字段，从而定位该 SQL 执行慢的根本原因；
  3. 使用 show profile 工具。Show Profile 是 MySQL 提供的可以用来分析当前会话中，SQL 语句资源消耗情况的工具，可用于 SQL 调优的测量。在当前会话中，默认情况下处于 show profile 是关闭状态，打开之后保存最近 15 次的运行结果针对运行慢的 SQL，通过 profile 工具进行详细分析。可以得到 SQL 执行过程中所有的资源开销情况。如 IO 开销、CPU 开销、内存开销等。

5. 索引

索引是存储引擎中用于快速找到记录的一种数据结构。在关系型数据库中，索引具体是一种对数据库中一列或多列的值进行排序的存储结构。

5.1 索引类型

5.1.1 数据结构（逻辑）角度

MySQL 有 B-Tree索引、哈希索引、R-Tree索引、全文索引。

算法复杂度分析

Hash 索引

Hash 索引基于哈希表实现，只有查询条件精确匹配 Hash 索引中的列时，才能够使用到 Hash 索引。对于 Hash 索引中的所有列，存储引擎会为每行计算一个 hashcode，Hash 索引中存储的就是 hashcode。Hash 索引适用于等值查询，通过 Hash 索引比 B-Tree 索引查询更加迅速（算法复杂度为 O(1)）。但是 Hash 索引不支持范围查找例如 <、>、<=、>= 等。

对于底层的选择，对于会出现重复数据，不要选择 Hash 作为底层，否则碰撞会让效率大大降低。并且 Hash 索引不能进行范围查询，B+ Tree 可以，因为 Hash 的索引是无序的，B+ Tree 树是有序的；Hash 也不支持模糊查询和联合索引的最左侧原则。

B-Tree & B+Tree

InnoDB 使用 B+ Tree 作为索引的底层。B-Tree 和 B+Tree 都是平衡多路查找树，它们的区别在于节点的组织方式不同。

在 B-Tree 中，每个节点既包含键值信息，也包含数据记录。

而在 B+Tree 中，所有数据记录节点都按照键值大小顺序存放在同一层的叶子节点上，非叶子节点上只存储键值信息。这样可以大大增加每个节点存储的键值数量，降低 B+Tree 的高度。所有叶子节点增加了一个链指针，方便进行范围查询。

为什么数据库不用红黑树用 B+ 树？

红黑树（二叉树）的出度为 2，而 B Tree 的出度一般都非常大。红黑树的树高 h 很明显比 B Tree 大非常多，IO次数很多，导致会比较慢，因此检索的次数也就更多。

自适应 Hash 索引

InnoDB 对于频繁使用的某些索引值，会在内存中基于 B-Tree 索引之上再创键一个哈希索引，这也被称为自适应 Hash 索引。

原理过程

5.1.2 物理存储角度

主键（聚簇）索引

Primary Key，也就是熟知的主键（主键具备索引功能，创建或设置主键的时候，MySQL 会自动添加一个与主键对应的唯一索引，不需要再做额外的添加）。

InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键 row_id。

主键索引示例

二级索引

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

  1. 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率；
  2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL；
  3. 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符；
  4. 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。MySQL 5.6 之前只有 MyISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

二级索引图示

5.2 索引分类

5.2.1 聚集索引

MyISAM 是非聚集索引，Innodb 是聚集索引。非聚集索引的数据文件是分离的，索引保存的是数据文件的指针；聚集索引的数据文件和索引文件是合并的，数据存储在主键索引的叶子节点上。

聚集索引即索引结构和数据一起存放的索引。

在 MySQL 中，InnoDB 引擎的表的 .ibd 文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引（B+ Tree）的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。聚集索引按每张表的主键构建一棵 B+ 树，数据库中的每个搜索键值都有一个索引记录，每个数据页通过双向链表连接。表数据访问更快，但表更新代价高。

优点：

  1. 数据访问更快，聚集索引将索引和数据保存在同一个 B+ 树中，比非聚集索引更快；
  2. 对于主键排序查找和范围查找速度非常快（因为自身有排序）；
  3. 因为数据和索引一起，节省了大量的 IO 操作。

缺点：

  1. 插入速度严重依赖插入顺序，按照主键顺序插入是最快的方式，否则会出现页分裂，严重影响性能。因为，一般 InnoDB 表定义一个自增 ID 列为主键；
  2. 更新主键代价很高，一般的主键不可更新，否则会导致更新的行被移动。

5.2.2 非聚集索引

非聚集索引制定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致（在逻辑上数据是按顺序排存放的，但是物理上在真实的存储器中是散列存放的）。更新代价比聚集索引要小 ，非聚集索引的叶子节点不存放数据。

优点：

两种索引都采用 B+ Tree 结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。所以如果表的读操作远远多于写操作，那么就可以使用非聚集索引。

特点：

  • 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个；
  • 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。

缺点：

  • 跟聚集索引一样，非聚集索引也依赖于有序的数据；
  • 可能会二次查询(回表) ：当查到索引对应的指针或主键后，可能需要根据指针或主键再到数据文件或表中查询。

5.2.3 覆盖索引

在非聚集索引中，可能会有二次查询的情况，但是不一定，会出现覆盖索引的情况。覆盖索引指一个索引包含或覆盖了所有需要查询的字段的值，不需要回表查询，即索引本身存了对应的值。

SELECT id FROM table WHERE id=1;

主键索引本身的 key 就是主键，查到返回就行了。这种情况就称之为覆盖索引了。覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求。

5.2.4 联合索引

两个或更多个列上的索引被称作复合（联合）索引。

一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是 key index (a,b,c)。可以支持 a | a,b| a,b,c 3种组合进行查找，但不支持 b,c 进行查找。当最左侧字段是常量引用时，索引就十分有效。

联合索引的创建，能在某些情况下让单索引效率得到提升。索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这正是业务 DBA，或者称为业务数据架构师的工作。

5.3 索引失效

如果出现以下几种情况，索引会有失效的情况：

  1. 类型不一致，隐式转换导致索引失效。如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引；
  2. 不等于（!= || <>）索引失效，索引列上参与计算会导致索引失效；
  3. is null 可以使用索引，is not null 无法使用索引；
  4. like 以通配符 % 开头索引失效，（页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决《阿里巴巴规范手册》）；
  5. OR 前后存在非索引的列，索引失效（需要全部为索引列）；
  6. 不同字段对应的字符集不一致；
  7. 如果 MySQL 估计全表扫描要比使用索引要快，会不适用索引

5.4 索引规范

索引可以提高效率同样可以降低效率。索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

对于索引的数量不建议超过五个。

对于索引的建议列：

  • 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列；
  • 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段；
  • 并不要将符合 1 和 2 中的字段的列都建立一个索引， 通常将 1、2 中的字段建立联合索引效果更好；
  • 使用列类型小的创建索引；
  • 多表 join 的关联列（连接表的数量尽量不要超过3张，因为每增加一次表就相当于多了一层循环）。

避免使用双 % 号的查询条件。如：a like '%123%'，（如果无前置 %,只有后置 %，是可以用到列上的索引的）。

5.5 最左前缀原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

最左匹配原则是指在使用复合索引时，查询条件必须包含复合索引的最左边的列，才能使索引生效。例如，如果有一个复合索引是 (a,b,c)，那么查询条件中必须包含 a 列，才能使索引生效。如果查询条件中只包含 b 或 c 列，则不会使用该复合索引。

用（name，age）这个联合索引来分析

如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是”where name like ‘张 %’”。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

基于最左前缀原则，需要考虑以下方面：

  • 当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的；
  • 如果因为业务问题，需要同时维护 (a,b)、(b) 这两个索引。考虑的原则就是空间，在小字段建立索引。

6. MySQL 架构

MySQL 的架构可以从不同的角度进行划分。

6.1 软件本身划分

MySQL 主要分为 Server 层和存储引擎层（这种划分方式侧重于描述 MySQL 软件本身的架构）：

  • Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块；
  • 存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，MySQL 5.5.5 版本开始作为默认存储引擎。

6.1.1 连接层

连接层负责将 MySQL 服务层通过线程池和 TCP 连接池与多个客户端连接。主要工作是：连接处理、授权认证、安全防护。在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

长连接

数据库里，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

但是全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

考虑以下两种处理方式：

  1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连；
  2. 如果使用 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 MySQL_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

6.1.2 Server 层

服务层用于处理核心服务，如标准的 SQL 接口、查询解析、SQL 优化和统计、全局的和引擎依赖的缓存与缓冲器等等。所有的与存储引擎无关的工作，如过程、函数等，都会在这一层来处理。在该层上，服务器会解析查询并创建相应的内部解析树，并对其完成优化，如确定查询表的顺序，是否利用索引等，最后生成相关的执行操作。如果是 SELECT 语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

流程示例图

连接器

连接器负责与客户端建立连接、获取权限、维持和管理连接。当客户端使用用户名和密码请求建立与 MySQL 服务器的连接时，连接器负责验证客户端的身份是否合法。如果认证通过，连接器会查询权限表获取该用户的权限，之后这个连接的权限都据此来判断。

MySQL 中每个客户端连接都对应一个线程，由该线程负责该连接的监听和数据处理。当客户端断开连接时，线程会销毁。为了提高性能，MySQL 引入了线程池技术来复用线程。

查询缓存

查询缓存负责缓存查询结果。当客户端发出查询请求时，MySQL 会先检查查询缓存，如果能够在其中找到对应的查询结果，则直接返回给客户端，而不需要再进行 SQL 解析、优化和执行等操作。

在 MySQL 8.0 及更高版本中，查询缓存功能已被删除。这是因为在许多情况下，查询缓存并不能提高性能，反而会成为性能瓶颈。例如，在高并发的场景下，对于更新频繁的表来说，维护查询缓存所需的开销可能会超过其带来的好处。

分析器

分析器负责对 SQL 语句进行词法分析和语法分析。当客户端发出查询请求时，MySQL 会先检查查询缓存，如果未能在其中找到对应的查询结果，则需要进行 SQL 解析、优化和执行等操作。在 SQL 解析阶段，分析器会先对 SQL 语句进行词法分析，将其拆分成一个个的词法单元，并识别它们的类型。例如，关键字 SELECT 会被识别为一个关键字类型的词法单元。

接下来，分析器会根据 MySQL 的语法规则对这些词法单元进行语法分析，判断它们是否符合语法规则。如果不符合，则会返回错误信息。

优化器

优化器负责生成执行计划、选择索引。在 SQL 解析阶段完成后，MySQL 会根据语法分析的结果生成一棵语法树。接下来，优化器会对这棵语法树进行优化，生成最终的执行计划。

在这个过程中，优化器会考虑多种因素，例如表中数据的分布情况、索引的选择等。它会尝试多种不同的执行计划，并通过代价估算模型来评估它们的代价，最终选择代价最小的执行计划。

优化器决定使用哪些索引进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。它使用 “选取 - 投影 - 连接” 策略进行查询。例如：

SELECT id,name FROM student WHERE gender = '女';
  • 先根据 WHERE 语句进行选取，而不是将表全部查询出来以后再进行 gender 过滤；
  • 根据 id 和 name 进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件连接起来生成最终查询结果。
执行器

执行器负责执行操作，并将结果返回给客户端。在优化器生成最终的执行计划后，MySQL 会根据执行计划中的指令来执行操作。在这个过程中，执行器会调用存储引擎的 API 来对数据进行读写。例如，在执行 SELECT 语句时，它会调用存储引擎的 API 来读取数据；在执行 INSERT 语句时，它会调用存储引擎的 API 来写入数据。

当操作完成后，执行器会将结果返回给客户端。

总结来说，Server 层每个结构的作用如下：

  • 连接器： 身份认证和权限相关（登录 MySQL）；
  • 查询缓存： 执行查询语句的时候，会先查询缓存（从 MySQL 5.7.20 开始，不推荐使用查询缓存，MySQL 8.0 版本后移除，因为这个功能不太实用）；
  • 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，简单来说，分析器是要查看 SQL 语句的目的，再检查 SQL 语句语法是否正确；
  • 优化器： 按照 MySQL 认为最优的方案去执行；
  • 执行器： 优化器优化之后，执行对应 MySQL 语句。

于是执行 SQL 语言的顺序就是连接器进行身份认证->查询缓存（MySQL 8.x 版本去除）->没有命中缓存到分析器、优化器、执行器。

6.1.3 引擎层

存储引擎层，存储引擎负责实际的 MySQL 数据的存储与提取，服务器通过 API 与存储引擎进行通信。不同的存储引擎功能和特性有所不同，这样可以根据实际需要有针对性的使用不同的存储引擎。

InnoDB 存储引擎提供了事务支持、行级锁定和外键约束等功能，适用于需要高并发写入和事务支持的场景；MyISAM 存储引擎则提供了全文索引等功能，适用于需要快速全文检索的场景。

MySQL 有多种常用的存储引擎，包括：

  • InnoDB：支持事务、行级锁定和外键约束等功能，适用于需要高并发写入和事务支持的场景。
  • MyISAM：提供了全文索引等功能，适用于需要快速全文检索的场景。
  • Memory：将数据存储在内存中，提供快速的读写性能，适用于临时表和缓存数据等场景。
  • Archive：提供高效的数据压缩功能，适用于大量只读数据的归档场景。

6.2 MySQL 处理划分

MySQL 可以分为应用层，逻辑层，数据库引擎层，物理层。

  • 应用层：负责和客户端，响应客户端请求，建立连接，返回数据（提供图形界面等交互）；
  • 逻辑层：包括 SQK 接口，解析器，优化器，Cache 与 buffer。
  • 数据库引擎层：有常见的 MyISAM,InnoDB 等等。
  • 物理层：负责文件存储，日志等等。
+---------------------+
|     Application     |
+---------------------+
|      Connector      |
+---------------------+
|       Server        |
+---------------------+
| InnoDB Storage Engine
+---------------------+
| Buffer Pool | Change Buffer | Adaptive Hash Index | Data Dictionary | File Space Management | Undo Log | Redo Log |
+-------------------------------------------------------------------------------------------------------------------+
| Data Files | Log Files | Doublewrite Buffer |
+---------------------------------------------+

7. 主从复制

同 Redis 一样，如果单节点 MySQL 遇到处理数据困难的问题，可以使用主从复制。MySQL 提供的主从复制模式有多种：

  • 一主多从：当 Slave 增加到一定数量时，Slave 对 Master 的负载以及网络带宽都会成为一个严重的问题；
  • 多主一从：将多个库的数据备份到一个库中存储；
  • 双主复制：两个 MySQL 服务器互做对方的从，任何一方有变更，都会复制对方的数据到自己的数据库。双主适用于写压力比较大的业务场景，或者 DBA 做维护需要主从切换的场景，通过双主架构避免了重复搭建从库的麻烦；
  • 解决了一主多从场景下多个从库复制对主库的压力，带来的弊端就是数据同步延迟比较大。
    级联复制示图

7.1 流程

主从复制主要涉及三个线程: binlog 线程、I/O 线程和 SQL 线程。

  • binlog 线程 : 负责将主服务器上的数据更改写入二进制日志中。主库会生成一个 log dump 线程,用来给从库 I/O 线程传 Binlog 数据；
  • I/O 线程 : 负责从主服务器上读取二进制日志，并写入从服务器的中继日志（relay log）中；
  • SQL 线程 : 负责读取中继日志并重放其中的 SQL 语句，读取 relay log 文件中的日志，并解析成 SQL 语句逐一执行。

主从复制示例

7.1.1 主节点 log dump 线程

当从节点连接主节点时，主节点会为其创建一个 log dump 线程，用于发送和读取 Binlog 的内容。在读取 Binlog 中的操作时，log dump 线程会对主节点上的 Binlog 加锁；当读取完成发送给从节点之前，锁会被释放。主节点会为自己的每一个从节点创建一个 log dump 线程。

7.1.2 从节点I/O线程

当从节点上执行 start slave 命令之后，从节点会创建一个 I/O 线程用来连接主节点，请求主库中更新的 Binlog。I/O 线程接收到主节点的 log dump 进程发来的更新之后，保存在本地 relay-log（中继日志）中。

7.1.3 relay log

从服务器 I/O 线程将主服务器的 Binlog 日志读取过来，解析到各类 Events 之后记录到从服务器本地文件，这个文件就被称为 relay log。然后 SQL 线程会读取 relay log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。中继日志充当缓冲区，这样 master 就不必等待 slave 执行完成才发送下一个事件。

MySQL>  show variables like '%relay%';
+---------------------------+------------------------------------------------------------+
| Variable_name             | Value                                                      |
+---------------------------+------------------------------------------------------------+
| max_relay_log_size        | 0                                                          |
| relay_log                 | yangyuedeMacBook-Pro-relay-bin                             |
| relay_log_basename        | /usr/local/MySQL/data/yangyuedeMacBook-Pro-relay-bin       |
| relay_log_index           | /usr/local/MySQL/data/yangyuedeMacBook-Pro-relay-bin.index |
| relay_log_info_file       | relay-log.info                                             |
| relay_log_info_repository | TABLE                                                      |
| relay_log_purge           | ON                                                         |
| relay_log_recovery        | OFF                                                        |
| relay_log_space_limit     | 0                                                          |
| sync_relay_log            | 10000                                                      |
| sync_relay_log_info       | 10000                                                      |
+---------------------------+------------------------------------------------------------+
11 rows in set (0.03 sec)

max_relay_log_size：

标记 relay log 允许的最大值，如果该值为 0，则默认值为 max_binlog_size(1G)；如果不为 0，则max_relay_log_size 则为最大的 relay_log 文件大小。

relay_log_purge：

是否自动清空不再需要中继日志时。默认值为1(启用)。

relay_log_recovery：

当 slave 从库宕机后，假如 relay log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay log，并且重新从 master 上获取日志，这样就保证了 relay log 的完整性。默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为 1 时，可在 slave 从库上开启该功能，建议开启。

relay_log_space_limit：

防止中继日志写满磁盘，这里设置中继日志最大限额。但此设置存在主库崩溃，从库中继日志不全的情况，不到万不得已，不推荐使用。

sync_relay_log：

和 Binlog 中的 sync_binlog作用相同。当设置为 1 时，slave 的 I/O 线程每次接收到 master 发送过来的 Binlog 日志都要写入系统缓冲区，然后刷入 relay log 中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量 I/O。

当设置为 0 时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘 I/O 操作。这个值默认是 0，可动态修改，建议采用默认值。

sync_relay_log_info：

当设置为 1 时，slave 的 I/O 线程每次接收到 master 发送过来的 Binlog 日志都要写入系统缓冲区，然后刷入 relay-log.info 里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量 I/O。当设置为 0 时，并不是马上就刷入 relay-log.info 里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘 I/O 操作。这个值默认是0，可动态修改，建议采用默认值。

7.1.4 从节点 SQL 线程

SQL 线程负责读取 relay log 中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

对于每一个主从连接，都需要这三个进程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个 log dump 进程，而每个从节点都有自己的 I/O 进程，SQL 进程。

从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时 I/O 进程可以很快从主节点获取更新，尽管 SQL 进程还没有执行。如果在 SQL 进程执行之前从节点服务停止，至少 I/O 进程已经从主节点拉取到了最新的变更并且保存在本地 relay log 中，当服务再次起来之后就可以完成数据的同步。

复制过程

  1. 在从节点上执行 sart slave 命令开启主从复制开关，开始进行主从复制。从节点上的 I/O 进程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；
  2. 主节点接收到来自从节点的 I/O 请求后，通过负责复制的 I/O 进程（log Dump Thread）根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的 Binlog file 以及 Binlog position（Binlog 下一个数据读取位置）；
  3. 从节点的 I/O 进程接收到主节点发送过来的日志内容、日志文件及位置点后，将接收到的日志内容更新到本机的 relay log 文件（MySQL-relay-bin.xxx）的最末端，并将读取到的 Binlog 文件名和位置保存到 master-info 文件中，以便在下一次读取的时候能够清楚的告诉 Master ：“ 我需要从哪个 Binlog 的哪个位置开始往后的日志内容，请发给我”；
  4. Slave 的 SQL 线程检测到 relay log 中新增加了内容后，会将 relay log 的内容解析成在能够执行 SQL 语句，然后在本数据库中按照解析出来的顺序执行，并在 relay log.info 中记录当前应用中继日志的文件名和位置点。

7.2 主从复制模式

MySQL 主从复制默认是 异步的模式。MySQL 增删改操作会全部记录在 Binlog 中，当 slave 节点连接 master 时，会主动从 master 处获取最新的 Binlog 文件。并把 Binlog 存储到本地的 relay log 中，然后去执行 relay log 的更新内容。

7.2.1 异步模式

主节点不会主动推送数据到从节点，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理。

这样就会有一个问题，主节点如果崩溃掉了，此时主节点上已经提交的事务可能并没有传到从节点上，如果此时，强行将从提升为主，可能导致新主节点上的数据不完整。

异步模式

7.2.2 半同步模式

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到 relay log 中才返回成功信息给客户端（只能保证主库的 Binlog 至少传输到了一个从节点上），否则需要等待直到超时时间然后切换成异步模式再提交。

相对于异步复制，半同步复制提高了数据的安全性，一定程度的保证了数据能成功备份到从库，同时它也造成了一定程度的延迟，但是比全同步模式延迟要低，这个延迟最少是一个 TCP/IP 往返的时间。所以，半同步复制最好在低延时的网络中使用。

半同步模式不是 MySQL 内置的，从 MySQL 5.5 开始集成，需要 master 和 slave 安装插件开启半同步模式。

半同步模式

7.2.3 全同步模式

指当主库执行完一个事务，然后所有的从库都复制了该事务并成功执行完才返回成功信息给客户端。因为需要等待所有从库执行完该事务才能返回成功信息，所以全同步复制的性能必然会收到严重的影响。

7.3 Slave 同步延迟

Slave 端通过 I/O thread 单线程来实现数据解析入库；而 Master 端写 Binlog 由于是顺序写效率很高，当主库的 TPS（事务数/秒）很高的时候，必然 Master 端的写效率要高过 Slave 端的读效率，这时候就有同步延迟的问题。I/O Thread 的同步是基于库的，即同步几个库就会开启几个 I/O Thread。

可以通过 show slave status 命令查看 Seconds_Behind_Master 的值来看是否出现同步延迟，这个值代表主从同步延迟的时间，值越大说明延迟越严重。值为 0 为正常情况，正值表示已经出现延迟，数字越大从库落后主库越多。

在 MySQL 5.7 版本引入了基于组提交的并行复制（官方称为Enhanced Multi-threaded Slaves，即MTS），设置参数：slave_parallel_workers>0 即可，并且 global.slave_parallel_type＝‘LOGICAL_CLOCK’，即可支持一个 schema(库) 下，slave_parallel_workers 个 worker 线程并发执行 relay log 中主库提交的事务。

8. 分表分库

分表分库的具体问题无非在于两点：

  • 用户请求量过大：单个服务器 TPS 有限，一般会把请求分配到多个服务器上；
  • 数据库/表过大：数据库过大，单数据库处理能力有限，IO 操作瓶颈，磁盘空间不够；表数据过多，索引膨胀，简单业务出现延迟过大的问题。这个时候需要考虑分表分库。

8.1 垂直拆分

在出现表多而导致数据多的情况后，需要考虑垂直拆分。

8.1.1 垂直分表

一般是表中的字段较多，将不常用的，数据较大，长度较长（比如 text、blob 类型字段）的拆分到“扩展表”。 一般针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。

8.1.2 垂直分库

将一个系统中不同业务进行拆分，置于多个服务器上。

数据库业务层面的拆分，和服务的“治理”，“降级”机制类似，也能对不同业务的数据分别的进行管理，维护，监控，扩展等。一定程度能突破单机资源的瓶颈。

8.2 水平拆分

针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE，HASH 取模等），切分到多张表里。

8.2.1 划分规则

  • range：从 0 到 10000 一个表，10001 到 20000 一个表；
  • hash 取模：通过主键 id 取模分配到不同数据库表中；
  • 地理区域：七牛云按照华东华南这样的地理位置划分不同业务；
  • 时间：将 6个月、1年之前的数据放入一个表，因为是“冷数据”，查询概率也会变小。

常见的分表算法是上面两种。通过 hash 取模更方便数据平衡，但是不利于迁移数据。

8.3 常见问题

8.3.1 查询问题

针对于字段 A 的分表，在使用字段 B 查询时就会出现无法定位的问题。如果没法处理，就会导致最后全文搜索的情况。

  • 映射表：新建一个字段 A 和字段 B 的映射表，通过这个表查询对应关系。会导致修改时同时更改两个表的问题，且新建的表可能也需要分库分表；
  • 业务双写：同一份数据两套分表规则，两套完全一样的数据库，冗余一份数据。双写复杂性，两套分库分表同样存在分布式事务问题，多存储一份数据，资源浪费；
  • 异步双写：两套数据，两套分表规则，但是只是单写，另一个库使用 Binlog 变化去进行数据同步。多存储一份数据，资源浪费，引入新的中间件，增加系统复杂度；
  • 双纬度合一：把订单 id 和 userid 合并到一个纬度中，举个例子，当新产生一条订单的时候，生成订单 id，此时订单的用户 id 是 123456，那么其订单 id 可以根据分布式发号器生成一部分 1001，然后拼接一部分的用户 id 生成。新订单 id=1001456，前面是发号器生成，蓝色是截取用户 id 后 3位，因为根据分表 256张表，只和后 3位值有关系。那么此时新的订单 id 就包含用户 id 信息和 orderid 信息了。
    此时如果调用方根据订单 id 查询之间根据分表规则查询即可，如果调用方根据用户 id 查询，那么截取用户 id=123456 的后 3位，依然可以定位到指定数据表。

8.3.2 Join 问题

分库分表后 Join 无法使用了，针对这种情况，一般有下面几种方案解决：

  1. 不在数据库层做 Join，单用单表查询，在代码层做结果拼装；
  2. 宽表，新建立一个表，提前把 Join 后的视图写入这个表，重写轻读（类似物理视图）；
  3. 搜索引擎 ES；
  4. CQRS，读写模型分离，读的模型通过监听主库变化，实现写好读模型（Join 后的数据）。

8.3.3 分布式事务问题

分库分表后分布式事务问题可能产生，但是一般产生分布式事务可以通过产品层，代码层的流程优化解决，不一定非要使用分布式事务。实在解决不了，可以使用，TCC，Seats，消息补偿保障最终一致性等方案解决。

9. MVCC

MVCC（Multiversion Concurrency Control），多版本并发控制。通过数据行的多个版本管理来实现数据库的并发控制。相比于用锁来实现并发控制，MVCC 的并发能力更高（只有写 - 写之间相互阻塞），但是无法保证读出的数据是最新版本。

快照读和当前读

  • 快照读 又叫一致性读，读取的是快照数据而不是实时的最新数据。不会与写操作冲突。不加锁的简单的 SELECT 都是快照读。快照读的幻读由 MVCC 解决；
  • 当前读 读取的是最新版本，与写操作冲突，要保证读取过程中其他并发事务不能修改当前记录。加锁的 SELECT 或增删改操作都会执行当前读。当前读的幻读由临键锁解决。

MVCC 实现依赖于：版本链（trx_id 和 roll_pointer）、Undo 日志、ReadView

9.1 版本链

在 InnoDB 行格式中，每个聚簇索引都包含三个隐藏列

列名 是否必须 说明
row_id 否 创建的表中有主键或者非 NULL 的 UNIQUE 键时都不会包含 row_id 列
trx_id 是 事务 ID，每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务 id 赋值给 trx_id 隐藏列
roll_pointer 是 回滚指针，每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo 日志中，然后用 roll_pointer 指向这个旧的版本。同时，旧的版本也会有一个自己的 roll_pointer 指向更旧的一个版本。

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

版本链样子

9.2 Undo 日志

Undo 日志除了可以保证事务在 rollback 时的原子性和一致性，还可以用于存放 MVCC 的快照读的数据。

9.3 ReadView

上面说到，改动的记录都在 undo 日志中，那如何选择到底读取哪个版本的记录呢？

  • 对于使用 READ UNCOMMITTED 隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了；
  • 对于使用 SERIALIZABLE 隔离级别的事务来说，InnoDB 使用加锁的方式来访问记录，不存在并发问题；
  • 对于使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的。

核心问题就是：READ COMMITTED 和 REPEATABLE READ 隔离级别在不可重复读和幻读上的区别在哪里？这两种隔离级别对应的不可重复读与幻读都是指同一个事务在两次读取记录时出现不一致的情况，这两种隔离级别关键是需要判断版本链中的哪个版本是当前事务可见的。

ReadView 就可以用来帮助我们解决可见性问题。事务进行快照读操作的时候就会产生 ReadView，它保存了当前事务开启时所有活跃的事务列表（活跃指的是未提交的事务）。

ReadView 中主要保存了以下几个比较重要的内容：

  1. creator_trx_id，创建这个 ReadView 的事务 ID；

    说明：只有在对表中的记录做改动时（执行 INSERT、DELETE、UPDATE 这些语句时）才会为事务分配事务 id，否则在一个只读事务中的事务 id 值都默认为 0。

  2. m_ids，生成 ReadView 时当前系统中活跃的读写事务的事务 id 列表；

  3. min_trx_id，生成 ReadView 时当前系统中活跃的读写事务中最小的事务 id 也就是 m_ids 中的最小值；

  4. max_trx_id，表示生成 ReadView 时系统中应该分配给下一个事务的 id 值。

    注意：max_trx_id 并不是 m_ids 中的最大值，事务 id 是递增分配的。比如，现在有 id 为 1，2，3 这三个事务，之后 id 为 3 的事务提交了。那么一个新的读事务在生成 ReadView 时，m_ids 就包括 1 和 2，min_trx_id 的值就是 1，max_trx_id 的值就是 4。

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

  1. trx_id = creator_trx_id ，可访问 如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问；
  2. trx_id < min_trx_id ，可访问 如果被访问版本的 trx_id 属性值小于 ReadView 中的 min_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问；
  3. trx_id >= max_trx_id ，不可访问 如果被访问版本的 trx_id 属性值大于或等于 ReadView 中的 max_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问；
  4. min_trx_id <= trx_id < max_trx_id，并且存在 m_ids 列表中，不可访问 如果被访问版本的 trx_id 属性值在 ReadView 的 min_trx_id 和 max_trx_id 之间，那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问；
  5. 某个版本的数据对当前事务不可见 如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

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

当事务处在 READ COMMITTED 中，事务中的每条读语句都会重新生成一个 ReadView，这意味着历史版本对于这个事务的读操作是会不断变化的，因此有可能导致连续的两次读取内容不同，也就是不可重复读。

当事务处在 REPEATABLE READ 中，事务中只有第一条读语句会生成一个 ReadView，后面的所有读操作都会沿用第一次的 ReadView，从而保证每次读取的内容都一致。这样也就一次性解决了不可重复读和幻读的问题。

需要注意的一点：因为 ReadView 是只对快照读生效的，所以 MVCC 并不能完全解决幻读问题。当前读的幻读问题需要 Next-key Locks 解决。

MVCC 在可重复读的隔离级别下解决了以下问题：

  1. 通过历史版本，让读 - 写操作可以并发执行，提高了并发效率；
  2. 解决了脏读、不可重复读、（快照读情况下）幻读。

10. MySQL 事务

MySQL 的事务简要来说就是，要么都成功，要么都失败。

这就保证我们在插入一个表失败的时候，不会影响到其他表。因为MySQL有自动提交模式（即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交）。

事务的状态：

  • 活动（active）：事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态；
  • 部分提交的（partially committed）：当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在部分提交的状态；
  • 失败（failed）：当事务处在活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态；
  • 中止（aborted）：如果事务执行了一部分而变为失败的状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态。换句话说，就是要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称之为回滚。当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了中止的状态；
  • 提交（committed）：当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处在了提交的状态。

10.1 ACID

  • 原子性（Atomicity）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样；
  • 一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作；
  • 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）；
  • 持久性（Durability）： 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
set autocommit = 0 -- 关闭自动提交
start transaction -- 标记一个事务的开始
insert into `student`(`name`, `age`) values('cxy', 18)
insert into `employee`(`name`, `job`, `location`) values('ckt', 20, '南京市')
commit -- 提交：持久化（成功）
rollback
set autocommit = 1 -- 开启自动提交

savepoint 保存点名 -- 设置一个事务的保存点
rollback to savepoint 保存点名
release savepoint -- 撤销保存点

10.2 可能问题

  • 脏读：一个事务在执行的过程中读取到了其他事务还没有提交的数据；
  • 不可重复读：在同一事务中，多次读取同一数据返回的结果有所不同，换句话说，后续读取可以读到另一事务已提交的更新数据；
  • 幻读：当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。

10.3 隔离级别

隔离级别对问题的处理能力

  • READ UNCOMMITTED（未提交读）：事务中的修改，即使没有提交，对其他事务也都是可见的；
  • READ COMMITTED（提交读）：一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的；
  • REPEATABLE READ（可重复读）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的；
  • SERIALIZABLE（可串行化）：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

下面用一个例子，解释四种隔离模式的情况：

事务 A 和 事务 B

  • 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2；
  • 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2；
  • 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的；
  • 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

10.4 事务实现

以“可重复读”举例，在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

假设一个值从 1 被按顺序改成了 2、3、4

在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。

10.5 使用事务

MySQL 的事务启动方式有以下几种：

  1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback；
  2. set autocommit = 0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

10.5.1 显示事务

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

MySQL> BEGIN; 

MySQL> START TRANSACTION;

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

①READ ONLY：标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。

②READ WRITE：标识当前事务是一个读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。

③WITH CONSISTENT SNAPSHOT：启动一致性读。

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

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

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

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

其中关于 SAVEPOINT 相关操作有：

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

10.5.2 隐式事务

在 MySQL 中，如果没有显示地用 START TRANSACTION 或者 BEGIN 开启事务，那么每一条 DML 语句都会被包装成一个独立的事务并自动提交。

有一个系统变量 autocommit ，默认为 true ，可以将其修改为 false 来关闭自动提交效果。这样的话写入的多条 DML 语句都会属于同一个事务，直到手动提交或回滚。

有些客户端连接框架会默认连接成功后先执行一个 set autocommit = 0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。

在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。

SELECT
	* 
FROM
	information_schema.innodb_trx 
WHERE
	TIME_TO_SEC(
	timediff( now(), trx_started ))> 60

10.5.3 隐式提交数据的情况

  • 当使用 DDL 语句、修改 MySQL 的表结构时，就会隐式地提交之前未提交的事务；
  • 事务控制或关于锁定的语句；
  • 当我们在一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了另一个事务时，会隐式的提交上一个事务；
  • autocommit 为 true；
  • 使用 LOCK TABLES、UNLOCK TABLES 等关于锁定的语句也会隐式的提交前边语句所属的事务。

11. MySQL 三大范式

在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。可以理解为，一张数据表的设计结构需要满足的某种设计标准的级别。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

目前关系型数据库有六种常见范式，按照范式级别，从低到高分别是：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯 - 科德范式（BCNF）、第四范式 (4NF）和第五范式（5NF，又称完美范式）。高级别的范式满足低级别的范式。

范式只是设计的标准，实际上设计数据表时，不一定要完全满足这些标准。

属性：表中的字段；

元组：表中的一行数据；

码：表中可以唯一确定一个元组的某个属性（或者属性组），如果这样的码有不止一个，那么大家都叫候选码，我们从候选码中挑一个出来做老大，它就叫主码；

全码：如果一个码包含了所有的属性，这个码就是全码；

主属性：一个属性只要在任何一个候选码中出现过，这个属性就是主属性；

非主属性：与上面相反，没有在任何候选码中出现过，这个属性就是非主属性；

外码：一个属性（或属性组），它不是码，但是它别的表的码，它就是外码。

范式的优点：有助于消除数据冗余。

范式的缺点：可能会降低查询效率，因为范式等级越高，设计的表就越多，越精细，查询时就需要关联更多的表。

在实际开发中，一般只满足到第三范式或巴斯范式；为了提高查询的效率，可能会适当增加冗余数据。

11.1 三大范式

第一范式

数据表的每一列都要保持它的原子特性，也就是列不能再被分割。第一范式的合理遵循需要根据系统的实际需求来定。

比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。

将“地址”进行拆分

上表所示的用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。

第二范式

在第一范式满足的基础上，我们考虑第二范式。属性必须完全依赖于主键，每张表只描述一件事情。

完全依赖：设 X,Y 是关系 R 的两个属性集合，X’ 是 X 的真子集，存在 X→Y，但对每一个 X’ 都有 X’!→Y，则称 Y 完全函数依赖于 X。

部分依赖：设 X,Y 是关系 R 的两个属性集合，存在 X→Y，若 X’是 X 的真子集，存在 X’→Y，则称 Y 部分函数依赖于 X。

例如有一张表中有 key1，key2，列 1，列 2，列 3。完全依赖就是只有知道了 key1 和 key2 的值，才能唯一确定列 1，列 2，列 3。而如果只知道 key1 或 key2，则无法唯一确定列 1，列 2，列 3。

也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

img

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

通过商品编号获取商品

这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。

第三范式

在第一、第二范式的基础上，我们考虑第三范式。

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

每个表的属性都跟数据库直接相关

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。

11.2 巴斯范式

满足第三范式，主属性之间没有互相依赖。

11.3 实际使用

三大范式给了我们一个良好的规范，这样就容易导致我们创建多个表。

所以我们在考虑三大范式的同时，需要关注我们数据库本身的效率。我们可以在效率的基础上同时考虑规范。

当冗余信息有价值或者能够大幅提高查询效率，就会采取反范式化来优化。

添加冗余字段的建议：

  1. 冗余字段不需要经常修改；
  2. 冗余字段查询时不可或缺。

Redis

简单来说Redis 就是一个使用 C 语言开发的非关系型数据库，不过与传统数据库不同的是Redis 的数据是存在内存中的，读写速度非常快，因此被广泛应用于缓存方向。

另外，Redis 除了做缓存之外，也经常用来做分布式锁，甚至是消息队列。Redis 提供了多种数据类型来支持不同的业务场景，支持事务 、持久化、Lua 脚本、多种集群方案。

1. Redis 数据结构

1.1 String

String 的实现类似于 Java 中的 ArrayList，作为变长字符串。

一般常用在需要计数的场景，比如用户的访问次数、热点文章的点赞转发数量等等。

常用命令：set, get, strlen, exists, decr, incr, setex, mset, mget。

String 数据结构图示

1.2 List

实现类似 Java 中的 LinkedList，双向链表，对两端操作效率较高，但不适合使用索引查找。

List 的数据结构为快速链表 quickList。在列表元素较少的情况下会使用一块连续的内存存储 ziplist，分配连续的内存。当ziplist节点个数过多，quicklist 退化为双向链表，一个极端的情况就是每个 ziplist 节点只包含一个 entry，即只有一个元素。当 ziplist 元素个数过少时，quicklist 可退化为 ziplist，一种极端的情况就是 quicklist 中只有一个 ziplist 节点。

List 数据结构图示

常用命令：rpush, lpop, lpush, rpop, lrange, llen

1.3 Hash

hash 类似于 JDK1.8 前的 HashMap，内部实现也差不多(数组 + 链表)。hash 是一个 string 类型的 field 和 value 的映射表，特别适合用于存储对象，可以只修改值中某个字段。

常用命令：hset, hmset, hexists, hget, hgetall, hkeys, hvals。

hmset 1 name "cxy621" age 18 birthday 8-28

hgetall 1
# 1) "name"
# 2) "cxy621"
# 3) "age"
# 4) "18"
# 5) "birthday"
# 6) "8-28"

Hash 类型对应的数据结构是两种：ziplist（压缩列表），hashtable（哈希表）。当 field-value 长度较短且个数较少时，使用 ziplist，否则使用 hashtable。

1.4 Set

set 类似于 Java 中的 HashSet 。Redis 中的 set 类型是一种无序集合，集合中的元素没有先后顺序，数据不重复。

常用命令：sadd, spop, smembers, sismember, scard, sinterstore, sunion。

127.0.0.1:6379> sadd mySet value1 value2 # 添加元素进去
(integer) 2
127.0.0.1:6379> sadd mySet value1 # 不允许有重复元素
(integer) 0
127.0.0.1:6379> smembers mySet # 查看 set 中所有的元素
1) "value1"
2) "value2"
127.0.0.1:6379> scard mySet # 查看 set 的长度
(integer) 2
127.0.0.1:6379> sismember mySet value1 # 检查某个元素是否存在set 中，只能接收单个元素
(integer) 1
127.0.0.1:6379> sadd mySet2 value2 value3
(integer) 2
127.0.0.1:6379> sinterstore mySet3 mySet mySet2 # 获取 mySet 和 mySet2 的交集并存放在 mySet3 中
(integer) 1
127.0.0.1:6379> smembers mySet3
1) "value2"

1.5 ZSet

sorted set 增加了一个权重参数 score，使得集合中的元素能够按 score 进行有序排列，还可以通过 score 的范围来获取元素的列表。

常用命令：zadd, zcard, zscore, zrange, zrevrange, zrem。

Redis 采用的是跳跃表，跳跃表效率堪比红黑树，实现远比红黑树简单。

查询数据“51”

1.6 新的类型

  1. geospatial：Redis 3.2 推出 Geo 类型，该功能可以推算出地理位置信息，两地之间的距离；

  2. hyperloglog：基数：数学上集合的元素个数，是不能重复的。这个数据结构常用于统计网站的 UV；

  3. bitmap：bitmap 就是通过最小的单位 bit 来进行0或者1的设置，表示某个元素对应的值或者状态。一个 bit 的值，或者是0，或者是1；也就是说一个 bit 能存储的最多信息是2。bitmap 常用于统计用户信息比如活跃粉丝和不活跃粉丝、登录和未登录、是否打卡等。

2. Redis 线程

Redis 基于 Reactor 模式来设计开发了自己的一套高效的事件处理模型 （Netty 的线程模型也基于 Reactor 模式）。由于文件事件处理器（file event handler）是单线程方式运行的，所以我们一般都说 Redis 是单线程模型。

Redis 通过 IO 多路复用程序 来监听来自客户端的连接（或者说是监听多个 socket），根据套接字目前执行的任务来为套接字关联不同的事件处理器。 I/O 多路复用技术的使用让 Redis 不需要额外创建多余的线程来监听客户端的大量连接，降低了资源的消耗。

虽然文件事件处理器以单线程方式运行，但通过使用 I/O 多路复用程序来监听多个套接字，文件事件处理器既实现了高性能的网络通信模型，又可以很好地与 Redis 服务器中其他同样以单线程方式运行的模块进行对接，这保持了 Redis 内部单线程设计的简单性。

为什么 Redis 这么快？：

  • 完全基于内存，数据存在内存中，绝大部分请求是纯粹的内存操作，避免了通过磁盘 IO 读取到内存的开销；
  • 数据结构简单，对数据操作也简单。Redis 中的数据结构是专门进行设计的，每种数据结构都有一种或多种数据结构来支持。Redis 依赖这些灵活的数据结构，来提升读写性能；
  • 采用单线程，省去了很多上下文切换的时间以及 CPU 消耗，不存在竞争条件，不用去考虑各种锁的问题，不存在加锁释放锁操作，也不会出现死锁而导致的性能消耗；
  • 使用基于 IO 多路复用机制的线程模型，处理并发链接（多路：多个客户端连接（连接就是套接字描述符），复用：使用单进程就能够实现同时处理多个客户端的连接）；
  • Redis 直接自己构建了 VM 机制 ，避免调用系统函数的时候，浪费时间去移动和请求。

处理流程图示

2.1 Redis “单线程”

Redis 的单线程，指的是网络请求模块使用一个线程来处理，即一个线程处理所有网络请求，其他模块仍用了多个线程。

CPU 不是 Redis 的瓶颈，Redis 的瓶颈最有可能是机器内存或者网络带宽。单线程容易实现，所以最开始采用单线程策略。使用单线程的方式无法发挥多核 CPU 性能，可以通过在单机开多个 Redis 实例来解决这个问题。

Redis 在 4.0 之后的版本中已经加入对多线程的支持。Redis 4.0 增加的多线程主要是针对一些大键值对的删除操作的命令，使用这些命令就会使用主处理之外的其他线程来“异步处理”。大体上来说，Redis 6.0 之前主要还是单线程处理。

多线程模型虽然在某些方面表现优异，却引入程序执行顺序的不确定性，带来了并发读写的一系列问题，增加了系统复杂度、同时可能存在线程切换、甚至加锁解锁、死锁造成的性能损耗。

2.1.1

2.2 Redis “多线程”

为了处理网络 I/O 模块带来的 CPU 耗时，Redis 6.0 引入多线程，减少网络I/O阻塞带来的性能损耗。

默认情况下 Redis 是关闭多线程的，可以在 conf 文件进行配置开启：

io-threads-do-reads yes

io-threads # 线程数

## 官方建议的线程数设置：4核的机器建议设置为2或3个线程，8核的建议设置为6个线程，线程数一定要小于机器核数，尽量不超过8个。

Redis 的多线程部分只是用来处理网络数据的读写和协议解析，执行命令仍然是单线程顺序执行，也就不存在并发安全问题。

3. 持久化

Redis 提供两种持久化方式（将内存中的数据存入磁盘，防止数据丢失）。

3.1 RDB

Redis DataBase。在指定的时间间隔内将内存中的数据集快照写入磁盘， 也就是生成 Snapshot 快照，恢复时将快照文件直接读入内存中。

在 redis.conf 中配置文件名称，默认为 dump.rdb，默认的位置是 Redis 的安装目录。

Redis 单独创建（fork）一个子进程进行持久化，首先将数据写入一个临时文件中，待持久化过程都结束了，再用这个临时文件替换上次持久化好的文件。

整个过程中，主进程不进行任何 IO 操作，确保了极高的性能。如果需要进行大规模数据的恢复，且对于数据恢复的完整性不是非常敏感，RDB 方式比 AOF 方式更加高效。RDB 的缺点是最后一次持久化后的数据可能丢失。

流程图示

默认保存策略 config

在以下情况，RDB 会被触发：

  • save ：save 时只进行保存，全部阻塞。手动保存的选择，不建议使用；

  • bgsave：Redis 会在后台异步进行快照操作，快照同时还可以响应客户端请求；

  • 可以通过 lastsave 命令获取最后一次成功执行快照的时间；

  • flushall 也会执行，不过生成的文件为空，没有意义。

优势：

  • 适合大规模的数据恢复；

  • 对数据完整性和一致性要求不高更适合使用；

  • 节省磁盘空间；

  • 恢复速度快。

劣势：

  • Fork 的时候，内存中的数据被克隆了一份，大致 2 倍的膨胀性需要考虑；

  • 虽然 Redis 在 fork 时使用了写时拷贝技术，但是如果数据庞大时还是比较消耗性能；

  • 在备份周期在一定间隔时间做一次备份，所以如果 Redis 意外 down 掉，就会丢失最后一次快照后的所有修改。

3.2 AOF

鉴于 RDB 的劣势，AOF 已经成为主流的方式，默认没有开启。

以日志的形式来记录每个写操作（增量保存），将 Redis 执行过的所有写指令记录下来 (读操作不记录)， 只许追加文件但不可以改写文件，redis 启动之初会读取该文件重新构建数据。换言之，redis 重启根据日志文件的内容将写指令从前到后执行一次以完成数据的恢复工作。

运行流程示例

正常恢复：

  • 修改默认的 appendonly no，改为 yes；
  • 将有数据的 aof 文件复制一份保存到对应目录 (查看目录：config get dir)；
  • 恢复：重启 redis 然后重新加载。 
# 设置备份频率
appendfsync always    # 每次有数据修改发生时都会写入AOF文件，这样会严重降低Redis的速度
appendfsync everysec  # 每秒钟同步一次，显式地将多个写命令同步到硬盘
appendfsync no        # 让操作系统决定何时进行同步

AOF 采用文件追加方式，文件会越来越大为避免出现此种情况，新增了重写机制，当 AOF 文件的大小超过所设定的阈值时，Redis 就会启动 AOF 文件的内容压缩，只保留可以恢复数据的最小指令集，可以使用命令 bgrewriteaof。

AOF 文件持续增长而过大时，会 fork 出一条新进程来将文件重写 (也是先写临时文件最后再 rename)，Redis 4.0 版本后的重写，是指把 rdb 的快照，以二进制的形式附在新的 aof 头部，作为已有的历史数据，替换掉原来的流水账操作，节省磁盘。

AOF文件重写并不需要对现有的AOF文件进行任何读取、分享和写入操作，而是通过读取服务器当前的数据库状态来实现。

Redis 会记录上次重写时的 AOF 大小，默认配置是当 AOF 文件大小是上次 rewrite 后大小的一倍且文件大于 64M 时触发。

auto-aof-rewrite-percentage：100% 
# 设置重写的基准值，文件是原来重写后文件的 2 倍时触发。

auto-aof-rewrite-min-size：64
# 设置重写的基准值，最小文件 64MB。达到这个值开始重写。

# 系统载入时或者上次重写完毕时，Redis 会记录此时 AOF 大小，设为 base_size,
# 如果 Redis 的 AOF 当前大小 >= base_size + base_size * 100% (默认) 
# 且当前大小 >=64mb (默认) 的情况下，Redis 会对 AOF 进行重写。

重写流程图示

优势：

  • 备份机制更稳健，丢失数据概率更低；

  • 可读的日志文本，通过操作 AOF 稳健，可以处理误操作。

劣势：

  • 比起 RDB 占用更多的磁盘空间；

  • 恢复备份速度要慢；

  • 每次读写都同步的话，有一定的性能压力；

  • 存在个别 Bug，造成恢复不能。

3.3 官方建议

Redis 4.0 开始支持 RDB 和 AOF 的混合持久化（默认关闭，可以通过配置项 aof-use-rdb-preamble 开启）。

如果把混合持久化打开，AOF 重写的时候就直接把 RDB 的内容写到 AOF 文件开头。

这样做的好处是可以结合 RDB 和 AOF 的优点, 快速加载同时避免丢失过多的数据。缺点在于 AOF 里面的 RDB 部分是压缩格式不再是 AOF 格式，可读性较差。

性能建议：

  • RDB 文件只用作后备用途，建议只在 Slave 上持久化 RDB 文件，15分钟备份一次就够了，只保留 save 9001 这条规则；
  • 如果使用 AOF，好处是在最恶劣情况下也只会丢失不超过两秒数据，启动脚本较简单，只 load 自己的 AOF 文件就可以了；
  • AOF 代价：
    • 是带来了持续的 IO；
    • AOF rewrite 的最后，将 rewrite 过程中产生的新数据写到新文件造成的阻塞几乎是不可避免的。
  • 只要硬盘许可，应该尽量减少 AOF rewrite 的频率，AOF 重写的基础大小可以设到 5G 以上。默认超过原大小100%大小时重写可以改到适当的数值。

4. 内存淘汰

Redis 通过过期字典（可以看作是 hash 表）来保存数据过期的时间。过期字典的键指向 Redis 数据库中的某个 key，过期字典的值是一个 long long 类型的整数，这个整数保存了 key 所指向的数据库键的过期时间（毫秒精度的 UNIX 时间戳）。

typedef struct redisDb {
    ...
    dict *dict;     // 数据库键空间，保存着数据库中所有键值对
    dict *expires;   // 过期字典，保存着键的过期时间
    ...
} redisDb;
// 过期字典数据结构

4.1 删除策略

  1. 惰性删除：只在取出 key 的时候对数据进行过期检查。对 CPU 最友好，但是可能会造成太多过期 key 没有被删除，增加内存负担。
  2. 定期删除： 每隔一段时间抽取一批 key 执行删除过期 key 操作。并且，Redis 底层通过限制删除操作执行的时长和频率来减少删除操作对 CPU 时间的影响。

Redis 采用惰性删除+定期删除的方式。

4.2 淘汰策略

早期 Redis 六种淘汰策略：

  1. noeviction：不淘汰任何数据，当内存不足时，新增操作会报错，Redis 默认内存淘汰策略；
  2. allkeys-lru：淘汰整个键值中最久未使用的键值；
  3. allkeys-random：随机淘汰任意键值;
  4. volatile-lru：淘汰所有设置了过期时间的键值中最久未使用的键值（使用 LRU 算法淘汰过期键值）；
  5. volatile-random：随机淘汰设置了过期时间的任意键值；
  6. volatile-ttl：优先淘汰更早过期的键值。

Redis 4.0 后增加两种：

  1. volatile-lfu（least frequently used）：从已设置过期时间的数据集（server.db[i].expires）中挑选最不经常使用的数据淘汰；
  2. allkeys-lfu（least frequently used）：当内存不足以容纳新写入数据时，在键空间中，移除最不经常使用的 key。

LRU 和 LFU 算法：

  • LRU(Least Recently Used)：淘汰很久没被访问过的数据，以最近一次访问时间作为参考；
  • LFU(Least Frequently Used)：淘汰最近一段时间被访问次数最少的数据，以次数作为参考。

大部分情况使用 LRU 算法，如果存在热点数据，使用 LFU 算法相关策略可能会更好。

5. 常见问题

5.1 缓存穿透

大量请求的 key 根本不存在于缓存中，导致请求直接查询数据库，越过缓存。

解决方法：

  1. 对每一个查不到的 key，都在 Redis 中进行缓存。如果会对不同 key 进行请求，依然无法从根本上解决问题；

  2. 对于一些具有特定格式 key，可以使用规则进行过滤；

  3. 布隆过滤器：

    • 使用布隆过滤器中的哈希函数对元素值进行计算，得到哈希值（有几个哈希函数得到几个哈希值）；

    • 根据得到的哈希值，在位数组中把对应下标的值置为1。

    布隆过滤器存在误判情况：布隆过滤器说某个元素存在，小概率会误判。布隆过滤器说某个元素不在，那么这个元素一定不在。

    原理图示

5.2 缓存雪崩

缓存在同一时间大面积的失效，后面的请求都直接落到了数据库上，造成数据库短时间内承受大量请求。

有一些被大量访问数据（热点缓存）在某一时刻大面积失效，导致对应的请求直接落到了数据库上。

针对 Redis 服务不可用的情况：

  1. 采用 Redis 集群，避免单机出现问题整个缓存服务都没办法使用；
  2. 限流，避免同时处理大量的请求。

针对热点缓存失效的情况：

  1. 分析用户行为，尽量让失效时间点均匀分布。避免缓存雪崩的出现；
  2. 采用加锁计数，或者使用合理的队列数量来避免缓存失效时对数据库造成太大的压力。这种办法虽然能缓解数据库的压力，但是同时又降低了系统的吞吐量；
  3. 如果是因为某台缓存服务器宕机，可以考虑做主备，比如：redis主备，但是双缓存涉及到更新事务的问题，update可能读到脏数据，需要好好解决；
  4. 缓存永不失效。

6. Redis 事务

Redis 通过 MULTI、EXEC、WATCH 等命令来实现事务（transaction）功能。

  • 单独的隔离操作 ：事务中的所有命令都会序列化、按顺序地执行。事务在执行的过程中，不会被其他客户端发送来的命令请求所打断；

  • 没有隔离级别的概念 ：队列中的命令没有提交之前都不会实际被执行，因为事务提交前任何指令都不会被实际执行。Redis 本身也是单线程处理。所以就不会产生我们使用关系型数据库需要关注的脏读，幻读，重复读的问题；

  • 不保证原子性 ：事务中如果有一条命令执行失败，其后的命令仍然会被执行，没有回滚 （这点跟 MySQL 区别很大）。

  • 持久化具有一定缺陷：
    • 在单纯的内存模式下，事务肯定是不持久的；
    • 在 RDB 模式下，服务器可能在事务执行之后、RDB 文件更新之前的这段时间失败，所以 RDB 模式下的 Redis 事务也是不持久的；
    • 在 AOF 的总是 SYNC 模式下，事务的每条命令在执行成功之后，都会立即调用 fsync 或 fdatasync 将事务数据写入到 AOF 文件。但是，这种保存是由后台线程进行的，主线程不会阻塞直到保存成功，所以从命令执行成功到数据保存到硬盘之间，还是有一段非常小的间隔，所以这种模式下的事务也是不持久的。
# MULTI 开启事务
redis> MULTI
OK
# 事务开启之后，执行的命令添加到事务队列中
redis> SET "name" "Practical Common Lisp"
QUEUED
redis> GET "name"
QUEUED
redis> SET "author" "Peter Seibel"
QUEUED
redis> GET "author"
QUEUED
# EXEC 执行事务
redis> EXEC
1) OK
2) "Practical Common Lisp"
3) OK
4) "Peter Seibel"

WATCH 提供乐观锁，针对某一个键，如果在事务队列中，检测出被修改，事务执行失败。

Redis 事务并不支持回滚功能，导致 Redis 不保证原子性。Redis 命令只会因为错误的语法而失败（并且这些问题不能在入队时发现），或是命令用在了错误类型的键上面。换而言之，从实用性的角度来说，失败的命令是由编程错误造成的，而这些错误应该在开发的过程中被发现，而不应该出现在生产环境中。

因为不需要对回滚进行支持，所以 Redis 的内部可以保持简单且快速。

7. 主从复制

主机数据更新后根据配置和策略， 自动同步到备机的 master/slave 机制，Master 以写为主，Slave 以读为主，主从复制节点间数据是全量的。实现读写分离，减轻服务器的压力。

在另一个 Redis 实例中使用 SLAVEOF 命令，就会成为别人的从机。

7.1 复制

主从节点通过复制的操作，保证两者数据统一。在 Redis 2.8 之后，对原先的复制操作进行了优化。

复制的过程中，Redis 存在心跳检测，在命令传播阶段，从服务器默认会以每秒一次的频率，向主服务器发送命令。

2.7.1 老复制

Redis的复制功能分为同步（sync）和命令传播（command propagate）两个操作。

当客户端向从服务器发送SLAVEOF命令，要求从服务器复制主服务器时，从服务器首先需要执行同步操作。

收到 SYNC 命令的主服务器执行 BGSAVE 命令，在后台生成一个 RDB 文件，并使用一个缓冲区记录从现在开始执行的所有写命令。当主服务器的 BGSAVE 命令执行完毕时，主服务器会将 BGSAVE 命令生成的 RDB 文件发送给从服务器，从服务器接收并载入这个 RDB 文件。

sync 执行图示

在主服务器数据被修改时，为了让主从服务器再次回到一致状态，会执行命令传播。主服务器会将自己执行的写命令，也即是造成主从服务器不一致的那条写命令，发送给从服务器执行，当从服务器执行了相同的写命令之后，主从服务器将再次回到一致状态。

缺点：如果主服务器断线，从服务器通过自动重连接重新连上了主服务器，并继续复制主服务器。如果相差的数据只有一部分，整个复制的策略就显得十分低效（SYNC 命令是一个非常耗费资源的操作）。

2.7.2 新复制

Redis 2.8 开始，使用 PSYNC 命令代替 SYNC 命令来执行复制时的同步操作。

初次复制的情况和 SYNC 基本一致，都是完整重同步。

部分重同步则用于处理断线后重复制情况，当从服务器在断线后重新连接主服务器时，如果条件允许，主服务器可以将主从服务器连接断开期间执行的写命令发送给从服务器，从服务器只要接收并执行这些写命令，就可以将数据库更新至主服务器当前所处的状态。

PSYNC 示例

PSYNC 执行步骤

复制偏移量

执行复制的双方——主服务器和从服务器会分别维护一个复制偏移量，主服务器每次向从服务器传播N个字节的数据时，就将自己的复制偏移量的值加上N，从服务器每次收到主服务器传播来的N个字节的数据时，就将自己的复制偏移量的值加上N。

偏移量图示

如果主从服务器处于一致状态，那么主从服务器两者的偏移量总是相同的；如果主从服务器两者的偏移量并不相同，那么说明主从服务器并未处于一致状态。

复制积压缓冲区

执行部分重同步的话，主服务器通过复制积压缓冲区补偿从服务器A在断线期间丢失部分的数据。

复制积压缓冲区是由主服务器维护的一个固定长度（fixed-size）先进先出（FIFO）队列，默认大小为 1MB。可以根据需要调整复制积压缓冲区的大小。为了安全起见，可以将复制积压缓冲区的大小设为2 * second * write_size_per_second，这样可以保证绝大部分断线情况都能用部分重同步来处理。

如果 offset 偏移量之后的数据已经不存在于复制积压缓冲区，那么主服务器将对从服务器执行完整重同步操作。

服务器运行 ID

每个 Redis 服务器，不论主服务器还是从服务，都会有自己的运行 ID（RID）。运行 ID 在服务器启动时自动生成，由40个随机的十六进制字符组成。

如果从服务器保存的运行 ID 和当前连接的主服务器的运行 ID 并不相同，那么说明从服务器断线之前复制的主服务器并不是当前连接的这个主服务器，主服务器将对从服务器执行完整重同步操作。

7.2 哨兵模式

由一个或多个 Sentinel 实例（instance）组成的 Sentinel 系统（system）可以监视任意多个主服务器，以及这些主服务器属下的所有从服务器，并在被监视的主服务器进入下线状态时，自动将下线主服务器属下的某个从服务器升级为新的主服务器，然后由新的主服务器代替已下线的主服务器继续处理命令请求。

当一个 master 宕机后，后面的 slave 可以立刻升为 master，其后面的 slave 不用做任何修改。用 slaveof no one 指令将从机变为主机。哨兵模式能够后台监控主机是否故障，如果故障了根据投票数自动将从库转换为主库。

服务器与 Sentinel 系统

为了不丢失__sentinel__:hello频道的信息，Sentinel 必须专门用一个订阅连接来接收该频道的信息。

除了订阅频道之外，Sentinel 还必须向主服务器发送命令，与主服务器进行通信，所以 Sentinel 还必须向主服务器创建命令连接。

Sentinel 需要与多个实例创建多个网络连接，所以Sentinel使用的是异步连接。

双连接

7.3 Redis 集群

Redis 的哨兵模式基本已经可以实现高可用，读写分离 ，但是哨兵模式下每台 Redis 服务器都存储相同的数据，浪费内存。在 Redis 3.0上加入了 Cluster 集群模式，实现了 Redis 的分布式存储（每台 Redis 节点上存储不同的内容）。

客户端可以跟任意的节点进行连接，将数据存储在不同的节点位置。

image-20200531184321294

Redis 本身是使用 Hash 进行数据的存储，Cluster 集群模式中引入哈希槽（hash slot）。

Redis 集群有16384个哈希槽，每个 key 通过 CRC16 校验后对16384取模来决定放置哪个槽。集群的每个节点负责一部分 hash 槽。

如果当前集群有三个节点：

  • 节点 A 包含0到5460号哈希槽；
  • 节点 B 包含5461到10922号哈希槽；
  • 节点 C 包含10923到16383号哈希槽。

在 Redis 的每一个节点上，都有这么两个东西，一个是插槽（slot），它的的取值范围是：0-16383。还有一个就是 cluster，可以理解为是一个集群管理的插件。当我们的存取的 Key 到达的时候，Redis 会根据 CRC16 的算法得出一个结果，然后把结果对16384求余数，这样每个 key 都会对应一个编号在 0-16383 之间的哈希槽。

  • 所有的 redis 节点彼此互联（PING-PONG机制），内部使用二进制协议优化传输速度和带宽；
  • 节点的 fail 是通过集群中超过半数的节点检测失效时才生效；
  • 客户端与 Redis 节点直连，不需要中间代理层.客户端不需要连接集群所有节点，连接集群中任何一个可用节点即可。

8. 双写一致性

在业务的使用中，我们需要保证缓存和数据库保持一致，因此会指定一系列相关策略。

8.1 更新/删除缓存

淘汰 cache：

优点：操作简单，无论更新操作是否复杂，直接将缓存中的旧值淘汰；

缺点：淘汰 cache 后，下一次查询无法在 cache 中查到，会有一次 cache miss，这时需要重新读取数据库。

更新 cache：

更新 chache 的意思就是将更新操作也放到缓冲中执行，并不是数据库中的值更新后再将最新值传到缓存

优点：命中率高，直接更新缓存，不会有 cache miss 的情况；

缺点：更新 cache 消耗较大。

对于更新情况复杂的缓存，考虑到开销问题，更推荐删除缓存。

8.1.1 缓存/数据库

不管是选择先更新缓存还是先更新数据库，都会出现“ABBA”问题，即两个线程同时修改出现的脏读问题。

对同一个数据的修改，要以串行化的方式先后执行。

8.2 执行顺序

对于淘汰缓存，依然会存在各种问题，需要通过消息队列等其他手段进行优化。

8.2.1 淘汰缓存，更新数据库

在并发量较大的情况下，采用异步更新缓存的策略：

  1. A 线程进行写操作，淘汰缓存，但由于网络或其它原因，还未更新数据库或正在更新；  
  2. B 线程进行读操作，此时缓存已经被淘汰，从数据库中读取数据，但 B 线程只从数据库中读取数据，并不将这个数据放入缓存中，不会导致缓存与数据库的不一致；
  3. A 线程更新数据库后，通过订阅 binlog 来异步更新缓存。

可以下面方法进行优化：

  1. 串行化：

    保证对同一个数据的读写严格按照先后顺序串行化进行，避免并发时，多个线程同时对同一数据进行操作时带来的数据不一致性；

  2. 延时双删+设置缓存：

    在 A 线程更新完数据之后，休眠 M 秒（时间需要大于业务中读取数据库的时间），之后再次进行缓存淘汰。需要引入“重试”机制防止淘汰失败。

针对双删导致的效率问题，可以使用“异步淘汰”的策略，将休眠M秒以及二次淘汰放在另一个线程中，A线程在更新完数据库后，可以直接返回成功而不用等待。

异步淘汰 图示

8.2.2 更新数据库，淘汰缓存

可能会出现数据库未更新或者缓存没有及时清除，导致的不一致情况。

推荐使用这种策略：

  • 如果先行淘汰缓存，可能会出现缓存穿透问题，导致大量请求直接访问数据库，造成负担；
  • 延时双删的具体业务时间难以掌握。

9. 坑

9.1 持久化问题

开启 AOF 持久化后，如果 Redis 重启，默认会从 AOF 文件恢复数据，而不是从 RDB 文件中恢复。如果 AOF 文件损坏或者不存在，那么数据就会丢失。此外，在开启 AOF 持久化时，每执行一条会更改 Redis 数据的命令，Redis 就会将该命令写入缓存 server.aof_buf 中，然后根据参数决定什么时间将缓存中的数据写入磁盘。

为了避免开启 AOF 后数据丢失，你可以在开启 AOF 持久化之前，先在 redis-cli 中执行 BGREWRITEAOF 命令。这样，在开启 AOF 持久化后，Redis 会将内存中的数据对应的日志写入 AOF 文件中。此时，AOF 和 RDB 两份数据文件的数据就同步了。

BGREWRITEAOF 命令用于异步执行重写 AOF 文件的操作。当 AOF 文件过大时，可以使用该命令对 AOF 文件进行重写，以减小文件大小。在执行该命令时，Redis 会根据内存中的数据生成一份新的 AOF 文件，然后用新的 AOF 文件替换旧的 AOF 文件。

参考文章

  1. JavaGuide
  2. MySQL 存储引擎 InnoDB 详解
  3. MySQL 行格式选择
  4. MySQL InnoDB 锁算法
  5. MySQL 执行计划
  6. MySQL 字符集不一致导致索引失效
  7. 深入解析 MySQL binlog
  8. binlog 详解
  9. MySQL Binlog
  10. PolarDB 特性分析 Explain Format Tree 详解
  11. MySQL 数据库教程天花板
  12. MySQL InnoDB 中的锁-插入意向锁
  13. MySQL InnoDB 隐式锁功能解析
  14. 彻底搞懂 MySQL 死锁
  15. MySQL 自增主键为什么不连续
  16. MySQL 聚集索引和非聚集索引
  17. MySQL 主键还需要建立索引吗
  18. MySQL 联合索引
  19. 常见 MySQL 的慢查询优化方式
  20. MySQL 中 USING 用法
  21. MySQL 主从复制原理不再难
  22. 看完这篇还不懂 MySQL 主从复制，可以回家躺平了
  23. MySQL 分库分表方案
  24. MySQL - 分库分表产生的问题&解决方案
  25. 分库分表后，数据库数据一致性问题如何解决？
  26. 面试突击：MySQL 常用引擎有哪些
  27. MySQL 如何解决幻读（MVCC 原理分析）
  28. 数据库设计三大范式-阿里云开发者社区
  29. 一文揭秘单线程的 Redis 为什么这么快
  30. 《面试八股文》之 Redis 16卷
  31. Redis 持久化机制
  32. Redis 学习核心实战
  33. 缓存雪崩，缓存穿透解决方案
  34. Redis 事务为什么不支持回滚
  35. Redis 设计与实现
  36. 如何保证缓存与数据库的一致性
  37. Redis 的三种集群模式
  38. CRC-8 和 CRC-16 算法
文章作者: 陈鑫扬
文章链接: https://hexo.cxy621.top/2022/04/26/shu-ju-ku-ba-gu-wen/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 陈鑫扬 !
Redis MySQL
评论
  目录