Mysql sql执行过程和Binlog学习总结

sql执行过程

MySQL 可以分为 Server 层和存储引擎层两部分。

Server层：

主要包括连接器、查询缓存、分析器、优化器、执行器等。
所有的内置函数、存储过程、触发器、视图等都在这一层完成。
连接器： 连接器负责跟客户端建立连接、获取权限、维持和管理连接。
TCP 握手后，连接器就要开始认证你的身份，如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限，所以说一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改权限表后，只有再新建的连接才会使用新的权限设置。
连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，可以在 show processlist 命令中看到它，Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接，关闭连接可以用kill id。
查询缓存：
MySQL 拿到一个查询请求后，会先到查询缓存看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，直接缓存在内存中，key 是查询的语句，value 是查询的结果。如果语句不在查询缓存中，会继续后面的执行阶段，执行完成后，执行结果会被存入查询缓存中。
查询缓存的失效非常频繁，对于更新压力大的数据库来说，查询缓存的命中率会非常低。可以在静态表里使用查询缓存，就是一般我们极少更新的表。可以将my.cnf参数query_cache_type 设置成 2。

my.cnf中
query_cache_type有3个值 
0代表关闭查询缓存OFF
1代表开启ON
2代表当sql语句中有SQL_CACHE关键词时才缓存
query_cache_type=2
select SQL_CACHE * from test where ID=1；

分析器： 如果没有命中查询缓存，就要开始真正执行语句了
分析器先会做词法分析，词法分析会对sql进行语法校验

然后进入优化器：
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

例：
select * from test1 join test2 using(ID) where test1.name=zhangsan and test2.name=lisi;
既可以先从表 test1 里面取出 name=zhangsan的记录的 ID 值，再根据 ID 值关联到表 test2，再判断 test2 里面 name的值是否等于 lisi。
也可以先从表 test2 里面取出 name=lisi 的记录的 ID 值，再根据 ID 值关联到 test1，再判断 test1 里面 name 的值是否等于 zhangsan。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

执行器： 优化器阶段完成后，然后进入执行器阶段。
开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

bin-log归档

binlog是Server层实现的二进制日志,他会记录我们的cud操作。
使用binlog，需要配置my.cnf开启MySQL的binlog：

log-bin=/usr/local/mysql/data/binlog/mysql-bin

5.7以及更高版本需要配置本项：
server-id=123454（自定义,保证唯一性）
#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定
sync-binlog=1
binlog-format=ROW 

binlog-firmat格式有3种：
statement： 基于SQL语句的复制
每一条会修改数据的sql都会记录到master的binlog中，但可能造成主从同步数据不一致。
row： 基于行的复制
不记录执行的sql语句的上下文相关信息，仅记录哪一条记录被修改了，修改成什么信样了，row的日志内容会非常清楚的记录下每一行数据修改的细节，但每行都记录，可能会产生大量的日志内容。
mixed： 混合模式复制
会根据执行的每一条sql语句来区分对待记录的日志形式，在Statement和row之间选择一种。

binlog命令：

查询命令：
mysql> show variables like '%log_bin%'; 查看bin-log是否开启
mysql> flush logs; 会多一个最新的bin-log日志
mysql> show master status; 查看最后一个bin-log日志的相关信息
mysql> reset master; 清空所有的bin-log日志
mysql> /usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 查看binlog内容
数据归档命令：
恢复全部数据：
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p 数据库名
恢复指定位置数据：
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position="408" --stop-position="731"  /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p 数据库名
恢复指定时间段数据：
/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 数据库名

binlog里的内容不具备可读性，判断恢复的逻辑点位，可以看begin,commit这种关键词信息，你就可以理解为begin-commit之间的信息是一个完整的事务逻辑,然后再根据位置position判断恢复即可。