InnoDB поддерживает сжатие страниц для таблиц, находящихся в файловых системах. Эта функция называется Прозрачное сжатие страниц. Сжатие страниц включается путем указания атрибута COMPRESSION с помощью CREATE TABLE или ALTER TABLE. Поддерживаемые алгоритмы сжатия включают Zlib и LZ4.

Поддерживаемые платформы

Для сжатия страниц требуется поддержка разреженных файлов и «дырок». Сжатие страниц поддерживается в Windows с NTFS и в следующих подмножествах поддерживаемых MySQL Linux-платформ, где поддержка «дырок» предоставляется на уровне ядра:

Как работает сжатие страниц

При записи страницы она сжимается с использованием указанного алгоритма сжатия. Сжатые данные записываются на диск, где механизм «дырок» освобождает пустые блоки с конца страницы. Если сжатие не удаётся, данные записываются как есть.

Размер «дырки» в Linux

В системах Linux размер блока файловой системы является единицей размера, используемой для создания «дырок». Поэтому сжатие страниц работает только в том случае, если данные страницы можно сжать до размера, меньшего или равного размеру страницы InnoDB минус размер блока файловой системы. Например, если innodb_page_size=16K и размер блока файловой системы составляет 4 КБ, данные страницы должны быть сжаты менее чем или равны 12 КБ, чтобы сделать создание «дырок» возможным.

Размер «дырки» в Windows

В системах Windows основная инфраструктура для разреженных файлов основана на сжатии NTFS. Размер «дырки» — это единица сжатия NTFS, которая в 16 раз больше размера кластера NTFS. Размеры кластеров и их единицы сжатия показаны в следующей таблице:

Сжатие страниц в системах Windows работает только в том случае, если данные страницы можно сжать до размера, меньшего или равного размеру страницы InnoDB минус размер единицы сжатия.

По умолчанию размер кластера NTFS составляет 4 КБ, для которого размер единицы сжатия составляет 64 КБ. Это означает, что сжатие страниц не приносит пользы для стандартной конфигурации Windows NTFS, так как максимальное значение innodb_page_size также составляет 64 КБ.

Для работы сжатия страниц в Windows файловая система должна быть создана с размером кластера меньше 4 КБ, а innodb_page_size должно быть как минимум вдвое больше размера единицы сжатия. Например, для работы сжатия страниц в Windows вы можете создать файловую систему с размером кластера 512 байт (что имеет единицу сжатия 8 КБ) и инициализировать InnoDB со значением innodb_page_size 16 КБ или больше.

Включение сжатия страниц

Для включения сжатия страниц укажите атрибут COMPRESSION в CREATE TABLE операторе. Например:

CREATE TABLE t1 (c1 INT) COMPRESSION="zlib";

Вы также можете включить сжатие страниц в операторе ALTER TABLE. Однако ALTER TABLE ... COMPRESSION обновляет только атрибут сжатия табличного пространства. Записи в табличное пространство, которые происходят после установки нового алгоритма сжатия, используют новые настройки, но для применения нового алгоритма сжатия к существующим страницам необходимо перестроить таблицу с помощью OPTIMIZE TABLE.

ALTER TABLE t1 COMPRESSION="zlib";
OPTIMIZE TABLE t1;

Отключение сжатия страниц

Чтобы отключить сжатие страниц, установите значение COMPRESSION=None с помощью ALTER TABLE. Записи в табличное пространство, которые происходят после установки COMPRESSION=None, больше не используют сжатие страниц. Чтобы расжать существующие страницы, необходимо перестроить таблицу с помощью OPTIMIZE TABLE после установки COMPRESSION=None.

ALTER TABLE t1 COMPRESSION="None";
OPTIMIZE TABLE t1;

Метаданные сжатия страниц

Метаданные сжатия страниц находятся в таблице Информационной схемы INNODB_TABLESPACES в следующих столбцах:

В следующем примере метаданные сжатия страниц для таблицы employees извлекаются из таблицы Информационной схемы INNODB_TABLESPACES.

# Create the employees table with Zlib page compression

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
) COMPRESSION="zlib";

# Insert data (not shown)

# Query page compression metadata in INFORMATION_SCHEMA.INNODB_TABLESPACES

mysql> SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM
       INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='employees/employees'\G
*************************** 1. row ***************************
SPACE: 45
NAME: employees/employees
FS_BLOCK_SIZE: 4096
FILE_SIZE: 23068672
ALLOCATED_SIZE: 19415040

Метаданные сжатия страниц для таблицы employees показывают, что видимый размер файла составляет 23068672 байта, а фактический размер файла (со сжатием страниц) — 19415040 байт. Размер блока файловой системы составляет 4096 байт, что является размером блока, используемого для создания «дырок».

Идентификация таблиц с включенным сжатием страниц

Чтобы определить таблицы, для которых включено сжатие страниц, вы можете проверить столбец CREATE_OPTIONS таблицы Информационной схемы TABLES для таблиц, определённых с атрибутом COMPRESSION:

mysql> SELECT TABLE_NAME, TABLE_SCHEMA, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES 
       WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';
+------------+--------------+--------------------+
| TABLE_NAME | TABLE_SCHEMA | CREATE_OPTIONS     |
+------------+--------------+--------------------+
| employees  | test         | COMPRESSION="zlib" |
+------------+--------------+--------------------+

SHOW CREATE TABLE также отображает атрибут COMPRESSION, если он используется.

Ограничения сжатия страниц и заметки по использованию