Pre-General Availability Draft: 2017-12-16
When a table is created with
InnoDB can store long variable-length column
TEXT types) fully off-page, with
the clustered index record containing only a 20-byte pointer to
the overflow page.
InnoDB also encodes
fixed-length fields greater than or equal to 768 bytes in length
as variable-length fields. For example, a
CHAR(255) column can exceed 768 bytes if the
maximum byte length of the character set is greater than 3, as it
Whether any columns are stored off-page depends on the page size
and the total size of the row. When the row is too long,
InnoDB chooses the longest columns for off-page
storage until the clustered index record fits on the
BLOB columns that are less than or
equal to 40 bytes are always stored in-line.
DYNAMIC row format maintains the efficiency
of storing the entire row in the index node if it fits (as do the
formats), but the
DYNAMIC row format avoids the
problem of filling B-tree nodes with a large number of data bytes
of long columns. The
DYNAMIC format is based on
the idea that if a portion of a long data value is stored
off-page, it is usually most efficient to store all of the value
DYNAMIC format, shorter columns
are likely to remain in the B-tree node, minimizing the number of
overflow pages needed for any given row.
COMPRESSED row format uses similar internal
details for off-page storage as the
format, with additional storage and performance considerations
from the table and index data being compressed and using smaller
page sizes. With 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
overflow pages. For full details about the
COMPRESSED row format, see
Section 15.9, “InnoDB Table and Page Compression”.
row formats support index key prefixes up to 3072 bytes.
Tables that use the
COMPRESSED row format can
be created in file-per-table
tablespaces. The system tablespace does not support the
COMPRESSED row format. To store a
COMPRESSED table in a file-per-table
must be enabled. The
configuration options is not applicable to general tablespaces.
General tablespaces support all row formats with the caveat that
compressed and uncompressed tables cannot coexist in the same
general tablespace due to different physical page sizes. For more
information about general tablespaces, see
Section 15.7.9, “InnoDB General Tablespaces”.
DYNAMIC tables can be stored in file-per-table
tablespaces, general tablespaces, and the system tablespace. To
DYNAMIC tables in the system tablespace,
you can either disable
innodb_file_per_table and use a
CREATE TABLE or
TABLE statement, or you can use the
[=] innodb_system table option with
ALTER TABLE without having
to alter your
configuration option is not applicable to general tablespaces, nor
are they applicable when using the
innodb_system table option to store
DYNAMIC tables in the system tablespace.
InnoDB does not support compressed temporary
is enabled (the default),
TABLE returns an error if
KEY_BLOCK_SIZE is specified. If
innodb_strict_mode is disabled,
warnings are issued and the temporary table is created using a
non-compressed row format.
formats are variations of the
format and therefore handle
storage in the same way as the
format. For more information, see
Section 184.108.40.206, “The Physical Row Structure of an InnoDB Table”.