MySQL训练营 <1> 从Sql语句执行流程探究Mysql架构
一条查询 SQL 语句是如何执行的
mysql的链接方式有：TCP/IP ; 命名管道和共享内存(window) ;Unix域套接字(Unix，Linux)，查看服务端当前有多少个链接：
show global status like '%Thread%'; mysql 当前链接
Variable_name | Value | 说明 |
Delayed_insert_threads | 0 | |
Performance_schema_thread_classes_lost | 0 | |
Performance_schema_thread_instances_lost | 0 | |
Slow_launch_threads | 0 | |
Threads_cached | 2 | 缓存的线程数 |
Threads_connected | 2 | 已经连接过的线程数 |
Threads_created | 4 | 创建过的线程数 |
Threads_running | 1 | 当前并发的线程数 |
每产生一个链接（会话），服务器端就是创建一个线程来处理这个会话。mysql服务端保持这些链接，会消耗服务器资源，mysql就会把那些长时间不活动的链接断开。那么，超过多少时间会把链接断开？有两个参数
查看链接断开时间：
-- 非交互式超时时间，如JDBC程序
show global VARIABLES like '%wait_timeout%';
-- 交互式超时时间，如数据库工具
show global VARIABLES like '%interactive_timeout%';Variable_name | Value |
wait_timeout | 28800 |
单位是 28800 秒 ，即8个小时，该链接不处于活动状态，那就断开
Variable_name | Value |
interactive_timeout | 28800 |
单位是 28800 秒 ，即8个小时，该链接不处于活动状态，那就断开。
如果需要尽快回收链接，那么这个值应该设置小一点，比如60秒，120秒等等。
查看 mysql 最大默认连接数 ：
show variables like 'max_connections';Variable_name | Value |
max_connections | 151 |
mysql 最大连接数 ： 默认是151， 最小是1个， 最大是100000个
刚刚使用的一些mysql的一些变量：
show global status like '%Thread%';
-- 非交互式超时时间，如JDBC程序
show global VARIABLES like '%wait_timeout%';
-- 交互式超时时间，如数据库工具
show global VARIABLES like '%interactive_timeout%';
show variables like 'max_connections';
show variables like '%isolation%';
set global tansaction_isolation = xxx
其实这些变量都有两个级别， 如果不加【global】 那么就是会话级别。即你打开一个新窗口，或者新链接，就失效了。比如如果修改一个全局的变量， 那么必须要加上 【global】 。 那么set , show 才是全局的。但是这样修改是临时的，如果mysql服务器重启了，那么刚刚设置的就失效了。 mysql 有个配置文件在 /etc/my.cnf 配置文件，可以直接在该配置文件中修改，这样mysql重启服务了，回去读取该配置，重启就不会失效了
MySQL在 5.7 之后默认关闭了缓存
1. 查询缓存 ：
show variables like '%query_cache%'; mysql 缓存信息
Variable_name | Value | 说明 |
have_query_cache | YES | |
query_cache_limit | 1048576 | |
query_cache_min_res_unit | 4096 | |
query_cache_size | 1048576 | |
query_cache_type | OFF | 这个缓存使用，默认是关闭的 |
query_cache_wlock_invalidate | OFF |
mysql 缓存表里如果有一条数据更新，修改，那么缓存就失效了。 或者查询语句有一点点不同，也会查不到缓存。 mysql 8.0 版本已经移除了 查询缓存功能了。
我们写的一条sql语句， mysql如何知道这个语句有没有问题的呢？那就是
2. 解析器 paser：
词法分析，语法分析。
先通过词法分析：
从左到右一个字符、一个字符地输入，然后根据构词规则识别单词。
接下来，进行语法解析，判断输入的这个 SQL 语句是否满足 MySQL 语法.
根据MySQL 定义的语法规则，根据SQL 语句生成一个数据结构，这个数据结构我们把它叫做解析树（select_lex）。
3. 预处理器 pre processer：
得到新的解析树，保证语句没有歧义。 然后被执行。
4. 优化器 optimzer :
一条 sql 语句有不同的执行路径，但是得到的结果是相同的，返回的结果是相同的。一个sql 语句有多种的执行路径，那么该如何选择执行路径？执行方式又该怎么得到呢？
一条 sql 语句
1：优化sql 2: 生成 执行路径 3：选择一个它认为最优的执行路径
优化sql ：多表关联查询时，多个 join ，在服务端是只能先查一张表，然后把查询出来的结果再去查另一张表。在关联查询时，先查哪张表， 优化器会去优化。 多个索引时，判断使用哪个索引。 1=1 恒等式， 优化器会去移除这些 等等...
优化器是基于成本（cost）的优化器，它根据CPU，内存计算成本最小的执行路径
优化器的作用就是找到这其中最好的执行计划
5. 执行计划 execution plans
explain sql 语句 . 告诉你服务器会尝试用什么样的方式去执行这个sql语句。我们可以根据这个信息去优化。
如果想要更加完整的执行信息 explain FORMAT=JSON sql 语句 .返回一串JSON结果，里面可以看到具体的成本分析结果
explain FORMAT=JSON select * from user_innodb;还有一种更加详细的： optimizer trance 把这个命令在服务器端打开， 设置 ON 。可以看到优化器生成了哪几个执行计划，选择了哪种执行计划执行 SQL 语句。
6. 执行器 Executor
执行器拿到执行计划 去操作存储引擎里的数据
查看数据放在哪个路径下：
show variables like 'datadir';输出结果：
Variable_name | Value |
datadir | C:\phpstudy_pro\Extensions\MySQL5.7.26\data\ |
在这个目录下C:\phpstudy_pro\Extensions\MySQL5.7.26\data\ 每个数据库会有一个文件夹，我这的是test文件夹（数据库）
可以看到不同的存储引擎，会有不同的数量文件存在磁盘中。 frm代表是表结构文件
Innodb存储引擎：事务安全的（ACID），行级别索引，支持崩溃恢复，有聚集索引减少磁盘IO
Mysiam：表级别锁，读很快。
memory：数据放在内存中，只能用临时表
CSV：迁移数据
服务层过滤数据，解析，优化。索引是在存储引擎里的。
一条更新语句如何执行
mysql 中两大类的操作， select , update 。这个update 是更新的意思它包含了 insert，update，delete。
更新一条语句，首先得查询，从磁盘里加载到 存储引擎的内存，再从存储引擎加载到 server（服务） 层，在服务层这边修改。
mysql有个预读取的概念，是一块一块拿附近的数据。数据从磁盘加载到内存中，读取固定大小的单位， 数据页（page）的概念。mysql 数据页的大小是16KB .
Innodb buffer pool
在存储引擎的内存中，有一个缓冲池 buffer pool ,前面加载过的数据页会放在这个缓冲池中。 客户端来读取数据，会先从缓冲池中读取数据，如果缓冲池中刚好有需要的数据页，就把pool中数据页返回。就不需要去磁盘中去拿了。
当server（服务）层修改了数据页的一条数据，也是先把结果写到内存的 buffer pool 。 InnoDB buffer pool 是提升读写性能的一个关键点。在某些数据库专用的服务器上 buffer poo 可以占到内存的80%。 服务器100G内存，buffer pool可以达到80G。
当服务层修改了某个数据页的一条数据，也是先写到 buffer pool 中（再到磁盘中），那么就会与磁盘中的数据不一致了。此时在buffer pool 中修改过数据的那个页就叫做 dirty page (脏页），把脏页数据同步到磁盘中去的这个动作就叫做刷脏 。 这个是由后台线程来做的。
查看 Innodb buffer pool 的大小： Linux上默认大小是128M，这个可以调整
buffer pool 数据丢失
当 buffer pool 中的脏页还没来及同步到磁盘中时，此时服务器故障挂掉，或者断电了。内存中的数据就会丢失了吗？innodb中设置了一个日志文件叫redo log（中文名叫重做日志） 。但凡写到 buffer pool 中的数据都要写到 redo log 日志中，而这个日志记录就保存在磁盘中。redo log 最大的作用就是解决崩溃恢复的作用。假设断电了，内存中的脏页数据丢失了，就可以通过 redolog写到磁盘中去。ACID 中的持久性就是依靠 redolog
为何多此一举把数据写到 redolog 日志文件中，而不直接写到磁盘中？数据写在磁盘中和写在日志文件中区别在哪里？
操作系统中操作磁盘数据时候， i/o 有两种类型：
随机I/O: 一个页16KB 这里面包含很多条数据，数据是很分散的，随机的，需要寻址。 是很消耗性能的
顺序I/O: 数据是连续的。不需要寻址，直接在后面 append。根本就没有寻址的过程。 顺序IO在某一些情况下性能大过内存
所以 为什么需要先把数据写到日志文件，再通过后台线程写到磁盘中。
redolog 存放位置及大小设置
通过 show variables like 'datadir' 查看目录下，有两个文件，就是 redolog日志文件
redolog 配置信息 ： show variables like '%innodb_log%';
inno_db_log_files_in_group = 2，把 redolog 默认是切分成两个组 这个可以修改。默认大小是48M，那每个组就是24M。如果设置redolog大小是4个G，切分为4个组，那么每个组大小就是1G.
redolog 几个日志文件形成一个环形，当日志写满时也代表无法为 buffer pool 做后续操作的日志写入，此时会触发后台线程的刷脏动作写入到磁盘中（buffer pool 数据同步到磁盘中）。日志写满后会覆盖前面的日志数据。所以redolog做不了数据恢复。其实数据写到 redo log 这之间还有个 log buffer 日志缓冲区，并不是一下直接写到 日志文件中的。 图中是切成4个组。
redo log 特点：
- 记录数据页的改动，属于物理日志（解读：物理日志是一个绝对的改动，在哪一个数据页做了哪个具体的修改，记录的是这个东西）
- 大小固定，前面的内容会被覆盖
- 在 InnoDB 存储引擎层实现（所以其他引擎也带不来崩溃恢复的特性）
- 用于崩溃恢复
undo log
InnoDB 中还设计了另一个日志叫 undo log . 我们叫它撤销日志或者回滚日志，记录的是数据修改之前的数据。undo log 是逻辑日志
redo log 和 undo log 合起来叫做事务日志
insert，update，delete操作会产生记录 undo log 。 undo log 可以用来实现回滚操作 ------ 回滚可以保持事务的原子性。
如果要执行一个恢复的操作，undo log 是在逻辑上把一个事务恢复到原来的状态。 undo log 是做一个反向的操作
insert 操作 undolog 中会有一个反向的记录 delete。update 操作 undolog中有一个反向的update操作，是一个逻辑格式的日志。
undo log 默认是放在一个 ibdata1（ibdata1叫系统表空间，很多系统共享的数据就放在这里面） 文件中。查看 undo log 参数：show variables like '%undo%';(本机是8.0的版本)
（这是5.7.1版本）
有了 redo log 和undo log 简单总结下一条更新语句流程：
假设 user 表 有条数据 name = 'qingshan'，现在改为 name = ‘penyuyan’。 更新语句会自带事务，开启事务，在事务里进行操作。这是极其简化版：
- 事务开启。从 磁盘数据 加载到 存储引擎的内存RAM（即 buffer pool）,（如果这个数据页本身已经在 内存中了，就不要读磁盘了），然后在 Server 层操作数据
- Server 层拿到这个数据页，把 name = 'qingshan' 这条数据 修改成 name = ‘penyuyan’
- 记录 undo log 和 redo log
- 调用存储引擎API ， 写入到 buffer pool
- 事务提交
后台线程
后台线程的主要作用是负责刷新内存池中的数据和把修改的数据页刷新到磁盘。后 台线程分为：master thread，IO thread，purge thread，page cleaner thread。
InnoDB 存储引擎和磁盘交互结构图
左侧存储引擎内存，小方块就是页，mysql会为热点的页建立一个索引放在自适应的hashmap中。
buffer pool 内存满了后，有一个 LRU算法， 八分之五 是热区，3/8是冷区，冷区的会优先淘汰
binlog
除了 InnoDB 架构中的日志文件，MySQL 的 Server 层也有一个日志文件，叫做 binlog（二进制日志），它可以被所有的存储引擎使用。
binlog 是二进制日志，是在服务层的。那就意味着其他的存储引擎也能用到它。 binlog默认是关闭的，开启会消耗服务器性能。
binlog 以事件的形式记录了所有的 DDL 和 DML 语句（建表，删表，增删改的语句），比如“给 ID=1 这一行的 count 字段加 1 ”,因为它记录的是操作而不是数据值，属于 逻辑 日志）。
binlog 特性：
- 记录DDL和DML语句，属于逻辑日志
- 没有固定大小，内容可以追加
- Server层实现，可以被所有存储引擎使用
- 用于数据恢复和主从辅助
binlog 有两个非常重要的作用：
- 主从复制 (主从复制的原理就是从服务器读取主服务器的 binlog，然后执行一遍)
- 数据恢复 (我们可以把 binlog 导出成 SQL 语句，把所有的操作重放一遍，来实现数据的恢复)
主从复制原理：Slave 上一个 i/O线程 先发起请求，去请求Master上的 二进制日志 ，Mater 把准备好的 binlog 通过后台线程 Log Dump Thread 响应给 Slave 。Slave 先写到 relay log 中继日志， SQL Thread 去中继日志中解析出来，所有的sql语句在 Slave中重放一遍。就实现了主从复制
binlog实现数据的恢复前提是，有数据库的全量备份。
举例：每天凌晨 1 点做数据库的全量备份，上午 9 点，数据库被删除跑路了。
首先，恢复到凌晨 1 点的数据。然后利用 1 点到 9 点之间的 binlog，剔除 drop， 恢复数据。
开启binlog 功能： /etc/my.cnf
[mysqld]
# binlog 配置
#binlog日志文件的名字为mysql-bin，以及其存储路径
log-bin=/var/lib/mysql/mysql-bin
#选择row模式
binlog-format=ROW
#server-id表示单个结点的id,若集群中有多个结点，则id不能相同。配置mysql replaction需要定义，不能和canal的slaveId重复.
server_id=1
mysql8.0 版本 查看binlog 是否开启：
show variables like 'log_bin';Variable_name | Value |
log_bin | ON |
有了redolog 、binlog 这两个日志之后，我们来看一下一条更新语句是怎么执行的（这里省略了 undo）：
例如一条语句：update user set name='盆鱼宴' where id=1;
- 从buffer pool 或者磁盘加载数据
- 在 server 把 set name='盆鱼宴'
- 调用存储引擎API 把数据页 写到 buffer pool
- 记录 操作日志 到 redolog , 并将这行记录状态设置为 prepare
- 通知Server层提交事务，提交事务前， 写入 binlog
- 提交事务
- 把 redolog 里这个事务的相关记录状态设置为 commit 状态
先写入 redolog 并将这行记录状态设置为 prepare， 再写入 binlog，提交事务后，redolog 设置为 commit 状态。
为什么 redolog 要分成两次？ 假设 redolog 在 prepare那一步直接写入成功，但是 binlog写入失败会怎么样？
一个事务的成功是由binlog是否写入成功决定的
