【MySQL】逻辑架构简述

本文是笔记类型文章，只有结论没有证明，可用于复习巩固知识，不能用于新知识的学习。如有错误，恳请指正，不胜感谢。

转载请于文首标明出处：【MySQL】逻辑架构简述 - 掘金 (juejin.cn)

文章仍未完工，内容会逐步完善

系列文章：

逻辑架构

MySQL 逻辑架构主要分为三层：

连接层：用户与客户端进行连接和交互。主要进行连接处理、授权认证和安全等功能。
服务层：服务层负责 SQL 的解析、优化、增强等功能。包括查询解析、分析、优化、缓存、内置函数、存储过程、触发器和视图等。
引擎层：存储引擎负责数据的存储和提取。引擎层由多种存储引擎共同构成，每个存储引擎都有它的优势和劣势。服务层通过存储引擎向上层开放的 API 与存储引擎进行交互。这些 API 隐藏了各个存储引擎的差异，使得这些差异对上层的查询过程变得透明。存储引擎不会解析 SQL，也不会相互进行通信，仅仅只是响应服务器的请求而已。

不过例外的是 InnoDB 存储引擎会解析 SQL 中的外键定义，因为服务层并没有定义外键这一功能。

存储引擎

在文件系统中，MySQL 将每个数据库（Schema）保存为其数据目录下的一个子目录。创建表时，会在所属数据库目录下创建和表同名的 .frm，.frm 文件用于保存表的定义，MySQL 服务层负责表的定义，因此无论表是使用哪一种存储引擎存储，其 .frm 文件的存储格式和内容格式都是一样的。而表的数据存储文件名、文件格式则由相应的存储引擎自行设置。

由于通过所在操作系统的目录和文件名确定数据库和表之间的关系，因此其命名会受所在的操作系统文件格式影响。在 Windows 中数据库和表名是大小写无关的，而在类 Unix 如 Linux 中则是大小写相关的。

可以通过 show variables like '%datadir%'; 来查找 MySQL 的数据存储目录。可以通过 SHOW TABLE STATUS LIKE 'table_name' 查询表信息或 DESC 'table_name' 查询表数据定义。可以通过 SHOW CREATE TABLE 'table_name' 查询表的建表语句。

InnoDB

InnoDB 是 MySQL 默认的事务型引擎，主要面向在线事务处理应用。

InnoDB 作为 MySQL 最重要的存储引擎，它的外部特性有：事务、多版本并发控制、意向锁、行级锁与间隙锁、支持外键、支持跨引擎查询、File per Table、支持压缩、崩溃恢复与热备份。它的内部特性有：Buffer Poll 机制、Change Buffering 机制、自适应哈希索引、支持动态行格式等。

MyISAM

MyISAM 是 MySQL 5.1 之前的默认存储引擎，虽然 MyISAM 不支持事务，不支持高并发读写，但是 MyISAM 的只读性能非常恐怖。MyISAM 的特性为：

仅支持表锁
MyISAM 仅支持表锁，这也是 MyISAM 只读性能极高的原因。MyISAM 表读取时会对整张表添加共享锁，写入时对表添加排他锁。不过在读取时，可以往表中插入新的数据，称为并发插入（Concurrent Insert）。
数据索引分开存储
MySIAM 的表数据文件存储为 .MYD 文件，索引文件存储为 .MYI 文件。MyISAM 表支持动态或静态行，同时可以存储行数据数。
可以进行表检查与表修复
MyISAM 支持手工或者自动执行检查和修复操作，不过修复的耗时非常长。
支持前缀索引、全文索引和空间函数。
可以缓存索引和延迟更新索引键
MyISAM可以通过Key_buffer_size缓存索引，以大大提高访问性能，减少磁盘 IO，但是这个缓存区只会缓存索引，而不会缓存数据。如果对表或全局指定了 DELAY_KEY_WRITE 选项，每次表修改完成后，都不会立刻将修改的索引数据写入磁盘，而是写在内存的键缓冲区（In-memory Key Buffer）中，只有在清理键缓冲区或者关闭表时会刷入磁盘。这种方式可以极大提高写入性能，但是如果服务宕机会造成索引损坏，需要执行修复操作。
支持压缩表
压缩表只读不能修改，但可以极大减少磁盘空间占用，同时可以减少磁盘 IO，提高查询性能。因此对于像 City 或者 Category 这种表，使用 MyISAM 的压缩表会使得访问性能非常快。

InnoDB 与 MyISAM

InnoDB 和 MyISAM 的简单区别有：

| 引擎　  | 事务 | 行锁 | 外键  | 索引数据结构  | 设计目标　        |
|:-------|:----:|:----:|:----:|:-------------|:-----------------|
| InnoDB |  √   |  √   |  √   | B+ 树        | 在线高并发事务场景 |
| MyISAM |  ×   |  ×   |  ×   | B 树         | 多读少写快速读场景 |

其他引擎

Memory 引擎：

为了快速访问而设计的基于内存的存储引擎。比 MyISAM 快接近一个量级，但重启数据就丢失，因为没有持久化。虽然是内存数据引擎，但无法与 Redis 相提并论，因为 Memory 是表锁，写入性能非常低；而且它不支持 BLOB 或 TEXT 类型的列，行长度固定，存储变长字符串也会被转化为定长字符串导致内存浪费。
NDB 引擎：

面向集群架构的存储引擎，使用 share nothing 的集群架构，因此拥有非常高的可用性。NDB 引擎的索引数据放在内存中，因此主键查询的速度极快，并且可以通过添加节点线性地提高数据库性能，是高可用、高性能的集群。但 NDB 的 JOIN 操作是在 MySQL 服务器层完成的，因此 JOIN 查询速度很慢。
Maria 引擎：

用于取代 MyISAM 的存储引擎，由 MySQL 创始人之一开发，支持缓存数据和索引文件、支持行锁、支持多并发版本控制（MVCC）、支持事务和拥有更好的性能。后面演变出了 MariaDB。
CSV 引擎：

用来与外界简易交换数据的存储引擎，可以将普通的 CSV 文件作为 MySQL 表来处理，但不支持索引。我们把 CSV 文件直接存储到 MySQL 目录下即可让 MySQL 读取。MySQL 在使用它的时候，其他程序可以立即读取它的数据。
Archive 引擎：

针对高速插入和压缩的存储引擎。只支持插入和查询操作，MySQL5.1 前不支持索引，每次查询时都需要全表扫描，但会缓存所有的写并利用 zlib 算法对输入数据进行压缩，所以比 MyISAM 的磁盘 IO 更快。