Introducing the four engines in MySQL

1. MyISAM storage engine

does not support transactions or foreign keys. The advantage is fast access speed , applications that have no requirements for transaction integrity or that are mainly select and insert can basically use this engine to create tables

Supports 3 different storage formats, namely: static table; dynamic table; compressed table

Static table: The fields in the table are all non-variable length fields, so that each record has a fixed length. The advantages are very fast storage, easy to cache, and easy to recover in case of failure; the disadvantage is that it usually takes up more space than dynamic There are many tables (because the spaces will be filled according to the column width definition during storage) ps: When fetching data, the spaces behind the fields will be removed by default. If you are not careful, the spaces in the data itself will also be ignored.

Dynamic table: The records are not of fixed length. The advantage of such storage is that it takes up relatively less space. Disadvantages: Frequent updates and deletions of data are prone to fragmentation. You need to execute the OPTIMIZE TABLE or myisamchk-r command regularly. Improve performance

Compressed table: Because each record is compressed individually, there is only a very small access overhead

2. InnoDB storage engine

This storage engine provides transaction security with commit, rollback and crash recovery capabilities. However, compared with the MyISAM engine, the write processing efficiency will be worse, and it will take up more disk space to retain data and indexes.

Features of InnoDB storage engine: supports automatic growth of columns and foreign key constraints

3. Memory storage engine

Memory storage engine uses existing Create the table from the contents of memory. Each memory table actually corresponds to only one disk file, and the format is .frm. Memory type table access is very fast because its data is stored in memory and uses HASH index by default. However, once the service is shut down, the data in the table will be lost.

MEMORY storage engine tables can choose to use BTREE index or HASH index. The two different types of indexes have different usage ranges

Hash index advantages:

Hash index Due to the particularity of the structure, its retrieval efficiency is very high. The index retrieval can be located at one time. Unlike the B-Tree index, which requires multiple IO accesses from the root node to the branch node and finally to the page node, the query of the Hash index The efficiency is much higher than B-Tree index.

Disadvantages of Hash index: Inexact search is also obvious, because the hash algorithm is based on equal value calculation, so the hash index for range searches such as "like" is invalid and not supported;

## The #Memory type storage engine is mainly used for code tables whose content changes infrequently, or as an intermediate result table for statistical operations, to facilitate efficient analysis of intermediate results and obtain final statistical results. Be cautious when updating tables whose storage engine is memory, because the data is not actually written to the disk, so you must consider how to obtain the modified data after the next restart of the service.

4. Merge storage engine

Merge storage engine is a combination of a set of MyISAM tables. These MyISAM tables must have the same structure. The merge table itself has no data. Merge type tables can perform query, update, and delete operations. These operations are actually performed on the internal MyISAM table.

Recommended tutorial: "

MySQL Tutorial"

The above is the detailed content of Introducing the four engines in MySQL. For more information, please follow other related articles on the PHP Chinese website!