Database Storage Engine

The concept of storage engine is mentioned in MySQL. In short, the storage engine is the type of the table. The storage engine of the database determines how the table is stored on the computer.

The show engines statement can view the storage engines supported by the MySQL database.

InnoDB Storage Engine

The InnoDB storage engine provides MySQL tables with things, rollback, crash fixes, and multiple versions of concurrency-controlled things security. InnoDB is the first table engine on MySQL that provides a foreign key constraint. and InnoDB's ability to deal with transactions is unmatched by other storage engines.

The InnoDB storage engine supports foreign key foreign key. The table where the foreign key resides is the child table, and the table that the foreign key depends on is the parent table. The field in the parent table that is associated with the outer table key must be the primary key. When you delete, update a piece of information from the parent table, the child table must also have a corresponding change.

The structure of the tables created by InnoDB is stored in the. frm file.

The advantage of the InnoDB storage engine is that it provides good transaction management, crash repair and other functions. The disadvantage is that the reading and writing efficiency is slightly poor, occupy the data space relatively large.

MyISAM Storage Engine

MyISAM was once the default storage engine for MySQL. It stores the table as 3 files. The name of the file is the same as the name of the table. The extension includes frm,nyd,myi. Where the frm file stores the structure of the table, the Nyd file stores the table's data, and the Myi file stores the table's index.

It has the advantage of small footprint and fast processing speed. The disadvantage is that the integrity and concurrency of transactions are not supported.

Memory Storage Engine

Creates a table using the contents stored in memory, and all the data is in memory. Each table that is based on the memory storage engine actually corresponds to a disk file. The file has the same filename as the table name, and the type is the frm type. The file value stores the structure of the table. and its data files are stored in memory. This facilitates the fast processing of data and improves the processing efficiency of the whole table. The server should have enough memory to make use of the storage engine table.

The memory storage engine uses a hash index by default. It's faster than the B-tree.

Category: MySQL

Database Storage Engine