Summary of Mysql storage Engine Features

Summary of Mysql storage Engine Features

Features of several common storage engines

Next we will focus on several common storage engines and compare the differences and recommended usage methods between different 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	Supported
Index Cache	Supported		Supported	Supported
Data Compression	Supported				Supported
Space usage	Low	Low	N/	High	Very low
Memory usage	Low	Low	Moderate	High	Low
Batch insert speed	High	High	High	Low	Very high
Supports Foreign keys				Supported

The two most commonly used storage engines:

• Myisam is the default storage engine of Mysql. When creating a new table without specifying the storage engine for the new table, Myisam is used by default. 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 (MYData, storage data), and. MYI (MYIndex, storage index ). Data Files and index files can be placed in different directories, and I/O is evenly distributed for faster speed.

• The InnoDB Storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared with the storage engine of Myisam, InnoDB writes less efficiently and occupies more disk space to retain data and indexes.

How to select an appropriate storage engine

Standard selection: select a suitable storage engine based on application characteristics. For complex application systems, you can select multiple storage engines for combination based on the actual situation.

The following is the applicable environment for common storage engines:

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

2. InnoDB: used for transaction processing applications. It has many features, including ACID transaction support.

3. Memory: stores all data in RAM and provides extremely fast access in environments where you need to quickly search for references and other similar data.

4. Merge: Allows MySQL DBAs or developers to logically combine a series of equivalent MyISAM tables and reference them as one object. It is suitable for VLDB environments such as data warehousing.

Briefly describe several MYSQL database storage engines and briefly describe their features

MySQL has multiple storage engines. Each storage engine has its own advantages and disadvantages. You can select the preferred one:
MyISAM, InnoDB, MERGE, MEMORY (HEAP), BDB (BerkeleyDB), EXAMPLE, FEDERATED, ARCHIVE, CSV, and BLACKHOLE.
MySQL supports several storage engines as processors for different table types. The MySQL storage engine includes the engine for processing transaction security tables and the engine for processing non-transaction security tables:
· MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations. It is the default storage engine, unless you configure MySQL to use another engine by default.
· The MEMORY storage engine provides "in-MEMORY" tables. The MERGE storage engine allows a set to process the same MyISAM table as a separate table. Like MyISAM, MEMORY and MERGE storage engines process non-transaction tables. Both engines are included in MySQL by default.
Note: The MEMORY storage engine is officially identified as the HEAP engine.
· InnoDB and BDB storage engines provide transaction security tables. BDB is included in the MySQL-Max binary distribution version released for the operating system that supports it. InnoDB is also included in all MySQL 5.1 binary distributions by default. You can configure MySQL to allow or disable any engine as you like.
· The EXAMPLE storage engine is a "stub" engine, which does not do anything. You can use this engine to create tables, but no data is stored or retrieved from them. The purpose of this engine is to provide a service. In the MySQL source code example, it demonstrates how to start writing a new storage engine. Similarly, it is mainly interested in developers.
· NDB Cluster is a storage engine used by MySQL Cluster to split tables into multiple computers. It is provided in MySQL-Max 5.1 binary distribution. Currently, this storage engine is only supported by Linux, Solaris, and Mac OS X. In the future MySQL distribution version, we want to add support for this engine from other platforms, including Windows.
· The ARCHIVE storage engine is used to cover a large amount of data stored without indexing.
· The CSV storage engine stores data in text files in comma-separated formats.
· The BLACKHOLE storage engine accepts but does not store data, and retrieval always returns an empty set.
· FEDERATED storage engine stores data in a remote database. In MySQL 5.1, it only works with MySQL and uses the MySQL C Client API. In future distributions, we want to connect it to another data source using another drive or client connection method.
MyISAM and InnoBD are commonly used.
 
What is the MySQL storage engine?

MySQL has multiple storage engines. Each storage engine has its own advantages and disadvantages. You can select the preferred one:

MyISAM, InnoDB, MERGE, MEMORY (HEAP), BDB (BerkeleyDB), EXAMPLE, FEDERATED, ARCHIVE, CSV, and BLACKHOLE.

MySQL supports several storage engines as processors for different table types. The MySQL storage engine includes the engine for processing transaction security tables and the engine for processing non-transaction security tables:

· MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations. It is the default storage engine, unless you configure MySQL to use another engine by default.

· The MEMORY storage engine provides "in-MEMORY" tables. The MERGE storage engine allows a set to process the same MyISAM table as a separate table. Like MyISAM, MEMORY and MERGE storage engines process non-transaction tables. Both engines are included in MySQL by default.

Note: The MEMORY storage engine is officially identified as the HEAP engine.

· InnoDB and BDB storage engines provide transaction security tables. BDB is included in the MySQL-Max binary distribution version released for the operating system that supports it. InnoDB is also included in all MySQL 5.1 binary distributions by default. You can configure MySQL to allow or disable any engine as you like.

· The EXAMPLE storage engine is a "stub" engine, which does not do anything. You can use this engine to create tables, but no data is stored or retrieved from them. The purpose of this engine is to provide a service. In the MySQL source code example, it demonstrates how to start writing a new storage engine. Similarly, it is mainly interested in developers.

· NDB Cluster is a storage engine used by MySQL Cluster to split tables into multiple computers. It is provided in MySQL-Max 5.1 binary distribution. Currently, this storage engine is only supported by Linux, Solaris, and Mac OS X. In the future MySQL distribution version, we want to add support for this engine from other platforms, including Windows.

· The ARCHIVE storage engine is used to cover a large amount of data stored without indexing.

· The CSV storage engine stores data in text files in comma-separated formats.

· The BLACKHOLE storage engine accepts but does not store data, and retrieval always returns an empty set.

· FEDERATED storage engine stores data in a remote database. In MySQL 5.1, it only works with MySQL and uses the MySQL C Client API. In future distributions, we want to connect it to another data source using another drive or client connection method.

MyISAM and InnoBD are commonly used.