17.9.1.2 Creating Compressed Tables — MySQL 8.4

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 KEY_BLOCK_SIZE, должен быть равен FILE_BLOCK_SIZE/1024. Например, если innodb_page_size=16384 и FILE_BLOCK_SIZE=8192, размер страницы таблицы должен быть 8. Дополнительную информацию см. в Разделе 17.6.3.3, «Общие табличные пространства».

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