sql语句的执行流程

系统管理员 2022-12-15 15:29 157阅读 0赞

### sql语句在mysql中的执行流程 ###

*  Mysql的基本逻辑结构示意图
 *  用户权限
 *  mysql分层
 *  Mysql的缓存机制
 *  解析器：
 *  预处理器 pre processer：
 *  优化器 optimzer
 *   *  优化器可以做什么？
     *  优化器是怎么得到执行计划的
 *  mysql日志模块
 *   *  Bin Log归档日志
     *   *  二进制日志
         *  Bin Log设置
         *  恢复日志

一条sql语句在mysql中的执行流程

# Mysql的基本逻辑结构示意图 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center]

win下查看mysql数据文件存储位置  
show global variables like ‘%datadir%’;

# 用户权限 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 1]  
这里注意，当已经建立了连接后，在mysql-user表里面修改了用户的权限后，已经建立的连接权限是不会受影响的。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 2]

# mysql分层 #

主要是分三层

*  客户端  
    navicat，sqlyog之类的
 *  server层：

连接器、查询缓存、分析器、优化器、执行器  
内置函数：日期、时间、数学、加密函数等  
跨存储引擎功能：存储过程、触发器、视图等。

*  存储引擎层：

# Mysql的缓存机制 #

缓存数据形式key-value,key为查询SQL  
试用场景，读多写少，准确的说是几乎不怎么写  
需要在my.cnf中配置query\_cache\_type参数（0,1,2）,这个默认在5.7后版本是关闭的  
按需使用缓存：select SQL\_CACHE \* from test  
查看缓存运行状态： show status like ‘%Qcache%’  
查询缓存：show variables like ‘%query\_cache%’;  
参考：https://www.cnblogs.com/yueyun00/p/10898677.html  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 4]

# 解析器： #

词法分析，语法分析。

先通过词法分析：

从左到右一个字符、一个字符地输入，然后根据构词规则识别单词。

接下来，进行语法解析，判断输入的这个 SQL 语句是否满足 MySQL 语法.

根据MySQL 定义的语法规则，根据SQL 语句生成一个数据结构，这个数据结构我们把它叫做解析树（select\_lex）。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 5]

# 预处理器 pre processer： #

得到新的解析树，保证语句没有歧义。然后执行。

# 优化器 optimzer #

一条sql语句有不同的执行路径，但是得到的结果是相同的，返回结果是相同的。一个sql语句有多种执行路径，那么该如何选择执行路径？ 执行方式又该怎么得到呢？

一条sql语句  
1：优化sql 2：生成 执行路径 3：选择一个它认为最优的执行路径

**优化sql** ：多表关联查询时，多个join，在服务端是智能先查一张表，然后查询出来的结果再去查另外一张表。在关联查询时，先查哪张表，优化器会去优化。多个索引时，判断使用哪个索引。1=1恒等式，优化器会去除这些 等等…

优化器是基于成本cost 的优化器，它根据CPU，内存计算成本最小的执行路径  
优化器的作用就是找到这其中最好的执行计划。

## 优化器可以做什么？ ##

Mysql的优化器能处理哪些优化类型呢？  
eg：  
1，当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表。  
2，有多个索引可以使用的时候，选择哪个索引。  
实际上，对于每一种数据库来说，优化器的模块都是必不可少的，他们通过复杂的算法实现尽可能优化查询效率的目标。

## 优化器是怎么得到执行计划的 ##

首先我们要启用优化器的追踪（默认是关闭的）  
[https://dev.mysql.com/doc/internals/en/optimizer-tracing.html][https_dev.mysql.com_doc_internals_en_optimizer-tracing.html]

SHOW VARIABLES LIKE 'optimizer_trace';
     set optimizer_trace='enabled=on';

# mysql日志模块 #

## Bin Log归档日志 ##

### 二进制日志 ###

Binlog在Mysql的Server层实现（引擎公用）  
Binlog为逻辑日志，记录的是一条语句的原始逻辑  
Binlog日志不限大小，追加写入，不会覆盖以前的日志

### Bin Log设置 ###

*  开启bin-log
    
    查看bin-log是否开启：show variables like ‘%log\_bin%’;  
    如果是关闭状态的话  
    window下修改my.ini文件 C:\\Program Files\\MySQL\\MySQL Server 5.5在安装mysql的根目录下，linux的话修改my.conf

log-bin=mysql-bin
    binlog-format=Row
    
    server-id=125

然后再C:\ProgramData\MySQL\MySQL Server 5.5\data文件目录下可以看到

![在这里插入图片描述][2020101813580165.png_pic_center]

*  window查看binlog:

win+r => cmd => 连接mysql=> show binary logs; =>可以查看自己binlog的名称

win+r => cmd => 连接mysql=> show binlog events; =>可以查看已生成的binlog

*  bin-log命令  
    sync\_binlog:设置为1，表示每次事务binlog都将持久化到磁盘  
    flush logs：会多一个最新的bin-log日志  
    show master status：查看最后一个bin-log日志的相关信息  
    reset master：清空所有的bin-log日志
 *  window下查看binlog日志：

mysqlbinlog “C:\\ProgramData\\MySQL\\MySQL Server 5.5\\data\\mysql-bin.000001” | mysql -u root -p test

mysqlbinlog “C:\\ProgramData\\MySQL\\MySQL Server 5.5\\data\\mysql-bin.000001”  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 6]

### 恢复日志 ###

1.  新增一个日志文件： flush logs;

![在这里插入图片描述][20201018143740992.png_pic_center]

![在这里插入图片描述][20201018143801198.png_pic_center]

1.  清空一个表里面的所有数据： truncate test.user\_info;
2.  查看指定binlog文件的内容

show binlog events in ‘mysql-bin.000002’;  
show binlog events in ‘SZ-PD-604177-bin.000010’;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 7]

1.  回滚 然后使用开始位置和结束位置来恢复数据，执行以下命令  
    执行语句：  
    mysqlbinlog --start-position=4 --stop-position=1710 “C:\\ProgramData\\MySQL\\MySQL Server 5.5\\data\\mysql-bin.000002” > d:\\test1.sql  
    ![在这里插入图片描述][20201018150951866.png_pic_center]

然后再D盘我们能看到这个文件  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 8]

1.  (这里是用我们binlog备份的test1.sql去恢复数据哦)：  
    source d://test1.sql  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 9]

最后我们看到我们的日志恢复了  
![在这里插入图片描述][20201018151302322.png_pic_center]

C:\Program Files\MySQL\MySQL Server 8.0\bin
    
    
    mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 8.0\Data\SZ-PD-604177-bin.000010" | mysql -u root -p test
    
    
    
    show binlog events in 'SZ-PD-604177-bin.000010';
    
    truncate test.user_info;
    
    mysqlbinlog --start-position=4 --stop-position=479 “C:\ProgramData\MySQL\MySQL Server 8.0\Data\SZ-PD-604177-bin.000010” > d:\test1.sql
    
    source d://test1.sql
    
    INSERT INTO `test`.`user_info`(`id`, `name`, `age`, `skill`, `evaluate`, `fraction`) VALUES (1, '小明', 20, '画画', '该学生在画画方面有一定天赋', 89);
    INSERT INTO `test`.`user_info`(`id`, `name`, `age`, `skill`, `evaluate`, `fraction`) VALUES (2, '小兰', 19, '游戏', '近期该学生由于游戏的原因导致分数降低了', 64);
    INSERT INTO `test`.`user_info`(`id`, `name`, `age`, `skill`, `evaluate`, `fraction`) VALUES (3, '张张', 18, '英语', '近期该学生参加英语比赛获得二等奖', 90);
    INSERT INTO `test`.`user_info`(`id`, `name`, `age`, `skill`, `evaluate`, `fraction`) VALUES (4, '大黄', 20, '体育', '该学生近期由于参加篮球比赛,导致脚伤', 76);
    INSERT INTO `test`.`user_info`(`id`, `name`, `age`, `skill`, `evaluate`, `fraction`) VALUES (5, '大白', 17, '绘画', '该学生参加美术大赛获得三等奖', 77);
    INSERT INTO `test`.`user_info`(`id`, `name`, `age`, `skill`, `evaluate`, `fraction`) VALUES (7, '小龙', 18, 'JAVA', '该学生是一个在改BUG的码农', 59);
    INSERT INTO `test`.`user_info`(`id`, `name`, `age`, `skill`, `evaluate`, `fraction`) VALUES (9, 'Sans', 18, '睡觉', 'Sans是一个爱睡觉,并且身材较矮骨骼巨大的骷髅小胖子', 60);
    INSERT INTO `test`.`user_info`(`id`, `name`, `age`, `skill`, `evaluate`, `fraction`) VALUES (10, 'papyrus', 18, 'JAVA', 'Papyrus是一个讲话大声、个性张扬的骷髅，给人自信、有魅力的骷髅小瘦子', 58);
    INSERT INTO `test`.`user_info`(`id`, `name`, `age`, `skill`, `evaluate`, `fraction`) VALUES (11, '删除数据1', 3, '画肖像', NULL, 61);
    INSERT INTO `test`.`user_info`(`id`, `name`, `age`, `skill`, `evaluate`, `fraction`) VALUES (12, '删除数据2', 3, NULL, NULL, 61);
    INSERT INTO `test`.`user_info`(`id`, `name`, `age`, `skill`, `evaluate`, `fraction`) VALUES (13, '删除数据3', 3, NULL, NULL, 61);
    INSERT INTO `test`.`user_info`(`id`, `name`, `age`, `skill`, `evaluate`, `fraction`) VALUES (14, '删除数据4', 5, '删除', NULL, 10);
    INSERT INTO `test`.`user_info`(`id`, `name`, `age`, `skill`, `evaluate`, `fraction`) VALUES (15, '删除数据5', 6, '删除', NULL, 10);
    INSERT INTO `test`.`user_info`(`id`, `name`, `age`, `skill`, `evaluate`, `fraction`) VALUES (16, '删1111', 6, '删除', NULL, 10);

Redo日志

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/0bad2a11b3924b46aa2d3d7e59ffa141.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/b88ec8527f3b425fbb1b3b8c2c820ba3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/3cd7667d1f144fe69480b77fd84d795d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221123/7c1882779039449690554406826f05d9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221123/e2edae7ec070440784d6b6cc9acd0dd0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221123/f90275ff7f3e47e6a017395ab84f459a.png
[https_dev.mysql.com_doc_internals_en_optimizer-tracing.html]: https://dev.mysql.com/doc/internals/en/optimizer-tracing.html
[2020101813580165.png_pic_center]: /images/20221123/6d9175a82f08468db69c3ba4e641a60d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221123/ba0c1bcec3fa40e491b4610d1cc3e854.png
[20201018143740992.png_pic_center]: /images/20221123/619d36f3781d4433840724654baac661.png
[20201018143801198.png_pic_center]: /images/20221123/1dfed3c27cb644439a285fb30ee61a9a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221123/3378e01d1f9b4175ad8bb7d376331b5a.png
[20201018150951866.png_pic_center]: /images/20221123/df54534fa08e471e83462140504b0188.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20221123/a1b1f6f45f60449bb609858618ac8908.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zMDQwOTkyNw_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20221123/14bc4504261c47c19527cdbf767dfce9.png
[20201018151302322.png_pic_center]: /images/20221123/aa34d8a3fc4c45518cb57a614b9d7a19.png