What is the storage engine in Mysql database

Storage Engine

What is a storage engine?

Like the engines in airplanes and rockets, the storage engine is a very critical component of the database. Obviously not, because the use of storage engines must be based on specific scenarios. In Mysql, the storage engine is the same, and it is not good or bad. We need to choose the appropriate storage engine to adapt to different scenarios in order to do our best. The storage engine is the implementation of technologies such as storing data, creating indexes, and updating/querying data. Table types can be called storage engines because storage engines are based on tables rather than libraries.

The architecture of Mysql:

The architecture of Mysql is divided into four layers:

The architecture of MYsql is shown in the figure below:

Connection layer

The top layer is some clients and link services, which mainly complete some connection processing, authorization authentication, and related security solutions. The server will also provide secure access for each A client verifies the operating permissions it has.

Service layer

The second layer architecture mainly completes most of the core service functions, such as SOL interface, and completes cache query, SOL analysis and optimization, and the execution of some built-in functions. All cross-storage engine functions are also implemented in this layer, such as procedures, functions, etc.

Engine layer

The storage engine is the key component responsible for data storage and retrieval in MvSOL. The server communicates with the storage engine using APIs. Choose the appropriate storage engine according to your needs, because different storage engines have different functions.

Storage layer

Mainly stores data on the file system and completes the interaction with the storage engine.

View of storage engines

Starting from MySQL 5.5, the InnoDB storage engine becomes the default storage engine, and of course there are many other storage engines to choose from. Previously, the default was the Memory storage engine.

show engines;

create table Course(
    Con int primary key auto_increment,
    Cname varchar(10),
    Cpon int,
    Ccredit int
)

show create table course;

Specification of storage engine

For example, we can specify when creating a table The storage engine type for this table.

create table test_mysql(
    name varchar(10),
    age int
) engine = Memory;

Features of storage engine

Here we focus on the following InnoDB

InnoDB introduction

InnoDB is a general-purpose storage engine that combines high reliability and high performance. After MvSOL 5.5, InnoDB is the default MvSOL storage engine.

InnoDB features

• DML operations follow the ACID model and support transactions;

• Row-level locks to improve concurrent access performance;

• Support foreign key FOREIGN KEY constraints to ensure the integrity and correctness of data:

InnoDB file

• xxx.ibd:xxx represents the table name. Each table in the innoDB engine will correspond to such a table Spatial file, which stores the table structure (frm, sdi), data and indexes of the table.

• Parameters: innodb file per table

# #Finally, use a picture to show the logical storage structure of InnoDB.

Summary: InnoDB supports transactions, but MyISAM does not; InnoDB supports row locks. MyISAM does not support it and supports table locks; InnoDB supports foreign keys, but MyISAM does not support it;

Selection of storage engine

The above is the detailed content of What is the storage engine in Mysql database. For more information, please follow other related articles on the PHP Chinese website!