Обзор онлайн-DDL InnoDB

Таблицы InnoDB поддерживают онлайн-DDL, что позволяет выполнять DML параллельно и использовать оптимизации для избежания ненужного копирования таблицы.

Оператор ALTER TABLE поддерживает две клаузы, используемые для реализации онлайн-DDL:

• ALGORITHM - Эта клауза управляет тем, как выполняется операция DDL.
• LOCK - Эта клауза управляет уровнем конкурентности, разрешенной во время выполнения операции DDL.

Алгоритмы изменения

InnoDB поддерживает несколько алгоритмов для выполнения операций DDL. Это обеспечивает значительное улучшение производительности по сравнению с предыдущими версиями. Поддерживаемые алгоритмы:

• DEFAULT - Это подразумевает поведение по умолчанию для конкретной операции.
• COPY
• INPLACE
• NOCOPY - Это было добавлено в MariaDB 10.3.7.
• INSTANT - Это было добавлено в MariaDB 10.3.7.

Указание алгоритма изменения

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

• COPY
• INPLACE
• NOCOPY
• INSTANT

Когда пользователь указывает алгоритм изменения для операции DDL, MariaDB необязательно использует этот конкретный алгоритм для операции. Она интерпретирует выбор следующим образом:

• Если пользователь указывает COPY, тогда InnoDB использует алгоритм COPY.
• Если пользователь указывает любой другой алгоритм, InnoDB интерпретирует этот выбор как наименее эффективный алгоритм, который пользователь готов принять. Это означает, что если пользователь указывает INPLACE, тогда InnoDB будет использовать наиболее эффективный алгоритм, поддерживаемый конкретной операцией из набора (INPLACE, NOCOPY, INSTANT). Аналогично, если пользователь указывает NOCOPY, тогда InnoDB будет использовать наиболее эффективный алгоритм, поддерживаемый конкретной операцией из набора (NOCOPY, INSTANT).

Также существует специальное значение, которое можно указать:

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

Указание алгоритма изменения с помощью клаузы ALGORITHM

InnoDB поддерживает клаузу ALGORITHM.

Клаузу ALGORITHM можно использовать для указания наименее эффективного алгоритма, который пользователь готов принять. Она поддерживается операторами ALTER TABLE и CREATE INDEX.

Например, если пользователь хотел добавить столбец в таблицу, но только если операция использовала алгоритм, по крайней мере, такой же эффективный, как INPLACE, он мог бы выполнить следующее:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50)
);

ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE;

В MariaDB 10.3 и более поздних версиях вышеуказанная операция фактически использовала бы алгоритм INSTANT, потому что операция ADD COLUMN поддерживает алгоритм INSTANT, а алгоритм INSTANT более эффективен, чем алгоритм INPLACE.

Указание алгоритма изменения с помощью системных переменных

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD COLUMN c varchar(50);

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50)
);

SET SESSION old_alter_table=1;
ALTER TABLE tab ADD COLUMN c varchar(50);

Поддерживаемые алгоритмы изменения

Описание поддерживаемых алгоритмов приведено ниже.

Алгоритм по умолчанию

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

Это означает, что если операция поддерживает алгоритм INSTANT, то она будет использовать этот алгоритм по умолчанию. Если операция не поддерживает алгоритм INSTANT, но поддерживает алгоритм NOCOPY, то она будет использовать этот алгоритм по умолчанию. Если операция не поддерживает алгоритм NOCOPY, но поддерживает алгоритм INPLACE, то она будет использовать этот алгоритм по умолчанию.

Алгоритм COPY

Алгоритм COPY относится к оригинальному алгоритму ALTER TABLE.

Когда используется алгоритм COPY, MariaDB по существу выполняет следующие операции:

-- Create a temporary table with the new definition
CREATE TEMPORARY TABLE tmp_tab (
...
);

-- Copy the data from the original table
INSERT INTO tmp_tab
   SELECT * FROM original_tab;

-- Drop the original table
DROP TABLE original_tab;

-- Rename the temporary table, so that it replaces the original one
RENAME TABLE tmp_tab TO original_tab;

Этот алгоритм очень неэффективен, но он универсален, поэтому он работает со всеми движками хранения.

Если алгоритм COPY указан в клаузе ALGORITHM или с системной переменной alter_algorithm, то алгоритм COPY будет использоваться, даже если это не нужно. Это может привести к длительному копированию таблицы. Если требуются несколько операций ALTER TABLE, для каждой из которых требуется пересоздание таблицы, то лучше указать все операции в одном операторе ALTER TABLE, чтобы таблица была перестроена только один раз.

Использование алгоритма COPY с InnoDB

Если алгоритм COPY используется с таблицей InnoDB, то применяются следующие утверждения:

• Таблица будет перестроена с использованием текущих значений системных переменных innodb_file_per_table, innodb_file_format и innodb_default_row_format.

• Для выполнения копирования таблицы будет создана временная таблица. Эта временная таблица будет находиться в том же каталоге, что и исходная таблица, а ее имя файла будет иметь формат #sql${PID}_${THREAD_ID}_${TMP_TABLE_COUNT}, где ${PID} - идентификатор процесса mysqld, ${THREAD_ID} - идентификатор подключения, а ${TMP_TABLE_COUNT} - количество временных таблиц, открытых подключением. Таким образом, datadir может содержать файлы с именами файлов, подобными #sql1234_12_1.ibd.

• Операция вставляет по одной записи за раз в каждый индекс, что очень неэффективно.

• InnoDB не использует буфер сортировки.

• В MariaDB 10.2.13, MariaDB 10.3.5 и более поздних версиях операция копирования таблицы создает гораздо меньше записей в журнале отката InnoDB undo log. См. MDEV-11415 для получения дополнительной информации.

• Операция копирования таблицы создает много записей в журнале InnoDB redo log.

Алгоритм INPLACE

Алгоритм COPY может быть невероятно медленным, потому что вся таблица должна быть скопирована и перестроена. Алгоритм INPLACE был введен как способ избежать этого, выполняя операции на месте и избегая копирования и перестройки таблицы, когда это возможно.

Когда используется алгоритм INPLACE, базовый движок хранения использует оптимизации для выполнения операции, избегая копирования и перестройки таблицы. Однако INPLACE является несколько неудачным названием, так как некоторые операции все еще могут потребовать перестройки таблицы для некоторых движков хранения. Независимо от этого, для некоторых движков хранения несколько операций могут быть выполнены без полной копии таблицы.

Более точное название для алгоритма было бы ENGINE алгоритм, так как движок хранения принимает решение о том, как реализовать алгоритм.

Если операция ALTER TABLE поддерживает алгоритм INPLACE, она может быть выполнена с оптимизациями базовым движком хранения, но может быть перестроена.

Если алгоритм INPLACE указан с помощью предложения ALGORITHM или системной переменной alter_algorithm и если операция ALTER TABLE не поддерживает алгоритм INPLACE, то будет поднято исключение. Например:

SET SESSION alter_algorithm='INPLACE';

ALTER TABLE tab MODIFY COLUMN c int;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

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

Использование алгоритма INPLACE с InnoDB

Если алгоритм INPLACE используется с таблицей InnoDB, то применяются следующие правила:

• Операция может потребовать записи файлов сортировки в директории, определенной системной переменной innodb_tmpdir.

• Операция также может потребовать записи временного файла журнала для отслеживания изменений данных запросами DML, выполненными во время операции. Максимальный размер этого файла журнала настраивается системной переменной innodb_online_alter_log_max_size.

• Некоторые операции требуют перестроения таблицы, даже если алгоритм ошибочно называется "на месте". Это включает операции, такие как добавление или удаление столбцов, добавление первичного ключа, изменение столбца на NULL и т. д.

• Если операция требует перестроения таблицы, то операция может потребовать создания временных таблиц.
  • Может потребоваться создание временной промежуточной таблицы для фактического перестроения таблицы.
    • В MariaDB 10.2.19 и более поздних версиях эта временная таблица будет находиться в той же директории, что и исходная таблица, а ее имя файла будет в формате #sql${PID}_${THREAD_ID}_${TMP_TABLE_COUNT}, где ${PID} — идентификатор процесса mysqld, ${THREAD_ID} — идентификатор подключения и ${TMP_TABLE_COUNT} — количество открытых временных таблиц для подключения. Поэтому, в datadir могут содержаться файлы с именами файлов, похожими на #sql1234_12_1.ibd.
    • В MariaDB 10.2.18 и более ранних версиях эта временная таблица будет находиться в той же директории, что и исходная таблица, а ее имя файла будет в формате #sql-ib${TABLESPACE_ID}-${RAND}, где ${TABLESPACE_ID} — идентификатор табличного пространства исходной таблицы в InnoDB, а ${RAND} — случайно инициализированное число. Поэтому в datadir могут содержаться файлы с именами файлов, похожими на #sql-ib230291-1363966925.ibd.
  • При замене исходной таблицы перестроенной таблицей, может потребоваться переименование исходной таблицы с использованием временного имени таблицы.
    • Если сервер MariaDB 10.3 или более поздний, или если он работает под MariaDB 10.2, а системная переменная innodb_safe_truncate установлена в OFF, то формат будет фактически #sql-ib${TABLESPACE_ID}-${RAND}, где ${TABLESPACE_ID} — идентификатор табличного пространства исходной таблицы в InnoDB, а ${RAND} — случайно инициализированное число. Таким образом, в datadir могут содержаться файлы с именами файлов, похожими на #sql-ib230291-1363966925.ibd.
    • Если сервер работает под MariaDB 10.1 или более ранней версией, или если он работает под MariaDB 10.2, а системная переменная innodb_safe_truncate установлена в ON, то переименованная таблица будет иметь временное имя таблицы в формате #sql-ib${TABLESPACE_ID}, где ${TABLESPACE_ID} — идентификатор табличного пространства исходной таблицы в InnoDB. Поэтому в datadir могут содержаться файлы с именами файлов, похожими на #sql-ib230291.ibd.

• Необходимый объем памяти для вышеперечисленных пунктов может достигать размера исходной таблицы или больше в некоторых случаях.

• Некоторые операции выполняются мгновенно, если требуется изменить только метаданные таблицы. Это включает такие операции, как переименование столбца, изменение значения DEFAULT столбца и т. д.

Операции, поддерживаемые InnoDB с алгоритмом INPLACE

Что касается разрешенных операций, алгоритм INPLACE поддерживает подмножество операций, поддерживаемых алгоритмом COPY, и он поддерживает надмножество операций, поддерживаемых алгоритмом NOCOPY.

Дополнительную информацию см. в разделе InnoDB Online DDL Operations with ALGORITHM=INPLACE.

Алгоритм NOCOPY

Алгоритм INPLACE иногда может быть неожиданно медленным в случаях, когда требуется перестроение кластеризованного индекса, потому что при перестроении кластеризованного индекса требуется перестроение всей таблицы. Алгоритм NOCOPY был разработан для того, чтобы избежать этого.

Если операция ALTER TABLE поддерживает алгоритм NOCOPY, то она может быть выполнена без перестроения кластеризованного индекса.

Если алгоритм NOCOPY указан с помощью предложения ALGORITHM или системной переменной alter_algorithm и если операция ALTER TABLE не поддерживает алгоритм NOCOPY, то будет поднято исключение. Например:

SET SESSION alter_algorithm='NOCOPY';

ALTER TABLE tab MODIFY COLUMN c int;
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

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

Операции, поддерживаемые InnoDB с алгоритмом NOCOPY

Что касается разрешенных операций, алгоритм NOCOPY поддерживает подмножество операций, поддерживаемых алгоритмом INPLACE, и он поддерживает надмножество операций, поддерживаемых алгоритмом INSTANT.

Дополнительную информацию см. в разделе InnoDB Online DDL Operations with ALGORITHM=NOCOPY.

Алгоритм INSTANT

Алгоритм INPLACE иногда может быть неожиданно медленным в случаях, когда требуется изменение файлов данных. Алгоритм INSTANT был разработан для того, чтобы избежать этого.

Если операция ALTER TABLE поддерживает алгоритм INSTANT, то она может быть выполнена без изменения каких-либо файлов данных.

Если алгоритм INSTANT указан с помощью предложения ALGORITHM или системной переменной alter_algorithm и если операция ALTER TABLE не поддерживает алгоритм INSTANT, то будет поднято исключение. Например:

SET SESSION alter_algorithm='INSTANT';

ALTER TABLE tab MODIFY COLUMN c int;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

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

Операции, поддерживаемые InnoDB с алгоритмом INSTANT

Что касается разрешенных операций, алгоритм INSTANT поддерживает подмножество операций, поддерживаемых алгоритмом NOCOPY.

Дополнительную информацию см. в разделе InnoDB Online DDL Operations with ALGORITHM=INSTANT.

Стратегии блокировки ALTER

InnoDB поддерживает несколько стратегий блокировки для выполнения операций DDL. Это существенно повышает производительность по сравнению с предыдущими версиями. Поддерживаемые стратегии блокировки:

• DEFAULT — это подразумевает стандартное поведение для конкретной операции.
• NONE
• SHARED
• EXCLUSIVE

Независимо от используемой стратегии блокировки для выполнения операции DDL, InnoDB будет захватывать эксклюзивную блокировку таблицы на короткое время в начале и в конце выполнения операции. Это означает, что все активные транзакции, которые могли обращаться к таблице, должны быть подтверждены или прерваны для продолжения операции. Это относится к большинству операторов DDL, таких как ALTER TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, RENAME TABLE и т. д.

Указание стратегии блокировки ALTER

Указание стратегии блокировки ALTER с помощью предложения LOCK

Оператор ALTER TABLE поддерживает предложение LOCK.

Предложение LOCK может использоваться для указания стратегии блокировки, которую пользователь готов принять. Она поддерживается операторами ALTER TABLE и CREATE INDEX.

Например, если пользователь хочет добавить столбец в таблицу, но только если операция не использует блокировку, то он может выполнить следующее:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50)
);

ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE, LOCK=NONE;

Если предложение LOCK не указано явно, то операция использует LOCK=DEFAULT.

Указание стратегии блокировки ALTER с помощью ALTER ONLINE TABLE

ALTER ONLINE TABLE эквивалентно LOCK=NONE. Поэтому оператор ALTER ONLINE TABLE может использоваться для обеспечения того, чтобы операция ALTER TABLE позволяла все одновременные операции DML.

Поддерживаемые стратегии блокировки ALTER

Поддерживаемые алгоритмы описаны ниже более подробно.

Чтобы узнать, какие стратегии блокировки InnoDB поддерживаются для каждой операции, см. страницы, описывающие поддерживаемые операции для каждого алгоритма:

• Операции InnoDB Online DDL с ALGORITHM=INPLACE
• Операции InnoDB Online DDL с ALGORITHM=NOCOPY
• Операции InnoDB Online DDL с ALGORITHM=INSTANT

Стратегия блокировки по умолчанию

По умолчанию, если LOCK=DEFAULT указано, или если LOCK вообще не указано, приобретается наименее жесткая блокировка таблицы, поддерживаемая для конкретной операции. Это позволяет максимальное количество одновременности, поддерживаемое для данной операции.

Стратегия блокировки NONE

Стратегия блокировки NONE выполняет операцию без приобретения какой-либо блокировки на таблице. Это позволяет все одновременные операции DML.

Если эта стратегия блокировки недопустима для операции, генерируется ошибка.

Стратегия блокировки SHARED

Стратегия блокировки SHARED выполняет операцию после приобретения блокировки чтения на таблице. Это разрешает только чтение одновременных операций DML.

Если эта стратегия блокировки недопустима для операции, генерируется ошибка.

Стратегия блокировки EXCLUSIVE

Стратегия блокировки EXCLUSIVE выполняет операцию после приобретения блокировки записи на таблице. Это не разрешает одновременные операции DML.