MySQL 进阶笔记

😀😀😀创作不易，各位看官点赞收藏，在这里隆重感谢尚硅谷宋红康老师。，

MySQL 进阶笔记

1、Centos 环境安装 MySQL

安装前检查工作：

# 检查 MariaDB 是否存在，会和 MySQL 冲突
rpm -qa|grep mariadb
# 删除 MariaDB
rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64
rm /etc/my.cnf

# 检查电脑是否安装过 MySQL
rpm -qa | grep mysql
rpm -e --nodeps mysql-libs-5.1.73-5.el6_6.x86_64

# 查找相关文件，/ 表示从哪个目录下开始查找
find / -name 文件名称
1
2
3
4
5
6
7
8
9
10
11
12

下载安装包，并上传到服务器，压缩包大概400多MB，不要下错了

官网地址：https://downloads.mysql.com/archives/community/

安装命令

# 解压命令
tar -zxvf 压缩包

# 在/usr/local/mysql下创建一个data文件夹
mkdir -p /usr/local/server/mysql/data
1
2
3
4
5

创建MySQL用户组

#进入mysql目录
cd /usr/local/server/mysql
#添加用户组
groupadd mysql
# 添加用户，mysql组名，xxx用户名
useradd -g mysql mysql
# 修改mysql文件权限，xxxx用户名，mysql组名
chown -R mysql.mysql /usr/local/server/mysql
1
2
3
4
5
6
7
8

配置环境

# 创建配置文件
touch /etc/my.cnf

vim my.cnf
1
2
3
4

[client]
port=3306
# 集群需要唯一
server-id=1
socket=/tmp/mysql.sock
skip-locking

[mysqld]
# -------------------------- 文件参数 ------------------
# pid文件路径
pid-file=/usr/local/mysql/var/mysql.pid
# MySQL 安装路径
basedir=/usr/local/mysql
# MySQL 数据目录
datadir=/usr/local/mysql/data
# 默认表类型
default_table_type=InnoDB
# 服务器时区
default-time-zone=system
# server 级别字符集
character-set-server=utf8
# 默认存储引擎
default-storage-engine=InnoDB

# ------------------------ 服务器参数 ----------------
# 最大连接数，默认：200
max_connections=500
# 线程池缓存数，建议：内存 1G->8 2G-> 16 3G -> 32 3G以上 -> 64默认：20
thread_cache_size=64
# 等待超时时间，单位：秒，默认：10s
wait_timeout=10

# ----------------------- 调优参数 ------------------
# 缓存大小
key_buffer_size=256M
# 排序缓存大小
sort_buffer_size=6M
myisam_sort_buffer_size=64M
# 缓存大小
read_buffer_size=4M
read_rnd_buffer_size=16M
# 连表查询缓存参数
join_buffer_size=8M
query_cache_size=64M
tmp_table_size=256M

# ------------------- 日志参数 -----------------------
# 【慢查询日志】
# 是否开启慢查询，建议在优化时开启平时不开启，默认：不开启
# slow_query_log = 0
# 慢查询时间，默认：50s
# long-query-time = 50
# 慢查询日志保存路径
# slow_query_log_file=

# 【通用查询日志】
# 是否开启通用查询日志，默认不开启
# general_log=0
# 通用查询日志保存路径
# general_log_file=/usr/local/mysql/log/mysql.log 

# 【错误日志】
# 错误日志
log-error=/usr/local/mysql/log/error.log

# 【bin log】
# 是否开启bin log，默认：开启
sql_log_bin=1
# bin log日志文件前缀名
log-bin=binlog
# bin log过期时间，单位：秒，默认：2592000，30天
binlog_expire_logs_seconds=2592000
# 单个bin log大小，默认:1024M
max_binlog_size=1024M
# bin log刷盘策略，默认：1
sync_binlog=1


# 【redo log】
# redo log缓存大小，默认：16MB
innodb_log_buffer_size=16M
# redo log单个文件大小，默认：48M
innodb_log_file_size=48M
# redo log刷盘策略，默认：1
innodb_flush_log_at_trx_commit=1
# redo log保存路径
innodb_log_group_home_dir=/usr/local/mysql/log

# 【undo log】
#undo log保存路径
innodb_undo_directory=/usr/local/mysql/log


# ---------------- 主从配置 -------------
# 【主机】
#read-only=0 # 服务器是否只读，默认0：读写，1：只读
#binlog-ignore-db= # 不同步的数据库名
#binlog-do-db= # 需要同步的数据名，默认全部同步
#binlog_format= # binlog格式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99

初始化

mkdir /etc/my.cnf.d
# 进入到mysql安装目录下的bin文件夹下
./mysqld --defaults-file=/etc/my.cnf --initialize
# foCCn=7wTbn
1
2
3
4

加入系统服务

cp /opt/server/mysql/support-files/mysql.server /etc/init.d/mysql

# 启动mysql
systemctl start mysql
1
2
3
4

遇见错误：Failed to start LSB: start and stop MySQL

# 权限不住，需要指定的用户是mysql，并且在MySQL的安装目录下给mysql权限
chown -R mysql.mysql /usr/local/server/mysql

# 输入mysql命令不存在，可以是没有配置环境变量
vim /etc/profile
# 加入下面一句话
export PATH=$PATH:mysql的bin路径
# 刷新环境变量
source /etc/profile
1
2
3
4
5
6
7
8
9

修改 root 密码

# 修改密码命令
ALTER user 'root'@'localhost' IDENTIFIED BY '新密码';
#刷新权限
flush privileges;
1
2
3
4

配置 MySQL 远程登录：

# 使用这个数据库
use mysql;
select user,host,plugin,authentication_string from user;
# mysql8认证方式改了,mysql_native_password这个才能远程连接mysql
# localhost表示本地连接，%，所有ip都可以连接
update user set host='%' where user='root';
flush privileges;
1
2
3
4
5
6
7

2、MySQL常用设置

2.1、字符集设置

在 MySQL8.0 之前它默认的编码集是 Latinl ，如何去插入一些中文汉字就会出现乱码的请求，在8之前就需要去手动指定数据库的编码格式，一般是utf-8。在8之后它默认的编码集就是 utf8mb4，从而避免了乱码问题。

# 查看默认字符集
show variables like '%character%';
show variables like '%char%';

# 查看数据库支持字符集
show charset;
show character set;
1
2
3
4
5
6
7

• character_set_client：服务器解码请求时的字符集。
• character_set_connection：服务器请求时会把character_set_client的字符串用自己的字符集转换一下。
• character_set_results：服务器向客户端返回数据时的字符集。

# 修改数据库默认字符集，如果在创建数据库时没有指定字符集就会使用默认字符集
vim /etc/my.cnf
# 在配置文件中添加下面一句话，然后重启mysql，之前创建数据库的字符集不会改变，还是修改字符集之前的字符集。
character_set_server=xxx字符集
1
2
3
4

# 修改已有数据库字符集
alter database xxx character set '字符集';
# 修改已有表字符集
alter table xxx convert to character set '字符集';
1
2
3
4

MySQL 字符级别：

• 服务器级别：这个级别最高，如果没有设置其它字符集，就是用服务器级别。(character_set_server)
• 数据库级别：在创建数据库时可以指定数据库字符集，不指定用服务器级字符集。(character_set_database)
• 表级别：在创建数据库表时可以指定表字符集，不指定用数据库字符集。
• 列级别：在创建表字段时，可以在字段的后面指定字符集，不指定用表级字符集。

utf-8字符集：表示一个字符需要1~4个字节，但是日常的字符3个字节就可以，但是一些特殊字符需要4个字节，例如表情。

• utf8mb3：使用 1~3 个字节表示一个字符，这个就是 utf8。
• utf8mb4：使用 1~4 个字节表示一个字符。

字符比较规则：字符串排序时使用的一种规则。

# 查看某一个字符集的比较规则
show collation like '字符集'
# 修改数据库字符集 
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET '字符集' COLLATE '比较规则';
# 修改表的字符集
ALTER TABLE 表名 DEFAULT CHARACTER SET '字符集' COLLATE '比较规则';
1
2
3
4
5
6

utf8 常用比较规则：如果数据库中的数据存在德语、法语、俄语，一定要使用utf8_unicode_ci。

• utf8_unicode_ci：校准速度慢，准度高。
• utf8_general_ci：校准速度快，准度差。

2.2、SQL 大小写

Windows下对于一些表名、字段名不区分大小写，但是在 Linux 环境下是要区分大小写的，但是对于SQL中的关键字、函数都是不区分大小写。

# 查看是否区分大小写，1：不区分大小写，0：区分大小写
show variables like '%lower_case_table_name%';
1
2

Linux 下设置大小写规则：在配置文件中的 [mysqld] 中加入lower_case_table_names=1，然后重启服务器。

[mysqld]
# 这个设置方式只适用于 5.7，但是在8下设置重启 MySQL 服务会恢复默认的大小写配置。如果在8下修改的话，需要将数据库现有的数据库数据文件夹删除，然后重启服务，不建议修改这个参数。
lower_case_table_names=1
1
2
3

SQL编写建议：

• 关键字、函数名全部大写。
• 数据库名、表名、字段名、字段别名全部小写。
• SQL语句必须以分号 ; 结尾。

2.3、sql_mode 模式

sql_mode：会影响 MySQL 支持的SQL语法以及它执行的SQL校验，可以完成不同程度的数据校验，它有两种模式(宽松模式、严格模式)。

• 宽松模式：在写SQL语句时，即使发生了错误也会被接受，例如在插入一条数据，数据长度超过了字段指定的长度，在宽松模式下会截取对应长度保存下来，它不会报错。
• 严格模式：严格检查编写的SQL语句，也会对数据进行校验，如果发现错误就直接报错不会进行对应的操作。

# 查看当前sql_mode
# 当前会话
select @@session.sql_mode;
# 全局
select @@global.sql_mode;

# 设置 sql_mode 变量
set SESSION sql_mode = '值';
set GLOBAL sql_mode = '值';

# 永久设置方式：在配置文件中[mysqld]下面加一句话，然后重启服务
[mysqld]
sql_mode=值
# 宽松模式下的 sql_model 的值为空
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.4、MySQL 数据目录

MySQL默认数据库：

• mysql：核心数据库，存储了用户账户信息、权限信息、存储过程、事件定义信息、运行日志信息等。
• information_schema：存储 MySQL 中其它数据库信息，例如某个数据库的表、索引、视图、触发器等。
• sys：通过视图方式将information_schema和performance_schema结合起来，帮助管理员和开发人员监控数据库性能。
• performance_schema：保存 MySQL 服务器运行过程中一些状态信息，用来监控 MySQL 服务的各类性能指标。

MySQL 把表数据存放在文件系统中，就是在配置文件中指定路径，datadir=/usr/local/server/mysql/data。不同的存储引擎在存储时数据的表结构不一样，不同的版本数据存储结构也是不一样的。(可以通过命令：show variables like 'datadir';查看数据存储路径)

InnoDB 存储引擎：

• MySQL5.7：
  • db.opt：存放数据库信息，例如字符集、比较规则等。
  • .frm：存储 emp1 表的表结构，例如字段信息、类型、约束等。
  • .ibd：存储 emp1 表的数据。

• MySQL8.0：
  • .ibd：存储了 gen_table 表的所有数据，数据库信息、表结构、表数据都是存放在这里里面，相当于 5.7 版本 opt、frm、ibd 加起来。

MyISAM存储引擎：

• MySQL5.7：
  • db.opt：存放数据库信息，例如数据库字符集、比较规则等。
  • .frm：存储表结构信息，约束、字段。
  • .MYD：存储表数据。
  • .MYI：存储表索引信息。

• MySQL8.0：
  • .sdi：相当于 frm，存放数据库信息和表结构信息。
  • .MYD：存储表数据。
  • .MYI：存储表索引信息。

2.5、用户管理

MySQL 用户分为 root 和普通用户，root 是超级用户拥有所有的权限(用户创建、删除、修改等)。普通用户只能使用被赋予的权限。

# 登录MySQL，-h 数据库的地址(默认localhost)，-P 登录端口(默认3306)，-u 登录账户(默认root) -p 登录密码
mysql -h xxx.xxx.xxx.xx -P port -u xxx -p xxxx

# 创建用户
CREATE user '用户名'@'登录地址' identified by '登录密码';
# 例如，不指定登录地址默认是 %
CREATE user 'zhangsan'@'localhost' identified by 'zhangsan';

# 修改用户
UPDATE mysql.user SET user='新账号' WHERE user = '旧帐号';
# 例如
UPDATE mysql.user SET user='zhangsan' WHERE user = 'lisi';
# 修改mysql.user表后必须执行下面命令
FLUSH PRIVILEGES;

# 删除用户 
# 方式一：不指定连接地址默认是%（推荐方式）
DROP user '用户名'@'连接地址';
# 方式二：直接删除 mysql.user 表中的数据，这种方式可能在系统中有残留信息
DELETE FROM mysql.user WHERE user = '用户名';
FLUSH PRIVILEGES;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

修改密码：root 有很高的权限不仅可以修改自己密码，还可以修改其他人的密码。

# 修改自己登录密码，user()是获取当前用户
# 方式一
ALTER user user() identified by '新密码';
# 方式二
SET PASSWORD = '新密码';

# 修改其它用户密码，这个需要 root 权限才可以，不指定连接地址，默认 %
# 方式一
ALTER user '用户名'@'连接地址' identified by '新密码';
# 方式二
SET PASSWORD for '用户名'@'连接地址' = '新密码';
1
2
3
4
5
6
7
8
9
10
11

2.6、权限管理

MySQL 数据库中通过一些权限操作赋予某些用户可以在数据库进行得操作。MySQL 服务器通过权限表来控制用户对数据库的访问，权限表存放在 mysql 数据库中，在 MySQL 启动时，服务器将这些数据库表中权限信息内容读入内存中进行权限的控制。(user、db、tables_priv、column_priv、procs_priv)

# 查看数据库有哪些权限
show privileges;

# 查看当前用户权限
show grants;
# 查看某个用户权限
SHOW GRANTS FOR '用户名'@'连接地址';

# 赋予权限，如果不存在这个用户就会新建用户
GRANT 权限1,权限2...... ON 数据库名称.表名 TO '用户名'@'连接地址';
# 赋予所有数据库下所有表的所有权限，但是不能给其它用户赋予权限，如果需要给与权限分配，需要给 WITH GRANT OPTION 权限
GRANT all privileges on *.* TO TO '用户名'@'连接地址';

# 回收权限
REVOKE 权限1,权限2..... ON 数据库名称.表名 FROM '用户名'@'连接地址';
REVOKE all privileges on *.* FROM '用户名'@'连接地址';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2.7、角色管理

在 MySQL8.0 中引入角色管理，将对应权限封装到对应角色下，然后将角色赋予给对应的用户，这样用户就拥有了这个角色下的所有权限。(RBAC模型)

# 创建角色
CREATE ROLE '角色名称'@'连接地址';

# 给角色赋权
GRANT 权限1,权限2.... ON 数据库名.表名 TO '角色名'@'连接地址';
GRANT ALL PRIVILEGES ON *.* TO '角色名'@'连接地址';

# 查看角色权限
SHOW GRANTS FOR '角色名'@'连接地址';

# 回收权限
REVOKE 权限1,权限2..... ON 数据库名称.表名 FROM '角色名'@'连接地址';
REVOKE all privileges on *.* FROM '角色名'@'连接地址';

# 删除角色
DROP ROLE '角色名'@'连接地址';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 给某个用户激活角色
SET DEFAULT ROLE '角色名'@'连接地址' ALL TO '用户名'@'连接地址';

# 给用户赋予角色
GRANT '角色名'@'连接地址' TO '用户名'@'连接地址';

# 设置角色默认为激活状态
SET GLOBAL activate_all_roles_on_login = ON;

# 撤销用户角色
REVOKE '角色名'@'连接地址' FROM '用户名'@'连接地址';
1
2
3
4
5
6
7
8
9
10
11

注意：MySQL 中创建角色默认是没有被激活的，使用角色必须先要激活角色，除非在配置文件中设置角色默认都是激活。

设置默认角色：默认给每个创建的用户设置默认角色，强制角色无法被删除或者被回收。

# 方式一：在配置文件中添加默认角色
[mysqlId]
mandatory_roles='角色名@连接地址,角色名@连接地址...'

# 方式二：运行时设置变量
SET GLOBAL mandatory_roles = '角色名@连接地址,角色名@连接地址...';
1
2
3
4
5
6

3、MySQL 逻辑架构

MySQL 是经典的 C/S 架构，客户端进程想服务器进程发送命令，服务器进程处理后再向客户端发送处理结果。

连接层：客户端访问数据库服务器首先建立 TCP 连接，根据账号和密码做身份认证、权限获取。

• Connection Pool 连接池中的线程建立 TCP 连接并认证账户信息，每一个连接都是从线程池中分配一个线程进行交互。
• 账户认证通过后，从权限表查询出与账号相关联权限。

服务层：主要是对客户端请求进行处理并返回处理结果。

• SQL 接口：接收客户端的 SQL 命令并返回用户需要的查询结果。
• Parser 解析器：在解析器中对 SQL 语句进行语法分析、语义分析，将 SQL 语句分解成数据结构，创建 SQL 语句对应的语法树并将解析结果传递给后续步骤，如果在解析过程中出现错误说明这是一个错误的 SQL 语句。
• Optimizer 查询优化器：SQL 语句在语法解析之后，会根据查询优化器确定 SQL 语句的执行计划，这个执行计划表明应该使用那些索引、表与表之间怎样连接、如何调用存储引擎等等。
• Caches 查询缓存：在 MySQL8.0 之前有查询缓存，对于同一个 SQL 查询语句会以 SQL 语句为 key，查询结果为值缓存起来，下次查询直接返回处理结果，但是在 MySQL8.0 之后就删除了。

引擎层：真正负责 MySQL 中数据的存储与提取，对物理服务器级别维护的底层数据执行操作。

# 查看所有的存储引擎
show engines;
1
2

SQL 语句执行流程：

1. 客户端建立连接后，通过账户认证和权限认证，先根据 SQL 语句去查询缓存，但是缓存的命中率很低。必须对于一模一样的 SQL 语句才会命中缓存，还有可能出现缓存失效的情况。
2. 没有命中缓存就会将 SQL 进行解析器进行解析，包括词法分析与语义分析检查是否满足 MySQL 的语法。如果 SQL 语句没有错误就会生成一个语法树。
3. 然后在优化器中确定 SQL 的执行路径，称为执行计划。一条 SQL 与可能有多种执行方式，优化器作用就是找出最好的执行计划。
   • 物理查询优化，通过索引、表连接方式等技术进行优化。
   • 逻辑查询优化，通过 SQL 语句等价变换进行优化，用不同 SQL 完成相同的逻辑处理。
4. 然后通过执行器执行对应的计划，通过调用存储引擎 API 查询数据。

# 打开 SQL 执行流程记录设置，值为1就记录 SQL 执行流程，值为0则反之
SET @@SESSION.PROFILING = 1;

# 查询最近所有 SQL 语句的执行流程
SHOW PROFILES;

# 查看执行 SQL 语句的执行流程，id是执行SQL语句顺序的id
SHOW PROFILE FOR QUERY id;
# 查看最近一次的 SQL 执行流程
SHOW PROFILE;
1
2
3
4
5
6
7
8
9
10

数据库缓冲池：InnoDB 存储引擎中，以页为单位来存储数据的，MySQL 数据库会占用内存来作为数据缓冲池，查询数据相当于是在内存中进行操作，效率大大增加。在访问数据之前会将磁盘中页数据缓存到内存中的 Buffer Pool 中，然后从内存中获取数据，减少与磁盘的 I/O 时间。

• 缓冲池空间有限，它会优先缓冲使用频次高的数据。
• 缓冲池也会与缓冲目标数据周围的一些数据。
• 在更新操作时，也是先更新缓冲池中的数据，然后定期将缓冲池中的数据刷盘，这就存在数据一致性问题。

# MyISAM 存储引擎
# 查看缓冲池大小
SHOW VARIABLES LIKE '%key_buffer_size%';
# 如何设置缓冲区，在配置文件中修改对应大小，也可以直接设置变量
[sever]
key_buffer_size=值(单位字节)


# InnoDB 存储引擎
# 查看缓冲池大小
SHOW VARIABLES LIKE '%innodb_buffer_pool_size%';
# 如何设置缓冲区，在配置文件中修改对应大小，也可以直接设置变量，默认是 128MB
[sever]
innodb_buffer_pool_size=值(单位字节)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

缓冲池实例：对于多线程环境下，访问缓冲池都会加锁处理，对于高并发情况下可能会影响效率，所以可以将缓冲池拆分成几个小的缓冲池，他们独立去处理数据。多个缓冲池会平分设置的缓冲池大小，缓冲池设置小与 1GB，设置缓冲池个数无效。

# 配置文件设置缓冲池个数，默认是1个
[server]
innodb_buffer_pool_instances = 值
1
2
3

注意：对于更新数据操作，如果只是修改了缓冲池中的数据并没有将数据刷盘到磁盘，这时候 MySQL 宕机就会出现数据丢失，对于这种情况采用 Redo Log 和 Undo Logo 去解决。

4、存储引擎

存储引擎：表示表的类型，不同的存储引擎的表数据的存储方式不一样。功能就是接受上层传下来的指令，然后对表中数据进行提取或写入。

# 查看 MySQL 所有存储引擎，5.5 之前默认是MyISAM，5.5 之后默认使用的是 InnoDB 存储引擎
SHOW ENGINES;
# 查看默认存储引擎
SHOW VARIABLES LIKE '%default_storage_engine%';

# 修改默认的存储引擎，可以通过修改配置文件
[mysqld]
default_storage_engine=存储引擎

# 修改表的存储引擎
ALTER TABLE 表名 ENGINE = 存储引擎;
1
2
3
4
5
6
7
8
9
10
11

注意：可以在创建表时指定表使用的存储引擎，如果不指定存储引擎，那么就会使用默认的存储引擎。

InnoDB 存储引擎：具备外键功能的事务存储引擎。

优点：

• 支持事务，如果除了增加、查询，还有更新、删除操作应该选择 InnoDB 存储引擎。
• 对于巨大数据量的处理，建议使用 InnoDB。
• InnoDB 支持表锁，对于高并发场景下性能更高。

缺点：

• InnoDB 对于写处理效率低一些，并且占用更多磁盘空间来存储数据和索引。
• 对于缓存，InnoDB 不仅缓存索引而且缓存了真实数据，对内存要求高。

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

优点：

• 访问数据快，对于事务完整性没有要求的表为主要应用，或者应用与一些只读场景的引应用。
• 在针对 count(*) 统计时，因为有常数存储数据条数，这个统计效率很高。

缺点：

• 不支持事务，MySQL 数据崩溃后无法安全恢复。
• 支持表级锁，对于高并发场景性能较低。

其它引擎：

• archive 引擎：仅支持插入和查询两种功能，且数据插入后不能修改，拥有很好的数据压缩机制，支持行级锁。
• blackhole 引擎：丢弃写操作，读操作会返回空内容。
• CSV 引擎：存储数据时，以逗号分隔各个数据项，可以将普通的 csv 文件当做表进行处理，每个字段都必须有非空约束。
• Memory 引擎：数据存储在内存中，响应速度快，但是存储 MySQL 宕机数据丢失的情况，它对应的表空间是有限制的，可以通过参数设置。

5、Index 索引

索引：是存储引擎快速找到数据记录的一种数据结构，不同存储引擎有不同的索引。索引相当于是一本书的目录，是为了缩小扫描的范围，索引检索的查找效率较高。

优点：

• 降低磁盘 IO 次数，提高数据检索效率。
• 在连表查询时，可以加速表与表之间的连接。
• 在使用分组和排序进行数据查询时，针对该字段存在索引，可以减少查询时分组和排序的时间。

缺点：

• 创建索引和维护索引要消耗时间，并且随着数据量增加，消耗时间也会增加。
• 创建索引需要占用一定量的磁盘空间。
• 提高了数据的查询效率，但是降低了插入操作的效率。

数据页：在 MySQL 数据库中，数据最基本存储单位是数据页，每一页默认大小存储 16KB 数据。

• 单数据页查询，每行数据在逻辑上是链式存储，使用单链表连接每一行数据，然后遍历这页数据进行查找。
• 多数据页查询，页数据与页数据之间使用双链表连接的，需要先根据索引去找到对应的数据页，然后在数据页中去找对应的数据。

5.1、简单主键索引

行格式：每一条数据行的存储格式

• record_type：记录头信息的属性，0表示普通记录，1表示目录页，2表示最小记录，3表示最大记录。
• next_record：表示下一条数据的地址偏移，每条数据都是以单链表的方式存储的。

数据格式：主键总是有顺序排列，即使新增数据也会根据主键去插入到对应位置上。

索引结构：针对每一页数据创建对应的目录列表，将主键的最小值和页号存储起来，然后根据二分法去查询数据应该在那一页数据，不用遍历每一页中的数据。

数据目录页==（B+树）：每个数据页有一个目录项对应，将多个目录项也可以当成一个数据页，称为目录数据页==。多个目录数据页也可以通过双向链表链接起来，类似于数据页。

总结：

1. 页中有对应行数据，每一行有自己的行格式，行数据与行数据之间采用单链表方式链接，这样构成数据页。
2. 数据页默认大小 16 KB，每个数据页之间采用双链表进行连接。
3. 每个数据页有一个目录项，多个目录项集合成一个目录页，目录页中也有行数据，存储方式和数据页类似。
4. 当每层目录页大于 1 个，就会向上层生成新的目录页。

5.2、索引存储结构分类

聚簇索引：针对于主键构建的索引结构，不是一种单独的索引类型，而是一种数据存储方式（数据存储在叶子节点），索引及数据，数据及索引==（索引和数据存储在一起，底层就是B+树）==。

• 访问速度快，聚簇索引将用户数据和索引数据都保存在一颗 B+ 树中。
• 针对主键的排序查找和范围查找很快，本身在 B+ 树的数据按照主键排序存储。
• 新增速度依赖于新增主键的顺序，按照主键顺序插入最快，如果主键没有顺序就会出现页分裂现象，会将插入位置的后面数据后移。
• 二级索引访问需要先找到主键值，然后根据主键值查找到数据。

限制：

1. MySQL 数据库只有 InnoDB 存储引擎支持聚簇索引，而 MyISAM 不支持聚簇索引。
2. 每个表只能有一个聚簇索引，一般就是表的主键。
3. 如果没有表没有定义主键，那么就会选择非空唯一索引代替，如果也没有这样的索引，那么 innoDB 引擎就会自定义一个隐式的 row_id 作为聚簇索引。
4. 为了充分利用聚簇索引特性，所以在选用 InnoDB 的主键列尽量选择有序的顺序 id，不建议使用 UUID、字符串等等无序数据。

二级索引（非聚簇索引）：使用除主键以外的其它列构建索引，采用不同的排序规则构建 B+ 树。

回表：在二级索引 B+ 树中并不会存储对应的数据，而是存储的是聚簇索引对应主键数据。在查询数据时，先根据二级索引查询到对应数据对应的主键值，然后通过主键值去聚簇索引 B+ 树查询对应的数据，称为：回表。

联合索引：可以同时以多个列来建立索引，让 B+ 树根据多个字段进行排序构建，按照联合索引字段的顺序进行排序，如果某个索引字段相同，那就以下一个字段进行排序。(非聚簇索引)

B+ 树索引总结：

1. 针对于聚簇索引，根页面永远不动 ，在创建表时会创建一个根数据页，当这个根数据页存储空间不足，就会生成新的数据页并且将当前当前数据页的数据复制给新数据页，而当前数据页就会升级成目录页。(相当于 B+ 树向下生长)。
2. 针对非聚簇索引，非叶子节点除掉页号后需要唯一，也就是说在非叶子节点（目录数据页）需要将主键数据保存下来，如果没有主键保持唯一，那么针对相同数据在不同页时，不能区分属于那一数据页的数据。

3. Innod B+ 树数据页每页最少存储两条数据。

InnoDB 存储引擎与 MyISAM 存储引擎的索引对比：

InnoDB 存储引擎：底层存储数据在 .ibd 文件中，将索引和数据存储在一起的。

• 支持聚簇索引并且一定是包含一个聚簇索引。

MyISAM 存储引擎：底层的数据和索引分开存储的，数据存储在 .MYD 文件，而索引存储在 .MYI 文件中。

• 不支持聚簇索引，在 MyISAM 存储引擎中的 B+ 树中的叶子节点存储的是主键值 + 数据的地址而不是数据本身。
• 因为没有聚簇索引，每次查询都需要进行回表操作。

注意：创建索引存在空间和时间上的消耗，不能盲目创建索引，这样会增加维护成本。

5.3、为什么选择 B+ 树作为索引数据结构？

Hash 索引：这种索引只在 Memory 存储引擎支持，在 InnoDB 和 MyISAM 存储引擎都不支持。

• Hash 索引满足对 =、!=、IN 查询，对于范围查询还是需要进行全表扫描。
• Hash 索引的数据存储是无序的，对于排序的情况需要重新排序。
• Hash 索引对于联合索引，不能通过 hash 值来单独对一个或多个索引进行查询。
• 对于大量重复数据，在 hash 索引中会通过链式存储重复数据，查询时也会遍历这个链表。

自适应 Hash 索引：InnoDB 存储引擎中并不支持 Hash 索引，但是内部有一个自适应 Hash 索引优化。当某个数据经常被访问到，当满足某个条件时(MySQL 自己判断)，会在自己内存缓冲区开辟一段空间创建 Hash 索引，key：数据值，value：对应的数据页。

# 开启对应的自适应 Hash 索引，在配置文件中开启，默认是开启的
[mysqld]
adaptive-hash-index=on|off
1
2
3

B 树、B+ 树：

• B+ 树查询更加稳定，数据都是存储在叶子节点上，都需要经过 N 层的 IO 次数，而 B 树的查询 IO 次数可能1次也可能多次(非叶子节点上也存储了数据)。
• B+ 树查询效率高，由于 B+ 树非叶子节点不存储数据，这样存储的目录项越多，生成树的层树就越少，查询的 IO 次数就越少。
• B+ 树范围查询更快，由于 B+ 树的所有数据都有序存储在叶子节点上，可以快速进行范围查询，而B树需要去遍历非叶子节点。

5.4、InnoDB 数据存储结构

数据页：InnoDB 是 MySQL 的默认存储引擎，它以数据页为基本单位，默认数据页大小是 16KB。在数据库中，无论是读写一行数据，都是以当前行所在页加载到内存中，也就是说存储的基本单位是数据页，IO 操作的最小单位是数据页。

数据库存储空间：

• 区：比数据页大一级的存储结构，一个区会分配 64 个连续数据页，所以默认大小 64 * 16KB = 1MB 。
• 段：包含多个区，是数据库中分配单位。不同类型的数据库对象以不同的段形式存在。例如：表段、索引段等等。
• 表空间：分为系统表空间、独立表空间，逻辑上段存储在表空间中，一个表空间可以有多个段，但是一个段只有一个表空间。

5.4.1、页结构

页类型划分成数据页、系统页、Undo 页、事务数据页等，默认 大小 16KB。页的存储空间被划分成文件头、页头、最大最小记录、用户记录、空闲空间、页目录、文件尾7个部分。

• 文件头：存储当前数据页的基本信息。
  1. 页号，用于构建 B+ 树。
  2. 页类型，用于表示当前页是数据页、系统页、事务页。
  3. 前页、后页的地址偏移，页与页之间使用双向链表连接。
  4. 页校验和，与文件尾中校验和配对是否一致，保证数据在刷盘过程中的完整性。
  5. 后修改时对应的日志序列位置，与文件尾日志序列位置进行配对保证数据完整。
• 文件尾：存储当前数据页基本信息。
  1. 页校验和，与文件头校验和配对是否一致，保证数据在刷盘过程中的完整性。
  2. 最后修改时对应日志序列位置，与文件头日志序列位置进行配对保证数据完整。
• 用户记录、空闲空间、最小最大记录：用户记录按照指定行格式进行存储，空闲空间用于存储记录，当空闲空间存储满后就会新开辟页存储。最小最大记录分别用行格式的中 record_type 值的 2、3 表示，页中存在数据就会默认创建这两条数据（伪数据）。
• 页目录： 页数据中会根据主键值进行分组，每组数据只会保存最大元素（每组最后一个元素）的地址偏移量到页目录中，在进行查询时根据二分法查询数据应该位于哪个组中，这就是页目录。
  1. 最小、最大记录也会包含在里面，最小记录单独为一组，最大记录在最后一组，但是分组中不包含已删除记录。
  2. 每组数据的最后一条记录的记录头中的 n_owned 记录了这组有多少条数据。
  3. 除第一组以外，每组数据记录条数在 1~8 条。
• 页头：存储数据页中存储记录的状态信息，例如页中存储数据条数、第一条记录位置、页目录中有多少记录、第一条删除记录地址等等信息。

5.4.2、行格式

行格式： 数据存储在磁盘上的存放方式称为行格式，InnoDB 支持行格式有 Compact、Redundant、Dynamic、Compressed。

# 查看默认的行格式，默认行格式是dynamic
select @@innodb_default_row_format;

# 创建表时指定行格式
CREATE TABLE IF NOT EXISTS 表名(
....
)engine=... ROW_FORMAT=....;

# 查看表的行格式
SHOW TABLe STATUS LIKE '表名';

# 修改表行格式
ALTER TABLE 表名 ROW_FORMAT=...;
1
2
3
4
5
6
7
8
9
10
11
12
13

Compact 行格式：一条完整数据在行格式中主要分成两部分，真实数据(数据对应列的值)、额外信息(变长字段长度列表、NULL 值列表、记录头信息)。

• 变长字段长度列表：针对于变长数据类型的字段，需要记录数据真实存储的长度。
• NULL 值列表：将记录为 NULL 的字段统一进行管理，为 NULL 用1表示，不为 NULL 用0表示。
• 记录头信息：
  1. delete_mask：标识记录是否被删除，0 未删除，1 删除，删除的数据很多会形成垃圾链表，这部分空间可以被新数据覆盖。
  2. min_rec_mask：非叶子节点的最小记录会被标记成1，其它都标记为0。
  3. record_type：0 - 普通记录；1 - 非叶子节点记录；2 - 最小记录；3 - 最大记录。
  4. heap_no：当前记录在页中的位置，最小记录为0，最大记录为1，其它记录一次递增。
  5. n_owned：页目录中分组最后一条记录的头信息会存储该组的记录条数。
  6. next_record：下一条记录的地址偏移量，实现数据与数据之间的链式存储。
• 真实数据记录：真实数据记录除了记录数据外，还有三个隐藏列字段。
  1. row_id：行 id，唯一标识一条记录，如果表没有主键并且没有唯一不为空字段，那么 InnoDB 用这个字段构建聚簇索引。
  2. transaction_id：事务 id。
  3. roll_pointer：回滚指针。

其它行格式：

• 行溢出：页数据默认大小是 16KB，但是某些行数据大小超过了 16KB，这就出现了一行数据在一页中存放不需要多页数据进行存储，称为行溢出。

• Dynamic 行格式：与 Compact 行格式类似，区别在于处理行格式时，Commpact 采用部分溢出即只将存储不下的数据在其它页进行存储，但是 Dynamic 会把所有数据溢出存储在其它页中。

• Compressed 行格式：与 Dynamic 行格式类似，区别在于 Compressed 采用 zlib 算法压缩数据，对于大长度数据能够有效存储。

• Redundant 行格式：与 Compact 行格式类似，区别在于它的字段长度列表记录了所有列的存储长度，去掉了 NULL 列表。

  5.4.3、区、段、碎片区、表空间

• 区：

  1. 区的作用：页与页之间采用双向链表链接，可能存在存储位置不连续的情况，在 IO 时需要随机从磁盘读取数据效率低，所以分配连续空间用于存储页数据，逻辑上叫区。

  2. 区的大小：一个区默认大小 1MB，可以连续存储 64 个页数据，当数据量大时，分配空间就可以按照区进行分配，区与区之间使用双向链表链接。

• 段：

  1. 段的作用：逻辑上用于存放区的空间，将叶子节点数据存储在区中，然后把这些区归属到一个段上，称为数据段；将非叶子节点存储在区中，把这些区归属到一个段上，称为索引段；段的空间中将区与区之间连续起来，磁盘上可以进行顺序 IO 加载。

• 碎片区：

  1. 碎片区的作用：对于表中数据很少，如果也给它分配段进行存储，那么需要分配数据区、索引区（总共2MB），太浪费空间。所以对于少量数据，不同页可以共用一个区，称为为片区。
  2. 碎片区不属于某一个段，而是直接有表空间进行管理，当碎片区达到 32 个就会以完整区为单位来分配空间。

区的分类：

• 空闲区：还没有用到这个区的页面去存储数据。
• 有剩余空间的碎片区：表示碎片区还有可用空间。
• 没有剩余空间的碎片区：表示碎片区的所有页面都被使用，没有空闲空间。
• 附属某个段的区。

表空间：分为独立表空间、系统表空间、撤销表空间。独立表空间。

• 独立表空间：每个表分配一个存储文件。
• 系统表空间：所有表数据都存储在一个存储文件中。

5.5、索引的使用

索引分类：

• 从功能逻辑上，索引主要分为普通索引、唯一索引、主键索引、全文索引。
• 从物理实现方式上，索引分为聚簇索引（主键索引）、非聚簇索引（二级索引）。
• 从作用字段个数上，索引分为单列索引、联合索引。

1. 普通索引：只为提高查询效率，可以创建在任何类型字段上，建立字段对应的 B+ 树，查询时可以通过索引查询。
2. 唯一性索引：该索引字段值必须是唯一，添加唯一性约束会自动添加唯一性索引。
3. 主键索引：字段值唯一不为空，可以添加主键约束就会添加主键索引，添加唯一性 + 不为空约束相当于是主键索引。
4. 全文索引：适合大型数据集，查询数据量大的字符串类型字段，使用全文索引提高查询速度，只能作用在字符串类型的字段。
5. 单列索引：索引作用在一个字段上。
6. 联合索引：多个字段联合作为索引，可以通过多个字段进行查询，使用联合索引需要匹配最左前缀原则。

索引操作：

# 创建表时创建索引
CREATE TABLE IF NOT EXISTS 表名(
    id varchar(19) PRIMARY KEY, # 隐式添加主键索引
    name varchar(64) UNIQUE, # 隐式添加唯一性索引
    sex varchar(1),
    # 显示创建普通索引
    INDEX 索引名(作用字段),
    # 创建唯一性索引
    UNIQUE INDEX 索引名(作用字段),
    # 创建联合索引
    INDEX 索引名(作用字段1,作用字段2).
    # 创建全文索引
    FULLTEXT INDEX 索引名(作用字段)
);

# 已有表创建索引
ALTER TABLE 表名 ADD [索引类型：UNIQUE|FULLTEXT|NORMAL] INDEX 索引名称 ON 表名(作用字段) [索引排列方式(默认ASC)：ASC|DESC];
CREATE [索引类型：UNIQUE|FULLTEXT|NORMAL] INDEX 索引名称 ON 表名(作用字段) [索引排列方式(默认ASC)：ASC|DESC];

# 删除索引
ALTER TABLE 表名 DROP INDEX 索引名;
DROP INDEX 索引名 ON 表名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

MySQL8.0 索引新特性：

• 降序索引：在 B+ 树中索引默认排序是升序排列，在 MySQL8.0 之前也可以指定索引降序排列，但是是进行升序的反向扫描，在 MySQL8.0 之后 B+ 树才是真正能够降序排列。

CREATE 索引类型 INDEX 索引名 ON 表名(作用字段) ASC; # 升序索引（默认）
CREATE 索引类型 INDEX 索引名 ON 表名(作用字段) DESC; # 降序索引
1
2

• 隐藏索引：在删除索引后可能在业务上出现错误，再 MySQL8.0 之后可能将需要删除索引设置为隐藏索引，这时索引就无效了，然后业务没有影响后才真正删除索引，称为：软删除。隐藏索引可以用于检查查询语句的效率，在隐藏前后的查询时间是否有影响。

CREATE 索引类型 INDEX 索引名 ON 表名(作用字段) VISIBLE; # 可见（默认）
CREATE 索引类型 INDEX 索引名 ON 表名(作用字段) INVISIBLE; # 隐藏索引

# 修改索引可见性
ALTER TABLE 表名 ALTER INDEX 索引名 [VISIBLE|INVISIBLE];
1
2
3
4
5

注意：主键索引不能被设置成隐藏索引，隐藏索引在更新索引时也是会更新，但是隐藏索引并没有用到。

适合索引场景：

1. 具有唯一性的字段需要添加唯一性索引或者添加唯一性约束，添加唯一性约束会自动添加唯一性索引。
2. 频繁在 WHERE 条件中使用的字段，普通索引能够大大提高查询效率。
3. 频繁在 GROUP BY 和 ORDER BY 中使用的字段，B+ 树层的数据已经按照顺序排列，SQL 语句中在索引字段上进行排序或分组能大大提高查询效率，对于同时存在 GROUP BY 、ORDER BY，可以创建对应的联合索引，注意执行顺序以及联合索引顺序。
4. 在 UPDATE、DELETE 语句中的 WHERE 的字段添加索引，能提高数据更新效率。
5. DISTINCT 查询的字段适合添加索引。
6. 多表连接 join 时创建索引事项：（连接表数量不能超过3张）
   • 在连接过滤条件中的 WHERE 中字段添加索引。
   • 表与表之间连接字段添加索引，并且两张表中连接字段的类型必须保持一样，隐式转换会使用函数导致索引失效。
7. 推荐使用类型小的索引，节省数据空间以及索引空间，索引空间小目录页存储数据项多，层级就越低。
8. 使用字符串前缀创建索引，字符串很长创建索引可能导致索引空间很大并且在 B+ 树底层排序时字符串很长排序效率很低，所以采用字符串前缀一部分作为索引，如果存在前缀相同的重复数据需要回表查询对应满足条件的数据。

注意：字符串类型的索引必须指定索引长度，根据文本区分度选择索引长度，该字段上使用 ORDER BY 排序可能不准确，注意避免。

# 创建指定长度的索引
CREATE [索引类型] INDEX 索引名 ON 表名(作用字段(索引长度));

# 文本区分度，索引长度截取选择
# 截取name字段的前面几个，然后去重除以所有记录条数，越接近1越有效但是注意字符长度
SELECT COUNT(DISTINCT LEFT(`name`, 2)) / COUNT(*) as t2,
COUNT(DISTINCT LEFT(`name`, 5)) / COUNT(*) as t5,
COUNT(DISTINCT LEFT(`name`, 7)) / COUNT(*) as t7,
COUNT(DISTINCT LEFT(`name`, 9)) / COUNT(*) as t9,
COUNT(DISTINCT LEFT(`name`, 8)) / COUNT(*) as t8
FROM temp;
1
2
3
4
5
6
7
8
9
10
11

9. 字段值的基数（重复数据个数）越大适合创建索引，基数越大数据区分度越高，条件过滤的数据越多。

# 区分度越接近1越好
SELECT COUNT(DISTINCT LEFT(`name`, 2)) / COUNT(*) as t2 FROM temp
1
2

10. 使用最频繁列放在联合索引的前面（最左匹配原则）。
11. 多个字段需要创建索引，联合索引优于单个索引。

不适合索引场景：

1. 在 WHERE、ORDER BY、GROUP BY 中使用不到字段不要创建索引。
2. 数据量小的表最好不要创建索引，创建索引对于查询效率并不大。
3. 有大量重复数据字段上不要创建索引，重复度高于 10% 就不要建索引。
4. 避免对频繁更新的字段创建索引，更新索引字段会更新索引树，更新效率低。
5. 不建议在无序字段上创建索引。
6. 不要定义冗余重复索引，例如：当前索引在联合索引在第一个，当前索引和联合索引冗余。

索引使用注意：

• 单张表的索引数量不能超过6个，索引需要占用磁盘空间，数据更新索引字段更改会导致索引树修改效率低。
• 在查询生成执行计划时，优化器会评判每一个索引，索引过多会导致生成执行计划时间长效率降低。
• 索引有利有弊，能提高查询效率，但是降低了增加、更新的效率。

6、MySQL 数据库调优

SQL 优化步骤：

系统性能参数：

• connections：MySQL 服务启动后连接的次数。
• uptime：MySQL 服务上线时间，单位秒。
• slow_queries：查看慢查询次数。
• innodb_rows_read|inserted|updated|deleted：执行查询、插入、更新、删除的记录条数。
• com_select|insert|update|delete：执行查询、插入、更新、删除的次数。
• last_query_cost：查看最新查询成本，需要加载几个数据页。

# 查看参数信息，这些参数重启服务就会置零
SHOW [GLOBAL|SESSION] STATUS LIKE '参数';
1
2

6.1、慢查询 SQL

MySQL 有记录慢查询的日志，记录超过设定 SQL 阈值的查询语句，通过设置 long_query_time 慢查询的阈值，默认值是 10s。MySQL 默认是没有开启慢查询日志，需要手动开启一般，建议在调优时开启，其它情况关闭。

# 查看是否开启慢查询
show variables like 'slow_query_log%';
# 慢查询日志保存文件路径
show variables like 'slow_query_log_file';
# 开启慢查询
set global slow_query_log = on;

# 查看慢查询时间阈值
show variables like 'long_query_time';
# 设置阈值
set global long_query_time = 1;


# 配置文件永久设置慢查询参数
[mysqld]
slow_query_log=on
slow_query_log_file=文件路径/文件名
long_query_time=值 
log_output=FILE # 输出放在文件中
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

查看慢查询 SQL 语句：使用 mysqldumpslow 脚本查询慢查询日志，也可以直接查看文件中内容。

# 查看命令帮助 
mysqldumpslow -help

# 查看慢查询日志
mysqldumpslow -a -t 10 文件路径
# -a：显示查询参数
# -t：显示花费时间前10条，默认降序排序

# 重置慢查询日志文件
# 先删除日志
rm -f 文件路径
mysqladmin -uroot -p flush-logs slow
1
2
3
4
5
6
7
8
9
10
11
12

记录 SQL 执行流程：

# 查看是否开启 SQL 执行流程
SHOW VARIABLES LIKE 'profiling';
# 开启 SQL 执行记录流程
set global profiling=on;

# 记录sql执行类表
show profiles;
# 查看当前最新sql执行过程
show profile;
# 查看指定 id 的 sql 执行过程
show profile for query id
1
2
3
4
5
6
7
8
9
10
11

6.2、explain 分析工具

定位到慢查询 SQL 语句后可以使用 explain 工具分析语句查看 SQL 的执行计划，可以针对性分析语句的性能。

# 使用方式，查看执行计划并非真正去执行 SQL 语句
explain SQL语句
1
2

• id：在查询中每一个 select 都会有一个对应唯一 id。
• select_type：对应查询类型。
• table：查询的表名。
• partitions：匹配的分区信息。
• type：针对单表的访问方法，使用索引访问或者全表扫描。
• possible_keys：可能使用到的索引。
• key：实际上使用到的索引。
• key_len：实际使用索引的长度。
• ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。
• rows：预计需要读取的记录条数。
• filtered：表更过条件过滤后剩余记录的百分比。
• Extra：额外信息

6.2.1、explain 属性

table：每一行记录都会对应一张表名，可能是临时表或者是连接表。

EXPLAIN SELECT t.* FROM temp t LEFT JOIN department ON t.department_id = department.id;
1

id：在一个大查询中每一个 select 都对应一个唯一的 id，但是对于子查询优化器可能优化 SQL 改成连接查询。

EXPLAIN SELECT * FROM temp WHERE temp.department_id = (
		SELECT id FROM department WHERE id = '1'
);
1
2
3

• id 相同表示是一组查询，从上往下顺序执行。
• id 值越大，优先级越高，越先被执行。
• 每一个不同 id 表示一次独立查询，sql 语句中查询次数越少越好。

select_type：每个操作都有一个操作类型，表示扮演什么角色。

• SIMPLE：SELECT 查询语句中不包含 UNION 和子查询都属于 SIMPLE 类型，或者是 UPDATE、DELETE、INSERT。
• PRIMARY：UNION 和子查询的左边查询。
• SUBQUERY：非相关子查询不能优化成连接查询，查询类型就是 SUBQUERY。
• UNION：联合查询。
• DEPENDENT SUBQUERY ：相关子查询。
• DERIVED：派生类型，存在派生表的操作。

type：表示 SQL 执行语句时访问表的方法，下面的性能降序排列。

• system：表中只有一条记录并且存储引擎是 MyISAM、Memory 时的访问方法。
• const：根据主键或者唯一二级索引进行等值匹配的访问方法。
• eq_ref：连接查询时针对驱动表通过索引等值匹配时对应驱动表的访问方式。
• ref：根据二级索引进行等值匹配时的访问方法。
• ref_or_null：根据二级索引进行等值匹配时，二级索引条件值可能为 NULL 时的访问方法。

# name 上有个普通索引
EXPLAIN SELECT * FROM temp WHERE name = '张三100' OR name IS NULL;
1
2

• index_merge：多个二级索引条件之间使用 OR 连接起来的访问方式，相当于同时使用多个索引，但是条件中存在非索引列会导致索引失效。

# name1 和 name2上都有索引
EXPLAIN SELECT * FROM temp WHERE name1 = '张三100' OR name2 = '李四';
1
2

• range：获取索引是某些范围区间记录。
• index：使用索引覆盖（二级索引不用回表操作），但是需要扫描全部的索引记录，查询字段和条件字段都是索引字段。
• all：全表扫描。

system > const > eq_ref > ref > ref_or_null > index_merge > range > index > all，SQL 优化目标至少要达到 range 级别，要求是 ref。

possible_keys、keys、key_len：

• possible_keys：在查询语句中可能所有用到的索引字段。
• key：在查询语句中实际用到的索引字段，执行优化器会选择成本最低的索引。
• key_len：索引字段使用到的长度，与索引自己长度相比之越大越好，一般在联合索引中起作用。

ref：使用索引列等值查询时，与索引列等值匹配时的对象信息，可能是常数、字段、函数。

# 常数、在连接查询时等于另一个字段值、或者使用函数处理了的字段
EXPLAIN SELECT * FROM temp WHERE id = 100;
1
2

rows、filtered：

• rows：预计需要读取的记录条数，值越小越好。
• filtered：某个经过条件搜索过滤后剩余记录条数占比，越高越好，一般在连接查询时关注。

Extra：展示 SQL 语句一些额外信息，更准确理解 MySQL 到底如何执行查询语句的。

• impossible where：查询语句中的条件永远是 false。
• NULL：使用索引时，字段为 NULL。
• No match row xxx：查询记录里面没有匹配的记录。
• use index：使用了覆盖索引，不用回表操作。
• join buffer：在进行连接查询时，被驱动表不能有效使用索引，就会分配一块 join buffer 空间加快查询数据。
• use temporary：使用临时表。

6.2.2、explain 显示格式

传统格式：explain sql语句

json 格式：explain format=json SQL语句，返回 json 字符串展示比传统方式更多信息。

tree 方式：explain format=tree SQL语句，展示有层次但是信息少。

6.3、索引失效场景

MySQL 中提高性能最有效方式是对数据库表设计合理索引，索引能提高访问数据效率，但是最终是否使用索引由优化器成本开销决定。

• 左前缀法则：联合索引中索引的顺序对构建 B+ 树有影响，如果在联合索引中没有用到最左边索引就会使索引失效。

# 索引：(key1,key2,key3)
select ..... where key1 = ... and key2 = ... and key3 = ... # 索引有效
select ..... where key1 = ... and key2 = ... # 索引有效
select ..... where key1 = ... and key3 = ... # 索引有效
select ..... where key2 = ... and key3 = ... # 并没有使用到最左边的索引，索引无效
1
2
3
4
5

• 操作索引失效：在索引字段进行操作，例如计算、函数、类型转换等等会使索引失效。

# 索引：name
select ... where name like 'a%'; # 使用到索引
select ... where left(name,3) = 'abc'; # 索引字段使用函数会导致索引失效
1
2
3

• 范围条件右边索引失效：在联合索引中，某一索引使用了范围索引，它的右边的其它索引将会失效。

# 索引：(key1,key2,key3)
select ..... where key1 = ... and key2 > ... and key3 = ... # 联合索引只会使用到key1和key2，key3索引不会使用到
1
2

• 不等值：在索引字段添加不等于条件时索引失效。

# 索引：name
select ... where name != 'xxx'; # name 字段索引失效
1
2

• NULL 值：当条件中出现 NULL 值，优化器会根据 SQL 执行成本判断是否使用索引，可能使用也可能不使用（数据量小索引可以生效）。

# 索引：name
select ... where name IS NOT NULL # 索引可能失效
1
2

• like 以 % 开头：模糊查询时以 % 开头的匹配，会导致索引失效。

# 索引：name
select ... where name like '%aaa'; # 索引失效
1
2

注意：在开发中禁止使用左模糊或者全模糊，如果需要可以使用搜索引擎解决。

• or 条件：在 or 条件的左右存在非索引列，会导致索引失效，or 条件左右都是索引字段可以使用索引。

# 索引：name
select ... where name = '张三' OR age > 10; # 索引
1
2

6.4、查询优化

6.4.1、连接查询优化

连接查询底层原理：对于内、外连接查询由优化器选择谁是驱动表、被驱动表（外连接也可能会被重写成内连接）。

• 简单嵌套循环连接：从驱动表中取出一条数据，然后再和被驱动表中所有数据匹配，成功匹配的数据返回（循环匹配）。

• 块嵌套循环连接：不再一条一条获取驱动表数据而是一块一块加载，引入到 join buffer 缓冲区，然后全表扫描被驱动表批量进行匹配记录，降低了被驱动表访问次数。

# 默认缓冲区是开启的，主要设置缓冲区大小
# 查询缓冲区大小
show VARIABLES LIKE '%join_buffer_size%'; # 默认大小256KB
set global|session join_buffer_size=值;
1
2
3
4

• 索引嵌套循环连接：减少内层表数据匹配次数，需要在被驱动表的匹配条件字段加上索引。

• hash join：在 MySQL8.0 之前默认使用块嵌套方式，MySQL 8.0 后引入 hash join 默认使用这种方式，它分为两个阶段构建、探测阶段，这种方式对于多表连接效率更好。
  1. 构建阶段：遍历驱动表以 join 条件的 hash 值作为 key，查询列作为 value 构建一个 hash table。
  2. 探测阶段：遍历被驱动表，然后计算 join 条件的 hash 值去 hash table 中匹配，匹配到就返回。

总结：索引嵌套 > 块级嵌套 > 循环嵌套

1. 用小结果集驱动大结果集，尽量在被驱动表的连接条件上加上索引，减少内层循环次数。
2. 增加 join_buffer_size 大小，缓存数据多内层扫表次数就少。
3. 减少驱动表不必要的查询字段，字段少在缓冲池缓冲数据就多。

6.4.2、子查询优化

子查询：子查询可以实现比较复杂的查询，但是子查询效率不高，建议编写SQL使用连接查询替代子查询。

• 子查询会在内层建立一个临时表，外层从临时表中查询数据，查询完毕后再撤销临时表，消耗大量资源。
• 建立临时表无法建立索引，如果数据量大查询效率低。

6.4.3、排序优化

MySQL 排序：支持两种排序方式，FileSort、Index排序。

• Index：索引保证了数据有序性，不需要再排序。
• FileSort：数据加载到内存中进行排序，占用较多 CPU 资源。

优化建议：

• ORDER BY 中使用索引字段来避免 FileSort 排序。
• 如果 ORDER BY 和 WHERE 中使用不同索引，可以建立两个索引的联合索引，注意联合索引排列的顺序。
• 无法使用 index 索引排序，需要对 FileSort 优化。
• 需要注意索引字段排列顺序与 ORDER BY 排列顺序。
• ORDER BY、GROUP BY、DISTINCT这些查询语句使用 WHERE 过滤后的数据尽量保持在1000条以内，否则 SQL 查询慢。

# 设置FileSort中排序缓存区大小
SHOW VARIABLES LIKE 'sort_buffer_size'; # 默认大小256KB，建议修改成1-8MB
SET GLOBAL|SESSION sort_buffer_size=值;
1
2
3

6.5、覆盖索引

覆盖索引：SELECT ... FROM 查询列表是索引列+主键列的全部或者一部分，在查询时就不用进行回表操作，对应 type 为 index。

# 索引：name
select name,sex from temp where name like '%张'; # 由于二级索引可以直接使用覆盖索引，这个索引会生效
1
2

注意：是否使用索引，完全由执行优化器计算出的执行成本决定的，有些情况未使用索引成本比使用索引更低。

• 覆盖索引避免了回表操作，回表操作是随机 IO，减少回表操作可以大大提高效率。
• 维护覆盖索引需要一定成本。

索引下推：在二级索引上，通过索引查询到数据，会在回表之前会将查询的数据用索引失效的条件进行过滤后再回表，索引下推减少了二级索引回表的数据。

# 索引：（name，password）
EXPLAIN SELECT * FROM temp WHERE `name` = '张三6002' AND `name` LIKE '%张%'; # `name` LIKE '%张%' 索引会失效，但是会在回表之前过滤掉部分数据

# `password` LIKE '%dasgfusdfs%' 索引会失效，但是会在回表之前过滤掉部分数据
EXPLAIN SELECT * FROM temp WHERE `name` = '张三6002' AND `password` LIKE '%dasgfusdfs%';
1
2
3
4
5

# 默认情况下索引下推是开启的
SET optimizer_switch = 'index_condition_pushdown=off|on';
1
2

6.6、其它调优

SQL 编写优化：

• 禁止使用 select * ，需要具体指明字段，优化会去查询系统表解析字段，效率较低。
• union all 替换 union，减少建立临时表。
• join 表不能超过 5 个，多表连接效率很低。
• 减少使用 order by、group by、distinct，减少CPU消耗，使用这些语句的结果尽量保证在 1000 条。
• 多次 alter 表操作尽量合并成一次操作，进行 alter 操作会导致表锁，尽量避免高峰期进行。
• 事务中的 SQL 语句最好不要超过 5 个。

数据库调优步骤：

1. 选择合适的数据库，不同场景适合不同数据库，例如 MySQL、Oracle、Redis 非关系型数据库等等。
2. 优化设计，需要遵循数据库设计范式，表字段设计优化，减少表中数据的冗余。
3. 优化逻辑查询优化，编写效率更高 SQL 语句达到相同查询效果。
4. 物理查询优化，正确使用索引，提高查询效率。
5. 优化数据库服务器参数，增加数据吞吐量。
6. 库级优化，包含读写分离、数据分片（分库分表）。

MySQL 服务器优化：

• 硬件优化：

  1. 选用较大内存、高速磁盘系统，但是成本较高。
  2. 合理分布磁盘 IO，配备多处理器，使用多核 CPU。

• MySQL 参数优化：

  1. innodb_buffer_pool_size缓冲区大小，可以缓存表数据以及索引数据，值越大查询速度越快，但是过大会影响操作系统性能。（线程共享）
  2. key_buffer_size索引缓存大小，增大索引缓存区大小可以更好处理索引，但是太大会降低系统性能，4GB内存设置为258MB。（线程共享）
  3. table_open_cache表示同时打开表个数，默认4000，同时打开表太多会影响操作系统。（线程共享）
  4. query_cache_size查询缓存大小，MySQL8.0 之后弃用，与 query_cache_type 值为1开启缓存，0关闭缓存。
  5. sort_buffer_size排序线程设置缓存区大小，值越大排序、分组效率更高，默认2MB对于4GB建议设置成6-8MB。（单线程）
  6. join_buffer_size连接查询是缓存区大小，提高连接查询效率。（单线程）
  7. read_buffer_szie扫描表设置缓冲区大小，默认64KB，建议设置成4MB。（单线程）
  8. innodb_log_buffer_size事务日志使用缓存区，为了提高性能先将日志信息写入缓存，在一次性写入磁盘中。
  9. max_connections表示连接到 MySQL 数据库的最大数量，默认值200，当 connection_errors_max_connections 在不断增加表示允许连接数满了可以适当增加连接数，性能好点服务器支持 500-1000 左右。
  10. back_log当连接满了控制 MySQL 监听 TCP 端口设置的积压请求栈大小。
  11. thread_cache_size线程池缓存线程数量大小，客户端断开连接会将当前线程缓存，当客户端重新连接无需创建新线程，默认值10可以修改成120。
  12. wait_timeout请求最大连接时间，一般设置成5-10s。

  MySQL 服务器参数调优建议列表：

  [mysqld]
  port=3306
  server-id=1
  socket=/tmp/mysql.sock
  skip-locking
  #避免MySQL的外部锁定，减少出错几率增强稳定性
  # skip-name-resolve
  #禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！
  back_log=384
  key_buffer_size=256M
  max_allowed_packet=4M
  thread_stack=256K
  table_cache=128K
  sort_buffer_size=6M
  read_buffer_size=4M
  read_rnd_buffer_size=16M
  join_buffer_size=8M
  myisam_sort_buffer_size=64M
  thread_cache_size=64
  query_cache_size=64M
  tmp_table_size=256M
  max_connections=768
  max_connect_errors=10000000
  wait_timeout=10
  thread_concurrency=8
  #该参数取值为服务器逻辑CPU数量*2
  skip-networking
  #开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果web服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项。
  innodb_additional_mem_pool_size=4M
  innodb_flush_log_at_trx_commit=1
  innodb_log_buffer_size=2M
  innodb_thread_concurrency=8
  #与服务器CPU数量保持一致
  tmp_table_size=64M
  thread_cache_size=120
  query_cache_size=32M
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36

  其它优化策略：

  • 优化数据结构，优先选择符合存储需要的最小数据类型。
    1. 选择合适的数据库约束，例如非空、唯一约束。
    2. 适当增加冗余字段，但是注意数据一致性问题。
    3. 优化表，使用 optimize table 表名 整理表存储碎片，减少存储大小。
  • 大表优化
    1. 限制查询范围条件，减少查询的数据量。
    2. 读写分离，主数据库负责写，然后同步给从数据库，读数据给到从数据库。
    3. 垂直拆分，将功能相关联的表放在一个数据库中，将常用列与不常用列拆分成不同表。
    4. 水平拆分，表中数据量大可以将数据进行分片存储，但是处理逻辑复杂。

7、MySQL 事务

事务：指一个完整的业务，最小的工作单位不可在分，事务要么成功，要么就失败。一般只有 insert、update、delete 涉及数据的修改会有事务出现，MySQL 只有 InnoDB 存储引擎支持事务。

• 原子性（A）：事务是最小的工作单元，不能再分。

• 一致性（C）：在同一个事务中，所有的操作必须同时成功或者同时失败。

• 隔离性（I）：指一个事务执行在对应隔离级别中不能被其它事务干扰，事务之间并发操作之间使用的数据是隔离的，通过锁机制实现。

• 持久性（D）：事务结束最终保障是事务提交，将数据永久持久化到硬盘上，持久性是通过事务日志(重做日志、回滚日志)保证。

7.1、事务实现

事务执行流程：开启事务 --> 系列 DML 操作 --> 提交事务或者回滚事务

显示事务：手动开启事务提交事务。

# 开启事务
start transaction [read only | read write（默认）| with consistent snapshot]
# 一系列 DML 操作
.....
# 提交事务
commit
# 回滚事务，回滚到最近一次commit之后
rollback 

# 保存点：在事务中创建保存点，事务回滚可以回滚到具体保存点，然后在进行后续提交或者回滚操作，一个事务可以有多个保存点
savepoint 保存点名称;
# 回滚导具体保存点
rollback to 保存点名称
# 删除保存点
release savepoint 保存点名称
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

隐式事务：MySQL 数据库默认开启自动提交，每一条 DML 操作都是一个事务，可以通过 autocommit 变量设置。

注意：有些 SQL 语句不会受 autocommit 影响会自动提交事务，例如：数据定义语言、使用 mysql 中的表、手动开启一个事务但未提交又开启一个事务前一个事务会自动提交、关于 MySQL 复制语句。

事务分类：

• 普通事务：在 MySQL 中使用 commit 和 rollback 的事务操作。
• 保存点事务：在普通事务中增加保存点操作。
• 链式事务： 事务由多个子事务组成，一个事务提交会自动开启下一个事务。通过 completion_type 参数设置，0(默认值)：下一个事务还是需要手动开启，1：自动开启下一个事务，2：提交事务后会与服务器断开连接。
• 嵌套事务：一个大事务下存在很多小事务。
• 分布式事务：在分布式系统下的普通事务。

7.2、事务隔离级别

事务隔离级别：

• 读未提交（read uncommitted）：事务 a 可读取到事务 b 未提交的数据，可能出现脏读问题。
• 读已提交（read committed）：事务 a 只能读取到事务 b 已经提交的数据，解决脏读问题但是存在不可重复读和幻读问题。
• 可重复读（repeatable read）：事务 a 读取数据，事务 b 修改数据并提交，事务 a 再次读取数据和第一次读取数据一样即使事务 b 修改了数据，但是会读取到事务 b 新增的事务，可能出现幻读问题（MySQL 默认隔离级别）。
• 序列化（serializable） ：事务与事务之间排队执行，解决了所有的问题，但是效率极低。

# 查看隔离级别
SELECT @@transaction_isolation;
# 设置隔离级别
set [global|session] transaction isolation level 隔离级别; # read uncommitted。。。。。
# 或
set [global|session] transaction_isolation = '隔离级别'; # read-uncommitted、read-committed。。。。。。
1
2
3
4
5
6

读未提交：

结论：当处于读未提交级别时，事务 B 没有提交的数据，在事务 A 中也能够查询到。

读已提交：

结论：事务 B 插入一条数据，但未提交事务，事务 A 不能查询到事务 B 未提交的数据。只有事务 B 提交事务后才能查询到数据。

可重复读：

结论：事务 A 查询的到数据始终是一致的，是第一次查询到的数据，即使事务 B 修改了并提交了数据。

序列化：

结论：事务A和事务B会排队进行。

7.3、事务日志

事务日志：事务隔离性通过锁机制实现，而事务原子性、一致性、持久性由事务日志保证，包含 redo log 和 undo log。

• redo log 重做日志：在存储引擎生成的日志，记录物理级别的修改，记录页号、偏移量、修改数据，用于保证事务持久性。
• undo log 回滚日志：在存储引擎生成的日志，记录逻辑级别的修改，对于一条 DML 语句都会记录一条相反的 DML 语句用于事务回滚，保证事务原子性、一致性。

7.3.1、redo log 日志

redo log 作用：

MySQL 在访问数据时会将页数据加载到内存缓冲池中（buffer pool），更新数据都会先更新到缓冲池中并不是每次修改都会将内存数据持久化到磁盘，而是每隔一段时间去持久化，这样就会出现事务提交但是未持久化到磁盘数据库宕机就会数据丢失，在事务操作时，会将数据先写入日志才会成功提交事务（WAL 优先写日志），而 redo 日志会不断记录事务中每个操作，在数据库宕机重启后会从 redo 日志中加载还未来得及持久化的数据（数据恢复）。

• redo 日志降低磁盘持久化频率，提高系统性能。
• redo 日志占用空间小，数据恢复快。

redo log 组成：

• redo log buffe：服务器启动时会申请一块空间，通过 innodb_log_buffer_size 变量设置，默认大小 16 MB最大 4096 MB。在内存中保存 redo log 日志数据，然后持久化到 redo log file。
• redo log file：持久化磁盘中用于事务数据恢复，保存在 mysql 安装目录。MySQL8.0.30 之前采用 ib_logfilex 文件存储，之后采用动态调整文件大小将文件存储在 #innodb_redo 目录下并分成 32 个小文件存储，每个文件大小由 innodb_redo_log_capacity/32 变量决定，正在使用的文件名为 #ib_redoN ，空闲文件名为 \#ib_redoN_tmp。对于已经刷盘的数据，在 redo log file 占用的空间可以重用。
  1. innodb_log_group_home_dir：指定 redo log 日志文件保存路径，默认为数据保存路径。
  2. innodb_log_files_in_group：指定 redo log file 文件个数，默认两2个，最大100个。
  3. innodb_log_file_size：单个文件大小，默认48MB。
  4. innodb_redo_log_capacity：MySQL8.0.30 后如果设置了这个参数前面三个参数无效，则默认 32 个文件，大小平均。

redo log 刷盘策略：redo 日志记录事务操作会先将数据写到 redo log buffe，然后根据不同刷盘策略持久化到 redo log file 文件中，通过 innodb_flush_log_at_trx_commit 设置对应的刷盘策略。

• 0：表示每次事务提交时不进行刷盘，而是后台线程每隔 1s 进行刷盘操作，可能存在未提交事务也会被持久化。

• 1（默认值）：表示每次提交事务都会进行刷盘操作。
• 2：表示每次事务提交，redo log buffer 会把数据交给系统缓存，后续全部交给操作系统完成，可能出现操作系统宕机数据丢失。

注意：redo log buffer 是不断记录事务的所有操作，而 redo log file 是根据不同的刷盘策略将内存的 redo log buffer 数据持久化到文件中。

7.3.2、undo log 日志

undo log：保证事务原子性、一致性，是事务更新数据的前置操作，会把更新数据（增、删、改）之前的数据写到 undo log 中，发生事务回滚恢复成原来的数据。

• 回滚数据， 逻辑上 undo log 记录了事务每个操作的逆过程，发生事务回滚实际是执行逆过程逻辑操作。
• MVCC 多版本控制是 undo log 实现的，当事务读取一行记录，但这行记录被其它事务占用并修改，当前事务可以通过 undo log 读取之前版本的行数据，实现非锁定读。

undo 存储结构：采用段方式存储，称为回滚段。每个段记录 1024 个 undo log segment，每个片进行 undo log 页申请。innodb 最大支持 128 个回滚段，所以同时支持事务为 128 * 1024 个。

• innodb_undo_directory：设置 undo log 日志文件保存路径，默认在当前数据目录下。
• innodb_undo_logs：设置回滚段个数，默认128个。

undo 页重用：当提交事务时不会立即删除 undo 页，所以一个页中掺杂着其它事务日志，在页中的 undo 日志提交后会将提交的以链表方式存储，然后判断如果页中使用空间小于3/4就可以重用不会被回收，其它事务可以继续添加事务日志到当前页中。

1. 事务提交时，会将 undo log 日志放入链表，提供给后面的 purge 线程操作。
2. 事务提交时，判断 undo log 数据页是否可以重用，若可以就分配给其它事务使用。

undo log 页中类型：

1. 未提交的回滚数据：该数据关联的事务未提交，不能被其它事务的数据覆盖。
2. 提交但未过期的回滚数据：该数据关联的事务已提交，但受 undo retention 参数保持时间影响。
3. 提交且已过期的回滚数据：属于过期数据，当回滚段满后优先覆盖这类数据。

undo log 类型：

• insert undo log：在 insert 数据时产生的 undo 日志，只对本事务可见，其它事务不可见，提交事务后可以直接删除。
• update undo log：修改、删除数据时产生的 undo 日志，因为需要提供 MVCC 机制，可能其它事务需要读取数据，因此在事务提交时不能直接删除而是需要等待后续 purge 线程去删除。

8、MySQL 锁

8.1、共享锁和排他锁

• 共享锁（读锁 S）：针对同一份数据，多个事务的读操作可以同时进行相互不影响。
• 排它锁（写锁 X）：针对同一份数据，事务的写锁会阻塞其它事务的写锁和读锁，事务之前需要排队执行，写操作需要加写锁。

注意：在 MyISAM 存储引擎中只支持表级锁，在 InnoDB 存储引擎支持表级、行级锁，共享锁和排它锁可以加在表上也可以加在行上。

# 给事务中的sql语句加锁
begin; # 开启事务
# 加上共享锁
select ... lock in share mode;
# 8.0写法
select ... for share;

# 加上排它锁
select ... for update;
1
2
3
4
5
6
7
8
9

超时：如果事务一直获取不到锁就会等待，innodb_lock_wait_timeout设置等待超时时间，超过这个时间还未获取到锁就直接报错。MySQL8.0 新特性可以在事务语句后面添加 nowait、skip locked。

1. notwait：如果发现查询行数据已经加锁，就不等待直接报错。
2. skip locked：只返回未加锁的行数据，不包含加锁的行。

8.2、表锁

表锁：MySQL 最基本的锁，会将整张表锁定不会依赖某个存储引擎，避免了死锁问题但是并发性低。MyISAM 存储引擎只支持表锁，而 InnoDB 支持表锁和行锁，在 InnoDB 中尽量使用行锁提高并发度。

# 查看数据库中表锁情况，其中in_use字段0：未加锁，1：加锁
show open tables;
# 查看数据库中加锁的表
show open tables where in_use > 0;
# 释放数据库锁住的表
unlock tables;
1
2
3
4
5
6

表锁之共享锁和排它锁：

# 给表加上共享锁
lock tables 表名 read;
# 给表加上排它锁
lock tables 表名 write;
1
2
3
4

注意：MyISAM 存储引擎在读数据时会给表加上读锁，更新数据时会给表加上写锁；InnoDB 存储引擎在读取、更新数据不会给表上加锁而是在行记录上加锁。

表锁之意向锁： 在表中某一行记录加上锁，它会自动在表级别上加上意向锁，如果某个事务需要给这个表加锁发现对应表有意向锁，表示表中数据有数据被锁需要阻塞，不同每条数据去判断是否加锁。（协调行锁与表锁并存）

• InnoDB 支持多粒度锁，行级锁和表级锁可以共存。
• 意向锁之间不排斥，但是意向锁会与其它锁排斥。

表锁之自增锁和元数据锁：

• 自增锁：当向有自增主键约束表中插入值时，每条插入语句都会竞争自增锁保证主键唯一，在 innodb 中可以通过 innodb_autoinc_lock_mode 设置自增锁的锁定模式。
  1. innodb_autoinc_lock_mode=0：传统模式，每条插入语句排队执行，并发度低。
  2. innodb_autoinc_lock_mode=1（8.0之前默认）：连续模式，事先确定插入的记录条数先会获取自增锁并申请对应条数就释放锁，事先不确定插入条数也会排队等待。
  3. innodb_autoinc_lock_mode=2（8.0之后默认）：不会使用自增锁，它只会保证生成的主键唯一的但是不保证连续，并发度最高。
• 元数据锁：当对表做增删改查操作时会加上元数据读锁，当对表做表结构改变时会加上元数据写锁，不需要显示加锁进行对应操作自动加锁。

8.3、行锁

行锁（记录锁）：只有在 innodb 中支持行锁，锁定表中某些行记录，在存储引擎实现行级锁。这种锁的粒度小并发度高，但是锁开销大加锁慢容易出现死锁。

• 一个事务获取记录的S型锁，其它事务可以获取记录的S型锁，但是不能获取记录的X型锁。
• 一个事务获取记录的X型锁，其它事务不能获取记录的S型锁和X型锁。

间隙锁：为解决幻读问题，可以采用 MVCC 和加锁机制，加锁就是加上间隙锁解决。

注意：当加间隙锁范围大于表中数据最大返回，那么加锁范围就是(当前最大记录，页中最大记录)。

间隙锁中的死锁：由于间隙锁之间不是互斥的，可以重复添加，如果一个事务先对某个范围加锁，另一个事务也对这个范围加锁，然后在该范围添加数据就会出现死锁。

临建锁： 有时即想锁住当前记录又想锁住该记录前边的间隙插入新记录，这就是临建锁（记录锁和间隙锁的合体），innodb 默认使用临建锁。

插入意向锁：在使用间隙锁时，其它事务在范围内插入数据会进行排队等待，也会给这个操作加上锁就是插入意向锁。

8.4、悲观锁和乐观锁

悲观锁：总是假设最坏情况，拿取数据时总认为会有其它线程修改数据就会把数据进行加锁，通过 MySQL 中的锁机制实现，其它事务需要使用加锁数据就要排队等待。

# sql 加锁，其它事务访问id为1的数据就需排队等待
select * from account where id = 1 for update;
1
2

注意：select .... for update 会把执行过程中所有扫描的行都会加上锁，因此在使用悲观锁时确定使用了索引避免全表扫描，否则会将全表数据锁住。

乐观锁：总假设最好情况，不会认为有其它事务修改数据就不会对数据进行加锁，乐观锁并不是通过MySQL实现而是通过应用程序去实现。

乐观锁版本号机制：表中添加一个 version 字段，应用程序开始事务前先读取数据并记录 version，然后更新数据时会与 version 版本号匹配是否一致，如果一致就可以更新数据并将 version 版本号+1，如果不一致表示有其它事务更新了这条数据就更新失败。

# 版本号机制
update ... set version = version+1 where version = version;
1
2

注意：如果在并发量大时，使用乐观锁机制可能会出现大量修改请求失败的情况。

• 乐观锁适合多读少写的场景，由程序实现不存在死锁问题，但是对应程序外的数据库操作无法阻止。
• 悲观锁适合多写少读的场景，由 MySQL 锁机制实现，并发性低。

其它锁：

• 全局锁：对整个数据库加锁，加锁后数据库处于只读状态，其它更新事务全都会被阻塞适合备份场景。

# 加上全局锁
flush tables with read lock;
# 解锁
unlock tables;
1
2
3
4

• 死锁：两个事务都持有对方需要的锁并且等待对方释放锁，双方都不会释放自己的锁，这样两个事务就循环等待。
  1. 等待事务超时（innodb_lock_wait_timeout 默认50s），当事务执行中等待锁时间超过 50s 就会自动回滚事务。
  2. 死锁检测机制，在 innodb 中提供了 wait-for graph 死锁检测算法，一旦检测到死锁就会会滚事务最小的事务。

# 开启死锁检测机制(默认开启)
innodb_deadlock_detect=1
1
2

如何避免死锁？

• 合理设计索引，SQL 语句扫描行尽量少，减少锁住行数。
• 调整业务执行顺序，避免长时间的更新语句在事务前面执行。
• 避免大事务，将事务拆分成小事务执行，减少锁冲突。

锁监控：show status like 'innodb_row_lock%' 参数可以查看锁的一些基本信息。

8.5、MVCC 多版本并发控制

MVCC：通过数据行的多个版本管理实现数据库的并发控制，在 innodb 存储引擎中保证一致性读，为了查询到一些正在被另一个事务更新行更新前的数据。

8.5.1、MVCC 实现原理

• 行记录隐藏字段：
  1. trx_id：一个事务修改记录时，都会把事务 id 赋值给行记录的 trx_id 隐藏字段。
  2. roll_pointer：回滚指针，事务修改记录时都会把旧的版本写入到 undo 日志中，这个隐藏列指向旧版本的数据。
• undo 日志版本链：

ReadView：ReadView 是事务使用 MVCC 机制进行快照读操作产生的读视图，当事务启动时会生成数据库当前快照，为每个事务构建一个数组用来记录系统当前 活跃事务id（启动但未提交的事务）。由于 MVCC 读的是历史快照的数据，所以只对于 READ COMMITTED(读已提交)、REPEATABLE READ(可重复读) 这两个事务级别有效。

• creator_trx_id：创建这个 ReadView 的事务id，只有对表记录进行更新操作时才会分配事务id（依次递增），对于只读事务的事务id默认为0。
• trx_ids：在生成 ReadView 时当前系统中活跃的读写事务的事务id列表。
• up_limit_id：活跃事务中最小事务id。
• low_limit_id：表示生成 ReadView 时系统分配给下一个事务的id，是系统最大的事务id。

ReadView 规则：通过规则判断记录数据的哪个版本是可见的。

• 如果访问版本 trx_id 和 creator_trx_id 相同，表示当前版本是本事务修改过的记录。(可见)
• 如果访问版本 trx_id 小于 ReadView 中的活跃事务最小事务id（up_limit_id），表示当前版本是已提交事务数据。(可见)
• 如果访问版本 trx_id 大于 ReadView 中的 low_limit_id，表示当前版本是生成 ReadView 之后开启的事务。(不可见)
• 如果访问版本 trx_id 在 [up_limit_id，low_limit_id）之间，需要判断是否在 trx_ids 中。
  1. 如果在，表示生成当前版本事务还未提交。(不可见)
  2. 如果不在，表示生成当前版本事务已提交。(可见)

8.5.2、read committed 下的 MVCC

在这个事务隔离级别下，每次读取数据都会生成一个 ReadView，解决了脏读问题但是存在不可重复读问题。

解决脏读问题：

不可重复读问题：

8.5.3、repeatable read 下的 MVCC

在这个事务隔离级别下，只在第一次执行查询操作时生成一个 ReadView，解决了不可重复读和幻读问题。

解决不可重复读问题：

解决幻读问题：在可重复读隔离级别下，MVCC 只在第一次查询时生成 ReadView，对于后面事务插入数据提交后也是不能读取到对应数据，解决了幻读问题。

9、MySQL 日志文件

• 慢查询日志：记录所有执行时间操过 long_query_time 的查询语句，方便优化。
• 通用查询日志：记录所有连接发送给数据库服务器的所有指令，有利于发现问题以及优化数据库。
• 错误日志：记录 MySQL 服务启动、运行、停止时出现的错误，方便我们了解服务器状态。
• 二进制日志：记录所有更改数据的语句，用于主从服务器之间的数据同步以及数据的无损恢复。
• 中继日志：从服务器用来存放主服务器的二进制日志内容的一个中间文件，从服务器读取中继日志内容来同步主服务器上的数据。
• 数据定义语句日志：记录数据定于语句执行的元数据操作。

日志的弊端：

• 日志功能会降低 MySQL 数据库性能，在进行操作时会花费时间去记录日志，像慢查询日志。通用查询日志默认关闭减少消耗。
• 日志会占用大量磁盘空间，有时日志大小比用户数据还要大，因此要定期整理日志。

9.1、通用查询日志和错误日志

通用查询日志：

# 通用查询日志默认是关闭的
show VARIABLES LIKE '%general%';

# 重置通用查询日志
# 先删除日志文件
rm -rf 文件
mysqladmin -uroot -p flush-logs
1
2
3
4
5
6
7

错误日志：

# 默认开启，目录在数据目录下，不同版本的名称不相同
show VARIABLES LIKE '%log_error%';

# 通过文件修改错误日志，需要创建文件并给与权限
[mysqld]
log-error=路径/文件名

# 删除重置错误日志
rm -f 错误日志
# 可能会报错需要先执行下面这个命令
install -omysql -gmysql -m0644 /dev/null 错误日志路径
mysqladmin -uroot -p flush-logs
1
2
3
4
5
6
7
8
9
10
11
12

9.2、bin log 日志

bin log 日志：记录了数据库所有执行更新事件的语句，并以二进制方式保存至文件中。

• 数据恢复，如果 MySQL 意外停止可以通过 bin log 二进制文件记录来恢复数据库数据。
• 数据复制，主机可以通过 bin log 日志传递给从机完成数据一致性，用于数据备份、主备、主主、主从。

# 查看参数设置
show VARIABLES LIKE '%log_bin%';

# 设置参数
[mysqld]
log-bin=文件前缀名
binlog_expire_logs_seconds=过期时间 # 单位秒，默认2592000，30天 
max_binlog_size=100M # bin log 文件最大大小，默认1024MB，操作这个大小会重新生成一个
1
2
3
4
5
6
7
8

查看 bin log 文件：

# 工具查看 bin log 文件，最后n行数据
mysqlbinlog -v 文件路劲 |tail -n

# sql 语句查看bin log文件
show binlog events in '文件名' limit index,size;
1
2
3
4
5

数据恢复：使用 mysqlbinlog 工具通过 bin log 日志恢复数据库数据。

# 通过时间节点去恢复数据
mysqlbinlog --start-datetime='yyyy-MM-dd HH:mm:ss' --stop-datetime='yyyy-MM-dd HH:mm:ss' --database=数据库名 binlog日志 | mysql -uroot -p密码 -v 数据库名
1
2

注意：在恢复数据时建议刷新一下 bin log 日志，因为恢复数据的更新操作也会记录到日志文件中，刷新日志会重新生成一个bin log 日志文件去记录不会影响

删除 bin log 日志：

# 删除当前日志文件之前的日志，不包括当前日志
purge master logs to '日志文件'

# 按照时间去删除
purge master logs before 'yyyyMMdd';

# 删除所有bin log日志，注意不要再生产环境中使用
reset master;
1
2
3
4
5
6
7
8

bin log 写入策略：先把日志写入到 binlog_cache 中，事务提交时再把 binlog_cache 数据写到 binlog 文件中，可以通过设置 binlog_cahce_size 设置单个线程的缓存大小。为了保证事务完整性，如果事务大小超过了 binlog_cahce_size 会将数据暂存到磁盘。

刷盘策略参数：通过 sync_binlog 设置刷盘策略。

• 0：只有每次事务提交时将缓存数据写入到 page cache 中，后续由操作系统完成数据刷盘，可能出现系统宕机数据丢失。
• 1（默认）：每次提交事务时直接进行刷盘操作。
• N( N>1 )：每次提交事务时都写入到 page_cache 中，当提交事务个数达到 N 就执行刷盘操作。

binlog 与 redolog 对比：

• redolog 是物理日志，记录的是真实存储上的数据由存储引擎层生成。而 binlog 是逻辑日志，由Server层产生。
• redolog 侧重保证事务的一致性，而 binlog 侧重保证 MySQL 集群的数据一致性。
• redolog 在事务执行中可以不断写入，而 binlog 只有在事务提交时才进行写入。

两阶段提交：由于 redolog 与 undolog 两份日志之间逻辑不一致，可能出现主机修改a=1并成功持久化 redolog，但是未成功持久化 binlog 日志，那么在主从复制时出现主机与从机数据不一致问题。

为解决两份日志不一致问题，使用两阶段提交，将 redolog 的写入拆分成两个部分 prepare 和 commit 两个阶段，而 binlog 日志的写入在这两个阶段中间执行。

• prepare 阶段出错：直接回滚数据，主机与从机数据一致。
• binlog 阶段出错：在使用 redolog 恢复数据时判断 binlog 写入出错，直接回滚数据，主机与从机数据一致。
• commit 阶段出错：在使用 redolog 恢复数据时判断 binlog 成功写入，即使 commit 出错也会提交事务，主机从机数据一致。

10、MySQL 主从复制

主从复制：提高数据库系统的吞吐量，使系统具有高可用降低系统出现故障的几率。

• 读写分离：写操作在主机上执行，读操作在从机上执行，大大提高系统的并发能力。
• 数据备份：可以通过主从将数据备份到其它机器，当出现故障可以及时恢复数据。
• 系统高可用：当主机宕机后，从机可以切换成主机继续保持系统服务。

主从复制原理：slaver 会从 master 读取 binlog 日志来进行数据同步。slaver 先读取 master 上的 binlog 日志到 slaver 中的中继日志，然后 slaver 会从中继日志中读取并同步 master 上的数据。

• 二进制日志转储线程：master 中的线程，当 slaver 连接时这个线程会将 binlog 发送给 slaver，在这个线程读取数据时会进行加锁操作读取完后会释放锁。
• 从库 IO 线程：连接到 master 并发出同步 binlog 请求，接收到 master 发送的数据后拷贝到 slaver 中的中继日志文件中。
• 从库 SQL 线程：从中继日志中读取数据，并执行日志中的事件将数据同步到 slaver 数据库中。

10.1、一主一从搭建

准备工作：两台虚拟机(192.168.32.131主机、192.168.32.132从机) 并安装相同版本的 MySQL 服务器。

# 主机修改配置文件
# 【必须】
server-id=1 # 数据库id，唯一
log-bin=test # binlog的前缀文件名

# 【可选】
read-only=0 # 服务器是否只读，默认0：读写，1：只读
binlog-ignore-db= # 不同步的数据库名
binlog-do-db= # 需要同步的数据名，默认全部同步
binlog_format= # binlog格式
1
2
3
4
5
6
7
8
9
10

# 从机配置
# 【必须】
server-id=2
# 【可选】
relay-log=test-relay # 中继日志文件名前缀
1
2
3
4
5

创建主从复制账户：

# 在主机上创建用户用于同步数据
create user 'slaver'@'%' identified by '密码'
# 赋予同步数据权限
grant replication slave on *.* to 'slaver'@'%';

# mysql8.0需要执行下面操作
alter user 'slaver'@'%' identified with mysql_native_password by '密码';
flush privileges;

# 查看主机同步状态：
show master status;
1
2
3
4
5
6
7
8
9
10
11

开启从机同步数据：

# 从机创建同步
change master to master_host='主机ip' master_uer='主机用户' master_password='主机密码' master_log_file='binlog具体文件' master_log_pos=同步开始位置

# 例如：
change master to master_host='192.168.32.131',master_uer='slaver',master_password='liuhongjun', master_log_file='test.000001',master_log_pos=1137

# 开启同步
start slave;
# 停止同步
stop slave;
# 如果启动报错，可以尝试删除从机的relaylog日志并重新开启新的relaylog文件
reset slave;

# 查看同步信息
show slave status;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

10.2、binlog 格式

# 查看binlog格式
show VARIABLES LIKE '%binlog_format%';
1
2

• STATEMENT 格式：在 5.7.7 以及之前的默认格式，这种格式每一条修改数据的 SQL 语句会记录到 binlog 中。
• ROW 格式： 在 5.7.7 之后默认格式，这种格式不仅记录了每一条修改数据的 SQL 语句，而且记录 SQL 语句影响的具体数据。
• MIXED 格式：结合 STATEMENT 和 ROW 两种格式，一般的更新语句使用 STATEMENT 格式存储，对于一些函数操作无法使用 STATEMENT 完成主从复制则采用 ROW 格式完成。

三种格式对比：

• STATEMENT 格式不需要记录每一条数据变化，减少 binlog 日志量，而 ROW 格式不仅记录 SQL 语句而且记录每一条具体数据，生成的 binlog 文件较大。
• STATEMENT 格式记录 SQL 语句，对于主从机之间版本要求更加兼容，而 ROW 格式对版本要求更高。
• STATEMENT 格式对于函数来说主从记录可能不同步，而 ROW 记录了具体数据可以同步函数数据。
• STATEMENT 格式在执行更新操作时锁住数据更多，而 ROW 可以直接锁住具体记录。

11、数据备份

• 物理备份：备份数据文件，转储数据库物理文件到新的目录下，物理备份恢复速度快但占用空间大。
• 逻辑备份：备份 sql 语句，恢复时执行备份的 sql 语句实现数据备份，通常使用 mysqldump 工具进行备份。

mysqldump 备份数据：

# 备份指定数据库文件
mysqldump -uroot -h 主机ip -pliuhongjun [数据库1,数据库2] > 备份数据.sql

# 备份全部数据库文件
mysqldump -uroot -h 主机ip -pliuhongjun [--all-databases | -a] > 备份文件.sql

# 备份数据下的部分表
mysqldump -uroot -h 主机ip -pliuhongjun 数据库名 [表名1 表名2] > 备份文件.sql
# 排除不备份的表
mysqldump -uroot -h 主机ip -pliuhongjun 数据库名 --ignore-table=数据库名.表名 > 备份文件.sql
# 只备份表结构不备份数据
mysqldump -uroot -h 主机ip -pliuhongjun 数据库名 --no-data > 备份文件.sql
# 只备份数据不备份表结构
mysqldump -uroot -h 主机ip -pliuhongjun 数据库名 -no-create-info > 备份文件.sql
# 上面备份是不备份存储函数、存储过程以及事件
# 备份存储函数、存储过程以及事件
mysqldump -uroot -h 主机ip -pliuhongjun 数据库名 -R(函数、存储过程) -E(事件) > 备份文件.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

数据恢复：使用备份好的 sql 文件进行恢复数据。

mysql -uroot -pliuhongjun [数据库名] < 备份文件.sql # 导入的数据库名，如何在备份文件中指定了数据库创建，可以不指定

# 全库恢复单库
# 当备份全部数据库，然后需要恢复执行数据库，需要先从all.sql中查询到恢复数据库的数据
sed -n '/^-- Current Database: `数据库名`/,/^-- Current Database: `/p' all.sql > 数据名.sql
# 然后会生成一个sql文件可以直接通过sql文件进行恢复

# 数据库中恢复单表
# 提取表结构
cat 数据库.sql | sed -e '/./{H;S!d;}' -e 'X;/CREATE TABLE `class`/!d;q' > 表结构.sql
cat 数据库.sql | grep --ignore-case 'insert into `class`' > 表数据.sql
# 执行sql语句
source 表结构.sql;
source 表数据.sql;
1
2
3
4
5
6
7
8
9
10
11
12
13
14