mysql之server层-sql底层执行过程

chenchen_cui
258 阅读7分钟

在这一小节,我们只关注server层,而不关注执行引擎层

mysql-server层

mysql整体逻辑结构图

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

连接器

建立连接的命令 mysql ‐h host[数据库地址] ‐u root[用户] ‐p root[密码] ‐P 3306 连接完成后，如果没有后续的请求(command)访问，这个连接就处于空闲状态;

则在 show processlist 中显示为sleep

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: <remote_host>
     db: mysql
Command: Sleep //连接完成,但是没有后续请求,则连接处于空闲状态
   Time: 1030455
  State: 
   Info: NULL

客户端如果长时间不发送command到Server端，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

mysql> show global variables like "wait_timeout";
mysql> set global wait_timeout = 28800; // 设置全局服务器关闭非交互连接之前等待活动的秒数

关于连接的问答板块
如何查看 MySQL 服务被多少个客户端连接了？
show processlist

空闲连接会一直占用着吗？
当然不是了，MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28880秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。

MySQL 的连接数有限制吗？
MySQL 服务支持的最大连接数由 max_connections 参数控制，比如我的 MySQL 服务默认是 151 个,超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。
只要是长连接,则服务器都需要维护它,并且随着长连接的累计,服务占用内存就会增加,进而导致OOM;

怎么解决长连接占用内存的问题？
第一种，客户端定期断开长连接。
第二种，客户端主动重置连接。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口，注意这是接口函数不是命令，那么当客户端执行了一个很大的操作后，在代码里调用 mysql_reset_connection 函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

思考题:我们常用的druid-连接池,对于空闲连接如何处理?
当访问流量大的时候，连接池中的连接数会升级到maxActive(最大连接数)的配置，但是当流量下来后，我们需要回收部分空闲的链接，最终让连接池中保留至少minIdle(最小连接数)的配置。回收的过程就需要定时对连接进行检查，判断连接是否应该回收。每隔timeBetweenEvictionRunsMillis<检查连接空闲周期>对连接池的连接做一次检查，如果有连接空闲时间超过minEvictableIdleTimeMillis<连接保持空闲而不被驱逐的最小时间>就回收该链接。

可以看到,在连接池端,解决过多长连接的思路,即为定期断开长连接(释放空闲连接)!

查询缓存
大多数情况查询缓存就是个鸡肋，为什么呢？
因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。
需要注意的是，MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。

词法分析
sql语句,实际上就是一个特殊的语法;若感兴趣,可以研究下antlr4;
请自行在gitHub上搜索antlr4;这不是本小节重点;
优化器
优化器 1.当查询使用join时,mysql会自动帮你确定驱动表和被驱动表;(紫色,请自行搜索)
2.选择索引,之前Explain解析中存在possible_key,此处优化器,会大致估算执行成本;
而使用二级索引的执行成本,很多情况下,是由回表次数决定的;在后面小节会再次讨论!

接下来重点围绕mysql-server层的cache和buffer来详细描述server层的作用!
BinLog 以及 BinLog-Cache
Binlog(增量写日志)是MySQL-Server层特有的日志,binlog会记录sql(增量操作)执行逻辑记录;
binlog主要目的:
1、备份mysql数据,防止数据丢失;
2、mysql主从集群复制时,使用binlog,进行主从复制;
在整个MySQL体系结构中，Redo Log和Binlog二者相结合才能保证关系型数据库ACID的特性。
在之后会再次重点介绍innodb中由Redo Log和Binlog保证的ACID特性!
这里先简单解释下,为了保证mysql内存和磁盘中数据的一致性,那么我们在提交数据从内存再到磁盘的过程,必定需要存在一个二阶段过程!只有当我们成功写入binlog时,我们才能说redlog中的数据是提交完成的;然后才能刷新数据到磁盘!
binlog的写入机制

事务块TRX

IN MEMORY

BINLOG CACHE

事务为BINLOG执行单位

BINLOG CACHE

WRITE

WRITE

LINUX内核态

FILE SYSTEM PAGE CAHCE

FSYNC

BINLOG FILES

实际磁盘

MYSQL-BIN.00002

MYSQL-BIN.00001

MYSQL-BIN.INDEX

image.png

page cache
本质上linux内核管理内存区域，应用程序通过mmap以及sendfile方式通过Buffer_io将文件读取到内存空间，实际上就是读取到即为内核态中的page_cache;
比方我们常说的零拷贝中的linux内核态对应的缓冲区,其实就是pageCache;

零拷贝技术( DMA、PageCache)

write和fsync
fsync 实际上就是linux中系统命令,在mysql中通过调用c语言的fsync实现!
write，指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快。
fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为fsync才占磁盘的IO。

write 和fsync的时机，是由参数sync_binlog控制的：
●sync_binlog=0的时候，表示每次提交事务都只write，不fsync；最佳性能
●sync_binlog=1的时候，表示每次提交事务都会执行fsync；强一致
●sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。
因此，在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其设置为100~1000中的某个数值。
但是，将sync_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。
经常听到：MySQL的双“1”设置。其实双“1”就代表：innodb_flush_log_at_trx_commit=1 && sync_binlog=1，因为在这种组合配置下，MySQL的数据是最有保障的。

相关重要配置
●参数：binlog_cache_size
含义：设置Binlog Cache的大小，默认大小为8MB，单位：B（字节）。
●参数：sync_binlog
含义：控制binlog文件的刷盘策略，可选的参数为0、1或N。
binlog简单操作命令

#查看配置
show variables like '%log_bin%'; 查看bin‐log是否开启

#操作binlog
flush logs; 在新的bin‐log中,增加日志
show master status; 查看最后一个bin‐log日志信息
show master logs;查看所有的bin-log文件
reset master; 清空所有的bin‐log日志

#查看binlog
mysqlbinlog ‐‐no‐defaults <bin_log.000001>
#以解密方式查看binlog
mysqlbinlog --base64-output=decode-rows -v <bin_log.000001>

#从bin‐log恢复数据
#恢复全部数据
mysqlbinlog ‐‐no‐defaults <bin_log.000001> | mysql ‐uroot ‐p <database>
#恢复指定位置数据
mysqlbinlog ‐‐no‐defaults ‐‐start‐position=<p0> ‐‐stop‐position=<p1><bin_log.000001>
| mysql ‐uroot ‐p <database>
#恢复指定时间段数据
mysqlbinlog ‐‐no‐defaults <bin_log.000001>
‐‐stop‐date= <d0> ‐‐start‐date= <d1> | mysql ‐uroot ‐p <database>

mysqlbinlog命令 是由mysql在bin目录默认提供的命令

avatar
文章
阅读
粉丝