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

This article will explain in detail what the commonly used storage engine of mysql is. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

The storage engines in MySql include MyISAM engine, MyISAM Merge engine, InnoDB engine, memory (heap) engine and archive engine.

Storage engine in MySql

MyISAM engine

This kind of engine was first provided by mysql, and it can be divided into three types: static MyISAM, dynamic MyISAM and compressed MyISAM:

(1) static MyISAM: if the length of each data column in the data table is fixed in advance, the server will automatically select this table type. Because each record in the data table occupies the same space, this kind of table access and update is very efficient. Recovery is also easier to do when data is corrupted.

(2) dynamic MyISAM: if a varchar, xxxtext, or xxxBLOB field appears in the data table, the server will automatically select this table type. Compared with the static MyISAM, the storage space of this table is relatively small, but because the length of each record is different, the data in the data table may be stored in memory after many modifications, resulting in a decline in execution efficiency. At the same time, there may also be a lot of fragmentation in memory. Therefore, this type of table is often defragmented with optimize table commands or optimization tools.

(3) Compression MyISAM: both types of tables mentioned above can be compressed with the myisamchk tool. This type of table further reduces the storage footprint, but the table cannot be modified after compression. In addition, because the data is compressed, this kind of table needs to be decompressed in advance when it is read.

However, no matter what kind of MyISAM table it is, it currently does not support transactions, row-level locks and foreign key constraints.

MyISAM Merge engine

This type is a variant of the MyISAM type. A merge table is a combination of several identical MyISAM tables into a virtual table. It is often used in logs and data warehouses.

InnoDB engine

The InnoDB table type can be seen as a further update to MyISAM, providing the capabilities of transactions, row-level locking mechanisms, and foreign key constraints.

Memory (heap) engine

This type of data table exists only in memory. It uses a hash index, so the data is accessed very quickly. Because it exists in memory, this type is often used in temporary tables.

Archive engine

This type only supports select and insert statements, and does not support indexes. It is often used in logging and aggregation analysis.

About mysql commonly used storage engine what is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.