sql语句执行过程

spring to do

已于 2022-08-08 17:44:54 修改

阅读量516

点赞数

分类专栏： mysql 文章标签： sql 数据库 mysql

于 2021-11-14 17:16:52 首次发布

原文链接：https://www.cnblogs.com/mengxinJ/p/14045520.html

版权

mysql 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

写操作执行过程

Server层
存储引擎（InnoDB）层
SQL执行顺序

如果这条sql是写操作(insert、update、delete)，那么大致的过程如下，其中引擎层是属于 InnoDB 存储引擎的，因为InnoDB 是默认的存储引擎，也是主流的，所以这里只说明 InnoDB 的引擎层过程。由于写操作较查询操作更为复杂，所以先看一下写操作的执行图。方便后面解析。
在这里插入图片描述

Server层

连接器

1）负责与客户端的通信，是半双工模式，这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据，而不能同时进行。

2）验证用户名和密码是否正确（数据库mysql的user表中进行验证），如果错误返回错误通知（deAcess nied for user ‘root’@‘localhost’（using password：YES）），如果正确，则会去 mysql 的权限表（mysql中的 user、db、columns_priv、Host 表，分别存储的是全局级别、数据库级别、表级别、列级别、配合 db 的数据库级别）查询当前用户的权限。

缓存（Cache）

也称为查询缓存，存储的数据是以键值对的形式进行存储，如果开启了缓存，那么在一条查询sql语句进来时会先判断缓存中是否包含当前的sql语句键值对，如果存在直接将其对应的结果返回，如果不存在再执行后面一系列操作。如果没有开启则直接跳过。

相关操作：

查看缓存配置：show  variables  like  'have_query_cache';
查看是否开启：show  variables  like  'query_cache_type';
查看缓存占用大小：show  variables  like  'query_cache_size';
查看缓存状态信息：show  status  like  'Qcache%';

在这里插入图片描述

 缓存失效场景：
1、查询语句不一致。前后两条查询SQL必须完全一致。
2、查询语句中含有一些不确定的值时，则不会缓存。比如 now()、current_date()、curdate()、curtime()、rand()、uuid()等。
3、不使用任何表查询。如 select 'A';
4、查询 mysql、information_schema 或 performance_schema 数据库中的表时，不会走查询缓存
5、在存储的函数，触发器或事件的主体内执行的查询。
6、如果表更改，则使用该表的所有高速缓存查询都变为无效并从缓存中删除，这包括使用 MERGE 映射到已更改表的表的查询。一个表可以被许多类型的语句改变，如 insert、update、delete、truncate rable、alter table、drop table、drop database。

通过上面的失效场景可以看出缓存是很容易失效的，所以如果不是查询次数远大于修改次数的话，使用缓存不仅不能提升查询效率还会拉低效率（每次读取后需要向缓存中保存一份，而缓存又容易被清除）。所以在 MYSQL5.6默认是关闭缓存的，并且在 8.0 直接被移除了。当然，如果场景需要用到，还是可以使用的。

缓存开启

在配置文件(linux下是安装目录的cnf文件，windows是安装目录下的ini文件)中，增加配置： query_cache_type = 1

关于 query_type_type 参数的说明：
在这里插入图片描述

分析器

对客户端传来的 sql 进行分析，这将包括预处理与解析过程，并进行关键词的提取、解析，并组成一个解析树。具体的解析词包括但不局限于 select/update/delete/or/in/where/group by/having/count/limit 等，如果分析到语法错误，会直接抛给客户端异常：ERROR:You have an error in your SQL syntax.

比如：select * from user where userId =1234;

在分析器中就通过语义规则器将select from where这些关键词提取和匹配出来,mysql会自动判断关键词和非关键词，将用户的匹配字段和自定义语句识别出来。这个阶段也会做一些校验；比如校验当前数据库是否存在user表，同时假如User表中不存在userId这个字段同样会报错：unknown column in field list.

优化器

进入优化器说明sql语句是符合标准语义规则并且可以执行。优化器会根据执行计划选择最优的选择，匹配合适的索引，选择最佳的方案。比如一个典型的例子是这样的：

表T,对A、B、C列建立联合索引(A,B,C)，在进行查询的时候，当sql查询条件是:select xx where B=x and A=x and C=x很多人会以为是用不到索引的，但其实会用到,虽然索引必须符合最左原则才能使用,但是本质上,优化器会自动将这条sql优化为:where A=x and B=x and C=X,这种优化会为了底层能够匹配到索引，同时在这个阶段是自动按照执行计划进行预处理,mysql会计算各个执行方法的最佳时间,最终确定一条执行的sql交给最后的执行器。

优化器会根据扫描行数、是否使用临时表、是否排序等来判断是否使用某个索引，其中扫描行数的计算可以通过统计信息来估算得出，而统计信息可以看作是索引唯一数的数量，可以使用部分采样来估算，具体就是选择 N 个数据页，统计这些页上数据的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了。但是因为索引数据会变化，所以索引的统计信息也会变化。当变更的数据行数超过 1/M 的时候，就会重新计算一次统计信息。

执行器

执行器会调用对应的存储引擎执行 sql。主流的是MyISAM 和 Innodb。
在这里插入图片描述

存储引擎（InnoDB）层

undo log 与 MVCC

undo log是 Innodb 引擎专属的日志，是记录每行数据事务执行前的数据。主要作用是用于实现MVCC版本控制，保证事务隔离级别的读已提交和读未提交级别。而 MVCC 相关的可以参考 MySQL中的事务原理和锁机制。

redo log 与 Buffer Pool

InnoDB 内部维护了一个缓冲池，用于减少对磁盘数据的直接IO操作，并配合 redo log、内部的 change buffer 来实现异步的落盘，保证程序的高效执行。redo log 大小固定，采用循环写
在这里插入图片描述
write pos 表示当前正在记录的位置，会向后记录， checkpoint 表示数据落盘的边界，也就是 checkpoint 与 write pos中间是已记录的，当 write pos写完 id_logfile_3后，会回到id_logfile_0循环写，而追上 checkpomnit 后则需要先等数据进行落盘，等待 checkponit向后面移动一段距离再写。redo log存储的内容是对数据页的修改逻辑。

bin log(Server 层)

redo log 因为大小固定，所以不能存储过多的数据，它只能用于未更新的数据落盘，而数据操作的备份恢复、以及主从复制是靠 bin log（如果数据库误删需要还原，那么需要某个时间点的数据备份以及bin log）。5.7默认记录的是操作语句涉及的每一行修改前后的行记录。4

三个日志的比较（undo、redo、bin）

1、undo log是用于事务的回滚、保证事务隔离级别读已提交、可重复读实现的。redo log是用于对暂不更新到磁盘上的操作进行记录，使得其可以延迟落盘，保证程序的效率。bin log是对数据操作进行备份恢复（并不能依靠 bin log 直接完成数据恢复）。

2、undo log 与 redo log 是存储引擎层的日志，只能在 InnoDB 下使用；而bin log 是 Server 层的日志，可以在任何引擎下使用。

3、redo log 大小有限，超过后会循环写；另外两个大小不会。

4、undo log 记录的是行记录变化前的数据；redo log 记录的是 sql 的数据页修改逻辑以及 change buffer 的变更；bin log记录操作语句对具体行的操作以及操作前的整行信息（5.7默认）或者sql语句。

5、单独的 binlog 没有 crash-safe 能力，也就是在异常断电后，之前已经提交但未更新的事务操作到磁盘的操作会丢失，也就是主从复制的一致性无法保障，而 redo log 有 crash-safe 能力，通过与 redo log 的配合实现 “三步提交”，就可以让主从库的数据也能保证一致性。

6、redo log 是物理日志，它记录的是数据页修改逻辑以及 change buffer 的变更，只能在当前存储引擎下使用，而 binlog 是逻辑日志，它记录的是操作语句涉及的每一行修改前后的值，在任何存储引擎下都可以使用。