The main difference is that InnoDB supports transactions while MyISAM does not.
There are numerous other differences, however the common one's i am aware of are:
MyISAM has typically been considered faster at searching, but recent InnoDB improvements are removing this difference and improving high concurrency workload performance
InnoDB support transactions whilst MyISAM does not
InnoDB supports referential integrity whilst MyISAM does not
InnoDB handles indexes a bit differently, storing the primary key as part of every index (making indexes take up more room on the disk, but also making a covering index more likely)
MyISAM does table level locking while InnoDB can do row level locking
Different memory/buffer/index settings are used in the MySQL configuration files
InnoDB is typically said to have better crash recovery
As mentioned in another answer, the data is store on disk differently. I believe InnoDB is configurable in this area and can have one file per table etc. if required
I'm sure a google search or the MySQL site will bring up numerous other differences in more detail.
InnoDB is more strict in data integrity while MyISAM is loose.
InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.
InnoDB has transactions while MyISAM does not.
InnoDB has foreign keys and relationship contraints while MyISAM does not.
InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
MyISAM has full-text search index while InnoDB has not.
In light of these differences, InnoDB and MyISAM have their unique advantages and disadvantages against each other. They each are more suitable in some scenarios than the other.
Advantages of InnoDB
InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.
Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.
Disadvantages of InnoDB
Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.
Consumes more system resources such as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.
No full-text indexing.
Advantages of MyISAM
Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
Full-text indexing.
Especially good for read-intensive (select) tables.
Disadvantages of MyISAM
No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.
Doesn’t support transactions which is essential in critical data applications such as that of banking.
Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.
The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.
The most important difference between MyISAM and InnoDB is that InnoDB supports transactions and foreign keys. If you need foreign keys and related functionality (for example automatically cascading deletes), you will need to use InnoDB.
InnoDB is slower than MyISAM for most uses, but can perform faster in certain conditions due to a better locking mechanism; MyISAM locks the whole table for reading while inserts/updates are executing. InnoDB can do row-level locking, thus allowing multiple concurrent writes and read on the table.
MyISAM and InnoDB also store their data on disk differently. MyISAM uses a data file and an index file for each table, stored in a directory named after the database. InnoDB seems to lump everything together in a file called ibdata1.
If reliability is a consideration for
your data, do not configure InnoDB to
use data files or log files on NFS
volumes. Potential problems vary
according to OS and version of NFS,
and include such issues as lack of
protection from conflicting writes,
and limitations on maximum file sizes.
通过使用 DATA DIRECTORY='/ path/to/data/directory' 或 INDEX DIRECTORY='/path/to/index/directory' 你可以指定MyISAM存储引擎应该在哪里
放一个表的数据文件和索引文件。该目录必须是目录的完整路径名，而不是相对路径。
InnoDB Features
1. Provides Full transaction capability with full ACID (Atomicity, Consistency, Isolation, and Durability) compliance.
It has row level locking.By supporting row level locking, you can add data to an InnoDB table without the engine locking the table with each insert and this speeds up both the recovery and storage of information in the database.
The key to the InnoDB system is a database, caching and indexing structure where both indexes and data are cached in memory as well as being stored on disk This enables very fast recovery, and works even on very large data sets.
InnoDB supports foreign key constraints
InnoDB supports automatic crash recovery
InnoDB supports table compression (read/write)
InnoDB supports spatial data types (no spatial indexes)
Innodb support non-locking ANALYZE TABLE and is only required when the server has been running for a long time since it dives into the index statistics and gets the index information when the table opens.
Innodb does not have separate index files so they do not have to be opened.
Innodb builds its indexes one row at a time in primary key order (after an ALTER), which means index trees aren't built in optimal order and are fragmented.There is currently no way to defragment InnoDB indexes, as InnoDB can't build indexes by sorting in MySQL 5.0. Even dropping and recreating InnoDB indexes may result in fragmented indexes, depending on the data.
A table can contain a maximum of 1000 columns.
The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. (1024 bytes for non-64-bit builds before MySQL 5.0.17, and for all builds before 5.0.15.)
The default database page size in InnoDB is 16KB. By recompiling the code, you can set it to values ranging from 8KB to 64KB. You must update the values of UNIV_PAGE_SIZE and UNIV_PAGE_SIZE_SHIFT in the univ.i source file.
InnoDB tables do not support FULLTEXT indexes.
MYISAM Features
No Transaction support
Table level locking
Provides Full Text search
No limit to data in table.
fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used)
full text indexing
smaller disk footprint
very high table compression (read only)
spatial data types and indexes (R-tree)
By using DATA DIRECTORY='/path/to/data/directory' or INDEX DIRECTORY='/path/to/index/directory' you can specify where the MyISAM storage engine should
put a table's data file and index file. The directory must be the full path name to the directory, not a relative path.
MyISAM 在备份方面更方便，因为只需锁定所有表并直接在文件系统中复制文件就相当简单了。 （mysqlhotcopy 是一个 perl 脚本，甚至是 mysql 的一部分，据我所知）
InnoDB 稍微复杂一些，仅仅复制文件是不行的，因为它们不能在另一台机器上开箱即用地恢复。
然而，有一些商业软件提供 InnoDB 热复制。
MyISAM is more convienient when it comes to backup, since it's rather simple to just lock all tables and copy the files directly in the filesystem. (mysqlhotcopy which is a perl-script is even part of mysql afaik)
InnoDB is a little more complex and just copying the files won't do since they cannot be restored on another machine out-of-the-box.
However, there are commercial software that offers InnoDB hotcopying.
发布评论
评论（12）
主要区别在于 InnoDB 支持事务，而 MyISAM 不支持。
还有许多其他差异，但我知道的常见差异是：
我确信谷歌搜索或 MySQL 站点会更详细地显示许多其他差异。
The main difference is that InnoDB supports transactions while MyISAM does not.
There are numerous other differences, however the common one's i am aware of are:
I'm sure a google search or the MySQL site will bring up numerous other differences in more detail.
功能和性能比较：
鉴于这些差异，InnoDB 和 MyISAM 各有其独特的优点和缺点。它们在某些情况下比另一个更适合。
InnoDB 的优点
InnoDB 的缺点
MyISAM 的优点
MyISAM 的缺点
比较非常简单。 InnoDB更适合需要频繁插入和更新的数据关键情况。另一方面，MyISAM 对于不太依赖数据完整性且大多只是选择和显示数据的应用程序表现更好。
参考：
比较InnoDB 和 MyISAM
您还可以在这里查看更多详细信息：
MyISAM 或 InnoDB MySQL 引擎？
希望这会有所帮助。
Features and Performance comparison:
In light of these differences, InnoDB and MyISAM have their unique advantages and disadvantages against each other. They each are more suitable in some scenarios than the other.
Advantages of InnoDB
Disadvantages of InnoDB
Advantages of MyISAM
Disadvantages of MyISAM
The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.
Reference:
Comparison InnoDB and MyISAM
You can also check it out here for further details:
MyISAM Or InnoDB MySQL engine?
Hope this helps.
MyISAM 支持（非标准 SQL）全文索引，而 InnoDB 仍然不支持。这是我们今天使用 MyISAM 的唯一原因。
MyISAM supports (non-standard-SQL) fulltext indexing which InnoDB still does not. This is the only reason we ever use MyISAM today.
MyISAM和InnoDB之间最重要的区别是InnoDB支持事务和外键。如果您需要外键和相关功能（例如自动级联删除），则需要使用 InnoDB。
在大多数情况下，InnoDB 比 MyISAM 慢，但由于更好的锁定机制，在某些情况下可以执行得更快； MyISAM 在执行插入/更新时锁定整个表以供读取。 InnoDB 可以进行行级锁定，从而允许对表进行多个并发写入和读取。
The most important difference between MyISAM and InnoDB is that InnoDB supports transactions and foreign keys. If you need foreign keys and related functionality (for example automatically cascading deletes), you will need to use InnoDB.
InnoDB is slower than MyISAM for most uses, but can perform faster in certain conditions due to a better locking mechanism; MyISAM locks the whole table for reading while inserts/updates are executing. InnoDB can do row-level locking, thus allowing multiple concurrent writes and read on the table.
您可以获得有关MyISAM & 的更多信息。 MySQL 中的 InnoDB 文档：
http://dev.mysql .com/doc/refman/5.1/en/myisam-storage-engine.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-overview.html
You can have more information about MyISAM & InnoDB in MySQL Documentation:
http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-overview.html
主要区别在于 InnoDB 支持 事务 ，而 MyISAM 则没有。
The major difference is that InnoDB supports transactions, whereas MyISAM doesn't.
MyISAM 和 InnoDB 在磁盘上存储数据的方式也不同。 MyISAM为每个表使用一个数据文件和一个索引文件，存储在以数据库命名的目录中。 InnoDB 似乎将所有内容都集中在一个名为 ibdata1 的文件中。
MyISAM and InnoDB also store their data on disk differently. MyISAM uses a data file and an index file for each table, stored in a directory named after the database. InnoDB seems to lump everything together in a file called ibdata1.
NFS 支持
与 MyISAM 不同，InnoDB 在 NFS 上可能会出现问题。
来自配置 InnoDB（MySQL 版本 5.5）
NFS support
Unlike MyISAM, InnoDB may have problems on NFS.
From Configuring InnoDB (MySQL version 5.5)
InnoDB特性
1. 提供完整的事务能力，并完全符合ACID（原子性、一致性、隔离性和持久性）。
它具有行级锁定。通过支持行级锁定，您可以将数据添加到 InnoDB 表中，而无需引擎在每次插入时锁定表，这可以加快数据库中信息的恢复和存储速度。
InnoDB系统的关键是数据库、缓存和索引结构，其中索引和数据都缓存在内存中以及存储在磁盘上，这使得恢复非常快，甚至可以处理非常大的数据集。
InnoDB 支持外键约束
InnoDB 支持空间数据类型（无空间索引）
Innodb 支持非锁定 ANALYZE TABLE，并且仅当服务器运行很长时间时才需要，因为它会深入索引统计并在表打开时获取索引信息。< /p>
Innodb 没有单独的索引文件，因此不必打开它们。
Innodb 按照主键顺序（在 ALTER 之后）一次构建一行索引，这意味着索引树不是按照最佳顺序构建的并且是碎片化的。目前没有办法对 InnoDB 索引进行碎片整理，而 InnoDB 可以MySQL 5.0 不再通过排序建立索引。即使删除并重新创建 InnoDB 索引也可能会导致索引碎片，具体取决于数据。
一个表最多可以包含 1000 列。
InnoDB内部最大键长度为3500字节，但MySQL本身将其限制为3072字节。 （对于 MySQL 5.0.17 之前的非 64 位版本以及 5.0.15 之前的所有版本，为 1024 字节。）
InnoDB 表不支持 FULLTEXT 索引。
MYISAM 功能
放一个表的数据文件和索引文件。该目录必须是目录的完整路径名，而不是相对路径。
您可以在以下位置找到更多详细信息
http://faisalbhagat.blogspot.com/2014/09/innodb-vs -myisam.html
InnoDB Features
1. Provides Full transaction capability with full ACID (Atomicity, Consistency, Isolation, and Durability) compliance.
It has row level locking.By supporting row level locking, you can add data to an InnoDB table without the engine locking the table with each insert and this speeds up both the recovery and storage of information in the database.
The key to the InnoDB system is a database, caching and indexing structure where both indexes and data are cached in memory as well as being stored on disk This enables very fast recovery, and works even on very large data sets.
InnoDB supports foreign key constraints
InnoDB supports spatial data types (no spatial indexes)
Innodb support non-locking ANALYZE TABLE and is only required when the server has been running for a long time since it dives into the index statistics and gets the index information when the table opens.
Innodb does not have separate index files so they do not have to be opened.
Innodb builds its indexes one row at a time in primary key order (after an ALTER), which means index trees aren't built in optimal order and are fragmented.There is currently no way to defragment InnoDB indexes, as InnoDB can't build indexes by sorting in MySQL 5.0. Even dropping and recreating InnoDB indexes may result in fragmented indexes, depending on the data.
A table can contain a maximum of 1000 columns.
The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. (1024 bytes for non-64-bit builds before MySQL 5.0.17, and for all builds before 5.0.15.)
InnoDB tables do not support FULLTEXT indexes.
MYISAM Features
put a table's data file and index file. The directory must be the full path name to the directory, not a relative path.
you can find more detail at
http://faisalbhagat.blogspot.com/2014/09/innodb-vs-myisam.html
下面是 InnoDB 和 MyIsam 之间差异的描述：
InnoDB 和 MyIsam 之间的差异
一些差异：
Here is a description of differences between InnoDB and MyIsam:
Differences between InnoDB and MyIsam
Few differences:
MyISAM 在备份方面更方便，因为只需锁定所有表并直接在文件系统中复制文件就相当简单了。 （mysqlhotcopy 是一个 perl 脚本，甚至是 mysql 的一部分，据我所知）
InnoDB 稍微复杂一些，仅仅复制文件是不行的，因为它们不能在另一台机器上开箱即用地恢复。
然而，有一些商业软件提供 InnoDB 热复制。
MyISAM is more convienient when it comes to backup, since it's rather simple to just lock all tables and copy the files directly in the filesystem. (mysqlhotcopy which is a perl-script is even part of mysql afaik)
InnoDB is a little more complex and just copying the files won't do since they cannot be restored on another machine out-of-the-box.
However, there are commercial software that offers InnoDB hotcopying.
虽然事务支持是主要区别，但如果您有 长时间运行的 SELECT 查询与 UPDATE 语句混合。
While transaction support is the major difference, table-level locking can be an issue if you have long-running SELECT queries mixed with UPDATE statements.