16.5 MySQL存储引擎

存储引擎简介

​ MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。

​ 例如,如果你在研究大量的临时数据,你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。

常用的存储引擎及其特点

MyISAM存储引擎

在5.5版本之前,MyISAM是MySQL的默认存储引擎。

每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名 为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

MyISAM文件的格式是平台无关的,这意味着你可以将数据和索引文件从一个intel服务器上拷贝到一台PowerPC或者Sun SPARC上,而不会出任何问题。

该存储引擎并发性差,不支持事务,所以使用场景比较少,主要特点为:

  • 不支持事务

  • 不支持外键,如果强行增加外键,不会提示错误,只是外键不起作用

  • 对数据的查询缓存只会缓存索引,不会像InnoDB一样缓存数据,而且是利用操作系统本身的缓存

  • 默认的锁粒度为表级锁,所以并发度很差,加锁快,锁冲突较少,所以不太容易发生死锁

  • 支持全文索引(MySQL5.6之后,InnoDB存储引擎也对全文索引做了支持),但是MySQL的全文索引基本不会使用,对于全文索引,现在有其他成熟的解决方案,比如:ElasticSearch,Solr,Sphinx等

  • 数据库所在主机如果宕机,MyISAM的数据文件容易损坏,而且难恢复

InnoDB存储引擎

从MySQL5.5版本之后,MySQL的默认内置存储引擎已经是InnoDB了,它的主要特点有:

  • 灾难恢复性比较好

  • 支持事务,默认的事务隔离级别为可重复度,通过MVCC(并发版本控制)来实现的

  • 使用的锁粒度为行级锁,可以支持更高的并发

  • 支持外键

  • 配合一些热备工具可以支持在线热备份

  • 在InnoDB中存在着缓冲管理,通过缓冲池,将索引和数据全部缓存起来,加快查询的速度

  • 对于InnoDB类型的表,其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块,都位于B+数的叶子节点上;

独享表空间:使用 .ibd 文件来存放数据,且每一张 InnoDB 表对应一个 .ibd 文件,文件存放在所属数据库的目录下。

共享表空间:使用 .ibdata 文件,所有表共同使用一个(或多个,自行配置).ibdata 文件。

配置:innodb_file_per_table 参数

根据版本不同,默认参数不同

MEMORY存储引擎

将数据存在内存中,和市场上的Redis,memcached等思想类似,为了提高数据的访问速度,主要特点:

  • 支持的数据类型有限制,比如:不支持TEXT和BLOB类型,对于字符串类型的数据,只支持固定长度的行,VARCHAR会被自动存储为CHAR类型

  • 支持的锁粒度为表级锁。所以,在访问量比较大时,表级锁会成为MEMORY存储引擎的瓶颈

  • 由于数据是存放在内存中,所以在服务器重启之后,所有数据都会丢失;

  • 查询的时候,如果有用到临时表,而且临时表中有BLOB,TEXT类型的字段,那么这个临时表就会转化为MyISAM类型的表,性能会急剧降低

ARCHIVE存储引擎

ARCHIVE存储引擎适合的场景有限,由于其支持压缩,故主要是用来做日志,流水等数据的归档,主要特点:

  • 支持Zlib压缩,数据在插入表之前,会先被压缩

  • 仅支持SELECT和INSERT操作,存入的数据就只能查询,不能做修改和删除

  • 只支持自增键上的索引,不支持其他索引

CSV存储引擎

数据中转试用,主要特点:

  • 其数据格式为.csv格式的文本,可以直接编辑保存

  • 导入导出比较方便,可以将某个表中的数据直接导出为csv,试用Excel办公软件打开;

MyISAM和InnoDB的主要区别

  • MyISAM锁的粒度是表级,而InnoDB支持行级锁定

    • 由于锁粒度的不同,InnoDB比MyISAM支持更高的并发

    • InnoDB相对于MyISAM来说,更容易发生死锁,锁冲突的概率更大,而且上锁的开销也更大,因为需要为每一行加锁

  • 在备份容灾上,InnoDB支持在线热备,有很成熟的在线热备解决方案

  • 查询性能上,MyISAM的查询效率高于InnoDB,因为InnoDB在查询过程中,是需要维护数据缓存,而且查询过程是先定位到行所在的数据块,然后在从数据块中定位到要查找的行;而MyISAM可以直接定位到数据所在的内存地址,可以直接找到数据

  • MyISAM的表结构文件包括:.frm(表结构定义),.MYI(索引),.MYD(数据);而InnoDB的表数据文件为:.ibd(数据)和.frm(表结构定义)

  • MyISAM是非事务安全型的,而InnoDB是事务安全型的。

  • MyISAM支持全文类型索引,而InnoDB不支持全文索引。

  • MyISAM表是保存成文件的形式,在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。

如何选择合适的存储引擎

1、使用场景是否需要事务支持; 2、是否需要支持高并发,InnoDB的并发度远高于MyISAM; 3、是否需要支持外键; 4、是否需要支持在线热备; 5、高效缓冲数据,InnoDB对数据和索引都做了缓冲,而MyISAM只缓冲了索引; 6、索引,不同存储引擎的索引并不太一样;

参考链接:https://segmentfault.com/a/1190000019400925

Last updated