MySQL 存储引擎与分类｜学习笔记

开发者学堂课程【MySQL 实操课程：MySQL 存储引擎与分类】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/717/detail/12807

MySQL 存储引擎与分类

 

目录

一、介绍 MySQL 存储引擎与权限管理

二、介绍存储引擎

三、MySQL 的两种存储引擎

四、MyISAM 存储引擎

五、InnoDB 存储引擎

六、MyISAM 与 InnoDB 的区别

 

一、介绍 MySQL 存储引擎与权限管理

分为三个小结

1.MySQL 存储引擎与分类

本次课程会去讲解 MySQL 的两种存储引擎：MyISAM、InnoDB。会分享两种存储引擎的特点以及它们所应用的场景，同时还包括这两种存储引擎在物理存储目录上的一个差异。

2.MySQL 创建不同存储引擎库、表

会讲解怎么样去创建不同存储引擎的表，因为存储引擎是针对表而言的。可以针对同一个数据库下面不同的表去创建不同的存储引擎。

3.MySQL 权限体系、账户的管理

会讲解怎么样去创建MySQL的账号，并且对这个账号进行对应的权限的管理，给它授予指定库、表的权限。

 

二、介绍存储引擎

1.存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法；

2.在关系数据库中数据的存储是以表的形式存储，所以存储引擎也可以称为表类型（即存储和操作此表的类型）；

所以说存储引擎它主要是针对表而言的。之前在讲到存储的时候，一定是对应的某个表的存储引擎。之前讲过怎么样查看表的数据结构：通过 show databasses 可以看到表的详细的信息，那么在这里就可以看到指定的表的对应的存储引擎。

3.类型有 MyISAM,InnoDB,MERGE,MEMORY(HEAP) 等；

MySQL 是提供了一种插件式的存储引擎，MySQL 特点是可以在不同的表给它设置为不同的存储引擎。具体怎么样去设置或者设置成什么类型的存储引擎要根据实际应用场合做决定。

4.当讲解存储引擎的时候怎么去看当前 MySQL 支持什么类型的存储引擎

打开阿里云控制台

输入 SHOW engines; 通过 SHOW engines 的指令可以看见 MySQL 的对于存储引擎支持的情况。

可以看见有支持很多不同类型的存储引擎，YES 代表是支持，NO 代表不支持。InnoDB 是作为默认的存储引擎也就是说阿里云的 RDS 的 for MySQL 实例，它默认的存储就是 InnoDB。

 

三、MySQL 的两种存储引擎

InnoDB 与 Myisam 的差异（物理存储的角度）

1.InnoDB

（1）数据和索引合并存储一个文件（.frm，.ibd）

（2）每个表单独存储/ibdata存储

2.Myisam

（1）数据和索引单独存储与两个文件（.frm，.MYI,.MYD）

（2）frm 是表的结构描述文件，.MYI 是表对应的索引文件，.MYD 是数据文件

 

四、MyISAM存储引擎

1.Myisam 存储引擎的特点

（1）MySQL 是5.1版本之前默认的存储引擎

①讲解 MySQL 是5.1版本之前默认的存储引擎

5.1版本之前默认的存储引擎是 Myisam，也就是说在创建表的时候它会按照默认的存储引擎去创建。

②指定为 InnoDB 的存储引擎有两种方式

如果想给它指定为 InnoDB 的存储引擎有两种方式：

可以直接在建表的时候去指定安检的参数是属于哪一种存储引擎。

通过 MySQL 的备注文件直接去进行对应参数的修改，在 MySQL 重启之后，它就会按照新的配置生效。

（2）表级锁定

①讲解表级锁定

表级锁定的特点是它会减少降低数据库读写并发。所以说它对于性能方面（特别是写入的性能方面）相对来说会低一些。

那么对于5.1之后的版本基本上默认采用 MySQL 存储引擎，在 MySQL 8.0之后的版本是完全已经弃用了 Myisam 存储引擎，这也是为什么阿里云 RDS 对于这个存储引擎，它只支持 InnoDB 存储引擎的原因。

②为什么 RDS MySQL 不支持 MyISAM 引擎？
· MyISAM 对数据完整性的保护存在缺陷，且这些缺陷会导效数据库数据的损坏甚至丢失。另外，这整缺陷很多是设计问题，无法在不被坏兼容性的前提下修复。
·MyISAM 在出现数据损害情况下。很多都需要手工修复，无法适用于产品服务的方式。
·对于 RDS 的存储而言， MyISAM 对于 I/O 的操作不是最优化的方案，导致MyISAM 的性能相对于InnoD0 的优势不大。

· MyISAM  向 InnoDB 的迁移代价低，大多数应用仅需要改动建表的代码即可完成迁移。
· MylSAM 的发展在向 InnoDB 转移，在最新的5.7版本中 MySQL 可以完全不是 MylSAM ，系统的数控也被转移到了 InnoDB。

③MylSAM 存储引擎对于全文索引的支持

MylSAM 存储引擎对于全文索引的支持最早是支持的，但是它不支持事物也不支持外界，所以 MylSAM 主要应用的场景针对的是大量读操作的表。

（3）适用于大量读操作的表

MylSAM 执行 select 操作的时候会有一些优势，特别是在进行统计计算的时候。

（4）文件存储结构

创建了一个表，它生成了物理文件的结构的情况。也可以手动设置默认存储引擎。

2.手动设置默认存储引擎

因为这个 RDS for MySQL 数据库已经弃用了 MylSAM，所以可以通过自己安装的原生的 MySQL 做对比来讲解：

找到原来的云服务主机，找到公网的 IP（先远程登录）

远程登录：输入 ssh root@47.112.159.55

（输入完成之后会自动生成其他代码）

输入 ps -eflgrep mysqld, 可以看见 MySQL 已经成功启动了

 

连接 MySQL 服务（不是连接 RDS for MySQL，而是连接自己基于阿里云的 EDS 云组织下本地安装的 MySQL 实例，进入 MySQL 的客户端，连接 MySQL 服务）。

输入 /usr/local/mysql/bin/mysql -uroot -p

输入 show engines;

观察对存储引擎的支持

 

查看 MySQL 配置文件的位置

输入 vi /etc/my.cnf

存储目录在 data/mysql

 

观察 data/mysql 目录

输入 cd data/mysql

可以看见图中有 ibdata1、ib_logfile0、ib_logfile1，它们就是 InnoDB 的存储引擎，是 InnoDB 默认形式，把它所有的数据以及索引放在一起。

 

3.创建 MySQL 的存储引擎

创建 MySQL 的存储引擎有两种方式

第一种：在创建表的时候指定存储引擎；

第二种：创建表之后改变存储引擎。

4.创建 MylSAM 存储引擎

创建测试的表

为了方便同时打开两个窗口

在第二个窗口操作：

查看目录：输入 cd/data/mysql

 

在第一个窗口操作：

登录 MySQL：输入  /usr/local/mysql/bin/mysql -uroot -p

创建数据库：输入 create database aliyun; 再输入 use aliyun;

 

创建表：输入 create table emp(ename varchar(20),deptno int(3));

查看表：输入 show create table emp; 可以再图中很明显的看出是 InnoDB 存储引擎。

 

在第二个窗口操作：

查看创建 InnoDB 之后，有没有产生新的文件：输入11

可以看见是没有的，因为它现在本身是一个 InnoDB 的存储引擎，它会把数据、索引全部放在 ibdata1 里面。ib_logfile0、ib_logfile1 是 ibdata 的日志文件。

 

5.如何调整存储引擎

（1）第一种方法

在第一个窗口操作：

输入 alter table emp engine=MyISAM; 可以看见存储引擎已经变成了 MyISAM。

在第二个窗口操作：

输入11，可以看见 MyISAM 的存储引擎已经创建完成。

 

插入数据

在第一个窗口操作：

输入 insert into rmp values('zhansan',1);

再输入 select * from emp; 在图中可以看见 ('zhansan',1) 已经插入成功。

 

在第二个窗口操作：

进入 aliyun 文件夹

输入 cd aliyun/; 在图中可以看见它包含的几个文件：第一个文件是 db.opt，它是在创建 InnoDB 的时候产生的，如果后面有改变存储引擎它会自动创建对应的文件夹：emp.frm (表的结构描述文件)、emp.MYD（数据文件）、emp.MYI（索引文件）。

（2）第二种

可以创建一个新的数据库（创建一个 MyISAM 的存储引擎），或者在当前设置。

在第一个窗口操作：

输入 show engines; 可以看见当前默认的存储引擎就是 InnoDB。

可以通过设置将他更改：输入 set default_storage_engine=MyISM;

再输入 show engines；可以看见当前默认的存储引擎已经更改为 InnoDB。

在此基础上再次创建：输入 create database aliyun;

输入 use aliyun;

在第二个窗口操作：

输入 cd aliyun; 可以看见它自动创建 db.opt 文件夹。

在第一个窗口操作：

创建一个表：输入 creat table emp(ename varchar(20),deptno int(3)); 这里没有指定存储引擎，当时在之前已经把默认存储引擎设置为 MyISAM。

输入 show create table emp; 这里的存储引擎就是 MyISAM。

在第二个窗口操作：

输入11，可以看多个文件夹，文件夹是在创建 MyISAM emp 的表的时候所产生的文件。

 

6.介绍 MySQL 文件类型

每个数据库在数据库美丽中有三个文件：一个样式（描述文件）、一个数据文件和一个索引文件。每个文件的基本名是表名，文件名扩展名代表文件类型。扩展名如下表。数据和索引文件的扩展名指出表使用老式 IASM 索引或新式 MyISAM 索引。

示例：创建 dept 表

输入 create table dept (id int(3),deptname varchar(10);

在输入 show create table dept;

输入11；在图中可以看见就自动放在 dept 文件夹中。

 

 

五、InnoDB 存储引擎

1.InnoDB 存储引擎的特点

（1）支持外键，事务处理

（2）行锁定

简单来说，就是在进行事物写入的时候，它会只锁定指定的数据行，不会像MyISAM 存储引擎直接将表锁定。所以InnoDB行锁定对数据锁定的效率相对来说较高。

（3）具有提交，回滚和崩溃恢复能力的事务安全

当在事务的过程中发生了异常，InnoDB 就会自动帮忙回滚开始之前的状态。

（4）适用于大量的写读作的表

主要是适用于大量的写操作。因为对于数据写入一般都会有事务的要求。不能说是不支持事务（特别是 ONTB），现在很多应用系统都是基于 ONTB 为核心。

2.InnoDB 与 MyISAM 相比较

对于全文索引最开始是以 MyISAM  为存储引擎，新版本的 MySQL 已经开始全面支持全文索引，所以它与 MyISAM 相比较，MyISAM 原有的优势 InnoDB 已经开始逐渐必备了，这就是后面逐渐将 MyISAM 替代的原因。

3.InnoDB 数据存储

InnoDB 会有一个数据存储的情况：物理文件会把它存储到 ibdata1

里面。

针对 InnoDB 存储引擎，它有一个独享表空间的概念，在创建 aliyun 下面 emp 表的时候是没有单独申请一个文件的（可以设置）。

通过进入 MySQL 客户端查看：

输入 show variables like '&innodb_file_per_table&'; 它默认已经开始了，它对于每一个表都会创建一个独立的库。当创建存储引擎的表，实际上就会独立出来，这是一个配置的选项。

在这里可以把 MySQL 默认的存储引擎设置回来：

创建 dept 表：输入 create table dept(id int(3),deptname varchar(10));

在输入 show create table dept;

输入11；可以看见已经创建单独的 dept 文件

 

查看默认的配置：输入 show variables like '&innodb_file_per_table&';

这里的意思是每一个表创建一个单独的文件。这样的好处是在多个文件夹地方方便迁移而且不会导致 idbata 文件变得越来越大。

 

4.查看阿里云 RDS for MySQL

输入 show variables like '%innodb_file_per_tables%';

这个是公司比较推荐的方式。

 

六、MyISAM 与 InnoDB 的区别

1.MyISAM- 适用于大量的读操作的表，因为是表锁定，读写是串行方式，索引文件和数据文件分开

2.InnoDB- 适用于大量的写读作的表，因为是行数定，读写是并行方式，索引和数据存放在同一个表空间，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM 表不同，比如在 MyISAM 表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。