Kiedy i dlaczego połączenia z bazami danych są drogie?

To, co większość komentatorów nie zauważa, to szeroki zakres metod łączenia dostępnych w złożonych RDBMS, a denormalizatory niezmiennie przewyższają wyższe koszty utrzymania denormalizowanych danych. Nie każde połączenie opiera się na indeksach, A bazy danych mają wiele zoptymalizowanych metod i metod łączenia, które mają na celu obniżenie kosztów połączenia.

W każdym przypadku koszt połączenia zależy od jego rodzaju i kilku innych czynników. Wcale nie musi być droga-niektóre przykłady.

• połączenie hash, w którym dane zbiorcze są równe, jest bardzo tanie, a koszt staje się znaczący tylko wtedy, gdy tabela hash nie może być buforowana w pamięci. Indeks nie jest wymagany. Equi-partycjonowanie między połączonymi zestawami danych może być bardzo pomocne.
Koszt połączenia sort-merge zależy od kosztu sortowania, a nie połączenia-metoda dostępu oparta na indeksach może praktycznie wyeliminować koszt sortowania.
• koszt połączenia zagnieżdżonej pętli na indeksie wynosi napędzany przez wysokość indeksu drzewa b i dostęp do samego bloku tabeli. Jest szybki, ale nie nadaje się do łączenia luzem.
• zagnieżdżona pętla join oparta na klastrze jest znacznie tańsza, z mniejszą liczbą logicznych IO wymaganych na jeden wiersz join -- jeśli połączone tabele znajdują się w tym samym klastrze, to join staje się bardzo tani poprzez kolokację połączonych wierszy.

Bazy danych są zaprojektowane do łączenia, są bardzo elastyczne w tym, jak to robią i ogólnie bardzo wydajne, chyba że błąd w mechanizmie łączenia.

Myślę, że całe pytanie opiera się na fałszywej przesłance. Połączenia na dużych stołach są nie koniecznie drogie. W rzeczywistości, wydajne łączenie jest jednym z głównych powodów, dla których istnieją relacyjne bazy danych. Łączenie dużych zbiorów często jest kosztowne, ale bardzo rzadko chcesz połączyć całą zawartość dużej tabeli a z całą zawartością dużej tabeli B. zamiast tego piszesz zapytanie w taki sposób, że używane są tylko ważne wiersze każdej tabeli a rzeczywisty zestaw utrzymywany przez połączenie pozostaje mniejszy.

DODATKOWO masz wydajność wspomnianą przez Petera Wone ' a, taką, że tylko ważne części każdego rekordu muszą być w pamięci, dopóki ostateczny zestaw wyników nie zostanie zmaterializowany. Ponadto, w przypadku dużych zapytań z wieloma połączeniami zazwyczaj chcesz zacząć od mniejszych zestawów tabel i przejść do dużych, tak aby zestaw przechowywany w pamięci pozostał tak mały, jak to możliwe, jak najdłużej.

Po poprawnym wykonaniu, dołącza są zazwyczaj najlepszym sposobem porównywania, łączenia lub filtrowania dużych ilości danych.

Wąskim gardłem jest prawie Zawsze We/Wy dysku, a dokładniej-losowe We/Wy dysku (dla porównania, odczyty sekwencyjne są dość szybkie i mogą być buforowane za pomocą strategii read ahead).

Joins can increase random seeks-if you ' re jumping around reading small parts of a large table. Ale optymalizatory zapytań szukają tego i zamienią go w sekwencyjne skanowanie tabeli (odrzucanie niepotrzebnych wierszy), jeśli uzna, że byłoby lepiej.

A single denormalizowana tabela ma podobny problem - wiersze są duże, a więc mniej pasują do jednej strony danych. Jeśli potrzebujesz wierszy, które znajdują się daleko od innych (a duży rozmiar wierszy sprawia, że są dalej od siebie), będziesz mieć więcej losowych We/Wy.ponownie, skanowanie tabeli może być zmuszone tego uniknąć. Ale tym razem skanowanie tabeli musi odczytać więcej danych ze względu na duży rozmiar wiersza. Dodaj do tego fakt, że kopiujesz DANE z jednej lokalizacji do wielu lokalizacji, a RDBMS ma to dużo więcej do przeczytania (i pamięci podręcznej).

Z 2 tabel, można również uzyskać 2 indeksy klastrowe - i ogólnie można indeksować Więcej (z powodu mniej wstawiania / aktualizacji narzutu), co może uzyskać drastycznie zwiększoną wydajność (głównie, ponownie, ponieważ indeksy są (stosunkowo) małe, szybkie do odczytu z dysku (lub Tanie do pamięci podręcznej), i zmniejszyć ilość wierszy tabeli trzeba czytać z dysku).

O tylko narzut z łącznikiem pochodzi z wymyślania pasujących wierszy. SQL Server używa 3 różnych typy złączeń, głównie na podstawie rozmiarów zbiorów danych, w celu znalezienia pasujących wierszy. Jeśli optymalizator wybierze niewłaściwy typ połączenia (z powodu niedokładnych statystyk, nieodpowiednich indeksów lub po prostu błędu optymalizatora lub przypadku krawędzi), może drastycznie wpłynąć na czasy zapytań.

• połączenie pętli jest zdecydowanie Tanie dla (przynajmniej 1) małego zbioru danych.
• połączenie merge wymaga najpierw obu zestawów danych. Jeśli jednak dołączysz do zindeksowanej kolumny, wtedy indeks jest już posortowany i nie musisz już pracować załatwione. W przeciwnym razie w sortowaniu jest trochę narzutu procesora i pamięci.
• połączenie hashowe wymaga zarówno pamięci (do przechowywania hashtable), jak i procesora (do budowania hash). Ponownie, jest to dość szybkie w odniesieniu do We/Wy dysku. jednakże, jeśli nie ma wystarczającej ilości pamięci RAM do przechowywania hashtable, Sql Server użyje tempdb do przechowywania części hashtable i znalezionych wierszy, a następnie przetworzy tylko części hashtable na raz. Podobnie jak w przypadku wszystkich dysków, jest to dość powolne.

In optymalny przypadek, powodują one brak We/Wy dysku - a więc są znikome z punktu widzenia wydajności.

W sumie, w najgorszym przypadku-powinno być szybciej odczytać taką samą ilość danych logicznych z połączonych tabel x, ponieważ jest to z jednej denormalizowanej tabeli ze względu na mniejszy odczyt dysku. Aby odczytać taką samą ilość danych fizycznych, mogą wystąpić niewielkie koszty ogólne.

Ponieważ czas zapytań jest zwykle zdominowany przez koszty We/Wy, a Rozmiar danych nie zmiana (minus kilka bardzo drobnych rzędów na górze) z denormalizacją, nie ma ogromnej korzyści, aby mieć po prostu łączenie tabel razem. Typ denormalizacji, który ma tendencję do zwiększania wydajności, IME, jest buforowanie obliczonych wartości zamiast odczytu 10 000 wierszy wymaganych do ich obliczenia.

Kolejność łączenia tabel jest niezwykle ważna. Jeśli masz dwa zestawy danych, spróbuj zbudować zapytanie w taki sposób, aby najmniejszy został użyty jako pierwszy, aby zmniejszyć ilość danych, na których zapytanie ma pracować.

W przypadku niektórych baz danych nie ma to znaczenia, na przykład MS SQL przez większość czasu zna właściwą kolejność łączenia. Dla niektórych (jak IBM Informix) kolejność robi różnicę.

Podjęcie decyzji o denormalizacji lub normalizacji jest dość prostym procesem, jeśli weźmiemy pod uwagę klasę złożoności połączenia. Na przykład, mam tendencję do projektowania moich baz danych z normalizacji, gdy zapytania są O (k log n), gdzie k jest w stosunku do żądanej wielkości wyjściowej.

Łatwym sposobem na denormalizację i optymalizację wydajności jest zastanowienie się, jak zmiany w strukturze normalizacyjnej wpływają na denormalizowaną strukturę. Może być jednak problematyczne, ponieważ może wymagać logika transakcyjna do pracy na denormalizowanej strukturze.

Debata na temat normalizacji i denormalizacji nie zakończy się, ponieważ problemy są ogromne. Istnieje wiele problemów, w których naturalne rozwiązanie wymaga obu podejść.

Ogólnie rzecz biorąc, zawsze przechowywałem znormalizowaną strukturę i denormalizowaną pamięć podręczną, które można zrekonstruować. W końcu te Cache ratują mój tyłek, aby rozwiązać przyszłe problemy normalizacji.

Opracowanie tego, co powiedzieli inni,

Łączniki są tylko produktami kartezjańskimi z pewną liposukcją. {1,2,3,4} x{1,2,3} dałoby nam 12 kombinacji (nXn=N^2). Ten zbiór obliczeniowy działa jako punkt odniesienia, w odniesieniu do których stosowane są warunki. DBMS stosuje warunki (takie jak gdzie zarówno lewa, jak i prawa są 2 lub 3), aby dać nam pasujące warunki. W rzeczywistości jest bardziej zoptymalizowany, ale problem jest ten sam. Zmiany rozmiaru zestawów zwiększyłyby wykładniczo rozmiar wyniku. Kwota zużyte cykle pamięci i procesora są wykonywane w ujęciu wykładniczym.

Kiedy denormalizujemy, unikamy tego obliczenia całkowicie, pomyśl o tym, aby mieć kolorowy lepki, dołączony do każdej strony książki. Można wywnioskować informacje z zewnątrz za pomocą odniesienia. Kara, którą płacimy, polega na tym, że narażamy istotę DBMS (optimal organization of data)