Learn MySQL in depth and understand the logical storage structure of InnoDB

This article will take you through the InnoDB logical storage structure, I hope it will be helpful to you!

InnoDB logical storage structure

In InnoDB, the tables we create and the corresponding index data are stored in the extension In the .ibd file, this file path can be obtained by first checking the mysql variable datadir, and then enter the corresponding database name directory. You will see many ibds. The file name is the table name. There are two types here. table space, shared table space (or system table space) and independent table space files. [Related recommendations: mysql video tutorial]

For shared table spaces, all table data and corresponding indexes are stored here, while independent table spaces are the data and indexes of each table. They are all stored in a separate ibd file. In the current MySQL version, independent table spaces are used by default.

The shared table space file name can be obtained through innodb_data_file_path,

mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

Independent and shared table spaces can be switched through innodb_file_per_table, if it is enabled, Then the data in each table is placed in a separate table space file. It should also be noted that the table space of each table only stores data and indexes, and other types of data, such as rollback information, system transaction information, and secondary writes. The buffer is still stored in the original shared tablespace.

The following statement can check the status of innodb_file_per_table.

mysql> show variables like '%innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

Modificationinnodb_file_per_tableStatus

set global innodb_file_per_table=0;

If innodb_file_per_table is equal to 0, that is, when OFF is closed, the created table will be stored In the table space shared by the system, such as the following example.

1.创建database_1数据库
mysql> create  database database_1;
Query OK, 1 row affected (0.02 sec)

2. 当前状态
mysql> show variables like '%innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

3. 创建表
mysql> create table t1(id int(11));
Query OK, 0 rows affected, 1 warning (0.05 sec)


4. 查看ibd文件
root@hxl-PC:/var/lib/mysql/database_1# ls
t1.ibd
root@hxl-PC:/var/lib/mysql/database_1# 

5. 关闭innodb_file_per_table后在创建表
mysql> set global innodb_file_per_table=0;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2(id int(11));
Query OK, 0 rows affected, 1 warning (0.05 sec)
6. 查看ibd文件
root@hxl-PC:/var/lib/mysql/database_1# ls
t1.ibd

It can be found that no new ibd file is created after closing.

The table space is composed of segments, extents, and pages, and introduces a network picture.

1. Segment

The table space is composed of segments. It is a logical structure used to manage physical files. Common Segments include data segment, index segment, and rollback segment. Each segment consists of N areas and 32 scattered pages.

InnoDB storage engine tables are organized by index, so the data is the index, and the index is the data. Normally, when creating an index, two segments will be created, namely non-leaf node segments and leaf node segments.

2. Area

The area is a space composed of consecutive pages. In any case, the size of each area is 1MB. In order to ensure the continuity of pages within the region, the InnoDB storage engine applies for 4-5 regions from the disk at a time. By default, the page size of the InnoDB storage engine is 16KB, which means there are 64 consecutive pages, 16*64=1024=1M.

InnoDB1.2.x version adds parameter innodb_page_size, this parameter allows setting the default page size to 4K, 8K,

3. Page

Page is the smallest unit of disk management for the InnoDB storage engine. The default is 16kb. The page size can be set to 4K, 8K, or 16K through the parameter innodb_page_size. InnoDB has designed many types of pages to achieve different purposes. Common page types in the InnoDB storage engine are:

• Data page

• undo Page

• System page

• Transaction data page (trading system page)

• Insert buffer bit Image page

• Insert buffer free list page

• Uncompressed binary large object page

• Compressed binary large object page

You can check the size through the following command.

mysql> show status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

mysql>

The following is the InnoDB data page structure, which consists of seven parts.

##NameFunction File header records some information of the page header, cheksum, Previous and next page recordsPage header records the status information of the page And storage information, the position of the first recordInfimum supremumInnoDB each data page has two virtual row records, used to limit the record boundaryRow recordsActually stored row data informationFree spaceFree space, also a linked list structurePage directory stores the relative location of the recordFile trailerinnodb uses this to ensure Page written completely to disk

4.行

InnoDB存储引擎是面向行的，页里面又记录着行记录的信息，也就是数据是按照行存储的。行记录数据又是按照行格式进行存放的。每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200行，也就是7992行。

InnoDB存储引擎有两种文件格式，一种叫Antelops,另外一种叫Barracuda。

在Antelope文件格式下，有compact和redundant两种行记录格式。

在Barracuda文件格式下，有compressed和dynamic两种行记录格式。

可以通过以下方式查看当前格式，其中Row_format就是对应行格式存储类型。

mysql> show table status \G;
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 15
 Avg_row_length: 1092
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-08-24 09:43:29
    Update_time: 2021-08-24 14:43:35
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment:

原文地址：https://juejin.cn/post/6999936914119720996

作者：i听风逝夜

更多编程相关知识，请访问：编程视频！！

The above is the detailed content of Learn MySQL in depth and understand the logical storage structure of InnoDB. For more information, please follow other related articles on the PHP Chinese website!