Ogromna różnica w wydajności podczas korzystania z grupowania według i odrębnych

Question 1

Przeprowadzam testy na HSQLDBserwerze z tabelą zawierającą 500 000 wpisów. Tabela nie zawiera indeksów. Istnieje 5000 różnych kluczy biznesowych. Potrzebuję ich listy. Oczywiście zacząłem od DISTINCTzapytania:

SELECT DISTINCT business_key FROM memory WHERE
   concept <> 'case' or 
   attrib <> 'status' or 
   value <> 'closed'

Zajmuje to około 90 sekund !!!

Następnie spróbowałem użyć GROUP BY:

SELECT business_key FROM memory WHERE
       concept <> 'case' or 
       attrib <> 'status' or 
       value <> 'closed'
GROUP BY business_key

I zajmuje to 1 sekundę !!!

Próbując ustalić różnicę, uruchomiłem, EXLAIN PLAN FORale wydaje się, że podaje te same informacje dla obu zapytań.

EXLAIN PLAN FOR DISTINCT ...

isAggregated=[false]
columns=[
  COLUMN: PUBLIC.MEMORY.BUSINESS_KEY
]
[range variable 1
  join type=INNER
  table=MEMORY
  alias=M
  access=FULL SCAN
  condition = [    index=SYS_IDX_SYS_PK_10057_10058
    other condition=[
    OR arg_left=[
     OR arg_left=[
      NOT_EQUAL arg_left=[
       COLUMN: PUBLIC.MEMORY.CONCEPT] arg_right=[
       VALUE = case, TYPE = CHARACTER]] arg_right=[
      NOT_EQUAL arg_left=[
       COLUMN: PUBLIC.MEMORY.ATTRIB] arg_right=[
       VALUE = status, TYPE = CHARACTER]]] arg_right=[
     NOT_EQUAL arg_left=[
      COLUMN: PUBLIC.MEMORY.VALUE] arg_right=[
      VALUE = closed, TYPE = CHARACTER]]]
  ]
]]
PARAMETERS=[]
SUBQUERIES[]
Object References
PUBLIC.MEMORY
PUBLIC.MEMORY.CONCEPT
PUBLIC.MEMORY.ATTRIB
PUBLIC.MEMORY.VALUE
PUBLIC.MEMORY.BUSINESS_KEY
Read Locks
PUBLIC.MEMORY
WriteLocks

EXLAIN PLAN FOR SELECT ... GROUP BY ...

isDistinctSelect=[false]
isGrouped=[true]
isAggregated=[false]
columns=[
  COLUMN: PUBLIC.MEMORY.BUSINESS_KEY
]
[range variable 1
  join type=INNER
  table=MEMORY
  alias=M
  access=FULL SCAN
  condition = [    index=SYS_IDX_SYS_PK_10057_10058
    other condition=[
    OR arg_left=[
     OR arg_left=[
      NOT_EQUAL arg_left=[
       COLUMN: PUBLIC.MEMORY.CONCEPT] arg_right=[
       VALUE = case, TYPE = CHARACTER]] arg_right=[
      NOT_EQUAL arg_left=[
       COLUMN: PUBLIC.MEMORY.ATTRIB] arg_right=[
       VALUE = status, TYPE = CHARACTER]]] arg_right=[
     NOT_EQUAL arg_left=[
      COLUMN: PUBLIC.MEMORY.VALUE] arg_right=[
      VALUE = closed, TYPE = CHARACTER]]]
  ]
]]
groupColumns=[
COLUMN: PUBLIC.MEMORY.BUSINESS_KEY]
PARAMETERS=[]
SUBQUERIES[]
Object References
PUBLIC.MEMORY
PUBLIC.MEMORY.CONCEPT
PUBLIC.MEMORY.ATTRIB
PUBLIC.MEMORY.VALUE
PUBLIC.MEMORY.BUSINESS_KEY
Read Locks
PUBLIC.MEMORY
WriteLocks

EDYCJA : Zrobiłem dodatkowe testy. Dzięki 500 000 rekordów HSQLDBze wszystkimi odrębnymi kluczami biznesowymi wydajność DISTINCTjest teraz lepsza - 3 sekundy, w porównaniu z tym, GROUP BYktóre zajęło około 9 sekund.

W MySQLobu zapytaniach wykonaj to samo:

MySQL: 500 000 wierszy - 5 000 różnych kluczy biznesowych: Oba zapytania: 0,5 sekundy MySQL: 500 000 wierszy - wszystkie różne klucze biznesowe: SELECT DISTINCT ...- 11 sekund SELECT ... GROUP BY business_key- 13 sekund

Więc problem dotyczy tylko HSQLDB.

Będę bardzo wdzięczny, jeśli ktoś może wyjaśnić, dlaczego istnieje tak drastyczna różnica.

Question 2

Te dwa zapytania wyrażają to samo pytanie. Najwyraźniej optymalizator zapytań wybiera dwa różne plany wykonania. Domyślam się, że distinctpodejście jest wykonywane w następujący sposób:

Skopiuj wszystkie business_keywartości do tabeli tymczasowej
Sortuj tabelę tymczasową
Przeskanuj tabelę tymczasową, zwracając każdy element inny niż poprzedni

group byMożna wykonać następująco:

Przeskanuj całą tabelę, przechowując każdą wartość business keyw tablicy hashy
Zwróć klucze tablicy hashy

Pierwsza metoda jest optymalizowana pod kątem wykorzystania pamięci: nadal działałaby dość dobrze, gdyby część tabeli tymczasowej musiała zostać wymieniona. Druga metoda jest zoptymalizowana pod kątem szybkości, ale potencjalnie wymaga dużej ilości pamięci, jeśli istnieje wiele różnych kluczy.

Ponieważ masz wystarczającą ilość pamięci lub kilka różnych kluczy, druga metoda jest skuteczniejsza od pierwszej. Nie jest niczym niezwykłym, że między dwoma planami wykonania różnice w wydajności wynoszą 10 lub nawet 100.