The concept of logic ： surface 、 Field 、 That's ok

physical concept （ From big to small ）： Table space 、 Data area group 、 Data area 、 Data pages 、 data row

Storage format of data rows ：

With COMPACT Row storage format Take an example to illustrate ：

List of variable length fields ,NULL List of values , Data header ,column01 Value ,column02 Value ,column03 Value .......

In addition to the value of each field , There is also some additional information , This extra information is used to describe this line of data .

Storage of variable length fields ：

such as varchar(10) Fields of type can store hello, Can be stored hi

For ease of reading , You need to record the actual length of the variable length field , And stored in reverse order

For example, store hello hi hao, When it's really stored ：0x03  0x02  0x05  null List of values Header fields hello hi hao

NULL Storage of field values ：

If you store it directly “NULL”, It's a waste of space , And it's not necessary , therefore NULL The value is in binary bit Bit to store , If bit The value of is 0, The explanation is not NULL, If bit The value of is 1, That is the NULL.

Definition table ：

CREATE TABLE customer(

Name varchar(10) NOT NULL,

Address varchar(20),

Gender char(1),

Job varchar(30),

School varchar(50)

)ROW_FORMAT=COMPACT;

Now store a row of data ：Jack NULL m NULL xx_school

If the value of the variable length field is NULL, Then there is no need to change the length of the value stored in the field length list .

All allowed are NULL All fields have a binary bit The value of a .

NULL The value list is also stored in reverse order , And it's 8 individual bit Multiple of bits , If it's not enough, make up for it 0.

Actual storage ：

0x09 0x04 00000101 Header information Jack m xx_school

Then encode the character set of the string and store ：

0x09 0x04 00000101 Header information 616161 636320 626262656566

Hide fields ：

When actually storing a row of data , In his real data section , Add some hidden fields .

DB_ROW_ID： A unique identification of the data row

DB_TRX_ID： Business ID, Explain which transaction updated the data

DB_ROLL_PTR： rollback pointer , For transaction rollback

line overflow ：

The default size of a data page is 16KB, If the size of a row of data exceeds the size of the data page , What should I do ？

For example, a table defines a varchar(65532) Field of , That is 65532 Bytes , Far more than 16KB. This is the time , In the data page where this row of data is stored , That field is only part of the data page , Include one 20 Byte pointer , Points to other data pages , The data pages are connected by linked lists , Store this varchar(65532) Data in super large fields .

image TXET、BLOB The row of this field , There will be row overflow , Then a row of data will be stored in multiple data pages .

Storage format of data pages ：

Data page default size bits 16KB, Many data rows are stored , There are many parts inside , Generally speaking, it includes ： The file header 、 Data header 、 Minimum record and maximum record 、 Multiple data rows 、 Free space 、 Data page directory 、 At the end of the file

File header occupation 38 Bytes , The data header occupies 56 Bytes , The maximum and minimum records occupy 26 Bytes , The size of data row area and free area are not fixed , The size of the data directory is not fixed , The end of the file occupies 8 Bytes .

Tablespaces and data areas ：

Table space ：

Each table has a corresponding table space , On the disk will correspond to “ indicate .ibd” Such a disk data file .

At the physical level , A table space is a data file on disk .

Data area ：

A data area is continuous 64 Data pages , Every data page 16KB, So a data area is 1MB. then 256 A data area is divided into a group .

The first three data pages of the first data area of the first data area group of the table space store special information .

The first two data pages of the first data area of other data area groups in the table space , It also stores special information .