MySQL的存储引擎有哪些?以及它们的对比和使用场景
# 背景
mysql的存储引擎也是面试中的常客了:
面试官:你了解的mysql存储引擎都有哪些?他们有什么区别,以及使用场景是什么?
我:……
面试官:你能说说mysql存储引擎中的 InnoDB 和 MyISAM有什么区别吗?
我:……
之前已经总结过了InnoDB 和 MyISAM的区别和使用场景,但是面试中仅仅掌握这些好像还不够,面试官更倾向于你对存储引擎了解的更多。所以这篇文章再来总结一番mysql的存储引擎。
# 存储引擎简介
存储引擎其实就是对于数据库文件的一种存取机制,如何实现存储数据,如何为存储的数据建立索引以及如何更新,查询数据等技术实现的方法。
MySQL中的数据用各种不同的技术存储在文件(或内存)中,这些技术中的每一种技术都使用不同的存储机制,索引技巧,锁定水平并且最终提供广泛的不同功能和能力。在MySQL中将这些不同的技术及配套的相关功能称为存储引擎。
# MySQL 中查看存储引擎
MySQL提供了查询存储引擎的功能,执行以下sql即可查询到mysql中的存储引擎
SHOW
ENGINES;
2
mysql版本是8.0.12,下面是在Navicat中执行的结果:
其他命令:
# 查看mysql 默认的存储引擎
show variables like '% storage_engine';
# 查看具体某一个表所使用的存储引擎
show create table tablename;
#准确查看某个数据库中的某一表所使用的存储引擎
show table status from database where name = "tablename";
2
3
4
5
6
7
8
从上图中可以看出,mysql提供了9中存储引擎,默认使用的是InnoDB。但是我们常用的一般也就是4中存储引擎,分别是:InnoDB、MyISAM、MEMORY和ARCHIVE,所以我们重点分析这4中存储引擎。
# MySQL 各种存储引擎分析
# InnoDB存储引擎
InnoDB是目前MYSQL的默认存储引擎,是事务型数据库的首选引擎,是目前最重要、使用最广泛的存储引擎。主要特性有:
- 提供了事务,回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全。
- 支持自增长列(auto_increment)。
- 支持外键(foreign key)。
- 支持MVCC的行级锁。
- 索引使用的是B+Tree。
优点:提供了良好的事务处理、崩溃修复能力和并发控制。
缺点:读写效率较差,占用的数据空间相对较大。
场景:既有读写也挺频繁,请使用InnoDB。不知道如何选择时,也可以选择InnoDB,用以应对未来可能存在的复杂业务。
# MyISAM存储引擎
MyISAM 这种存储引擎不支持事务,不支持行级锁,只支持并发插入的表锁,主要用于高负载的select
。
优点:占用空间小,处理速度快。
缺点:是不支持事务的完整性和并发性。
场景:表中绝大多数都只是读查询(一般R/W > 100:1且update相对较少),可以考虑 MyISAM
# MEMORY存储引擎
MEMORY存储引擎将表中的数据存储到内存中。
每个基于MEMORY存储引擎的表实际对应一个磁盘文件,该文件的文件名和表名是相同的,类型为.frm。该文件只存储表的结构,而其数据文件,都是存储在内存中,这样有利于对数据的快速处理,提高整个表的处理能力。
MEMORY存储引擎默认使用哈希(HASH)索引,其速度比B+Tree要快,如果希望使用B树型,可在创建表的时候使用。
优点:数据快速访问和处理。
缺点:一旦发生异常,重启或关闭机器,数据都会丢失。
场景:适合用于查询的临时表。
# ARCHIVE存储引擎
Archive是归档的意思,在归档之后很多的高级功能就不再支持了,仅仅支持最基本的插入和查询两种功能。
在MySQL 5.5版以前,Archive是不支持索引,但是在MySQL 5.5以后的版本中就开始支持索引了。
Archive拥有很好的压缩机制,它使用zlib压缩库,拥有高效的插入速度。
优点:高压缩,快速插入。
缺点:不适合频繁查询。
场景:非常适合存储大量独立的、作为历史记录的数据。例如:非常适合作为日志表的存储引擎,但是前提是不经常对该表进行查询操作。
# 四种常用的存储引擎如何选择
功 能 | InnoDB | MYISAM | Memory | Archive |
---|---|---|---|---|
存储限制 | 64TB | 256TB | RAM | None |
支持事务 | Yes | No | No | No |
支持全文索引 | No | Yes | No | No |
支持数索引 | Yes | Yes | Yes | No |
支持哈希索引 | No | No | Yes | No |
支持数据缓存 | Yes | No | N/A | No |
支持外键 | Yes | No | No | No |
# 其他不常用的存储引擎分析(了解即可)
# CSV存储引擎
使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件(所以,它可以将CSV类型的文件当做表进行处理),这种文件是一种普通文本文件,每个数据行占用一个文本行。
该种类型的存储引擎不支持索引,即使用该种类型的表没有主键列;另外也不允许表中的字段为null。csv的编码转换需要格外注意。
场景:支持从数据库中拷入/拷出CSV文件。如果从电子表格软件输出一个CSV文件,将其存放在MySQL服务器的数据目录中,服务器就能够马上读取相关的CSV文件。同样,如果写数据库到一个CSV表,外部程序也可以立刻读取它。** 在实现某种类型的日志记录时,CSV表作为一种数据交换格式,特别有用**。
# BLACKHOLE存储引擎(黑洞引擎)
支持事务,而且支持MVCC的行级锁,写入这种引擎表中的任何数据都会消失,主要用于做日志记录或同步归档的中继存储。
这个存储引擎除非有特别目的,否则不适合使用。
像Unix系统下面的"/dev/null
"设备一样,不管我们写入任何信息,都是有去无回,那么BLACKHOLE存储引擎有什么用呢?
在数据迁移过程中,数据须要经过一个中转的MySQL服务器做一些相关的转换操作,然后再通过复制移植到新的服务器上面。如果没有足够的空间来支持这个中转服务器的动作,这时候就显示出BLACKHOLE的功效了,他不会记录下任何数据,但是会在binlog中记录下所有的Query。而这些Query最终都会被复制利用,并实施到最终的slave端。
所以,如果配置一主多从的话,多个从服务器会在主服务器上分别开启自己相对应的线程,执行binlogdump命令而且多个此类进程并不是共享的。为了避免因多个从服务器同时请求同样的事件而导致主机资源耗尽,可以单独建立一个伪的从服务器或者叫分发服务器。
MySQL的用户手册上面介绍了BLACKHOLE存储引擎其他几项用途:
- Query语法的验证;
- 测试二进制日志记录的性能开销,如通过比较允许二进制日志功能的BLACKHOLE与禁止二进制日志功能的BLACKHOLE的性能来实现
- 查找与存储引擎自身不相关的性能瓶颈,因为BLACKHOLE本质上是一个"no-op"的存储引擎
# PERFORMANCE_SCHEMA存储引擎
该引擎主要用于收集数据库服务器性能参数。
这种引擎提供以下功能:提供进程等待的详细信息,包括锁、互斥变量、文件信息;保存历史的事件汇总信息,为提供MySQL服务器性能做出详细的判断;对于新增和删除监控事件点都非常容易,并可以随意改变mysql服务器的监控周期,例如(CYCLE、MICROSECOND)。
MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。
场景:分析性能降低可能是由于哪些瓶颈。
# Federated存储引擎
该存储引擎可以不同的Mysql服务器联合起来,逻辑上组成一个完整的数据库。这种存储引擎非常适合数据库分布式应用。
Federated存储引擎可以使你在本地数据库中访问远程数据库中的数据,针对federated存储引擎表的查询会被发送到远程数据库的表上执行,本地是不存储任何数据的。缺点:
对本地虚拟表的结构修改,并不会修改远程表的结构
truncate 命令,会清除远程表数据
drop命令只会删除虚拟表,并不会删除远程表
不支持 alter table 命令
select count(*)
,select * from limit M, N
等语句执行效率非常低,数据量较大时存在很严重的问题,但是按主键或索引列查询,则很快如果虚拟虚拟表中字段未建立索引,而实体表中为此字段建立了索引,此种情况下,性能也相当差。但是当给虚拟表建立索引后,性能恢复正常。
类似
where name like "str%" limit 1
的查询,即使在 name 列上创建了索引,也会导致查询过慢,是因为federated引擎会将所有满足条件的记录读取到本地,再进行 limit 处理。
# MERGE存储引擎
MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表结构必须完全相同,尽管其使用不如其它引擎突出,但是在某些情况下非常有用。
场景:对于服务器日志这种信息,一般常用的存储策略是将数据分成很多表,每个名称与特定的时间端相关。例如:可以用12个相同的表来存储服务器日志数据,每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表,这意味着需要编写并更新多表查询,以反映这些表中的信息。与其编写这些可能出现错误的查询,不如将这些表合并起来使用一条查询,之后再删除Merge表,而不影响原来的数据,删除Merge表只是删除Merge表的定义,对内部的表没有任何影响。
# 面试题:Mysql 中 MyISAM 和 InnoDB 的区别有哪些?
- InnoDB 支持事务,MyISAM 不支持事务。
- 这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一。
- 对于 InnoDB 每一条 SQL 语句都默认封装成事务进行提交,这样就会影响速度,优化速度的方式是将多条 SQL 语句放在 begin 和 commit 之间,组成一个事务。
- InnoDB 支持外键,而 MyISAM 不支持。
- 对一个包含外键的 InnoDB 表转为 MYISAM 会失败。
- InnoDB 主键索引是聚集索引,非主键索引是非聚集索引;MyISAM 都是是非聚集索引。
- 聚簇索引的文件存放在主键索引的叶子节点上,因此 InnoDB 必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。
- MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
- InnoDB 不保存表的具体行数,执行
select count(*) from table
时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快。 - InnoDB 最小的锁粒度是行锁,MyISAM 最小的锁粒度是表锁。
- MyISAM 一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞,因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一;
- 存储结构不同。
- MyISAM在磁盘上存储成三个文件,数据和索引分离。
- InnoDB在磁盘上存储成两个文件,数据和索引存储在一个文件。
- 表的主键策略不同。
- MyISAM:允许没有任何索引和主键的表存在,索引都是保存行的地址。
- InnoDB:如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。
# 面试题:MySQL各种存储引擎的使用场景?
mysql默认提供9中存储引擎。
InnoDB
支持事务处理,支持外键,支持崩溃修复能力和并发控制。
- 如果需要对事务的完整性要求比较高(比如银行),要求实现并发控制(比如售票),那选择InnoDB有很大的优势。
- 如果需要频繁的更新、删除操作的数据库,也可以选择InnoDB,因为支持事务的提交(commit)和回滚(rollback)。
MyISAM
插入数据快,空间和内存使用比较低。如果数据表主要用来插入和查询记录,则MyISAM引擎能提供较高的处理效率。
- 表中绝大多数都只是读查询(一般R/W > 100:1且update相对较少),可以考虑 MyISAM。
- 如果对应用的完整性、并发性要求比较低,也可以使用。
MEMORY
所有的数据都在内存中,数据的处理速度快,但是安全性不高;它对表的大小有要求,不能建立太大的表,所以这类数据库只适用在相对较小的数据库表。
- 如果需要很快的读写速度,对数据的安全性要求较低,可以选择MEMOEY。
- 如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,
例如在MySQL中使用该引擎作为临时表,存放查询的中间结果。
ARCHIVE
- 非常适合存储大量独立的、作为历史记录的数据。Archive非常适合存储归档数据。例如:非常适合作为日志表的存储引擎,但是前提是不经常对该表进行查询操作。
- 如果只有INSERT和SELECT操作,对数据的安全性要求较低,可以选择Archive,Archive支持高并发的插入操作。
CSV
使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件(所以,它可以将CSV类型的文件当做表进行处理)。
- 在实现某种类型的日志记录时,CSV表作为一种数据交换格式。
BLACKHOLE存储引擎(黑洞引擎)
支持事务,而且支持MVCC的行级锁,写入这种引擎表中的任何数据都会消失,主要用于一主多从的日志记录或同步归档的中继存储。
PERFORMANCE_SCHEMA存储引擎
收集数据库服务器性能参数。分析性能降低可能是由于哪些瓶颈。
注意,同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理,可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表,可以选择MEMORY存储引擎。
# 参看
mysql存储引擎 (opens new window) (比较全面)
BLACKHOLE存储引擎 (opens new window) (介绍了BLACKHOLE存储引擎(黑洞引擎)的使用场景)