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

Pięć prostych optymalizacji, które można wdrożyć wyłącznie w oparciu o metadane (czyli ograniczenia) i samo zapytanie Oferujemy Państwu adaptację artykułu Lukasa Edera, przeznaczoną dla osób posiadających ogólną wiedzę na temat baz danych i SQL, a także pewne praktyczne doświadczenie z DBMS . Optymalizacja kosztów to właściwie standardowy sposób optymalizacji zapytań SQL w nowoczesnych bazach danych. Dlatego tak trudno jest ręcznie napisać w 3GL (językach programowania trzeciej generacji) złożony algorytm , którego wydajność przekroczyłaby dynamicznie wyliczany plan wykonania wygenerowany przez nowoczesny optymalizator. Dziś nie będziemy omawiać optymalizacji kosztów, czyli optymalizacji w oparciu o model kosztowy bazy danych. Przyjrzymy się znacznie prostszym optymalizacjom. Takie, które można wdrożyć wyłącznie w oparciu o metadane (czyli ograniczenia) i samo żądanie. Zwykle ich implementacja dla bazy danych nie jest dwumianem Newtona, ponieważ w tym przypadku jakakolwiek optymalizacja doprowadzi do lepszego planu wykonania, niezależnie od obecności indeksów, woluminów danych i skośności rozkładu danych. „To nie jest dwumian Newtona” nie oznacza, jak łatwo jest wdrożyć optymalizację, ale czy należy to zrobić. Optymalizacje te eliminują niepotrzebną, dodatkową pracę [dla bazy danych] ( w przeciwieństwie do niepotrzebnej, wymaganej pracy, o której już pisałem ).

Do czego służą te optymalizacje?

Większość z nich służy do:

• poprawki błędów w zapytaniach;
• umożliwiając ponowne wykorzystanie widoków bez faktycznego wykonywania przez bazę danych logiki widoku.

W pierwszym przypadku można by powiedzieć: „No i co z tego, po prostu napraw to głupie zapytanie SQL”. Ale niech pierwszy rzuci we mnie kamieniem ten, kto nigdy się nie pomylił. Drugi przypadek jest szczególnie interesujący: daje nam możliwość tworzenia złożonych bibliotek widoków i funkcji tabelarycznych, które można ponownie wykorzystać na wielu warstwach.

Wykorzystane bazy danych

W tym artykule porównamy 10 optymalizacji SQL w pięciu najczęściej używanych systemach DBMS ( według rankingów baz danych ):

• Oracle 12.2;
• MySQL 8.0.2;
• SQL Server 2014;
• PostgreSQL 9.6;
• DB2 LUW 10.5.

Inna ocena niemal to potwierdza. Jak zwykle w tym artykule będę przeszukiwał bazę danych Sakila . Oto lista dziesięciu typów optymalizacji:

1. zamknięcie przechodnie;
2. niemożliwe predykaty i niepotrzebne wywołania tabel;
3. eliminacja DOŁĄCZ;
4. eliminacja „bezsensownych” predykatów;
5. rzuty w podzapytaniach EXISTS;
6. łączenie predykatów;
7. prawdopodobnie puste zbiory;
8. KONTROLA ograniczeń;
9. niepotrzebne połączenia odruchowe;
10. Predykaty pushdown

Dziś omówimy s. 1-3, w drugiej części - 4 i 5, a w części 3 - 6-10.

1. Domknięcie przechodnie

Zacznijmy od czegoś prostszego: domknięcia przechodniego . Jest to trywialna koncepcja, która ma zastosowanie do wielu operacji matematycznych, takich jak operator równości. Można to sformułować w tym przypadku następująco: jeśli A = B i B = C, to A = C.

Nie jest to trudne, prawda? Ma to jednak pewne interesujące implikacje dla optymalizatorów SQL. Spójrzmy na przykład. Wyodrębnijmy wszystkie filmy z ACTOR_ID = 1:

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE a.actor_id = 1;

Wynik jest następujący:

FIRST_NAME      LAST_NAME  FILM_ID
PENELOPE        GUINESS    1
PENELOPE        GUINESS    23
PENELOPE        GUINESS    25
PENELOPE        GUINESS    106
PENELOPE        GUINESS    140
PENELOPE        GUINESS    166
...

Przyjrzyjmy się teraz planowi wykonania tego zapytania w przypadku Oracle DBMS:

--------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |       |
|   1 |  NESTED LOOPS                |               |    19 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACTOR         |     1 |
|*  3 |    INDEX UNIQUE SCAN         | PK_ACTOR      |     1 |
|*  4 |   INDEX RANGE SCAN           | PK_FILM_ACTOR |    19 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."ACTOR_ID"=1)
   4 - access("FA"."ACTOR_ID"=1)

Sekcja dotycząca predykatów jest tutaj szczególnie interesująca. Predykat ACTOR_ID = 1, ze względu na domknięcie przechodnie, dotyczy zarówno tabeli ACTOR, jak i tabeli FILM_ACTOR. Jeśli:

• A.ACTOR_ID = 1 (из предиката WHERE) и…
• A.ACTOR_ID = FA.ACTOR_ID (из предиката ON)
  То:
• FA.ACTOR_ID = 1

W przypadku bardziej złożonych zapytań daje to bardzo dobre wyniki. W szczególności dokładność oszacowań liczebności znacznie wzrasta, gdyż możliwe staje się wybieranie estymacji na podstawie określonej stałej wartości predykatu, a nie np. średniej liczby filmów poszczególnych aktorów, jak w poniższym zapytaniu (zwracając ten sam wynik):

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE first_name = 'PENELOPE'
AND last_name = 'GUINESS'

Jego plan:

----------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |       |
|   1 |  NESTED LOOPS                        |                     |     2 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR               |     1 |
|*  3 |    INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME |     3 |
|*  4 |   INDEX RANGE SCAN                   | PK_FILM_ACTOR       |    27 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."FIRST_NAME"='PENELOPE')
   3 - access("A"."LAST_NAME"='GUINESS')
   4 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")

Jak widać liczba wierszy w tabeli FILM_ACTOR jest przeszacowana, natomiast NESTED LOOP niedoszacowana. Oto kilka interesujących wartości:

SELECT count(*) FROM film_actor WHERE actor_id = 1;
SELECT avg(c) FROM (
  SELECT count(*) c FROM film_actor GROUP BY actor_id
);

Wynik:

19
27.315

Stąd pochodzą szacunki. Jeśli baza danych wie, że mówimy o ACTOR_ID = 1, to może zbierać statystyki dotyczące liczby filmów tego konkretnego aktora . Jeśli tak nie jest (ponieważ standardowy mechanizm zbierania statystyk nie koreluje IMIĘ/NAZWISKO z ACTOR_ID), wówczas otrzymamy średnią liczbę filmów wszystkich aktorów . W tym konkretnym przypadku prosty, nieistotny błąd, ale w złożonym zapytaniu może się on rozprzestrzeniać dalej, kumulować i prowadzić dalej w zapytaniu (wyżej w planie) do nieprawidłowego wyboru JOIN. Więc kiedy tylko możesz, projektuj swoje łączenia i proste predykaty, aby wykorzystać domknięcie przechodnie. Jakie inne bazy danych obsługują tę funkcję?

DB2

Tak!

Explain Plan
-----------------------------------------------------------
ID | Operation              |                 Rows | Cost
 1 | RETURN                 |                      |   13
 2 |  NLJOIN                |              27 of 1 |   13
 3 |   FETCH ACTOR          |     1 of 1 (100.00%) |    6
 4 |    IXSCAN PK_ACTOR     |   1 of 200 (   .50%) |    0
 5 |   IXSCAN PK_FILM_ACTOR | 27 of 5462 (   .49%) |    6
Predicate Information
 4 - START (Q2.ACTOR_ID = 1)
      STOP (Q2.ACTOR_ID = 1)
 5 - START (1 = Q1.ACTOR_ID)
      STOP (1 = Q1.ACTOR_ID)

Swoją drogą, jeśli lubisz takie fajne plany wykonania, sprawdź scenariusz Markusa Winanda .

MySQL'a

Niestety plany wykonania MySQL nie nadają się dobrze do tego typu analiz. W informacjach wyjściowych brakuje samego predykatu:

ID  SELECT TYPE  TABLE  TYPE   REF    ROWS
------------------------------------------
1   SIMPLE       a      const  const  1
1   SIMPLE       fa     ref    const  19

Jednak fakt, że const została podana dwukrotnie w kolumnie REF pokazuje, że obie tabele szukają wartości stałej. Jednocześnie plan zapytań z FIRST_NAME/LAST_NAME wygląda następująco:

ID  SELECT TYPE  TABLE  TYPE   REF         ROWS
-----------------------------------------------
1   SIMPLE       a      ref    const       3
1   SIMPLE       fa     ref    a.actor_id  27

Jak widać, REF odwołuje się teraz do kolumny z predykatu JOIN. Wynik kardynalności jest prawie taki sam jak w Oracle. Więc tak, MySQL obsługuje również domknięcia przechodnie.

PostgreSQL

Tak!

QUERY PLAN
------------------------------------------------------------------------------------
Nested Loop  (cost=4.49..40.24 rows=27 width=15)
  ->  Seq Scan on actor a  (cost=0.00..4.50 rows=1 width=17)
        Filter: (actor_id = 1)
  ->  Bitmap Heap Scan on film_actor fa  (cost=4.49..35.47 rows=27 width=4)
        Recheck Cond: (actor_id = 1)
        ->  Bitmap Index Scan on film_actor_pkey  (cost=0.00..4.48 rows=27 width=0)
              Index Cond: (actor_id = 1)

Serwer SQL

Tak!

|--Nested Loops(Inner Join)
     |--Nested Loops(Inner Join)
     |    |--Index Seek (SEEK:([a].[actor_id]=(1)))
     |    |--RID Lookup
     |--Index Seek (SEEK:([fa].[actor_id]=(1)))

Streszczenie

Wszystkie nasze bazy danych obsługują domknięcie przechodnie. Poczekaj jednak na #6 w dalszej części artykułu. Istnieją złożone przypadki domknięcia przechodniego, z którymi nie wszystkie bazy danych sobie radzą.

2. Niemożliwe predykaty i niepotrzebne wywołania tabel

To całkowicie głupia optymalizacja, ale czemu nie? Jeśli użytkownicy piszą niemożliwe predykaty, po co zawracać sobie głowę ich wykonywaniem? Oto kilka przykładów:

-- "Очевидный"
SELECT * FROM actor WHERE 1 = 0
-- "Хитрый"
SELECT * FROM actor WHERE NULL = NULL

Pierwsze zapytanie oczywiście nigdy nie zwróci żadnych wyników, ale to samo stwierdzenie jest prawdziwe w przypadku drugiego. Przecież chociaż NULL IS NULL ma zawsze wartość PRAWDA, to wynikiem obliczenia NULL = NULL jest NULL, co zgodnie z logiką trójwartościową jest równoznaczne z FAŁSZ. Jest to dość oczywiste, więc przejdźmy od razu do sprawdzenia, które bazy danych przeprowadzają tę optymalizację.

DB2

Tak!

Explain Plan
-----------------------------------
ID | Operation      |   Rows | Cost
 1 | RETURN         |        |    0
 2 |  TBSCAN GENROW | 0 of 0 |    0

Jak widać, dostęp do stołu ACTOR jest całkowicie wykluczony z planu. Zawiera jedynie operację GENROW, która generuje zero wierszy. Doskonały.

MySQL'a

Tak!

ID  SELECT TYPE  TABLE   EXTRAS
-----------------------------------------
1   SIMPLE         Impossible WHERE

Tym razem MySQL był na tyle miły, że poinformował nas o niemożliwej klauzuli WHERE. Dziękuję! Dzięki temu analiza jest znacznie łatwiejsza, szczególnie w porównaniu z innymi bazami danych.

Wyrocznia

Tak!

---------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      0 |
|*  1 |  FILTER            |       |      1 |        |      0 |
|   2 |   TABLE ACCESS FULL| ACTOR |      0 |    200 |      0 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)

Widzimy, że w planie nadal jest mowa o dostępie do tabeli ACTOR, a oczekiwana liczba wierszy to nadal 200, ale jest też operacja filtrowania (FILTER) z Id=1, gdzie nigdy nie będzie TRUE. Ze względu na niechęć Oracle do standardowego typu danych SQL Boolean , Oracle wyświetla w planie wartość NULL IS NOT NULL, a nie tylko FALSE. No cóż... Ale poważnie, zwróć uwagę na to orzeczenie. Miałem okazję debugować plany wykonania z 1000-liniowymi poddrzewami i wyjątkowo wysokimi kosztami, ale po tym, jak odkryłem, że całe poddrzewo było „odcinane” przez filtr NULL IS NOT NULL. Trochę zniechęcające, mówię wam.

PostgreSQL

Tak!

QUERY PLAN
-------------------------------------------
Result  (cost=0.00..0.00 rows=0 width=228)
  One-Time Filter: false

Już lepiej. Żadnych irytujących wywołań tabeli ACTOR i schludnego, małego predykatu FALSE.

Serwer SQL?

Tak!

|--Constant Scan

SQL Server nazywa to „ ciągłym skanowaniem”, czyli skanowaniem, podczas którego nic się nie dzieje – podobnie jak w przypadku DB2. Wszystkie nasze bazy danych mogą wykluczać niemożliwe predykaty:

3. Wyeliminuj ŁĄCZENIA

W poprzedniej sekcji zaobserwowaliśmy niepotrzebne dostępy do tabel w zapytaniach jednotabelowych. Ale co się stanie, jeśli JOIN nie wymaga dostępu do jednego z kilku tabel? O wyeliminowaniu JOIN pisałem już w poprzednim poście na moim blogu . Silnik SQL jest w stanie na podstawie typu zapytania oraz obecności kluczy podstawowych i obcych określić, czy w danym zapytaniu faktycznie potrzebny jest konkretny JOIN, czy też jego wyeliminowanie nie wpłynie na semantykę zapytania. We wszystkich trzech kolejnych przykładach JOIN nie jest potrzebny. Wewnętrzne łączenie ... do jednego można wyeliminować, stosując klucz obcy NOT NULL. Zamiast tego:

SELECT first_name, last_name
FROM customer c
JOIN address a ON c.address_id = a.address_id

Baza danych może wykonywać następujące czynności:

SELECT first_name, last_name
FROM customer c

INNER JOIN typu „...-to-one” można zastąpić, jeśli istnieje klucz obcy dopuszczający wartość null. Powyższe zapytanie działa, jeśli klucz obcy podlega ograniczeniu NOT NULL. Jeśli nie, na przykład jak w tym żądaniu:

SELECT title
FROM film f
JOIN language l ON f.original_language_id = l.language_id

wtedy JOIN nadal można wyeliminować, ale będziesz musiał dodać predykat NOT NULL, w ten sposób:

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

OUTER JOIN typu „...-to-one” można usunąć, jeśli istnieje unikalny klucz. Zamiast tego:

SELECT first_name, last_name
FROM customer c
LEFT JOIN address a ON c.address_id = a.address_id

Baza danych ponownie może wykonać następujące czynności:

SELECT first_name, last_name
FROM customer c

... nawet jeśli nie ma klucza obcego dla CUSTOMER.ADDRESS_ID. Unikalne połączenie zewnętrzne (DISTINCT OUTER JOIN) typu „...-to-many” można usunąć. Zamiast tego:

SELECT DISTINCT first_name, last_name
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id

Baza danych może wykonywać następujące czynności:

SELECT DISTINCT first_name, last_name
FROM actor a

Wszystkie te przykłady zostały szczegółowo omówione w poprzednim artykule, więc nie będę się powtarzał, ale podsumuję wszystko, co różne bazy danych mogą wyeliminować: Niestety nie wszystkie bazy danych obsługują wszystkie typy połączeń. DB2 i SQL Server są tutaj niekwestionowanymi liderami! Ciąg dalszy nastąpi