In a clustered index,
that are not part of the primary key may be stored on
separately allocated (“overflow”) pages. We call
these “off-page columns” whose values are stored
on singly-linked lists of overflow pages.
For tables created in
ROW_FORMAT=COMPRESSED, the values of
VARCHAR columns may be stored fully
off-page, depending on their length and the length of the
entire row. For columns that are stored off-page, the
clustered index record only contains 20-byte pointers to the
overflow pages, one per column. 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 to fit entirely within the page
of the clustered index, InnoDB chooses the longest columns
for off-page storage until the row fits on the clustered index
page. As noted above, if a row does not fit by itself on a
compressed page, an error occurs.
Tables created in previous versions of InnoDB use the
“Antelope” file format, which supports only
ROW_FORMAT=COMPACT. In these
formats, InnoDB stores the first 768 bytes of
TEXT columns in the clustered index record
along with the primary key. The 768-byte prefix is followed by
a 20-byte pointer to the overflow pages that contain the rest
of the column value.
When a table is in
COMPRESSED format, all data written to
overflow pages is compressed “as is”; that is,
InnoDB applies the zlib compression algorithm to the entire
data item. Other than the data, compressed overflow pages
contain an uncompressed header and trailer comprising a page
checksum and a link to the next overflow page, among other
things. Therefore, very significant storage savings can be
obtained for longer
if the data is highly compressible, as is often the case with
text data (but not previously compressed images).
The overflow pages are of the same size as other pages. A row containing ten columns stored off-page occupies ten overflow pages, even if the total length of the columns is only 8K bytes. In an uncompressed table, ten uncompressed overflow pages occupy 160K bytes. In a compressed table with an 8K page size, they occupy only 80K bytes. Thus, it is often more efficient to use compressed table format for tables with long column values.
Using a 16K compressed page size can reduce storage and I/O
TEXT columns, because such data often
compress well, and might therefore require fewer
“overflow” pages, even though the B-tree nodes
themselves take as many pages as in the uncompressed form.