This section discusses the
COMPRESSED row formats for InnoDB tables. You
can only create these kinds of tables in
tablespaces when the
option is set to
Barracuda, or in
tablespaces. InnoDB file format settings do not apply to
General tablespaces provide support for all row formats and
associated features. For more information, see
Section 14.5.9, “InnoDB General Tablespaces”.
The Barracuda file format also allows the
formats for InnoDB tables stored in file-per-table tablespaces.
When a table is created with
ROW_FORMAT=COMPRESSED, long column values
are stored fully off-page, and the clustered index record contains
only a 20-byte pointer to the overflow page.
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 B-tree page.
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 this new 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 off-page. With
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 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 14.7, “InnoDB Compressed Tables”.