Работа с идентификаторами

А зачем их вообще сдвигать? Лишняя огромная работа и внезапно может оказаться что реляционные связи указывают не на ту запись что нужно.

Но тогда получается, что будут оставаться пустые ID, верно?
Или они будут автоматически заполняться новыми записями?

А чем пустые ID мешают? Если значения ключа имеют какой-то смысл, добавьте столбец натурального ключа. Менять же PRIMARY KEY — это может быть дорого с точки зрения производительности и, если не использовать внешние ключи, может привести к ошибкам связности.

и чем пустые id мешают?

Если автоинкримент самостоятельно ищет пустые id и присываивает их новой информации, то ничем не мешают :)

Порядка хочется.

1. А
2. Б
3. В
4. Г
5. Д

Удалилили/Добавили:

1.
2. Е
3. В
4. З
5. И

Потом хотим добавить Ъ. Если автоинкримент сделает так:

1. Ъ
... и так далее, то вопрос в общем-то исчерпан.
Но если так:

1.
2. Е
3. В
4. З
5. И
6. Ъ

то я хочу знать как бороться с пустыми ID, сомневаюсь, что они не занимают места в БД и не мешают быстрому поиску.

Записи не смещаются после удаления. Повторно ID не используются, если их прямо не указывать в INSERT.

Они не занимают места в бд и не мешают быстрому поиску. Их там просто нет, посмотри на дамп своего же примера, первой строчки там не будет вообще.

Борьба с пустыми полями — экономия на спичках.

Поиску они никак не помешают, их просто в индексе не будет.

Т. е. в итоге: борьба с пустыми полями — ересь и смысла в этом — ноль?

А чем пустые ID мешают?

Не в данном конкретном случае, но вообще:

1. ID могут требоваться исключительно 32-х битные (скажем, требования бинарного протокола), а генерироваться их может много.

2. Даже без ограничения на разрядность, при большом количестве создаваемых ключей может понадобиться переход на BIGINT. А ключи с ним работают уже медленнее, чем с INT(10..11) :)

Только в исключительных случаях.

а вообще какая-нибудь экономия разве будет от этого? ТС неправильно же написал - не будет никакого пустой записи с id=1.

Если автоинкримент сделает так:

1. Ъ

а если у тебя на PK 1 ссылается какая-то другая сущность, то ты отгребешь логическую ошибку, да.

Но если так:

Вот именно так и будет. И это правильно. Попробуй позабивать данных, а потом посмотри на show create table, там будет следующее значение автоинкремента.

то я хочу знать как бороться с пустыми ID, сомневаюсь, что они не занимают места в БД и не мешают быстрому поиску.

Дорогой, тебе правда делать нефиг? Почитай, как мускуль хранит индексы и подумай еще раз.

Если чем-то не подходят автоинкрементные PK, используй свои собственные хеши и сделай шардинг.

P.S. и почитай, что есть преждевременная оптимизация.

Спасибо за совет, пойду читать и дальше проектировать.

Как правильно спроектировать БД, чтобы не было глюков из-за сдвигов идентификаторов?

Исключить сдвиги. Всё остальное - костыли.

борьба с пустыми полями

Вот они, Дон-Кихоты нашего времени :)

Главное убедиться, что в связанных таблицах не осталось фантомных записей, ссылающихся на эти идентификаторы. Для этого есть всякие cascade delete.

1. ID могут требоваться исключительно 32-х битные (скажем, требования бинарного протокола), а генерироваться их может много.

1000 записей в секунду в течение 24 часов — понадобится где-то полтора месяца, чтобы исчерпать 32-битный диапазон :-)

И будет ли база данных подходящим генератором таких дентификаторов? :-)

1000 записей в секунду в течение 24 часов — понадобится где-то полтора месяца, чтобы исчерпать 32-битный диапазон :-)

Представь себе, бывают системы, работающие годами :) 32 беззнаковых бита за два года исчерпаются всего на скорости 34 записи в секунду. А за пять лет — на 13 записях в секунду. У меня же есть работающие БД, которым за 12..13 лет. К счастью, там обновлений поменьше ;)

И будет ли база данных подходящим генератором таких дентификаторов? :-)

Это уже от конкретной задачи зависит, конечно.

Представь себе, бывают системы, работающие годами :)

Я в курсе :-) Мой комментарий относился к протоколу, требующему уникальные 32-битные идентификаторы.

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

А за пять лет — на 13 записях в секунду.

За пять лет можно вполне догадаться изменить тип столбца ;-)

За пять лет можно вполне догадаться изменить тип столбца ;-)

Работа с идентификаторами (комментарий)

Закажите Java EE + Oracle DB прямо сейчас, и вы получите целых 10 бесплатных идентификаторов в подарок!

Даже без ограничения на разрядность, при большом количестве создаваемых ключей может понадобиться переход на BIGINT. А ключи с ним работают уже медленнее, чем с INT(10..11) :)

Специально вчера перстроил таблицу (~250 миллионов записей, InnoDB), изменив PK с BIGINT UNSIGNED на INT UNSIGNED. Выборок/сортировок по первичному ключу в приложении достаточно. Но скорость изменилась в пределах статистической погрешности.

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

Т.е если была последовательность ide'шников:
1, 2, 3, 4
Вызвали DELETE, в итоге, получили например следующее:
1, 2, 4
Потом вывали INSERT, в итоге:
1, 2, 4, 5

?

32 беззнаковых бита

ССЗБ, нефиг юзать 32-битные системы)

ССЗБ, нефиг юзать 32-битные системы)

Как бы очевидно, что речь о legacy :)

Хм, думаю, что надо исключительно реальной базе смотреть. Взять в пример тот же ВК - там овер9000 записей, миллион хомячков зарегистрировано, но поиск работает быстро, страница выдается практически мгновенно. Фейков было убито масса и еще сколько будет удалено. Там пустых ID навалом. По мне, так это «вес» базы. Ясно, что песчинка ничего не весит, но набери песчинок на 10 киллограм и это будет очень даже ощутимо.