Introduction to selecting mysql storage engine standard _ MySQL

This article describes how to select a mysql Storage Engine. it explains how to use the storage engine and the features of several common engines. For more information, see Introduction to main storage engines

1. InnoDB storage engine

InnoDB is the default transaction engine of MySQL. it is designed to process a large number of short-term (short-lived) transactions. Unless other storage engines are needed for special reasons, the InnoDB engine should be given priority.
We recommend that you use MySQL5.5 or later versions because the InnoDB engine performance of this version and later versions is better.
In versions later than MySQL, InnoDB can store the data and indexes of each table in a separate file. This has obvious advantages in operations such as copy backup crash recovery. You can enable this function by adding innodb_file_per_table to my. cnf. As follows:

InnoDB adopts MVCC to support high concurrency and achieves four standard isolation levels. The default value is repeatable read, and the next-key locking policy is used to prevent Phantom READ. (The isolation level of transactions and transactions is another big question. make up your own network ).

InnoDB is based on clustering indexes, which have high performance on primary key query. However, its secondary index (non-primary key index) must contain primary key columns. Therefore, if the primary key column is large, all other indexes will be large. Therefore, if there are many indexes on the table, the primary key should be as small as possible.

The storage format of InnoDB is platform-independent. Data and index files can be copied from Intel platform to Sun or other platforms.

InnoDB supports real hot backup through some mechanisms and tools. Other storage engines of MySQL do not support hot backup.

2. MyISAM storage engine

MyISAM provides many features, including full-text indexing, compression, and spatial functions (GIS). However, MyISAM does not support transactions and row-level locks, there is no doubt that security cannot be recovered after a crash.

MyISAM stores the table in two files: data file and index file. MYD and. MYI are extension files.
Before MySQL 5.0, only 4G data can be processed, and TB data can be processed.

When the data is no longer modified, you can compress the MyISAM table and increase the read capability after compression, because the disk I/O is reduced.

3. Archive engine

The Archive storage engine only supports INSERT and SELECT operations, and does not support indexes before MySQL5.1.
Archive tables are suitable for log and data collection applications.
The Archive engine supports row-level locks and dedicated cache areas, so it can implement highly concurrent inserts, but it is not a thing-type engine, it is a simple engine optimized for high-speed insertion and compression.

Features of several common storage engines

Next we will focus on several common storage engines and compare the differences and recommended usage methods between different storage engines.

MostTwo storage engines are commonly used:
Myisam is the default storage engine of Mysql. When creating a new table without specifying the storage engine for the new table, Myisam is used by default. Each MyISAM is stored as three files on the disk. The file names are the same as the table names. the extensions are. frm (storage table definition),. MYD (MYData, storage data), and. MYI (MYIndex, storage index ). Data files and index files can be placed in different directories, and I/O is evenly distributed for faster speed.
The InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared with the storage engine of Myisam, InnoDB writes less efficiently and occupies more disk space to retain data and indexes.

How to select an appropriate storage engine

Select criteria:Select a suitable storage engine based on the application characteristics. you can select multiple storage engines for a complex application system based on the actual situation.

The following is the applicable environment for common storage engines:
MyISAM: the default MySQL plug-in storage engine, which is one of the most commonly used storage engines in Web, data warehousing, and other application environments.
InnoDB: used for transaction processing applications. it has many features, including ACID transaction support.
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.

The above is all the content of this article. I hope you will like it.