Co jest szybsze, wybierz DISTINCT lub GROUP BY w MySQL?


273

Jeśli mam stolik

CREATE TABLE users (
  id int(10) unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  profession varchar(255) NOT NULL,
  employer varchar(255) NOT NULL,
  PRIMARY KEY  (id)
)

i chcę uzyskać wszystkie unikalne wartości professionpola, co byłoby szybsze (lub zalecane):

SELECT DISTINCT u.profession FROM users u

lub

SELECT u.profession FROM users u GROUP BY u.profession

?


2
Możesz samemu sprawdzić się, zadając pytanie. Irytujące jest prawie niemożliwe zbudowanie scenariusza, w którym DISTINCT przewyższa GROUP BY - co jest denerwujące, ponieważ najwyraźniej nie jest to celem GROUP BY. Jednak GROUP BY może dawać mylące wyniki, które moim zdaniem są wystarczającym powodem, aby tego uniknąć.
Strawberry

Jest inny duplikat z inną odpowiedzią. zobacz MySql - Distinct vs Group By <<< mówi, że GROUP BY jest lepszy
kolunar

Sprawdź tutaj, czy chcesz zmierzyć różnicę czasu między DISTINCT a GROUP BY, uruchamiając zapytanie.
kolunar

Odpowiedzi:


258

Są one zasadniczo sobie równe (w rzeczywistości tak właśnie niektóre bazy danych implementują DISTINCTpod maską).

Jeśli jeden z nich jest szybszy, tak będzie DISTINCT. Jest tak, ponieważ chociaż oba są takie same, optymalizator zapytań musiałby wychwycić fakt, że GROUP BYnie korzysta się z żadnych członków grupy, tylko ich klucze. DISTINCTwyjaśnia to wyraźnie, abyś mógł uciec z nieco głupszym optymalizatorem.

W razie wątpliwości przetestuj!


76
DISTINCT będzie szybszy tylko wtedy, gdy NIE masz indeksu (ponieważ się nie sortuje). Kiedy masz indeks i jest on używany, są to synonimy.
Quassnoi

10
Definicja DISTINCTi GROUP BYróżni się tym, DISTINCTże nie musi sortować danych wyjściowych, a GROUP BYdomyślnie tak. Jednak w MySQL nawet DISTINCT+ ORDER BYmoże być jeszcze szybszy niż GROUP BYze względu na dodatkowe wskazówki dla optymalizatora wyjaśnione przez SquareCog.
rustyx

1
DISTINCT jest znacznie szybszy przy dużych ilościach danych.
Pankaj Wanjari,

7
Przetestowałem to i stwierdziłem, że w indeksowanej kolumnie mysql grupowanie według było około 6 razy wolniejsze niż wyraźne przy dość skomplikowanym zapytaniu. Wystarczy dodać to jako punkt danych. Około 100 tys. Rzędów. Przetestuj to i przekonaj się sam.
Lizardx,

zobacz MySql - Distinct vs Group By <<< mówi, że GROUP BY jest lepszy
kolunar

100

Jeśli masz indeks profession, te dwa są synonimami.

Jeśli nie, użyj DISTINCT.

GROUP BYw MySQLsortowaniu wyników. Możesz nawet zrobić:

SELECT u.profession FROM users u GROUP BY u.profession DESC

i uporządkuj swoje zawody DESC.

DISTINCTtworzy tabelę tymczasową i używa jej do przechowywania duplikatów. GROUP BYrobi to samo, ale później sortuje wyraźne wyniki.

Więc

SELECT DISTINCT u.profession FROM users u

jest szybszy, jeśli nie masz indeksu profession.


6
Możesz dodać ORDER BY NULLdo, GROUP BYaby uniknąć sortowania.
Ariel,

Nadal wolniej, nawet przy grupowaniu według wartości null
Thanh Trung

@ThanhTrung: co jest wolniejsze niż co?
Quassnoi

@Quassnoi groupby wolniej niż wyraźnie, nawet jeśli unika sortowania
Thanh Trung

Uwaga: Kwalifikatory zamówień dla GROUP BY zostały uznane za przestarzałe w MySQL 8.
Matthew Lenz

18

Wszystkie powyższe odpowiedzi są poprawne, w przypadku DISTINCT w jednej kolumnie vs GROUP BY w jednej kolumnie. Każdy silnik db ma własną implementację i optymalizacje, a jeśli zależy ci na bardzo niewielkiej różnicy (w większości przypadków), musisz przetestować konkretny serwer ORAZ określoną wersję! Ponieważ implementacje mogą ulec zmianie ...

ALE, jeśli wybierzesz więcej niż jedną kolumnę w zapytaniu, DISTINCT będzie zasadniczo inny! Ponieważ w tym przypadku porówna WSZYSTKIE kolumny wszystkich wierszy, zamiast tylko jednej kolumny.

Więc jeśli masz coś takiego:

// This will NOT return unique by [id], but unique by (id,name)
SELECT DISTINCT id, name FROM some_query_with_joins

// This will select unique by [id].
SELECT id, name FROM some_query_with_joins GROUP BY id

Powszechnym błędem jest myślenie, że słowo kluczowe DISTINCT rozróżnia wiersze według pierwszej określonej kolumny, ale słowo kluczowe DISTINCT jest w ten sposób ogólnym słowem kluczowym.

Dlatego ludzie muszą uważać, aby nie traktować powyższych odpowiedzi jako poprawnych we wszystkich przypadkach ... Możesz się zdezorientować i uzyskać błędne wyniki, podczas gdy wszystko, czego chciałeś, to optymalizacja!


3
Choć kwestia ta jest o MySQL należy zauważyć, że drugie zapytanie będzie działać tylko w MySQL. Niemal co drugi DBMS odrzuci drugą instrukcję, ponieważ jest to nieprawidłowe użycie operatora GROUP BY.
a_horse_w_no_name

Cóż, „prawie” jest problematyczną definicją :-) Byłoby o wiele bardziej pomocne, jeśli podałeś konkretny DBMS, który przetestowałeś, aby zobaczyć, że generuje błąd dla tej instrukcji.
daniel.gindi

3
Postgres, Oracle, Firebird, DB2, SQL Server na początek. MySQL: sqlfiddle.com/#!2/6897c/1 Postgres: sqlfiddle.com/#!12/6897c/1 Oracle: sqlfiddle.com/#!12/6897c/1 SQL Server: sqlfiddle.com/#!6/ 6897c / 1
a_horse_with_no_name

17

Wybierz najprostsze i najkrótsze, jeśli możesz - DISTINCT wydaje się być tym, czego szukasz tylko dlatego, że da Ci DOKŁADNIE odpowiedź, której potrzebujesz i tylko to!


7

Grupowanie według jest droższe niż Wyraźny, ponieważ Grupowanie według sortuje wynik, podczas gdy odrębny go unika. Ale jeśli chcesz utworzyć grupę według wydajności, uzyskuj ten sam wynik, co odrębny, podaj porządek przez zero .

SELECT DISTINCT u.profession FROM users u

jest równe

SELECT u.profession FROM users u GROUP BY u.profession order by null

jest równySELECT profession FROM users GROUP BY profession

6

wyraźne mogą być wolniejsze niż grupowanie w niektórych przypadkach w postgresie (nie wiem o innych dbs).

testowany przykład:

postgres=# select count(*) from (select distinct i from g) a;

count 

10001
(1 row)

Time: 1563,109 ms

postgres=# select count(*) from (select i from g group by i) a;

count
10001
(1 row)

Time: 594,481 ms

http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks_I

więc uważaj ... :)


5

Wygląda na to, że zapytania nie są dokładnie takie same. Przynajmniej dla MySQL.

Porównać:

  1. opisz wybierz odrębną nazwę produktu z northwind.products
  2. opisz wybraną nazwę produktu z grupy northwind.products według nazwy produktu

Drugie zapytanie zawiera dodatkowo „Korzystanie z sortowania plików” w Extra.


1
Są takie same pod względem tego, co otrzymują, a nie pod względem sposobu ich uzyskania. Idealny optymalizator wykonałby je w ten sam sposób, ale optymalizator MySQL nie jest idealny. Na podstawie twoich dowodów mogłoby się wydawać, że DISTINCT poszedłby szybciej - O (n) vs O (n * log n).
SquareCog

Zatem „korzystanie z plików” jest w zasadzie czymś złym?
vava

W tym przypadku jest tak, ponieważ nie musisz sortować (zrobiłbyś to, gdybyś potrzebował grup). MySQL sortuje, aby umieścić te same wpisy razem, a następnie uzyskać grupy, skanując posortowany plik. Potrzebujesz tylko różnic, więc po prostu haszuj klucze podczas skanowania pojedynczej tabeli.
SquareCog,

1
Dodaj ORDER BY NULLdo GROUP BYwersji i będą takie same.
Ariel,

3

W MySQLGroup By” wykorzystuje dodatkowy krok: filesort. Zdaję sobie sprawę, że DISTINCTjest szybszy GROUP BYi to była niespodzianka.


3

Po ciężkich testach doszliśmy do wniosku, że GROUP BY jest szybszy

WYBIERZ sql_no_cache opnamegroep_intern FROM telwerken WHERE opnemergroepIN (7,8,9,10,11,12,13) ​​grupa według opnamegroep_intern

635 suma 0,0944 sekundy Weergave van records 0 - 29 (635 suma, zapytanie duurde 0,0484 s)

WYBIERZ sql_no_cache odrębny (opnamegroep_intern) OD telwerken GDZIEopnemergroep (7,8,9,10,11,12,13)

635 suma 0,2117 sekund (prawie 100% wolniej) Weergave van rekordy 0 - 29 (635 suma, zapytanie duurde 0,3468 s)


2

(bardziej funkcjonalna nuta)

Są przypadki, w których musisz użyć GROUP BY, na przykład, jeśli chcesz uzyskać liczbę pracowników na pracodawcę:

SELECT u.employer, COUNT(u.id) AS "total employees" FROM users u GROUP BY u.employer

W takim scenariuszu DISTINCT u.employernie działa dobrze. Być może istnieje sposób, ale po prostu go nie znam. (Jeśli ktoś wie, jak wykonać takie zapytanie za pomocą DISTINCT, proszę dodać notatkę!)


2

Oto proste podejście, które wydrukuje 2 różne czasy, które upłynęły dla każdego zapytania.

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();
SELECT DISTINCT u.profession FROM users u; --Query with DISTINCT
SET @t2 = GETDATE();
PRINT 'Elapsed time (ms): ' + CAST(DATEDIFF(millisecond, @t1, @t2) AS varchar);

SET @t1 = GETDATE();
SELECT u.profession FROM users u GROUP BY u.profession; --Query with GROUP BY
SET @t2 = GETDATE();
PRINT 'Elapsed time (ms): ' + CAST(DATEDIFF(millisecond, @t1, @t2) AS varchar);

LUB spróbuj USTAWIĆ CZAS STATYSTYKI (Transact-SQL)

SET STATISTICS TIME ON;
SELECT DISTINCT u.profession FROM users u; --Query with DISTINCT
SELECT u.profession FROM users u GROUP BY u.profession; --Query with GROUP BY
SET STATISTICS TIME OFF;

Po prostu wyświetla liczbę milisekund wymaganych do parsowania, kompilacji i wykonania każdej instrukcji, jak poniżej:

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

1

To nie jest reguła

Dla każdego zapytania .... spróbuj osobno oddzielić, a następnie pogrupuj według ... porównaj czas na zakończenie każdego zapytania i użyj szybszego ....

W moim projekcie czasami używam grupowania według i innych


0

Jeśli nie musisz wykonywać żadnych funkcji grupowych (suma, średnia itp. W przypadku, gdy chcesz dodać dane liczbowe do tabeli), użyj SELECT DISTINCT. Podejrzewam, że jest szybszy, ale nie mam na to żadnych dowodów.

W każdym razie, jeśli martwisz się szybkością, utwórz indeks w kolumnie.


0

SELECT DISTINCT zawsze będzie taki sam lub szybszy niż GROUP BY. W niektórych systemach (np. Oracle) może być zoptymalizowany tak, aby był taki sam jak DISTINCT dla większości zapytań. W innych (np. SQL Server) może być znacznie szybszy.


0

Jeśli problem na to pozwala, spróbuj z EXISTS, ponieważ jest zoptymalizowany, aby skończył się natychmiast po znalezieniu wyniku (I nie buforuj żadnej odpowiedzi), więc jeśli próbujesz po prostu znormalizować dane dla klauzuli WHERE takiej jak ta

SELECT FROM SOMETHING S WHERE S.ID IN ( SELECT DISTINCT DCR.SOMETHING_ID FROM DIFF_CARDINALITY_RELATIONSHIP DCR ) -- to keep same cardinality

Szybsza odpowiedź to:

SELECT FROM SOMETHING S WHERE EXISTS ( SELECT 1 FROM DIFF_CARDINALITY_RELATIONSHIP DCR WHERE DCR.SOMETHING_ID = S.ID )

Nie zawsze jest to możliwe, ale gdy będzie dostępne, zobaczysz szybszą odpowiedź.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.