一、Mysql 架构体系

前言

了解Mysql的架构体系，对mysql的学习以及性能优化有很大的帮助。比如很多查询优化工作实际上就是遵循服务层的一些原则让mysql优化器能够按照预想的合理方式运行。mysql整体架构分为4层，如下图。

自顶向下分别为：网络连接层、服务层、存储引擎层、系统文件层

一、网络连接层

1、功能

主要负责连接管理、授权认证、安全等。
当 MySQL 启动(MySQL 服务器就是一个进程)，等待客户端连接，每一个客户端连接请求， 服务器都会新建一个线程处理(如果是线程池的话，则是分配一个空的线程)，每个线程独 立，拥有各自的内存处理空间。

// 查看mysql允许的最大连接数
show VARIABLES like '%max_connections%'

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

二、服务层（核心层）

主要功能：sql语句的解析、优化、查询缓存，MySQL 内置函数的实现，跨存储引擎功能(所谓跨存储引擎就是说每个引擎都需提供的功能(引擎需对外提供接口))，例如: 存储过程、触发器、视图等。

2⃣️查询缓存
如果是查询语句(select 语句)，首先会查询缓存是否已有相应结果，有则返回结果，无则 进行下一步(如果不是查询语句，同样调到下一步)

// 查看缓存是否开启-- 默认不开启
show variables like '%query_cache_type%'
// 查看缓存大小--默认值 1M
show variables like '%query_cache_size%' 

// 会报错，query_cache_type 只能配置在 my.cnf 文件中
SET GLOBAL query_cache_type = 1

在生产环境建议不开启，除非经常有 sql 完全一模一样的查询。 QC 严格要求 2 次 SQL 请求要完全一样，包括 SQL 语句，连接的数据库、协议版本、字符 集等因素都会影响

3⃣️解析器与预处理器
MySQL会解析查询，并创建了一个内部数据结构（解析树）。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理会根据MySQL的规则进一步检查解析树是否合法。比如要查询的数据表和数据列是否存在等。

4⃣️查询优化器：
优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。优化器并不关心使用的什么存储引擎，但是存储引擎对优化查询是有影响的。优化器要求存储引擎提供容量或某个具体操作的开销信息来评估执行时间。

5⃣️查询引擎
在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令调用存储引擎的接口得出结果。

三、存储引擎层

1、作用

负责MySQL中数据的存储与提取。 服务器中的查询执行引擎通过API与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。
存储引擎是针对于表的而不是针对库的（一个库中不同表可以使用不同的存储引擎），服务器通过API与存储引擎进行通信，用来屏蔽不同存储引擎之间的差异。

#看你的 mysql 现在已提供什么存储引擎:
mysql> show engines;
#看你的 mysql 当前默认的存储引擎:
mysql> show variables like '%storage_engine%';

2、MyISAM

2.1特点

不支持事务、并发加表锁、支持全文搜索。
支持压缩

myisampack -b -f /usr/local/mysql/data/mall/testmysam.MYI

# 压缩后再往表里面新增数据就新增不了
insert into testmysam VALUES(1),(2),(3

2.2适用场景

• 非事务型应用(数据仓库，报表，日志数据)
• 只读类应用
• 空间类应用(空间函数，坐标)

3、Innodb

3.1特点

• Innodb 是一种事务性存储引擎
• 完全支持事务得ACID特性
• Redo Log 和 Undo Log
• Innodb支持行级锁(并发程度更高)

3.2对比

4、CSV

4.1特点

• 以csv格式进行数据存储
• 所有列都不能为null的
• 不支持索引(不适合大表，不适合在线处理)
• 可以对数据文件直接编辑(保存文本文件内容)

# 建表
create table mycsv(id int not null,
c1 VARCHAR(10) not null,
c2 char(10) not null) 
engine=csv;
# 插入数据
insert into mycsv values(1,'aaa','bbb'),(2,'cccc','dddd');
# 打开csv数据，修改文本数据
vi /usr/local/mysql/data/mall/mycsv.CSV
# 刷新修改后的数据
flush TABLES;
# 查找数据，发现数据和在文本中修改的数据相同 
select * from mycsv
// 创建索引报错，csv存储引擎不支持索引
create index idx_id on mycsv(id)

5、Archive

以 zlib 对表数据进行压缩，磁盘 I/O 更少 数据存储在 ARZ 为后缀的文件中

5.1特点

• 只支持 insert 和 select 操作
• 只允许在自增 ID 列上加索引

5.2适用场景

日志系统

6、Memory

6.1特点

• HEAP 存储引擎，所以数据保存在内存中
• 支持HASH索引和BTree索引
• 所有字段都是固定长度 varchar(10) = char(10)
• 不支持Blog和Text等大字段
• Memory存储引擎使用表级锁
• 最大大小由max_heap_table_size参数决定

6.2与临时表的区别

6.3适用场景

• hash索引用于查找或者是映射表(邮编和地区的对应表)
• 用于保存数据分析中产生的中间表
• 用于缓存周期性聚合数据的结果表

7、Ferderated

7.1、特点：

• 提供了访问远程MySQL服务器上表的方法
• 本地不存储数据，数据全部放到远程服务器上
• 本地需要保存表结构和远程服务器的连接信息

7.2、使用场景

偶尔的统计分析及手工查询(某些游戏行业)

# 建表
create database local;
# 建库
create database remote;
# 建表
create table remote_fed
(id int auto_increment not null,
c1 varchar(10) not null default '',
c2 char(10) not null default '',
primary key(id)) engine = INNODB
# 插入数据
INSERT into remote_fed(c1,c2) values('aaa','bbb'),('ccc','ddd'),('eee','fff');

# 建表
CREATE TABLE `local_fed` (
`id` int(11) NOT NULL AUTO_INCREMENT,
 `c1` varchar(10) NOT NULL DEFAULT '', 
 `c2` char(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)) ENGINE=federated CONNECTION='mysql://root:root1234%@127.0.0.1:3306/remote/remote_fed'


 # 访问本地库即可访问远程innodb的数据库
select * from local_fed
delete from local_fed where id = 2 select * from remote.remote_fed

四、系统文件层-物理存储结构

1、数据库的数据库(DataDir)

mysql 安装的时候都要指定 datadir,其查看方式为,其规定所有建立的数据库存放位置

show VARIABLES like 'datadir'

2、数据库

创建了一个数据库后，会在上面的 datadir 目录新建一个子文件夹

3、表文件

用户建立的表都会在上面的目录中，它和具体的存储引擎相关，但有个共同的就是都有个 frm 文件，它存放的是表的数据格式

查看表的结构

mysqlfrm --diagnostic /usr/local/mysql/data/mall/account.frm

3.1MyISAM表结构

• frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等。
• MYD文件：MyISAM存储引擎专用，用于存储MyISAM表的数据
• MYI文件：MyISAM存储引擎专用，用于存储MyISAM表的索引相关信息

3.2InnoDb表结构

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

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

.ibd存储的是独享表空间数据，并且每个表一个ibd文件。

.ibdata文件是共享表空间存储方式，所有的表共同使用一个ibdata文件，即所有的数据文件都存在一个文件中。

配置
决定使用哪种表的存储方式可以通过mysql的配置文件中 innodb_file_per_table选项来指定

InnoDB默认使用的是独享表的存储方式，这种方式的好处是当数据库产生大量文件碎片的时，整理磁盘碎片对线上运行环境的影响较小