Application and features of the MySQL storage engine

The following articles mainly describe the actual application of the MySQL storage engine and the unique characteristics of the main storage engines in the MySQL database, I hope that you will be helpful when selecting the database storage engine after reading this article. The specific content of this article is as follows. MySQL has multiple storage engines: MyISAM, InnoDB, MERGE, MEMORY (HEAP), and B

The following articles mainly describe the actual application of the MySQL storage engine and the unique characteristics of the main storage engines in the MySQL database, I hope that you will be helpful when selecting the database storage engine after reading this article. The specific content of this article is as follows. MySQL has multiple storage engines: MyISAM, InnoDB, MERGE, MEMORY (HEAP), and B

The following articles mainly describe the actual application of the MySQL storage engine and the unique characteristics of the main storage engines in the MySQL database, I hope that you will be helpful when selecting the database storage engine after reading this article. The specific content of this article is as follows.

MySQL has multiple storage engines:

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 MySQL 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.

The 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 that 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 store a large amount of data without indexing.

The CSV storage engine stores data in text files in a comma-separated format.

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.

When creating a new TABLE, you can add an ENGINE or TYPE option to the create table statement to tell MySQL what TYPE of TABLE you want to CREATE:

 
 

  
  
CREATE TABLE t (i INT) ENGINE = INNODB; 
  
  
CREATE TABLE t (i INT) TYPE = MEMORY; 
 
 

Although TYPE is still supported in MySQL 5.1, ENGINE is the preferred term.

How to select the storage engine that best suits you?

The following storage engines are the most commonly used:

MyISAM: the default MySQL plug-in storage engine, which is one of the most commonly used MySQL storage engines in Web, data warehousing, and other application environments. Note: by changing the STORAGE_ENGINE configuration variables, you can easily change the default storage engine of the MySQL server.

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

BDB: it can replace the transaction engine of InnoDB and supports COMMIT, ROLLBACK, and other transaction features.

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.

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.

Archive: provides a perfect solution for storing and retrieving a large number of rarely referenced historical, archived, or security audit information.

Federated: connects multiple separated MySQL servers to create a logical database from multiple physical servers. It is very suitable for distributed environments or data mart environments.

Cluster/NDB: MySQL's Cluster-based database engine is especially suitable for applications with high-performance search requirements. Such search requirements also require the highest normal working time and availability.

Other: Other storage engines include CSV (referencing files separated by commas as database tables), Blackhole (used to temporarily prohibit database application input ), and the Example engine (which can help you quickly create a custom plug-in storage engine ).

Remember that you do not have to use the same storage engine for the entire server or solution. You can use different MySQL storage engines for each table in the solution. This is very important.

 
 

  
  
mysql> show engines; 
  
  
+————+———+—————————————————————-+ 
  
  
| Engine | Support | Comment | 
  
  
+————+———+—————————————————————-+ 
  
  
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | 
  
  
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | 
  
  
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | 
  
  
| BerkeleyDB | NO | Supports transactions and page-level locking | 
  
  
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | 
  
  
| EXAMPLE | NO | Example storage engine | 
  
  
| ARCHIVE | NO | Archive storage engine | 
  
  
| CSV | NO | CSV storage engine | 
  
  
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables | 
  
  
| FEDERATED | NO | Federated MySQL storage engine | 
  
  
| MRG_MYISAM | YES | Collection of identical MyISAM tables | 
  
  
| ISAM | NO | Obsolete storage engine | 
  
  
+————+———+—————————————————————-+ 
 
 

The above content is an introduction to how to select the appropriate MySQL storage engine and the features of the main MySQL storage engines. I hope you will get some benefits.