Jakie są względy wydajnościowe między używaniem szerokiej PK a osobnym kluczem syntetycznym a UQ?

10

Mam kilka tabel, w których rekordy można jednoznacznie identyfikować z kilkoma szerokimi obszarami działalności. W przeszłości używałem tych pól jako PK, mając na uwadze następujące korzyści:

Prostota; nie ma żadnych obcych pól i tylko jeden indeks
Grupowanie pozwala na szybkie łączenie scalające i filtry oparte na zakresie

Jednak słyszałem obudowę wykonaną za stworzenie syntetycznego IDENTITY INTPK, a zamiast egzekwowania klawisz biznesowych z osobnym UNIQUEograniczeń. Zaletą jest to, że wąski PK tworzy znacznie mniejsze wskaźniki wtórne.

Jeśli tabela nie ma innych wskaźników niż PK, nie widzę żadnego powodu, aby faworyzować drugie podejście, chociaż w dużej tabeli prawdopodobnie najlepiej założyć, że wskaźniki mogą być konieczne w przyszłości, a zatem faworyzują wąskie syntetyczne PK . Czy brakuje mi jakichkolwiek uwag?

Nawiasem mówiąc, nie sprzeciwiam się używaniu kluczy syntetycznych w hurtowniach danych, jestem tylko zainteresowany, kiedy użyć jednego szerokiego PK, a kiedy użyć wąskiego PK i szerokiej Wielkiej Brytanii.

— Jon of All Trades
źródło

1

może się okazać, to czy to pomocne między innymi pytania w miejscu

— Jack mówi spróbować topanswers.xyz

11

Nie ma znaczącej wady używania klucza naturalnego jako indeksu klastrowego

nie ma indeksów nieklastrowanych
brak kluczy obcych odnoszących się do tej tabeli (jest to wiersz nadrzędny)

Minusem byłyby większe podziały stron, ponieważ wstawiane dane byłyby rozmieszczone w danych zamiast na końcu.

W przypadku indeksów FK lub NC zastosowanie wąskiego, numerycznego, rosnącego indeksu klastrowego ma zalety. Powtarzasz tylko kilka bajtów danych dla każdego wpisu NC lub FK, a nie klucz biznesowy / naturalny while.

Po co, przeczytaj zbyt 5 artykułów z Google

Uwaga: Unikałem użycia „klucza podstawowego”.

Możesz mieć indeks klastrowany na kluczu zastępczym, ale zachowaj PK zgodnie z regułami biznesowymi, ale jako nieklastrowany. Upewnij się tylko, że klaster jest unikalny, ponieważ SQL doda „unikalizator”, aby tak było.

Wreszcie sensowne może być posiadanie klucza zastępczego, ale nie na ślepo na każdym stole : wiele-wiele tabel nie potrzebuje jednego lub w przypadku, gdy wystarczy klucz złożony z tabel nadrzędnych

— gbn
źródło

+1 za odniesienie Pani Tripp wybitne artykuły w indeksowaniu.

— Fabricio Araujo

2

+1 za to, że wydajność nie ma nic wspólnego z kluczami głównymi i wszystko z indeksami.

— nvogel

4

Chociaż zaryzykuję stwierdzenie oczywistości, indeks klucza zastępczego (numer identyfikacyjny) jest przydatny, jeśli potrzebujesz zlokalizować rzeczy według ich numeru identyfikacyjnego. Użytkownicy nie będą mieli do czynienia z numerem identyfikacyjnym; zajmą się tekstem czytelnym dla człowieka. Musisz więc często przekazywać tekst i jego numer identyfikacyjny, aby interfejs użytkownika mógł wyświetlać tekst i operować na numerze identyfikacyjnym.

Dbms użyje tego rodzaju indeksu do obsługi kluczy obcych, jeśli zdefiniujesz je w ten sposób.

Czasami można poprawić wydajność, używając numerów identyfikacyjnych jako kluczy obcych, ale nie jest to absolutna poprawa. W naszym systemie OLTP klucze obce przy użyciu kluczy naturalnych przewyższały klucze obce przy użyciu numerów identyfikacyjnych w zestawie testowym około 130 (myślę) reprezentatywnych zapytań. (Ponieważ ważne informacje są często przenoszone w klawiszach, użycie kluczy naturalnych pozwoliło uniknąć wielu sprzężeń.) Mediana przyspieszenia była czynnikiem 85 (sprzężenia przy użyciu numerów identyfikacyjnych zwracały wiersze 85 razy dłużej).

Testy wykazały, że łączenia na numerach identyfikacyjnych nie będą działały szybciej niż odczyty naturalnych kluczy w naszej bazie danych, dopóki niektóre tabele nie osiągną wielu milionów wierszy. Ma to wiele wspólnego z szerokością wiersza - szersze wiersze oznaczają, że mniej wierszy mieści się na stronie, więc musisz przeczytać więcej stron, aby uzyskać wiersze „n”. Prawie wszystkie nasze stoły są w 5NF; większość stołów jest dość wąska.

Zanim dołączenia zaczną wykonywać proste odczyty tutaj , umieszczenie krytycznych tabel i indeksów na dysku SSD może wyrównać wydajność do setek milionów wierszy.

— Mike Sherrill „Cat Recall”
źródło

3

Mam całą bazę danych oltp zaprojektowaną przy użyciu kolumn tożsamości do grupowania + pk. Działa dość szybko przy wstawianiu / wyszukiwaniu, ale widziałem kilka problemów:
1. opcja wypełniania indeksu jest bezużyteczna, ponieważ wstawki występują tylko na końcu indeksu
2. więcej miejsca do przechowywania. Mam tabele z dziesiątkami milionów rekordów i 1 int sama zajmuje miejsce. Każda tabela z kolumną tożsamości dla jej pk musi mieć inny indeks dla poszukiwań biznesowych, więc wymagane jest jeszcze więcej miejsca.
3. skalowalność. To jest najgorszy problem. Ponieważ każda wstawka przechodzi na koniec indeksu, każda wstawka będzie podkreślać tylko koniec indeksu (alokacja, io dla zapisów itp.). Używając klucza biznesowego jako klucza klastrowania, można równomiernie rozmieszczać wstawki w indeksie. Oznacza to, że właśnie wyeliminowałeś duży punkt dostępu. Możesz łatwo użyć więcej plików do indeksu, każdy plik na osobnym dysku, każdy dysk działa osobno.

Zacząłem zmieniać tabele z kolumn tożsamości na klucze naturalne (być może osobne dla klastrowania i pk). Teraz jest po prostu lepiej.

Sugerowałbym następujące (przynajmniej dla oltp db):
1. użyj jako klucza klastrowania odpowiednich kolumn we właściwej kolejności, aby zoptymalizować najczęstsze zapytania
2. użyj pk odpowiednich kolumn, które mają sens dla twojej tabeli

Jeśli klucz klastrowany nie jest prosty i zawiera znaki (char [], varchar, nvarchar), myślę, że odpowiedź brzmi „zależy”, należy przeanalizować każdy przypadek indywidualnie.

Przestrzegam następującej zasady: optymalizuj pod kątem najczęstszych zapytań, jednocześnie minimalizując najgorszy scenariusz.

Prawie zapomniałem jednego przykładu. Mam kilka tabel, które się do siebie odwołują. Jeśli tabela ma kolumnę tożsamości dla swojego klucza podstawowego, wówczas wstawienie jednego wiersza może wymagać aktualizacji, a wstawienie więcej niż jednego wiersza na raz może być trudne, jeśli nie niemożliwe (zależy to od projektu tabeli).

— Catalin Adler
źródło

4

Twoja koncepcja „hotspotu” jest mitem: dba.stackexchange.com/questions/1584/... A kiedy mówisz „Po prostu lepiej się teraz czuje”. czy przeprowadzałeś testy?

— gbn

4

Tak, zapisy są wykonywane w pamięci, a nie bezpośrednio na dysk. Jeśli zapiszesz 20 nowych wierszy na stronie, to tylko jeden fizyczny zapis do pliku danych, gdy nastąpi punkt kontrolny.

— mrdenny

@mrdenny z wystarczającą liczbą wstawek zapisujących wszystko na końcu indeksu wysyłałoby wszystkie żądania zapisu io do tego samego pliku. Podejrzewam, że przy normalnych transakcjach oltp ten scenariusz będzie trudny do odtworzenia, ale przy użyciu specjalnych scenariuszy, takich jak wprowadzanie rekordów zbiorczych / wsadowych, przenoszenie niektórych danych biznesowych za pomocą ssis.

— Catalin Adler

1

@ user973156 tak wszystkie żądania byłyby wykonane dla tego samego pliku, ale zapisy nie trafiają na dysk, dopóki nie zostaną sprawdzone, co dzieje się co minutę (domyślnie) lub gdy bufor zapisu jest zapełniony w 50%. Ta reguła nie ma znaczenia, jak zapisujesz dane.

— mrdenny,

2

@ user973156 Użycie losowo dystrybuowanego klucza klastrowania spowoduje fragmentację indeksu. Fragmentacja indeksu spowoduje problemy z wydajnością. A twoja tabela stanie się na tyle duża, że wykonanie defragmentacji indeksu zajmie „dużo czasu” i zajmie miejsce w dzienniku oraz potencjalnie w tempDB. Kiedy ludzie tacy jak Kimberly Tripp mówią mi, że to dobry pomysł, słucham. ( sqlskills.com/BLOGS/KIMBERLY/post/… )

— Matt M

2

Z punktu widzenia wydajności wybór klucza „kluczowego” nie ma żadnego znaczenia. Nie ma różnicy między użyciem klucza podstawowego a ograniczeniem UNIKALNYM w celu wymuszenia kluczy.

Wydajność zależy od wyboru i rodzaju indeksów i innych opcji przechowywania oraz od sposobu użycia kluczy w zapytaniach i kodzie.

— nvogel
źródło