MySQL InnoDB Storage Engine table (i)

This paper mainly introduces the logical storage and implementation of the INNODB storage engine table. Focus on how the data is organized and stored in the table.

1. Index Organization table (Index organized table)

In the InnoDB storage engine, tables are organized according to the primary key order, which is called the Index organization table. In the InnoDB presence engine table, each table has a primary key (Primary key), and if no primary key is displayed when the table is created, the primary key is selected or created as follows: A. Determines whether there is a unique index that is not empty (unique NOT null), and if so, the column is the primary key. If there are more than one, then the selected build table is the first defined non-empty index as a primary key. Note: The primary key selection is based on the order in which the indexes are defined, not the columns when the table is built. B. If there is no unique index, the InnoDB storage engine field creates a 6-byte-size pointer.

2.InnoDB Logical Storage structure
In the InnoDB storage engine, all data is logically stored in a space called a tablespace (tablespace). Table spaces are also composed of segments (segment), extents (extent), and Pages (page). Pages are sometimes called blocks in some documents. The logical storage structure of the InnoDB storage engine is as follows.

Let's introduce each one separately.
A. Table Space
You can see the top level of the logical structure of the INNODB storage engine, and all the data is stored in the table space. Table spaces are also divided into separate table spaces and shared table spaces. The parameter innodb_file_per_table parameter is used to determine what type of tablespace to use. It is important to note, however, that only data, indexes, and insert buffer pages are stored in a separate tablespace, other data such as rollback information, insert buffer index pages, system transaction information, two write buffers (double write buffer), etc., or in the original shared table space.
B. Paragraph
Table spaces consist of individual segments. Common segments are data segments, index segments, rollback segments, and so on. The InnoDB storage Engine is the index organization table, so the data and index, the index is the data. The data segment is the leaf point of the B + Tree (leaf node segment) and the index segment is a non-indexed node of the B + data (non-leaf nodes segment). The rollback segment is more special later in the introduction. Segments are managed by the engine itself.
C. District
A zone is a space made up of contiguous pages. The InnoDB Storage Engine page is 16KB in size and has 64 contiguous pages in one area, so each area is 1MB in size. InnoDB 1.0.x Releases the introduction of compressed pages, that is, the size of each page can be set to 2K, 4K, 8K by parameter key_block_size on the table, so the number of pages per zone is 512, 256, 126. InnoDB 1.2.X Version New parameter innodb_page_size sets the default page size to 4K, 8K, but the data in the page is not compressed, which is the same number in the zone 256, 128. In a word, no matter how the size of the wheel changes, the size of the area does not change 1M. But there is a problem: After opening the stand-alone table space, the default size of the created table is 96K, the area is 64 contiguous pages, the table space created should be 1M right? This is because, at the beginning of each segment, the data is saved with a 32 page-size fragmented page (Fragment page), which is the application for 64 consecutive pages after the pages are used. This is done for some small table or undo such segments, you can start to request less space, saving disk capacity overhead.
D. Page
page is the smallest unit of INNODB Disk Management. The default size is 16K, and you can set the page size to 4K, 8K, 16K with Innodb_page_size, and the page size in all tables is set and cannot be modified again. Unless a new library is generated by mysqldump import and export operations. Common page types are: Data page (B-tree Node), Undo page (Unod Log page), System page, Transaction data page (Transaction system page), insert buffer free list page (insert Buffer free List), uncompressed Binary large Object page (uncompressed BLOB page), Compressed binary object page (compressed BLOB page)
E. Line
The InnoDB storage engine is line-oriented (row-oriented), which means that the data is stored by row. The row records that are stored on each page are also hard-defined, running up to a record of 16k/2-200 rows, which is 7992 rows of records.

3.InnoDB Row Record format
The records for the INNODB storage engine are stored as rows, which indicates that the page holds data for a row of rows in the table. Its type has redundant, COMPACT, COMPRESS, dynamic four kinds. You can view the data format for each table in the current library by using Show Table status:

A.compact
Introduced in MySQL 5.0, its design goal is to store data happily. That is, the more rows in a single page, the higher the performance. How the compact bank records are stored:

It is shown that the first of the compact line record format is a non-null variable-length list of field lengths, which are placed in reverse order in columns, with a length of:
If the length of the column is less than 255 bytes, denoted by 1 bytes;
If it is greater than 255 bytes, it is represented by 2 bytes.
The second part after the variable-length field is the null flag bit, which indicates whether there is a null value in the row data, which is represented by 1. This section accounts for 1 bytes. The next step is to occupy 5 bytes of header information (the record header), each of which has the following meanings:

The final part of

         is to actually store the data for each column. Note that NULL does not occupy any space in that part, that is, NULL in addition to the null flag bit, the actual storage does not occupy any space. Each row has 2 hidden columns (The Transaction id column (6 bytes) and the Rollback pointer column (7 bytes), in addition to the user-defined column. If the InnoDB table does not have a primary key defined, a 6-byte rowid column is added. Null values in the compact format do not occupy any storage space, whether it is a char type or a varchar type.
        compact line format features:
             Each also record has a 5-byte length field that points to the next contiguous page, and can be used to implement row locks
             have a variable-length field, The number of fields in the storage page that may be null, the length of CEILING (N/8) bytes, and the length of time required to store the edge length field with one or both bytes
             All non-null variable-length fields that store their length information with a byte or bytes
             rows record the header field, followed by all non-null fields
           the char (n) field of the  utf8 character set, first removing the trailing space, attempting to store it in N bytes, and reserve n bytes for subsequent data updates to avoid fragmentation
    b.redundant
         Redundant is the line record storage method that was InnoDB before MySQL version 5.0.

The header of the redundant row record format is a field-length offset list, which is also placed in reverse order of the columns. If the length of the column is less than 255 bytes, denoted by 1 bytes, or greater than 255 bytes, expressed in 2 bytes. The second section Records header information (record header). Such as:

The final part is the data for each column of time storage.
Redundant line format features:
Each page contains a 6-byte rowid that points to the next contiguous page and can be used to implement row locks
The clustered index contains all the fields and contains an additional 6 bytes trx_id, and 7 bytes Roll_ptr
If there is no explicit primary key, the implied 6-byte rowid is used as the primary key
A pointer to all fields is stored in the row record, which takes 1 bytes when the field length is less than 128 bytes, otherwise 2 bytes is required
Fixed-length Type field similar to char, also used for constant-width storage, and does not remove trailing spaces
Like the varchar variable-length field, NULL does not occupy the actual storage space, whereas the char fixed-length field, NULL occupies the corresponding number of bytes
C. Row overflow Data
The InnoDB storage engine can store some of the data in a record outside the data page that is being used. Storage of large object types, such as BLOBs and lobs, is generally considered to store data outside the data page. This understanding is a bit biased, blobs, etc. can not overflow the data house page, and even the varchar column data type may still be stored as row overflow data. This depends primarily on the data that is actually stored.
D.compress and dynamic
The InnoDB 1.0.x release introduces a new file format, the previously supported compact and redundant formats called the Antelope file format, and the new file format is called the Barracuda file format. Its barracuda includes two new row record formats: COMPRESSD and dynamic. The new two record format pairs the data stored in the BLOB in a full row overflow, only 20 bytes of pointers in the data page, the time data stored in the off page. And the previous compact and redundant would have deposited money with 768 bytes of prefix
The difference between compact and redundant
Compact compared to redundant savings of about 20%, compress compared to the compact can save about 50%, but will lead to increased CPU consumption, TPS may only be the original 10%
A fixed-length type similar to char, also used for constant-width storage, and does not delete the trailing spaces (varchar will remove spaces)
All secondary indexes store those that exist in the primary key definition but do not exist in the secondary index (improving the efficiency of Secondary Index retrieval)
In the DYNAMIC, compressed format, the long field only stores 20 bytes, the rest of the off-page (the actual length within 40 bytes, will not occur off-page, even Text/blob)

Not to be continued ...

MySQL InnoDB Storage Engine table (i)