Mysql存储引擎

一、Mysql的存储引擎

简单来说，存储引擎就是指表的类型以及表在计算机上的存储方式。

存储引擎的概念是MySQL的特点，Oracle中没有专门的存储引擎的概念，Oracle有OLTP和OLAP模式的区分。不同的存储引擎决定了MySQL数据库中的表可以用不同的方式来存储。我们可以根据数据的特点来选择不同的存储引擎。

在MySQL中的存储引擎有很多种，可以通过“SHOW ENGINES”语句来查看。

从截图我们可以看到mysql默认的是InnoDB，并且还有其他存储引擎以及它们的作用。
我们主要讨论前三种比较常见的存储引擎——InnoDB，MYISAM、Memory。

二、InnoDB存储引擎

1、InnoDB 是 MySQL 默认的事务型存储引擎，只有在需要 InnoDB 不支持的特性时，才考虑使用其它存储引擎。
2、InnoDB给MySQL的表提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全。
3、InnoDB支持外键、自增长。自动增长列的值不能为空，并且值必须唯一。
4、采用 MVCC 来支持高并发，并且实现了四个标准的隔离级别，默认级别是可重复读。
5、表是基于聚簇索引建立的，它对主键的查询性能有很高的提升。
6、内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够自动在内存中创建哈希索引以加速读操作的自适应哈希索引、能够加速插入操作的插入缓冲区等。
7、通过一些机制和工具支持真正的热备份。
8、InnoDB中，创建的表的表结构存储在.frm文件中。数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中。

InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。

三、MyISAM存储引擎

1、MyISAM是MySQL中常见的存储引擎，曾经是MySQL的默认存储引擎。
2、MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等
3、 MyISAM 不支持事务和行级锁，有表级锁
4、崩溃后无法安全恢复。可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。
5、可以包含动态或者静态的行。
6、如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。
7、MyISAM的表存储成3个文件。文件的名字与表名相同。拓展名为frm、MYD、MYI。其实，frm文件存储表的结构；MYD文件存储数据，是MYData的缩写；MYI文件存储索引，是MYIndex的缩写。
8、基于MyISAM存储引擎的表支持3种不同的存储格式。包括静态型、动态型和压缩型。其中，静态型是MyISAM的默认存储格式，它的字段是固定长度的；动态型包含变长字段，记录的长度不是固定的；压缩型需要用到myisampack工具，占用的磁盘空间较小。

MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。
如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表适合采用 MyISAM 压缩表。
对于只读数据，或者表比较小、可以容忍修复操作，则依然可以继续使用 MyISAM。

InnoDB和MyISAM比较
事务：InnoDB 是事务型的。
备份：InnoDB 支持在线热备份。
处理速度：MyISAM速度比InnoDB快。
崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB高很多，而且恢复的速度也更慢。
并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
其它特性：MyISAM 支持全文索引，地理空间索引。

为什么MyISAM会比Innodb的查询速度快?
INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多:
1）数据块，INNODB要缓存，MYISAM只缓存索引块， 这中间还有换进换出的减少；
2）innodb寻址要映射到块，再到行，MYISAM记录的直接是文件的OFFSET，定位比INNODB要快
3）INNODB还需要维护MVCC一致：
InnoDB通过为每一行记录添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。【但是InnoDB并不存储这些事件发生时的实际时间，相反它只存储这些事件发生时的系统版本号。这是一个随着事务的创建而不断增长的数字。每个事务在事务开始时会记录它自己的系统版本号。】每个查询必须去检查每行数据的版本号与事务的版本号是否相同。

参考：MySQL与B树的那些事

四、MEMORY存储引擎

MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。

MEMORY默认使用哈希索引。速度比使用B+型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。

注意，MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

五、如何选择？

InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。