MySQL Storage engine comparison

MySQL, as an open source free database, is often used in regular projects, while our focus in the project is generally designed to use the database rather than MySQL itself, so when it comes to MySQL's storage engine, it is not generally known, This is a summary of the MySQL storage engine under the Web-related literature review.

What is a storage engine

In a database management system (DBMS), a database engine (or storage engine) refers to a software component that is used to perform operations such as pruning and checking data. Most database management systems provide their own application interfaces for users to interact with the engine. This is an explanation from the wiki that the storage engine is the method that the database uses to manipulate the data, and different approaches may have different points of interest and effects. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, the type that stores and operates this table).

There is only one storage engine in a database such as Oracle and SQL Server, and all data storage management mechanisms are the same. The MySQL database provides a variety of storage engines. Users can choose different storage engines for the data table according to different requirements, and users can write their own storage engine according to their own needs.

The storage engine in MySQL

1. MyISAM: This engine was first provided by MySQL. This engine can also be divided into static MyISAM, dynamic MyISAM and compression MyISAM three kinds:

• Static MyISAM : If the length of each data column in the datasheet is pre-fixed, the server will automatically select this type of table. Because each record in the data table occupies the same amount of space, the table accesses and updates are highly efficient. When data is compromised, recovery is easier to do.
• Dynamic MyISAM : The server will automatically select this table type if the varchar, xxxtext, or Xxxblob fields appear in the datasheet. Compared with static MyISAM, this kind of table storage space is relatively small, but because of the length of each record is different, so the data in the data table can be stored in memory after multiple modifications, resulting in a decrease in execution efficiency. Also, there may be a lot of fragmentation in memory. Therefore, this type of table is often defragmented with the Optimize table command or the Optimization tool.
• Compression MyISAM : The two types of tables mentioned above can be compressed with the Myisamchk tool. This type of table further reduces the amount of storage consumed, but the table can no longer be modified after it is compressed. In addition, because it is compressed data, such a table should be read to the first time to extract the rows.

However, regardless of the MyISAM table, it does not currently support transactional, row-level, and foreign key constraints.

　　2. MyISAM Merge Engine: This type is a variant of the MyISAM type. Merging tables is the merging of several identical MyISAM tables into a single virtual table. Often applied to logs and data warehouses.

3. InnoDB:The InnoDB table type can be seen as a further update to the MyISAM product, which provides the functionality of transaction, row-level locking mechanisms, and foreign key constraints.

4. Memory (heap): This type of data table only exists in memory. It uses a hash index, so the data is accessed very quickly. Because it exists in memory, this type is often applied to temporary tables.

5. Archive: This type only supports SELECT and INSERT statements, and does not support indexing. Often applied to logging and aggregation analysis.

　　6. CSV: This type of storage engine uses a colon-separated value format to present data on a text file. We can use the CSV storage engine to easily import and export data in CSV format or to exchange data with other software.

Of course, MySQL supports more than just a few types of tables.

How to choose (the advantages and disadvantages between storage engines)

MySQL offers a variety of storage engines that take different design priorities, taking into account different use cases. So in order to make efficient use of these storage engines, I looked for a list of the advantages and disadvantages of several storage engines from the MySQL website, as follows:

Storage Engine Feature Table

--reference

Wiki-http://en.wikipedia.org/wiki/database_engine

Blog-http://www.cnblogs.com/lina1006/archive/2011/04/29/2032894.html

Mysql-https://dev.mysql.com/doc/refman/5.1/en/storage-engines.html

MySQL Storage engine comparison