MySQL storage engine Introduction

MySQL uses a plug-in storage engine.

The storage engines include MyISAM, Innodb, NDB Cluster, Maria, Falcon, Memory, Archive, Merge, and Federated.

The most widely used storage engines are MyISAM and Innodb, so I will give a brief introduction to them.

Introduction to the MyISAM storage engine

MyISAM storage engine tables are stored in the database. Each table is stored as three physical files named after the table.

1. (. frm file) any storage engine is indispensable for storing the table structure definition information.

2. (. MYD file) file for storing table data

3. (. MYI file) file for storing all index data of the table

Note: There are only three such files used as the storage of MyISAM storage tables.

MyISAM tables are stored in a. MYD file with the same Suffix in different formats.

The formats include static (FIXED) FIXED length, DYNAMIC (DYNAMIC) variable length, and COMPRESSED.

Compression: You can use ROW_FORMAT to specify {COMPRESSED | DEFAULT} when creating a table (no compression by DEFAULT)

You can also use the myisampack tool for compression.

Non-compression:

DYNAMIC: As long as a field of Variable Length exists in the table, the table must be in DYNAMIC format.

Static: If no variable length field exists, it is in FIXED format.

MyISAM supports the following three types of indexes:

1. B-Tree indexes

B-Tree indexes. All index nodes are stored according to the data structure of the balance tree, and all index data nodes are on the leaf node.

2. R-Tree indexes

The storage method of the R-Tree index is different from that of the B-tree index. It is mainly designed to index the fields of the storage space and multi-dimensional data, and supports the indexing of geometry fields.

3. Full-text index

The Full-text index is the Full-text index we have long mentioned. Its storage structure is also B-tree. This is mainly to solve the problem of inefficient like queries.

Note: The length of all fields involved in an index cannot exceed 1000 bytes.

Introduction to the Innodb Storage Engine
1. Supports transaction Installation

All four levels defined by the SQL92 standard are implemented (read uncommitted, read committed, repeatable read and SERIALIZABLE)

2. Multi-version Data Reading

While Innodb supports transactions, in order to ensure the performance when data consistency has been concurrent, it implements Multi-version Data Reading through undo information.

3. Improvement of Locking Mechanism

Innodb changed the lock mechanism of MyISAM and implemented the row lock. The implementation of the row lock mechanism is achieved through indexing.

4. Implement Foreign keys

We recommend that you do not use foreign keys. database system tuning experts recommend that you do not use them.

5. Physical Structure of Innodb

Like MyISAM: (. frm file) to store metadata related to table structure definitions.

Unlike MyISAM, table data and index data are stored together.

The physical structure of Innodb is divided into two parts:

1. Data Files (table data and index data)

Store data in a data table and all index data, including primary keys and other common indexes.

There are two types of Innodb tablespace:

One is the shared tablespace, that is, all tables and index data are stored in the same tablespace (one or more data files) and specified through innodb_data_file_path, adding data files requires downtime and restart.

The other is exclusive tablespace, that is, the data and indexes of each table are stored in a separate. ibd file.

Note: The shared tablespace must exist because Innodb's undo information and other metadata information are stored in the shared tablespace.

Data files in the shared tablespace can be set to fixed size or automatically scalable. Files in the automatic scaling mode can be set to the maximum file size and the amount of each extension.

2. Log Files

The log files of Innodb are similar to the redo logs of Oracle:

You can set multiple log groups (at least two) and adopt a round robin policy to write data in sequence. Even in old versions, the same log archiving feature as Oracle is available.

Note: Do not delete all log files of innodb, because it is likely to cause crash in your database to fail to start or lose data.

Innodb can use the redo log to restore the transaction that has completed the database Crash at the moment but has not been able to write data to the disk, it can also roll back unfinished transactions that have been partially completed and written to the disk and restore data.

All Innodb parameters have the prefix "innodb _". You can use the parameter (skip-innodb) to block the Innodb Storage engine in MySQL, in this way, even if we install the Innodb Storage engine during installation and compilation, users cannot create Innodb tables.