Jeśli, jak stwierdzono w twoim poście, intencją jest utworzenie relacyjnej bazy danych (RDB dla zwięzłości), a zatem oczekuje się, że ona funkcjonuje jako taka, krótka odpowiedź brzmi:
- Nie, nie powinieneś przeoczyć ograniczeń integralności danych .
Podstawowym celem powinno być zarządzanie stosownymi danymi, ponieważ są one dość cennym zasobem organizacyjnym, a niezawodnym sposobem na osiągnięcie tego celu jest zastosowanie środków technicznych, które są poparte solidną teorią.
Tak więc, jako specjaliści od baz danych, możesz skorzystać z najnowocześniejszych i eleganckich mechanizmów modelu relacyjnego dostarczonych przez dr. EF Codda w celu egzekwowania reguł biznesowych i uniknięcia problemów, które ostatecznie powstałyby, gdyby nie zostały wykorzystane.
W tym względzie podzielę się (a) moim ogólnym podejściem do ograniczeń, a także (b) kilkoma rozważaniami na temat stanu bazy danych i środowiska pracy w następujący sposób.
KLUCZE ZAGRANICZNE ograniczenia, relacje danych i integralność referencyjna
RDB musi odzwierciedlać cechy interesującego kontekstu biznesowego z dużą dokładnością, co zdecydowanie wymaga dogłębnej analizy na poziomie koncepcyjnym, prowadzonej przez modelarza lub projektanta, który postępuje zgodnie z najlepszymi praktykami, przy niezastąpionej pomocy ekspertów biznesowych. Analiza ta musi zapewnić prawidłową identyfikację i sformułowanie obowiązujących reguł biznesowych .
W związku z tym, jeśli taki modelarz stwierdził, że istnieją istotne powiązania między istotnymi danymi, musi on skonfigurować odpowiednie ograniczenia na poziomie logicznym, aby system zarządzania bazą danych (DBMS) mógł zagwarantować, że dane pozostają zgodne z dokładnymi cechami i zasady określone w analizie, o której mowa powyżej przez cały czas .
Jeśli chodzi o omawianą bazę danych, można wywnioskować, że zidentyfikowano stosowne powiązania, ponieważ wspominasz, że istnieje proceduralna (i łatwa do obejścia) próba wymuszenia ich spoza obiektów DBMS za pomocą kodu programu aplikacji (który jest podejściem relacyjnym), które w każdym przypadku musi „dotknąć” bazy danych, aby spróbować zweryfikować całość wspomnianych powiązań.
Jak jednak wiadomo, nie jest to optymalna technika ochrony integralności referencyjnej , ponieważ nauka relacyjna wyznaczyła do tego celu bardzo potężny instrument, tj. Ograniczenia KLUCZ ZAGRANICZNY (FK). Ograniczenia te są bardzo łatwe do stworzenia (dzięki nadrzędnemu podejściu deklaratywnemu), ponieważ są to pojedyncze zdania, które unikają stosowania niepotrzebnych i podatnych na błędy procedur ad hoc. Warto zauważyć, że szybkość wykonywania ograniczeń FK została wysoce zoptymalizowana przez wyspecjalizowanych programistów (a główni dostawcy platform pracowali nad tym już od dziesięcioleci).
Ponadto, ponieważ RDB musi być niezależnym (samoobronym, samoopisującym itp.) Komponentem oprogramowania, do którego może mieć dostęp wiele programów aplikacyjnych (komputerowych, automatycznych, internetowych, mobilnych, ich kombinacji), nie powinno być „W połączeniu” z kodem dowolnej z tych aplikacji.
Podobnie, dane - będące znaczącym zasobem organizacyjnym - naturalnie mają tendencję do przeżywania programów aplikacyjnych, programistów aplikacji, platform rozwoju aplikacji i paradygmatów programowania.
PODSTAWOWE KLUCZOWE ograniczenia i implikacje zduplikowanych wierszy
Kiedy - mówiąc koncepcyjnie - określony rodzaj rzeczy został uznany za istotny w środowisku biznesowym, modelista bazy danych musi (1) określić jego istotne cechy - tj. Jego właściwości - potwierdzić wspomnianą rzecz jako prototyp instancji encji - tj. typ jednostki - i (2) reprezentują go za pomocą tabeli, która jest zintegrowana z jedną lub większą liczbą kolumn w logicznym projekcie.
Następnie, podobnie jak w przypadku świata rzeczywistego niezwykle ważne jest rozróżnienie poszczególnych instancji danego typu encji, każdy wiersz ujęty w tabeli musi być również wyjątkowo rozróżniany. Jeśli tabela nie ma zadeklarowanego KLUCZA, ostatecznie zachowa duplikaty, a jeśli istnieją dwa lub więcej wierszy, które zachowują dokładnie te same wartości, wszystkie mają takie samo znaczenie , wszystkie reprezentują ten sam fakt .
W tym momencie zduplikowane wiersze należy odrzucić z wielu powodów. Z teoretycznego punktu widzenia projektant musi upewnić się, że każdy wiersz jest zawsze unikatowy, aby tabele działały tak relacyjnie, jak pozwala na to podjęzyk danych SQL (co ma istotne konsekwencje dla operacji manipulacji danymi). Poza tym, z perspektywy informacyjnej, jeśli wiele wierszy reprezentuje ten sam fakt, ich rejestrowanie jest nie tylko zbędne, ale szkodliwe , jak pokazano poniżej:
- Załóżmy, że ktoś wstawił dwa identyczne wiersze do określonej tabeli.
- Później przychodzi ktoś inny i aktualizuje tylko jedno wystąpienie duplikatów. W związku z tym drugie zdarzenie nie jest już aktualne.
- Kolejna osoba aktualizuje zdarzenie, które nie było dotychczas modyfikowane. W ten sposób oba duplikaty uległy różnym zmianom w różnych punktach czasowych.
- Następnie, gdy ktoś jest zainteresowany wyborem informacji przekazywanych przez dane wiersze, może on znaleźć dwie różne „wersje” tych informacji.
W ten sposób:
- Którą „wersję” można uznać za poprawną, niezawodną?
- Który dokładnie odzwierciedla rzeczywisty świat?
Jak wiecie, zjawisko to może mieć nawet implikacje prawne, co z pewnością ma ogromne znaczenie.
Poza tym czas i wysiłek, który należy poświęcić, aby poradzić sobie z takimi sprzecznościami (być może poprzez pewnego rodzaju „synchronizację aktualizacji”), należy lepiej poświęcić na zadania, które w rzeczywistości przynoszą wartość dla Twojej organizacji. Dlatego należy unikać utrzymywania sprzecznych wierszy w projekcie, aby zachować spójność bazy danych.
Dlatego projektant bazy danych powinien zawsze identyfikować KLUCZ PODSTAWOWY (PK) i deklarować odpowiednie ograniczenie . Ale należy również wspomnieć, że tabela może zawierać więcej niż jedną kolumnę lub kombinację kolumn, które zawierają wartości, które jednoznacznie identyfikują każdy wiersz; w konsekwencji, oprócz ustanowienia ograniczenia PK (najlepiej ustalonego jako PODSTAWOWE z powodów pragmatycznych), projektant musi również zadeklarować jeden lub więcej KLUCZÓW ALTERNATYWNYCH (zwykle definiowanych przez jeden lub więcej ograniczeń UNIKALNYCH plus NIE NULL), gdy ma zastosowanie (co jest dość powszechne).
Inną korzystną właściwością PK jest to, że „migrowane” do innych tabel w celu wzięcia udziału w pojedynczych lub złożonych FK, mogą pomóc w wymuszeniu współczynników liczności relacji istniejących między danymi. Wszystko to za pomocą prostych i skutecznych ustawień deklaratywnych, zapewnionych przez DBMS.
(Bieżące) ograniczenia CHECK i sprawdzanie poprawności w jednym wierszu
Nie zapominajmy o istotności (bieżących) ograniczeń CHECK, które, deklaratywnie ograniczając prawidłowy zestaw wartości kolumn w wierszu (co może wydawać się proste, ale w rzeczywistości jest podstawową cechą relacyjnego DBMS), pomagają również pewne, że reguły kontekstu biznesowego są zawsze precyzyjnie odzwierciedlane.
Gdy zaznaczyłeś swoje pytanie tagiem MySQL, musisz wspomnieć, że niestety taka platforma pozwala na deklarację tego rodzaju ograniczenia, ale jednocześnie ignoruje jego egzekwowanie! , sytuacja, która, co zrozumiałe, była zgłaszana jako błąd od 2004 r .
W związku z tym należy zająć się tym czynnikiem innymi sposobami, np. TRANSAKCJAMI KWASU, TRIGGERAMI lub innymi metodami w samym DBMS ( informacje na ten temat można znaleźć w odpowiedzi na @ ypercubeᵀᴹ
), aby dane nadal być konsekwentnym.
Ograniczenia ASSERTION: deklaratywne ustawianie dalszych reguł biznesowych zawierających wiele wierszy i tabel
Jednym z aspektów, który z jakichkolwiek powodów jest bardzo słabo obsługiwany - jeśli w ogóle - przez różne DBMS SQL, w tym MySQL, umożliwia deklaracje w wielu wierszach i wielu tabelach - oczywiście poza PK i FK - oczywiście.
Ze swojej strony standard SQL zawiera ASSERTION od wielu lat. Nie wiem, jakie reguły środowiska biznesowego skorzystałyby na tym podejściu do sprawdzania poprawności na poziomie logicznym, ale jako projektant bazy danych uważam, że dość przydatne byłoby ograniczenie danych za pomocą co najmniej jednego ASSERTION, chociaż muszę o tym wspomnieć z Z punktu widzenia deweloperów DBMS to niezwykle ważne narzędzie było trudne do wdrożenia na poziomie fizycznym abstrakcji.
Wygląda na to, że sprzedawca i / lub programiści Oracle oceniają wsparcie ASSERTION od 2016 roku, a to sprawiłoby, że DBMS byłby bardziej zgodny pod względem relacji, a tym samym bardziej solidny i konkurencyjny. Sądzę, że jeśli (i) ich klienci będą naciskać i (ii) Oracle odniesie sukces we wdrażaniu, wówczas (iii) inni dostawcy / społeczności DBMS również będą musieli im umożliwić, a ich użycie zacznie się rozprzestrzeniać. Z pewnością byłby to ogromny postęp w dziedzinie zarządzania bazą danych i będąc jednym z najbardziej charakterystycznych narzędzi przewidzianych przez dr Codda, osobiście mam nadzieję, że wkrótce to nastąpi.
Spójność danych i proces decyzyjny
Jak omówiono powyżej, jednym z najważniejszych aspektów RDB jest to, że sam gwarantuje spójność przechowywanych danych, a wspomniana spójność jest spełniona tylko wtedy, gdy RDB spełnia ograniczenia integralności zadeklarowane przez modelera.
W związku z tym obowiązkowe są tabele podstawowe (te utworzone w strukturze DDL), których integralność jest chroniona, aby można było tworzyć tabele pochodne (np. Instrukcja SELECT lub widok pobierający kolumny z wielu tabel), które są godne zaufania , ponieważ tabele pochodne muszą być tworzone koniecznie pod względem tabel podstawowych.
Powszechnie wiadomo, że ludzie wykorzystują informacje jako główne narzędzie w organizacyjnym (i zwykłym) procesie decyzyjnym. Następnie, jeśli informacje przedstawione przez bazę danych nie są spójne i dokładne, decyzje oparte na takich informacjach nie będą rozsądne (co najmniej). Dlatego RDB musi być starannie zaprojektowany i wdrożony: powinien zostać zbudowany, aby stać się niezawodnym zasobem, który może pomóc użytkownikom w podejmowaniu uzasadnionych decyzji.
„Denormalizacja”
Niestety, „zdenormalizowana” baza danych jest szybsza niż znormalizowana baza danych to szeroko rozpowszechnione nieporozumienie, chociaż jest to również argument, który można obalić z logicznych, fizycznych i pragmatycznych powodów.
Po pierwsze, denormalizacja oznacza koniecznie, że tabela podstawowa została uprzednio znormalizowana (na mocy formalnej , opartej na nauce procedury spełnianej na poziomie logicznym abstrakcji bazy danych).
Zakładając, że wspomniana tabela faktycznie została właściwie znormalizowana, „denormalizując” ją (co, w przeciwieństwie do formalnego znaczenia tego słowa, polega na dołączeniu do niej kolumn, które należą do innych tabel w reklamie i są ich częścią) moda hoc ) może pomóc np. przyspieszyć (na poziomie fizycznym) przetwarzanie tylko jednej lub kilku konkretnych instrukcji SELECT, podczas gdy taki sposób działania może jednocześnie zagrozić wykonaniu wielu innych powiązanych danych operacje manipulacji (np. kilka instrukcji INSERT, UPDATE, DELETE i SELECT lub ich kombinacje ujęte w jednej lub wielu transakcjach ACID).
Ponadto denormalizacja (formalna lub nieformalna) wprowadziłaby anomalie aktualizacji / modyfikacji, które pogarszałyby spójność bazy danych, problem, który „można” rozwiązać za pomocą skomplikowanych, kosztownych i podatnych na błędy procedur, gdy można temu zapobiec Sam początek.
Rusztowania na poziomie fizycznym obsługujące znormalizowane i „zdenormalizowane” tabele
Logiczny (abstrakcyjny) układ (projekt SQL-DDL), który ma być wykorzystywany w świecie rzeczywistym, wyraźnie ma fizyczne (konkretne) konsekwencje, które należy wziąć pod uwagę.
W ten sposób „zdenormalizowana” tabela z konieczności byłaby „szersza” (zawierająca dodatkowe kolumny), co oznacza, że jej rzędy byłyby z konieczności cięższe (wymagając coraz większej liczby elementów fizycznych), co oznacza, że leżące u podstaw procesy obliczeniowe (np. , te, które mają związek z dyskiem twardym lub pamięcią), można łatwo zwolnić.
W przeciwieństwie do tego znormalizowana tabela, która jest oczywiście „węższa” (z mniejszą liczbą kolumn) byłaby „lżejszym” elementem (obsługiwanym przez coraz mniejszą liczbę elementów fizycznych), który „zachowuje się szybciej”, co przyspieszyłoby serię działań związanych z , np. zapis i odczyt danych.
W związku z tym bardzo wygodne jest (a) znormalizowanie odpowiednich tabel formalnie i ostrożnie, utrzymanie ich jako takich, a następnie (b) wykorzystanie dowolnego zasobu na poziomie fizycznym, który może zoptymalizować szybkość pobierania i modyfikacji danych, np. Wdrożenie ostrożna i wydajna strategia indeksowania, umożliwiająca prawidłowe konfiguracje serwerów oprogramowania i sprzętu, zwiększanie przepustowości sieci itp.
Funkcjonowanie rozważanej bazy danych
Poniższe akapity twojego pytania dotyczą szybkości operacji pobierania danych:
[A] s produkt „działa”, waha się, aby ulepszyć bazę danych; niemniej pierwszą rzeczą, którą zauważyłem, jest załadowanie jednej strony (trwa 1 minutę (tak, 60 sekund!).
Jeśli obciążenie określonej strony zajmuje tyle, jest oczywiste, że użytkownicy systemu nie otrzymują dobrej usługi; dlatego nawet gdy „działa”, jego funkcjonowanie wcale nie wydaje się optymalne, co wskazuje na to, że twoje zamiary zwiększenia wydajności całego środowiska (bazy danych i aplikacji) są dobrze utrzymane i wykazuje bardzo konstruktywne podejście.
Wtedy, nawet jeśli nauka zdecydowanie cię wspiera, a zatem powinieneś zachować stanowczą postawę, sugeruję podejście do sytuacji w sposób dyplomatyczny, ponieważ pod koniec dnia twoi pracodawcy, koledzy i ty dołączyliście do wysiłków, aby stworzyć całą organizację bardziej udany. Dlatego jest to jeden z argumentów, na który należy zwrócić uwagę, że chociaż robią one więcej niż dobrze, poprawa ogólnych i szczegółowych praktyk zarządzania danymi może znacznie pomóc w zwiększeniu wzrostu organizacyjnego i indywidualnego.
Większość odpowiednich zapytań obejmuje operacje JOIN, co powoduje, że działają one bardzo, bardzo, bardzo wolno z dużą ilością danych (baza danych zawiera miliony wierszy).
Warto zauważyć, że operator JOIN jest istotnym i potężnym elementem, który dotyczy relacyjnej manipulacji danymi. Następnie, chociaż solidniejsze platformy obsługują je przy stosunkowo szybszych wykonaniach, opisywana okoliczność jest najprawdopodobniej objawem nieefektywnego projektu (na poziomie koncepcyjnym, logicznym i fizycznym abstrakcji). Tak więc moje pierwsze oszacowania to:
- Ustawienia INDEKSU mogą wymagać poprawy.
- Definicje typów i rozmiarów kolumn PK i FK muszą zostać przejrzane (i całkowicie zgadzam się z @Rick James w kwestii jego rozważań dotyczących PK , ponieważ złożone klucze KLUCZOWE są zwykle znacznie bardziej wydajne niż dołączone odpowiedniki w odpowiednich przypadkach).
- Dalsza (formalna, oparta na podstawach naukowych) normalizacja może pomóc w złagodzeniu tych problemów, z uwagi na fakt, że w odpowiednich okolicznościach (tj. Przeprowadzane w dobrze zaprojektowanej bazie danych) połączenia JOIN są wykonywane bardzo szybko .
Ponadto tak, jak wspomina @TommCatt w swojej odpowiedzi , czasami (logiczne) przepisanie zapytania modyfikuje jego (fizyczny) plan wykonania, przyspieszając odczyt / zapis danych, co jest czynnikiem, który należy zdecydowanie wziąć pod uwagę.