Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Проектирование БД Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Стоит ли всегда записи описывать внешними ключами?  [new]
kormot
Member

Откуда:
Сообщений: 249
Добрый день Уважаемые!

Хочу спросить, правильно ли при проектировании БД в таблицах имеющих поля однозначно являющимися ID других записей в другой таблице оформлять их внешним ключом или нет?
20 апр 19, 13:31    [21867770]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8927
Скажем так - нет ни одной вменяемой причины этого не делать. Теоретически без обьявления внешних ключей обновления происходят чуть-чуть быстрее, но на практике это заметить, ээ, непросто - а вот затупы оптимизатора из-за недостатка информации и грязь в данных заметить куда как легче.
20 апр 19, 14:32    [21867787]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7063
И про индексы на внешние ключи не забываем, во избежании...
20 апр 19, 15:39    [21867803]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
Serguei
Member

Откуда: Papua New Guinea
Сообщений: 680
kormot
правильно ли при проектировании БД в таблицах имеющих поля однозначно являющимися ID других записей в другой таблице оформлять их внешним ключом или нет?


в зависимости от зависимости. Бывают разные случаи: чаще всего да надо ссылки делать FK, но бывают случаи когда так делать не нужно. Универсального рецепта нет.
На каждый конкретный пример надо смотреть и решать.
20 апр 19, 15:41    [21867804]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
kormot
Member

Откуда:
Сообщений: 249
Relic Hunter
И про индексы на внешние ключи не забываем, во избежании...

Это что значит? Т.е. кроме описания
FK objID->objects(id) ещё надо
KEY objIdx (objID)?

Разве наличие FK не даёт индекса? Объясните пожалуйста. На всяк случай я в MariaDB всё это делаю.

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

Хорошо, учту. А то делал всегда делал всё жёстко описывая связями а потом подумал, так а надо ли и решил уточнить тут. Спасибо!
20 апр 19, 15:48    [21867807]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 32129
Блог
kormot,

Если у вас oltp, то нужно.
Если же dwh, то не нужно.
20 апр 19, 15:54    [21867809]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
kormot
Member

Откуда:
Сообщений: 249
Relic Hunter
И про индексы на внешние ключи не забываем, во избежании...

Или это имеется ввиду накладывать условие уникальности FK или их комбинации в случае такого правила в предметной области?
20 апр 19, 18:32    [21867860]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7063
kormot
Разве наличие FK не даёт индекса? Объясните пожалуйста.
The foreign key constraint alone does not provide the index - one must (and should) be created.

https://sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys
20 апр 19, 22:26    [21867938]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7063
kormot
Relic Hunter
И про индексы на внешние ключи не забываем, во избежании...

Или это имеется ввиду накладывать условие уникальности FK или их комбинации в случае такого правила в предметной области?


No.
20 апр 19, 22:28    [21867940]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
miksoft
Member

Откуда:
Сообщений: 37672
kormot
Разве наличие FK не даёт индекса? Объясните пожалуйста. На всяк случай я в MariaDB всё это делаю.
Зависит от СУБД.
https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.
20 апр 19, 22:40    [21867950]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
kormot
Member

Откуда:
Сообщений: 249
Relic Hunter
The foreign key constraint alone does not provide the index - one must (and should) be created.

miksoft
https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

Охренеть. Вот что называется век живи и столько же учись. А я всю жизнь жил в полной уверенности, что описания FK хватает и не парился.
Жесть какая.
Т.е. для каждого FOREIGN KEY надо создать дополнительно KEY idx (fkID)?
21 апр 19, 09:08    [21868062]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
ПЕНСИОНЕРКА
Member

Откуда: Владимирская обл
Сообщений: 4592
kormot
Жесть какая.

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


сколько же всего мы не знаем, проработав в аксесс 20 лет
21 апр 19, 10:04    [21868073]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
полудух
Member

Откуда: планета орков, г.Зверополис
Сообщений: 728
kormot
Охренеть. Вот что называется век живи и столько же учись. А я всю жизнь жил в полной уверенности, что описания FK хватает и не парился.
Жесть какая.
Т.е. для каждого FOREIGN KEY надо создать дополнительно KEY idx (fkID)?

(postgres):
ALTER TABLE users_ops ADD FOREIGN KEY (uid) REFERENCES users(id) ON DELETE CASCADE;

id в users должен иметь уникальный индекс (обычно PK)
uid в users_ops должен иметь любой индекс
21 апр 19, 13:41    [21868174]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
полудух
Member

Откуда: планета орков, г.Зверополис
Сообщений: 728
и да, каждый такой индекс будет грузить таблицу, поэтому нет, не нужно бездумно лепить FK на каждый чих
вот конкретно такой FK - милое дело, т.к. эти индексы и так всегда присутствуют
в идеале связи ставятся на колонки, в которых уже есть индекс
а вот если новый индекс надо создавать только под FK, вот тут уже надо подумать и взвесить - часто можно и без него обойтись.
21 апр 19, 13:45    [21868175]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
полудух
Member

Откуда: планета орков, г.Зверополис
Сообщений: 728
но в любом случае, когда в одной таблице id, а в другой ссылка на этот id, то FK нужен, чтобы при удалении автоматом всё делалось
21 апр 19, 13:47    [21868178]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
полудух
Member

Откуда: планета орков, г.Зверополис
Сообщений: 728
вот с триггерами ситуация гораздо хуже
они могут попасть в цикл и так протормозить базу, что раком встанет весь сайт
вот их я вообще не использую.
21 апр 19, 13:49    [21868179]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
ChA
Member

Откуда: Москва
Сообщений: 10843
kormot
Т.е. для каждого FOREIGN KEY надо создать дополнительно KEY idx (fkID)?
Совсем необязательно. Основная задача внешнего ключа гарантировать ссылочную целостность. Для этого вполне достаточно основного ключа или ограничения уникальности на основной таблице. В основном, индекс на внешнем ключе нужен только если по этому полю(ям) выполняется фильтрация, да и то есть ситуации, когда даже в таком случае индекс всё равно может оказаться излишним.
21 апр 19, 22:23    [21868361]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3618
kormot
Добрый день Уважаемые!

Хочу спросить, правильно ли при проектировании БД в таблицах имеющих поля однозначно являющимися ID других записей в другой таблице оформлять их внешним ключом или нет?

что за вопрос. при ПРОЕКТИРОВАНИИ - конечно же правильно.
т.е на уровне логической модели - да
При реализации уже (на уровне физической модели) - возможны варианты
26 апр 19, 12:02    [21872445]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
fkthat
Member

Откуда:
Сообщений: 1163
ChA
kormot
Т.е. для каждого FOREIGN KEY надо создать дополнительно KEY idx (fkID)?
Совсем необязательно. Основная задача внешнего ключа гарантировать ссылочную целостность. Для этого вполне достаточно основного ключа или ограничения уникальности на основной таблице. В основном, индекс на внешнем ключе нужен только если по этому полю(ям) выполняется фильтрация, да и то есть ситуации, когда даже в таком случае индекс всё равно может оказаться излишним.

Индекс больше нужен потому, что где внешний ключ - там наверняка будут по нему джойны с таблицей, на которую он ссылается. Надобность проверить по-моему очень легко - сджойнить эти таблицы с реальными данными и посмотреть план запроса - используется ли индекс или нет, потому что это очень сильно может зависеть от объема данных и от статистики.
28 апр 19, 00:49    [21873571]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
ChA
Member

Откуда: Москва
Сообщений: 10843
fkthat
ChA
Индекс больше нужен потому, что где внешний ключ - там наверняка будут по нему джойны с таблицей, на которую он ссылается. Надобность проверить по-моему очень легко - сджойнить эти таблицы с реальными данными и посмотреть план запроса - используется ли индекс или нет, потому что это очень сильно может зависеть от объема данных и от статистики.
Безусловно, практика - критерий истины, но по моим многолетним наблюдениям для JOIN часто более чем достаточно индекса со стороны основной таблицы, так как в запросах хотят видеть их данные, потому что чаще всего это какие-то справочники. Само слияние по индексам, к сожалению, не спасает от bookmark lookup в подчинённой таблице, если только он удачно не впишется в кластерный индекс. В результате польза от него бывает не так велика, как бы хотелось. В то же время, если внешние значения активно и независимо используются при фильтрации, то тут, конечно, без индекса никуда. Если, конечно, выборка, опять же, не идёт по всей таблице или фильтрация по другим полю(ям) уже её неплохо ограничивают. Так что речь идёт не столько о том, будет или нет использоваться индекс, а будет ли от него реальный выигрыш, а не только лишние траты ресурсов на его создание и поддержание. Обилие индексов может само собой создавать немалые проблемы, их создание должно быть объективно аргументированно, избегая бездумной комбинаторики на все мыслимые и немыслимые случаи.
28 апр 19, 10:41    [21873628]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
vadiminfo
Member

Откуда: Обнинск
Сообщений: 4757
ChA
Безусловно, практика - критерий истины, …...

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

Индексы на внешние ключи нужны в некоторых СУБД, так как иначе может в некоторых ситуациях возникнуть блокировка всей дочерней таблы без такого индекса, а с индексом нет. Наблюдал такое в 8 версии Оракла. Случай редкий. Но все же у заказчика начались зависания. С тех пор предпочитал ставить индексы.
30 апр 19, 14:36    [21875567]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
fkthat
Member

Откуда:
Сообщений: 1163
полудух
и да, каждый такой индекс будет грузить таблицу, поэтому нет, не нужно бездумно лепить FK на каждый чих
вот конкретно такой FK - милое дело, т.к. эти индексы и так всегда присутствуют
в идеале связи ставятся на колонки, в которых уже есть индекс
а вот если новый индекс надо создавать только под FK, вот тут уже надо подумать и взвесить - часто можно и без него обойтись.


Давайте новый флейм про FK на пару дюжин страниц
30 апр 19, 17:06    [21875769]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
fkthat
Member

Откуда:
Сообщений: 1163
полудух
но в любом случае, когда в одной таблице id, а в другой ссылка на этот id, то FK нужен, чтобы при удалении автоматом всё делалось


FK нужен не для "автоматом", а чтобы в таблицу гамнища гору не навалили. "Автоматом" это уже так, просто опциональное удобство, которое не всегда и удобно.
30 апр 19, 17:09    [21875773]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34486
kormot
Добрый день Уважаемые!

Хочу спросить, правильно ли при проектировании БД в таблицах имеющих поля однозначно являющимися ID других записей в другой таблице оформлять их внешним ключом или нет?


Конечно стоит, всегда.
14 май 19, 12:48    [21884306]     Ответить | Цитировать Сообщить модератору
 Re: Стоит ли всегда записи описывать внешними ключами?  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 58418
Блог
kormot
Хочу спросить, правильно ли при проектировании БД в таблицах имеющих поля однозначно являющимися ID других записей в другой таблице оформлять их внешним ключом или нет?


К сообщению приложен файл. Размер - 11Kb
14 май 19, 13:40    [21884374]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Проектирование БД Ответить