MySQL storage engine features and selection suggestions

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: