「面试题」MySQL几种常用的存储引擎及其区别

说一说MySQL几种常用的存储引擎？

它们的区别是什么?

前言

存储引擎（Storage Engine）就是指表的类型以及表在计算机上的存储方式。

存储引擎的概念是MySQL的特点，提供了存储引擎接口，有兴趣的开发者可以编写自己的存储引擎。Oracle、SQL Server面试的时候不会问到存储引擎，所以这里不再叙述。

存储引擎查看

在mysql客户端中，使用以下命令可以查看MySQL支持的引擎：

mysql> show engines;

InnoDB存储引擎

在MySQL从3.23.34a开始包含InnoDB。有以下特点：

它遵循ACID模式设计，提供了具有提交、回滚和崩溃恢复能力的事务安全。

为了维护数据完整性，InnoDB还支持外键完整性约束。

支持自动增加列AUTO_INCREMENT（自动增长列）属性。

InnoDB 提供行级锁，大幅度提高了多用户并发操作的性能。

总结：InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。使用场景，如：银行（对事务的完整性要求比较高），售票（要求实现并发控制）。

MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。MyISAM拥有较高的插入、查询速度，但不支持事物。曾经是MySQL的默认存储引擎。

MyISAM的表存储成3个文件。文件的名字与表名相同。文件类型：文件存储表定义的扩展名为.frm、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）。

基于MyISAM存储引擎的表支持3种不同的存储格式。包括静态型、动态型和压缩型。其中，静态型是MyISAM的默认存储格式，它的字段是固定长度的；动态型包含变长字段，记录的长度不是固定的；压缩型需要用到myisampack工具，占用的磁盘空间较小。

总结：MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。

MEMORY存储引擎

MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。

数据存储在内存中，这样有利于快速处理，提高整个表的效率。这就要求，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。

总结：MEMORY用到的很少，因为它是把数据存到内存中，如果重启或者关机，所有数据都会消失。

Archive存储引擎

用于数据归档，压缩比非常高，存储空间大概是innodb的10-15分之一。所以它用来存储历史数据非常适合，如记录日志信息。archive存储引擎支持insert、replace和select操作，但是不支持update和delete，不支持事务，其设计目标只是提供高速的插入和压缩功能。

MERGE存储引擎

MERGE存储引擎，也被认识为MRG_MyISAM引擎，是一组MyISAM表的组合，这些MyISAM表结构必须完全相同。

举个例子：

我们现在要记录用户登录和操作的日志，假设这里使用MyISAM引擎建表。为了防止历史数据积累，使表过大。一般的做法是将数据分成很多表，每个名称与特定的时间相关。比如`t_log_201801`,`t_log_201802`。

假设`t_log_201801`表有以下数据：

`t_log_201801`表

假设`t_log_201802`表有以下数据：

`t_log_201802`表

如果我们要查Tom的登录情况，可以用join联立多张表。也可以用MERGE存储引擎将这些表合并成一张“虚拟表”。

CREATE TABLE IF NOT EXISTS `u_log` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`log` varchar(50) DEFAULT NULL,

`username` varchar(50) DEFAULT NULL,

INDEX(id)

) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 UNIOn=(t_log_201801,t_log_201802)

查询`u_log`后，得：

`u_log`表

总结：主要用于合并查询多张结构相同的表。

如何指定表使用某种存储引擎？

CREATE TABLE IF NOT EXISTS `t_test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`log` varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM;

后面跟ENGINE=MyISAM，即可指定使用MyISAM存储引擎，以此类推。

2.如何查看表使用了某种存储引擎?

show create table `t_test`; --`t_test`为表名

3.如何修改表使用的存储引擎？

alter table `t_test` engine=MyISAM; --`t_test`为表名

4.附上表格：

原创作品，本系列为面试题

关注小编，后续会推出更多面试系列作品