核心概念

1. 镜像： 容器的模板 （模具）
2. 容器： 镜像的实例 （糕点）
3. 镜像仓库： 存储镜像的中心仓库 （模具库）

相关命令行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

docker --version # 查看docker 版本 安装成功

docker pull nginx # 下载nginx 镜像

docker images # 查看已安装所有镜像
# 输出结果如下：
# IMAGE          ID             DISK USAGE   CONTENT SIZE   EXTRA
# nginx:latest   d0d674272be3        271MB         67.9MB  

docker rmi d0d674272be3 # 删除nginx 镜像 （rmi + 镜像ID）
docker rmi nginx # 删除nginx 镜像 （rmi + 镜像名）

docker run nginx # 启动nginx 容器，但是不会在后台运行

docker run -d -p 80:80 nginx # 启动nginx 容器
-d 表示后台运行
-p 80:80 表示映射端口，冒号后面80 是容器运行端口，冒号前面80 是主机端口

docker ps # 查看所有正在运行的容器
# 输出结果如下：
# CONTAINER ID   IMAGE     COMMAND                  CREATED         STATUS         PORTS                                 NAMES
# 465f1343d282   nginx     "/docker-entrypoint.…"   5 seconds ago   Up 5 seconds   0.0.0.0:80->80/tcp, [::]:80->80/tcp   kind_lovelace



docker stop 465f1343d282 # 停止nginx 容器 
docker ps -a # 查看所有容器，包括停止的容器
docker start 465f1343d282 # 启动nginx 容器
docker rm -f 465f1343d282 # 删除nginx 容器
-f 表示强制删除, 即使容器正在运行也会删除


docker run -d -p 80:80 -v /Users/project/docker/html:/usr/share/nginx/html nginx # 启动nginx 容器，挂载本地目录到容器目录
-v 表示挂载宿主机目录到容器目录，冒号后面是容器目录，冒号前面是宿主机目录，此时访问宿主机IP:80 就可以访问到宿主机目录下的文件了 【绑定挂载】
   宿主机目录会覆盖容器内目录，宿主机目录修改改变时，容器内目录也会改变，容器被删除时，目录内容在宿主机上依然存在，从而实现数据持久化

docker volume create docker_html # 创建一个docker_html 的卷，卷名称为docker_html
docker run -d -p 80:80 -v docker_html:/usr/share/nginx/html nginx # 启动nginx 容器，挂载docker_html 卷到容器目录, 就不用指定宿主机挂载目录了
docker volume inspect docker_html # 查看docker_html 卷信息
docker volume rm docker_html # 删除docker_html 卷
docker volume prune -a  # 删除所有容器都未使用的卷

# 启动mongoDB 容器，并传入环境变量，设置数据库用户名和密码
docker run -d \
-p 27017:27017 \
-e MONGO_INITDB_ROOT_USERNAME=tech \ 
-e MONGO_INITDB_ROOT_PASSWORD=tech123456 \ 

-e 表示设置环境变量

docker run -d --name my_nginx nginx # 启动nginx 容器，容器名称为my_nginx
--name 表示设置容器名称，要再宿主机中唯一，方便识别，之后删除容器也可用这里命名的这个名字

docker run -it --rm alpine  # 临时进入容器调试
-it 表示进入容器并开启交互模式
--rm 表示容器退出后，自动删除容器

docker run -d --restart always nginx # 启动nginx 容器，容器退出后，自动重启
--restart 配置容器停止时的重启策略
always 表示容器退出（崩溃，断电等意外 + 手动停止）后，自动重启
unless-stopped 跟always 类似，但是容器被手动停止时，不会重启

docker inspect 465f1343d282 # 查看nginx 容器信息, 包括启动时的配置参数

docker create nginx # 创建一个nginx 容器，但是不会在后台运行 需要再执行docker start nginx 启动容器
docker logs 465f1343d282 # 查看nginx 容器日志
docker logs 465f1343d282 -f # 滚动查看日志
-f --follow 表示实时查看日志，容器退出后，日志也会继续打印

docker exec 465f1343d282 linux命令 # 进入容器 执行linux命令
docker exec -it 465f1343d282 /bin/sh # 进入一个正在运行的docker 容器 内部，获得一个交互式的命令行环境，容器内容就像一个独立操作系统
cat /etc/os-release # 查看当前容器内Linux 发行版本，方便安装其他命令行，eg.vi
exit # 退出容器交互式环境，容器会自动停止

dockerfile

dockerfile 是一个文件，用于描述如何构建一个镜像
制作镜像步骤

1. 实际脚本

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

   docker_test/main.py # 启动一个fastapi服务，默认端口为8000 import uvicorn from fastapi import FastAPI app = FastAPI() @app.get("/") def read_root(): return {"Hello": "World"} if __name__ == "__main__": uvicorn.run(app, host="0.0.0.0", port=8000) docker_test/requirements.txt fastapi uvicorn

2. 创建Dockerfile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

docker_test/Dockerfile
# 注意Dockerfile 是一个文本文件，所有命令都大写，不带任何后缀

# 指定基础镜像为python:3.13-slim
FROM python:3.13-slim

WORKDIR /app

COPY . .

RUN pip install -r requirements.txt

EXPOSE 8000

CMD ["python3", "main.py"]

3. 构建镜像

1
2
3
4
5
6
7

docker build -t docker_test . # 在docker_test 目录下，构建镜像，镜像名称为docker_test

docker run -d -p 8000:8000 docker_test # 启动容器，映射端口8000:8000，容器名称为docker_test，本地测试

docker login # 登录docker 镜像仓库，需要输入用户名和密码，登录后即可推送镜像到仓库
docker build -t yoohannah/docker_test . # 在docker_test 目录下，重新构建携带用户名的镜像，镜像名称为yoohannah/docker_test，这里用户名充当namespace作用
docker push yoohannah/docker_test # 推送镜像到仓库

4. 查找使用
   在https://hub.docker.com/ 查找yoohannah/docker_test

1

docker pull yoohannah/docker_test

其他人即可以使用刚刚创建的镜像

docker 网络

默认bridge ,桥接模式

容器内部有容器网络，容器可以根据分配的IP 地址进行通信
容器网络和宿主机网路隔离

1

docker network create network1 # 创建一个子网network1, 属于bridge 模式一种

可以指定容器加入不同子网，
在不同子网的容器之间不能直接通信，
在相同子网的容器之间可以相互通信,docker 子网内部有一个dns机制，可以把名字转成IP地址
因此，同一个子网的容器，可以直接使用容器名字互相访问，而不必使用IP地址

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

docker run -d --name docker_test --network network1 docker_test
docker run -d --name my_nginx --network network1 nginx

docker exec -it my_nginx /bin/sh 
apt-get install -y iputils-ping
ping docker_test # 测试容器间通信，在my_nginx 容器内ping docker_test 容器

打印结果：
PING docker_test (172.18.0.2) 56(84) bytes of data.
64 bytes from docker_test.network1 (172.18.0.2): icmp_seq=1 ttl=64 time=0.254 ms
64 bytes from docker_test.network1 (172.18.0.2): icmp_seq=2 ttl=64 time=0.166 ms
64 bytes from docker_test.network1 (172.18.0.2): icmp_seq=3 ttl=64 time=0.195 ms
64 bytes from docker_test.network1 (172.18.0.2): icmp_seq=4 ttl=64 time=0.169 ms
64 bytes from docker_test.network1 (172.18.0.2): icmp_seq=5 ttl=64 time=0.206 ms
64 bytes from docker_test.network1 (172.18.0.2): icmp_seq=6 ttl=64 time=0.200 ms
^C
--- docker_test ping statistics ---
6 packets transmitted, 6 received, 0% packet loss, time 5128ms
rtt min/avg/max/mdev = 0.166/0.198/0.254/0.029 ms

host 模式

docker 容器直接共享宿主机的网络
容器直接使用宿主机的IP地址，无-p 映射端口，容器服务直接运行在宿主机的端口上
通过宿主机的IP地址和端口，可以直接访问到容器的服务
mac 需要开启设置
开启路径是：Settings → Resources → Network → Enable host networking。

1
2
3
4
5
6
7

docker run -d --network host nginx

# 进入容器内部 产看ip 信息
docker exec -it ae2a9a90a075 /bin/sh
 apt-get update
 apt-get install iproute2 -y
 ip addr show# 查看容器内ip 信息

直接用宿主机的IP地址和端口，可以直接访问到容器的服务

1

http://127.0.0.1:80

none 模式

容器不联网

查看docker 网络

1
2
3
4
5
6
7
8
9

docker network list # 展示所有docker网络

NETWORK ID     NAME       DRIVER    SCOPE
82a512410da4   bridge     bridge    local
97f3e9474823   host       host      local
383595c36af6   network1   bridge    local
415059793d19   none       null      local

docker network rm NETWORKID # 删除指定网络

docker compose

容器编排技术
使用yml文件管理多个容器，里面列出了容器之间是如何创建以及如何协同工作的
docker compose 文件可以被理解成一个多个docker run 命令 按照特定格式列到了一个文件中

docker 为每一个 compose 文件会自动创建子网，同一个 compose 文件中定义的所有容器自动加入同一个子网，所以在文件中不用二外写入网络创建和指定子网

compose 文件 可以自定义容器启动顺序

1
2
3
4
5
6
7

# 当前目录下有docker-compose.yaml 文件
docker compose up -d # 创建子网 启动compose 文件中定义的所有容器，-d 表示后台运行
docker compose down # 停止并删除compose 文件中定义的所有容器
docker compose stop # 停止compose 文件中定义的所有容器
docker compose start # 启动compose 文件中定义的所有容器

docker compose -f /user/test.yaml up -d  # 通过-f 指定非标 yaml 文件,启动容器

主从模式

架构模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

                +----------+
                |  Master  |
                +----------+
                /    |     \
               /     |      \
              /      |       \
             v       v        v
       +---------+ +---------+ +---------+
       |  Slave  | |  Slave  | |  Slave  |
       +---------+ +---------+ +---------+
        /       \
       /         \
      v           v
+---------+   +---------+
|  Slave  |   |  Slave  |
+---------+   +---------+

一主多从复制有四大优点：分摊负载、专机专用、便于冷备、高可用。

实现原理

复制概述

Replication，MySQL 的主从复制是一种数据同步机制，除了可以将一个主数据库中的数据同步复制到一个从数据库上，还可以将一个主数据库上的数据同步复制到多个从数据库上，也就是所谓的 MySQL 的一主多从复制。

多个从数据库关联到主数据库后，将主数据库上的 Binlog 日志同步地复制到了多个从数据库上，通过执行日志，每个从数据库的数据都和主数据库上的数据保持了一致。

这里面的数据更新操作表示的是所有数据库的更新操作，除了 SELECT 之类的查询读操作以外，其他的 INSERT、DELETE、UPDATE 这样的 DML 写操作，以及 CREATE TABLE、DROP TABLE、ALTER TABLE 等 DDL 操作都可以同步复制到从数据库上去。

Replication，复制使来自一个MySQL数据库服务器（源）的数据可以复制到一个或多个MySQL数据库服务器（副本）。

• 默认情况下，复制是异步的
• 复制品不需要永久连接即可接收源的更新
• 根据配置，可以复制数据库中的所有数据库、选定的数据库，甚至选定的表。

MySQL中复制的优点包括：

• 扩展解决方案：在多个副本之间分散负载以提高性能。
  • 读写分离，所有写入和更新都必须在源服务器上进行。然而，读取可能会在一个或多个副本上进行。
  • 该模型可以提高写入性能（源服务器专门用于更新），同时大幅提高越来越多的副本的读取速度。
• 数据安全：由于副本可以暂停复制过程，因此可以在副本上运行备份服务，而不会损坏相应的源数据。
• 分析：可以在源代码上创建实时数据，而信息分析可以在副本上进行，而不会影响源的性能。
• 远程数据分发：可以使用复制创建本地数据副本供远程站点使用，而无需永久访问源。

异步复制

复制流程：

• 主库 Master 将数据库的变更操作记录在二进制日志 Binary Log 中。
• 备库 Slave 读取主库上的日志并写入到本地中继日志 Relay Log 中。
• 备库读取中继日志 Relay Log 中的 Event 事件在备库上进行重放 Replay（回放）。

线程工作：

1. Master 服务器上对数据库的变更操作记录在 Binlog 中。
2. Master 的 Binlog Dump Thread 接到写入请求后读取 Binlog 推送给 Slave I/O Thread。
3. Slave I/O Thread 将读取的 Binlog 写入到本地 relay log 文件。
4. Slave SQL Thread 检测到 relay log 的变更请求，解析 relay log 并在从库上进行应用。

以上整个复制过程都是异步操作，所以主从复制俗称异步复制（MySQL 默认的复制策略），存在数据延迟。Master 数据变更后记录 Binlog，只是通知 Binlog Dump Thread 线程处理，然后告诉存储引擎提交事务，并不会关注 Slave 是否接受并落地 Binlog Event。

异步复制性能最好，但是一旦 Master 崩溃，发送主从切换将会发送数据不一致性的风险。

考虑到一个场景：主库正常写入数据并提交事务 T1，但是 Slave1 和 Slave2 由于某种原因（例如网络原因）一直无法接受到 Binlog Dump Thread Event 的推送请求，如果这时候 Master Crash，Slave 提升为 Master 后导致事务 T1 数据丢失。

全同步复制

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

对于全同步复制，当主库提交事务之后，所有的从库节点必须收到，APPLY并且提交这些事务，然后主库线程才能继续做后续操作。这里面有一个很明显的缺点就是，主库完成一个事务的时间被拉长，性能降低。

半同步复制

除了内置的异步复制外，MySQL 8.0还支持由插件实现的半同步复制接口。半同步复制介于异步复制和全同步复制之间。主服务器等待至少一个副本接收并记录事件，然后提交事务。

主服务器不会等待所有副本确认接收，它只需要副本的确认，而不是事件已在副本端完全执行和提交。因此，半同步复制保证，如果主服务器崩溃，它提交的所有事务都已传输到至少一个副本。

相比异步复制，半同步复制提高了数据完整性，在一个事务提交成功之后，这个事务就至少会存在于两个地方（主服务器、某一个从服务器）。

与异步复制相比，半同步复制的性能和数据完整性的权衡。

增加的延迟是将提交发送到副本并等待副本确认接收的TCP/IP往返时间（发送binlog到从机、从机写入relay log、从机返回ack）。这意味着半同步复制最适合通过快速网络通信的近距离服务器，而最不适合通过慢网络通信的远程服务器。半同步复制还限制了二进制日志事件从源（主服务器 master）发送到副本（从服务器 slave）的速度，超时时间。当一个用户太忙时，这会减慢速度，这在某些部署情况下可能会有用。

半同步复制具体特性：

• 从库会在连接到主库时告诉主库，它是否支持半同步。
• 如果半同步复制在主库端是开启了的，并且至少有一个半同步复制的从库节点，那么此时主库的事务线程在提交时会被阻塞并等待，结果有两种可能，要么至少一个从库节点通知它已经收到了所有这个事务的Binlog事件，要么一直等待直到超时，如果超时则半同步复制将自动关闭，转换为异步复制。
• 从库节点只有在接收到某一个事务的所有Binlog，将其写入并Flush到Relay Log文件之后，才会通知对应主库上面的等待线程。
• 半同步复制必须是在主库和从库两端都开启时才行，如果在主库上没打开，或者在主库上开启了而在从库上没有开启，主库都会使用异步方式复制。

对于当前会话的客户端进行事务提交后，主库等待 ACK 的过程中有两种情况。

1. 事务还没发送到从库，主库 crash 并发起切换，从库为新主库。客户端收到事务提交失败的信息，需要重新提交该事务。
2. 事务已经发送到从库，主库 crash 并发起切换，从库为新主库。从库已经应用该事务并写入数据，但客户端连接重置同样会收到事务提交失败的信息，重新提交该事务时会报错数据已存在（如订单已提交成功）

复制延时解决

由于主库和从库是两个不同的数据源，主从复制过程会存在一个延时，当主库有数据写入之后，同时写入 binlog 日志文件中，然后从库通过 binlog 文件同步数据，由于需要额外执行日志同步和写入操作，这期间会有一定时间的延迟。特别是在高并发场景下，刚写入主库的数据是不能马上在从库读取的，要等待几十毫秒或者上百毫秒以后才可以。

主从复制本质是实现的数据的最终一致性，而不是强一致性。在非常短的时间内，主从机器可能会存在数据不一致的情况。

在某些对一致性要求较高的业务场景中，这种主从导致的延迟会引起一些业务问题，比如订单支付，付款已经完成，主库数据更新了，从库还没有，这时候去从库读数据，会出现订单未支付的情况，在业务中是不能接受的。

为了解决主从同步延迟的问题，通常有以下几个方法：

1. 敏感业务强制读主库
   在开发中有部分业务需要写库后实时读数据，这一类操作通常可以通过强制读主库来解决。

2. 关键业务不进行读写分离
   对一致性不敏感的业务，比如电商中的订单评论、个人信息等可以进行读写分离，对一致性要求比较高的业务，比如金融支付，不进行读写分离，避免延迟导致的问题。

读写分离

大多数互联网业务中，往往读多写少，这时候数据库的读会首先成为数据库的瓶颈。如果我们已经优化了SQL，但是读依旧还是瓶颈时，这时就可以选择“读写分离”架构了。读写分离首先需要将数据库分为主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过主从复制机制进行数据的同步，如图所示。
写：主库
读：主库、从库

在应用中可以在从库追加多个索引来优化查询，主库这些索引可以不加，用于提升写效率。
读写分离架构也能够消除读写锁冲突从而提升数据库的读写性能。使用读写分离架构需要注意：主从同步延迟和读写分配机制问题

主从同步延迟

使用读写分离架构时，数据库主从同步具有延迟性，数据一致性会有影响，对于一些实时性要求比较高的操作，可以采用以下存储层解决方案。

• 写后立刻读
  在写入数据库后，某个时间段内读操作就去主库，之后读操作访问从库。
• 二次查询策略
  先去从库读取数据，找不到时就去主库进行数据读取。该操作容易将读压力返还给主库，为了避免恶意攻击，建议对数据库访问API操作进行封装，有利于安全和低耦合。
• 根据业务特殊处理
  根据业务特点和重要程度进行调整，比如重要的，实时性要求高的业务数据读写可以放在主库。对于次要的业务，实时性要求不高可以进行读写分离，查询时去从库查询。

读写分离落地

读写路由分配机制是实现读写分离架构最关键的一个环节，就是控制何时去主库写，何时去从库读。目前较为常见的实现方案分为以下两种：

• 基于编程和配置实现（应用层）
  程序员在代码中封装数据库的操作，代码中可以根据操作类型进行路由分配，增删改时操作主库，查询时操作从库。这类方法也是目前中小型企业生产环境下应用最广泛的。
  优点是实现简单，因为程序在代码中实现，不需要增加额外的硬件开支，缺点是需要开发人员来实现，运维人员无从下手，如果其中一个数据库宕机了，就需要修改配置重启项目。
• 基于数据库代理实现（数据网关层）

中间件代理一般介于应用服务器和数据库服务器之间，从图中可以看到，应用服务器并不直接进入到master数据库或者slave数据库，而是进入MySQL proxy代理服务器。代理服务器接收到应用服务器的请求后，先进行判断然后转发到后端master和slave数据库。

• MySQL Proxy：是官方提供的MySQL中间件产品可以实现负载平衡、读写分离等。
• MyCat：MyCat是一款基于阿里开源产品Cobar而研发的，基于 Java 语言编写的开源数据库中间件。
• ShardingSphere：ShardingSphere是一套开源的分布式数据库中间件解决方案，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar （计划中）这3款相互独立的产品组成。已经在2020年4月16日从Apache孵化器毕业，成为Apache顶级项目。
• Atlas：Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个数据库中间件。

主主模式

很多企业刚开始都是使用MySQL主从模式，一主多从、读写分离等。但是单主如果发生单点故障，从库切换成主库还需要作改动。因此，如果是双主或者多主，就会增加MySQL入口，提升了主库的可用性。

因此随着业务的发展，数据库架构可以由主从模式演变为双主模式。双主模式是指两台服务器互为主从，任何一台服务器数据变更，都会通过复制应用到另外一方的数据库中。

工作原理

一主多从，当主数据库宕机不可用的时候，数据依然是不能够写入的，因为数据不能够写入到从服务器上面去，从服务器是只读的。

为了解决主服务器的可用性问题，我们可以使用 MySQL 的主主复制方案。所谓的主主复制方案是指两台服务器都当作主服务器，任何一台服务器上收到的写操作都会复制到另一台服务器上。

当客户端程序对主服务器 A 进行数据更新操作的时候，主服务器 A 会把更新操作写入到 Binlog 日志中，然后 Binlog 会将数据日志同步到主服务器 B，写入到主服务器的 Relay log 中，然后执行 Relay log，获得 Relay log 中的更新日志，执行 SQL 操作写入到数据库服务器 B 的本地数据库中。B 服务器上的更新也同样通过 Binlog 复制到了服务器 A 的 Relay log 中，然后通过 Relay log 将数据更新到服务器 A 中，通过这种方式，服务器 A 或者 B 任何一台服务器收到了数据的写操作都会同步更新到另一台服务器，实现了数据库主主复制。主主复制可以提高系统的写可用，实现写操作的高可用。

如下图所示，正常情况下用户会写入到主服务器 A 中，然后数据从 A 复制到主服务器 B 上。当主服务器 A 失效的时候，写操作会被发送到主服务器 B 中去，数据从 B 服务器复制到 A 服务器。

再具体看一下主主失效的维护过程，如下图。

最开始的时候，所有的主服务器都可以正常使用，当主服务器 A 失效的时候，进入故障状态，应用程序检测到主服务器 A 失效，检测过程可能需要几秒钟或者几分钟的时间，然后应用程序需要进行失效转移，将写操作发送到备份主服务器 B 上面去，将读操作发送到 B 服务器对应的从服务器上面去。一段时间后故障结束，A 服务器需要重建失效期间丢失的数据，也就是把自己当作从服务器去从 B 服务器上面同步数据，同步完成后系统才能恢复正常。这个时候 B 服务器是用户的主要访问服务器，A 服务器当作备份服务器。

主主复制注意事项

• 不要对两个数据库同时进行数据写操作，因为这种情况会导致数据冲突。
  两个服务器对同一条记录进行写操作，互相进行数据复制的时候，数据库就不知道哪条数据是正确的。

• 主主复制并没有增加写并发的能力和系统存储能力。
  因为数据复制后，所有的数据库存储的数据都是一样的，不管是主主复制，还是主从复制。如果存储资源不足、磁盘不够大，数据复制后，即使写到多个服务器上，存储依然是不够用的。同时，它也没有增加写并发能力，即使使用主主复制，应用程序一个时间内也只能向一个数据库写入。

• 更新数据表的结构会导致巨大的同步延迟。
  比如要在一张表中增加一个字段，要执行一个 ALTER TABLE 操作，这个操作会导致同步延迟巨大，因为该操作会阻塞其它的 Binlog 日志同步，这时候主数据库的很多写操作都无法同步到从数据库上面去，导致数据不一致。所以在实践中需要更新表结构的操作，不要写入到 Binlog 中，也就是关闭更新表结构的 Binlog。如果要对表结构进行更新，应该有运维工程师DBA 对所有主从数据库分别手动进行数据表结构的更新操作。

单写还是双写

使用双主双写还是双主单写？

建议大家使用双主单写，因为双主双写存在以下问题：

• ID冲突
  • 自增策略，A（1,2）1,3,5…. B（2,2）2,4,6,…
  • 分布式ID生成解决方案，借助于雪花算法、中间件生成id
• 写冲突
  • 两个客户端分别在同一个时刻针对同一个行数据进行更新，Thread1->A，Thread2—>B

高可用架构如下图所示，其中一个Master提供线上服务，另一个Master作为备胎供高可用切换，Master下游挂载Slave承担读请求。

随着业务发展，架构会从主从模式演变为双主模式，建议用双主单写，再引入高可用组件，例如Keepalived和MMM等工具，实现主库故障自动切换。

集群高可用

高可用性

系统高可用的挑战

一个互联网应用想要完整地呈现在最终用户的面前，需要经过很多个环节，任何一个环节出了问题，都有可能会导致系统不可用。

系统的高可用架构，说的就是如何去应对这些问题和挑战。

互联网应用可用性的度量

业务常用的描述方法是“系统的可用性达到几个 9”。这“几个 9”就表示可用水平，正如下图所示，9 越多意味着可用性越高。常说的“5 个 9”意味着系统每年故障时间小于 5.3 分钟，其计算方式也很简单：系统 1 年内服务中断维护了 5 分钟，HA = 1年/(1 年+5 分钟) = 99.999% 。

故障分类

故障分类的计算方式是用故障时间乘以故障权重来计算得到的。而故障的权重通常是在故障产生以后，根据影响程度，由运营方确定的一个故障权重值。下图是故障权重的示例。

故障分=故障时间x故障权重

一般互联网应用的故障处理流程和故障时间的确定，如下图。

(流程图)

1
2
3
4
5
6

+---------------------+      +---------------------+      +---------------------+      +---------------------+      +---------------------+
|    客服报告故障     |      |  提交故障给相关     |      |                     |      |    故障处理完毕     |      |    确认故障归属     |
|        或           | ---> |  部门接口人         | ---> |   故障接手&处理     | ---> |    故障归档         | ---> |  记入绩效考核       |
| 监控系统发现故障    |      |                     |      |                     |      | (故障结束时间)      |      |                     |
| (故障开始时间)      |      +---------------------+      +---------------------+      +---------------------+      +---------------------+
+---------------------+

高可用策略

（1）负载均衡

*   HTTP 重定向负载均衡
*   DNS 负载均衡
*   反向代理负载均衡
*   IP 层负载均衡（四层负载均衡
*   数据链路层负载均衡

（2） 数据库复制与失效转移
（3） 消息队列隔离
（4）限流和降级

*   系统高可用的另一个策略是限流和降级。主要针对的是，在高并发场景下，如果系统的访问量超过了系统的承受能力，如何对系统进行保护

（5）异地多活机房架构

*   系统高可用的另一个策略是异地多活的架构。

（6）高可用运维

*   自动化测试
*   自动化监控
    *   业务指标：用户访问量、订单量、查询量
    *   技术指标：CPU、磁盘、内存
*   预发布
*   灰度发布

高可用架构

MMM 架构

企业初期使用较多的高可用架构：

一类是基于 Keepalived + VIP + MySQL 主从/双主
一类是封装好的 MMM 集群，两者本质是一样的，MMM 相比前者多了一套工具集来帮助运维。

MMM故障处理机制：

MMM 的VIP包含writer和reader两类角色，分别对应写节点和读节点。

• 当 writer节点出现故障，程序会自动移除该节点上的VIP
• 写操作切换到 Master2，并将Master2设置为writer
• 将所有Slave节点会指向Master2

除了管理双主节点，MMM 也会管理 Slave 节点，在出现宕机、复制延迟或复制错误，MMM 会移除该节点的 VIP，直到节点恢复正常。

MHA架构

MHA（Master High Availability）是一套比较成熟的 MySQL 高可用方案，也是一款优秀的故障切换和主从提升的高可用软件。

MHA为了保证 Master 的高可用，通常会部署一个 Standby（备用） 角色的 Master。在 MySQL 故障切换过程中，MHA能做到在 0~30 秒内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA 能最大程度的保证数据的一致性，以达到真正意义上的高可用。

如下图，整个 MHA 架构分为 MHA Manager 节点和 MHA Node 节点，其中 MHA Manager 节点是单点部署，MHA Node节点是部署在每个需要监控的 MySQL 集群节点上的。MHA Manager 会定时探测集群中的 Master 节点，当 Master 出现故障时，它可以自动将最新数据的 Standby Master 或 Slave 提升为新的 Master，然后将其他的 Slave 重新指向新的Master。

MHA由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。

• MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。负责检测master是否宕机、控制故障转移、检查MySQL复制状况等。
• MHA Node运行在每台MySQL服务器上，不管是Master角色，还是Slave角色，都称为Node，是被监控管理的对象节点，负责保存和复制master的二进制日志、识别差异的中继日志事件并将其差异的事件应用于其他的slave、清除中继日志。

MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master，整个故障转移过程对应用程序完全透明。

MHA故障处理机制：

• 把宕机master的binlog保存下来
• 根据binlog位置点找到最新的slave
• 用最新slave的relay log修复其它slave
• 将保存下来的binlog在最新的slave上恢复
• 将最新的slave提升为master
• 将其它slave重新指向新提升的master，并开启主从复制

MHA优点：

• 自动故障转移快
• 主库崩溃不存在数据一致性问题
• 性能优秀，支持半同步复制和异步复制
• 一个Manager监控节点可以监控多个集群

MHA Manager 和 MMM Monitor 一样，都没有 watch dog 守护进程，存在单点故障的问题。

首先是集群脑裂，当一部分应用程序 Client 和 Master 形成内部网络，而 Manager、Slave、Standby及其他 Client 端组成内部网络时。首先集群的复制状态中断，两边数据不一致。其次 Manager 会认为 Master 已经 Crash，会发起故障切换，例如将 Standby Master 提升为新主库，将 Slave 自动 change master 挂载到 Standby Master 节点下形成新的数据库集群。这时候集群一分为二，出现集群脑裂的故障。

其次是数据丢失，同样当出现 Master 和 Standby（最新的 Slave）节点所处的服务器Crash 或组成内部网时，Manager 只能提升为 Slave（数据非最新，存在延迟），并为新 Master 提供服务，这就导致数据丢失。虽然 MHA 可以使用半同步复制来保证数据安全，但是半同步复制在网络抖动时同样是会存在退化为异步复制的风险。

MHA 作者承诺最大程度上保证数据的一致性，但故障切换过程中存在数据丢失的风险。

QMHA架构

下图是去哪儿网基于分布式监控哨兵构建的 QMHA 高可用集群，它是加强版的 MHA 集群。

QMHA 集群能够满足数据一致性的需求，支持跨机房部署，但不支持多点写入。通过分布式哨兵选举投票来减少误切换，发起切换时会比对 GTID 来进行主从数据的快速比对，然后进行数据补齐。同时使用 Semi Sync 半同步复制提高数据安全性。

PXC/MGR

对于 MySQL，PXC 集群和 MGR 集群同样可以使用分布式监控哨兵进行集群高可用性守护

相比传统主从复制，PXC 集群和 MGR 集群支持多点写入，更完美得满足了高可用切换的需求，集群自身保证数据强一致性，不需要额外进行数据补齐操作。多点写入意味着切换时间更短，集群切换后恢复更快。

MySQL事务原理

事务特性

首先看看什么是事务？事务具有哪些特性？

简单来说，事务是指作为单个逻辑工作单元执行的一系列操作，这些操作要么全做，要么全不做，是一个不可分割的工作单元。

一个逻辑工作单元要成为事务，在关系型数据库管理系统中，必须满足 4 个特性。

数据库事务的特性包括原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durabilily），简称 ACID。

原子性

原子性：事务的所有操作，要么全部完成，要么全部不完成，不会结束在某个中间环节。

• 原子性：即要么改了，要么没改。也就是说用户感受不到一个正在改的状态。
• MySQL 是通过 WAL（Write Ahead Log）技术来实现这种效果的。
• 举例来讲，如果事务提交了，那改了的数据就生效了，如果此时 Buffer Pool 的脏页没有刷盘，如何来保证改了的数据生效呢？就需要使用 Redo 日志恢复出来的数据。而如果事务没有提交，且 Buffer Pool 的脏页被刷盘了，那这个本不应该存在的数据如何消失呢？就需要通过 Undo 来实现了，Undo 又是通过 Redo 来保证的，所以最终原子性的保证还是靠 Redo 的 WAL 机制实现的。

每一个写事务，都会修改 Buffer Pool，从而产生相应的 Redo 日志，这些日志信息会被记录到 ib_logfiles 文件中。因为 Redo 日志是遵循 Write Ahead Log 的方式写的，所以事务是顺序被记录的。

在 MySQL 中，任何 Buffer Pool 中的页被刷到磁盘之前，都会先写入到日志文件中，这样做有两方面的保证。

如果 Buffer Pool 中的这个页没有刷成功，此时数据库挂了，那在数据库再次启动之后，可以通过 Redo 日志将其恢复出来，以保证脏页写下去的数据不会丢失，所以必须要保证 Redo 先写。

因为 Buffer Pool 的空间是有限的，要载入新页时，需要从 LRU 链表中淘汰一些页，而这些页必须要刷盘之后，才可以重新使用，那这时的刷盘就需要保证对应的 LSN（log sequence number，日志序列号）的日志也要提前写到 ib_logfiles 中，如果没有写的话，恰巧这个事务又没有提交，数据库挂了，在数据库启动之后，这个事务就没法回滚了。

所以如果不写日志的话，这些数据对应的回滚日志可能就不存在，导致未提交的事务回滚不了，从而不能保证原子性，所以原子性就是通过 WAL（Write Ahead logging） 来保证的。

持久性

持久性：事务完成之后，事务所做的修改进行持久化保存，不会丢失。

• 所谓持久性，就是指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，接下来的操作或故障不应该对其有任何影响。
• 事务的原子性可以保证一个事务要么全执行，要么全不执行的特性，这可以从逻辑上保证用户看不到中间的状态。但持久性是如何保证的呢？
• 一旦事务提交，通过原子性，即便是遇到宕机，也可以从逻辑上将数据找回来后再次写入物理存储空间，这样就从逻辑和物理两个方面保证了数据不会丢失，即保证了数据库的持久性。

一个“提交”动作触发的操作有：binlog 落地、发送 binlog、存储引擎提交、flush_logs，check_point、事务提交标记等。这些都是数据库保证其数据完整性、持久性的手段。

1

update User set Age = Age+1 where ID = 2

图中白色框表示是在 InnoDB 内部执行的，绿色框表示是在执行器中执行的。

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存（InnoDB Buffer Pool）中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

从图中可以看出，在最后提交事务的时候，需要有3个步骤：

• 写入redo log，处于prepare状态
• 写binlog
• 修改redo log状态为commit
  • redo log的提交分为prepare和commit两个阶段，所以称之为两阶段提交 2PC

为什么需要两阶段提交？

假设当前 ID=2 的行，字段 age 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

1. 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 age 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 age 的值就是 0，与原库的值不同。
2. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 age 的值是 0。但是 binlog 里面已经记录了“把 age 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 age 的值就是 1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

崩溃恢复

如果在图中时刻 A 的地方，也就是写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash），由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：

• a. 如果是，则提交事务；
• b. 否则，回滚事务。

这里，时刻 B 发生 crash 对应的就是 2(a) 的情况，崩溃恢复过程中事务会被提交。

注：两阶段提交的最后一个阶段的操作本身是不会失败的，除非是系统或硬件错误，所以也就不再需要回滚（不然就无限循环下去了）。

隔离性

隔离性：当多个事务并发访问数据库中的同一数据时，所表现出来的相互关系。

• 隔离性，指的是一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的并发事务是隔离的。
• 锁和多版本控制就符合隔离性。

InnoDB支持的隔离性有 4 种，隔离性从低到高分别为：读未提交、读提交、可重复读、可串行化。后面详细讲。

一致性

一致性：事务开始之前和事务结束之后，数据库的完整性限制未被破坏。

• 一致性其实包括两部分内容，分别是约束一致性和数据一致性。
• 约束一致性：数据库中创建表结构时所指定的外键、唯一索引等约束，所以约束一致性就非常容易理解了。
• 数据一致性：是一个综合性的规定，或者说是一个把握全局的规定。因为它是由原子性、持久性、隔离性共同保证的结果，而不是单单依赖于某一种技术。

一致性可以归纳为数据的完整性。

根据前文可知，数据的完整性是通过其他三个特性来保证的，包括原子性、隔离性、持久性，而这三个特性，又是通过Redo/Undo来保证的，正所谓：合久必分，分久必合，三足鼎力，三分归晋，数据库也是，为了保证数据的完整性，提出来三个特性，这三个特性又是由同一个技术来实现的，所以理解Redo/Undo才能理解数据库的本质。

事务隔离

并发问题

在数据库执行中，多个并发执行的事务如果涉及到同一份数据的读写就容易出现数据不一致的情况，不一致的异常现象有以下几种。

• 脏读，是指一个事务中访问到了另外一个事务未提交的数据。
  • 例如事务 T1 中修改的数据（张三–>李四）项在尚未提交的情况下被其他事务（T2）读取到，如果 T1 进行回滚操作，则T2刚刚读取到的数据实际并不存在。

• 不可重复读，是指一个事务读取同一条记录 2 次，得到的结果不一致。
  • 例如事务 T1 第一次读取数据，接下来 T2 对其中的数据进行了更新或者删除，并且 Commit 成功。这时候 T1 再次读取这些数据，那么会得到 T2 修改后的数据，发现数据已经变更，这样 T1 在一个事务中的两次读取，返回的结果集会不一致。
    
• 幻读，是指一个事务读取 2 次，得到的记录条数不一致。
  • 例如事务 T1 查询获得一个结果集，T2 插入新的数据，T2 Commit 成功后，T1 再次执行同样的查询，此时得到的结果集记录数不同。
    
    

隔离级别

SQL 标准根据三种不一致的异常现象，将隔离性定义为四个隔离级别（Isolation Level），隔离级别和数据库的性能呈反比（安全性和执行速度），隔离级别越低，数据库性能越高；而隔离级别越高，数据库性能越差，具体如下：

按隔离水平高低排序，读未提交 < 读已提交 < 可重复度 < 串行化。

（1）Read uncommitted 读未提交

在该级别下，一个事务对数据修改的过程中，不允许另一个事务对该行数据进行修改，但允许另一个事务对该行数据进行读，不会出现更新丢失，但会出现脏读、不可重复读的情况。

它能读到一个事务的中间过程，违背了 ACID 特性，存在脏读的问题，所以基本不会用到，可以忽略。

（2）Read committed 读已提交

在该级别下，未提交的写事务不允许其他事务访问该行，不会出现脏读，但是读取数据的事务允许其他事务访问该行数据，因此会出现不可重复读的情况。

A事务正在写，不允许其他事务访问的

A事务正在读，允许其他事务写

它表示如果其他事务已经提交，那么我们就可以看到，这也是一种最普遍适用的级别。但由于一些历史原因，RC 在生产环境中用的并不多。

（3）Repeatable read 可重复读

可能出现幻读

在该级别下，在同一个事务内的查询都是和事务开始时刻一致的，保证对同一字段的多次读取结果都相同，除非数据是被本身事务自己所修改，不会出现同一事务读到两次不同数据的情况。因为没有约束其他事务的增Insert操作，所以 SQL 标准中可重复读级别会出现幻读。

A事务正在读，不允许其他事务写，但是允许事务事务读。

值得一提的是，可重复读是 MySQL InnoDB 引擎的默认隔离级别，但是在 MySQL 额外添加了间隙锁（Gap Lock），可以防止幻读。是目前被使用得最多的一种级别，在这种级别下有一定概率会发生死锁、低并发等问题。

1
2
3
4
5

show variables like 'transaction_isolation';

Variable_name          |Value            |
-----------------------+-----------------+
transaction_isolation  |REPEATABLE-READ  |

（4）Serializable 序列化

该级别要求所有事务都必须串行执行，可以避免各种并发引起的问题，效率也最低。

可串行化，这种实现方式，其实已经并不是多版本了，又回到了单版本的状态，因为它所有的实现都是通过锁来实现的。

对不同隔离级别的解释，其实是为了保持数据库事务中的隔离性（Isolation），目标是使并发事务的执行效果与串行一致，隔离级别的提升带来的是并发能力的下降，两者是负相关的关系。

并发事务控制

• 单版本控制-锁

先来看锁，锁用独占的方式来保证在只有一个版本的情况下事务之间相互隔离，所以锁可以理解为单版本控制。

在 MySQL 事务中，锁的实现与隔离级别有关系，在 RR（Repeatable Read）隔离级别下，MySQL 为了解决幻读的问题，以牺牲并行度为代价，通过 Gap 锁来防止数据的写入，而这种锁，因为其并行度不够，冲突很多，经常会引起死锁。

现在流行的 Row 模式可以避免很多冲突甚至死锁问题，所以推荐默认使用 Row + RC（Read Committed）模式的隔离级别，可以很大程度上提高数据库的读写并行度。

• 多版本控制-MVCC

多版本控制也叫作 MVCC，是指在数据库中，为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。

那个多版本是如何生成的呢？每一次对数据库的修改，都会在 Undo 日志中记录当前修改记录的事务号及修改前数据状态的存储地址（即 ROLL_PTR），以便在必要的时候可以回滚到老的数据版本。例如，一个读事务查询到当前记录，而最新的事务还未提交，根据原子性，读事务看不到最新数据，但可以去回滚段中找到老版本的数据，这样就生成了多个版本。

多版本控制很巧妙地将稀缺资源的独占互斥转换为并发，大大提高了数据库的吞吐量及读写性能。

锁机制

锁分类

在 MySQL 中有三种级别的锁：页(Page)级锁、表级锁、行级锁。

1. 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。会发生在：MyISAM、memory、InnoDB、BDB 等存储引擎中。
2. 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。会发生在：InnoDB 存储引擎。
3. 页级锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。会发生在：BDB 存储引擎。

三种级别的锁分别对应存储引擎关系如下图所示。

在InnoDB 存储引擎中，锁分为行锁和表锁，其中行锁包括两种锁：

• 共享锁（S）：读锁，允许持有锁的事务读取行，多个事务可以一起读，共享锁之间不互斥，共享锁会阻塞排它锁。
  • 事务A获得共享锁，事务B可以同时获得共享锁 from Table where col1 = 1
  • 事务A获得共享锁，事务B如果是个写操作，阻塞等待锁
• 排他锁（X）：写锁，允许获得排他锁的事务更新或者删除数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
  • 如果要对某一行进行写操作，首先要先获得排它锁
  • 如果对id=5这一行获得X锁，此时自该行无法加X锁和S锁

如果事务T1在r行上持有共享（S）锁，则来自某些不同事务T2对行r上锁的请求将按以下方式处理：

1. T2可以立即批准S 锁的请求。因此，T1 和 T2 在 r 上都持有 S 锁。
2. T2 对 X 锁的请求无法立即批准。

如果事务T1 在行 r 上持有排他性（X）锁，则无法立即批准来自某个不同事务T2 对 r 上任一类型锁的请求。相反，事务T2 必须等待事务T1 释放其对行 r 的锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。表锁又分为三种：

• 意向共享锁（IS）：事务计划给数据行加行共享锁（S），事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务计划给数据行加行排他锁（X），事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
• 自增锁（AUTO-INC Locks）：特殊表锁，自增长计数器通过该“锁”来获得子增长计数器最大的计数值。

InnoDB 锁关系矩阵如下图所示，其中：+ 表示兼容，- 表示不兼容。

从操作的性能可分为乐观锁和悲观锁。

1. 乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。
2. 悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。
3. 共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

自增锁

在MySQL InnoDB 存储引擎中，我们在设计表结构的时候，通常会建议添加一列作为自增主键。这里就会涉及一个特殊的锁：自增锁（即：AUTO-INC Locks），它属于表锁的一种，在 INSERT 结束后立即释放。我们可以执行 show engine innodb status 来查看自增锁的状态信息。

理解自增锁是一个接口，实现的形式有多种。

在自增锁的使用过程中，有一个核心参数，需要关注，即 innodb_autoinc_lock_mode,它有0、1、2三个值。保持默认值就行。具体的含义可以参考官方文档，这里不再赘述，如下图所示。

1
2
3
4
5
6

show variables like '%innodb_autoinc_lock_mode%';

Name            |Value                    |
----------------+-------------------------+
Variable_name   |innodb_autoinc_lock_mode |
Value           |2                        |

0传统模式：

                        +-----------------------------------------+
                        |                 执行中                  |
                        |                                         |
                        |   +---------------------------------+   |
+----------------+      |   |      分配 AUTO_INCREMENT        |   |
|   INSERT       |      |   +---------------------------------+   |
|   语句 4       |      |                    |                    |
+----------------+      |                    v                    |
|   INSERT       |      |   +---------------------------------+   |
|   语句 3       |      |   |           INSERT 语句 1         |   |
+----------------+      |   +---------------------------------+   |
|   INSERT       | ---->|                                         |
|   语句 2       |      +-----------------------------------------+
+----------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

以下是为您提取的图片文字内容，以及用纯文本（ASCII字符）重新绘制的下方流程图：

1-连续模式：

连续模式（Consecutive）是 MySQL 8.0 之前默认的模式，之所以提出这种模式，是因为传统模式存在影响性能的弊端，所以才有了连续模式。

比如执行能够确定插入条数的Insert语句时，不会使用自增锁，会直接将Insert语句锁需要的自增值预留出来即可，就可以继续执行下一个语句了。

在实际分配ID的过程中个，InnoDB会使用轻量级的Mutex锁，来放置ID重复分配，ID分配完，Mutex锁自动释放。

但是如果Insert语句不能确认插入的数量，还是需要获得自增锁。INSERT INTO ...SELECT....

交叉模式：

交叉模式（Interleaved）下，所有的 INSERT 语句，包含 INSERT 和 INSERT INTO ... SELECT ，都不会使用 AUTO-INC 自增锁，而是使用较为轻量的 `mutex` 锁。这样一来，多条 INSERT 语句可以并发的执行，这也是三种锁模式中扩展性最好的一种。

**交叉模式流程图（纯文本重绘）：**

```text
                                         +-----------------------------------------------------------------+
                                         |                             执行中                              |
                                         |                                                                 |
                                         |            +-----------------------------------------+          |
                                         |            |          分配 AUTO_INCEMENT             |          |
                                         |            +-----------------------------------------+          |
                                         |                 |                    |                    |          |
                                         |                 |                    |                    |          |
                                         |                 v                    v                    v          |
                                         |      +-------------------+  +-------------------+  +-------------------+ 
                                         |      |    INSERT         |  |    INSERT         |  |    INSERT         |
                                         |      |    语句 1         |  |    语句 2         |  |    语句 3         |
                                         |      +-------------------+  +-------------------+  +-------------------+
                                         |                                                                 |
+----------------+                       |                                                                 |
|    INSERT      |                       |                                                                 |
|    语句 4      |                       |                                                                 |
+----------------+                       |                                                                 |
|    INSERT      |                       |                                                                 |
|    语句 3      |                       |                                                                 |
+----------------+                       |                                                                 |
|    INSERT      |                       |                                                                 |
|    语句 2      | --------------------->|                                                                 |
+----------------+                       +-----------------------------------------------------------------+

副作用就是单个Insert的自增值有可能是不连续的，因为AUTO_INCREMENT的值会在多个INSERT语句中来回复交叉执行。

优点：效率高

缺点：在并发情况下无法保持数据的一致性

Binlog: Statement、Row、Mixed

如果采用的是Statement格式，同步的SQL语句，并且有采用了交叉模式，数据不一致问题。

InnoDB 行锁

InnoDB行锁是通过对索引数据页上的记录（record）加锁实现的，主要实现算法有 3 种：

• Record Lock：单个行记录的锁（锁数据，不锁 Gap）。（记录锁，RC、RR隔离级别都支持）
• Gap Lock：间隙锁，锁定一个范围，不包括记录本身（不锁数据，仅仅锁数据前面的Gap）。（范围锁，RR隔离级别支持）
• Next-key Lock：同时锁住数据，并且锁住数据前面的 Gap。（记录锁+范围锁，RR隔离级别支持）

第一种情况：主键 + RR

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

id =10 name=zs
id=10 name=ls

+-----------------------------------------------------------+
| Table: T1(id primary key, name)                           |
|                                                           |
| Primary Key                     X锁                       |
|                                  |                        |
|                                  v                        |
|     +---------------------------------------------+       |
|     |  id  |  1  |  4  |  7  |  10 |  20 |  30 |   |      |
|     +---------------------------------------------+       |
|     | name |  a  |  c  |  b  |  a  |  d  |  b  |   |      |
|     +---------------------------------------------+       |
|                                                           |
+-----------------------------------------------------------+

假设条件是：

• update t1 set name=’XX’ where id=10
• id 为主键索引。

加锁行为：仅在 id=10 的主键索引记录上加X锁。

秒杀，扣减场景 SKU -1
N个请求同时编辑同一个数据库记录
解决：
CDN Nginx
限流 熔断、降级
缓存
分片

以下是图中内容的纯文本提取：

第二种情况：唯一键 + RR

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

+-----------------------------------------------------------------------+
|                                                                       |
| Table: T1(name primary key, id unique key)                            |
|                                                                       |
|                                                                       |
| +-------------------+                           X锁                   |
| | Unique Key (id)   |                            |                    |
| +-------------------+                            v                    |
|                                                                       |
|          +--------------------------------------------+               |
|          | id   | 1  | 2  | 3  | 5  | 6  | 10 (X锁)  |               |
|          +------+----+----+----+----+----+-----------|               |
|          | name | f  | zz | b  | a  | c  | d  (X锁)  |               |
|          +--------------------------------------------+               |
|                                     |                                 |
|                                     |                                 |
| +-------------------+               |  X锁                             |
| | Primary Key       |               |  |                               |
| +-------------------+               v  v                               |
|                                                                       |
|          +--------------------------------------------+               |
|          | name | a  | b  | c  | d (X锁) | f  | zz   |               |
|          +------+----+----+----+---------+----+------|               |
|          | id   | 5  | 3  | 6  | 10 (X锁)| 1  | 2    |               |
|          +--------------------------------------------+               |
|                                                                       |
+-----------------------------------------------------------------------+

假设条件是：

• update t1 set name=’XX’ where id=10。
• id 为唯一索引。

加锁行为：

• 先在唯一索引 id 上加 id=10 的 X 锁。
• 再在 id=10 的主键索引记录上加 X 锁。

第三种情况：非唯一键 + RR

假设条件是：

• update t1 set name=’XX’ where id=10。
• id 为非唯一索引。

加锁行为：

• 先通过 id=10 在 key(id) 上定位到第一个满足的记录，对该记录加 X 锁，而且要在 (6,c)~(10,b) 之间加上 Gap lock，为了防止幻读。然后在主键索引 name 上加对应记录的X 锁；
• 再通过 id=10 在 key(id) 上定位到第二个满足的记录，对该记录加 X 锁，而且要在(10,b)~(10,d)之间加上 Gap lock，为了防止幻读。然后在主键索引 name 上加对应记录的X 锁；
• 最后直到 id=11 发现没有满足的记录，此时不需要加 X 锁，但要再加一个 Gap lock：(10,d)~(11,f)。

第四种情况：无索引 + RR

假设条件是：

• update t1 set name=’XX’ where id=10。
• id 列无索引。

加锁行为：

• 表里所有行和间隙均加 X 锁。

这样加锁就会很多

因此尽可能通过主键进行加锁，减少加锁数量

InnoDB死锁

在 MySQL 中死锁不会发生在 MyISAM 存储引擎中，但会发生在 InnoDB 存储引擎中，因为 InnoDB 是逐行加锁的，极容易产生死锁。那么死锁产生的四个条件是什么呢？

1. 互斥条件：一个资源每次只能被一个进程使用；
2. 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；
3. 不剥夺条件：进程已获得的资源，在没使用完之前，不能强行剥夺；
4. 循环等待条件：多个进程之间形成的一种互相循环等待资源的关系。

在发生死锁时，InnoDB 存储引擎会自动检测，并且会自动回滚代价较小的事务来解决死锁问题。但很多时候一旦发生死锁，InnoDB 存储引擎的处理的效率是很低下的或者有时候根本解决不了问题，需要人为手动去解决。

既然死锁问题会导致严重的后果，那么在开发或者使用数据库的过程中，如何避免死锁的产生呢？这里给出一些建议：

• 加锁顺序一致；
• 尽量基于 primary 或 unique key 更新数据。
• 单次操作数据量不宜过多，涉及表尽量少。
• 减少表上索引，减少锁定资源。
• 相关工具：pt-deadlock-logger。

https://www.percona.com/doc/percona-toolkit/3.0/pt-deadlock-logger.html

eg：

面条
一根筷子
一个刀子

牛排
一根筷子
一个叉子

以下是为您提取并整理的图中文字内容：

表级锁死锁

产生原因：
用户A访问表A（锁住了表A），然后又访问表B；另一个用户B访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

Session 1 –> A表（表锁） –> B表（表锁）
Session 2 –> B表（表锁） –> A表（表锁）

解决方案：
这种死锁比较不常见，是由于程序设计不合理或程序Bug产生的，除了调整的程序的逻辑没有其它的办法。

仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理，必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

行级锁死锁

产生原因1：
如果在事务中执行了一条没有索引条件的查询，引发全表扫描（update User set age=20 where name=’ShangJun’），把行级锁上升为全表记录锁定（等价于表级锁，表中的每个行记录都要加排它锁，行与行之间都加gap lock），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。

解决方案1：
SQL语句中不要使用太复杂的关联多表的查询。

使用Explain对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化。

尽可能通过主键索引或唯一索引作为编辑数据的条件。

产生原因2：
两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。

1
2
3
4
5
6
7
8

     T1                                T2
     |                                 |
     |                                 |
[ 锁 id=1 ]  <------- 等待释放 ------->  [ 锁 id=2 ]
     ^                                 ^
     |                                 |
     |                                 |
[ 锁 id=2 ]  <------- 等待释放 ------->  [ 锁 id=1 ]

Session1 / Session2 代码示例：

1
2
3
4
5
6
7
8
9

Session1
begin;
update t1 set c1=1 where id=1;
update t1 set c1=2 where id=2;

Session2
begin;
update t1 set c1=2 where id=2;
update t1 set c1=1 where id=1;

解决方案2：

• 在同一个事务中，尽可能做到一次锁定所需要的所有资源
• 按照id对资源排序，然后按顺序进行处理

资源争用死锁

下面分享一个基于资源争用导致死锁的情况，如下图所示。

死锁情况一

同上面情况2

Table: T1(id primary key, name)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

session 1
begin;
select * from t1 where id = 1 for update;

update t1 set name=' qqq' where id = 5;

死锁发生！！！


session 2
begin;

delete from t1 where id = 5;

delete from t1 where id = 1;

元数据锁导致死锁

下面分享一个 Metadata lock（即元数据锁）导致的死锁的情况，如下图所示。

session1 首先拿到 id=1 的锁，session2 同期拿到了 id=5 的锁后，两者分别想拿到对方持有的锁，于是产生死锁。

session1 和 session2 都在抢占 id=1 和 id=6 的元数据的资源，产生死锁。

查看 MySQL 数据库中死锁的相关信息，可以执行 show engine innodb status\G 来进行查看，重点关注 “LATEST DETECTED DEADLOCK” 部分。

给大家一些开发建议来避免线上业务因死锁造成的不必要的影响。

• 更新 SQL 的 where 条件时尽量用索引；
• 加锁索引准确，缩小锁定范围；
• 减少范围更新，尤其不建议非主键/非唯一索引上的范围更新。
• 控制事务大小，减少锁定数据量和锁定时间长度（innodb_row_lock_time_avg）。
• 加锁顺序一致，尽可能一次性锁定所有所需的数据行。

以下是为您提取的图中文字内容，已按照两图的内容进行合并整理：

死锁排查

MySQL提供了几个与锁有关的参数和命令，可以辅助我们优化锁操作，减少死锁发生。

（1）查看死锁日志

通过 show engine innodb status 命令查看近期死锁日志信息。

使用方法：

①、查看近期死锁日志信息；
②、使用explain查看下SQL执行计划

（2）查看死锁信息

1
2
3
4
5
6
7
8

/* 查询死锁表和事务 */
select * from performance_schema.data_locks;

/* 查询等待锁的事务 */
select * from performance_schema.data_lock_waits;

/* 查询是否锁表 */
SHOW OPEN TABLES where In_use > 0;

（3）解除死锁

如果需要解除死锁，有一种最简单粗暴的方式，那就是找到进程id之后，直接干掉。

查看当前正在进行中的进程

1
2
3
4

show processlist

/* 也可以使用 */
SELECT * FROM information_schema.INNODB_TRX;

这两个命令找出来的进程id 是同一个。

杀掉进程对应的进程 id

1

kill id

验证（kill后再看是否还有锁）

1

SHOW OPEN TABLES where In_use > 0;

MVCC

以下是为您提取的图中文字内容，已整合并按纯文本形式展示：

Multi-Version Concurrency Control 多版本并发控制。MySQL InnoDB 存储引擎，实现的是基于多版本的并发控制协议——MVCC，而不是基于锁的并发控制。

MVCC 最大的好处是读不加锁，读写不冲突。在读多写少的 OLTP（On-Line Transaction Processing）应用中，读写不冲突是非常重要的，极大的提高了系统的并发性能，这也是为什么现阶段几乎所有的 RDBMS（Relational Database Management System），都支持 MVCC 的原因。

常规的服务分类：

• 读服务：电商搜索、查看订单，缓存、索引库、数据库
• 写服务：先插入缓存系统然后再异步写入到数据，直接写入数据库
• 扣减服务：update，秒杀服务

5.6.1. 快照读与当前读

在 MVCC 并发控制中，读操作可以分为两类: 快照读（Snapshot Read）与当前读 （Current Read）。

• 快照读：读取的是记录的可见版本（有可能是历史版本），不用加锁。
• 当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加锁，保证其他事务不会再并发修改这条记录。

注意：MVCC 只在 Read Commited 和 Repeatable Read 两种隔离级别下工作。

如何区分快照读和当前读呢？ 可以简单的理解为：

• 快照读：简单的 select 操作，属于快照读，不需要加锁。
• 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

假设 F1～F6 是表中字段的名字，1～6 是其对应的数据。后面三个隐含字段分别对应该行的隐含ID、事务号和回滚指针，如下图所示。

1
2
3
4
5
6
7
8
9
10
11
12
13

+----+----+----+----+----+----+------------+------------+-------------+
| F1 | F2 | F3 | F4 | F5 | F6 | DB_ROW_ID  | DB_TRX_ID  | DB_ROLL_PT  |
+----+----+----+----+----+----+------------+------------+-------------+
| 1  | 2  | 3  | 4  | 5  | 6  |            |            |             |
+----+----+----+----+----+----+------------+------------+-------------+
  |_________________________|
              |
            DATA
                               |__________________________|
                                             |
                                         隐含ID
                                         事务ID
                                         回滚指针

• 隐含 ID（DB_ROW_ID），6 个字节，当由 InnoDB 自动产生聚集索引时，聚集索引包括这个 DB_ROW_ID 的值。
• 事务号（DB_TRX_ID），6 个字节，标记了最新更新这条行记录的 Transaction ID，每处理一个事务，其值自动 +1。
• 回滚指针（DB_ROLL_PT），7 个字节，指向当前记录项的 Rollback Segment 的 Undo log记录，通过这个指针才能查找之前版本的数据。

具体的更新过程，简单描述如下：

首先，假如这条数据是刚 INSERT 的，可以认为 ID 为 1，其他两个字段为空。

然后，当事务 1 更改该行的数据值时，会进行如下操作，如下图所示。

• 用排他锁锁定该行；记录 Redo log；
• 把该行修改前的值复制到 Undo log，即图中下面的行；
• 修改当前行的值，填写事务编号，使回滚指针指向 Undo log 中修改前的行。

接下来，与事务 1 相同，此时 Undo log 中有两行记录，并且通过回滚指针连在一起。因此，如果 Undo log 一直不删除，则会通过当前记录的回滚指针回溯到该行创建时的初始内容，所幸的是在 InnoDB 中存在 purge 线程，它会查询那些比现在最老的活动事务还早的 Undo log，并删除它们，从而保证 Undo log 文件不会无限增长，如下图所示。

存储引擎概述

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎，MySql的核心就是插件式存储引擎。

Mysql中不同的表可以指定不同的存储引擎，也就是说一套Mysql服务器可以同时使用N种不同的存储引擎。

InnoDB 事务型数据库的首选，支持事务安全表（ACID），支持行锁定和外键。

MySQL 5.5.5 之后，InnoDB 作为默认存储引擎。

1
2
3
4

/** 查看系统所支持的引擎类型 */
SHOW ENGINES;

SELECT * FROM INFORMATION_SCHEMA.ENGINES;

不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。

MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一

InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。

Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

使用下面的语句可以修改数据库临时的默认存储引擎

1

SET default_storage_engine='存储引擎名'

InnoDB

简介

InnoDB是一款通用存储引擎，平衡了高可靠性和高性能。

• 高可靠性：任何时候可以保证数据是不丢失的
• 高性能：数据读取（查询、检索）和数据变更效率高、RT短（Response Time，服务响应时间）
• 通常性能和可靠性是相悖的，磁盘的效率是远远低于内存的。

MySQL 8.0中，InnoDB是默认的MySQL存储引擎。除非配置了不同的默认存储引擎，否则在没有ENGINE子句的情况下发布CREATE TABLE语句会创建InnoDB表。

主要优势：

• 其DML操作遵循ACID模型（事务模型），其事务具有提交、回滚和崩溃恢复功能（InnoDB redo、undo日志），以保护用户数据。
• 行级锁定和甲骨文风格的一致读取提高了多用户并发性和性能。
• InnoDB表在磁盘上排列数据，以根据主键优化查询。每个InnoDB表都有一个名为聚类索引的主键索引，该索引组织数据以最小化主键查找的I/O。
• 为了保持数据完整性，InnoDB支持FOREIGN KEY约束。使用外键，会检查插入、更新和删除，以确保它们不会导致相关表之间的不一致。

架构

内存

buffer pool

Buffer Pool，中文名：缓冲池。

在使用MySQL进行查询时，具体查询数据其实是在存储引擎中实现的，MySQL数据是存储于磁盘里，如果每次查询都直接从磁盘里面查询，这样势必会很影响性能（从磁盘进行数据的检索/提取效率是最低的，最佳方式是从内存中提取），所以一定是先把数据从磁盘中取出，然后放在内存中，下次查询直接从内存中去查询。

缓冲池与查询缓存的对比：

查询缓存：在服务层，存储的数据是查询的结果集。

缓冲池：在存储引擎层，缓冲的数据其实是磁盘上的数据信息，就可以在缓冲池进行数据的查询。将磁盘中的部分热点数据页都给放入到内存某一个区域中（缓冲池），从内存中进行检索，所以使用缓冲池可以大大提升查询效率。

Buffer Pool是MySQL或者说InnoDB中，十分重要、非常核心的一部分，位于主内存。

缓冲池是主内存中的一个区域，InnoDB在访问时缓存表和索引数据。缓冲池允许直接从内存访问常用数据，从而加快处理速度。在专用服务器上，高达80%的物理内存通常分配给缓冲池（官方建议）。

缓冲池不可能缓存所有的数据，如果缓冲池中没有要检索的数据也怎么办？缓冲池是有大小限制的，满了怎么办？

• 会将要检索数据对应的数据页都给加载到缓冲池中
• 缓冲池针对LRU算法进行了优化，淘汰算法

在Inno DB中，数据的访问是按照数据页（默认情况下，数据页的大小是 16kb）的方式从数据文件中读取到 Buffer Pool 中，所以对应的，在 Buffer Pool 中，也是以数据页为数据单位，存放着很多数据。但是我们通常叫做缓存页。

磁盘中的数据也在内存中用同样大小的内存空间做一个映射。为了提高访问速度MySQL 预先就分配许多这样的空间，为的就是与MySQL数据文件中的页做交换，来把数据文件中的页放到事先准备好的内存中。

怎么识别数据在哪个缓存页中

每个缓存页都会对应着一个描述数据块，里面包含数据页所属的表空间、数据页的编号，缓存页在 Buffer Pool 中的地址等等。

描述数据块本身也是一块数据，它的大小大概是缓存页大小的5%左右，大概800个字节左右的大小。假设你设置的buffer pool大小是128MB，实际上Buffer Pool真正的最终大小会超出一些，可能有个130多MB的样子，因为还要存放每个缓存页的描述数据。

在Buffer Pool中，每个缓存页的描述数据放在最前面，然后各个缓存页放在后面。

InnoDB会维护一个哈希表数据结构，它使用表空间号+数据页号，作为一个key，然后缓冲页对应的控制块作为value。

(表格：数据页缓存的Hash表)

• 当需要访问某个页的数据时，先从哈希表中根据表空间号+页号看看是否存在对应的缓冲页。
• 如果有，则直接使用；如果没有，就从free链表中选出一个空闲的缓冲页，然后把磁盘中对应的页加载到该缓冲页的位置

InnoDB使用了链表来组织页和页中存储的数据，页与页之间形成了双向链表，这样可以方便的从当前页跳到下一页，同时使用LRU（Least Recently Used）算法去淘汰那些不经常使用的数据。
(注释：内存空间不连续，但是每个节点都是指向下一个节点和上一个节点)

(图示：页与页之间通过双向链表连接，页内部有上一页指针、下一页指针，以及User Records)

+-----------------------------+          +-----------------------------+
|             页              |          |             页              |
|                             |          |                             |
|  +-----------+-----------+  |          |  +-----------+-----------+  |
|  | 上一页指针 | 下一页指针 |  | -------> |  | 上一页指针 | 下一页指针 |  |
|  +-----------+-----------+  | <------- |  +-----------+-----------+  |
|                             |          |                             |
|  +-----------------------+  |          |  +-----------------------+  |
|  |                       |  |          |  |                       |  |
|  |      User Records     |  |          |  |      User Records     |  |
|  |                       |  |          |  |                       |  |
|  +-----------------------+  |          |  +-----------------------+  |
|                             |          |                             |
+-----------------------------+          +-----------------------------+

同时，每页中的一行行数据是通过单向链表进行链接。因为这些数据是分散到Buffer Pool中的，单向链表将这些分散的内存给连接了起来。

(图示：页内部的User Records通过单向链表连接1 -> 2 -> 3)

+-----------------------------+
|             页              |
|                             |
|  +-----------+-----------+  |
|  | 上一页指针 | 下一页指针 |  |
|  +-----------+-----------+  |
|                             |
|  +-----------------------+  |
|  |      User Records     |  |
|  |                       |  |
|  |   +-----------+       |  |
|  |   |     1     |       |  |
|  |   +-----------+       |  |
|  |         |             |  |
|  |         v             |  |
|  |   +-----------+       |  |
|  |   |     2     |       |  |
|  |   +-----------+       |  |
|  |         |             |  |
|  |         v             |  |
|  |   +-----------+       |  |
|  |   |     3     |       |  |
|  |   +-----------+       |  |
|  |         |             |  |
|  |         v             |  |
|  |   +-----------+       |  |
|  |   |    ...    |       |  |
|  |   +-----------+       |  |
|  +-----------------------+  |
|                             |
+-----------------------------+

那 InnoDB 为什么要这么设计？

假设我们没有页这个概念，那么当我们查询时，成千上万的数据要如何做到快速的查询出结果？

众所周知，MySQL 的性能是不错的，而如果没有页，我们剩下的只能是逐条逐条的遍历数据了。

那页是如何做到快速查询的呢？

在当前页中，可以通过 User Records 中的连接每条记录的单链表来进行遍历，如果在当前页中没有找到，则可以通过下一页指针快速的跳到下一页进行查询。

有人可能会说了，你在 User Records 中还不是通过遍历来解决的，你就是简单的把数据分了个组而已。如果我的数据根本不在当前这个页中，那我难道还是得把之前的页中的每一条数据全部遍历完？这效率也太低了。

当然，MySQL 也考虑到了这个问题，所以实际上在页中还存在一块区域叫做 The Infimum and Supremum Records，代表了当前页中最大和最小的记录（在一个页中所有的记录默认都是连续的，比如1-100）。

（图示 1）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

      +-------+                               +--------+
      |   1   |                               |  100   |
      +-------+                               +--------+
          ^                                       ^
          |                                       |
+---------------------------------------------------------+
|                            页                           |
|  +-------------+  +-------------+                       |
|  |  最小记录   |  |  最大记录   |                       |
|  +-------------+  +-------------+                       |
|                                                         |
|  +-------------+  +-------------+                       |
|  | 上一页指针  |  | 下一页指针  |                       |
|  +-------------+  +-------------+                       |
|                                                         |
|  +---------------------------------------------------+  |
|  |                                                   |  |
|  |                    User Records                   |  |
|  |                   （也就是行数据）                |  |
|  |                                                   |  |
|  +---------------------------------------------------+  |
|                                                         |
+---------------------------------------------------------+

有了 Infimum Record 和 Supremum Record，现在查询不需要将某一页的 User Records 全部遍历完，只需要将这两个记录和待查询的目标记录进行比较。比如我要查询的数据 id = 101，那很明显不在当前页。接下来就可以通过下一页指针跳到下页进行检索。

使用Page Directory（Page目录）：

问题： User Records 中是单链表，那么即使我知道我要找的数据在当前页，那最坏的情况下，也得挨个挨个的遍历100次才能找到想要的数据。你管这也叫效率高？

答案：为了解决这个问题，MySQL 又在页中加入了另一个区域 Page Directory 。

（图示 2）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

+-----------------------+
|      假设存储了       |
|      1-100的数据      |
+-----------------------+

 +---------------------------+          +----------------+
 |            页             |          |       1        |
 |                           |          +----------------+
 |  +---------------------+  |          |       7        |
 |  |    Page Directory   |--|--------->+----------------+
 |  +---------------------+  |          |       13       |
 |                           |          +----------------+
 |  +---------+ +---------+  |          |       .        |
 |  | 最小记录| | 最大记录|  |          |       .        |
 |  +---------+ +---------+  |          |       .        |
 |                           |          +----------------+
 |  +---------+ +---------+  |
 |  |上一页指针| |下一页指针|  |
 |  +---------+ +---------+  |
 |                           |
 |  +---------------------+  |
 |  |                     |  |
 |  |     User Records    |  |
 |  |    （也就是行数据） |  |
 |  |                     |  |
 |  +---------------------+  |
 |                           |
 +---------------------------+

顾名思义，Page Directory 是个目录，里面有很多个槽位（Slots），每一个槽位都指向了一条 User Records 中的记录。大家可以看到，每隔几条数据，就会创建一个槽位。图中给出的数据是非常严格按照其设定来的，在一个完整的页中，每隔6条数据就会有一个 Slot。

Page Directory 的设计不知道有没有让你想起另一个数据结构——跳表，只不过这里只抽象了一层索引。

MySQL 会在新增数据的时候就将对应的 Slot 创建好，有了 Page Directory ，就可以对一张页的数据进行粗略的二分查找。至于为什么是粗略，毕竟 Page Directory 中不是完整的数据，二分查找出来的结果只能是个大概的位置，找到了这个大概的位置之后，还需要回到 User Records 中继续的进行挨个遍历匹配。

查找过程

淘汰策略（算法）- LRU

• LRU：Least Recently Userd，最近最少使用
• FIFO：先进先出置换算法
• LFU：最少使用置换算法，移位寄存器（用来记录页被访问的频率）
• OPT：最佳置换算法

Buffer Pool的LRU算法是如何实现将最近没有使用过的数据给过期的。

传统的LRU：

核心思想：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰。

① 页已经在缓冲池里：那就只做移至LRU头部的动作，而没有页被淘汰

（图示1）

1
2
3
4

                          head                                          tail
                           |                                             |
                           v                                             v
[ LRU ] -> [ 1 ] -> [ 3 ] -> [ 5 ] -> [ 4 ] -> [ 2 ] -> [ 10 ] -> [ 30 ] -> [ 20 ] -> [ 40 ] -> [ 7 ]

假如管理缓冲池的LRU长度为10，缓冲了页号为1，3，5…，40，7的页。
假如，接下来要访问的数据在页号为4的页中：

（图示2）

1
2
3
4

                          head                                          tail
                           |                                             |
                           v                                             v
[ LRU ] -> [ 4 ] -> [ 1 ] -> [ 3 ] -> [ 5 ] -> [ 2 ] -> [ 10 ] -> [ 30 ] -> [ 20 ] -> [ 40 ] -> [ 7 ]

1. 页号为4的页，本来就在缓冲池里。
2. 把页号为4的页，放到LRU的头部即可，没有页被淘汰。

② 页不在缓冲池里：除了做放入LRU头部的动作，还要做淘汰LRU尾部页的动作。

假如，再接下来要访问的数据在页号为50的页中：

（图示3）

1
2
3
4

                          head                                          tail
                           |                                             |
                           v                                             v
[ LRU ] -> [ 50 ] -> [ 4 ] -> [ 1 ] -> [ 3 ] -> [ 5 ] -> [ 2 ] -> [ 10 ] -> [ 30 ] -> [ 20 ] -> [ 40 ] -> [ 7 ]

1. 页号为50的页，原来不在缓冲池里；
2. 把页号为50的页，放到LRU头部，同时淘汰尾部页号为7的页；

传统LRU对于MySQL的劣势：

1. 预读失效

   由于预读（Read-Ahead），提前把页放入了缓冲池，但最终MySQL并没有从页中读取数据，称为预读失效。

• 什么是预读
  磁盘读写，并不是按需读取，而是按页读取，一次至少读一页数据(16K)，如果未来要读取的数据就在页中，就能够省去后续的磁盘IO，提高效率。
• 为什么预读
  数据访问，通常都遵循“集中读写”的原则，使用一些数据，大概率会使用附近的数据，这就是所谓的“局部性原理”，它表明提前加载是有效的，确实能够减少磁盘IO。
• 按页读取，和InnoDB的缓冲池设计有啥关系？
  磁盘访问按页读取能够提高性能，所以缓冲池一般也是按页缓存数据。
  预读机制启示了我们，能把一些“可能要访问”的页提前加入缓冲池，避免未来的磁盘IO操作。

2. MySQL缓冲池污染

   为什么呢？

   因为实际生产环境中会存在全表扫描的情况（大部分时候我们希望不做全表扫描的，依赖索引），如果数据量较大，可能会将Buffer Pool中存下来的热点数据给全部替换出去，而这样就会导致该段时间MySQL性能断崖式下跌。

   对于这种情况，MySQL有一个专用名词叫缓冲池污染。所以MySQL对LRU算法做了优化。

预读失效优化

Buffer Pool预读机制:

预读是mysql提高性能的一个重要的特性。预读就是 IO 异步读取多个页数据读入 Buffer Pool 的一个过程，并且这些页被认为很快就会被读取到的。InnoDB使用两种预读算法来提高I/O性能：线性预读（Linear Read-Ahead）和随机预读（Random Read-Ahead）

为了区分这两种预读的方式，我们可以把线性预读放到以extent为单位，而随机预读放到以extent中的page为单位。线性预读着眼于将下一个extent提前读取到buffer pool中，而随机预读着眼于将当前extent中的剩余的page提前读取到buffer pool中。

（1）Linear线性预读

线性预读的单位是extend，一个extend中有64个page。线性预读的一个重要参数是innodb_read_ahead_threshold，是指在连续访问多少个页面之后，把下一个extend读入到buffer pool中，不过预读是一个异步的操作。当然这个参数不能超过64，因为一个extend最多只有64个页面。

MySQL InnoDB逻辑存储结构:

所有数据都会被逻辑地存储在空间中，成为表空间。表空间对应的物理结构就一个在磁盘上一个个的文件，日志、数据等等。

表空间的逻辑组成：段（Segment）、区（extent）、页（Page）组成。页也有称为块（Block）。

表空间是有各个段组成，常见段有数据段、索引段、回滚段。

区是由连续的页组成的，在任何情况下每个区的大小都是1MB，为了保证页连续性，InnoDB每次从磁盘上一次申请4-5区。页大小16K，一个区有多少个页？ （1MB/16K = 64）

例如，innodb_read_ahead_threshold = 56，就是指在连续访问了一个extend的56个页面之后把下一个extend读入到buffer pool中。在添加此参数之前，InnoDB仅计算当它在当前范围的最后一页中读取时是否为整个下一个范围发出异步预取请求。

1

show variables like '%innodb_read_ahead_threshold%';

（2）Random随机预读

随机预读方式则是表示当同一个extent中的一些page在buffer pool中发现时，Innodb会将该extent中的剩余page一并读到buffer pool中。由于随机预读方式给innodb code带来了一些不必要的复杂性，同时在性能也存在不稳定性，在5.5中已经将这种预读方式废弃，默认是OFF。若要启用此功能，即将配置变量设置innodb_random_read_ahead为ON（不建议启用）。

1

SET GLOBAL innodb_random_read_ahead='ON';

LRU具体结构

该算法将常用的Page页面保留在新生代（New Sublist）中。老生代（Old Sublist）包含较少使用的Page页面；Old Sublist中的Page页面，会在后续Buffer Pool剩余空间不足、或者有新的页加入时被移除掉。

该链表存储的数据来源有两部分，分别是：

• MySQL的预读线程预先加载的数据。
• 用户的操作，例如Query查询。

预读失效的处理：

要优化预读失效，思路是：

（1）让预读失败的页（通过预读加载到缓冲池的数据页没有被检索），停留在缓冲池LRU里的时间尽可能短。

（2）让真正被读取的页，才挪到缓冲池LRU的头部（新生代的头部New SubList的头部）

默认情况下，由用户操作影响而进入到Buffer Pool中的数据，会被立即放到链表的最前端（New SubList），也就是New Sublist 的 Head 部分。

如果是MySQL启动时预加载或者预读的数据，则会放入MidPoint中，如果这部分数据被用户访问过之后，才会放到链表的最前端，如果没有被访问过，就会被移动到后3/8的 Old Sublist中去，直到被清理掉。

解决流程
① 预读的数据或者MySQL启动时加载的数据，不放入New SubList的头部，而是放入到MidPoint中，如果被用户访问到了才放入New SubList的头部，如果没有被访问到，放入到Old SubList中

② 用户查询加载的数据页（一定会被立刻访问的），直接放入到New SubList的头部（热点数据）。

③ 随着时间的推移，New SubList中冷数据会逐渐放入到OldSubList中，如果Old Sublist中的数据被用户访问了，这次会立刻放入到New SubList的头部

污染优化

缓冲池污染处理：

MySQL缓冲池加入了一个【老生代停留时间窗口】的机制：

（1）假设 T 代表老生代停留时间窗口；

（2）插入老生代头部的页，即使立刻被访问，并不会立刻放入新生代头部；

（3）只有满足【被访问】并且【在老生代停留时间 > T】，才会被放入新生代头部；

假设批量数据扫描，有51，52，53，54，55等五个页面将要依次被访问：

（图示1）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

                    head      new-sublist      tail
                     |                         |
                     v                         v
[ LRU ] -> [ 1 ] -> [ 3 ] -> [ 5 ] -> [ 4 ] -> [ 2 ] -> [ 10 ] -> [ 30 ] -> [ 20 ] -> [ 40 ] -> [ 7 ]
                                                                               ^                  ^
                                                                               |                  |
                                                                              head               tail
                                                                             old-sublist

                                                                                ^
                                                                                |
                                                                              [ 51 ]  <-- 大批量扫描，即将[依次]被访问的页
                                                                              [ 52 ]
                                                                              [ 53 ]
                                                                              [ 54 ]
                                                                              [ 55 ]

如果没有“老生代停留时间窗口”的策略，这些批量被访问的页面，会换出大量热数据：

（图示2）

1
2
3
4
5
6
7
8
9

                    head      new-sublist      tail
                     |                         |
                     v                         v
[ LRU ] -> [ 55 ] -> [ 54 ] -> [ 53 ] -> [ 52 ] -> [ 51 ] -> [ 1 ] -> [ 3 ] -> [ 5 ] -> [ 4 ] -> [ 2 ]   ( 10 )   ( 30 )   [ 20 ] -> [ 40 ] -> [ 7 ]
                                                                                                       批量扫码会导致大量热数据被换出
                                                          ^                  ^
                                                          |                  |
                                                         head               tail
                                                        old-sublist

加入“老生代停留时间窗口”策略后，短时间内被大量加载的页，并不会立刻插入新生代头部，而是优先淘汰那些，短期内仅仅访问了一次的页：

（图示3）

1
2
3
4
5
6
7
8
9
10

                    head      new-sublist      tail
                     |                         |
                     v                         v
[ LRU ] -> [ 1 ] -> [ 3 ] -> [ 5 ] -> [ 4 ] -> [ 2 ] -> [ 10 ] -> [ 30 ] -> [ 55 ] -> [ 54 ] -> [ 53 ]   [ 52 ]   [ 51 ]   [ 20 ] -> [ 40 ] -> [ 7 ]
                                                                               批量扫描，页面即使被访问，依然被淘汰
                                                                               ^                  ^
                                                                               |                  |
                                                                              head               tail
                                                                             old-sublist
                                                                              即使都被立刻访问，也没有立刻移动到新生代头部（整个LRU头部）

而只有在老生代呆的时间足够久，停留时间大于T，才会被插入新生代头部：

（图示4）

1
2
3
4
5
6
7
8
9

                    head      new-sublist      tail
                     |                         |
                     v                         v
[ LRU ] -> [ 55 ] -> [ 54 ] -> [ 53 ] -> [ 1 ] -> [ 3 ] -> [ 5 ] -> [ 4 ] -> [ 2 ] -> [ 10 ] -> [ 30 ]   [ 52 ]   [ 51 ]   [ 20 ] -> [ 40 ] -> [ 7 ]
                                                                               ^                  ^
                                                                               |                  |
                                                                              head               tail
                                                                             old-sublist
                                                                              在老生代停留时间>T，才会移动到新生代

相关参数优化

参数：innodb_buffer_pool_size
介绍：配置缓冲池的大小，在内存允许的情况下，DBA往往会建议调大这个参数，越多数据和索引放到内存里，数据库的性能会越好。【建议70% -80% ，监控sql执行效率，然后再设置最合适的值】

参数：innodb_old_blocks_pct
介绍：老生代占整个LRU链长度的比例，默认是37，即整个LRU中新生代与老生代长度比例是63:37。若把这个参数设为100，就退化为普通LRU了。【建议用默认值】

参数：innodb_old_blocks_time
介绍：老生代停留时间窗口，单位是毫秒，默认是1000，即同时满足“被访问”与“在老生代停留时间超过1秒”两个条件，才会被插入到新生代头部。【建议用默认值】

修改配置：

1

SET GLOBAL innodb_buffer_pool_size=402653184;

页和链表

三种页

(1) Free List

• Free 链表存放的是空闲页面，缓冲池初始化过程中（在MySQL启动时进行初始化），向操作系统申请连续的内存空间，然后划分成若干个【控制块&缓冲页】的键值对。
• Free List是把所有空闲的缓冲页对应的控制块作为一个个的节点放到一个链表中，这个链表便称之为free链表。
• 基节点: free链表中只有一个基节点是不记录缓存页信息(单独申请空间)，它里面就存放了free链表的头节点的地址、尾节点的地址、还有free链表里当前有多少个节点，等于是存放的自身的描述信息/元数据。
• 在执行SQL的过程中，每次成功load 页面到内存后，会判断Free 链表的页面是否够用。如果不够用的话，就刷新 LRU 链表（将冷数据页从内存中释放）和Flush 链表来释放空闲页（将数据变动写入磁盘）。如果够用，就从Free 链表里面删除对应的页面，在LRU 链表增加页面，保持总数不变。

磁盘加载页的流程:

1. 从Free链表中取出一个空闲的控制块（对应缓冲页）。
2. 把该缓冲页对应的控制块的信息填上（例如：页所在的表空间、页号之类的信息）。
3. 把该缓冲页对应的Free链表节点（即：控制块）从链表中移除，表示该缓冲页已经被使用了。

(2) Flush List

表示需要刷新到磁盘的缓冲区，管理脏页（Dirty Page）内部Page按修改时间排序。

InnoDB引擎为了提高处理效率，在每次修改缓冲页后，并不是立刻把修改刷新到磁盘上，而是在未来的某个时间点进行刷新操作。所以需要使用到flush链表存储脏页，凡是被修改过的缓冲页对应的控制块都会作为节点加入到Flush链表。Flush链表的结构与free链表的结构相似。

• Flush 链表里面保存的都是脏页，也会存在于LRU 链表。
• 当有页面被修改的时候，对应的Page进入Flush 链表
• 如果当前页面已经是脏页，就不需要再次加入Flush List，否则是第一次修改，需要加入Flush 链表
• 当Page Cleaner线程执行Flush操作的时候，从尾部开始Scan，将一定的脏页写入磁盘，推进检查点，减少Recover的时间

Change Buffer

Chnage Buffer，又称写缓冲区、变更/更改缓冲区。

更改缓冲区是一种特殊的数据结构，当辅助索引/次要索引（非唯一索引）页面不在缓冲池中时，它会缓存对这些页面的更改。当页面通过其他读取操作加载到缓冲池时，缓冲更改可能由INSERT、UPDATE或DELETE操作（DML）而合并。

Change Buffer更新机制：

情况1:对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；

情况2:对于普通索引来说，则是将更新记录在 Change Buffer，流程如下：

1. 更新一条记录时，该记录在BufferPool存在，直接在BufferPool修改，一次内存操作。
2. 如果该记录在Buffer Pool不存在（没有命中），在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 Change Buffer 中不用再去磁盘查询数据，避免一次磁盘IO。
3. 当下次查询记录时，会将数据页读入内存，然后执行Buffer Pool中与这个页有关的操作，通过这种方式就能保证这个数据逻辑的正确性。

什么情况下进行 merge ？

将 Change Buffer 中的操作应用到原数据页，得到最新结果的过程称为merge 。

变更写入到Change Buffer后，此时变只是在Change Bufer这（此刻并没有将数据写入到磁盘），而Buffer Pool中没有该数据对应的数据页。将有查询进来加载到变更数据对应的数据页（没有修改），此时会将Change Buffer中的变更信息与加载到缓冲池中的数据页进行合并。

合并的意义：

Buffer Pool可以将变动刷到磁盘（数据同步，线程异步）

将变动从Change Pool写入到Buffer Pool

提升效率，减少磁盘IO。

思考：如果不在内存中（Buffer Pool），把数据页加载到Buffer Pool中，在Buffer Pool中更新不好吗？为什么要多个Change Buffer呢？

因为非唯一对应的数据是很长多的，有可能涉及到非常多的数据页，去磁盘进行大量的扫描工作。 （效率极低）

Change Buffer，实际上它是可以持久化的数据。也就是说：Change Buffer在内存中有拷贝，也会被写入到磁盘上，以下情况会进行持久化:

1. 访问（Select）这个数据页会触发 merge
2. 系统有后台线程会定期 merge。
3. 在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

写缓冲区，仅适用于非唯一普通索引页，为什么？

如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，做一次IO操作。

会直接将记录查询到Buffer Pool中，然后在缓冲池修改，不会在ChangeBuffer操作。

配置缓冲区：

当在表上执行INSERT、UPDATE和DELETE操作时，索引列的值（特别是辅助键的值）通常按未排序顺序排列，需要大量的I/O才能使辅助索引更新。当相关页面不在缓冲池中时，更改缓冲区缓存对辅助索引条目的更改，从而通过不立即从磁盘读取页面来避免昂贵的I/O操作。当页面加载到缓冲池时，缓冲更改会合并，更新后的页面稍后会刷新到磁盘。当服务器几乎处于空闲状态和缓慢关机期间，InnoDB主线程合并缓冲更改。

由于更改缓冲可以减少磁盘读写，因此更改缓冲对I/O绑定的工作负载最有价值；例如，具有大量DML操作（如批量插入）的应用程序受益于写缓冲（Change Buffers）。

然而，更改缓冲区占据了缓冲池的一部分，减少了可用于缓存数据页面的内存。如果工作集几乎适合缓冲池，或者如果你的表的辅助索引相对较少，则禁用更改缓冲可能会有用。如果工作数据集完全适合缓冲池，则更改缓冲不会施加额外的开销，因为它仅适用于不在缓冲池中的页面。

innodb_change_buffering变量控制InnoDB执行更改缓冲的程度。您可以启用或禁用插入的缓冲、删除操作（当索引记录最初标记为删除时）和清除操作（当索引记录被物理删除时）。更新操作是插入和删除的组合。

1

show variables like '%innodb_change_buffering%';

允许innodb_change_buffering值包括：

• all
  默认值：缓冲区插入、删除标记操作和清除。
• none
  不要缓冲任何操作。
• inserts
  缓冲区插入操作。
• deletes
  缓冲区删除标记操作（当索引记录最初被标记为删除时，不是物理删除）。
• changes
  缓冲插入和删除标记操作。
• purges
  缓冲在后台发生的物理删除操作。

配置更改缓冲区最大大小:

通过innodb_change_buffer_max_size变量可以更改缓冲区(Change Buffer)的最大大小配置为缓冲池总(Buffer Pool)大小的百分比。默认情况下，innodb_change_buffer_max_size占Buffer Pool的25%，最大设置为50%。

考虑在具有大量插入、更新和删除活动的MySQL服务器上增加innodb_change_buffer_max_size，其中更改缓冲区合并与新的更改缓冲区条目跟不上进度，导致更改缓冲区达到最大大小限制。

考虑在MySQL服务器上使用数据经常用于查询，或者如果更改缓冲区占用与缓冲池共享的内存空间过多，导致页面比预期更快地从缓冲池中老化，则考虑减少innodb_change_buffer_max_size。

自适应哈希索引

基于二级索引的热点查询缓存

InnoDB存储引擎会监控对表上索引页（二级索引，非主键的索引，比如在name列创建的索引）的查询，自动建立合适的Hash索引，提升数据页的访问效率。

特点：

• 哈希索引，查询消耗O(1)，非常高的
• 降低对二级索引树的频繁访问
• 自适应（不用开发者自己去维护，由InnoDB引擎去维护）

缺点：

• Hash自适应索引会占用Buffer Pool
• 只适合与等值查询
  • select * from table where index_col = “郭德纲”;
  • 范围查询不可以

自适应散列索引使InnoDB能够在具有适当组合工作负载和缓冲池充足内存的系统上运行更像内存数据库（接近于Redis），而不会牺牲事务功能或可靠性。自适应哈希索引由innodb_adaptive_hash_index变量启用，或在服务器启动时由--skip-innodb-adaptive-Hash-index关闭。

1

show variables like '%innodb_adaptive_Hash_index%';

Log Buffer

Log Buffer：日志缓冲区，主要是用于记录InnoDB引擎日志，在DML操作时会产生Redo和Undo日志，该缓冲区是写入磁盘上日志文件的数据的内存区域，用来保存要写入磁盘上Log文件（Redo/Undo）的数据，日志缓冲区的内容定期刷新到磁盘Log文件中。日志缓冲区满时会自动将其刷新到磁盘，当遇到BLOB或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘I/O。

参数配置：

日志缓冲区大小由innodb_log_buffer_size变量定义。默认大小为16MB。日志缓冲区的内容定期刷新到磁盘。

• 日志缓冲区使事务能够运行，而无需在事务提交之前将重做（Redo）日志数据写入磁盘。
• 如果有更新、插入或删除许多行的事务，增加日志缓冲区的大小将保存磁盘I/O。

innodb_flush_log_at_trx_commit变量控制日志刷新频率，默认为1

• 0：每隔1秒写日志文件（从内存写入到磁盘，Log Buffer–> OS Cache–>刷盘OS Cache—>判断）和刷盘操作，最多丢失1秒数据
• 1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作
• 2：事务提交，立刻写日志文件，每隔1秒钟进行刷盘操作

磁盘

表空间

表空间（Tablespaces）：用于存储表结构和数据（含索引）。
表空间又分为系统表空间、独立表（每表 File-Per）空间、通用表空间、临时表空间、Undo表空间等多种类型；

系统表空间（The System Tablespace）

系统表空间是Change Buffer在磁盘上的存储区域。
如果表格是在系统表空间中创建的，而不是独立表空间或通用表空间中创建的，它也可能包含表和索引数据。
在之前的MySQL版本中，系统表空间还包含双写缓冲区，此存储区域位于MySQL 8.0.20的单独双写文件中。
系统表空间可以包含一个或多个数据文件。默认情况下，在数据目录中创建一个名为ibdata1的系统表空间数据文件。系统表空间数据文件的大小和数量由innodb_data_file_path启动选项定义。

1
2
3
4
5
6

show variables like '%innodb_data_file_path%';

Result:
Variable_name            |Value
-------------------------+-------------------------+
innodb_data_file_path    |ibdata1:12M:autoextend   |

ibdataba1: 文件名
12M: 默认文件大小
autoextend: 自动扩展，当指定autoextend属性时，数据文件的大小会自动增加64MB的增量

例如，此表空间有一个自动扩展的数据文件：

1
2

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

假设随着时间的推移，数据文件已增长到988MB。这是修改大小属性以反映当前数据文件大小后，以及在指定新的50MB自动扩展数据文件后，innodb_data_file_path设置：

1
2

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

独立表空间（File-Per-Table Tablespaces）

独立表空间（每表表空间）文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统中的单个数据文件中。
每个表在该表空间都有一个对应的文件，包括该表的数据和索引信息。
按表文件表空间配置：
InnoDB默认情况下，在每个表文件表空间中创建表。
此行为由innodb_file_per_table变量控制。
禁用innodb_file_per_table会导致InnoDB在系统表空间中创建表。

这个选项直接控制数据的物理存储方式：
开启时（=1，默认）：每个 InnoDB 表都会创建一个独立的 .ibd 数据文件，用于存放该表的数据和索引。
关闭时（=0）：所有表的数据和索引都混在一起，存放在共享的 系统表空间（通常是 ibdata1 文件）中。

innodb_file_per_table设置可以在配置文件中指定，
也可以在运行时使用SET GLOBAL语句配置。
在运行时更改设置需要足以设置全局系统变量的特权。
默认开启：

1
2
3
4
5
6

show variables like '%innodb_file_per_table%';

Result:
Variable_name            |Value |
-------------------------+------+
innodb_file_per_table    |ON    |

选项文件：

1
2

[mysqld]
innodb_file_per_table=ON

在运行时使用SET GLOBAL：

1

mysql> SET GLOBAL innodb_file_per_table=ON;

在MySQL数据目录下的模式目录中的.ibd数据文件中创建每个表空间文件空间。.ibd文件以表命名（table_name.ibd)

通用表空间（General Tablespaces）

共享表空间包括 InnoDB 系统表空间和通用表空间，可以被多个表所共享。

默认情况下在使用InnoDB引擎时创建的表其实使用的独立（File-Per-Table）表空间。

功能：

• 与系统表空间类似，通用表空间是能够为多个表存储数据的共享表空间（需要显式指定）。
• 与独立表空间相比，通用表空间具有潜在的内存优势。
  • 服务器在表空间的寿命周期内将表空间元数据保存在内存中。与独立表空间中单独文件的相同数量的表相比，较少的通用表空间中的多个表对表空间元数据消耗的内存更少。
• 通用表空间数据文件可以独立于MySQL数据目录。
• 通用表空间支持所有表行格式和相关功能。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

/* 创建通用的表空间 */
/* 如果在创建通用表空间的时候不指定文件名，mysql生成，格式是128位的UUID，格式化五组十六进制的数字，中间-相连接
 * aaaa-bbbb-cccc-dddd-eeeee   1c1c94ed-c3a4-11ec-aae0-0f42ac110002.ibd
 * 数据文件.ibd文件扩展名    /var/lib/mysql
 * */
create tablespace myts;

/**创建通用表空间*/
CREATE TABLESPACE tablespace_name
    [ADD DATAFILE 'file_name']
    [FILE_BLOCK_SIZE = value]
        [ENGINE [=] engine_name]
/* 在数据目录中创建一个通用表空间： */
CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
mysql> CREATE TABLESPACE `ts1` Engine=InnoDB;

/* 在数据目录之外的目录中创建一个通用表空间： */
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine=InnoDB;

/* 将表格添加到通用表空间 */
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;

/* 移除通用表空间 */
drop tablespace myts;

/* 移动表格到通用表空间 */
ALTER TABLE t2 TABLESPACE ts1;

/**
 * innodb_system 系统表空间
 * innodb_file_per_table: 独立表空间
 */
alter table t1 tablespace innodb_file_per_table;

撤销表空间（Undo Tablespaces）

撤销表空间存储Undo日志（Undo Log通常用于事务回滚），由多个包含Undo日志文件组成。在MySQL 5.7版本之前Undo占用的是System Tablespace共享区，从5.7开始将Undo从System Tablespace分离了出来。

在MySQL8.0.23之前对于数据页为16K，默认撤销表空间的初识大小是10M，在MySQL8.0.23及其之后的版本中初识大小是16M。

可以通过 innodb_undo_directory属性 查看回滚表空间的位置。默认路径是mysql的数据存储路径。

1
2
3
4
5
6

mysql> show variables like 'innodb_undo_directory';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_undo_directory | ./    |
+-----------------------+-------+

MySQL实例最多支持127个撤销表空间，包括初始化MySQL实例时创建的两个默认撤销表空间。

1
2
3
4
5
6
7

mysql> show variables like '%innodb_undo_tablespace%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_undo_tablespaces  | 2     |
+--------------------------+-------+
1 row in set (0.01 sec)

要查看撤销表空间名称和路径：

1
2
3
4
5
6
7

mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES
    WHERE FILE_TYPE LIKE 'UNDO LOG';

TABLESPACE_NAME|FILE_NAME |
----------------+----------+
innodb_undo_001|./undo_001|
innodb_undo_002|./undo_002|

临时表空间（Temporary Tablespaces）

InnoDB使用会话临时表空间（Session Temporary Tablespaces）和全局临时表空间（Global Temporary Tablespace）。

会话临时表空间

会话临时表空间存储用户创建的临时表和优化器创建的内部临时表，

外部临时表
create temporary table，创建的表就是临时表，会话结束，就自动清理。show tables不显示临时表的信息。

内部临时表
执行复杂查询的时候，比如：Group By、Order By、Distinct、Union等，执行计划中包含Using Temporary，还有就是在执行Undo回滚的时候，如果空间不足的情况下，MySQL内部将使用自动生成的临时表。

从MySQL 8.0.16开始用于磁盘内部临时表的存储引擎是InnoDB，以前是MYISAM引擎。
服务器启动时，会创建一个由10个临时表空间组成池（文件后缀为.ibt），当会话断开连接时，其占用的临时表空间将被释放回池中。池的大小永远不会缩小，磁盘空间会根据需要自动添加到池中。
临时表空间池在正常关机或初始化中止时被删除。
innodb_temp_tablespaces_dir变量定义了创建会话临时表空间的位置。默认位置是数据目录中的#innodb_temp目录。如果无法创建临时表空间池，则拒绝启动。

1
2
3
4

mysql> show variables like '%innodb_temp_tablespaces_dir%';
Variable_name            |Value             |
-------------------------+------------------+
innodb_temp_tablespaces_dir|./#innodb_temp/ |

1
2
3
4

$> cd BASEDIR/data/#innodb_temp
$> ls
temp_10.ibt  temp_2.ibt  temp_4.ibt  temp_6.ibt  temp_8.ibt
temp_1.ibt   temp_3.ibt  temp_5.ibt  temp_7.ibt  temp_9.ibt

它的关键机制是按需分配、用完即还：每个会话首次需要创建磁盘临时表时，才会从预分配的池中获取一个表空间；当会话断开连接时，它占用的临时表空间会被截断（清空数据）并释放回池中，等待下一个会话使用。这样可以有效隔离各会话的临时数据，并实现资源的循环利用。

全局临时表空间

全局临时表空间（ibtmp1）存储对用户创建的临时表进行更改的回滚段。
innodb_temp_data_file_path变量定义了全局临时表空间数据文件的相对路径、名称、大小和属性。如果没有为innodb_temp_data_file_path指定值，默认行为是在innodb_data_home_dir目录中创建一个名为ibtmp1的自动扩展数据文件。初始文件大小略大于12MB。
全局临时表空间在正常关机或中止初始化时被删除，并在每次启动服务器时重新创建。全局临时表空间在创建时会收到动态生成的空间ID。如果无法创建全局临时表空间，则拒绝启动。如果服务器意外停止，则不会删除全局临时表空间。在这种情况下，数据库管理员可以手动删除全局临时表空间或重新启动MySQL服务器。重新启动MySQL服务器会自动删除并重新创建全局临时表空间。
默认情况下，全局临时表空间数据文件会自动扩展，并根据需要增加大小。

1
2
3
4
5
6
7

mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+
1 row in set (0.00 sec)

要检查全局临时表空间数据文件的信息：

1
2
3
4
5
6
7
8
9

mysql> SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE
    ->        AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES
    ->        WHERE TABLESPACE_NAME = 'innodb_temporary';
+-----------+-------------------+--------+--------------+----------------+-----------+--------------+
| FILE_NAME | TABLESPACE_NAME   | ENGINE | INITIAL_SIZE | TotalSizeBytes | DATA_FREE | MAXIMUM_SIZE |
+-----------+-------------------+--------+--------------+----------------+-----------+--------------+
| ./ibtmp1  | innodb_temporary  | InnoDB |     12582912 |       12582912 |   6291456 |         NULL |
+-----------+-------------------+--------+--------------+----------------+-----------+--------------+
1 row in set (0.00 sec)

要限制全局临时表空间数据文件的大小，请将innodb_temp_data_file_path为指定最大文件大小。例如：

1
2

[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

配置innodb_temp_data_file_path需要重新启动服务器。

在一个临时表上执行修改操作（如 INSERT、UPDATE、DELETE）时，用于支持事务回滚的“撤销数据”就存放在这里。
它的生命周期与服务器实例绑定：正常关闭时会被删除，每次启动时重新创建。

双写缓冲区（Double Write Buffer）

双写缓冲区是一个存储区域，InnoDB将数据页写入到 InnoDB 数据文件之前，会写入从缓冲池中刷新的页面。如果页面写入过程中存在操作系统、存储子系统或意外的mysqld进程退出，InnoDB可以在崩溃恢复期间从双写缓冲区找到数据页的副本。

在MySQL 8.0.20之前，双写缓冲区位于 InnoDB 系统表空间中。从MySQL 8.0.20起，双写缓冲区位于双写文件中。

什么是写失效（部分页失效）

InnoDB的页（16K）和操作系统的页大小不一致，InnoDB页大小一般为16K，操作系统页大小为4K，InnoDB的页写入到磁盘时，一个页需要分4次写。如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了4K，就宕机了，这种情况叫做部分写失效（partial page write），可能会导致数据丢失。

为了解决写失效问题，InnoDB实现了Double write buffer Files。在Buffer Pool的page页刷新到磁盘真正的位置前，会先将数据存在Double write 缓冲区。这样在服务器宕机重启时，如果出现数据页损坏，那么在应用Redo Log之前，需要通过该页的副本来还原该页，然后再进行Redo Log重做，Double Write实现了InnoDB引擎数据页的可靠性。

在MySQL 8.0.20之前，双写缓冲区位于 InnoDB 系统表空间中。从MySQL 8.0.20起，双写缓冲区位于双写文件中。

1
2
3
4
5
6
7
8
9

show variables like '%innodb_doublewrite%';

Variable_name                   |Value|
--------------------------------+-----+
innodb_doublewrite              |ON   |
innodb_doublewrite_batch_size   |0    |
innodb_doublewrite_dir          |     |
innodb_doublewrite_files        |2    |
innodb_doublewrite_pages        |4    |

innodb_doublewrite变量控制是否启用doublwrite缓冲区。在大多数情况下，默认情况下启用它。

innodb_doublewrite_batch_size变量（在MySQL 8.0.20中引入）控制批量写入的双写页数。此变量用于高级性能调优。默认值应该适合大多数用户。

innodb_doublewrite_dir变量（在MySQL 8.0.20中引入）定义了InnoDB创建双写文件的目录。如果没有指定目录，则在innodb_data_home_dir目录中创建双写文件，如果未指定，该目录默认为数据目录。

innodb_doublewrite_files变量定义了doublewrite文件的数量。默认情况下，为每个缓冲池实例创建两个双写文件

innodb_doublewrite_pages变量（在MySQL 8.0.20中引入）控制每个线程的最大双写页面数量。如果没有指定值，innodb_doublewrite_pages将设置为innodb_write_io_threads值。此变量用于高级性能调优。默认值适合大多数用户。

重做日志，Redo Log。

WAL（Write-Ahead Logging）策略：

WAL的全称是 Write-Ahead Logging，中文称预写式日志（写前日志），是一种数据安全写入机制。就是先写日志，然后再写入磁盘，这样既能提高性能又可以保证数据的安全性，MySQL中的Redo Log就是采用WAL机制。

写后日志：先将数据写入到磁盘，然后将数据写入到日志，这种策略不适合MySQL中使用，适合的场景是内存型数据库的备份。比如Redis的AOF的持久化策略（记录操作命令），宕机恢复进行AOF日志的回放，该方式本质上就是写入日志。

• InnoDB首先将重做（Redo Log）日志信息先放到重做日志缓存
• 按一定频率刷新到重做日志文件（Redo Log File）

为什么使用WAL？

磁盘的写（使用SQL语句执行编辑操作）操作是随机IO，比较耗性能，所以如果把每一次的更新操作都先写入Log中，那么就成了顺序写操作（效率远远高于随机IO），实际更新操作由后台线程再根据Log异步写入。这样对于Client端，延迟就降低了。并且，由于顺序写入大概率是在一个磁盘块内，这样产生的IO次数也大大降低。所以WAL的核心在于将随机写转变为了顺序写，降低了客户端的延迟，提升了吞吐量。

Redo Log 基本概念:

InnoDB引擎对数据的更新，是先将更新记录写入Redo Log日志，然后会在系统空闲的时候或者是按照设定的更新策略再将日志中的内容更新到磁盘之中。这就是所谓的预写式技术（Write Ahead logging）。这种技术可以大大减少IO操作的频率，提升数据刷新的效率。

Redo Log：被称作重做日志，包括两部分：一个是内存中的日志缓冲：Redo Log Buffer，另一个是磁盘上的日志文件：Redo Log file 。默认情况下，重做日志在磁盘上由两个名为ib_logfile0和ib_logfile1的文件物理表示。

MySQL 每执行一条 DML 语句，先将记录写入Redo Log Buffer（Redo日志记录的是事务对数据库做了哪些修改）。后续某个时间点再一次性将多个操作记录写到 Redo Log File 。当故障发生致使内存数据丢失后，InnoDB会在重启时，经过重放Redo，将Page恢复到崩溃之前的状态 通过Redo Log可以实现事务的持久性 。

Redo Log数据落盘流程:

将内存中的数据页持久化到磁盘,需要下面的两个流程来完成：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

+-----------------------------------------------------------------------------------+
|                                  内存Buffer                                       |
|                                                                                   |
|     +--------------+      事务提交, 写Redo log      +-------------------+         |
|     |  Buffer Pool | ------------------------------> |  Redo Log Buffer  |         |
|     +--------------+                                 +-------------------+         |
+------------|---------------------------------------------------|------------------+
             |                                                   |
             | 根据Checkpoint                                    | 按时机持久化
             | 机制刷新脏页                                      | redo log buffer
             |                                                   |
+------------|---------------------------------------------------|------------------+
|            v                                                   v                  |
|     +--------------+                                 +-------------------+         |
|     |              |                                 |                   |         |
|     |    表空间    | <------------------------------ |   Redo Log File   |         |
|     |              |      数据丢失时从redo           |                   |         |
|     +--------------+      log file 恢复              +-------------------+         |
|                                                                                   |
|                                  文件系统                                         |
+-----------------------------------------------------------------------------------+

当进行数据页的修改操作时: 首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。

Redo Log Buffer刷新到Redo Log File ，下面三种情况刷新：

• Master Thread每一秒将重做日志缓冲刷新到重做日志文件
• 每个事务（Commit）提交时会将重做日志缓冲刷新到重做日志文件
• 当重做日志缓冲池剩余空间小于1/2时，重做日志刷新到重做日志文件

补充上述三种情况第二种，触发写磁盘过程由参数innodb_flush_log_at_trx_commit控制，表示提交(commit)操作时，处理重做日志的方式。

1

show variables like '%innodb_flush_log_at_trx_commit%';

参数innodb_flush_log_at_trx_commit有效值有0、1、2

• 0表示当提交事务时，并不将事务的重做日志写入磁盘上的日志文件，而是等待主线程每秒刷新。
• 1表示在执行commit时将重做日志缓冲同步写到磁盘（数据安全性有保障，MySQL Server），即伴有fsync的调用
  • fsync函数式Linux系统的写入磁盘的函数
  • 兼顾了效率和安全性
  • 默认值 ,不建议修改
• 2表示将重做日志异步写到磁盘，即写到文件系统的缓存中，不保证commit时肯定会写入重做日志文件。

0，当数据库发生宕机时，部分日志未刷新到磁盘，因此会丢失最后一段时间的事务。
2，当操作系统宕机时，重启数据库后会丢失未从文件系统缓存刷新到重做日志文件那部分事务。

Undo Logs

Undo Logs是一种用于撤销回退的日志，在数据库事务开始之前，MySQL会先记录更新前的数据到 Undo Logs日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 Undo Logs来进行回退。

Undo Log产生和销毁：Undo Log在事务开始前产生；事务在提交时，并不会立刻删除Undo Logs，InnoDB会将该事务对应的Undo Logs放入到删除列表中，后面会通过后台线程Purge Thread进行回收处理。

注意: undo log也会产生Redo Log，因为undo log也要实现持久性保护。

undo log的工作原理:

在更新数据之前，MySQL会提前生成Undo Logs日志，当事务提交的时候，并不会立即删除Undo Logs，因为后面可能需要进行回滚操作，要执行回滚（Rollback）操作时，从缓存中读取数据。

1. 事务A执行Update更新操作，在事务没有提交之前，会将旧版本数据备份到对应的Undo Buffer中，然后再由Undo Buffer持久化到磁盘中的Undo Logs文件中,之后才会对User进行更新操作,然后持久化到磁盘.
2. 在事务A执行的过程中，事务B对User进行了查询。

InnoDB 线程模型

InnoDB使用多线程模型，后台有多个不同的线程负责不同的任务。

(1) AIO Thread

异步IO（Async IO）广泛用于InnoDB存储引擎，以处理写入IO请求（读写处理），极大提高数据库的性能。IO Thread主要负责这些IO请求的回调。在InnoDB1.0版本之前共有4个IO Thread，分别是write，read，insert buffer和log thread，后来版本将read thread和write thread分别增大到了4个，一共有10个了。

• read thread：负责读取操作，将数据从磁盘（每个表都有一个数据文件）加载到缓存page页 （Buffer Pool），4个。
• write thread：负责写操作，将缓存脏页（Buffer Pool Dirty Pages）刷新到磁盘。
• log thread：负责将日志缓冲区内容刷新到磁盘。(处理log buffer –> 磁盘)
• insert buffer thread：负责将写缓冲内容刷新到磁盘。（处理change buffer–> 磁盘）

(2) 清除线程（Purge Thread）

提交事务后，可能不再需要它使用的撤销日志，因此需要清除线程来回收分配和使用的Undo页面。InnoDB支持多个清除线程，这加快了UNDO页面的恢复速度，提高了CPU使用率，并提高了存储引擎的性能。

1

mysql> show variables like '%innodb_purge_threads%';

InnoDB1.2+（对应的MySQL的版本是5.6）开始，支持多个Purge Thread这样做的目的为了加快回收Undo页（释放内存）。

(3) 页面清洁线程（Page Cleaner Thread）

页面清理线程的作用是将脏页面刷新到磁盘（持久化），脏数据刷盘后相应的Redo Log也就可以覆盖，即可以同步数据，又能达到Redo Log循环使用的目的，会调用Write Thread线程处理。

1
2
3
4
5
6

mysql> show variables like '%innodb_page_cleaners%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_cleaners | 1     |
+------------------+-------+

(4) 主线程（Master Thread）

Master thread是InnoDB的主线程，负责调度其他各线程，优先级最高，它主要负责从缓冲池到磁盘的数据异步刷新，以确保数据一致性。包含：脏页的刷新（Page Cleaner Thread）、Undo页回收（Purge Thread）、Redo日志刷新（Redo Log thread）、合并写缓冲等。刷新脏页数据到磁盘，根据脏页比例达到90%才操作（innodb_max_dirty_pages_pct）。

一致性日志

主要作用用于事务回撤、崩溃回复、主从同步或者数据同步（数据同步中间件Canal）。

在MySQL Server和 InnoDB 引擎中和一致性相关的有重做日志（Redo Log）、回滚日志（Undo log）和二进制日志（BinLog）。其中Redo Log属于物理日志，Undo log和BinLog属于逻辑日志。

• Redo 和Undo Log是属于InnoDB引擎层的日志
• BinLog是属于服务层

日志分类

物理日志和逻辑日志在存储内容上有很大区别，存储内容是区分它们的最重要手段。

物理日志

• 存储内容：存储数据库中特定记录的变更，通常是 page oriented（面向数据页，记录的日志都是基于数据页），即描述具体某一个 Page 的修改操作；
• 例子：一条更新请求对应的初始值（original value）以及更新值（after value）；

逻辑日志：

• 存储内容：存储事务中的一个操作；
• 例子：事务中的 UPDATE、DELETE 以及 INSERT 操作。

更新操作作用于Page42，将字段“Kemera”修改为“camera”。更新操作对应的日志为：

"Page 42:image at 367,2; before:'Ke';after:'ca'"

其中：

• Page 42 用于说明更新操作作用的page；
• 367：用于说明更新操作相对于page的offset；
• 2：用于说明更新操作的作用长度，即length，2代表仅仅修改了两个字符；
• before:’Ke’：这里表示Undo information，也可以称为Undo log；
• after:’ca’：这里表示Redo information，也可以称为Redo Log；

当然，一条物理日志可以有多个字段的修改，下面是一个抽象版本：

" (Page ID, Record Offset,length, (Filed 1, Value 1) ... (Filed i, Value i) ... )"

注意事项：

• 物理日志实际上以字节编码落盘，而不是字符编码，因此通常肉眼不可见
• image的含义通常指代镜像，但这里不是说对page做整个镜像，而是对更改或增量（change/delta）操作做镜像，before image代表写操作作用之前的字段副本，after image代表写操作作用之后的字段副本

物理日志中的一条记录对应某一个Page页上的某些字段做了什么改动的落盘。

逻辑日志

逻辑日志又被称为High-Level Logging，这是相对于物理日志而言的。

有一张CameraLingo表，我们试图纠正itemID为0的拼写错误，即将“Kemera”修改为“Cemera”。逻辑日志的格式如下：

CameraLingo:update(0,'Kermera'=>'camera')

逻辑日志被称为high level的原因是其更抽象，其不需要指明更新操作具体作用于哪一块page，因此也对底层少了一些限制。如果利用物理日志进行宕机后的数据恢复，那么需要确保page不能够改变，但利用逻辑日志并不在乎底层page是否改变。

逻辑日志与SQL语句非常类似，逻辑日志的本质就是对更新语句（update query）本身的落盘。

例子中，只需要指明在哪一张表上的哪一行，对哪些字段进行什么修改即可。逻辑日志不用物理上的page，而用逻辑上的表。

undo log 工作原理

记录修改前的数据，方便发生意外时方便回滚

概述

Redo Log是重做日志，提供前滚操作（重做），Undo log是回滚日志，提供回滚操作（Rollback）。
Undo log有两个作用：提供回滚和多个行版本控制（MVCC Multi-Versioin Concurrency Control）。
事务回滚（日志回滚）：
在设计数据库时，我们假设数据库可能在任何时刻，由于如硬件故障，软件Bug，运维操作等原因突然崩溃。这个时候尚未完成提交的事务可能已经有部分数据写入了磁盘，如果不加处理，会违反数据库对Atomic的保证，也就是任何事务的修改要么全部提交，要么全部取消。
数据库实现中通常会在正常事务进行中，就不断的连续写入Undo Log（每有一个DML语句执行，都会记录Undo Log），来记录本次修改之前的历史值。当Crash真正发生时，可以在Recovery过程中通过回放Undo Log将未提交事务的修改抹掉（恢复到修改之前的状态）。
既然已经有了在Crash Recovery时支持事务回滚的Undo Log，在正常运行过程中，死锁处理或用户请求的事务回滚也可以利用这部分数据来完成。

存储结构

Innodb存储引擎对Undo的管理采用段的方式。Rollback Segment 称为回滚段，每个回滚段中有1024个Undo Log Segment。每个Undo Log Segment对应一个回滚日志。
一个Rollback Segment = 1024 Undo Log Segment
默认支持128个Rollback Segment，即支持1281024个Undo操作，还可以通过变量 innodb_rollback_segments 自定义多少个Rollback Segment，默认值为128。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

mysql> show variables like '%innodb_Undo%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_Undo_directory    | ./    |
| innodb_Undo_log_encrypt  | OFF   |
| innodb_Undo_tablespaces  | 2     |
+--------------------------+-------+
3 rows in set (0.01 sec)

/*
 * ./ : 当前的数据目录, /var/lib/mysql
 */

/* 回滚段的数量 */
mysql> show variables like '%innodb_rollback_segments%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_rollback_segments | 128   |
+--------------------------+-------+
1 row in set (0.00 sec)

回滚段支持的事务数量取决于回滚段中的撤销插槽数量以及每个事务所需的撤销日志数量。回滚段中的撤销插槽数量因InnoDB页面大小而异。

存储机制

如上图，Undo Log日志里面不仅存放着数据更新前的记录，还记录着RowID、事务ID、回滚指针。其中事务ID每次递增，回滚指针第一次如果是Insert语句的话，回滚指针为NULL，第二次update之后的Undo Log的回滚指针就会指向刚刚那一条Undo Log日志，依次类推，就会形成一条Undo Log的回滚链，方便找到该条记录的历史版本。一旦执行Commit，提交事务，将变动信息真正的写入到磁盘，此时Uno Log也就没有意义，会被清理掉。

工作原理

在更新数据之前，MySQL会提前生成Undo log日志，当事务提交的时候，并不会立即删除Undo log，因为后面可能需要进行回滚操作，要执行回滚（rollback）操作时，从缓存中读取数据。Undo log日志的删除是通过通过后台purge线程进行回收处理的。

事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。
事务B手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读取。

Redo Log 工作原理

概述

Redo Log，重做日志，也叫重放日志。和Undo log回滚日志一样，都是在数据库发生意外时用来进行数据恢复的。

• Undo log记录的是数据更新前的样子，主要保证事务的原子性。
  • 例如一个事务包含两个操作：update、update，如果第一个update执行了，但是数据库崩溃了，数据出现了中间状态，违反了事务的原子性，可以通过Undo Log回滚，将数据恢复到之前的样子。
• Redo Log则记录的是事务执行过程中的修改情况（记录的是修改之后），Redo Log主要保证事务的持久性
  • 例如一个事务：insert，加入已经将事务提交了，修改数据页（Dirty Page），崩溃了，恢复就可以通过redo log进行回放，修改后的数据就恢复了。

当数据库对数据做修改的时候，需要把数据页从磁盘读到buffer pool中，然后在buffer pool中进行修改（从内存中修改快），那么这个时候buffer pool中的数据页就与磁盘上的数据页内容不一致，称buffer pool的数据页为dirty page 脏数据，如果这个时候发生非正常的DB服务重启，那么这些数据还没在内存，并没有同步到磁盘文件中（注意，同步到磁盘文件是个随机IO），也就是会发生数据丢失。

如果这个时候，能够在有一个文件，当buffer pool 中的数据页变更结束后，把相应修改记录记录到这个文件（注意，记录日志是顺序IO，效率非常高，不需要寻址），那么当DB服务发生crash，进行恢复DB的时候，可以根据这个文件的记录内容，重新持久化刷新到磁盘文件，保持数据的一致性。

这个文件其实就是Redo Log，用于记录数据修改后的记录，顺序记录，主要用于数据的持久化操作。写入到磁盘中的数据页中属于随机IO，效率低。

Redo Log的作用

1、保证事务的持久性
如果buffer pool缓冲池中的脏页【脏数据】还没有进行刷盘的时候，此时数据库发生crash，重启服务后，我们可以通过Redo Log日志找到需要重放到磁盘文件的那些数据记录。
2、提高事务提交的速度
buffer pool缓冲池中的数据直接刷新到磁盘，是一个随机IO，效率较差，而把buffer pool中的数据记录到Redo Log，是一个顺序IO，可以提高事务提交的速度。例如：
我们执行了一条更新语句：

1

update User set name = '弼马温' where id = 1  --更新前name = '齐天大圣'

此时Redo Log就会用来存在name = ‘弼马温’这条更新后的新纪录，如果在刷盘时发生异常，我们可以通过Redo Log找到这条记录，然后进行重放操作，以保证事务的持久性。

工作原理

和Undo log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，
不需要将数据持久化，不需要将数据持久化，不需要将数据持久化，重要的事情说三遍！。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化到磁盘中，系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

假设有A、B两个数据，值分别为1, 2，开始一个事务，事务的操作内容为：把1修改为3，2修改为4，那么实际的记录如下（简化）：

1. 事务开始.
2. 记录A=1到Undo log.
3. 修改A=3.
4. 记录A=3到Redo Log.
5. 记录B=2到Undo log. ——– 记录Undo log回滚日志
6. 修改B=4. ——– 修改数据
7. 记录B=4到Redo Log. ——– 记录Redo Log重做日志
8. 将Redo Log写入磁盘。 ——– Redo Log刷盘持久化
9. 事务提交 ——– 提交事务

如上过程，我们可以总结出来Undo + Redo事务的特点：

• 为了保证持久性，必须在事务提交前将Redo Log日志持久化。
• 数据不需要在事务提交前写入磁盘，而是缓存在内存中（数据页 Buffer Pool中的Dirty Page）。
• Redo Log保证事务的持久性。
• Undo log保证事务的原子性。
• 数据必须要晚于Redo Log写入持久存储。

Redo Log的相关参数

innodb_flush_log_at_trx_commit，控制commit动作是否刷新log buffer到磁盘。该变量有3种值：0、1、2，默认为1。

1
2
3
4
5
6
7

mysql> show variables like '%innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

• innodb_flush_log_at_trx_commit=0：（延迟写）事务提交时不会将log buffer中日志写入到os buffer，然后每秒调用fsync()写入到log file on disk磁盘文件中。
• 这种情况下如果系统崩溃，会丢失1秒钟的数据。
• innodb_flush_log_at_trx_commit=1：（实时写，实时刷）事务每次提交，会保存到log buffer，接着保存到os buffer操作系统缓存，并调用fsync()刷到log file on disk磁盘文件中。
• 这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
  • innodb_flush_log_at_trx_commit=2：（实时写，延迟刷），每次事务提交，数据不写到log buffer，仅写入到os buffer，然后是每秒调用fsync () 将os buffer中的日志写入到log file on disk磁盘文件中。

正常情况下，设置参数值为0或者2能提供插入的效率，但在故障的时候可能会丢失1秒钟数据。并且参数值为2和0的时候差距并不大，因为它们都是每秒从os buffer刷到磁盘，它们之间的时间差体现在log buffer刷到os buffer上。

因为将log buffer中的日志刷新到os buffer只是内存数据的转移，并没有太大的开销，所以每次提交和每秒刷入差距并不大。但值为1的性能却会差很多。

• innodb_log_buffer_size
  • 指定 log buffer【Redo Log缓存区】的大小，默认16M。延迟事务日志写入磁盘，把Redo Log 放到该缓冲区，然后根据 innodb_flush_log_at_trx_commit参数的设置，再把日志从buffer中flush 到磁盘中。
• innodb_log_file_size
  • 指定事务日志的大小，默认5M。
• innodb_log_files_in_group =2
  • log group表示的是Redo Log group，一个组内由多个大小完全相同的Redo Log file组成。
  • innodb_log_files_in_group指定事务日志组中的事务日志文件个数，默认2个，最大是100个。

BinLog

概述

Redo Log 和Undo Log是属于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binary log（二进制日志），简称BinLog。
BinLog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作。BinLog日志是以事件（Event）形式记录，还包含语句所执行的消耗时间。BinLog日志有以下两个重要的使用场景：

• 主从复制：在主库中开启BinLog功能（MySQL8版本中默认开启），这样主库就可以把BinLog传递给从库，从库拿到BinLog后实现数据恢复达到主从数据一致性（从库的数据与主库保持一致）
  • 前提是必须在主服务器上开启BinLog
  • 开发工作中的主要使用场景（为什么要使用MySQL集群，提升读写性能及可用性）
• 数据恢复：通过mysqlBinLog工具在备份文件恢复的基础上，通过BinLog日志，可以将数据库恢复到某一时间点。
• 操作审计：对所有更改数据的操作进行审计

BinLog数据格式:

MySQL的 BinLog 分为三种数据格式：statement、row 及 mixed 格式。

1
2
3
4
5
6
7
8

create table my_table{
  id bigint not null auto_increment comment '主键',
  message varchar(100) not null comment '消息',
  status tinyint not null comment '状态',
  created datetime not null '创建时间',
  modified datetime not null '修改时间',
  primary key ('id') using btree
}

1.statement 格式

statement 格式是把每次执行的 SQL 语句记录到 BinLog 文件里，在主从复制时，基于 BinLog 里的 SQL 语句进行回放来完成主从复制。批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件。

执行SQL：

1

update my_table set status='无效' where id =1

BinLog 中记录的便是上述这条具体的 SQL。

采用 SQL 格式的 BinLog 的好处是内容太少，传输速度快。由于它是记录执行语句，所以，为了让这些语句在Slave端也能正确执行，那么它还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息（Context），来保证所有语句在Slave端能够得到和在Master端相同的执行结果。

优点：日志量小，减少磁盘IO，提升存储和恢复速度
缺点：在某些情况下会导致主从数据不一致，比如last_insert_id () 、now () 等函数。

2. row 格式

row 格式的 BinLog 会把当次执行的 SQL 命中的那条数据库行的变更前和变更后的内容，都记录到 BinLog 文件里。以上述 statement 格式里的 SQL 作为示例，该 SQL 在 row 格式下执行后会产生如下的数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

{
  "before":{
    "id":1,
    "message":"文本",
    "status":"有效",
    "created":"xxxx-xx-xx",
    "modified":"xxxx-xx-xx"
  },
  "after":{
    "id":1,
    "message":"文本",
    "status":"无效",
    "created":"xxxx-xx-xx",
    "modified":"xxxx-xx-xx"
  },
  "change_fields":["status"]
}

优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。而且不会出现某些特定情况下存储过程或function，以及trigger的调用和触发器无法被正确复制的问题。
缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。

3. mixed 模式

mixed（混合）模式是上述两种模式的动态结合。采用 mixed 模式的 BinLog 会根据每一条执行的 SQL 动态判断是记录为row 格式还是 statement 格式。

比如一些 DDL 语句，如新增加字段的 SQL，就没有必要记录为 row 模式，记录为 statement 即可，因为它本身并没有涉及数据变更。对于STATEMENT模式无法复制的操作使用ROW模式保存BinLog。

在实际应用中，推荐使用 row 模式或者 mixed 模式，主要有以下两个原因。

原因一：这两种格式的数据量全，可以让你做更多的逻辑。因为随着业务需求的发展，同步逻辑会出现非常多的个性化需求，越多信息的数据，在编写代码时会越简单。

原因二：row 模式无须解析SQL，实现复杂度非常低。在执行的 SQL 非常复杂时，对 statement 模式里记录的 SQL 的解析需要耗费大量开发精力，越复杂的解析越容易产生 Bug，所以推荐更加简单的 row 模式的数据格式。

1
2
3
4
5

show variables like 'binlog_format'

Variable_name|Value|
-------------+-----+
binlog_format|ROW  |

BinLog日志配置

索引文件：binlog.index，记录哪些日志文件正在被使用

日志文件：binlog.000001等等，记录数据库所有的DDL和DML事件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

/* 查看配置 */
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  |
+------------------+------------------------------+
3 rows in set (0.00 sec)
/*
  在8.0以上版本中BinLog默认开启，在早期版本中默认关闭
*/

/* 指定了单个二进制日志文件的最大值，默认为1G，如果超过该值，会写入新的文件，并记录到.index文件。 */
mysql> show variables like 'max_binlog_size';

/* 二进制日志文件列表 */
mysql> show binary logs;
+------------------+------------+-----------+
| Log_name         | File_size  | Encrypted |
+------------------+------------+-----------+
| BinLog.000001    |        179 | No        |
| BinLog.000002    |       2445 | No        |
| BinLog.000003    |        179 | No        |
| BinLog.000004    |    1723875 | No        |
| BinLog.000005    |        156 | No        |
+------------------+------------+-----------+
5 rows in set (0.03 sec)

/* 查看正在使用的BinLog文件 */
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | BinLog_Do_DB | BinLog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| BinLog.000005    |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

filename 参数指定二进制文件的文件名，其形式为 filename.number，number 的形式为 000001、000002 等。每次重启MySQL 服务后，都会生成一个新的二进制日志文件，这些日志文件的文件名中 filename 部分不会改变，number 会不断递增。

BinLog落盘策略

BinLog的写入顺序：BinLog cache (write) -> OS cache -> (fsync) disk.

BinLog的写入逻辑比较简单：

事务执行过程中，先把日志写到BinLog cache，这个操作会调用write方法，并没有把数据持久化到磁盘，速度比较快；事务提交的时候，再把BinLog cache写到BinLog文件中，这步会调用fsync将数据持久化到磁盘，速度比较慢。

write表示：写入文件系统缓存，fsync表示持久化到磁盘的时机。

BinLog刷数据到磁盘由参数sync_BinLog进行配置

• sync_BinLog=0的时候，表示每次提交事务都只 write，不 fsync;
• sync_BinLog=1的时候，表示每次提交事务都会执行 fsync;
• sync_BinLog=N(N>1)的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

1
2
3
4
5
6
7

mysql> show variables like '%sync_BinLog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_BinLog   | 1     |
+---------------+-------+
1 row in set (0.00 sec)

注意: 不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。如果设置成 0，主动重启丢失的数据不可控制。设置成 1，效率低下，设置成N(N>1)，则主机重启，造成最多N个事务的BinLog日志丢失，但是性能高，丢失数据量可控。

在出现IO瓶颈的场景中，将sync_BinLog设置成一个比较大的值，可以提升性能。实际业务场景中，通常设置为100～1000中的某个数值。这样做对应的风险是：如果主机发生一场重启，会丢失最近N个事务的BinLog。

如果是核心业务，那么建议1，如果是非核心（非黄金链路）可以容忍部分数据的丢失，可以将值设置为100-1000，如果是我们的日志系统，那么该值可以设置的更大，根本上取决于业务对数据丢失的容忍程度。

和BinLog一样，Redo Log也是先写到Redo Log buffer，然后再合适的时间持久化到磁盘。

InnoDB提供了配置参数innodb_flush_log_at_trx_commit。

1
2
3
4
5
6
7

mysql> show variables like '%innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.01 sec)

• 设置为0的时候，表示每次事务提交时都只是把Redo Log留在Redo Log buffer中。
• 设置为1的时候，表示每次事务提交时都将Redo Log直接持久化到磁盘。
• 设置为2的时候，表示每次事务提交时都只是把Redo Log写到page cache。
• InnoDB有一个后台线程，每隔1s就会把Redo Log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘。

通常我们说MySQL的“双1”配置，指的就是sync_BinLog和innodb_flush_log_at_trx_commit都设置成1.也就是说，一个事务完整提交前，需要等待两次刷盘，一次是Redo Log（prepare阶段），一次是BinLog。

1
2
3

// Redo Log crash-safe
innodb_flush_log_at_trx_commit 设置为1 表示每次事务的Redo Log都直接持久化到磁盘
sync_BinLog 设置为1，表示每次事务的BinLog都持久化到磁盘

我们可以看到，WAL机制是减少磁盘写，可是每次提交事务都要写Redo Log和BinLog，磁盘读写次数并没有减少，但是性能有提高，WAL主要得益于Redo Log和BinLog都是顺序写，磁盘的顺序写比随机写速度要快。

日志对比

1. Redo Log是InnoDB引擎特有的；BinLog是MySQL的Server层实现的，所有存储引擎都可以使用
2. Redo Log是物理日志，记录的是“在XXX数据页上做了XXX修改”；BinLog是逻辑日志，记录的是原始逻辑，其记录是对应的SQL语句
3. Redo Log是循环写的，空间一定会用完，需要write pos和check point搭配；BinLog是追加写，写到一定大小会切换到下一个，并不会覆盖以前的日志
4. Redo Log作为服务器异常宕机后事务数据自动恢复使用，BinLog可以作为主从复制和数据恢复使用。BinLog没有自动crash-safe能力

Crash Safe指MySQL服务器宕机重启后，能够保证：
所有已经提交的事务的数据仍然存在。
所有没有提交的事务的数据自动回滚。

思考问题

Q1: 数据库是如何根据BinLog 的三种数据格式进行数据恢复的

数据库使用 Binlog 进行恢复时，核心流程是统一的：先用全量备份恢复到某个时间点，然后用 mysqlbinlog 解析出 Binlog 中的事件，重放到数据库里，完成增量恢复。

三种格式的区别，主要体现在 Binlog 里记录了什么，以及 mysqlbinlog 如何解析和重放。

📝 STATEMENT 格式的恢复方式

记录内容：直接记录修改数据的 SQL 语句本身，比如 UPDATE orders SET status=1 WHERE id=100;。

恢复原理：mysqlbinlog 解析出来的就是原始的 SQL 语句。恢复时，直接重新执行这些 SQL 即可。

关键风险：如果原 SQL 包含非确定性函数（如 NOW()、RAND()、UUID()），或者依赖特定的会话变量、执行顺序（如 DELETE ... LIMIT 1 未加 ORDER BY），在恢复环境重新执行时，很可能得到与原始执行不同的结果，导致数据不一致。

📝 ROW 格式的恢复方式

记录内容：不记录 SQL 语句，而是记录每一行数据被修改前后的镜像（前镜像/后镜像）。比如，一个 UPDATE 影响 10 万行，Binlog 里就会有 10 万条行变更记录。

恢复原理：mysqlbinlog 配合 --base64-output=decode-rows -v 参数，可以解析出行变更的详细信息。恢复时，基于这些行数据变更事件，在数据库内部直接应用修改，不涉及重新执行原始 SQL。

关键特性：因为直接记录数据本身的变化，完全避免了 STATEMENT 模式的不确定性问题，恢复结果最可靠。此外，ROW 格式还支持 “闪回”：通过解析 Binlog 生成反向 SQL（如把 DELETE 事件转换成 INSERT），可以精准恢复误删的数据。

📝 MIXED 格式的恢复方式

记录内容：由 MySQL 自动选择。对于安全的、确定性的语句，用 STATEMENT 格式记录；对于可能引起不一致的语句（如含不确定函数），则自动切换为 ROW 格式记录。

恢复原理：mysqlbinlog 解析时，根据每条事件实际的记录格式来决定如何处理。如果解析出的是 SQL，就重放 SQL；如果解析出的是行数据变更，就应用行变更。

实际效果：可以看作是 “智能的 STATEMENT”。它试图在 STATEMENT 的轻量和 ROW 的安全之间做平衡，但仍然以 STATEMENT 为基础，如果 MySQL 判断失误，仍然存在数据不一致的风险。

💡 给生产环境的建议

目前 MySQL 8.0.34 及以后版本已弃用 binlog_format 参数，默认且推荐使用 ROW 格式。因为 ROW 格式是唯一能保证主从/恢复数据绝对一致，并且支持闪回恢复的模式。对于需要精确、安全恢复的生产环境，务必使用 ROW 格式。

Q2: redo log 和 undo log 是怎么帮助数据库恢复数据的

Redo log 和 Undo log 的恢复机制，与 Binlog 有本质区别：Binlog 是逻辑日志，用于”重放”已提交事务；而 Redo/Undo 是物理/逻辑物理日志，用于保证数据库的崩溃恢复（Crash Recovery）和事务原子性。

下面分别说明两者的恢复原理。

一、Redo Log：崩溃恢复的”重做”

1. 核心作用

Redo Log 记录的是“在某个数据页上做了什么修改”（物理逻辑日志），用于保证 已提交事务的持久性（Durability）。

关键机制是 WAL（Write-Ahead Logging）：事务提交时，先写 Redo Log（顺序写，快），再异步刷脏页到磁盘（随机写，慢）。只要 Redo Log 落盘，即使数据页还没刷盘，事务也算提交成功。

2. 恢复原理

当数据库异常崩溃后重启，InnoDB 会进入崩溃恢复流程，Redo Log 负责 “重做”：

• 扫描 Redo Log：从最近一次 Checkpoint（检查点）位置开始，向后扫描。
• 重放已提交事务：对于已经提交但数据页尚未刷盘的事务，根据 Redo Log 中的记录，重新应用到数据页上，使数据恢复到崩溃前的状态。
• 保证持久性：这样，已提交的事务不会因为崩溃而丢失。

3. 关键概念：LSN 与 Checkpoint

• LSN（Log Sequence Number）：全局递增的日志序列号，标识 Redo Log 的位置。
• Checkpoint：表示”此 LSN 之前的所有脏页都已经刷盘”。恢复时只需从 Checkpoint 之后开始扫描，无需重放全部日志，大大缩短恢复时间。

二、Undo Log：事务回滚与 MVCC 的”撤销”

1. 核心作用

Undo Log 记录的是数据被修改前的旧版本，用于：

• 事务回滚（Rollback）：保证原子性（Atomicity）。
• MVCC（多版本并发控制）：为读操作提供历史版本，实现一致性读。

2. 恢复原理

Undo Log 的”恢复”分两种场景：

场景 A：事务主动回滚

当事务执行 ROLLBACK 时，InnoDB 会根据 Undo Log 中的记录，反向执行：

• INSERT → 对应 DELETE
• DELETE → 对应 INSERT
• UPDATE → 用旧值覆盖新值

从而将数据恢复到事务开始前的状态。

场景 B：崩溃恢复时的回滚

数据库崩溃重启后，Redo Log 会先 “重做” 所有已提交和未提交的事务（因为崩溃时可能有些事务只写了一半）。然后，InnoDB 会检查 Undo Log，找出崩溃时尚未提交的事务，对它们执行 “回滚”，撤销这些未完成事务的影响。

这就是为什么崩溃恢复分两个阶段：先 Redo 重做，再 Undo 回滚。

3. Undo Log 的存储与清理

• Undo Log 存放在 Undo Tablespace（如 undo_001、undo_002）中。
• 事务提交后，Undo Log 不会立即删除，因为 MVCC 可能还需要它提供历史版本。只有当没有更早的 Read View 需要它时，才会被 Purge 线程清理。

三、三者对比总结

四、崩溃恢复的完整流程（一句话概括）

崩溃重启后，InnoDB 先根据 Redo Log 把所有”已提交但未刷盘”的修改重做一遍（保证持久性），再根据 Undo Log 把所有”崩溃时未提交”的事务回滚掉（保证原子性），最终使数据库达到一个一致状态。

这个机制就是经典的 WAL + ARIES 恢复算法 的核心思想：Redo 重做，Undo 回滚。

Q3: Redo Log 和undo log 存的是什么

Redo Log 和 Undo Log 存储的内容，本质区别在于：Redo 存的是”修改后的新值/操作”，Undo 存的是”修改前的旧值”。下面分开说明。

一、Redo Log 存的是什么

Redo Log 记录的是 “在某个数据页上做了什么修改”，属于物理逻辑日志（physiological log）。

具体内容
它记录的不是完整的行数据，而是页级别的修改操作，通常包含：

• 表空间 ID（space ID）
• 数据页号（page number）
• 页内偏移量（offset）
• 修改的具体操作（如”在偏移量 X 处写入值 Y”）

举例
假设执行 UPDATE orders SET status = 1 WHERE id = 100;，Redo Log 里记录的不是这条 SQL，而是类似：

1

在表空间 5、页号 20、偏移量 88 处，将值从 0 改为 1

关键特点

• 面向页：记录的是对数据页的物理修改。
• 只记新值：只记录”改成什么”，不关心”原来是什么”。
• 用于重做：崩溃恢复时，直接把这些修改重新应用到数据页上。
• 循环写：Redo Log 是固定大小的环形文件（如 ib_logfile0、ib_logfile1），写满后覆盖最旧的部分。

二、Undo Log 存的是什么

Undo Log 记录的是 “数据被修改前的旧版本”，属于逻辑日志。

具体内容
它记录的是反向操作所需的信息，即如何把数据改回去。具体取决于操作类型：

举例
同样是 UPDATE orders SET status = 1 WHERE id = 100;（假设原来 status = 0），Undo Log 里记录的是：

1

id = 100 这条记录的 status 字段，旧值是 0

回滚时，就用旧值 0 覆盖回去。

关键特点

• 面向行：记录的是行级别的旧数据。
• 只记旧值：记录”原来是什么”，用于回滚和 MVCC。
• 用于回滚：事务回滚或崩溃恢复时，用旧值撤销修改。
• 支持 MVCC：为一致性读提供历史版本。
• 持久化存储：存放在 Undo Tablespace 中，不是环形覆盖，而是由 Purge 线程按需清理。

三、对比总结

四、一句话记忆

Redo Log 记”改成了什么”，用于崩溃后重做；Undo Log 记”原来是什么”，用于回滚和 MVCC。

两者配合，共同保证了 InnoDB 的 ACID 特性：Redo 保证 D（持久性），Undo 保证 A（原子性） 和 I（隔离性，通过 MVCC）。

概述

连接层

MySQL的最上层是连接服务，引入了线程池的概念，允许多台客户端连接。主要工作是：连接处理、授权认证、安全防护、管理连接等。

当客户端连接到 MySQL 服务器时，服务器对其进行认证。基于用户名、原始主机信息和密码。一旦客户端连接成功，服务器会继续验证客户端是否具有执行某个特定权限（比如：是否对某张表具有某种操作权限）。

连接层为通过安全认证的接入用户提供线程，同样，在该层上可以实现基于SSL 的安全连接。连接层中还负责 MySQL Server 与客户端的通信，接受客户端的命令请求，传递 Server 端的结果信息等。

连接处理：每个客户端连接都会分配一个线程，来自客户端的所有查询都是在该线程中执行，从MySQL5.5版本开始支持线程池。

授权认证：对客户端进行身份验证，基于用户名、主机信息、密码。

安全防护：验证客户端发出某些查询的权限。

连接层为通过安全认证的用户提供线程，也支持SSL的安全连接，还负责MySQL Server与客户端的通信，接受客户端的命令请求，传递Server端处理的结果。

查看当前连接的信息：

1

show full processlist

(表格内容)

User: 客户端连接使用的用户

Host: 客户端的主机信息

DB: 客户端连接使用的库

Command: 执行命令的类别

Time: 客户端从建立连接到现在的时间

Info: 详细的描述信息

1
2
3
4
5
6
7
8
9
10

/*查看最大的连接数*/
show variables like '%max_connections%'
/*
修改最大连接数
1.修改mysql启动参数文件，永久有效
2.set global max_connections=200, 有效期从命令开始生效，当服务器意外宕机或正常重启失效。
*/

/*跟超时相关的变量*/
show variables like '%timeout%'

服务层

• 服务层用于处理核心服务，如标准的SQL接口、NoSQL接口、查询解析、SQL优化和统计、全局的和引擎依赖的缓存与缓冲器等等。
• 所有的与存储引擎无关的工作，如过程、函数等，都会在这一层来处理。该层负责MySQL关系数据库系统所有的逻辑功能。
• 在该层上，服务器会解析查询并创建相应的内部解析树，并对其完成优化，如确定查询表的顺序，是否利用索引等，最后生成相关的执行操作。
• 如果是SELECT 语句，服务器还会查询内部的缓存（Select Id, Name from User_Token where Id=1）。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能（MySQL8版本中已经将查询缓存移除，可以通过架构设计，增加分布式缓存进行替代，用户查询时先在额外的本地分布式缓存中取数据，没有的话，再请求数据库，这样比直接将缓存放在数据库侧效率更高）。

MySQL的服务层分为各种子组件：

组件一：系统管理和实用程序（Management Service & Utilities）。

包括备份恢复、安全管理、集群管理服务和工具。

组件二：SQL接口（SQL Interface）

接收客户端发送的各种 SQL 语句，比如 DML、DDL 和存储过程等，并且返回用户执行的结果。

组件三：缓存（Cache & Buffer）

主要功能是将客户端提交 给MySQL 的 Select 类 Query 请求的返回结果集 Cache 到内存中，与该 Query 的一个 Hash 值做一个对应（将Query对应的查询结果缓存起来，Query的SQL语句做一个Hash，得到一个Hash值。如果大小写、空格等会影响到Hash值的结果，比如：Select Id, Name from User_Token where Id=1和Select id, Name from User_Token where Id=1不一样的值）。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 Query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的，当然它对内存的消耗也是非常大的。

对于SELECT语句，在解析查询之前，服务器会先检查查询缓存（Query Cache） ，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。

但不推荐使用查询缓存，即大多数情况下不建议使用查询缓存。为什么呢？

原因：查询缓存的失效机制是有缺陷的，只要对一个表有更新操作（增、删、改），这个表所有的查询缓存全部清空。除非你的业务就是一张静态表，很长时间才更新一次。比如：系统配置表、菜单表。

组件四：SQL解析器（Parser）

如果缓存没有命中，SQL命令传递到解析器的时候会被解析器验证和解析，最终对 SQL 语句进行语法解析生成解析树（树形的数据结构）。

• 词法分析
  • 需要识别出SQL语句中的字符串分别是什么，各代表什么
  • 通过识别到的Select关键字就可以知道这是一个查询语句
  • 把字符串”T”识别为表名，把”Col1”，”Col2”识别为列名
• 语法分析
  • 语法解析器会根据语法规则，判断输入的SQL语句是否满足MySQL语句要求
  • 比如检查表名、列名是否正确等等。

如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，比如下面这个语句 select 少打了开头的字母“s”。

1
2
3

mysql> elect * from t where ID=1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容。

总结：

将SQL语句进行语义和语法的分析，分解成数据结构（解析树） ，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。

解析树是通过解析器来解析SQL的关键字和非关键字，SQL语句按照关键字和非关键字进行分类，生成树。

(图示：SELECT语句 -> SELECT -> Fields -> NAME, FROM -> TABLES -> USER_INFO, WHERE -> CONDITIONS -> AND -> ( > -> AGE, 20 ) 和 ( = -> AGE, 20 ))

组件五：查询优化器（Optimizer）

接下来并不是直接执行，而是会在优化器这一层进行优化。

优化器是个非常复杂的部件，它会帮我们去使用他自己认为的最好的方式去优化这条 SQL 语句，并生成一条条的执行计划。

MySQL使用 explain + sql 语句查看执行计划，该执行计划不一定完全正确但是可以参考。

1

EXPLAIN SELECT * FROM User WHERE nid = 3;

1 row in set, 1 warning (0.00 sec)

根据解析树生成最优的执行计划。MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时的优化），动态优化（运行时的优化）。

(1) 等价变换策略：
where 5=5 and a>5 —> where a>5

(2) 优化count、min、max等函数：
Inno引擎min函数只需要查找索引最左边
Inno引擎max函数只需要查找索引最又边
Count()，不需要计算，直接返回

(3) 提前终止查询：
Limit查询，获得Limit所需要的数据之后，不需要遍历后面的数据了

(4) in的优化：
MySQL针对in查询，会进行排序，再采用区分查找法去找数据。in(2,1,3) —> in(1,2,3)

(5) 条件查询：

SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求Query，根据客户端请求的 Query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 Query 语句的结果。使用的是“选取-投影-联接”策略进行查询。

1

select uid,name from User where gender = 1;

选取：先根据where语句进行选择，而不是先将表全部查询出来之后再根据gender过滤。
投影：会根据uid和name进行属性投影，而不是将属性全部取出来再进行过滤。
联接：将上面这两个查询条件连接起来生成最终的查询结果。

(6) 连接查询：
在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

1

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

• 既可以从表t1里面取出c=10的记录的ID值，再根据ID值关联到表t2，再判断t2里面d的值是否等于20。
• 也可以先从表t2里面取出d=20的记录的ID值，再根据ID值关联到t1，再判断t1里面c的值是否等于10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

组件六：执行器

工作内容：

1. 判断对这个表有没有查询权限
2. 有权限，则继续执行；如果没有，就会返回没有权限的错误
3. 调用存储引擎接口进行执行查询或其他操作
4. 最终将查询结果集返回给客户端，语句即执行完成

解析器知道了你想做什么
优化器知道该怎么做最合适
执行器去执行，担任指挥角色，告诉存储引擎做什么

1
2

mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to User 'b'@'localhost' for table 'T'

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义（创建表的时候可以执行该表对应的存储引擎，如果不指定那么默认使用InnoDB），去使用这个引擎提供的接口。

比如我们这个例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：

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

至此，这个语句就执行完成了。

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

存储引擎层

存储引擎层负责 MySQL 中数据的存储与提取，服务器中的查询执行引擎通过 API 与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。

MySQL 采用插件式的存储引擎。MySQL 为我们提供了许多存储引擎，每种存储引擎有不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。如果对于存储引擎的性能不满意，可以通过修改源码来得到自己想要达到的性能。

1

show engines

特点：

• MySQL 采用插件式的存储引擎。
• 存储引擎是针对于表的而不是针对库的（一个库中不同表可以使用不同的存储引擎），服务器通过 API 与存储引擎进行通信，用来屏蔽不同存储引擎之间的差异。
• 不管表采用什么样的存储引擎，都会在数据区，产生对应的一个的一个 frm 文件（表结构定义描述文件）

物理存储层

存储引擎底部是物理存储层，是文件的物理存储层（磁盘），包括二进制（BinLog）日志、数据文件、错误日志、慢查询日志、全日志、redo/undo 日志（事务）等。

1
2
3
4
5
6
7
8
9
10
11
12
13

/*数据目录*/
show variables like '%datadir%'

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

• mysql：MySQL系统自带的核心库，存储MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，运行过程中产生日志信息，一些帮助信息及其时区信息。
• information_schema：保存着MySQL服务器维护的其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、那些列、哪些索引。
• performance_schema：保存MySQL运行过程中的状态信息，监控服务器的各类性能指标。
• sys：通过视图的形式把performance_schema和information_schema结合起来。

运行流程

C/S通信协议

建立连接（Connectors&Connection Pool），通过客户端/服务器通信协议与MySQL建立连接。MySQL 客户端与服务端的通信方式是“半双工”。对于每一个 MySQL 的连接，时刻都有一个线程状态来标识这个连接正在做什么。

通讯机制：

• 全双工：任意时刻，客户端和服务器既可以发送数据，也可以接收数据。
• 半双工：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。
• 单工

MySQL客户端/服务端通信协议是“半双工”的，一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。

1
2
3
4
5
6
7

show VARIABLES like '%max_allowed_packet%';

+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 67108864   |
+--------------------------+------------+

以上说明目前的配置是：64M

尽可能查询简单，且只返回必须得数据，这样可以降低数据包大小和数量，避免select * ,加上 limit

查询缓存

备注：

从MySQL 5.7.20起，查询缓存已被弃用，并在MySQL 8.0中删除。

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据（命中率）。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL将缓存存放在一个引用表（类似于HashMap的数据结构），通过一个哈希值索引（针对于客户端描述信息以及SQL语句），这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

缓存管理和配置：

have_query_cache：该MySQL Server是否支持Query Cache。

query_cache_limit：MySQL能够缓存的最大查询结果，查询结果大于该值时不会被缓存。

query_cache_min_res_unit：查询缓存分配的最小块的大小(字节)。

当查询进行的时候，MySQL把查询结果保存在qurey cache中，但如果要保存的结果比较大，超过query_cache_min_res_unit的值，这时候mysql将一边检索结果，一边进行保存结果，也就是说，有可能在一次查询中mysql要进行多次内存分配的操作。适当的调节query_cache_min_res_unit可以优化内存。

query_cache_size：为缓存查询结果分配的内存的大小，单位是字节，且数值必须是1024的整数倍。默认值是1M，如0即禁用查询缓存。

query_cache_type：设置查询缓存类型，默认为OFF。设置GLOBAL值可以设置后面的所有客户端连接的类型。客户端以设置SESSION值以影响他们自己对查询缓存的使用。

下面的表显示了可能的值：

query_cache_wlock_invalidate：如果某个表被锁住，是否返回缓存中的数据，默认关闭，也是建议的。

缓存规则：

• MySQL缓存机制简单的说就是缓存sql文本及查询结果（缓存的结构类似于HashMap结构，Key是客户端特征以及SQL语句综合之后得到的Hash值，Value是查询结果），如果运行完全相同的SQL，服务器直接从缓存中取到结果，而不需要再去解析和执行SQL。
• 如果表中任何数据或是结构发生改变，包括INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等，那么使用这个表的所有缓存查询将不再有效，查询缓存中值相关条目被清空（保证缓存数据与物理数据保持一致性，缓存与数据源一致性：本地缓存和分布式缓存、分布式缓存与数据库、缓冲区与物理文件）。
• 将查询语句和结果集返回到内存，下次再查直接从内存中取；
• Sessions共享，一个Client查询的缓存结果，另一个Client也可以使用；
• SQL必须完全一致才会导致Cache命中；
• 不确定的函数将永远不会被Cache, 比如current_date, now等；
• 太大的result set不会被Cache(< query_cache_limit)；
• MySQL缓存在分库分表环境下是不起作用的；
• 执行SQL里有触发器，自定义函数时，MySQL缓存也是不起作用的；
• 在表的结构或数据发生改变时，基于该表相关Cache立即全部失效。

缓存机制中的内存管理：

MySQL Query Cache 使用内存池技术，自己管理内存释放和分配，而不是通过操作系统。内存池使用的基本单位是变长的Block,用来存储类型、大小、数据等信息；一个result set的cache通过链表把这些block串起来。block最短长度为query_cache_min_res_unit。

当服务器启动的时候，会初始化缓存需要的内存，是一个完整的空闲块。当查询结果需要缓存的时候，先从空闲块中申请一个数据块为参数query_cache_min_res_unit配置的空间，即使缓存数据很小，申请数据块也是这个，因为查询开始返回结果的时候就分配空间，此时无法预知结果多大。

分配内存块需要先锁住空间块，所以操作很慢，MySQL会尽量避免这个操作，选择尽可能小的内存块，如果不够，继续申请，如果存储完时有空余则释放多余的。

但是如果并发的操作，余下的需要回收的空间很小，小于query_cache_min_res_unit，不能再次被使用，就会产生碎片。

优点：
Query Cache的查询，发生在MySQL接收到客户端的查询请求、查询权限验证之后和查询SQL解析之前。

也就是说，当MySQL接收到客户端的查询SQL之后，仅仅只需要对其进行相应的权限验证之后，就会通过Query Cache来查找结果，甚至都不需要经过Optimizer模块进行执行计划的分析优化，更不需要发生任何存储引擎的交互。

由于Query Cache是基于内存的，直接从内存中返回相应的查询结果，因此减少了大量的磁盘I/O和CPU计算，导致效率非常高。

缺点：

• MySQL会对每条接收到的SELECT类型的查询进行Hash计算，然后查找这个查询的缓存结果是否存在。虽然Hash计算和查找的效率已经足够高了，一条查询语句所带来的开销可以忽略，但一旦涉及到高并发，有成百上千条查询语句时，Hash计算和查找所带来的开销就必须重视了。
• Query Cache的失效问题。如果表的变更比较频繁，则会造成Query Cache的失效率非常高。表的变更不仅仅指表中的数据发生变化，还包括表结构或者索引的任何变化。
• 查询语句不同，但查询结果相同的查询都会被缓存，这样便会造成内存资源的过度消耗。查询语句的字符大小写、空格或者注释的不同，Query Cache都会认为是不同的查询（因为他们的Hash值会不同）。
• 相关系统变量设置不合理会造成大量的内存碎片，这样便会导致Query Cache频繁清理内存。

对性能的影响：

• 读查询开始之前必须检查是否命中缓存。
• 如果读查询可以缓存，那么执行完查询操作后，会查询结果和查询语句写入缓存。
• 当向某个表写入数据的时候，必须将这个表所有的缓存设置为失效，如果缓存空间很大，则消耗也会很大，可能使系统僵死一段时间，因为这个操作是靠全局锁操作来保护的。
• 对InnoDB表，当修改一个表时，设置了缓存失效，但是多版本特性会暂时将这修改对其他事务屏蔽，在这个事务提交之前，所有查询都无法使用缓存，直到这个事务被提交，所以长时间的事务，会大大降低查询缓存的命中。

生产如何设置缓存：

MySQL中的Query Cache是一个适用较少情况的缓存机制。如果你的应用对数据库的更新很少，那么QC将会作用显著。比较典型的如博客系统，一般博客更新相对较慢，数据表相对稳定不变，这时候QC的作用会比较明显。

但是一个更新频繁系统。Query Cache缓存的作用是很微小的，如果应用层能够实现缓存，将可以忽略Query Cache的效果。所以，如果经常有更新的系统，想要获得较高tps的话，建议一开始就关闭Query Cache

查询缓存的替代方案MySQL查询缓存工作的原则是：执行查询最快的方式就是不去执行，但是查询仍然需要发送到服务器端，服务器也还需要做一点点工作，如果对于某些查询完全不需要与服务器通信效果会如何呢，这时客户端缓存可以很大程度上分担MySQL服务器的压力。

性能消耗：

在任何的写操作时，MySQL会将对应表的所有缓存都设置为失效。

如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。

查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

1. 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本节讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

1. 用多个小表代替一个大表，注意不要过度设计
2. 批量插入代替循环单条插入
3. 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
4. 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

分布式缓存

MySQL8版本中已经将查询缓存移除，可以通过架构设计，增加分布式缓存进行替代，用户查询时先在额外的本地分布式缓存中取数据，没有的话，再请求数据库，这样比直接将缓存放在数据库侧效率更高

小结

语法解析和预处理

MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。

这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等。

流程：词法分析 -> 语法分析 -> 解析树 -> 预处理 -> 检查权限 -> 新解析树）

查询优化器

根据解析树生成最优的执行计划。

1
2
3
4
5
6
7
8
9
10
11
12
13

      +-----------------------------------------------------------------------------+
      |                            优化器 (Optimizer)                               |
      |                                                                             |
      |   +-------------------+       +-----------------------------------------+   |
----->|   |                   |       |               优化器模式                  |   |
      |   |     查询重写       | ----> |                                          |   |
      |   |                   |       |    +---------------+--------------+     |   |
      |   +-------------------+       |    |      CBO      |      RBO     |     |   |
      |                               |    +---------------+--------------+     |   |
      |                               +-----------------------------------------+   |
      |                                                                             |
      |     逻辑查询优化                        物理查询优化                        |
      +-----------------------------------------------------------------------------+

1. 逻辑查询优化：
   • 对SQL语句做一些等价交换、对条件表达式进行等价谓词重写、条件顺序的调整、条件简化、视图重写、子查询、连接查询优化。
2. 物理查询优化
   • CBO：Cost-Based Optimizer，基于代价的优化，根据模型计算出各个可能的执行计划的代价，选择代价最小的一个，它会利用数据库里面的统计信息来做判断，动态。
   • RBO：Rule-Based Optimizer，基于规则的优化，主要基于一些预置的规则，对查询进行优化。

查询执行引擎

查询执行引擎负责执行 SQL 语句（选择对应的存储引擎去执行），此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。
若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存中，以后若有相同的 SQL 语句执行则直接返回结果。

• 如果开启了查询缓存，先将查询结果做缓存操作
• 返回结果过多，采用增量模式返回

单表优化

避免全表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 需求一：查询所有名字中包含李的用户姓名和手机号,并根据user_id字段排序。
EXPLAIN SELECT
    NAME,
    mobile
FROM user_contacts WHERE NAME LIKE '%李%' ORDER BY user_id;

--  添加联合索引，包含要查询的字段，实现索引覆盖，一并解决 like %在左边的问题。
ALTER TABLE user_contacts ADD INDEX idx_nm(NAME,mobile,user_id);

-- 删除索引
DROP INDEX idx_nm ON user_contacts;

-- 优化后，extra字段中包含filesort文件排序，还需要继续优化
-- 这里用user_id 字段排序，所以将user_id 放在联合索引的最前面
ALTER TABLE user_contacts ADD INDEX idx_nm(user_id,NAME,mobile);

联合索引不生效

1
2
3
4
5
6
7
8
9

-- 需求二：统计手机号是135、136、186、187开头的用户数量。
EXPLAIN SELECT
    COUNT(*)
FROM user_contacts
WHERE mobile LIKE '135%' OR mobile LIKE '136%' OR mobile LIKE '186%' OR mobile LIKE '187%';

-- extra = using where + using index,表示查询的列被索引覆盖了，但是无法通过索引直接获取数据。
-- 联合索引无法生效，根据mobile单独建立一个索引
ALTER TABLE user_contacts ADD INDEX idx_m(mobile);

查询大范围数据如何减少耗时

1
2
3
4
5
6
7
8
9
10
11
12
13

-- 需求三：获取用户通讯录表第10万条数据开始后的100条数据。
EXPLAIN SELECT * FROM user_contacts LIMIT 4000000,10000;

-- 1. 通过索引进行分页，比如id字段如果是自增的话，就可以根据查询的记录数算出id的范围
EXPLAIN SELECT * FROM user_contacts WHERE id >= 100001 LIMIT 100;

-- 2.使用了子查询
-- 首先 定位偏移位置的id
SELECT id FROM user_contacts LIMIT 100000,1;

-- 根据获取到的id 向后查询
EXPLAIN SELECT * FROM user_contacts
WHERE id >= (SELECT id FROM user_contacts LIMIT 100000,1) LIMIT 100;

多表优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- 用户认证表：mob_auth,有11万数据，保存的是通过手机认证后的用户数据。
-- 与其他表关联的字段 user_id
SELECT COUNT(*) FROM mob_auth;

-- 紧急联系人表：ugncy_cntct_psn ,大概有22万条数据，用户注册成功后，保存填写的紧急联系人信息
-- 关联字段：user_id
SELECT COUNT(*) FROM ugncy_cntct_psn;

-- 借款申请表：loan_apply, 接近11万条数据，保存的是每次用户申请借款时填写的信息
-- 关联字段：user_id
SELECT COUNT(*) FROM loan_apply;

-- 需求1：查询所有认证用户的手机号以及认证用户的紧急联系人的姓名与手机号信息。
EXPLAIN SELECT
    ma.`mobile` '认证用户的手机号',
    ucp.`cntct_psn_name` '紧急联系人的姓名',
    ucp.`cntct_psn_mob` '紧急联系人的手机号'
FROM mob_auth ma
LEFT JOIN ugncy_cntct_psn ucp ON ma.`user_id` = ucp.`user_id`;

-- 优化：为 mob_auth的user_id字段添加索引
ALTER TABLE mob_auth ADD INDEX idx_uid(user_id);
-- 上面的索引没有生效，因为mob表是驱动表，驱动表建立索引也不会生效。
-- 一般情况下：左连接，左表是驱动表，右表被驱动表，右连接反之，我们应该在被驱动表连接字段上建立索引
ALTER TABLE ugncy_cntct_psn ADD INDEX idx_uid(user_id);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 需求二：获取紧急联系人数量>8的，用户手机号信息。
-- 1.获取紧急联系人 > 8的用户的id
SELECT user_id,COUNT(user_id) c FROM ugncy_cntct_psn GROUP BY user_id
HAVING c > 8;

-- 2.获取认证用户的id和手机号
SELECT user_id,mobile FROM mob_auth;

-- 3.将上面两条SQL进行连接
EXPLAIN SELECT
    ucp.user_id,
    COUNT(ucp.user_id) c,
    m.user_id,
    m.mobile
FROM ugncy_cntct_psn ucp INNER JOIN (SELECT user_id,mobile FROM mob_auth) m
ON ucp.`user_id` = m.user_id
GROUP BY ucp.user_id HAVING c > 8 ORDER BY NULL;

– 需求3：获取所有智能审核的用户手机号和申请额度、申请时间、审核额度。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 需求3：获取所有智能审核的用户手机号和申请额度、申请时间、审核额度。

EXPLAIN SELECT
    ma.`mobile` '用户认证手机号',
    la.`apply_limit` '申请额度',
    la.`apply_time` '申请时间',
    la.`audit_limit` '审核额度'
FROM mob_auth ma INNER JOIN loan_apply la ON ma.id = la.`mob_auth_id`
WHERE la.audit_mod_cde = 2;

-- 选择性低（重复率高）的字段不适合创建索引
ALTER TABLE loan_apply ADD INDEX idx_amc(audit_mod_cde);

SELECT COUNT(*) FROM loan_apply la WHERE la.audit_mod_cde = 2;
-- 如果必须要通过选择性低的状态字段查询的话，可以根据业务需求，添加一个日期条件
EXPLAIN SELECT
    ma.`mobile` '用户认证手机号',
    la.`apply_limit` '申请额度',
    la.`apply_time` '申请时间',
    la.`audit_limit` '审核额度'
FROM mob_auth ma INNER JOIN loan_apply la ON ma.id = la.`mob_auth_id`
WHERE la.`apply_time` BETWEEN '2017-01-01 00:00:00' AND '2017-01-01 23:59:59'
AND la.audit_mod_cde = 2;

什么是成本？

在MySQL中，一条查询语句的执行成本实际上是由两部分构成的：

• I/O 成本
  • MyISAM 和 InnoDB都需要将数据和索引存储到磁盘，当进行查询时，就需要把数据或者索引加载到内存中。从磁盘到内存这个加载过程，损耗的时间，我们称之为 I/O成本。
• CPU成本
  • 读取记录的时候，需要检测记录是否满足对应的搜索条件、对结果集进行排序等等操作发生的损耗都称之为 CPU成本。
• 成本常数
  • I/O成本常数：1.0 ，MySQL规定读取一个页面花费的成本默认是1.0
  • CPU成本常数：0.2，读取以及检测一条记录是否符合搜索条件的成本，默认是0.2

    单表查询成本

MySQL中找出成本最低方案的过程，大致如下：

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

根据搜索条件，找出所有可能使用的索引。

1
2
3
4
5
6
7
8

SELECT * FROM orders
    WHERE order_no
    IN ('DD00_6S', 'DD00_9S', 'DD00_10S')
    AND expire_time> '2021-03-22 18:28:28'
    AND expire_time<= '2021-03-22 18:35:09'
    AND insert_time > expire_time
    AND order_note LIKE '%7****排1%'
    AND order_status = 0;

分析一下上面SQL中的涉及到的搜索条件：

1) IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)这个搜索条件，可以使用二级索引：idx_order_no。

2) AND expire_time > ‘2021-03-22 18:28:28’ AND expire_time <= ‘2021-03-22 18:35:09’这个搜索条件可以使用二级索引 idx_expire_time。

3) AND insert_time > expire_time： 这个搜索条件索引列没有和常数进行比较，所以就不能使用索引。

4) AND order_note LIKE ‘%7排1%’： order_note是有索引的，但是由于like查询 % 出现在了左边，不符合最左前缀原则，索引索引就失效。

5) AND order_status = 0; order_status 是处在联合索引的第二个位置上，所以索引是无法使用的。

1
2
3
4
5
6
7
8

EXPLAIN SELECT * FROM orders
    WHERE order_no
    IN ('DD00_6S', 'DD00_9S', 'DD00_10S')
    AND expire_time> '2021-03-22 18:28:28'
    AND expire_time<= '2021-03-22 18:35:09'
    AND insert_time > expire_time
    AND order_note LIKE '%7****排1%'
    AND order_status = 0;

计算全表扫描的代价

对于InnoDB存储引擎来说，全表扫描指的就是把聚簇索引中的记录和给定的搜索条件依次进行比较，把符合条件的记录加入到结果集中。要把聚簇索引对应的页面加载到内存中，然后在检测记录是否符合条件。

查询成本 = I/O成本+CPU 的成本,所以就计算全表扫描的代价需要两个信息：

1) 聚簇索引占用的页面数
2) 该表的记录数

1

show table status like 'orders'\G

1) rows :表示的是表中的记录数，值为10591。而实际的记录数为10567。在InnoDB中 rows是一个估值。但是计算成本的时候，按照 show table status like ‘orders’\G显示的结果来进行计算。

2) Data_length :表示当前表 占用的存储空间的字节数。对于InnoDB来说，聚簇索引占用的存储空间的大小就是该值的大小。

• Data_length = 聚簇索引的页面的数量 x 每个页面的大小。
• 聚簇索引页面数量 = 1589248 / 16 / 1024 = 97

得到上两个值之后，接下来计算一下全表扫描的成本：

• I/O成本： 97 x 1.0 + 1.1 = 98.1;
  • 97表示聚簇索引页面数
  • 1.0 表示加载一个页面的成本常数
  • 1.1 是一个微调值
• CPU成本： 10591 x 0.2 + 1.0 = 2119.2
  • 10591表中记录数，但是是估算值
  • 0.2 访问一条记录所需的成本常数
  • 1.0 微调值。
• I/O + CPU，全表扫描所需总成本 = 2217.3

计算使用不同的索引，执行查询的代价

使用idx_expire_time执行查询的成本分析

对应的搜索条件为：

1

AND expire_time> '2021-03-22 18:28:28' AND expire_time<= '2021-03-22 18:35:09'

上面的条件的区间范围是 2021-03-22 18:28:28 , 2021-03-22 18:35:09

使用idx_expire_time进行搜索会使用 二级索引 + 回表查询的方式，MySQL计算这种查询的成本的依赖两个方面的数据：

1) 范围区间的数量

• 查询优化器认为 读取索引一个范围区间的I/O 成本和读取一个页是相同。
• 范围区间的二级索引付出的I/O 成本： 1 x 1.0 = 1.0;

2) 需要回表的记录数

对于本例来说，就是要计算2021-03-22 18:28:28到2021-03-22 18:35:09这个范围中包含多少二级索引记录，计算过程：

根据MySQL的算法，测出的该二级索引的范围区间的记录数大约是：
AND expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’ 之间大约有38条记录。

读取38条二级索引记录需要付出CPU成本：

• 38 x 0.2 + 0.01 = 7.61

在通过二级索引获取到记录之后，还要干两件事情：

1. 根据这些记录的主键值，到聚簇索引中做回表操作：
   • 二级索引之间有多少记录，就需要多少次回表，有多少次回表，就需要进行多少次的页面IO。
   • 上面的执行计划中预计有38条需要进行回表，回表带来的I/O成本： 38 x 1.0 = 38

2. 回表操作后，得到完整的用户记录，然后在检测其他的搜索条件是否成立。

   • 找到完整的记录之后，再检测

     1 2 3 4

     IN ('DD00_6S', 'DD00_9S', 'DD00_10S') AND insert_time > expire_time AND order_note LIKE '%7****排1%' AND order_status = 0;

   • 二级索引区间范围中共有38条数据，对应了聚簇索引汇中38条完整记录，读取并且检测是否符合其余查询条件的CPU成本： 38 x 0.2 = 7.6

本例中，使用idx_expire_time索引执行查询的成本如下：

• I/O成本： 38 x 1.0 + 1.0 = 39
• CPU成本： 38 x 0.2 +0.01 + 38 x0.2 = 15.21 (读取二级索引记录的成本 + 读取并检查回表聚簇索引的记录成本)

idx_expire_time执行查询的总成本就是： 39 + 15.21 = 54.21

补充

1. 为什么这里计算I/O 没有微调参数？
   I/O 相关的微调值 只有在全量表扫描时才会使用，当前两次I/O成本计算直接使用页数 × 1.0 来计算
2. 为什么这里计算CPU 有的加微调参数，有的没加？
   上面两处CPU成本计算
   +0.01 是 MySQL 给”读取索引记录”这一操作附带的固定启动成本，只挂在 cpu_index_tuple_cost 的计算路径上
   而”回表读取聚簇索引完整记录”走的是 cpu_tuple_cost 路径，公式里本来就没有这个常数项
   小结—>不同场景计算公式不同

使用idx_order_no执行查询的成本分析

idx_order_no 对应的搜索条件是

1

IN ('DD00_6S', 'DD00_9S', 'DD00_10S')

上面的条件是三个单点区间：

• 访问这三个范围区间的二级索引付出的I/O成本： 3 x 1.0 = 3
• 需要回表的记录数：为58

• 读取这些二级索引记录的CPU成本： 58 x 0.2 + 0.01 = 11.61
  • 根据这些记录的主键值到聚簇索引中做回表。所需的I/O成本： 58 x 1.0 = 58.0
  • 获取到完整记录之后，还需要用其他条件进行检查过滤， CPU成本: 58 x 0.2 = 11.6
• 所以本例中的成本总值为：
  • IO成本： 3 + 58 x 1.0 = 61 (范围区间数量 + 预估二级索引的记录数)
  • CPU成本： 58 x 0.2 + 0.01 + 58 x 0.2 = 23.21
  • 总成本： 61 + 23.21 = 84.21

对比各种方案，找出成本最低的

• 全表扫描：2117.3
• 使用idx_expire_time索引：54.21
• 使用idx_order_no索引：84.21

最终选择 idx_expire_time。

连接查询成本

驱动表的扇出值计算

连接查询成本的构成：

1) 单次查询驱动表的成本
2) 多次查询被驱动表的成本

对驱动表进行查询后，得到的记录条数称之为驱动表的扇出。