Pytanie Dlaczego sprzężenia są złe, biorąc pod uwagę skalowalność?


Dlaczego sprzężenia są złe lub "wolne". Wiem, że słyszałem to więcej niż raz. Znalazłem ten cytat

Problem polega na tym, że połączenia są względnie   wolno, szczególnie w przypadku bardzo dużych danych   zestawy, a jeśli są one powolne   witryna jest powolna. To zajmuje dużo czasu   zdobyć wszystkie te oddzielne fragmenty   informacje z dysku i umieść je wszystkie   znowu razem.

źródło

Zawsze myślałem, że są szybkie, szczególnie gdy patrzę na PK. Dlaczego są "powolni"?


76
2018-04-12 17:02


pochodzenie




Odpowiedzi:


Łączenie dwóch oddzielnych źródeł danych jest stosunkowo powolne, przynajmniej w porównaniu do braku dołączenia do nich. Ale pamiętaj, że alternatywą jest brak w ogóle dwóch oddzielnych danych; musisz umieścić dwa odmienne punkty danych w tym samym rekordzie. Nie można łączyć dwóch różnych elementów danych, nie ma gdzieś konsekwencji, więc upewnij się, że rozumiesz kompromis.

Dobrą wiadomością jest to, że nowoczesne relacyjne bazy danych są dobry przy złączach. Nie powinieneś myśleć o łączeniach tak wolnych z dobrą bazą danych. Baza danych udostępnia wiele sposobów łączenia nieprzetworzonych połączeń i ich tworzenia dużo szybciej:

  • Dołącz do zastępczego klucza (kolumna autonumer / tożsamość) zamiast klucza naturalnego. Oznacza to mniejsze (a zatem i szybsze) porównania podczas operacji łączenia
  • Indeksy
  • Widoki zmaterializowane / indeksowane (pomyśl o tym jako o wstępnie obliczonym sprzężeniu lub zarządzane de-normalizacja)
  • Kolumna obliczeniowa. Możesz użyć tego do skrótu lub w inny sposób wstępnie obliczyć kluczowe kolumny sprzężenia, tak że to, co byłoby skomplikowanym porównaniem dla łączenia, jest teraz dużo mniejsze i potencjalnie wstępnie zindeksowane.
  • Partycje tabel (pomaga w dużych zestawach danych, rozkładając obciążenie na wiele dysków lub ograniczając to, co mogło być skanowaniem tabeli do skanowania partycji)
  • OLAP (wstępnie oblicza wyniki niektórych rodzajów zapytań / sprzężeń.) Nie jest to do końca prawdą, ale możesz myśleć o tym jako ogólny denormalizacja)

Posunąłbym się tak daleko, aby to powiedzieć Głównym powodem, dla którego istnieją relacyjne bazy danych, jest umożliwienie wydajnego łączenia*. Z pewnością nie jest to tylko przechowywanie danych strukturalnych (można to zrobić przy użyciu płaskich konstrukcji plików, takich jak csv lub xml). Kilka opcji, które wymieniłem, pozwoli ci nawet całkowicie skompilować twoją łączność z góry, więc wyniki są już zrobione przed wysłaniem zapytania - tak, jakbyś zdenormalizował dane (co prawda kosztem wolniejszych operacji zapisu).

Jeśli masz wolne połączenie, prawdopodobnie nie używasz poprawnie bazy danych. 

Oddalenie normalizacji powinno nastąpić dopiero po niepowodzeniu tych innych technik. Jedynym sposobem, w jaki można naprawdę ocenić "porażkę", jest ustalenie znaczących celów wydajności i zmierzenie się z tymi celami. Jeśli nie mierzysz, to jest zbyt wcześnie, aby nawet pomyśleć o normalizacji.

* Oznacza to, że istnieją jako podmioty odrębne od zwykłych zbiorów tabel. Dodatkowym powodem prawdziwego rdbms jest bezpieczny równoczesny dostęp.


76
2018-04-12 17:23



Indeksy powinny znajdować się u góry listy. Dużo (kaszel) wydaje się, że programiści zapominają o nich podczas testowania na małym zestawie danych, a następnie przynoszą bazę danych do kolan w produkcji. Widziałem zapytania, które działają 100 000 razy szybciej, po prostu dodając indeksy. A to są arbitralne indeksy bez nawet dogłębnej analizy danych, aby określić najlepszą mieszankę dla lewostronnego dopasowywania prefiksów. - Duncan
Wydaje mi się, że mam już porządek - większość deweloperów robi już pierwszy przedmiot, więc indeksy to pierwszy element, w którym trzeba wprowadzić zmiany. - Joel Coehoorn
W trzeciej pozycji wspominasz o "Zmaterializowanych / indeksowanych widokach". Czy mówisz o zwykłych widokach SQL, czy o czymś innym? - slolife
Widoki sqlife regular sql są jak uruchamianie dodatkowych zapytań w tle w locie, gdy używasz zapytania odwołującego się do widoku. Ale możesz również powiedzieć serwerowi SQL, aby "zmaterializował" niektóre widoki. Gdy to zrobisz, serwer sql będzie przechowywać dodatkową kopię danych widoku, podobnie jak zwykła tabela, tak że kiedy odwołasz się do widoku w zapytaniu, nie musisz już uruchamiać tego zapytania w tle, ponieważ dane już tam są . Możesz także umieścić różne indeksy w widoku niż w tabeli źródłowej, aby jeszcze bardziej ułatwić dostrojenie wydajności. - Joel Coehoorn
Dzięki Joel. Będę musiał się nad tym zastanowić. - slolife


Połączenia mogą być wolniej niż unikanie ich poprzez normalizację, ale jeśli są stosowane poprawnie (dołączanie do kolumn z odpowiednimi indeksami itd.) nie są z natury powolne.

De-normalizacja jest jedną z wielu technik optymalizacji, które można rozważyć, jeśli dobrze zaprojektowany schemat bazy danych wykazuje problemy z wydajnością.


28
2018-04-12 17:11



... z wyjątkiem MySQL, który wydaje się mieć problemy z wydajnością z dużą liczbą złączeń, niezależnie od tego, jak wyglądają twoje indeksy. A przynajmniej tak było w przeszłości. - Powerlord
Punkt, jeśli istnieją znane problemy z konkretnym DBMS (a może nawet wersja), wtedy ta rada może mieć sens, ale jako ogólna rada jest dość myląca, jeśli korzystasz z relacyjnej bazy danych. Wspomniane nierelacyjne mechanizmy przechowywania stają się coraz bardziej popularne w Amazon's SimpleDB i CouchDB (couchdb.apache.org) są przykładami. Jeśli lepiej służysz, zostawiając za sobą model relacyjny, prawdopodobnie powinieneś zostawić produkty, które zoptymalizowały dla tylu, i poszukać innych narzędzi. - Tendayi Mawushe


artykuł mówi, że są powolne w porównaniu do braku złączeń. można to osiągnąć poprzez denormalizację. więc istnieje kompromis między szybkością a normalizacją. nie zapomnij również o przedwczesnej optymalizacji :)


12
2018-04-12 17:08



nawet nie jest to trudna reguła, jeśli dołączasz do tabeli, mysql może użyć indeksu do wykonania tego sprzężenia - to sprzężenie indeksu może przyciąć wiele wierszy, a także inny indeks dla dowolnej klauzuli where w tabelach. Jeśli się nie połączysz, mysql będzie zwykle używał tylko jednego indeksu (który może nie być najskuteczniejszy), bez względu na to, jak twoja klauzula where zostanie utworzona. - leeeroy


Po pierwsze, relacyjna racja bazy danych relacyjnych (powód bytu) to możliwość modelowania relacji między jednostkami. Połączenia są po prostu mechanizmami, przez które przechodzimy przez te relacje. Z pewnością kosztują one nominalnie, ale bez połączeń nie ma powodu, by mieć relacyjną bazę danych.

W świecie akademickim dowiadujemy się o różnych normalnych formach (1., 2., 3., Boyce-Codd itd.) I uczymy się różnych rodzajów kluczy (podstawowych, obcych, alternatywnych, unikalnych itp.) Oraz te rzeczy pasują do projektu bazy danych. Uczymy się podstaw języka SQL, a także manipulujemy strukturą i danymi (DDL i DML).

W świecie korporacyjnym wiele akademickich konstrukcji okazuje się znacznie mniej opłacalne, niż nam się wydawało. Doskonałym przykładem jest pojęcie klucza podstawowego. Zasadniczo jest to atrybut (lub zbiór atrybutów), który jednoznacznie identyfikuje jeden wiersz w tabeli. Tak więc w wielu problematycznych dziedzinach właściwy akademicki klucz podstawowy jest złożony z 3 lub 4 atrybutów. Jednak prawie wszyscy w nowoczesnym świecie korporacyjnym używają generowanej automatycznie, sekwencyjnej liczby całkowitej jako klucza podstawowego tabeli. Czemu? Dwa powody. Po pierwsze, sprawia, że ​​model jest o wiele bardziej przejrzysty, gdy migruje się pliki FK w każdym miejscu. Drugim, i najbardziej istotnym dla tego pytania, jest to, że pobieranie danych przez połączenia jest szybsze i bardziej efektywne na jednej liczbie całkowitej niż na 4 kolumnach varchar (jak już wspomniano przez kilka osób).

Zagłębmy się teraz nieco głębiej w dwa konkretne podtypy baz danych świata rzeczywistego. Pierwszy typ to transakcyjna baza danych. Jest to podstawą wielu aplikacji e-commerce lub aplikacji do zarządzania treścią obsługujących nowoczesne witryny. Z transakcyjnym DB optymalizujesz się w kierunku "przepustowości transakcji". Większość aplikacji do obsługi handlu lub treści musi równoważyć wydajność zapytań (z niektórych tabel) z wydajnością wstawiania (w innych tabelach), chociaż każda aplikacja będzie miała własne, unikalne problemy biznesowe, które należy rozwiązać.

Drugi typ rzeczywistej bazy danych to baza danych raportowania. Są one używane niemal wyłącznie do agregowania danych biznesowych i generowania znaczących raportów biznesowych. Zwykle są one kształtowane inaczej niż bazy danych transakcji, w których generowane są dane i są wysoce zoptymalizowane pod kątem szybkości ładowania danych masowych (ETL) i wydajności zapytań z dużymi lub złożonymi zbiorami danych.

W każdym przypadku deweloper lub DBA musi dokładnie zbilansować zarówno funkcjonalność, jak i krzywe wydajności, i istnieje wiele sztuczek zwiększających wydajność po obu stronach równania. W Oracle możesz zrobić tak zwany "plan wyjaśniający", dzięki czemu możesz zobaczyć, w jaki sposób zapytanie jest analizowane i wykonywane. Chcesz zmaksymalizować właściwe korzystanie z indeksów przez DB. Jednym naprawdę nieprzyjemnym nie-nie jest umieszczenie funkcji w klauzuli where zapytania. Ilekroć to robisz, gwarantujesz, że Oracle nie użyje żadnych indeksów w tej konkretnej kolumnie i prawdopodobnie zobaczysz pełne lub częściowe skanowanie tabeli w planie wyjaśniania. To tylko jeden konkretny przykład tego, jak można napisać zapytanie, które kończy się powoli i nie ma nic wspólnego z łączeniami.

I chociaż mówimy o skanowaniu tabeli, to oczywiście wpływają one na szybkość zapytań proporcjonalnie do rozmiaru tabeli. Pełne skanowanie tabeli 100 wierszy nie jest nawet zauważalne. Przeprowadź to samo zapytanie na stole z 100 milionami wierszy i musisz wrócić za zwrot w przyszłym tygodniu.

Porozmawiajmy o normalizacji na minutę. Jest to kolejny w dużej mierze pozytywny temat akademicki, który może ulec nadmiernemu stresowi. W większości przypadków, gdy mówimy o normalizacji, mamy na myśli eliminację duplikatów danych poprzez umieszczenie ich we własnej tabeli i migrację FK. Ludzie zwykle omijają całą zależność opisaną przez 2NF i 3NF. A jednak w skrajnym przypadku z całą pewnością można mieć doskonałą bazę danych BCNF, która jest ogromna i kompletna bestia do pisania kodu, ponieważ jest tak znormalizowana.

Więc gdzie się balansujemy? Nie ma jednej najlepszej odpowiedzi. Wszystkie lepsze odpowiedzi wydają się być kompromisem między łatwą konserwacją struktury, łatwą konserwacją danych i łatwością tworzenia / utrzymywania kodu. Ogólnie rzecz biorąc, im mniej powielanych danych, tym lepiej.

Dlaczego połączenia są czasem wolne? Czasami jest to zły projekt relacyjny. Czasami jest to nieskuteczne indeksowanie. Czasami jest to problem z woluminem danych. Czasami jest to okropnie napisane zapytanie.

Przepraszam za tak długotrwałą odpowiedź, ale czułem się zmuszony do zapewnienia bardziej mięsistego kontekstu w moich komentarzach, a nie tylko odskoczyć od 4-punktowej odpowiedzi.


10
2018-04-13 01:00





Ludzie z bazami o rozmiarach terabajtów nadal używają złączeń, jeśli mogą je zmusić do pracy pod względem wydajności, więc możesz też.

Istnieje wiele powodów, dla których nie należy denomalizować. Po pierwsze, szybkość wybranych zapytań nie jest jedynym, a nawet głównym problemem związanym z bazami danych. Podstawowym problemem jest integralność danych. Jeśli dokonasz denormalizacji, musisz wprowadzić techniki, które pozwolą na denormalizację danych w miarę zmian danych macierzystych. Załóżmy, że bierzesz do przechowywania nazwy klienta we wszystkich tabelach, zamiast łączenia się z tabelą klienta w client_Id. Teraz, gdy zmieni się nazwa klienta (100% szansy, że niektóre nazwy klientów będą się zmieniać w czasie), teraz musisz zaktualizować wszystkie rekordy podrzędne, aby odzwierciedlić tę zmianę. Jeśli to zrobisz, będziesz miał aktualizację kaskadową i masz miliony rekordów podrzędnych, jak szybko przypuszczasz, ile osób będzie cierpieć z powodu problemów z blokowaniem i opóźnień w ich pracy, kiedy to się stanie? Ponadto większość osób, które denormalizują się, ponieważ "połączenia są wolne", nie mają wystarczającej wiedzy na temat baz danych, aby właściwie upewnić się, że ich integralność danych jest chroniona i często trafiają do baz danych, które mają niepotrzebne dane, ponieważ integralność jest tak zła.

Denormalizacja to złożony proces, który wymaga dogłębnego zrozumienia wydajności i integralności bazy danych, jeśli ma być wykonany prawidłowo. Nie próbuj denormalizować, chyba że posiadasz taką wiedzę na temat personelu.

Połączenia są dość szybkie, jeśli robisz kilka rzeczy. Najpierw użyj klawisza suggorgate, int join to prawie alizuje najszybsze dołączenie. Drugi zawsze indeksuj klucz obcy. Użyj tabel pochodnych lub warunków łączenia, aby utworzyć mniejszy zestaw danych do filtrowania. Jeśli masz dużą, bardzo złożoną bazę danych, zatrudnij profesjonalną bazę danych z doświadczeniem w partycjonowaniu i zarządzaniu ogromnymi bazami danych. Istnieje wiele technik poprawy wydajności bez pozbycia się złączeń.

Jeśli potrzebujesz tylko funkcji zapytania, możesz tak zaprojektować serwer danych, który może być zdenormalizowany i jest zapełniany przez narzędzie ETL (zoptymalizowane pod kątem szybkości), a nie wprowadzanie danych przez użytkownika.


9
2018-04-12 17:44





Połączenia są powolne, jeśli

  • dane są nieprawidłowo zindeksowane
  • wyniki źle filtrowane
  • przystępowanie do zapytania źle napisane
  • zestawy danych są bardzo duże i złożone

Prawdą jest, że im większy zestaw danych, tym więcej przetwarzania potrzebujesz do wykonania kwerendy, ale sprawdzanie i praca nad pierwszymi trzema opcjami powyższego często przynosi wspaniałe rezultaty.

Twoje źródło daje denormalizację jako opcję. Jest to w porządku, o ile wyczerpała się lepsza alternatywa.


8
2018-04-12 17:13





Połączenia mogą być powolne, jeśli trzeba zeskanować duże porcje rekordów z każdej strony.

Lubię to:

SELECT  SUM(transaction)
FROM    customers
JOIN    accounts
ON      account_customer = customer_id

Nawet jeśli zdefiniowano indeks account_customer, wszystkie zapisy z tego ostatniego wciąż wymagają skanowania.

Na liście zapytań to przyzwoite optymalizatory prawdopodobnie nie będą nawet uwzględniać ścieżki dostępu do indeksu, robiąc a HASH JOIN lub MERGE JOIN zamiast.

Zauważ, że dla zapytania takiego jak to:

SELECT  SUM(transaction)
FROM    customers
JOIN    accounts
ON      account_customer = customer_id
WHERE   customer_last_name = 'Stellphlug'

połączenie najprawdopodobniej będzie szybkie: najpierw indeks na customer_last_name będzie użyty do filtrowania wszystkich Stellphlug (które są oczywiście niezbyt liczne), a następnie skanowania indeksu account_customer będzie wydany dla każdego Stellphlug, aby znaleźć swoje transakcje.

Pomimo tego, że mogą to być miliardy rekordów accounts i customers, tylko kilka naprawdę będzie musiało zostać zeskanowanych.


7
2018-04-12 17:07



ale trudno tego uniknąć. zaprojektuj swoją aplikację, aby tego typu zapytania nie były wykonywane zbyt często. - Andrey
Jeśli zdefiniowany jest indeks accounts(account_customer) większość RDBMSes użyłby tego indeksu, aby dowiedzieć się dokładnie, które wiersze customers baza danych musi zostać zeskanowana. - jemfinch
tak, ale i tak nie jest to tania operacja. możesz przechowywać sumę w niektórych polach i aktualizować każdą transakcję. - Andrey
@ Jafinch: nie, nie będą. Wymagałoby to zeskanowania całego indeksu tylko po to, by odfiltrować klientów, a następnie zeskanować indeks klienta w zagnieżdżonej pętli. ZA HASH JOIN byłby znacznie szybszy, więc to, co będzie używane, z wyjątkiem wszystkich głównych baz danych z wyjątkiem MySQL, które po prostu sprawi customers prowadzenie w pętli zagnieżdżonej (ponieważ ma mniejszy rozmiar) - Quassnoi


Połączenia wymagają dodatkowego przetwarzania, ponieważ muszą szukać więcej plików i więcej indeksów, aby "połączyć" dane razem. Jednak "bardzo duże zbiory danych" są względne. Jaka jest definicja dużych? W przypadku JOINs, myślę, że jest to odniesienie do dużego zestawu wyników, a nie całego zestawu danych.

Większość baz danych może bardzo szybko przetworzyć zapytanie, które wybiera 5 rekordów z podstawowej tabeli i łączy 5 rekordów z powiązanej tabeli dla każdego rekordu (zakładając, że poprawne indeksy są na miejscu). Tabele te mogą zawierać setki milionów rekordów, a nawet miliardy.

Gdy zestaw wyników zacznie się rozrastać, wszystko będzie zwalniać. Używając tego samego przykładu, jeśli tabela podstawowa da 100 000 rekordów, to trzeba będzie znaleźć 500 000 rekordów "złączonych". Po prostu wyciąga tyle danych z bazy danych z opóźnieniami dodawania.

Nie unikaj JOINów, tylko wiedz, że możesz potrzebować zoptymalizować / zdenormalizować, kiedy zbiory danych staną się "bardzo duże".


3
2018-04-12 17:45





Połączenia są uważane za siłę przeciwną do skalowalności, ponieważ są zwykle wąskim gardłem i nie mogą być łatwo dystrybuowane lub porównywane.


2
2018-04-12 17:09



Nie jestem pewien, czy to prawda. Wiem, że Teradata z pewnością jest w stanie dystrybuować połączenia pomiędzy Ampami. Oczywiście niektóre rodzaje połączeń mogą być trudniejsze / trudniejsze do pokonania niż inne. - Cade Roux
indeksy można podzielić na partycje w RDBMS, od mysql do Oracle. AFAIK, który skaluje (jest rozprowadzany i może być połączony równolegle). - Unreason


Odpowiednio zaprojektowane tabele zawierające odpowiednie oznaczenia i poprawnie napisane zapytania nie zawsze są wolne. Gdziekolwiek to słyszałeś:

Dlaczego sprzężenia są złe lub "wolne"

nie ma pojęcia, o czym mówią! Większość złącz będzie bardzo szybka. Jeśli musisz dołączyć do wielu wielu wierszy jednocześnie, możesz wziąć trafienie w porównaniu do tabeli zdenormalizowanej, ale to sięga do prawidłowo zaprojektowanych tabel, wiedzieć, kiedy denormalizować, a kiedy nie. w ciężkim systemie raportowania, rozbijaj dane w zdenormalizowanych tabelach raportów, a nawet twórz hurtownię danych. W transakcyjnym systemie ciężkim znormalizuj tabele.


2
2018-04-12 17:09





Joins are fast. Połączenia powinny być uważane za standardową praktykę z prawidłowo znormalizowanym schematem bazy danych. Połączenia umożliwiają znaczące powiązanie odmiennych grup danych. Nie bój się dołączenia.

Ograniczeniem jest to, że musisz zrozumieć normalizację, dołączanie i właściwe używanie indeksów.

Uważaj na przedwczesną optymalizację, ponieważ największym niepowodzeniem wszystkich projektów rozwojowych jest dotrzymanie terminu. Po ukończeniu projektu i zrozumieniu kompromisów, możesz złamać zasady, jeśli możesz to uzasadnić.

To prawda, że ​​wydajność łączenia zmniejsza się nieliniowo wraz ze wzrostem rozmiaru zestawu danych. Dlatego nie skaluje się tak dobrze, jak zapytania pojedynczej tabeli, ale wciąż jest skalowany.

Prawdą jest również, że ptak leci szybciej bez skrzydeł, ale tylko prosto w dół.


2
2018-04-12 18:02