To tylko jedna z 2 stron tej notatki. Zaloguj się aby zobaczyć ten dokument.
Zobacz
całą notatkę
Compressing BLOB, VARCHAR, and TEXT Columns
In an InnoDB table, BLOB, VARCHAR, and TEXT columns that are not part of the primary key may be
stored on separately allocated overflow pages. We refer to these columns as off-page columns. Their
values are stored on singly-linked lists of overflow pages.
For tables created in ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, the values of BLOB,
TEXT, or 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, MySQL 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 older versions of MySQL use the Antelope file format, which supports only
ROW_FORMAT=REDUNDANT and ROW_FORMAT=COMPACT. In these formats, MySQL stores the first 768
bytes of BLOB, VARCHAR, and 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,
MySQL 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 BLOB, TEXT, or VARCHAR columns if the data is highly compressible, as is often the
case with text data. Image data, such as JPEG, is typically already compressed and so does not benefit
much from being stored in a compressed table; the double compression can waste CPU cycles for little
or no space savings.
The overflow pages are of the same size as other pages. A row containing ten columns stored offpage
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 costs for
(…)
… 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 costs for BLOB, VARCHAR, or 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…
... zobacz całą notatkę
Komentarze użytkowników (0)