MyISAM vs InnoDB 分析之二

参考http://stackoverflow.com/questions/12614541/whats-the-difference-between-myisam-and-innodb。

up vote221down voteaccepted

+100

The main differences between InnoDB and MyISAM ("with respect to designing a table or database" you asked about) are support for "referential integrity" and "transactions".

If you need the database to enforce foreign key constraints, or you need the database to support transactions (i.e. changes made by two or more DML operations handled as single unit of work, with all of the changes either applied, or all the changes reverted) then you would choose the InnoDB engine, since these features are absent from the MyISAM engine.

Those are the two biggest differences. Another big difference is concurrency. With MyISAM, a DML statement will obtain an exclusive lock on the table, and while that lock is held, no other session can perform a SELECT or a DML operation on the table.

Those two specific engines you asked about (InnoDB and MyISAM) have different design goals. MySQL also has other storage engines, with their own design goals.

So, in choosing between InnoDB and MyISAM, the first step is in determining if you need the features provided by InnoDB. If not, then MyISAM is up for consideration.

A more detailed discussion of differences is rather impractical (in this forum) absent a more detailed discussion of the problem space... how the application will use the database, how many tables, size of the tables, the transaction load, volumes of select, insert, updates, concurrency requirements, replication features, etc.

---

The logical design of the database should be centered around data analysis and user requirements; the choice to use a relational database would come later, and even later would the the choice of MySQL as a relational database management system, and then the selection of a storage engine for each table.

shareimprove this answer

edited Apr 29 '13 at 15:16

上面这一段是被采纳的，主要内容如下：

1.如果需要外键约束，或者需要数据库支持事务，请选择InnoDB。

2.MyISAM，一个DML语句将包含一个排它锁在表上，一旦锁被获取，其它会话都将等候。

参考http://stackoverflow.com/questions/20148/myisam-versus-innodb，这里面没有被采纳的回答，但是有很多精彩的内容。

up vote23down vote

InnoDB offers:

ACID transactions
row-level locking
foreign key constraints
automatic crash recovery
table compression (read/write)
spatial data types (no spatial indexes)

In InnoDB all data in a row except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. In InnoDB the COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used) execute slower than in MyISAM because the row count is not stored internally. InnoDB stores both data and indexes in one file. InnoDB uses a buffer pool to cache both data and indexes.

MyISAM offers:

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)

MyISAM has table-level locking, but no row-level locking. No transactions. No automatic crash recovery, but it does offer repair table functionality. No foreign key constraints. MyISAM tables are generally more compact in size on disk when compared to InnoDB tables. MyISAM tables could be further highly reduced in size by compressing with myisampack if needed, but become read-only. MyISAM stores indexes in one file and data in another. MyISAM uses key buffers for caching indexes and leaves the data caching management to the operating system.

Overall I would recommend InnoDB for most purposes and MyISAM for specialized uses only. InnoDB is now the default engine in new MySQL versions.

shareimprove this answer

answered May 28 '13 at 7:03 Pankaj Khurana 29934

这段说的挺有意思，主要内容如下：

InnoDB提供：

1.ACID的事务。

2.行级锁。

3.外键约束。

4.自动崩溃恢复。

5.表压缩（读和写）。

6.空间数据类型（没有索引）。

MyISAM提供：

1.更快的count求和（没有where、group by、和join被使用）。

2.全文检索。

3.更小的磁盘脚印。查询了一下磁盘脚印的意思，这里的意思应该就是磁盘使用。

4.更高的表压缩（只读）。

5.空间数据类型和索引（R-tree）。