MyISAM VS InnoDB Storage

Mysql 5.6

MYISAM Features：

Storage limits	256TB	Transactions	No	Locking granularity	Table
MVCC	No	Geospatial data type support	Yes	Geospatial indexing support	Yes
B-tree indexes	Yes	T-tree indexes	No	Hash indexes	No
Full-text search indexes	Yes	Clustered indexes	No	Data caches	No
Index caches	Yes	Compressed data	Yes[a]	Encrypted data[b]	Yes
Cluster database support	No	Replication support[c]	Yes	Foreign key support	No
Backup / point-in-time recovery[d]	Yes	Query cache support	Yes	Update statistics for data dictionary	Yes
[a] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only. [b] Implemented in the server (via encryption functions), rather than in the storage engine. [c] Implemented in the server, rather than in the storage engine. [d] Implemented in the server, rather than in the storage engine.

InnoDB Features：

Storage limits	64TB	Transactions	Yes	Locking granularity	Row
MVCC	Yes	Geospatial data type support	Yes	Geospatial indexing support	Yes[a]
B-tree indexes	Yes	T-tree indexes	No	Hash indexes	No[b]
Full-text search indexes	Yes[c]	Clustered indexes	Yes	Data caches	Yes
Index caches	Yes	Compressed data	Yes[d]	Encrypted data[e]	Yes
Cluster database support	No	Replication support[f]	Yes	Foreign key support	Yes
Backup / point-in-time recovery[g]	Yes	Query cache support	Yes	Update statistics for data dictionary	Yes
[a] InnoDB support for geospatial indexing is available in MySQL 5.7.5 and higher. [b] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature. [c] InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher. [d] Compressed InnoDB tables require the InnoDB Barracuda file format. [e] Implemented in the server (via encryption functions), rather than in the storage engine. [f] Implemented in the server, rather than in the storage engine. [g] Implemented in the server, rather than in the storage engine.

如果我们想将MyISAM表转换为InnoDB表，我们需要注意什么呢，其实了解他们的具体属性，我们就可以很容易知道有哪些关键点了：


1、MyISAM的主键索引中，可以在非第一列(非第一个字段)使用自增列，而InnoDB的主键索引中包含自增列时，必须在最前面；这个特性在discuz论坛中，被设计用于“抢楼”功能，因此，若有类似的业务，则无法将该表从MyISAM转成InnoDB，需要自行变通实现（我们则是将其改到Redis中实现）；
2、不带条件频繁统计全表总记录数时(SELECT COUNT(*) FROM TAB)，InnoDB相对较慢，而MyISAM则飞快；不过，如果是基于索引条件的统计，则二者相差不大；
3、InnoDB在5.6以前不支持全文索引，不过这个相信无所谓，没什么人会在MySQL里直接跑全文索引，尤其是对中文的全文索引（前阵子有开发同学提需求直接被我否了），确实有需要的话，可以采用Sphinx、Lucene等其他方案实现；
4、一次性导入大量数据并且后续还要进行加工处理的，可以先导入到MyISAM引擎表中，经过一通加工处理完后，再导入InnoDB表（我曾经在业务中用此方法提高数据批量导入及处理效率）；
5、InnoDB不支持LOAD TABLE FROM MASTER语法（不过应该也很少人使用吧）；
以上关键点引用：http://imysql.com/