sql语句的执行流程

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

Mysql的基本逻辑结构示意图

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

用户权限

这里注意，当已经建立了连接后，在mysql-user表里面修改了用户的权限后，已经建立的连接权限是不会受影响的。

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

解析器：

词法分析，语法分析。

先通过词法分析：

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

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

根据MySQL 定义的语法规则，根据SQL 语句生成一个数据结构，这个数据结构我们把它叫做解析树（select_lex）。

预处理器 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

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文件目录下可以看到

• 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”

恢复日志

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

2. 清空一个表里面的所有数据： truncate test.user_info;

3. 查看指定binlog文件的内容

show binlog events in ‘mysql-bin.000002’;
show binlog events in ‘SZ-PD-604177-bin.000010’;

4. 回滚 然后使用开始位置和结束位置来恢复数据，执行以下命令
   执行语句：
   mysqlbinlog --start-position=4 --stop-position=1710 “C:\ProgramData\MySQL\MySQL Server 5.5\data\mysql-bin.000002” > d:\test1.sql
   

然后再D盘我们能看到这个文件

5. (这里是用我们binlog备份的test1.sql去恢复数据哦)：
   source d://test1.sql
   

最后我们看到我们的日志恢复了

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日志