Let's learn about the MySQL storage engine

Preface

There are many file formats in daily life, and different file formats have corresponding storage methods and processing mechanisms (such as: .txt, .pdf, .mp4...)

There should be different processing mechanisms for storing different data.

Storage engines are different processing mechanisms

1. MySQL main storage engine:

. Innodb

. myisam

. memory

. blackhole

Let’s take a look at them one by one

'''
Innodb
It is the default storage engine for MySQL version 5.5 and later, which is more secure to store data.'''

'''
myisam
It is the default storage engine before MySQL 5.5. It is faster than Innodb, but we pay more attention to data security.

'''
memory
Memory engine (all data is stored in memory) power failure and data loss'''

'''
blackhole
Whatever is stored will disappear immediately (like a black hole)
'''

View the SQL statements for all storage engines:

show engines;

2. Examples of how different storage engines store tables

First, let's build a separate database

create database day45

Then switch to the database

use day45;

Create four tables with four different storage engines

create table t1(id int) engine=innodb;

create table t2(id int) engine=myisam;

create table t3(id int) engine=blackhole;

create table t4(id int) engine=memory;

After creating four tables, you will see four files with different storage engines under the data file.

The reason why the t3 table does not have the t3.MYD table data file is because the blackhole storage engine is like a black hole. If you throw a file into it, it will disappear, so it is not easy to store data in the file.

The t4 table is a memory storage engine. It is stored in memory and is a short-term storage of data. It is not easy to store it on the hard disk. Therefore, there is no table data file.

Next, we insert a piece of data into each table of different storage engines to see what the result will be:

insert into t1 values(1);

insert into t2 values(1);

insert into t3 values(1);

insert into t4 values(1);

You can see that when querying the data of each table separately, t3 does not store any data, because t3 is a blackhole storage engine, and the stored data will be lost immediately, so there will be no data when selecting. The experiment is not just for now. When you restart MySQL and re-query the data in each table, you will have new discoveries.

Obviously, the table of t4 also shows that there is no stored data at this time. Why is that?

Because t4 uses the memory storage engine, it stores data in memory instead of permanently. When you shut down MySQL and restart it, the data will be lost.

This is the end of this article about understanding MySQL storage engine. For more relevant MySQL storage engine content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!