17.6.1.6 AUTO_INCREMENT Handling in InnoDB — MySQL 9.2

17.6.1.6 Обработка AUTO_INCREMENT в InnoDB

InnoDB предоставляет настраиваемый механизм блокировки, который может значительно улучшить масштабируемость и производительность SQL-запросов, добавляющих строки в таблицы с AUTO_INCREMENT столбцами. Для использования механизма AUTO_INCREMENT с таблицей InnoDB, столбец AUTO_INCREMENT должен быть определен как первый или единственный столбец в каком-либо индексе, таким образом, чтобы можно было выполнить эквивалентный индексированному SELECT MAX(ai_col) поиску в таблице для получения максимального значения столбца. Индекс не обязательно должен быть PRIMARY KEY или UNIQUE, но для предотвращения дублирования значений в столбце AUTO_INCREMENT, рекомендуется использовать эти типы индексов.

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

Режимы блокировки InnoDB AUTO_INCREMENT

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

В описании настроек innodb_autoinc_lock_mode используются следующие термины:

“INSERT-подобные” запросы

Все запросы, которые генерируют новые строки в таблице, включая INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT и LOAD DATA. Включает “простые вставки”, “массовые вставки” и вставки “смешанного типа”.
“Простые вставки”

Запросы, для которых количество строк, подлежащих вставке, может быть определено заранее (при первоначальной обработке запроса). Это включает запросы INSERT и REPLACE с одной или несколькими строками, у которых нет вложенных подзапросов, но не INSERT ... ON DUPLICATE KEY UPDATE.
“Массовые вставки”

Запросы, для которых количество строк, подлежащих вставке (и количество необходимых значений автоинкремента), неизвестно заранее. Это включает INSERT ... SELECT, REPLACE ... SELECT и LOAD DATA запросы, но не обычные INSERT. InnoDB присваивает новые значения для столбца AUTO_INCREMENT по одному за раз по мере обработки каждой строки.
“Вставки смешанного типа”

Это запросы “простая вставка”, которые указывают значение автоинкремента для некоторых (но не всех) новых строк. Пример: где c1 является столбцом AUTO_INCREMENT таблицы t1:
```
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
```
Другой тип “вставок смешанного типа” — INSERT ... ON DUPLICATE KEY UPDATE, который в худшем случае представляет собой INSERT, за которым следует UPDATE, где назначенное значение для столбца AUTO_INCREMENT может или не может использоваться на этапе обновления.

Для переменной innodb_autoinc_lock_mode существуют три возможных значения. Значения 0, 1 или 2 соответствуют режимам блокировки “традиционный”, “последовательный” или “перемежающийся” соответственно. Режим перемежающейся блокировки (innodb_autoinc_lock_mode=2) является по умолчанию.

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

Режим блокировки по умолчанию (“традиционный” режим блокировки)

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

В этом режиме блокировки все операторы типа “INSERT-like” получают специальную блокировку на уровне таблицы AUTO-INC для вставок в таблицы с AUTO_INCREMENT столбцами. Эта блокировка обычно удерживается до конца оператора (а не до конца транзакции), чтобы гарантировать, что значения автоинкремента назначаются в предсказуемом и повторяемом порядке для данного набора операторов INSERT, и чтобы гарантировать, что значения автоинкремента, назначенные любым данным оператором, будут последовательными.

В случае репликации на основе операторов это означает, что при репликации оператора SQL на сервере-реплике для столбца автоинкремента используются те же значения, что и на исходном сервере. Результат выполнения нескольких операторов INSERT детерминирован, и реплика воспроизводит те же данные, что и на источнике. Если значения автоинкремента, сгенерированные несколькими операторами INSERT, были бы переплетены, результат выполнения двух одновременных операторов INSERT был бы недетерминированным и не мог бы надёжно распространяться на сервер реплики с репликацией на основе операторов.

Для большей ясности рассмотрим пример с использованием этой таблицы:
```
CREATE TABLE t1 (
  c1 INT(11) NOT NULL AUTO_INCREMENT,
  c2 VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB;
```
Предположим, что выполняются две транзакции, каждая из которых вставляет строки в таблицу со столбцом AUTO_INCREMENT. Одна транзакция использует оператор INSERT ... SELECT, который вставляет 1000 строк, а другая использует простой оператор INSERT, который вставляет одну строку:
```
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
```
InnoDB не может заранее определить, сколько строк извлечено из SELECT в операторе INSERT в Tx1, и он назначает значения автоинкремента по одному, по мере выполнения оператора. При наличии блокировки на уровне таблицы, удерживаемой до конца оператора, только один оператор INSERT, относящийся к таблице t1, может выполняться одновременно, и генерирование номеров автоинкремента разными операторами не переплетается. Значения автоинкремента, сгенерированные оператором Tx1 INSERT ... SELECT, являются последовательными, а (единственное) значение автоинкремента, используемое оператором INSERT в Tx2, меньше или больше всех значений, использованных для Tx1, в зависимости от того, какой оператор выполняется первым.

Пока операторы SQL выполняются в том же порядке при повторном воспроизведении из двоичного журнала (при использовании репликации на основе операторов или в сценариях восстановления), результаты остаются такими же, как при первом выполнении Tx1 и Tx2. Таким образом, блокировки на уровне таблицы, удерживаемые до конца оператора, делают операторы INSERT с использованием автоинкремента безопасными для использования с репликацией на основе операторов. Однако эти блокировки на уровне таблицы ограничивают параллельность и масштабируемость при выполнении нескольких транзакций с операторами вставки одновременно.

В приведённом примере, если бы не было блокировки на уровне таблицы, значение столбца автоинкремента, используемого для INSERT в Tx2, зависит от того, когда именно выполняется оператор. Если INSERT Tx2 выполняется, когда выполняется INSERT Tx1 (а не до его начала или после его завершения), конкретные значения автоинкремента, назначенные двумя операторами INSERT, являются недетерминированными и могут изменяться от выполнения к выполнению.

В режиме блокировки последовательно, InnoDB может избежать использования блокировок на уровне таблицы AUTO-INC для операторов “простых вставок”, где количество строк известно заранее, и при этом сохранить детерминированное выполнение и безопасность для репликации на основе операторов.

Если вы не используете двоичный журнал для повторного выполнения операторов SQL в рамках восстановления или репликации, режим блокировки перемежающийся может быть использован для устранения всех блокировок на уровне таблицы AUTO-INC для ещё большей параллельности и производительности, но при этом допускается наличие пробелов в номерах автоинкремента, назначенных оператором, и возможна интерпретация номеров, назначенных одновременно выполняющимися операторами.
Режим блокировки последовательно (“последовательный” режим блокировки)

В этом режиме “массовые вставки” используют специальную блокировку на уровне таблицы AUTO-INC и удерживают её до конца оператора. Это относится ко всем операторам INSERT ... SELECT, REPLACE ... SELECT и LOAD DATA. Одновременно может выполняться только один оператор, удерживающий блокировку AUTO-INC. Если исходная таблица операции массовой вставки отличается от целевой таблицы, блокировка AUTO-INC на целевой таблице накладывается после наложения общей блокировки на первую строку, выбранную из исходной таблицы. Если исходная и целевая таблицы операции массовой вставки совпадают, блокировка AUTO-INC накладывается после наложения общих блокировок на все выбранные строки.

“Простые вставки” (для которых количество строк, которые нужно вставить, известно заранее) избегают блокировок на уровне таблицы AUTO-INC, получая необходимое количество значений автоинкремента под управлением мьютекса (лёгкой блокировки), который удерживается только в течение процесса выделения, а не до завершения оператора. Блокировка на уровне таблицы AUTO-INC не используется, если блокировка AUTO-INC не удерживается другой транзакцией. Если другая транзакция удерживает блокировку AUTO-INC, “простая вставка” ожидает блокировки AUTO-INC, как если бы это была “массовая вставка”.

Этот режим блокировки гарантирует, что в случае операторов INSERT, где количество строк не известно заранее (и где номера автоинкремента назначаются по мере выполнения оператора), все номера автоинкремента, назначенные любым оператором типа “INSERT-like”, будут последовательными, и операции будут безопасными для репликации на основе операторов.

Проще говоря, этот режим блокировки значительно улучшает масштабируемость и безопасен для использования с репликацией на основе операторов. Кроме того, как и в режиме блокировки “традиционный”, номера автоинкремента, назначенные любым данным оператором, последовательны. Семантика не изменяется по сравнению с режимом “традиционный” для любого оператора, использующего автоинкремент, за одним важным исключением.

Исключением являются “смешанные вставки”, где пользователь предоставляет явные значения для столбца AUTO_INCREMENT для некоторых, но не всех строк в “простой вставке” с несколькими строками. Для таких вставок InnoDB выделяет больше значений автоинкремента, чем количество строк для вставки. Однако все автоматически назначенные значения генерируются последовательно (и, следовательно, больше) значения автоинкремента, сгенерированного наиболее недавно выполненным предыдущим оператором. “Избыточные” числа теряются.
Режим блокировки перемежающийся (“перемежающийся” режим блокировки)

В этом режиме блокировки ни один оператор “INSERT-like” не использует блокировку на уровне таблицы AUTO-INC, и несколько операторов могут выполняться одновременно. Это самый быстрый и масштабируемый режим блокировки, но он не безопасен при использовании репликации на основе операторов или сценариев восстановления, когда операторы SQL повторно воспроизводятся из двоичного журнала.

В этом режиме блокировки значения автоинкремента гарантированно уникальны и монотонно возрастают для всех выполняющихся одновременно операторов “INSERT-like”. Однако, поскольку несколько операторов могут генерировать номера одновременно (то есть, выделение номеров перемежается между операторами), значения, сгенерированные для строк, вставленных любым данным оператором, могут не быть последовательными.

Если выполняются только операторы “простые вставки”, где количество строк для вставки известно заранее, в номерах, сгенерированных для одного оператора, нет пробелов, за исключением “смешанных вставок”. Однако при выполнении “массовых вставок” в значениях автоинкремента, назначенных любым данным оператором, могут быть пробелы.

Последствия использования режима блокировки AUTO_INCREMENT в InnoDB

Использование автоинкремента с репликацией

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

Если вы используете репликацию на основе строк или смешанную репликацию, все режимы блокировки автоинкремента безопасны, так как репликация на основе строк не чувствительна к порядку выполнения SQL-запросов (а смешанный формат использует репликацию на основе строк для любых инструкций, которые небезопасны для репликации на основе инструкций).
«Потерянные» значения автоинкремента и разрывы последовательности

Во всех режимах блокировки (0, 1 и 2), если транзакция, сгенерировавшая значения автоинкремента, отменяется, эти значения автоинкремента «теряются». Как только значение генерируется для столбца автоинкремента, оно не может быть отменено, независимо от того, завершена ли инструкция типа «INSERT-подобная» или нет, и независимо от того, отменена ли содержащая её транзакция. Такие потерянные значения не используются повторно. Таким образом, могут быть разрывы в значениях, хранящихся в столбце AUTO_INCREMENT таблицы.
Указание NULL или 0 для столбца AUTO_INCREMENT

Во всех режимах блокировки (0, 1 и 2), если пользователь указывает NULL или 0 для столбца AUTO_INCREMENT в инструкции INSERT, система InnoDB обрабатывает строку так, как если бы значение не было указано, и генерирует для неё новое значение.
Присвоение отрицательного значения столбцу AUTO_INCREMENT

Во всех режимах блокировки (0, 1 и 2) поведение механизма автоинкремента не определено, если вы присваиваете отрицательное значение столбцу AUTO_INCREMENT.
Если значение AUTO_INCREMENT становится больше максимального целого числа для указанного целочисленного типа

Во всех режимах блокировки (0, 1 и 2) поведение механизма автоинкремента не определено, если значение становится больше максимального целого числа, которое может быть сохранено в указанном целочисленном типе.
Разрывы в значениях автоинкремента для «массовых вставок»

При innodb_autoinc_lock_mode, установленной в 0 («традиционный») или 1 («последовательный»), значения автоинкремента, генерируемые каждой инструкцией, являются последовательными без разрывов, потому что блокировка таблицы AUTO-INC удерживается до конца инструкции, и только одна такая инструкция может выполняться одновременно.

При innodb_autoinc_lock_mode, установленной в 2 («перемежающийся»), в значениях автоинкремента, генерируемых «массовыми вставками», могут быть разрывы, но только если выполняются одновременно инструкции типа «INSERT-подобные».

В режимах блокировки 1 или 2 разрывы могут возникать между последовательными инструкциями, потому что для массовых вставок точное количество значений автоинкремента, требуемое каждой инструкцией, может быть неизвестно, и возможна переоценка.
Значения автоинкремента, назначенные «вставками смешанного режима»

Рассмотрим «вставку смешанного режима», где «простая вставка» указывает значение автоинкремента для некоторых (но не всех) результирующих строк. Такая инструкция ведет себя по-разному в режимах блокировки 0, 1 и 2. Например, предположим, что c1 — это столбец AUTO_INCREMENT таблицы t1, и что последнее сгенерированное последовательное число — 100.
```
mysql> CREATE TABLE t1 (
    -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> c2 CHAR(1)
    -> ) ENGINE = INNODB;
```
Теперь рассмотрим следующую инструкцию «вставка смешанного режима»:
```
mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
```
При innodb_autoinc_lock_mode, установленной в 0 («традиционный»), четыре новые строки будут такими:
```
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+
```
Следующее доступное значение автоинкремента будет 103, потому что значения автоинкремента выделяются по одному, а не все сразу в начале выполнения инструкции. Этот результат верен независимо от того, выполняются ли одновременно инструкции типа «INSERT-подобные» (любого типа).

При innodb_autoinc_lock_mode, установленной в 1 («последовательный»), четыре новые строки также будут такими:
```
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+
```
Однако в этом случае следующее доступное значение автоинкремента будет 105, а не 103, потому что четыре значения автоинкремента выделяются в момент обработки инструкции, но используются только два. Этот результат верен независимо от того, выполняются ли одновременно инструкции типа «INSERT-подобные» (любого типа).

При innodb_autoinc_lock_mode, установленной в 2 («перемежающийся»), четыре новые строки будут такими:
```
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
+-----+------+
```
Значения x и y уникальны и больше, чем любые ранее сгенерированные строки. Однако конкретные значения x и y зависят от количества значений автоинкремента, генерируемых одновременно выполняемыми инструкциями.

Наконец, рассмотрим следующую инструкцию, выпущенную, когда последнее сгенерированное последовательное число — 100:
```
mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');
```
При любом значении innodb_autoinc_lock_mode эта инструкция генерирует ошибку дублирования ключа 23000 (Can't write; duplicate key in table), потому что 101 выделяется для строки (NULL, 'b'), и вставка строки (101, 'c') терпит неудачу.
Изменение значений столбца AUTO_INCREMENT посреди последовательности инструкций INSERT

Если вы изменяете значение столбца AUTO_INCREMENT на значение, большее текущего максимального значения автоинкремента, новое значение сохраняется, и последующие операции INSERT распределяют значения автоинкремента, начиная с нового, большего значения. Это поведение показано в следующем примере:
```
mysql> CREATE TABLE t1 (
    -> c1 INT NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (c1)
    ->  ) ENGINE = InnoDB;

mysql> INSERT INTO t1 VALUES(0), (0), (3);

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
+----+

mysql> UPDATE t1 SET c1 = 4 WHERE c1 = 1;

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  2 |
|  3 |
|  4 |
+----+

mysql> INSERT INTO t1 VALUES(0);

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  2 |
|  3 |
|  4 |
|  5 |
+----+
```

Инициализация счётчика AUTO_INCREMENT InnoDB

В этом разделе описывается, как InnoDB инициализирует счётчики AUTO_INCREMENT.

Если вы указываете столбец AUTO_INCREMENT для таблицы InnoDB, объект таблицы в оперативной памяти содержит специальный счётчик, счётчик автоинкремента, используемый при назначении новых значений для столбца.

Текущее максимальное значение счётчика автоинкремента записывается в журнал переигрывания каждый раз, когда оно изменяется, и сохраняется в словаре данных при каждом контрольном пункте; это делает текущее максимальное значение счётчика автоинкремента постоянным после перезапуска сервера.

При перезапуске сервера после нормального завершения работы InnoDB инициализирует счётчик автоинкремента в оперативной памяти, используя текущее максимальное значение автоинкремента, сохранённое в словаре данных.

При перезапуске сервера во время восстановления после сбоя InnoDB инициализирует счётчик автоинкремента в оперативной памяти, используя текущее максимальное значение автоинкремента, сохранённое в словаре данных, и сканирует журнал переигрывания в поисках значений счётчика автоинкремента, записанных с момента последнего контрольного пункта. Если значение, записанное в журнал переигрывания, больше значения счётчика в оперативной памяти, применяется значение, записанное в журнал переигрывания. Однако в случае неожиданного выхода сервера гарантия повторного использования ранее выделенного значения автоинкремента отсутствует. Каждый раз, когда текущее максимальное значение автоинкремента изменяется из-за операции INSERT или UPDATE, новое значение записывается в журнал переигрывания, но если неожиданный выход происходит до того, как журнал переигрывания запишется на диск, ранее выделенное значение может быть повторно использовано при инициализации счётчика автоинкремента после перезапуска сервера.

Единственный случай, в котором InnoDB использует эквивалент оператора SELECT MAX(ai_col) FROM table_name FOR UPDATE для инициализации счётчика автоинкремента, это импорт таблицы без файла метаданных .cfg. В противном случае текущее максимальное значение счётчика автоинкремента считывается из файла метаданных .cfg, если он присутствует. Помимо инициализации значения счётчика, эквивалент оператора SELECT MAX(ai_col) FROM table_name используется для определения текущего максимального значения счётчика автоинкремента таблицы при попытке установить значение счётчика на значение, меньшее или равное сохранённому значению счётчика с помощью оператора ALTER TABLE ... AUTO_INCREMENT = N. Например, вы можете попытаться установить значение счётчика на меньшее значение после удаления некоторых записей. В этом случае таблица должна быть просканирована, чтобы убедиться, что новое значение счётчика не меньше или равно фактическому текущему максимальному значению счётчика.

Перезапуск сервера не отменяет действие параметра таблицы AUTO_INCREMENT = N. Если вы инициализируете счётчик автоинкремента конкретным значением или изменяете значение счётчика автоинкремента на большее значение, новое значение сохраняется после перезапуска сервера.

Примечание

ALTER TABLE ... AUTO_INCREMENT = N может изменить значение счётчика автоинкремента только на большее значение, чем текущее максимальное.

Текущее максимальное значение автоинкремента сохраняется, предотвращая повторное использование ранее выделенных значений.

Если оператор SHOW TABLE STATUS проверяет таблицу до инициализации счётчика автоинкремента, InnoDB открывает таблицу и инициализирует значение счётчика, используя текущее максимальное значение автоинкремента, хранящееся в словаре данных. Затем значение хранится в памяти для использования в последующих операциях вставки или обновления. Инициализация значения счётчика использует обычное эксклюзивное блокирующее чтение таблицы, которое длится до конца транзакции. InnoDB следует той же процедуре при инициализации счётчика автоинкремента для новой таблицы, у которой заданное пользователем значение автоинкремента больше 0.

После инициализации счётчика автоинкремента, если вы не явно не указываете значение автоинкремента при вставке строки, InnoDB неявно увеличивает счётчик и назначает новое значение столбцу. Если вы вставляете строку, которая явно указывает значение столбца автоинкремента, и это значение больше текущего максимального значения счётчика, счётчик устанавливается на указанное значение.

InnoDB использует счётчик автоинкремента в оперативной памяти, пока сервер работает. Когда сервер останавливается и перезапускается, InnoDB повторно инициализирует счётчик автоинкремента, как описано ранее.

Переменная auto_increment_offset определяет стартовую точку для значения столбца AUTO_INCREMENT. Значение по умолчанию — 1.

Переменная auto_increment_increment контролирует интервал между последовательными значениями столбца. Значение по умолчанию — 1.

Примечания

Когда целые числа AUTO_INCREMENT столбца заканчиваются, последующая операция INSERT возвращает ошибку дубликата ключа. Это общее поведение MySQL.