preface

A good database, must involve persistence, persistence, there will be data fall disk operation. There is a drop disk operation is bound to have file storage location planning. And the persistence file of the database is also the premise to understand the high availability and scalability of the database. Databases such as Hbase, Redis, Rocksdb, tiDB and so on all have similarities, which will be shared later.

InnoDB is a storage engine that stores data from tables to disk. Such storage engines store tables on file systems. When we want to read data, these storage engines will read data from the file system and send it back to us, and when we want to write data, these storage engines will write the data back to the file system.

How does InnoDB engine organize data? This is to understand the innoDB file storage structure.

The location of the data store

Data is stored in the data directory, which is used to store the data generated during the running of MySQL. The data directory is different from the MySQL data installation directory. We can check this by using the following statement.

SHOW VARIABLES LIKE 'datadir';
Copy the code

Mapping between database tables and file systems

In fact, each database corresponds to a subdirectory under the data directory, or a folder.

When we create a database, mysql does two things for us:

  • Create a subdirectory (or folder) under the data directory with the same name as the database.

  • Create a file named db.opt under the subdirectory with the same name as the database. This file contains various attributes of the database, such as the character set, comparison rules, and so on.

For example, see what databases I have on my current machine:

And the location of the corresponding file system:

Disk perspective

From a disk perspective, physical storage consists of system tablespaces, independent tablespaces, generic tablespaces, undo tablespaces, and redo logs.

System tablespace

The system table space can correspond to one or more actual files on the file system. By default, InnoDB creates a 12MB file named ibData1 in the data directory (look for it in your data directory). This file is the representation of the corresponding system table space on the file system. This file is self-expanding. In a MySQL server, there is only one system tablespace.

File-per-table tablespace

InnoDB does not store the data of each table in the system table space by default. Instead, it creates a separate table space for each table, which means that there are as many separate table Spaces as we create. A file representing the independent tablespace is created in the subdirectory of the database to which the table belongs. The file name is the same as the table name, but the extension of the. Ibd is added.

The name of the table. IbdCopy the code

Mysql > create tablespace test; create tablespace test; create tablespace test;

test.ibd
Copy the code

The test.ibd file is used to store data and indexes in the test table. However, we can also specify whether to use the system tablespace or a separate tablespace to store data. This function is controlled by the innodb_file_per_table startup parameter.

Other types of table Spaces

With the development of mysql, there are general tablespace, Undo tablespace, and Temporary tablespace.

Mysql architecture diagram

Understanding the mysql architecture diagram helps you understand the composition of several physical files for the InnoDB engine.

This is my understanding of mysql physical storage.

Welcome to clap brick message exchange, if you think it is good, you can help to like, add a concern, thank you!

Of course, you can also pay attention to my public number [Wang Ge writing code]

The resources

InnoDB Storage Engine (Jiang Chengyao)

This article uses the article synchronization assistant to synchronize