MySQL体系结构

不管是用哪种数据库，了解数据库的体系结构都是极为重要的。MySQL体系结构主要由数据库和数据库实例构成。

数据库，物理操作系统文件或者其它文件的集合，在MySQL中，数据库文件可以是frm、myd、myi、ibd等结尾的文件，当使用ndb存储引擎时，不是系统文件，是存放于内存中的文件；数据库实例，由数据库后台进程/线程以及一个共享内存区组成，共享内存可以被运行的后台进程/线程所共享。

如果把MySQL数据库比喻成一条龙，那么体系结构就是它的龙头部分。学习MySQL数据库，又好比盖房子，如果想把房子盖得特别高，地基一定要稳，基础得牢固。所以学习MySQL数据库就需要先了解它的体系结构，这是学好MySQL数据库的前提。比如，很多查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行。

MySQL的体系结构可以分为两层，MySQL Server层和存储引擎层。在MySQL Server层中又包括连接层和SQL层。

应用程序通过接口（如ODBC、JDBC）来连接MySQL。最先连接处理的是连接层，连接层包括通信协议、线程处理、用户名密码认证三个部分。通信协议负责检测客户端版本是否兼容MySQL服务端。线程处理是指每一个连接请求都会分配一个对应的线程，相当于一条SQL对应一个线程，一个线程对应一个逻辑CPU，并会在多个逻辑CPU之间进行切换。用户名密码认证验证创建的账号和密码，以及host主机授权是否可以连接到MySQL服务器。

SQL层包含权限判断、查询缓存、解析器、预处理、查询优化器、缓存和执行计划。权限判断可以审核用户有没有访问某个库、某个表，或者表里某行的权限。查询缓存通过Query Cache进行操作，如果数据在Query Cache中，则直接返回结果给客户端。查询解析器针对SQL语句进行解析，判断语法是否正确。预处理器对解析器无法解析的语义进行处理。优化器对SQL进行改写和相应的优化，并生成最优的执行计划，就可以调用程序的API接口，通过存储引擎层访问数据。

存储引擎层也是MySQL数据库区别于其他数据库最核心的一点。（存储引擎的具体细节在后面的博文介绍）

详解体系结构如下:

• 连接层

思想：为解决资源的频繁分配﹑释放所造成的问题，为数据库连接建立一个“缓冲池”。

原理：预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。

作用：进行身份验证、线程重用，连接限制，检查内存，数据缓存；管理用户的连接，线程处理等需要缓存的需求。

• SQL层

管理服务和工具组件

    从备份和恢复的安全性、复制、集群、管理、配置、迁移和元数据等方面管理数据库

SQL接口组件

    进行DML、DDL，存储过程、视图、触发器等操作和管理；用户SQL命令接口

解析器组件

    验证和解析SQL命令

查询优化器组件

    对SQL语句查询进行优化

    “选取、投影和连接”

缓存和缓冲区

    由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

• 插件式存储引擎

MySQL的存储引擎是插件式的。它根据MySQL AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制，这种访问机制就叫存储引擎。

MyISAM：它查询速度快，有较好的索引优化和数据压缩技术。但是它不支持事务。

InnoDB：它支持事务，并且提供行级的锁定，应用也相当广泛。

BDB：不再从底层支持BDB

Memory：适合存储临时数据

Archive：适合存储历史数据

• 物理文件层

支持的文件类型

    EXT3、EXT4、NTFS、NFS

文件内容

    数据文件、日志文件

MySQL体系结构图

MySQL从概念上可以分为四层，如上图。这四层自顶向下分别是网络连接层，服务层（核心层），存储引擎层，系统文件层。

最上层的服务并不是MySQL所独有的，大多数基于网络的客户端/服务器端工具或者服务都有类似的架构。比如连接处理、授权认证、安全等。

第二层架构大多数MySQL核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。

第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。和GNU/Linux下的各种文件系统一样，每个存储引擎都有它的优势和劣势。服务器通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎API包含几十个底层函数，用于执行诸如“开始一个事务”或者“根据主键提取一行记录”等操作。但存储引擎不会去解析SQL，不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器端请求。

网络连接层

作用

主要负责连接管理、授权认证、安全等等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过SSL证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。这一层并不是MySQL所特有的技术。

为什么要设计成线程池？

在服务器内部，每个client都要有自己的线程。这个连接的查询都在一个单独的线程中执行。想象现实场景中数据库访问连接实在是太多了，如果每次连接都要创建一个线程，同时还要负责该线程的销毁。对于系统来说是多么大的消耗。由于线程是操作系统宝贵的资源。这时候线程池的出现就显得自然了，服务器缓存了线程，因此不需要为每个Client连接创建和销毁线程。

服务层

作用

第二层服务层是MySQL的核心，MySQL的核心服务层都在这一层，查询解析，SQL执行计划分析，SQL执行计划优化，查询缓存。以及跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等。通过下图来观察服务层的内部结构：

图中红色框中标出来的就是MySQL服务层内部执行的过程

下面来简单分析SQL语句在服务层中具体的流程：

• 查询缓存：在解析查询之前，服务器会检查查询缓存，如果能找到对应的查询，服务器不必进行查询解析、优化和执行的过程，直接返回缓存中的结果集。
• 解析器与预处理器：MySQL会解析查询，并创建了一个内部数据结构（解析树）。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理会根据MySQL的规则进一步检查解析树是否合法。比如要查询的数据表和数据列是否存在等。
• 查询优化器：优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。优化器并不关心使用的什么存储引擎，但是存储引擎对优化查询是有影响的。优化器要求存储引擎提供容量或某个具体操作的开销信息来评估执行时间。
• 查询引擎：在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令调用存储引擎的接口得出结果。

存储引擎层

作用：负责MySQL中数据的存储与提取。 服务器中的查询执行引擎通过API与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。MySQL采用插件式的存储引擎。MySQL为我们提供了许多存储引擎，每种存储引擎有不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。如果对于存储引擎的性能不满意，可以通过修改源码来得到自己想要达到的性能。例如阿里巴巴的X-Engine，为了满足企业的需求facebook与google都对InnoDB存储引擎进行了扩充。

特点：存储引擎是针对于表的而不是针对库的（一个库中不同表可以使用不同的存储引擎），服务器通过API与存储引擎进行通信，用来屏蔽不同存储引擎之间的差异。

下面大致介绍一下MySQL中常见的的存储引擎

InnoDB

特点：支持事务，适合OLTP应用，假设没有什么特殊的需求，一般都采用InnoDB作为存储引擎。支持行级锁，从MySQL5.5.8开始,InnoDB存储引擎是默认的存储引擎。

MyISAM

特点：不支持事务，表锁设计，支持全文索引，主要应用于OLAP应用

场景

在排序、分组等操作中，当数量超过一定大小之后，由查询优化器建立的临时表就是MyISAM类型

报表，数据仓库

Memory

特点：数据都存放在内存中，数据库重启或崩溃，表中的数据都将消失，但是标的结构还是会保存下来。默认使用Hash索引。

场景

适合存储OLTP应用的临时数据或中间表。

用于查找或是映射表，例如邮编和地区的对应表。

除此之外还有CSV,Federated、Archive等等。后面博客会专门讲解MySQL存储引擎。

系统文件层

作用

该层主要是将数据库的数据存储在文件系统之上，并完成与存储引擎的交互。

InnoDB物理文件结构

• 先建两个InnoD存储引擎的表：

• 进入MySQL数据存储目录查看数据表在文件上的体现：

• 注意上面的每个表都有一个*.frm与*.ibd后缀文件他们的作用分别是：

.frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等。

.ibd文件：存放innodb表的数据文件。

除了.ibd文件InnoDB还有一种文件的存储格式为.ibdata文件，那么他们之间有什么区别呢？ 

这两种文件都是存放innodb数据的文件，之所以用两种文件来存放innodb的数据，是因为InnoDB的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。独享表空间存储方式使用.ibd文件，并且每个表一个ibd文件。共享表空间存储方式采用.ibdata文件，所有的表共同使用一个ibdata文件，即所有的数据文件都存在一个文件中。决定使用哪种表的存储方式可以通过mysql的配置文件中 innodb_file_per_table选项来指定。InnoDB默认使用的是独享表的存储方式，这种方式的好处是当数据库产生大量文件碎片的时，整理磁盘碎片对线上运行环境的影响较小。

MyISAM物理文件结构为

• 为了掩饰我先建一个MyISAM存储引擎的表：

• 进入MySQL数据存储目录查看数据表在文件上的体现：

.frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等。

.MYD文件：MyISAM存储引擎专用，用于存储MyISAM表的数据

.MYI文件：MyISAM存储引擎专用，用于存储MyISAM表的索引相关信息

总结——MySQL体系结构

从概念上来说 ：

数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合。数据库实例是应用程序，是位于用户与操作系统之 间的一层数据管理软件。用户对数据库数据的任何操作，包括数据库定义、数据查询、数据维护、数据库运行控制等，都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道 。

如果你觉得烦琐，我直白来说：

数据库是由 一个个文件组成 （一般来说都是二进制的文件） 的，如果要对这些文件执行诸如 SELECT、INSERT、UPDATE和DETELE 之类的操作，不能通过简单的操作文件来更改数据库的内容，需要通过数据库实例来完成对数据库的操作。

体系结构由以下几部分组成：

连接池组件、管理服务和工具组件、SQL接口组件、查询分析器组件、优化器组件、缓冲（cache）组件、插件式存储引擎、物理文件

注意：存储引擎是基于表，而不是数据库。MySQL区别于其它数据库的最重要的特点就是其插件式的表存储引擎。

MySQL InnoDB存储引擎

InnoDB存储引擎支持事务，主要面向在线事务处理（ OLTP ）方面的应用 。

其特点是行锁设计、支持外键，并支持类似于Oracle的非锁定读，即默认情况下读取操作不会产生锁。

MySQL在Windows版本下的InnoDB 是默认的存储引擎，同时lnnoDB默认地被包含在所有的MySQL二进制发布版本中 。

InnoDB文件组织

你也可以通过SHOW ENGINES语句查看当前使用 的MySQL数据库所支持的存储引擎 ，也可以通过查找information_schema 架构下的ENGINES表来查看。

存储引擎

通过MySQL提供的示例数据库来简单显示各种存储引擎之间的区别。

分别运行以下语句，然后统计每次使用各种存储引擎后表的大小。

mysql> create table mytest engine=myisam as select * f rom salaries1 Query OK , 284404 7 rows af f ected ( 4 .37 sec )

Records: 2844047 Duplicates: 0 Warnings: 0

mysql> alter table mytest engine=innodb1 Query OK , 284 4 04 7 rows af f ected ( 15 .86 sec )

Records: 2844 04 7 Duplicates: 0 Warnings: 0

mysql> alter table mytest engine=ARCHIVE1 Query OK , 2 844 04 7 rows af f ected ( 16 .03 sec )

Records: 284404 7 Duplicates: 0 Warnings: 0

通过每次的统计我们发现，当最初的表使用MyISAM存储引擎时，表的大小为40.7MB，使用InnoDB存储引擎时表增大到了113.6MB，而使用Archive存储引擎时表的大小却只有20.2MB。

连接者：不同语言的代码程序和mysql的交互（SQL交互）

1、连接池 管理、缓冲用户的连接，线程处理等需要缓存的需求

2、管理服务和工具组件 系统管理和控制工具，例如备份恢复、Mysql复制、集群等 

3、sql接口 接受用户的SQL命令，并且返回用户需要查询的结果

4、查询解析器 SQL命令传递到解析器的时候会被解析器验证和解析(权限、语法结构) 

5、查询优化器 SQL语句在查询之前会使用查询优化器对查询进行优化

select id,name from user where age = 40; 

a、这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行age过滤 

b、这个select查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤 

c、将这两个查询条件联接起来生成最终查询结果 

6、缓存 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据

7、插入式存储引擎 存储引擎说白了就是如何管理操作数据（存储数据、如何更新、查询数据等）的一种方法。因为在关系数据库 
中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。

说明： 

在Oracle 和SQL Server等数据库中，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。

用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。

参考链接：

https://www.cnblogs.com/zhoubaojian/articles/7866292.html

https://www.cnblogs.com/yanjieli/p/9780100.html