MYSQL 深入学习
文章目录
- MYSQL 深入学习
- 一. 字符集
- 二. SQL大小写规范
- 三. SQL数据目录
- 四. 用户和权限管理
- 五. 逻辑架构
- 六. 存储引擎
- 七. 索引的数据结构
- 八. InnoDB 数据存储结构
- 九. 索引的创建和设计原则
- 十. 性能分析工具的使用
- 十一. 索引优化和查询优化
- 十二. 数据库调优的其他策略
- 十三. 事务基本知识
- 十四. MYSQL 事务日志
- 十五. 锁
- 十六. 多版本并发控制 *
- 十七. 其他数据库日志
- 十八. 主从复制
- 十九. 数据库备份和恢复
远程连接：
1.检查防火墙。
查看防火墙状态：
systemctl status firewalld关闭防火墙：
systemctl stop firewalld开机禁用防火墙：
systemctl disable firewalld
- 重启防火墙：
systemctl enable firewalld2.MYSQL8 默认不允许用root用户远程连接
USE mysql; UPDATE user SET host='%' WHERE user='root'; FLUSH PRIVILEGES;3.MYSQL8 的加密规则进行了修改
USE mysql; ALTER USER 'root@localhost' IDENTIFIED WITH mysql_native_password BY 'xxx'; FLUSH PRIVILEGES;
一. 字符集
在 MYSQL8 之前的版本中，默认字符集为 latin1 ，MYSQL8 开始，数据库将默认编码改为 utf8mb4。
-- 查看默认使用的字符集
show variables like '%char%';
在使用 MYSQL5.7 的时候，若没有修改指定字符集，在插入数据的时候就可能会因乱码而插入失败。
修改不同级别的字符集配置：①服务器级别 ②数据库级别 ③表级别 ④列级别
①（MYSQL5.7及之前版本）my.cnf 文件：
[mysqld]
character_set_server=utf8
② 数据库级别
-- 数据库级别
-- 创建数据库时指定
CREATE DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];
-- 修改已建数据库的字符集
ALTER DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];
③ 表级别
-- 表级别
-- 创建数据库时指定
CREATE TABLE 表名(列信息)
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称];
-- 修改已建表的字符集
ALTER TABLE 表名(列信息)
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称];
-- 或
ALTER TABLE 表名 CONVERT TO CHARACTER SET 'utf8';
④ 列级别
-- 列级别
-- 创建列时指定
CREATE TABLE 表名(
列名 数据类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
...
);
-- 修改已建列的字符集
ALTER TABLE 表名 MODIFY 列名 数据类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];
1. 比较规则
-- 查看支持的字符集
SHOW CHARSET;
| 后缀 | 描述 |
|---|---|
| _ai | 不区分重音 |
| _as | 区分重音 |
| _ci | 不区分大小写 |
| _cs | 区分大小写 |
| _bin | 以二进制方式比较 |
*说明：
utf8_unicode_ci 和 utf8_general_ci 对中、英文来说没有实质区别。
utf8_general_ci 校对速度快，但准确度稍差；
utf8_unicode_ci 准确度高，但校对速度稍慢。
一般来说，utf8_general_ci 就足够了，但是若应用中有 德语、法语或俄语，一定要用 utf8_unicode_ci ！
请求到相应过程中字符集的转化：
二. SQL大小写规范
1. Windows平台和Linux平台的区别
windows系统默认大小写不敏感，linux系统大小写敏感。
但是在SQL中，关键字和函数名是不用区分大小写的。
SHOW VARIABLES LIKE '%lower_case_table_names%';
lower_case_table_names = 0 ：表名和列名是大小写敏感的（默认值，适用于在Linux系统上运行）
lower_case_table_names = 1 ：表名不区分大小写，列名区分大小写（适用于在Windows系统上运行）
lower_case_table_names = 2 ：表名和列名都不区分大小写
三. SQL数据目录
[root@localhost ~]# find / -name mysql
1. MYSQL8 的主要目录结构
① MYSQL 数据库文件存放路径：/var/lib/mysql
-- 查看数据目录
SHOW VARIABLES LIKE 'datadir';
② 相关命令目录
相关命令目录：/usr/bin（mysqladmin、mysqlbinlog、mysqldump等）和 /usr/sbin
③ 配置文件目录
配置文件目录：/usr/share/mysql-8.0（命令及配置文件）和 /etc/mysql（如my.cnf）
2. 数据库和文件系统的关系
如 InnoDB、MyISAM 这样的存储引擎都是把表存储在文件系统（硬盘）上的，在需要读/写数据的时候，这些存储引擎就会将数据从文件系统中读取/写回。
-- 查看默认数据库
SHOW DATABASES;
mysql：
MYSQL 系统自带的核心数据库，存储了 MYSQL 的用户账户和权限信息，一些存储信息、事件的定义信息，一些运行时产生的日志信息、一些帮助信息和时区信息等。
information_schema：
MYSQL 系统自带的数据库，这个数据库保存着 MYSQL 服务器维护的所有其他数据库的信息，比如有哪些表、触发器、列、索引等。这些不是真实的用户数据，而是一些描述性的信息。在系统数据库 information_schema 中提供了一些以 innodb_sys 开头的表，用于表示内部系统表。
performance_schema：
MYSQL 系统自带的数据库，这个数据库主要保存 MYSQL 服务器运行过程中的一些状态信息，可以用来监控 MYSQL 服务的各类性能指标。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多少时间，内存的使用情况等等。
sys：
MYSQL 系统自带的数据库，这个数据库主要通过视图的形式把 information_schema 和 performance_schema 结合起来，帮助系统管理员和开发人员监控 MYSQL 的技术性能。
3. 数据库在文件系统中的表示
3.1 InnoDB 存储引擎模式 （MYSQL 默认引擎）
—— MYSQL5.7 中：
1.使用 CREATE DATABASE 数据库名; 语句创建了一个数据库后，文件系统发生的过程：
① 在数据目录下创建一个与数据库同名的子目录。
② 在与该数据库同名的子目录下创建一个名为 db.opt 文件，这个文件中包含了该数据库的各种属性，比如该数据库的字符集和比较规则。
2.使用 CREATE TABLE 表名; 语句后，文件系统发生的变化：
① 在与该数据库同名的子目录下创建 表名.frm 和 表名.ibd 文件
frm文件中存储的是表结构，而表数据的存放（MYSQL5.5/5.6 版本中是存放在上级目录的ibdata1文件（系统表空间）中，到了 MYSQL5.7 增加了子目录中的ibd文件（独立表空间））默认存放在ibd文件中，也可以存放在上级目录的ibdata1文件中。参数
innodb_file_per_table为 ON 时，表示使用独立表空间。
—— MYSQL8 中：
1.使用 CREATE DATABASE 数据库名; 语句创建了一个数据库后，文件系统发生的过程：
① 在数据目录下创建一个与数据库同名的子目录。
2.使用 CREATE TABLE 表名; 语句后，文件系统发生的变化：
① 在与该数据库同名的子目录下创建 表名.ibd 文件
frm 文件在 MYSQL8 中不存在了，那去哪里了呢？
我们可以去解析 ibd 文件。Oracle 官方将 frm 文件的信息及更多信息移动到了叫做序列化字典信息（SDI）的地方，SDI 被写在了 ibd 文件内部。MYSQL8 属于 Oracle 旗下的，所以同理。
为了从 ibd 文件中提取 SDI 信息，Oracle 提供了一个应用环境 ibd2sdi
ibd2sdi 官方文档：（MYSQL8 自带）
① 查看表结构
在存储 ibd 文件的目录下，执行：
ibd2sdi --dump-file=表名.txt 表名.ibd② 查看 txt 文件可以知道，MYSQL8 把之前版本的 frm 文件合并到 ibd 文件中去了。
3.2 MyISAM 存储引擎模式
在存储结构方面，MyISAM 与 InnoDB 一样，也是在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的 表名.frm 文件。
在 MyISAM 中的索引都是二级索引，该存储引擎的数据和索引都是分开来放的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件，同时表数据都放在对应的数据库子目录下。
—— MYSQL5.7 中：
若在数据库下建表，会在数据库对应目录下创建三个文件：① frm 文件 ② MYD 文件 ③ MYI 文件
表名.frm – 用来存储表结构
表名.MYD – 用来存储表数据（MYData）
表名.MYI – 用来存储表索引（MYIndex）
—— MYSQL8 中：
跟 InnoDB 一样，MYSQL8 也去掉了 frm 文件，放进了 sdi 文件中。
表名_363.sdi
表名.MYD – 用来存储表数据（MYData）
表名.MYI – 用来存储表索引（MYIndex）
4. 视图在文件系统中的表示
由于视图是一张虚拟的表，索引存储视图的时候是不需要存储真实的数据的，，只需要把表结构存储起来就好了。和表一样，视图的表结构会被放在数据库对应的子目录下，存储到 视图名.frm 的文件中。
5. 其他文件
数据目录下还有一些其他的文件，为了更好的运行程序。
① 服务器进程文件
每运行一个 MYSQL 服务器都会开启一个进程。MYSQL 服务器会把自己的进程 ID 写进到一个文件中。
② 服务器日志文件
在服务器运行时产生的各种各样的日志文件，比如常规的查询日志、错误日志、二进制日志、redo 日志等。
③ 默认/自动生成的 SSL 和 RSA 证书和密钥文件
主要是为了客户端和服务端安全通信创建的文件。
四. 用户和权限管理
1. 用户管理
MYSQL 将用户分为普通用户和 root 用户。
1.1 登录 MYSQL 服务器
mysql -h hostname|hostIP -P port -u username -p DatabaseName -e "SQL语句"
-h 参数：后面接主机名
-P 参数：后面接 MYSQL 服务的端口号
-u 参数：后面接用户名
-p 参数：回车后输入密码
DatabaseName 参数：指明登录到哪一个数据库中，默认登陆 mysql 数据库
-e 参数：登陆后直接执行后面的 SQL 语句，然后退出登录
1.2 创建用户
CREATE USER '用户名'[@'hostname'] [INENTIFIED BY '密码'][,用户名 [INENTIFIED BY '密码']];
hostname 默认值为 %
1.3 修改用户
-- 修改用户名
UPDATE mysql.user SET user='name1' WHERE user='name2';
FLUSH PRIVILEGES;
1.4 删除用户
① DROP 删除（推荐）
DROP USER 'user'[@'hostname'][,'user'[@'hostname']] ...;
② DELETE 删除（不推荐，会有残留信息保存）
-- 需要有对 mysql.user 的删除权限
DELETE FROM mysql.user WHERE Host='hostname' NAD User='username';
FLUSH PRIVILEGES;
1.5 修改密码
—— 当前用户
① ALTER USER 修改当前用户密码（推荐）
ALTER USER USER() IDENTIFIED BY 'newpassword';
② SET 语句修改当前用户密码
SET PASSWORD = 'newpassword';
—— 其他用户
① ALTER USER 修改其他用户密码（推荐）
ALTER USER 'username'[@'hostname'] IDENTIFIED BY 'newpassword';
② SET 语句修改其他用户密码
SET PASSWORD FOR 'username'[@'hostname']='newpassword';
2. 权限管理
2.1 权限列表
SHOW PRIVILEGES;
2.2 授予权限的原则
权限控制主要出于安全因素，需要遵循以下原则：
1.只赋予能满足的最小权限。
2.创建用户的时候限制用户的登录主机，一般是限制成指定 ip 或者 ip 内网段。
3.为每个用户设置满足密码复杂度的密码。
4.定期清理不需要的用户，回收权限或者删除用户。
2.3 授予权限
给用户授权的方式有2种，通过角色赋予用户给用户授权和直接给用户授权。
GRANT 权限1,权限2.. ON 数据库名.表名 TO 'username'@'hostname'
[IDENTIFIED BY '密码口令']
[WITH GRANT OPTION];
若未发现该用户，会新建一个用户。
2.4 查看权限
SHOW GRANTS [FOR 'username'@'hostname'];
2.5 收回权限
使用 REVOKE 语句取消用户的权限，收回权限后，用户账户的记录将从 db、host、table_priv 和 columns_priv 表中删除，但是用户账户记录仍然在 user 表中保存。
注意：在将用户账户从 user 表删除之前，应该回收相应用户的所有权限（DROP USER 语句）。
-- 回收权限
REVOKE 权限1,权限2,... ON 数据库名.表名 FROM 'username'@'hostname';
3. 权限表
MYSQL 服务器通过权限表来控制用户对数据库的访问，权限表存放在 mysql 数据库中。MYSQL 数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的有 user 表、db 表。除此之外还有 tables_priv表、columns_priv 表和 proc_priv 表等。在 MYSQL 服务启动时，服务器将这些数据库中权限信息的内容读入内存。
| 表名 | 描述 |
|---|---|
| user | 用户账号以及权限信息 |
| global_grants | 动态全局授权 |
| db | 数据库层级的权限 |
| tables_priv | 表层级的权限 |
| columns_priv | 列层级的权限 |
| proc_priv | 存储的过程和函数权限 |
| proxies_priv | 代理用户的权限 |
| default_roles | 账号连接并认证后默认授予的角色 |
| role_edges | 角色子图的边界 |
| password_history | 密码更改信息 |
4. 访问控制
当 MYSQL 允许一个用户执行各种操作时，它将首先核实用户向 MYSQL 服务器发送的连接请求，然后确认用户的操作请求是否被允许。这个过程被称为 MYSQL 中的访问控制过程。MYSQL 的访问控制分为两个阶段：连接核实阶段 和 请求核实阶段。
4.1 连接核实阶段
当用户试图连接 MYSQL 时，服务器基于用户的身份以及用户是否能提供正确的密码验证身份来确定接受还是拒绝连接。MYSQL 服务器接收到用户请求后，会使用 user 表中的 host、user 以及 authentication_string 这三个字段匹配客户端提供的信息。
4.2 请求核实阶段
一旦建立了连接，服务器就进入了请求核实阶段。对此连接上来的每一个请求，服务器检查这些请求要执行什么操作、是否有足够的权限来执行，这些权限可以来自 user、db、tables_priv 和 columns_priv 表。
确认权限时，MYSQL 首先检查 user 表，如果指定的权限没有在 user 表中被赋予，那么 MYSQL 就会继续检查 db 表，db 表是下一安全层级，其中的权限限定于数据库层级；如果在该层级没有找到限定的权限，则 MYSQL 继续检查 tables_priv 表和 columns_priv 表。如果所有的权限表都检查完毕，但还是没有找到允许的权限操作，MYSQL 将返回错误信息，用户请求的操作不能执行。
5. 角色管理 （MYSQL8）
5.1 角色的理解
角色是 MYSQL8 引入的新功能，在 MYSQL 中，角色是权限的集合，可以为角色添加或移除权限。用户可以被赋予角色，同时也会被赋予该角色所拥有的权限。
引入角色的目的是方便管理者拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性。
5.2 创建角色
CREATE ROLE 'rolename'[@'hostname'] [,'rolename'[@'hostname']] ...;
5.3 给角色赋予权限
GRANT 权限1,权限2,... ON 数据库名.表名 TO 'rolename'[@'hostname'];
5.4 查看角色权限
SHOW GRANTS FOR 'rolename'[@'hostname'];
5.5 回收角色权限
REVOKE 权限1,权限2,... ON 数据库名.表名 FROM 'rolename';
5.6 删除角色
DROP ROLE 'rolename';
如果删除了这个角色，则该角色下的所有用户都失去了这个角色拥有的权限。
5.7 给用户赋予角色
GRANT 'rolename1'[,'rolename1'] TO 'username'[@'hostname'][,'username'[@'hostname']];
SELECT CURRENT_ROLE(); -- 查看被赋予的角色是否激活
MYSQL 创建了角色后，默认情况下都是没有激活的，也就是无法使用，必须要手动激活，然后赋予了这些角色的用户才会真正获得相应的权限。
5.8 激活角色
① 方式一： SET DEFAULT ROLE 激活
SET DEFAULT ROLE 'rolename'[@'hostname'] TO 'rolename'[@'hostname'];
注意：用户需要退出重新登录，才能看到赋予的角色。
② 方式二： 将 activate_all_roles_on_login 设置为 ON
SHOW VARIABLES LIKE 'activate_all_roles_on_login'; -- 默认为OFF
SET GLOBAL activate_all_roles_on_login=ON; -- 对所有角色永久激活
查看当前会话已激活的角色：
SELECT CURRENT_ROLE();
5.9 撤销角色
REVOKE 'rolename'[@'hostname'] FROM 'username'[@'hostname'];
5.10 设置强制角色（mandatory role）（很少用）
强制角色是给每个创建账户的默认角色，不需要手动设置。强制角色无法被 REVOKE 和 DROP。
方式1：服务启动前设置：
[mysqld]
mandatory_roles='role1,role2@localhost,role3@%'
方式2：运行时设置：
SET PERSIST mandatory_roles = 'role1,role2@localhost,role3@%'; -- 系统重启后仍有效
-- 或 SET GLOBAL mandatory_roles = 'role1,role2@localhost,role3@%'; -- 系统重启后失效
6. 配置文件的使用
…
7. 系统变量
…
五. 逻辑架构
1. 逻辑架构剖析
MYSQL 是典型的 C/S 架构，服务器端使用的 mysqld。
无论服务器和客户端以怎样的方式通信，都是客户端先向服务器进程发送一段文本（SQL 语句），然后服务器进程处理后再向客户端进程发送一段文本（处理结果）。
以查询请求为例：
客户端 →
→ 第一部分：连接管理（处理连接）
→ 第二部分：解析和优化（查询缓存、语法解析、查询优化）
→ 第三部分：存储引擎（InnoDB/MyISAM/Memory…）
→ 文件系统
Connectors
指不同的语言与 SQL 交互。MYSQL 是一个网络程序，在 TCP 之上定义了自己的应用层协议。所以要使用 MYSQL，我们可以编写代码跟 MYSQL Server 进行 TCP 连接，之后按照其定义好的协议进行交互，或者比较方便的办法就是调用 SDK ，比如 JDBC、PHP 等各语言的 MYSQL Connector，或者通过 ODBC。但是通过 SDK 来访问 MYSQL ，本质上还是在 TPC 连接上通过 MYSQL 协议跟 MYSQL 交互的。
MYSQL Server 可以分为三层：
① 连接层
客户端访问 MYSQL 服务器前，做的第一件事就是建立 TCP 连接。
经过三次握手连接成功后，MYSQL 服务器对 TCP 传输过来的账号密码做身份验证、权限获取。
用户名和密码不对，会收到 Access denied for user 错误，客户端程序结束执行。
用户名和密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限。
多个系统可以跟 MYSQL 服务器建立连接，每个服务器建立的连接也不止一个。所以为了解决 TCP 无限创建与 TCP 频繁创建和销毁带来的资源耗尽、性能下降的问题。MYSQL 服务器里有专门的 TCP 连接池限制连接数，采用长连接模式复用 TCP 连接，来解决上述问题。
TCP 连接收到请求后，必须分配一个线程专门与这个客户端交互，所以还会有一个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁的开销。这些都在 MYSQL 的连接管理组件中，其职责是负责认证、管理连接、获取权限信息。
② 服务层
第二层架构主要完成大多数核心服务功能，如 SQL 接口，并完成缓存查询、SQL 的分析和优化以及部分内置函数的执行。
在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化：比如确定查询表的顺序、是否利用索引等，最后生成相应的执行操作。（如果是 SELECT 语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提高系统性能）
-
MYSQL interface（MYSQL 接口）
- 接收用户的 SQL 命令，并且返回用户需要查询的结果。
- MYSQL 支持 DML、DDL、存储过程、视图、触发器、自定义函数等多种 SQL 语言接口。
-
Parser（解析器）
- 在 SQL 命令传到解析器的时候会被解析器验证和解析，并为其创建语法树，并根据数据字典丰富查询语法树，会验证该客户端是否有执行该查询的权限。创建好语法树后，MYSQL 还会对 SQL 查询进行语法上的优化，进行查询重写。
-
Optimizer（查询优化器）
- SQL 语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个执行计划。
- 这个执行计划表明应该使用哪些索引进行查询（全表检索还是索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询。
- 使用 ”选取-投影-连接“ 策略进行查询：先根据 WHERE 语句进行选取，而不是将表全部查询后再过滤；然后先根据指定属性来进行投影，而不是将所有属性全部取出后再进行过滤；最后将这两个查询条件连接起来生成最后的查询结果。
-
Caches & Buffers（查询缓存组件）
- 这些缓存机制有一系列小缓存组成，比如表缓存，记录缓存，key缓存，权限缓存等。
- 查询缓存可以在不同客户端共享。MYSQL5.7.20 开始不推荐使用查询缓存，并在 MYSQL8 中删除。
③ 引擎层
与其他数据库不同，MYSQL 的架构可以在多种不同的场景中应用并发挥良好作用，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。同时开源的 MYSQL 还允许开发人员设置自己的存储引擎。
插件式存储引擎层，真正的负责了 MYSQL 中数据的存储和提取，对物理服务器级别维护的底层数据进行操作，服务器通过 API 与存储引擎通信。不同的引擎具有的功能不同，可以根据自己的实际需求进行选取。
存储层：所有的数据，数据库、表的定义、表每一行的内容、索引都是存在文件系统上的，以文件的方式存在的，并完成与存储引擎的交互。
2. SQL 执行流程
2.1 MYSQL5.7 中 SQL 执行流程
MYSQL 的查询流程：
① 查询缓存
服务器如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入解析器阶段。（由于查询缓存往往效率不高，所以在 MYSQL8 就抛弃了这个功能）
② SQL 语句解析
通过 ’词法分析” 和 “语法分析” 生成语法树。
③ 查询优化
确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引检索
在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段。
逻辑查询优化是通过 SQL 等价变换提高查询效率，就是换一种查询写法执行效率可能更高；
物理查询优化是通过索引和表连接方式等技术进行优化，重点需要掌握索引的使用。
④ 执行器
再执行之前需要判断用户是否具备权限，若没有，返回权限错误。若有，就执行 SQL 查询并返回结果。
在 MYSQL8 以下的版本中，若设置了查询缓存，就会对查询结果进行缓存。
执行的时候会调用存储引擎的 API 对文件系统中的数据进行查询。
总结：SQL 语句 → 查询缓存 →解析器 → 优化器 → 执行器
2.2 MYSQL8 中 SQL 执行流程
了解查询语句底层执行的过程，查看是否开启计划（开启它可以让 MYSQL 收集在 SQL 执行所使用的资源情况，0-关闭 1-开启）：
SELECT @@PROFILING;
-- 或 SHOW VARIABLES LIKE '%PROFILING%';
-- 开启 PROFILING
SET PROFILING=1;
多次执行相同的 SQL 查询
SELECT ...;
SELECT ...;
查看查询记录：
-- 查看查询记录
SHOW PROFILES;
-- 查看详细查询流程
SHOW PROFILE [FOR QUERY 'Query_id'];
还可以查询以下参数的利用情况
SHOW PROFILE [type1][,[type2]..] [FOR QUERY 'Query_id']; type:{ ALL, -- 所有参数 | BLOCK IO, -- IO相关开销 | CONTEXT SWITCHES, -- 上下文切换相关开销 | CPU, -- cpu相关开销 | IPC, -- 发送和接收相关开销 | MEMORY, -- 内存相关开销 | PAGE FAULTS, -- 页面错误相关开销 | SOURCE, -- 和Source_function,Source_file,Source_line相关开销 | SWAPS -- 交换次数相关开销 }
3. 数据库缓冲池（Buffer Pool）
InnoDB 存储引擎是以页为单位来管理存储引擎的，增删改查操作本质上都是在访问页面。而磁盘 IO 需要消耗的时间很多，而在内存中操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们使用，DBMS 会申请占用内存来作为数据缓冲池。在真正访问页面之前，需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才能访问。
这样的好处是可以让磁盘的活动最小化，从而减少与磁盘直接进行 IO 的时间。
3.1 缓冲池 vs 查询缓存
不是同一个概念
① 缓冲池 （Buffer Pool）
在 InnoDB 存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种数据的缓存：
缓冲池（InnoDB_buffer_pool）：数据页、插入缓存、自适应哈希索引、索引页、锁信息、数据字典信息。
缓存原则：“位置 * 频次” 原则
位置决定效率，缓冲池就是为了在内存中可以直接访问数据。
频次决定优先级顺序，因为缓冲池的大小是有限的，按存储容量排序为磁盘大于内存大于缓冲池，既然无法将所有数据都加载到缓冲池里，就涉及到优先级顺序，会优先对频次高的热数据进行加载。
缓冲池的预读特性：
缓冲池的作用就是提升 IO 效率，而读取数据的时候存在一个“局部性原理”，就是说当使用了一部分数据，大概率会使用它周围的一些数据，因此采用预读的机制提前加载，可以减少未来可能的 IO 操作。
② 查询缓存
查询缓存是提前把查询结果缓存起来，这样下次不需要执行就能查询到结果，查询缓存是以键值对存储的，键为 SQL 语句，值为查询对应的结果，命中条件苛刻，所以命中率很低。
3.2 缓冲池是如何读取数据的
数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或者磁盘将页面存放到缓冲池中再进行读取。
数据一致性问题？
当对数据库中的记录进行修改时，首先会修改缓冲池中页里面的记录数据，然后数据库会以一定的频率（策略）刷新到磁盘上，即刷盘。（注意，并不是每次发生更新操作，都会立即进行磁盘回写。缓冲池会采用一种叫做 checkpoint 的机制将数据写回磁盘上，这样做的好处就是提升了数据库的整体性能）
比如，当缓冲池不够用的时候，需要释放掉一些不常用的页，此时就可以强行采用 checkpoint 的方式，将不常用的脏页写回到磁盘上，然后再从缓冲池中将这些页释放掉，这里的脏页指的是缓冲池中被修改过的页，即与磁盘上的数据页不一致。
3.3 查看/设置缓冲池大小
MyISAM 存储引擎只缓存索引，不缓存数据，对应的键缓存参数为 key_buffer_size 。
SHOW VARIABLES LIKE 'key_buffer_size';
InnoDB 存储引擎可以通过查看 innodb_buffer_pool_size 变量查看缓冲池大小。
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
修改缓冲池大小：
SET GLOBAL innodb_buffer_pool_size = 268435456; -- 256MB
或
[server]
innodb_buffer_pool_size = 268435456; -- 256MB
3.4 多个 Buffer Pool 实例
Buffer Pool 的本质是 InnoDB 向操作系统申请的一块连续的内存地址，在多线程环境下，访问 Buffer Pool 中的数据都需要加锁处理，在 Buffer Pool 特别大而且多线程并发访问特别高的情况下，单一的 Buffer Pool 可能会影响请求的处理速度。所以在 Buffer Pool 特别大的时候，可以把他们拆分成若干个小的 Buffer Pool，每个 Buffer Pool 都称为一个实例，它们都独立地申请内存空间，独立地管理各种链表。所以在多线程访问时不会相互影响，从而提高并发处理能力。
可以在服务器启动的时候通过设置 innodb_buffer_pool_instances 的值来修改 Buffer Pool 实例的个数。
[server]
innodb_buffer_pool_instances = 2 -- 2个实例
查看缓冲池实例个数：
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
每个缓冲池实例的大小为：innodb_buffer_pool_size / innodb_buffer_pool_instances
不过也不是说实例越多越好，分别管理各个 Buffer Pool 也是需要性能开销的，InnoDB 规定：当 innodb_buffer_pool_size 小于 1G 的时候设置多个实例是无效的，InnoDB 会默认把 innodb_buffer_pool_instances 的值修改为1。而我们鼓励在 Buffer Pool 大于等于 1G 的时候设置多个 Buffer Pool 实例。
3.5 引申
“黑盒” 下的更新数据流程
在查询数据的时候，会先去 Buffer Pool 中去查询。如果 Buffer Pool 中不存在，存储引擎会先将数据从磁盘中加载到 Buffer Pool 中，然后再去 Buffer Pool 中查询数据返回给客户端；同理，在更新某个数据的时候，如果这个数据不存在 Buffer Pool 中，存储引擎同样会先将数据从磁盘中加载捡来，然后修改内存中的数据。被修改的数据会在之后统一刷入磁盘。
假设我们修改 Buffer Pool 中的数据成功，但是在将修改的数据刷入磁盘之前 MYSQL 就挂了怎么办呢。按照先前的逻辑，更新之后的数据只存在于 Buffer Pool 中，此时 MYSQL 宕机了，这部分的数据将永久的丢失。
或者想要回滚到之前的版本，这就要依赖于 MYSQL 的数据持久化和事务回滚。
六. 存储引擎
连接管理、查询缓存、语法解析、查询优化这些不涉及真实数据存储的功能称为 MYSQL server 的功能；把真实存取数据的功能称为存储引擎的功能。
1. 查看存储引擎
SHOW ENGINES;
MYSQL8 支持九种存储引擎：InnoDB、MRG_MYISAM、MEMORY、BLACKHOLE、MyISAM、CSV、ARCHIVE、PERFORMANCE_SCHEMA、FEDERATED
2. 设置系统默认的存储引擎
-- 查看当前默认的存储引擎
SHOW VARIABLES LIKE '%storage_engine%';
-- 或 SELECT @@default_storage_engine;
修改默认存储引擎：
SET DEFAULT_STORAGE_ENGINE=MyISAM;
或修改 my.cnf：
default_storage_engine=MyISAM
3. 引擎介绍
① InnoDB：具备外键支持功能的事务存储引擎
- InnoDB 是 MYSQL 的默认事务型引擎，它被设计用来处理大量的短期事务，可以确保事务的完整提交（commit）和回滚（rollback）
- 除了新增和查询外，还需要更新、删除操作，那么应该优先选择 InnoDB 引擎
- 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑 InnoDB 引擎
- InnoDB 是为处理巨大数据量的最大性能设计
- 对比 MyISAM 的存储引擎，InnoDB 写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引
- MyISAM 只缓存索引，不缓存真实数据；InnoDB 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。
② MyISAM：主要的非事务处理存储引擎
- MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但是 MyISAM 不支持事务，行级锁，外键。有一个毫无疑问的缺陷就是崩溃后无法安全恢复
- 优点是访问的速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用
- 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高
- 应用场景：只读应用或者以读为主的业务
…
七. 索引的数据结构
1. 为什么使用索引
减少磁盘 IO 次数，加快查询效率。
2. 索引以及其优缺点
索引是帮助 MYSQL 高效获取数据的数据结构。
索引的本质：索引是数据结构，且满足特定的查找算法。这些数据结构以某种方式指向数据，来实现高速查找算法。
索引实在存储引擎中实现的，每种存储引擎的索引不一定完全相同，并且每一种存储引擎不一定支持所有的索引类型。
2.1 优点
- 降低数据库 IO 成本
- 通过创建唯一索引，保证数据库表中每一行的数据唯一性
- 加快表与表之间的连接，即对于有依赖关系的子表和父表联合查询时，可以提高查询效率
- 在使用分组查询和排序子句进行查询时，可以显著减少查询中分组和排序时间，降低 cpu 消耗
2.2 缺点
- 创建和维护索引要耗费时间，并且随着数据量的增加，耗费的时间也会增加
- 索引需要占用磁盘空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸
- 虽然索引大大提高了查询效率，同时却会降低更新表的速度，因为更新数据的时候索引也要动态维护
索引可以提高查询的速度，但是会影响插入记录的速度。这种情况下，最好的办法就是先删除表中所有的索引，然后插入数据，插入完成后在创建索引。
3. InnoDB 中的索引
3.1 索引之前的查找
① 在一个页中查找
- 以主键为搜索条件
- 可以在页目录中使用二分法快速定位到相应的槽，然后再遍历该槽对应分组中的记录即可。
- 以其他列为搜索条件
- 由于数据页中没有对非主键列建立所谓的页目录，所以无法通过二分法快速定位，只能从最小记录开始依次遍历单链表中的每条数据，然后对比记录是否符合条件，这种效率很低。
② 在很多页中查找
两个步骤：定位到数据所在页、在所在页内找到相应的数据。
没有索引的情况下，不论是根据主键查找还是其他列的值进行查找，由于不能快速定位到记录所在页所以只能从第一个页沿着双向链表一直往下找，这种方式是非常耗时的。
3.2 InnoDB 的索引方案
InnoDB 数据页内的记录是按照主键值的顺序排列，并且是以一种紧密、连续的方式存储的。这种存储方式有助于提高数据的访问效率。可以通过偏移量使用二分法快速定位到主键值，而如果使用单向链表存储，在查询的时候只能遍历查询。
虽然顺序存储在插入和删除操作中不如单链表，但 InnoDB 通过一系列优化措施来确保这些操作的高效性。
① 插入记录时，InnoDB 会尝试找到合适的位置插入新记录，当数据页中的空间不足时，InnoDB 会分裂数据页，将部分记录移到新的数据页中。
② 删除记录时，InnoDB 通常不会真正地物理删除记录，而是标记记录为“逻辑删除”状态。被标记为删除的记录所占用的空间可以被重用。
InnoDB 默认每个数据页为16KB，假设每条记录160B，即每个数据页可以存放100条记录。
假设存放目录项的数据页可以存放1600项
- 1层 B+ 树：100 条记录
- 2层 B+ 树：100 × 1600 ≈ 160,000 条记录
- 3层 B+ 树：100 × 1600 × 1600 ≈ 256,000,000 条记录
- 4层 B+ 树：100 × 1600 × 1600 × 1600 ≈ 409,600,000,000 条记录
此外，InnoDB 本身不支持 Hash 索引，但是提供自适应 Hash 索引。当某个数据经常被访问到，当满足一定条件的时候，就会将这个数据页的地址存放到 Hash 表中。这样下次查询的时候，就可以直接找到这个页面所在的位置。这样让 B+ 树页具备了 Hash 索引的优点。
InnoDB 的自适应 Hash 索引默认开启
3.3 常见索引概念
索引按照物理方式实现，索引可以分为两种：聚簇（聚集）索引和非聚簇（非聚集）索引。非聚簇索引也成为二级索引或辅助索引。
① 聚簇索引
聚簇索引是一种数据存储方式（所有的用户记录都存储在了叶子节点上），也就是所谓的索引即数据，数据即索引
聚簇表示数据行和相邻的键值聚簇的存储在一起
特点：
- 使用记录主键的大小进行记录和页的排序
- 页内的记录是按照主键的大小顺序排列的顺序存储。
- 各个存放记录的页也是根据页中用户记录的主键大小顺序排列的双向链表
- 存放目录项记录的页分为不同的层次，在同一层次的页也是根据页中目录项记录的主键大小顺序排列的双向链表
- B+ 树的叶子节点存储的是完整的用户记录（所有列的值）
这种聚簇索引并不需要我们在 MYSQL 语句中显式地使用 INDEX 语句来创建，InnoDB 存储引擎会自动为我们创建聚簇索引。
优点：数据访问快、对主键排序查找和范围查找速度快、节省大量 IO
缺点：插入速度严重依赖于插入顺序（按照主键插入最快，否则会出现页分裂，严重影响性能，所以对于 InnoDB 表一般定义自增的 id 列为主键）、更新主键的代价高（一般定义 InnoDB 的主键不可更新）、二级索引查找需要两次索引查找
MYSQL 目前只有 InnoDB 支持聚簇索引，MyISAM 不支持聚簇索引。
每个 MYSQL 的表只能有一个聚簇索引，一般情况下是该表的主键。
如果没有定义主键，InnoDB 会选择非空的唯一索引替代，如果没有这样的的索引，InnoDB 会隐式的定义一个主键作为聚簇索引。
InnoDB 的主键尽量选用有序的自增顺序 id，不建议使用无需的 id，如uuid、md5、hash等。
② 二级索引（辅助索引）
以非主键列在创建一棵 B+ 树（该 B+ 树只存储该非主键列以及其主键，其他的列数据不会一块存储进来），然后通过这个 B+ 树查找到该记录的主键，再通过聚簇索引的 B+ 树通过主键查找该记录槽。（查询2棵B+树）
通过二级索引找到主键再用主键查询记录的过程叫做回表
③ 联合索引
同时以多个列的大小作为排序规则，也就是同时为多个列建立索引。本质上也是二级索引。
3.4 InnoDB 的B+树索引注意事项
① 根节点位置始终不变
当为某个表创建B+树索引的时候，都会为这个索引创建一个根节点页面。
- 最开始表中没有数据的时候，B+树的根节点中既没有用户记录，也没有目录项记录。
- 随后向表中插入用户数据的时候，先把用户记录存到根节点中。
- 当根节点的可用空间用完后继续插入数据时，此时会将根节点的所有记录复制到一个新分配的页中，然后对这个新分配的页进行页分裂操作，得到另一个新页。此时新插入的记录会根据键值（聚簇索引的主键/二级索引的索引列的值）的大小分配到页分裂的两个页面中，而根节点则会升级为存储目录项记录的页。
页分裂：？？？
1.数据页已满：当一个数据页内的可用空间不足以容纳新记录时。
2.需要维持顺序性：当插入的新记录需要按照主键顺序插入到已存在的记录之间时，如果插入位置没有足够的空间，则需要进行页分裂。
——页分裂通常有两种方式：
垂直分裂：将数据页分割成两个部分，一部分保留原有记录的一部分，另一部分包含其余记录和新插入的记录。
水平分裂：将数据页中的记录分成两部分，并将一部分记录移动到新的数据页中。
——每次页分裂后会有未利用的空间怎么办呢？
InnoDB 的空间管理机制：
- 预留空间：
- InnoDB 通常会预留一些空间，以便在插入新记录时可以直接使用，而不需要立即进行页分裂。这意味着在数据页接近满时，预留的空间可以暂时容纳新记录，从而减少页分裂的频率。
- 重用空闲空间：
- InnoDB 会重用数据页中的空闲空间。当数据页中的记录被删除时，InnoDB 会标记这些空间为可用，并在后续插入新记录时优先使用这些空间。
- 自适应填充因子：
- InnoDB 可以调整填充因子（Fill Factor），使得数据页在插入时预留更多空间，从而减少页分裂的次数。填充因子是指数据页在插入时被允许填满的程度。例如，如果填充因子为 50%，则数据页在填满 50% 时就会停止插入，并预留剩余的空间用于未来的插入操作。
- 合并数据页：
- 当数据页中的记录被大量删除后，InnoDB 会尝试合并相邻的数据页，以减少空闲空间的浪费。
② 内节点中目录项记录的唯一性
假如在一个二级索引的B+树中，在一个内节点里有两个目录项存储的键值相同，若要再插入一个该键值的记录就无法判断插入的记录是在哪一个目录下的，因此为了保证目录项记录的唯一性，会将二级索引的键跟主键合并作为二级索引的键。
4. MyISAM 中的索引
B+树索引适用的存储引擎有：InnoDB、MyISAM、Memory
即使多个存储引擎同时适用同一种索引，他们的实现原理也是不同的。InnoDB 和 MyISAM 默认的索引是B+tree 索引，而 Memory 默认的索引为 Hash 索引。
MyISAM 引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址。
4.1 MyISAM 索引的原理
MyISAM 在插入数据的时候，数据和索引是分开存储的。
数据信息按照插入顺序直接存储在数据文件（MYD 文件）中；而索引信息存储到另一个索引文件（MYI 文件）中。MyISAM 会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值＋数据记录地址的组合。
MyISAM 中不存在聚簇索引，主键索引和二级索引在结构上没有任何区别。MyISAM 的索引文件仅仅保存数据记录的地址。
4.2 MyISAM 与 InnoDB 对比
MyISAM 的索引方式都是非聚簇的，与 InnoDB 包含一个聚簇索引是不同的。小结两者的区别：
① 在 InnoDB 中，只需要根据主键值对聚簇索引进行一次查找就能找到相应的记录，而在 MyISAM 中却只能通过非聚簇索引查找后再回表操作才能找到相应的记录，因为 MyISAM 中建立的索引全部都是二级索引。
② InnoDB 的数据文件本身就是索引文件，而 MyISAM 索引文件和数据文件是分离的，索引文件仅仅保存数据记录的地址。
③ InnoDB 的非聚簇索引（二级索引）data域存储相应记录的主键的值，而 MyISAM 索引记录的是地址。也就是说，InnoDB 所有的非聚簇索引都引用主键作为data域。
④ MyISAM 的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的；反观 InnoDB 是通过获取主键之后再去聚簇索引中找记录的，虽然也不慢，但是还是比不上直接用地址去访问。
⑤ InnoDB 要求必须要有主键（MyISAM 可以没有）。如果没有显式指定，则 MYSQL 系统会自动选择一个非空且唯一标识的列作为主键，若不存在这样的列，则 MYSQL 会自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长6个字节，为长整型。
5. 索引的代价
空间代价：
每建立一个索引都是一个B+树，每一个B+树的节点都是一个数据页，一个页默认占用16KB的存储空间，一棵很大的树有许多的数据页组成，那就是很大的一片存储空间。
时间代价：
每次对表中的数据进行增删改操作时，都需要去修改各个B+树索引。
八. InnoDB 数据存储结构
1. 数据库的存储结构：页
索引信息和数据记录都是存放在文件上的，确切来说是存放在页结构上。另一方面，索引是在存储引擎中实现的，MYSQL 服务器上的存储引擎负责对表中数据的读取和写入工作，不同存储引擎中存放的格式是不同的，甚至有的存储引擎比如 Memory 都不用磁盘来存储数据。
1.1 磁盘与内存交互的基本单位：页
InnoDB 将数据划分为若干个页，InnoDB 中页的默认大小为16KB。
以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中16KB的内容刷新到磁盘里。**在数据库中，不管是读一行，还是读多行，都是将这些行所在的页进行加载，也就是说，数据库管理存储空间的基本单位是页，数据库 IO 的最小单位是页。**一个页中存储多个行记录。
1.2 页的大小
不同的 DBMS 的页大小不同，MYSQL 的 InnoDB 存储引擎中，默认的页大小为16KB，可以如下命令查看：
SHOW VARIABLES LIKE '%innodb_page_size';
SQL server 中页的大小为8KB，而在 Oracle 中我们用 块 来代表页，Oracle 支持的块的大小为 2KB、4KB、8KB、16KB、32KB、64KB。
1.3 页的上层结构
在数据库中，还存在着区、段和表空间的概念。行、页、区、段、表空间如下：
区是比页大一级的存储结构，InnoDB 中，一个区会分配 64 个连续的页，所以一个区默认的大小为 64KB。
段是由一个或多个区组成的，区在文件系统中是一个连续分配的空间（在 InnoDB 中是连续的64个页），不过在段中不要求区与区是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在，当我们创建数据表、索引的时候，就会相应创建对应的段。
表空间是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间，数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。
2. 页的内部结构*
页按类型划分：数据页、系统页、Undo页和事务数据页等。数据页是最常使用的。
数据页的16KB大小的存储空间被划分为七个部分：① 文件头 ② 页头 ③ 最大最小记录（行记录） ④ 用户记录（行记录） ⑤ 空闲空间 ⑥ 页目录 ⑦ 文件尾
| 名称 | 占用大小 | 说明 |
|---|---|---|
| 文件头 | 38字节 | 描述页的信息 |
| 页头 | 56字节 | 页的状态信息 |
| 最大最小记录 | 26字节 | 这是两个虚拟的行记录 |
| 用户记录 | —— | 存储行记录的内容 |
| 空闲空间 | —— | 页中还没有被使用的空间 |
| 页目录 | —— | 存储用户记录的相对位置 |
| 文件尾 | 8字节 | 校验页是否完整 |
2.1 文件头和文件尾
文件头：描述页的各种信息（比如页的编号、上一页、下一页是谁等等）
还有页偏移量、页类型、前后指针、校验和、页长度
2.2 最大最小记录、用户记录和空闲空间
用户记录按照一定的行格式存储，相互之间形成单链表。
2.3 页头和页目录
页目录：将链表存储的记录（不包含已删除的记录）划分为多个组，通过二分查找定位到某个组中然后在该组中查找数据（第一组默认仅由最小记录组成，最后一个组包含了最大记录可以有18个记录，其余组有48个记录），而页目录主要存储每个组最后一个记录的地址偏移量。
页头：页目录的槽数量、空闲空间的开始地址、本页中物理记录的数量（包括最小、最大、删除过的记录），垃圾链表的表头、本页中用户记录的数量（不包括最小、最大、删除过的记录）、B+树层级、索引id等等。
2.4 从数据页角度看B+树查询
如果通过B+树索引来查询行记录，首先是从B+树的根开始逐层检索，直到找到叶子节点，也就是找到相应的数据页为止，将数据页加载到内存中。页目录的槽采用二分查找的方式先找到一个粗略的记录分组，然后在分组中通过链表遍历的方式查找记录。
普通索引和唯一索引在查询效率上有什么不同？
唯一索引在普通索引的基础上加上了唯一性约束，所以检索到了会停止检索，而普通索引存在用户记录中关键字相同的情况，根据页的结构，当我们读取到一条数据，会将整个数据页一并加载到内存中读取，因此在普通字段上的查找也就是在内存中多次判断 “下一条记录” 的操作，对cpu而言，这些操作的消耗是可以忽略不计的。所以对一个字段进行检索，采用普通索引还是唯一索引在检索效率上没有差别。
3. InnoDB 行格式（记录格式）*
-- 查询默认行格式
SELECT @@innodb_default_row_format; -- 默认为 dynamic
① Compact 行格式（紧凑型）：记录的额外信息（变长字段长度列表、NULL值列表、记录头信息）和记录的真实数据
- 变长字段长度列表：被变长数据类型修饰的列叫做变长字段，所有变长字段的真实数据占用的字节长度存放在这个列表。
- NULL值列表：可以把为NULL值的列（非“NOT NULL”约束的列）统一管理起来，存在一个标记为NULL的列表中。
- 记录头信息：逻辑删除、最小记录标记、本页中当前记录位置（最小最大记录为0和1，用户记录从2开始）、记录类型、下一个记录的偏移量、n_owned（页目录中每组最后一个记录的头信息会存储该组有多少条记录）。
② Dynamic 和 Compressed 行格式（动态型和压缩型）
- Dynamic 和 Compressed 行格式与 Compact 差不多（除了处理行溢出方面）
- 行溢出：对于存放在 BLOB 中的数据采用了完全的行溢出的方式，也就是在数据页中只存放20个字节的指针（溢出页地址），实际的数据都存放在 Off Page （溢出页）中。Compressed 行记录格式的另一个功能是存储在其中的行数据会以 zlib 的算法进行压缩，因此对于 BLOB、TEXT、VARCHAR 这类大长度类型的数据能够进行非常有效的存储。
行溢出：一个数据页的大小为16KB，也就是16384个字节，而一个VARCHAR(M)类型的列最多存储65533个字节（65535字节 - 变长字段长度列表的2个字节），这样就出现了一个页存放不了一条记录，这种现象被称为行溢出。
在 Compact 和 Redundant 行格式中，对于占用存储空间十分大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中进行分页存储，然后记录的真实数据处用20个字节存储指向这些页的地址，
③ Redundant 行格式（冗余）
MYSQL 5.0 以前的行存储格式
4. 区、段和碎片区
4.1 为什么要有区
B+树索引中页与页中间是通过双向链表来连接的，但是相邻两个页之间的物理地址可能里的非常远。如果在B+树索引中使用范围查询，只需要查询到第一个记录然后沿着双向链表向后扫就行了，而如果相邻两个页之间的物理地址特别远的话，也就是所谓的随机 IO，随机 IO 是非常慢的，所以我们尽量让链表中相邻的页的物理位置也相邻。这样查找的时候才可以使用所谓的顺序 IO。
引入区的概念，一个区就是物理位置上连续的64个页。因为 InnoDB 默认页大小位16KB，所以一个区的大小为64 × 16KB = 1MB。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配。甚至在表中数据特别多的时候，可以一次性分配多个连续的区，虽然可能造成一点空间的浪费（数据不足以填满整个区），但是从性能的角度来看，可以消除很多随机 IO。
4.2 为什么要有段
InnoDB 对 B+树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区，存放叶子节点的区的集合是一个段，存放非叶子节点的区的集合也是一个段。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。
段其实不对应表空间某一连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成。
4.3 碎片区
一个区默认占用1MB的空间，一个索引换产生2个段，所以默认情况下一个只存了几条数据的小表也需要2M的空间吗？为了考虑对于数据量太小的表太浪费存储空间的情况，InnoDB 提出了一个碎片区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的。碎片区直属于表空间，并不属于任何一个段。
- 在刚开始插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。
- 当某个段已经占用了32个碎片区页面之后，就会申请以完整的区为单位来分配存储空间。
所以段更精确来说应该是某些零散的页面以及一些完整的区的集合
4.4 区的分类
区大致分为4类：
① 空闲的区（FREE）：还没有用到这个区中的任何字段
② 有剩余空间的碎片区（FREE_FRAG）：表示碎片区中还有可用的空间
③ 没有剩余空间的碎片区（FULL_FRAG）：表示碎片区中所有的页面都被使用，没有空闲的区
④ 附属于某个段的区（FSEG）：每一个索引都可以分为叶子节点段和非叶子节点段
处于 FREE、FREE_FRAG 和 FULL_FRAG 这三种状态的区都是独立的，直属于表空间。而处于 FSEG 状态的区是附属于某个段的。
5. 表空间
表空间是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。
表空间是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或者多个段，但是一个段只能属于一个表空间。表空间数据库由一个或者多个表空间组成，表空间从管理上分为系统表空间、独立表空间、撤销表空间和临时表空间。
5.1 独立表空间
独立的表空间可以在不同的数据库之间进行迁移。
空间可以回收，DROP TABLE 后可以自动回收表空间。对于使用独立表空间的表，不管怎么删除，表空间的碎片都不会太影响性能，而且还有机会处理。
独立表空间结构：段、区、页
真实表空间对应的文件大小：在数据目录中会发现，当我们新建了一个表的时候对应的 ibd 文件就占了96K，仅仅6个页面的大小，这是因为一开始表空间占用的空间很小，因为表里面没有数据，不过这些 ibd 文件都是自扩展的，随着表中的数据增多，表空间对应的文件也逐渐增大。
-- 查看InnoDB表空间的类型
SHOW VARIABLES LIKE 'innodb_file_per_table';
5.2 系统表空间
系统表空间的结构跟独立表空间的结构基本类似，只不过因为整个 MYSQL 进程只有一个系统表空间，在系统表空间会额外记录一些有关整个系统信息的页面。用于存放 InnoDB 数据字典。
九. 索引的创建和设计原则
1. 索引的声明和使用
1.1 索引的分类
MYSQL 索引包括：普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
- 功能逻辑上：普通索引、唯一索引、主键索引、全文索引。
- 物理实现方法上：聚簇索引、非聚簇索引。
- 作用字段个数上：单列索引、联合索引。
① 普通索引
不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中。
② 唯一性索引
使用 UNIQUE 参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表中可以有多个唯一索引。
③ 主键索引
主键索引就是一种特殊的唯一性索引，在唯一性约束的基础上又加了不为空的约束，也就是 NOT NULL + UNIQUE，一张表最多有一个主键索引（因为数据存储在文件中只能以一种顺序进行存储）。
④ 单列索引
在表的单个字段创建索引，一个表可以有多个单列索引。
⑤ 多列（联合、组合）索引
在表的多个字段组合上创建一个索引，该索引指向创建时对应的多个索引，可以通过这几个字段进行查询，但是只有查询条件中使用了第一个字段时才会被使用。使用组合索引时遵循最左前缀集合。
⑥ 全文索引
是目前搜索引擎的关键技术，有自然语言的全文索引和布尔全文索引。
⑦ 空间索引
1.2 创建索引
MYSQL 支持多种方式在单个或多个列创建索引：① 建表是 CREATE TABLE 中指定索引列 ② 使用 ALTER TABLE 在存在的表上创建索引或使用 CREATE INDEX 在存在的表上添加索引
① 建表时创建
使用 CREATE TABLE 创建表，除了可以定义列的数据类型之外，还可以定义主键约束，不管哪种约束，在定义约束的同时相当于在指定列创建了一个索引。（隐式创建）
显式创建表索引：
CREATE TABLE 表名 [列名 数据类型]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [索引名] (列名 [(长度)]) [ASC | DESC];
- UNIQUE 、 FULLTEXT 、 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引。
- INDEX 和 KEY 为同义词，两者作用相同，用来指定创建索引。
- 索引名不指定则默认为列名。
- 列名表示需要创建索引的字段列。
- 长度表示索引长度，只有字符串类型的字段才能指定。
- ASC 或 DESC 表示指定升序或降序的索引值来存储。
查看索引：
SHOW CREATE TABLE 表名;
-- 或 SHOW INDEX FROM 表名;
② 建表后创建
ALTER TABLE 表名 ADD INDEX 索引名(列名);
-- 或 CREATE INDEX 索引名 ON 表名 (列名);
1.3 删除索引
ALTER TABLE 表名 DROP INDEX 索引名;
-- 或 DROP INDEX 索引名 ON 表名;
添加了 AUTO_INCREMENT 约束字段的唯一索引不可以被删除。
2. MYSQL8 索引新特性
2.1 支持降序索引 （仅限于InnoDB）
ALTER TABLE 表名 ADD INDEX 索引名(a ASC, b DESC);
2.2 隐藏索引
MYSQL5.7 之前只能通过显式的方式去删除索引。此时，如果删除索引后发生了错误，只能通过显式创建索引将删除的索引重新创建回来，如果数据表中的数据量十分庞大，这种操作就会消耗系统过多的资源，操作成本非常高。
从 MYSQL8 开始支持隐藏索引，只需要将待删除的索引设置为隐藏索引，是优化查询器不再使用这个索引，确认将索引设置为隐藏索引后系统不受任何影响，就可以彻底删除索引。这种先将索引设置为隐藏索引，再删除索引的方式就是软删除。
同时，如果要验证某个索引删除之后的查询性能影响，也可以暂时先隐藏索引。
注意：主键不能被设置为隐藏索引。当表中没有显式主键时，表中第一个非空唯一索引会成为隐式主键，也不能设置为隐藏主键。
-- 设置隐藏索引
CREATE TABLE 表名(
...,
INDEX [索引名] (列名[(长度)]) [INVISIBLE | VISIBLE]
);
ALTER TABLE 表名 ADD INDEX [索引名] (列名[(长度)]) [INVISIBLE | VISIBLE];
-- 或 CREATE INDEX 索引名 ON 表名 (列名([长度])) [INVISIBLE | VISIBLE];
修改可见性：
ALTER TABLE 表名 ALTER INDEX 索引名 [INVISIBLE | VISIBLE];
注意：当索引被隐藏时，它的内容仍然和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响增删改的性能。
3. 索引的设计原则
3.1 适合加索引的情况
-- 示例
use db_test02;
CREATE TABLE student(
id INT auto_increment,
stu_id INT NOT NULL,
stu_name VARCHAR(50) NOT NULL,
major INT NOT NULL,
info VARCHAR(100),
create_time datetime NOT NULL,
PRIMARY KEY (id)
);
SELECT COUNT(*) from student; -- 1000,000 条数据
① 字段索引有唯一性限制
如果某个字段是唯一的，可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录。
业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。 ——Alibaba
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但是提高查找速度是明显的。
② 频繁作为 WHERE 查询条件的字段
某个字段在 SELECT 语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了，尤其是在数据量大的时候，创建普通索引就可以大幅提高查询的效率。
-- 未加索引之前:
SELECT * FROM student WHERE stu_id>1444400 AND stu_id<1444600; -- 1667ms
SELECT * FROM student WHERE stu_id>1644400 AND stu_id<1644600; -- 1824ms
-- 加索引
ALTER TABLE student ADD INDEX indx_sid(stu_id); -- 11219ms
SHOW INDEX FROM student;
-- 加索引之后:
SELECT * FROM student WHERE stu_id>1444400 AND stu_id<1444600; -- 125ms
SELECT * FROM student WHERE stu_id>1644400 AND stu_id<1644600; -- 76ms
③ 经常用到 GROUP BY 和 ORDER BY 的列
索引就是让数据按照某种顺序进行存储和检索，因此当我们使用 ORDER BY 对数据进行分组查询的时候，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或排序的字段进行索引。如果待排序的列有个，那么可以在这些列上建立组合索引。
GROUP BY：
-- 未加索引之前:
SELECT major FROM student GROUP BY major; -- 1701ms
-- 加索引
ALTER TABLE student ADD INDEX indx_major(major); -- 12293ms
SHOW INDEX FROM student;
-- 加索引之后:
SELECT major FROM student GROUP BY major; -- 72ms
ORDER BY：
-- 未加索引之前:
SELECT * FROM student ORDER BY create_time DESC LIMIT 1000; -- 2612ms
-- 加索引
ALTER TABLE student ADD INDEX indx_cret_time(create_time DESC); -- 12424ms
SHOW INDEX FROM student;
-- 加索引之后:
SELECT * FROM student ORDER BY create_time DESC LIMIT 1000; -- 150ms
联合索引：
-- 未加索引之前:
ALTER TABLE student DROP INDEX indx_sid;
SHOW INDEX FROM student;
SELECT * FROM student WHERE stu_id>1500000 AND stu_id<1500100 ORDER BY create_time DESC; -- 1262ms
-- 加索引
ALTER TABLE student ADD INDEX indx_sid_cret_time_desc(stu_id, create_time DESC); -- 15332ms
SHOW INDEX FROM student;
-- 加索引之后:
SELECT * FROM student WHERE stu_id>1500000 AND stu_id<1500100 ORDER BY create_time DESC; -- 81ms
④ UPDATE、DELETE 的 WHERE 条件列
⑤ DISTINCT 字段需要创建索引
⑥ 多表 JOIN 连接操作时，创建索引注意事项
- 首先，连接表的数量尽量不要超过三张，因为每增加一张表就相当于增加了一次嵌套循环，数量级增长会非常快，严重影响查询效率。
- 其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的条件下，没有 WHERE 条件过滤是非常可怕的。
- 最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。
⑦ 使用列的类型小的创建索引
这里的类型大小指的是该类型表示的数据范围的大小。比如能用 INT 就不要用 BIGINT，能用 MEDIUMINT 就不要用 INT。因为：
- 数据类型越小，在查询时进行的比较操作越快
- 数据类型越小，索引占用的储存空间越小，在一个数据页内就可以存放更多的数据，从而减少磁盘 IO 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率
这个建议对于表的主键来说更加适用，因为不仅是聚簇索引会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键值使用更小的数据类型，也就意味着节省更多的存储空间和更高效的 IO。
⑧ 使用字符串前缀创建索引
若字符串需要很大的空间（BLOB、TEXT），在我们需要为这个字符串创建索引的时候，就会有2个问题：
- B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大
- 如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间
我们可以通过截取字段前一部分内容建立索引，这个就是前缀索引。这样在查找记录时，虽然不能精确定位到记录的位置，但是能定位到前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串。既节约空间，又减少了字符串比较的时间，还能大体上解决排序问题。
怎么计算截取不同长度的选择性呢？
-- 公式 SELECT COUNT(DISTINCT LEFT(列名, 索引长度)) / COUNT(*) FROM 表名;拓展：Alibaba《Java开发手册》
在 VARCHAR 字段上建立索引的时候，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。一般对字符串类型数据，长度为20的索引，区分度会高达90%以上可以用
COUNT(DISTINCT LEFT(列名, 索引长度)) / COUNT(*)来确定
索引列前缀对排序的影响：
因为二级索引中不包含完整的索引列信息，只包含索引的前缀，所以无法支持使用索引排序，只能使用文件排序。
⑨ 区分度高（散列性高）的列适合创建索引
在记录行数一定的情况下，列的基数越大，该列中的值越分散；列的基数越小，该列中的值越集中。这个列的基数指标非常重要，最好使用列的基数大的列建立索引。
-- 公式
SELECT COUNT(DISTINCT 列名) / COUNT(*) FROM 表名;
区分度越接近1越好，一般超过33%就算是比较高效的索引了。
联合索引也要把区分度高的列放在前面。
⑩ 使用最频繁的列放到联合索引的最左侧
这样也可以较少的建立一些索引。同时，由于最左前缀原则，可以增加联合索引的使用率。
⑩ 在多个字段都要创建索引的情况下，联合索引优于单值索引
3.2 限制索引的数目
索引的数量不是越多越好的，建议单表的索引数量不要超过6个，因为：
- 每个索引都要占用磁盘空间，索引越多，需要的磁盘空间就越大
- 索引会影响 INSERT、DELETE、UPDATE 等语句的优化，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
- 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引进行评估，以生成一个最好的执行计划，如果有很多个索引都可以用于查询，会增加 MYSQL 优化器生成执行计划的时间,降低查询性能。
3.3 哪些情况不适合创建索引
① 在 WHERE / GROUP BY / ORDER BY 中使用不到的字段
② 数据量小的表最好不要使用索引
③ 有大量重复数据的列不要创建索引（数据重复度高于10%）
④ 避免对经常更新的表创建过多的索引
⑤ 不建议用无序的值建立索引
⑥ 删除不再使用或者很少使用的索引
⑦ 不要定义冗余 / 重复的索引
十. 性能分析工具的使用
1. 数据库服务器的优化步骤
整个流程分为观察和行动两个部分。
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
SHOW STATUS LIKE 'last_query_cost'; -- 结果为查询的数据页个数
SQL 查询是一个动态的过程，从页加载的角度，我们知道：
- 位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在内存中，会比在磁盘中读取效率高很多。
- 批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的，而采用顺序读取的方式,批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。
所以遇到 IO 不需要担心，首先考虑数据存放的位置，如果是经常使用的数据要尽量放到缓冲池中，其次我们要充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也得到了提升。
4. 定位执行慢的 SQL：慢查询日志
MYSQL 的慢查询日志，是用来记录 MYSQL 中响应时间超过阈值（long_query_time）的语句，则会被记录到慢查询当中去。long_query_time 默认值为 10，意思是运行 10 秒以上的语句，认为是慢查询。
它的主要作用是定位执行时间很慢的 SQL 查询，来进行有针对的优化，从而提高系统的整体效率，可以先定位慢查询，在用 EXPLAIN 进行全面分析。
默认情况下，MYSQL 没有开启慢查询日志，需要手动设置这个参数，如果不是调优需要，一般不建议开启该参数，因为开启慢查询或多或少对性能有影响。
4.1 开启慢查询参数
-- 查看是否开启
SHOW VARIABLES LIKE '%slow_query_log%';
-- 开启
SET GLOBAL slow_query_log=ON;
修改 long_query_time 阈值
-- 查看默认值
SHOW VARIABLES LIKE '%long_query_time%';
-- 修改
SET GLOBAL long_query_time=1;
SET SESSION long_query_time=1;
4.2 查看慢查询条目
SHOW GLOBAL STATUS LIKE 'Slow_queries';
4.3 慢查询日志分析工具：mysqldumpslow
在生产环境中，如果要手动分析日志，查找，分析 SQL，显然很麻烦，MYSQL 提供了日志分析工具 mysqldumpslow。
[root@localhost ~]# mysqldumpslow -help
4.4 关闭慢查询日志
SHOW VARIABLES LIKE '%slow_query_log%';
SET slow_query_log=OFF;
-- 重启MYSQL服务
-- 然后查看参数状态
SHOW VARIABLES LIKE '%slow_query_log%';
4.5 删除慢查询日志
手动删除慢查询日志即可
使用 mysqladmin flush-logs 来重新生成查询日志文件
[root@localhost ~]# mysqladmin -uroot -p flush-logs slow
慢查询日志都是通过使用 mysqladmin flush-logs 命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在于新的日志文件中，如果需要旧的查询日志，就必须事先备份。
5. 查看 SQL 执行成本：SHOW PROFILE
SHOW PROFILES;
SHOW PROFILE [参数1,参数2...] [FOR QUERY 查询id];
日常开发要记住的：
① converting HELP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了
② Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表
③ Copying to tmp table on disk： 把内存中临时表复制到磁盘上，警惕！
④ locked。
如果在 show profile 中出现了以上任何一条，则 SQL 语句需要优化
不过 SHOW PROFILE 命令将被弃用，我们可以从 information_schema 中的 profiling 数据表进行查看。
6. 分析查询语句：EXPLAIN
6.1 概述
定位了查询慢的 SQL 之后，我们就可以使用 EXPLAIN 或 DESCRIBE 工具做针对性的分析查询语句。（DESCRIBE 语句的使用方法和 EXPLAIN 语句是一样的）
MYSQL 中有专门负责优化 SELECT 语句的优化器模块，功能是通过分析系统中收集到的统计信息，为客户端请求的 Query 提供它认为最优的执行计划。MYSQL 为我们提供了 EXPLAIN 语句来帮助我们查看某个语句的具体执行计划。
包括：
- 表的读取数据
- 数据读取操作的操作类型
- 哪些索引可以被使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
6.2 基本语法
EXPLAIN SQL语句;
| 列名 | 描述 |
|---|---|
| id | 在一个大的查询语句中每个 SELECT 关键字都有一个唯一的 id |
| select_type | SELECT 关键字对应的查询的类型 |
| table | 表名 |
| partitions | 匹配的分区信息 |
| type | 针对单表的访问方法 |
| possible_keys | 可能用到的索引 |
| key | 实际上使用的索引 |
| key_len | 实际上使用到的索引长度 |
| ref | 当使用索引列等值查询的时候，与索引列进行等值匹配的对象信息 |
| rows | 预估的需要读取的记录条数 |
| filtered | 某个表通过搜索条件过滤后剩余记录条数的百分比 |
| Extra | 一些额外信息 |
6.3 EXPLAIN 各列作用
table
查询的每一条记录都对应着一张单表。
id
在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的id，id的每个号码表示一趟独立的查询，一个 SQL 的查询趟数越少越好。
查询优化器可能会对涉及子查询的查询语句进行重写
select_type
每个 SELECT 关键字代表着一个小的查询，而每个 SELECT 关键字的 FROM 子句中都包含了若干张表，每一张表都对应着执行计划输出中的每一条记录，对于在同一个 SELECT 关键字中的表来说，他们的id值是相同的。
只要知道了小查询的 select_type 属性，就知道了这个小查询在大查询中扮演了什么样的角色。
partitions（略）
匹配的分区信息，不涉及分区时为 NULL
type *
执行查询时的访问方法
完整的访问方法如下（按最优性能的顺序排列）：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL
possible_keys 和 key
可能用到的索引和真实用到的的索引
key_len *
实际使用到的索引长度，用来检查是否充分利用到了索引，值越大越好（指的是跟自己的索引进行比较），主要针对于联合索引。
ref
当使用索引列等值查询时，与索引列进行等值匹配的对象信息。
rows *
预估的需要读取的条目数（越小越好）
filtered
某个表通过搜索条件过滤后剩余记录条数的百分比。
Extra *
通过这些额外信息来更加准确的理解 MYSQL 到底如何执行给定的查询语句。
我们说回表操作其实是随机 IO，比较耗时，可以通过索引条件下推的改进，来省去回表的操作。
7. EXPLAIN 进一步使用
7.1 EXPLAIN 四种输出格式
传统格式、JSON 格式、TREE 格式和可视化输出
① 传统格式：简单明了，就是表格的样式，概要说明查询计划
② JSON 格式
EXPLAIN FORMAT=JSON SELECT ...;
第一种格式中缺少了一个衡量执行计划好坏的重要标准——成本。而JSON 格式是4种格式里面输出信息最详尽的格式，里面包含了执行的成本信息。
③ TREE 格式
④ 可视化格式
8. 分析优化器查询计划：trace
OPTIMIZER_TRACE 是 MYSQL 的一项追踪功能，它可以追踪优化器做出的各种策略（访问表的方法、各种开销、各种转化等），并跟踪结果记录到 information_schema.optimizer_trace 表中。
此功能默认关闭。开启 trace，并设置格式为 JSON，同时设置 trace 最大能够使用的内存空间，避免解析过程中因为默认内存过小而不能完整展示。
SET optimizer_trace="enabled=ON",end_markers_in_json=on;
SET optimizer_trace_max_mem_size=1000000;
-- 执行SQL语句
-- 查看 information_schema.optimizer_trace 表
SELECT * FROM information_schema.optimizer_trace\G
9. MYSQL 监视分析视图：sys schema
关于 MYSQL 的性能监控和问题诊断，我们一般都从 performance_schema 中获取想要的数据
9.1 Sys schema 视图使用场景
索引情况：
-- 查询冗余索引
SELECT * FROM sys.schema_redundant_indexes;
-- 查询未使用过的索引
SELECT * FROM sys.schema_unused_indexes;
-- 查询索引的使用情况
SELECT index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
FROM sys.schema_index_statistics WHERE table_schema='数据库名';
表相关：
-- 查询表的访问量
SELECT table_schema,table_name,sum(io_read_requests+io_write_requests) AS io
FROM sys.schema_table_statistics GROUP BY table_schema,table_name ORDER BY io DESC;
-- 查询占用buffer pool较多的表
SELECT object_schema,object_name,allocated,data
FROM sys.innodb_buffer_stats_by_table ORDER BY allocated LIMIT 10;
-- 查看表的全表扫描情况
SELECT * FROM sys.statements_with_full_table_scans WHERE db="数据库名";
语句相关：
-- 监控SQL执行的频率
SELECT db,exec_count,query FROM sys.statement_analysis ORDER BY exec_count DESC;
-- 监控使用了排序的SQL
SELECT db,exec_count,first_seen,last_seen,query FROM sys.statements_with_sorting LIMIT 1;
-- 监控使用了临时表或者磁盘临时表的SQL
SELECT db,exec_count,tmp_tables,tmp_disk_tables,query
FROM sys.statement_analysis WHERE tmp_tables>0 OR tmp_disk_tables>0
ORDER BY (tmp_tables+tmp_disk_tables) DESC;
IO 相关：
-- 查看消耗磁盘IO的文件
SELECT file,avg_read,avg_write,avg_read+avg_write AS avg_io
FROM sys.io_global_by_file_by_bytes ORDER BY avg_read LIMIT 10;
InnoDB 相关：
-- 行锁阻塞情况
SELECT * FROM sys.innodb_lock_waits;
风险提示：
通过sys库去查询的时候，MYSQL 会消耗大量的资源去收集相关的信息，严重的可能会导致业务请求被阻塞。建议生产上不要频繁的去查询 sys 或者 performance_schema 和 information_schema 来完成监控、巡检等工作。
十一. 索引优化和查询优化
哪些维度可以进行数据库调优？
- 索引失效、没有充分利用到索引 —— 索引建立
- 关联查询太多的 JOIN（设计缺陷或不得已需求）—— SQL优化
- 服务器调优以及各个参数设置（缓冲、线程数）—— 调整 my.cnf
- 数据过多 —— 分表分库
虽然 SQL 查询优化的技术有很多，但是大方向可以分为物理查询优化和逻辑查询优化。
① 物理查询优化：通过索引和表连接方式等技术进行优化，重点掌握索引的使用。
② 逻辑查询优化：通过 SQL 等价变换提升查询效率，就是换一种查询写法执行效率可能更高。
1. 索引失效
MYSQL 中提高性能的一个最有效的方式是对数据库设计合理的索引。
- 使用索引可以快速的定位表中的某条数据，从而提高数据库查询速度
- 如果查询时没有使用索引，查询语句就会扫描表中的所有记录，在数据量大的情况下，这样的查询速度会很慢
大多数情况下（默认）都是用B+树来创建索引。其实用不用索引，最终都是优化器说了算，而优化器，是基于cost开销，他不是基于规则，也不是基于语义，怎么样开销小就怎么来。另外，SQL 语句是否使用索引，跟数据库版本、数据量和数据选择度都有关系。
1.1 全值匹配
1.2 最佳左前缀规则
在 MYSQL 建立联合索引的时候会遵循最佳左前缀规则，即最左优先，在检索数据的时候从联合索引的最左边开始匹配。
对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序依次满足，一旦跳过某个字段，索引后面的字段都无法使用。
1.3 主键插入顺序
对于一个使用 InnoDB 存储引擎的表来说，在没有显式创建索引时，表中的数据实际上是存储在聚簇索引的叶子节点上的。而记录是存储在数据页中的，数据页和记录又是按照主键值从小到大的顺序来排列的，所以如果插入的主键顺序是依次增大的话，那每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了，会发生页分裂，严重影响性能。所以建议给主键加上 AUTO_INCREMENT。
1.4 计算、函数、类型转换（自动或者手动）导致索引失效。
ALTER TABLE student ADD INDEX indx_info(info (3));
-- 比较
EXPLAIN SELECT * FROM student WHERE info like 'cl2%'; -- type = range
EXPLAIN SELECT * FROM student WHERE LEFT(info,3)='cl2'; -- type = ALL
使用 LEFT() 函数会导致索引失效
1.5 类型转换导致索引失效
EXPLAIN SELECT * FROM student WHERE name=1234; -- name为varchar类型，1234为数值类型
1.6 范围条件右边的索引失效
在创建联合索引的时候，务必把涉及范围的字段写在最后。
1.7 不等于（!= 或 <>）索引失效
主要取决于优化器在对比使用索引和不使用索引时的选择，该情况不完全会使索引失效，比如在使用覆盖索引的时候。
1.8 IS NULL 可以使用索引，IS NOT NULL 不能使用索引
最好在设计数据库的时候就将字段设置 NOT NULL 约束然后设置一个默认值。
1.9 LIKE 以通配符%开头索引失效
只有通配符%不在开头，索引才会起作用。
Alibaba《Java开发手册》：
严禁使用左模糊或者全模糊，如果需要请使用搜索引擎来解决。
1.10 OR 前后存在非索引的列，索引失效
1.11 数据库和表的字符集统一使用 utf8mb4
不同的字符集之间进行转换也会使索引失效。
1.12 小结
一般而言，对于单列索引，尽量选择当前查询中区分度高的列当索引；对于联合索引而言，尽量让当前查询中区分度高的列位置靠前。在选择联合索引的时候，尽量选择能包含当前查询中 WHERE 子句中更多字段的索引，如果某个字段可能出现范围查询，尽量把这个字段放在索引次序的最后。
总之要尽量避免索引失效的情况。
2. 关联查询优化
2.1 左外连接
对于左外连接来讲，可以给被驱动表的连接条件的列添加索引，也可以给驱动表的连接条件的列添加索引。
2.2 内连接
对于内连接而言，若有一个表的连接条件上有索引，该表会作为被驱动表。
当两个表的连接条件上都有索引的时候，优化器有决定哪个表作为驱动表，哪个表作为被驱动表的权利。一般来说，会选择数据量小的表来作为驱动表，数据量大的作为被驱动表，即 “小表连大表”。
2.3 JOIN 语句原理
JOIN 方式连接多个表，本质就是多个表之间数据的循环匹配。MYSQL5.5 之前，只支持嵌套循环，若关联表数据量很大，JOIN 关联时间会很长。MYSQL5.5 之后，MYSQL引入了 BNLJ 算法来优化嵌套循环。
驱动表和被驱动表
驱动表就是主表，被驱动表就是从表。
- 对内连接来说
SELECT * FROM A JOIN B ON ...;
A 不一定是驱动表，优化器会根据查询语句做优化，然后由优化器决定谁作为驱动表。
- 对于外连接来说
SELECT * FROM A LEFT JOIN B ON ...;
-- 或
SELECT * FROM B RIGHT JOIN A ON ...;
一般来说，通常认为 A 就是驱动表，B 就是非驱动表。
但是也不一定，优化器在做 SQL 优化时可能会根据条件将外连接优化为内连接，这时候谁做驱动表就由优化器来决定了。
- 简单嵌套循环连接（Simple Nested Loop Join）
算法很简单，就是从表A中取出一条数据，然后遍历表B，以此类推
- 索引嵌套循环连接（Index Nested Loop Join）
其优化的思路就是减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免了和内层表的每条记录去比较，极大的减少了对内层表的匹配次数。
- 块嵌套索引连接（Block Nested Loop Join）
如果 JOIN 的列没有索引，为了减少被驱动表的 IO 次数，不在是逐条的获取驱动表的数据，而是一块一块的获取，进入了 JOIN Buffer 缓冲区，将驱动表 JOIN 相关的数据列缓存到 JOIN Buffer 中，然后全表扫描被驱动表，被驱动表的每一条记录和 JOIN Buffer 中所有驱动表记录进行匹配，将简单嵌套循环的多次比较合并到一次，降低了被驱动表的访问频率。
-- 查看block_nested_loop状态（默认开启） SHOW VARIABLES LIKE '%optimizer_switch%'; -- JOIN Buffer 缓冲池大小（默认256k） SHOW VARIABLES LIKE 'join_buffer_size';
2.4 小结
-
整体效率：INLJ > BNLJ > SNLJ
-
永远是小结果集驱动大结果集（小的度量单位是指：行数 * 每行的大小）
-
为被驱动表的连接条件添加索引
-
增大 JOIN Buffer 缓冲池的大小
-
减少驱动表不必要的字段查询
2.5 Hash JOIN（MYSQL8）
MYSQL8 废弃了 BNLJ，因为 MYSQL8 加入了 Hash JOIN 默认都会使用 Hash JOIN。
- Nested Loop：对于被连接的数据子集比较小的情况，Nested Loop 是个比较好的选择。
- Hash JOIN：是做大数据集连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用 JOIN Key 在内存中建立散列表，然后扫描较大的表并探测散列表，找出与 Hash 表匹配的行。
- 这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。
- 在表很大的情况下并不能完全放入内存，这时优化器会将它分割为若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求由较大的临时段来提高 IO 性能。
- 它能很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。Hash JOIN 只能适用于等值连接，这是由 Hash 的特点来决定的。
3. 子查询优化
子查询是 MYSQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询，但是，子查询的执行效率不高。因为：
- 执行子查询时，MYSQL 需要为内层查询语句的查询结果建立一个临时表，然后外层查询从临时表中查询记录。查询完毕后，再撤销这些临时表。这样消耗过多的 CPU 和 IO 资源，产生大量的慢查询。
- 子查询结果所产生的临时表，不论是内存临时表还是磁盘临时表，都不会存在索引，所以查询性能会有一定的影响。
- 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
再 MYSQL 中，可以使用 JOIN 连接来替代子查询。连接查询不需要建立临时表，其速度比临时表要快，如果查询中使用索引的话，性能就会更好。
尽量不要使用 NOT IN 或者 NOT EXISTS，用 LEFT JOIN xxx ON xxx WHERE xxx IS NULL 替代
4. 排序优化
4.1 Index 排序
在 MYSQL 中，支持两种排序方式，分别是 FileSort 和 Index 排序。
- Index 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
- FileSort 排序则一般在内存中进行排序，占用 CPU 较多。如果待排结果较大，会产生临时文件 IO 到磁盘进行排序的情况，效率会很低。
优化建议：
① 在 SQL 中，可以在 WHERE 和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描，在 ORDER BY 子句避免使用 FileSort 排序。
② 尽量使用 Index 完成 ORDER BY 排序，如果 WHERE 和 ORDER BY 后面是相同的列就使用单列索引，如果不同就是用联合索引。
③ 无法使用 Index 时，需要对 FileSort 排序进行调优。
ORDER BY 时使用 LIMIT 可以使用上索引，不使用 LIMIT 会使索引失效（因为索引检索完要再回表排序）
多级 ORDER BY 顺序错误时，索引也会失效。
ORDER BY 的规则不一致时，，索引也会失效。
所有的排序都是在过滤之后执行的。所以如果条件过滤掉大部分数据的话，剩下的数据进行排序其实不是很消耗性能，即使索引优化了排序，但实际提升的性能有限。相对的在范围查询的过滤条件中，这个如果没有用到索引的话，要对几万条数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。
结论：
- 两个索引同时存在，MYSQL 自动选择最优方案，但是随着数据量的变化，这样的索引也会随之变化的。
- 当范围条件和 ORDER BY / GROUP BY 的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数量足够多，而需要排序的数量并不多时，优先把索引放在范围字段上，反之亦然。
4.2 FileSort 算法：双路排序和单路排序
- 双路排序（慢）：要对磁盘进行2次扫描，IO 很耗时
- 单路排序（快）：从磁盘读取需要查询的所有列，按照 ORDER BY 列在 Buffer 对他们进行排序，然后扫描排序后的列表进行输出，避免了第二次读取数据，把随机 IO 变成了顺序 IO，但是它会占用更多的空间。
引申的问题：
在 sort_buffer 中，单路比多路要占用很多的空间，因为单路是把所有字段都取出，所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取 sort_buffer 容量大小，再排序，…从而多次 IO。本想省一次 IO，反而导致了大量的 IO 操作，得不偿失。
优化策略：
- 尝试提高 sort_buffer_size：根据系统的能力去提高，MYSQL5.7 中，InnoDB 存储引擎默认值为 1MB。
SHOW VARIABLES LIKE '%sort_buffer_size%';
- 尝试提高 max_length_for_sort_data
SHOW VARIABLES LIKE '%max_length_for_sort_data%'; -- 默认1024字节如果设置的太高，数据总容量超出 sort_buffer_size 的概率就越大，明显症状就是高的磁盘 IO 和低的处理器使用率。如果需要返回的列的最大总长度大于 max_length_for_sort_data，使用双路算法，否则使用单路算法。1024~8192字节之间调整。
- ORDER BY 时使用 SELECT * 是大忌，最好只查询需要的字段。
当 Query 的字段大小总和小于 max_length_for_sort_data，而且排序字段不是 TEXT / BLOB 类型时，会使用改进后的算法 —— 单路排序，否则用老算法 —— 双路排序。
两种算法的数据都有可能超出 sort_buffer_size 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 IO，但是用单路排序算法的风险更大一些，所以要提高 sort_buffer_size。
5. 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、GROUP BY、DISTINCT 这些语句都较为耗费CPU，数据库的 CPU 是极其宝贵的。
- 包含了 ORDER BY、GROUP BY、DISTINCT 这些查询的语句，WHERE 条件过滤出来的结果请保持在1000行以内。否则 SQL 会很慢。
6. 优化分页查询
一般分页查询的时候，通过创建索引能够比较好的提高性能。一个头疼的问题就是 LIMIT 200000,10 ，此时需要 MYSQL 排序前 200010 条记录，仅仅返回 200000~200010 条记录，其他记录丢弃，查询索引的代价非常大。
SELECT * FROM student LIMIT 200000,10;
优化思路一
在索引上完成排序分页操作，最后根据主键关联回表查询所需要的其他列的内容。
SELECT * FROM student s,(SELECT id FROM student ORDER BY id LIMIT 200000,10) a
WHERE s.id = a.id;
优化思路二
-- 适用于主键自增的表，可以把LIMIT查询转换为某个位置的查询
SELECT * FROM student WHERE id > 200000 LIMIT 10;
实际开发中一般会有自增约束，由于增删改操作，id表示值的不一定就是顺序的第多少多少个，所以这里的id可以换一种理解，即上一页中最后一条记录的主键id值。
7. 优先考虑覆盖索引
7.1 覆盖索引是什么
① 索引是高效找到行的方法，但是一般数据库也能使用索引找到一个列的数据，因此他不必读取整个行，毕竟索引的叶子节点存储了他们需要的数据；当能通过读取索引就获得想要的数据，也就不需要回表读取整行的数据了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
② 非聚簇索引的一种形式，它包括在查询里的 SELECT、JOIN 和 WHERE 子句用到的所有索引（即建索引的字段正好是覆盖查询条件中所涉及的字段）
简单来说就是，索引列+主键 包含 SELECT 到 FROM 之间查询的列。
7.2 覆盖索引的利弊
好处：
- 避免了 InnoDB 表进行索引的二次查询（回表）
InnoDB 是以聚簇索引的顺序来存储的，对于 InnoDB 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询查询数据，在找到相应的键值后，还需通过主键回表进行二次查询才能获取需要的其他信息。
在覆盖索引中，二级索引的键值中可以直接获得需要查询的所有数据，避免了对主键的二次查询，减少了 IO 操作，提升了查询效率。
- 可以把随机 IO 变成顺序 IO 加快查询效率
由于覆盖索引是按照键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行数据 IO 要少得多，因此利用覆盖索引在访问时也可以把磁盘的随机 IO 转变成索引查找的顺序 IO。
由于覆盖索引可以减少树的搜索次数，显著提高查询性能，所以使用覆盖索引是一个常用的性能优化手段。
弊端：
- 索引字段的维护总是有代价的，因此在建立冗余索引来支持覆盖索引时就需要权衡利弊了。这是业务 DBA 或者业务数据架构师的工作。
8. 索引下推（ICP）
8.1 使用前后对比
是一种在存储引擎层使用索引过滤数据的优化方法。
- 如果没有 ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给 MYSQL 服务器，由 MYSQL 服务器来评估 WHERE 后面的条件是否保留行。
- 启用 ICP 后，如果部分 WHERE 条件可以仅使用索引中的列来筛选，则 MYSQL 服务器会把这部分 WHERE 条件放到存储引擎筛选。然后，存储引擎用过索引条目来筛选数据，并且只有在满足这一条件时才从表中取行。
- 好处：ICP 可以减少存储引擎必须访问基表的次数和 MYSQL 服务器必须访问存储引擎的次数。
- 但是，ICP 的加速效果取决于在存储引擎内通过 ICP 筛选掉的数据的比例。
简单来说，就是如果在二级索引检索完成后，WHERE 条件中还可以对二级索引中的相关列进行进一步过滤（通常是一些引发索引失效的条件），在完全过滤完成后再进行回表操作。而不是在二级索引检索完立即回表然后用聚簇索引查询每一条记录并依次过滤。
8.2 ICP 的开启 / 关闭
默认情况下开启索引条件下推
-- 打开索引下推
SET optimizer_switch = 'index_condition_pushdown=on';
-- 关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=off';
8.3 ICP 的使用条件
① 如果表的访问类型是 range、ref、eq_ref 和 ref_or_null 可以使用 ICP。
② ICP 可以用于 InnoDB 和 MyISAM 表，包括分区表 InnoDB 和 MyISAM 表。
③ 对于 InnoDB 表，ICP 仅用于二级索引，ICP 的目标是减少全行读取次数，从而减少 IO 操作。
④ 当 SQL 使用覆盖索引时，不支持 ICP（因为没必要用上），这种情况下使用 ICP 不会减少 IO。
⑤ 相关子查询的条件不能使用 ICP。
9. 其他查询优化策略
9.1 EXISTS 和 IN 的区分
还是要看表的大小，总之：小表驱动大表
EXISTS 的实现相当于外表循环，EXISTS 里面的表应该为大表。IN 里面的表应该是小表。
9.2 COUNT(*) 和 COUNT(具体字段) 效率
如果具体字段不包含 NULL 值，那么 COUNT(*)、COUNT(1) 和 COUNT(具体字段) 其实是一样的。但是如果具体字段包含了 NULL 值，则在统计个数的时候不会将 NULL 值统计进去。
如果是 MyISAM 存储引擎，统计数据表的行数只需要 O(1) 的复杂度。因为每张 MyISAM 数据表都有一个 meta 信息存储了 row_count 值，而一致性则由表级锁来保证。
如果是 InnoDB 存储引擎，因为 InnoDB 支持事务，采用了行级锁和 MVCC 机制，所以无法像 MyISAM 一样维护一个 row_count 值，因此需要扫描全表，进行循环+计数的方式，复杂度为 O(n)。
9.3 关于 SELECT(*)
建议明确字段，原因：
① MYSQL 在解析的过程中，会通过查询数据字典，将 “ * ” 转换为所有的列名，会耗费资源和时间。
② 无法使用覆盖索引。
9.4 LIMIT 1 对优化的影响
针对扫描全表的 SQL 语句，如果确定数据集只有一条，可以加上 LIMIT 1，这样找到一条结果的时候就不会继续扫描了，会加快查询速度。
如果已经对该字段建立了索引，那么就可以通过索引查询，就不用加上 LIMIT 1 了。
9.5 多使用 COMMIT
只要有可能，在程序中尽量多使用 COMMIT，这样性能会提高，需求也会因为 COMMIT 所释放的资源而减少。
COMMIT 释放的资源：
- 回滚段上用于恢复数据的资源
- 被程序语句获得的锁
- redo / undo log buffer 中的空间
- 管理上述3种资源的内部花费
10. 淘宝数据库主键是怎么设计的？
10.1 自增 id 的问题
- 可靠性不高：存在自增 id 回溯的问题，直到 MYSQL8 才修复。
- 安全性不高：对外暴露的接口很容易被猜到对应的信息。比如 /user/1 很容易猜测 user 的 id 就是1。
- 性能差：需要在服务端数据库来生成。
- 交互多：业务需要额外执行一次 last_insert_id() 才能知道刚才插入的自增值，在海量并发的系统中，多一条 SQL，就多一次性能上的开销。
- 局部唯一性：不是全局唯一，只有在当前数据库中唯一，对于分布式系统来说很麻烦。
10.2 业务字段做主键？
建议尽量不要使用跟业务有关的字段作为主键，作为项目设计的技术人员，谁也无法预测在项目的整个生命周期中，哪个业务字段会因为业务需求而有重复，或者重用的情况出现。
10.3 淘宝的主键设计
以订单号为例：（猜测）订单ID = 时间 + 去重字段 + 用户ID后6位
这样的设计能做到全局唯一，且对分布式系统查询及其友好
10.4 推荐的主键设计
非核心业务：对应表的主键自增 id，如告警、日志、监控信息等
核心业务：主键设计至少应该是全局唯一且单调自增的。全局唯一保证在各个系统之间都是唯一的，单调递增是希望插入时不影响数据库性能。
最简单的主键设计：UUID
UUID 特点：全局唯一、占用38字节、数据无序、插入性能差
MYSQL 数据库 UUID 组成：
UUID = 时间 + UUID版本（16字节）- 时钟序列（4字节）- MAC地址（12字节）
例子：15334611-6fdc-11ef-90c4-0242ac110002
15334611-6fdc-11ef：时间+版本（16字节）
90c4：时间序列（4字节）
0242ac110002：MAC地址（12字节）
16 + 4 + 12 + 4（4个短横线的字节）= 36字节
—— 改造 UUID ：
将时间高低位互换，则时间的就是单调递增的了。MYSQL8 可以更换时间低位和时间高位的存储方式，这样 UUID 就是有序的 UUID 了。
MYSQL8 还解决了 UUID 存在的空间占用问题，除去了 UUID 字符串中毫无意义的短横线 ‘-’ ，并将字符串用二进制类型保存，这样存储空间降低为了16字节。
可以通过 MYSQL8 提供的 uuid_to_bin 函数实现上述功能，同样的，MYSQL 也提供了 bin_to_uuid 函数进行转化。
SET @uuid = UUID();
SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);
有序UUID性能测试（自增ID、UUID、有序UUID 对比）
速度：有序 UUID > 自增 ID > UUID
空间：自增 ID < 自增 UUID < UUID
在实际业务中使用的有序 UUID 在业务端就可以生成，还可以进一步减少 SQL 交互次数。
十二. 数据库调优的其他策略
1. 调优的目标
- 尽可能节省系统资源，以便系统可以提供更大负荷的服务。（增大吞吐量）
- 合理的结构设计和参数调整，以提高用户操作响应的速度。
- 减少系统的瓶颈，提高 MYSQL 数据库整体的性能。
1.1 定位调优的问题
用户在不同时间段访问服务器遇到的瓶颈不同，比如大规模的并发访问，不同业务中数据库的事务处理和 SQL 查询都会有所不同。
- 用户反馈（主要）
- 日志分析（主要）
- 服务器资源使用监控
- 数据库内部状况监控
- 其他
1.2 调优的维度和步骤
① 选择合适的 DBMS
② 优化表的设计
③ 优化逻辑查询
④ 优化物理优化
⑤ 使用 Redis 或 Memcached 作为缓存
⑥ 库级优化（主从架构）
- 读写分离
- 数据分片（分库分表）
2. 优化 MYSQL 服务器
分为两个方面：硬件优化和服务参数优化
3. 优化数据库结构
3.1 拆分表：冷热数据分离
思路：把一个包含了很多字段的表拆分为2个或者多个相对较小的表。这样做的原因是，这些表中某些字段的操作频率很高（热数据），某些字段的操作频率很低（冷数据）。冷热数据分离，可以减小表的宽度。
3.2 添加中间表
3.3 添加合适的冗余字段（反范式化）
3.4 优化数据类型
3.5 优化插入记录的速度
3.6 优化插入记录的速度
3.7 使用非空约束
4. 大表优化
4.1 限定查询的范围
禁止不带任何限制数据范围条件的查询。
4.2 读 / 写分离
经典的数据库拆分方案，主库负责写，从库负责读。
- 一主一从模式
- 双主双从模式
4.3 垂直拆分
把一个数据库切成多分，放到不同的数据库服务器上，减少对单一数据库服务器的压力。
- 如果数据库中的数据表过多，可以采用垂直分库的方式，将关联的数据表部署在同一个数据库上
- 如果数据表中的列过多，可以采用垂直分表的方式，将一张数据表拆成多张数据表，把经常一起使用的列放到同一张表中。
4.4 水平拆分
十三. 事务基本知识
1. 数据库事务
1.1 存储引擎支持情况
SHOW ENGINES;
只有 InnoDB 存储引擎支持事务
1.2 事务的 ACID 特性
- 原子性
事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。
- 一致性
事务执行前后，数据从一个合法性状态变换到另一个合法性状态（满足预定的约束的状态就是合法性状态），满足这个状态，数据就是一致的。
- 隔离性
一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰。
- 持久性
数据库一旦提交，对数据库中的数据的改变就是永久性的。
持久性是通过事务日志来保证的，日志包括了 redo 日志和 undo 日志。
1.3 事务的状态
事务是一个抽象的概念，MYSQL 会根据这些操作所执行的不同阶段把事务分为以下几个状态：
- 活动的
事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态。
- 部分提交的
当事务的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘中，我们就说该事务是部分提交的。
- 失败的
当事务处于活动的或者部分提交的状态时，可能遇到了某些错误而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。
- 中止的（ROLLBACK）
如果事务执行了一部分而变为了失败的状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态。就是说要撤销失败事务对当前数据库造成的影响。这个撤销的过程就是回滚。当回滚操作执行完毕时，也就是数据库回到了事务执行之前的状态，该事务就处在了中止的状态。
- 提交的（COMMIT）
当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说事务处在了提交的状态。
只有当事务处于提交或者中止的状态时，一个事务的生命周期才算是结束了。
2. 如何使用事务
分两种：显式事务和隐式事务
2.1 显式事务
步骤一：开启事务
-- 显式开启一个事务
BEGIN;
-- 或 START TRANSACTION [READ ONLY]|[READ WRITE] [WITH CONSISTENT SNAPSHOT];
START TRANSACTION 相较于 BEGIN 的特别之处在于，后面可以跟随几个修饰符
① READ ONLY（只读事务）：只能读取数据，不能修改数据
② READ WRITE（读写事务）：默认
③ WITH CONSISTENT SNAPSHOT（一致性读）
步骤二：执行一系列事务中的操作（主要是 DML）
步骤三：提交事务或中止事务（回滚）
-- 提交事务
COMMIT;
-- 回滚事务(回滚到开启事务前)
ROLLBACK;
-- 回滚到某个保存点
ROLLBACK TO [SAVEPOINT];
关于 SAVEPOINT 的操作有：
-- 在事务中创建保存点，方便后续对保存点进行回滚，一个事务中可以有多个保存点 SAVEPOINT 保存点名称; -- 删除保存点 RELEASE SAVEPOINT 保存点名称;
2.2 隐式事务
AUTOCOMMIT 关键字：将每一条查询都当作是一个事务
SHOW VARIABLES LIKE 'autocommit'; -- 默认为ON
-- 关闭自动提交
SET autocommit = FALSE;
在 autocommit 为 on 的情况下，使用 START TRANSACTION 或 BEGIN 开启事务，那么 DML 操作就不会自动提交数据
2.3 隐式提交数据的情况
- 数据定义语言（DDL）
数据库对象，就是数据库、表、视图、存储过程的语句。当我们使用 CREATE、ALTER、DROP 等语句去修改数据库对象时，就会隐式地提交前边语句所属于的事务。如：
BEGIN;
SELECT ...;
....
CREATE TABLE(); -- 此语句会隐式提交前面所有语句所属于的事务
- 隐式使用或者修改 mysql 数据库中的表
比如 ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD 等
- 事务控制或者关于锁定的语言
① 当我们在一个事务还没提交或者回滚时就又使用 START TRANSACTION 或 BEGIN 语句开启了另一个事务时，会隐式提交上一个事务。
② 当前的 autocommit 为 OFF，我们手动将该值修改为 ON 时，也会隐式的提交前面语句所属的事务。
③ 使用 LOCK TABLES、UNLOCK TABLES 等关于锁定的语句也会隐式的提交前边语句所属的事务。
- 加载数据的语句
使用 LOAD DATA 语句来批量往数据库中导入数据时，也会隐式的提交前边语句的事务。
- 关于 MYSQL 复制的一些语句
使用 START SLAVE、STOP SLAVE、RESTART SLAVE、CHANGE MASTER TO 等语句时会隐式的提交前边的语句所属的事务。
- 其他一些语句
使用 ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、LOAD INDEX INFO CACHE、RESET 等语句也会隐式的提交前边的语句所属的事务。
2.4 completion_type 参数
这个参数有三个值：
- completion_type = 0，这是默认情况。当我们执行了 COMMIT 的时候会提交事务，在执行下一个事务时，还需要使用 START TRANSACTION 或者 BEGIN 来开启。
- completion_type = 1，这种情况下，当我们提交事务后，相当于执行了 COMMIT AND CHAIN，也就是开启了一个链式事务，及当我们提交事务之后，会开启一个相同隔离级别的事务。
- completion_type = 2，这种情况下 COMMIT = COMMIT AND RELEASE，也就是当我们提交后，会自动与服务器断开连接。
当我们使用 autocommit = 0时，不论是否采用 START TRANSACTION 或者 BEGIN 的方式来开启事务，都需要用 COMMIT 来进行提交，让事务生效，使用 ROLLBACK 对事务进行回滚。
当我们使用 autocommit = 1时，每条 SQL 语句都会自动进行提交。不过这时，如果采用了 START TRANSACTION 或者 BEGIN 的方式来显式开启事务，那么这个事务只有在 COMMIT 时才会生效，在 ROLLBACK 时才会回滚。
3. 事务隔离级别
事务具有隔离性，某个事务对某个数据进行访问的时候，其他事务应该进行排队，当事务提交之后，其他事务才可以继续访问这个事务，但是这样对性能的影响太大。既要保证隔离性，又要提高性能，就需要对两者进行权衡了。
3.1 数据并发问题
- 脏写（Dirty write）
对于两个事务 Session A 和 Session B ，如果 Session A 修改了另一个未提交的事务 Session B 修改过的数据，就意味着发生了脏写。
- 脏读（Dirty read）
对于两个事务 Session A 和 Session B ，Session A 读取了已经被 Session B 更新但是没有被提交的字段。之后如果 Session B 回滚，Session A 读取的数据就是无效的。
- 不可重读读（Non-Repeatable read）
对于两个事务 Session A 和 Session B ，Session A 读取了一个字段，然后 Session B 更新了该字段，之后 Session A 再次读取同一个字段，值就截然不同了。这就意味着发生了不可重复读。
- 幻读（Phantom）
对于两个事务 Session A 和 Session B ，Session A 从表中读取了一个字段，然后 Session B 在该表插入了一些新的行。之后如果 Session A 再次读取同一个表，就会多出几行。这就是幻读。
3.2 SQL 中的四种隔离级别
上述的数据并发问题按严重程度排序：脏写 > 脏读 > 不可重复读 > 幻读
SQL 标准中设立了4个隔离级别：
- READ UNCOMMITTED：读未提交，在该隔离级别，所有事务可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。
- READ COMMITTED：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交的事务所作的改变，这是大多数数据库系统默认的隔离级别（但不是 MYSQL 默认的）。可以避免脏读，但是不能避免不可重复读、幻读。
- REPEATABLE READ：可重复度，事务A在读到一条数据后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容，可以避免脏读、不可重复读，但是不能避免幻读。
- SERIALIZABLE：串行化，确保一个事务可以从一个表中读取相同的行。在这个事务处理的期间，禁止其他事务对这个行进行增删改操作，所有的并发问题都可以解决，但是性能十分低下。
3.3 MYSQL 支持的四种隔离级别（默认 REPEATABLE READ）
查看当前隔离级别：
SHOW VARIABLES LIKE 'transaction_isolation';
3.4 设置不同的隔离级别
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL = 隔离级别;
-- 隔离级别：READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE
或者：
SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别';
-- 隔离级别：READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE
4. 事务的常见分类
- 扁平事务
扁平事务是最简单的一种，通过 BEGIN 来开启，通过 COMMIT 或 ROLLBACK 来结束。
-
带有保存点的扁平事务
-
链事务
链事务是指一个事务由多个子事务链式组成，，它可以被视为保存点模式的一个变种。带有保存点的扁平事务，当系统发生崩溃的时候，所有的保存点都会消失，这意味着在进行恢复时，事务要从开始处重新执行，而不能从最近的一个保存点继续执行。链事务的思想就是：再提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式的传给下一个要开始的事务，前一个子事务的提交操作和下一个子事务的开始操作合并成一个原子操作，这意味着下一个事务将看到上一个事务的结果。这样，在提交子事务的时候就可以释放不需要的数据对象，而不必等到整个事务完成后才释放。
-
嵌套事务
-
分布式事务
十四. MYSQL 事务日志
-
事务的隔离由锁机制实现
-
而事务的原子性、一致性、持久性由事务的 redo 日志和 undo 日志来保证
- redo log 称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。
- undo log 称为回滚日志，回滚行记录到某个特定的版本，用来保证事务的原子性、一致性。
redo 和 undo 都是一种恢复操作，但是：
- redo log：是存储引擎层（InnoDB） 生成的日志，记录的是物理级别上的页修改操作，主要为了保证数据的可靠性。
- undo log：是存储引擎层（InnoDB）生成的日志，记录的是逻辑操作日志。主要用于事务回滚（undo log 记录的是每个修改操作的逆操作）和一致性非锁定读（undo log 回滚行记录到某一种特定的版本——MVCC，即多版本并发控制）。
1. redo 日志
InnoDB 存储引擎是以页为单位来管理储存空间的，在真正访问页面之前，需要把磁盘上的页缓存到内存中的 Buffer Pool 之后才可以访问，所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘（checkpoint 机制），通过缓冲池来优化 cpu 和磁盘之间的鸿沟，这样可以保证整体的性能不会下降得太快。
1.1 为什么需要 redo 日志
由于 checkpoint 并不是每次变更的时候就触发的，而是 master 线程隔一段时间去处理的。最坏的情况就是事务提交后，刚写完缓冲池，数据库宕机了，那么这段数据就是丢失的，无法恢复。
另一方面，事务包含持久性特征，就是说对于一个已经提交的事务，在事务提交后即使系统发生了崩溃，这个事务对数据库中所作的更改也不能丢失。
如何保证这个持久性呢：
方案一：在事务提交完成之前把该事务所修改的所有页面都刷新磁盘，但是有些问题：
-
修改量与刷新磁盘工作量严重不成比例
-
随机 IO 刷新慢
方案二：在每次事务提交时把修改了哪些东西记录一下
InnoDB 存储引擎采用了 WAL（write ahead logging），这种技术的思想就是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这里的日志就是 redo 日志。当发生宕机且数据未刷到磁盘的时候，可以通过 redo log 来恢复，保证持久性，这就是 redo log 的作用。
1.2 redo 日志的好处、特点
好处：
- redo 日志降低了刷盘频率
- redo 日志占用的空间非常小（存储表空间id、页号、偏移量以及需要更新的值，所需的空间很小，刷盘快）
特点：
- redo 日志是顺序 IO
在执行事务的过程中，每执行一条语句，就可能产生若干条 redo 日志，这些日志是按照产生的顺序写入磁盘的，也就是顺序 IO。
- 事务执行过程中，redo log 不断记录
redo log 和 bin log 的区别就是，redo log 是存储引擎产生的，而 bin log 是数据库层产生的。假设一个事务，对表做10万行的插入，在这个过程中，一直不断地往 redo log 顺序记录，而 bin log 不会，知道这个事务提交，才会一次写入到 bin log 文件中。
1.3 redo 的组成
简单分为两个部分：
- 重做日志的缓存（redo log buffer）：保存在内存中，是易失的。
在服务器启动时就向操作系统申请了一大片称为 redo log buffer 的连续内存空间。就是 redo 日志缓冲区。这片内存空间被划分为多个 redo log block，一个 redo log block 占了512字节。
参数：innodb_log_buffer_size（默认16M）
- 重做日志文件（redo log file）：保存在硬盘中，是持久的。
保存在数据目录下：ib_logfile0 和 ib_logfile1
1.4 redo 的整体流程
redo log 流转过程：
① 先将原始数据从磁盘中读入内存来，修改数据的内存拷贝
② 生成一条重做日志并写入 redo log buffer，记录的是数据被修改后的值
③ 当事务 commit 时，将 redo log buffer 中的内容刷新到 redo log file，对 redo log file 采用追加写的方式
④ 定期将内存中修改的数据刷到磁盘中
1.5 redo log 的刷盘策略
redo log 的写入并不是直接写到磁盘的，InnoDB 存储引擎会先将 redo log 写入 redo log buffer，然后以一定的频率刷入到真正的 redo log file 中，这里的频率就是它的刷盘策略。
redo log buffer 刷盘到 redo log file 的过程并不是真正的刷到磁盘中去，只是刷入到文件系统缓存（Page Cache）中去（这是现代操作系统为了提高文件写入效率的一种优化），真正的写入会交给系统自己去决定。对于 InnoDB 来说就有一个问题，如果交给系统来同步，同样如果系统宕机，那么数据也丢失了（虽然概率很小）。
针对这种情况，InnoDB 给出了 innodb_flush_log_at_trx_commit 参数，该参数控制 commit 提交事务时，如何将 redo log buffer 中的日志刷新到 redo log file 中。它支持三种策略：
- 设置为0：表示每次事务提交时不进行刷盘操作。（系统默认 master thread 每隔1s进行一次重做日志的同步）
- 设置为1：表示每次事务提交时都进行同步，刷盘操作（默认值）
- 设置为2：表示每次事务提交时都只把 redo log buffer 内容写入到 Page Cache，不进行同步。由os自己决定什么时候同步到磁盘文件
另外，InnoDB 存储引擎有一个后台线程，每隔1秒就会把 redo log buffer 中的内容写入到文件系统缓存（Page Cache），然后调用刷盘操作。
也就是说，一些还没有提交的事务的 redo log 记录，也可能会刷盘。
小结：innodb_flush_log_at_trx_commit = 1 时，只要事务提交成功，redo log 的记录一定在磁盘里的，不会有任何数据丢失。但是效率是最差的。
innodb_flush_log_at_trx_commit = 2 时，只要操作系统不挂，就没问题，效率是最高的。
不过还是建议使用默认值，既然开启了事务，那么数据的安全性还是更加重要些。
2. undo 日志
undo log 是事务原子性的保证。在事务中更新数据和前置操作其实还是要先写入一个 undo log。
2.1 如何理解 undo 日志
事务要保证原子性，也就是要么全部完成，要么全部不做。
当我们需要把数据改回原来的样子，这个过程就是回滚，让这个事务看起来根本没有执行过，这就符合了原子性。
当对一条记录进行增删改操作时，会把需要回滚的东西记录下来：
- 新增数据时，至少把该记录的主键值记录下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。（对于每个 INSERT，InnoDB 存储引擎会完成一个 DELETE）
- 删除数据时，至少要把这条记录的内容记录下来，之后回滚的时候只需要将这些内容组成的记录插入到表中就好了。（对于每个 DELETE，InnoDB 存储引擎都会完成一个 INSERT）
- 修改数据时，至少把修改这条记录前的旧值记录下来，这样回滚之后再把这条记录更新为旧值就好了。（对于每个 UPDATE，InnoDB 存储引擎会执行一个相反的 UPDATE，将修改前的行放回去）
查询语句不会产生 undo log
此外，undo log 也会产生 redo log，undo log 的产生会伴随着 redo log 的产生，因为 undo log 也需要持久性的保护。
2.2 undo log 的作用
① 回滚数据：undo 的逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有的修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能会不同。
② MVCC：在 InnoDB 存储引擎中 MVCC 的实现是通过 undo 来完成的，当用户读取一行记录时，若该事务的记录已经被其他事务占用，当前事务可以通过 undo 读取之前的行版本信息，以此实现非锁定读取。
2.3 undo log 的存储结构
InnoDB 对 undo log 的管理采用段的方式，也就是回滚段。每个回滚段记录了1024个 undo log segment，而在每个 undo log segment 中申请 undo 页。
参数：innodb_undo_logs
回滚段中的数据分类：
- 未提交的回滚数据：该数据所关联的事务并未提交，用于实现读一致性，所以该数据不能被其他事务的数据覆盖。
- 已经提交但未过期的回滚数据：该数据关联的事务已经提交，但是仍受到 undo retention 参数的保持时间的影响。
- 事务已经提交并过期的数据：事务已经提交，而且数据保存时间已经超过 undo retention 参数的指定时间，属于已经过期的数据。当回滚段满了之后，会优先覆盖“事务已经提交并过期的数据”。
2.4 undo log 的类型
分两种：
- insert undo log：指的是在 INSERT 操作中产生的 undo log。因为 INSERT 操作的记录只对事务本身可见，对其他事务不可见，故该 undo log 可以在事务提交后直接删除，不需要进行 purge 操作。
- update undo log：指的是对 UPDATE 和 DELETE 操作产生的 undo log，该 undo log 可能需要 MVCC 机制，因此不能在事务提交时就进行删除。提交时会放入 undo log 链表，等待 perge 线程进行最后的删除。
十五. 锁
事务的隔离性由锁来实现
1. 概述
在数据库中，数据也是一种供许多用户共享的资源，为了保证数据一致性，需要对并发操作进行控制，因此产生了锁。同时锁机制也为实现 MYSQL 各个隔离级别提供了保证，锁冲突也是影响数据库并发访问性能的重要因素。
2. MYSQL 数据库并发访问相同记录
2.1 读-读
即并发事务相继读取相同的记录。读取操作对记录不会有什么影响，所以允许这种情况发生。
2.2 写-写
即并发事务相继对相同的记录进行修改。
这种情况下会发生脏写的问题，任何一种隔离级别都不允许这种问题的发生，所以在多个未提交事务对同一条记录进行改动的时候，需要让他们排队执行，这个排队就是通过锁来实现的，这个锁其实就是内存中的一个结构，在事务执行前本来是没有锁的，也就是说一开始是没有锁结构和记录进行关联的。
当一个事务想对这条记录做改动的时候，首先会看看内存中有没有与这条记录关联的锁结构，当没有的时候会在内存中生成一个锁结构与之关联。在锁结构中有很多信息，其中有两个重要属性：
- trx 信息：代表这个锁结构是哪个事务产生的
- is_waiting：代表当前事务是否在等待
不加锁：不需要再内存中生成对应的锁结构，可以直接进行操作
获取锁成功，或者加锁成功：内存中生成了相应的锁结构，而且锁结构的 is_waiting 属性为 false，也就是该事务可以继续执行操作
获取锁失败，或者加锁失败，或者没有获取到锁：在内存中生成了相应的锁结构，不过锁结构的 is_waiting 属性为 true，也就是事务需要等待，不可以继续执行操作
2.3 读-写
即一个事务进行读操作，另一个事务进行写操作。这种情况下可能会发生脏读、不可重复读、幻读的问题。
MVCC 的功劳下，MYSQL 在 REPEATABLE READ 隔离级别上也已经解决了幻读。
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，这样也避免了不可重复读和幻读的问题
- 方案二：读和写都采用加锁的方式
如果一些业务场景不允许读取记录的旧版本，而是每次都必须去读取记录的最新版本。
可以解决脏读和不可重复读的问题，解决幻读的时候会有些麻烦（因为插入的数据不会加锁）
- 对比：
- 采用 MVCC 的话，读-写操作并不冲突，性能更高
- 采用加锁的话，读-写操作需要彼此排队执行，影响性能
一般情况下都使用 MVCC 来解决读-写操作并发执行的问题，但是业务在某些特殊场景下，要求必须采用加锁的方式执行
3. 从数据操作类型上：读锁、写锁（共享锁和排他锁）
共享锁（S Lock）和排他锁（X Lock）
- 共享锁：针对同一份数据，多个事务的读操作可以同时进行且不会相互影响，相互不阻塞的
- 排他锁：当前写操作没有完成前，它会阻断其他写锁和读锁，这样就能确保在给定的时间里，只有一个事务可以执行写入，并防止其他用户读取正在写入的同一资源
对于 InnoDB 存储引擎来说，读锁和写锁可以加在表上，也可以加在行上。
| X 锁 | S 锁 | |
|---|---|---|
| X 锁 | 不兼容 | 不兼容 |
| S 锁 | 不兼容 | 兼容 |
① 锁定读
-- 对读取的记录加S锁
SELECT ... LOCK IN SHARE MODE;
-- 或者（MYSQL8新语法）
SELECT ... FOR SHARE;
-- 对读取的记录加X锁
SELECT ... FOR UPDATE;
不兼容的锁会发生阻塞，直到当前的事务提交完将阻塞的锁释放掉。
MYSQL8 新特性：
在5.7以及之前的版本，SELECT … FOR UPDATE，如果一直获取不到锁，会一直等待，直到 innodb_lock_wait_timeout 超时。在8.0版本中，SELECT … FOR UPDATE、SELECT … FOR SHARE 添加 NOWAIT、SKIP LOCKED 语法，跳过锁等待或者跳过锁定。
- 通过 NOWAIT、SKIP LOCKED 语法，能够立即返回。如果查询的行已经加锁。
- 那么 NOWAIT 会立即报错并返回
- NOWAIT、SKIP LOCKED 也会立即返回，只是返回的结果中不包含被锁定的行
② 写操作
增删改都是加 X 锁
新增操作不是真正意义上的加 X 锁，而是加了隐式锁
4. 从数据操作的粒度划分：表级锁、页级锁、行锁
① 表锁（Table Lock）
会锁定整张表，是 MYSQL 中最基本的锁策略，并不依赖于存储引擎，并且表锁是开销最小的（因为粒度比较大）。由于表锁会一次将整个表锁定，所以可以很好的避免死锁的问题。但是会导致并发率大打折扣。
—— 表级别的 S 锁和 X 锁
InnoDB的表锁不常用，以 MyISAM 存储引擎举例：
| 锁类型 | 自己可读 | 自己可写 | 自己可操作其他表 | 他人可读 | 他人可写 |
|---|---|---|---|---|---|
| 读锁 | 是 | 否 | 否 | 是 | 否，等待 |
| 写锁 | 是 | 是 | 否 | 否，等待 | 否，等待 |
—— 意向锁
InnoDB 支持多颗粒锁，它允许表级锁和行级锁共存，而意向锁就是一种表锁。
- 意向锁的存在就是为了协调锁和表锁的关系，支持多颗粒的锁并存
- 意向锁是一种不与行级锁冲突的表级锁
- 表明了某个事务正在某些行持有了锁或者该事务准备去持有锁
意向锁分为两种：
- 意向共享锁（IS）：事务有意向对表中的某些行加共享锁（S 锁）
- 意向排他锁（IX）：事务有意向对表中的某些行加排他锁（X 锁）
InnoDB 支持多粒度锁，特定场景下，意向锁和行级锁可以共存
意向锁之间互不排斥，但除了 S 和 IS 兼容外，意向锁会和共享锁和排他锁互斥
IX 和 IS 是表级锁，不会和行级的 X 锁和 S 锁发生冲突，只会和表级的 X 锁和 S 锁冲突
意向锁在保证并发的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。
—— 自增锁（AUTO_INC 锁）
在使用 MYSQL 过程中，我们可以为表的某个列添加 AUTO_INCREMENT 属性。
—— 元数据锁（MDL 锁）
MDL 锁的作用是保证了读写的正确性，当对一个表进行增删改操作的时候，加 MDL 读锁；当对表结构做变更操作的时候，加 MDL 写锁。
解决了 DML 和 DDL 操作之间的一致性。不需要显式使用，在访问一个表的时候会自动添上。
② InnoDB 中的行锁
行级锁只在存储引擎层实现。
优点：锁定粒度小，发生锁冲突概率低，可以实现的并发度高。
缺点：对于锁的开销比较大，加锁会比较慢，容易出现死锁。
—— 记录锁
记录锁是有 S 锁和 X 锁之分的。
—— 间隙锁（Gap Locks）
MYSQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种：可以使用 MVCC 方案解决，也可以采用加锁的方式，但是在使用加锁的方式上也存在问题，就是事务在第一次进行读取操作的时候，那些幻影记录并不存在，我们无法给这些幻影记录加上记录锁。InnoDB 提出了一种称为 Gap Locks 的锁。gap 锁的提出仅仅是为了防止插入幻影记录而提出的。虽然有共享 gap 锁和独占 gap 锁的说法，他们起到的作用都是一样的，而且如果对一条记录加了 gap 锁，并不会限制对其他事务这条记录加记录锁或者继续加 gap 锁。
可能会产生死锁
—— 临键锁（默认）
既锁定某条记录，又要阻止其他事务在该记录的前面的间隙插入新记录
本质上是记录锁和 gap 锁的合体。
—— 插入意向锁
一个事务在插入一条记录的时候需要判断一下插入的位置是不是被别的事务加了 gap 锁 / 临键锁，如果有的话，插入操作需要等待，知道拥有 gap 锁的那个事务提交，但是 InnoDB 规定事务在等待的时候也需要在内存中生成一个锁结构，表明事务想在某个间隙中插入新纪录，但是现在在等待的状态。这个锁就是插入意向锁，插入意向锁是一种 gap 锁，不是意向锁，在 INSERT 操作中产生。
③ 页锁
介于表锁和行锁之间
5. 从对待锁的态度划分：乐观锁和悲观锁
这两种锁是两种看待数据并发的思维方式，并不是实实在在的锁，而是锁的思想。
5.1 悲观锁
悲观锁总是假设最坏的情况，每次拿数据都认为别人会修改，所以每次拿数据的时候都会上锁（共享资源每次只给一个线程使用，其他线程阻塞，用完后再把资源转让给其他线程）。比如行锁、表锁、读锁、写锁等，都是操作之前先上锁，其他线程想要访问数据的时候，都需要被阻塞挂起。Java中 synchronized 和 ReentrantLock 等独占锁就是悲观锁的思想。
注意：SELECT … FOR UPDATE 语句执行的过程中所有扫描的行都会被锁上，因此在 MYSQL 中使用悲观锁必须确定使用了索引，而不是全表扫描，否则会把整个表都锁上。
5.2 乐观锁
乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库本身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者CAS机制来实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量。在Java中 java.util.concurrent.atomic 包下的原子变量类就是使用了乐观锁的一种实现方式：CAS实现的。
5.3 两种锁的使用场景
乐观锁：合适读操作多的场景，相对来说写的操作比较少。优点在于程序实现，不存在死锁的问题。不过适用场景也会相对乐观，因为它阻止不了程序以外的数据库操作。
悲观锁：适合写操作多的场景，因为写的操作具有排他性，采用悲观锁的方式，可以在数据库层面阻止其他的事务对该数据的操作权限，防止读-写、写-写冲突。
6. 按加锁的方式划分：显式锁和隐式锁
6.1 隐式锁
一个事务在执行 INSERT 操作时，如果即将插入的间隙已经被其他事务加了 gap 锁，那么本次 INSERT 操作会被阻塞，并且当前事务会在该间隙上加一个插入意向锁，否则一般条件下 INSERT 操作是不加锁的。那如果一个事务首先插入了一条记录，然后另一个事务：
- 立即使用 SELECT … LOCK IN SHARE MODE 语句读取这条记录，也就是获得这条记录的 S 锁，或者使用 SELECT … FOR UPDATE 语句读取这条记录，也就是要获取这条记录的 X 锁，怎么办呢？如果允许这种情况的发生，那么可能产生脏读问题。
- 立即修改这条记录，也就是要获取这条记录的 X 锁，怎么办？如果允许这种情况法发生，那么可能产生脏写的问题。
这时候事务 id 就起作用了
一个事务对新插入的记录可以不显式地加锁（生成一个锁结构），但是由于事务 id 的存在，相当于加了一个隐式锁，别的事务在对这条记录加 S 锁或者 X 锁时，由于隐式锁的存在，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构后进入等待状态。隐式锁是一种延迟加锁的机制，从而来减少锁的数量。、
隐式锁在实际内存对象中并不含有这个锁信息。只有当产生锁等待时，隐式锁转化为显式锁。
6.2 显式锁
通过特定的语句加锁，就是显式锁。
7. 其他锁
7.1 全局锁
就是对整个数据库实例加锁，当需要让整个数据库处于只读状态时，可以用这个命令。全局锁的典型适用场景：做全库逻辑备份。
FLUSH TABLES WITH READ LOCK;
7.2 死锁
两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，导致恶性循环。
产生死锁的必要条件：
- 至少两个事务
- 每个事务都已经持有锁并且申请新的锁
- 锁资源同时只能被同一个事务持有或不兼容
- 事务之间因为持有锁和申请锁导致彼此循环等待
死锁的关键在于：两个（或以上）的 Session 加锁的顺序不一样
如何处理死锁？
方式一：等待，直到超时（innodb_lock_wait_timeout=50s）
对于在线服务来说，这个等待时间是无法接受的。
方式二：使用死锁检测进行死锁处理
InnoDB 提供了 wait_for_graph 算法来主动进行死锁的检测，每当加锁请求无法立即满足需要并进入等待时，wait_for_graph 算法都会被触发，这是一种较为主动的死锁检测机制，要求数据库保存锁的信息链表和事务等待链表两部分。
原理是构建一个以事务为顶点，锁为边的有向图，判断有向图是否有环，存在即死锁。
一旦检测到有死锁，InnoDB 会选择回滚 undo 量最小的事务，让其他事务继续执行（innodb_deadlock_detect=on 表示开启）。
缺点：每个新的被阻塞的线程，都要判断是不是由于自己的加入导致了死锁，这个操作的复杂度是 O(n) ，并发量很大时会很耗时。
如何解决？
控制并发访问的数量，比如在中间件实现对于相同的行的更新，在进入引擎之前排队，这样在 InnoDB 内部就不会有大量的死锁检测工作。
如何避免死锁？
- 合理设计索引，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。
- 调整业务逻辑 SQL 的执行顺序，避免 UPDATE / DELETE 长时间持有锁的 SQL 在事务前面。
- 避免大事务，尽量将大事务拆成多个小事务来处理，小事务缩短锁定资源的时间，发生锁冲突的几率也会减少。
- 在并发很高的事务中，不要显式加锁，特别是在事务里显式加锁。
- 降低隔离级别。如果业务允许，将隔离级别调到最低也是较好的选择，比如从 RR 调整到 RC，可以避免掉很多因为 gap 锁造成的死锁。
8. 锁的内存结构
9. 锁监控
通过检查 innodb_row_lock 等状态变量来分析系统上的行锁的争夺情况。
SHOW STATUS LIKE '%innodb_row_lock%';
十六. 多版本并发控制 *
1. 什么是 MVCC
MVCC 是通过数据行的多个版本管理来实现数据库的并发控制。这项技术使得 InnoDB 的事务隔离级别下执行一致性读操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到他们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。
MYSQL 通过使用 MVCC，即使是 REPEATABLE READ 隔离级别，也可以解决幻读的问题
2. 快照读和当前读
MVCC 在 MYSQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读，而这个读指的就是快照读，而非当前读。当前读实际上就是一种加锁的操作，是悲观锁的实现。而 MVCC 本质上使用的是乐观锁思想的一种方式。
2.1 快照读
快照读又叫做一致性读，读取的是快照数据。不加锁的简单 SELECT 都属于快照读，即不加锁的非阻塞读，比如：
SELECT * FROM .. WHERE ..;
之所以出现快照读的情况，是基于提高并发性能的考虑，快照读实现的是基于 MVCC，它在很多情况下，避免了加锁操作，降低了开销。
既然是基于多版本，那么快照读可能读到的并不是数据的最新版本，可能是之前的历史版本。
快照读的前提是隔离级别不是串行级别，串行级别的快照读会退化为当前读。
2.2 当前读
读取到的记录是最新版本（不是历史版本的数据了），读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁，加锁的 SELECT，或者对数据进行增删改都会进行当前读。
3. MVCC 实现原理之 ReadView
MVCC 的实现依赖于：隐藏字段、undo log、ReadView
3.1 什么是 ReadView
在 MVCC 机制中，多个事务对同一行记录进行更新会产生多个历史快照，这些历史快照保存在了 undo log 里面。如果一个事务想要查询这个行记录，需要读取哪个版本的行记录呢？这时候就需要用到 ReadView 了。
ReadView 就是事务在使用 MVCC 机制进行快照读操作时产生的读视图，当事务启动时，会生成数据库系统当前的一个快照，InnoDB 为每一个事务构成了一个数组，用来记录并维护系统当前活跃事务的 ID（活跃就是指启动了但是还没提交）。
3.2 设计思路
使用 READ UNCOMMITTED 隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。
使用 SERIALIZABLE 隔离级别的事务，InnoDB 规定使用加锁的方式来访问记录。
使用 READ COMMITTED 或者 REPEATABLE READ 隔离级别的事务，都必须保证读到已经提交了的事务修改过的记录。假如一个事务已经修改了记录但是尚未提交，是不能读取最新版本的记录的，核心问题就是需要判断一下版本链中哪个版本是当前事务可见的，这是 ReadView 要解决的主要问题。
ReadView 包含了4个重要部分：
- creator_trx_id：创建这个 ReadView 的事务 ID
说明：只有对表中的记录做修改的时候（INSERT、DELETE、UPDATE 这些语句）才会为事务分配事务 ID，否则在一个只读事务中的事务 ID 的值都默认为 0。
- trx_ids：表示在生成 ReadView 时当前系统中活跃的读写事务的事务 ID 列表
- up_limit_id：活跃的事务中最小的事务 ID
- low_limit_id：生成 ReadView 时系统中应该分配给下一个事务的 ID 值，low_limit_id 是系统最大的事务 ID 值，注意是系统中的事务 ID，区别于正在活跃的事务 ID
3.3 ReadView 的规则
- 如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
- 如果被访问版本的 trx_id 属性值小于 ReadView 中的 up_limit_id 值，表明该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
- 如果被访问版本的 trx_id 属性值大于或等于 ReadView 中的 low_limit_id 值，表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
- 如果被访问版本的 trx_id 属性值在 ReadView 的 up_limit_id 和 low_limit_id 之间，那就需要判断一下 trx_id 属性值是不是在 trx_ids 列表中。
- 如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问
- 如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问
3.4 MVCC 整体操作流程
① 首先获取事务自己的版本号，也就是事务 ID
② 获取 ReadView
③ 查询得到的数据，然后与 ReadView 中的事务版本号进行比较
④ 如果不符合 ReadView 规则，就需要从 undo log 中获取历史快照
⑤ 返回最后符合规则的数据
当隔离级别为 READ COMMITTED 时，一个事务中的每一次 SELECT 查询都会重新获取一次 ReadView。
当隔离级别为 REPEATABLE READ 时，就避免了不可重复读，这是因为一个事务只在第一次 SELECT 的时候会获取一次 ReadView。而后所有的 SELECT 都会复用这个 ReadView。
4. 小结
通过 MVCC 我们可以解决：
- 读写之间阻塞的问题：通过 MVCC 可以让读写互不阻塞，即读不阻塞写，写不阻塞读，可以提高事务并发处理能力。
- 降低了死锁的频率：MVCC 采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行。
- 解决快照读的问题：当我们查询数据库某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的结果。
十七. 其他数据库日志
1. MYSQL 支持的日志
1.1 日志类型
MYSQL 有不同类型的日志文件：二进制日志、错误日志、通用查询日志和慢查询日志。MYSQL8 又进新增两种：中继日志和数据定义语言日志。
-
慢查询日志：记录所有执行时间超过 long_query_time 的所有查询，让便我们对查询进行优化。
-
通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至对数据库操作的审计都有很大帮助
-
错误日志：记录 MYSQL 服务的启动、运行或停止 MYSQL 服务器出现的问题，方便我们了解服务器状态，从而对服务器进行维护
-
二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复
-
中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件，从服务器通过通过读取中继日志的内容，来同步主服务器上的操作
-
数据定义语言日志：记录数据定义语句执行的元数据操作
除了二进制日志，其他日志都是文本文件。默认情况下，所有日志创建于 MYSQL 数据目录中。
1.2 日志的弊端
-
日志功能会降低 MYSQL 数据库的性能，如果开启了通用查询日志和慢查询日志，MYSQL 会花费很多时间来记录日志。
-
日志会占用很多的磁盘空间。对用户量非常大、操作非常频繁的数据库，日志文件需要的存储空间甚至比数据库文件需要的内存都大。
2. 通用查询日志
用来记录用户的所有操作，包括启动和关闭 MYSQL 服务，所有用户的连接开始和截止时间、并发给 MYSQL 数据库的所有 SQL 指令等。当我们的数据库发生异常的时候，查看通用查询日志，还原操作时的具体场景，可以帮我们准确定位问题。
SHOW VARIABLES LIKE '%general_log%';
[root@localhost ~]# mysqladmin -u 用户名 -p flush-logs; # 删除 / 刷新日志
3. 错误日志
错误日志记录了 MYSQL 启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等等。如果 MYSQL 服务出现异常，错误日志是发现问题、解决故障的首选。
3.1 启动日志
MYSQL 数据库中，错误日志是默认开启的。 而且错误日志无法被禁止。
默认情况下，错误日志存储在 MYSQL 服务器的数据目录下，名称默认为 mysqld.log（LinuxOS） 或者 hostname.err （MacOS）文件。
[mysqld]
# 定制文件名
log-error=[path/[filename]]
# 重启生效
3.2 查看日志
SHOW VARIABLES LIKE '%log_err%'；
通过文本编辑器查看
4. 二进制日志（bin log）
binlog 是 MYSQL 中比较重要的日志了，它记录了数据库执行的所有 DDL 和 DML 等数据库更新事件的语句，但是不包括没有修改任何数据的语句。
它以事件的形式记录并保存在二进制文件中
4.1 binlog 主要应用场景：
- 用于数据恢复，如果 MYSQL 服务器意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
- 用于数据复制，由于日志的延续性和时效性，master 把他的二进制日志文件传递给 slave 来达到 master-slave 数据一致性的目的。
MYSQL 数据库的数据备份、主备、主主、主从都离不开binlog，需要依赖 binlog 来同步数据，保证数据一致性。
SHOW VARIABLES LIKE '%log_bin%';
数据库文件最好不要与日志文件放在同一个磁盘上，这样当数据库文件所在的磁盘发生故障的时候，可以使用日志文件恢复数据。
4.2 使用日志恢复数据
SHOW BINARY LOGS;
SHOW BINARY EVENTS IN 'log_name';
[root@localhost ~]# mysqlbinlog [option] filename|mysql -u 用户名 -p;
4.3 删除二进制日志
PURGE {MASTER | BINARY} LOGS TO '日志文件名';
PURGE {MASTER | BINARY} LOGS BEFORE '日期';
5. 深入理解二进制日志
5.1 写入机制
binlog 的写入机制很简单，事务的执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到binlog 文件中。因为一个事务的 binlog 不能拆开，无论这个事务多大，也要保证一次性写入，所以系统会给每一个线程分配一个块内存作为 binlog cache。
可以通过设置 binlog_cache_size 参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。
5.2 binlog 对比 redolog
- redo log 是物理日志，记录内容是“在某个数据页上做了什么修改”，是 InnoDB 存储引擎层产生的
- 而 binlog 是逻辑日志，记录内容是语句的原始逻辑，属于 MYSQL Server 层
- 虽然他们都是属于持久化的保证，但是重点不同
- redo log 让 InnoDB 存储引擎有了崩溃恢复能力
- binlog 保证了 MYSQL 集群架构的数据一致性
5.3 两阶段提交
在执行更新语句过程中，会记录 redo log 和 binlog 两块日志，以基本的事务为单位，redo log 在事务执行过程中可以不断写入，而 binlog 只有在提交事务时才写入，所以 redo log 和 binlog 的写入时机不一样。
redo log 与 binlog 两份日志之间的逻辑不一致会发生什么问题？
导致主从 / 主备数据库数据不一致
为了解决两份日志之间的逻辑一致问题，InnoDB 存储引擎使用两阶段提交方案。原理很简单，将 redo log 的写入拆成了两个步骤 prepare 和 commit，就是两阶段提交。使用两阶段提交后，写入 binlog 时发生异常也不会有影响。因为 MYSQL 根据 redo log 日志恢复数据时，发现 redo log 还处于 prepare 阶段，并且没有对应 binlog 日志，就会回滚该事务。
6. 中继日志
中继日志只在主从服务器的从服务器上存在。
十八. 主从复制
1. 概述
在实际工作中，我们常常使用 Redis 作为缓存与 MYSQL 配合使用，当有请求的时候，首先从缓存中进行查找，如果存在就直接取出，不用再访问数据库，这样就提升了读取的效率，也减少了对后端数据库的访问压力。Redis 的缓存架构是高并发架构中非常重要的一环。
一般应用对数据库而言就是“读多写少”，当数据库读取数据的压力比较大时，有一个思路就是采用数据库集群的方案，做主从架构进行读写分离，可以提高数据库并发处理能力。
如果我们的目的是提高数据库高并发访问的效率，那么首先考虑的应该是优化 SQL 和索引，其次才是缓存的策略，比如使用 Redis 将热点数据保存在内存数据库中，提升读取的效率，最后才是对数据库采用主从架构，进行读写分离。
1.2 主从复制的作用
① 读写分离：通过主从复制的方式来同步数据库，然后通过读写分离提高数据库并发处理能力。
其中一个是 Master 主库，负责写入数据，被称为写库。
另一个是 Slave 从库，负责读取数据，被称为读库。
当主库进行更新的时候，会自动将数据复制到从库中，而我们在客户端读取数据的时候，会从从库中进行读取。
面对“读多写少”的需求，采用读写分离的方式，可以实现更高的并发访问。同时还能对从服务器进行负载均衡，让不同的读请求按照策略均匀的分发到不同的从服务器上，让读取更加顺畅。读取顺畅的另一个原因就是减少了锁表的影响。
② 数据备份
通过主从复制将主库上的数据复制到从库上，相当于是一种热备份机制，也就是服务器在主库正常运行的情况下进行的备份，不会影响到服务。
③ 高可用性
数据备份实际上是一种冗余的机制，通过这种冗余的机制可以换取数据库的高可用性，也就是当服务器出现故障或者宕机的情况下，可以切换到从服务器上，确保数据的正常运行。
2. 主从复制的原理
Slave 会从 Master 读取 binlog 来进行数据同步。
2.1 原理剖析
三个线程
- 二进制日志传储线程（Binlog dump thread）：是一个主库线程。当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件的时候，会在 Binlog 上面加锁，读取完成之后，再将锁释放掉。
- 从库 IO 线程：会连接到主库，向主库发送请求更新 Binlog，这是从库的 IO 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志。
- 从库 SQL 线程：会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。
4. 同步数据一致性问题
主从同步的要求：
- 读库和写库的数据一致（最终一致）
- 写数据必须写到写库
- 读数据必须到读库（不一定）
4.1 主从延迟问题
进行主从同步的内容是二进制日志，它是一个文件，在进行网络传输的过程中就一定会存在主从延迟，这样可能造成用户在从库上读取的数据不是最新的数据，也就是主从同步中的数据不一致问题。
主从延迟的时间点包括以下三点：
- T1：主库A执行完一个事务，写入 binlog 的时刻
- T2：之后传给从库B，从库B接收完这个 binlog 的时刻
- T3：从库B执行完这个事务的时刻
4.2 数据一致性问题
读写分离的情况下，解决主从同步数据不一致的问题，就是解决主从之间数据复制的问题，按数据一致性从弱到强来划分：
方式一：异步复制
异步方式就是客户端提交 COMMIT 后不需要等待从库返回的任何结果，而是直接将结果返回给客户端，这样不会影响主库写的效率，但可能存在主机宕机，但是 Binlog 还没有同步到从库的情况，也就是此时的主库和从库数据不一致。
方式二：半同步复制
在提交了 COMMIT 之后不直接将结果返回给客户端，而是等待至少有一个库接收到了 Binlog，并且写入到中继日志中，在返回给客户端。这样的好处就是保持了数据一致性，对于异步复制来说，至少多增加了一个网络连接的延迟，降低了主库写的效率。
方式三：组复制
异步复制和半同步复制都无法最终保证数据的一致性问题。MGR 很好地弥补了这两种复制模式的不足。
组复制技术（MGR）是基于 Paxos 协议的状态机复制。我们将多个节点共同组成一个复制组，在执行读写事务的时候，需要通过一致性协议层的同意，大多数指同意的节点数需要大于一半，这样才可以提交。而对于只读的事务则不需要经过组内同意，直接 Commit 就好。
十九. 数据库备份和恢复
1. 物理备份和逻辑备份
物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但是占用空间大。MYSQL 中可以用 xtrabackup 工具进行物理备份。
逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但是占用空间小，更加灵活。MYSQL 中常用的逻辑备份工具为 mysqldump。逻辑备份就是备份 SQL 语句，在恢复的时候执行备份的 SQL 语句实现数据库数据的重现。
2. mysqldump 实现逻辑备份
2.1 备份一个数据库
mysqldump -u 用户名 -p 待备份的数据库名称[tbanme[,tbname...]] > 备份文件名.sql;
2.2 mysql 命令恢复数据
mysql -u 用户名 -p [dbname] < backup.sql;
3. 物理备份：直接复制整个数据库
为了保证备份的一致性：
- 方式1：备份前，将服务器停止。
- 方式2：备份前，对相关表执行 FLUSH TABLES WITH READ LOCK 操作。
但是实际情况可能是不允许停止 MYSQL 数据库或者锁住表，而且这种方法对于 InnoDB 存储引擎的表不适用，对于 MyISAM 的表来说很方便。但是最好还要是相同版本的 MYSQL。
注意：物理备份完毕后，执行 UNLOCK TABLES 来结算其他客户对表的修改行为
扫一扫
1622
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
