官网权威Innodb 与 MyISAM 区别总结

官网权威Innodb 与 MyISAM 区别总结

先总结一下：

• InnoDB支持『事务』，MyISAM不支持

• InnoDB支持『聚簇索引』，MyISAM不支持

• InnoDB支持『数据缓存』，MyISAM不支持数据缓存

• InnoDB支持『MVCC』（多版本控制锁），MyISAM不支持

• InnoDB支持『外键』，MyISAM不支持

• InnoDB从MySQL 5.6版本以后才支持全文索引，MyISAM一直支持

• InnoDB从MySQL5.7版本以后才支持地理空间索引，MyISAM一直支持

• InnoDB内部创建哈希索引来实现自适应哈希索引特性，MyISAM没有hash 索引

• InnoDB支持『表锁 + 行锁』，MyISAM只支持『表锁』

• InnoDB存储最大限制是64TB，MyISAM存储最大限制是256TB

一共是上述十点，对应的表格对比如下表格所示：

Table 15.1 InnoDB Storage Engine Features

Feature	Support
B-tree indexes（其实这里指的是B+树）	Yes
Backup/point-in-time recovery (Implemented in the server, rather than in the storage engine.)	Yes
Cluster database support	No
Clustered indexes	Yes
Compressed data	Yes
Data caches	Yes
Encrypted data	Yes (Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest encryption is supported.)
Foreign key support	Yes
Full-text search indexes	Yes (Support for FULLTEXT indexes is available in MySQL 5.6 and later.)
Geospatial data type support	Yes
Geospatial indexing support	Yes (Support for geospatial indexing is available in MySQL 5.7 and later.)
Hash indexes	No (InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.)
Index caches	Yes
Locking granularity	Row
MVCC	Yes
Replication support (Implemented in the server, rather than in the storage engine.)	Yes
Storage limits	64TB
T-tree indexes	No
Transactions	Yes
Update statistics for data dictionary	Yes

Feature	Support

表格来源

Table 16.2 MyISAM Storage Engine Features

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

表格来源