MySQL架构

236 阅读13分钟

MySQL架构

逻辑结构

简版SQL执行流程如下图所示

image.png MySQL的逻辑架构主要分为server层和存储引擎层。server层主要分为:连接器、解析器、优化器、查询缓存。

  • 连接器:主要作用就是用户认证,给用户创建连接并使用连接池维护连接。

  • 查询缓存:建立连接之后,就可以进行SQL操作了,如果该SQL语句在查询缓存中可以匹配到,则不需要找后面的模块了,但是查询缓存不建议使用,查询缓存中缓存的记录,如果对于的表发生写操作,那么这些记录都会从缓存中删除,在MySQL8以后,该功能直接去掉了,在查询缓存中,key就是SQL语句,value就是对应的查询结果。

  • 解析器:词法分析,语法解析

    • 词法解析:根据空格将SQL语句拆分,拆分出来的SQL关键字和非关键字(表、列)。
    • 语法解析:将SQL进行语法校验,是否符合SQL92标准或者MySQL的语法标准,并且在此分析该SQL是DDL,还是DML,还是DDL。
  • 优化器:优化索引,在此时会选择一个索引使用,比关切在此时形成执行计划(如何使用索引,如何多表关联)。

  • 存储引擎层:是真正和数据的读写有关系的,存储引擎曾是MySQL模块中真正和物理磁盘进行交互的部分,而存储引擎你高层在MySQL是采用插件式方式的。

简单对于下面的一条简单的SQL分析如下:

客户端:

select * from t where id=1;

服务端:

  • 1.调用连接器,去用户认证,建立连接,查询该用户的权限。
  • 2.查询缓存,缓存中如果有记录,则直接返回结果。
  • 3.调用解析器,对SQL语句进行词法分析和语法分析,也会进行SQL语法校验。
  • 4.调用优化器,对SQL生成执行计划,并且在此时选择哪个索引进行查询。
  • 5.调用执行器,按照SQL的语句的执行计划,去调用存储引擎曾,完成操作。
  • 6.把查询结果返回给客户端,并且保存在查询缓存中。

详细的逻辑架构如下:

image.png

1.连接层:最上层是一些客户端和连接服务,包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2.服务层:第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如过程、函数等。在该层,服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化如确定查询表的顺序,是否利用索引等,最后生成相应的执行操作。如果是select语句,服务器还会查询内部的缓存。如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能。

image.png

3.引擎层:存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过APl与存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB

4.存储层:数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互。

MySQL 部件

  • 1.Connectors:指的是不同语言中与SQL的交互

  • 2.Management Serveices & Utilities: 系统管理和控制工具

  • 3.Connection Pool:连接池,管理缓冲用户连接,线程处理等需要缓存的需求。负责监听对 MySQL Server 的各种请求,接收连接请求,转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配(或创建)一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信。接受客户端的命令请求,传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建,线程的 cache 等。

  • 4.SQL Interface:SQL接口。接受用户的SQL命令,并且返回用户需要查询的结果。比如select from就是调用SQL Interface

  • 5.Parser:解析器,SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的,是一个很长的脚本。在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 Query,在 MySQL Server 里面,连接线程接收到客户端的一个 Query 后,会直接将该 Query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。解析器的主要功能:

    • 将SQL语句进行语义和语法的分析,分解成数据结构,然后按照不同的操作类型进行分类,然后做出针对性的转发到后续步骤,以后SQL语句的传递和处理就是基于这个结构的。
    • 如果在分解构成中遇到错误,那么就说明这个sql语句是不合理的
  • 6.Optimizer:查询优化器,SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端发送过来的 sql 语句 ,根据客户端请求的 query 语句,和数据库中的一些统计信息,在一系列算法的基础上进行分析,得出一个最优的策略,告诉后面的程序如何取得这个 query 语句的结果,他使用的是“选取-投影-联接”策略进行查询。

    • 用一个例子就可以理解: select uid,name from user where gender = 1;
    • 这个select 查询先根据where 语句进行选取,而不是先将表全部查询出来以后再进行gender过滤
    • 这个select查询先根据uid和name进行属性投影,而不是将属性全部取出以后再进行过滤将这两个查询条件联接起来生成最终查询结果
  • 7.Cache和Buffer:查询缓存,他的主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中,与该 query 的一个 hash 值 做一个对应。该 Query 所取数据的基表发生任何数据的变化之后, MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中, Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存,记录缓存,key缓存,权限缓存等

  • 8.存储引擎接口,存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中,基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是 一个抽象类,但正是因为它成功地将各种数据处理高度抽象化,才成就了今天 MySQL 可插拔存储引擎的特色。从上图还可以看出,MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持,这些标准与存储引擎本身无关,可能是每个数据库系统本身都必需的,如SQL分析器和优化器等,而存储引擎是底层物理结构的实现,每个存储引擎开发者都可以按照自己的意愿来进行开发。

注意:存储引擎是基于表的,而不是数据库。存储引擎是跟表有关系的,即表建立的可以指定,不同的表可以使用不同的存储引擎,比如create table末尾可以指定engines = innodb

MySQL存储引擎种类如下:

image.png

查看存储引擎:

mysql>show engines;

InnoDB和MyISAM存储引擎区别:

image.png

物理结构

  • MySQL是通过文件系统对数据和索引进行存储的。
  • MySQL从物理结构上可以分为日志文件和数据索引文件。

MySQL在Linux中的数据索引文件和日志文件都在/var/lib/mysql目录下。配置文件在/etc/my.cnf.

  • 顺序IO:在磁盘中存储是顺序的,分布在同一个磁道中,如果该磁道写满则换一个磁道,只需要记住首地址和偏移量即可,读取较快,因为节省了磁盘中的磁臂的寻道时间。

  • 随机IO:在磁盘中存储不是顺序的,是随机的。读取速度较慢,,不同的数据分布在不同的磁道中,比较乱,寻道时间较长。

InnoDB和MyISAM索引文件和数据文件区别如下:

InnoDB:

  • .frm文件:主要存放与表相关的数据信息,主要包括表结果定义信息。
  • .ibd文件:使用独享表空间存储表数据和索引信息,一张表对应一个.ibd文件。
  • ibdata文件:使用共享表空间存储表数据和索引信息,所有表共同使用一个或者多个ibdata文件,ibdata文件里面存储字典信息,也有跟事务相关的undo文件

MyISAM

  • .frm文件:主要存放与表相关的数据信息,主要包括表结果定义信息。
  • .myd文件:主要用来存储表数据信息
  • .myi文件:主要用来存储表数据文件中任何索引的数据树。

日志文件(顺序IO)

MySQL通过日志记录了数据库操作信息和错误信息。常用的日志文件包括错误日志、二进制日志、查询日志、慢查询日志和事务redo日志、中继日志等等。

可以通过命令查看当前数据库中的日志使用信息:

show variables like 'log_%';

image.png

错误日志(err log)

默认是开启的,而且从mysql5.5.7以后无法关闭错误日志,错误日志记录了运行过程中遇到的所有严重的错误信息,以及MySQL每次启动和关闭的详细信息。

默认的错误日志名称:hostname.err。

错误日志所记录的信息是可以通过log_error和log_warnings来定义的,其中log_err是定义是否启用错误日志的功能和错误日志的存储位置,log_warnings是定义是否将警告信息也定义至错误日志中。

  • log_error可以直接定义为文件路径,也可以为ON|OFF
  • log_warnings之呢个使用1|0来定义开关启动

image.png

如上图所示,MySQL的默认的错误日志存储路径:/var/log/mysqld.log

二进制日志(bin log)

默认是关闭的,需要在/etc/my,cnf配置文件中的添加下面这一行的配置:

log#-bin=mysql-bin

进行开启,其中mysql-bin是bin log日志文件的basename,bin log日志文件的名称:mysql-bin-00001.log

bin log记录了数据库所有DDL语句和DML语句,但不包括select语句内容,语句以事件的形式保存,描述了数据的变更顺序,bin log还包括了每个更新语句的执行时间信息,bin log主要作用是用于恢复数据,因此bin log对于灾难恢复和备份恢复来说至关重要。

注意:

  • 如果是DDL语句,则直接记录到bin log日志中,而DML语句,必须通过事务提交才能记录到bin log日志中。
  • bin log主要用于实现mysql主从复制,数据备份,数据恢复。

通用查询日志(general query log)

默认情况下通用查询日志是关闭的。由于通用查询日志会记录用户的所有操作,其中包括增删改查等信息,在并发操作大环境下会产生大量的信息,从而导致不必要的磁盘IO,会影响mysql的性能的,如若不是为了调试数据库的目的建议不要开启查询日志。

慢查询日志(slow query log)

默认是关闭的,需要在/etc/my.cnf配置文件通过以下设置进行开启:

slow_query_log=ON --开启慢查询日志
long_query_time=10 --设置时间

慢查询日志会记录执行时间超过long_query_time秒的所有查询,便于收集查询时间比较长的SQL语句。

慢查询日志一般在定位慢SQL,需要性能优化的情况下开启。

事务日志(redo / undo.log)

  • 事务日志(InnoDB特有的日志)也叫做redo日志
  • 文件名为ib_logfile0ib_logfile1,默认存放在表空间所在目录。
  • 还有一个日志文件叫做undo日志,默认存储在ib_data目录下。

image.png

中继日志(relay log)

  • 中继日志是在主从复制环境中产生的日志。
  • 主要作用是为了从机可以从中继日志中获取到主机同步过来的SQL语句,然后执行到从机中。

主从复制原理如下图所示

image.png

slave会从master读取binlog来进行数据同步,主从复制的三步骤

  • master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件(binary log events)
  • slave将master的binary log events拷贝到它的中继日志(relay log)
  • slave重做中继日志中的事件,将改变应用到自己的数据库中。MySQL复制是异步的且串行化的

数据文件(随机IO)

查看MySQL数据文件:

show variables like '%datadir%';

image.png

当然这是在/etc/my.cnf配置文件配置的结果:

image.png

InnoDB数据文件

  • .frm文件:主要存放与表相关的数据信息,主要包括表结构的定义信息。
  • .ibd:使用独享表空间存储表数据和索引信息,一张表对应一个ibd文件。
  • ibdata文件,使用共享表空间存储表数据和索引信息,所有表共同使用一个或者多个ibdata文件。

MyISAM数据文件

  • .frm文件:主要存放与表相关的数据信息,主要包括表结构的定义信息。
  • .myd文件:主要用来存储表数据信息。
  • .myi文件:主要用来存储表数据中任何索引的数据树。

查看一下/var/lib/mysql/mysql目录下的数据文件

image.png