MySQL学习笔记1 - MySQL架构

MySQL架构可以分为物理架构与逻辑架构

1、逻辑架构

逻辑架构图式如下

1.1、连接器（Connectors）

---

MySQL连接器为客户端程序提供与MySQL服务器的连接。 连接器使您能够连接和执行来自另一种语言或环境的MySQL语句，包括ODBC，Java（JDBC），Perl，Python，PHP，Ruby等MySQL实例。

以JAVA为例，mysql-connector-java是MySQL的JDBC驱动包，通过该jar包实现JDBC连接MySQL。Oracle收购MySQL后，为MySQL开发了很多连接器，如Connector / C ++、Connector/NET等。分别为不同语言环境提供与MySQL的交互。

若主机上安装了MySQL，则可以通过该主机上的黑窗口打开远程MySQL服务。

mysql -h127.0.0.1 -P3306 -uroot -p123456

参数	含义	说明
h	host	MySQL服务
P	port	端口
u	username	用户名
p	password	密码

1.2、系统管理与控制工具（Management Serveices & Utilities）

---

系统管理和控制工具集合，例如备份还原，安全复制等功能

1.3、连接池（Connection Pool）

---

当一个应用需要进行数据库请求操作，则我们需要创建一个数据库对连接，请求完成后，关闭该连接即可。然而创建连接过程的开销相对较大，当一个应用频繁访问数据库时，则频繁建立、关闭连接会极大的消耗系统的性能，因为对于连接的使用成了系统性能的瓶颈。线程池通过监听并接收MySQL Server等数据库连接请求，转发所有请求到线程管理模块。线程管理模块负责管理并维护（如线程创建、线程cache）数据库连接线程，当收到请求后会为其分配或创建一个线程为其单独服务。这些线程的主要工作就是负责MySQL Server与客户端之间的通信。

1.4、SQL接口（Sql Interface）

---

用于接收用户的MySQL命令，并且返回客户需要查询的结果，如SELECT * FROM tb_name; 就是通过调用SQL Interface实现查询功能。SQL接口与解析器、优化器、缓存、存储引擎组成了MySQL。

1.5、解析器（Parser）

---

我们通常将客户端传入的SQL命令称之为Query，Query不能被MySQL直接执行。连接线程接收到客户端的一个Query后，会直接将该Query传递给解析器，解析器对Query进行语义和语法的解析，并分解成数据结构：若分解失败，则说明这个SQL语句是不合理的；若分解成功，则会根据分解结果获取操作类型，并针对性的转发到对应的处理模块。

1.6、查询优化器（Optimizer）

---

在MySQL语句执行Query之前，MySQL会采用选取 -> 投影 -> 联接的方式，根据请求的Query语句和数据库中的一些统计数据，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 Query的结果。如：

SELECT name, age FROM users WHERE gender='1';

选取：该SELECT查询会根据条件选取，而不是查询所有数据之后再进行条件过滤。
投影：该SELECT查询会先根据name和age进行属性投影，而不是将属性全部取出以后再进行过滤。
联接：将这两个查询条件联接起来生成最终查询结果。

1.7、查询缓存（Cache & Buffer）

---

通过哈希算法计算客户端传入的SELECT类Query请求（hash(querySql)），获取对应hash值。并与该SELECT类查询语句得出的结果集一一对应，将结果cache到内存中。在后续查询中，若查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。在读比例非常高的应用系统中，缓存可以极大的提高查询的性能。

然而，该hash值（也就是Query）所取数据的基表发生任何数据变化时，都会使该Query对应的cache失效。这就意味着，对于写操作频繁的表，缓存是不合适的；同时，缓存提高查询性能的原因是因为查询的结果集存放在内存中，这就意味着缓存方式对于内存来说是一个挑战，对于查询结果较大，或者系统内存拮据的情况，缓存的做法是不合适的；再者，有很多优秀的工具，如Redis、MongoDB等，他们对于内存数据的操作要远优于该缓存机制。所以MySQL的查询缓存功能显得没那么强大，MySQL8.0后不再使用。

1.8、可插拔式存储引擎（Pluggable Storage Engines）

---

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

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

存储引擎是基于表的，而不是数据库。最常用的就是InnoDB、MyISAM，创建存储引擎：

-- MySQL5.5后默认为InnoDB，此处以MyISAM为例
create table product(...) engine=MyISAM;

• 常见存储引擎

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

• MyISAM与InnoDB比较

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

2、物理架构

2.1、MySQL数据文件

---

Linux下MySQL的数据文件默认存放路径为 /var/lib/mysql/数据库/ 下，可以通过指令查找：

SHOW VARIABLES LIKE '%datadir%';

进入数据库目录下后，可以查看到各个数据库表到数据文件，如下图。各个存储引擎对应的存储文件都不相同，如：以MyISAM为存储引擎的表，会生成以下三类文件：.frm、.myd、.myi；以InnoDB为存储引擎的表，会生成以下两种文件：.frm、.ibd。

2.2、MySQL日志文件

---

多数日志文件是可以自行控制开关，可通过以下命令查看启用情况：

SHOW VARIABLES LIKE 'log_%';

• 错误日志（error log）
  默认是开启的，而且从5.5.7以后无法关闭错误日志。
  错误日志记录了运行过程中遇到的所有严重的错误信息,以及 MySQL每次启动和关闭的详细信息。
• 二进制日志（bin log）
  记录数据变化，用来进行数据备份、恢复、主从。生产中开启。binlog日志记录了数据库所有的ddl语句和dml语句，但不包括select语句内容，语句以事件的形式保存，描述了数据的变更顺序，binlog还包括了每个更新语句的执行时间信息。如果是DDL语句，则直接记录到binlog日志，而DML语句，必须通过事务提交才能记录到binlog中。
• 通用查询日志（general query log）
  啥都记录，极其耗费性能，故生产中不开启，可用于平时开发纠错。
• 慢查询日志（slow query log）
  记录查询较慢的语句，默认是关闭的。需要通过设置参数进行开启。可用于SQL调优，用于定位慢的SELECT，可以通过修改配置文件去定义“慢”的条件：

# 进入mysql配置文件目录并编辑配置文件
vi /etc/my.cnf

# 开启慢查询日志
slow_query_log=ON
# 慢查询的阈值，记录执行时间超过long_query_time秒的所有查询，便于收集查询时间比较长的SQL语句
long_query_time=3
# 日志记录文件如果没有给出file_name值，默认为主机名，后缀为-slow.log。如果给出了文件名， 但不是绝对路径名，文件则写入数据目录。 
slow_query_log_file=file_name

• 重做日志（redo log）
  帮助实现持久化事务处理。
• 回滚日志（undo log）
  帮助实现回滚事务处理。
• 中继日志（relay log）
  与bin log里的内容一致
  
  

文件写入顺序

---

• 日志文件：
  日志文件写入顺序：顺序写入。顺序写入只需要一次寻道（磁道地址），所以其执行速度快，耗费时间短。
• 数据文件：
  数据文件写入顺序：随机写入。随机写入需要多次寻道，所以其执行速度慢，耗费时间长。但数据文件中存在大量的新增或删除的操作，若使用顺序写入，则在进行删除操作的时候，会删除连续磁道中的片段，导致该片段无法复用，从而产生磁道碎片。数据磁盘落盘可利用日志文件去提升IO性能。

本文地址：https://blog.csdn.net/weixin_39997438/article/details/107308099