MySQL体系结构

• 连接层：最上层是一些客户端连接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户验证它所具有的操作权限；
• 服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口、并完成缓存的查询、SQL的分析和优化、部分内置函数的执行。所有的跨存储引擎功能也在这一层实现， 如过程、函数等；
• 引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。Index是在存储引擎层实现的，意味着不同的存储引擎索引的结构不同；InnoDB存储引擎是MySQL5.5版本后默认的存储引擎。
• 存储层：存储引擎控制数据如何存取和组织，而具体的数据最终存储在磁盘中，存储层还包含redo,Index,Binary,Error,Query等日志。

存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

查询当前数据库支持的存储引擎

• MySQL有很多存储引擎，不同的存储引擎提供了不同的数据存储方案。
• MySQL5.5版本以后，默认使用的存储引擎是InnoDB。

show engines;

在创建表时，指定存储引擎

create table 表名(
字段1 类型，
字段2 类型，
......
) engine = innodb ;

示例1：创建表，指定MyISAM存储引擎

create table my_myisam(
    id int,
    name varchar(20)
) engine = MyISAM;
show create table my_myisam;

示例2：创建表，指定Memory存储引擎

create table my_memory(
    id int,
    name varchar(20)
) engine = Memory;
show create table my_memory;

存储引擎特点

我们通过上一小节学会了怎么在创建表时指定存储引擎，那这些存储引擎都有什么特点和区别呢。

InnoDB

InnoDB表空间文件

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎。

特点

• DML操作遵循ACID模型，支持事务；
• 行级锁，提高并发访问性能；
• 支持外键FOREIGNKEY约束，保证数据的完整性和正确性；

存储文件

InnoDB引擎的每张表都会对应一个xxx.ibd表空间文件，xxx代表表名，这个表空间文件存储该表的表结构（frm、sdi）、数据和索引。

• frm：MySQL8之前版本表结构存放在frm文件中，表数据和索引放在.idb文件中；
• sdi：sdi是一个存储在xxx.idb表空间中的数据字典，负责存储表结构数据（MySQL8.0版本开始使用sdi存储表结构）。
• 参数 innodb_file_per_table：决定表空间是单表专用，还是多表共享，默认的配置是单表专用。

show variables like 'innodb_file_per_table';

查看本地存储的表空间文件

InnoDB逻辑存储结构

• InnoDB的逻辑存储结构为：TableSpace 表空间 --> Segment 段 --> Extent 区 --> Page 页 --> Row 行 ；
• 区的大小固定1M，包含了64个页；
• 页的大小固定16K，分为索引页和数据页，是磁盘操作的最小单元。
• 行包含了Trx id （最后一次操作事务的id）、Roll pointer（指针）和 column（字段值）。

MyISAM

MyISAM是MySQL早期的默认存储引擎。

特点

• 不支持事务，不支持外键，支持表锁；
• 不支持行锁，访问速度快。

存储文件

MyISAM表有三个存储文件

• xxx.sdi：存储表结构信息；
• XXX.MYD：存储数据；
• XXX.MYI：存储索引。

Memory

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点

• 使用内存存放：通过内存存取数据，有很高的查询速率；
• 使用hash索引（默认）。

存储文件

XXX.sdi：Memory存储引擎只有一个存储文件，用于存储表结构信息。

存储引擎的区别

这里主要说明一些InnoDB与MyISAM的区别。

• InnoDB支持事务，而MyISAM不支持事务；
• InnoDB支持行锁，而MyISAM只支持表锁；
• InnoDB支持外键，而MyISAM不支持外键。

存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

• InnoDB：是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
• MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。不过MyISAM这个存储引擎用得很少了，在使用MyISAM引擎的场景会使用MongoDB替代；
• MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。Memory存储引擎用得也很少，在使用Memory引擎的场景会使用Redis替代。

小结

存储引擎这一节我们主要讲解了MySQL的体系结构的连接层、服务层、引擎层和存储层；然后介绍了存储引擎，不同的存储引擎提供了不同的数据存储和组织方式，我们可以通过建表语句指定存储引擎；通过认识不同存储引擎的特点，这里重点关注InnoDB和MyISAM的三大区别，InnoDB支持事务、外键和行级锁，而MyISAM不支持；最后是存储引擎的应用，InnoDB适用于存储对事务、数据完整性要求较高的核心数据，而MyISAM可以用于存储非核心数据。