MySQL storage engine _ MySQL-mysql tutorial

MySQL storage engine bitsCN.com

The storage engine, to put it bluntly, is how to store data, how to index the stored data, and how to update and query data. Because in relational databases, data is stored as tables, the storage engine can also be called the table type (that is, the type of storing and operating the table ).

There is only one storage engine in Oracle, SQL Server, and other databases. all data storage management mechanisms are the same. MySql databases provide multiple storage engines. You can select different storage engines for data tables based on your needs. you can also write your own storage engines based on your needs.

MySql has the following storage engines: MyISAM and InnoDB.

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

◦ Static MyISAM: If the length of each data column in the data table is pre-fixed, the server automatically selects this table type. Because each record in a data table occupies the same space, the efficiency of table access and update is very high. When data is damaged, recovery is easier.

◦ Dynamic MyISAM: If the varchar, xxxtext, or xxxBLOB fields appear in the data table, the server automatically selects this table type. Compared with static MyISAM, this table has a small storage space, but because the length of each record is different, after data is modified multiple times, the data in the data table may be stored discretely in the memory, this leads to a decrease in execution efficiency. At the same time, many fragments may occur in the memory. Therefore, this type of table often uses the optimize table command or optimization tool for fragment.

Compression MyISAM: the two types of tables mentioned above can be compressed using myisamchk. This type of table further reduces the storage used, but the table cannot be modified after compression. In addition, because the data is compressed, such tables must be decompressed first.

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

2. InnoDB: The InnoDB table type can be viewed as a product for further updating MyISAM. It provides the functions of transactions, row-level locks, and foreign key constraints.

3. MyISAM Merge engine: this type is a variant of the MyISAM type. Merging tables combines several identical MyISAM tables into a virtual table. It is often used in logs and data warehouses.

4. memory (heap): This type of data table only exists in memory. It uses hash indexes, so the data access speed is very fast. Because it exists in the memory, this type is often used in temporary tables.

5. archive: this type only supports select and insert statements, and does not support indexes. It is often used in logging and aggregate analysis.

BitsCN.com