Mysql Storage Engine Features summary _mysql

Features of several common storage engines

Here we focus on several common storage engines and compare the differences between each storage engine and how they are recommended for use.

features	Myisam	BDB	Memory	InnoDB	Archive
Storage limits	No	No	Yes	64TB	No
Transaction security		Support		Support
Lock mechanism	Table lock	Page locks	Table lock	Row lock	Row lock
B-Tree Index	Support	Support	Support	Support
Hash index			Support	Support
Full-text indexing	Support
Clustered index				Support
Data caching			Support	Support
Index cache	Support		Support	Support
Data can be compressed	Support				Support
Space use	Low	Low	N/A	High	Very low
Memory usage	Low	Low	Medium	High	Low
The speed of bulk inserts	High	High	High	Low	Very high
Support for foreign keys				Support

The 2 most commonly used storage engines:

MyISAM is the default storage engine for MySQL. When create creates a new table, MyISAM is used by default when no storage engine is specified for the new table. Each myisam is stored on disk as three files. The file name is the same as the table name, and the extension is. frm (storage table definition),. MyD (MYData, storing data),. Myi (myindex, storage index). Data files and index files can be placed in different directories, with an average distribution of IO, to achieve faster speeds.

The InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared to the MyISAM storage engine, InnoDB writes less efficiently and consumes more disk space to preserve data and indexes.

How to choose the right storage engine

Selection criteria: According to the application characteristics of the appropriate storage engine, for the complex application system can choose a variety of storage engines to combine.

The following are the applicable environments for common storage engines:

1.MyISAM: The default MySQL plug-in storage engine, which is one of the most commonly used storage engines in the Web, data warehousing, and other application environments

2.InnoDB: For transaction processing applications, with many features, including acid transaction support.

3.Memory: Keep all data in RAM and provide extremely fast access in environments where you need to quickly find references and other similar data.

4.Merge: Allows MySQL dba or developer to combine a series of equivalent MyISAM tables logically and reference them as 1 objects. It is ideal for VLDB environments such as data warehousing.