mysql 高级

第1篇 高级架构篇

第1章 Linux环境下MySQL的安装与使用

1 安装并启动好两台虚拟机

掌握克隆虚拟机的操作：

  • mac 地址

  • 主机名

  • ip 地址

  • UUID

5 字符集的相关操作

5.2 各级别的字符集

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

  • 服务器级别

  • 数据库级别

  • 表级别

  • 列级别

show variables like 'character%';


character_set_server：服务器级别的字符集
character_set_database：当前数据库的字符集
character_set_client：服务器解码请求时使用的字符集
character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
character_set_results：服务器向客户端返回数据时使用的字符集

4个级别字符集和比较规则的联系：

  • 如果 创建或修改列 时没有显式的指定字符集和比较规则，则该列 默认用表 的字符集和比较规则。

  • 如果 创建表时 没有显式的指定字符集和比较规则，则该表 默认用数据库 的字符集和比较规则。

  • 如果 创建数据库时 没有显式的指定字符集和比较规则，则该数据库 默认用服务器 的字符集和比较规则。

5.3 字符集和比较规则

  1. utf8 与 utf8mb4

  • utf8mb3：阉割过的 utf8 字符集，只用 1~3 个字节表示字符。

  • utf8mb4：正宗的 utf8 字符集，使用 1~4 个字节表示字符。

  1. 比较规则

5.4 请求到响应过程中字符集的变化

  1. 使用操作系统的字符集编码请求字符串。（客户端）

  2. 从 character_set_client 转换为 character_set_connection。服务器接收到客户端发送的请求其实是一串二进制的字节，它会认为这串字节采用的字符集是 character_set_client，然后把这串字节转换为 character_set_connection 编码。

  3. 从 character_set_connection 转换为具体的列使用的字符集。

  4. 将查询结果从具体的列使用的字符集转换为 character_set_results。

  5. 使用操作系统的字符集解码响应字节串。（客户端）

6 SQL 大小写规范

SHOW VARIABLES LIKE '%lower_case_table_names%'

lower_case_table_names 参数值的设置：

  • 默认为0，大小写敏感。

  • 设置为1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于 sql 语句都是转换为小写对表和数据库进行查找。

  • 设置为2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。

两个平台SQL大小写的区别具体来说：

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：
1.数据库名、表名、表的别名、变量名是严格区分大小写的。
2.关键字、函数名称在 SQL 中不区分大小写。
3.列名（或字段名）与列的别名在所有情况下均是忽略大小写的。


MySQL在Windows的环境下全部不区分大小写

7 sql_mode 的合理设置

  • 宽松模式

  • 严格模式

第2章 MySQL的数据目录

MySQL 的主要目录结构

find / -name mysql

MySQL 服务器程序在启动时会到文件系统的某个目录下加载一些文件，之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中，这个目录就称为 数据目录。

MySQL 把数据都存到哪个路径下呢？其实 数据目录 对应着一个系统变量 datadir。

  1. MySQL 的数据目录：/var/lib/mysql

  2. 相关命令目录：/usr/bin（mysqladmin、mysqlbinlog、mysqldump命令等） 和 /usr/sbin。

  3. 配置文件目录：/usr/share/mysql8.0（命令及配置文件），/etc/mysql（如my.cnf）。

数据库和文件系统的关系

  • mysql

    MySQL 系统自带的核心数据库，它存储了 MySQL 的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

  • information_schema

    MySQL 系统自带的数据库。这个数据库保存着 着MySQL服务器 维护的所有其他数据库的信息 ，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为 元数据 。在系统数据库 information_schema 中提供了一些以innodb_sys 开头的表，用于表示内部系统表。

  • performance_schema

    MySQL 系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以用来 监控 MySQL 服务的各类性能指标 。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。

  • sys

    MySQL 系统自带的数据库，这个数据库主要是通过 视图 的形式把 information_schema 和 performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。

  1. InnoDB 存储引擎模式

  • 表结构：表名.frm。MySQL 8.0 中表结构、数据和索引合并到 表名.bid 中。

  • 表中数据和索引：

    • 系统表空间

    • 独立表空间

  1. MyISAM 存储引擎模式

  • 表结构：表名.frm。

  • 表中数据和索引：该存储引擎的数据和索引是分开存放的。

第3章 用户与管理权限

1 用户管理

2 权限管理

2.1 权限列表

MySQL 到底能有哪些权限呢？

show privileges;

  1. CREATE 和 DROP 权限：可以创建新的数据库和表，或删除（移掉）已有的数据库和表。

  2. SELECT、INSERT、UPDATE 和 DELETE 权限：允许在一个数据库现有的表上实施操作。

  3. SELECT 权限：只有在它们真正的从一个表中检索行时才被用到。

  4. INDEX 权限：允许创建或删除索引，INDEX 适用于已有的表。

  5. ALTER 权限：可以使用 ALTER TABLE 来更改表的结构和重命名表。

  6. CREATE ROUTINE 权限：用来创建保存的程序（函数和程序），ALTER ROUTINE 权限用来更改和删除保存的程序，EXECUTE 权限 用来执行保存的程序。

  7. GRANT 权限：允许授权给其他用户，可用于数据库、表和保存的程序。

  8. FILE 权限：使用的用户可以使用 LOAD DATE INFILE 和 SELECT ... INTO OUTFILE 语句读或写服务器上的文件，任何被授予 FILE 权限的用户都能够读写 MySQL 服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。

2.3 授予权限

  • 角色赋予用户给用户授权

  • 直接给用户授权

2.4 查看权限

  • 查看当前用户权限

    SHOW GRANTS;
#或
SHOW GRANTS FOR CURRENT_USER;
#或
SHOW GRANTS FOR CURRENT_USER();

  • 查看某用户的全局权限

    SHOW GRANTS FOR 'user'@'主机地址';

3. 权限表

MySQL 服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库汇总。

3.1 user 表

user 表记录 用户账号和权限 信息。

这些字段可分为4类：

  1. 范围列（或用户列）

  2. 权限列

  3. 安全列

  4. 资源控制列

3.2 db 表

  1. 用户列

  2. 权限列

3.3 tables_priv 表和 columns_priv 表

  • tables_priv 表用来对表设置操作权限。

  • columns_priv 表用来对表的某一列设置权限。

3.4 procs_priv 表

procs_priv 表可以对存储过程和存储函数设置操作权限。

5.角色管理

引入角色的目的是 方便管理拥有相同权限的用户 。恰当的权限设定，可以确保数据的安全性，这是至关重要的。

6.配置文件的使用

6.1 配置文件格式

与在命令行中指定启动选项不同的是，配置文件中的启动选项被划分为若干个组，每个组有一个组名，用中括号 [] 括起来。

6.2 启动命令与选项组

配置文件中不同的选项组是给不同的启动命令去使用的。不过有两个选项组比较特别：

  • [server] 组下面的启动选项将作用于 所有的服务器 程序。

  • [client] 组下面的启动选项将作用于 所有的客户端 程序。

下面是启动命令能够读取到的选项组有哪些：

启动命令 类别 能读取的组
mysqld 启动服务器 [mysqld]、[server]
mysqld.safe 启动服务器 [mysqld]、[server]、[mysqld.safe]
mysql.server 启动服务器 [mysqld]、[server]、[mysqld.server]
mysql 启动客户端 [mysql]、[client]
mysqladmin 启动客户端 [mysqladmin]、[client]
mysqldump 启动客户端 [mysqldump]、[client]

6.4 同一个配置文件中多个组的优先级

我们说同一个命令可以访问文件中的多个组，比如 mysqld 可以访问 [mysqld]、[server] 组，如果在同一个配置文件汇总，在这些组中出现了同样的配置项，将以最后一个组中的启动选项为准。

6.5 命令行和配置文件中启动选项的区别

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

第4章 逻辑架构

2 SQL执行过程

2.1 MySQL 中的 SQL 执行过程

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

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

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

  4. 执行器：在执行之前需要判断该用户是否 具备权限 。

2.2 MySQL8 中 SQL 执行原理

  1. 确认 profiling 是否开启
select @@profiling;
show varibales like 'profiling';

没开启的需要开启 profiling。

  1. 多次执行相同SQL查询

  2. 查看 profiles

show profiles;
  1. 查看 profile
show profile for query 7;

2.5 Oracle 中的 SQL 执行流程（了解）

Oracle 中采用了 共享池 来判断 SQL 语句是否存在缓存和执行计划，通过这一步我们可以知道应该采用硬解析还是软解析。

3 数据库缓冲池（buffer pool）

InnoDB 存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面（包括读页面、写页面、创建新页面等操作）。在真正访问页面之前，需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才可以访问。

3.2 缓冲池如何读取数据

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

3.4 多个 Buffer Pool 实例

Buffer Pool 本质是 InnoDB 向操作系统申请的一块 连续的内存空间，在多线程环境下，访问 Buffer Pool 中的数据都需要 加锁 处理。在 Buffer Pool 特别大的时候，我们可以把它们拆分成若干个小的 Buffer Pool，每个 Buffer Pool 都称为一个 实例，它们都是独立的，独立的去申请内存空间，独立的管理各种链表。

我们可以在服务器启动的时候通过设置 innnodb_buffer_pool_instance 的值来修改 Buffer Pool 实例的个数。

3.5 引申问题

黑盒下的更新数据流程

当我们查询数据的时候，会先去 Buffer Pool 中查询。如果 Buffer Pool 中不存在，存储引擎会先将磁盘加载到 Buffer Pool 中，然后将数据返回给客户端；同理，当我们更新某个数据的时候，如果这个数据不存在于 Buffer Pool，同样会先将数据加载进来，然后修改内存的数据，被修改过得数据会在之后统一刷入磁盘。

问：还没来得及将数据输入磁盘 MySQL 就挂了怎么办？

答：Redo Log & Undo Log

第5章 存储引擎

1 查看存储引擎

show engines\G

4 引擎介绍

4.1 InnoDB 引擎：具备外键支持功能的事务存储引擎

数据文件结构：（在《第02章_MySQL数据目录》章节已讲）

  • 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）

  • 表名.ibd 存储数据和索引

4.2 MyISAM 引擎：主要的非事务处理存储引擎

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级锁、外键 ，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

数据文件结构：（在《第02章_MySQL数据目录》章节已讲）

  • 表名.frm 存储表结构

  • 表名.MYD 存储数据 (MYData)

  • 表名.MYI 存储索引 (MYIndex)

针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高。

4.3 Archive 引擎：用于数据存档

4.4 Blackhole 引擎：丢弃写操作，读操作会返回空内容

4.5 CSV 引擎：存储数据时，以逗号分隔各个数据项

4.6 Memory 引擎：置于内存的表

Memory 采用的逻辑介质是 内存 ， 响应速度很快 ，但是当mysqld守护进程崩溃的时候 数据会丢失 。

4.7 Federated 引擎：访问远程表

4.8 Merge引擎：管理多个MyISAM表构成的表集合

4.9 NDB引擎：MySQL集群专用存储引擎

5 MyISAM 和 InnoDB

对比项 MyISAM InnoDB
外键 不支持 支持
事务 不支持 支持
行表锁 表锁 行锁
缓存 只缓存索引，不缓存真实数据 不仅缓存索引，还要缓存真实数据，对内存要求较高
自带系统表使用 Y N
关注点 性能：节省资源、消耗小、简单业务 事务：并发写、事务、更大资源
默认安装 Y Y
默认使用 N Y

第2篇 索引及调优篇

第6章 索引的数据结构

1 为什么使用索引

目的就是为了 减少磁盘 I/O 的次数，加快查询速率。

2 索引及其优缺点

2.1 索引概述

索引的本质：索引是数据结构。这些数据结构以某种方式指向数据。

索引是在存储引擎中实现的。存储引起可以定义每个表的 最大索引数 和 最大索引长度。

3 InnoDB 中索引的推演

3.3 常见索引概念

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

1. 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的 索引即数据，数据及索引。

术语“聚簇”表示数据航和相邻的键值聚簇的存储在一起。

限制：

  • 对于 MySQL 数据库目前只有 InnoDB 数据引擎支持聚簇索引，而 MyISAM 并不支持聚簇索引。

  • 由于数据物理存储排序方式只能有一种，所以每个 MySQL 的表只能有一个聚簇索引。一般情况下就是该表的主键。

  • 如果没有定义主键，Innodb 会选择 非空的唯一索引 代替。如果没有这样的索引，Innodb 会隐式的定义一个主键来作为聚簇索引。

  • 为了充分利用聚簇索引的聚簇特性，所以 innodb 表的主键列尽量选择有序的顺序 id，而不建议用无序的 id，比如 UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长。

2. 二级索引（辅助索引、非聚簇索引）

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

聚簇索引的 叶子节点 存储的就是我们的 数据记录，非聚簇索引的叶子节点存储的是 数据位置。非聚簇索引不会影响数据表的物理存储顺序。

3. 联合索引（非聚簇索引）

3.4 InnoDB 中 B+ 索引的注意事项

  1. 根页面位置万年不动

  2. 内节点中目录项记录的唯一性

  3. 一个页面最少存储2条记录

4 MyISAM 中的索引方案

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

4.3 MyISAM 与 InnoDB 对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区别：

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

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

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

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

第7章 InnoDB 数据存储结构

1 数据库的存储结构：页

本章剖析 InnoDB 存储引擎的数据存储结构。

1.1 磁盘与内存交互基本单位：页

InnoDB 将数据划分为若干个页，InnoDB 中页的大小默认为 16KB。

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

1.2 页结构概述

页a、页b、页c ... 页 n 这些页可以 不在物理结构上相连，只要通过 双向链表 相关联即可。每个数据页中的记录会按照主键值从小到大的顺序组成一个 单向链表，每个数据页都会为存储在它里面的记录生成一个 页目录，在通过主键查找某条记录的时候可以在页目录中 使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

2 页的内部结构

页如果按类型划分的话，常见的有 数据页（保存 B+ 树节点）、系统页、Undo 页 和 事务数据页 等。

数据页的页结构：

名称 占用大小 说明
File Header 38字节 文件头，描述页的信息
Page Header 56字节 页头，页的状态信息
Infimum + Supremum 26字节 最大和最小记录，这是两个虚拟的行记录
User Records 不确定 用户记录，存储行记录内容
Free Space 不确定 空闲记录，页中还没有被使用的空间
Page Directory 不确定 页目录，存储用户记录的相对位置
File Trailer 8字节 文件尾，校验页是否完整

3 InnoDB 行格式（或记录格式）

在创建或修改表的语句中指定行格式。

COMPACT 行格式

  • 变长字段长度列表

    Compact 行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。

  • NULL 值列表

    Compact 行格式会把可以为 NULL 的列同一管理起来，存在一个标记为 NULL 值列表中。

  • 记录头信息

  • 记录的真实数据

    记录的真实数据除了我们自己定义的列的数据以外，还会有三个隐藏列。

Dynamic 和 Compressed 行格式

行溢出：一个页的大小一般是 16 KB，也就是 16384 字节，而一个 VARCHAR(M) 类型的列就最多可以存储 65535 个字节，这样就可能出现一个页存放不了一条记录，这种现象称为行溢出。

在 Compact 和 Reduntant 行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中进行分页存储，然后记录的真实数据处用 20 个字节存储指向这些页的地址。

  • Compressed 和 Dynamic 两种记录格式对于存放在 BLOB 中的数据采用了完全的行溢出的方式。如图，在数据页中只存放 20 个字节的指针（溢出页的地址），实际的数据都存放在 Off Page（溢出页）中。

  • Compact 和 Redundant 两种格式会记录的真实数据存储一部分数据（存放768个前缀字节）。

Redundant 行格式

4 区、段与碎片区

4.1 区

引入 区 的概念，一个区就是在物理位置上连续的 64个页。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照 区为单位分配。

4.2 段

叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的区的集合就算是一个段。

段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念，更精确的应该是 某些零散的页面 以及 一些完整的区 的集合。

4.3 碎片区

4.4 区的分类

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

  • 空闲的区(FREE)：现在还没有用到这个区的任何页面。

  • 有剩余空间的碎片区(FREE_FRAG)：表示碎片区中还有可用的页面。

  • 没有剩余空间的碎片区(FULL)FRAG)：表示碎片区中的所有页面都被使用，没有空闲页面。

  • 附属于某个段的区(FSEG)：每一个索引都可以分为叶子节点和非叶子节点。

5 表空间

表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。

表空间是一个 逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。表空间数据库是由一个或多个表空间组成，表空间从管理上可以划分为 系统表空间(System tablespace)、独立表空间(File-per-table tablespace)、撤销表空间(Undo Tablespace) 和 临时表空间(Temporary Tablespace 等。

第8章 索引的创建与设计原则

1 索引的声明与使用

1.1 索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

  • 从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。

  • 按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。

  • 按照 作用字段个数 进行划分，分成单列索引和联合索引。

不同的存储引擎支持的索引类型也不一样。

1.2 创建索引

1 创建表的时候创建索引

显式创建表时创建索引的话，基本语法格式如下：

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

#案例1：创建联合索引
CREATE TABLE book4(
    book_id INT,
    book_name VARCHAR(100),
    authors VARCHAR(100),
    info VARCHAR(100),
    comment VARCHAR(100),
    year_publication YEAR,
    #声明索引
    INDEX (book_id,book_name,info)
)

##案例1：创建全文索引
CREATE TABLE test4(
    id INT NOT NULL,
    name CHAR(30) NOT NULL,
    age INT NOT NULL,
    info VARCHAR(100),
    comment VARCHAR(255),
    FULLTEXT INDEX futxt_idx_info(info(50)
)
全文索引用match + against 方式查询
2 在已经存在的表上创建索引
  1. 使用 ALTER TABLE ... ADD ...
ALTER TABLE 表名 ADD [UNIQUE] INDEX 索引名(字段名);
  1. 使用 CREATE INDEX 创建索引：
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

1.3 查看索引

#方式一：
SHOW CREATE TABLE 表名;

#方式二
SHOW INDEX FROM 表名\G

1.4 创建索引

  1. 使用ALTER TABLE删除索引
ALTER TABLE table_name DROP INDEX index_name;
  1. 使用 DROP INDEX 语句删除索引
DROP INDEX index_name ON table_name;

2 MySQL8.0 索引新特性

2.1 支持降序索引

#案例：
CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

注意：降序索引只对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低。

2.2 隐藏索引

从MySQL 8.x开始支持 隐藏索引（invisible indexes） ，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。 这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。

同时，如果你想验证某个索引删除之后的 查询性能影响，就可以暂时先隐藏该索引。

注意：主键不能被设置为隐藏索引。当表中没有显式主键时，表中第一个唯一非空所以会成为隐式主键，也不能设置为隐藏索引。

索引默认是可见的，在使用 CREATE TABLE，CREATE INDEX 或 ALTER TABLE 等语句时可以通过 VISIBLE 或者 INVISIBLE 关键字设置索引的可见性。

  1. 创建表时直接创建：
CREATE TABLE tablename(
propname1 type1[CONSTRAINT1],
propname2 type2[CONSTRAINT2],
……
propnamen typen,
INDEX [indexname](propname1 [(length)]) INVISIBLE
);
  1. 在已经存在的表上创建

3 索引的设计原则

3.2 哪些情况适合创建索引

  1. 字段的数值有唯一性的限制

  2. 频繁作为 WHERE 查询条件的字段

  3. 经常 GROUP BY 和 ORDER BY 的列

  4. UPDATE、DELETE 的 WHERE 条件列

  5. DISTINCT 字段需要创建索引

  6. 多表 JOIN 连接操作时，创建索引注意事项

首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。

  1. 使用列的类型小的创建索引

  2. 使用字符串前缀创建索引

怎么计算不同的长度的选择性呢？

select count(distinct address) / count(*) from shop;
#公式：
count(distinct left(列名, 索引长度))/count(*)
#通过不同长度去计算，与全表的选择性对比

  1. 区分度高(散列性高)的列适合作为索引

  2. 使用最频繁的列放到联合索引的左侧

  3. 在多个字段都要创建索引的情况下，联合索引优于单值索引

3.3 限制索引的数目

我们需要限制每张表上索引数量，建议单张表索引数量 不超过6个。

优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来执行评估，以生成一个最好的执行计划，如果同时有很多歌索引都可以用于查询，会增加 MySQL 优化器生成执行计划时间，降低查询性能。

3.4 哪些情况不适合创建索引

  1. 在 WHERE 中使用不到的字段，不要设置索引

  2. 数据量小的表最好不要使用索引

  3. 有大量重复数据的列上不要建立索引

当数据重复度大，比如 高于10% 的时候，也不需要对这个字段使用索引。

  1. 避免对经常更新的表创建过多的索引

  2. 不建议用无序的值作为索引

比如身份证、UUID（在索引比较时需要转为 ASCIII，并且插入时可能造成页分裂）、MD5、HASH、无序长字符串等等。

  1. 删除不再使用或者很少使用的索引

  2. 不要定义冗余或重复的索引

第9章 性能分析工具的使用

2 查看系统性能参数

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率 。

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

一些常用的性能参数如下：

  • Connections：连接 MySQL服务器的次数。

  • Uptime：MySQL服务器的上线时间。

  • Slow_queries：慢查询的次数。

  • Innodb_rows_read：Select 查询返回的行数

  • Innodb_rows_inserted：执行INSERT操作插入的行数

  • Innodb_rows_updated：执行UPDATE操作更新的行数

  • Innodb_rows_deleted：执行DELETE操作删除的行数

  • Com_select：查询操作的次数。

  • Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。

  • Com_update：更新操作的次数。

  • Com_delete：删除操作的次数。

3 统计 SQL 的查询成本：last_query_cost

一条 SQL 查询语句在执行前需要确定查询执行计划，如果存在多种执行计划的话，MySQL 会计算每个执行计划所需要的成本，从中选择成本最小的一个座位最终执行的执行计划。

last_query_cost 这个查询成本对应的是 SQL 语句所需要读取的页的数量。

SHOW STATUS LIKE 'last_query_cost';

4 定位执行慢的 SQL：慢查询日志

MySQL 的慢查询日志，用来记录在 MySQL 中 响应时间超过阈值 的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。

默认情况下，MySQL 数据库 没有开启 慢查询日志。

4.1 开启慢查询日志参数

4.5 慢查询日志分析工具：mysqldumpslow

4.6 关闭慢查询日志

使用 SHOW 语句显示慢查询日志信息，具体 SQL 语句如下：

SHOW VARIABLES LIKE 'slow_query_log%';

从执行结果可以看出，慢查询日志的目录，在该目录下手动删除慢查询日志即可。

使用命令 mysqladmin flush-logs 来重新生成查询日志文件，具体命令如下，执行完毕后会在数据目录下重新生成慢查询日志文件：

mysqladmin -uroot -p flush-logs slow

5 查看 SQL 执行成本：SHOW PROFILE

show variables like 'profiling';

开启 profile。

查看开销：

show profiles; #查看所有
show profile; #查看最近一次
show profile cpu,block io for query 2; #查看某一个

6 分析查询语句：EXPLAIN

定位了查询慢的 SQL 之后，我们就可以使用 EXPLAIN 或 DESCRIBE 工具做针对性的分析查询语句。

列名 描述
id 在一个大的查询语句中每个SELECT关键字都对应一个 唯一的id
select_type SELECT关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际上使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息

id

  • id如果相同，可以认为是一组，从上往下顺序执行

  • 在所有组中，id值越大，优先级越高，越先执行

  • 关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

select_type

一条大的查询语句里面可以包含若干个 SELECT 关键字，每个 SELECT 关键字代表着一个小的查询语句，而每个 SELECT 关键字的 FROM 子句都可以包含若干张表（这些表用来做连接查询），每一张表都对应着执行计划输出中的一条记录，对于在同一个 SELECT 关键字中的表来说，它们的 id 值是相同的。

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

partitions

代表分区表中的民众情况，非分区表，该项为 NULL。

type

完整的访问方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ，index_merge ， unique_subquery ， index_subquery ，range ， index ， ALL 。

  • system

  • const

    当我们根据 主键 或者 唯一二级索引列 与常数进行等值匹配时，对单表的访问方式就是 const。

  • 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref。

    EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

  • 当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref。

  • 当对普通二级索引进行等值匹配查询时，该索引列的值也可以是 NULL 值时，那么对该表的访问方法可能是 ref_or_null。

小结：

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL 。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）

possible_keys 和 key

在 EXPLAIN 语句输出的执行计划中，possible_keys 列表表示在某个查询语句中，对某个表执行 单表查询时可能用到的索引 有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key 列表示 实际用到的索引 有哪些，如果为 NULL，则没有使用索引。

key_len

实际使用到的索引长度（即：字节数）。

帮你检查是否充分的利用上了索引，值越大越好，主要针对于联合索引，有一定的参考意义。

ref

当使用索引列等值查询时，与索引列进行等值匹配的对象信息。

rows

预估的需要读取的记录条数。

值越小越好。

filtered

某个表经过搜索条件过滤后剩余记录条数的百分比。

  • 如果使用的是索引执行的单表扫描，那么计算时需要顾及出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

  • 对于单表查询来说，这个 filtered 列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的 filtered 值，它决定了被驱动表要执行的次数（即：rows * filtered）

Extra

7 EXPLAIN 的进一步使用

7.1 EXPLAIN 四种输出格式

EXPLAIN 可以输出四种格式：传统格式、JSON格式、TREE格式、可视化输出

  • JSON 格式是四种格式输出里面输出 信息最详尽 的格式，里面包含了执行的成本信息。

  • TREE 格式主要根据查询的 各个部分之间的关系 和 各部分的执行顺序 来描述如何查询。

7.2 SHOW WARNINGS的使用

8 分析优化器执行计划：trace

OPTIMIZER_TRACE 可以跟踪优化器做出的各种决策（比如访问表的方法、各种开销计算、各种转换等），并将跟踪结果记录到 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中。

此功能默认关闭。开启 trace，并设置格式为 JSON，同时设置 trace 最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

SET optimizer_trace="enabled=on",end_markers_in_json=on;
SET optimizer_trace_max_mem_size=100000;

测试：执行如下SQL语句

SELECT * FROM student where id <10;

最后，查询 information_schema.optimizer_trace 就可以知道 MySQL 是如何执行 SQL 的：

SELECT * FROM information_schema.optimizer_trace\G

9 MySQL监控分析视图-sys schema

关于 MySQL 的性能监控和问题诊断，我们一般都从 performance_schema 中去获取想要的数据，在 MySQL5.7.7 版本中新增 sys schema，它将 performance_schema 和 information_schema 中的数据以更容易理解的方式总结归纳为“视图”，其目的就是为了 降低查询 performance_schema的复杂度。

第10章 索引优化与查询优化

虽然 SQL 查询优化的技术有很多，但是大方向上完全可以分为 物理查询优化 和 逻辑查询优化 两大块。

  • 物理查询优化是通过 索引 和 表连接方式 等技术来进行优化。

  • 逻辑查询优化是通过 SQL 等价变化 提升查询效率。

2 索引失效案例

大多数情况下都（默认）采用 B+树 来构建索引，只是空间列类型的索引使用 R- 树，并且 MEMOEY 引擎还支持 hash索引。

2.1 全值匹配我最爱

2.2 最佳左前缀法则

MySQL 可以为多个字段创建索引，一个索引可以包含 16 个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

2.3 主键插入顺序

2.4 计算、函数、类型转换(自动或手动)导致索引失效

2.5 类型转换导致索引失效

2.6 范围条件右边的列索引失效

2.7 不等于(!= 或者<>)索引失效

2.8 is null可以使用索引，is not null无法使用索引

最好在设计数据表的时候就将 字段设置为 NOT NULL 约束。

2.9 like以通配符%开头索引失效

2.10 OR 前后存在非索引的列，索引失效

在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。也就是说，OR 前后的两个条件中的列都是索引时，查询中才使用索引。

2.11 数据库和表的字符集统一使用utf8mb4

统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。

3 关联查询优化

3.2 采用左外链接

给被驱动表建立索引，避免全表扫描。

3.3 采用内连接

对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的。

对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表。

在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表，小表驱动大表。

3.4 join 语句原理

join 方式连接多个表，本质就是各个表之间的数据的循环匹配。MySQL 5.5 版本之前，MySQL 只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则 join 关联的执行时间会非常长。在 MySQL5.5 以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。

1 驱动表和被驱动表

驱动表就是主表，被驱动表就是从表、非驱动表。

2 Simple Nested-Loop Join(简单嵌套循环连接)
3 Index Nested-Loop Join(索引嵌套循环连接)

其优化思路主要是为了 减少内层表数据的匹配次数，所以要求被驱动表上必须 有索引 才行。通过外层表匹配条件直接与内层表进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。

4 Block Nested-Loop Join(块嵌套循环连接)

如果存在索引，那么会使用 index 的方式进行 join，如果 join 的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条数据，然后把被驱动表的记录再加载到内存匹配，这样周而复始，大大增加了 IO 的次数。为了减少被驱动表的 IO 次数，就出现了 Block Nested-Loop Join 的方式。

不再是逐条获取 驱动表 的数据，而是一块一块的获取，引入了 join buffer缓冲区，将驱动表 join 相关的部分数据列（大小受 join buffer 的限制）缓存到 join buffer 中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和 join buffer 中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。

注意：
这里缓存的不只是关联表的列，select 后面的列也会缓存起来。

在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。
5 Join 小结

  1. 整体效率比较：INLJ > BNLJ > SNLJ

  2. 永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量）（小的度量单位指的是 表行数 * 每行大小 ）

  3. 为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）。

  4. 增大 join buffer size 的大小，一次缓存的数据越多，那么内存包的扫表次数就越少。

  5. 减少驱动表不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）

  6. 在决定哪个表做驱动表的时候，应该是两个表按照按照各自的条件过滤，过滤完成之后，计算参数 join 的各个字段的总数据量，数据量小的那张表，就是“小表”，应该作为驱动表。

6 Hash Join

从 MySQL 8.0.20 版本开始废弃 BNLJ，因为从 MySQL 8.0.18版本开始就加入了 hash join，默认都会使用 hash join。

4 子查询优化

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

  • 执行子查询时，MySQL需要为内层查询语句的查询结果 建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再 撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。

  • 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会受到一定的影响。

  • 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询 不需要建立临时表 ，其 速度比子查询要快 ，如果查询中使用索引的话，性能就会更好

结论：尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代。

5 排序优化

5.1 排序优化

问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？

回答：在 MySQL 中，支持两种排序方式，分别是 FileSort 和 Index 排序。

  • Index 排序汇总，索引可以保证数据的有序性，不需要再进行排序，效率更高。

  • FileSort 排序则一般在 内存中进行排序，占用 CPU 较多。如果待排序结果较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率较低。

SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序 不一定 比索引慢。

5.4 filesort算法：双路排序和单路排序

双路排序（慢）

  • MySQL 4.1 之前是使用双路排序 ，字面意思就是两次扫描磁盘，最终得到数据， 读取 行指针 和 order by列 ，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

  • 从磁盘取排序字段，在buffer进行排序，再从 磁盘取其他字段 。

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序 （快）

从磁盘读取查询需要的 所有列 ，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

优化策略

  1. 尝试提高 sort_buffer_size

  2. 尝试提高 max_length_for_sort_data

  3. Order by 时select * 是一个大忌。最好只Query需要的字段。

6 GROUP BY优化

  • group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。

  • group by 先排序再分组，遵照索引建的最佳左前缀法则。

  • 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置。

  • where 效率高于 having，能写在where限定的条件就不要写在 having 中了。

  • 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、groupby、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

  • 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在 1000 行以内，否则 SQL 会很慢。

7 优化分页查询

8 优先考虑覆盖索引

8.1 什么事覆盖索引

理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

理解方式二：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。

8.2 覆盖索引的利弊

好处：

  1. 避免Innodb表进行索引的二次查询（回表）

  2. 可以把随机IO变成顺序IO加快查询效率

弊端：

索引字段的维护 总是有代价的。

9 如何给字符串添加索引

10 索引下推

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

10.5 ICP的使用条件

  1. 如果表访问的类型为 range、ref、eq_ref 和 ref_or_null 可以使用 ICP。

  2. ICP 可以用于 InnoDB 和 MyISAM 表，包括分区表 InnoDB 和 MyISAM 表。

  3. 对于 InnoDB 表，ICP 仅用于 二级索引。ICP 的目标是减少全行读取次数，从而减少 I/O 操作。

  4. 当 SQL 使用覆盖索引时，不支持 ICP。因为这种情况下使用 ICP 不会减少 I/O。

  5. 相关子查询的条件不能使用 ICP。

12 其他查询优化策略

12.1 EXISTS 和 IN 的区分

索引是前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为 小表驱动大表，在这种方式下效率是最高的。

比如下面这样：

SELECT * FROM A WHARE cc IN (SELECT cc FROM B);
SELECT * FROM A WHARE EXISTS(SELECT cc FROM B WHERE b.cc=A.cc);

当 A 小于 B 时，用 EXISTS。因为 EXISTS 的实现，相当于外表循环，实现的逻辑类似于：

for i in A
    for j in B
        if j.cc = i.cc then ...

当 B 小于 A 时用 IN，因为实现逻辑类似于：

for i in B
    for j in A
        if j.cc = i.cc then ...

哪个表小就用哪个表来驱动。

12.2 COUNT(*) 与 COUNT(具体字段)效率

环节1：如果是 MyISAM，统计数据表的行数只需要 O(1) 的复杂度，这是因为每张 MyISAM 的数据表都有一个 meta 信息存储了 row_count，而一致性则由表级锁来保证。

环节2：如果是 InnoDB 存储引擎，因为 InnoDB 支持事务，采用行级锁和 MVCC 机制，所以无法向 MyISAM 一样，维护一个 row_count 变量，因此需要采用 扫描全表，是 O(n)的复杂度，进行 循环+计数 的方式来完成统计。

环节3：在 InnoDB 引擎中，如果采用 COUNT(具体字段) 来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于 COUNT(*) 和 COUNT(1) 来说，它们不需要查找具体的行，只是统计行数，系统会 自动 采用占用空间更小的二级索引来统计。

如果有多个二级索引，会使用 key_len 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

12.2 关于 SELECT(*)

在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。原因：

① MySQL 在解析的过程中，会通过 查询数据字典 将"*"按序转换成所有列名，这会大大的耗费资源和时间。

② 无法使用 覆盖索引。

13 淘宝数据库，主键如何设计的？

13.2 业务字段做主键

建议尽量不要用跟业务有关的字段做主键。毕竟，作为项目设计的技术人员，我们谁也无法预测在项目的整个生命周期中，哪个业务字段会因为项目的业务需求而有重复，或者重用之类的情况出现。

13.3 淘宝的主键设计

大胆猜测，淘宝的订单ID设计应该是：

订单ID = 时间 + 去重字段 + 用户ID后6位尾号

这样的设计能做到全局唯一，且对分布式系统查询及其友好。

13.4 推荐的主键设计

非核心业务 ：对应表的主键自增ID，如告警、日志、监控等信息。

核心业务 ：主键设计至少应该是全局唯一且是单调递增。全局唯一保证在各系统之间都是唯一的，单调递增是希望插入时不影响数据库性能。这里推荐最简单的一种主键设计：UUID。

UUID的特点：

全局唯一，占用36字节，数据无序，插入性能差。

改造UUID：

若将时间高低位互换，则时间就是单调递增的了，也就变得单调递增了。MySQL 8.0可以更换时间低位和时间高位的存储方式，这样UUID就是有序的UUID了。

SET @uuid = UUID();
SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

通过函数uuid_to_bin(@uuid,true)将UUID转化为有序UUID了。全局唯一 + 单调递增，这不就是我们想要的主键！

第11章 数据库的设计规范

2 范式

2.1 范式简介

在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。

范式的英文名称是 Normal Form ，简称 NF。

2.2 范式都包括哪些

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

数据库的范式设计越高阶，冗余度就越低，同时高阶的范式一定符合低阶范式的要求。

2.3 键和相关属性的概念

2.4 第一范式(1st NF)

第一范式主要是确保数据表中每个字段的值必须具有 原子性，也就是说数据表中每个字段的值为不可再次拆分的最小数据单元。

2.5 第二范式(1nd NF)

第二范式要求，在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。

1NF 告诉我们字段属性需要是原子性的，而 2NF 告诉我们一张表就是一个独立的对象，一张表只表达一个意思。

2.6 第三范式(3rd NF)

第三范式是在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段。（即，不能存在非主属性A依赖于非主属性B，非主属性B依赖于主键C的情况，即存在"A->B->C"的决定关系）通俗的讲，该规则的意思是所有 非主键属性 之间不能有依赖关系，必须相互独立。

2.7 小结

关于数据表的设计，有三个范式要遵循：

  1. 第一范式(1NF)，确保每列保持 原子性。

数据库的每一列都是不可分割的原子数据项，不可再分的最小数据单元，而不能是集合、数组、记录等非原子数据项。

  1. 第二范式(2NF)，确保每列和主键 完全依赖。

尤其是在复合主键的情况下，非主键部分不应该依赖于部分主键。

  1. 第三范式(3NF)，确保每列都和主键 直接相关，而不是间接相关。

范式的缺点：范式的使用，可能 降低查询的效率，因为范式等级越高。设计出来的数据表就越多、越精细，数据的冗余度就越低，进行数据查询的时候可能需要 关联多张表，这不但代价昂贵，也可能使一些索引策略无效。

3 反范式化

3.1 概述

如果数据库中的数据量比较大，系统的 UV 和 PV 访问频次比较高，则完全按照 MySQL 的三大范式设计数据表，读数据时会产生大量的关联查询，在一定程度上会影响数据库的读性能。如果我们想对查询效率进行优化，反范式化 也是一种优化思路。此时可以通过在数据表中 增加冗余字段 来提高数据库的读性能。

4 BCNF(巴斯范式)

8 ER 模型

ER 模型中有三个要素，分别是实体、属性和关系。

实体 ，可以看做是数据对象，往往对应于现实生活中的真实存在的个体。在 ER 模型中，用 矩形 来表示。实体分为两类，分别是 强实体 和 弱实体 。强实体是指不依赖于其他实体的实体；弱实体是指对另一个实体有很强的依赖关系的实体。

属性 ，则是指实体的特性。比如超市的地址、联系电话、员工数等。在 ER 模型中用 椭圆形 来表示。

关系 ，则是指实体之间的联系。比如超市把商品卖给顾客，就是一种超市与顾客之间的联系。在 ER 模型中用 菱形 来表示。

注意：实体和属性不容易区分。这里提供一个原则：我们要从系统整体的角度出发去看，可以独立存在的是实体，不可再分的是属性。也就是说，属性不能包含其他属性

8.5 ER 模型图转换成数据表

通过绘制 ER 模型，我们已经理清了业务逻辑，现在，我们就要进行非常重要的一步了：把绘制好的 ER

模型，转换成具体的数据表，下面介绍下转换的原则：

（1）一个 实体 通常转换成一个 数据表 ；

（2）一个 多对多的关系 ，通常也转换成一个 数据表；

（3）一个 1 对 1 ，或者 1 对多 的关系，往往通过表的 外键 来表达，而不是设计一个新的数据表；

（4） 属性 转换成表的 字段。

11 PowerDesigner的使用

PowerDesigner是一款开发人员常用的数据库建模工具，用户利用该软件可以方便地制作 数据流程图 、概念数据模型 、 物理数据模型 ，它几乎包括了数据库模型设计的全过程。

第12章 数据库其他调优策略

1 数据库调优的措施

1.4 调优的维度和步骤

第1步：选择合适的 DBMS
第2步：优化表设计
第3步：优化逻辑查询

逻辑查询优化就是通过改变 SQL 语句的内容让 SQL 执行效率更高，采用的方法是对 SQL 语句进行等价变换，对查询进行重写。

SQL 的查询重构包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等。

第4步：优化物理查询

索引。

第5步：使用 Redis 或 Memcached 作为缓存
第6步：库级优化

  1. 读写分离

  2. 数据分片

对 数据库分库分表。当数据量级达到千万级以上时，有时候我们需要把一个数据库切成多份，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力。如果你使用的是 MySQL，就可以使用 MySQL 自带的分区表功能，当然你也可考虑自己做 垂直拆分(分库)、水平拆分(分表)、垂直+水平拆分(分库分表)。

2 优化 MySQL 服务器

2.1 优化服务器硬件

2.1 优化 MySQL 的参数

3 优化数据库结构

3.1 拆分表：冷热数据分离

拆分表的思路是，把一个包含很多字段的表拆分成 2 个或者多个相对较小的表。冷热数据分离，可以减小表的宽度。

3.2 增加中间表

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

3.3 增加冗余字段

3.4 优化数据类型

优先选择符合存储需要的最小的数据类型。

3.5 优化插入记录的速度

插入记录时，影响插入素的主要是索引、唯一性校验、一次插入记录条数等。

3.6 使用非空约束

3.7 分析表、检查表与优化表

1 分析表
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]…

默认的，MySQL服务会将 ANALYZE TABLE语句写到 binlog 中，以便在主从架构中，从服务能够同步数据。可以添加参数LOCAL 或者 NO_WRITE_TO_BINLOG取消将语句写到binlog中。

使用 ANALYZE TABLE 分析表的过程中，数据库系统会自动对表加一个 只读锁 。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB和MyISAM类型的表，但是不能作用于视图。

ANALYZE TABLE分析后的统计结果会反应到 cardinality 的值，该值统计了表中某一键所在的列不重复的值的个数。该值越接近表中的总行数，则在表连接查询或者索引查询时，就越优先被优化器选择使用。也就是索引列的cardinality的值与表中数据的总条数差距越大，即使查询的时候使用了该索引作为查询条件，存储引擎实际查询的时候使用的概率就越小。

2 检查表

MySQL中可以使用 CHECK TABLE 语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误。CHECK TABLE语句在执行过程中也会给表加上 只读锁 。

3 优化表

  1. 方式一：OPTIMIZE TABLE

  2. 方式二：使用 mysqlcheck 命令

4 大表优化

4.1 限定查询的范围

4.2 读/写分离

经典的数据库拆分方案，主库负责写，从库负责读。

  • 一主一从模式

  • 双主双从模式

4.3 垂直拆分

垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区

可以简化表的结构，易于维护。

4.4 水平拆分

5 其他调优策略

5.1 服务器语句超时处理

5.2 创建全局通用表空间

5.3 MySQL 8.0新特性：隐藏索引对调优的帮助

第13章 事务基础知识

1.2 基本概念

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

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

实际上，哪怕事务里某一语句出现了错误，一旦你执行 commit，前面正常的修改仍然会被提交，MySQL 不会自动判断事务中的 SQL 执行成功与否。 MySQL运行时报错事务不会回滚。

1.3 事务的ACID特性

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

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

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

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

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

1.4 事务的状态

我们现在知道 事务 是一个抽象的概念，它其实对应着一个或多个数据库操作，MySQL根据这些操作所执行的不同阶段把事务大致划分成几个状态：

  • 活动的（active）

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

  • 部分提交的（partially committed）

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

  • 失败的（failed）

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

  • 中止的（aborted）

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

  • 提交的（committed）

2 如何使用事务

2.1 显式事务

步骤1：开启事务

步骤2：一系列的 DML 操作

步骤3：事务结束的状态：提交的状态(COMMIT)、中止的状态(ROLLBACK)

如何开启？使用关键字：start transaction 或 begin

start transaction 后面可以跟：read only、read write(默认)、with consistent snapshot

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

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

补充：只读事务中只是不允许修改那些其他事务也能访问到的表中的数据，对于临时表来说（我们使用 CREATE TEMPORYRY TABLE 创建的表），由于他们只能在当前会话中可见，所以只读事务其实也可以对临时表进行增、删、改操作的。

  1. WITH CONSISTENT SNAPSHOT ：启动一致性读。
# 提交事务。当提交事务后，对数据库的修改是永久性的。
mysql> COMMIT;


# 回滚事务。即撤销正在进行的所有没有提交的修改
mysql> ROLLBACK; 

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

2.2 隐式事务

  • 显式的的使用 START TRANSACTION 或者 BEGIN 语句开启一个事务。这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。

  • 把系统变量 autocommit 的值设置为 OFF。

2.3 隐式提交数据的情况

  • 数据定义语言（Data definition language，缩写为：DDL）

    数据库对象，指的就是 数据库、表、视图、存储过程 等结果。当我们使用 CREATE、ALTER、DROP 等语句去修改数据库对象时，就会隐式的提交前边语句所属于的事务。

    BEGIN:

SELECT ... #事务中的一条语句
UPDATE ... #事务中的一条语句
... #事务中的其它语句

CREATE TABLE ... #此语句会隐式的提交前边语句所属于的事务

  • 隐式使用或修改mysql数据库中的表

    当我们使用 ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD 等语句时也会隐式的提交前边语句所属于的事务。

  • 事务控制或关于锁定的语句

    (1)当我们在一个事务还没提交或者回滚时就又使用 START TRANSACTION 或 BEGIN语句开启了另一个事务时，会 隐式的提交 上一个事务。

    (2)当前的 autocommit 系统变量的值为 OFF，我们手动把它调为 ON 时，也会 隐式的提交 前边语句所属的事务。

    (3) 使用 LOCK TABLES、UNLOCK TABLES 等关于锁定的语句也会 隐式的提交 前边语句所属的事务。

  • 加载数据的语句

    使用 LOAD DATA 语句来批量往数据库汇总导入数据时，也会 隐式的提交 前边语句所属的事务。

  • 关于MySQL复制的一些语句

  • 其它的一些语句

2.4 使用举例

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

链式事务

#在事务开始之前设置
SET @@completion_type=1;

completion_type 有3种可能：

  • completion_type=0，这是 默认情况。当我们执行 COMMIT 的时候会提交事务，在执行下一个事务时，还需要使用 START TRANSACTION 或者 BEING 来开启。

  • completion_type=1，这种情况下，当我们提交事务后，相当于执行了 COMMIT AND CHAIN，也就是开启一个链式事务，即当我们提交事务之后会开启一个相同隔离级别的事务。

  • completion_type=2，这种情况下 COMMIT = COMMIT AND RELEASE，也就是当我们提交之后，会自动与服务器断开连接。

3 事务隔离级别

3.2 数据并发问题

1 脏写（ Dirty Write ）

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

2 脏读（ Dirty Read ）

对于两个事务 Session A、Session B，Session A 读取 了已经被 Session B 更新 但还 没有被提交 的字段。之后若 Session B 回滚 ，Session A 读取 的内容就是 临时且无效 的。

3 不可重复读（ Non-Repeatable Read ）

对于两个事务Session A、Session B，Session A 读取 了一个字段，然后 Session B 更新 了该字段。 之后Session A 再次读取 同一个字段， 值就不同 了。那就意味着发生了不可重复读。

4 幻读（ Phantom ）

对于两个事务Session A、Session B, Session A 从一个表中 读取 了一个字段, 然后 Session B 在该表中 插入 了一些新的行。 之后, 如果 Session A 再次读取 同一个表, 就会多出几行。那就意味着发生了幻读。

注意1：

有的人会有疑问，那如果 Session B 中 删除了 一些符合查询条件的记录而不是插入记录，那 Session A 之后再根据查询条件读取的 记录变少了，这种现象算不算 幻读 呢？这种现象 不属于幻读，幻读强调的是一个事务按照某个 相同条件多次读取 记录时，后续读取时读到了之前 没有读到的记录。

注意2：

对于先前已经读到的记录，之后又读取不到的这种情况，算啥呢？这相当于对每一条记录都发生了 不可重复读 的现象。幻读只是重点强调了读取到了之前没有获取到的记录。

3.3 SQL中的四种隔离级别

上面介绍了几种并发事务执行过程中可能遇到的一些问题，这些问题有轻重缓急之分，我们给这些问题按照严重性来排一下序：

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

SQL标准 中设立了4个 隔离级别 ：

  • READ UNCOMMITTED ：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。

  • READ COMMITTED ：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可重复读、幻读问题仍然存在。

  • REPEATABLE READ ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。

  • SERIALIZABLE ：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读。

隔离级别 脏读可能性 不可重复度可能性 幻读可能性 加锁读
READ UNCOMMITTED Y Y Y N
READ COMMITTED N Y Y N
REPEATABLE READ N N Y N
SERIALIZABLE N N N Y

脏写 怎么没涉及到？因为脏写这个问题太严重了，不论是哪种隔离级别，都不允许脏写的情况发生。

不同的隔离级别有不同的现象，并有不同的锁和并发机制，隔离级别越高，数据库的并发性能就越差。

3.4 MySQL支持的四种隔离级别

# 查看隔离级别，MySQL 5.7.20的版本之前：
SHOW VARIABLES LIKE 'tx_isolation';

# MySQL 5.7.20版本之后，引入transaction_isolation来替换tx_isolation
SHOW VARIABLES LIKE 'transaction_isolation';

#或者不同MySQL版本中都可以使用的： 
SELECT @@transaction_isolation;

3.5 如何设置事务的隔离级别

SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别' 
#其中，隔离级别格式： 
# READ-UNCOMMITTED 
# READ-COMMITTED 
# REPEATABLE-READ 
# SERIALIZABLE
关于设置时使用GLOBAL或SESSION的影响：

  • 使用 GLOBAL 关键字（在全局范围影响）：

    • 当前已经存在的会话无效

    • 只对执行完该语句之后产生的会话起作用

  • 使用 SESSION 关键字（在会话范围影响）：

    • 对当前会话的所有后续的事务有效

    • 如果在事务之间执行，则对后续的事务有效

    • 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务

4 事务的常见分类

从事务理论的角度来看，可以把事务分为以下几种类型：

  • 扁平事务（Flat Transactions）

  • 带有保存点的扁平事务（Flat Transactions with Savepoints）

  • 链事务（Chained Transactions）

  • 嵌套事务（Nested Transactions）

  • 分布式事务（Distributed Transactions）

第14章 MySQL 事务日志

事务有4种特性：原子性、一致性、隔离性和持久性。那么事务的四种特性到底是基于什么机制实现呢？

  • 事务的隔离性由 锁机制 实现。

  • 而事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。

    • REDO LOG 称为 重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。

    • UNDO LOG 称为 回滚日志，回滚记录到某个特定版本，用来保证事务的原子性、一致性。

有的 dba 或许会认为 UNDO 是 REDO 的逆过程，其实不然。REDO 和 UNDO 都可以视为是一种 恢复操作。

  • redo log：是存储引擎层(innodb)生成的日志，记录的是 物理级别 上的页修改操作。比如页号xxx、偏移yyy写入了zzz 数据。主要为了保证数据的可靠性。

  • undo log：是存储引擎层(innodb)生成的日志，记录的是 逻辑操作 日志，比如对某一行数据进行了 insert语句操作，那么就记录一条与之相反的 delete 操作，主要用于 事物的回滚（undo log记录的是每一个修改操作的逆操作)和 一致性非锁定读(undo log 回滚记录到某一特定的版本——MVCC，即多版本并发控制）。

1 redo日志

1.1 为什么需要 REDO 日志

InnoDB 存储引擎是以 页为单位 来管理存储空间的。在真正访问页面之前，需要把在 磁盘上 的页缓存到内存中的 buffer pool 之后才可以访问。所有的变更必须 先更新缓冲池 中的数据，然后缓冲池中的 脏页 会一定的频率被刷入磁盘(checkpoint 机制)。通过缓冲池来优化CPU和磁盘之间的鸿沟，这样可以保证整体的性能不会下降太快。

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

1.2 REDO 日志的好处、特点

1 好处

  • redo 日志降低了刷盘频率

  • redo 日志占用的空间非常小

存储表空间ID、页号、偏移量以及需要更新的值，所需的存储空间是很小的，刷盘快。

2 特点
  • redo 日志是顺序写入磁盘的

在执行事务的过程中，每执行一条语句，就可能产生若干条 redo 日志，这些日志是按照 产生的顺序写入磁盘的。

  • 事务执行过程中，redo log 不断记录

redo log 跟 bin log 的区别，redo log 是 存储引擎层 产生的，而bin log 是 数据库层 产生的。假设一个事务，对表做 10 万行的记录插入，在这个过程中，一直不断的往 redo log 顺序记录，而 bin log 不会记录，直到这个事务提交，才会一次写入到 bin log 文件中。

1.4 redo的整体流程

以一个更新事务为例：

  1. 先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝。

  2. 生成一条重做日志并写入 redo log buffer，记录的是数据被修改后的值。

  3. 当事务 commit 时，将 redo log buffer 中的内容刷新到 redo log file，对 redo log file 采用追加写的方式。

  4. 定期将内存中修改的数据刷新到磁盘中。

1.5 redo log 的刷盘策略

redo log buffer刷盘到 redo log file 的过程并不是真正的刷到磁盘中去，只是刷入到 文件系统缓存（page cache）中去（这是现代操作系统为了提高文件写入效率做的一个优化），真正的写入会交给系统自己来决定（比如page cache足够大了）。

针对这种情况，InnoDB给出 innodb_flush_log_at_trx_commit 参数，该参数控制 commit 提交事务时，如何将 redo log buffer 中的日志刷新到 redo log file 中。

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

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

    小结：innodb_flush_log_at_trx_commit =1

为1时，只要事务提交成功，redo log 记录就一定在硬盘里，不会有任何数据丢失

如果事务执行期间 MySQL 挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失。可以保证ACID的D，数据绝对不会丢失，但是效率最差的。

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

也就是说，一个没有提交事务的 redo log 记录，也可能会刷盘。因为在事务执行过程 redo log 记录是会写入 redo log buffer 中，这些 redo log 记录会被 后台线程 刷盘。

1.7 写入 redo log buffer 过程

1 补充概念：Mini-Transaction

MySQL 把对底层页面中的一次原子访问的过程称之为一个 Mini-Transaction，简称 mtr。比如，向某个索引对应的 B+ 树中插入一条记录的过程就是一个 Mini-Transaction。一个所谓的 mtr 可以包含一组 redo 日志，在进行崩溃恢复时这一组 redo 日志作为一个不可分割的整体。

一个事务可以包含若干条语句，每一条语句其实是由若干个 mtr 组成，每一个 mtr 又可以包含若干条 redo 日志。

2 redo 日志写入 log buffer

一个 mtr 执行过程中可能产生若干条 redo 日志，这些 redo 日志是不可分割的组，所以其实并不是每生成一条 redo 日志，就将其插入到 log buffer 中，而是每个 mtr 运行过程中产生的日志先暂存到一个地方，当该 mtr 结束的时候，将过程中产生的一组 redo 日志再全部复制到 log buffer 中。

3 redo log block 的结构图

1.8 redo log file

1 相关参数设置

  • innodb_log_group_home_dir ：指定 redo log 文件组所在的路径，默认值为 ./，表示在数据库的数据目录下。MySQL的默认数据目录（ var/lib/mysql ）下默认有两个名为 ib_logfile0 和 ib_logfile1 的文件，log buffer 中的日志默认情况下就是刷新到这两个磁盘文件中。此 redo 日志文件位置还可以修改。

  • innodb_log_files_in_group：指明 redo log file 的个数，命名方式如：ib_logfile0，iblogfile1... iblogfilen。默认2个，最大100个。

  • innodb_flush_log_at_trx_commit：控制 redo log 刷新到磁盘的策略，默认为1。

  • innodb_log_file_size：单个 redo log 文件设置大小，默认值为 48M 。最大值为512G，注意最大值指的是整个 redo log 系列文件之和，即（nnodb_log_files_in_group * innodb_log_file_size ）不能大于最大值512G

2 日志文件组

从上边的描述中可以看到，磁盘上的 redo 日志文件不止一个，而是以一个 日志文件组 的形式出现的。这些文件将以 ib_logfile[数字] 的形式进行命名，每个 redo 日志文件大小都是一样的。

在将 redo 日志写入日志文件组时，是从 ib_logfile0 开始写，如果 ib_logfile0 写满了，就接着 ib_logfile1 写。如果写到最后一个文件该咋办？那就重新转到 ib_logfile0 继续写。

采用循环使用的方式向redo日志文件组里写数据的话，会导致后写入的redo日志覆盖掉前边写的redo日志？当然！所以InnoDB的设计者提出了checkpoint的概念。

checkpoint

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

  • write pos 是当前记录的位置，一边写一边后移

  • checkpoint 是当前要擦除的位置，也是往后移

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

2 undo日志

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

注意，由于查询操作(SELECT)并不会修改任何用户记录，所以在查询操作执行时，并 不需要记录 对应的 undo 日志。

此外，undo log 会产生 redo log，也就是 undo log 的产生会伴随 redo log 的产生，这是因为 undo log 也需要持久性的保护。

2.2 Undo 日志的作用

作用1：回滚数据

用户对 undo 日志可能 有误解：undo 用于将物理数据库物理地恢复到执行语句或事务之前的样子。但事实并非如此。undo 是 逻辑日志，因此只是将数据库逻辑的恢复到原来的样子。所有修改都被逻辑的取消了，但是数据结构和页本身在回滚之后可能大不相同。

这是因为在多用户并发系统中可能会有数十、数百分甚至数千个并发事务，数据库的主要任务就是协调对数据记录的并发访问。比如一个数在修改当前一个页中的某几条记录，同时还有别的事务对同一个页中另几条记录进行修改。因此不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

作用2：MVCC

undo 的另一个作用是 MVCC，即在 InnoDB 存储引擎中 MVCC 实现是通过undo 来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过 undo 读取之前的行版本信息，以此实现非锁定读取。

2.5 undo log的生命周期

1 简要生成过程

以下是 undo + redo 事务的简化过程

假设有2个数值，分别为 A=1和B=2，然后将A修改为3，B修改为4

1.start transaction;
2.记录 A=1 到 undo log;
3.update A=3;(修改的是内存中的数据)
4.记录 A=3 到 redo log;
5.记录 B=2 到 undo log;
6.update B=4;
7.记录B=4到redo log;
8.将redo log刷新到磁盘;
9.commit

  • 在1-8步骤的任意一步系统宕机，事务未提交，该事务就不会对磁盘上的数据做任何影响。

  • 如果在8-9之间宕机，恢复之后可以选择回滚，也可以选择继续完成事务提交，因为此时 redo log 已经持久化。

  • 若在9之后系统宕机，内存映射中变更的数据还来不及刷回磁盘，那么系统恢复后，可以根据 redo log 把数据刷回磁盘。

第15章 锁

2 MySQL并发事务访问相同记录

并发事务访问相同记录的情况大致可以划分为3种：

2.1 读-读情况

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

2.2 写-写情况

在这种情况下会发生 脏写 的问题。

在多个未提交事务相继对一条记录做改动时，需要让它们 排队执行。

一个事务生成一个锁结构。

2.3 读-写或写-读情况

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

2.4 并发问题的解决方案

怎么解决 脏读、不可重复度、幻读 这些问题呢？其实有两种可选的解决方案：

方案一：读操作利用多版本并发控制(MVCC)，写操作进行加锁

所谓的 MVCC，就是生成一个 ReadView，通过 ReadView 找到符合条件的记录版本（历史版本由 undo日志 构建）。查询语句只能 读 到在生成 ReadView 之前 已提交事务所做的更改，在生成 ReadView 之前未提交的事务或者之后才开启的事务所做的更改是看不到的。而 写操作 肯定针对的是 最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用 MVCC 时，读-写 操作并不冲突。

普通的 SELECT 语句在 READ COMMITTED 和 REPEATABLE READ 隔离级别下会使用到MVCC读取记录。

  • 在 READ COMMITTED 隔离级别下，一个事务在执行过程中每次执行 SELECT 操作时都会生成一个ReadView，ReadView 的存在本身就保证了 事务不可以读取到未提交的事务所做的更改 ，也就是避免了脏读现象；

  • 在 REPEATABLE READ 隔离级别下，一个事务在执行过程中只有 第一次执行SELECT操作 才会生成一个ReadView，之后的SELECT操作都 复用 这个 ReadView，这样也就避免了不可重复读和幻读的问题。

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

3 锁的不同角度分类

3.1 从数据操作的类型划分：读锁、写锁

  • 共享锁(Shared Lock,S Lock)，也叫 读锁(read lock)

  • 排他锁(Exclusive Lock,X Lock)，也叫 写锁(write lock)

  • 读锁 ：也称为 共享锁 、英文用 S 表示。针对同一份数据，多个事务的 读 操作可以同时进行而不会互相影响，相互不阻塞的。

  • 写锁 ：也称为 排他锁 、英文用 X 表示。当前写操作没有完成前，它会阻断其他 写锁 和 读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

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

总结：这里的兼容是指对同一张表或记录的锁的兼容性情况。

X 锁 S 锁
X 锁 不兼容 不兼容
S 锁 不兼容 兼容
1 锁定读

在采用 加锁 方式解决 脏读、不可重复度、幻读 这些问题时，读取一条记录时需要获取该条记录的 S锁，其实是不严谨的，有时候需要在读取记录的时候就获取记录的 X锁，来禁止别的事务读写该记录，为此 MySQL 提出了两种比较特殊的 SELECT 语句格式：

  • 对读取的记录加 S锁：

    SELECT ... LOCK IN SHARE MODE;
#或
SELECT ... FOR SHARE; #(8.0新语法)

    在普通的 SELECT 语句后面加 LOCK IN SHARE MODE，如果当前事务执行了该语句，那么它会为读取到的记录加 S 锁，这样允许别的事务继续获取这些记录的 S锁，但是不能获取这些记录的 X锁。如果别的事务想要获取这些记录的 X锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的 S锁 释放掉。

  • 对读取的记录加 X锁：

    SELECT ... FOR UPDATE;

MySQL 8.0 新特性：

在 5.7 及之前的版本，SELECT ... FOR UPDATE，如果获取不到锁，会一直等待，直到 innodb_lock_wait_timeout 超时。在 8.0 版本汇总，SELECT ... FOR UPDATE，SELECT ... FOR SHARE 添加 NOWAIT、SKIP LOCKED 语法，跳过锁等待，或者跳过锁定。

  • 通过添加 NOWAIT、SKIP LOCKED 语法，能够立即返回。如果查询的行已经加锁：

    • 那么 NOWAIT 会立即报错返回。

    • 而 SKIP LOCKED 也会立即返回，只是返回的结果中不包含被锁定的行。

2 写操作

  • DELETE

    对一条记录做 DELETE 操作的过程其实是先在 B+ 树中定位到这条记录的位置，然后获取这条记录的 X锁，再执行 delete mark 操作。我们也可以把这个定位待删除记录在B+树中位置的过程看成是一个获取 X锁 的 锁定读。

  • UPDATE

  • INSERT

    一般情况下，新插入的一条记录的操作并不加锁，通过一种称之为 隐式锁 的结构来保护这条新插入的记录在本事务提交前不被被的事务访问。

3.2 从数据操作的粒度划分：表级锁、页级锁、行锁

1 表锁（Table Lock）

该锁会锁定整张表，它是 MySQL 中最基本的锁策略，并 不依赖于存储引擎。

  1. 表级别的 S锁、X锁

在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的 S锁 或者 X锁 的。在对某个表执行一些诸如 ALTER TABLE 、 DROP TABLE 这类的 DDL 语句时，其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞。同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行 DDL 语句也会发生阻塞。这个过程其实是通过在 server层 使用一种称之为 元数据锁 （英文名： Metadata Locks ，简称 MDL ）结构来实现的。

一般情况下，不会使用InnoDB存储引擎提供的表级别的 S锁 和 X锁 。只会在一些特殊情况下，比方说 崩溃恢复 过程中用到。比如，在系统变量 autocommit=0，innodb_table_locks = 1 时， 手动 获取InnoDB存储引擎提供的表t 的 S锁 或者 X锁 可以这么写：

  • LOCK TABLES t READ ：InnoDB存储引擎会对表 t 加表级别的 S锁 。

  • LOCK TABLES t WRITE ：InnoDB存储引擎会对表 t 加表级别的 X锁 。

总结：MyISAM 在执行查询语句（SELECT）前，会给涉及的所有表加读锁，在执行增删改操作之前，会给涉及的表加写锁。InnoDB存储引擎是不会为这个表添加表级别的 读锁 或者 写锁 的。

MySQL的表级锁有两种模式：（以MyISAM表进行操作的演示）

  • 表共享读锁（Table Read Lock）

  • 表独占写锁（Table Write Lock）

锁类型 自己可读 自己可写 自己可操作其他表 他人可读 他人可写
读锁 是 否 否 是 否，等
写锁 是 是 否 否，等 否，等
  1. 意向锁（intention lock）

InnoDB 支持 多粒度锁（multiple granularity locking） ，它允许 行级锁 与 表级锁 共存，而意向锁就是其中的一种 表锁 。

  • 意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加 共享锁（S锁）

    -- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 
SELECT column FROM table ... LOCK IN SHARE MODE;

  • 意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加 排他锁（X锁）

    -- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。 
SELECT column FROM table ... FOR UPDATE;

即：意向锁是由存储引擎 自己维护的 ，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行 所在数据表的对应意向锁 。

在数据表的场景中，如果我们给某一行数据加上了排它锁，数据库会自动给更大一级的空间，比如数据页或者数据表加上意向锁，告诉其他人这个数据页或者数据表已经有人上过排它锁了。这样当其他人想要获取数据表排它锁的时候，只需要了解是否有人已经获取了这个数据表的意向排它锁即可。

  • 如果事务想要获得数据表中的某些记录的共享锁，就需要在数据表上 添加意向共享锁。

  • 如果事务想要获得数据表中某些记录的排他锁，就需要在数据表上 添加意向排他锁。

    这时，意向锁会告诉其他事务已经有人锁定了表中的某些记录。

意向共享锁(IS) 意向排他锁(IX)
意向共享锁(IS) 兼容 兼容
意向排他锁(IX) 兼容 兼容

IX，IS 是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。

意向共享锁(IS) 意向排他锁(IX)
共享锁(S) 兼容 互斥
排他锁(X) 互斥 互斥

  1. 自增锁（AUTO-INC锁）

  2. “Simple inserts” （简单插入）

  3. “Bulk inserts” （批量插入）

  4. “Mixed-mode inserts” （混合模式插入）

AUTO-INC 锁是向当前使用含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁。

  1. 元数据锁（MDL锁）

MDL 的作用是，保证读写的正确性。比如，一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个 表结构做变更，增加了一列，那么查询线程拿到的结果跟表结构对不上，肯定不行的。

不需要显式调用。

2 InnoDB 中的行锁

MySQL 服务器层并没有实现行锁机制，行级锁只在存储引擎层实现。

  1. 记录锁（Record Locks）

记录锁也就是仅仅把一条记录锁上，官方的类型名称为： LOCK_REC_NOT_GAP 。

记录锁是有S锁和X锁之分的，称之为 S型记录锁 和 X型记录锁 。

  • 当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁；

  • 当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不可以继续获取X型记录锁。

  1. 间隙锁（Gap Locks）

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用 加锁 方案解决。但是在使用加锁方案解决时有个大问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些 幻影记录 加上 记录锁 。InnoDB提出了一种称之为 Gap Locks 的锁，官方的类型名称为： LOCK_GAP ，我们可以简称为 gap锁 。

gap 锁的提出仅仅是为了防止插入幻影记录而提出的。

间隙锁的引入，可能会导致同样的语句锁住更大的范围，会产生 死锁。

  1. 临键锁（Next-Key Locks）

有时候我们既想 锁住某条记录 ，又想 阻止 其他事务在该记录前边的 间隙插入新记录 ，所以InnoDB就提出了一种称之为 Next-Key Locks 的锁，官方的类型名称为： LOCK_ORDINARY ，我们也可以简称为 next-key锁 。Next-Key Locks是在存储引擎 innodb 、事务级别在 可重复读 的情况下使用的数据库锁，innodb默认的锁就是Next-Key locks。

begin; 
select * from student where id <=8 and id > 3 for update;
  1. 插入意向锁（Insert Intention Locks）

InnoDB 规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个 间隙 中 插入 新记录，但是现在在等待。

3 页锁

每个层级的锁数量是有限制的，因为锁会占用内存空间， 锁空间的大小是有限的 。当某个层级的锁数量超过了这个层级的阈值时，就会进行 锁升级 。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

3.3 从对待锁的态度划分:乐观锁、悲观锁

1 悲观锁（Pessimistic Locking）

悲观锁是一种思想，顾名思义，就是很悲观，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。

悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 阻塞 直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当其他线程想要访问数据时，都需要阻塞挂起。

select ... for update 是 MySQL 中的悲观锁。

select ... for update 语句执行过程中所有扫描的行都会被锁上，因此在 MySQL 中用悲观锁必须确定使用了索引，而不是全表扫描，否则将把整个表锁住。

2 乐观锁（Optimistic Locking）

乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用 版本号机制 或者 CAS机制 实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量。

  1. 乐观锁的版本号机制

在表中设计一个 版本字段 version ，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行 UPDATE ... SET version=version+1 WHERE version=version 。此时如果已经有事务对这条数据进行了更改，修改就不会成功。

  1. 乐观锁的时间戳机制

时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。

3.4 按加锁的方式划分：显式锁、隐式锁

1 隐式锁

一个事务在执行 INSERT 操作时，如果即将插入的 间隙 已经被其他事务加了 gap锁，那么本次的 INSERT 操作会阻塞，并且当前事务会在此间隙上加一个 插入意向锁，否则一般情况下 INSERT 操作是不加锁的。

2 显式锁

3.5 其它锁之：全局锁

全局锁就是对 整个数据库实例 加锁。当你需要让整个库处于 只读状态 的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用 场景 是：做 全库逻辑备份 。

3.6 其它锁之：死锁

1 概念

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。死锁示例：

事务1 事务2
1 start transaction;
update account set money=10 where id=1;		 start transaction;
2 update account set money=10 where id=2;
3 update account set money=20 where id=2;
4 update account set money=20 where id=1;
2 产生死锁的必要条件

  1. 两个或两个以上的事务

  2. 每个事务都已经持有并且申请新的锁

  3. 锁资源同时只能被同一个事务持有或者不兼容

  4. 事务之前因为持有锁和申请锁导致彼此循环等待。

死锁的关键在于：两个（或多个）的 Session 加锁顺序不一致。

3 如何处理死锁

方式一：等待，直到超时（innodb_lock_wait_timeout=50s）。

即当两个事务互相等待时，当一个事务等待时间超过设置的阈值时，就将其 回滚，另外事务继续进行。

方式二：使用死锁检测进行死锁处理

方式1检测死锁太过被动，innoDB 还提供了 wait-for gragh算法 来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph 算法都会被触发。

一旦检测到回路，有死锁，这时候 InnoDB 存储引起会选择 回滚undo量最小的事务，让其他事务继续执行。

将参数 innodb_deadlock_detect 设置为 on ，表示开启这个逻辑。

第二种策略的成本分析

方法1：如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是 业务无损 的。而关掉死锁检测意味着可能会出现大量的超时，这是 业务有损 的。

方法2：控制并发度。如果并发能够控制住，比如同一行同时最多只有10个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。

这个并发控制要做在 数据库服务端 。如果你有中间件，可以考虑在 中间件实现 ；甚至有能力修改MySQL源码的人，也可以做在MySQL里面。基本思路就是，对于相同行的更新，在进入引擎之前排队，这样在InnoDB内部就不会有大量的死锁检测工作了。

4 锁的内存结构

5 锁监控

关于MySQL锁的监控，我们一般可以通过检查 InnoDB_row_lock 等状态变量来分析系统上的行锁的争夺情况。

其他监控方法：

MySQL把事务和锁的信息记录在了 information_schema 库中，涉及到的三张表分别是 INNODB_TRX 、 INNODB_LOCKS 和 INNODB_LOCK_WAITS 。

第16章 多版本并发控制

1 什么是MVCC

MVCC （Multiversion Concurrency Control），多版本并发控制。顾名思义，MVCC 是通过数据行的多个版本管理来实现数据库的 并发控制 。这项技术使得在 InnoDB 的事务隔离级别下执行 一致性读 操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。

2 快照读与当前读

4 MVCC实现原理之ReadView

MVCC 的实现依赖于：隐藏字段、Undo Log、Read View。

4.1 什么是 ReadView

在 MVCC 机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在 Undo Log 里。如果一个事务想要查询这个行记录，需要读取哪个版本的行记录呢？这时就需要用到 ReadView 了，它帮我们解决了行的可见性问题。

ReadView 就是一个事务在使用 MVCC 机制进行快照读操作时产生的读视图。当事务启动时，会生成数据库系统当前的一个快照，InnoDB 为每个事务构造了一个数组，用来记录并维护当前 活跃事务 的 ID（“活跃”指的就是，启动了但还没提交）。

4.4 MVCC 整体操作流程

当查询一条记录时，系统如何通过 MVCC 找到它：

  1. 首先获取事务自己的版本号，也就是事务 ID；

  2. 获取 ReadView；

  3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；

  4. 如果不符合 ReadView 规则，就需要从 undo log 中获取历史快照。

  5. 最后返回符合规则的数据。

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

InnoDB 中，MVCC 是通过 undo log + readview 进行数据读取，undo log 保存了历史快照，而 readview 规则帮我们判断当前版本的数据是否可见。

第17章 其他数据库日志

1 MySQL支持的日志

1.1 日志类型

MySQL有不同类型的日志文件，用来存储不同类型的日志，分为 二进制日志 、 错误日志 、 通用查询日志 和 慢查询日志，这也是常用的4种。MySQL 8又新增两种支持的日志： 中继日志 和 数据定义语句日志 。使用这些日志文件，可以查看MySQL内部发生的事情。

这6类日志分别为：

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

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

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

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

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

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

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

3 通用查询日志

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

3.2 查看当前状态

SHOW VARIABLES LIKE '%general%';

3.3 启动日志

方式1：永久性方式

修改my.cnf或者my.ini配置文件来设置。在[mysqld]组下加入log选项，并重启MySQL服务。格式如下：

[mysqld]
general_log=ON
general_log_file=[path[filename]] #日志文件所在目录路径，filename为日志文件名

如果不指定目录和文件名，通用查询日志将默认存储在MySQL数据目录中的hostname.log文件中，hostname表示主机名。

方式2：临时性方式
SET GLOBAL general_log=on; # 开启通用查询日志
SET GLOBAL general_log_file=’path/filename’; # 设置日志文件保存位置
SET GLOBAL general_log=off; # 关闭通用查询日志

3.4 查看日志

通用查询日志是以 文本文件 的形式存储在文件系统中的，可以使用文本编辑器直接打开日志文件。

3.6 删除/刷新日志

手动删除文件
SHOW VARIABLES LIKE 'general_log%'; #在该目录下手动删除通用查询日志atguigu01.log。

使用如下命令重新生成查询日志文件，具体命令如下。刷新MySQL数据目录，发现创建了新的日志文件。前提一定要开启通用日志。

mysqladmin -uroot -p flush-logs

4 错误日志(error log)

4.1 启动日志

在MySQL数据库中，错误日志功能是 默认开启 的。而且，错误日志 无法被禁止 。

默认情况下，错误日志存储在MySQL数据库的数据文件夹下，名称默认为 mysqld.log （Linux系统）或hostname.err （mac系统）。如果需要制定文件名，则需要在my.cnf或者my.ini中做如下配置：

[mysqld]
log-error=[path/[filename]] #path为日志文件所在的目录路径，filename为日志文件名

修改配置项后，需要重启MySQL服务以生效。

4.2 查看日志

SHOW VARIABLES LIKE 'log_err%';

4.3 删除/刷新日志

MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以 直接删除。

mysqladmin -uroot -p flush-logs
#会报错

#补充操作
install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log

5 二进制日志(bin log)

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

它以 事件形式 记录并保存在 二进制文件 中。通过这些信息，我们可以再现数据更新操作的全过程。

binlog主要应用场景：

  • 数据恢复

  • 数据复制

5.1 查看默认情况

show variables like '%log_bin%';

  • log_bin_basename：是 binlog 日志的基本文件名，后面会追加标识来标识每一个文件。

  • log_bin_index：是 binlog 文件的索引文件，这个文件管理了所有的 binlog 文件的目录。

  • log_bin_trust_function_creators：限制存储过程，这是因为二进制日志的一个重要功能用于主从复制，而存储函数有可能导致主从的数据不一致(比如 now()函数)。所以当开启二进制日志后，需要限制存储函数的创建、修改、调用。

  • log_bin_user_v1_row_events：此只读变量已启用。

5.2 日志参数设置

方式1：永久性方式

修改MySQL的 my.cnf 或 my.ini 文件可以设置二进制日志的相关参数：

[mysqld] 
#启用二进制日志 
log-bin=atguigu-bin 
binlog_expire_logs_seconds=600 #此参数控制二进制文件的保留时长，单位是秒
max_binlog_size=100M #控制单个二进制日志大小，当前日志文件大小超过此变量时，执行切换操作。此参数的最大和默认值是1GB，该设置并不能严格控制 binlog 的大小。

设置带文件夹的 bin-log 日志存放目录

如果想改变日志文件的目录和名称，可以对my.cnf或my.ini中的log_bin参数修改如下：

[mysqld] 
log-bin="/var/lib/mysql/binlog/atguigu-bin"


#注意：新建的文件夹需要使用mysql用户，使用下面的命令即可。
chown -R -v mysql:mysql binlog

数据

方式2：临时性方式

如果不希望通过修改配置文件并重启的方式设置二进制日志的话，还可以使用如下指令，需要注意的是在mysql8中只有 会话级别 的设置，没有了global级别的设置。

SET sql_log_bin=0;

5.3 查看日志

当MySQL创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一个以“filename”为名称、以“.000001”为后缀的文件。

MySQL服务 重新启动一次 ，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的个数与MySQL服务启动的次数相同；如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件。

查看当前的二进制日志文件列表及大小。指令如下：

SHOW BINARY LOGS;

下面命令将行事件以 `伪SQL的形式`` 表现出来：

mysqlbinlog -v "/var/lib/mysql/binlog/atguigu-bin.000002"

上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

  • IN 'log_name' ：指定要查询的binlog文件名（不指定就是第一个binlog文件）　

  • FROM pos ：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）

  • LIMIT [offset] ：偏移量(不指定就是0)

  • row_count :查询总条数（不指定就是所有行）

上面我们讲了这么多都是基于binlog的默认格式，binlog格式查看：

show variables like 'binlog_format';

除此之外，binlog还有2种格式，分别是Statement和Mixed

  • Statement

    每一条会修改数据的sql都会记录在binlog中。

    优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

  • Row

    5.1.5版本的MySQL才开始支持row level 的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修改。

    优点：row level 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。

  • Mixed

    从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。

5.4 使用日志恢复数据

mysqlbinlog恢复数据的语法如下：

mysqlbinlog [option] filename|mysql –uuser -ppass;

这个命令可以这样理解：使用mysqlbinlog命令来读取filename中的内容，然后使用mysql命令将这些内容恢复到数据库中。

  • filename ：是日志文件名。

  • option ：可选项，比较重要的两对option参数是--start-date、--stop-date 和 --start-position、--stop-position。

    • --start-date 和 --stop-date ：可以指定恢复数据库的起始时间点和结束时间点。

    • --start-position 和 --stop-position ：可以指定恢复数据的开始位置和结束位置。

注意：使用mysqlbinlog命令进行恢复操作时，必须是编号小的先恢复，例如atguigu-bin.000001必须在atguigu-bin.000002之前恢复。

5.5 删除二进制日志

MySQL的二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制文件的方法。PURGE MASTER LOGS 只删除指定部分的二进制日志文件， RESET MASTER 删除所有的二进制日志文件。具体如下：

6 再谈二进制日志(binlog)

6.1 写入机制

binlog 的写入时机也非常简单，事务执行过程中，先把日志写到 binlog cache ，事务提交的时候，再把 binlog cache 写到 binlog 文件中。因为一个事务的 binlog 不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为 binlog cache。

6.3 两阶段提交

在执行更新语句过程，会记录 redo log 与 bin log 两块日志，以基本的事务为单位，redo log 在事务执行过程中可以不断写入，而 binlog 只有在提交事务时才写入，所以 redo log 与 binlog 的 写入时机 不一样。

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。

7 中继日志(relay log)

7.1 介绍

中继日志只在 主从服务器架构 的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入 本地的日志文件 中，这个从服务器本地的日志文件就叫 中继日志 。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的 数据同步。

文件名的格式是： 从服务器名 -relay-bin.序号 。中继日志还有一个索引文件： 从服务器名 -relay-bin.index ，用来定位当前正在使用的中继日志。

第18章 主从复制

2 主从复制的原理

Slave 会从 Master 读取 binlog 来进行数据同步。

三个线程

实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于 3 个线程 来操作，一个主库线程，两个从库线程。

二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上 加锁 ，读取完成之后，再将锁释放掉。

从库 I/O 线程 会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。

从库 SQL 线程 会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

复制三步骤

步骤1： Master 将写操作记录到二进制日志（ binlog ）。

步骤2： Slave 将 Master 的 binary log events 拷贝到它的中继日志（ relay log ）；

步骤3： Slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化

的，而且重启后从 接入点 开始复制。

复制的问题

复制的最大问题： 延时

2.2 复制的基本原则

  • 每个 Slave 只有一个 Master

  • 每个 Slave 只能有一个唯一的服务器ID

  • 每个 Master 可以有多个 Slave

3 一主一从架构搭建

3.2 主机配置文件

建议mysql版本一致且后台以服务运行，主从所有配置项都配置在 [mysqld] 节点下，且都是小写字母。

具体参数配置如下：

  • 必选
#[必须]主服务器唯一ID 
server-id=1 
#[必须]启用二进制日志,指名路径。比如：自己本地的路径/log/mysqlbin
log-bin=atguigu-bin
  • 可选
#[可选] 0（默认）表示读写（主机），1表示只读（从机） 
read-only=0 

#设置日志文件保留的时长，单位是秒 
binlog_expire_logs_seconds=6000 

#控制单个二进制日志大小。此参数的最大和默认值是1GB 
max_binlog_size=200M 

#[可选]设置不要复制的数据库 
binlog-ignore-db=test 

#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
binlog-do-db=需要复制的主数据库名字 

#[可选]设置binlog格式
binlog_format=STATEMENT

binlog 格式设置：

  1. STATEMENT模式 （基于SQL语句的复制(statement-based replication, SBR)）
binlog_format=STATEMENT

使用以下函数的语句也无法被复制：LOAD_FILE()、UUID()、USER()、FOUND_ROWS()、SYSDATE()(除非启动时启用了 --sysdate-is-now 选项)

  1. ROW模式（基于行的复制(row-based replication, RBR)）
binlog_format=ROW

5.1.5版本的MySQL才开始支持，不记录每条sql语句的上下文信息，仅记录哪条数据被修改了，修改成什么样了。

  1. MIXED模式（混合模式复制(mixed-based replication, MBR)）
binlog_format=MIXED

在Mixed模式下，一般的语句修改使用statment格式保存binlog。如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog。

3.3 从机配置文件

要求主从所有配置项都配置在 my.cnf 的 [mysqld] 栏位下，且都是小写字母。

  • 必选
#[必须]从服务器唯一ID
server-id=2
  • 可选
#[可选]启用中继日志
relay-log=mysql-relay

重启后台mysql服务，使配置生效。

3.4 主机：建立账户并授权

#在主机MySQL里执行授权主从复制的命令
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'从机器数据库IP' IDENTIFIED BY 'abc123';
#5.5,5.7

查询Master的状态，并记录下File和Position的值

show master status;

注意：执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化。

3.5 从机：配置需要复制的主机

步骤1：从机上复制主机的命令

CHANGE MASTER TO 
MASTER_HOST='主机的IP地址', 
MASTER_USER='主机用户名', 
MASTER_PASSWORD='主机用户名的密码', 
MASTER_LOG_FILE='mysql-bin.具体数字', 
MASTER_LOG_POS=具体值;

步骤2：

#启动slave同步 
START SLAVE;

可以执行如下操作，删除之前的relay_log信息。然后重新执行 CHANGE MASTER TO ...语句即可。

reset slave; #删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件

3.7 停止主从同步

  • 停止主从同步命令：
stop slave;
  • 如何重新配置主从

重新配置主从，需要在从机上执行：

stop slave; 

reset master; #删除Master中所有的binglog文件，并将日志索引文件清空，重新开始所有新的日志文件(慎用)

4 同步数据一致性问题

4.4 如何解决一致性问题

读写分离情况下，解决主从同步中数据不一致的问题， 就是解决主从之间 数据复制方式 的问题，如果按

照数据一致性 从弱到强 来进行划分，有以下 3 种复制方式。

方法 1：异步复制

异步模式就是客户端提交 COMMIT 之后不需要等从库返回任何结果，而是直接将结果返回给客户端。这样做的好处是不会影响主库写的效率，但可能会存在主库宕机，而 Binlog 还没有同步从库的情况，也就是此时的主库和从库数据不一致。这时候从从库中选择一个作为新主，那么新主则可能缺少原来主服务器中已提交的事务。所以，这种复制模式下的数据一致性是最弱的。

方法 2：半同步复制

MySQL 5.5 版本之后开始支持半同步复制的方式。原理是在客户端提交 COMMIT 之后不直接将结果返回给客户端，而是等待至少有一个从库收到了 Binlog，并且写入到中继日志中，再返回给客户端。

方法 3：组复制

异步复制和半同步复制都无法最终保证数据的一致性问题，半同步复制是通过判断从库响应的个数来决定是否返回给客户端。

组复制技术，简称 MGR（MySQL Group Replication）。是 MySQL 在 5.7.17 版本中推出的一种新的数据复制技术，这种复制技术是基于 Paxos 协议的状态机复制。

MGR 是如何工作的

首先我们将多个节点共同组成一个复制组，在 执行读写（RW）事务 的时候，需要通过一致性协议层（Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节点）的同意，大多数指的是同意的节点数量需要大于 （N/2+1），这样才可以进行提交，而不是原发起方一个说了算。而针对 只读（RO）事务 则不需要经过组内同意，直接 COMMIT 即可。

在一个复制组内有多个节点组成，它们各自维护了自己的数据副本，并且在一致性协议层实现了原子消息和全局有序消息，从而保证组内数据的一致性。

第19章 数据库备份与恢复

1 物理备份与逻辑备份

物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大，MySQL中可以用 xtrabackup 工具来进行物理备份。

逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。MySQL 中常用的逻辑备份工具为 mysqldump 。逻辑备份就是 备份sql语句 ，在恢复的时候执行备份的sql语句实现数据库数据的重现。

mysqldump 实现逻辑备份

mysqldump 是 MySQL 提供的一个非常有用的数据库备份工具。

2.1 备份一个数据库
mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名 称.sql

备份文件开始的一些语句以数字开头。这些数字代表了 MySQL 版本号，告诉我们这些语句只有在特定的 MySQL 版本或者比该版本高的情况下才能执行。

2.2 备份全部数据库

若想用mysqldump备份整个实例，可以使用 --all-databases 或 -A 参数：

mysqldump -uroot -pxxxxxx --all-databases > all_database.sql
mysqldump -uroot -pxxxxxx -A > all_database.sql

2.3 备份部分数据库

使用 --databases 或 -B 参数了，该参数后面跟数据库名称，多个数据库间用空格隔开。如果指定databases参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在。语法如下：

mysqldump –u user –h host –p --databases [数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql

2.4 备份部分表

mysqldump –u user –h host –p 数据库的名称 [表名1 [表名2...]] > 备份文件名称.sql

2.5 备份单表的部分数据

有些时候一张表的数据量很大，我们只需要部分数据。这时就可以使用 --where 选项了。where后面附带需要满足的条件。

mysqldump -uroot -p atguigu student --where="id < 10 " > student_part_id10_low_bak.sql

2.6 排除某些表的备份

如果我们想备份某个库，但是某些表数据量很大或者与业务关联不大，这个时候可以考虑排除掉这些表，同样的，选项 --ignore-table 可以完成这个功能。

mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql

2.7 只备份结构或只备份数据

只备份结构的话可以使用 --no-data 简写为 -d 选项；

只备份数据可以使用 --no-create-info 简写为 -t 选项。

#只备份结构
mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql

#只备份数据
mysqldump -uroot -p atguigu --no-create-info > atguigu_no_create_info_bak.sql

2.8 备份中包含存储过程、函数、事件

mysqldump备份默认是不包含存储过程，自定义函数及事件的。可以使用 --routines 或 -R 选项来备份存储过程及函数，使用 --events 或 -E 参数来备份事件。

3 mysql命令恢复数据

使用 mysqldump 命令将数据库中的数据备份成一个文本文件。需要恢复时，可以使用 mysql命令 来恢复备份的数据。

基本语法：

mysql –u root –p [dbname] < backup.sql

其中，dbname 参数表示数据库名。不指定数据库名时，表示还原文件中的所有数据库。

3.1 单库备份中恢复单库

如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称，如下所示：

mysql -uroot -p < atguigu.sql

否则需要指定数据库名称，如下所示：

mysql -uroot -p atguigu4 < atguigu.sql

3.2 全量备份恢复

如果我们现在有昨天的全量备份，现在想整个恢复，则可以这样操作：

mysql –u root –p < all.sql

3.3 从全量备份中恢复单库

可能有这样的需求，比如说我们只想恢复某一个库，但是我们有的是整个实例的备份，这个时候我们可以从全量备份中分离出单个库的备份。

#举例：
sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql > atguigu.sql 
#分离完成后我们再导入atguigu.sql即可恢复单个库

3.4 从单库备份中恢复单表

cat atguigu.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > class_structure.sql 
cat atguigu.sql | grep --ignore-case 'insert into `class`' > class_data.sql 
#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复

4 物理备份：直接复制整个数据库

但为了保证备份的一致性。需要保证：

方式1：备份前，将服务器停止。

方式2：备份前，对相关表执行 FLUSH TABLES WITH READ LOCK 操作。这样当复制数据库目录中的文件时，允许其他客户继续查询表。同时，FLUSH TABLES语句来确保开始备份前将所有激活的索引页写入硬盘。

这种方式方便、快速，但不是最好的备份方法，因为实际情况可能 不允许停止MySQL服务器 或者 锁住表 ，而且这种方法 对InnoDB存储引擎 的表不适用。

6 表的导出与导入

6.1 表的导出

1 使用SELECT…INTO OUTFILE导出文本文件

在MySQL中，可以使用SELECT…INTO OUTFILE语句将表的内容导出成一个文本文件。

2 使用mysqldump命令导出文本文件
3 使用mysql命令导出文本文件
mysql -uroot -p --execute="SELECT * FROM account;" atguigu> "/var/lib/mysqlfiles/account.txt"

6.2 表的导入

1 使用LOAD DATA INFILE方式导入文本文件
2 使用mysqlimport方式导入文本文件

7 数据库迁移

数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外， 验证迁移数据的完整性 和 退役原来旧的数据存储 ，也被认为是整个数据迁移过程的一部分。

8 删库了不敢跑，能干点啥？

传统的高可用架构是不能预防误删数据的，因为主库的一个 drop table 命令，会通过 binlog 传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。

8.1 delete：误删行

处理措施1：数据恢复

使用 Flashback工具 恢复数据。

原理：修改binlog 内容，拿回原库重放。如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来再执行。

使用前提：binlog_format=row 和 binlog_row_image=FULL。

8.2 truncate/drop ：误删库/表

背景：

delete 全表是很慢的，需要生成回滚日志、写 redo、写 binlog。所以，从性能角度考虑，优先考虑使用 truncate table 或 drop table 命令。

使用 delete 命令删除的数据，你还可以用 Flashback 来恢复。而使用 truncate/drop table 和 drop database 命令删除的数据，就没办法通过 Flashback 来恢复了。因为，即使我们配置了 binlog_format=row，执行者三个命令时，记录的 binlog 还是 statement 格式。binlog 里面就只有一个 truncate/drop 语句，这些信息是恢复不出数据的。

这种情况下，要想恢复数据，就需要使用 全量备份 ，加 增量日志 的方式了。这个方案要求线上有定期的全量备份，并且实时备份binlog。

在这两个条件都具备的情况下，加入有人中午12点误删了一个库，恢复数据的流程如下：

  1. 取最近一次 全量备份，假设这个库是一天一备，上次备份是当天 凌晨2点。

  2. 用备份恢复出一个 临时库。

  3. 从日志备份里面，取出凌晨2点之后的日志。

8.3 延迟复制备库

延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N秒的延迟 。

8.4 预防误删库/表的方法

  1. 权限分离

  2. 制定操作规范

  3. 设置延迟复制备份

参考

https://www.bilibili.com/video/BV1iq4y1u7vj  有课件  

posted on 2022-12-02 22:59  slivermirror  阅读(63)  评论(0)    收藏  举报

导航