MySql架构与SQL执行流程

最新推荐文章于 2024-07-29 20:17:23 发布

磐石彬

最新推荐文章于 2024-07-29 20:17:23 发布

阅读量90

点赞数

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

本文链接：https://blog.csdn.net/ccssddnn928/article/details/110305610

版权

MySql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySql的发展历史和版本分支

在这里插入图片描述

一、MySql语句的执行流程

一条查询sql语句是如何执行
![在这里插入图片描述](https://img-blog.csdnimg.cn/20201129103521589.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Njc3NkZG5uOTI4,size_16,color_FFFFFF,t_70

1. 客户端

MySql服务监听端口默认是3306，客户端连接服务端方式有很多。可以是同步、异步、长连接、短链接、TCP和Unix Scoket。Mysql连接有专门连接模块，连接的时候需要验证权限

通过show status 命令模糊匹配Thread，查看MySql当前有多个连接
```
show global status like 'Thread%';
```

字段	含义
Threads_cached	缓存中的线程连接数
Threads_connected	当前打开的连接数
Threads_created	为处理连接创建的线程数
Threads_running	非睡眠状态的连接数，通常指并发连接数

其它

客户端的连接和服务端的线程有什么关系？为什么连接数需要查看线程？
客户端每产生一个连接或者一个会话，在服务端就会创建一个线程来处理。反过来，如果要杀死会话，就是kill线程。既然是分配线程的话，保持连接肯定会消耗服务端的资源。MySql会把那些长时间不活动的（SLEEP）连接自动断开
```
show global variables like 'wait_timeout'; -- 非交互式超时时间，如JDBC程序
show global variables like 'interactive_timeout'; -- 交互式超时时间，如数据库工具 
```
Mysql服务连接数
MySql在5.7版本中默认连接数是151个，最大可设置100000个
```
show variables like 'max_connections'; -- 查看连接数
```
参数级别
MySql中变量参数分别为session和global级别，分别是当前会话中生效和全局生效，但并不是每个参数都有两个级别，比如max_connections只是全局级别。当没有带参数的时候，默认是session级别

2.查询缓存

MySql内部自带一个缓存模块，默认关闭

查询一张100W行没有索引表数据，查询sql一模一样，多次查询效率也相差无几。
主要是MySql自带缓存应用场景有限，第一个是它的要求SQL语句必须一摸一样，中间多个空格以及字母大小写都会认为不同的SQL。其二是表中任何一条数据发生变化，这张表所有缓存都会失效，所以对有大量数据更新的应用，也不合适

⚠️ 在MySql8.0中，查询缓存已经被移除了。

3. 语法解析和预处理器

词法解析
词法解析就是把一个完整的SQL语句拆分成单独每一个单词。比如：
mysql select id where table where id = 1;
会把该sql拆分成8个字符，每个字符类型以及开始位置和结束位置
语法解析
语法解析会对SQL做一些语法检查，比如单引号是否完整，以及根据MySql定义的语法规则，根据SQL语句生成一个数据结构，这个数据结构我们成为解析树。比如：
mysql select name from user_info where id = 1 and age > 30
预处理器
对Sql语句生成解析树后，会检测表名和列名是否存在，检查字段别名是否有歧义或冲突。预处理之后会得到一个新的解析树。

4.查询优化与查询执行计划

查询优化
查询优化器的目的就是根据解析树生成不同的执行计划，然后选择一种最优的执行计划，MySql里面使用的是基于开销(cost)的优化器，根据执行计划开销最小来进行选择。
查询执行计划
优化器能处理那些优化类型：
1. 多个表，以哪张表作为基准表
2. 多个索引，要选择哪个索引
  优化完后，优化器最终会把解析树变成一个查询执行计划，查询执行计划是一个数据结构。
  我们在SQL语句前加上EXPLAIN 就可以看到执行计划的信息；如果要得到更详细的信息，还可以用FORMAT=JSON，或开启optimizer trace
```
explain SELECT * from user_info;  -- 查看执行计划
explain FORMAT = JSON select * from user_info; -- 查看详细执行计划
```

5.存储引擎

存储引擎基本介绍
默认情况下，每个数据有一个自己文件夹，任何一个存储引擎都有一个frm文件，这个是表结构定义文件。
不同的存储引擎存放数据的方式不一样，产生的文件也不一样。
- innodb：1个
- memroy：0个
- myisam：2个
创建表的时候通过ENGINE关键字，指定使用存储引擎。
```
	CREATE TABLE `y_extension_order` (
	  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id 主键id',
	  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
	  PRIMARY KEY (`id`),
	  KEY `bind_relation_id` (`bind_relation_id`)
) ENGINE=InnoDB AUTO_INCREMENT=983 DEFAULT CHARSET=utf8mb4;
```
MySql版本5.5.5之前，默认存储引擎是MyISAM，5.5.5之后存储引擎默认是InnoDB

存储引擎区别

MyISAM:

应用范围比较小。表级锁定限制了读/写的性能，因此在Web和数据仓库配置中，它通常用于只读或以读为主的作用。
特点：
1. 支持表级别的所（插入更新会锁表），不支持事务。
2. 拥有较高的插入(insert)和查询(select)速度。
3. 存储来表的行数(count速度更快)
4. 支持全文类型索引，Innodb不支持全文索引

怎样快速向数据库插入100W条数据？

我们先用MyISAM插入数据，然后修改存储引擎为InnoDB

⚠️ 只读的数据适合数据分析的项目

InnoDB

InnoDB是一个事务安全（与ACID兼容）的MySql存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。
特点：

支持事务，支持外键，因此数据完整性、一致性更高
支持行级别的锁和表级别的锁
支持读写并发，写不阻塞读（MVCC）
特殊的索引存放方式，可以减少IO，提升查询效率

适合：经常更新的表，存在并发读写或者有事务处理的业务系统

Memory

将所有数据存储在RAM中，以便在需要快速查询非关键数据的环境中快速访问，这个引擎以前被称为堆引擎。

特点：

数据存在内存中，读写速度更快，但数据库重启或者崩溃，数据会全部消失，只适合做临时表。

CSV

它的表实际上是带有逗号（，）分隔值的文本文件。csv表允许以csv格式导入或转存数据，以便与读写相同格式的脚本和应用程序交互数据。因此csv表没有索引，所以通常在正常操作期间将数据保持在innodb表中，并且只在导入或导出阶段使用csv表。

特点：

不允许空行，不支持索引。格式通用，可以直接编辑，适合在不同数据库之间导入导出。

如何选择存储引擎

如果对数据一致性要求较高，需要事务支持，可以选择InnoDB
如果数据查询多更新少，对查询性能要求比较高，可以选择MyISM
如果需要一个用于查询的临时表，可以选择Memory

6.执行引擎

数据存储引擎是存储数据形式，具体执行是由执行引擎去根据相应的API来完成操作。

二、MySql的架构与内部模块

1.架构分层

MySql可以划分成三层，跟客户端对接的连接成，执行操作的服务层和跟硬件打交道的存储引擎层。
在这里插入图片描述

连接层
通过客户端连接到MySql服务和3306端口，必须要跟服务建立连接，验证客户端的身份和权限，这些功能就在连接层完成
服务层
连接层会把sql交由服务层，服务层根据sql调用相应的接口，以及sql语句进行词法和语法解析；然后就是优化器，MySql底层会根据一定的规则对sql语句进行优化，最后再交给执行器去执行
存储引擎
存储引擎就是数据真正存放的地方，在MySql里面支持不同的存储引擎。

2.一条SQL是如何执行的

![在这里插入图片描述](https://img-blog.csdnimg.cn/20201214095600169.png

（示例）：

update user_info set age = 18 where id = 1;

流程：

事务开始，从内存（buffer pool）或磁盘（data file）取到包含这条数据的数据页，返回给Server的执行器
Server的执行器修改数据页的这一行数据的值为18
记录 age = 10 到undo log
记录 age = 10 到redo log
调用存储引擎接口，记录数据页到buffer pool（修改age = 18）
提交事务

缓冲池 Buffer Pool
InnoDB存储引擎数据都放在磁盘上的，存储引擎要操作数据，必须先把磁盘里面的数据加载到内存里面才可以操作。

show variables like '%innodb_buffer_pool%';

在这里插入图片描述

局部性原理：不管是操作系统还是存储引擎，当读取磁盘上一块数据时，很有可能会读取该数据位置附近数据也会读取到。

InnoDB设定了一个存储引擎从磁盘读取数据到内存的最小单位，叫做页，默认是16kb。如果要修改这个值大小，需要清空数据重新初始化服务。
InnoDB设计了一个内存的缓冲区。读取数据的时候，先判断是不是在这个内存区域，如果是就直接读取，然后操作，如果不是，读取后就写到内存的缓冲区。修改数据的时候也是先写入到该内存区域，这个内存区域称为Buffer Pool。
InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘，每隔一段时间就一次性的把多个修改写入磁盘，这个动作叫做刷脏。

LRU
传统LRU，可以用Map+链表实现，value存的是在链表中的地址。
在这里插入图片描述

InnoDB中使用了一个双向链表，LRU list。但是这个LRU list放的不是data page，而是指向缓存页的指针。如果写buffer pool的时候发现没有空闲页了，就要从buffer pool中淘汰数据页了，它要根据LRU链表的数据来操作。
InnoDB有一个预读机制，就是访问某个page的数据的时候，相邻的一些page可能会很快被访问到，所以先把这些page放到buffer pool缓存起来。
当预读导致占用内存空间不足情况下，LRU分为两部分，靠近head的叫做new sublist，用来存放热数据（简称热区）。靠近tail的叫做old sublist，用来存放冷数据（简称冷区）。中间分割线叫做midpoint，是对冷热做一个分离。
默认情况下，热区占了5/8大小，冷区占了3/8，这个值有innodb_old_blocks_pct控制，它代表的是old区的大小，默认是37%。innodb_old_blocks_pct值可以调整，在5%到95%之间，值越大，new区越小。如果这个值太小，old区没有被访问的速度淘汰会更快。
通过innodb_old_blocks_time参数控制规定时间内，访问到的数据不做冷热区数据转移，必须等超过规定时间后，再次访问才生效。这样就很大程度避免全表扫描或者预读的数据如染真正热数据。

总结：
所有数据加入到Buffer Pool的时候，一律先放到冷数据区的head，不管是预读的，还是普通的读操作。所以如果有一些预读的数据没有被用到，会在old sublist直接淘汰
放到LRU List以后，如果再次被访问，都把它移动到热区的head。
如果热区的数据长时间没有被访问，会被先移动到冷数据区的head，最后慢慢在tail被淘汰。
Buffer Pool的作用是为了提高读写的效率

Redo log
当Buffer Pool里面的脏页还没有刷入磁盘时，数据库宕机或重启，这些数据就丢失。为了避免该问题，InnoDB把所有对页面的修改操作专门写入一个日志文件。如果有未同步到磁盘的数据，数据库在启动的时候，会从这个日志文件进行恢复操作（实现crash-safe）。也就是我们说的事务的ACID里面D（持久化），就是用它来实现的。

show variables like '%innodb_log%';

在这里插入图片描述

redo log位于/var/lib/mysql目录下的ib_logfile0和ib_logfile1，默认2个文件，每个48M

特点：

redo log是InnoDB存储引擎实现的，并不是所有存储引擎都有。支持崩溃恢复是InnoDB的一个特性
redo log不是记录数据页更新之后的状态，而是记录“在某个数据页上做了什么修改”，属于物理日志
redo log的大小是固定的，前面的内容会被覆盖，一旦写满，就会触发Buffer Pool到磁盘的同步，以便腾出空间记录后面的修改

log buffer 写入磁盘的时机，由一个参数控制，默认是1

show variables like 'innodb_flush_log_at_trx_commit';

在这里插入图片描述

除了redo log之外，还有一个跟修改有关的日志，叫做undo log，redo log和undo log与事务密切相关，统称为事务日志。

Undo log
undo log（撤销日志或回滚日志）记录了事务发生之前的数据状态，分为insrt undo log和update undo log。如何修改数据时出现异常，可以用undo log来实现回滚操作（保持原子性）

show variables like '%undo%';

在这里插入图片描述

三、Binlog

Binlog是以事件的形式记录来所有的DDL和DML语言，可以用来做主从复制和数据恢复，属于逻辑日志。

binlog跟redo log不一样，它的文件内容是可以追加的，没有固定大小限制
在开启binlog功能的情况下，我们可以把binlog导出成sql语言，把所有的操作重放一遍，用来实现数据恢复
binlog的另一个功能就是用来实现主从复制，它的原理就是从服务器读取主服务器的binlog，然后执行一遍。

⚠️重点

先记录到内存，再写日志文件
记录redo log分为两个阶段
存储引擎和Server记录不同的日志
先记录redo log，在记录bin log

为什么需要两阶段提交？
在修改age = 18时，如果redo log写完，bin log还没写完，MySql发生宕机或服务重启情况时，redo log可以在重启时用于数据恢复，所以写入磁盘是age = 18，但是bin log里面没有记录这个逻辑日志，所以- 这时候用bin log去恢复数据或同步到从库时，就会出现数据不一致的情况。