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支持)，根据主键查询时效率高，但根据主键进行删改时效率低。聚集索引：物理存储顺序与索引顺序相同
- 支持在线热备
适用场景
- 对安全性有要求（事务+恢复性好），比如财务、计费、银行
- 并发高（行级锁）
- 使用外键
可以在join查询中混用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存储引擎的瓶颈
适用场景
- 存储临时、不重要的数据
- 大量读 ，作为缓存
如何选择合适的存储引擎
- 安全性要求（事务）
- 并发高不高
- 使不使用外键
- 是否需要支持在线热备
一个数据库中，不同的表可以使用不同的存储引擎。
使用合适的存储引擎，会提高数据库的性能。