SQL在MySQL中执行流程（三）

数据库笔记

• Mysql索引底层（一）
• Explain工具和索引优化（二）
• SQL在MySQL中执行流程（三）
• MySQL索引优化（四）
• MySQL事务与锁 （五）
• MVCC和BufferPool（六）

SQL在MySQL执行过程

MySQL内部组件结构

Server层

1. 定义

server层是涵盖MySQL大多数核心服务功能的一层。内置函数、跨存储引擎功能都在这一层实现。【存储过程、触发器、视图】

2. 组成

• 连接器 —— 通过TCP链接🔗和权限校验
• 查询缓存 —— 缓存查询过的SQL结果
• 词法分析器 —— 词法分析、语法分析
• 优化器 —— 执行计划生成、索引选择
• 执行器 —— 调用引擎接口、获取查询结果

一、连接器

1. 定义

连接器用于管理各种客户端对MySQL发起的连接，负责客户端与服务器端建立通信连接。

2. 流程

• 发起连接🔗

  # 连接命令
  mysql -h host[数据库地址] -u root[用户] -p root[密码] -p 3306
  
  # 完成经典的TCP三次握手操作
  

• 校验用户名和密码

  用户名或密码

  • ❌：返回 "Access denied for user"的❌，客户端结束执行
  • ✔：查询权限表，将权限存入本次连接的session中

3. 特点

• 建立连接后，权限不会被修改

  因为连接器将用户权限写在本次连接的session中，当管理员账号对该用户的权限进行修改，也不会影响到session中的用户权限，只有断开连接【重启MySQL服务等】，才能使新权限生效。

  用户的权限表在mysql.user表中

• ==无交互的连接超时,==自动断开

  通过全局变量 wait_timeout 来控制全局服务器关闭无交互的连接之前等待的秒数

  断开后，客户端发送请求会提示：Lost connection to MySQL server during query

• 长连接在Pool内管理，导致OOM

  为什么导致OOM？

  答：因为MySQL在执行过程中使用的临时资源是存放在连接对象里面的，这些资源在连接断开的时候才会释放，长连接累积下来，可能会导致OOM

  如何解决这类问题？

  答：

  1. 定期断开长连接 或者 程序执行过一个占用内存的大查询后，断开连接【释放资源】，之后要查询再重新连接。
  2. MySQL5.7或更高版本，程序每执行一次比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源，不需要重连和权限验证。

二、查询缓存

1. 定义

MySQL在拿到一条查询SQL后，会将该SQL作为key，去查询缓存 【会做权限校验】

查找到缓存，则直接返回给客户端；

查找不到缓存，则执行后面的阶段，完成后，将执行结果存入到缓存中。

2. 流程

• 开启查询缓存

  # 修改配置文件 my.cnf
  # query_cache_type = [0|1|2]
  # 0：关闭缓存
  # 1：开启缓存
  # 2：DEMAND 存在SQL_CACHE关键词进行缓存
  

• 假如选择DEMAND

   select SQL_CACHE * from test where ID=5;
   show status like'%Qcache%'; //查看运行的缓存信息
  

字段含义：

• Qcache_free_blocks：表示查询缓存中剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理

• Qcache_free_memory：查询缓存的内存大小

  • Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想
  • Qcache_inserts：表示多少次未命中然后插入，新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。insert的情况次数越多，表示查询缓存命中少
  • Qcache_lowmem_prunes：该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小
  • Qcache_not_cached：表示因为query_cache_type的设置【2】而没有被缓存的查询数量
  • Qcache_queries_in_cache:当前缓存中缓存的查询数量
  • Qcache_total_blocks:当前缓存的block数量

三、词法分析器

作用：

用于分析SQL语句的分析、构造执行树、生成执行计划

SQL语句的分析分为词法分析与语法分析

• 词法分析由MySQLLex【MySQL自己实现的】完成

• 语法分析由Bison生成

语法树：

四、优化器

作用：

优化器是在表中有多个索引的时候，决定先使用哪个索引 【索引选择】

一个语句有多表关联（join），决定表的连接顺序 【连接顺序选择】

例子：

select * from test1 join test2 using(ID) where test1.name = 'yangguo' and test2.name = 'xiaolongnv';

## 既可以先从表 test1 里面取出 name=yangguo的记录的 ID 值，再根据 ID 值关联到表 test2，再判断 test2 里面 name的值是否等于 yangguo

## 也可以先从表 test2 里面取出 name=xiaolongnv 的记录的 ID 值，再根据 ID 值关联到 test1，再判断 test1 里面 name的值是否等于 yangguo

### 效率不同，由优化器决定！

五、执行器

作用：

开始执行前，判断用户对表T有没有查询权限 【权限校验】

调用表的引擎提供的接口，获取数据 【调用引擎】

流程：

select * from test where id = 10;

比如我们这个例子中的表 test 中，ID 字段没有索引，那么执行器的执行流程是这样的：

1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。 至此，这个语句就执行完成了

对于有索引的表：

1. 第一次调用的是“取满足条件的第一行”这个接口
2. 之后循环取“满足条件的下一行”这个接口
3. 数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值表示执行器调用次数。
4. 在有些场景下，执行器调用一次，在引擎内部则扫描了多行，【有序】因此引擎扫描行数跟 rows_examined 并不是完全相同的

bin-log归档

1.定义

bin-log在Server层实现的二进制日志，会记录CUD操作，SQL执行时，sql语句的执行逻辑记录存放在bin-log中

2. 特点

• 与引擎共用
• 逻辑日志，记录是语句的原始逻辑
• 追加写入，不会覆盖以前的日志

3. 使用

• 配置my.cnf

  #配置开启binlog
  log‐bin=/usr/local/mysql/data/binlog/mysql‐bin
  #注意5.7以及更高版本需要配置本项：server‐id=123454（自定义,保证唯一性）;
  
  # binlog格式，有3种statement,row,mixed	
  # statement：记录语句的逻辑 【你做了啥】
  #	- 有可能导致主从不一致
  # row：记录 语句影响记录 的结果 【记录你做之前的结果】
  # 	- 记录的数据量更大
  # mixed：由优化器决定
  binlog‐format=ROW
  
  # 1表示，每一次执行写入就与硬盘同步，会影响性能
  # 0表示，事务提交时mysql不做刷盘操作，由系统决定
  sync‐binlog=1
  

• binlog命令

  show variables like '%log_bin%'; # 查看bin‐log是否开启
  flush logs; # 会多一个最新的bin‐log日志
  show master status; # 查看最后一个bin‐log日志的相关信息
  reset master; # 清空所有的bin‐log日志
  

• 查看bin-log

  /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 # 查看binlog内容
  
  ## 在bin-log中关键找到begin、commit的事务关键词，根据位置position数字🔢进行恢复。
  

• 数据归档操作

  # 从bin‐log恢复数据
  ## 恢复全部数据
  /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p tuling(数据库名)
  
  ## 恢复指定位置数据
  /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults ‐‐start‐position="408" ‐‐stop‐position="731" /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p tuling(数据库)
  
  ## 恢复指定时间段数据
  /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(数据库)
  

Store层

1. 定义

store层是负责数据的存储和提取，是与磁盘交互的一层。插件式架构，MySQL5.5.5版本后，默认存储引擎为InnoDB

一条SQL的执行过程（服务层）

1. 通过命令 mysql -h{port} -u -p 进行连接，**通过连接器连接到服务器端，**连接成功后，权限变更不会影响到当前连接，默认连接有效期为8小时，应及时重置连接资源，防止OOM
2. 当查询缓存开启，以SQL为key，**去查询缓存中查询，并校验用户对表的权限，**有权且命中则返回
3. 没有缓存命中，通过分析器分析语法和词法，生成执行树
4. 通过优化器对索引以及多表连接的顺序进行选择
5. 通过执行器，==校验用户对表的权限，==调用存储引擎接口，按语法循环执行，返回结果集，rows_examined 可以观察执行器执行次数。

一条SQL的执行过程（引擎层）