Чому в колонці AUTO_INCREMENT з'являються пропуски в нумерації?

Пропуски в AUTO_INCREMENT - нормальна поведінка, а не помилка. Лічильник гарантує унікальність і зростання, але не неперервність.

Звідки беруться пропуски:

• Відкат транзакції. Значення видається під час вставки й не повертається при ROLLBACK:

START TRANSACTION;
INSERT INTO orders (total) VALUES (100);   -- отримав id = 41
ROLLBACK;
INSERT INTO orders (total) VALUES (200);   -- id = 42, 41 пропущено назавжди

• Невдала вставка. Порушення унікальності чи іншого обмеження після того, як номер уже виділено.
• INSERT ... ON DUPLICATE KEY UPDATE та INSERT IGNORE. Номер може бути виділений, навіть коли рядок оновлено чи пропущено. Таблиця з частими upsert-ами може «з'їдати» номери значно швидше, ніж росте кількість рядків.
• Масові вставки. Для INSERT ... SELECT і LOAD DATA, коли кількість рядків заздалегідь невідома, InnoDB виділяє номери блоками (1, 2, 4, 8...), і залишок блоку пропадає.
• Видалення рядків - звісно, теж.

Режим блокування лічильника innodb_autoinc_lock_mode. У MySQL 8 за замовчуванням 2 (interleaved): паралельні вставки отримують номери без табличного блокування, тож номери з різних транзакцій можуть перемежовуватися. Це швидко, але безпечно лише з рядковим форматом бінарного журналу (за замовчуванням ROW).

Після перезапуску сервера: від MySQL 8.0 лічильник зберігається (записується в журнал повтору). У 5.7 і раніше після перезапуску він ставав MAX(id) + 1, тож видалені «хвостові» номери могли видатися повторно.

Чого не робити:

• не використовувати id як номер рахунку чи накладної, якщо закон вимагає неперервної нумерації, - для цього окрема послідовність, яку видають у транзакції з блокуванням;
• не «ущільнювати» id після видалень - на них посилаються зовнішні ключі, кеші, URL.

Про переповнення: INT UNSIGNED закінчується на ~4,29 млрд. Таблиця з частими upsert-ами може дістатися ліміту раніше, ніж здається. Тому в Laravel id() створює BIGINT UNSIGNED.

Докладніше в документації: AUTO_INCREMENT в InnoDB