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, если он используется.

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