Разрабатываю базу данных с очень большим количеством таблиц на пределе возможностей мускула.
Теоретически, между всеми таблицами существует множество зависимостей
На этапе проектирования эти зависимости описываются внешними ключами.
Изменяемые таблицы InnoDB, не изменяемые MYISAM.
Таблицы генерируются в процессе работы приложения, большинство ключей тоже.

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

Достоинства внешних ключей:
1) Не обязательно следить за корректностью операций удаления или изменения записей.

Недостатки:
1)  В следствие необязательности контроля за корректностью операций удаления или изменения записей
 многие запросы будут обрабатываться впустую, расходуя ресурсы сервера, а исключительные события будут тормозить программу, как это имеет место при любой обработке исключений.

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

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

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

Что думает уважаемый All по этому поводу? 

JavaCraft, 
Ответь себе на вопрос: откуда берутся внешние ключи и почему без них практически невозможно обойтись? Намек: БД без справочников не бывает. Заодно поймешь, что всякие сбои - выдуманная тобой проблема.


А не рано? Может вначале в общих принципах разобраться? Чтобы потом не переделывать все по новой.

Если у Вас половина таблиц myisam половина innodb - как вы собираетесь FK применять?

  Не рано.

Если к собственной логике относиться не критично, т.е., по вашему, то обойтись невозможно, это точно.

Между InnoDB. Для MyISAM таблиц FK не нужны. Это справочники только для чтения. В модели FK к ним строятся, но метятся как не реализуемые, в реализации отключаются за ненадобностью.
ТОлько я всё больше убеждаюсь, что FK и для InnoDB не нужны, поскольку модель данных почти всегда требует прямой проверки условий внутри хранимых процедур и функций. Каскадное удаление в моей задаче в принципе не нужно, удалять справочные данные без проверки я не собираюсь. 

Кроме того, мои опасения по поводу FK подкрепляются множеством топиков на этом сайте, а также некоторыми статьями, не рекомендующими FK для БД Web-приложений.
В качестве замены FK рекомендуют более тщательно разрабатывать код.

Это сообщение отредактировал(а) JavaCraft - 2.5.2007, 12:11

JavaCraft, 
Объясняю без намеков: если нет справочников, то и нет необходимости в FK. Но тогда получившееся изделие БД назвать нельзя - это будет просто сборище разнородных таблиц. 
Если справочники есть, то "ручками" связывать их с нужными таблицами - мартышкин труд. Надежнее и проще чем FK не придумаешь, поскольку они для реализации этой связи и созданы.

Добавлено через 2 минуты и 45 секунд

А нука подробнее, на примере. Без заумных размышлений.

Вродебы я сказал, что справочники есть...


Я так понимаю. По вашему деление на базы и не базы следует вести по признаку наличия и отсутствия FK?


Что-то типа такого

И разные варианты такого



Покажите как оно должно быть по взрослому. Тоже код плиз.

JavaCraft, 

Настоятельно рекомендую все-таки прочитать и понять, что такое нормальные формы. Поскольку все на самом деле ОЧЕНЬ просто: 
1. БД без применения нормализации не бывает. Более понятно (упрощенно): нет БД без справочников.
2. Если есть справочник, то есть FK. Любой другой подход - просто попытки носить воду в решете.
Если очень хочется дальше спорить - пожалуйста. Только это спор не со мной, а с Дейтом, Ульманом, Коддом и т.п. И я сильно сомневаюсь, что новичок в СУБД сумеет придумать что-то умнее и лучше, чем то, что наработано профессионалами за полвека.


На. Таблица жителей

Один из справочников для нее - степень родства с основным квартиросъемщиком.. 


FK_gitel_rods:
Primary Key Table:      Rods, Field: cod  
Foreign Key table:      Gitel,  Field: cod_rods
Это MS SQL. MySQL у меня в другом месте крутится. Тратить 2 часа на дорогу - не буду.

Спор не о чем.


При чем тут нормальные формы! Мы говорим не об организации данных, а о вполне конкретной декларации
ссылочной целостности. Вы спорите непонятно о чём. Похоже, что Вы не поняли вопроса.

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

Покажите мне код, который работает эффективнее с FK чем без них, при одинаковой организации данных, включая нормализацию и наличие индексов!

JavaCraft, 

Я не спорю. Я констатирую факты. 
Факт №1: если нет справочников, то нет БД. Вытекает из нормализации.
Факт №2: если есть справочники, то есть FK. Вытекает из устройства серверов БД, точнее из ACID, что должно соблюдаться сервером. Все остальные "решения" - плоды буйной фантазии недоучек. Кончаются всегда плохо. 
И не надо декларировать "сферического коня в вакууме" и прочие "ссылочные целостности". Надо доки читать. И понимать.


Я не могу показать квадратное колесо. Т.е. код, работающий на БД без FK. Поскольку БД без FK не бывает.

Сколько слюней и всё не по делу.
А судя по топику, что такое FK и БД ты вообще не знаешь.

Если речь о МОЕМ примере, то повторю:

FK_gitel_rods - это Foreign Key для таблицы gitel. А в коде применять - ЭТО КАК? Сам-то понял, что сказал?

Добавлено через 5 минут и 33 секунды
JavaCraft, 
Знаешь, "знаток", ПРОЧТИ-ТАКИ ДОКИ. И не весели публику изобретательством. Пока я наблюдаю упорное старание подвести под БД то, что БД не является. Я привожу факты. А в ответ -  "а я не то имел в виду, да никто ничего не понял, кроме меня, самого умного и красивого".

Это чё за хрень? Ты на MySQL напиши. Топик и раздел по MySQL. Куда смотрит модератор?

Я то понял, а ты нихрена не въезжаешь.
По русски написано. "Покажи код который эффективнее работает при наличии FK чем без них".

JavaCraft, 
Еще раз:
Я не могу показать квадратное колесо. Т.е. код, работающий на БД без FK. Поскольку БД без FK не бывает. 

1) Для одновременного использования myisam и innodb требуются очень веские причины. Для нормальной работы myisam надо выделять хороший key_buffer, для innodb - солидный innodb_buffer_pool_size. Буферы, которые используются myisam и innodb не пересекаются между собой, каждый использует только свои. Это значит - при использовании только innodb можно выделить ему гораздо больше ресурсов. если вы используете и то и другое, каждый из них будет "голодать" (memory starving)  "на пределе возможностей" mysql

2) FK выполняются на гораздо более низком уровне чем хранимки. де-факто хранимки выполняются поверх уровня движков(innodb, myisam и тд), FK отрабатываются внутри engine, и во многих случаях вообще не лезет в таблицу (если страница индекса в кэше). Производительность FK выше чем хранимок. Если Вам нужны дополнительные проверки, их лучше делать в добавление к FK, не "вместо" них.

3) Не очень понятна идея хранить справочники в myisam а таблицы в innodb, и  на основании того, что myisam таблицы не изменяются, считать что FK им не нужны..  Innodb таблицы то меняются (и не только на insert/delete как вы везде пишете)  но и на update.  Справочники не меняются, но сылки то на них есть в основных таблицах. Без FK можете получить ссылки на отсутствующие/не те  записи в справочниках. Разумнее было бы все же все хранить в innodb, и использовать FK. Тем более что оттюненый innodb на больших нагрузках работает шустрее myisam.

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

5) "Если произойдет маловероятный но возможный системный сбой внешнего ключа, то поднять базу будет очень проблематично, может быть даже не поможет откат транзакции." Это что имеется в виду?  Приведите пример когда откат транзакции не поможет? В голову только приходит случай если часть таблиц в innodb а часть в myisam. Кстати SET UNIQUE_CHECKS=0; и SET FOREIGN_KEY_CHECKS=0; при восстановлении вполне спасут и в случае такой (очень гипотетической) ситуации. А вообще процедуры восстановления достаточно обкатаны в mysql.