InnoDB logical storage unit is mainly divided into table space, segment, area and page

1. Tablespace

All data of InnoDB is stored in table space, which is divided into system table space and independent table space

MySQL5. After 7, there are temporary table spaces and general table spaces

1) SYSTEM tablespace

The system table space is mainly named after ibdata1.
When installing the database and initializing the database, the system is creating an ibddata1 tablespace file, which will store all data information and undo information.

innodb_data_file_path is responsible for defining the path, initialization size and automatic expansion strategy of the system table space, as follows:
In MySQL 8, the default size of ibdata1 is 12M

mysql> show variables like '%data_file%';
+----------------------------+------------------------+
| Variable_name              | Value                  |
+----------------------------+------------------------+
| innodb_data_file_path      | ibdata1:12M:autoextend |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend  |
+----------------------------+------------------------+
2 rows in set

mysql> 

The default automatic expansion size of the database is 64M, as follows:

innodb_ autoextend_ The default value of increment is 64M

mysql> show variables like '%auto
%';
+----------------------------------------------+-------+
| Variable_name                                | Value |
+----------------------------------------------+-------+
| auto_generate_certs                          | ON    |
| auto_increment_increment                     | 1     |
| auto_increment_offset                        | 1     |
| autocommit                                   | ON    |
| automatic_sp_privileges                      | ON    |
| caching_sha2_password_auto_generate_rsa_keys | ON    |
| innodb_autoextend_increment                  | 64   |
| innodb_autoinc_lock_mode                     | 2     |
| innodb_stats_auto_recalc                     | ON    |
| sha256_password_auto_generate_rsa_keys       | ON    |
| sql_auto_is_null                             | OFF   |
+----------------------------------------------+-------+
11 rows in set

2) Independent table space

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

It is enabled by default. If it is not enabled, InnoDB is set_ file_ per_ Table = 1.

Each table has its own independent table space, which can realize the transfer of table space, and the recycling of table space is particularly convenient. It does not need to be stored in idbdata1. The independent table space file stores the B + tree structure, index, insert buffer and other information of the corresponding table. The rest of the information is stored in the default table space.

The disadvantage is that every table has its own frm and The two file descriptors of ibd are prone to performance problems if the single standard grows too fast.

The data and files of the shared table space are easy to manage, but they cannot be recycled. The statistical analysis and log systems are not suitable for using the shared table space.

2. Paragraph

Table spaces are composed of segments, and a table can also be understood as a segment. There are usually data segments, rollback segments, index segments, etc.
Each segment consists of N areas and 32 scattered pages. The segment space expansion is carried out in the unit of area.

Generally, when creating an index, two segments will be created at the same time, namely, non child node segment and child node segment.

A table has four segments

3. District

The area is composed of continuous pages and is a section of space allocated continuously physically. The size of each area is fixed at 1M, and the area is equal to 64 pages, that is, 64*16kb=1M

4. Page

The minimum physical storage allocation unit of innodb is page, including data page and rollback page. Generally, an area consists of 64 consecutive pages, and the default size of the page is 16KB, as shown in the following table:

innodb_page_size 16384

mysql> show variables like '%page
%';
+--------------------------------------+-----------+
| Variable_name                        | Value     |
+--------------------------------------+-----------+
| innodb_doublewrite_pages             | 4         |
| innodb_log_compressed_pages          | ON        |
| innodb_max_dirty_pages_pct           | 90.000000 |
| innodb_max_dirty_pages_pct_lwm       | 10.000000 |
| innodb_page_cleaners                 | 1         |
| innodb_page_size                     | 16384     |
| innodb_stats_persistent_sample_pages | 20        |
| innodb_stats_transient_sample_pages  | 8         |
| large_page_size                      | 0         |
| large_pages                          | OFF       |
+--------------------------------------+-----------+
10 rows in set

that 's ok

Row information is recorded in the page, and data is stored according to rows in innodb.

This article is from ID: Internet old Xin more content is concerned about the official account of the "geek operation and maintenance home".