MySQL存储引擎InnoDB和MyISAM区别

InnoDB 和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。

以前MySQL 默认的存储引擎是MYISAM，从5.5.5之后默认的存储引擎就改用了 InnoDB

它们的基本的差别为：

1）MyISAM类型不支持事务处理等高级处理，而InnoDB 类型支持。

2）MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持，以及外部键等高级数据库功能。

MyIASM是IASM表的新版本，有如下扩展：

二进制层次的可移植性。
NULL列索引。
对变长行比ISAM表有更少的碎片。
支持大文件。
更好的索引压缩。
更好的键码统计分布。
更好和更快的auto_increment处理。

以下是一些细节和具体实现的差别：

InnoDB不支持FULLTEXT类型的索引。
InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

在高性能MYSQL上还说默认MYISAM，其实要看那个版本，我现在的默认就是InnoDB，手册上是这么说的：
InnoDB is a high-reliability and high-performance storage engine for MySQL. Starting with MySQL 5.5, it is the default MySQL storage engine. Key advantages of InnoDB include:
Its design follows the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.
Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance.
InnoDB tables arrange your data on disk to optimize common queries based on primary keys. Each InnoDB table has a primary key index called the clustered index that organizes the data to minimize I/O for primary key lookups.
To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.
You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement. For example, you can use a join operation to combine data from InnoDB and MEMORY tables in a single query.
To determine whether your server supports InnoDB use the SHOW ENGINES statement.

Before MySQL 5.5.5, MyISAM is the default storage engine. (The default was changed to InnoDB in MySQL 5.5.5.) MyISAM is based on the older (and no longer available) ISAM storage engine but has many useful extensions.

Table 13.10. MyISAM Storage Engine Features

Storage limits	256TB	Transactions	No	Locking granularity	Table
MVCC	No	Geospatial data type support	Yes	Geospatial indexing support	Yes
B-tree indexes	Yes	Hash indexes	No	Full-text search indexes	Yes
Clustered indexes	No	Data caches	No	Index caches	Yes
Compressed data	Yes	Encrypted data	Yes	Cluster database support	No
Replication support	Yes	Foreign key support	No	Backup / point-in-time recovery	Yes
Query cache support	Yes	Update statistics for data dictionary	Yes