Mysql (架构篇) - 架构图、日志文件、数据文件、SQL语句的完整执行流程

站长

2024年06月10日 17:14 · 阅读数 31

1. MySQL服务器环境

OS：CentOS 7.6
MySQL：MySQL 5.7.30

2. MySQL架构图

2.1 逻辑架构图

Mysql (架构篇) - 架构图、日志文件、数据文件、SQL语句的完整执行流程

Connectors客户端连接器：负责跟客户端建立连接
Management Serveices & Utilities系统管理和控制工具
Connection Pool连接池：管理用户连接，监听并接受连接的请求，转发所有连接的请求到线程管理模块
SQL Interface SQL接口：接收用户的SQL命令，并返回SQL执行结果
Parser解析器：SQL传递到解析器的时候，会被解析器验证和解析
Optimizer查询优化器：SQL查询语句在查询之前，会通过查询优化器进行优化，explain语句查看的SQL语句执行计划，就是查询优化器生成
Cache和Buffer查询缓存：在MySQL5.7中包含缓存组件。在MySQL8中移除了
Pluggable Storage Engines存储引擎：存储引擎就是存储数据、建立、更新索引、查询数据等技术的实现方法

2.2 MySQL日志文件

MySQL是通过文件系统对数据索引后进行存储的，MySQL从物理结构上可以分为日志文件和数据及索引文件。MySQL在Linux中的数据索引文件和日志文件通常放在/var/lib/mysql目录下。MySQL通过日志记录了数据库操作信息和错误信息。

常见的日志文件：

错误日志（error log）：
二进制日志(bin log)
查询日志(general query log)
慢日志(slow query log)
事务重做日志(redo log)
中继日志(relay log)
。。。

可以通过命令查看当前数据库中的日志使用信息：

mysql> show variables like 'log_%';

Mysql (架构篇) - 架构图、日志文件、数据文件、SQL语句的完整执行流程

2.2.1 错误日志：error log

默认开启，错误日志记录了运行过程中遇到的所有严重的错误信息，以及mysql每次启动和关闭的详细信息。错误日志所记录的信息是可以通过log_error和log_warnings配置来定义的。从5.5.7之后不能关闭错误日志。

log_error: 指定错误日志存储位置
log_warnings: 是否将警告信息输出到错误日志中
- log_warinings 为0: 表示不记录告警信息
- log_warinings 为1: 表示告警信息写入错误日志
- log_warinings 大约1: 表示各类告警信息，例如有关网络故障的信息和重新连接信息写入错误日志

log_error = /var/log/mysql-error.log
log_warnings = 2

2.2.2 二进制日志：bin log

默认关闭，需要通过以下配置进行开启。binlog记录了数据库所有的ddl语句和dml语句，但不包括select语句内容，语句以事件的形式保存，描述了数据的更变顺序，binlog还包含了每个更新语句的执行时间信息。如果是DDL语句，则直接记录到binlog日志，而DML语句，必须通过事务提交才能记录到binlog日志中。

binlog主要用于实现mysql 主从复制、数据备份、数据恢复。

配置中mysql-bin是binlog日志文件的basename, binlog日志文件的完整名称：mysql-bin.000001.

server_id = 42
log-bin = mysql-bin

数据定义语言：简称DDL(Data Definition Language)

作用：用来定义数据库对象：数据库，表，列等

关键字： create, alter, drop等

数据操作语言：简称DML(Data Manipulation Language)

作用：用来对数据库中表的记录进行更新

关键字： insert, update, delete等

数据查询语言：简称DQL(Data Query Language)

作用：用来查询数据库中表的记录

关键字：select、from、where等

数据控制语言：简称DCL(Data Control Language)

作用：用来定义数据库的访问权限和安全级别，及创建用户

2.2.3 通用查询日志：general query log

默认关闭，由于通用查询日志会记录用户的所有操作，其中还包含增删改查等信息，在并发操作大环境会产生大量的信息从而导致不必要的磁盘IO，会影响Mysql性能。

如果不是为了调试数据库，不建议开启查询日志。

# 查询通用查询日志变量信息
mysql> show global variables like '%general_log%'

开启方式：

# 启动开关
general_log = {ON|OFF}
# 日志文件变量，而general_log_file如果没有指定，默认名是host_name.log
general_log_file = /var/lib/mysql/host_name.log

2.2.4 慢查询日志：slow query log

默认关闭，通过以下以下设置开启。记录执行时间超过long_query_time秒的所有查询，便于收集查询时间比较长的SQL语句。

查看阈值

show global status like '%slow_queries%';
show variables like '%slow_query%';
show variables like '%long_query_time%';

配置慢查询开启

#开启慢查询日志
slow_query_log = ON
#慢查询的阈值，单位秒
long_query_time = 10
#日志记录文件
#如果没有给出file_name值，默认为主机名，后缀为-slow.log
#如果给了文件名，但不是绝对路径名，文件写入数据目录
slow_query_log_file = slow_query_log.log

2.3 Mysql数据文件

查看Mysql数据文件show variables like '%datadir%';

ibdata文件：使用系统表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件。

InnoDB存储引擎的数据文件：

.frm文件：主要存放与表相关的数据信息，主要包含表结构的定义信息
.ibd文件：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件

MyISAM存储引擎的数据文件

.frm文件：主要存放与表相关的数据信息，主要包含表结构的定义信息
.myd文件：主要存储表数据信息
.myi文件：主要用来存储表数据文件中的任何索引的数据树

2.4 一条SQL语句的完整执行流程

分析SQL语句如下 select c_id,first_name,last_name from customer where c_id=14;

大体来说，Mysql可以分为 Server层和存储引擎层两部分。

1、 Server层

包含：连接器、查询缓存、分析器、优化器、执行器等
涵盖Mysql大多数的核心服务功能
所有内置的函数（如日期、时间、数学、加密函数等），所有跨存储引擎的功能都在这一层实现
- 如存储过程、触发器、视图等

2、存储引擎层

负责数据的存储和提取
可插拔式存储引擎：InnoDb、 MyISAM、 Memory等
最常用的存储引擎是InnoDb
从Mysql 5.5版本开始，默认存储引擎是InnoDb

Mysql (架构篇) - 架构图、日志文件、数据文件、SQL语句的完整执行流程

第一步：连接到数据库

首先会连接到这个数据库上，这时候接待你的就是连接器。

-- 连接命令
mysql -h127.0.0.1 -p3306 -uroot -p

连接完成后，如果你没有后续的操作，这个连接就处于空闲状态。客户端如果太长时间没动静，连接器会自动断开。这个时间是由参数wait_timeout控制的默认值是8小时。 mysql> show processlist; 其中的command列显示为‘sleep’的这一行，就表示现在系统里面有一个空闲连接

Mysql (架构篇) - 架构图、日志文件、数据文件、SQL语句的完整执行流程

第二步：查缓存

Mysql拿到一个查询请求后，会先到查询缓存，看看之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句hash之后的值，value是查询的结果。

如果你的查询语句在缓存中，会被直接返回给客户端
如果查询语句不在缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

如果查询缓存命中，Mysql不需要执行后面的复杂操作就可以直接返回结果，效率会很高。但是不建议使用Mysql的内置缓存功能。

案例1: 查询缓存 查询缓存默认是关闭的状态

# (1) 查看是否开启缓存
mysql> show variables like 'query_cache_type';
# (2) 查看缓存的命中次数
mysql> show status like 'qcache_hits';
# (3) 开启缓存
在/etc/my.cnf文件中修改‘query_cache_type’参数
值为 ‘0或OFF’会禁止使用缓存。
值为 ‘1或ON’将会启用缓存，但以‘SELECT SQL_NO_CACHE’开头的语句除外。
值为 ‘2或DEMAND’时，只缓存以‘SELECT SQL_CACHE’开头的语句。

修改配置文件my.cnf，在文件中增加如下内容开启缓存： query_cache_type = 1

清空查询缓存：

可以使用下面三个SQL来清理缓存

FLUSH QUERY CACHE; #清理查询缓存内存碎片
RESET QUERY CACHE; #从查询缓存中移除所有查询
FLUSH TABLES; #关闭所有打开的表，同时该操作会清空查询缓存中的内容

为什么不建议使用Mysql的查询缓存？

因为查询缓存往往弊大于利

成本高：**查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。**因此往往你费劲得把结果存起来，还没有使用，就被一个更新清除了。
命中率不高：对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表的查询才适合使用查询缓存。
功能并不如专业的缓存工具更好：redis、memcache、ehcache...

好在Mysql也提供这种按需使用的方式。你可以将参数query_cache_type设置为DEMADN，这样对于默认的SQL语句都不实用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样： mysql> select sql_cache * from city where city_id = 1;

注意： Mysql8.0 版本直接将查询缓存的整块功能删掉了！

第三步：分析SQL语句

如果查询缓存没有命中，接下来要进入正式的查询阶段了。客户端发送过来的请求，实际上只是一个字符串而已，所以mysql服务器程序首先需要对这个字符串做分析，判断请求的语法是否正确，然后从字符串中将要查询的表、列和各种查询条件都提取出来。本质上是对一个SQL语句编译的过程，涉及词法解析、语法分析、预处理器等

词法分析：就是把一个完整的SQL语句分割成一个个的字符串
语法分析：根据词法分析的结果做语法检查，判断你输入的SQL语句是否满足mysql语法
预处理器：预处理器会进一步检查解析树是否合法，比如表名是否存在，语句中的列是否存在等等。在这一步Mysql会校验用户是否有表的操作权限。

1）词法分析

# 分隔前

select c_id,first_name,last_name from customer where c_id=14;

# 分隔后

select，c_id,first_name,last_name，from，customer，where，c_id，=，14

Mysql同时需要识别出这个SQL语句中的字符串分别是什么，代表什么。

把"select"这个关键字识别出来，这是一个查询语句
把“customer”识别成“表名 customer”
把“c_id识别成“列 c_id”。

2）语法分析

如果语法正确就会根据Mysql语法规则与SQL语句生成一个数据结构，这个数据结构我们叫做解析树。

”You have an error in your SQL syntax“错误就是在这个位置出现的

比如：

mysql> select c_id,first_name,last_name form customer where c_id=14;

[Err] 1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'customer where c_id=14' at line 1

# 错误原因：from 写为 form

解析树举例：

Mysql (架构篇) - 架构图、日志文件、数据文件、SQL语句的完整执行流程

3）预处理器 预处理器会进一步查询解析树是否合法，比如表名是否存在，语句中表的列是否存在等等，在这一步MySQL会检验用户是否有表的操作权限。

预处理之后会得到一个新的解析树，然后调用对应执行模块

第四步：优化器-优化SQL语句

优化器顾名思义就是对查询进行优化。作用是根据解析树生成不通的执行计划，然后选择最优的执行计划。

mysql里面使用的基于成本模型的执行器，那种执行计划explain执行时成本最小就用哪一个。而且它是io_cost和cpu_cost的开销总和，它通常也是我们评价一个查询的执行效率的一个常用指标。

查看上次查询成本开销，默认值是0 show status like 'Last_query_cost';

优化器可以做那些优化：

当有多个索引可用的时候，决定使用哪个索引。
在一个语句有多表关联（join）时，决定各个表的连接顺序，以哪个表为基准表。

举个例子：（1）比如hello数据库中表customer上执行下面的语句，这个语句用到了两个索引 last_name 和address_id 。

# hello数据库中表customer

explain select * from customer where last_name='刘皇叔' and address_id=11;

既可以使用last_name索引查询，然后过滤列address_id
也可以使用address_id索引查询，然后过滤列last_name

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

使用explain工具可以查看优化器的执行计划

Mysql (架构篇) - 架构图、日志文件、数据文件、SQL语句的完整执行流程

注意：优化器最多就是辅助，作用很有限，我们的SQL语句不能依赖于MySQL的优化器去调优！如果SQL语句垃圾，则没有可优化的空间。优化SQL的根本在于掌握MySQL分析与调优知识。

第五步：执行器-执行SQL语句

1）判断执行权限 开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有，就会返回没有权限的报错。

举个例子比如：我们新建一个用户mysql_user，只有表test的查询权限，没有表customer的查询权限。

CREATE USER `mysql_user`@`localhost` IDENTIFIED BY '123456@heroA';
GRANT Select ON TABLE `hello`.`test` TO `mysql_user`@`localhost`;

使用这个用户mysql_user连接mysql，执行下面的查询语句，就会返回没有权限的错误。

mysql -umysql_user -p123456@heroA
mysql> select * from customer where c_id=14;

Mysql (架构篇) - 架构图、日志文件、数据文件、SQL语句的完整执行流程

2) 调用存储引擎接口查询 如果有权限，就使用指定的存储引擎打开表开始查询。执行器会根据表的引擎定义，去使用这个表提供的查询接口提取数据。

c_id是主键执行流程：
- 调用InnoDb引擎接口，从主键索引中检索c_id=14的数据
- 主键索引等值查询只会查询出一条记录，直接将该记录返回客户端
- 至此，这个语句就执行完成了
c_id不是主键执行流程：全表扫描
- 调用InnoDb引擎接口取这个表的第一行，判断c_id值是不是14，如果不是则跳过，如果是则将这行数据缓存在结果集中。
- 调用引擎接口取“下一行”，重复相同的逻辑，直到取到这个表的最后一行
- 执行器将上述遍历过程中所有满足条件的行组成的结果集返回给客户端。
- 至此，这个语句就执行完成了

转载自:https://juejin.cn/post/7361468008528937012