3.SQL底层执行原理

SQL底层执行原理

MySQL的内部组件结构

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

Server层

​ 主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有内置函数（日期、时间、加密函数等），所有跨存储引擎的功能都在这一层时间的，例如存错过程、触发器、视图等。

Store层

​ 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MySAM、Memory等多个存储引擎。现在用的最多是InnoDB.

连接器

​ 由于MySQL是开源的，它有很多种客户端常用的有：Navicat，Mysql front,SQLyog等，客户端要向MySQL发起通信都必须先跟server端建立连接，建立连接的工作就是有连接器完成。

​ 第一步：连接到数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理链接。连数据库命令

mysql -h [host 数据库地址] -u [root 用户] -p [root 密码]  -P 3306[端口]

连接命令中的mysql是客户端工具，用来跟服务建立连接。完成经典的TCP手后，连接器就要开始认证你的身份（用户名和密码）。

1、若用户或密码不对，会返回“Access denied for user”的错误，客户端程序结束执行。

2、如果用户和密码认证通过，连接器回到权限表中查出用户拥有的权限，之后这个连接里的权限判断，都将依赖此时读到的权限。

注意：一个用户陈宫简历连接后，即使你用管理员账号对此用户权限修改，也不影响已经存在的连接权限。修改完成后，只有在新建的连接才会使用新的权限设置，用户的权限表系统表空间mysql 的user表中。

修改用户信息操作

create user 'username'@'host' identified by 'password';//创建新用户
grant all privileges on *.* to 'username'@'%';//赋权限，%表示所有（host）
flush privileges //刷新数据库
update user set password=password("123456") where user='root' //设置用户密码
show grants for root@'%' //查看用户权限

连接完成后，若不进行操作，这连接处于空闲状态，可以用show processlist 命令中看到它，若结果中的

Command 列显示Sleep 的，表示是一个空闲连接。

查询数据库连接时长：连接空闲超过配置的时长就会关闭，无法使用。

show global variables like 'wait_timeout'; //28800 秒 8个小时

连接建立后，就可以执行select语句了，执行逻辑来到第二步：查询缓存。

MySQL拿到一个查询请求后，会先到查询缓存看看，是否有这个key,找到直接返回。

查询缓存 ：之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么value就会被直接返回客户端。

注意：查询缓存往往弊大于利。查询缓存失效非常频繁，只要对一个表更新，这个表上的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。不建议使用（除了万年不变的配置表等等）。

MySQL开启缓存建议：

my.cnf
#query_cache_type 有3个值（0关闭查询缓存，1开启，2 DEMAND代表 SQL语句中有SQL_CACHE关键词时才缓存）
query_cache_type=2

例如下面

select SQL_CACH * from test where id =2;

查询是否开启缓存

show global variables like '%query_cache_type%'

监控查询缓存命中率

show status like 'Qcache';

分析器

​ 如果没有命中查询缓存，就要开始真正执行SQL。

​ 分析器首先做‘词法分析’。对你输入的SQL语句，识别出里面字符串时什么，代表什么。‘词法分析’之后要做‘语法分析’。根据词法分析结果，语法分析器会根据语法规则，判断输入的SQL是否满足MySQL语法。

词法分析器原理

​ 词法分析器分成6个主要步骤完成对SQL语句分析

​ 1、词法分析

​ 2、语法分析

​ 3、语义分析

​ 4、构造执行树

​ 5、生成执行计划

​ 6、计划的执行

优化器

​ 经过分析器，MySQL就知道你要什么了，在开始执行之前，还要先经过优化器处理。

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

执行器

​ 开始执行的时候，要先判断下当前用户对这个表有没有执行权限，如果没有，就会返回没有权限的错误。如果有权限就会继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供接口。

select * from test where id =1;

上面这个SQL如果ID没有索引，那么执行器执行的流程如下：

1.调用InnoDB引擎接口去这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是将此行存在结果集中。

2.调用引擎接口去“下一行”，重复相同的判断逻辑，知道渠道这表的最后一行。

3.执行器将上述遍历过程中所有满足条件的行组成记录集作为结果返回给客户端。

有索引的和上面的执行过程差不多。

bin-log归档

binlog是Server层实现的二进制日志，它会记录CUD操作。有以下特点：

1、Binlog 在MySQL的Server层实现（引擎共用）；

2、Binlog 为逻辑日志，记录的是一条语句的原始逻辑；

3、Binlog 不限大小，追加写入，不会覆盖以前的日志

开启bin-log功能：

配置开启bin-log
log-bin=/usr/local/mysql/data/binlog/myslq-bin
注意5.7以及更高版本需要配置本项：server-id=123456(自定义，唯一)
#binlog格式 ：statement 操作本身，row 操作整个过程及影响，mixed 前两种（自动识别用那种）。
binlog-format=ROW 
#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，有系统决定。
sync-binlog=1

binlog命令

查看bin-log是否开启
show variables like '%log_bin%';
创建一个最新的bin-log日志
flush logs;
查看最后一个bin-log日志的相关信息
show master status;
清空所有的bin-log日志
reset master;
查看binlog内容
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001

数据恢复

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

查看binlog 内容

binlog 内容中有一些关键信息，比如 begin,commit这种关键词信息，只要在binlog中看到了，就可以理解为begin-commit之间的信息是一个完成的事务逻辑，然后根据位置position 判断恢复即可。