MySQL的存储引擎的使用场景分析

适用场景

Posted by chensong on 2019-07-29 22::48::39

前言

MySQL在我们日常开发中经常使用存储数据使用, 但是出现问题优化和存储引擎的选择适合我们实际业务场景,就要对MySQL的存储索引的原理要有所了解。

正文

一, MySQL的存储引擎介绍

① InnoDB存储引擎

InnoDB存储引擎支持事务,主要面向在线事务处理(OLTP)方面的应用。其特点是行锁设计,支持外健,并支持类类于Oracle的非锁定读,即默认情况下读取存储不会产生锁。MySQL在Windows版本小的InnoDB是默认的存储引擎,同时InnoDB默认地包含在所有的MySQL二进制发布版本。

InnoDB存储引擎将数据放到一个逻辑的表空间中,这个表空间就像黑盒一样由InnoDB自身进行管理。从MySQL4.1版本开始,它可以将每个InnoDB存储引擎的表单独存放到一个独立的ibd文件中。与Oracle类似,InnoDB存储引擎同样可以使用设备(row disk)来建立其表空间。

InnoDb通过使用多版本并发控制(MVCC)来获取高并发性,并且实现了SQL标准的4中隔离级别,默认为REPEATABLE级别。同时使用一种被称为next-key locking的策略了避免幻读(phantom)现象的产生。除此之外,InnoDB存储引擎还提供了插入缓存(insert buffer),二次写(double write),自适应哈希索引(adaptive hash index),预读取(read ahead)等高性能和高可用的功能。

对于表中数据的存储,InnoDB存储引擎采用了聚集(clustered)的方式,这种方法类似于Oracle的索引聚集表(index organized table, IOT)。 每张表的存储都按主键的顺序存放,如果没有显式地在表定义时指定主键,InnoDB存储引擎为每一行生成一个6字节的ROWID,并以此作为主键。

② MyISAM存储引擎

MyISAM存储引擎s MySQL官方提供的存储引擎。其特点是不支持事务,表锁和全文索引,对于一些OLAP(Online Analytical Processing, 在线分析处理)操作速度快。除Windows版本外,是所有MySQL版本默认的存储引擎。

MySQL存储引擎表由MYD和MYI组成,MYD原理存放数据文件,MYI用来存放索引文件,可以通过myisampack工具来进一步压缩数据文件,因为myisampack工具使用哈夫曼(Huffan)编码静态算法来压缩数据,因此使用myisampack工具压缩后的表是只读的,当然你也可以通过myisampack来解压数据文件。

在MySQL5.0版本之前,MyISAM默认支持的表大小为4G,如果需要支持大于4G的MyISAM表时,则需要制定MAX_ROWS和AVG_ROW_LENGTH的属性。从MyISAM5.0版本开始,MYIASM默认支持256T的单表数据,这足够一般应用的需求。

③ NDB存储索引

2003年,MySQL AB公司从Song Ericsson公司收购了NDB集群引擎。NDB存储引擎是一个集群存储引擎,类似于Orace了的RAC集群,不过与Oracle RAC share everything结构不同的是,其结构是share nothing的集群架构,因此能提供更高的可用性。NDB的特点是数据区别放在内存中(从MySQL5.1版本开始,可以将非索引数据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且通过添加NDB数据存储节点(Data Node)可以线性地提高数据库性能,是高可用、高性能的集群系统。

关于DNB存储引擎,有一个问题值得注意,那就是NDB存储引擎的连接操作(JOIN)是在MySQL数据库层完成的,而不是在存储引擎层完成的。这意味着,复杂的连接操作需要巨大的网络开销,因此查询速度很慢。如果解决了这个问题,NDB存储引擎的出去应该是非常巨大的。

④ Memory存储引擎

Memory存储引擎(之前称为HEAP存储引擎)将表中的数据存放在内存中,如果数据库重启或者发生崩溃表中的数据都将消失。它非常适合用于存储临时数据的临时表,以及数据仓库中纬度表。Memory存储引擎默认使用哈希索引,而不是我们熟悉的B+树索引。

虽然Memory存储引擎速度非常快,但在使用上还是有一定的限制。比如,只支持表锁,并发性能比较差,并且不支持TEXT和BLOB列类型。最重要的是,存储变长字段(varchar)时是按照定长字段(char)的方式进行的,因此会比较浪费内存(这个问题之前已经提到,eBay的工程师lgor Chernyshow 已经给出了patch解决方案)。

此外有一点容易被忽视,MySQL数据库使用Memory存储引擎作为临时表来存放查询的中间结果集(intermediate result)。如果中间结果集大于Memory存储引擎表的容量设置,又或者中间结果含有TEXT或BLOB列类型字段,则MySQL数据库会把其转换到MySQL存储引擎表二存放到磁盘中。之前提到MyISAM不缓存数据文件,因此这时产生的临时表的性能对于查询会有损失。

⑤ Archive存储引擎

Archive存储引擎只支持INSERT和SELECT操作,从MySQL5.1开始支持索引。Archive存储引擎索引zlib算法将数据行(row)进行压缩后存储,压缩比一般可达1:10。正如其名字所示,Archive存储引擎非常适合存储归档数据,比如日志信息。Archive存储引擎索引行锁来实现高并发的插入操作,但是其本身并不是事务安全的存储引擎,其设计目标主要提供高速的插入和压缩功能。

⑥ Federated存储引擎

Federated存储引擎表并不存放数据,它只是指向一台远程MySQL数据库服务器上的表。这非常类似于SQL Server的链接服务器和Oracle的透明网关,不同的是,当前Federated存储引擎只支持MySQL数据表,不支持异构数据库表。

二, MySQL数据库引擎适应场景分析

① InnoDB存储引擎

适合做网站的用户的登录的的基本信息, InnoDB存储引擎相比其他的存储引擎还是比较快的, InnoDB存储引擎不适合存储大量业务的数据, 这个原理是查询效率比较慢,再对于用户体验上不是太好, 一般我们业务都是操作缓存(redis)中数据,这样服务之间的响应时间变快了,用户体验上去了,InnoDB存储引擎还有一个好处就是MySQL服务器崩溃了都是做了数据备份(binlog)重启的时候读取binlog恢复数据。

② MyISAM存储引擎

这个存储引擎表锁,适合上说的缓存数据选择存储引擎,基本内存存储, 一个不好缺点就是需要开发者自己维护MySQL服务器崩溃了做日志备份 在重启服务器后 日志输入到数据库中

③ Federated存储引擎

这个引擎不是用来存储的, 主要把别的主机上的MySQL的库映射到本地数据中方便查询类似于远程调用

结语

MySQL的不同存储引擎有着不同适用场景, 需要开发者对不同场景业务之比较深的理解