Introduction to four common storage engines in MySQL

The introduction of four kinds of engines commonly used by MySQL

(1): MyISAM Storage Engine:

Does not support transactions, and foreign keys are not supported, the advantage is that access is fast, the transaction integrity is not required or Select,insert-based applications can basically use this engine to create a table

Support 3 different storage formats: static table; dynamic table; Compact table

Static tables: The fields in the table are non-variable length fields, so that each record is a fixed length, the advantages of storage is very fast, easy to cache, the failure is easy to recover; The disadvantage is that the space occupied is usually more than the dynamic table (because the storage will be defined by the width of the column) PS: When fetching data, The default is to remove the space after the field, if you do not notice the data itself with the space will be ignored.

Dynamic tables: Records are not fixed-length, so the advantage of storage is that it takes up relatively little space; disadvantages: Frequent updates, deletion of data are prone to fragmentation, and regular optimize table or MYISAMCHK-R commands are required to improve performance

Compressed tables: Because each record is individually compressed, only very small access expenses

(2) InnoDB storage Engine *

The storage engine provides transactional security with commit, rollback, and crash resiliency. But compared to the MyISAM engine, write processing is less efficient and consumes more disk space to preserve data and indexes.
Features of the INNODB storage Engine: Support for auto-grow columns, support for FOREIGN KEY constraints

(3): Memory storage Engine

The memory storage engine creates tables using the content that exists in the RAM. Each memory meter only actually corresponds to one disk file, in the format. frm. The memory type of table access is very fast because its data is in memory and the hash index is used by default, but once the service is closed, the data in the table is lost.
Memory Storage Engine table can choose to use Btree index or hash index, two different types of indexes have their different use range

Hash Index Advantages:
Hash index structure of the particularity, its retrieval efficiency is very high, index retrieval can be located at once, unlike B-tree index need from the root node to the side point, and finally access to the page node so many IO access, so the Hash index query efficiency is much higher than the B-tree index.
Hash index disadvantage: Then not accurate search, it is also obvious, because the hash algorithm is based on the equivalent calculation, so for "like" and other scopes to find the hash index is invalid, not supported;

Memory type storage engine is mainly used for the code table which changes infrequently, or as the intermediate result table of statistic operation, it is convenient to analyze the intermediate result efficiently and get the final statistic result. It is prudent to update the storage engine's memory tables because the data is not actually written to disk, so be sure to consider how to obtain the modified data after the next service restart.

(4) Merge storage engine

The merge storage engine is a combination of a set of MyISAM tables that must be structurally identical, that the merge table itself does not have data, that the merge type table can be queried, updated, and deleted, and that these operations are actually performed on the internal MyISAM table.

Introduction to four common storage engines in MySQL