Server层

主要包括连接器、查询缓存、分析器、优化器、执行器等#xff0c;涵盖

MySQL

的大多数核心服务功能#xff0c;以及所有的内置函数#xff08;如日期、…MySQL的内部组件结构如下

大体来说MySQL

的大多数核心服务功能以及所有的内置函数如日期、时间、数学和加密函数等所有跨存储引擎的功能都在这一层实现比如存储过程、触发器、视图等。

存储引擎层

table时不指定表的存储引擎类型,默认会给你设置存储引擎为InnoDB。

连接器

由于MySQL是开源的它有非常多种类的客户端navicat,mysql

front,jdbc,SQLyog等非常丰富的客户端包括各种编程语言实现的客户端连接程序这些客户端要向mysql发起通信都必须先跟Server端建立通信连接而建立连接的工作就是有连接器完成的。

第一步先连接到这个数据库上这时候接待你的就是连接器。

连接器负责跟客户端建立连接、获取权限、维持和管理连接。

连接命令一般是这么写的

[root192

握手后连接器就要开始认证你的身份这个时候用的就是你输入的用户名和密码。

denied

user的错误然后客户端程序结束执行。

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

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

这就意味着一个用户成功建立连接后即使用管理员账号对这个用户的权限做了修改也不会影响已经存在连接的权限。

修改完成后只有再新建的连接才会使用新的权限设置。

查询缓存

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

之前执行过的语句及其结果可能会以

key-value

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

执行完成后执行结果会被存入查询缓存中。

你可以看到如果查询命中缓存MySQL

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

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

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

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

一般建议在静态表里使用查询缓存什么叫静态表呢就是一般极少更新的表。

比如一个系统配置表、字典表那这张表上的查询才适合使用查询缓存。

好在

MySQL

0代表关闭查询缓存OFF1代表开启ON2DEMAND代表当sql语句中有SQL_CACHE关键词时才缓存

SQL

分析器先会做“词法分析”。

你输入的是由多个字符串和空格组成的一条

SQL

从你输入的select这个关键字识别出来这是一个查询语句。

它也要把字符串“T”识别成“表名

T”把字符串“ID”识别成“列

做完了这些识别以后就要做“语法分析”。

根据词法分析的结果语法分析器会根据语法规则判断你输入的这个

SQL

优化器是在表里面有多个索引的时候决定使用哪个索引或者在一个语句有多表关联join的时候决定各个表的连接顺序以及一些MySQL自己内部的优化机制。

执行器

(在工程实现上如果命中查询缓存会在查询缓存返回结果的时候做权限验证)。

mysql

id10;如果有权限就打开表继续执行。

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

Innodb底层原理与Mysql日志机制

%innodb_log_buffer_size%;innodb_log_group_home_dir设置redo

log文件存储位置参数默认值为./即innodb数据文件存储位置其中的

ib_logfile0

%innodb_log_group_home_dir%;innodb_log_files_in_group设置redo

ib_logfile0,

%innodb_log_files_in_group%;innodb_log_file_size设置单个redo

log文件大小默认值为48M。

最大值为512G注意最大值指的是整个

redo

log系列文件之和即(innodb_log_files_in_group

innodb_log_file_size)不能大于最大值512G。

show

从头开始写写完一个文件继续写另一个文件写到最后一个文件末尾就又回到第一个文件开头循环写如下面这个图所示。

write

是当前要擦除的位置也是往后推移并且循环的擦除记录前要把记录更新到数据文件里。

write

innodb_flush_log_at_trx_commit这个参数控制

redo

中数据库宕机可能会丢失数据。

设置为1(默认值)表示每次事务提交时都将

redo

直接持久化到磁盘数据最安全不会因为数据库宕机丢失数据但是效率稍微差一点线上系统推荐这个设置。

设置为2表示每次事务提交时都只是把

redo

cache里这种情况如果数据库宕机是不会丢失数据的但是操作系统如果宕机了page

InnoDB

查看innodb_flush_log_at_trx_commit参数值

show

innodb_flush_log_at_trx_commit;

设置innodb_flush_log_at_trx_commit参数值(也可以在my.ini或my.cnf文件里配置)

set

innodb_flush_log_at_trx_commit1;

binlog二进制归档日志

binlog二进制日志记录保存了所有执行过的修改操作语句不保存查询操作。

如果

MySQL

服务意外停止可通过二进制日志文件排查用户操作或表结构操作从而来恢复数据库数据。

启动binlog记录功能会影响服务器性能但如果需要恢复数据或主从复制功能则好处则大于对服务器的影响。

查看binlog相关参数

版本中binlog默认是关闭的8.0版本默认是打开的。

上图中log_bin的值是OFF就代表binlog是关闭状态打开binlog功能需要修改配置文件my.ini(windows)或my.cnf(linux)然后重启数据库。

log-bin设置binlog的存放位置可以是绝对路径也可以是相对路径这里写的相对路径则binlog文件默认会放在data数据目录下

log-binmysql-binlog

Id是数据库服务器id随便写一个数都可以这个id用来在mysql集群环境中标记唯一mysql服务器集群环境中每台mysql服务器的id不能一样不加启动会报错

server-id1

%log_bin%;log_binbinlog日志是否打开状态

log_bin_basename是binlog日志的基本文件名后面会追加标识来表示每一个文件binlog日志文件会滚动增加

log_bin_index指定的是binlog文件的索引文件这个文件管理了所有的binlog文件的目录。

sql_log_binsql语句是否写入binlog文件ON代表需要写入OFF代表不需要写入。

如果想在主库上执行一些操作但不复制到slave库上可以通过修改参数sql_log_bin来实现。

比如说模拟主从同步复制异常。

binlog

用参数

STATEMENT基于SQL语句的复制每一条会修改数据的sql都会记录到master机器的bin-log中这种方式日志量小节约IO开销提高性能但是对于一些执行过程中才能确定结果的函数比如UUID()、SYSDATE()等函数如果随sql同步到slave机器去执行则结果跟master机器执行的不一样。

ROW基于行的复制日志中会记录成每一行数据被修改的形式然后在slave端再对相同的数据进行修改记录下每一行数据修改的细节可以解决函数、存储过程等在slave机器的复制问题但这种方式日志量较大性能不如Statement。

举个例子假设update语句更新10行数据Statement方式就记录这条update语句Row方式会记录被修改的10行数据。

MIXED混合模式复制实际就是前两种模式的结合在Mixed模式下MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式也就是在Statement和Row之间选择一种如果sql里有函数或一些在执行时才知道结果的情况会选择Row其它情况选择Statement推荐使用这一种。

binlog写入磁盘机制

写入磁盘这种方式最安全。

还有一种折中方式可以设置为N(N1)表示每次提交事务都write

到page

删除指定日志文件之前的所有日志文件下面这个是删除6之前的所有日志文件当前这个文件不删除

purge

D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007

查看bin-log二进制文件带查询条件

D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007

00:00:00

stop-position20000执行mysqlbinlog命令

mysqlbinlog

D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007查出来的binlog日志文件内容如下

/*!50530

OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/;

DELIMITER

session.pseudo_thread_id3/*!*/;

SET

session.sql_mode1342177280/*!*/;

SET

session.auto_increment_increment1,

session.auto_increment_offset1/*!*/;

/*!\C

session.character_set_client33,session.collation_connection33,session.collation_server33/*!*/;

SET

session.collation_databaseDEFAULT/*!*/;

BEGIN

。

。

。

能看到里面有具体执行的修改伪sql语句以及执行时的相关情况。

binlog日志文件恢复数据

用binlog日志文件恢复数据其实就是回放执行之前记录在binlog文件里的sql举一个数据恢复的例子

先执行刷新日志的命令生成一个新的binlog文件mysql-binlog.000008后面我们的修改操作日志都会记录在最新的这个文件里

flush

3;现在需要恢复被删除的两条数据我们先查看binlog日志文件

mysqlbinlog

D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000008文件内容如下

SET

session.pseudo_thread_id5/*!*/;

SET

session.sql_mode1342177280/*!*/;

SET

session.auto_increment_increment1,

session.auto_increment_offset1/*!*/;

/*!\C

session.character_set_client33,session.collation_connection33,session.collation_server33/*!*/;

SET

session.collation_databaseDEFAULT/*!*/;

BEGIN

。

。

。

。

。

。

找到两条插入数据的sql每条sql的上下都有BEGIN和COMMIT我们找到第一条sql

701

D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009

mysql

TIMESTAMP1674833663转成datetime格式

mysqlbinlog

D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009

mysql

注意如果要恢复大量数据比如程序员经常说的删库跑路的话题假设我们把数据库所有数据都删除了要怎么恢复了如果数据库之前没有备份所有的binlog日志都在的话就从binlog第一个文件开始逐个恢复每个binlog文件里的数据这种一般不太可能因为binlog日志比较大早期的binlog文件会定期删除的所以一般不可能用binlog文件恢复整个数据库的。

一般我们推荐的是每天(在凌晨后)需要做一次全量数据库备份那么恢复数据库可以用最近的一次全量备份再加上备份时间点之后的binlog来恢复数据。

命令工具

#恢复整个数据库test为数据库名称需要自己先建一个数据库test为什么会有redo

log和binlog两份日志呢

就可以保证即使数据库发生异常重启之前提交的记录都不会丢失这个能力称为crash-safe。

undo

在MySQL5.5的时候只有一个回滚段那么最大同时支持的事务数量为1024个。

在MySQL

5.6开始InnoDB支持最大128个回滚段故其支持同时在线的事务限制提高到了

128*1024

log文件所在的路径。

该参数的默认值为./即innodb数据文件存储位置目录下ibdata1文件就是undo

innodb_undo_logs:

log文件的数量这样回滚段可以较为平均地分布在多个文件中。

设置该参数后会在路径innodb_undo_directory看到undo为前缀的文件。

undo

log日志什么时候删除

修改类型的事务提交之后不能立即清除掉这些日志会用于mvcc。

只有当没有事务用到该版本信息时才可以清除。

为什么Mysql不能直接更新磁盘上的数据而设置这么一套复杂的机制来执行SQL了

因为来一个请求就直接对磁盘文件进行随机读写然后更新磁盘文件里的数据性能可能相当差。

因为磁盘随机读写的性能是非常差的所以直接更新磁盘文件是不能让数据库抗住很高并发的。

Mysql这套机制看起来复杂但它可以保证每个更新请求都是更新内存BufferPool然后顺序写日志文件同时还能保证各种异常情况下的数据一致性。

更新内存的性能是极高的然后顺序写磁盘上的日志文件的性能也是非常高的要远高于随机读写磁盘文件。

正是通过这套机制才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。

错误日志

Mysql还有一个比较重要的日志是错误日志它记录了数据库启动和停止以及运行过程中发生任何严重错误时的相关信息。

当数据库出现任何故障导致无法正常使用时建议首先查看此日志。

查看错误日志存放位置

通用查询日志记录用户的所有操作包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给

MySQL

指令等如select、show等无论SQL的语法正确还是错误、也无论SQL执行成功还是失败MySQL都会将其记录下来。

通用查询日志用来还原操作时的具体场景可以帮助我们准确定位一些疑难问题比如重复支付等问题。

general_log是否开启日志参数默认为OFF处于关闭状态因为开启会消耗系统资源并且占用磁盘空间。

一般不建议开启只在需要调试查询问题时开启。

show