Compressed tables can be created in file-per-table tablespaces or in general tablespaces. Table compression is not available for the InnoDB system tablespace. The system tablespace (space 0, the .ibdata files) can contain user-created tables, but it also contains internal system data, which is never compressed. Thus, compression applies only to tables (and indexes) stored in file-per-table or general tablespaces.
Creating a Compressed Table in File-Per-Table Tablespace
To create a compressed table in a file-per-table tablespace,
innodb_file_per_table must be
enabled (the default). You can set this parameter in the MySQL
configuration file (
my.ini) or dynamically, using a
option is configured, specify the
ROW_FORMAT=COMPRESSED clause or
KEY_BLOCK_SIZE clause, or both, in a
CREATE TABLE or
ALTER TABLE statement to create a
compressed table in a file-per-table tablespace.
For example, you might use the following statements:
SET GLOBAL innodb_file_per_table=1; CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Creating a Compressed Table in a General Tablespace
To create a compressed table in a general tablespace,
FILE_BLOCK_SIZE must be defined for the
general tablespace, which is specified when the tablespace is
FILE_BLOCK_SIZE value must be a
valid compressed page size in relation to the
innodb_page_size value, and the
page size of the compressed table, defined by the
CREATE TABLE or
KEY_BLOCK_SIZE clause, must be equal to
FILE_BLOCK_SIZE/1024. For example, if
KEY_BLOCK_SIZE of the table must be 8. For
more information, see Section 184.108.40.206, “General Tablespaces”.
The following example demonstrates creating a general tablespace
and adding a compressed table. The example assumes a default
innodb_page_size of 16K. The
FILE_BLOCK_SIZE of 8192 requires that the
compressed table have a
KEY_BLOCK_SIZE of 8.
mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB; mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
As of MySQL 8.0, the tablespace file for a compressed table is created using the physical page size instead of the
InnoDBpage size, which makes the initial size of a tablespace file for an empty compressed table smaller than in previous MySQL releases.
If you specify
ROW_FORMAT=COMPRESSED, you can omit
KEY_BLOCK_SIZEsetting defaults to half the
If you specify a valid
KEY_BLOCK_SIZEvalue, 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
.ibdfiles and see how well each performs with a realistic workload. For general tablespaces, keep in mind that dropping a table does not reduce the size of the general tablespace
.ibdfile, nor does it return disk space to the operating system. For more information, see Section 220.127.116.11, “General Tablespaces”.
KEY_BLOCK_SIZEvalue is treated as a hint; a different size could be used by
InnoDBif necessary. For file-per-table tablespaces, the
KEY_BLOCK_SIZEcan only be less than or equal to the
innodb_page_sizevalue. If you specify a value greater than the
innodb_page_sizevalue, the specified value is ignored, a warning is issued, and
KEY_BLOCK_SIZEis set to half of the
innodb_strict_mode=ON, specifying an invalid
KEY_BLOCK_SIZEvalue returns an error. For general tablespaces, valid
KEY_BLOCK_SIZEvalues depend on the
FILE_BLOCK_SIZEsetting of the tablespace. For more information, see Section 18.104.22.168, “General Tablespaces”.
InnoDBsupports 32KB and 64KB page sizes but these page sizes do not support compression. For more information, refer to the
The default uncompressed size of
InnoDBdata pages is 16KB. Depending on the combination of option values, MySQL uses a page size of 1KB, 2KB, 4KB, 8KB, or 16KB for the tablespace data file (
.ibdfile). The actual compression algorithm is not affected by the
KEY_BLOCK_SIZEvalue; the value determines how large each compressed chunk is, which in turn affects how many rows can be packed into each compressed page.
When creating a compressed table in a file-per-table tablespace, setting
KEY_BLOCK_SIZEequal to the
InnoDBpage size does not typically result in much compression. For example, setting
KEY_BLOCK_SIZE=16typically would not result in much compression, since the normal
InnoDBpage size is 16KB. This setting may still be useful for tables with many long
TEXTcolumns, because such values often do compress well, and might therefore require fewer overflow pages as described in Section 22.214.171.124, “How Compression Works for InnoDB Tables”. For general tablespaces, a
KEY_BLOCK_SIZEvalue equal to the
InnoDBpage size is not permitted. For more information, see Section 126.96.36.199, “General Tablespaces”.
All indexes of a table (including the clustered index) are compressed using the same page size, as specified in the
ALTER TABLEstatement. Table attributes such as
KEY_BLOCK_SIZEare not part of the
CREATE INDEXsyntax for
InnoDBtables, and are ignored if they are specified (although, if specified, they appear in the output of the
SHOW CREATE TABLEstatement).
For performance-related configuration options, see Section 188.8.131.52, “Tuning Compression for InnoDB Tables”.
Restrictions on Compressed Tables
Compressed tables cannot be stored in the
General tablespaces can contain multiple tables, but compressed and uncompressed tables cannot coexist within the same general tablespace.
Compression applies to an entire table and all its associated indexes, not to individual rows, despite the clause name
InnoDBdoes not support compressed temporary tables. When
innodb_strict_modeis enabled (the default),
CREATE TEMPORARY TABLEreturns errors if
KEY_BLOCK_SIZEis specified. If
innodb_strict_modeis disabled, warnings are issued and the temporary table is created using a non-compressed row format. The same restrictions apply to
ALTER TABLEoperations on temporary tables.