MySQL 存储引擎概述

MySQL 数据库提供了独有的插件式存储引擎,常见存储引擎有 InnoDB、MyISAM、NDB、Memory、Archive、Federated、Maria 等等,并且不同的存储引擎有着完全不同的功能,建表的时候可以指定存储引擎的类型,若不指定存储引擎类型,MySQL8.0 默认的存储引擎就是 InnoDB。

1. InnoDB 存储引擎

InnoDB 存储引擎最大的特点是支持事务,它主要应用于事务(OLTP)相关的数据存储。它的功能特点有行锁、支持外键,并且一般操作查询不会产生锁。InnoDB 存储引擎从 MySLQ 5.5.5 之后的版本都是其默认的存储引擎。
InnoDB 有多版本并发控制,并且有 4 种隔离级别,这种隔离级别分别为 顺序读(SERIALIZABLE)、可重复读(REPEATABLE READ)、读已提交(READ COMMITTED)、读未提交(READ UNCOMMITTED)

下面通过一个建表 sql 来新建一个 InnoDB 存储引擎类型的数据表:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT ''`age` int(10) unsigned NOT NULL,
  `id_number` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

执行结果如下图 :

图片描述

Tips:如上图所示, 其中 ENGINE=InnoDB 表示建表指定存储引擎类型为 InnoDB。

2. MyISAM 存储引擎

MyISAM 存储引擎是 MySQL 5.5.8 版本以前默认使用的存储引擎,其不支持事务,MyISAM 存储引擎表由 MYD 和 MYI 组成,其中 MYD 用来存放数据的文件,MYI 用来存放索引的文件。

下面通过一个建表 sql 来新建一个 MyISAM 存储引擎类型的数据表:

CREATE TABLE `test_my` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT ''`age` int(10) unsigned NOT NULL,
  `id_number` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

执行结果如下图 :

图片描述

Tips:注意:对于 MyISAM 存储引擎, MySQL 数据库只缓存索引文件,数据库的数据文件由操作系统完成。

3. NDB 存储引擎

NDB 存储引擎是一个集群存储引擎,其特点是数据全都存放在内存中(可以将非索引的数据放在磁盘上),通过主键查找的速度非常快, NDB 存储引擎的表连接操作(JOIN) 是由 MySQL Server 层完成的,其执行速度比较慢。因 NDB 是一个集群存储引擎,这里不方便做演示了。

4. MEMORY 存储引擎

Memory 存储引擎将表中的数据存放在内存中,如果数据库发生 崩溃(crash)或者重启,则表中的数据会丢失。

下面通过一个建表 sql 来新建一个 MyISAM 存储引擎类型的数据表:

CREATE TABLE `test_memory` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT ''`age` int(10) unsigned NOT NULL,
  `id_number` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

执行结果如下图 :

图片描述

5. ARCHIVE 存储引擎

ARCHIVE 存储引擎只支持 INSERT插入和 SELECT 查询 操作,这就意味着插入进去的数据不能更改,所以 ARCHIVE 存储引擎非常适合存储归档数据。
下面通过一个建表 sql 来新建一个 ARCHIVE 存储引擎类型的数据表:

CREATE TABLE `test_archive` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT ''`age` int(10) unsigned NOT NULL,
  `id_number` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=ARCHIVE AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

执行结果如下图 :

图片描述

Tips:注意:ARCHIVE 存储引擎使用行锁来实现高并发的写入操作,但其本身不是 crash safe 的存储引擎。

6. FEDERATED 存储引擎

FEDERATED 存储引擎不存放数据,它指向远程 MySQL 数据库。本地只存放表的机构信息,数据都通过远程连接存放到远程服务器,增删改查操作都是通过建立的连接来访问远程数据库进行操作,把结果返回给本地。FEDERATED 存储引擎默认是不开启的,可以先查看所有存储引擎开启状态:

SHOW ENGINES; 

执行结果如下 :

图片描述

Tips:若 FEDERATED 存储引擎没有启用,可以在 MySQL 配置文件中 [mysqld] 下面增加一行 federated,然后重启 MySQL 即可开启。

下面通过一个建表 sql 来新建一个 FEDERATED 存储引擎类型的数据表:

CREATE TABLE `test_fed` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT ''`age` int(10) unsigned NOT NULL,
  `id_number` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=FEDERATED CONNECTION='mysql://root:123456@127.0.0.1:3306/sakila/actor';

执行结果如下图 :

图片描述

7. MARIA 存储引擎

MARIA 存储引擎是一种比较新的存储引擎,是为了取代原有的 MyISAM 存储引擎,可以理解为 MyISAM 存储引擎的后续版本。MARIA 存储引擎的特点是支持数据和文件索引,应用了行锁设计,提供了多版本并发控制(MVCC),支持事务。

8. CSV 存储引擎

逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.CSV文件。这是一种普通文本文件,每个数据行占用一个文本行。CSV存储引擎不支持索引。

9. 小结

本小节介绍了 8 种存储引擎, MySQL 还提供了很多其他的存储引擎,例如 Merge、Sphinx、Infobright,它们分别有各自合适的应用场景,初学者多学习 InnoDB 存储引擎, InnoDB 存储引擎也是在面试中问的最频繁的存储引擎了。本小节内容只是简单地介绍了这几种存储引擎,了解一下存储引擎基础知识,更深入的关于存储引擎的知识就需要阅读底层源码了。