MySQL 存储引擎

存储引擎

DBMS使用存储引擎进行数据的创建、查询、更新、删除操作。

不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。

MySQL提供了多种存储引擎，最常见的有3种。

1、MyISAM 

MySQL5.5之前的默认的存储引擎，每个MyISAM在磁盘上存储成三个文件，文件名和表名相同，扩展名分别是

（1）frm文件：存储表的定义

（2）MYD文件：存储表中的数据（记录）

（3）MYI文件：存储索引。索引保存的是数据文件的指针

特点

• 访问速度快（优）
• 支持全文索引（优）。但基本不使用MySQL的全文索引，全文索引一般都是使用第三方的ElasticSearch，Solr等更加成熟的解决方案。

• 不支持事务、外键（缺）
• 默认的锁粒度为表级锁，所以并发度很差（缺），但加锁快，锁冲突较少，不容易发生死锁（优）；

• 对数据的查询缓存只缓存索引，不缓存数据（记录），而且使用的是操作系统本身的缓存。
• 自动增长列可以是组合主键中的任何一列。设置自动增长列：创建表时，在字段后加auto_increment，默认初始值是1

适用场景

• 不需要事务
• 不使用外键
• 操作以select、insert为主

MyISAM支持三种不同的存储格式

（1）静态表（fixed）

表中不能包含变长字段（比如VARCHAR, BLOB, TEXT），每个记录都是固定的长度。如果MyISAM表中没有一个变长字段，则默认使用静态表。

优点：存储迅速，出现故障容易恢复

缺点：占用空间比动态表大。静态表在进行数据存储时会按照事先定义的列宽补足空格，但在访问的时候会去掉补的空格

（2）动态表（dynamic）

可包含变长字段（varchar、blob、text），如果一个MyISAM表包含任何可变长度的字段，或者该表创建时用row_format=dynamic指定，则该表使用动态格式存储

优点：占用空间小

缺点：频繁的更新、删除操作会产生碎片，需要定期用optimize table语句或myisamchk -r命令来改善性能，出现故障后较难恢复

（3）压缩表

由myisampack工具创建，每条记录都会被单独压缩，占据硬盘空间极小。

2、InnoDB 

MySQL5.5及其之后默认的存储引擎

InnoDB有2种存储方式

• 共享表空间存储：所有表的定义、数据、索引存放在同一个表空间中。
• 独占表空间存储：一张表独占一个表空间，表的定义保存在.frm文件中，数据、索引保存在.ibd文件中。

特点

• 自动增长列必须是主键，如果是组合主键，也必须是组合主键的第一列

• 支持外键约束。MySQL的存储引擎中只有innoDB支持外键。外键降低了查询速度（因为要查多张表）、使多张表耦合在一起，但更好地体现了实体、表之间的关联。
• 支持事务，恢复能力强。默认的事务隔离级别为可重复读
• 使用的锁粒度为行级锁，支持更高的并发。行级锁、事务，安全性有了，但是以牺牲效率换来的。
• 会将查询的结果（索引+数据）放到缓冲池中，加快后续查询的速度
• 发生故障后恢复性好。未完成的事务将根据redo log的数据重做；已提交但未写入的修改，将从doublewrite buffer重做；系统闲时会purge buffer
• 主键索引是聚集索引(Clustered index，仅InnoDB支持)，根据主键查询时效率高，但根据主键进行删改时效率低。聚集索引：物理存储顺序与索引顺序相同
• 支持在线热备

适用场景

• 对安全性有要求（事务+恢复性好），比如财务、计费、银行
• 并发高（行级锁）
• 使用外键

MyISAM、InnoDB的对比

（1）锁粒度不同，InnoDB为行级锁，MyISAM为表级锁

• InnoDB对并发的支持远比MyISAM高
• 但InnoDB锁冲突的概率更大，更容易发生死锁，而且为每一行加锁，开销也很大

（2）InnoDB支持外键，MyISAM不支持

（3）InnoDB支持事务，MyISAM不支持

• InnoDB更安全，开销也更大

（4）InnoDB缓存查询到的索引、数据，MyISAM只缓存索引

（5）查询效率MyISAM远高于InnoDB，尤其是在数据表行数多的时候

• MyISAM的索引中存储的是数据（记录）的指针（地址），先查索引确定要操作的记录的地址，直接就去访问这个地址
• InnoDB的索引存储的是记录的行号，从索引中查到行号（行坐标），还需要逐行统计行号（从第一行开始数）
• 而且InnoDB在查询过程中，要维护缓冲池中的查询缓存（索引+数据），MyISAM只需维护缓存中的索引

（6）InnoDB支持在线热备，有很成熟的在线热备解决方案

（7）MyISAM的表文件包括：.frm(表定义)，.MYI(索引)，.MYD(数据)，InnoDB的表文件为.frm(表定义)，.ibd（索引、数据）

一句话，MyISAM效率更高、速度更快，InnoDB功能更全、安全性更好、开销更大。

3、MEMORY

数据（记录）不是储存在文件中，而是存储在内存中。每个memory表对应一个.frm文件（表定义、索引）。

特点

• 访问速度极快。数据存放在内存中+使用HASH索引
• 宕机、关闭服务器，数据会丢失
• 表的大小有限制（内初有限）
• 对表的数据类型有限制。比如：只支持定长类型，VARCHAR会被自动存储为CHAR类型，不支持TEXT、BLOB
• 锁粒度为表级锁。并发量大的时候，表级锁会成为MEMORY存储引擎的瓶颈

如何选择合适的存储引擎

• 安全性要求（事务）
• 并发高不高
• 使不使用外键
• 是否需要支持在线热备

一个数据库中，不同的表可以使用不同的存储引擎。

使用合适的存储引擎，会提高数据库的性能。