MyISAM VS InnoDB Storage

Mysql 5.6

MYISAM Features：

Storage limits256TBTransactionsNoLocking granularityTableMVCCNoGeospatial data type supportYesGeospatial indexing supportYesB-tree indexesYesT-tree indexesNoHash indexesNoFull-text search indexesYesClustered indexesNoData cachesNoIndex cachesYesCompressed dataYes^[a]Encrypted data^[b]YesCluster database supportNoReplication support^[c]YesForeign key supportNoBackup / point-in-time recovery^[d]YesQuery cache supportYesUpdate statistics for data dictionaryYes

^[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 limits64TBTransactionsYesLocking granularityRowMVCCYesGeospatial data type supportYesGeospatial indexing supportYes^[a]B-tree indexesYesT-tree indexesNoHash indexesNo^[b]Full-text search indexesYes^[c]Clustered indexesYesData cachesYesIndex cachesYesCompressed dataYes^[d]Encrypted data^[e]YesCluster database supportNoReplication support^[f]YesForeign key supportYesBackup / point-in-time recovery^[g]YesQuery cache supportYesUpdate statistics for data dictionaryYes

^[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/