InnoDB logical storage structure

In InnoDB, tables and index data are stored in a file with an extension of.ibd. The path of this file can be obtained by looking up mysql variable datadir, and then going into the corresponding database name directory, you can see many IBDs. The file name is the name of the table. Shared table Spaces (or system table Spaces) and separate table space files.

For a shared table space, all table data and corresponding indexes are stored there, while for a separate table space, data and indexes for each table are stored in a separate IBD file. In the current version of MySQL, the default is to use a separate table space.

The shared tablespace file name can be obtained by 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)

Copy the code

Innodb_file_per_table can be used to switch between independent and shared tablespaces. If innodb_file_per_table is enabled, the data in each table is stored in a separate tablespace file. Note that each tablespace only stores data, indexes, and other classes. For example, rollback information, system transaction information, secondary write buffer is still stored in the original shared table space.

Check innodb_file_per_table status.

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

Change innodb_file_per_table status

set global innodb_file_per_table=0;
Copy the code

If innodb_file_per_table is set to 0 (OFF), the tables created will be stored in system-shared tablespaces, as shown in the following example.

1.Create database_1 database mysql> create  database database_1;
Query OK, 1 row affected (0.02 sec)

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

3.Create the tables in mysql> create table t1(id int(11));
Query OK, 0 rows affected, 1 warning (0.05 sec)


4.View the ibD file root@hxl-PC:/var/lib/mysql/database_1# ls
t1.ibd
root@hxl-PC:/var/lib/mysql/database_1# 

5.Create table mysql after innodb_file_per_table is disabled> 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.View the ibD file root@hxl-PC:/var/lib/mysql/database_1# ls
t1.ibd

Copy the code

You can see that no new IBD files are created after closing.

A tablespace consists of segments, extents, and pages, including a network image.

1. The period of

A table space consists of segments, which are logical structures used to manage physical files. Common segments include data segments, index segments, and rollback segments. Each segment consists of N extents and 32 scattered pages.

InnoDB storage engine tables are organized by index, so data is index and index is data. Normally, when an index is created, two segments are created, one for non-leaf nodes and the other for leaf nodes.

2. The area

Extents are Spaces made up of contiguous pages, each of which is 1MB in size anyway. To ensure the continuity of pages within a region, InnoDB storage engine requests 4-5 regions from disk at a time. By default, InnoDB storage engine page size is 16KB, that is, there are 64 consecutive pages, 16*64=1024=1M.

The innodb1.2. x version added the parameter Innodb_page_size, which allows the default page size to be set to 4K, 8K,

On page 3.

A 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 with the innodb_page_size parameter. InnoDB has many different pages designed for different purposes. Common page types in InnoDB storage engine are:

1. Data page

2. Undo the page

3. The system page

4. Transaction data page (Transaction System page)

5. Insert buffer bitmap page

6. Insert buffer free list page

7. Uncompressed binary large object page

8. Compressed binary large object pages

You can view the size by running 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> 

Copy the code

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

Line 4.

InnoDB storage engine is row oriented, and pages record row information, that is, data is stored by row. Row data is stored in a row format. The number of rows per page is also rigidly defined, with a maximum of 16KB/2-200 rows (7992 rows) allowed.

InnoDB storage engine has two file formats, one called Antelops and the other called Barracuda.

Under the Antelope file format, there are compact and REDUNDANT row recording formats.

In the Barracuda file format, there are two row recording formats, compressed and dynamic.

You can view the current format in the following way, where Row_format is the row format storage type.

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: 
   
Copy the code