Talking about the standard _mysql of choosing Mysql storage Engine

Introduction to the primary storage engine

1, InnoDB storage engine

InnoDB is the default transaction engine for MySQL, which is designed to handle a large number of short-term (short-lived) transactions. Unless there are very specific reasons to use a different storage engine, the InnoDB engine should be given priority.
MySQL5.5 and later versions are recommended because the InnoDB engine performs better with this version and later versions.
In later versions of MySQL4.1, InnoDB can store data and indexes for each table in a separate file. This has a significant advantage in operations such as replication backup crash recovery. This function can be turned on by adding innodb_file_per_table to the my.cnf. As follows:

InnoDB uses MVCC to support high concurrency and implements four standard isolation levels. The default level is REPEATABLE READ (repeatable read) and prevents the occurrence of phantom reads through a gap lock (next-key locking) policy. (Transaction and transaction isolation levels are another big topic, and the respective network complements it).

InnoDB is based on clustered index, clustered index has high performance on primary key query. However, its two-level index (secondary index, non-primary key) must contain primary key columns, so if the primary key columns are large, all other indexes will be large. As a result, the primary key should be as small as possible with more indexes on the table.

The INNODB storage format is platform-independent and can replicate data and index files from Intel platforms to sun SPARC platforms or other platforms.

InnoDB supports real hot backups through a number of mechanisms and tools, and MySQL's other storage engines do not support hot backups.

2, MyISAM storage engine

MyISAM provides a large number of features, including Full-text indexing, compression, space functions (GIS), and so on, but MyISAM does not support transaction and row-level locks, there is no doubt that a failure after the crash can not be safely restored.

MyISAM will store the table in two files in: Data files and index files, respectively. MyD and. myi are extension names.
Before MySQL5.0, only 4G of data could be processed, and 256T data could be processed in 5.0.

When the data is no longer modified, the MyISAM table can be compressed, and the reading ability can be increased after compression, due to reduced disk I/O.

3. Archive engine

The archive storage engine only supports insert and select operations, and indexes are not supported before MySQL5.1.
The archive table is suitable for logging and data collection classes applications.
The archive engine supports row-level locks and dedicated buffers, so high concurrent inserts can be achieved, but it is not an object-type engine, but rather a simple engine optimized for high-speed inserts and compression.

Features of several common storage engines

Here we focus on several common storage engines and compare the differences between each storage engine and how they are recommended for use.

The 2 most commonly used storage engines:
MyISAM is the default storage engine for MySQL. When create creates a new table, MyISAM is used by default when no storage engine is specified for the new table. Each myisam is stored on disk as three files. The file name is the same as the table name, and the extension is. frm (storage table definition),. MyD (MYData, storing data),. Myi (myindex, storage index). Data files and index files can be placed in different directories, with an average distribution of IO, to achieve faster speeds.
The InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared to the MyISAM storage engine, InnoDB writes less efficiently and consumes more disk space to preserve data and indexes.

How to choose the right storage engine

selection criteria: According to the application characteristics of the appropriate storage engine, for the complex application system can choose a variety of storage engines to combine.

The following are the applicable environments for common storage engines:
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
InnoDB: For transactional applications, with many features, including acid transaction support.
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.

The above is the entire content of this article, I hope you can enjoy.