4、一条SQL在Mysql中如何执行的?
# MySQL的内部组件结构
# Server层
主要包括连接器、查询缓存、分析器、优化器、执行器等,涵盖MySQL的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学、加密函数),所有垮存储引起的功能都在这一层实现,比如存储过程、触发器、视图等。
# Store层
存储引起负责数据的存储和提取。其架构模式是插件式的,支持InnoDB、MyISAM、Memory等存储引擎。现在最常用的存储引擎是InnoDB,现在默认的存储引擎是InnoDB
# 测试脚本
create table `test` (
`id` int(11) not null auto_increment,
`name` varchar(255) default null,
primary key (`id`)
) ENGINE = InnoDB auto_increment = 9 default CHARSET = utf8;
2
3
4
5
# 查询缓存(5.6以前版本)
一般建议在静态表(系统配置表、字典表等)中使用缓存,MySQL提供了“按需使用”的方式。(my.cnf参数query_catch_type
设置为DEMAND
)。
my.cnf
# query_catch_type有3个值(OFF):0标识关闭查询缓存(ON);1代表开启 2(DEMAND)代表当SQL语句中有SQL_CACHE关键词时才使用缓存
query_cache_type = 2
2
3
4
此时默认SQL都不实用缓存查询,在使用查询缓存的语句可以通过SQL_CACHE
显示指定。
select SQL_CACHE * from test where ID=5;
不展开学习查询缓存内容,此内容已经在mysql8.0
# 分析器
如果没有命中查询缓存,就要开始真正执行语句了。首先,MySQL 需要知道你要做什么,因此需要对 SQL 语句做解析。 分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句,MySQL 需要识别出里面的字符串分别是 什么,代表什么。
MySQL 从你输入的"select"这个关键字识别出来,这是一个查询语句。它也要把字符串“T”识别成“表名 T”,把字符 串“ID”识别成“列 ID”。
做完了这些识别以后,就要做“语法分析”。根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个 SQL 语句 是否满足 MySQL 语法。 如果你的语句不对,就会收到“You have an error in your SQL syntax”的错误提醒,比如下面这个语句 from 写成了 "rom"。
# 词法分析器原理
词法分析器分成6个主要步骤完成对sql语句的分析
- 词法分析
- 语法分析
- 语义分析
- 构造执行树
- 生成执行计划
- 计划的执行
SQL语句的分析分为词法分析与语法分析,mysql的词法分析由MySQLLex完成,语法分析由Bison生成。
经过Bison语法分析后,会生成语法树:
# 优化器
经过了分析器,MySQL 就知道你要做什么了。在开始执行之前,还要先经过优化器的处理。
优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接 顺序。比如你执行下面这样的语句,这个语句是执行两个表的 join:
select * from test1 join test2 using(ID) where test1.name='t1' and test2.name='t2';
既可以先从表 test1 里面取出 name=t1的记录的 ID 值,再根据 ID 值关联到表 test2,再判断 test2 里面 name的 值是否等于 t2。
也可以先从表 test2 里面取出 name=t2 的记录的 ID 值,再根据 ID 值关联到 test1,再判断 test1 里面 name 的值是否等于 t1。
这两种执行方法的逻辑结果是一样的,但是执行的效率会有不同,而优化器的作用就是决定选择使用哪一个方案。优化器阶段 完成后,这个语句的执行方案就确定下来了,然后进入执行器阶段。如果你还有一些疑问,比如优化器是怎么选择索引的,有 没有可能选择错等等。
# 执行器
开始执行的时候,要先判断一下你对这个表 T 有没有执行查询的权限,如果没有,就会返回没有权限的错误,如下所示 (在工程实现上,如果命中查询缓存,会在查询缓存返回结果的时候,做权限验证。查询也会在优化器之前调用 precheck 验证权限)。
select * from test where id=1
如果有权限,就打开表继续执行。打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口。
比如我们这个例子中的表 test 中,ID 字段没有索引,那么执行器的执行流程是这样的:
- 1.调用 InnoDB 引擎接口取这个表的第一行,判断 ID 值是不是 10,如果不是则跳过,如果是则将这行存在结果集中;
- 2.调用引擎接口取“下一行”,重复相同的判断逻辑,直到取到这个表的最后一行。
- 3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
慢SQL日志中的rows_examined字段,标识这个语句执行了过程中扫描了多少行。
# bin-log归档
binlog是Server层实现的二进制日志,他会记录我们的cud操作。Binlog有以下几个特点:
- 1、Binlog在MySQL的Server层实现(引擎共用)
- 2、Binlog为逻辑日志,记录的是一条语句的原始逻辑
- 3、Binlog不限大小,追加写入,不会覆盖以前的日志
如果,我们误删了数据库,可以使用binlog进行归档!要使用binlog归档,首先我们得记录binlog,因此需要先开启MySQL的 binlog功能。
# 配置开启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
3
4
5
6
# binlog命令
-- 查看bin‐log是否开启
show variables like '%log_bin%';
-- 会多一个最新的bin‐log日志
flush logs;
-- 查看最后一个bin‐log日志的相关信息
show master status;
-- 清空所有的bin‐log日志
reset master;
2
3
4
5
6
7
8
# 查看binlog内容
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001
binlog里的内容不具备可读性,所以需要我们自己去判断恢复的逻辑点位,怎么观察呢?看重点信息,比如begin,commit这种 关键词信息,只要在binlog当中看到了,你就可以理解为begin-commit之间的信息是一个完整的事务逻辑,然后再根据位置 position判断恢复即可。
binlog内容如下:
# 数据归档操作
-- 从bin‐log恢复数据
-- 恢复全部数据
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p tuling(数据库名)
-- 恢复指定位置数据
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults ‐‐start‐position="408" ‐‐stop‐position="731" /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p tuling(数据库)
-- 恢复指定时间段数据
/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(数 据库)
2
3
4
5
6
7
8
9
# 归档测试
1、定义存储过程,写入数据
drop procedure if exists tproc;
delimiter $$
create procedure tproc(i int)
begin
declare s int default 1;
declare c char(50) default repeat('a',50);
while s<=i do
start transaction;
insert into test values(null,c);
commit;
set s=s+1;
end while;
end
delimiter ;
2
3
4
5
6
7
8
9
10
11
12
13
14
2、删除数据
truncate test;
3、利用binlog归档
-- linux
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐ bin.000001 |mysql ‐uroot ‐p tuling(数据库名)
2
-- windows
-- 查询binlog列表
show master logs;
2
3
-- 查看具体的日志
show binlog events in 'binlog.000001';
2
-- 找到日志文件目录
show variables like '%log_bin%';
2
# 执行shell命令
mysqlbinlog --start-position=622 --stop-position=1790 --database=binlog_test mysql-bin.000001 | mysql -uroot -p123456
2