MySQL features of various storage engines and how to choose the storage engine

Features of several common storage engines

Here we focus on several common storage engines and compare the differences and recommended usage between each storage engine.

features	Myisam	BDB	Memory	InnoDB	Archive
Storage limits	No	No	Yes	64TB	No
Transaction security		Support		Support
Lock mechanism	Table lock	Page lock	Table lock	Row lock	Row lock
B-Tree Index	Support	Support	Support	Support
Hash index			Support	Support
Full-Text Indexing	Support
Cluster 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
Speed of BULK Insert	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 the storage engine for the new table is not specified. Each myisam is stored as three files on disk. The file name is the same as the table name, and the extension is. frm (store table definition), respectively. MYD (MYData, storing data),. MYI (myindex, storage index). Data files and index files can be placed in different directories, evenly distributed IO, for faster speeds.

• The InnoDB storage Engine provides transactional security with commit, rollback, and crash resiliency. However, compared to the MyISAM storage engine, InnoDB writes are less efficient and consume 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 be selected according to the actual situation of a variety of storage engine combinations.

The following are the applicable environments for common storage engines:

1. MyISAM: 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 transactional applications, with many features, including acid transaction support.

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

4. Merge: Allows a MySQL DBA or developer to logically group together a series of equivalent MyISAM tables and reference them as 1 objects. Ideal for VLDB environments such as data warehousing.

This article is from "Xiao Yang" blog, please be sure to keep this source http://aqiang.blog.51cto.com/6086626/1896096

MySQL features of various storage engines and how to choose the storage engine