MySQL存储引擎

在MySQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的.frm文件，.frm文件是用来保存每个数据表的元数据（Meta）信息，包括表结构的定义等，.frm文件跟数据库存储引擎无关，也就是任何存储引擎的数据表都必须有.frm文件，命名方式为数据表名.frm，如user.frm. .frm文件可以用来在数据库崩溃时恢复表结构。
思考：

• 你的数据库有外键吗？
• 你需要事务支持吗？
• 你需要全文索引吗？
• 你经常使用什么样的查询模式？
• 你的数据有多大？

MyISAM是MySQL的默认存储引擎，基于传统的ISAM类型，支持全文搜索，但不是事务安全的，而且不支持外键。每张MyISAM表存放在三个文件中：frm 文件存放表格定义；数据文件是MYD (MYData)；索引文件是MYI (MYIndex)。
InnoDB是事务型引擎，支持回滚、崩溃恢复能力、多版本并发控制、ACID事务，支持行级锁定（InnoDB表的行锁不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，如like操作时的SQL语句），以及提供与Oracle类型一致的不加锁读取方式。InnoDB存储它的表和索引在一个表空间中，表空间可以包含数个文件。
从物理意义上来讲，InnoDB表由共享表空间文件（ibdata1）、独占表空间文件（ibd）、表结构文件（.frm）、以及日志文件（redo文件等）组成。
InnoDB存储引擎的管理是由引擎本身完成的，表空间（Tablespace）是由分散的段(Segment)组成。一个段(Segment)包含多个区（Extent）。
区（Extent）由64个连续的页（Page）组成，每个页大小为16K，即每个区大小为1MB，创建新表时，先使用32页大小的碎片页存放数据，使用完后才是区的申请（InnoDB最多每次申请4个区，保证数据的顺序性能）。页类型有：数据页、Undo页、系统页、事务数据页、插入缓冲位图页、以及插入缓冲空闲列表页。
独占表空间文件：若将innodb_file_per_table设置为on，则系统将为每一个表单独的生成一个table_name.ibd的文件，在此文件中，存储与该表相关的数据、索引、表的内部数据字典信息。
优点：

1. 每个表都有自已独立的表空间；
2. 每个表的数据和索引都会存在自已的表空间中；
3. 可以实现单表在不同的数据库中移动；
4. 空间可以回收（除drop table操作处，表空不能自已回收）：
   • drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过 alter table TableName engine=innodb; 回缩不用的空间；
   • 对于使innodb-plugin的InnoDB使用truncate table也会使空间收缩；
   • 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

缺点：
单表增加过大，如超过100个G。
共享表空间文件：在InnoDB存储引擎中，默认表空间文件是ibdata1（主要存储的是共享表空间数据），初始化为10M，且可以扩展。
优点：
可以放表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同步的文件上）。数据和文件放在一起方便管理。
缺点：
所有的数据和索引存放到一个文件中以为着将有一个很常大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。
InnoDB的表空间文件是可以修改的。使用共享表空间存储方式时，InnoDB的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。从InnoDB的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，InnoDB的单表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。
而在使用单独表空间存储方式时，每个表的数据以一个单独的文件来存放，这个时候的单表限制，又变成文件系统的大小限制了。
Windows用户请注意：FAT和VFAT(FAT32)不适合MySQL的生产使用。应使用NTFS。

总结

主要区别：

• MyISAM是非事务安全型的，而InnoDB是事务安全型的。
• MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
• MyISAM支持全文类型索引，而InnoDB不支持全文索引。
• MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
• MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
• InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。

应用场景：
MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。