「MySQL高级篇」MySQL存储引擎

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第一天，点击查看活动详情

大家好，我是Zhan，一名个人练习时长一年半的大二后台练习生，最近在学MySQL高级篇，欢迎各路大佬一起交流讨论

👉本篇速览

早在MySQL基础篇的学习中，我们就一直看到innodb这个存储引擎，但是好像对于其他的存储引擎也没有去学习和了解，而innodb有何种特点也不得而知，而本篇将从一下四点，带你逐一攻破Ta：

1️⃣首先系统地介绍一下MySQL数据库服务器的体系结构，在学习存储引擎之前，我们需要了解存储引擎在MySQL数据库服务器中所占的地位和作用
2️⃣再介绍一下MySQL数据库中的存储引擎，即MySQL有哪些存储引擎，以及我们如何去选择存储引擎，此处的选择是指设置存储引擎为某个存储引擎
3️⃣介绍各个存储引擎的特点和区别，只有了解了存储引擎之间的区别以及各自的优势，我们才能在实际环境下选择最合适的存储引擎
4️⃣第四个方面，讲解各个存储引擎的应用场景，系统的总结各个存储引擎合适的应用场景，方便后续选择时的思路确定

1️⃣ MySQL数据库服务器的体系结构

以下MySQL数据库服务器的体系架构图，最上面的一层就是作为后端开发的我经常会使用到的客户端连接器JDBC，当然，除了Java，Python,PHP,Ruby也可以成为客户端连接器而我们主要关注的是客户端连接器以下的，MySQL服务端的体系结构，即MySQL Server

首先第一层：连接层。它的作用是：接收客户端连接，完成连接的处理，认证授权，安全方案，检查是否超过最大连接数…… 其中认证授权就是我们在使用用户名和密码登陆的时候的过程，并赋予该用户对应的权限

再就是第二层：服务层。核心功能基本上都在这一层完成：SQL接口、解析器、查询优化器、缓存，像创建存储过程、视图、触发器这些都在服务层完成

第三层：引擎层。上图中一个个紫色的圆筒就是不同种类的存储引擎，其中第一个就是我们常用的innodb，上图中是MySQL提供给我们的存储引擎，而我们自己也可以对存储引擎进行扩展它被称为：可插拔式存储引擎，类似于接口，我们需要就把它插上来，不需要就把它拔掉存储引擎控制的是我们的数据存储和拿取的方式

这里还有一点要注意的是：索引是在存储引擎这一层决定的

最后一层：存储层。引擎层只是决定了我们如何去存取数据，而数据实际上是存储在磁盘中的，同时，数据库的日志也会存储在磁盘当中

2️⃣ MySQL数据库中的存储引擎

尽管前面介绍了这么多，但是什么是存储引擎呢？我们在操作数据库的时候如何去指定存储引擎也是一个问题。

📣存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，也就是说一张表使用一个存储引擎，数据库中的不同表的存储引擎不一定相同，存储引擎因此可以被称为表类型。

在之前MySQL基础篇的时候，我们是没有指定存储引擎的，但是在下面导出创建表SQL语句的时候，我们会发现它的存储引擎是innodb

这是因为在MySQL5.5以后，数据库表的默认存储引擎是innodb

🎯指定存储引擎

其实有了上述导出的那条创表的SQL语句，我们其实不难得出指定存储引擎的方法，也就是：

create table `table_name`(
	`字段1` 字段1类型 [COMMENT 备注],
	...,
	`字段n` 字段n类型 [COMMENT 备注]
) ENGINE=引擎类型 [COMMENT 备注]

但是我们如何知道当前数据库版本支持哪些存储引擎呢，这就需要执行SHOW ENGINES

通过这张表我们能得到的信息：

Support字段也就是当前MySQL版本是否支持
- 我们可以得到的信息有：
- 支持的有：MEMORY,MRG_MYISAM,CSV,PERFORMANCE_SCHEMA,MYISAM,InnoDB,BLACKHOLE,ARCHIVE
- 不支持的有：FEDERATED
- 默认的存储引擎为InnoDB
补充信息：MyIsAM是以前版本的默认存储引擎

3️⃣各个存储引擎的特点和区别

这里主要对这三个主要使用的存储引擎InnoDB,MYISAM,MEMORY进行介绍

⚙InnoDB

它的特点可以用数据库中查询出来的COMMENT中的三点来概括：

Supports transactions:支持事务。在使用InnoDB作为存储引擎的时候，进行DML操作的时候遵循ACID模型，支持事务
- DML：对于数据的增删改操作
- ACID：原子性(atomicity)、一致性(consistency)、隔离性(isolation)、持久性(durablility)
row-level locking：行级别锁。提高并发访问的性能。
foreign keys：支持外键约束，保证数据的完整性和正确性

除此之外还要谈谈InnoDB存储数据的文件

它存储数据库文件的类型为xxx.ibd，其中xxx表示表名，也就是说每一个使用InnoDB存储引擎的表都会对应一个ibd文件，存储该表的表结构(frm,sdi)、数据和索引
其中表结构文件，早期的时候有一个文件叫frm，表结构都存储在这个里面，但是后来在MySQL8.0,以后，表结构都存储在了sdi文件中，而sdi又融入到了ibd文件中。
综上所述，一张表对应一个ibd文件，好像说了半天的废话hhh
同时，我们可以通过配置把多张表放进一个ibd文件中（系统默认是一张表对应一个ibd文件）

说了这么多ibd文件，我们不妨去找找ibd文件的位置，我们可以在Data中找到对应的ibd文件：

而该文件是二进制编码，我们无法直接打开，可以通过命令ibd2sdi，从ibd文件中提取sdi的表结构数据

最后再简单介绍一下InnoDB的逻辑存储结构：

其实就是一层一层的包含关系：表空间包含多个段，一个段又包含多个区，一个区的固定大小为1M，最多能包含64页，因为一页的固定大小为16K，一页能包含多列，一列就是我们数据库中的一条数据，当然除了记录数据以外还有Trx_id事务id，Roll_pointer指针。

此处只是做简单介绍，后续在SQL优化的时候会具体讲解。

⚔MyIsAM

MyISAM是MySQL早期默认的存储引擎，说它的特点我们可以与现在的默认存储引擎InnoDB进行对比：

MyISAM 不支持事务，也不支持外键
MyISAM 不支持行锁，但是支持表锁
由于不保证它的事务，因此它的访问速度更快

而MyISAM涉及到的存储文件的类型有三种：MYD,MYI,SDI

其中sdi与上面的结构一样，用于存储表结构
MYD存放数据库中的数据
MYI存放数据库中的索引

🔗MEMORY

MEMORY 最大的特点就是，它的表数据是存储在内存中的，因此容易受宕机、断电……的影响，导致数据丢失，因此一般 MEMORY 存储引擎用于临时表和缓存它的特点由于和前面两种存储引擎类型不同，不做比较：

它由于是内存存放，因此访问速度很快
支持Hash索引，后续在讲索引的时候会详细讲到

而说到存储它的文件类型，就只有sdi一种类型，因为数据都存放在内存而不是磁盘中，因此不需更多的文件类型去存放数据，只需要一个sdi文件去记录表结构

💡总结

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

4️⃣ 存储引擎的选择

其实不难发现，学到现在，各种存储引擎都没有所谓的高低之分，没有说哪个最好，只能说根据应用系统的特点去选择合适的存储引擎，不一定全部选InnoDB，对于复杂的应用系统，我们可以选择多种存储引擎组合使用，以达到最好的效果

InnoDB：如果对于事务的完整性有比较高的要求，在并发条件下要求数据的一致性，选择InnoDB是不错的选择
MyISAM：如果数据以读和插入为主，更新和删除很少，对于事务的完整性、并发性要求不高，那么这个存储引擎更为合适。最开始我是没有想到有什么场景不需要完整性的，但是其实电商平台的浏览记录就很符合这个特点，插入和查询居多，而且对于事务的完整性要求不高，少那么一两条其实也问题不大
MEMORY：通常用于临时表和缓存，它的缺陷就是由于内存有限，因此表的大小也有限制，同时，它无法保证数据的安全性。