SQL - klucz podstawowy tabeli wiele do wielu

125

To pytanie pojawia się po przeczytaniu komentarza do tego pytania:

Tworząc tabelę „wiele do wielu”, należy utworzyć złożony klucz podstawowy na dwóch kolumnach klucza obcego, czy też utworzyć zastępczy klucz podstawowy „ID” z automatyczną inkrementacją i po prostu umieścić indeksy w dwóch kolumnach FK (i być może unikalne ograniczenie)? Jakie są konsekwencje dla wydajności wstawiania nowych rekordów / ponownego indeksowania w każdym przypadku?

Zasadniczo to:

PartDevice
----------
PartID (PK/FK)
DeviceID (PK/FK)

w porównaniu z tym:

PartDevice
----------
ID (PK/auto-increment)
PartID (FK)
DeviceID (FK)

Komentator mówi:

uczynienie dwóch identyfikatorów PK oznacza, że tabela jest fizycznie sortowana na dysku w tej kolejności. Więc jeśli wstawimy (Part1 / Device1), (Part1 / Device2), (Part2 / Device3), to (Part 1 / Device3) baza danych będzie musiała rozdzielić tabelę i wstawić ostatnią pomiędzy wpisy 2 i 3. Dla wielu rekordów, staje się to bardzo problematyczne, ponieważ wiąże się z tasowaniem setek, tysięcy lub milionów rekordów za każdym razem, gdy jest dodawany. Z kolei autoinkrementacja PK pozwala na dopięcie nowych rekordów do końca.

Pytam o to, że zawsze skłaniałem się do tworzenia złożonego klucza podstawowego bez zastępczej kolumny autoinkrementacji, ale nie jestem pewien, czy klucz zastępczy jest rzeczywiście bardziej wydajny.

sql primary-key many-to-many

— Andy White
źródło

Oto pytanie silimar opublikowane na SO: stackoverflow.com/questions/344068/…

— Tony

(Próbowałem dodać to do mojego poprzedniego komentarza, ale nie mogę) W zależności od liczby wstawień możesz również okresowo przebudowywać indeks, aby zapewnić szybkie zwracanie wyników. W SQL Server można również dostosować FILLFACTOR indeksu, aby zapewnić wystarczającą ilość miejsca na wstawianie, zanim będzie musiał przenosić dane.

— Tony

1

Czy odpowiedź na to pytanie nie zależy od używanego DBMS? Podejrzewam, że MySQL zachowa się w tym przypadku w inny sposób, SQL-Server trochę inaczej itd.

— Radu Murzea

Uwaga: bez określonego tagu bazy danych wiele z tego, co tu powiedziano, jest podejrzanych. Różne silniki działają inaczej!

— Rick James

85

W przypadku prostego mapowania wiele-do-wielu z dwiema kolumnami, nie widzę żadnych korzyści z posiadania klucza zastępczego. Posiadanie klucza podstawowego (col1,col2)jest gwarantowane jako unikalne (zakładając, że twoje col1i col2wartości w tabelach, do których się odwołujesz są unikalne), a oddzielny indeks na (col2,col1)włączyłby te przypadki, w których odwrotna kolejność wykonywałaby się szybciej. Surogat to strata miejsca.

Nie będziesz potrzebować indeksów w poszczególnych kolumnach, ponieważ tabela powinna być używana tylko do łączenia dwóch tabel, do których istnieją odniesienia.

Moim zdaniem ten komentarz, do którego odnosisz się w pytaniu, nie jest wart wykorzystanych przez niego elektronów. Wygląda na to, że autor uważa, że tabela jest przechowywana w tablicy, a nie w zbalansowanej strukturze drzewiastej o wyjątkowo wysokiej wydajności.

Po pierwsze, nigdy nie jest konieczne przechowywanie ani pobieranie posortowanej tabeli , tylko indeks. Indeks nie będzie przechowywany sekwencyjnie, będzie przechowywany w efektywny sposób, aby można go było szybko odzyskać.

Ponadto zdecydowana większość tabel bazy danych jest czytana znacznie częściej niż zapisywana. To sprawia, że wszystko, co robisz po stronie wybranej, jest znacznie bardziej istotne niż cokolwiek po stronie wkładki.

— paxdiablo
źródło

Ostatni punkt nie jest dobrym uogólnieniem: „zdecydowana większość tabel bazy danych jest czytana znacznie częściej niż zapisywana”. Znajduję wiele przykładów tabel asocjacyjnych, do których trzeba bardzo często pisać, np. Tabela łącząca klienta z zamówieniem.

— użytkownik

5

@buffer, podtrzymam ten komentarz (technicznie rzecz biorąc, jest to uogólnienie tylko wtedy, gdy powiem „wszystkie tabele”, „zdecydowana większość” jest oparta na doświadczeniu). Pomyślmy również o Twoim przykładzie, zamówienie jest tworzone raz (może być aktualizowane od czasu do czasu, ale jest mało prawdopodobne, aby zmieniło to informacje o kluczu / indeksie, bardziej dotyczy rzeczy takich jak stan zamówienia. Jednak te aktualizacje i wybory, które musisz zrobić, wydrukowanie faktur lub wygenerowanie raportów zarządczych przeważy nad oryginalną ulotką.

— paxdiablo

Pomyśl o Amazon - tysiące zamówień tworzonych co godzinę.

— użytkownik

9

@buffer tak, ale znowu, każdy z tych zleceń będzie prawie na pewno być pytani wiele razy do zrobienia (na przykład) pakowanie, fakturowanie, aktualizacje statusu, analiz biznesowych i tak dalej. Bezwzględna liczba kreacji jest mniej ważna niż stosunek między utworzeniami a odczytami.

— paxdiablo

1

Chodzi mi o to, że insertbędzie miało znaczenie, jeśli będzie to wykonywane tysiące razy na godzinę. Nie można go po prostu zignorować tylko dlatego, że stosunek insertdo selectwynosi <1. W tym przypadku klientowi zależy na tym, ile czasu zajmuje złożenie zamówienia.

— użytkownik

19

W przypadku tabel linków nie jest wymagany klucz zastępczy.

Jeden PK na (col1, col2) i inny unikalny indeks na (col2, col1) to wszystko, czego potrzebujesz

Chyba że używasz ORM, który nie może sobie poradzić i dyktuje ci projekt DB ...

Edycja: Odpowiedziałem to samo tutaj: SQL: Czy potrzebujesz automatycznego przyrostowego klucza podstawowego dla wielu tabel?

— gbn
źródło

3

Możesz być w porządku z indeksem dups na col2 zamiast unikalnego indeksu na (col2, col1). Zaletą indeksu dwukolumnowego jest to, że umożliwia skanowanie tylko indeksu na samej kolumnie2 lub na obu kolumnach (col1, col2) (chociaż drugi indeks (col1, col2) również obsługuje przypadek „obu”). Wadą jest dodatkowe miejsce potrzebne na dodatkową kolumnę. Zwykle nie ma to znaczenia, więc rada nie jest straszna. Niemniej jednak, jeśli kol1 i kol2 są duże lub mają bardzo różne rozmiary, możesz zaoszczędzić trochę miejsca bez pogorszenia wydajności, wybierając drugi indeks tylko na krótszej kolumnie.

— Jonathan Leffler

@gbn: Drugi indeks na (col2, col1) nie musi być unikalny, prawda?

— użytkownik

1

umieszczenie unikalnego indeksu na (kol1, kol2) po tym, jak jest już PK jest całkowicie zbędne

— Don Cheadle

@mmcrae: gdzie to robimy?

— gbn

2

@mmcrae: Twój komentarz to „umieszczenie unikalnego indeksu na (col1, col2) ..”. Kolejność kolumn w indeksie ma znaczenie. (col2, col1)nie jest (col1, col2). PK (col1, col2)może nie być odpowiedni dla wszystkich zapytań i generowania skanów, więc odwrócenie tego poprawia wydajność, ponieważ umożliwia wyszukiwanie, gdzie col2 jest lepsze. Na przykład walidacja FK, gdy tabela z col2 ma usunięcie. Stolik podrzędny może być sprawdzony

— gbn

12

Jeśli istnieje odwołanie do tabeli, może być potrzebny przyrostowy klucz podstawowy. W tabeli wiele-do-wielu mogą znajdować się szczegóły, które należy pobrać z innej tabeli przy użyciu przyrostowego klucza podstawowego.

na przykład

PartDevice
----------
ID (PK/auto-increment)
PartID (FK)
DeviceID (FK)
Other Details

Można łatwo pobrać „Inne szczegóły”, używając PartDevice.ID jako FK. W związku z tym konieczne jest użycie przyrostowego klucza podstawowego.

— Jronny
źródło

1

Dzięki! Doszedłem do odpowiedzi, ponieważ szukałem prawie tego samego scenariusza, który opisałeś. Ale odszedłeś od pierwszego zdania, dodając „Inne szczegóły”. Co jeśli mam wiele do wielu tabel odwzorowań, do których muszę odwołać się z innej tabeli? Oznacza to, że tabela mapowania wiele do wielu nie przechowuje żadnych innych informacji ... Czy i tak dodatkowa kolumna ID miałaby sens? Jeśli nie, jak zamiast tego odwołać się do jednego rekordu tabeli odwzorowań?

— misanthrop

Są tutaj dwie opcje, możesz użyć klucza złożonego jako klucza obcego z tabeli referencyjnej (to dodaje dodatkową kolumnę do twojej nowej tabeli) lub możesz utworzyć kolumnę id do tabeli mapowania i ustawić unikalne ograniczenie na oryginalny związek klucz podstawowy, podczas gdy nowa kolumna id stanie się kluczem podstawowym.

— Vočko

6

Najkrótszym i najbardziej bezpośrednim sposobem, w jaki mogę odpowiedzieć na twoje pytanie, jest stwierdzenie, że wpłynie to na wydajność, jeśli dwie tabele, które łączysz, nie mają sekwencyjnych kluczy podstawowych. Jak powiedziałeś / cytowałeś, indeks tabeli dowiązań albo zostanie pofragmentowany, albo DBMS będzie pracował ciężej, aby wstawić rekordy, jeśli tablica dowiązań nie ma własnego sekwencyjnego klucza podstawowego. To jest powód, dla którego większość ludzi umieszcza sekwencyjnie rosnący klucz podstawowy w tabelach łączy.

— Bernhard Hofmann
źródło

2

Wygląda więc na to, że jeśli JEDYNYM zadaniem jest połączenie dwóch tabel, najlepszym PK będzie PK z dwoma kolumnami.

Ale jeśli służy to innym celom, dodaj kolejny NDX jako PK z obcymi kluczami i drugim unikalnym indeksem.

Indeks lub PK to najlepszy sposób na upewnienie się, że nie ma duplikatów. PK pozwala narzędziom takim jak Microsoft Management Studio wykonać część pracy (tworzenie widoków) za Ciebie

— michael kosak
źródło