Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "example analysis of logical storage structure in InnoDB", which is easy to understand and well-organized. I hope it can help you solve your doubts. Let the editor lead you to study and learn the article "sample analysis of logical storage structure in InnoDB".

InnoDB logical storage structure

In InnoDB, the table we created and the corresponding index data are stored in the file with the .ibd extension. The file path can be obtained by looking up the mysql variable datadir, and then entering the corresponding database name directory, you will see a lot of ibd, and the file name is the table name. There are two types of table spaces, shared table space (or system table space) and independent table space file.

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

The file name of the shared tablespace 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 tablespaces can be switched through innodb_file_per_table. If enabled, the data in each table will be placed in a separate tablespace file. It should also be noted that only data and indexes are stored in the tablespace of each table, while other types of data, such as rollback information, system transaction information, and secondary write buffers, are still stored in the original shared tablespace.

The following statement allows you to view 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.01sec)

Modify innodb_file_per_table statu

Set global innodb_file_per_table=0

If innodb_file_per_table is equal to 0, that is, when OFF is turned off, the created tables will be stored in the system-shared tablespace, such as the columns below.

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.01sec) 3. Create the table 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# lst1.ibdroot@hxl-PC:/var/lib/mysql/database_1# 5. After closing innodb_file_per_table, create 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. View the ibd file root@hxl-PC:/var/lib/mysql/database_1# lst1.ibd

You can see that no new ibd file was created after closing.

The tablespace is composed of segment, extent and page, introducing a network picture.

1. Segment

The tablespace consists of segments, which are a logical structure for managing physical files. Common segments are data segments, index segments, and rollback segments, each consisting of N extents and 32 fragmented pages.

The InnoDB storage engine table is organized by indexes, so the data is the index, and the index is the data. Usually, when an index is created, two segments are created, one is the non-leaf node and the other is the leaf node.

two。 Zone

An extent is a space made up of contiguous pages, and in any case, the size of each zone is 1MB. In order to ensure the continuity of pages in the area, the InnoDB storage engine requests 4-5 areas from disk at a time. By default, the page size of the InnoDB storage engine is 16KB, that is, there are 64 consecutive pages, 16'64 '1024' 1m.

The InnoDB1.2.x version adds the parameter innodb_page_size, which allows you to set the default page size to 4K and 8K.

3. Page

Page is the smallest unit of disk management in InnoDB storage engine. By default, 16kb, you can set the page size to 4K, 8K, 16K by parameter innodb_page_size. InnoDB designs many kinds of pages for different purposes. The common page types in the InnoDB storage engine are:

Data page

Undo page

System page

Transaction data page (transaction system page)

Insert buffer bitmap page

Insert buffer free list page

Uncompressed binary large object page

Compress binary large object pages

You can view the size with 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.

The name function File header records some information of the page header, the record Page header of cheksum, Ppervious and next page records the status information and storage information of the page, and the first record positionInfimum+ supremumInnoDB has two virtual row records per data page, which is used to limit the Free space free space of the row data information actually stored by the record boundary Row records. It is also the linked list structure Page directory that stores the relative position of the record File trailerinnodb uses it to ensure that the page is completely written to disk 4. OK

The InnoDB storage engine is row-oriented, and the information of row records is recorded in the page, that is, the data is stored by row. The row record data is stored in the row format. The row records stored in each page are also rigidly defined, with a maximum of 16KB/2-200rows, or 7992 rows.

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

Under the Antelope file format, there are two line record formats: compact and redundant.

Under the Barracuda file format, there are two line record formats: compressed and dynamic.

You can view the current format in the following ways, where Row_format is the corresponding line 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: 16384Max_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: above is all the content of this article entitled "sample Analysis of logical Storage structures in InnoDB" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.