Насколько внешние ключи любят ресурсы?

Question

[Arman]

Решил начать их использовать по полной, но переживаю не увлекся ли я с ними, я понимаю когда важно сохранять целостность важных данных и т.д, но вот допустим у меня почти все модели которые хранят id-пользователей которые создали, меняли и т.д. насколько это важно? т.е. за 5 лет опыта вроде никто не спрашивали такую информацию и я сам вроде не смотрел, мне не лень наставить внешних ключей, но насколько это скажется на ресурсах? сайты пока простые, нагрузки почти нет, поэтому большого опыта нет. В целом буду рад услышать плохой опыт с внешними ключами, до этого вроде жил без них, но может только везло

Answer 1

[Melkij]

Делать внешние ключи обязательно.
И уникальные индексы проставлять. Ещё бы check constraint ставить, да у вас глупый mysql в тегах, он такое не умеет.
Это всё ваши помощники. Они помогают искать ошибки и отлично мешают делать глупости.
И выбирать подходящие типы данных. Ну и раз у вас mysql - то всенепременно проверить sql_mode и выставить его максимально агрессивным. В 5.7 стало гораздо лучше, до этого из коробки уж слишком много позволял делать глупостей.

Дорастёте до террабайтной базы как авито - тогда можете начинать думать, а сколько денег нам стоит целостность внешних ключей или дешевле будет периодически проверять целостность скриптами и озадачивать разработчиков. О, кстати, вот запись доклада с pgday15, на 27 минуте как раз начинается обсуждение вопроса из зала "ребята, а вы в своём уме отключать FK?"

Comments

[Василий Назаров]

Делать внешние ключи не обязательно. Иногда - надо, но далеко не всегда.
Идея ставить check constraint на каждый чих - это от DBA или PG головного мозга.

[Melkij]

Василий Назаров: делать внешние ключи обязательно. Иногда - не надо. И для этого "не надо" нужна чёткая аргументация
Если вы ранее никогда не вляпывались в ситуацию "wtf? в той табличке значение есть, а ссылается куда-то в никуда" - то вы просто слишком недавно работаете. Я за 7 лет опыта веб-разработчиком уже повидал такого.
Потому что shit happens. Если ограничение проверить может ещё кто-то помимо приложения - этим надо пользоваться. Не допускать писать кривые данные куда проще и дешевле, чем потом разбираться с мусором вместо данных. Да и искать ошибку проще по стектрейсу попытки записи невалидных данных, т.к. ошибка где-то там.

[Василий Назаров]

Melkij:

вы просто слишком недавно работаете

Вы смешной.. прямо совсем смешной..

значение есть, а ссылается куда-то в никуда

Это бывает у особо одарённых разработчиков. Таким, конечно, никак без контроля со стороны компа (это когда комп работает умнее, чем разраб, его программирующий)

[Melkij]

Василий Назаров: ну, вляпаетесь ещё когда-нибудь. Таких коллег тоже встречал, уверенно утверждавших, что приложения достаточно для контроля ссылок. А потом случается "ой!"

[Василий Назаров]

Melkij:
Вы считаете, "умная" БД спасёт от того, что приложение позволяет получить "ой".
Я с вами согласен.
Если всё настолько плохо - можно (и нужно) просить БД делать работу проектировщика. Лучше тупой интеллект, чем никакого.

Answer 2

[Иван Корюков]

Смотря какие ресурсы. Внешний ключ, а точнее индекс по полю внешнего ключа ускоряет поиск по таблице. Т.е. когда вы делает запрос
select * from my_table where creator = 123456
полного перебора таблицы не происходит. СУБД по индексу этой таблицы сразу берёт нужные строки.
Отсюда - меньшее потребление процессорного времени, но большее потребление диска, т.к. индекс это тоже файл.

Comments

[IceJOKER]

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

[Иван Корюков]

IceJOKER: Да, но в MySQL при использовании движка InnoDB при создании внешнего ключа индекс создаётся автоматически.

[Arman]

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

[Stalker_RED]

Иван Корюков: Ну и что? Его можно удалить, при желании. И создать другой индекс, или несколько.

[Иван Корюков]

Stalker_RED: Вы уверены? Т.е. пробовали удалить индекс, который был создан для внешнего ключа?

[Stalker_RED]

Arik: да, лишние индексы могут замедлить работу. На маленьких объемах это не важно а на больших лучше погонять тесты.

[Иван Корюков]

Arik: Да. Внешний ключ даже без индекса тратит время на проверку того что в таблице на которую ссылаестя ключ есть нужная запись. Делается это, опять же пои индексу (в этот раз уже индексу первичного ключа).
Т.е. на вопрос - "Тратятся ли ресурсы на внешние ключи" ответ - да, тратятся.
Другое дело, что пока это похоже на преждевременную оптимизацию. Высоконагруженные сайты быстро работают совсем не из-за того, что они индексы направо и налево не вешают.

[Stalker_RED]

Иван Корюков: Да, вы правы, не дает ни удалить индекс ни создать ключ по полю без индекса. Но и автоматически не создает.

LATEST FOREIGN KEY ERROR
------------------------
170804 11:11:11 Error in foreign key constraint of table test3/item_has_category:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
...

[Иван Корюков]

Stalker_RED: от версии СУБД зависит. Старая требует явного создания индекса.

[Stalker_RED]

Иван Корюков: Да, я на довольно древней 5.1.7 проверял, ей лет десять, наверное.

Answer 3

[Fortop]

Или

я понимаю когда важно сохранять целостность важных данных и т.д

Или

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

Т.е. все же не совсем понимаете.
На самом деле все зависит от бизнеспроцессов, которые вы реализуете.
Хранить всех посетителей общественного туалета за 10 лет вряд ли нужно.
А вот в разрезе какой-то сети супермаркетов хранить всех посетителей уже можно. И делать по ним анализ.

Аналогично и внутренними процессами. В общем случае действия операторов лучше протоколировать, для выяснения причин тех или событий (например, груз ушел не тому адресату - почему?)

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

Comments

[Arman]

ну вот допустим вы открыли чужой код, там кучу моделей имеют createdBy, editedBy и т.д. как целые числа, явно id-пользователей, но при переходе в таблицу пользователей такого юзер нет) или в админ-панели где должно быть наипсано кто создал, будет написано в лучшем случаи ID удаленного юзера. Это все явно плохо? или плохо что из-за большого кол-ва внешних ключей весь сайт тормозит.

[Fortop]

Arik:
Насколько важно вам знать кто что создал или удалил?

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

Что касается "торможения", то для начала нужно выяснить что является его причиной.
Для этого используются профилировщики (xhprof, xdebug.profile).
И только когда вы нашли слабые места в скорости обработки, тогда можно принимать решения.

Answer 4

[Василий Назаров]

Решил начать их использовать по полной

Помните поговорку про того, кто лоб расшибёт?
Использовать нужно не "по полной", а "по необходимости".
Любой индекс замедляет вставку (не так уж сильно, но на млн. записей - ощутимо), и тратит не столько диск (это фигня), сколько память (что тоже недорого, но просто её нужно будет докупить).
Зато индексы нередко в 10-ки/100-ни раз ускоряют выборку.
Соответственно, если у вас есть регулярная выборка типа "все заказы пользователя", то по "пользователю" лучше (надо) индекс создать.
Заметьте, я описать только индексы, но FK - это не только индексы, это и контроль целостности, что тоже ресурсы (польза тоже бывает, но совсем не всегда и в простых приложениях типа инет-магазина скорее бессмысленна).