首页  思维笔记  详情

5-03 MySQL底层执行原理

2022-12-11 14:11:57   0  举报





仅支持查看

03 MySQL底层执行原理

MySQL

作者其他创作

大纲/内容

MySQL的内部组件结构

MySQL大体可分为 Server层 和 存储引擎层 两部分

<a class="link" target="_bank" href="https://www.processon.com/diagraming/6393e4826376895515fa19e1">MySQL的内部组件结构概览图</a>

Server层

主要包括连接器、查询缓存、分析器、优化器、执行器等

涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学、加密函数等）

所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等

Store层（存储引擎层）

存储引擎层负责数据的存储和提取

其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎

目前最常用的存储引擎是InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎

Server层内部组件作用

示例表：（应用于下面的所有示例）

连接器

由于MySQL是开源的，它有非常多种类的客户端：navicat、mysql front、jdbc、SQLyog 等

以上这些客户端要向MySQL发起通信都必须先与Server端建立通信连接，而建立连接的工作就是由连接器完成的。

具体连接过程如下：

1）你要先连接到MySQL数据库上，这时候接待你的就是连接器

连接器职责（与客户端）：

1. 建立连接

2. 获取权限

3. 维持和管理连接

连接命令如下：

1. 查看MySQL服务是否启动：

[root@192 ~]# ps -ef|grep mysql

2. 客户端连接Mysql服务：

[root@192 ~]# mysql ‐h host[数据库地址] ‐u [用户] ‐p [密码] ‐P 3306

连接命令中的 mysql 是客户端工具，用来跟服务端建立连接

1. 完成经典的 TCP 握手

2. 开始认证你的身份（这时候用的就是你输入的用户名和密码）

如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行

如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限

之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限

2）一个用户成功建立连接后

成功建立连接后，MySQL会划出一块用来存放连接的内存区域，并将获取到的用户权限信息也会缓存到当前连接会话

从上可知，即使你用管理员账号对这个用户的权限做了修改（会话中缓存的user是不会更新的），也不会影响已经存在连接的权限

MySQL为什么这么设计？

举例说明：假如正在进行秒杀活动，更新user信息后如果要同步更新会话中缓存的user信息，MySQL为保护隔离进行阻塞所有连接就会出现性能上的短暂假死，从而产生性能波动，这对一个秒杀活动是致命的。

用户权限修改后，如果要使用新的权限设置，只有再新建的连接才会使用新的权限设置

用户的权限表在系统表空间的mysql的user表中

如下图所示：

创建新用户、赋权限、修改user密码：

创建新用户：

mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password';

赋权限，%表示所有（host）：

mysql> grant all privileges on *.* to 'username'@'%';

刷新数据库：

mysql> flush privileges

设置用户密码：

mysql> update user set password=password(”123456″) where user=’root’;

查看当前用户的权限：

mysql> show grants for root@"%";

查看连接当前状态：

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态

可以通过 show processlist 命令查看连接的状态，其中 Command 列显示的就是当前连接状态

命令如下：

mysql> show processlist;

3）连接被断开

客户端如果长时间不发送command到Server端，连接器就会自动将它断开

这里的时间是由参数 wait_timeout（）控制的，默认值是 8h

查看wait_timeout：（单位-秒）

mysql> show global variables like "wait_timeout";

设置wait_timeout：（单位-秒）

mysql> set global wait_timeout=28800;

在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query

长连接、短连接：

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接

数据库里面，短连接是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个

实际开发我们大多数用的都是长连接，通常把长连接放在Pool内进行管理

使用长连接存在的问题：

1. 长连接有时候会导致MySQL占用内存涨的特别快

2. 如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了

这类问题产生的原因：

1. 因为MySQL在执行过程中临时使用的内存是管理在连接里面的

2. 这些资源会在连接断开的时候才释放

怎么解决这类问题呢？

1. 定期断开长连接：使用一段时间或者程序里面判断执行过一个占用内存的大查询后断开连接，之后要查询再重建连接

2. MySQL 5.7 或更高版本：

可以在每次执行一个比较大的操作后，通过 mysql_reset_connection 来重新初始化连接资源

这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态

查询缓存

常用的一些操作命令：

显示所有数据库

mysql> show databases;

打开数据库

mysql> use dbname；

 显示数据库mysql中所有的表

mysql> show tables;

显示表mysql数据库中user表的列信息

mysql> describe user;

连接建立完成后的 select 执行逻辑：

1. 连接建立完成后，就可以执行 select 语句了；执行逻辑就会来到第二步：查询缓存

2. MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句

3. 之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中

key 是查询的语句，value 是查询的结果

4. 如果命中查询缓存，那么这个 value 就会被直接返回给客户端（在查询缓存返回结果的时候，做权限验证）

5. 如果语句不在查询缓存中，就会继续后面的执行阶段

6. 执行完成后，执行结果会被存入查询缓存中

大多数情况查询缓存就是个鸡肋，为什么呢？

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

1. 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空

2. 因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了

3. 对于更新压力大的数据库来说，查询缓存的命中率会非常低

一般建议在静态表里使用查询缓存

静态表：就是一般我们极少更新的表

比如，一个系统配置表、字典表

这些张表上的查询才适合使用查询缓存

MySQL也提供了“按需使用”配置

你可以将 my.cnf 参数 query_cache_type 设置成 DEMAND

有3个值：0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有 SQL_CACHE 关键词时才缓存

这样对于默认的 SQL 语句都不使用查询缓存

这样对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，如下图所示：

mysql> select SQL_CACHE * from test where ID=5；

查看当前mysql实例是否开启缓存机制：

mysql> show global variables like "%query_cache_type%";

监控查询缓存的命中率：（查看运行的缓存信息）

mysql> show status like '%Qcache%';

Qcache_free_blocks：表示查询缓存中目前还有多少剩余的blocks

如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理

Qcache_free_memory：查询缓存的内存大小

通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整

Qcache_hits：表示有多少次命中缓存

我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想

Qcache_inserts：表示多少次未命中然后插入

意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中

这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。

当然系统刚启动后，查询缓存是空的，这很正常

Qcache_lowmem_prunes：该参数记录有多少条查询因为内存不足而被移除出查询缓存

通过这个值，用户可以适当的调整缓存大小。

Qcache_not_cached：表示因为query_cache_type的设置而没有被缓存的查询数量

Qcache_queries_in_cache：当前缓存中缓存的查询数量

Qcache_total_blocks：当前缓存的block数量

MySQL 8.0 已经移除了查询缓存功能

分析器

如果没有命中查询缓存，就要开始真正执行语句了

首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析

分析器对 SQL 语句解析过程：

1. 分析器先会做“词法分析”：

1）你输入的是由多个字符串和空格组成的一条 SQL 语句

2） MySQL 需要识别出里面的字符串分别是么，代表什么

3） MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句

4）它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”

2. 做完“词法分析”后，就要做“语法分析”：

1）根据词法分析的结果，语法分析器会根据语法规则则，判断你输入的这个 SQL 语句是否满足 MySQL 语法

2）如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒

比如：下面这个语句 from 写成了"rom"

词法分析器原理：

词法分析器分成6个主要步骤完成对sql语句的分析：

1、词法分析

2、语法分析

3、语义分析

4、构造执行树

5、生成执行计划

6、计划的执行

<a class="link" target="_bank" href="https://www.processon.com/diagraming/6393e4826376895515fa19e1">SQL词法分析的过程步骤如下图所示：</a>

SQL语句的分析分为词法分析与语法分析

词法分析由MySQLLex[MySQL自己实现的]完成

语法分析由Bison生成

经过Bison语法分析之后，会生成一个这样的<a class="link" target="_bank" href="https://www.processon.com/diagraming/6393e4826376895515fa19e1">语法树</a>

关于语法树深入研究可以参考这篇wiki文章：<a class="link" target="_bank" href="https://en.wikipedia.org/wiki/LR_parser">https://en.wikipedia.org/wiki/LR_parser</a>

Java也有开源的词法结构分析工具：如 Antlr4

ANTLR从语法生成一个解析器，可以构建和遍历解析树

可以在IDEA工具当中安装插件：antlr v4 grammar plugin

优化器

经过了分析器，MySQL就知道你要做什么了

在开始执行之前，还要先经过优化器处理

1、优化器是在表里有多个索引的时候，决定使用哪个索引

2、在一个语句有多表关联（join）的时候，决定各个表的连接顺序

如下多表关联示例：

mysql> select * from test1 join test2 using(ID) where test1.name=yangguo and test2.name=xiaolongnv;

1）既可以先从表 test1 里面取出 name=yangguo 的记录的 ID 值，再根据 ID 值关联到表 test2，再判断 test2 里面 name的值是否等于 yangguo

2）也可以先从表 test2 里面取出 name=xiaolongnv 的记录的 ID 值，再根据 ID 值关联到 test1，再判断 test1 里面 name的值是否等于 yangguo

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

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段

执行器

1. 先判断一下你对这个表 T 有没有执行查询的权限？（查询在优化器之前也会调用 precheck 验证权限）

如果没有权限，就会返回没有权限的错误

如果有权限，就打开表继续执行

2. 打开表的时候，执行器会根据表的引擎定义，去使用这个引擎提供的接口

对于没有索引的表，执行器的执行流程，如下所示：

mysql> select * from test where id=1;

1）调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 1？

如果不是，则跳过

如果是，则将这行存在结果集中

2）调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行

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

至此，这个语句就执行完成了

对于有索引的表，执行的逻辑也差不多，如下所示：

1）第一次调用的是“取满足条件的第一行”这个接口

2）之后循环取“满足条件的下一行”这个接口

这些接口都是引擎中已经定义好的

慢查询日志中的 rows_examined：

你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行

rows_examined  值就是在执行器每次调用引擎获取数据行的时候累加的

在有些场景下，执行器调用一次在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的

Binlog归档

什么是bin-log呢？

binlog是Server层实现的二进制日志，他会记录我们的crud操作

bin-log的特点

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

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

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

bin-log可以用来恢复数据

因为我们的SQL执行时，会将SQL语句的执行逻辑记录在我们的bin-log当中

如果我们误删了数据库，可以使用bin-log进行归档（恢复数据），不需要跑路

要使用bin-log归档，首先我们得记录bin-log，因此需要先开启MySQL的bin-log功能

开启MySQL的bin-log功能

1. 进入 my.cnf 文件 编辑模式，配置bin-log

2. 配置开启 bin-log，即设置bin-log的目录位置

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

3. 配置 server-id，注意5.7以及更高版本需要配置本项（保证唯一性）

server‐id=123454

4. 配置 bin-log 格式（有3种值：statement、row、mixed）

binlog‐format=ROW

5. 配置 bin-log 执行写入与磁盘同步机制（有2种值：0-由系统决定，事务提交时不刷盘；1-每次执行写入就与磁盘同步，影响性能）

sync‐binlog=1

bin-log 相关命令

1. 查看 bin‐log 是否开启：

mysql> show variables like '%log_bin%';

2. 新开一个最新的 bin‐log 日志：

mysql> flush logs;

3. 查看最后一个 bin‐log 日志的相关信息：

mysql> show master status;

4. 清空所有的 bin‐log 日志：

mysql> reset master;

5. 查看 bin-log 内容：

bin-log 的内容

binlog内容如下：

bin-log 里的内容不具备可读性，所以需要我们自己去判断恢复的逻辑点位，怎么观察呢？

看重点信息，比如 begin、commit 这种关键词信息

可以理解为 begin ~ commit 之间的信息是一个完整的事务逻辑，然后再根据位置 position 判断恢复即可

bin-log 数据归档操作

1. 从 bin-log 恢复全部数据

2. 从 bin-log 恢复指定位置数据

mysql> /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults ‐‐start‐position="408" ‐‐stop‐position="731" /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p fw_test

3. 从 bin-log 恢复指定时间段数据

mysql> /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 fw_test

bin-log 数据归档应用示例

1、定义一个存储过程，写入数据

2、删除数据

mysql> truncate test;

3、利用bin-log归档

4、归档完毕，数据恢复