【原】【第0天】SQL快速入门-了解MySQL存储引擎（SQL 小虚竹)

小虚竹 2023-02-16 发布于福建

展开全文

回城传送–》《32天SQL筑基》

文章目录

零、前言

今天是学习 SQL 打卡的第 0 天，每天我会提供一篇文章供群成员阅读（ 不需要订阅付钱 ）。

希望大家先自己思考，如果实在没有想法，再看下面的解题思路，自己再实现一遍。在小虚竹JAVA社区中对应的【打卡贴】打卡，今天的任务就算完成了，养成每天学习打卡的好习惯。

虚竹哥会组织大家一起学习同一篇文章，所以有什么问题都可以在群里问，群里的小伙伴可以迅速地帮到你，一个人可以走得很快，一群人可以走得很远，有一起学习交流的战友，是多么幸运的事情。

我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 SQL 已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。

今天的学习内容是：SQL快速入门-了解MySQL存储引擎

一、什么是数据库引擎

数据库引擎是数据库用于存储、处理和保护数据的核心服务。

不同的数据库引擎有其各自的特点，如存储机制、索引技巧、主键的处理、锁的粒度等特点便随着引擎的不同而变化。

因此，针对自己项目特点选择合适的数据库引擎可以改善服务器端存储性能。

二、MYSQL中有哪些数据库引擎

登录数据库后，执行查询MYSQL引擎命令：

show ENGINES;

在这里插入图片描述
从图上可知，博主用的MYSQL8.0,MYSQL默认的数据库引擎是InnoDB。
常见的数据库引擎有：InnoDB、MyISAM、Memoey等。下面介绍下这几个数据库引擎。

2.1、MyISAM

没有提供对数据库事务的支持，不支持细粒度的锁（行锁）及外键，当表Insert与update时需要锁定整个表，因此效率会低一些，在高并发时可能会遇到瓶颈，但MyIsam引擎独立与操作系统，可以在windows及linux上使用。

适用于：不需要事务支持、外键功能、及需要对整个表加锁的情形。
小结：

不支持外键、事物、行锁、数据缓存。insert与update时锁表，效率相对InnoDB较低。
如果数据文件损坏，难以恢复数据。
在索引存储上，索引文件与数据文件分离。

2.2、Memoey

MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。

但是一旦服务关闭，表中的数据就会丢失掉。 HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。

适用于：那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地堆中间结果进行分析并得到最终的统计结果。

小结：

存放在内存中，本身就是缓存，访问速度快，适用于内容变化不频繁的表，或者作为统计的中间结果表。
不支持TEXT和BLOB数据类型，只支持固定长度的字符串类型。例如，在MEMORY存储引擎中，会将VARCHAR类型自动转化成CHAR类型。
锁级别为表锁，在高并发场景下会成为瓶颈。
通常会被作为临时表使用，存储查询数据时产生中间结果。
数据存储在内存中，重启服务器后数据会丢失。如果是需要持久化的数据，不适合存储在MEMORY存储引擎的数据表中。

2.3、InnoDB

是一种事务型存储引擎，提供了对ACID事务的支持，以及四种事务隔离级别。具备行级锁定（适合高并发，不是锁住整张表）以及外键支持（只有InnoDB支持外键）。

适用于：处理大容量数据，MySQL在运行时InnoDB会在内存中建立缓冲池，用于缓存数据及索引。

小结：

支持外键、事务、行锁、数据缓存，可以处理高并发、大容量数据。
能够通过二进制日志恢复数据。
在索引存储上，索引和数据存储在同一个文件中，默认按照B+Tree组织索引的结构。同时，主键索引的叶子节点存储完整的数据记录，非主键索引的叶子节点存储主键的值。

三、MyISAM和InnoDB的区别

3.1、MYSQL版本支持默认引擎不同

MyISAM

MySQL 5.5以前的版本默认的存储引擎是MyISAM

InnoDB

MySQL 5.5以后的版本（包含5.5）开始将InnoDB作为默认的存储引擎

3.2、数据的存储结构不同

MyISAM

每个MyISAM在磁盘上存储成三个文件，它们以表的名字开头来命名。.frm文件存储表定义。.MYD(MYD)存储数据文件。.MYI(MYIndex)存储索引文件。
在这里插入图片描述

InnoDB

InnoDB在磁盘上保存为两个文件。.frm文件存储为表结构文件，.ibd文件存储的是数据和索引文件。

在这里插入图片描述

3.3、存储空间的消耗不同

MyISAM

MyISAM支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表，极大的减少磁盘的空间占用。
在这里插入图片描述

InnoDB

InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。InnoDB所在的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

在这里插入图片描述

扩展：什么是MVCC-多版本并发控制

MVCC是现代数据库（包括 MySQL 、Oracle 、 PostgreSQL 等）引擎实现中常用的处理读写冲突的手段，目的在于提高数据库高并发场景下的吞吐性能。

客观上，我们认为他就是乐观锁的一整实现方式，就是每行都有版本号，保存时根据版本号决定是否成功。
innodb会为每一行添加两个字段，分别表示该行创建的版本和删除的版本，填入的是事务的版本号，这个版本号随着事务的创建不断递增。

具体各种数据库操作的实现：

select：满足以下两个条件innodb会返回该行数据：
- （1）该行的创建版本号小于等于当前版本号，用于保证在select操作之前所有的操作已经执行落地。
- （2）该行的删除版本号大于当前版本或者为空。删除版本号大于当前版本意味着有一个并发事务将该行删除了。
insert：将新插入的行的创建版本号设置为当前系统的版本号。
delete：将要删除的行的删除版本号设置为当前系统的版本号。
update：不执行原地update，而是转换成insert + delete。将旧行的删除版本号设置为当前版本号，并将新行insert同时设置创建版本号为当前版本号。
写操作（insert、delete和update）执行时，需要将系统版本号递增。
由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除