MySQL08-存储引擎

1. 存储引擎

1.1 MySQL 体系结构

MySQL 的存储引擎架构将查询处理 （Query Processing）及其他系统任务（Server Task）和数据的存储/提取相分离，这种处理和存储分离的设计可以在使用时根据性能、特性，以及其他需求来选择数据存储的方式。MySQL的体系架构图如下：

MySQL的架构自上而下大致可以分为网络连接层、数据库服务层、存储引擎层和系统文件层四大部分，各部分介绍如下：

1. 网络连接层
   网络连接层位于整个MySQL体系架构的最上层，主要担任客户端连接器的角色。提供与MySQL服务器建立连接的能力，几乎支持所有主流的服务端语言，如 Java、C、C++、Python 等，各语言都是通过各自的API接口与MySQL建立连接。

2. 数据库服务层
   数据库服务层是整个数据库服务器的核心，主要包括了系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存等部分,主要完成如缓存的查询，SQL 的分析和优化，部分内置函数的执行等大多数核心服务功能。所有跨存储引擎的功能也在这一层实现，如过程、函数等。

3. 存储引擎层
   MySQL中的存储引擎层主要负责数据的写入和读取，与底层的文件进行交互。MySQL中的存储引擎是插件式的，服务器中的查询执行引擎通过相关的接口与存储引擎进行通信，同时，接口屏蔽了不同存储引擎之间的差异。MySQL中，最常用的存储引擎就是InnoDB和MyISAM。

4. 数据存储层
   数据存储层主要包括MySQL中存储数据的底层文件，与上层的存储引擎进行交互，是文件的物理存储层。其存储的文件主要有：日志文件、数据文件、配置文件、MySQL的进行pid文件和socket文件等。

1.2 存储引擎概述

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

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

创建新表时如果不指定存储引擎，那么系统就会使用默认存储引擎，MySQL5.5 之前的默认存储引擎是 MyISAM，MySQL5.5 之后改为了 InnoDB。MySQL 中同一个数据库，不同的表格可以选择不同的存储引擎。

以下是一些和存储引擎有关的 SQL 命令：

-- 查询建表语句，通过建表语句可以查看当前数据表的存储引擎
show create table 表名;
-- 查询数据表状态信息，可以通过该信息查看当前数据表的存储引擎
show table status like '表名';

-- 查看数据表的默认存储引擎
show variables like 'default_storage_engine';

-- 查看当前数据库支持的存储引擎
show engines;

-- 建表时指定存储引擎
create table 表名 (
字段名 数据类型
...
) engine = 存储引擎;

-- 建表后修改存储引擎
alter table 表名 engine = 存储引擎;

1.3 常见存储引擎概述

1.3.1 InnoDB

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎，同时，InnoDB也是事务型数据库的首选引擎。

引擎特点：

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

文件结构：

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

可以用以下命令查看数据库中关于表空间的信息：

# 查看数据库是否启用独立表空间
show variables like 'innodb_file_per_table';
-- 参数 innodb_file_per_table 决定多张表共享一个表空间还是每张表对应一个表空间
-- 如果返回 innodb_file_per_table=ON，就表示启用了独立表空间

InnoDB 引擎将 frm 表结构文件融入到了 idb 文件中，但认可通过命令行提取表结构数据，相关命令如下：

# 在 cmd 命令行中运行
ibd2sdi xxx.ibd

逻辑存储结构：

InnoDB中所有数据都被逻辑地存放在一个空间中，称之为表空间（tablespace）。表空间又由段（segment）、区（extent）、页（page）组成，页在一些文档中有时也称为块（block）。逻辑存储结构大致如下图：

每个页默认为16KB，每个区默认大小为1MB，所以，每个区有64个连续的页。现在的InnoDB版本中，还有能为页压缩的功能，可以压缩到2KB、4KB、8KB。

1.3.2 MyISAM

MyISAM 基于 ISAM 存储引擎，并对其进行扩展，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务，不支持外键。MyISAM 是 MySQL5.5 之前的默认存储引擎。

引擎特点：

1. 不支持事务，不支持外键；
2. 支持表锁，不支持行锁；
3. 访问速度快；

文件结构：

使用MyISAM引擎创建数据库，将产生3个文件，文件名即表名，其中，各文件用处如下：

1. 表名.sdi：存储表结构信息
2. 表名.MYD：MYData，存储数据
3. 表名.MYI：MYIndex，存储索引

1.3.3 Memory

Memory 引擎的表数据是存储在内存中的，为查询和引用其他表数据提供快速访问，所以其数据受硬件、断电问题的影响，安全性不高，只能将这些表作为临时表或缓存使用。

特点：

1. 存放在内存中，速度快
2. hash索引（默认）

文件：

Memory 引擎用 表名.sdi 文件存储表的结构信息。

1.4 存储引擎选择

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

1.4.1 常见存储引擎特点

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
B+tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	支持（5.6版本之后）	支持	-
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	-	-

1.4.2 常见存储引擎应用

1. InnoDB

支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交和回滚。

2. MyISAM

插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

3. Memory

所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择Memory。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

此外，同一个数据库的不同表可以使用不同的存储引擎。如果一个表要求比较高的事务处理，可以选择InnoDB。如果一个表查询要求比较高，可以选择 MyISAM。如果一个表是用于查询的临时表，可以选择 Memory。各平台的足迹和评论适合使用 MyISAM 引擎，缓存适合使用 Memory 引擎，根据实际需要选择即可。