SQL执行底层原理

MySQL内部结构

MySQL大致可以分为Server层和存储引擎层，我们更多接触到的都是Server层。

Server层

Server层包括连接器、查询缓存、词法分析器、优化器、执行器等，以及所有内置函数（日期、时间、加减法、函数）
存储过程、触发器、视图都是在Server实现

连接器

连接器作用

客户端与MySQL服务端的连接建立和断开
- 使用TCP协议，用的Socket套接字
- MySQL客户端和服务端采用半双工的通信方式
  - 数据传输可以双向通信，但是同一时间只允许数据在一个方向传输，同一时间只能发送或接受数据。
对客户端的连接进行权限校验
- 校验客户端IP是否允许访问
- 校验连接的用户是否具备访问权限、用户密码是否正确

连接器校验过程

连接器收到客户端的连接，完成TCP三次握手
校验客户端IP是否允许访问，如果不允许则返回失败
连接器进行用户和密码校验
1. 校验失败：则返回 Access denied for user，执行结束。
2. 校验成功：连接器会到权限表（MySQL-User）查询用户权限，在该连接中开辟临时内存空间，并把用户权限load到内存中
3. 当有SQL命令在连接中执行，会从连接的内存读取权限进行判断，用户是否存在相应权限。

注意：当用户建立连接后，如果修改了该用户的权限，已经建立的连接的内存也不会实时刷新（旧的权限），只有重新建立连接才会读取最新的权限设置。

查看连接进程

mysql> show PROCESSLIST;
通过该命令可以查询到连接到该数据库的连接，并可直观的看到连接最近的命令（Command），以及连接时间（Time）和连接状态（State）。
kill [id] ，通过该命令可以直接结束掉MySQL的连接。
- mysql> kill 5

![查看连接进程][image-59]

查看连接超时时间

mysql> show global VARIABLES like 'wait_timeout'
默认超时时间8小时（28800秒）

MySQL缓存

缓存功能默认是关闭的，如果开启后，当我们首次执该SQL时会以key（SQL为key）-value（结果集为value）的形式缓存内存，当再次执行该SQL会直接返回该SQL结果。

缓存过程（开启了缓存）

客户端发起查询SQL时，连接器会先去缓存中查询，缓存是否存在该SQL的结果集

如果存在则直接返回给客户端
如果不存在，在通过词法分析器进行分析词法，在通过MySQL优化器根据执行计划选择索引，最后由执行器调用引擎接口获取查询结果，把结果集到缓存后并返回给客户端

因功能较为鸡肋，在MySQL 8.0及以上版本已经移除了缓存机制

鸡肋原因：

查询缓存失效非常频繁，当我们对一个表进行了更新，这个表的所有查询缓存会被清空，很可能你缓存了一堆东西还没有使用，表更新新的数据后又给你清空了。
InnoDB也存在了Buffer Pool 缓冲池，以页为单位，每页默认16KB，该缓冲区专门缓存数据页和索引页。

按需缓存（推荐）

一般缓存系统表，菜单表，权限表，字典表等长时间不会变更数据的表，我们可以把MySQL的query_cache_type设置成2，这样默认查询SQL不缓存，只要我们通过关键字才会进行缓存 SQL_CACHE 。

缓存命令

开启缓存

找到MySQL配置文件（my.cnf）

1 2	#query_cache_type有3个值 0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE 关键词时才缓存 query_cache_type=2

SQL_CACHE 按需缓存，我们传入关键字就可以触发缓存
- mysql> select SQL_CACHE * from test where ID=5；

查看是否开启缓存
- mysql> show global variables like "%query_cache_type%";
监控缓存命中状态
- mysql> show status like'%Qcache%';
- Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片
  
  过多了，可能在一定的时间进行整理。
- Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多
  
  了，还是不够用，DBA可以根据实际情况做出调整。
- Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
- Qcache_inserts: 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行
  
  查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理
  
  想。当然系统刚启动后，查询缓存是空的，这很正常。
- Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的
  
  调整缓存大小。
- Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。
- Qcache_queries_in_cache:当前缓存中缓存的查询数量。
- Qcache_total_blocks:当前缓存的block数量。

#####

分析器

如果没有命中缓存（或开启缓存），则真正开始执行语句。

词法分析步骤

词法分析：
语法分析
语义分析
构造执行树
生成执行计划
执行计划

词法分析过程

通过词法分析器（算法：MySQLLex）完成对SQL词法的分析
根据词法分析的结果，语法分析器（算法：Bison）判断SQL是否满足MySQL语法，如果不满足则会返回错误提示
1. select * fro test;
2. ![语法错误][image-60]
语法分析器校验成功后，通过Bison算法生成一颗语法树，然后从根节点一步一步往下生成执行计划。
1. ![语法树][image-61]

优化器

优化器作用

优化器会根据优化算法，当前SQL决定是否走索引，如果存在多个索引的话，走哪个索引
如果有关联表查询，优化器会判断用哪张表来作为驱动表
构建执行计划

执行器

判断你的用户对这个表是否有相应的权限，如果没有则返回权限错误
如果有权限，通过该SQL词法分析的结果，判断是否走索引，还是走全表扫描
打开该表，通过存储引擎的接口获取查询结果

BinLog日志

当我们开启了BinLog日志后，我们的每条SQL执行都会以二进制的形式写入到日志中。

BinLog特点

BinLog是在MySQL业务层（Server）实现的。
BinLog是逻辑日志，记录的是一条完整语句的逻辑日志。
BinLog不限大小，追加写入，不会覆盖之前的日志。

如果我们误删了数据库，我们可以通过BinLog日志来恢复删除的数据。

BinLog开启

BinLog默认是关闭状态，可通过my.cnf开启。

配置my.cnf：

log‐bin=/usr/local/mysql/data/binlog/mysql‐bin 
#binlog格式，有3种statement,row,mixed 
binlog‐format=ROW 
#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定 
sync‐binlog=1

binlog格式

statement
- 记录逻辑语句，缺点：主从复制的时候容易出现数据不一致
- 使用场景
- 不设置主从复制
row（推荐）
- 记录SQL执行后的那一行数据
- 使用场景
- 适用于主从复制，数据强一致性
- 市场上很多解析工具，默认都是解析row
mixed （智能判断，对数据库性能比较友好）
- MySQL引擎判断当前的SQL不会产生一致性问题，则走statement，如果会产生一致性问题则走row

BinLog命令

mysql> show variables like '%log_bin%'; 查看bin‐log是否开启
mysql> flush logs; 会多一个最新的bin‐log日志，下一条SQL将写入到新的bin-log日志
mysql> show master status; 查看最后一个bin‐log日志的相关信息
mysql> reset master; 清空所有的bin‐log日志

查看BinLog日志
mysql> /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001

BinLog数据恢复

BinLog日志是由二进制组成，不具备可读性，为此我们需要通过MySQL自带的工具来进行恢复数据。

恢复方式：

整个日志恢复全部数据
1. /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p xing(数据库名)
恢复指定位置范围数据
1. 我们需要观察，begin,commit关键字，根据我们的语句，找到begin，commit的end_log_pos的开始位置和结束位置，才可以准确定位到我们需要恢复的位置范围
2. ![恢复指定位置范围数据][image-62]
3. /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults ‐‐start‐position="0" ‐‐stop‐position="492" /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p xing(数据库)
恢复指定时间范围数据
1. /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 ‐‐stop‐date= "2018‐03‐02 12:00:00" ‐‐start‐date= "2019‐03‐02 11:55:00"|mysql ‐uroot ‐p test(数据库)