MySQL物理架构及逻辑架构简介

足至迹留
1,399 阅读12分钟
以下内容适用Mysql 5.7版本

Mysql与其他关系型数据库系统（如Oracle, Sql server等）最大的区别就是存储引擎（storage engine)与数据库server是松耦合的，存储引擎以插件(plugin)的形式出现在mysql server中，不同的应用场景可以选择不同的存储引擎。比如默认的InnoDB存储引擎支持事务，支持行级锁，MyISAM不支持事务不支持行级锁等。其中最重要的存储引擎就是InnoDB。

但由于这种开放插件式的存储引擎，从而导致文件的一致性大大降低。在SQL执行优化方面，也就有着一些不可避免的瓶颈。在多表关联、子查询优化、统计函数等方面是软肋，而且只支持极简单的HINT。

1. Mysql物理架构

Mysql的物理架构如上图示，就是存储文件的结构，不同的文件存储不同类型的数据，以不同的方式存储在磁盘上。

文件主要分为两组: Mysql 基目录和Mysql数据目录。

1.1 Mysql 基目录

基目录里主要包含程序日志文件、程序可执行文件。

  • MySQL pid 文件记录的是当前 mysqld 进程的 pid，pid 即 Process ID。

  • MySQL socket文件的作用：Mysql有两种连接方式：

    （1）TCP/IP（2）socket。对mysql.sock来说，其作用是当mysql客户端程序mysql与mysql服务器端程序mysqlserver处于同一台机器，发起本地连接时用，它比tcp快。

  • Mysql可执行程序是MySQL Command-Line Client, a simple SQL shell with input line editing capabilities.

如：

shell> mysql --user=user_name --password db_nameEnter password: your_password

然后就可以执行sql语句。

  • mysqld also known as the mysql server。在mysql的安装过程中作为主程序完成很多的任务。mysql server管理访问mysql的数据目录（data directory). mysqld执行时可以指定很多选项，如：

shell> mysqld --verbose --help

  • mysqladmin is a client for performing administrative operations. 可以用来检查服务器的配置和当前状态，create或drop database。如：

shell> mysqladmin [options] command [command-arg] [command [command-arg]] ...

1.2 Mysql数据目录

数据目录下主要是日志文件，数据文件等。

1.2.1 日志文件

主要有error log，binary log, query log, slow query log, innodb redo log, innodb undo log等。

  • error log: 记录mysql启动,关闭和运行时产生的重大的错误的信息;

  • binary log(sql执行后写日志，默认不开启）:

  1. 记录所有对数据库更新和潜在的更新语句,语句以事件(event)的方式存储,并且记录了语句发生时间、执行时长、操作的数据等等。

  2. 它不记录那些不修改任何数据的语句,如果想要记录所有的语句,可以使用query log;

  3. 它主要的目的是在做还原操作时尽可能全的更新数据库,因为它包含在一次备份后的所有更新操作.它同样被用在master replication server中作为一个记录发送给slave servers;

  4. 打开二进制日志会损失1%的性能,但是它带来的好处远远超过这些;

  5. 查看二进制日志是否打开,需要查看log_bin参数是否是ON: >show variables like ‘binlog_format’;

  6. 有3种不同的格式可选：Mixed,Statement,Row，默认格式是 Statement。

  7. 每次重启mysql服务或运行mysql> flush logs;都会生成一个新的二进制日志文件，这些日志文件的number会不断地递增。除了生成filename.number文件外还会自动生成filename.index的文件。

  • general query log:

  1. 一般查询日志记录服务器运行期间所有操作的日志.当客户端连接或者断开的时候服务器会记录信息到日志中,并记录所有从客户端接收到的sql语句,它对于在客户端中排错和查看是哪个客户端发送的命令很有帮助;

  2. mysqld是按照接收到命令的方式记录语句的,这可能跟它们执行的顺序不同(这与二进制日志是有区别的,二进制日志是执行后记录).

  • slow query log:

  1. 调优时使用,记录超出指定时间的sql语句;

  2. 慢查询日志主要记录执行时间超过long_query_time变量指定时间的sql语句,这个时间不包括获得锁的时间,只包含执行时间,系统默认时间是10s(long_query_time=10.0);

  3. MySQL 还提供了专门用来分析慢查询日志的工具程序mysqldumpslow

  • innodb redo log (与innodb数据引擎相关)

  1. 用来实现灾难恢复(crash recovery),突然断电会导致innodb表空间中的数据没有写到磁盘上,通过执行redo log能够重新执行这些操作来恢复数据;

  2. 提升innodb的i/o性能,innodb引擎把数据和索引都载入到内存中的缓冲池（buffer pool）中,如果每次修改数据和索引都需要更新到磁盘,必定会增加i/o请求,而且因为每次更新的位置都是随机的,磁头需要频繁的定位导致效率很低,所以innodb每处理完一个事务后只添加一条日志log（顺序IO),另外有一个线程负责读取日志文件并批量更新到磁盘上(随机IO),实现最高效的磁盘写入;

  3. redo log是存储引擎负责写入的，binary log是数据库server负责写入的。

1.2.2 数据文件

主要有数据文件和索引文件等。比如：

MySQL的每个数据库的数据文件都对应存放在一个与数据库同名的文件夹中(该目录下datadir参数对应的目录下)，MySQL数据库文件包括MySQL所建数据库文件和MySQL所用存储引擎创建的数据库文件。

MySQL如果使用MyISAM存储引擎，数据库文件类型就包括.frm、.MYD、.MYI

MySQL如果使用InnoDB存储引擎，数据库文件类型就包括.frm、ibdata1、.ibd，存放位置有两个，ibdata1、.ibd文件默认存放位置是MySQL安装目录下的data文件夹。

  • .frm文件：存储数据表的框架结构，文件名与表名相同，每个表对应一个同名frm文件，与操作系统和存储引擎无关，不管MySQL运行在何种操作系统上，使用何种存储引擎，都有这个文件。

除了必有的.frm文件，根据MySQL所使用的存储引擎的不同（MySQL常用的两个存储引擎是MyISAM和InnoDB），存储引擎会创建各自不同的数据库文件。

  • .MYD文件：即MY Data，表数据文件

  • .MYI文件：即MY Index，索引文件

  • .log文件：日志文件

表空间包含所有使用InnoDB引擎表的数据和索引信息,同样也包括了事务回滚所必须的undo logs.日志文件记录了提交过的事务的信息,用来防止数据丢失.默认情况下,表空间和日志文件保存在数据目录.

以下是InnoDB的表空间结构图：

InnoDB存储引擎的管理是由引擎本身完成的，表空间（Tablespace）是由分散的段(Segment)组成。常见的段有数据段、索引段、回滚段等。InnoDB存储引擎表是索引组织的（index organized），因此数据即索引，索引即数据。那么数据段即为B+树的页节点（上图的leaf node segment），索引段即为B+树的非索引节点（上图的non-leaf node segment）。

一个段(Segment)由多个区（Extent）组成。区（Extent）由64个连续的页（Page，有些文档也称为块，block）组成，每个页大小为16K，即每个区大小为1MB，创建新表时，先使用32页大小的碎片页存放数据，使用完后才是区的申请（InnoDB最多每次申请4个区，保证数据的顺序性能）

  • undo日志

用于存放数据修改被修改前的值，假设修改 t1表中 id=1的行数据，把Name='A' 修改为Name = 'A2' ，那么undo日志就会用来存放Name='A'的记录，如果这个修改出现异常，可以使用undo日志来实现回滚操作，保证事务的一致性。

对数据的变更操作，主要来自 INSERT UPDATE DELETE，而UNDO LOG中分为两种类型，一种是 INSERT_UNDO（INSERT操作），记录插入的唯一键值；一种是 UPDATE_UNDO（包含UPDATE及DELETE操作），记录修改的唯一键值以及old column记录。

UNDO内部由多个回滚段组成，即 Rollback segment，一共有128个，保存在ibdata系统表空间中，分别从resg slot0 - resg slot127，每一个resg slot，也就是每一个回滚段，内部由1024个undo segment 组成。

UNDO通过保留已修改未提交数据来管理当前活动事务[MVCC]的一致读取。未修改的数据可以从此存储区域检索。undo日志也称为回滚段.

2. Mysql 逻辑架构

Mysql的逻辑架构如下图所示，是典型的C/S系统。

2.1 Client层

最上层的为客户端连接服务，用于不同语言与SQL的交互。可以通过:

> show variables like '%connections%'

命令查看MySQL实例的最大连接数和单个用户的最大连接数。

2.2 Server层

MySQL Server 逻辑系统架构分为3层:

  1. 应用层

  2. MySQL服务层

  3. 存储引擎层

2.2.1 应用层

应用层是MySQL体系架构的最上层，它可其他client-server架构一样，主要包含如下内容:

1. 连接处理当一个客户端向服务端发送连接请求后，MySQL server会从线程池中分配一个线程来和客户端进行连接，以后该客户端的请求都会被分配到该线程上。MySQL Server为了提高性能，提供了线程池，减少了创建线程和释放线程所花费的时间。

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

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

2.2.2 MySQL 服务层

该层是MySQL Server的核心层，提供了MySQL Server数据库系统的所有逻辑功能，该层可以分为如下不同的组件:

  • MySQL Management Server & utilities(系统管理)

  • SQL Interface(SQL 接口)

  • SQL Parser(SQL 解析器)

  • Optimizer (查询优化器)

  • Caches & buffers(缓存)

1. MySQL Management Server & utilities(系统管理) 提供了丰富的数据库管理功能，具体如下:

  • 数据库备份和恢复

  • 数据库安全管理，如用户及权限管理

  • 数据库复制管理

  • 数据库集群管理

  • 数据库分区，分库，分表管理

  • 数据库元数据管理

2. SQL Interface(SQL 接口) SQL接口，接收用户的SQL命令并进行处理，得到用户所需要的结果，具体处理功能如下:

  • Data Manipulation Language (DML).

  • Data Definition Language (DDL).

  • 存储过程

  • 视图

  • 触发器

3. SQL Parser(SQL 解析器) 解析器的作用主要是解析查询语句，最终生成语法树。首先解析器会对查询语句进行语法分析，如果语句语法有错误，则返回相应的错误信息。语法检查通过后，解析器会查询缓存，如果缓存中有对应的语句，就直接返回结果不进行接下来的优化执行操作。Parser会为每个查询语句生成SQL_ID。

注：从缓存中查出来的数据不会与真实的数据不一致，因为缓存中数据被修改，会被清出缓存。

4. Optimizer(查询优化器) 优化器的作用主要是对查询语句进行优化，包括重写查询、决定表的读取顺序以及选择合适的索引。

优化器并不关心表使用的什么存储引擎，但是存储引擎会影响优化。优化器会请求存储引擎提供容量或某个操作具体的开销信息以及表数据的统计信息等。

5. Caches & buffers(缓存) 包括全局和引擎特定的缓存，提高查询的效率。如果查询缓存中有命中的查询结果，则查询语句就可以从缓存中取数据，无须再通过解析和执行。这个缓存机制是由一系列小缓存组成，如表缓存、记录缓存、key缓存、权限缓存等。

Query cache如何工作

Query cache在会话之间是共享的，基于SQL_ID执行查询。只有完全一模一样的sql语句才会被认为是相等的，byte for byte的比较。

cache在以下两种场景不生效：

  • Queries that are a subquery of an outer query

  • Queries executed within the body of a stored function, trigger, or event

sql执行的流程如下图所示：

2.2.3 存储引擎层

1. 存储引擎 存储引擎是MySQL中具体与文件打交道的子系统，也是MySQL最有特色的地方。MySQL区别于其他数据库的最重要特点是其插件式的表存储引擎。他根据MySQL AB公司提供的文件访问层抽象接口来定制一种文件访问的机制（该机制叫存储引擎）。

服务器通过API与存储引擎进行通信，这些接口屏蔽了不同存储引擎的差异。

2. 物理文件 物理文件包括：redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等.

InnoDB的存储引擎架构：




参考文档：

https://www.rathishkumar.in/2016/04/understanding-mysql-architecture.html

https://lalitvc.wordpress.com/2016/11/03/mysql-architecture-and-components/

https://blog.csdn.net/crpp0902/article/details/77249279

https://blog.51cto.com/zpf666/1910497

https://www.cnblogs.com/wade-luffy/p/6288656.html

https://zhuanlan.zhihu.com/p/43736857

https://www.jianshu.com/p/7d1552883289

https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

                                                              -- END --

                                                    欢迎关注留言、发信交流~

                                           


avatar
文章
阅读
粉丝