Co jest bardziej wydajne, klauzula where lub łączenie z milionami tabel wierszy?

17

Prowadzimy stronę internetową, która ma 250 mln wierszy w jednej tabeli, aw drugiej tabeli, do której dołączamy, w przypadku większości zapytań ma nieco mniej niż 15 mln wierszy.

Przykładowe struktury:

MasterTable (Id, UserId, Created, Updated...) -- 15MM Rows
DetailsTable (Id, MasterId, SomeColumn...) -- 250MM Rows
UserTable (Id, Role, Created, UserName...) -- 12K Rows

Regularnie musimy wykonać kilka zapytań w stosunku do wszystkich tych tabel. Jednym z nich jest zbieranie statystyk dla darmowych użytkowników (~ 10 000 darmowych użytkowników).

Select Count(1) from DetailsTable dt 
join MasterTable mt on mt.Id = dt.MasterId 
join UserTable ut on ut.Id = mt.UserId 
where ut.Role is null and mt.created between @date1 and @date2

Problem w tym, że to zapytanie będzie czasem działało bardzo cholernie z powodu faktu, że łączenia odbywają się na długo przed tym, gdzie.

Czy w takim przypadku rozsądniej byłoby użyć gdzieś zamiast złączeń, a może where column in(...)?

join best-practices

— Jeremy Boyd
źródło

1

Jaka baza danych i wersja?

— Leigh Riffel

2

próbowałeś na dwa sposoby?

— gbn

Gdyby to był Oracle, stworzyłbym indeks oparty na funkcjach dla UserTable na NVL2 (Rola, NULL, ID), ale wygląda to na inną bazę danych.

— Leigh Riffel

20

W przypadku nowoczesnych RDBMS nie ma różnicy między „jawnym JOIN” a „JOIN-in-the-WHERE” (jeśli wszystkie JOINS są INNER) pod względem wydajności i planu zapytań.

Wyraźna składnia JOIN jest jaśniejsza i mniej dwuznaczna (patrz linki poniżej)

Teraz JOIN-before-WHERE to przetwarzanie logiczne, a nie przetwarzanie rzeczywiste, a nowoczesne optymalizatory są wystarczająco sprytne, aby to zrealizować.

Twoim problemem tutaj jest najprawdopodobniej indeksowanie.

Pokaż nam wszystkie indeksy i klucze w tych tabelach. I plany zapytań

Uwaga: to pytanie byłoby już blisko StackOverflow za to, że jest duplikatem ... COUNT (1) vs COUNT (*) to kolejny mit, który został zniszczony.

— gbn
źródło

2

To nie zawsze jest prawdą, że nie ma różnicy pomiędzy joini whereklauzula. Cały czas optymalizuję długo działające zapytania, a czasem zapytania korzystające z whereklauzuli działają lepiej niż te, które są używane joinnawet 70 razy. Gdyby to było takie proste, życie byłoby wszystkimi tęczami i jednorożcami. I nie chodzi tu o jakiś starożytny, niejasny silnik - teraz patrzę na 70-krotną przewagę whereklauzuli w SQL 2012.

— ajeh

Co więcej, często obserwuję dokładnie te same plany z obu podejść i izoluję zapytania, które działają dokładnie tak samo, ale kiedy wherezapytanie klauzulowe działa w dużej partii, powinno być częścią tego zadania , znacznie przewyższa joinzapytanie. Zapytania SQL nie wykonują się w próżni - wpływa na nich reszta obciążenia serwera, a często wherezapytania klauzulowe wypadają całkiem dobrze, co jest uciążliwe, ponieważ joinskładnia jest rzeczywiście o wiele czystsza.

— ajeh

3

@ajeh: Sugerowałbym, że twoje doświadczenie jest bardzo nietypowe. Masz większe problemy z zapytaniami, jeśli masz różnice x70: to takie proste

— gbn

5

Musisz całkowicie przefiltrować zapytanie

Spróbuj wykonać klauzule WHERE wcześniej, a JOIN później

Select Count(1) from DetailsTable dt
join (Select UserId,Id FROM MasterTable where
created between @date1 and @date2) mt on mt.Id = dt.MasterId 
join (Select Id FROM UserTable WHERE Role is NULL) ut
on ut.Id = mt.UserId;

Nawet jeśli uruchomisz plan EXPLAIN dla tego refaktoryzowanego zapytania i wygląda to gorzej niż oryginał, wypróbuj go mimo to. Tabele tymczasowe utworzone wewnętrznie będą wykonywać połączenia kartezjańskie, ale te tabele są mniejsze do pracy.

Ten pomysł pochodzi z tego filmu na YouTube .

Wypróbowałem zasady z filmu w bardzo złożonym pytaniu w StackOverflow i dostałem nagrodę za 200 punktów.

@gbn wspomniał o upewnieniu się, że masz odpowiednie indeksy. W takim przypadku zindeksuj utworzoną kolumnę w MasterTable.

Spróbuj !!!

AKTUALIZACJA 2011-06-24 22:31 EDT

Powinieneś uruchomić następujące zapytania:

SELECT COUNT(1) AllRoles FROM UserTable;
SELECT COUNT(1) NullRoles FROM UserTable WHERE Role is NULL;

Jeśli NullRoles X 20 <AllRoles (innymi słowy, jeśli NullRoles jest mniejszy niż 5% wierszy tabeli), należy utworzyć nieunikalny indeks roli w UserTable. W przeciwnym razie wystarczyłaby pełna tabela UserTable, ponieważ Optymalizator zapytań może wykluczyć użycie indeksu.

AKTUALIZACJA 2011-06-25 12:40 EDT

Ponieważ jestem DBA MySQL, moja metoda robienia rzeczy wymaga nieufności MySQL Query Optimizer poprzez pozytywny pesymizm i bycie konserwatywnym. Dlatego spróbuję ponownie przeformułować zapytanie lub utworzyć niezbędne indeksy pokrywające, aby wyprzedzić ukryte złe nawyki Optymalizatora zapytań MySQL. Odpowiedź @ gbn wydaje się bardziej kompletna, ponieważ SQL Server może mieć więcej „zdrowego rozsądku” podczas oceny zapytań.

— RolandoMySQLDBA
źródło

0

Mieliśmy tabelę [Szczegóły] około 75 milionów wierszy; tabela [Master] około 400 000 wierszy i powiązana tabela [Item], która miała 7 wierszy - zawsze i na zawsze. Przechowywał mały zestaw „numerów artykułów” (1-7) i modelował formę papierową, z której miliony drukowano i dystrybuowano co miesiąc. Najszybszym zapytaniem było to, o którym najprawdopodobniej nie pomyślałeś w pierwszej kolejności, używając połączenia kartezjańskiego. IIRC, to było coś takiego:

SELECT m.order_id, i.line_nr, d.Item_amt
FROM Master m, Item i 
INNER JOIN Detail d ON m.order_id = d.order_id

Mimo że istnieje logiczne połączenie „id” między [Przedmiotem] a [Szczegółem], DOŁĄCZ DO SKRZYDŁA działał lepiej niż DOŁĄCZ DO WEWNĘTRZNEGO.

RDBMS to Teradata z technologią MPP, a IDR to schemat indeksowania. Tabela 7 wierszy nie miała indeksu, ponieważ SKANOWANIE TABELI zawsze działało najlepiej.

— Timothy Oleary
źródło