1.1 MySQL 体系结构

MySQL 采用分层架构，分为四层：

1. 连接层

   • 提供客户端连接服务（如 TCP/IP、本地 Socket 通信）。
   • 完成连接处理、授权认证及安全方案（如 SSL 加密）。
   • 引入线程池，为认证通过的客户端分配线程。

2. 服务层

   • 核心功能：SQL 接口、缓存查询、SQL 解析与优化（如索引选择、执行计划生成）。
   • 实现跨存储引擎功能（存储过程、函数等）。
   • SELECT 语句优先查询缓存，提升读密集场景性能。

3. 引擎层

   • 负责数据存储和提取，通过 API 与服务器交互。
   • 索引在引擎层实现，支持插件式引擎（如 InnoDB、MyISAM）。

4. 存储层

   • 存储数据文件（表数据、索引）、日志（redo/undo log、二进制日志、慢查询日志等）。
   • 与存储引擎交互完成数据读写。

与其他数据库相比，MySQL 的独特之处在于其架构可以在多种场景中应用并发挥良好作用，这主要体现在存储引擎上。插件式的存储引擎架构将查询处理和其他系统任务与数据的存储提取分离，使得可以根据业务需求选择合适的存储引擎。

1.2 存储引擎核心概念

存储引擎是存储数据、建立索引、更新 / 查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，因此存储引擎也被称为表类型。可以在创建表时指定存储引擎，如果没有指定，则使用默认存储引擎。

1. 建表指定引擎：

CREATE TABLE 表名 (
    字段1 类型 [COMMENT '注释'],
    ...
) ENGINE = InnoDB [COMMENT '表注释'];

2. 查询支持的引擎

   SHOW ENGINES;

1.3 三大存储引擎特性

1.3.1 InnoDB

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎。自 MySQL 5.5 起，它成为 MySQL 的默认存储引擎。

1. 特点

   • 支持 DML 操作遵循 ACID 模型，确保事务完整性。
   • 使用行级锁定机制，提升并发访问性能。
   • 支持 FOREIGN KEY 外键约束，保障数据完整性和正确性。

2. 文件结构

   • 每个 InnoDB 表对应一个 .ibd 表空间文件（文件名基于表名，如 account.ibd）。该文件存储表的表结构（早期版本使用 .frm 文件，新版本使用 .sdi 文件）、数据及索引信息。

   • 可使用 innodb_file_per_table 参数检查 .ibd 文件独立性：

     show variables like 'innodb_file_per_table';

     • 代码解释：此命令查询 InnoDB 是否启用每表独立文件模式。输出结果通常为 ON（开启），表示每张表生成单独的 .ibd 文件。这利于数据库管理，如备份和空间优化。

   • 可使用 MySQL 指令 ibd2sdi 提取表结构信息（例如，执行 ibd2sdi account.ibd 从 account.ibd 中读取 sdi 字典数据）。

3. 逻辑存储结构

   InnoDB 的逻辑存储结构由高到低依次为：表空间 (Tablespace)、段 (Segment)、区 (Extent)、页 (Page) 和行 (Row)。

   
   • 表空间 (Tablespace)：InnoDB 存储引擎逻辑结构的最高层，.ibd 文件就是表空间文件，其中可以包含多个段 (Segment)。
   • 段 (Segment)：表空间由各个段组成，常见的段有数据段、索引段、回滚段等。InnoDB 中对于段的管理由引擎自身完成，不需要人为控制，一个段中包含多个区 (Extent)。
   • 区 (Extent)：区是表空间的单元结构，每个区的大小为 1M。默认情况下，InnoDB 存储引擎页大小为 16K，即一个区中一共有 64 个连续的页 (Page)。
   • 页 (Page)：页是组成区的最小单元，也是 InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
   • 行 (Row)：InnoDB 存储引擎是面向行的，数据按行存放。每一行中除了定义表时指定的字段以外，还包含两个隐藏字段。

1.3.2 MyISAM

MyISAM 定位为高性能但功能简化的存储引擎，曾是 MySQL 默认引擎。

1. 特点

   • 不支持事务或外键约束，设计更轻量。
   • 仅支持表级锁，限制了并发能力，但提升了读访问速度。
   • 访问速度快，尤其针对读密集型操作。

2. 文件结构

   • 每个 MyISAM 表对应三个独立文件：
     • xxx.sdi：存储表结构信息（基于 JSON 格式）。
     • xxx.MYD：存储实际数据（MYData）。
     • xxx.MYI：存储索引（MYIndex）。

1.3.3 Memory

Memory 将数据完全存储在内存中，适合临时表或缓存场景；断电或故障会导致数据丢失。

1. 特点

   • 数据驻留内存（非持久化存储），访问极快。
   • 默认使用 Hash 索引，优化等值查询效率。

2. 文件结构

   • 仅需一个文件：xxx.sdi，存储表结构信息。数据本身不落盘，依赖内存管理。

1.3.4 存储引擎的区别及特点

InnoDB、MyISAM 和 Memory 存储引擎的区别及特点总结如下表：

特点	InnoDB	MyISAM	Memory
数据存储方式	基于聚簇索引，数据和索引存储在一起	数据和索引分开存储	数据存储在内存中，重启丢失
存储限制	64TB	默认 4GB (可配置，最大 256TB)	受限于可用内存大小
事务安全	支持 ACID 事务，提供事务隔离和数据一致性	不支持事务	不支持事务
锁机制	行锁 (基于索引，减少锁冲突)	表锁 (锁粒度大，并发性能较低)	表锁
B+tree 索引	支持 (聚簇索引和二级索引)	支持	支持
Hash 索引	自适应哈希索引 (InnoDB 内部优化，不可控)	不支持	支持
全文索引	支持 (5.6 版本后)	支持	不支持
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	不支持	不支持

1.4 存储引擎对比与选型

特性	InnoDB	MyISAM	Memory
事务支持	✔️	✘	✘
外键	✔️	✘	✘
锁粒度	行锁	表锁	表锁
索引类型	B+Tree	B+Tree	Hash（默认）
存储限制	64TB	有	内存大小
可靠性	高（崩溃恢复）	低	低（断电丢失）

选型建议：

• InnoDB：需事务、高并发写操作（如订单系统）。
• MyISAM：读密集、少更新场景（如数据仓库）。
• Memory：高速临时数据处理（如会话缓存）。