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

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

4. Eliminacja „bezsensownych” predykatów

Równie bezsensowne są predykaty, które są (prawie) zawsze prawdziwe. Jak możesz sobie wyobrazić, jeśli pytasz:

SELECT * FROM actor WHERE 1 = 1;

...wtedy bazy danych faktycznie go nie wykonają, ale po prostu je zignorują. Kiedyś odpowiedziałem na pytanie na ten temat na Stack Overflow i dlatego zdecydowałem się napisać ten artykuł. Testowanie tego pozostawię czytelnikowi jako ćwiczenie, ale co się stanie, jeśli predykat będzie trochę mniej „bezsensowny”? Na przykład:

SELECT * FROM film WHERE release_year = release_year;

Czy naprawdę musisz porównywać wartość ze sobą w każdym wierszu? Nie, nie ma wartości, dla której ten predykat miałby wartość FALSE , prawda? Ale nadal musimy to sprawdzić. Chociaż predykat nie może być równy FALSE , może wszędzie mieć wartość NULL , ponownie ze względu na logikę trójwartościową. Kolumna RELEASE_YEAR dopuszcza wartość null i jeśli którykolwiek z wierszy zawiera RELEASE_YEAR IS NULL , wówczas wartość NULL = NULL daje wartość NULL i wiersz należy wyeliminować. Zatem żądanie staje się następujące:

SELECT * FROM film WHERE release_year IS NOT NULL;

Które bazy danych to robią?

DB2

Tak!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49
Predicate Information
 2 - SARG Q1.RELEASE_YEAR IS NOT NULL

MySQL'a

Szkoda, ale MySQL ponownie nie odwzorowuje predykatów na plany wykonania, więc ustalenie, czy MySQL implementuje tę konkretną optymalizację, jest trochę trudne. Można przeprowadzić ocenę wydajności i sprawdzić, czy przeprowadzane są porównania na dużą skalę. Możesz też dodać indeks:

CREATE INDEX i_release_year ON film (release_year);

W zamian uzyskaj plany dotyczące następujących żądań:

SELECT * FROM film WHERE release_year = release_year;
SELECT * FROM film WHERE release_year IS NOT NULL;

Jeśli optymalizacja zadziała, to plany obu zapytań powinny być w przybliżeniu takie same. Ale w tym przypadku tak nie jest:

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film             1000  10.00           Using where

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film   i_release_year  1000  100.00    Using where

Jak widać nasze dwa zapytania różnią się znacząco wartościami kolumn POSSIBLE_KEYS i FILTERED . Zaryzykuję więc rozsądne przypuszczenie, że MySQL tego nie optymalizuje.

Wyrocznia

Tak!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|*  1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RELEASE_YEAR" IS NOT NULL)

PostgreSQL

Niestety nie!

QUERY PLAN
--------------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: ((release_year)::integer = (release_year)::integer)

Plany i koszty są różne. Mianowicie spójrz na ocenę liczności, która jest absolutnie niedobra, podczas gdy ten predykat:

SELECT * FROM film WHERE release_year IS NOT NULL;

daje dużo lepsze rezultaty:

QUERY PLAN
---------------------------------------------------------
Seq Scan on film  (cost=0.00..65.00 rows=1000 width=386)
  Filter: (release_year IS NOT NULL)

Porażka!

Serwer SQL

Co dziwne, SQL Server też tego nie robi:

|--Table Scan(OBJECT:([film]), WHERE:([release_year]=[release_year]))

Jednak na podstawie wyglądu planu ocena liczności jest prawidłowa, podobnie jak koszt. Jednak z mojego doświadczenia z SQL Server powiedziałbym, że w tym przypadku nie zachodzi żadna optymalizacja, ponieważ SQL Server wyświetli faktycznie wykonany predykat w planie (aby zobaczyć dlaczego, spójrz na przykłady ograniczeń CHECK poniżej). A co z „bezsensownymi” predykatami w kolumnach NOT NULL ? Powyższa konwersja była konieczna tylko dlatego, że RELEASE_YEAR może być niezdefiniowany. Co się stanie, jeśli uruchomisz to samo bezsensowne zapytanie na przykład w kolumnie FILM_ID ?

SELECT * FROM film WHERE film_id = film_id

Czy w ogóle nie odpowiada to żadnemu predykatowi? A przynajmniej tak powinno być. Ale czy tak jest?

DB2

Tak!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49

Żadne predykaty nie są stosowane i wybieramy wszystkie filmy.

MySQL'a

Tak! (Ponownie, wykształcone przypuszczenie)

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film                   1000  100.00

Zauważ, że kolumna EXTRA jest teraz pusta, jakbyśmy w ogóle nie mieli klauzuli WHERE!

Wyrocznia

Tak!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|   1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------

Ponownie nie obowiązują żadne predykaty.

PostgreSQL

Oj, znowu nie!

QUERY PLAN
------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: (film_id = film_id)

Filtr został zastosowany, a wynik kardynalności nadal wynosi 5. Kiepsko!

Serwer SQL

I tu znowu nie!

|--Table Scan(OBJECT:([film]), WHERE:([film_id]=[film_id]))

Streszczenie

Wydaje się to prostą optymalizacją, ale nie jest używana we wszystkich systemach DBMS; w szczególności, co dziwne, nie jest używana w SQL Server!

5. Projekcje w podzapytaniach EXISTS

Co ciekawe, cały czas jestem o nie pytany na moich kursach mistrzowskich, gdzie bronię poglądu, że SELECT* zwykle do niczego dobrego nie prowadzi. Pytanie brzmi: czy można użyć SELECT * w podzapytaniu EXISTS ? Na przykład, jeśli musimy znaleźć aktorów, którzy grali w filmach...

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT * -- Is this OK?
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

A odpowiedź brzmi... tak. Móc. Gwiazdka nie ma wpływu na żądanie. Jak możesz być tego pewien? Rozważ następujące zapytanie:

-- DB2
SELECT 1 / 0 FROM sysibm.dual
-- Oracle
SELECT 1 / 0 FROM dual
-- PostgreSQL, SQL Server
SELECT 1 / 0
-- MySQL
SELECT pow(-1, 0.5);

Wszystkie te bazy danych zgłaszają błąd dzielenia przez zero. Zwróć uwagę na ciekawy fakt: w MySQL, gdy dzielimy przez zero, zamiast błędu otrzymujemy NULL , więc musimy zrobić coś innego, co jest niedozwolone. Co się teraz stanie, jeśli zamiast powyższych wykonamy następujące zapytania?

-- DB2
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM sysibm.dual
) THEN 1 ELSE 0 END
FROM sysibm.dual
-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
-- SQL Server
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0
) THEN 1 ELSE 0 END
-- MySQL
SELECT EXISTS (SELECT pow(-1, 0.5));

Teraz żadna z baz danych nie zwraca błędu. Wszystkie zwracają TRUE lub 1 . Oznacza to, że żadna z naszych baz danych tak naprawdę nie ocenia rzutowania (czyli klauzuli SELECT ) podzapytania EXISTS . Na przykład SQL Server pokazuje następujący plan:

|--Constant Scan(VALUES:((CASE WHEN (1) THEN (1) ELSE (0) END)))

Jak widać, wyrażenie CASE zostało zamienione na stałą, a podzapytanie zostało wyeliminowane. Inne bazy danych przechowują podzapytanie w planie i nie wspominają nic o projekcji, więc przyjrzyjmy się jeszcze raz oryginalnemu planowi zapytań w Oracle:

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT *
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Powyższy plan zapytań wygląda następująco:

------------------------------------------------------------------
| Id  | Operation             | Name                    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |        |
|*  1 |  HASH JOIN SEMI       |                         |    200 |
|   2 |   TABLE ACCESS FULL   | ACTOR                   |    200 |
|   3 |   INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR |   5462 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) LAST_NAME, FIRST_NAME
   2 - (rowset=256) A.ACTOR_ID, FIRST_NAME, LAST_NAME
   3 - FA.ACTOR_ID

Informację o projekcji obserwujemy przy Id=3 . Tak naprawdę nie mamy nawet dostępu do tabeli FILM_ACTOR , ponieważ nie jest to nam potrzebne. Predykat EXISTS można wykonać przy użyciu indeksu klucza obcego w pojedynczej kolumnie ACTOR_ID - wszystko, co jest potrzebne dla tego zapytania - mimo że napisaliśmy SELECT * .

Streszczenie

Na szczęście wszystkie nasze bazy danych usuwają projekcję z podzapytań EXISTS : Bądź na bieżąco z częścią 3 , w której omówimy inne fajne optymalizacje SQL.