MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
Before creating a compressed table, make sure the
innodb_file_per_table
configuration option is enabled, and
innodb_file_format
is set to
Barracuda
. You can set these parameters in the
MySQL configuration
file my.cnf
or
my.ini
, or with the SET
statement without shutting down the MySQL server.
To enable compression for a table, you use the clauses
ROW_FORMAT=COMPRESSED
,
KEY_BLOCK_SIZE
, or both in a
CREATE TABLE
or
ALTER TABLE
statement.
To create a compressed table, you might use statements like these:
SET GLOBAL innodb_file_per_table=1; SET GLOBAL innodb_file_format=Barracuda; CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
If you specify ROW_FORMAT=COMPRESSED
, you
can omit KEY_BLOCK_SIZE
; the default page
size value is used, which is half the
innodb_page_size
value.
If you specify KEY_BLOCK_SIZE
, you can omit
ROW_FORMAT=COMPRESSED
; compression is
enabled automatically.
To determine the best value for
KEY_BLOCK_SIZE
, typically you create
several copies of the same table with different values for
this clause, then measure the size of the resulting
.ibd
files and see how well each performs
with a realistic
workload.
The KEY_BLOCK_SIZE
value is treated as a
hint; a different size could be used by
InnoDB
if necessary. A value of 0
represents the default compressed page size, which is half of
the innodb_page_size
value.
The KEY_BLOCK_SIZE
can only be less than or
equal to the innodb_page_size
value. If you specify a value greater than the
innodb_page_size
value, the
specified value is ignored, a warning is issued, and
KEY_BLOCK_SIZE
is set to half of the
innodb_page_size
value. If
innodb_strict_mode=ON
,
specifying an invalid KEY_BLOCK_SIZE
value
returns an error.
For additional performance-related configuration options, see Section 14.9.3, “Tuning Compression for InnoDB Tables”.
The default uncompressed size of InnoDB
data
pages is 16KB. Depending on the
combination of option values, MySQL uses a page size of 1KB, 2KB,
4KB, 8KB, or 16KB for the .ibd
file of the
table. The actual compression algorithm is not affected by the
KEY_BLOCK_SIZE
value; the value determines how
large each compressed chunk is, which in turn affects how many
rows can be packed into each compressed page.
Setting KEY_BLOCK_SIZE
equal to the
InnoDB
page
size does not typically result in much compression. For
example, setting KEY_BLOCK_SIZE=16
typically
would not result in much compression, since the normal
InnoDB
page
size is 16KB. This setting may still be useful for tables
with many long BLOB
,
VARCHAR
or
TEXT
columns, because such values
often do compress well, and might therefore require fewer
overflow pages as
described in Section 14.9.5, “How Compression Works for InnoDB Tables”.
All indexes of a table (including the
clustered index) are
compressed using the same page size, as specified in the
CREATE TABLE
or
ALTER TABLE
statement. Table
attributes such as ROW_FORMAT
and
KEY_BLOCK_SIZE
are not part of the
CREATE INDEX
syntax for
InnoDB
tables, and are ignored if they are
specified (although you see them in the output of the
SHOW CREATE TABLE
statement).
Because MySQL versions prior to 5.1 cannot process compressed
tables, using compression requires specifying the configuration
parameter
innodb_file_format=Barracuda
, to
avoid accidentally introducing compatibility issues.
Table compression is also not available for the InnoDB
system tablespace.
The system tablespace (space 0, the ibdata*
files) can contain user data, but it also contains internal system
information, and therefore is never compressed. Thus, compression
applies only to tables (and indexes) stored in their own
tablespaces, that is, created with the
innodb_file_per_table
option
enabled.
Compression applies to an entire table and all its associated
indexes, not to individual rows, despite the clause name
ROW_FORMAT
.