A plethora of MySQL storage engines, which is your dish?

I. MySQL storage engine classification and application

MyISAM, InnoDB, MERGE, MEMORY (HEAP), BDB (BerkeleyDB), EXAMPLE, Federated, ARCHIVE, CSV, Blackhole.

MySQL supports several storage engines as a processor 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 tables:

1.MyISAM managing non-transactional 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 by default using a different engine. MySQL before the 5.5.5 version is using MyISAM as the default storage engine;

MyISAM Storage Engine has high insert, query speed (full-text index), but does not support transaction –> highlight Advantage Retrieval Fast

Each MyISAM table is stored on disk as three files

. frm (definition of metadata, table structure)

. MYD (data)

. MYI (Index)

Direct copy of three files can be ported to other MySQL on the table

2.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, the memory and merge storage engines handle non-transactional tables, both of which are included by default in MySQL.

Note: The memory storage engine is formally identified as the heap engine.

All the data in the memory storage engine is placed in a storage engine with high insertion, update and query efficiency. Its table contains the. frm data

. frm (meta data, table structure definition, data)

3.InnoDB and BDB storage engines provide transaction-safe tables

BDB is included in the Mysql-max binary distribution released for the operating system that supports it. InnoDB is also included by default in all MySQL 5.1 binary distributions, and you can configure MySQL to allow or disallow any engine as you prefer. Beginning with version 5.5.5 , MySQL uses InnoDB as the default storage engine.

The InnoDB storage engine is the preferred engine for transactional databases, support for row-level locks, foreign keys, MVCC multi-version concurrency mechanisms –> Outstanding benefits support transactions, and each NNNODB table includes the following

. frm (definition of metadata, table structure)

. IBD (Storage table structure and data)

4.EXAMPLE Storage Engine

This is a "stub" engine and it doesn't do anything. You can use this engine to create a table, but no data is stored in it or retrieved from it. The purpose of this engine is to serve as an example in the MySQL source code, which demonstrates how to start writing a new storage engine. Similarly, its main interest is to developers.

The following storage engine applications are not as extensive as they are described.

5.BLACKHOLE Storage Engine

The data is accepted but not stored, and the retrieval always returns an empty set.

6.NDB cluster storage Engine

is the storage engine used by MySQL cluster to implement tables that are partitioned into 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 a future MySQL distribution, we want to add support for this engine from other platforms, including Windows.

7.ARCHIVE Storage Engine

is used to place a large amount of data that is stored in a very small way without indexing.

8.CSV Storage Engine

Stores data in a comma-delimited format in a text file.

9.FEDERATED Storage Engine

The data exists in the remote database. In MySQL 5.1, it works only with MySQL, using the MySQL C Client API. In future distributions, we want to have it connect to another data source using a different drive or client connection method.

Two. How to specify the storage engine

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

CREATE TABLE T (i INT) ENGINE = INNODB;

CREATE TABLE T (i INT) TYPE = MEMORY;

Although the type is still supported in MySQL 5.1, the engine is now the preferred term.

Three. Related query instructions

3.1 Query installed MySQL, what storage engine is supported, and what is the current default storage engine

MySQL command line mode execution

> Show engines;

+ ———— + ——— +

| Engine | Support |

+ ———— + ——— +

| MyISAM | DEFAULT |

| InnoDB | YES |

| Blackhole | YES |

| CSV | YES |

| MEMORY | YES |

| Federated | NO |

| ARCHIVE | YES |

| Mrg_myisam | YES |

+ ———— + ——— +

3.2 Viewing what a table's storage engine is

> Show create TABLE User; Displays the user creation statement and the engine used

> Show Table Status "from Db_name" where name= ' user '; Displays the current state of the user table and uses the engine

> Show table status like '%user% ';

3.3 Modifying a table's storage engine

The following command modifies the storage engine of the user table to InnoDB

>alter table User engine= ' InnoDB ';

3.4 Modifying the default storage engine

For example, modify the default storage engine to InnoDB, edit the MySQL configuration file, and add the following parameters:

# VI/ETC/MY.CNF

Default-storage-engine=innodb

# Service Mysqld Restart

Restart the MySQL service for changes to take effect

Four. How do I choose the most suitable storage engine for 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 storage engines in the Web, data warehousing, and other application environments. Note that it is easy to change the default storage engine of the MySQL server by changing the storage_engine configuration variable.

& 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 group together a series of equivalent MyISAM tables and reference them as 1 objects. Ideal for VLDB environments such as data warehousing.

& Archive: Provides the perfect solution for storing and retrieving a large number of rarely cited historical, archival, or security audit information.

& Federated: The ability to link multiple separate MySQL servers to 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 engine includes CSV (referencing a comma-delimited file used as a database table), blackhole (used to temporarily disable application input to the database), and the example engine, which helps to quickly create a custom plug-in storage engine ).

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.

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
It Technology Services Alliance is committed to sharing the IT industry classic technical articles, operational Knowledge Wisdom Summary, Enterprise Information Network construction program, enterprise-class hybrid cloud private cloud solution customization.

Welcome to the public number " It Technology Services Alliance ", pay attention to the public number, get the author ten years of Technical Essence collection information up to 2T, first-come first-served!

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/72/90/wKioL1Xm1ZOhCGafAABgc0oHa9o146.jpg "title=" QRCode _for_gh_030292027e2b_344 (1). jpg "alt=" wkiol1xm1zohcgafaabgc0oha9o146.jpg "/>

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

This article is from the "Technical Alliance blog" blog, please be sure to keep this source http://ittsunion.blog.51cto.com/798766/1690904

A plethora of MySQL storage engines, which is your dish?