InnoDB特点(相较与MyISAM)
- 支持事务
- 支持外键
- 聚簇索引
- 没有字段保存表的具体行数(count(*)的时候全表扫描)
- 锁粒度小(最小为行级锁)
- 存储量最多支持64T
MyISAM特点(相较与InnoDB)
- 不支持事物
- 读效率高
- 非聚簇索引
- 不支持外建
- 存储量最多支持256T
以上是主要区别,也是面试常遇见的,下面是 InnoDb 和 MyISAM 的详细对比:
InnoDb | MyISAM | |
---|---|---|
B-tree indexes(B树索引) | Yes | Yes |
Backup/point-in-time recovery(备份/还原) (Implemented in the server, rather than in the storage engine.) | Yes | Yes |
Cluster database support(集群支持) | No | No |
Clustered indexes(聚簇索引) | Yes | No |
Compressed data(数据压缩) | Yes | 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(数据缓存) | Yes | No |
Encrypted data(加密数据) | Yes (Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest tablespace encryption is supported.) | Yes (Implemented in the server via encryption functions.) |
Foreign key support(外建) | Yes | No |
Full-text search indexes(全文搜索索引) | Yes (InnoDB 在MySQL5.6及以后的版本中支持全文索引) | Yes |
Geospatial data type support(几何类型数据支持) | Yes | Yes |
Geospatial indexing support(几何类型数据索引) | Yes (InnoDB 在MySQL5.7及以后的版本中支持全文索引) | Yes |
Hash indexes(hash索引) | No (InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.) | No |
Index caches(索引缓存) | Yes | Yes |
Locking granularity(锁粒度) | Row | Table |
MVCC(多版本并发控制) | Yes | No |
Replication support (Implemented in the server, rather than in the storage engine.) | Yes | Yes |
Storage limits(存储限制) | 64TB | 256TB |
T-tree indexes(T-tree索引) | No | No |
Transactions(事物) | Yes | No |
Update statistics for data dictionary(更新数据字典的统计信息) | Yes | Yes |
该表格来自MySQL 5.7 Reference Manual, 但是两张表格是分开的,我这里把它合并到一起,方便查看