MySQL——架构与存储引擎

概述

MySQL逻辑架构分为四层，分别是连接层、服务层、引擎层、存储层。MySQL的存储引擎有MyISAM、Innodb、Archive、Memory等。

MySQL逻辑架构

Connectors

不同语言中与SQL的交互。

Management Serveices & Utilities：

系统管理和控制工具

Connection Pool（连接池）

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

SQL Interface（SQL接口）

接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface。

Parser（解析器）

SQL命令传递到解析器的时候会被解析器验证和解析。
主要功能：
a . 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。

Optimizer（查询优化器）

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

它使用的是“选取-投影-联接”策略进行查询。
用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where语句进行选取，而不是先将表全部查询出来以后再进行过滤。
这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。
将这两个查询条件联接起来生成最终查询结果。

Cache和Buffer（查询缓存）

他的主要功能是将客户端提交给MySQL的 select请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。

存储引擎接口

存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是 一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色。

从图还可以看出，MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。

注意：存储引擎是基于表的，而不是数据库。

MySQL执行流程

1）当MySQL启动（MySQL服务器就相当于一个进程），等待客户端连接，每一个客户端连接请求，服务器都会创建一个新的线程处理（如果是线程池的话，则分配一个空的线程），每个线程独立，拥有各自的内存处理空间，但是，如果这个请求只是查询，没关系，但是若是修改数据，很显然，当两个线程同一块内存是会引发数据同步问题的。

2）连接到服务器，服务器需要对其进行验证，也就是用户名、IP、密码验证，一旦连接成功，还要验证是否具有执行某个特定查询的权限（例如，是否允许客户端对某个数据库某个表的某个操作）。

3）接下来进行SQL语句的解析、优化、缓存的查询，MySQL内置函数的实现，跨存储引擎功能（所谓跨存储引擎就是说每个引擎都需提供的功能（引擎需对外提供接口）），例如：
存储过程、触发器、视图等。

1. 如果是查询语句（select语句），首先会查询缓存是否己有相应结果，有则返回结果，无则进行下一步（如果不是查询语句，同样调到下一步）
2. 解析查询，创建一个内部数据结构（解析树），这个解析树主要用来SQL语句的语义与语法解析；
3. 优化：优化SQL语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎，优化器会询问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化等。

存储引擎

多存储引擎是mysql有别于其他数据库的一大特性。

存储引擎是针对表的。

MySQL 5.5之后，默认的存储引擎由MyISAM变为InnoDB。

查看存储引擎：show engines;

MyISAM

1）MyISAM存储引擎由MYD和MYI组成
2）特性

1. 并发行与锁级别-表级锁
2. 支持全文检索
3. 支持数据压缩
   3）适用场景
4. 非事务型应用（数据仓库，报表，日志数据）
5. 只读类应用
6. 空间类应用（空间函数、坐标）

Innodb

1）特性
Innodb是一种事务性存储引擎
完全支持事务的ACID特性
Innodb支持行级锁（并发程度更高）
2）适用场景
Innodb适合于大多数OLTP应用

MyISAM和Innodb的对比

MySQL物理结构

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

日志文件

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

错误日志（err log）

默认是开启的，而且从5.5.7以后无法关闭错误日志。
记录了运行过程中遇到的所有严重的错误信息,以及 MySQL每次启动和关闭的详细信息。
默认的错误日志名称：hostname.err。
错误日志所记录的信息是可以通过log-error和log-warnings来定义的，其中log-err是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。
og_error可以直接定义为文件路径，也可以为ON|OFF；log_warings只能使用1|0来定义开关启动。

二进制日志（bin log）

默认是关闭的，需要通过配置：log-bin=mysql-bin进行开启。其中mysql-bin是binlog日志文件的basename，binlog日志文件的名称：mysql-bin-000001.log
binlog记录了数据库所有的ddl语句和dml语句，但不包括select语句内容，语句以事件的形式保存，描述了数据的变更顺序，binlog还包括了每个更新语句的执行时间信息，binlog主要作用是用于恢复数据，因此binlog对于灾难恢复和备份恢复来说至关重要。
如果是DDL语句，则直接记录到binlog日志，而DML语句，必须通过事务提交才能记录到binlog日志中。
binlog还用于实现mysql主从复制。
binlog还用于数据恢复。

通用查询日志（general query log）

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

慢查询日志（slow query log）

默认是关闭的。需要通过设置：slow_query_log=ON进行开启。
记录执行时间超过long_query_time秒的所有查询，便于收集查询时间比较长的SQL语句

事务日志

事务日志（InnoDB特有的日志）也叫redo日志。
文件名为"ib_logfile0"和“ib_logfile1”，默认存放在表空间所在目录。
还有一个日志文件叫undo 日志，默认存储在ib_data目录下。

中继日志

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

数据文件

查看MySQL数据文件：SHOW VARIABLES LIKE ‘%datadir%’;

.frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息。
.ibd和.ibdata文件：用来存储InnoDB存储引擎的表数据和索引信息。
.myd文件：主要用来存储使用MyISAM存储引擎的表数据信息。
.myi文件：主要用来存储使用MyISAM存储引擎的表数据文件中任何索引的数据树。