MySQLInnoDB storage engine table (1) _ MySQL

This section describes the logical storage and implementation of InnoDB storage engine tables. This section focuses on how data is organized and stored in tables. 1. indexorganizedtable in The InnoDB storage engine, tables are organized and stored according to the primary key order. this storage method mainly introduces the logical storage and implementation of InnoDB storage engine tables. This section focuses on how data is organized and stored in tables.

1. index organized table)

In the InnoDB storage engine, tables are organized and stored according to the primary key order. this storage method is called index organization table. In an InnoDB engine table, each table has a Primary key (Primary key). If the Primary key is not displayed during table creation, select or create a Primary key as follows:. determines whether a non-null unique index (unique not null) exists. If yes, this column is the primary key. If more than one table is created, the non-null value defined in the first table creation is used as the primary key in the index. Note: the primary key is selected based on the order in which the index is defined, rather than the column sequence during table creation. B. If no unique index exists, the InnoDB storage engine field creates a 6-byte pointer.

2. InnoDB logical storage structure
In the InnoDB storage engine, all data is logically stored in a single space, called a table space ). The tablespace consists of segments, partitions, and pages. Pages are sometimes called blocks in some documents ). The logical storage structure of the InnoDB storage engine is as follows.
Vc/clusters/g8YnI + CiAgICAgICAgx/jkx9p1_azq + NKz1 + cores + dLUw7 + cores + qyrzS/cores + cores/cG/cores/ release + Release/release/1eLKx9LyzqrU2sO/release + LXE0rO1xMnqx + Release/LV33VuZG/release + Release/ydLUzai5/release + tDCtcS/ 4qGjs6O8 + 7xe0ro1xmd1_m3t0drk/release + Release/release/W1xLb + release + bKx8Pmz/release + Release/release + 0 tbHHsL/release" http://www.bitsCN.com/uploadfile/Collfiles/20141202/20141202092356171.jpg "Alt =" \ ">
A. COMPACT
Introduced in MySQL 5.0, its design goal is to store data happily. That is, the more row data stored in a page, the higher the performance. Storage method of compact row records:

As shown in the figure, the header of the compact row record format is a list of non-NULL variable length field lengths, which are placed in reverse order according to the column sequence. Its length is:
If the column length is less than 255 bytes, 1 byte is used;
If it is greater than 255 bytes, it is expressed in 2 bytes.
The second part after the variable length field is the NULL flag, which indicates whether there is a NULL value in the data of the row, which is expressed as 1. This part occupies 1 byte. The next step is the record header, which occupies 5 bytes. the meaning of each record is as follows:

The last part is to actually store the data in each column. Note that NULL does not occupy any space in this part. that is to say, NULL occupies no space in actual storage except the NULL flag. In addition to user-defined columns, each row has two hidden columns (transaction ID column (6 bytes) and rollback pointer column (7 bytes )). If no primary key is defined in the InnoDB table, a six-byte rowid column is added. Whether it is char or varchar, the NULL value in compact format does not occupy any storage space.
COMPACT row format features:
Each record has a 5-byte length field, which is used to point to the next consecutive page and can be used to implement row locks.
There is a variable length field that stores the number of fields that may be NULL on the page. the length is CEILING (N/8) bytes, and 1 ~ Length required for the 2-byte storage edge length field
For all non-NULL variable-length fields, use 1 ~ 2 bytes to store its length information
After the line record header field, it is followed by all non-NULL fields.
The CHAR (N) field of the UTF8 character set first removes trailing spaces, tries to store them in N bytes, and reserves N bytes for subsequent data updates to avoid fragmentation.
B. REDUNDANT
Redundant is an InnoDB row record storage method before MySQL 5.0.

The header of the redundant row record format is a field length offset list, which is also placed in reverse order according to the column order. If the column length is less than 255 bytes, it is expressed in 1 byte; if it is greater than 255 bytes, it is expressed in 2 bytes. The second part is the record header ). For example:

The last part is the data of each column stored in time.
REDUNDANT row format features:
Each page contains 6 bytes of ROWID, which is used to point to the next consecutive page and can be used to implement row locks.
The clustered index contains all fields, including the additional 6-byte TRX_ID, and 7-byte ROLL_PTR.
If no explicit primary key exists, the hidden 6-byte ROWID is used as the primary key.
The row record stores the pointer pointing to all fields. when the field length is smaller than 128 bytes, the pointer occupies 1 byte; otherwise, 2 bytes are required.
For a CHAR-like fixed-length field, fixed-width storage is used, and spaces at the end are not deleted.
Similar to the VARCHAR variable-length type field, NULL does not occupy the actual storage space. in the CHAR variable-length field, NULL occupies the corresponding bytes.
C. Row overflow data
The InnoDB storage engine can store some data in a record outside the data page. It is generally believed that storage of large object types such as BLOB and LOB will store data outside the data page. This understanding is somewhat biased. BLOB and so on can not overflow the data House page, and even the varchar column data type may still be stored as row overflow data. This depends on the data actually stored.
D. COMPRESS and DYNAMIC
Innodb 1.0.x introduces a new file format. previously supported compact and redundant formats are called Antelope file formats, while the new file formats are called Barracuda file formats. Its Barracuda includes two new row record formats: Compressd and Dynamic. The new two record formats are correct. The data stored in BLOB is full row overflow, and only 20 bytes of pointers are stored on the data page, time data is stored in the Off Page. Earlier Compact and Redundant will store 768 prefix bytes
Differences between COMPACT and REDUNDANT
COMPACT can save about 20% compared with REDUNDANT, COMPRESS can save about 50% compared with COMPACT, but it will increase CPU consumption, and TPS may only be 10% of the original
Similar to the fixed length type of CHAR, the fixed width storage is used, and spaces at the end are not deleted (spaces are deleted when VARCHAR is used)
All secondary indexes store fields that exist in the primary key definition but do not exist in the secondary index (improving the efficiency of secondary index retrieval)
In DYNAMIC and COMPRESSED formats, long fields are only stored in 20 bytes, and all others are off-page (if the actual length is less than 40 bytes, off-page, even text/blob will not occur)

Not complete...