A brief discussion of four commonly used storage engines in MySQL

Introduction to four commonly used MySQL engines

(1): MyISAM storage engine:

It does not support transactions or foreign keys. Its advantage is fast access speed. Applications that do not require transaction integrity or are mainly select and insert can basically use this engine to create tables.

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

Static table: The fields in the table are all non-variable length fields, so each record is of fixed length. The advantages are very fast storage, easy caching, and easy recovery in case of failure. The disadvantage is that it usually takes up more space than a dynamic table (because the spaces are filled according to the column width definition during storage). PS: When retrieving data, the spaces after 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 record length is not fixed. The advantage of this storage is that it takes up relatively less space. Disadvantage: Frequent updates and deletions of data can easily cause fragmentation. You need to regularly execute the OPTIMIZE TABLE or myisamchk -r command to improve performance.

Compressed tables: Because each record is compressed individually, there is very little access overhead.

(2) InnoDB storage engine*

The storage engine provides transaction safety with commit, rollback, and crash recovery capabilities. However, compared to the MyISAM engine, the write processing efficiency is lower and it will take up more disk space to retain data and indexes.
Features of the InnoDB storage engine: support for automatic growth columns and foreign key constraints

(3): MEMORY storage engine

The Memory storage engine creates tables using content that exists in memory. Each memory table actually corresponds to only one disk file, and the format is .frm. Memory type tables are very fast to access because their data is stored in memory and uses HASH index by default, but once the service is shut down, the data in the table will be lost.
Tables with the MEMORY storage engine can choose to use BTREE indexes or HASH indexes. The two different types of indexes have different usage scopes.

Hash index advantages:
Due to the special structure of Hash index, its retrieval efficiency is very high. The index retrieval can be located once, unlike B-Tree index which needs multiple IO accesses from root node to branch node and finally to page node. Therefore, the query efficiency of Hash index is much higher than that of B-Tree index.
Disadvantages of hash index: What about inexact search? This is also obvious. Since hash algorithm is based on equal value calculation, hash index is invalid and does not support range search such as "like";

The Memory type storage engine is mainly used for code tables whose contents do not change frequently, or as intermediate result tables for statistical operations, so as to efficiently analyze the intermediate results and obtain the 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

The Merge storage engine is a combination of a group of MyISAM tables. These MyISAM tables must have exactly the same structure. The merge table itself does not have data. The merge type table can be queried, updated, and deleted. These operations are actually performed on the internal MyISAM table.

The above is a detailed explanation and integration of the four commonly used storage engines in MySQL introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!