MySQL engine comparison of various engines introduced

1. What is a storage engine?

The storage engine is similar to recorded video files, can be converted into different formats, such as Mp4,avi, and stored on our disk will also exist in different types of file systems such as: Windows common Ntfs,fat32 and so on. Ext3,ext4 that exists in a Linux system, XFS, but what is presented to the customer is the same, but the internal way of storage is different. The intuitive difference is that the clarity is different from the space occupied.

Analogy to, is the database table data stored in the database and disk system format characteristics, different storage engine access, engine function, occupy space size, read performance, etc. may be different.

2.MySQL storage Engine is an important part of MySQL database, the most commonly used for MyISAM and InnoDB two kinds of MySQL storage engine structure:

3. Introduction of common engines:

1). What is the MyISAM engine?
The MyISAM engine is the default storage engine for the MySQL relational database management system (MySQL5.5.5), a MySQL table storage structure that extends many useful functions from the old ISAM code.
At the time of storage, each MyISAM table corresponds to three files on the hard disk. So three files have the same file name, but the extension is not the same, there are different uses:. frm file save table definition, this file is not part of the MyISAM engine, but a part of the server. MyD Save the table data;. Myi is the index file for the table. . MyD and. Myi are the key points of MyISAM:

2). What is the InnoDB engine?

The InnoDB engine is another important engine of the MySQL database. and is becoming the current MySQL AB version of the release of the standard, is included in all binary installation package. Compared to other storage engines, the INNODB engine has the advantage of supporting acid-compliant transactions ( Similar to PostgreSQL), as well as parameter integrity (with foreign keys), and so on. Now innobase implement dual authentication authorization. MySQL5.5.5 The default storage engine is the InnoDB engine.

3. Other storage engines: see table below

4. Advantages and disadvantages of each engine:

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, which 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 large amounts of historically, archived, or security audit information that is rarely referenced.

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, also requires the highest uptime and availability.

Other : other storage engines include CSV (referencing a comma-separated file used as a database table), blackhole (used to temporarily disallow application input to the database), and Example engine (can help 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.

MySQL engine comparison of various engines introduced