跳至主要內容

5 MySQL存储引擎

ClayMySQL约 1633 字大约 5 分钟

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