谈谈我对MySQL存储引擎的理解

旧城等待， 2022-06-13 13:08 304阅读 0赞

这篇文章并不是在说某个技术，更多的是一种感悟，一种学习技术的方法，相信读者如果认真阅读这篇文章，一定会比学习一个单纯的技术收获更大（就是这么自信^o^）。

到底什么是存储引擎，存储引擎这个概念在Oracle中是没有的。或者说Oracle中只有一种存储引擎。

在说具体的存储引擎之前，先说说我对MySQL存储引擎的理解。我对于MySQL引擎的理解是这样，MySQL是一个大的框架，负责整体的管理工作，比如sql解析，查询缓存等等。而对于一个数据库来讲，最重要的是对数据的操作。如何操作数据呢，比如数据的存储，是要存储到磁盘，还是要放到内存。再比如是否需要事务等等，那么可能在实现数据的增删改查的方法上就不同。**既然有不同，我就要抽象接口，我抽象出来一个所有的关于数据操作的接口，然后形成一套规范**，有了接口不行，需要有实现类，而且只要实现了我定义的接口的实现类都可以为我所用。当然，我也先默认实现一个或者多个，那么这个么个东西就是引擎。这样看MySQL的系统结构可以从总体上分为两层，上层是提供用户接口，以及一些管理功能，下层就是我们的存储引擎。

很快，刚有了这些想法，就佐证了。那么在猜想之前用了这么多的时间为什么没有找到类似的说法呢，我想大概没有往那个方面去搜索的原因吧。上面提到了数据库的结构，那么我马上就可以搜一下数据库的结构来佐证，还真是不负有心人，找到一个系统结构的图片。

![Center][]

总的来说，MySQL可以看成是二层架构，第一层我们通常叫做SQLLayer，在MySQL数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断，sql解析，执行计划优化，querycache的处理等等；第二层就是存储引擎层，我们通常叫做StorageEngineLayer，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。

还有一张图来佐证我说的接口的问题，这张图似乎更加说明了我的猜测是正确的

> ![Center 1][]  
>

下面再说说猜想，猜想不是凭空猜想，是根据一些专业知识给出的猜测。为什么要接口，这是对系统架构的理解。一般要设计一个可扩展的项目，首先就是要抽象接口。好了，就不多说了。下面是关于MySQL引擎的具体内容。

MySQL给我们提供了多种存储引擎，通过命令来查看数据库所支持引擎

SHOW ENGINES

![Center 2][]

下面来看一下各种引擎的具体介绍

####     ####

#### **InnoDB存储引擎** ####

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有：

1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合

2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎所不能匹敌的

3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上

4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键

5、InnoDB被用在众多需要高性能的大型数据库站点上

InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib\_logfile0和ib\_logfile1的5MB大小的日志文件

InnerDB的存储方式有两种，一种是共享表空间的存储方式另外一种是独享表空间的存储方式。共享表空间的存储方式就是上面说到的，在MySQL的数据目录下创建一个名为ibdata1的自动扩展数据文件。当然可以通过配置来配置多个ibdata文件。独占表空间的存储方式和myISAM的存储类似，没张表单独存储成为一个文件，扩展名为.ibd。

我们该如何来查看我们使用的是共享表空间的存储方式还是独占表空间的存储方式呢？

修改独占空表空间配置，以下几个参数必须在一起加入

innodb\_data\_home\_dir = "/usr/local/mysql/var/" 数据库文件所存放的目录

innodb\_log\_group\_home\_dir = "/usr/local/mysql/var" 日志存放目录

innodb\_data\_file\_path=ibdata1:10M:autoextend 设置配置一个可扩展大小的尺寸为10MB的单独文件（共享数据文件），名为ibdata1。没有给出文件的位置，所以默认的是在MySQL的数据目录内（如 /db/mysql/ibdata1）。

innodb\_file\_per\_table=1 是否使用共享以及独占表空间（1 为使用独占表空间，0 为使用共享表空间）

innodb\_file\_per\_table 通过这个参数来实现的转化，如果为OFF说明所使用的是共享表空间【默认情况下，所使用的表空间为共享表空间】

innodb\_file\_per\_table值来进行修改即可，但是对于之前使用过的共享表空间则不会影响，除非手动的去进行修改或者是

注意：

InnoDB不创建目录，所以在启动服务器之前请确认”所配置的路径目录”的确存在。

做数据的移植以及备份时，要注意数据文件的完整性.

#### **MyISAM存储引擎** ####

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有：

1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持

2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成

3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16

4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上

5、BLOB和TEXT列可以被索引

6、NULL被允许在索引的列中，这个值占每个键的0~1个字节

7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩

8、每个MyISAM类型的表都有一个AUTO\_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO\_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO\_INCREMENT列更新比InnoDB类型的AUTO\_INCREMENT更快

9、可以把数据文件和索引文件放在不同目录

10、每个字符列可以有不同的字符集

11、有VARCHAR的表可以固定或动态记录长度

12、VARCHAR和CHAR列可以多达64KB

使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）

#### **MEMORY存储引擎** ####

MEMORY存储引擎将表中的数据 **存储到内存**中，未查询和引用其他表数据提供快速访问。MEMORY主要特性有：

1、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度

2、MEMORY存储引擎执行HASH和BTREE缩影

3、可以在一个MEMORY表中有非唯一键值

4、MEMORY表使用一个固定的记录长度格式

5、MEMORY不支持BLOB或TEXT列

6、MEMORY支持AUTO\_INCREMENT列和对可包含NULL值的列的索引

7、MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）

8、MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享

9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）

#### 最后关于存储引擎的操作 ####

**查看表使用的存储引擎**

show table status from db_name where name='table_name';

**指定存储引擎**

create table tt
    (
        id bigint not null auto_increment,
        name varchar(15) not null,
        primary key (id)
    ) engine=INNODB;

**修改存储引擎**

alter table table_name engine=innodb;

[Center]: /images/20220613/10967f8a1f414cf796e6904877ac61fe.png
[Center 1]: /images/20220613/e0f5bf36ff8046099f53e1ceb4ea8365.png
[Center 2]: /images/20220613/012940f935b049aea2908e060beb10f2.png