How to choose the appropriate Mysql storage engine _mysql

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

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

The memory storage engine provides an in-memory table. The merge storage engine allows the collection to be processed by the same MyISAM table as a separate table. Like MyISAM, memory and the merge storage engine handle non-transaction tables, both of which are included by default in MySQL.

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 that is released for the operating system that supports it. InnoDB is also included in all MySQL 5.1 binary distributions by default, and you can configure MySQL to allow or disable any engine as you prefer.

The example storage engine is a "stub" engine, and it does nothing. You can use this engine to create a table, but no data is stored in or retrieved from it. The purpose of this engine is to serve an example in the MySQL source code, which demonstrates how to start writing a new storage engine. Again, its main interest is for developers.

NDB cluster is a storage engine that is used by MySQL cluster to implement tables that are split onto multiple computers. It is available in the Mysql-max 5.1 binary distribution. This storage engine is currently supported only by Linux, Solaris, and Mac OS X. In the future MySQL distribution, we want to add other platforms to support this engine, including Windows.

The archive storage engine is used to easily overwrite large amounts of stored data without indexing.

The CSV storage engine stores data in a comma-delimited format in a text file.

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

The Federated storage Engine has the data in the remote database. In MySQL 5.1, it works only with MySQL, using the MySQL C Client API. In a future distribution, we would like to have it connect to another data source using a different drive or client connection method.

When you create a new table, you can tell MySQL what type of table you want to create by adding a engine or type option to the CREATE TABLE statement:

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 now the preferred term.

How do you choose the storage engine that best suits you?

The following storage engines are most commonly used:

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. Note that by changing the storage_engine configuration variable, you can easily change the default storage engine for the MySQL server.

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

BDB: An alternative to the INNODB transaction engine that supports commit, rollback, and other transactional features.

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.

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

Archive: Provides a perfect solution for storing and retrieving a large number of rarely-referenced histories, archives, or security audit information.

Federated: The ability to link multiple separate MySQL servers and create a logical database from multiple physical servers. Ideal for distributed environments or data mart environments.

Cluster/ndb:mysql's clustered database engine, especially for applications with high performance lookup requirements, requires the highest uptime and availability.

Other: The other storage engines include CSV (referencing a comma-separated file used as a database table), blackhole (used to temporarily prohibit application input to the database), and the example engine, which can help you create a custom plug-in storage engine quickly.

Keep in mind that you don't have to use the same storage engine for the entire server or scenario, and you can use a different storage engine for each table in the scenario, which is important.