MySQL storage engine features and selection suggestions
Common storage engines of MySQL official storage engines include Innodb, MyISAM, Memory, Archive, NDB, and BDB. The most famous third parties are TokuDB, Infobright, InnfiniDB, and XtraDB)
Comparison of features of the official storage engine
MySQL storage engine comparison
Features |
MyISAM |
InnoDB |
Memory |
Archive |
NDB |
BDB |
Storage restrictions |
No |
64 TB |
Yes |
No |
Yes |
No |
Transactions |
|
√ |
|
|
|
√ |
MVCC |
|
√ |
|
√ |
√ |
|
Lock Granularity |
Table |
Row |
Table |
Row |
Row |
Page |
B-Tree Index |
√ |
√ |
√ |
|
√ |
√ |
Hash Index |
|
√ |
√ |
|
√ |
|
Full-text index |
√ |
5.6 support for e-Files |
|
|
|
|
Cluster Index |
|
√ |
|
|
|
|
Data Cache |
|
√ |
√ |
|
√ |
|
Index Cache |
√ |
√ |
√ |
|
√ |
|
Data Compression |
√ |
|
|
√ |
|
|
Batch insert |
High |
Relatively low |
High |
Very high |
High |
High |
Memory consumption |
Low |
High |
Medium |
Low |
High |
Low |
Foreign key support |
|
√ |
|
|
|
|
Replication support |
√ |
√ |
√ |
√ |
√ |
√ |
Query Cache |
√ |
√ |
√ |
√ |
√ |
√ |
Backup Recovery |
√ |
√ |
√ |
√ |
√ |
√ |
Cluster Support |
|
|
|
|
√ |
|
TokuDB: supports data compression and high-speed writing, but is not suitable for scenarios with multiple updates.
Infobright/InfiniDB: A column-based storage engine for OLAP environments. Infobright community edition only supports load data operations.
Storage engine selection and suggestions: select a storage engine suitable for business based on different businesses. There are many MySQL storage engines, and different databases and tables support different storage engines, we recommend that you use the same storage engine for the same database because the join operation between tables of different storage engines is slow.
Common recommendations: Innodb, non-special scenarios, the Innodb Storage engine can generally meet requirements
If you want to write large data into batch read operations: TokuDB
InfiniDB/Infobright can be used for OLAP.
If the data volume is small, it requires fast speed and no persistence requirement: Memory
Do not select the MyISAM storage engine as far as possible: Because the MyISAM storage engine can only use a single CPU, the memory can only use 4 GB, the memory only has indexes, and the concurrency capability is poor.
This article permanently updates the link address: