В этом разделе описываются некоторые внутренние детали реализации сжатия для таблиц InnoDB. Информация здесь может быть полезна для настройки производительности, но не является необходимой для базового использования сжатия.

Алгоритмы сжатия

Некоторые операционные системы реализуют сжатие на уровне файловой системы. Файлы обычно делятся на блоки фиксированного размера, которые сжимаются в блоки переменного размера, что легко приводит к фрагментации. Каждый раз, когда что-то внутри блока изменяется, весь блок пересжимается перед записью на диск. Эти свойства делают эту технику сжатия непригодной для использования в интенсивно обновляемой системе баз данных.

MySQL использует сжатие с помощью известной библиотеки zlib, которая реализует алгоритм сжатия LZ77. Этот алгоритм зрелый, надёжный и эффективный как с точки зрения использования ЦП, так и с точки зрения уменьшения размера данных. Алгоритм является «бесплаточным», так что исходные несжатые данные всегда могут быть восстановлены из сжатой формы. Сжатие LZ77 работает, обнаруживая последовательности данных, повторяющиеся в сжимаемых данных. Образцы значений в ваших данных определяют, насколько хорошо они сжимаются, но типичные пользовательские данные часто сжимаются на 50% или более.

В отличие от сжатия, выполняемого приложением или функциями сжатия некоторых других систем управления базами данных, сжатие InnoDB применяется как к пользовательским данным, так и к индексам. Во многих случаях индексы могут составлять 40-50% или более от общего размера базы данных, поэтому это различие существенно. Когда сжатие хорошо работает для набора данных, размер файлов данных InnoDB (пространство таблиц или файлы .ibd) составляет от 25% до 50% от несжатого размера или может быть меньше. В зависимости от этого меньшего размера базы данных, в свою очередь, может привести к снижению ввода/вывода и увеличению пропускной способности при умеренных затратах на увеличение использования ЦП. Вы можете настроить баланс между уровнем сжатия и нагрузкой на ЦП, изменив параметр конфигурации innodb_compression_level.

Хранение данных InnoDB и сжатие

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

в таблицах InnoDB также являются B-деревьями, содержащими пары значений: ключ индекса и указатель на строку в кластеризованном индексе. Указатель фактически является значением первичного ключа таблицы, который используется для доступа к кластеризованному индексу, если требуется получить доступ к столбцам, отличным от ключа индекса и первичного ключа. Записи вторичного индекса всегда должны помещаться на одной странице B-дерева.

Сжатие узлов B-дерева (как кластеризованных, так и вторичных индексов) обрабатывается по-разному от сжатия используемых для хранения длинных VARCHAR, BLOB или TEXT столбцов, как описано в следующих разделах.

Сжатие страниц B-дерева

Поскольку они часто обновляются, страницы B-дерева требуют специального обращения. Важно минимизировать количество разбиений узлов B-дерева, а также минимизировать необходимость распаковки и повторного сжатия их содержимого.

Одна из техник, используемых MySQL, заключается в хранении некоторой системной информации в узле B-дерева в несжатом виде, что облегчает некоторые обновления на месте. Например, это позволяет помечать строки на удаление и удалять их без каких-либо операций сжатия.

Кроме того, MySQL пытается избежать ненужной распаковки и повторного сжатия страниц индексов при их изменении. В каждой странице B-дерева система сохраняет несжатый «журнал изменений», чтобы записывать изменения, внесенные в страницу. Обновления и вставки небольших записей могут быть записаны в этот журнал изменений без необходимости полной перестройки всей страницы.

Когда место для журнала изменений заканчивается, InnoDB распаковывает страницу, применяет изменения и пересжимает страницу. Если пересжатие не удаётся (ситуация, известная как ), узлы B-дерева разделяются, и процесс повторяется до тех пор, пока обновление или вставка не будут успешны.

Чтобы избежать частых отказов сжатия при интенсивной работе с записью, например, для приложений, MySQL иногда резервирует некоторое свободное место (заполнение) на странице, чтобы журнал изменений заполнялся быстрее, и страница пересжимается, пока есть достаточно места, чтобы избежать её разделения. Размер заполнения, оставленного на каждой странице, варьируется, поскольку система отслеживает частоту разделения страниц. На загруженном сервере, выполняющем частые записи в сжатые таблицы, вы можете настроить параметры конфигурации innodb_compression_failure_threshold_pct и innodb_compression_pad_pct_max, чтобы настроить эту механику.

Как правило, MySQL требует, чтобы каждая страница B-дерева в таблице InnoDB могла вместить по крайней мере две записи. Для сжатых таблиц это требование смягчено. Листовые страницы узлов B-дерева (как первичного ключа, так и вторичных индексов) должны вмещать только одну запись, но эта запись должна поместиться в несжатом виде в журнале изменений на странице. Если innodb_strict_mode ON, MySQL проверяет максимальный размер строки во время CREATE TABLE или CREATE INDEX. Если строка не помещается, выводится следующее сообщение об ошибке: ERROR HY000: Too big row.

Если вы создаёте таблицу, когда innodb_strict_mode выключен, и последующее INSERT или UPDATE предложение пытается создать запись индекса, которая не помещается в размер сжатой страницы, операция завершается ошибкой ERROR 42000: Row size too large. (В этом сообщении об ошибке не указано, для какого индекса запись слишком большая, или не указан размер записи индекса или максимальный размер записи на конкретной странице индекса.) Для решения этой проблемы перестройте таблицу с помощью ALTER TABLE и выберите больший размер сжатой страницы (KEY_BLOCK_SIZE), сократите индексы с префиксами столбцов или отключите сжатие полностью с помощью ROW_FORMAT=DYNAMIC или ROW_FORMAT=COMPACT.

innodb_strict_mode не относится к общим пространствам таблиц, которые также поддерживают сжатые таблицы. Правила управления пространством таблиц для общих пространств таблиц строго соблюдаются независимо от innodb_strict_mode. Для получения дополнительной информации см. Раздел 15.1.22, «CREATE TABLESPACE Statement».

Сжатие столбцов BLOB, VARCHAR и TEXT

В таблице InnoDB BLOB, VARCHAR и TEXT столбцы, которые не являются частью первичного ключа, могут храниться в отдельных выделенных . Мы называем эти столбцы . Их значения хранятся в связанных списках страниц переполнения.

Для таблиц, созданных в ROW_FORMAT=DYNAMIC или ROW_FORMAT=COMPRESSED, значения BLOB, TEXT или VARCHAR столбцов могут быть полностью сохранены вне страницы, в зависимости от их длины и длины всей строки. Для столбцов, которые хранятся вне страницы, запись кластеризованного индекса содержит только 20-байтовые указатели на страницы переполнения, по одному на каждый столбец. В зависимости от размера страницы и общего размера строки, хранятся ли какие-либо столбцы вне страницы. Если строка слишком длинная, чтобы полностью уместиться на странице кластеризованного индекса, MySQL выбирает самые длинные столбцы для хранения вне страницы до тех пор, пока строка не поместится на странице кластеризованного индекса. Как отмечено выше, если строка не помещается сама по себе на сжатую страницу, возникает ошибка.

Таблицы, использующие ROW_FORMAT=REDUNDANT и ROW_FORMAT=COMPACT, хранят первые 768 байт BLOB, VARCHAR и TEXT столбцов в записи кластеризованного индекса вместе с первичным ключом. 768-байтовый префикс сопровождается 20-байтовым указателем на страницы переполнения, содержащие остальную часть значения столбца.

Когда таблица находится в формате COMPRESSED, все данные, записанные на страницы переполнения, сжимаются «как есть»; то есть MySQL применяет алгоритм сжатия zlib к всему элементу данных. Помимо данных, сжатые страницы переполнения содержат несжатый заголовок и трейлер, состоящие из контрольной суммы страницы и ссылки на следующую страницу переполнения, среди прочего. Поэтому значительные экономии места могут быть получены для длинных BLOB, TEXT или VARCHAR столбцов, если данные сильно сжимаемы, как это часто бывает с текстовыми данными. Данные изображений, такие как JPEG, обычно уже сжаты, и поэтому не сильно выигрывают от хранения в сжатой таблице; двойное сжатие может тратить циклы ЦП с небольшими или без экономии места.

Страницы переполнения имеют тот же размер, что и другие страницы. Строка, содержащая десять столбцов, хранящихся вне страницы, занимает десять страниц переполнения, даже если общая длина столбцов составляет всего 8 КБ. В несжатой таблице десять несжатых страниц переполнения занимают 160 КБ. В сжатой таблице с размером страницы 8 КБ они занимают всего 80 КБ. Таким образом, для таблиц с длинными значениями столбцов часто эффективнее использовать формат сжатой таблицы.

Для табличных пространств, использование 16К сжатой страницы может уменьшить затраты на хранение и ввод-вывод для BLOB, VARCHAR или TEXT столбцов, поскольку такие данные часто хорошо сжимаются и, следовательно, могут потребовать меньше страниц переполнения, даже если узлы B-дерева занимают столько же страниц, как и в несжатой форме. Общие табличные пространства не поддерживают размер страницы сжатия 16К (KEY_BLOCK_SIZE). Для получения дополнительной информации см. Раздел 17.6.3.3, «Общие табличные пространства».

Сжатие и пул буфера InnoDB

В таблице со сжатым InnoDB, каждая сжатая страница (будь то 1К, 2К, 4К или 8К) соответствует несжатой странице размером 16К байт (или меньшему размеру, если innodb_page_size установлено). Для доступа к данным на странице MySQL считывает сжатую страницу с диска, если она еще не находится в , а затем распаковывает страницу до исходной формы. В этом разделе описывается, как InnoDB управляет пулом буфера в отношении страниц сжатых таблиц.

Для минимизации ввода-вывода и уменьшения необходимости распаковки страницы, иногда пул буфера содержит как сжатую, так и несжатую форму страницы базы данных. Для освобождения места для других необходимых страниц базы данных MySQL может извлечь из пула буфера несжатую страницу, оставив сжатую страницу в памяти. Или, если страница некоторое время не использовалась, сжатая форма страницы может быть записана на диск, чтобы освободить место для других данных. Таким образом, в любой момент времени пул буфера может содержать как сжатую, так и несжатую форму страницы, или только сжатую форму страницы, или ни одну из них.

MySQL отслеживает, какие страницы следует хранить в памяти, а какие извлекать, используя список наименее недавно используемых (), так что (часто используемые) данные имеют тенденцию оставаться в памяти. При доступе к сжатым таблицам MySQL использует адаптивный алгоритм LRU для достижения надлежащего баланса сжатых и несжатых страниц в памяти. Этот адаптивный алгоритм чувствителен к тому, работает ли система в или режиме. Цель состоит в том, чтобы избежать траты слишком многого времени на распаковку страниц, когда ЦП загружен, и избежать избыточного ввода-вывода, когда у ЦП есть свободные циклы, которые могут быть использованы для распаковки сжатых страниц (которые могут уже находиться в памяти). Когда система ограничена вводом-выводом, алгоритм предпочитает извлекать несжатую копию страницы, а не обе копии, чтобы освободить больше места для других страниц диска, которые должны стать резидентными в памяти. Когда система ограничена ЦП, MySQL предпочитает извлекать и сжатую, и несжатую страницу, чтобы больше памяти можно было использовать для “актуальных” страниц и уменьшить необходимость распаковки данных в памяти только в сжатом виде.

Сжатие и файлы журнала InnoDB

Перед записью сжатой страницы на , MySQL записывает копию страницы в журнал поновления (если она была пересжата с момента последней записи в базу данных). Это делается для обеспечения того, что журналы поновления могут быть использованы для , даже в маловероятном случае, что библиотека zlib обновлена, и это изменение вносит проблему совместимости со сжатыми данными. Поэтому можно ожидать некоторое увеличение размера , или необходимость более частых , при использовании сжатия. Величина увеличения размера файла журнала или частоты контрольных точек зависит от количества раз, когда сжатые страницы изменяются таким образом, что требуется реорганизация и пересжатие.

Чтобы создать сжатую таблицу в табличном пространстве по файлам на таблицу, innodb_file_per_table должен быть включен. Нет зависимости от настройки innodb_file_per_table при создании сжатой таблицы в общем табличном пространстве. Для получения дополнительной информации см. Раздел 17.6.3.3, «Общие табличные пространства».