According to MySQL technology insider: InnoDB storage engine (Second Edition)Nuggets brochure “how MySQL works: understanding MySQL from the root”A summary of the two books
This article will introduce the logical storage and implementation of MySQL tables, that is, if data is organized and stored in tables.
A table is a collection of data about a specific entity, which is also the core of the relational database model.
Index organization table
In InnoDB storage engine, tables are organized and stored according to the primary key order. Tables in this storage mode are called index organization tables.
Each table has a primary key. If not, a primary key will be selected or created as follows:
- First, judge whether there is a unique not null index in the table. If so, the column is the primary key
- If the above conditions are not met, the InnoDB storage engine automatically creates a 6-byte pointer
- When there are multiple non empty unique indexes in the table, InnoDB storage engine will select the first defined non empty unique index as the primary key when creating the table
InnoDB storage logical structure
All data are logically stored in a space, which is what we call the table space. The table space can be seen as the highest level of the logical structure of InnoDB storage engine. The structure diagram is as follows (the screenshot of the table is from inside MySQL Technology: InnoDB storage engine (Second Edition))
From the structure diagram, you can see that the table space is composed of four parts: segment, area, page and row
- Segment: the table space is composed of various segments. Common segments include data segment, index segment, rollback segment, etc
- Area: the section consists of each area. The area consists of consecutive pages. In any case, the size of the area is 1MB
- In order to ensure the continuity of pages in a zone, the InnoDB storage engine requests 4 ~ 5 zones from the disk at a time. By default, the size of InnoDB storage engine pages is 16kb, that is, there are 64 consecutive pages in a zone
- Page: the smallest unit of disk management of InnoDB storage engine [a separate article will be written to introduce data pages in detail]. Common page types include: data page, undo page, system page, transaction data page, insert buffer bitmap page, insert buffer free page, uncompressed binary large object page and compressed binary large object page
- Row: InnoDB storage engine is column oriented, that is, data is stored by row
InnoDB row record format
We normally insert data into the table in behavioral units. The format of these data stored on disk is called
Record format, there are many line record formats, such as:
Compressed, starting from MySQL 5.1, the default line format is
You can modify the line record format with the following command
Create table table name (column information) row_ Format = line format name Alter table table name row_ Format = line format name
CompactThe structure diagram of row record format (screenshot from MySQL technology insider: InnoDB storage engine (Second Edition)) is as follows:
- Variable length field length list: the header of compact row record format is a non null variable length field length list, which is placed in reverse order
- Store the byte length occupied by the real data of all variable length fields in the variable length field length list
- If the column length is less than 255 bytes, it is represented by 1 byte; if it is greater than 255 bytes, it is represented by 2 bytes
- Null flag bit: indicates whether there is a null value in the row data. If so, 1 indicates
- Record header information: it is fixed to occupy 5 bytes, that is, 40 binary bits. Different bits represent different meanings, as shown in figure ():
- Redundant line record format: line record format before MySQL version 5.1
- Row overflow data [described in detail below]
- Compressed and dynamic row record formats
- The dynamic row record format is similar to the compact row record format, except that it is different when dealing with row overflow data
- The difference between the compressed line record format and the dynamic line record format is that the compressed line record format uses the compression algorithm to compress the page to save space
The InnoDB storage engine can store some data in a record outside the real data page. It is generally believed that only blob and lob, which are large object data types, will store the data outside the page, but this understanding is a little biased,
Blobs can also not store data outside the page, and even varchar types may still be stored as row overflow data.
Overflow caused by too much data in the record
Not only too much big object data such as blob and lob will overflow, but also varchar type will overflow.
Critical point of row overflow
How many bytes of data does a row overflow occur when a column stores data?
MySQL stipulates that a data page should store at least two data records. We can first analyze how to use the space in the next page
- Each page needs to store other information besides our data records, such as
Page HeaderWait, what do you need
132A byte space, and other spaces can be used to store data records
- Each record requires additional
27Bytes of information,
27Byte information includes
- The length of 2 bytes used to store real data
- 1 byte is used to store whether the column is null
- 5-byte header information
- 6-byte row_ ID column
- 6-byte transaction_ ID column
- 7-byte roll_ Pointer column
Assuming that the number of data bytes stored in a column is n, the uncle of MySQL design stipulates that if the column does not overflow, the following formula needs to be satisfied:
132 + 2×(27 + n) < 16384
It is obtained that n is 8099, that is, this column can store 8099 bytes of information. If it is greater than 8099 bytes, it is overflow. In the case of only one column, if there are multiple columns, this value can not be used as a reference, so the final conclusion is: you don’t need to pay attention to the critical point, As long as we know that if the number of bytes of data stored in a column of a record is very large, the column may become an overflow column.
This work adoptsCC agreement, reprint must indicate the author and the link to this article