Shulou(Shulou.com)06/01 Report--

1. MyISAM storage engine

Do not support transactions, do not support foreign keys, the advantages are fast access, no requirements for transaction integrity, or select,insert-based applications can basically use this engine to create tables

Three different storage formats are supported: static table, dynamic table and compressed table.

Static table: the fields in the table are non-variable length fields, so that each record is of fixed length, and the advantages are stored very quickly, easy to cache, and easy to recover in the event of failure. The disadvantage is that it usually takes up more space than dynamic tables (because it will fill in the spaces according to the width of the column when storing) ps: when fetching data, the spaces after the fields will be removed by default, and the spaces in the data itself will be ignored if you are not careful.

Dynamic table: records are not of fixed length, so the advantage of storage is that it takes up relatively less space; disadvantages: frequent updates and deletions of data are prone to fragmentation, and regular OPTIMIZE TABLE or myisamchk-r commands are needed to improve performance.

Compressed table: because each record is compressed separately, there is only a very small access expense

2. InnoDB storage engine

The storage engine provides transaction security with commit, rollback, and crash recovery capabilities. But compared to the MyISAM engine, write processing is less efficient and takes up more disk space to retain data and indexes.

Features of InnoDB storage engine: support for automatic column growth and foreign key constraints

3. Memory storage engine

The Memory storage engine uses content that exists in memory to create tables. Each memory table actually corresponds to only one disk file in .frm format. Memory-type tables are accessed very quickly because their data is in memory and the HASH index is used by default, but once the service is turned off, the data in the table is lost.

Tables in MEMORY storage engine can choose to use BTREE index or HASH index, and the two different types of indexes have different scope of use.

Advantages of Hash index:

Because of the particularity of the Hash index structure, its retrieval efficiency is very high, and the index retrieval can be located at once, unlike the B-Tree index which needs to access the page node from the root node to the branch node for so many IO visits, so the query efficiency of the Hash index is much higher than that of the B-Tree index.

Disadvantages of Hash index: it is also obvious that imprecise search is based on hash algorithm, so it is invalid for "like" and other range search hash index, and does not support it.

The storage engine of Memory type is mainly used for the code table of which contents do not change frequently, or as the intermediate result table of statistical operation, which is convenient to analyze the intermediate results efficiently and get the final statistical results. Be careful when updating tables whose storage engine is memory, because the data is not actually written to disk, so be sure to consider how to get the modified data the next time the service is restarted.

4. Merge storage engine

Merge storage engine is a combination of a group of MyISAM tables, these MyISAM tables must have the same structure, the merge table itself has no data, the merge type table can be queried, updated, deleted, these operations are actually carried out on the internal MyISAM table.

These are the details of the four engines in MySQL. Please pay attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.