Czy lepiej jest definiować klucze obce w bazie danych lub w części kodu aplikacji?
Czy lepiej jest definiować klucze obce w bazie danych lub w części kodu aplikacji?
Odpowiedzi:
Umieść klucze obce w bazie danych. Nawet jeśli zweryfikujesz dane w aplikacji przed ich zapisaniem, FK są dobrym kawałkiem kopii zapasowej jakości. Dla pierwszego przybliżenia aplikacje zawsze mają problemy z danymi. Pozostawienie takich kontroli poza systemem po prostu zaprasza tryby awarii, w których dane zostają po cichu uszkodzone.
Nie ma nic takiego jak praca w hurtowni danych przez kilka lat, aby zobaczyć to w działaniu. Spędzasz czas na zbieraniu elementów po błędach popełnianych przez twórców aplikacji, którzy sądzili, że mogą wymusić integralność danych w kodzie aplikacji. Poświęć na to trochę czasu, a dojdziesz do wniosku, że integralność danych zarządzanych przez aplikację to niewiele więcej niż zarozumiałość.
Ponadto optymalizator zapytań może używać kluczy obcych do wnioskowania o sprzężeniach z tabelami, dzięki czemu FK zapewni bardziej wydajne plany zapytań.
Istnieje również wiele innych korzyści dla kluczy obcych. Wyświadcz wszystkim przysługę - umieść FK w bazie danych.
Integralność referencyjna powinna być obsługiwana na najniższym możliwym poziomie, który byłby bazą danych. Systemy zarządzania relacyjnymi bazami danych są zoptymalizowane do obsługi tego. Nie ma sensu wymyślać przysłowiowego koła.
Dopuszczalne jest zdefiniowanie logiki domeny w kodzie aplikacji, aby zapobiec sytuacji, w której instrukcja DML spowoduje nawet wyjątek RI, ale nie powinno to być postrzegane jako zamiennik relacji kluczy obcych w bazie danych.
Mam zamiar wyjść tutaj na całość, spodziewając się, że zostanie to odrzucone, ponieważ jest to grupa skoncentrowana na DBA.
Zgadzam się, że użycie ścisłych kluczy obcych jest najlepszą decyzją w większości scenariuszy. Jednak w niektórych przypadkach klucze obce powodują więcej problemów niż rozwiązują.
Gdy masz do czynienia z bardzo współbieżnym środowiskiem, takim jak aplikacja internetowa o dużym natężeniu ruchu, i używasz dobrze ugruntowanej, solidnej ORM, klucze obce mogą powodować problemy z blokowaniem, które utrudniają skalowanie i utrzymanie serwera. Podczas aktualizowania wierszy w tabeli podrzędnej również wiersz nadrzędny jest zablokowany. W wielu scenariuszach może to drastycznie ograniczyć współbieżność z powodu rywalizacji o blokowanie. Ponadto czasami trzeba wykonać konserwację poszczególnych tabel, takich jak procesy archiwizacji, w których konieczne może być (umyślne) złamanie reguł integralności referencyjnej, przynajmniej tymczasowo. Po zainstalowaniu kluczy obcych może to być niezwykle trudne, aw niektórych RDBMS wyłączenie ograniczeń klucza obcego spowoduje przebudowanie tabeli, co jest czasochłonnym procesem, który może wymagać znacznego przestoju.
Zrozum, że uwzględniam zastrzeżenie, że musisz użyć solidnego frameworka, który jest w stanie zrozumieć integralność referencyjną na zewnątrz bazy danych. Mimo to prawdopodobnie wystąpią problemy z integralnością referencyjną. Istnieje jednak wiele przypadków, w których osierocone wiersze lub drobne naruszenia integralności referencyjnej nie są tak duże. Twierdziłbym, że większość aplikacji internetowych należy do tej kategorii.
To powiedziawszy, nikt nie zaczyna jako Facebook. Zacznij od zdefiniowania kluczy obcych w bazie danych. Monitor. Jeśli wystąpią problemy, zrozum, że może być konieczne usunięcie niektórych z tych ograniczeń w celu skalowania.
Podsumowując: większość baz danych powinna mieć klucze obce. Wysoce współbieżne środowiska mogą być lepiej bez obcych kluczy. Jeśli osiągniesz ten punkt, być może będziesz musiał rozważyć usunięcie tych ograniczeń.
Idę teraz założyć ognioodporny kombinezon.
EDYCJA 23.03.2012, 7:00
Myśląc o konsekwencjach blokowania kluczy obcych, zapomniałem wspomnieć o kosztach wszystkich dodatkowych wyszukiwań wierszy, które są domyślnie generowane wewnętrznie, zwiększając obciążenie serwera.
Ostatecznie chodzi mi o to, że klucze obce nie są wolne. W wielu przypadkach koszt jest tego wart, ale istnieją scenariusze, w których koszt ten przewyższa ich korzyści.
EDYCJA 23.03.2012 07:38
Bądźmy konkretni. Wybieram MySQL / InnoDB w tym przykładzie, który nie jest bardzo szanowany ze względu na zachowanie klucza obcego, ale to jest to, co znam i jest prawdopodobnie najczęściej używaną internetową bazą danych. Nie jestem pewien, czy inna baza danych wypadłaby lepiej na przykładzie, który pokażę.
Rozważmy tabelę potomną z kluczem obcym odwołującym się do rodzica. Jako przykład zobacz tabele film i film_actor w przykładowej bazie danych sakila w MySQL:
CREATE TABLE `film` (
`film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`description` text,
`release_year` year(4) DEFAULT NULL,
`language_id` tinyint(3) unsigned NOT NULL,
`original_language_id` tinyint(3) unsigned DEFAULT NULL,
`rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
`rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
`length` smallint(5) unsigned DEFAULT NULL,
`replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
`rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
`special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`film_id`),
KEY `idx_title` (`title`),
KEY `idx_fk_language_id` (`language_id`),
KEY `idx_fk_original_language_id` (`original_language_id`),
CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
CREATE TABLE `film_actor` (
`actor_id` smallint(5) unsigned NOT NULL,
`film_id` smallint(5) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`,`film_id`),
KEY `idx_fk_film_id` (`film_id`),
CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8Odpowiednim ograniczeniem jest film_actor (fk_film_actor_film) w moim przykładzie.
session1> BEGIN;
session1> INSERT INTO film_actor (actor_id, film_id) VALUES (156, 508);
Query OK, 1 row affected (0.00 sec)
session2> BEGIN;
session2> UPDATE film SET release_year = 2005 WHERE film_id = 508;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionZauważ, że nie byłem w stanie zaktualizować niepowiązanego pola w wierszu nadrzędnym podczas wstawiania do tabeli podrzędnej. Dzieje się tak, ponieważ InnoDB trzyma blokadę współdzieloną w wierszu, w którym film.film_id = 508 z powodu ograniczenia FK w parametrze film_actor, dlatego AKTUALIZACJA tego wiersza nie może uzyskać wymaganej blokady wyłącznej. Jeśli odwrócisz tę operację i uruchomisz najpierw AKTUALIZACJĘ, masz takie samo zachowanie, ale WSTAW jest zablokowany.
session1> BEGIN;
session1> UPDATE film SET release_year = 2005 WHERE film_id = 508;
Query OK, 1 row affected (0.00 sec)
session2> BEGIN;
session2> INSERT INTO film_actor (actor_id, film_id) VALUES (156, 508);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionZastanów się nad userstabelą w aplikacji internetowej, w której często znajduje się kilkadziesiąt powiązanych tabel. Zasadniczo każda operacja w dowolnym powiązanym wierszu uniemożliwia aktualizację wiersza nadrzędnego. Może to stanowić trudny problem, gdy masz wiele relacji kluczy obcych i dużo współbieżności.
Ograniczenia FK mogą również utrudnić obejścia związane z konserwacją stołu. Peter Zaitsev z Percona ma na blogu post, który wyjaśnia to lepiej niż potrafię: przejmowanie kluczy obcych Innodb .
Dobrą praktyką jest używanie klucza obcego w bazie danych. To pomaga-
ON DELETE CASCADE