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