MySQL storage engine Selection

1.1 MySQL storage engine Overview

MySQL supports multiple storage engines. When processing different types of applications, you can select different storage engines to improve application efficiency or provide flexible storage.

Including MyISAM, InnoDB, bdb, memory, merge, example, NDB cluster, archive, and CSV. Among them, InnoDB and bdb provide security transaction tables, and other engines are non-transaction security tables.

 

1.2 features of various storage engines

Features	MyISAM	Bdb	Memory	InnoDB	Archive
Storage restrictions	No	No	Yes	64 TB	No
Transaction Security		Supported		Supported
Lock Mechanism	Table lock	Page lock	Table lock	Row lock	Row lock
B-Tree Index	Supported	Supported	Supported	Supported
Hash Index			Supported	Supported
Full-text index	Supported
Cluster Index				Supported
Data Cache				Supported
Index Cache			Supported	Supported
Data Compression	Supported				Supported
Space usage	Low	Low	No space consumption	High	Low
Memory usage	Low	Low	Moderate	High	Very high
Batch insert speed	High	High	High	Low	Very high
Supports Foreign keys				Supported

 

The most common storage engine:

1.
MyISAM storage engine: Each MyISAM is stored as three files on the disk. The file names are the same as the table names. The extensions are. frm (storage table definition),. MYD (storage data), and. myi (storage index ). Data Files and index files can be placed in different directories, and I/O is evenly distributed to achieve faster speed.

2.
InnoDB Storage engine: Provides transaction security capabilities such as commit, rollback, and crash recovery. Compared with MyISAM, InnoDB has a lower write efficiency and occupies more disk space to retain data and indexes.

 

1.3
How to select an appropriate engine

The following is the applicable environment for common storage engines:

1.
MyISAM: it is the most commonly used storage engine in Web, data warehousing, and other application environments.

2.
InnoDB: used for transaction processing applications, with more features, including ACID transaction features.

3.
Memory: all data is stored in Ram and can be accessed quickly when you need to quickly search for references and other similar data environments.

4.
Merge: Allows MySQL DBA (Database Administrator) or developers to logically combine a series of equivalent MyISAM tables and reference them as one object. Suitable for world warehousing and other environments.