DYNAMIC and COMPRESSED Row Formats

DYNAMIC and COMPRESSED Row Formats
This section discusses the DYNAMIC and COMPRESSED row formats for InnoDB tables. You can only
create these kinds of tables when the innodb_file_format [1752] configuration option is set to
Barracuda. (The Barracuda file format also allows the COMPACT and REDUNDANT row formats.)
When a table is created with ROW_FORMAT=DYNAMIC or 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.
The DYNAMIC row format maintains the efficiency of storing the entire row in the index node if it fits
(as do the COMPACT and REDUNDANT 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.
The COMPRESSED row format uses similar internal details for off-page storage as the DYNAMIC
row 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 option
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 5.4.6,
“Working with InnoDB Compressed Tables”.
... zobacz całą notatkę