MySQL-逻辑结构

2021/9/5 19:39:35

本文主要是介绍MySQL-逻辑结构,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

逻辑架构

MySQL的逻辑架构自顶向下大致可分为四层,包括网络连接层、服务层、引擎层和存储层。

2021-05-17-20-45-47-866332.png

MySQL体系架构

img

网络连接层

  • 客户端连接器(Client Connectors):提供与MySQL服务器建立的支持。目前几乎支持所有主流 的服务端编程技术,例如常见的 Java、C、Python、.NET等,它们通过各自API技术与MySQL建立连接。

  • 用户鉴权:当客户端向MySQL服务端发起连接请求后,MySQL server会对发起连接的用户进行鉴权处理,MySQL鉴权依据是: 用户名,客户端主机地址和用户密码

  • 安全管理:当客户连接到MySQL server后,MySQL server会根据用户的权限来判断用户具体可执行哪些操作

    • MySQL 提供的部分权限

      show privileges \G
      

服务层(MySQL Server)

服务层是MySQL Server的核心,主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分

  1. 连接池(Connection Pool):负责存储和管理客户端与数据库的连接,一个线程负责管理一个连接。[官网性能测试报告:引入线程池,性能稳定性与性能会有很大得提升,128并发,读写模式, mysql高出60倍,只读18倍,若不引用线程池,线程创建关闭性能消耗大]

  2. 系统管理和控制工具(Management Services & Utilities):例如备份恢复、安全管理、集群管理等

  3. SQL接口(SQL Interface):用于接受客户端发送的各种SQL命令,并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。

  4. 解析器(Parser):负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法。

  5. 查询优化器(Optimizer):当“解析树”通过解析器语法检查后,将交由优化器将其转化成执行计划,然后与存储引擎交互。

  6. 缓存(Cache&Buffer): 缓存机制是由一系列小缓存组成的。比如表缓存,记录缓存,权限缓 存,引擎缓存等。如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据。

2021-05-17-20-45-47-777591.png

存储引擎层(Pluggable Storage Engines)

存储引擎负责MySQL中数据的存储与提取,与底层系统文件进行交互。MySQL存储引擎是插件式的,服务器中的查询执行引擎通过接口与存储引擎进行通信,接口屏蔽了不同存储引擎之间的差异 。
现在有很多种存储引擎,各有各的特点,最常见的是MyISAM和InnoDB。

文件系统层(File System)

负责将数据库的数据和日志存储在文件系统之上,并完成与存储引擎的交互,是文件的物理存储层。主要包含日志文件,数据文件,配置文件,pid 文件,socket 文件等。

  1. 日志文件
    • 错误日志(Error log)
      默认开启,show variables like '%log_error%'
    • 通用查询日志(General query log)
      记录一般查询语句,show variables like '%general%';
    • 二进制日志(binary log)
      记录了对MySQL数据库执行的更改操作,并且记录了语句的发生时间、执行时长;
      但是它不记录select、show等不修改数据库的SQL。
      主要用于数据库恢复和主从复制。
      show variables like '%log_bin%'; //是否开启
      show variables like '%binlog%'; //参数查看
      show binary logs;//查看日志文件
    • 慢查询日志(Slow query log)
      记录所有执行时间超时的查询SQL,默认是10秒。
      show variables like '%slow_query%'; //是否开启
      show variables like '%long_query_time%'; //时长
  2. 配置文件
    用于存放MySQL所有的配置信息文件,比如my.cnf、my.ini等。
  3. 数据文件
    • db.opt 文件:记录这个库的默认使用的字符集和校验规则。
    • frm 文件:存储与表相关的元数据(meta)信息,包括表结构的定义信息等,每一张表都会有一个frm 文件。
    • MYD 文件:MyISAM 存储引擎专用,存放 MyISAM 表的数据(data),每一张表都会有一个.MYD 文件。
    • MYI 文件:MyISAM 存储引擎专用,存放 MyISAM 表的索引相关信息,每一张 MyISAM 表对应一个 .MYI 文件。
    • ibd文件和 IBDATA 文件:存放 InnoDB 的数据文件(包括索引)。
      InnoDB 存储引擎有两种表空间方式:独享表空间和共享表空间。
      • 独享表空间使用 .ibd 文件来存放数据,且每一张InnoDB 表对应一个 .ibd 文件。
        享表空间使用 .ibdata 文件,所有表共同使用一个(或多个,自行配置).ibdata 文件。
    • ibdata1 文件:系统表空间数据文件,存储表元数据、Undo日志等 。
    • ib_logfile0、ib_logfile1 文件:Redo log 日志文件。
  4. pid 文件
    pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件,和许多其他 Unix/Linux 服务端程序一样,它存放着自己的进程 id。
  5. socket 文件
    socket 文件也是在 Unix/Linux 环境下才有的,用户在 Unix/Linux 环境下客户端连接可以不通过TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL。

InnoDB体系结构

当前使用比较多的是MyISAM和Innodb 存储引擎,在MySQL8.0后不在支持MyISAM,默认都是用Innodb。

Innodb 体系结构是由多个内存块组成的缓冲池及多个后台进程组成。主要包括内存,线程和磁盘文件。

image

内存结构

buffer pool (缓冲池)

以Page页为单位,默认大小16K。底层采用链表结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存,以减少磁盘IO操作,提升效率。

Page管理机制
按状态分为三种类型
  • free page :空闲page,未被使用
  • clean page:被使用page,数据没有被修改过
  • dirty page:脏页,被使用page,数据被修改过,页中数据和磁盘的数据产生了不一致
对上述三种类型,使用不同链表结构管理
  • free list :表示空闲缓冲区,管理free page

  • flush list:表示需要刷新到磁盘的缓冲区,管理dirty page,内部page按修改时间排序。

    • 脏页即存在于flush链表,也在LRU链表中,但是两种互不影响
    • LRU链表负责管理page的可用性和释放,而flush链表负责管理脏页的刷盘操作。
  • lru list:表示正在使用的缓冲区,管理clean page和dirty page

    • 缓冲区以midpoint为基点,前面链表称为new列表区
    • 存放经常访问的数据,占63%;后面的链表称为old列表区,存放使用较少数据,占37%。
LRU 算法
普通LRU

末尾淘汰法,新数据从链表头部加入,释放空间时从末尾淘汰

改进型LRU

链表分为new和old两个部分,加入元素时并不是从表头插入,而是从中间midpoint位置插入,如果数据很快被访问,那么page就会向new列表头部移动,如果数据没有被访问,会逐步向old尾部移动,等待淘汰。

每当有新的page数据读取到buffer pool时,InnoDb引擎会判断是否有空闲页,是否足够,如果有就将free page从free list列表删除,放入到LRU列表中。没有空闲页,就会根据LRU算法淘汰LRU链表默认的页,将内存空间释放分配给新的页

配置参数
show variables like '%innodb_page_size%'; --查看page页大小
show variables like '%innodb_old%'; --查看lru list中old列表参数
show variables like '%innodb_buffer%'; --查看buffer pool参数

建议:将innodb_buffer_pool_size设置为总内存大小的60%-80%,
innodb_buffer_pool_instances可以设置为多个,这样可以避免缓存争夺。

change buffer (写缓冲区)

在进行DML操作时,如果Buffer Pool(BP)没有其相应的Page数据,并不会立刻将磁盘页加载到缓冲池,而是在Change Buffer记录缓冲变更,等未来数据被读取时,再将数据合并恢复到BP中。
Change Buffer占用Buffer Pool空间,默认占25%,最大允许占50%,可以根据读写业务量来进行调整。

参数
show variables like 'innodb_change_buffer_max_size';

当更新一条记录时,该记录在Buffer Pool存在,直接在Buffer Pool修改,一次内存操作。如果该记录在Buffer Pool不存在(没有命中),会直接在Change Buffer进行一次内存操作,不用再去磁盘查询数据,避免一次磁盘IO。当下次查询记录时,会先进行磁盘读取,然后再从Change Buffer中读取信息合并,最终载入Buffer Pool中。

log buffer (日志缓冲区)

用来保存要写入磁盘上log文件(Redo/Undo)的数据,日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘,当遇到 BLOB或多行更新的大事务操作时,增加日志缓冲区可以节省磁盘I/O。
Log Buffer主要是用于记录 InnoDB引擎日志,在 DML操作时会产生RedoUndo日志。
Log Buffer空间满了,会自动写入磁盘。可以通过将 innodb_log_buffer_size 参数调大,减少磁盘IO频率。innodb_flush_log_at_trx_commit参数控制日志刷新行为,默认为1。

innodb_flush_log_at_trx_commit参数值
  • innodb_flush_log_at_trx_commit=1: 事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据,但是因为每次提交都写入磁盘,IO的性能较差。
  • innodb_flush_log_at_trx_commit=0: 事务提交时不会将log buffer中日志写入到os buffer,而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的,当系统崩溃,会丢失1秒钟的数据。
  • innodb_flush_log_at_trx_commit=2: 每次提交都仅写入到os buffer,然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。
刷盘过程

刷盘过程

磁盘结构

InnoDB 存储结构主要有Tablespaces,InnoDB Data Dictionary,Doublewrite Buffer、Redo Log 和 Undo Logs。

Tablespaces(表空间)

表空间用于存储表结构和数据。

分类
系统表空间

系统表空间存储 InnoDB 数据字典,Doublewrite Buffer,Change Buffer,Undo Logs。任何用户创建的表数据和索引数据不指定独立表空间时,也会存储在系统表空间中。系统表空间是一个共享的表空间,它被多个表共享。

系统表空间的数据文件通过参数innodb_data_file_path控制,默认值是ibdata1:12M:autoextend(文件名为ibdata1、12MB、自动扩展)

show variables like 'innodb_data_file_path';
独立表空间

独立表空间是一个单表表空间,单独为该表创建自己的数据文件,而不是在系统表空间中。当innodb_file_per_table选项开启时,表将被创建于表空间中。否则,innodb将被创建于系统表空间中。每个表文件表空间由一个.ibd数据文件表示,该文件默认被创建于数据库目录中。表空间的表文件支持动态(dynamic)和压缩(commpressed)行格式。

show variables like 'innodb_file_per_table';
常规表空间

通过create tablespace语法创建的共享表空间,可以创建在mysql数据目录外其它表空间

CREATE TABLESPACE ts1 ADD DATAFILE ''/data1/ts1.ibd' Engine=InnoDB;
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
临时表空间

临时表空间分为:session temporary tablespace 和 global temporary tablespace

  • session temporary tablespace:存储的是用户创建的临时表和磁盘内部的临时表
  • global temporary tablespace:存储用户临时表的回滚段(rollback segments )
Undo表空间

由一个或多个包含Undo日志文件组成,在MySQL 5.6版本之前Undo放在系统表空间共享区。从5.6新增参数innodb_undo_directory、innodb_undo_logs和innodb_undo_tablespaces这3个参数,可以把undo log从ibdata1移出来单独存放。需要在初始化时配置独立的Undo表空间

  • innodb_undo_directory,指定单独存放undo表空间的目录,默认为.(即datadir),可以设置相对路径或者绝对路径。该参数实例初始化之后虽然不可直接改动,但是可以通过先停库,修改配置文件,然后移动undo表空间文件的方式去修改该参数;
  • innodb_undo_tablespaces,指定单独存放的undo表空间个数,例如如果设置为3,则undo表空间为undo001、undo002、undo003,每个文件初始大小默认为10M。该参数我们推荐设置为大于等于3,原因下文将解释。该参数实例初始化之后不可改动;
  • innodb_undo_logs,指定回滚段的个数(早期版本该参数名字是innodb_rollback_segments),默认128个。每个回滚段可同时支持1024个在线事务。这些回滚段会平均分布到各个undo表空间中。该变量可以动态调整,但是物理上的回滚段不会减少,只是会控制用到的回滚段的个数。

MySQL 5.7引入了新的参数,innodb_undo_log_truncate,开启后可在线收缩拆分出来的undo表空间。

undo表空间文件可在线收缩的条件:

  • innodb_undo_tablespaces>=2。因为truncate undo表空间时,该文件处于inactive状态,如果只有1个undo表空间,那么整个系统在此过程中将处于不可用状态。为了尽可能降低truncate对系统的影响,建议将该参数最少设置为3;
  • innodb_undo_logs>=35(默认128)。因为在MySQL 5.7中,第一个undo log永远在系统表空间中,另外32个undo log分配给了临时表空间,即ibtmp1,至少还有2个undo log才能保证2个undo表空间中每个里面至少有1个undo log;

undo表空间的自动truncate相关参数:

  • innodb_max_undo_log_size,undo表空间文件超过此值即标记为可收缩,默认1G,可在线修改
  • innodb_purge_rseg_truncate_frequency,指定purge操作被唤起多少次之后才释放rollback segments。当undo表空间里面的rollback segments被释放时,undo表空间才会被truncate。由此可见,该参数越小,undo表空间被尝试truncate的频率越高
创建表空间语法
CREATE TABLESPACE tablespace_name

  InnoDB and NDB:
    ADD DATAFILE 'file_name'

  InnoDB only:
    [FILE_BLOCK_SIZE = value]

  NDB only:
    USE LOGFILE GROUP logfile_group
    [EXTENT_SIZE [=] extent_size]
    [INITIAL_SIZE [=] initial_size]
    [AUTOEXTEND_SIZE [=] autoextend_size]
    [MAX_SIZE [=] max_size]
    [NODEGROUP [=] nodegroup_id]
    [WAIT]
    [COMMENT [=] 'string']

  InnoDB and NDB:
    [ENGINE [=] engine_name]
查看表空间
select * from information_schema.INNODB_SYS_TABLESPACES ;

段(segment)

段是表空间的主要组织结构,用来管理物理文件。创建一个索引(B+树)时,默认同时创建两个段,分别是内节点段(存储主键)和叶子节点段(存储数据)

区(extent)

区是由物理上连续分配的一段空间(每个区的大小都为1MB),默认innodb页(16K)的情况下,一个区由64个连续页组成。【16K * 64 = 1M】

页(page)

页是文件管理的最小单位,默认每个页的大小为16KB。innodb_page_size参数可以配置页面大小(2KB,4KB, 8KB,16KB等)

常见的页类型:

  • 数据页(B-tree Node)
  • undo页(undo Log Page)
  • 系统页(System Page)
  • 事务数据页(Transaction system Page)
  • 插入缓冲位图页(Insert Buffer Bitmap)
  • 插入缓冲空闲列表页(Insert Buffer Free List)
  • 未压缩的二进制大对象页(Uncompressed BLOB Page)
  • 压缩的二进制大对象页(compressed BLOB Page)

行(row)

关系型数据库中数据是按行进行存放的。

Redo Log(重做日志)

redo log 记录的是数据库中每个页的修改,而不是某一行或某几行修改成怎样,可以用来恢复提交后的物理数据页,且只能恢复到最后一次提交的位置。

Undo Log(回滚日志)

undo log的作用是提供事务的回滚和多个行版本控制(MVCC-非锁定读)。undo log是逻辑日志,如执行一条delete操作时,undo log将它的反向操作记录下来,undo log也会产生redo日志。当事务失败需要回滚时,就可以从undo log中的逻辑记录进行回滚到修改前的样子。



这篇关于MySQL-逻辑结构的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程