Fajne optymalizacje SQL, które nie zależą od modelu kosztowego. Część 5

Fajne optymalizacje SQL, które nie zależą od modelu kosztowego. Część 1 Fajne optymalizacje SQL niezależne od modelu kosztowego. Część 2 Fajne optymalizacje SQL niezależne od modelu kosztowego. Część 3 Fajne optymalizacje SQL niezależne od modelu kosztowego. Część 4

10. Przepychanie predykatów

Ta optymalizacja nie jest tu do końca trafna, bo nie można powiedzieć, że w ogóle nie opiera się na modelu kosztowym. Ponieważ jednak nie przychodzi mi do głowy ani jeden powód, dla którego optymalizator nie powinien umieszczać predykatów w tabelach pochodnych, wymienię go tutaj wraz z resztą optymalizacji niekosztowych. Rozważ prośbę:

SELECT *
FROM (
  SELECT *
  FROM actor
) a
WHERE a.actor_id = 1;

Wyprowadzona tabela w tym zapytaniu nie ma sensu i należy ją wyeliminować poprzez zmniejszenie liczby poziomów zagnieżdżenia zapytania. Ale na razie to pomińmy. Możesz oczekiwać, że baza danych wykona następujące zapytanie zamiast powyższego:

SELECT *
FROM (
  SELECT *
  FROM actor
  WHERE actor_id = 1
) a;

A potem znowu może wyeliminuj żądanie zewnętrzne. Bardziej złożony przykład uzyskuje się za pomocą UNION :

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
) people
WHERE people.last_name = 'DAVIS';

Wynik tego zapytania:

FIRST_NAME  LAST_NAME  TYPE
----------------------------
JENNIFER    DAVIS      actor
SUSAN       DAVIS      actor
SUSAN       DAVIS      actor
JENNIFER    DAVIS      customer

Byłoby wspaniale, gdyby optymalizator bazy danych zamiast tego uruchomił zapytanie takie jak to:

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  WHERE last_name = 'DAVIS'
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
  WHERE last_name = 'DAVIS'
) people;

To znaczy w taki sposób, że wypycha predykat do tabeli pochodnej, a stamtąd do dwóch podzapytań UNION ALL , bo przecież mamy indeks zarówno w kolumnie ACTOR.LAST_NAME , jak i w kolumnie CUSTOMER.LAST_NAME . Ponownie, ta konwersja jest prawdopodobnie oparta na szacunkach kosztów w większości baz danych, ale nadal uważam, że jest to oczywiste, ponieważ w przypadku dowolnego algorytmu prawie zawsze lepiej jest zmniejszyć liczbę przetwarzanych krotek tak wcześnie, jak to możliwe. Jeśli znasz przypadek, w którym taka transformacja okazała się złym pomysłem, chętnie wysłucham Twoich komentarzy! Byłbym bardzo zainteresowany. Która z naszych baz danych może to zrobić? (I proszę, to takie proste i takie ważne, niech odpowiedź brzmi: wszystko)

DB2

Prosta tabela pochodna Tak

Explain Plan
--------------------------------------------------
ID | Operation         |               Rows | Cost
 1 | RETURN            |                    |    6
 2 |  FETCH ACTOR      |   1 of 1 (100.00%) |    6
 3 |   IXSCAN PK_ACTOR | 1 of 200 (   .50%) |    0
Predicate Information
 3 - START (Q1.ACTOR_ID = 1)
      STOP (Q1.ACTOR_ID = 1)

Tabela pochodna z UNION Również tak:

Explain Plan
-----------------------------------------------------------------
ID | Operation                        |               Rows | Cost
 1 | RETURN                           |                    |   20
 2 |  UNION                           |             2 of 1 |   20
 3 |   FETCH CUSTOMER                 |   1 of 1 (100.00%) |   13
 4 |    IXSCAN IDX_CUSTOMER_LAST_NAME | 1 of 599 (   .17%) |    6
 5 |   FETCH ACTOR                    |   1 of 1 (100.00%) |    6
 6 |    IXSCAN IDX_ACTOR_LAST_NAME    | 1 of 200 (   .50%) |    0
Predicate Information
 4 - START (Q1.LAST_NAME = 'DAVIS')
      STOP (Q1.LAST_NAME = 'DAVIS')
 6 - START (Q3.LAST_NAME = 'DAVIS')
      STOP (Q3.LAST_NAME = 'DAVIS')

Również w obu przypadkach wyprowadzona tabela (widok) została wyłączona z planu, gdyż w rzeczywistości nie była potrzebna.

MySQL'a

Prosta tabela pochodna Tak

ID  TABLE  TYPE   KEY      REF    EXTRA
---------------------------------------
1   actor  const  PRIMARY  const

Używany jest zwykły dostęp do klucza podstawowego poprzez stałą wartość. Tabela pochodna z UNION. Ups, nie.

ID  SELECT_TYPE  TABLE       TYPE  KEY          REF    ROWS  EXTRA
------------------------------------------------------------------
1   PRIMARY        ref   	const  10
2   DERIVED      actor       ALL                       200
3   UNION        customer    ALL                       599

Ręczna konwersja skutkuje w planie:

ID  SELECT_TYPE  TABLE       TYPE  KEY                  REF    ROWS  EXTRA
--------------------------------------------------------------------------
1   PRIMARY        ALL                               5
2   DERIVED      actor       ref   idx_actor_last_name  const  3
3   UNION        customer    ref   idx_last_name        const  1

Jest to poważny problem podczas używania złożonych zapytań zagnieżdżonych w MySQL!

Wyrocznia

Prosta tabela pochodna Tak, to działa.

---------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR    |      1 |      1 |      1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ACTOR |      1 |      1 |      1 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ACTOR"."ACTOR_ID"=1)

Zmniejszono także liczbę poziomów zagnieżdżenia. Tabela pochodna z UNION działa również:

---------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | E-Rows |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |        |
|   1 |  VIEW                                 |                        |      4 |
|   2 |   UNION-ALL                           |                        |        |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR                  |      3 |
|*  4 |     INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME    |      3 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER               |      1 |
|*  6 |     INDEX RANGE SCAN                  | IDX_CUSTOMER_LAST_NAME |      1 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("LAST_NAME"='DAVIS')
   6 - access("LAST_NAME"='DAVIS')

Jednak bez zmniejszania liczby poziomów zagnieżdżenia. Id=1 „Widok” pokazuje, że tabela pochodna nadal tam jest. W tym przypadku nie jest to duży problem, a może jedynie niewielki dodatkowy koszt.

PostgreSQL

Prosta tabela pochodna Tak, to działa:

QUERY PLAN
----------------------------------------------------
Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)
  Filter: (actor_id = 1)

Należy jednak pamiętać, że PostgreSQL czasami nawet nie używa klucza podstawowego do wyszukiwania pojedynczego wiersza, ale zamiast tego skanuje całą tabelę. W tym przypadku 200 wierszy × 25 bajtów na wiersz („szerokość”) mieści się w jednym bloku, więc po co zawracać sobie głowę odczytami indeksów poza generowaniem niepotrzebnych operacji we/wy w celu uzyskania dostępu do tak małej tabeli? Tabela pochodna z UNION Tak, to też działa:

QUERY PLAN
-----------------------------------------------------------------------------------
Append  (cost=0.00..12.83 rows=4 width=45)
  ->  Seq Scan on actor  (cost=0.00..4.50 rows=3 width=45)
        Filter: ((last_name)::text = 'DAVIS'::text)
  ->  Index Scan using idx_last_name on customer  (cost=0.28..8.29 rows=1 width=45)
        Index Cond: ((last_name)::text = 'DAVIS'::text)

Ponownie nie jest używany indeks w kolumnie ACTOR.LAST_NAME , ale używany jest indeks w kolumnie CUSTOMER.LAST_NAME , ponieważ tabela CUSTOMER jest znacznie większa.

Serwer SQL

Prosta tabela pochodna Tak, to działa

|--Nested Loops(Inner Join)
     |--Index Seek(SEEK:([actor_id]=(1)))
     |--RID Lookup(OBJECT:([actor]))

Działa również tabela pochodna z UNION .

|--Concatenation
     |--Compute Scalar(DEFINE:([Expr1003]='actor'))
     |    |--Nested Loops(Inner Join)
     |         |--Index Seek(SEEK:([actor].[last_name]='DAVIS'))
     |         |--RID Lookup(OBJECT:([actor]))
     |--Compute Scalar(DEFINE:([Expr1007]='customer'))
          |--Nested Loops(Inner Join)
               |--Index Seek(SEEK:([customer].[last_name]='DAVIS'))
               |--RID Lookup(OBJECT:([customer]))

Streszczenie

Moje nadzieje się nie spełniły. MySQL 8.0.2 nie obsługuje jeszcze w pełni tej prostej optymalizacji. Wszyscy inni jednak ją wspierają.

Wniosek

Przedstawiona tutaj lista nie jest kompletna. Istnieje wiele innych prostych transformacji SQL, które nie są (lub nie powinny być) trudne do wdrożenia w przypadku baz danych, nawet przed zaangażowaniem optymalizatora kosztów. Eliminują niepotrzebną, dodatkową pracę [dla bazy danych] ( w przeciwieństwie do niepotrzebnej, wymaganej pracy, o której już pisałem ). Są to ważne narzędzia umożliwiające:

1. Głupie błędy [programisty] nie miały wpływu na wydajność. Błędy są nieuniknione, a gdy projekt się rozrasta, a zapytania SQL stają się coraz bardziej złożone, błędy te mogą się kumulować, miejmy nadzieję, że bez żadnego skutku.


2. Zapewnij możliwość ponownego wykorzystania złożonych bloków, takich jak widoki i funkcje tabelowe, które można osadzić w nadrzędnych zapytaniach SQL, przekształcić lub częściowo usunąć lub zapisać na nowo.

Możliwości te są krytyczne dla punktu 2. Bez nich bardzo trudno byłoby stworzyć 4000-wierszowe zapytania SQL o normalnej wydajności w oparciu o bibliotekę komponentów SQL wielokrotnego użytku. Ku wielkiemu rozczarowaniu użytkowników PostgreSQL i MySQL, te dwie popularne bazy danych typu open source mają jeszcze przed sobą długą drogę w porównaniu z ich komercyjnymi konkurentami DB2, Oracle i SQL Server, z których DB2 radził sobie najlepiej, a Oracle i SQL Server z grubsza równymi sobie. szyja, trochę z tyłu.