SQL的执行过程分析与Mysql的一些隐藏功能

笔者近几天学习了一下一条SQL语句是如何在Mysql中执行的，其中有一些隐藏的功能让读者眼前一亮，在这里和大家分享一下

---

一开始还是围绕下面一幅图大体的概述一下一条SQL是如何在Mysql中的执行过程；

一、SQL的执行过程

一条sql发送到mysql的时候会经过以下五个步骤

• 第一步会先经过连接器去校验账号信息是否正确还有账号是否有对应的增删改查权限，如果这些都符合要求则会进入下一步；
• 第二步是查询缓存中是否有对应的sql语句，如果有的话就不去磁盘读取直接从查询缓冲中拿到对应的数据（查询缓存中是以map的形式进行存储数据的，key为对应的查询sql语句，value为对应的查询结果集）；
• 第三步则会进入语法分析器，将对应的sql语句解析成mysql能够读取的语法树；
• 第四步则是进入优化器，优化器根据语法树中的结构找到合适的索引进行一定的语法优化；
• 第五步才是调用执行器去执行优化后的sql语句，通过对应的引擎进行磁盘IO读取出对应的结果集；

二、连接器权限校验中的一些注意事项

修改用户对数据库的操作权限后并不会立即生效，必须重启mysql服务才能生效；原因是权限生效的过程中需要进行一次相当于JVM中STW的操作，这个对用户的体验会有很大的影响，所以修改权限并不会立即生效，需要等版本更新的时候重新启动数据库。

三、缓存组件的缺陷

mysql中的缓存只对长久不会变动的数据和读写分离的数据有效，一旦数据出现了update，那么之前的查询缓存中的数据就变成脏数据了，此时mysql依然会从磁盘IO中读取数据，那么有没有缓存都一样了（数据查询并不会变快）。

四、回滚的原理

mysql中的回滚原理主要是预先在数据库中存入和原本执行的语句相反的的语句来进行的。比如Insert语句，它会拿到插入后返回的id，然后自动生成一条对应的delete语句，这样只要执行回滚操作，那么就会调用这条delete语句将原本插入的数据清除。

五、隐藏功能：bin-log

bin-log主要是为了预防误删数据而开发出来的一个功能。下面介绍一下bin-log的具体使用方法：

1、在my.cnf配置文件中开启bin-log功能

#配置开启binlog
log‐bin=/usr/local/mysql/data/binlog/mysql‐bin
#注意5.7以及更高版本需要配置本项：server‐id=123454（自定义,保证唯一性）;
#binlog格式，有3种statement,row,mixed
binlog‐format=ROW
#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定
sync‐binlog=1

2、bin-log基本命令

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

3、bin-log文件的查看方式

show binlog events in ‘binlog.000001’;（binlog.000001就是bin-log的表操作记录文件），下图是查询结果：

一般恢复都是从最后一栏有BIGIN的字段的那一行取开始的position（pos），然后再到有COMMIT的那一行取结束的position（End_log_pos），这样就可以恢复之前插入的数据了。

4、数据恢复操作

mac电脑最好进入bin目录下使用：./mysqlbinlog的指令来执行恢复操作，不然可能无法执行语句，笔者被这个坑埋了好久

# 恢复全部数据
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001
| mysql ‐uroot ‐p muyichen(数据库名)
# 恢复指定位置数据
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults ‐‐start‐position="155" ‐‐stop‐position="330"
/usr/local/mysql/data/binlog/mysql‐bin.000001 | mysql ‐uroot ‐p muyichen(数据库)
# 恢复指定时间段数据
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001
‐‐stop‐date= "2020‐06‐07 12:00:00" ‐‐start‐date= "2020‐06‐07 15:55:00"| mysql ‐uroot ‐p muyichen(数
据库)

5、bin-log的局限

它只能恢复在它开启后插入和更新的数据，在它开启前插入的数据是无法恢复的，原因是bin-log恢复数据的原理是记录插入、更新的语句，然后再恢复的时候重新执行一遍，这样的话在它开启之前插入的记录并没有插入、更新语句存储在bin-log中，这样就无法做到恢复的效果。