MySQL几种存储引擎介绍,以及适用场景
2019-02-27| 程成| 344| 0| MySQL

一、存储引擎


Isam

该引擎在包括MySQL 5.1及其以上版本的数据库中不再支持。

该引擎在读取数据方面速度很快,而且不占用大量的内存和存储资源;但是 Isam 不支持事务处理、不支持外键、不能够容错、也不支持索引。



Berkeley

该引擎在包括MySQL 5.1及其以上版本的数据库中不再支持。

该存储引擎支持COMMIT和ROLLBACK等事务特性。



CSV

使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件(所以,它可以将CSV类型的文件当做表进行处理),这种文件是一种普通文本文件,每个数据行占用一个文本行。该种类型的存储引擎不支持索引,即使用该种类型的表没有主键列;另外也不允许表中的字段为null。csv的编码转换需要格外注意。


场景:

这种引擎支持从数据库中拷入/拷出CSV文件。如果从电子表格软件输出一个CSV文件,将其存放在MySQL服务器的数据目录中,服务器就能够马上读取相关的CSV文件。同样,如果写数据库到一个CSV表,外部程序也可以立刻读取它。在实现某种类型的日志记录时,CSV表作为一种数据交换格式,特别有用。



HEAP(也称为MEMORY):

该存储引擎通过在内存中创建临时表来存储数据。每个基于该存储引擎的表实际对应一个磁盘文件,该文件的文件名和表名是相同的,类型为.frm。该磁盘文件只存储表的结构,而其数据存储在内存中,所以使用该种引擎的表拥有极高的插入、更新和查询效率。这种存储引擎默认使用哈希(HASH)索引,其速度比使用B-+Tree型要快,但也可以使用B树型索引。由于这种存储引擎所存储的数据保存在内存中,所以其保存的数据具有不稳定性,比如如果mysqld进程发生异常、重启或计算机关机等等都会造成这些数据的消失,所以这种存储引擎中的表的生命周期很短,一般只使用一次。


场景:如果需要该数据库中一个用于查询的临时表。



ARCHIVE:

区别于InnoDB和MyISAM这两种引擎,ARCHIVE提供了压缩功能,拥有高效的插入速度,但是这种引擎不支持索引,所以查询性能较差一些。 archive存储引擎支持insert、replace和select操作,但是不支持update和delete。


场景1:存储引擎基本上用于数据归档;它的压缩比非常的高,存储空间大概是innodb的10-15分之一所以它用来存储历史数据非常的适合,由于它不支持索引同时也不能缓存索引和数据,所以它不适合作为并发访问表的存储引擎。


场景2:由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎,但是前提是不经常对该表进行查询操作。



PERFORMANCE_SCHEMA:

该引擎主要用于收集数据库服务器性能参数。这种引擎提供以下功能:提供进程等待的详细信息,包括锁、互斥变量、文件信息;保存历史的事件汇总信息,为提供MySQL服务器性能做出详细的判断;对于新增和删除监控事件点都非常容易,并可以随意改变mysql服务器的监控周期,例如(CYCLE、MICROSECOND)。 MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。


场景: DBA能够较明细得了解性能降低可能是由于哪些瓶颈。



Merge

Merge允许将一组使用MyISAM存储引擎的并且表结构相同(即每张表的字段顺序、字段名称、字段类型、索引定义的顺序及其定义的方式必须相同)的数据表合并为一个表,方便了数据的查询。


场景:MySQL中没有物化视图,视图的效率极低,故数据仓库中数据量较大的每天、每周或者每个月都创建一个单一的表的历史数据的集合可以通过Merge存储引擎合并为一张表。



Federated

该存储引擎可以不同的Mysql服务器联合起来,逻辑上组成一个完整的数据库。

这种存储引擎非常适合数据库分布式应用。

Federated存储引擎可以使你在本地数据库中访问远程数据库中的数据,针对federated存储引擎表的查询会被发送到远程数据库的表上执行,本地是不存储任何数据的。


场景: dblink。


image.png


缺点:

  1. 对本地虚拟表的结构修改,并不会修改远程表的结构

  2. truncate 命令,会清除远程表数据

  3. drop命令只会删除虚拟表,并不会删除远程表

  4. 不支持 alter table 命令


select count(*), select * from limit M, N 等语句执行效率非常低,数据量较大时存在很严重的问题,但是按主键或索引列查询,则很快,如以下查询就非常慢(假设 id 为主索引)

select id from db.tablea where id >100 limit 10 ;


而以下查询就很快:

select id from db.tablea where id >100 and id<150


如果虚拟虚拟表中字段未建立索引,而实体表中为此字段建立了索引,此种情况下,性能也相当差。但是当给虚拟表建立索引后,性能恢复正常。


类似 where name like "str%" limit 1 的查询,即使在 name 列上创建了索引,也会导致查询过慢,是因为federated引擎会将所有满足条件的记录读取到本,再进行 limit 处理。



Cluster/NDB

该存储引擎用于多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大、安全和性能要求高的场景。

CAP理论。CAP理论(Brewer’s CAP Theorem) ,是说Consistency(一致性), Availability(可用性), Partition tolerance(分布) 三部分在系统实现只可同时满足二点,没法三者兼顾。如果对"一致性"要求高,且必需要做到"分区",那么就要牺牲可用性;而对大型网站,可用性与分区容忍性优先级要高于数据一致性,一般会尽量朝着 A、P 的方向设计,然后通过其它手段保证对于一致性的商务需求。



InnoDB

适用于更新密集型,支持事务,自动灾难恢复,行锁,外键

该存储引擎为MySQL表提供了ACID事务支持、系统崩溃修复能力和多版本并发控制(即MVCC Multi-Version Concurrency Control)的行锁

支持自增长列(auto_increment),自增长列的值不能为空,如果在使用的时候为空则自动从现有值开始增值,如果有但是比现在的还大,则直接保存这个值

支持外键(foreign key) ,外键所在的表称为子表而所依赖的表称为父表。

该引擎在5.5后的MySQL数据库中为默认存储引擎。



MyISAM

不支持事务,适用于选择密集型,插入密集型, mysql 默认的引擎


该引擎基于ISAM,除了提供ISAM所没有的索引和字段管理等大量功能


MyISAM还使用一种表锁机制来优化多个并发读写操作,但需要经常运行OPTIMIZE TABLE命令,来恢复被更新机制所浪费的空间,否则碎片也会随之增加,最终影响数据访问性能。


还有一些有用的扩展,例如用来修复数据库文件的MyISAM Chk工具和用来恢复浪费空间的 MyISAM Pack工具


MyISAM强调了快速读取操作,主要用于高负载的select,这可能也是MySQL深受Web开发的主要原因:在Web开发中进行的大量数据操作都是读,所以大多数虚拟主机提供商和Internet平台提供商(Internet Presence Provider,IPP)只允许使用MyISAM格式。


MyISAM类型的表支持三种不同的存储结构:静态型、动态型、压缩型。


静态表(默认的存储格式) 表中的字段都是非变长字段,这样每个记录都是固定长度的,这样存储

优点:非常迅速,易缓存,出现故障容易恢复

缺点:占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格,但是在访问的时候并不会得到这些空格,这些空格在返回给应用之前已经去掉。同时需要注意:在某些情况下可能需要返回字段后的空格,而使用这种格式时后面到空格会被自动处理掉。


动态表 包含变长字段,记录非固定长度的

优点:占用空间较少

缺点:频繁更新删除记录会产生碎片,需要定期执行OPTIMIZE TABLE或myisamchk -r改善性能,并且出现故障的时候恢复相对比较困难


压缩表 由myisamchk工具创建,占据非常小空间,因为每条记录都是被单独压缩,所以只有非常小的访问开支



二、第三方存储引擎


Infobright

mysql的列存储引擎,适用于数据分析和数据仓库设计。


优点:

1.查询性能高        --比普通Mysql 数据库引擎(MyISAM、InnoDB)  快5-60倍.

2.存储数据量大   --能存储的数据量特别大.

3.高压缩比             --与普通数据库存放的数据文件相比, 可以达到55:1

4.不需要建立索引  --省去了大量建立索引的时间.(对于我们非常有优势)


缺点:

1.不能高并发.最多10个并发

2.Infobright分两个版本:社区版(ICE,免费)、企业版(IEE,收费),社区版在添加数据时,只支持loaddata , 而不支持.insert,update ,delete . 企业版,则全部支持.



TokuDB

支持数据压缩,支持高速写入的一个引擎,但是不适合update多的场景。



XtraDBPBXT

是Percona公司基于InnoDB的一个改进版本



原文地址:https://www.jianshu.com/p/faae81f3d472




×
作者:程成
QQ:492245711