InnoDB底层原理与MySQL日志机制深入剖析_innodb底层原理与mysql日志机制剖析-CSDN博客

MySQL内部组件结构

在这里插入图片描述

大体来说,MySQL可以分为Server层和存储引擎层两部分

Server层

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

连接器

我们知道由于MySQL时开源的,他有非常多种类的客户端,navicat,jdbc等非常丰富的客户端,包括各种编程语言实现的客户端连接程序,这些客户端要向MySQL发起通信都必须要先跟Server端建立通信连接,而建立连接的工作就是由连接器完成的.

第一步,你会先连接到这个数据库上,这个时候接待你的就是连接器,连接器负责跟客户端建立连接,获取权限,维持和管理连接.连接命令一般是这么写的:

mysql -h 127.0.0.1(数据库地址) -uroot(用户名) -proot(密码) -P 3306(端口)

连接命令中的mysql是客户端工具,用来跟服务端建立连接,在完成经典的TCP握手后,连接器就开始认证你的身份,这个时候用的就是你输入的用户名和密码.

如果用户名/密码不对,你就会收到一个"ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)"的错误,然后客户端程序结束执行
如果用户名密码认证通过,连接器会到权限表里面查出你拥有的权限.之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限.(这就意味着,一个用户成功建立连接后,即使你用管理员账号对这个用户的权限做了修改,也不会影响已经存在连接的权限.修改完成后,只有再新建的连接才会使用新的权限设置)

查询缓存

连接完成后,就可以执行select语句了,执行逻辑就会来到第二步:查询缓存.

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

如果查询语句能够直接在这个缓存中找到对应的key.那么这个value就会被直接返回给客户端.
如果语句不在查询缓存中,就会继续后面的执行阶段.执行完成后,执行结果会被存入查询缓存中.如果命中缓存.MySQL不需要执行后面的复杂操作,就可以直接返回结果,这个效率会很高.

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

因为查询缓存往往弊大于利.查询缓存的失效非常频繁,只要又对一个表的更新,这个表上所有的查询缓存都会被清空.对于写多读少的数据库来说,查询缓存的命中率会非常低

一般建议在静态表(比如系统配置表,字典表)使用查询缓存,这种表极少更新.查询缓存的命中率会很高.MySQL也提供了这种"按需使用"的方式.可以将my.cnf参数query_cache_type设置为DEMAND.

query_cache_type=2

query_cache_type有3个值

0(默认值)(OFF):关闭查询缓存
1(ON):开启查询缓存
2(DEMAND):当sql语句中有SQL_CACHE关键词时才缓存

这样对于默认的SQL语句都不使用查询缓存,而对于确定使用查询缓存的语句,可以用SQL_CACHE显示指定.如下:

select SQL_CACHE * from employees where id = 5;

查看mysql是否开启缓存机制

show global variables like "%query_cache_type%";

在这里插入图片描述

MySQL8.0已经移出了查询缓存

分析器

如果没有命中查询缓存,就要开始真正执行语句了.首先,MySQL需要知道你做什么,因此需要对sql语句做解析.

分析器会先做"词法分析".你输入的是由多个字符串和空格组成的一条sql语句,MySQL需要识别出里面的字符串分别是什么,代表什么

MySQL从你输入的"select"这个关键字识别出来,这是一个查询语句.它也要把字符串"T"识别成"表名 T",把字符串"ID"识别成"列ID".

做完这些识别以后,就要做"语法分析".根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个sql语句是否满足MySQL语法.如果你的语句不催,就会收到错误提醒,

select * fro employees where id = 5;

在这里插入图片描述

分析器对sql的分析过程步骤:

在这里插入图片描述

SQL语句经过分析器分析之后,会生成一个这样的语法树

在这里插入图片描述

优化器

经过了分析器,MySQL就知道你要做什么了.在开始执行之前,还要经过优化器的处理.

优化器是在表里面有多个索引的时候,决定使用那个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接数据;以及MySQL自己内部的优化机制

执行器

开始执行的时候,要先判断一下你对这个表T有没有执行查询的权限,如果没有,就会返回没有权限的错误

如果有权限,就打开表继续执行,打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口.

存储引擎层

存储引擎层负责数据的存储和提取.其架构是插件式的,支持InnoDB,MyISAM,Memory等多个存储引擎.现在最常用的存储引擎是InnoDB,它从MySQL5.5.5版本开始成为了默认的存储引擎.也就是说如果我们在create table时不指定表的存储引擎类型.默认会给你设置存储引擎为InnoDB.

Innodb底层原理与MySQL日志机制

在这里插入图片描述

redo日志

innodb_log_buffer_size:设置redo log buffer大小参数,默认16M,最大值是4096M,最小值为1M.

show variables like '%innodb_log_buffer_size%';

在这里插入图片描述

innodb_log_group_home_dir:设置redo log文件存储位置参数,默认为"./",即innodb数据文件存储位置,其中的ib_logfile0和ib_logfile1即为redo log文件.

show variables like '%innodb_log_group_home_dir%';

在这里插入图片描述

innodb_log_files_in_group:设置redo log文件的个数,命名方式如:ib_logfile0,ib_logfile1…ib_logfileN.默认2个,最大100个.

show variables like '%innodb_log_files_in_group';

在这里插入图片描述

innodb_log_file_size:设置单个redo log文件大小,默认值为48M,最大值为512G,注意最大值指的是整个redo log系列文件之和,即(innodb_log_files_group*innodb_log_file_size)不能大于最大值512G.

show variables like '%innodb_log_file_size%';

在这里插入图片描述

redo log写入磁盘过程分析:

redo log从头开始写,写完一个文件继续写另一个文件,写到最后一个文件末尾又回到第一个文件开头循环写,如下图所示:

在这里插入图片描述

write pos:是当前记录的位置,一边写一边后移,写到第3号文件末尾后,就回到0号文件开头.

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

write pos和check point之间的部分就是可写部分,用来记录新的操作.如果write pos追上check point表示redo log写满了,这时候不能再执行新的更新,得停下来先擦掉一些记录,把check point推进一下.

innodb_flush_log_at_trx_commit:这个参数控制redo log的写入策略:

0:表示每次事务提交时都只是把redo log留在redo log buffer中,数据库宕机可能会丢失数据.
1(默认值):表示每次事务提交时都将redo log直接持久化到磁盘,数据最安全,不会因为数据库宕机丢失数据,但是效率稍微差一点(生产环境推荐设置).
2:表示每次事务提交时都只是把redo log写到OS的缓存page cache里,这种情况如果数据库宕机是不会丢失数据的,但是操作系统如果宕机了,page cache里的数据还没来得及写入磁盘文件的话就会丢失数据.

Innodb有一个后台线程,每隔1秒,就会把redo log buffer中的日志,调用OS函数write写到文件系统的page cache,然后调用OS函数fsync持久化到磁盘文件

redo log写入策略:

在这里插入图片描述

-- 查看innodb_flush_log_at_trx_commit参数值
show variables like 'innodb_flush_log_at_trx_commit';
-- 设置innodb_flush_log_at_trx_commit参数值(也可以再my.cnf文件里配置);
set global innodb_flush_log_at_trx_commit = 1;

在这里插入图片描述