MySQL数据库整体架构设计逻辑方法_mysql数据库设计流程图

了解掌握MySQL数据库的架构设计、文件系统，有利于更全面、系统的掌握MySQL数据库，是进阶精通MySQL的必修课。

MySQL逻辑架构

总体分为客户端连接器(Connectors)和服务器端(MySQL Server)两大部分。应用程序客户端或可视化数据库客户端通过提供的连接器连接到服务端来使用MySQL提供的服务。

架构图

整体架构分为客户端连接器(Connectors)和服务器端(MySQL Server)两大部分。其中服务器端分为3层，包括SQL层、可拔插存储引擎层、文件系统（物理结构），核心在SQL层、可拔插存储引擎层。

 

客户端(Connectors)

即连接器Connectors，包括MySQL提供的原生C语言API、实现了JDBC的连接器驱动程序等。基本上，大部分编程语言都实现了连接MySQL数据库的程序包，通过这些程序包连接到MySQL数据库的程序或应用，也可以认为是MySQL的客户端。

• Native C API
• JDBC
• ODBC
• .NET
• PHP
• Python
• Perl
• Ruby

服务器端(Server)

服务器端主要由SQL层、可拔插存储引擎层、文件系统三部分。

其中SQL层(SQL Layer)包含以下6个组件

• 系统管理和控制工具(Management Services & Utilities )

MySQL提供的一些系统管理工具，比如mysql、mysqladmin等命令行工具

• 连接池(Connection Pool)

连接池组件负责身份认证authentication、连接池的实现(线程复用、连接限制)、检查内存，缓存等

• SQL接口(SQL Interfaces)

实现各种语句DDL、DML等，以及存储过程、函数、触发器、视图等接口，负责命令分发

• 解析器(Parser)

解析SQL语句，负责对SQL语句的词法和语法解析，形成初级语法树

• 优化器(Optimizer)

对初级语法树进行优化，生成计划，选择合理的索引

• 缓存和缓冲(Caches & Buffers)

缓冲提交的SQL请求等；对查询结果进行缓存，如果有开启。8.0版本后，缓存Cache已弃用。

在5.5版本后，MySQL默认使用存储引擎是InnoDB。存储引擎是以表为单位应用的，创建表时可以指定使用的存储引擎，比如

CREATE TABLE tb_name(columns) ENGINE=InnoDB|MyISAM|Memory

以下是MySQL支持的常见引擎

在5.7版本中，CREATE TABLE语句创建表时默认使用的存储引擎为InnoDB，可以省略指定。目前使用比较多的存储引擎包括InnoDB和MyISAM。InnoDB与MyISAM的区别如下表所示，开发人员可以按照业务需求使用不同的引擎

一般情况下，InnoDB、MyISAM、Memory等几个存储引擎可以满足大部分的一个应用场景。下面是这三个存储引擎的使用场景或选型依据

• InnoDB

支持事务，支持外键，支持崩溃修复和并发控制。对事务的完整性要求比较高(比如银行)，要求实现并发控制(比如售票)，频繁更新、删除数据，这类应用场景建议使用InnoDB，因为支持事务的提交(commit)和回滚(rollback)。

• MyISAM

插入数据快，空间和内存使用比较低。如果主要用于插入新记录和读取记录，或应用的完整性和并发性要求比较低，可以考虑选择MyISAM。

• Memory

数据存储在内存中，处理速度较快，但安全性不高。如果需要很快的读写速度，对数据安全性要求不高，数据作为临时处理使用或不需要持久保存，可以选择Memory。它对表的大小有要求，不能建立太大的表。

注意，因为存储引擎是作用在表上的，所以同一个数据库，可以使用多个存储引擎，即不同的表可以有不同的存储引擎。如果一个表要求比较高的事务处理，可以选择InnoDB；而查询比较高的表，可以使用MyISAM；如果该数据库需要一个用于查询的临时表，也可以考虑使用Memory存储引擎。总之，根据不同的一个用场景，可以使用一种存储引擎或多种存储引擎组合使用。

文件系统包括数据文件和日志文件两大类：

• 数据文件

包括表定义文件、表数据、索引等

• 日志文件

包括通用日志文件、二级制文件binlog、中继日志relaylog、重做日志redolog、回滚日志undolog、错误日志errlog，以及慢查询日志slowlog等

执行流程

下图是MySQL执行一条语句的简单流程图

 

物理架构

即文件系统，MySQL的文件组织架构。MySQL是通过文件系统对数据和索引进行存储的。从物理结构可以分为数据索引文件和日志文件两大类。其中，数据索引文件采用随机IO的方式写入磁盘存储，日志文件采用顺序IO方式写入磁盘存储。两种写入磁盘的方式对比如下

 

可以使用下面的命令查看这些文件的保存目录，如果没有另外指定，一般情况下在/var/lib/mysql目录下

show variables like '%datadir%';

数据文件

这里主要介绍比较常用的两种存储引擎InnoDB和MyISAM的数据文件构成。

InnoDB数据文件包括以下三种格式

• .frm文件

主要存放与数据表相关的信息，包括表结构的定义信息

• .ibd文件

使用独享表空间也就是用户表空间存储表数据和索引信息，一张表对应一个.ibd文件

• ibdata文件

使用共享表空间也即系统表空间存储表数据和索引信息，所有表使用一个或多个ibdata文件,文件名一般加上后缀0...N，比如ibdata0，ibdata1等

MyISAM数据文件包括以下三种格式

• .frm文件

主要存放与数据表相关的信息，包括表结构的定义信息

• .myd文件

主要存储表数据信息

• .myi文件

主要存储表数据文件中任何索引的数据数

日志文件

可以下面的命令查看日志的开启情况，开启或配置了对应的日志文件后，才会生成相应的日志文件。

show variables like '%log_%';

日志文件大概分7类，包括错误日志(error log)、二进制日志(bin log)、通用查询日志(general query log)、慢查询日志(slow query log)、重做日志(redo log)、回滚日志(undo log)、中继日志(relay log)。

• 错误日志(error log)

错误日志默认是开启的，从5.5.7版本开始以后无法关闭，错误日志记录了运行过程中遇到的所有严重错误信息，以及MySQL服务器每次启动和关闭的详细信息。错误日志所记录的信息是可以通过log-error和log-warnings来定义的，其中log-err是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。

-- 可以直接定义为文件路径，也可以为ON|OFF，默认的错误日志名称：hostname.err
log_error=/var/log/mysqld.log
 
-- 只能使用1|0来定义开关启动，默认是启动的
log_warings=1

• 二进制日志(bin log)

二进制日志记录数据在运行过程中的所有变化。bin log通过存储数据库所有DDL和DML语句，但不包括SELECT语句，语句以事件的形式保存，描述了数据的变更顺序，bin log还包括了每个更新语句的执行时间信息。如果是DDL语句，则直接记录到bin log日志，而DML语句，必须通过事务提交才能记录到bin log日志中。默认是不开启的，需要手动开启。产品环境建议开启。

log-bin=mysql-bin

也可以这样

log-bin=ON|OFF
log_bin_basename=mysql-bin
-- 有必要也可以指定索引
-- log_bin_index=mysql-bin.index

其中mysql-bin是binlog日志文件的basename，binlog日志文件的完整名称：mysql-bin-000001.log

• 通用查询日志(general query log)

通用查询日志记录运行过程中的所有内容，耗性能，一般产品化境中不开启。可以通过下面的命令查看开通状态

show global variables like 'general_log';

可以通过下面的配置开通日志

-- 启动开关
general_log={ON|OFF}
-- 日志文件变量，而general_log_file如果没有指定，默认名是host_name.log
general_log_file=/PATH/TO/file
-- 记录类型
log_output={TABLE|FILE|NONE}

• 慢查询日志(slow query log)

记录执行较慢的select查询语句，用于SQL语句调优时开启，正常情况下不需要开启，默认亦是关闭的。可以通过设置全局变量的方式开启

-- 开启慢查询日志
slow_query_log=ON
-- 慢查询的阈值
long_query_time=3
 
-- 日志记录文件如果没有给出file_name值， 默认为主机名，后缀为-slow.log。
-- 如果给出了文件名，但不是绝对路径名，文件则写入数据目录。
slow_query_log_file=file_name

以上的设置，记录执行时间超过long_query_time秒的所有查询语句

• 重做日志(redo log)

事务相关的日志，ACID持久化，崩溃恢复相关。

它用于确保事务的持久性，防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。其记录的是物理数据页面的修改的信息，它是顺序写入redo log file的物理文件中去的。

事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行
过程中，便开始写入redo log文件中。

当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

默认情况下，对应的物理文件位于数据库的data目录下的ib_logfile1和ib_logfile2。

-- 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下
innodb_log_group_home_dir=./
  
-- 指定重做日志文件组中文件的数量，默认2
innodb_log_files_in_group=2
 
-- 重做日志文件的大小
innodb_log_file_size=50331648
 
-- 重做日志缓冲区大小
innodb_log_buffer_size=16777216

很重要一点，redo log是什么时候写入磁盘的？前面说了是在事务开始之后逐步写盘的。之所以说重做日志是在事务开始之后逐步写入重做日志文件，而不一定是事务提交才写入重做日志文件，原因就是，重做日志有一个缓存区，其默认大小为8M(或16M)，Innodb存储引擎先将重做日志内容写入缓存区中。然后以下三种情况下，都会将缓冲区的日志内容刷新到磁盘

1. 主线程每秒一次执行刷新重做日志缓冲区到重做日志文件

2. 每个事务提交时会将重做日志刷新到重做日志文件，如果有配置这个变量的话

3. 当重做日志缓存可用空间少于一半时，重做日志缓存会被刷新到重做日志文件

由此可以看出，重做日志通过不止一种方式写入到磁盘，尤其是对于第一种方式，重做日志从缓冲区到重做日志文件是主线程的定时任务。

因此重做日志的写盘，并不一定是随着事务的提交才写入重做日志文件的，而是随着事务的开始，逐步
开始的。

另外引用《MySQL技术内幕 Innodb 存储引擎》（page37）上的原话：
即使某个事务还没有提交，Innodb存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。这一点是必须要知道的，因为这可以很好地解释再大的事务的提交（commit）的时间也是很短暂的。

• 回滚日志(undo log)

也是事务相关的日志，实现回滚。

它保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。它是逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

事务开始之前，将当前的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性 。

当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

MySQL5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间文件的默认的名称是ibdata，位于数据文件目录中。

MySQL5.6之后，undo表空间可以配置成独立的文件，但是需要提前在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数。如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。

关于MySQL5.7之后的独立undo 表空间配置参数如下

-- undo独立表空间的存放目录，默认值是./
innodb_undo_directory = /data/undospace/ 
 
--回滚段为128KB，默认值得128
innodb_undo_logs = 128 
 
--指定有4个undo log文件，默认值0
innodb_undo_tablespaces = 4 

如果undo使用的共享表空间，这个共享表空间中又不仅仅是存储了undo的信息，共享表空间的默认为
与MySQL的数据目录下面，其属性由参数innodb_data_file_path配置。

mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+ 

undo是在事务开始之前保存的被修改数据的一个版本，产生undo日志的时候，同样会伴随类似于保护事务持久化机制的redolog的产生。

默认情况下undo文件是保存在共享表空间的，也即ibdata文件中，当数据库中发生一些大的事务
性操作的时候，要生成大量的undo信息，全部保存在共享表空间中的。

因此共享表空间可能会变得很大，默认情况下，也就是undo 日志使用共享表空间的时候，被“撑大”的共享表空间是不会也不能自动收缩的。

因此，mysql5.7之后的“独立undo 表空间”的配置就显得很有必要了 。

• 中继日志(relay log)

在主从复制应用场景下，其内容为从主机读取的bin log日志写入的内容。

总结

关于MySQL数据库的架构设计，主要了解其逻辑架构，执行流程，重点掌握InnoDB和MyISAM等引擎的选型，以及日志文件中二进制日志、慢查询日志、重做日志、回滚日志等日志的原理和配置。