17.9.1.2 Creating Compressed Tables — MySQL 9.2

17.9.1.2 Создание сжатых таблиц

Сжатые таблицы можно создавать в табличных пространствах или в . Сжатие таблиц недоступно для InnoDB . Системное табличное пространство (пространство 0, ) может содержать созданные пользователем таблицы, но также содержит внутренние системные данные, которые никогда не сжимаются. Таким образом, сжатие применяется только к таблицам (и индексам), хранящимся в табличных пространствах «на файл на таблицу» или общих табличных пространствах.

Создание сжатой таблицы в табличном пространстве «на файл на таблицу»

Для создания сжатой таблицы в табличном пространстве «на файл на таблицу» необходимо включить innodb_file_per_table (по умолчанию). Вы можете задать этот параметр в файле конфигурации MySQL (my.cnf или my.ini) или динамически, используя оператор SET.

После настройки параметра innodb_file_per_table укажите в операторе CREATE TABLE или ALTER TABLE предложение ROW_FORMAT=COMPRESSED или предложение KEY_BLOCK_SIZE, или оба, чтобы создать сжатую таблицу в табличном пространстве «на файл на таблицу».

Например, вы можете использовать следующие операторы:

SET GLOBAL innodb_file_per_table=1;
CREATE TABLE t1
 (c1 INT PRIMARY KEY)
 ROW_FORMAT=COMPRESSED
 KEY_BLOCK_SIZE=8;

Создание сжатой таблицы в общем табличном пространстве

Чтобы создать сжатую таблицу в общем табличном пространстве, необходимо определить FILE_BLOCK_SIZE для общего табличного пространства, которое указывается при создании табличного пространства. Значение FILE_BLOCK_SIZE должно быть допустимым размером сжатой страницы по отношению к значению innodb_page_size, и размер страницы сжатой таблицы, определенный предложением CREATE TABLE или ALTER TABLE в операторе CREATE TABLE или ALTER TABLE KEY_BLOCK_SIZE, должен быть равен FILE_BLOCK_SIZE/1024. Например, если innodb_page_size=16384 и FILE_BLOCK_SIZE=8192, то KEY_BLOCK_SIZE таблицы должна быть 8. Дополнительную информацию см. в Разделе 17.6.3.3, «Общие табличные пространства».

Следующий пример демонстрирует создание общего табличного пространства и добавление сжатой таблицы. В примере предполагается значение по умолчанию innodb_page_size 16К. Значение FILE_BLOCK_SIZE 8192 требует, чтобы у сжатой таблицы был KEY_BLOCK_SIZE 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;

Примечания

Начиная с MySQL 9.2, файл табличного пространства для сжатой таблицы создается с использованием физического размера страницы, а не InnoDB размера страницы, что делает начальный размер файла табличного пространства для пустой сжатой таблицы меньше, чем в предыдущих версиях MySQL.
Если вы укажете ROW_FORMAT=COMPRESSED, вы можете опустить KEY_BLOCK_SIZE; значение KEY_BLOCK_SIZE по умолчанию составляет половину значения innodb_page_size.
Если вы укажете допустимое значение KEY_BLOCK_SIZE, вы можете опустить ROW_FORMAT=COMPRESSED; сжатие включается автоматически.
Для определения наилучшего значения для KEY_BLOCK_SIZE,, как правило, создают несколько копий одной и той же таблицы с различными значениями для этого предложения, затем измеряют размер полученных файлов .ibd и видят, как хорошо каждый из них работает с реалистичными данными. Для общих табличных пространств следует помнить, что удаление таблицы не уменьшает размер файла общего табличного пространства .ibd, а также не возвращает место на диске операционной системе. Дополнительную информацию см. в Разделе 17.6.3.3, «Общие табличные пространства».
Значение KEY_BLOCK_SIZE рассматривается как подсказка; необходимый размер может быть изменен InnoDB при необходимости. Для табличных пространств «на файл на таблицу» значение KEY_BLOCK_SIZE может быть меньше или равно значению innodb_page_size. Если вы укажете значение больше, чем значение innodb_page_size, указанное значение игнорируется, выводится предупреждение, а KEY_BLOCK_SIZE устанавливается в половину значения innodb_page_size. Если innodb_strict_mode=ON, ввод некорректного значения KEY_BLOCK_SIZE приводит к ошибке. Для общих табличных пространств допустимые значения KEY_BLOCK_SIZE зависят от настройки FILE_BLOCK_SIZE табличного пространства. Дополнительную информацию см. в Разделе 17.6.3.3, «Общие табличные пространства».
InnoDB поддерживает размеры страниц 32КБ и 64КБ, но эти размеры страниц не поддерживают сжатие. Дополнительную информацию см. в документации innodb_page_size.
Значение по умолчанию для несжатых данных InnoDB составляет 16КБ. В зависимости от сочетания значений параметров MySQL использует размер страницы 1КБ, 2КБ, 4КБ, 8КБ или 16КБ для файла данных табличного пространства (файл .ibd). Фактический алгоритм сжатия не зависит от значения KEY_BLOCK_SIZE; значение определяет размер каждого сжатого фрагмента, что, в свою очередь, влияет на количество строк, которые можно упаковать в каждую сжатую страницу.
При создании сжатой таблицы в табличном пространстве «на файл на таблицу» установка значения KEY_BLOCK_SIZE, равного значению InnoDB, как правило, не приводит к значительному сжатию. Например, установка значения KEY_BLOCK_SIZE=16 обычно не приведет к значительному сжатию, так как обычный размер страницы InnoDB составляет 16КБ. Это значение может быть полезным для таблиц с множеством длинных столбцов BLOB, VARCHAR или TEXT, так как такие значения часто хорошо сжимаются и, следовательно, могут потребовать меньше места, как описано в Разделе 17.9.1.5, «Как работает сжатие для таблиц InnoDB». Для общих табличных пространств значение KEY_BLOCK_SIZE, равное значению InnoDB размера страницы, запрещено. Дополнительную информацию см. в Разделе 17.6.3.3, «Общие табличные пространства».
Все индексы таблицы (включая ) сжимаются с использованием одного и того же размера страницы, как указано в операторе CREATE TABLE или ALTER TABLE. Атрибуты таблицы, такие как ROW_FORMAT и KEY_BLOCK_SIZE, не являются частью синтаксиса CREATE INDEX для InnoDB таблиц и игнорируются, если они указаны (хотя, если они указаны, они отображаются в выводе оператора SHOW CREATE TABLE).
Для параметров конфигурации, связанных с производительностью, см. Раздел 17.9.1.3, «Настройка сжатия для таблиц InnoDB».

Ограничения для сжатых таблиц

Сжатые таблицы не могут храниться в системном табличном пространстве InnoDB.
Общие табличные пространства могут содержать несколько таблиц, но сжатые и несжатые таблицы не могут сосуществовать в одном общем табличном пространстве.
Сжатие применяется ко всей таблице и всем связанным с ней индексам, а не к отдельным строкам, несмотря на название предложения ROW_FORMAT.
InnoDB не поддерживает сжатые временные таблицы. При включенном innodb_strict_mode (по умолчанию), оператор CREATE TEMPORARY TABLE возвращает ошибки, если указаны ROW_FORMAT=COMPRESSED или KEY_BLOCK_SIZE. Если innodb_strict_mode отключен, выводится предупреждение, и временная таблица создается с использованием несжатого формата строк. Те же ограничения применяются к операциям ALTER TABLE над временными таблицами.