# 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、索引，不同存储引擎的索引并不太一样； > 参考链接：