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

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

Тем не менее, ниже приведено значение по умолчанию = 1.

Я собираюсь показать вам четыре примеры того, как легко создавать пробелы.

Пример 1:

create table x
(   id int auto_increment primary key,
someOtherUniqueKey varchar(50) not null,
touched int not null,
unique key(someOtherUniqueKey)
);
insert x(touched,someOtherUniqueKey) values (1,'dog') on duplicate key update touched=touched+1;
insert x(touched,someOtherUniqueKey) values (1,'dog') on duplicate key update touched=touched+1;
insert x(touched,someOtherUniqueKey) values (1,'cat') on duplicate key update touched=touched+1;

select * from x;
+----+--------------------+---------+
| id | someOtherUniqueKey | touched |
+----+--------------------+---------+
|  1 | dog                |       2 |
|  3 | cat                |       1 |
+----+--------------------+---------+

Разрыв (id = 2 пропущен) обусловлен одной из нескольких операций, причуд и нервных подергиваний INNODB двигатель. В своем высокопроизводительном режиме параллелизма по умолчанию он выполняет выделение пробелов в диапазоне для различных отправляемых ему запросов. Лучше иметь веские причины для изменения этого параметра, поскольку это влияет на производительность. Что-то вроде более поздних версий MySQL доставляет вам, и вы отключаетесь из-за Гиперфокусировки на пробелах в распечатанных листах (и боссах, которые говорят «Почему у нас пробелы»).

В случае обновления вставки при дублировании ключа (IODKU), он принимает 1 новую строку и выделяет для нее место. Помните, параллелизм и ваши коллеги выполняют одни и те же операции, возможно, сотни одновременно. Когда ИОДКУ превращается в UpdateЧто ж, здесь идет использование этой заброшенной и никогда не вставленной строки с id = 2 для вашего соединения и всех остальных.

Пример 2:

То же самое происходит во время Insert ... Select From как видно в Этот ответ мой. В этом я специально использую MyISAM из-за сообщения о счетчиках, мин, макс, в противном случае причуды разрыва диапазона будут выделять и не заполнять все. И цифры будут выглядеть странно, так как этот ответ имеет дело с фактическими числами. Так что старый двигатель (MyISAM) отлично работал для тесных пробелов. Обратите внимание, что в этом ответе я пытался сделать что-то быстрое и безопасное, и эту таблицу можно преобразовать в INNODB с ALTER TABLE после факта. Если бы я сделал этот пример в INNODB для начала было бы много пробелов (в режиме по умолчанию). Причина Insert ... Select From будет иметь создает пробелы в этом ответе, если бы я использовал INNODB был из-за неопределенности подсчета, механизм, который двигатель выбирает для безопасного (неопределенного) распределения дальности. INNODB Двигатель знает, что операция, естественно, знает, должен создать безопасный пул AUTO_INCREMENT ID, имеет параллелизм (другие пользователи должны думать), и разрывы процветают. Это факт. Попробуйте пример 2 с INNODB двигатель и посмотреть, что вы придумали для мин, макс и кол. Макс не будет равным счету.

Примеры 3 и 4:

Существуют различные ситуации, которые вызывают INNODB Недостатки задокументированы на веб-сайте Percona, поскольку они наталкиваются на большее и документируют их. Например, это происходит во время неудачных вставок из-за ограничений внешнего ключа, замеченных в этом 1452 Ошибка изображения. Или ошибка первичного ключа в этом 1062 Ошибка изображения.

Помните, что INNODB Недостатки — это побочный эффект производительности системы и безопасного двигателя. Это то, что действительно нужно отключить (производительность, более высокая пользовательская статистика, более высокий уровень параллелизма, отсутствие блокировок таблицы) ради более узких диапазонов идентификаторов? Диапазоны, в которых есть отверстия, удаляются в любом случае. Я хотел бы предложить не для моих реализаций, и по умолчанию с производительностью просто отлично.

У меня сейчас проблемы с первичным ключом ID который установлен в
auto increment, Это продолжает увеличиваться ON DUPLICATE KEY

Один из нас, должно быть, неправильно понимает проблему, или вы искажаете ее. ON DUPLICATE KEY UPDATE никогда не создает новую строку, поэтому ее нельзя увеличивать. От документы:

Если вы указываете ON DUPLICATE KEY UPDATE, и вставляется строка,
приведет к дублированию значения в уникальном индексе или PRIMARY KEY, MySQL
выполняет ОБНОВЛЕНИЕ старой строки.

Теперь это, вероятно, тот случай, когда автоинкремент происходит, когда вы вставляете и нет дубликат ключа найден. Если я предположу, что это то, что происходит, мой вопрос будет таким: почему это проблема?

Если вы абсолютно хотите контролировать значение вашего первичного ключа, измените структуру таблицы, чтобы удалить auto-increment флаг, но оставьте его обязательным, ненулевым полем. Это заставит вас самим предоставлять ключи, но я бы поспорил, что это станет еще большей головной болью для вас.

Мне действительно любопытно: зачем вам нужно закрывать все отверстия в ID ценности?