目录

• • • MyISAM的特性
    • InnoDB的特性
    • CSV的特性
    • Archive的特性
    • Memory的特性
    • Federated的特性
    • 如何选择存储引擎

MyISAM的特性

• 表级锁

  对表中的数据进行修改时，要对表进行加锁；读取表中的数据时要对表加共享锁。读取和写入是互斥的，读取时只能在表末进行插入。

• 不支持事物

  对MyISAM进行修复会导致数据丢失。

• 存储文件

  tableName.frm 存储表结构信息

  tableName.MYD 存储数据信息

  tableName.MYI 存储索引信息

• 修复表

  check table tableName;

  repaire table tableName;

  使用mysql工具：myisamchk

• 支持全文索引

  mysql 5.7之前的版本中原生支持全文索引的存储引擎

  支持对text等很大的字段的前500个字符建立前缀索引

  支持数据压缩，可以减小磁盘IO（压缩后不能插入，只能读取）：myisampack

• 单表容量限制

  版本小于5.0时，默认表大小为4G。可以通过修改MAX_Rows和AVG_ROW_LENGTH参数修改

  版本大于5.0时，单表默认支持256TB

• 适用场景

  非事务型应用。大多数不涉及报表或者财务的应用都是不需要事务的

  只读类应用。可以对表进行压缩，减少磁盘IO，另外读取表时共享锁之间是不会互相阻塞的。

  空间类应用。支持空间函数

InnoDB的特性

​ Mysql 5.5之后，innoDB成为Mysql默认的存储引擎。

• 支持事务

  完全支持ACID特性。通过redo log和undo log实现。

  1. redo log用于实现事务的持久性，作用于已提交的事务。由两部分组成：内存中的重做缓冲区，innodb_log_buffer_size设置缓冲区的大小（每隔一秒就会把缓冲区的数据刷到磁盘，不用设置的太大）；重做日志文件（id_logfileX）。
  2. undo log用于未提交事务的回滚，用于实现MVCC（多版本并发控制）。
  3. redo log基本是顺序写入，数据库运行时不需要对redo log进行读取操作；undo log要进行随机地读写，因此可以把undo log存储在固态存储设备上，如SSD或fashion io等

• 行级锁

  在对表进行写操作时，只需要对行加锁，可以最大程度地支持并发。当某一行被加上行级锁时，仍然可以读取这一行，会读到redo log中的记录。

  行级锁在存储引擎层实现，mysql服务器并不知道锁的具体实现

  注意：修改表结构会对表加表级锁，读取操作会被阻塞，直到表解锁。

• 存储文件

  innodb_file_per_table 设置为ON时：

  ​ tableName.frm 存储表结构

  ​ tableName.ibd 存储数据

  innodb_file_per_table 设置为OFF时：

  ​ tableName.frm 存储表结构

  ​ ibdataX 数据存储在系统表空间

• 如何选择系统表空间和独立表空间

  Mysql 5.5之前的版本默认使用系统表空间。一旦磁盘空间不足，我们为了释放磁盘空间，必须删除系统中无效的数据，但是删除了数据之后，系统表空间不会缩小，会造成大量的空间浪费，唯一的方式是将InnoDB表的数据导出后，删除系统表空间，再重启mysql服务器，进行表空间重建，再导入数据。十分复杂耗时，生产环境是不可接受的。

  如果使用独立表空间，可以使用optimize table 对表空间进行重建，收缩系统文件。

  另外，从IO来看，如果对多个表进行数据刷新，对于系统表空间，由于只有一个文件，这些操作是顺序进行的，而独立表空间可以同时向多个文件刷新数据。因此对于频繁进行数据刷新的应用来说，应该使用独立表空间。

• 系统表空间转移到独立表空间

  1. 使用mysqldump导出所有数据库表数据
  2. 停止Mysql服务（主从架构的服务器可以先从从服务器进行操作），修改innodb_file_per_table 参数，删除InnoDB文件（或重建data目录）
  3. 重启Mysql服务，重建InnoDB表空间
  4. 导入数据

• 其他

  MySql 5.7之后，InnoDB支持全文索引和空间函数

CSV的特性

• 存储

  数据以文本的方式存储在文件中，可以使用less、more等命令直接查看，也可以直接对数据文件进行编辑。

  tableName.CSV 存储表内容

  tableName.CSM 存储表的元数据，如表状态和数据量

  tableName. frm 存储表结构信息

• 所有列不能为NULL

• 不支持索引

• 适用场景

  适合作为数据交换的中间表，可以在数据库运行的时候直接导出或导入数据

Archive的特性

• 存储

  以zlib对数据进行压缩，磁盘IO更少

  tableName.ARZ 存储表数据

  tableName. frm 存储表结构信息

• 只支持insert和select操作

• 支持行级锁和专用的缓冲区

  适用于高并发场景

• 只允许在自增ID列上加索引

• 适用场景
  日志和数据采集类应用

Memory的特性

• 存储

  所有数据都存储在内存中，也叫HEAP存储引擎

  tableName. frm 存储表结构信息（存在磁盘中，断电不会丢失，但是数据会丢失）

• 支持hash索引和bTree索引

  等值查找适应hash索引

  返回查找使用bTree索引

• 所有字段的长度都固定为carchar(10) = char(10)

• 不支持blog和text等大字段

• 使用表级锁

• 表的最大大小由max_heap_table_size参数决定，默认为16M

• 适用场景

  查找或映射表（hash索引），如邮编和地区对应表

  保存数据分析中产生的中间表

  换成周期性聚合数据的结果表

  Meneory数据易丢失，要求数据可再生

  主db使用Memory存储引擎，从db使用其他存储引擎，但是主db重启时，主db会重建Memory表，从db也会跟着重建，所以还是不能保证数据不丢失

Federated的特性

Mysql默认禁止Federated存储引擎，启动时需要在mysql的启动文件my.cnf增加federated=1参数

• 提供了访问远程Mysql服务器上表的方法

• 本地不存储数据，数据全部放在远程服务器上，所有的操作都是在远程服务器上进行

• 本地保存表结构和连接远程服务器的信息

• 适用场景

  偶尔统计分析及手工查找

如何选择存储引擎

可以从以下几点来考虑：

1. 事务

   如果应用需要事务支持，InnoDB是目前最稳定，并且久经验证的事务存储引擎。如果不需要事务，并且只需要进行select和insert操作，myIsam也可以使用；如果select都比较少的情况下，可以使用Archive存储引擎，比如日志类应用（还可以压缩）

2. 备份

   如果需要热备份，目前只有Innodb有免费的热备份方案。mysql dump不算热备份（逻辑备份，而且需要加锁）

3. 崩溃恢复

   InnoDB是目前崩溃恢复最快的存储引擎

4. 存储引擎的特有特性

   如全文索引，地理空间搜索（Mysql 5.7之后InnoDB已经支持）

5. 尽量不要混合使用存储引擎

   如主从服务器中使用了InnoDB和MyIsam存储引擎，当事务发生回滚时，只有InnoBD表能够进行回滚。另外在线热备也会出现问题。