MySQL高级学习笔记_1)设置mysql的日志管理功能,并分析日志内容设置加密字段,并比较与未加密前用户级-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_66400215/article/details/130631560

MySQL高级

第一章、LInux下MySQL的安装和使用

安装前的说明
- 安装并启动好一台虚拟机：CentOS 7
- 掌握克隆虚拟机的操作
  - mac地址
  - 主机名
  - IP地址
  - UUID
- 安装有Xshell和Xftp等访问CentOS系统的工具
- CentOS 6和CentOS 7在MySQL的使用中的区别：
```
1. 防火墙：6是iptables， 7是firewalld
2. 启动服务的命令：6是service 7是systemctl
```
查看是否安装过MySQL
- 如果你是用rpm安装，检查一下RPM PACKAGE:
```
rpm -qa | grep -i mysql #-i 忽略大小写
```
- 检查mysql service：
```
systemctl status mysqld.service
```
MySQL的卸载：
1. 关闭mysql服务
```
systemctl stop mysqld.service
```
2. 检查当前mysql安装的状况
```
rpm -qa | grep -i mysql
#或者
yum list installed | grep mysql
```
3. 卸载上述命令查询出的已安装程序
```
yum remove mysql -xxx mysql -xxx mysql -xxx mysqlk-xxxx
```
  务必卸载干净，反复执行rpm -qa | grep -i mysql 确认是否有卸载残留
4. 删除mysql相关的文件
  - 查找相关文件
```
find / -name mysql
```
  - 删除上述命令查找出的相关文件
```
re -rf xxx
```
5. 删除my.cnf
```
rm -rf /etc/my.cnf
```

MySQL的安装（略）

查看MySQL服务状态

#查看状态
systemctl status mysql / mysqld
#启动服务
systemctl start mysql / mysqld

启动MySQL服务：

service mysql start 
或者
service mysqld start

#重启服务
systemctl restart mysql / mysqld

设置开机自启服务

systemctl enable mysqld.service / mysql.service

cd /usr/local/mysql/bin/
./mysql -uroot -p

MySQL实现远程登录

方式1：关闭防火墙
- 查看防火墙状态
```
systemctl status firewalld
```
- 关闭防火墙
```
systemctl stop firewalld
```
- 开机自动关闭防火墙
```
systemctl disable firewalld
```
方式2：打开防火墙并开放端口号(略)

注：完成上述操作后还需要修改mysql的用户访问权限

mysql> select host, user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+
#修改完成后刷新权限
flush privileges;

各个级别的字符集
- MySQL有4个级别的字符集和比较规则，分别是：
  - 服务器级别
  - 数据库级别
  - 表级别
  - 列级别
- 查看字符集
```
show variables like '%character%';
```
- 字符集与比较规则
  1. utf8与utf8mb4
    
    utf8字符集表示一个字符需要使用1_{4个字节，但是我们常用的一些字符使用1}3个字节就可以表示了，二字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计MySQL的设计者偷偷定义了两个概念
    - utf8mb3：阉割过的utf8字符集，只使用1~3个字节表示字符
    - utf8mb4：正宗的utf8字符集，使用1~4个字节表示字符
    在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着1~3个字节来表示一个字符。如果大家有使用4字节码编码一个字符的情况，比如存储一些emoji表情，那请用utf8mb4
    
    此外，通过如下指令亦可以查看MySQL支持的字符集
```
show charset;
#或者
show character set;
```
SQL大小写规范
- Windows和Linux平台的区别
  
  在SQL中，关键字和函数名是不用区分大小写的，比如select、where、order、group by等关键字，已经abs、mod、round、max等函数名。
  
  不过在SQL中，你还是要确定大小写的规范，因为Linux和Windows环境下，你可能会遇到不同的大小写问题，Windows系统默认大小写不敏感，但是Linux系统是大小写敏感的。
- MySQL在Linux下数据库名、表名、列名、别名大小名规则是这样的：
  1. 数据库名、表名、表的别名、变量名是严格区分大小写的。
  2. 关键字、函数名称在SQL中不区分大小写
  3. 列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的
SQL编写规范
1. 关键字和函数名称全部大写
2. 数据库名、表名、表别名、字段名、字段别名等全部小写
3. SQL语句必须以分号结尾
4. 数据库名、表名和字段名在Linux MySQL环境下是区分大小写的，因此建议你统一这些字段的命名规则，比如全部使用小写的方式。
5. 虽然关键字和函数名在SQL中不区分大小写，也就是如果小写的话同样可以执行，但是同时将关键词和函数名称全部大写，以便于区分数据库名、表名、字段名。
sql_mode的设置
- 宽松模式
- 严格模式

第二章、MySQL的数据目录

MySQL8的主要目录结构
```
[root@atguigu01 ~]#find / -name mysql
```
安装好MySQL8.0之后，我们查看如下的目录结构：
- 数据库文件的存放路径
  
  MySQL数据库文件的存放位置：/var/lib/mysql/
  
  MySQL服务器程序在启动时会到文件系统的某个目录下加载一些文件，之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中，这个目录就是数据目录
  
  MySQL把数据都存到哪个路径下呢？其实数据目录对应这一个系统变量datadir，我们在使用客户端与服务器端建立连接之后查看这个系统变量的值就可以了：
```
mysql> show variables like 'datadir';
```
- 相关命令目录
  
  相关命令目录：/usr/bin (mysqladmin, mysqlbinlog, mysqldump等命令)和/usr/sbin
  
  安装目录下非常重要的bin目录，它里面存储了许多关于控制客户端程序和服务器程序的命令（许多可执行文件，比如mysql、mysqld、mysqld_safe等）。而数据目录是用来存储MySQL在运行过程中产生的数据，注意区分二者。
- 配置文件目录
  
  配置文件目录：/usr/share/mysql-8.0（命令及配置文件）， /etc/mysql（如my.cnf）
数据库和文件系统的关系
- 查看默认数据库
```
mysql> show databases;
```
  可以看到有4个数据库是属于MySQL自带的系统数据库
  - mysql
```
MySQL系统自带的核心数据库，它存储了mysql的用户账户和权限信息，一些存储过程、时间的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
```
  - information_schema
```
MySQL系统自带得数据库，这个数据库保存着MySQL服务器维护所有其他数据库的信息，比如有哪些表、哪些视图、触发器、列、索引。这些信息并不是真实的用户信息，而是一些描述信息，有时候也称为元数据，在该数据库中提供了一些以innodb_sys开头的表，用于表示内部系统表。
```
  - performance_schema
```
MySQL系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以用来监控MySQL服务的各类性能指标。包括统计最近执行了哪些语句，在执行的过程中的每个阶段都花费了多长时间，内存的使用情况等信息。
```
  - sys
```
MySQL系统自带的数据库，这个数据库主要是通过视图的形式把information_scheme和performance_schema结合起来，帮助系统管理员和开发人员监控MySQL的技术性能。
```
- 小结
  
  举例：数据库a，表b
  1. 如果表b采用InnoDB，data\a中会产生1个或者2个文件
    - b.frm：描述表结构文件，字段长度等
    - 如果采用系统表空间模式的，数据信息和索引信息都存储在ibdata1中
    - 如果采用独立表空间存储模式，data\a中还会产生b.ibd文件（存储数据信息和索引信息）
    此外：
    1. MySQL5.7中会在data\a的目录下生成db.opt文件用于保存数据库的相关配置。比如字符集、比较规则。而MySQL8.0中不在提供db.opt文件
    2. MySQL8.0中不在提供单独的b.frm,而是合并在b.ibd文件中
  2. 如果表b采用MyISAM，data\a中会产生3个文件
    - MySQL5.7中：b.frm:描述表结构文件、字段长度等
      
      MySQL8.0中b.xxx.sdi:描述表结构文件、字段长度等
    - b.MYD：数据信息文件，存储数据信息（如果采用独立表存储模式）
    - b.MYI:存放索引信息文件
- 视图在文件系统的表示
  
  我们知道MySQL的视图其实是虚拟的表，也就是某个查询语句的一个别名而已，索引在存储视图的时候是不需要存储真实的数据的，只需要把它的结构存储起来就行了。和表一样，描述视图结构的文件也会被存储到所属数据库对应的子目录下边，只会存储一个视图.frm的文件，如emp_deatals_view.frm

第三章、用户与权限管理

1、用户管理

MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限；普通用户只拥有被授予的各种权限。
MySQL提供了许多语句用来管理用户账号，这些语句可以用来管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。
MySQL数据库的安全性需要通过账户管理来保证。

登录MySQL服务器

启动MySQL服务后，可以通过mysql命令来登录MySQL服务器，命令如下：

mysql -h hostname | hostIP -P port -u username -p DatabaseName -e "SQL语句"

[root@localhost bin]# ./mysql -h localhost -P 3306 -p it_cast -e "select * from user";
Enter password: 
+----+--------+----------+---------------------+
| id | gender | username | password            |
+----+--------+----------+---------------------+
|  1 | 男     | 关羽      | guanyuzuishuaic.com |
|  2 | 男     | 吕布      | libuzuishuaic.com   |
+----+--------+----------+---------------------+

2用户管理

创建用户

create user 'zhangsan' @ '%' identified by zhangsan;

修改用户

update mysql.user set user = 'lisi' where user = 'zhangsan';

flush privileges;

删除用户
```
drop user 'zhangsan'@'localhost';
```

2、用户密码的设置

设置当前用户的密码

适用于root用户修改自己的密码，以及普通用户登录后修改自己的密码

root用户拥有很高的权限，因此必须保证root用户的密码安全。root用户可以通过多种方式来修改密码，使用alter user修改用户密码是MySQL官方推荐的方式，也可以通过set语句修改密码。由于MySQL8中移除了password（）函数，因此不再使用update语句直接操作用户表修改密码

旧写法：
```
#mysql5.7测试有效
set password = password('root');
```
推荐写法:使用alter user命令来修改当前用户密码

用户可以使用alter命令来修改自身密码，基本语法如下：
```
alter user user() identified by 'new_password';
```
修改其他用户密码

root用户不仅可以修改自己的密码，还可以修改其他普通用户的密码。root用户登录MySQL服务器后，可以通过alter语句和set语句来修改普通用户的密码。由于password（）函数已经移除，因此使用update直接操作用户表的方式已不再使用
1. 使用alter语句来修改普通用户的密码
  
  基本语法：
```
alter user user_name identified by 'new_password';
```
2. 使用set命令来修改普通用户的密码
```
set password for 'user_name'@'host_name' = 'new_password';
```

3、权限管理

权限列表

MySQL到底都有多哪些权限呢？
```
mysql> show privileges;
```
授予权限的原则
1. 只能授予满足需要的最小权限，防止用户做出不当的操作。比如用户只是需要查询，那就只给select权限就可以了，不需要给用户赋予update、insert或者delete权限。
2. 创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段
3. 为每个用户设置满足密码复杂度的密码
4. 定期清理不需要的用户，回收权限或者删除用户。
查看用户权限
```
show grants;
```

为用户分配权限

grant 权限1，权限2，权限3 on 数据库名称.表名称 to 用户名@用户地址;

grant select, insert, delete on it_cast.* to zhangsan@%;

收回权限

revoke 权限1，权限2，权限3 on 数据库名称.表名称 from 用户名@用户地址;

总结：

	有一些程序员喜欢使用root超级用户来访问数据库，完全把权限控制放在应用层面实现。这样当然也可以。但是建议，尽量使用数据库自己的角色和用户控制机制来控制访问权限，不要轻易用root账号。因为root账号和密码放在代码里面不安全，一旦泄露，数据库就会完全时区保护。
	而且，MySQL的权限控制功能十分完善，应该尽量啊利用，可以提高效率，而且安全可靠。

权限表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中，MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表、db表。除此之外，还有table_priv表、column_priv表和proc_priv表等。在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。

4、角色管理(8.0的新特性)

创建角色：

create role 'role_name'[@'host_name'];

create role 'manager'@'localhost';

给角色赋予权限：

grant 权限1，权限2... on 数据库名.表名 to 'role_name'@'role_host';

grant select, insert on it_cast.* to 'manager';

查看角色的权限：
```
show grants for 'manager';
```

回收角色的权限：

revoke privileges on tablename from 'rolename';

删除角色
```
drop role role_name;
```

给用户赋予角色：

grant 'role_name' to 'user_name'@'user_host';

查看当前登录用户的角色
```
select current_role();
```

激活用户的角色

set default role all to 'user_name'@'user_host';

撤销用户角色
```
revoke role from use_name;
```

5、配置文件的使用

配置文件的格式

与命令行中指定启动选项不同的是，配置文件中的启动选项被划分为若干个组，每个组有一个组名，用中括号[]括起来，例如：
```
[server]
(具体的启动选项)
[mysqld]
(具体的启动选项)
[mysql_safe]
(具体的启动选项)
[clilent]
(具体的启动选项)
[mysql]
(具体的启动选项)
[mysqladmin]
```
像这个配置文件里就定义了许多个组，组名分别是server、mysqld、mysqld_safe…每个组下边可以定义若干个启动选项，我们以[server]组为例来看一下填写启动选项的形式
```
[server]
option1         #这是option1，该选项不需要选项值，类似于布尔类型的true和false
option2=value2  #这是option2，该选项需要选项值
```
同一个配置文件中多个组的优先级

如果在多个组中出现了同名的变量时，将最后一个出现组的的启动选项为准

第四章、逻辑架构

1、逻辑架构剖析

服务器处理客户端请求

首先MySQL是经典的C/S架构，即Client/Server架构，服务器端长须使用的mysqld

不论客户端进行和服务器进程是采用哪种方式进行通信，最后实现的效果都是：客户端进行向服务器进行发送一段文本（SQL语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）
Connectors

Connectors，指的是不同语言与SQL的交互，MySQL首先是一个网络程序，在TCP之上定义了自己的应用层协议。索引要使用MySQL，我们可以编写代码，跟MySQLServer建立TCP连接，之后按照其定义好的协议进行交互。或者比较方便的办法是调用SDK，比如JDBC等。但通过SDK来访问MySQL，本质上还是TCP连接上通过MySQL协议跟MySQL进行交互。

接下来MySQL Server结构可以分为如下的三层
- 第一层：连接层
  
  系统（客户端）访问MySQL服务器前，做的第一件事就是建立TCP连接。
  
  经过三次握手建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。
  - 用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行
  - 用户名面认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限
  问题：一个系统只会和MySQL服务器建立一个连接吗？只能有一个系统和MySQL服务器建立连接吗？
```
	当然不是，多个系统都可以和MySQL服务器建立连接，每个系统建立的连接肯定不止一个，所以，为了解决TCP无限创建与TCP频繁创建销毁带来得资源耗尽、性能下降的问题。MySQL服务器里有专门的TCP连接池限制连接数，采用长连接模式复用TCP连接，来解决上述问题。
	TCP连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后面的流程，每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。
	所以连接管理的职责是负责认证、管理连接、获取权限信息。
```
- 第二层：服务层
  - SQL Interface：SQL接口
    - 接受用户的SQL命令，并且返回用户需要查询的结果
    - MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口
  - Parser : 解析器
    - 在解析器中对SQL语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的
    - 在SQL语句传递到解析器的时候会被解析器验证和解析，并为其创建语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL还会对SQL查询进行语法上的优化，进行查询冲重写
  - Optimizer：查询优化器
  - Caches & Buffers：查询缓存组件

2、SQL执行流程

MySQL中的SQL执行流程

MySQL的查询流程
1. 查询缓存：Server如果在查询缓存中发现了这条SQL语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，索引MySQL8.0之后就抛弃这个功能了。（主要就是查询缓存的命中率）
  
  一般建议大家在静态表里使用查询缓存，什么叫静态表？就是一般我们极少更新的表，比如一个系统配置表、字典表，这张表上的查询才适合使用查询缓存。好在MySQL也提供了这种“按需使用”的方式。你可以将my.cof参数query_cache_type设置为DEMAND，代表sql语句中有SQL_CACHE关键词时才缓存。例如：
```
#query_cache_type 有3个值，0代表关闭查询缓存，1代表开启ON，2（DEMAND）
```
2. 解析器：在解析器中对SQL语句进行语法分析、语义分析
  
  如果没有命中查询缓存，就要开始真正执行语句了首先MySQL需要知道你要什么，因此需要对SQL语句做解析。SQL语句的分析分为词法分析和语法分析。
  
  首先，“词法分析”：你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么
  
  MySQL从你输入的“select”这个关键字识别出来，这是一个查询语句，它也要把字符串“T”识别程表名“T”，把字符串“ID”识别成列“ID”
  
  接着要进行“语法分析”：根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法
  
  如果语法不对，会收到"You have an error in your SQL syntax"的错误提醒
  
  如果语法正确，则会生成一颗语法树
3. 优化器：在优化器中会确定SQL语句的执行路径比如是根据全表检索，还是索引检索等。
  
  经过了解析器，MySQL就知道你要做什么了。在开始执行之前，还要先进过优化器的处理，一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
  
  在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段
4. 执行器：
  
  截止到现在，还没有真正去读写真实的表，仅仅只是产生了一个执行计划，于是就进入了执行器阶段
  
  在执行之前需要判断该用户是否具有权限。如果没有，就会返回权限错误。如果具备权限，就执行SQL查询并返回结果。在MySQL8.0以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。
  
  如果有权限，就会打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，调用引擎API对表进行读写。存储引擎API只是抽象接口，下面还有个存储引擎层，具体实现还是要看表选择的存储引擎
至此，这个语句就执行完成了。对于有索引的表，执行的逻辑也差不多。

SQL语句在MySQL的中的流程是：SQL语句->查询缓存->解析器->优化器->执行器
MySQL8中SQL执行原理
1. 确认profilling是否开启
  
  了解查询语句底层执行的过程：select @@profilling; 或者show variables like '%profilling%'查看是否开启计划。开启它可以让MySQL收集在SQL执行时所使用的资源情况，命令如下：
```
select @@profilling;
show variables like 'profilling';
```
  profilling=0代表关闭，我们需要把profilling打开，即设置为1：
```
set profilling=1;
```
  Profilling功能由MySQL会话变量：profilling控制。默认是OFF（关闭状态）
2. 多次执行相同的SQL查询
3. 查看profiles
```
show profiles;
```
4. 查看profile
  
  显示执行计划，查看程序的执行步骤
```
show profile for query 1;
```
  查看cpu、io阻塞等参数情况
```
show profile cpu,io
for query 1;
```
  除了查看cpu、io阻塞等参数情况，还可以查存下列参数的利用情况
```
all --显示所有参数的开销信息
block io-- 显示io的相关开销
cpu --显示cpu相关的开销信息
ipc -- 显示发送和接受相关开销的信息
memory --显示内存相关开销信息
page faults -- 显示页面错误相关开销信息
source --显示和Source_function,Source_file,Source_line相关的开销信息
swaps -- 显示交换次数相关的开销信息
```

3、数据库缓冲池（Buffer Poll）

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

好处就是让磁盘活动最小化，从而减少磁盘直接进行I/O的时间。

缓冲池

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

构成：数据页、索引页、插入缓存、锁信息、自适应索引哈希、数据字典信息

缓存原则：

"位置*频次"这个原则，可以帮我对I/O访问效率进行优化。
首先，位置决定效率，提供缓冲池也就是为了在内存中可以直接访问数据。
其次，频次决定优先顺序，因为缓冲池的大小是有限的，因为磁盘往往大于内存，无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会优先对使用频次高的热数据进行加载。

缓冲池的预读特性：

了解了缓冲池的作用之后，我们还需要了解缓冲池的另一个特性：预读。
缓冲池的作用就是提高I/O效率，而我们进行读取数据的时候存在一个"局部性原理"，也就是说我们使用了一些数据，大概率还会使用它周围的数据，因此采用"预读"的机制提前加载，可以减少未来可能的磁盘I/O操作。

如果我们执行SQL语句的时候更新了缓存池的数据，那么这些数据会马上同步到磁盘吗？

实际上，当我们对数据库中的记录进行修改的时候，首先会修改缓冲池中页里面的记录信息，然后数据库会以一定的频率刷新到磁盘上，注意并不是每次发生更新操作，都会立刻进行磁盘回写，缓冲池会采用一种叫做checkpoint的机制将数据回写到磁盘上，这样做的好处是提高了数据库的整体性能。

查看缓存池的大小

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)
#默认大小是128mb

设置缓冲池的大小
```
set global innodb_buffer_poll_size=268435456;
```
或者在配置文件中修改

查看缓冲池的实例大小

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

修改数据缓冲池数量

小结

并不是收Buffer Pool实例创建的越多越好，分别管理各个Buffer Pool也是需要性能开销的，InnoDB规定：当缓冲池大小小于1G的时候设置多个实例是无效的。InnoDB会默认把缓冲池实例个数修改为1。而我们鼓励在缓冲池大于或等于1G的时候设置多个缓冲池实例。

第五章、存储引擎

为了管理方便，人们把连接管理、查询缓存、语法分析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能。所以在MySQL server完成了查询优化后，只需要按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。

MySQL中提到了存储引擎的概念。简而言之，存储引擎就是指表的类型。其实存储引擎以前叫做表处理器，后来更名为存储引擎，它的功能就是接受上层传下来的指令，然后对表中的数据进行提取或写入操作。

查看存储引擎
```
show engines;
```

查看系统默认存储引擎

mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+

引擎的介绍：
- InnoDB：
  - InnoDB对比MyISAM，InnoDB写的处理效率会差一些，并且会占用更多的磁盘空间以保存数据和索引
  - MyISAM只缓存索引，不缓存真实数据InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。
- MyISAM：主要的非事务处理存储引擎
  - MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS），但MyISAM不支持事务、行级锁、外键，有一个毫无疑问的缺点就是崩溃后无法安全恢复。

第六章、索引的数据结构

1、索引及其优缺点

索引概述

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

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

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时存储引擎可以定义每个表的最大索引数和最大索引长度。
优点：
1. 提高数据检索的效率，减低数据库的IO成本。
2. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
3. 对于有依赖关系的子表和父表联合查询时，可以提高查询速度
4. 在使用分组和排序字句进行数据查询时，可以显著减少查询中分组和排序的时间，降低CPU的消耗
缺点：
1. 维护索引和创建索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加
2. 索引需要占用磁盘空间，除了数据表占用空间外，每一个索引还要占用一定的物理空间，存储在磁盘上
3. 虽然索引提高了查询速度，同时也会降低更新表的速度

2、常见索引的概念

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

1.聚簇索引

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

术语"聚簇"表示数据行和相邻的键值聚簇的存储在一起

特点：

使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
- 页内的记录是按照主键的大小顺序排成一个单向链表
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
- 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
B+树的叶子结点存储的是完整的用户记录

所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）

我们把具有这两种特性的B+数称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子结点处，这种聚簇索引并不需要我们在MySQL语句中显示的使用Index语句去创建，InnoDB存储引擎会自动的为我们创建聚簇索引。

优点：

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

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入时最快的方式。否则会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列作为主键
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
二级索引访问需要两次索引查找，第一次查找到主键值，第二次根据主键值找到行数据

限制：

对于MySQL数据库目前只有InnoDB数据引擎支持聚簇索引，而MyISAM并不支持聚簇索引
由于数据物理存储排序方式只有一种，所以每个MySQL表只有一个聚簇索引。一般情况下就是该表的主键
如果没有定义主键，InnoDB会选择非空的唯一索引代替。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引
为了充分利用聚簇索引的聚簇特定，所以InnoDB表的主键列尽量选用有序的顺序id，而不建议使用无序的id，比如UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长

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

上面介绍的聚簇索引只能在搜索条件时主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。那如果我们向以别的列作为搜索条件该怎么办？肯定不能是从头到尾沿着链表一次遍历记录一遍。

答案：我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则，再建一颗B+树。

3.回表

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

问题：为什么我们还需要一次回表操作呢？直接把完整的用户记录放到叶子结点不OK吗？

如果把完整的用户记录放到叶子结点是可以不用回表。但是太占用空间了，相当于每建立一颗B+树都需要把所有用户记录都拷贝一遍，这就有点太浪费空间了。

小结：聚簇索引与非聚簇索引的原理不同，在使用上也有一些区别：

聚簇索引的叶子结点存储的就是我们的数据记录，非聚簇索引的叶子结点存储的数据位置。非聚簇索引不会影响数据表的物理存储顺序。
一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据索引
使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入、删除、更新等操作，效率会比非聚簇索引低。

4.联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列键列索引，比方说我们想让B+树按照c2和c3列的大小进行排序，这个包含两层含义：

先把各个记录和页按照c2列进行排序
在记录的c2列相同的情况下，采用c3列进行排序

3、InnoDB的B+数索引的注意事项

1.根页面位置万年不动

我们前面介绍B+树索引的时候，为了大家理解上的方便，先把存储用户记录的叶子结点都画出来，然后接着画存储目录项目录的内结点，实际上B+树的形成过程是这样的：

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

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

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

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

一个B+树只需要很少的层级就可以轻松存储数亿条记录，查询速度相当不错！这是因为B+树本质上就是一个大的多层级目录，每经过一个目录时都会过滤掉许多无效得子目录，知道最后访问到存储真实数据的目录。那如果一个大的目录中只存放一个子目录是个啥效果？那就是目录层级非常的多，而且最后哪个存放真实数据的目录只能存放一条记录。费了半天劲只能存放一条真实的用户记录？所以InnoDB的一个数据页至少可以存放两条记录。

4、MyISAM和InnoDB对比

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

在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要建一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引
InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存记录的地址
InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM索引记录的是地址，换句话说所有非聚簇索引都引用主键作为data域
MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问
InnoDB要求必须要有主键（MyISAM可以没有）。如果没有显示规定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型

小结：

了解不同存储引擎的索引实现方式对正确使用索引和优化索引都非常有帮助，例如：

1.为什么不建议使用过长的字段作为主键索引？

因为所有二级索引都引用主键索引，过长的主键索引会令二级索引变得过大。

2.为什么在InnoDB中不建议使用非单调的字段作为主键？

因为InnoDB数据文件本身是一颗B+树，非单调的主键会造成在插入新纪录时，数据文件为了维持B+树的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

5、MySQL数据结构选择的合理性

哈希结构

Hash本身是一个函数，又称为散列函数，它可以帮助我们大幅度提升检索数据的效率。

Hash算法是通过某种确定的算法（比如MD5、SHA1、SHA2、SHA3）将输入转变为输出。相同的输入永远可以相同的输出，假设输入内容有微小偏差，在输出中通常会有不同的结果。

加速查找速度的数据结构，常见的有两类：
1. 树，例如平衡二叉搜索树，查询/插入/修改/删除的平均复杂度都是O(log2N);
2. 哈希，例如HashMap，查询/插入/修改/删除的平均复杂度都是O(1);
Hash结构效率高，那为什么索引结构要设计成树形呢？
```
原因1：Hash索引仅能满足等值查询。如果进行范围查询，哈希型的索引，时间复杂度就会退化为O(n)；而树形的“有序”特性，依然能够保持O(log2N)的高效率
原因2：Hash索引还有一个缺陷，数据的存储是没有顺序的，在Order by的情况下，使用Hash索引还需要对数据重新排序
原因3：对于联合索引的情况，Hash值时将联合索引建合并后一起来计算的，无法对单独的一个建或几个索引建进行查询。
原因4：对于等值查询来说，通常Hash索引的效率更高，不过页存在一种情况，就是索引列的重复值如果很多，效率就会降低。（哈希冲突较多时）
```
Hash索引适合的存储引擎：

索引/存储引擎 MyISAM InnoDB Memory
Hash索引不支持不支持支持
Hash索引的适用性：

Hash索引存在着很多限制，相比之下在数据库中B+树索引的使用面会更广，不过也有一些场景采用Hash索引效率更高，比如在键值型数据库中，Redis存储的核心就是Hash表

InnoDB本身不支持Hash索引，但是提供自适应Hash索引，什么情况下才会使用自适应Hash索引呢？如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到Hash表中，这样下次查询的时候，就可以直接找到这个页面的所在的位置，这样让B+树叶具备了Hash索引的优点。
AVL树

为了解决二叉搜索树退化成链表的问题，人们提出了平衡二叉搜索树（Balanced Binary Tree），又称为AVL树（有别于AVL算法），它在二叉搜索树的基础上增加了约束，具有以下性质：

它是一颗空树或它的左右两个子树的绝对值不超过1，并且左右两个子树都是一颗平衡二叉树。

这里说一下，常见的平衡二叉搜索树有很多，包括了平衡二叉搜索树、红黑树、树堆、伸展树。平衡二叉搜索树是最早提出的自平衡二叉搜索树，当我们提到平衡二叉树一般值得就是平衡二叉搜索树，事实上第一棵树就属于平衡二叉树，搜索时间复杂度就是O(log2N)
B-Tree

B树的英文是Balance Tree，也就是多路平衡查找树，简写为B-Tree

特点：
- B树在插入和删除节点的时候如果导致树不平衡，就通过自动调节结点的位置来保持树的自平衡。
- 关键字集分布在整棵树中，即叶子结点和非叶子结点都存放数据。搜索有可能在非叶子结点结束
- 其搜索性能等价于在关键字全集中做一次二分查找。
B+树和B树的差异：
1. 有k个孩子结点就有k个关键字。也就是孩子数量=关键字数，而B树中，孩子数量=关键字+1
2. 非叶子结点的关键字也会同时存在子节点中，并且是在子节点中所有关键字的最大（或最小）
3. 非叶子结点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子结点中。而B树中，非叶子结点既保存索引，也保存数据记录
4. 所有关键字都在叶子结点出现，叶子结点构成一个有序链表，而且叶子结点本身按照关键字的大小从小到大顺序连接

索引/存储引擎	MyISAM	InnoDB	Memory
Hash索引	不支持	不支持	支持

6、面试题

为什么InnoDB存储引擎选择使用B+tree索引结构？

相对于二叉树，层级更少，搜索效率更高
相对于B-tree，无论是叶子结点还是非叶子结点，都会保存数据，这样导致一个页中存储的键值减少，指针跟着减少，要同时保存大量数据，只能增加树的高度，导致性能降低
相对于Hash索引，B+tree支持的范围匹配和排序操作

InnoDB主键索引的B+tree高度为多高呢？

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eL5eak2C-1683812940543)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221128170729631.png)]

假设：

一行数据大小为1K，一页可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即视为bigint，占用字节数为8.

当树的高度为2时：

设非叶子结点存储的主键（key）个数为n，则n * 8 + （n + 1）* 6 = 16 * 1024，算出n约为1170，非叶子结点指针的个数 = 主键个数 + 1

说明一个非叶子结点下存储1171个指针（一个指针对应着一个页，一个页能存储16行数据），因为当前树的高度为2，也就能存储 1171 * 16 = 18736，即高度为2的树可以存储18736条数据

高度为3：

1171 * 1171 * 16 = 21939856

补充

InnoDB在插入时会产生页分裂，在合并的时候会产生页合并
在分组操作时，可以通过索引来提高效率
在分组操作时，索引的作用也是满足最左前缀法则的

limit优化：

limit 100000，10（效率低）
一般分页查询时，通过创建覆盖索引能够比较好的提高性能，可以通过覆盖索引加子查询的形式进行优化

update优化：

InnoDB的行锁是针对索引加锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁

第七章、InnoDB数据存储结构

1、数据的存储结构：页

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

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

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

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

不同的数据库管理系统的页大小不同，比如在MySQL的InnoDB存储引擎中，默认的页大小是16KB；SQL Server中页的大小是8KB；而在Oracle中我们使用术语“块”（Block）来代表“页”，Oracle支持的块大小为2、4、8、16、32和64KB
页的上层结构

另外在数据库中，还存在着区（Extent）、段（Segment）和表空间（Tablespace）的概念。行、页、区、段、表空间的关系如上图

区：是比页大一级的存储结构，在InnoDB存储引擎中，一个区会分配64个连续的页，因为InnoDB中页的大小默认是16KB，所以一个区的大小是64*16KB=1MB
段：是由一个或多个区组成，区在文件系统是一个连续分配的空间（在InnoDB中是连续的64个页），不过在段中不要求区与区之间是相邻的。段是数据库的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会响应创建对应的段。比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。
表空间是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等

2、从数据页的角度看B+树如何查询

一颗B+树按照结点类型可以分为两部分：

叶子结点：B+树最底层的结点，结点的高度为0，存储行记录。
非叶子结点，结点的高度大于0，存储索引键和页面指针，并不存储记录本身

1.B+树是如何进行记录检索的？

如果通过B+树的索引查询行记录，首先是从B+树的根开始，逐层检索，直到找到叶子结点，也就是找到对应的数据页为止，将数据页加载内存中，页目录中的槽（slot）采用二分查找的方式先找到一个粗略的记录分组，然后再在分组中通过链表遍历的方式查找记录。

2.普通索引和唯一索引在查询效率上有什么不同？

我们创建索引的时候可以是普通索引，也可以是唯一索引，那么这两个索引在查询效率上有什么不同呢？
唯一索引就是在普通索引上增加了约束性，也就是关键字唯一，找到了关键字就停止检索。而普通索引，可能会存在用户记录中的关键字相同的情况，根据页结构的原理，当我们读取一条记录的时候，不是单独将这条记录从磁盘中读出去，而是将这个记录所在的页加载到内存中进行读取。InnoDB存储引擎的页大小为16KB，在一个页中可能存储着上千条记录，因此在普通索引的字段上进行查找也就是在内存中多判断几次“判断下一条记录”的操作，对于CPU来说，这些操作所消耗的时间是可以忽略不计的。所以对一个索引字段进行检索，采用普通索引还是唯一索引在检索效率上基本没有差别。

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

常见的行格式：

compact
dynamic
compressed
redundant

4、区、段与碎片区

为什么要有区？

B+树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远。我们介绍B+树索引的适用场景的时候特别提到范围查找只需要定位到最左边的记录和最右边的记录，然互殴沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远，就是所谓的随机I/O。再一次强调，磁盘的速度和内存的速度差了好几个数量级，随机I/O是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查找的时候才可以使用所谓的顺序I/O。

引入区的概念：一个区就是在物理位置上连续的64个页。因为InnoDB中页的大小默认是16KB，所以一个区的大小是1MB。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费（数据不足以填满整个区），但是从性能角度看，可以消除很多随机I/O，功大于过！
为什么要有段？

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

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

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

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

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

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

所以以后某个段分配存储空间的策略是这样的：
- 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的
- 当某个段已经占用了32个碎片区之后，就会申请以完整的区为单位来分配存储空间
所以现在段不能仅定义为时某个区的集合，更精确的应该是某些零散的页面以及一些完整的区的集合。
区的分类

区大体上分为4中类型
- 空闲的区（free）：现在还没有用到这个区中的任何页面
- 有剩余空间的碎片区（free_frag）:表示碎片区中还有可用的页面
- 没有剩余空间的碎片区（full_frag）:表示碎片区中的所有页面都被使用，没有空闲页面
- 附属于某个段的区（fseg）：每一个索引都可以分为叶子借点段和非叶子节点段
处于free、free_frag以及full_frag这三个装填的区都是独立的，只属于表空间。而处于fseg状态的区是附属于某一个段的。

5、表空间

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

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

独立表空间

独立表空间，即每张表有一个独立表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间可以在不同的数据库之间进行迁移

空间可以回收（drop table操作可以自动回收表空间；其他情况，表空间不能自己回收）。如果对于统计分析或是日志表，删除大量数据后可以通过alter table tableName engine=InnoDB;回收不用的空间。对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重影响性能，而且还有机会处理
系统表空间

系统表空间的结构和独立表空间基本类似，只不过由于整个MySQL进程只有一个系统表空间，在系统表空间中会额外记录一些有关整个系统信息的页面，这部分是独立表空间中没有的。

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

1、索引的分类

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

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

1.普通索引

在创建普通索引的时候，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。

2.唯一性索引

使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值，在一张表里可以有多个唯一索引

3.主键索引

主键索引就是一种特殊的唯一性索引，在唯一性索引的基础上增加了不为空的约束，也就是NOT NULL + UNIQUE，一张表里最多只有一个主键索引

这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。

4.单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引。

5.多列（组合、联合）索引

多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。

使用组合索引时遵循最左前缀集合

6.全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用**【分词技术】**等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。

只能为char、varchar、text创建全文索引

使用参数FULLTEXT可以设置索引为全文索引。在定义索引的列上支持值的全文查找，允许这些索引列中插入重复值和空值。全文索引只能创建在char、varchar或text类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。

全文索引典型的有两种类型：自然语言的全文索引和布尔全文索引

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过50%的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。

随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，主键被solr、ElasticSearch等专门的搜索引擎所替代。

小结：不同的存储引擎支持的索引类型也不一样

InnoDB：支持B-tree、Full-text等索引，不支持Hash索引；

MyISAM：支持B-tree、Full-text等索引，不支持Hash索引；

Memory：支持B-tree、Hash等索引，不支持Full-text索引；

2、创建索引

MySQL支持多种方法在单个或多个列上创建索引：在创建表的定义语句create table中指定索引列，使用alter table语句在存在的表上创建索引，或者使用create index语句在已存在的表上添加索引。

1.创建表的时候创建索引

使用create table创建表时，除了可以定义列的数据类型外，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上隐式的创建了一个索引

create table if not exists t_user(
	id int primary key auto_increment,
    name varchar(15) unique,
    sex varchar(2)
);
#查看t_user表创建的索引
mysql> show index from t_user\G;
*************************** 1. row ***************************
        Table: t_user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: t_user
   Non_unique: 0
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

查看某张表的索引

show index from user;

显示创建表时创建索引，基本语法：

create table table_name [col_name data_type]
[unique | Fulltext | spatial] [index | key] [index_name] (col_name [length]) [ASC | DESC]

create table temp1(
	id int primary key auto_increment,
    price int,
    unique index idx_temp1_price (price) 
);
#查看t_user表创建的索引
mysql> show index from temp1\G;
*************************** 1. row ***************************
        Table: temp1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: temp1
   Non_unique: 0
     Key_name: idx_temp1_price
 Seq_in_index: 1
  Column_name: price
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

注：主键索引和primary key、唯一性索引和unique相互约束，有其中之一即可有相同的效果。

2.创建表之后创建索引

第一种语法格式：

alter table temp add  
[unique | Fulltext | spatial] [index | key] [index_name] (col_name [length]) [ASC | DESC]

第二种语法格式：

create [unique | fulltext | spatial] index index_name
on table_name (col_name[length],...) [ASC | DESC]

2.删除索引

方式一：语法格式

alter table tablename drop index index_name;

mysql> alter table goods drop index idx_goods_name;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

提示：添加auto_increment约束字段的唯一性索引不能删除

方式二：语法格式

drop index index_name on tablename;

mysql> drop index idx_user_password on user;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL8.0索引新特性
- 支持降序索引
- 隐藏索引

3、哪些情况适合创建索引

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

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段时唯一的，就可以直接创建唯一性索引，或者主键索引。这样可以更速地通过该索引来确定某条记录。

例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，可能会存在同名现象，从而降低查询速度。

建议：

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引（来源：Alibaba）
说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的。

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

某个字段在select语句的where条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

3.经常group by和order by的列

4.update、delete的where条件列

对数据按照某个条件进行查询后再进行update或delete的操作，如果对where字段创建了索引，就能大幅提升效率。原因是因为我们需要先根据where条件列检索出来这条记录，然后再对它进行更新和删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5.distinct字段需要创建索引

有时候我们需要对某个字段进行去重，使用distinct，那么对这个字段创建索引，也会提高查询效率。

原因：索引会对数据按某种顺序进行排序，所以在去重的时候也会快很多

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

首先，连接表的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
其次，对where条件创建索引，因为where才是对数据条件的过滤。如果在数据量非常大的情况下，没有where条件过滤是非常可怕的。
最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。（最好不要左边是int类型右边是varchar类型）

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

我们这里说的类型大小指的就是该类型表示的数据范围的大小。

我们在定义表结构的时候要显示的指定列的类型，以整数类型为例，有Tinyint、MediumInt、Int、BigInt等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型。这是因为：

数据类型越小，在查询时进行的比较操作较快
数据类型小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘的I/O带来得性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他二级索引的结点处都会存储一份记录的主键值，如果主键值使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O

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

假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题：

B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间约大
如果B+树索引中索引列存储的字符串很长，那么做字符串比较时会占用更多的时间

我们可以通过截取字段的前面一部分内容建立索引，这个就叫做前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。

建议：

【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
说明：索引的长度和区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名，索引长度)) / count(*)的区分度来确定。

9.区分度高（散列高）的列适合作为索引

列的基数是某一列中不重复数据的个数，比如2，5，8，2，5，8，2，5，8虽然有9条记录，但该列的基数却是3。也就是说，**在记录数一定的情况下，列的基数越大，该列中的值约分散；列的基数越小，该列中的值约集中。**这个列的基数指标非常重要，直接影响我们是否有效的利用索引，最好为列的基数大的列建立索引，为基数小的列建立索引的效果可能不好

可以使用公式select count(distinct a ) / count(*) from t;计算区分度，越接近1越好，一般超过0.33就算是比较高效的索引了。

扩展：联合索引把区分度高（散列性高的）列放在前面

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

这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率

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

4、哪些情况不适合创建索引

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

where条件（包括group by、order by）里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。

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

如果表记录太少，比如少于1000个，那么是不需要创建索引的。表记录太少，是否创建索引对查询效率的影响并不大。甚至说，查询花费的时间比遍历索引的时间还要短，索引可能不会产生优化效果。

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

在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量的重复数据，也不用创建索引。

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

第一层含义：频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。
第二层含义：避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却会降低更新表的速度。

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

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

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

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将他们删除，从而减少索引对更新操作的影响。

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

冗余索引

有时候有意无意的就对同一个列创建了多个索引，比附：index(a,b,c)相当于index(a)、 index(a,b)、index(a,b,c)

重复索引

例如col1既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，索引定义的唯一索引和普通索引时重复的，这种情况要避免。

第九章、性能分析工具的使用

1、数据库服务器的优化步骤

2、查看系统性能参数

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

show status 语句语法：

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：删除操作的次数

mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 7     |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show status like 'Uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 21566 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'innodb_rows_%'
    -> ;
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 5     |
| Innodb_rows_read     | 13    |
| Innodb_rows_updated  | 0     |
+----------------------+-------+
4 rows in set (0.00 sec)

查询最后一次查询的消耗

mysql> show status like 'last_query_cost';
#value 代表查询的数据页个数
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 1.599000 |
+-----------------+----------+
1 row in set (0.00 sec)

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

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句，认为是超出了我们的最大忍耐时间值。

它的主要作用是，帮助我们发现拿些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器放生阻塞、运行变慢的时候，检查一下慢查询日志，找出那些慢查询，对解决问题很有帮助。

默认情况下，MySQL服务器没有开启慢查询日志，需要我们手动设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件

开启慢查询日志参数

开启slow_query_log

#首先查看慢查询日志是否开启
mysql> show variables like '%slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set, 1 warning (0.00 sec)

#开启慢查询日志
#由于默认是global类型的变量因此需要指明global
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.01 sec)

查看慢查询阈值时间

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

#修改时间阈值
mysql> set long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

查看慢查询数目

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

补充说明：
除了上述变量，控制慢查询日志还有一个系统变量：min_examined_row_limit。这个变量的意思是，查询扫描过的最少的记录数。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描的记录数大于等于这个变量的值，并且查询执行时间超过long_query_time的值，那么，这个查询就被记录到慢查询日志中；反之，则不会被记录到慢查询日志中。
mysql> show variables like 'min_%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set, 1 warning (0.00 sec)

慢查询分析工具：mysqldumpslow

这是一个脚本程序，不是在mysql中执行的，具体使用（略）

4、查看SQL执行成本：show profile

showprofile是MySQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭的状态，并保存最近15次的运行结果。

#查看是开启状态
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
#打开profiling功能
mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看最近执行的SQL查询语句

show profiles;
mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration   | Query              |
+----------+------------+--------------------+
|        1 | 0.00019325 | select * from user |
+----------+------------+--------------------+
1 row in set, 1 warning (0.00 sec)

如果我们想查看最近一次查询的开销

mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000044 |
| checking permissions | 0.000004 |
| Opening tables       | 0.000016 |
| init                 | 0.000010 |
| System lock          | 0.000005 |
| optimizing           | 0.000003 |
| statistics           | 0.000008 |
| preparing            | 0.000006 |
| executing            | 0.000002 |
| Sending data         | 0.000037 |
| end                  | 0.000002 |
| query end            | 0.000005 |
| closing tables       | 0.000004 |
| freeing items        | 0.000043 |
| cleaning up          | 0.000007 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

show profile 常用的查询参数：
- all：显示所有的开销信息
- block io：显示块io开销
- context switches：上下文切换开销
- cpu：显示cpu开销信息
- ipc：显示发送和接受开销信息
- memory：显示内存开销信息
- page faults：显示页面错误开销信息
- source：显示source_function, source_file, source_line相关的开销信息
- swaps：显示交换次数开销信息
日常开发需要注意的结论：
- converting HEAP to MyISAM : 查询结果太大，内存不够，数据往磁盘上搬了
- creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表
- copying to tmp table on disk ：把内存中临时表复制到磁盘上，警惕!
- locked
如果在show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化

注意：
```
show profile 命令将被弃用，我们可以从information_schema中的profiling数据表进行查看。
```

5、分析查询语句explain（重点）

概述

	定位了慢查询的sql之后，我们就可以使用explain或describe工具做针对性的分析查询语句。describe语句的使用和explain语句是一样的，并且分析结果也一样。
	MSQL中有专门负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划。
	这个执行计划展示了接下来具体执行查询的方式。MySQL为我们提供了explain语句来帮助我们查询某个查询语句的具体执行计划，看懂explain语句的各个输出项，可以有针对性的提升我们查询语句的性能。

能做什么？
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询

explain语句输出的各个列的作用：

列名	描述
id	select查询的序列号，表示查询中执行select字句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）
select_type	表示select的类型，常见的取值有simple（简单表，即不使用表连接或者子查询）、primary（主查询，即外层查询）、union（union中的第二个或者后面的查询语句）、subquery（select或者where之后包含了子查询）等
table	表名
partitions	匹配的分区信息
type	表示连接类型，性能由好到坏：NULL、system、const、eq_ref、ref、range、index、all
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度（字节数）
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
extra	一些额外的信息

explain执行结果各个字段的一些说明

table

1.查询的每一行记录对应着一个单表

1.id如果相同，可以人为是一组，从上往下顺序执行
2.在所有组中，id值越大，优先级越高，越先执行
3.关注点：id号每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好
4.每一个id对应着一个select语句

select_type

1.select关键字对应的那个查询的类型，确定小查询在整个大查询中扮演了以一个什么角色
2.查询语句中不包含union和子查询的都算simple

type

执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称为“访问类型”，其中type列就表明了这个访问方法是啥，是较为重要的一个指标。比如看到type列的值时ref，表明MySQL将使用ref访问方法来执行对s1表的查询。

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

详细解释：

system

当表中只有一条记录并且该表使用的存储引擎的统计数据时精确的，比如MyISAM、Memory，那么对该表的访问方法就是system

#创建一张临时表，指定存储引擎为MyISAM
mysql> create table temp (id int) engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)

#向临时表中插入一条记录
mysql> insert into temp values (1);
Query OK, 1 row affected (0.01 sec)

#用explain工具查看当前语句的执行计划，所用的访问方法即为system，效率最高
mysql> explain select * from temp\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: temp
   partitions: NULL
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

const

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

#常见的就是用主键值进行等值匹配查找的时候，使用const
mysql> explain select * from goods where id = 1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

eq_ref

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

ref

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

mysql> explain select * from goods where account = 10\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
   partitions: NULL
         type: ref
possible_keys: idx_account
          key: idx_account
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ref_or_null

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

mysql> explain select * from goods where account=1 or account=null\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
   partitions: NULL
         type: ref_or_null
possible_keys: idx_account
          key: idx_account
      key_len: 5
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

index_merge

表单访问方法时在某些场景下可以使用"Intersection", “Union”, "Sort-Union"这三种索引合并方式
unique_subquery

是指对在一些包含in子查询的查询语句中，如果查询优化器决定将in子查询转化为exists子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery

如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法

mysql> explain select * from goods where id in (1,2)\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index

当我们通过二级索引查找数据，而不用会表时使用的访问方式就是index

小结：

结果从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null >index_merge > uinque_subquery > index_subquery > range > index > all

SQL性能优化的目标：至少要达到range级别，要求是ref级别，最好是const级别（阿里巴巴开发手册要求）
ref：当使用索引列进行等值查询时，与索引列进行等值匹配的对象信息
rows：预估的需要读取的记录条数（值越小越好）

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

mysql> select * from goods where id < 5 ;
+----+-----------+---------+-------+
| id | name      | account | price |
+----+-----------+---------+-------+
|  1 | computer  |     990 |  7999 |
|  2 | 拯救者    |     100 |  9999 |
|  3 | 小米      |      98 |  3999 |
|  4 | 荣耀      |       9 |  4999 |
+----+-----------+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from goods where id < 5 and account > 10;
+----+-----------+---------+-------+
| id | name      | account | price |
+----+-----------+---------+-------+
|  1 | computer  |     990 |  7999 |
|  2 | 拯救者    |     100 |  9999 |
|  3 | 小米      |      98 |  3999 |
+----+-----------+---------+-------+
3 rows in set (0.00 sec)

mysql> explain select * from goods where id < 5 and account > 10\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
   partitions: NULL
         type: range
possible_keys: PRIMARY,idx_account
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
     filtered: 75.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Extra

顾名思义，Extra列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，下面只介绍一些常见的信息。
- No tables used
  
  当查询的语句没有from字句时会提示该额外信息
```
mysql> explain select 1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
1 row in set, 1 warning (0.00 sec)
```

小结：
- Explain考虑各种Cache
- Explain不能显示MySQL在执行查询时所作得优化工作
- Explain不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
- 部分统计信息是估算的，并非精确值

6、Explain的进一步使用

explain的四种输出格式

explain可以输出四种格式：传统格式、JSON格式、tree格式以及可视化输出

1.传统格式

传统格式简单明了，输出是一个表格形式，概要说明执行计划。

2.JSON格式

传统格式的explain语句输出中缺少了一个衡量执行计划好坏的重要属性–成本。而JSON格式是四种格式里面输出信息最详尽的格式，里面包含了执行的成本信息。

JSON格式：在explain单词和真正的查询语句中间加上format=json

explain format=json select ...

mysql> explain format=json select * from user;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.60"
    },
    "table": {
      "table_name": "user",
      "access_type": "ALL",
      "rows_examined_per_scan": 3,
      "rows_produced_per_join": 3,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "0.60",
        "prefix_cost": "1.60",
        "data_read_per_join": "6K"
      },
      "used_columns": [
        "id",
        "name",
        "password",
        "email"
      ]
    }
  }
} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

3.Tree格式

Tree格式是8.0.16之后引入的，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。

4.可视化输出

在MySQLWorkbench中可以查看

show warnings

在我们使用explain语句查看了某个查询的执行计划后，紧接着还可以使用show warnings语句查看与这个查询的执行计划有关额一些扩展信息

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

optimizer_trace是MySQL5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策（比如访问表的方法、各种开销计算、各种转换等），并跟踪结果记录到information_schema、optimizer_tarce表中。

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

set optimizer_trace="enable=on", end_markers_in_json=on;

set optimizer_trace_max_men_size=1000000;

#测试sql语句
select * from student where id < 10;

select * from information_schema.optimizer_tarce\G;

9、MySQL监控分析视图-sys schema

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

Sys schema视图摘要
1. 主机相关：以host_summary开头，主要汇总了IO延迟的信息。
2. InnoDB相关：以innodb开头，汇总了Innodb buffer信息和事务等待innodb锁的信息
3. i/o相关：以io开头，汇总了等待I/O, I/O使用量情况
  
  。。。。。。。

通过sys库去查询时，MySQL会消耗大量资源去收集相关的信息，严重的可能会导致业务请求被阻塞，从而引起故障。建议生产上不要频繁的去查询sys或者performance_schema、information_schema来完成监控，巡检等工作

第十章、索引优化与查询优化

都有哪些维度可以进行数据库调优？

索引失效、没有充分利用到索引–索引建立
关联查询太多JOIN（设计缺陷或不得已的需求）–SQL优化
服务器调优以及各个参数（缓冲、线程数等）-- 调整my.cnf
数据过多-- 分库分表

关于数据库调优的知识点非常分散，大体上可以完全分成物理查询优化和逻辑查询优化两大块

物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用
逻辑查询优化就是通过SQL等价变化提高查询效率，直白一点就是换一种查询写法执行效率可以更高。

1、索引失效

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

使用索引可以快速定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。
如果查询时没有索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

大多数情况下都默认采用B+树来创建索引。只是空间列类型的索引使用R-树，并且Memory表还支持Hash索引。

其实，用不用索引，最终都是优化器说了算。优化器是基于cost开销（CostBaseOptimizer），它不是基于规则，也不基于语义。怎么开销小怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

最左前缀法则

	结论：MySQL可以为多个字段创建索引，一个索引可包含16个字段。对于多级索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有是一个这些字段中的第一个字段，多列（或联合）索引就不会被使用。
	《Java开发手册》：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

主键插入顺序

对于一个使用InnoDB存储引擎的表来说，在我们没有显示的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中，数据页和记录又是按照记录主键值从大到小的顺序进行排序，如果不按主键顺序插入容易导致页分裂导致性能损耗。

所以，如果我们想尽量避免这样性能损耗，最好让插入的记录的主键值依次递增。建议：让主键有auto_increment，让存储引擎自己为表生成主键，而不是我们手动插入。

计算、函数、类型转换（自动或手动）导致索引失效

select * from student where left(student.name, 3) = 'abc';

select * from id, stuno from student where stuno + 1 = 10001;

select id, stuno, name from student where substring(name, 1, 3)='abc';

#类型转换导致索引失效(name是varchar)
select * from student where name=123;
#范围条件右边的索引失效
#下面的三个字段是联合索引，但由于第二个索引采用了范围查找，导致name字段的索引没有使用上
select * from student where age = 30 and classId > 20 and name = 'abc';

注意：创建联合索引的字段，在查询语句中的顺序无所谓，优化器会对其进行优化。

范围条件右边的索引失效

应用开发过程中范围查询，例如：金额查询、日期查询往往都是范围查询，应将查询条件放置到where语句最后。

不等于，索引失效

isnull使用索引，is not null不使用索引（不固定，取决于数据分布，如果null值较多，is not null可能会走索引）

结论：最好在设计数据表的时候就将字段设置为not null约束，比如可以将int类型的字段，默认值设为0，将字符创类型的默认值设置为空字符串
扩展：同理，在查询中使用not like 也无法使用索引，导致全表扫描。

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

《Java开发手册》：【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

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

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引
select * from tb_user where phone >= '17999005' #走索引
select * from tb_user where phone >= '17999015' #不走索引

一般性建议：
- 对于单列索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的的时候，当query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
- 在选择组合索引的的时候，尽量选择能够包含当前query中的where字句中更多字段的索引
- 在选择组合索引的的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面

2、关联查询优化

内连接

结论：
1.对于内连接来说，查询优化器可以决定谁作为驱动表和被驱动表，因为内连接的两张表地位相同。
2.对于内连接来说，如果表的连接条件只有一个字段有索引，则有索引的字段所在的表会作为被驱动表
3.对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。"小表驱动大表"

JOIN语句原理

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

1.驱动表和被驱动表

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

2.Simple Nested-Loop Join（简单嵌套循环连接）

算法相当简单，从A表中取出一条数据1，遍历表B，将匹配到的数据放到result以此类推，驱动表A中的每一条记录与被驱动表B的记录进行判断。

这种方式效率非常低。

3.Index Nested-Loop Join（索引嵌套循环连接）

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

驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故mysql优化器都倾向于使用记录数较少的表作为驱动表（外表）。

4.Block Nested-Loop Join（块嵌套循环连接）

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

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

参数设置
- block_nested_loop
  
  通过show variables like ‘optimizer_switch’ 查看block_nested_loop状态。默认是开启的
- join_buffer_size
  
  驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下join_buffer_size=256K
  
  join_buffer_size的最大值在32位系统可以申请4G，而在64位操作系统下可以申请大于4G的大小。
- 小结：
```
1.整体效率比较：INLJ > BNLJ > SNLJ
2.永远用小结果集驱动大结果集（本质上就是减少外层循环的数据数量）（小的量度指的是 表行数*每行大小）
3.为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）
4.增大join buffer size大小（一次缓存的数据越多，那么内存表的扫描次数就越少）
5.减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多）
```
- Hash Join
  
  从MySQL的8.0.20版本开始将废弃BNLJ，因为MySQL8.0.18版本开始就加入了hash join默认都会使用hash join
  - Nested Loop：
    
    对于被连接的数据子集较小的情况，Nested Loop是个较好的选择
  - Hash Join是做大数据连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用Join Key在内存中建立散列表，然后扫描较大的表并探测散列表，找出与Hash 表匹配的行
```
1.这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。
2.在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干个不同的区，不能放入内存的部分就把带分区写入磁盘的临时段，此时要求有较大的临时段从而尽可能提高I/O的性能。
3.它能够很好的工作没有索引的大表和并行查询的环境中，并提供很好的性能。
```

3、子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行select语句的嵌套查询，即一个select查询的结果作为另一个select语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

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

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

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

4、排序优化

排序优化

在where字段上加索引，但是为什么在order by字段上还要加索引呢？

在MySQL中，支持两种排序方式，分别是FileSort和Index排序。
1.Index排序中，索引可以保证数据的有序性，不需要在进行排序，效率更高。
2.FileSort排序则一般在内存中进行排序，占用CPU较多。如果待排序结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率极低

优化建议：

1.SQL中，可以在where字句和order by字句中使用索引，目的是在where字句中避免全表扫描，在order by		字句避免FileSort排序，当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢，但总的来说，我们还是要避免，以提高查询效率。
2.尽量使用index完成order by排序。如果where和order by后面是相同的列就使用单列索引；如果不同就使用联合索引。
3.无法使用index时，需要对FileSort方式进行调优。

常见的没有使用上索引的情况：

order by时不limit，索引失效
order by时顺序错误，索引失效
order by时规则不一致，索引失效（顺序错，不索引；方向反、不索引）

使用filesort不一定是坏事

所有的排序都是在条件过滤之后才执行的。所以，如果条件过滤大部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。

小结：

1.当两个索引同时存在，mysql会自动选择最优的方案，但是随着数据量的变化，选择的索引会随之变化的。
2.当【范围条件】和【group by或者order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

filesort算法

双路排序（慢）
- MySQL4.1之前使用的是双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中读取对应的数据输出
- 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序（快）

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

注意：

order by时select * 是一个大忌，最好只query需要的字段

1.当query的字段大小总和小于max_length_for_sort_data，而且排序字段不是text|Bolb类型时，会用改进后的算法--单路排序，否则用老算法--多路排序。
2.两种算法的数据都有可能超出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、优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好的提高性能。

7、优先考虑覆盖索引

什么是覆盖索引？

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

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

简单说就是，索引 + 主键包括 select 到 from之间查询的列。

覆盖索引的利弊

好处

1.避免InnoDB表进行索引的二次查询（回表）
	InnoDB是以聚簇索引的顺序来存储的，对于InnoDB来说，二级索引在叶子结点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找到响应的键值后，还需要通过主键进行二次查询才能获取我们真实所需要的数据。
	在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提高了查询效率。

2.可以把随机IO变成顺序IO加快查询效率
	由于覆盖索引时按照键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以吧磁盘的随机读取的IO转变成索引查找的顺序IO。
	由于索引可以减少树的搜索次数，显著提高查询性能，索引使用覆盖索引是一个常用的性能优化手段。

缺点：

索引字段的维护总是有代价的，因此在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者成为业务数据架构师的工作。

8、索引下推

使用前后对比

index Condition Pushdown（ICP）是MySQL5.6中的新特性，是一种在存储引擎层使用索引过滤数据的优化方式。
- 如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将他们返回给MySQL服务器，由MySQL服务器评估where后面的条件是否保留行。
- 启用ICP后，如果部分where条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分where条件放到存储引擎筛选。然后，存储引擎通过索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行
  - 好处：ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数
  - 但是，ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例
ICP的开启和关闭
- 默认情况下启用索引条件下推。可以通过系统变量optimizer_switch控制：index_condition_pushdown
- ```
#关闭索引下推
set optimizer_switch = 'index_contition_pushdown=off';

#开启索引下推
set optimizer_switch = 'index_condition_pushdown=on';
```
- 当使用索引下推条件时，explain语句输出结果中Extra列显示为Using index condition

使用ICP案例

create index idx_zc_fn_ln on people(zipcode, firstname, lastname);

explain select * from people where zipcode = '00001' and lastname like '%zhgn%' and address like '%beijing%';

#Using index condition; Using where

ICP的使用条件
1. 如果表访问类型为range、ref、eq_ref和ref_or_null可以使用ICP
2. ICP可以用于InnoDB和MyISAM，包括分区表InnoDB和MyISAM表
3. 对于InnoDB表，ICP仅用于二级索引。ICP的目的是减少全行读取次数，从而减少IO操作
4. 当SQL使用索引覆盖时，不支持ICP。因为这种情况下使用ICP不会减少IO
5. 相关子查询的条件不能使用ICP

9、其他查询优化策略

exists和in的区别

问题：不太理解哪种情况使用exists，哪种情况下使用in。选择的标准是看是否使用表的索引吗？

回答：索引是一个前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为小表驱动大表。在这种情况下是最高的

例子：
```
#如果B表是小表，A表是大表
select * from A where cc in (select cc from B);

#如果A表是小表，B表是大表
select * from A where exists (select cc from B where B.cc = A.cc);
```
count(*) 与 count(具体字段)的效率

问：在MySQL中统计数据表的行数，可以使用三种方式：count（*）、count（1）和count（具体字段）

前提：如果你要统计的是某个字段的非空数据行数，则另当别论，毕竟比较执行效率的前提是结果一样才可以。

环节1：count（*）和count（1）都是对所有结果进行count，所以本质上没有区别（二者执行时间略有差别，不过你还是可以把他俩的执行效率看成是一样的）。如果有where字句，则是对所有符合筛选条件的数据进行统计；如果没有where字句，则是对数据表的数据行数进行统计。

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

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

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

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

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

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

②无法使用覆盖索引
LIMIT1对优化的影响

针对的是扫描全表的SQL语句，如果你可以确定结果集只有一条，那么加上LIMIT1的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。

如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上LIMIT1了。
多使用COMMIT

只要有可能，在程序中尽量多使用commit，这样程序的性能会得到提高，需求也会因为commit所释放的资源而减少。

COMMIT所释放的资源：
- 回滚段上用于恢复数据的信息
- 被程序语句获得的锁
- redo / undo log buffer中的空间
- 管理上述3中资源中的内部花费

10、淘宝数据库，主键是如何设计的？

聊一个实际的问题：淘宝的数据库，主键是如何设计的？

某些错的离谱的答案还在网上年复一年的流传着，甚至还成为了所谓的MySQL军规。其中，一个最明显的错误就是关于MySQL的主键设计。

大部分人的回答如此自信：用8个字节的BIGINT做主键，而不要用INT。错！

这样的回答，只站在了数据库这一层，而没有从业务的角度思考主键主键就是一个自增的ID吗？用自增做主键，架构设计上可能连及格都拿不到。

10.1自增ID的问题

自增ID做主键，简单易懂，几乎所有数据库都支持自增类型，只是实际上各自有所不同而已。自增ID除了简单，其他都是缺点，总体来看存在一下几个方面的问题：

1.可靠性不高

存在自增ID回溯的问题，这个问题直到最新版本的MySQL8.0才修复

2.安全性不高

对外暴露的接口可有非常容易猜测对应的信息。比如：/User/1这样的接口，可有非常容易猜测用户ID的值为多少，总用户量有多少，也可以非常容易的通过接口进行数据的爬取。

3.性能差

自增ID的性能比较差，需要在数据库服务端生成。

4.交互多

业务还需要额外执行一次类似last_insert_id()的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。

5.局部唯一性

最重要的一点，自增ID是局部唯一的，只有当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。对于目前分布式系统来说，这简直就是噩梦。

经验：

刚开始使用MySQL时，很多人都很容易犯的错误就是喜欢喜欢用业务字段做主键，想当然地认为了解业务需求，但实际情况往往出乎意料，而更改主键设置的成本非常高。

10.2推荐的主键设置

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

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

这里推荐最简单的一种主键设置：UUID

UUID的特点：

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

认识UUID：

为什么UUID是全局唯一的？

在UUID中时间部分占用60位，存储的类似TIMESTAMP的时间戳，但表示的是从1582-10-15 00:00:00到现在的100ns的计数。可以看到UUID存储的精度比时间戳更高，时间维度发生重复的概率降低到1/100ns。
时钟序列是为了避免时钟被回拨导致产生时间重复的可能性。MAC地址用于全局唯一。

为什么UUID占用36个字节？

为什么UUID是无序的？

因为UUID的设计中，将时间低位放在最前面，而这一部分的数据是一直变化的，并且是无序的。

MySQL数据库的UUID组成如下所示：

UUID = 时间 + UUID版本（16字节） - 时钟序列（4字节） - MAC地址（12字节）

改造UUID

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

MySQL8.0还解决了UUID存在空间占用问题，除去了UUID字符串中无意义的“-”字符串，并且将字符串用二进制类型保存，这样存储空间降低为了16个字节。

可以通过MySQL8.0提供的uuid_to_bin函数实现上述功能，同样的，MySQL也提供了bin_to_uuid函数进行转化：

set @uuid = UUID();
select @uuid, uuid_to_bin(@uuid), uuid_to_bin(@uuid, TRUE);

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

在当今的互联网环境中，非常不推荐自增ID作为主键的数据库设计。更推荐类似有序UUID的全局唯一的实现。

另外在真实的业务系统中，主键还可以加入业务和系统属性，如用户的尾号、机房的信息等。这样的主键设计就更为考验架构师的水平了。

如果不是8.0版本的话怎办？

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

第十一章、数据库的设计范式

1、范式

1.1范式简介

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

范式的英文名称为Normal Form，简称NF。它是英国人E.F.Codd在上个世纪70年代提出关系型数据库模型后总结出来的。范式是关系型数据库理论的基础，也是我们在设计数据库结构的过程中所要遵循的规则和指导方法。

1.2范式都包括哪些

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

数据库的范式设计越高级，冗余度就越低，同时高阶的范式一定符合低阶的要求，满足最低要求的范式是第一范式。在第一范式的基础上进一步满足更多规范要求的称为第二范式，其余范式依次类推。

一般来说，在关系型数据库设计中，最高也就遵循到巴斯-科德范式，普遍还是第三范式。但也不绝对，有时候为了提高某些性能，我们还需要破坏范式规则，也就是反范式化。

1.3键和相关属性的概念

范式的定义会使用到主键和候选键，数据库中的键（Key）由一个或多个属性组成。数据表中常用的几种键和属性的定义：

超键：能唯一标识元祖的属性集叫做超键
候选键：如果超键不包含多余的属性，那么这个超键就是候选键
主键：用户可以从候选键中选择一个作为主键
外键：如果数据表R1中的某属性集不是R1的主键，而是另一个数据表R2的主键，那么这个属性就是数据表R1的外键
主属性：包含在任一候选键中的属性称为主属性
非主属性：与主属性相对，指的是不包含在任何一个候选键中的属性

通常，我们也将候选键称之为"码"，把主键也称为"主码"。因为键可能是由多个属性组成的，针对单个属性，我们还可以用主属性和非主属性来进行区分。

1.4第一范式

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

我们在设计某个字段的时候，对于字段X来说，不能把字段X拆分为字段X-1和字段X-2。事实上，任何DBMS都会满足第一范式的要求，不会将字段进行拆分。

1.5第二范式

第二范式要求，在满足第二范式的基础上，还要满足数据表里的每一条记录，都是可以唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能依赖主键的一部分。如果知道主键的所有属性值，就可以检索到任何元祖（行）的任何属性的任何值。（要求中的主键，其实可以扩展替换为候选键）

举例：

成绩表（学号，课程号，成绩）关系中，（学号，课程号）可以决定成绩，但是学号不能决定成绩，课程号也不能决定成绩，所以“（学号，课程号）->成绩“就是完全依赖关系。

当不满足第二范式时，常出现的问题：

数据冗余
插入异常
删除异常
更新异常

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

小结：第二范式要求实体的属性完全依赖主关键字。如果存在不完全依赖，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系

1.6第三范式

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

这里的主键可以扩展为候选键。

符合第三范式的数据模型通俗地讲，第二范式和第三范式通常以这句话概括:“每个非主键属性依赖于主键，依赖于整个键，并且除了键别无他物”。

1.7小结

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

1）第一范式，确保每列保持原子性

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

2）第二范式，确保每列都和主键完全依赖

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

3）第三范式，确保每列都有和主键列直接相关，而不是间接相关

范式的优点：数据的标准化有助于消除数据库的数据冗余，第三范式通常被认为在性能、扩展性和数据完整性方面达到了最好的平衡。

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

范式只是提出了设计的标准，实际上设计数据表的时候，未必一定要符合这些标准。开发中，我们会出现为了性能和读取效率违反范式化的原则。通过增加少量冗余或重复的数据来提高数据库的读性能，减少关联查询，join表的次数，实现空间换取时间的目的。因此在实际的设计过程中要理论结合实际，灵活运用。

范式本身没有优劣之分，只有适用场景不同。没有完美的设计，只有合适的设计，我们在设计数据表时，还需要根据需求将范式和反范式混合使用。

2、反范式化

2.1概述

有的时候不能简单按照规范要求设计数据表，因为有的数据冗余，其实对业务来说十分重要，这个时候，我们就要遵循业务优先的原则，首先满足业务需求，再尽量减少冗余。

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

规范化vs性能

1.为满足某种商业目标，数据库性能比规范化数据库更重要

2.在数据规范的同时，要综合考虑数据库的性能

3.通过在给定的表中添加额外的字段，以大量减少需要从表搜索信息所需的时间

4.通过在给定的表中插入计算列，以方便查询

2.2反范式化的新问题

反范式可以通过空间换取时间，提升查询的效率，但是反范式也会带来一些新问题：

存储空间变大了
一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则数据不一致
若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常消耗系统资源
在数据量小的情况下，反范式不能体现出性能的优势，可能还会让数据库的设计更加复杂

2.3反范式的适用场景

当冗余数据有价值或者能大幅度调高查询效率的时候，我们才会采用反范式的优化。

1.增加冗余字段的建议：

增加冗余字段一定要符合如下两个条件。只有满足这两个条件，才可以考虑增加冗余字段。

1）这个冗余字段不需要经常进行修改

2）这个冗余字段查询的时候不可或缺

2.历史快照、历史数据的需要

在现实生活中，我们经常需要一些冗余信息，比如订单的收货人信息，包括姓名、电话和地址等。每次发生的订单收货信息都属于历史快照，需要进行保存，但用户可以随时修改自己的信息，这时保存这些冗余信息是非常有必要的。

反范式化也常用在数据仓库的设计中，因为数据仓库通常存储历史数据，对增删改的实时性要求不强，对历史数据的分析需求强。这时适当允许数据的冗余度，更方便进行数据分析。

数据仓库和数据库在使用上的区别：

数据库设计的目的在于捕获数据，而数据仓库设计的目的在于分析数据
数据库对数据的增删改实时性要求强，需要存储在线的用户数据，而数据仓库存储的一般是历史数据
数据库设计需要尽量避免冗余，但为了提高查询效率页允许一定的冗余度，而数据仓库在设计上更偏向于采用反范式设计

3、巴斯范式、第四范式、第五范式、域键范式

人么在第三范式的基础上进行改进，提出了巴斯范式，也叫巴斯-科德范式。巴斯范式被认为没有新的设计范式加入，只是对第三范式中设计规范要求更高，使得数据库冗余更小。所以，称为是修正的第三范式，或扩充的第三范式，巴斯范式不被称为第四范式。

若一个关系达到了第三范式，并且它只有一个候选键，或者它的每个候选键都是单属性，则该关系自然达到了巴斯范式。

一般来说，一个数据库设计符合第三范式或巴斯范式就可以了

4、ER模型

数据库设计是牵一发而动全身的。那有没有什么工具能够看到数据库的全貌呢？比如需要哪些数据表、数据表中应该有哪些字段，数据表与数据表之间有什么关系、通过什么字段进行连接，等等。这样我们才能进行整体的梳理和设计。

其实,ER模型就是一个这样的工具。ER模型也叫做实体关系模型，是用来描述现实生活中客观存在的事务

事物的属性，以及事物之间关系的一种数据模型。在开发基于数据库的信息系统的设计阶段，通常使用ER模型来描述信息需求和信息特性，帮助我们清理业务逻辑，从而设计出优秀的数据库。

4.1ER模型包括哪些要素？

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

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

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

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

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

4.3关系的类型

在ER模型的3个要素中，关系又可以分为3中类型，分别是一对一、一对多、多对多。

一对一：指实体之间的关系是一一对应的，比如个人域身份证信息之间的关系就是一对一的关系。一个人只能有一个身份信息，一个身份信息页只属于一个人。

一对多:指一边的实体通过关系，可以对应多个另外一边的实体。相反另外一边的实体通过这个关系，则只能对应唯一的一边的实体。比如说，我们新建一个班级表，而每个班级有多个学生，每个学生则对应一个班级，班级对学生就是一对多的关系。

多对多：指关系两边的实体都可以通过关系对应多个对方的实体。一个选课表，有许多的科目，每个科目都有学生选，而每个学生又可以选择多个科目，这就是多对多的关系。

4.3通过外键来表达一对多的关系

外键约束主要是在数据库层面上保证数据的一致性，但是因为插入和更新数据需要检查外键，理论上性能会有所下降，对性能是负面的影响。

实际的项目，不建议使用外键，一方面是降低开发的复杂度（有外键的话主从主表类的必须先操作主表），另外是由外键在处理数据的时候非常麻烦。在电商平台，由于并发业务量比较大，所以一般不设置外键，以免影响数据库性能。

在应用层做数据的一致性检查，本来就是一个正常的需求。

5.数据表的设计原则

综合以上内容，总结出数据表设计的一般原则：“三少一多”

1.数据表的个数越少越好

RDBMS的核心就在于对实体和联系的定义，也就是E-R（Entity Relationship Diagram），数据表越少，证明实体和联系设计约简洁，既方便理解又方便操作。

2.数据表的字段个数越少越好

字段个数越多，数据冗余的可能性越大。设置字段个数少的前提是各个字段相互独立，而不是某个字段的取值可以由其他字段计算出来。当然字段个数少是相对的，我们通常会在数据冗余和检索效率中进行平衡。

3.数据表中联合主键的字段个数越少越好

设置主键是为了确定唯一性，当一个字段无法确定唯一性的时候，就需要采用联合主键的方式。联合主键中的字段越多越好，占用的索引空间越大，不仅会加大理解难度，还会增加运行时间和索引空间，因此联合主键的字段个数越少越好。

4.使用主键和外键越多越好

数据库的设计实际上就是定义各种表，以及各种字段之间的关系。这些关系越多，证明这些实体之间的冗余度越低，利用度越高。这样做的好处在于不仅保证了数据表之间的独立性，还能提升相互之间的关联使用率。

6、关于索引

InnoDB表必须主键为id int/bigint auto_increment,且主键值禁止被更新。
InnoDB和MyISAM存储引擎表，索引类型必须为BTREE（这里指的就是B+树）。
主键的名称以pk_开头，唯一键以uni_或者uk_开头，普通索引以idx_开头，一律使用小写格式，以字段的名称或缩写作为后缀。
多单词组成的columnname，区前几个单词首字母，加末单词组成column_name。如：sample表member_id上的索引：idx_sample_mid
单个表上的索引个数不能超过6个。
在建议索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面
在多表JOIN的SQL里，保证被驱动表的连续列上有索引，这样JOIN执行效率最高
建表或加索引时，保证表里互相不存在冗余索引

7、PowerDesigner工具的使用

具体内容下载工具PowerDesigner，在MySQL高级篇P158中有讲解

第十二章、数据库其他调优策略

1、数据库调优的措施

1.1调优的目标

尽可能节省系统资源，以便系统可以提供更大符合的服务。（吞吐量更大）
合理的结构设计和参数调整，以提高用户操作响应的速度。（响应速度更快）
减少系统的瓶颈，提高MySQL数据库整体的性能。

1.2如何定位调优问题

不过随着用户量的不断增加，以及应用程序复杂度的提升，我们很难用“更快”去定义数据库调优的目标，因为用户在不同时间段访问服务器遇到的瓶颈不同，比如双十一促销的时候会带来大规模的并发访问；还有用户在进行不同业务操作的时候，数据库的事务处理和SQL查询都会有所不同。因此我们还需要更加精细的定位，去确定调优的目标。

如何确定呢？一般情况下，有如下几种方式：

用户的反馈（主要）

用户是我们的服务对象，因此他们的反馈是最直接的。虽然他们不会直接提出技术建议，但是有些问题往往是用户第一时间发现的。我们要重视用户的反馈，找到和数据相关的问题。

日志分析（主要）

我们可以通过查看数据库日志和操作系统等方式找出异常情况，通过它们来定位遇到的问题。
服务器资源使用监控

通过监控服务器的CPU、内存、IO等使用情况，可以实时了解服务器的性能，与历史情况进行对比。
数据库内部状况监控

在数据库的监控中，活动会话监控是一个重要的指标。通过它，你可以清楚地了解数据库当前是否处于非常繁忙的状态，是否存在SQL堆积等。
其他

除了活动会话监控之外，我们也可以对事物、锁等待等进行监控，这些都可以帮助我们对数据库的运行状态有更全面的认识。

2、优化MySQL服务器

优化MySQL服务器主要从两个方面来优化，一方面是对硬件进行优化；另一方面是对MySQL服务的参数进行优化。这部分的内容需要较全面的知识，一般只有专业的数据库管理员才能进行这一类的优化。对于可以定制参数的操作系统，也可以针对MySQL进行操作系统优化。

2.1优化服务器硬件

服务器的硬件性能直接决定着MySQL数据库的性能。硬件的性能瓶颈直接决定MySQL数据库的运行速度和效率。针对性能瓶颈提高硬件配置，可以提高MySQL数据库查询、更新的速度。

配置较大的内存。足够大的内存是提高MySQL数据库性能的方法之一。内存的速度比磁盘IO快的多，可以通过增加系统的缓冲区容量使数据在内存中停留的时间更长，以减少磁盘IO。
配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。磁盘的IO能力，也就是它的寻道能力，目前的SCSI高速旋转的是7200转/分钟，这样的速度，一旦访问的用户量上去，磁盘的压力就会过大，如果是每天的网站pv（page view）在150w，这样的一般配置就无法满足这样的需求。现在SSD盛行，在SSD上随机访问和顺序访问性能几乎差不多，使用SSD可以减少随机IO带来得性能损耗。
合理分布磁盘IO，把磁盘IO分散在多个设备上，以减少资源竞争，提高并行操作能力。
配置多处理器，MySQL是多线程的数据库，多处理器可以同时执行多个线程。

2.2优化MySQL的参数

具体参数略

3、优化数据库结构

3.1拆分表：冷热数据分离

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

MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节。表越宽，把表装载进内存缓冲池所占用的内存也就越大，也会消耗更多的IO。冷热数据分离的目的是：①减少磁盘IO，保证热数据的内存缓存命中率②更有效的利用缓存，避免读入无用的冷数据。

3.2增加中间表

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

首先，分析经常联合查询表中的字段；然后，使用这些字段建立一个中间表，并将原来联合查询的表的数据插入中间表中；最后使用中间表来进行查询。

3.3增加冗余字段

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

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

也就是反范式化。

3.4优化数据类型

改进表的设计时，可以考虑优化字段的数据类型，这个问题在大家刚开始从事开发时基本不算是问题。但是随着你经验越来越丰富，参与的项目越来越大，数据量也越来越多时，你就不能只从系统稳定性的角度来思考问题了，还要考虑到系统整体的稳定性和效率。此时，优先选择符合存储需要的最小的数据类型。

列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引结点的数量也就越少，在遍历时所需要的IO次数也就越多，索引的性能也就越差。

3.5使用非空字段

3.6小结

上述这些方法都是有利有弊的，比如：

修改数据类型，节省存储空间的同时，你要考虑到数据不能超过取值范围
增加冗余字段的时候，不要忘了确保数据一致性
把大表拆分，也意味着你的查询会增加新的连接，从而增加额外的开销和运维的成本

因此，你一定要结合实际的业务需求进行权衡。

4、大表优化

4.1限制查询的范围

禁止不带任何限制数据范围的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围；

4.2读/写分离

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

一主一从模式：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-G2OFq1Ml-1683812940544)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221022172355425.png)]
双主双从模式：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-t7nbmzrE-1683812940544)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221022172605452.png)]

4.3垂直拆分

当数据量级达到千万级以上时，有时候我们就需要把一个数据库切分成多份，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MVUHDC7F-1683812940545)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221022173026111.png)]

如果数据库中的数据过多，可以采用垂直分库的方式，将关联的数据表部署在同一个数据库上。
如果数据表的列过多，可以采用垂直分表的方式，将一张数据表分拆成多张数据表，把经常一起使用的列放到同一张表里

第十三章、事务基础知识

1、数据库事务概述

事务是数据库区别于文件系统的重要特性之一，当我们有了事务就会让数据库始终保持一致性，同时我们还能通过事务的机制恢复到某个时间点，这样可以保证已提交到数据库的修改不会因为系统崩溃而丢失。

目前MySQL存储引擎中，只有InnoDB支持事务。

1.1基本概念

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

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

1.2事务的ACID特性

原子性（atomicity）

	原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。即要么转账成功，要么转账失败，是不存在中间状态的。

一致性（consistency）

	根据定义，一致性是指事务执行前后，数据从一个合法性状态变换到另一个合法性状态，这种状态是语义上的而不是语法上的，跟具体的业务有关。
	那什么是合法的数据状态呢？满足预定的约束的状态就叫做合法的状态。通俗一点，这状态是由你来定义的。满足这个状态，数据就是一致的，不满足这个状态，数据就是不一致的！如果事务中的某个操作失败了，系统就会自动撤销当前正在执行的事务，返回到事务操作之前的状态。

隔离性(isolation)

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

持久性

	持久性是指一个事务一旦提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。
	持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志。当我们通过事务对数据进行修改的时候，首先会对数据库的变化信息记录到日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

总结：

ACID是事务的四大特性，在这四个特性中，原子性是基础，隔离性是手段，一致性是约束条件，而持久性是我们的目的。

数据库事务，其实就是数据库设计者为了方便起见，把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称为一个事务。

1.3事务的状态

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3UHgDvpk-1683812940545)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221023153614048.png)]

2、如何使用事务

2.1显示事务

步骤1：start transaction 或者 begin，作用是显示开启一个事务。

mysql> begin;
mysql> start transaction;

start transaction语句相较于begin特别之处在于，后边可以跟随几个修饰符：

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

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

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

③with consistent snapshot：启动一致性读。

如果我们不显示指定事务的访问方式，那么该事务的访问模式就是读写状态

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

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

#提交事务，当提交事务后，对数据库的修改是永久性的
mysq> commit;
#回滚事务。即撤销正在进行的所有没有提交的修改
mysql> rollback;
#将事务回滚到某个保存点
mysql> rollabck to [savepoint]

其中关于savepoint的相关操作：

#在事务中创建保存点，方便后续针对保存点进行回滚，一个事务可以存在多个保存点
savepoint 保存点名称

#删除某个保存点
release savepoint 保存点名称

2.2关于completion_type参数的作用

completion=0,这是默认情况。当我们执行commit的时候会提交事务，在执行下一个事务时，还需要使用start transaction或者begin来开启。
completion=1，这种情况下，当我们提交事务后，相当于执行了commit and chain，也就是开启一个链式事务，即当我们提交事务之后会开启一个相同隔离级别的事务。
completion=2，这种情况下commit=commit and realease，也就是当我们提交后，会自动与服务器断开连接。

3、事务的隔离级别

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

3.1数据并发问题

1.脏写（Dirty Write）

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ELsxyfPe-1683812940545)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221023172952538.png)]

2.脏读（Dirty Read）

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RgInY6YC-1683812940546)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221023173238151.png)]

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

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

4.幻读（Phantom）

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

3.2 SQL中的四种隔离级别

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

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

3.3 MySQL隔离级别的查看和设置

查看事务隔离级别：

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

修改事务的隔离级别

set [global | session] transaction_isolation = 隔离级别
#其中，隔离级别格式：
READ-UNCOMMITED
READ-COMMITED
REPEATABLE-READ
SERIALIZABLE

关于设置时使用global或session的影响：
- 使用global关键字（在全局范围影响）
  - 当前已经存在的会话无效
  - 只对执行完该语句之后产生的会话起作用
- 使用session关键字
  - 对当前会话的所有后续的事务有效
  - 如果在事务之间开启，则对后续事务有效
  - 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务

第十四章、MySQL事务日志

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

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

有的DBA获取会认为UNDO是REDO的逆过程，其实不然。READ和UNDO都可以视为是一种恢复操作。但是：

redo log：是存储引擎层生成的日志，记录的是“物理级别”上的页修改操作，比如页号XXX、位移量YYY，写入了ZZZ数据。主要是为了保证数据的可靠性
undo log：是存储引擎层生成的日志，记录的是逻辑操作日志，比如读某一行数据进行了insert语句操作，那么undo log就记录与之相反的delete操作。主要用于事务的回滚（undo log记录的是每个修改操作的逆操作）和一致性非锁定读（undo log回滚行记录到某种特定的版本—MVCC，即多版本并发控制）。

1、redo日志

1.1为什么需要REDO日志？

一方面，缓冲池可以帮助我们消除CPU和磁盘之间的鸿沟，checkpoint机制可以保证数据的最终落盘，然而由于checkpoint并不是每次变更的时候就触发的，而是master线程隔一段时间去处理的。所以最坏的情况就是事务提交后，刚写完缓冲池，数据库宕机了，那么这段数据就是丢失的，无法恢复。

另一方面，事务包含持久性的特性，就是说对于一个已经提交的事务，在事务提交后即使系统发生了崩溃，这个事务对数据库中所做的更改也不能丢失。

那么如何保证持久性呢？一个简单的做法：在事务提交完成之前就把该事务所修改的所有页面都刷新到磁盘，但是这个简单粗暴的做法有些问题：

修改量与刷新磁盘工作量严重不成比例
随机IO刷新较慢

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

1.2REDO日志的好处、特点

1.好处

redo日志降低了刷盘频率
redo日志占用的空间非常小

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

2.特点

redo日志是顺序写入磁盘的

在执行日志的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序IO，效率比随机IO快

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

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

1.3redo的组成

REDO log可以简单分为以下两个部分：

重做日志的缓冲（redo log buffer），保存在内存中，是易失的。

在服务器启动时就向操作系统申请了一大片称为redo log buffer的连续内存空间，翻译成中文就是redo日志缓冲区。这片内存空间被划分为若干个连续的redo log block，一个redo log block占用512字节大小。

参数设置：innodb_log_buffer_size：

redo log buffer大小，默认16M，最大是4096M，最小是1M。

重做日志文件（redo log file），保存在硬盘中，是持久的。

1.4 redo的整体流程

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M65gGf2j-1683812940546)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221024171857405.png)]

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

Write-Ahead Log(预先日志持久化)：在持久化一个数据页之前，先将内存中的响应的日志页持久化

1.5redo log的刷盘策略

注意，redo log buffer刷盘到redo log file的过程并不是真正的刷新到磁盘中去，只是刷入到文件系统缓存（page cache）中去（这是现代操作系统为了提高文件写入效率做的一个优化），真正的写入会交给系统自己来决定（比如page cache足够打了）。那么对于InnoDB来说就存在一个问题，如果交给系统来同步，同样如果系统宕机，那么数据也丢失了（虽然整个系统宕机的概率还是比较小的）。

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

设置为0：表示每次事务提交时不进行刷盘操作。（系统默认master thread 每隔一秒进行一次重做日志的同步）
设置为1：表示每次事务提交时都将进行同步，刷盘操作（默认值）
设置为2：表示每次事务提交时都只把redo log buffer内容写入page cache，不进行同步，有os自己决定什么时候同步到磁盘文件。

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

另外，InnoDB存储引擎有一个后台线程，每隔一秒，就会把redo log buffer中的内容写入文件系统缓存（page cache），然后调用刷盘操作。

除了后台线程每秒一次的轮询操作，还用一种情况，当redo log buffer占用的空间即将达到innodb_log_buffer_size（16M）的一半的时候，后台线程会主动刷盘。

小结：innodb_flush_log_at_trxcommit=1

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

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

建议使用默认值，虽然操作系统宕机的概率理论概率小于数据库宕机的概率，但是一般既然使用了事务，那么数据的安全性相对来说更重要些。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NguYRveQ-1683812940547)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221024174519346.png)]

小结：innodb_flush_at_trx_commit=0

为0时，master thread中每1秒进行一次重做日志的fsync操作，因此实例crach最多丢失1秒内的事务。

（master thread是负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性的）

数据0的话，是一种折中的做法，它的IO效率理论是高于1的，低于2的。这种策略也有丢失数据的风险，也无法保证D。

在视频P170里面

2、undo日志

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

2.1 如何理解Undo 日志

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

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

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

每当我们要对一条记录做改动时（如UPDATE INSERT DELETE）,都需要留一手，把回滚时的所需要的东西记录下来。

MySQL把这些为了回滚而记录的这些内容称之为撤销日志或者回滚日志（即 undo log）。注意，由于查询曹邹（select）并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的undo日志。

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

2.2Undo 日志的作用

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

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

作用2：MVCC

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

2.3undo的存储结构

回滚段与undo页

InnoDB对undo log的管理采用段的方式，也就是回滚段（rollback segment）。每个回滚段记录了1024个undo log segment，而在每个undo log segment段中进行undo页的申请。
- 在InnoDB1.1版本之前（不包括1.1版本），只有一个rollback segment，因此支持同时在线的事务限制为1024。虽然对绝大多数的应用来说都已经够用了。
- 从1.1版本开始InnoDB支持最大128个rollback segment，故其支持同时在线的事务限制提高到了128 * 1024
当事务提交时，InnoDB存储引擎会做一下两件事情：
- 将undo log放入列表中，以供之后的purge操作
- 判断undo log所在的页是否可以重用，若可以分配给下个事务使用

undo的类型

insert undo log

指的是在insert操作中产生的undo log。因为insert操作的记录，只对事务本身可见，对其他事务不可见（这是事务隔离性的要求），故该undo log可以在事务提交后直接删除。不需要进行purge操作。

update undo log

指的是delete和update操作产生的undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最好的删除。

2.4 小结

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eQPChB8f-1683812940547)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221025161109159.png)]

undo log是逻辑日志，对事务回滚时，只是将数据库逻辑地恢复到原来的样子。

redo log是物理日志，记录的是数据页的物理变化，undo log不是redolog的逆过程。

第十五章、锁

1、概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在程序开发中会存在多线程同步的问题，当多个线程并发访问某个数据的时候，尤其是针对一些敏感数据，我们就需要保证这个数据在任何时刻最多只有一个线程在访问，保证数据的完整性和一致性。在开发过程中加锁是为了保证数据的一致性，这个思想在数据库领域中同样也很重要。

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

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

2.1读-读情况

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

2.2写-写情况

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

再这种情况会发生脏写的问题，任何一种隔离界别都不允许这种问题的发生。所以在多个未提交事务相继对一条记录改动时，需要让他们排队执行，这个排队的过程其实是通过锁来实现的。这个所谓的锁其实是一个内存中的结构，在事务执行前本来是没有锁的。也就是说一开始是没有锁结构和记录进行关联的。

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

在锁结构中有两个比较重要的属性：

trx信息：代表这个锁结构是哪个事务生成的
is_waiting：代表当前事务是否在等待

当事务T1改动了这条记录后，就生成了一个锁结构与该记录关联，因为之前没有别的事务为这条记录加锁，所以is_waiting属性就是false，我们把这个场景就称之为获取锁成功，或者加锁成功，然后就可以继续执行操作了。

在事务T1提交之前，另一个事务T2也想对该记录做改动，那么先看看有没有锁结构与这条记录关联，发现有一个锁结构与之关联后，然后也生成了一个锁结构与这条记录关联，不过锁结构的is_waiting属性值为true，表示当前事务需要等待，我们把这个场景称之为获取锁失败，或者加锁失败。

2.3 读-写或写-读情况

读-写或写-读，即一个事务进行读取操作，另一个事务进行改动操作。这种情况下可能发生脏读（读到还没有提交的数据）、不可重复读、幻读的问题。

各个数据库厂商对SQL标准的支持都可能不一样，比如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、锁的不同角度分类

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7XNBnUAD-1683812940547)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221025171657106.png)]

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

对数据库中并发事务的读读情况并不会引起什么问题。对于写写、读写或写读这些情况可能会引起一些问题，需要使用MVCC或者加锁的方式来解决它们。在使用加锁的方式解决问题时，由于既要允许读读情况不受影响，又要使写写、读写或写读情况中的操作相互阻塞，所以MySQL实现一个由两种类型的锁组成的锁系统来解决。这两种类型的锁通常称为共享锁（Shared Lock， S Lock）和 排他锁（Exclusive Lock， X Lock），也叫读锁（readlock）和写锁（write lock）

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

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

锁定读

对读取的记录加S锁：

select ... lock in share mode;
或者
select ... for share;(8.0新写法)

对读取的记录加X锁
```
select ... for update;
```

写操作
- delete：
  
  对一条记录做delete操作的过程其实是先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，再执行delete mark操作。我们把可以把这个定位待删除记录在B+树中位置的过程看成是一个获取X锁的锁定读。
- update：在对一条记录做update操作分为三种情况：
  - 情况1：未修改该记录的键值，并且被更新的列占用的存储空间在修改前后未发生变化
    
    则先在B+树中定位到这条记录的位置，然后再获取一下记录的X锁，最后在原记录的位置进行修改操作。我们也可以把这个定位待修改记录在B+树中的位置的过程看成是一个获取X锁的锁定读。
  - 情况2：未修改该记录的键值，并且至少有一个被更新的列占用的存储空间在修改前后发生变化。
    
    则先在B+树中定位到这条记录的位置，然后获取一些记录的X锁，将该记录彻底删除掉，最后再插入一条新的记录。这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读，新插入的记录由insert操作提供的隐式锁进行保护。
  - 情况3：修改了该记录的键值，则相当于在原有记录上做delete操作之后再来一次insert操作，加锁操作就需要按照delete和insert的规则进行了。
- insert:
  
  一般情况下，新插入一条记录的操作并不加锁，通过一种称之为隐式锁的结构来保护这条新插入的记录在本事务提交之前不被其他事务访问。

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

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很消耗资源的事情。因此数据库系统需要在高并发和系统性能两方面进行平衡，这样就产生了锁粒度（Lock granularity）的概念。

对一条记录加锁影响的也只是这条记录而已，我们就说这个锁的粒度比较细；其实一个事务也可以在表级别进行加锁，自然就被称之为表级锁或者表锁，对一个表加锁影响整个表中的记录，我们就说这个锁的粒度比较粗。锁的粒度主要分为表级锁、页级锁和行锁。

表锁

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

3.3表级锁

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层使用一种称之为元数据锁结构实现的

一般情况下，不会使用InnoDB存储引擎提供的表级别的S锁和X锁。只会在一些特殊情况下，比方说崩溃恢复过程中用到。

手动设置S锁或者X锁

Lock tables t read:加表级别的S锁
Lock tables t write:加表级别的X锁

总结：

MyISAM在执行查询语句前，会给涉及的所有表加读锁，在执行增删改操作前，会给涉及的表加写锁。

InnoDB存储引擎是不会为这个表添加表级别的读锁或者写锁的。

MySQL的表级锁有两种模式：

表共享读锁
表独占写锁

锁类型	自己可读	自己可写	自己可操作其他表	他人可读	他人可写
读锁	是	否	否	是	否，等
写锁	是	是	否	否，等	否，等

2.意向锁

概念：如果我们给某一行数据加上了排他锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排他锁了，这样效率更高
InnoDB支持多粒度锁，它允许行级锁和表级锁共存，而意向锁就是其中的一种表锁。
1. 意向锁的存在是为了协调行锁和表锁的关系，支持多粒度的锁并存
2. 意向锁是一种不与行级锁冲突表级锁，这一点很重要。
  1. 表明“某个事务正在某些行持有了锁或该事务准备去持有锁”

意向共享锁：

select column from table ... lock in share mode;

意向排他锁

select column from table ... for update;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RubLdNXS-1683812940547)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221026153604744.png)]

小结：
- InnoDB支持多粒度锁，特定场景下，行级锁可以与表级锁共存
- 意向锁之间互不排斥，但除了IS和S兼容外，意向锁会与共享锁/排他锁互斥
- IX、IS是表级锁，不会与行级的X，S锁发生冲突。只会与表级的X和S发生冲突
- 意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求

3.自增锁

略

4.元数据锁（meta data lock）MDL

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

MDL加锁过程是系统自动控制的，无需显示调用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML和DDL冲突，保证读写的一致性。

因此，当对一个表做增删改查的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性，解决了DML和DDL操作之间的一致性问题。不需要显示使用，在访问一个表的时候会被自动加上。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XnftYmCu-1683812940548)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221129223943725.png)]

3.4行级锁

1.记录锁（Record Locks）

行锁（Row Lock）也称为记录锁，顾名思义，就是锁住某一行。需要注意的是，MySQL服务层并没有实行行锁机制，行级锁只在存储引擎层实现。

优点：锁定力度小，发生锁冲突概率低，可以实现的并发度高

缺点：对于锁的开销比较大，加锁会比较慢，容易出现死锁情况

InnoDB与MyISAM最大的不同是：一是支持事务，二是支持行级锁

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

当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁
当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取记录的S型记录锁，也不可以继续获取X型记录锁
针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将自动优化为行锁
InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁

2.间隙锁

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

gap锁的提出仅仅是为了防止插入幻影记录而提出的。虽然有共享gap锁和独占gap锁这样的说法，但是它们起到的作用是相同的。而且如果对一条记录加了gap锁（不论是共享gap锁还是独占gap锁），并不会限制其他事务对这条记录加记录锁或者继续加gap锁

注意，给一条记录加了gap锁只是不允许其他事务往这条记录前边的间隙插入新纪录，那对于最后一条记录之后的间隙，也就是student表中id值为20的记录之后的间隙该怎么办？也就是说哪条记录加gap锁才能阻止其他事务插入id值在（20,正无穷）这个区间的新纪录？这时候数据页的两条伪记录派上用场了：

Infimum记录，表示该页面中最小的记录
Supremum记录，表示该页面中最大的记录

为了实现阻止其他事务插入id值在（20，正无穷）这个区间的新纪录，我们可以给索引中的最后一条记录，也就是id值为20的那条记录所在的页面的supremum记录加上一个gap锁

行级锁容易出现死锁的情况

3.临键锁

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

next-key锁本身就是一个记录锁和一个gap锁的合体，它既能保护该条记录，又能阻止其他事务将新纪录插入被保护记录前边的间隙

begin;
#会锁住(5,10]之间的记录
select * from student where id > 5 and id <= 10 for update;
commit;

4.插入意向锁（Insert Intention Locks）

当向间隙中插入一条新纪录时，会发生阻塞，这时候也会产生一种锁结构，这就是插入意向锁，如果插入的数据行之间并不冲突，那么两个事务之间也不会产生冲突，例如一个往id=3插入一条行记录，另一个往id=4插入一条行记录，那么两者是不会冲突的。

3.5页锁

页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁粒度介于表锁和行锁之间，并发度一般。

3.6从对锁的态度划分：乐观锁、悲观锁

从对待锁的态度来看锁的话，可以将锁分为乐观锁和悲观锁，从名字也可以看出来这两种锁是看待数据并发的思维方式。需要注意的是，乐观锁和悲观锁并不是锁，而是锁的设计思想。

1.悲观锁

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

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

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

悲观锁不使用的场景比较多，它存在一些不足，因为悲观锁大多数情况下依靠数据库的锁机制来实现，以保证程序的并发访问性，同时这样对数据库性能开销影响很大，特别是长事务而言，这样的开销往往无法承受，这时就需要乐观锁。

2.乐观锁

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

1.乐观锁的版本号机制

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

这种方式类似我们熟悉的SVN、CVS版本管理系统，当我们修改了代码进行修改时，首先会检查当前版本号与服务器上的版本号是否一致，如果一致就直接提交，如果不一致就需要更新服务器上的新代码，然后再进行提交。

2.乐观锁的时间戳机制

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

你能看到乐观锁就是程序员自己控制数据并发操作的权限，基本是通过给数据行增加一个时间戳（版本号或时间戳），从而证明当前拿到的数据是否最新。

3.两种锁的适用场景

从这两种锁的设计思想中，我们总结一下乐观锁和悲观锁的适用场景：

乐观锁适用于读操作较多的场景，相对来说写的操作比较少。它的优点在于程序实现、不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
悲观锁适合写操作多的场景，因为写的操作具有排他性。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止读-写和写-写的冲突。

3.7从加锁方式划分：隐式锁和显式锁

1. 隐式锁

隐式锁在实际运用中出现较少

2.显示锁

通过特定的语句进行加锁，我们一般称之为显示加锁，例如：

显示加共享锁：

select ... lock in share mode

显示加排他锁：

select ... for update

3.8全局锁

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

全局锁的命令：

Flush tables with read lock;

3.8其他锁之：死锁

1.概念

两个事务都要持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁

	事务1	事务2
1	begin; update account set money = 100 where id = 1	begin;
2		update account set money = 100 where id = 2
3	update account set money = 200where id = 2
4		update account set money = 200where id = 1

2.产生死锁的必要条件

两个或两个以上的事务
每个事务都已经持有锁并且申请新的锁
锁资源同时只能被同一个事务持有或者不兼容
事务之间因为持有锁和申请锁导致彼此循环等待

死锁的关键在于：两个或以上的session加锁的顺序不一致。

3.如何处理死锁

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

即当两个事务互相等待的时候，当一个事务等待时间超过设置的阈值时，就将其回滚，另外事务继续进行。这种方法简单有效，在InnoDB中，参数innodb_lock_wait_timeout用来设置超时时间

缺点：对于在线服务来说，这个等待时间往往是无法接受的

那就将此值修改短一些，比如1s是否合适？不合适，容易误伤到普通的锁等待

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

方式1检测死锁太多被动，InnoDB还提供了wait_for_graph算法用来主动进行死锁检测，每当加锁请求无法立即满足需求并进入等待时，wait_for_graph算法都会被触发。

这是一种较为主动的死锁检测机制，要求数据库保存锁的信息链表和事务等待链表两部分信息。

死锁检测的原理就是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环、存在即有死锁

一旦检测到回路、有死锁这时候InnoDB存储引擎会选择回滚undo量最小的事务，让其他事务继续执行（innodb_deadlock_detect=on表示开启这个逻辑）

如何解决？

方式一：关闭死锁检测，但意味着可能会出现大量的超时，会导致业务有损
方式二：控制并发访问量，比如在中间件中实现对于相同行的更新，在进入引擎之前排队，这样在InnoDB内部就不会有大量死锁检测工作

4.如何避免死锁

合理设计索引，使业务SQL尽可能通过索引定位更少的行，减少锁竞争
调整业务逻辑SQL执行顺序，避免update/delete长时间持有锁的SQL在事务前面
避免大事务，尽量将大事务拆分为多个小事务来处理，小事务缩短锁定资源时间，发生锁冲突的几率也更小
在并发比较高的系统中，不要显示加锁，特别是在事务里显示加锁。
降低隔离级别。如果业务允许，将隔离级别条低也是比较好的选择，比如将隔离级别从RR调整到RC，可以避免掉很多因为gap锁造成的死锁。

4、锁的内存结构

我们前面说一条记录加锁的本质就是在内存中创建一个锁结构与之关联，那么是不是一个事务对多条记录加锁，就要创建多个锁结构呢？比如：

#事务1
select * from user lock in share mode;

理论上创建多个锁结构没问题，但是如果一个事务获取10000条记录的锁，生成10000个锁结构页太崩溃了！所以决定对不同记录加锁时，如果符合下面这写条件的记录会放到一个锁结构中。

在同一个事务中进行加锁操作
被加锁的记录在同一个页面中
加锁的类型是一样的
等待状态是一样的

InnoDB存储引擎中的锁结构如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NDJeP1Qp-1683812940548)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221028215222064.png)]

5、锁监控

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

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |当前正在等待锁定的数量
| Innodb_row_lock_time          | 0     |从系统启动到现在锁定总时间长度（等待总时长）
| Innodb_row_lock_time_avg      | 0     |每次等待花费平均时间（等待平均时长）
| Innodb_row_lock_time_max      | 0     |从系统启动到现在等待最长的一次所花的时间
| Innodb_row_lock_waits         | 0     |系统启动后到现在总共等待的次数（等待的总次数）
+-------------------------------+-------+
5 rows in set (0.00 sec)

第十六章、多版本并发控制

1、什么是MVCC

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

MVCC没有正式的标准，在不同的DBMS中的MVCC实现方式可能是不同的，也不是普遍的，这里讲的是InnoDB中的MVCC实现机制

2、快照读和当前读

MVCC在MySQL InnoDB中的实现主要就是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读 ，而这个读指的就是快照读，而非当前读。当前读实际上是一种加锁的操作，是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式。

2.1快照读

快照读又叫做一致性读，读取的是快照数据。不加锁的简单select都属于快照读，即不加锁的非阻塞读；比如

select * from player where ...

之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于MVCC，它在很多情况下，避免了加锁操作，降低了开销

既然是基于多版本，那么快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

快照读的前提是隔离级别不是串行化，串行化级别下的快照会退化为当前读。

2.2当前读

当前读读取的是记录的最新版本（最新数据，而不是历史版本的数据），读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加锁的select，或者对数据进行增删改查都会进行当前读。比如：

select * from user lock in share mode; #共享锁
select * from user for update; #排他锁
insert into user values(...); #排他锁
delete from user where ...; #排他锁
update user set ...; #排他锁

3、隐藏字段、Undo Log版本链

在MySQL中，默认的隔离级别是可重复读，可以解决脏读和不可重复读的问题，如果仅从定义的角度来看，它并不能解决幻读问题。如果我们想要解决幻读问题，就需要采用串行化的方式，也就是将隔离级别提高到最高，但这样一来就会大幅度降低数据库的事务并发能力。

MVCC可以不采用锁机制，而是采用乐观锁的方式来解决不可重复读和幻读问题！它可以在大多数情况下替代行级锁，降低系统的开销。

回顾一下Undo日志的版本链，对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列。

tx_id:每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列
roll_pointer:每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就像一个指针，可以通过它来找到该记录修改前的信息。

4、MVCC实现原理

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

4.1什么是ReadView

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

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

4.2思路

使用read uncommitted隔离级别的事务，由于可以读到未提交的事务修改过的记录，所以直接读取记录的最新版本就可以了。

使用serializable隔离级别的事务，InnoDB规定使用加锁的方式来访问记录

使用read committed和repeatable read隔离级别的事务，都必须保证读到已经提交了的事务修改过的记录。加入另一个事务已经修改了记录但是还没有提交，是不能直接读取最新版本的记录的，核心问题就是需要判断一下版本链中的哪个版本是当前事务可见的，这是ReadView要解决的问题。

这个ReadView中主要包含4个比较重要的内容，分别是：

creat_trx_id，创建这个ReadView的事务ID

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

trx_ids,表示在生成ReadView时当前系统中活跃的读写事务的事务id列表
up_limit_id,活跃的事务中最小的事务ID
low_limit_id,表示生成ReadView时系统中应该分配给下一个事务的id值。low_limit_id是系统最大的事务id值，这里需要注意的是系统中的事务id，需要区别于正在活跃的事务id。

注意：low_limit_id并不是trx_ids中的最大值，事务id是递增分配的。

4.3ReadView的规则

如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前是事务在访问它自己修改过的记录，所以该版本可以被当前是事务访问
如果被访问版本的trx_id属性值小于ReadView中的creator_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交了，所以当前该版本可以被当前事务访问
如果被访问版本的trx_id属性值大于或等于ReadView中的low_limit_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问
如果被访问版本的trx_id属性值在ReadView的up_limit_id和low_limit_id之间，那就需要判断一下trx_id属性值是不是在trx_ids列表中。
- 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以访问
- 如果不在，说明创建ReadView时生成该版本的事务已经被提交了，该版本可以被访问。

4.4MVCC整体操作流程

了解了这些概念之后，我们来看一下当查询一条记录的时候，系统如何通过MVCC找到它

首先获取事务自己的版本号，也就是事务ID
获取ReadView
查询得到的数据，然后与ReadView中的事务版本号进行比较
如果不符合ReadView规则，就需要从Undo Log中获取历史快照
最后返回符合规则的数据

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

在隔离级别为读已提交时，一个事务中的每一次select查询都会重新获取一次Read View

5.总结

这里介绍了MVCC在read committed、repeatable read这两种隔离级别的事务在执行快照读操作时访问记录的版本控制链的过程。这样使不同事务的读-写、写-读操作并发执行，从而提高系统性能。

核心在于ReadView的原理，Read committed、repeatable read这两个隔离级别的一个很大不同就是生成ReadView的时机不同

Read committed在每一次进行普通select操作前都会生成一个ReadView
repeatable read只在第一次进行普通select 操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了

通过MVCC我们可以解决：

读写之间阻塞的问题。通过MVCC可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力
降低了死锁的概率。这是因为MVCC采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行
解决快照读的问题。但我们查询数据库在某个时间点的快照时，只能看到这个时间点之前的事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果

第十七章、其他数据库日志

1、MySQL支持的日志

1.1日志类型

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

这六类日志分别为：

慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。
通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。
二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复
中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。
数据定义语句日志：记录数据定义语句执行的元数据操作

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

1.2日志的弊端

日志功能会降低MySQL数据库的性能。
日志会占用大量的磁盘空间。对于用户量非常大、操作非常频繁的数据库，日志文件需要的存储空间设置比数据库文件需要的存储空间还要大。

2.慢查询日志

3.通用查询日志

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

3.2查看当前状态

mysql> show variables like 'general%';
+------------------+----------------------------------------------------------------+
| Variable_name    | Value                                                          |
+------------------+----------------------------------------------------------------+
| general_log      | OFF                                                            |
| general_log_file | D:\GoogleDownload\mysql-5.7.39-winx64\data\DESKTOP-VPJ7A44.log |
+------------------+----------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

3.3启动日志

方式一：永久性方式

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

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

方式二：临时性方式

set global general_log=on;

4.错误日志（error log）

错误日志记录了MySQL服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。

通过错误日志可以查看系统的运行状态，便于即时发现故障、修复故障。如果MySQL服务出现异常，错误日志是发现问题、解决故障的首选。

4.1启动日志

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

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

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

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

4.2查看日志

MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看

mysql> show variables like 'log_err%';
+---------------------+----------------------------------------------------------------+
| Variable_name       | Value                                                          |
+---------------------+----------------------------------------------------------------+
| log_error           | D:\GoogleDownload\mysql-5.7.39-winx64\data\DESKTOP-VPJ7A44.err |
| log_error_verbosity | 3                                                              |
+---------------------+----------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

5、二进制日志（bin log）

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

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

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

如果想要记录所有语句，需要使用通用查询日志

binlog主要应用场景：

一是用于数据恢复：如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
二是用于数据复制，由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的

可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性

5.1查看默认情况

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set, 1 warning (0.00 sec)

5.2日志参数设置

方式一：永久性方式

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

[mysqld]
log-bin=atguigu-bin
binlog_expire_logs_seconds=600
max_binlog_size=100M

5.3查看日志

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

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

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

show binary logs;

所有对数据库的修改都会记录在binlog中。但binlog是二进制文件，无法直接查看，想要更直观的观测它就要借助mysqlbinlog命令工具。

mysql> mysqlbinlog "/var/lib/mysql/atguigu~bin.000001"
#加上-v可以看到具体的查询语句
mysql> mysqlbinlog "/var/lib/mysql/atguigu~bin.000001"

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

mysql> show binlog events [in 'log_name'] [from pos] [limit [offset,] row_count];

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

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

除此之外，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使用日志恢复数据

如果MySQL服务器启用了二进制日志，在数据库出现意外丢失数据时，可以使用MySQLbinlog工具从指定的时间点开始直到现在或另一个指定的时间点的日志中恢复数据。

mysqlbinlog恢复数据的语法如下：

mysqlbinlog [option] filename | mysql -uuser -ppwd;

这个命令可以这样理解：使用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之前恢复

查看系统当前的所有binlog文件

show binary logs;

刷新日志文件

flush logs;

5.5删除二进制日志

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

1.purge master logs:删除指定日志文件

purge master logs语法：

purge {master | binary} logs to '指定日志文件名'
purge {master | binary} logs before '指定日期'

举例：使用purge master logs语句删除创建时间比binlog.000005早的所有日志

（1）多次重新启动MySQL服务，便于生成多个日志文件。然后用show语句显示二进制日志文件列表

show binary logs;

(2)执行purge master logs语句删除创建时间比binlog.000005早的所有日志

purge master logs to "binlog.000005";

(3)显示二进制日志文件列表

show binary logs;

比binlog.000005早的所有日志文件都已经被删除了。

2.reset master：删除所有的二进制文件

使用reset master 语句，清空所有的binlog日志。MySQL会重新创建二进制文件，新的日志文件扩展名将重新从000001开始编号，慎用！

举例：使用reset master语句删除所有日志文件

（1）重启MySQL服务若干次，执行show语句显示二进制日志文件列表

show binary logs;

(2)执行reset master语句，删除所有日志文件

reset master;

5.6其他场景

二进制日志可以通过数据库的全量备份和二进制日志中保存的增量信息，完成数据库的无损失恢复。但是，如果遇到数据量大、数据库和数据表很多的场景，是很有挑战性的，因此起止位置不容易管理。

在这种情况下，一个有效的解决方法就是配置主从数据库服务器 ，甚至是 一主多从的架构，把二进制日志文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常问题。

5.7binlog与redolog对比

redo log是物理日志，记录内容是“在某个数据页上做了什么修改”，属于InnoDB存储引擎产生的。
而binlog是逻辑日志，记录内容是语句的原始逻辑，类似于“给id=2这一行的c字段加1”，属于MySQLServer层
虽然他们都属于持久化的保证，但是侧重点不同
- redo log让InnoDB存储引擎拥有了崩溃恢复的能力
- binlog保证了MySQL集群架构的数据一致性

6、中继日志

6.1介绍

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

搭建号主从服务器之后，中继日志默认会保存在从服务器的数据目录下。

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

6.2查看中继日志

中继日志和二进制日志的格式相同，可以用mysqlbinlog工具进行查看。

6.3恢复的典型错误

如果从服务器宕机，有的时候为了系统恢复，要重装操作系统，这样就可能回导致你的服务器名称与之前的不同。而中继日志里是包含从服务器名的。在这种情况下，就可能导致你恢复从服务器的时候，无法从宕机的中继日志里读取数据，以为是日志文件损坏了，其实是名称不对了。

解决的方法也很简单，把服务器的名称改回之前的名称。

第十八章、主从复制

1、主从复制概述

1.1如何提升数据库并发能力

在实际工作中，我们常常将Redis作为缓存与MySQL配合来使用，当有请求的时候，首先会从缓存中进行查找，如果存在就直接取出。如果不存在再访问数据库，这样就提升了读取的效率，也减少了对后端数据库的访问压力。Redis的缓存架构是高并发架构中非常重要的一环。

此外，一般应用对于数据而言都是“读多写少”，也就是说数据库读取数据的压力比较大，有一个思路就是采用数据库集群的方案，做主从架构，进行读写分离，这样同样可以提升数据库的并发处理能力。但并不是所有的应用都需要对数据库进行主从架构的设置，毕竟设置架构本身就是有成本的。

如果我们的目的在于提升数据库高并发访问的效率，那么首先考虑的是如何优化SQL和索引，这种方式简单有效；其次才是采取缓存的策略，比如使用Redis将热点数据保存在内存数据库中，提升读取的效率；最后才是对数据库采用主从架构，进行读写分离。

按照上面的方式进行优化，使用和维护的成本是由低到高的。

1.2主从复制的作用

主从同步设计不仅可以提高数据库的吞吐量，还有一下三个方面的作用：

作用一：读写分离

作用二：数据备份：我们通过主从复制将主库上的数据复制到了从库上，相当于是一种热备份机制，也就是在主库正常运行的情况进行的备份，不会影响到服务。

作用三：具有高可用性：数据备份实际上就是一种冗余的机制，通过这种冗余的方式可以换取数据库的高可用性，也就是当服务器出现故障或宕机的情况下，可以切换到从服务器上，保证服务的正运行。

2、主从复制的原理

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

2.1原理剖析

三个线程

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eilfODmj-1683812940548)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221030121515253.png)]

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

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

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Sw2vn4uk-1683812940549)(C:\Users\12086\AppData\Roaming\Typora\typora-user-images\image-20221030121936573.png)]

注意：

不是所有版本的MySQL都默认开启服务器的二进制日志。在进行主从同步的时候，我们需要先检查服务器是否已经开启了二进制日志。

除非特殊指定，默认情况下从服务器会执行所有主服务器中保存的事件。也可以通过配置，使从服务器执行特定的事件。

复制三步骤

步骤1：Master将写操作记录到二进制日志（binlog）。这些记录叫做二进制日志事件

步骤2：Slave将Master的二进制日志事件拷贝到它的中继日志

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

2.2复制的基本原则

每个slave只有一个master
每个slave只能有一个唯一的服务器ID
每个Master可以有多个slave
qlbinlog恢复数据的语法如下：

mysqlbinlog [option] filename | mysql -uuser -ppwd;