Как найти первый свободный ID в базе данных?

Question

[pibiv]

Добрый день.

В БД есть таблица в которой 2 поля
user_id(autoincrement)
full_name

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

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

То есть при создании пользователя, если есть свободный id например 325 то дать ему его.

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

Здравый смысл мне подсказывает что взять и перебрать несколько милионов записей это мягко говоря не вариант.

Интересует элегантное решение, в данный момент я остановился на следующих вариантах

• Создать отдельную таблицу в которой я буду хранить свободные ID
  
• Осуществлять поиск SQL запросом средней сдожности
  
  
  

Запрос типа такого

SELECT MIN( uid +1 ) 
FROM  `users `
WHERE uid +1 NOT 
IN (

SELECT uid
FROM  `users`
)

Загводка еще в том что с системой может одновременно работать 100 или 200 пользователей, и я так подразумеваю с точки зрения SQL это тоже может быть накладно.

Итог, золотая середина это допонтиельная таблица с свободными ID?

Answer 1

[Виталий]

> Создать отдельную таблицу в которой я буду хранить свободные ID
лучший вариант.

Comments

[pibiv]

Спасибо за то что поняли ситуацию!

[vovik0134]

На самом деле данное решение является плохим, если вы не будете использовать чтение с блокировкой (for update). Приведу пример:
1. Вы пытаетесь создать пользователя и получаете для него идентификатор из своей таблицы
2. Параллельно с вами другой администратор (роль с привилегией создания пользователя) пытается создать пользователя. Он также обращается к вашей таблице со свободными идентификаторами. И пока ваша транзакция (пункт 1) не зафиксирована для него свободным номером будет тот номер, который вы взяли себе (пункт 1)
3. Далее возможно два варианта
3.1. Вы фиксируете транзакцию раньше и тогда другой администратор при фиксации транзакции получает исключение dup_val_on_index (дублирование первичного ключа)
3.2. Другой администратор фиксирует транзакцию раньше и тогда вы получаете исключение dup_val_on_index.

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

Использование чтения с блокировкой тоже плохое решение, т.к. оно увеличивает время ожидания (время процедуры создания пользователя).

Что же является хорошим решением? Тут зависит от вас. Не вполне понятно зачем вы хотите использовать освободившиеся идентификаторы. Боитесь переполнения integer/big integer?

Обычным решением такого рода проблем является использования последовательностей (auto increment), т.к. в данном случае исчезают обе описанные выше проблемы. Вся ответственность за выдачу идентификаторов ложится на ядро СУБД.

Хорошо подумайте надо ли вам искать свободные идентификаторы? Любой из способов реализации такого механизма будет хуже по производительности, чем auto increment.

Answer 2

[Руслан Федосеев]

не надо вам это... Ну не обращайте вы внимание на значение автоинкрементного поля, забудьте про него...

Answer 3

[Алексей]

Правильно говорят. После запроса Insert можно получить присвоенный ID и оперировать им ну как угодно. Не забивайте код не нужными операциями. В большинстве это уже выполнено.

php.net/manual/ru/mysqli.insert-id.php