What is InnoDB row storage format

In earlier InnoDB versions, since there was only one file format, there was no need to name this file format. As the InnoDB engine evolves, new file formats that are incompatible with earlier versions are developed to support new features. Today we will introduce the InnoDB row storage format.

The InnoDB storage engine supports four formats: REDUNDANT, COMPACT, DYNAMIC, and COMPRESSED.

InnoDB row format overview

##REDUNDANT row format

REDUNDANT format provides compatibility with older versions of MySQL.

The REDUNDANT row format is supported by two InnoDB file formats (Antelope and Barracuda).

Tables using the REDUNDANT row format store the first 768 bytes of variable-length column values ​​(VARCHAR, VARBINARY, and BLOB and TEXT types) in index records within B-tree nodes, and the remainder in overflow page. Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length columns and can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3 utf8mb4.

If the column's value is 768 bytes or less, overflow pages are not used and may result in some savings in I/O because the value is stored entirely in the B-tree node. This works fine for relatively short BLOB column values, but can cause B-tree nodes to be populated with data rather than key values, making them less efficient. Tables with many BLOB columns can cause B-tree nodes to become too full and contain too few rows, making the entire index less efficient than if the rows were shorter or the column values ​​were stored off-page.

REDUNDANT row format storage characteristics

REDUNDANT row format has the following storage characteristics:

• Each index record contains a 6 Bytes header. Headers are used to link consecutive records together, and for row-level locking.

• Records in a clustered index contain fields for all user-defined columns. In addition, there is a 6-byte transaction ID field and a 7-byte rolling pointer field.

• If no primary key is defined for the table, each clustered index record also contains a 6-byte row ID field.

• Each secondary index record contains all primary key columns defined for the clustered index key that are not in the secondary index.

• Records contain pointers to each field of the record. If the total length of the fields in the record is less than 128 bytes, the pointer is one byte; otherwise, two bytes. The array of pointers is called the record directory. The area pointed to by the pointer is the data part of the record.

• Internally, fixed-length character columns such as CHAR(10) are stored in fixed-length format. Trailing spaces are not truncated from VARCHAR columns. Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length columns and can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3 utf8mb4.

• SQL NULL values ​​retain one or two bytes in the record directory. NULL SQL values ​​will retain zero bytes in the data portion of the record if stored in a variable-length column. For fixed-length columns, the fixed length of the column is retained in the data portion of the record. Reserving fixed space for NULL values ​​allows columns to be updated from NULL to non-NULL values ​​without causing index page fragmentation.

COMPACT row format

COMPACT row format reduces the number of rows by approximately 20% compared to REDUNDANT row format Storage space, REDUNDANT comes at the cost of increased CPU usage for certain operations. If your workload is typical and limited by cache hit ratio and disk speed, the COMPACT format may be faster. If the workload is limited by CPU speed, the compact format may be slower.

COMPACT line format is supported by two InnoDB file formats (Antelope and Barracuda).

Tables using the COMPACT row format store the first 768 bytes of variable-length column values ​​(VARCHAR, VARBINARY, and BLOB and TEXT types) in index records within B-tree nodes, and the remainder in overflow page.

Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length columns and can be stored off-page. For example, CHAR(255) if the maximum byte length of the character set is greater than 3, can exceed 768 bytes if the column is a utf8mb4 character type.

If the column's value is 768 bytes or less, overflow pages are not used and may result in some savings in I/O because the value is stored entirely in the B-tree node. This works fine for relatively short BLOB column values, but can cause B-tree nodes to be populated with data rather than key values, making them less efficient. Tables with many BLOB columns can cause B-tree nodes to become too full and contain too few rows, making the entire index less efficient than if the rows were shorter or the column values ​​were stored off-page.

COMPACT row format storage properties

COMPACT row format has the following storage characteristics:

• Each index record contains a 5-byte header, which can be preceded by a variable-length header. Headers are used to link consecutive records together, and for row-level locking.

• The variable-length portion of the record header contains a bit vector indicating a NULL column. If the number of columns in the index can be NULL , the bit vector occupies N bytes. (For example, if there can be anywhere from 9 to 16 columns, the bit vector uses two bytes.) Columns that do not occupy space beyond the bits in this vector. The variable-length portion of the header also contains the length of the variable-length column. Each length requires one or two bytes, depending on the maximum length of the column. If all columns in the index are CEILING(*N*/8)NULLNULLNOT NULL and have fixed length, the record header has no variable-length part.

• For each non-NULL variable-length field, the record header contains one or two bytes of the column length. Only two bytes are required if part of the column is stored outside the overflow page, or if the maximum length exceeds 255 bytes and the actual length exceeds 127 bytes. For external storage columns, the 2-byte length represents the length of the internal storage section plus a 20-byte pointer to the external storage section. The inner part is 768 bytes, so the length is 768 20. The 20-byte pointer stores the true length of the column.

• The record header is followed by the data content of the non-NULL column.

• Records in a clustered index contain fields for all user-defined columns. In addition, there is a 6-byte transaction ID field and a 7-byte rolling pointer field.

• If no primary key is defined for the table, each clustered index record also contains a 6-byte row ID field.

• Each secondary index record contains all primary key columns defined for the clustered index key that are not in the secondary index. If any primary key columns are variable length, then each secondary index's record header has a variable-length section to record their length, even if secondary indexes are defined on fixed-length columns.

• Internally, for non-variable-length character sets, a fixed-length character sequence (e.g. stored in CHAR(10) fixed-length format). Trailing spaces are not truncated from VARCHAR columns.

• Internally, for variable-length character sets such as utf8mb3 and utf8mb4, InnoDB attempts to store bytes by trimming trailing spaces. If the column value exceeds bytes, trailing spaces are adjusted to the minimum length of the column value in bytes. The maximum length of a column is the maximum character byte length × CHAR(*N*)NCHAR(*N*)NCHAR(*N*)

N retains the minimum number of bytes. Reserving minimum space in many cases allows column updates to be completed without causing index page fragmentation. In contrast, when using row format, columns occupy a maximum character byte length × CHAR(*N*)NCHAR(*N*)NREDUNDANT

Fixed-length columns greater than or equal to 768 bytes are encoded It is a variable length field and can be stored off-page. For example, CHAR(255) if the maximum byte length of the character set is greater than 3, can exceed 768 bytes if the column is a utf8mb4 character type.

COMPACT row format storage characteristics diagram

MySQL InnoDB COMPAT row format structure

##MySQL InnoDB COMPAT row format structure header information

MySQL InnoDB COMPAT row format structure header information description

| Name| Size (bit) | Description| | ———— | ——— | ———————————————————— | | Reserved bit | 1 | Unknown | | Reserved bit | 1 | Unknown | | delete_flag | 1 | Whether the row has been deleted | | min_rec_flag | 1 | 1 if the record is predefined as the smallest record | | n_owned | 4 | The number of records owned by this record | | heap_no | 13 | The sorted record of this record in the index heap | | record_type | 3 | Record type, 000 means normal, 001 means B-tree node pointer, 010 means infimum, 011 means supermum, 1xx means reserved | | next_record | 16 | The relative position of the next record in the page

Actually, InnoDB will add three hidden columns to each piece of data, which are

##DYNAMIC row format DYNAMIC The row format provides the same storage features as the COMPACT row format, but adds enhanced storage capabilities for long variable-length columns and support for large index key prefixes.

Barracuda file format supports DYNAMIC line format.

Create a table when using ROW_FORMAT=DYNAMIC. InnoDB can store long variable-length column values ​​​​completely off-page (for VARCHAR, VARBINARY, BLOB and TEXT types). Clustered index records only contain 20 words pointing to the overflow page. section pointer. Fixed-length fields greater than or equal to 768 bytes are encoded as variable-length fields. For example, CHAR(255) if the maximum byte length of the character set is greater than 3, can exceed 768 bytes if the column is a utf8mb4 character type.

Whether a column is stored off-page depends on the page size and the total size of the rows. When a row is too long, the longest column is selected for off-page storage until the clustered index record fits on a B-tree page. TEXT and BLOB are columns less than or equal to 40 bytes that are stored on the line.

The DYNAMIC row format maintains the efficiency of storing the entire row in the index node where it fits (as done by the COMPACT and REDUNDANT formats), but the DYNAMIC row format avoids filling B-tree nodes with a large number of long column data bytes The problem. The DYNAMIC row format is based on the idea that if part of a long data value is stored off the page, it is usually most efficient to store the entire value off the page. For DYNAMIC format, shorter columns may be retained in B-tree nodes, minimizing the number of overflow pages required for a given row.

DYNAMIC row format supports index key prefixes up to 3072 bytes. This feature is controlled by the innodb_large_prefix variable, which is enabled by default. For more information about innodb_large_prefix, see variable description.

Tables using DYNAMIC row format can be stored in system table spaces, file-per-table table spaces and general table spaces. To DYNAMIC store a table in the system tablespace, disable innodb_file_per_table and use a regular CREATE TABLE or ALTER TABLE statement, or use the TABLESPACE [=] innodb_system table option with CREATE TABLE or ALTER TABLE. The innodb_file_per_table and innodb_file_format variables do not apply to general tablespaces, nor are they applicable when the TABLESPACE [=] innodb_system table option is used to store DYNAMIC tables in the system tablespace.

DYNAMIC row format storage properties

DYNAMIC row format is a deviation from the COMPACT row format.

COMPRESSED row format

COMPRESSED row format provides the same storage features and functionality as the DYNAMIC row format, but with the addition of table and index data Compression support.

Barracuda file format supports COMPRESSED line format.

COMPRESSED row format uses similar internal details off page storage as the DYNAMIC row format, from which additional storage and performance considerations for table and index data are compressed and use smaller page sizes. Using the COMPRESSED row format, the KEY_BLOCK_SIZE option controls how much column data is stored in the clustered index, and how much is placed on the overflow page.

COMPRESSED row format supports index key prefixes up to 3072 bytes. This feature is controlled by the innodb_large_prefix variable, which is enabled by default.

COMPRESSED Tables using row format can be created in a file tablespace or a general tablespace per table. The system tablespace does not support the COMPRESSED row format. To store COMPRESSED tables in a file-per-table tablespace, the innodb_file_per_table variable must be enabled, and innodb_file_format must be set to Barracuda. The innodb_file_per_table and innodb_file_format variables do not apply to general table spaces. General table spaces support all row formats, but it should be noted that due to different physical page sizes, compressed and uncompressed tables cannot coexist in the same general table space. See Section 14.6.3.3, “General Tablespaces” for more information.

COMPRESSED row format storage properties

COMPRESSED row format is a deviation from the COMPACT row format. It's just that there is a little difference when dealing with row overflow data. The first 768 bytes of the string will not be stored at the real data recorded. Instead, all bytes will be stored in other pages, only at the real data recorded. The addresses of other pages are stored here. In addition, the Compressed row format will use a compression algorithm to compress the page.

Related recommendations: "mysql tutorial"

The above is the detailed content of What is InnoDB row storage format. For more information, please follow other related articles on the PHP Chinese website!