What are the commonly used storage engines in MySQL?

The storage engines in MySql are: 1. MyISAM engine; 2. MyISAM Merge engine; 3. InnoDB engine; 4. memory engine; 5. archive engine.

Storage engine in MySql

1. MyISAM engine

This engine is the earliest provided by mysql. This engine 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 They are all pre-fixed and the server will automatically select this table type. Because each record in the data table occupies the same space, the efficiency of table access and update is very high. When data is damaged, recovery is easier.

(2) Dynamic MyISAM: If varchar, xxxtext or xxxBLOB fields appear in the data table, the server will automatically select this table type. Compared with static MyISAM, the storage space of this kind of table is relatively small, but because the length of each record is different, after the data is modified multiple times, the data in the data table may be stored discretely in the memory, which in turn leads to a decrease in execution efficiency. At the same time, there may also be a lot of fragmentation in the memory. Therefore, this type of table should often be defragmented using the optimize table command or an optimization tool.

(3) Compress MyISAM: Both types of tables mentioned above can be compressed using the myisamchk tool. This type of table further reduces the storage footprint, but this type of table cannot be modified after compression. In addition, because it is compressed data, this kind of table must be decompressed first when reading.

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

2. MyISAM Merge engine

This type is a variant of the MyISAM type. Merging tables is to merge several identical MyISAM tables into a virtual table. Commonly used in logs and data warehouses.

3. InnoDB engine

The InnoDB table type can be regarded as a further updated product of MyISAM. It provides transactions, row-level locking mechanisms and foreign key constraints. Function.

4. Memory (heap) engine

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

5. Archive engine

This type only supports select and insert statements, and does not support indexes. Often used in logging and aggregate analysis.

The above is the detailed content of What are the commonly used storage engines in MySQL?. For more information, please follow other related articles on the PHP Chinese website!