MySQL架构篇

1. 逻辑架构

1.1 逻辑架构图

001.jpg

1.1.1 连接器（Connectors）

​ 连接器不属于mysql里面的层次，连接器说白了就是驱动，指的是不同语言中与SQL的交互。

1.1.2 系统管理和控制工具（Management Serveices & Utilities）

​ 就是一些管理工具，用于做比如说备份、恢复、安全、复制、集群等等的工作，这些其实主要是DBA的工作。

1.1.3 连接池（Connection Pool）

​ 要是连接mysql的话，mysql就需要管理连接，mysql不可能让你无限的去连接，所以首先要在连接池里 做一个连接验证，验证你能不能通过，如果你通过验证，说明你能连， 我从我的连接池里找一个连接出来给你。

1.1.4 SQL接口（SQL Interface）

​ 用来接收SQL（DML、 DDL、存储过程、视图、触发器等）语句的 ，sql语句都要通过它加载进来。加载进来以后要有一个分发的过程。

1.1.5 解析器（Parser）

​ SQL Interface接收到的sql语句其实就是字符串，这些字符串到底有什么样真实的含义，需要通过解析器来解析。比如接收这样的语句，select * from tab，在解析器里需要通过词法解析和语法解析。所谓此法解析就是将这条sql语句用中间的空格将其断开，断开以后形成一棵语法树。sql语句断开以后就需要语法解析，就 是说你写的这个sql语句到底符不符合sql语法（sql92、sql95）呢。解析以后这棵语法树就是一个正确的语法树了。注：limit是mysql自己的语法，它是最后被解析的。

1.1.6 查询优化器（Optimizer）

​ 这棵正确的语法树还不能被直接执行。因为mysql语句认为你写的sql语句不是完美的。mysql会通过自己的优化器来优化你的sql语句，看看你写到到底执行起来怎么样。 那优化什么呢，又怎么样去优化？

​ 它会优化这几个方面：

​ 一、索引。

​ 我们都知道索引可以建立多个，但真正执行的时候只会使用一个，它用哪个呢，它会用最优的那个。那 何为最优，看索引分析章节。

​ 二、多表关联的时候用小表来驱动大表。

​ 如果你写sql语句的时候把大表写在前面了，它会把小表调整在前面。

​ 三、where条件的执行。

​ mysql执行where条件并不是从左向右执行，它找过滤粒度最大的先执行。

​ 比如：

​ select * from stu where name='zhangsan' and id='10';

​ select * from stu where id='10' and name='zhangsan';

​ mysql优化器会先找到id='10'进行执行，再看找到的结果中的name是否为zhangsan，如果不是就返回空，如果是就直接返回。

​ 从右到左 Oracle？？？

1.1.7 查询缓存（Cache和Buffer）

​ mysql把查询结果存起来。比如select * from tuser where id=1，SQL语句经过hash计算后有一个唯一 值，这个唯一值表示这条sql语句。 这条sql语句执行完以后的查询结果会和sql语句以map的形式存储在查询缓存里，key是这条sql语句的hash值，value是查询结果。

​ MySQL8.0后不再使用，原因有二 ：

​ 1、sql有变化hash值就不唯一

​ 2、数据库数据有变化，缓存就回清除

1.1.8 存储引擎（Pluggable Storage Engines）

​ 存储引擎要跟数据打交道，就好像我们写的那一堆dao或者mapper，总之就是做存取数据工作的。存储引擎是可插拔的。存储引擎再向下就是物理层面了，就是文件和日志。MySQL的存储引擎是针对表进行指定的，以表为单位。mysql5.5以后默认情况下存储引擎是innodb。

存储引擎	说明
MyISAM	高速引擎，拥有较高的插入，查询速度，但不支持事务、不支持行锁、支持3种不同的存储格式。包括静态型、动态型和压缩型。
InnoDB	5.5 版本后 MySQL 的默认数据库，支持事务和行级锁定，事务处 理、回滚、崩溃修复能力和多版本并发控制的事务安全，比MyISAM处理速度稍慢、支持外键（ FOREIGN KEY）
ISAM	MyISAM的前身，MySQL5.0以后不再默认安装
MRG_MyISAM（MERGE）	将多个表联合成一个表使用，在超大规模数据存储时很有用
Memory	内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。只在内存上保存数据，意味着数据可能会丢失
Falcon	一种新的存储引擎，支持事物处理，传言可能是InnoDB的替代者
Archive	将数据压缩后进行存储，非常适合存储大量的独立的，作为历史记录的数据，但是只能进行插入和查询操作
CSV	CSV 存储引擎是基于 CSV 格式文件存储数据(应用于跨平台的数据交换)

​ xtraDB存储引擎是由Percona公司提供的存储引擎，该公司还出品了Percona Server这个产品，它是基于MySQL开源代码进行修改之后的产品。阿里对于Percona Server服务器进行修改，衍生了自己的产品alisql

1.1.8.1 nnoDB和MyISAM存储引擎区别：

	Innodb	Myisam
存储文件	.frm 表定义文件 .ibd 数据文件和索引文件	.frm 表定义文件 .myd 数据文件 .myi 索引文件
锁	表锁、行锁	表锁
事务	支持	不支持
CRDU	读、写	读多
count	扫表	专门存储的地方 （加where也扫表）
索引结构	B+ Tree	B+ Tree
外键	支持	不支持

1.1.8.2 存储引擎的选型：

InnoDB*：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比 较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的 更新、删除*操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM*：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录*，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。

MEMORY*：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度*， 对数据的安全性要求较低，不需要持久保存，可以选择MEMOEY。它对表的大小有要求，不能建立太大 的表。所以，这类数据库只使用在相对较小的数据库表。

注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询 的临时表，可以选择MEMORY存储引擎。

1.2 执行流程

1.2.1 简版执行流程图

002.png

1.2.2 详细执行流程图

​ 详细执行流程是到模块级的，也就是到程序一级的。

​ 各个模块之间的通信是很简单的通信，就是调方法。

003.jpg

​ 注：只要是带where条件的sql都会进入到查询优化器里。

2 物理结构

​ MySQL是通过文件系统对数据和索引进行存储的。

​ MySQL从物理结构上可以分为日志文件和数据索引文件。

​ MySQL在Linux中的数据索引文件和日志文件都在/var/lib/mysql目录下。

​ 日志文件采用顺序IO方式存储、数据文件采用随机IO方式存储。

​ 日志文件不会删也不会改，所以适合用顺序IO方式存储。

004.png

2.1 日志文件

2.1.1 错误日志（errorlog）

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

2.1.2 二进制日志（bin log）

​ 二进制日志是用来记录数据库的变化，binlog记录了数据库所有的DDL语句和DML语句，不包括DQL语句内容。语句以事件的形式保存，描述了数据的变更顺序，binlog还包括了每个更新语句的执行时间信息。如果是DDL语句，则直接记录到binlog日志，而DML语句，必须通过事务提交才能记录到binlog日志中。每重启一次mysql，产生一个bin log 日志。

​ 生产中一般都开启二进制日志，二进制日志一般可用于数据备份、恢复、主从关系的搭建。

2.1.3 通用查询日志（general query log）

​ 什么都记录，耗性能，生产中一般不开启。

2.1.4 慢查询日志（slow query log）

​ 用于SQL调优定位慢的查询语句，默认是关闭的。

​ 开启慢查询日志

​ slow_query_log=ON

​ 慢查询的阈值 单位S

​ long_query_time=3

​ 日志记录文件如果没有给出文件名值，默认为主机名，后缀为-slow.log

​ 如果给出了文件名值，但不是绝对路径名，文件则写入数据目录

​ slow_query_log_file=fileName

​ 查看日志开启情况:

​ show variables like 'log_%';

2.1.5 重做日志（redo log）

​ ib_logfile0、ib_logfile1都是重做日志，重做日志默认有两个。

2.1.6 回滚日志（undo log）

​ 用来回滚数据或者记录版本等等的。

2.1.7 中继日志（relay log）

​ 从机读取主机里边的binlog，读完以后往中继日志里边写，用于做主从的。

2.1.8 查看日志开启情况

​ show variables like 'log_%';

2.2 数据文件

2.2.1 InnoDB数据文件

​ .frm文件：要存放与表相关的数据信息,主要包括表结构的定义信息*

​ .ibd：使用独享表空间存储表数据和索引*信息，一张表对应一个ibd文件。

​ ibdata文件：使用共享表空间存储表数据和索引*信息，所有表共同使用一个或者多个ibdata文件。

2.2.2 MyIsam数据文件

​ .frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息*

​ .myd文件：主要用来存储表数据信息*。

​ .myi文件：主要用来存储表数据文件中任何索引的数据树。*