# 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、索引，不同存储引擎的索引并不太一样； > 参考链接： --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://wiki.clay-wangzhi.com/16-mysql/16.5-mysql-cun-chu-yin-qing.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.