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

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

8. SPRAWDŹ ograniczenia

O, to fajna sprawa! Nasza baza danych Sakila ma ograniczenie CHECK w kolumnie FILM.RATING :

CREATE TABLE film (
  ..
  RATING varchar(10) DEFAULT 'G',
  ..
  CONSTRAINT check_special_rating
    CHECK (rating IN ('G','PG','PG-13','R','NC-17')),
  ..
);

Poważnie, użyj ograniczeń CHECK , aby zapewnić integralność danych. Koszt ich dodania jest wyjątkowo niski - znacznie niższy niż w przypadku innych ograniczeń, np. PRIMARY , UNIQUE czy FOREIGN KEY , ponieważ nie wymagają one do działania indeksu, więc dostajesz je praktycznie "za darmo". Istnieje jednak interesujący niuans związany z optymalizacją! Rozważ następujące zapytania:

Niemożliwe predykaty

Napotkaliśmy już niemożliwe predykaty , nawet ograniczenia NOT NULL (które w rzeczywistości są specjalnym rodzajem ograniczenia CHECK ), ale to jest jeszcze fajniejsze:

SELECT *
FROM film
WHERE rating = 'N/A';

Nie ma takiego filmu i nie może być, gdyż ograniczenie CHECK uniemożliwia jego wstawienie (lub aktualizację). Ponownie powinno to przełożyć się na polecenie, aby nic nie robić. A co z tą prośbą?

CREATE INDEX idx_film_rating ON film (rating);

SELECT count(*)
FROM film
WHERE rating NOT IN ('G','PG','PG-13','R');

Dzięki powyższemu indeksowi prawdopodobnie wystarczy po prostu szybko przeskanować indeks i policzyć wszystkie filmy z oceną = 'NC-17' , ponieważ to jedyna pozostała ocena. Zatem zapytanie należy przepisać w następujący sposób:

SELECT count(*)
FROM film
WHERE rating = 'NC-17';

Powinno tak być niezależnie od indeksu, gdyż porównywanie kolumny z jedną wartością jest szybsze niż porównywanie z 4. Jakie bazy danych mogą to zrobić?

DB2

Predykat niemożliwy (ocena = „nie dotyczy”) Świetnie!

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

Predicate Information
 2 - RESID (1 = 0)

Odwrotny predykat (ocena = „NC-17”) Nie…

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |   34
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |   34
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |   34

Predicate Information
 3 - SARG  NOT(Q1.RATING IN ('G', 'PG', 'PG-13', 'R'))

Chociaż w kroku ID=3 używany jest indeks i choć liczebności są prawidłowe, następuje pełne skanowanie, ponieważ plan nie ma predykatu zakresu, a jedynie predykat „SARG”. Szczegóły znajdziesz w recenzji Marcusa Wynanda . Możesz to również zademonstrować, ręcznie odwracając predykat i uzyskując:

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |    7
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |    7
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |    7

Predicate Information
 3 - START (Q1.RATING = 'NC-17')
      STOP (Q1.RATING = 'NC-17')

Teraz mamy żądany predykat zakresu.

MySQL'a

MySQL obsługuje składnię ograniczeń CHECK , ale z jakiegoś powodu ich nie wymusza. Spróbuj tego:

CREATE TABLE x (a INT CHECK (a != 0));
INSERT INTO x VALUES (0);
SELECT * FROM x;

a otrzymasz:

A
-
0

Zero punktów dla MySQL (naprawdę, dlaczego nie po prostu wspierać ograniczeń CHECK ?)

Wyrocznia

Predykat niemożliwy (ocena = „nie dotyczy”)

--------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |
|*  1 |  FILTER            |      |      1 |        |      0 |
|*  2 |   TABLE ACCESS FULL| FILM |      0 |     89 |      0 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)
   2 - filter("RATING"='N/A')

Ponownie, bardzo dziwny filtr NULL IS NOT NULL , odcinający FULL TABLE SCAN , który równie łatwo można całkowicie usunąć z planu. Ale przynajmniej to działa! Odwrotny predykat (ocena = „NC - 17”)

----------------------------------------------------------------------------
| Id  | Operation             | Name            | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE       |                 |      1 |      1 |      1 |
|*  2 |   INDEX FAST FULL SCAN| IDX_FILM_RATING |      1 |    415 |    210 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((RATING'PG-13' AND RATING'R' AND RATING'PG' AND RATING'G'))

Predykatu nie da się odwrócić, ocena liczności jest bardzo kiepska, w dodatku zamiast INDEX RANGE SCAN otrzymujemy INDEX FAST FULL SCAN , a zamiast predykatu dostępu otrzymujemy predykat filtru . Ale to właśnie powinniśmy uzyskać, na przykład, ręcznie odwracając predykat:

------------------------------------------------------------------------
| Id  | Operation         | Name            | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE   |                 |      1 |      1 |      1 |
|*  2 |   INDEX RANGE SCAN| IDX_FILM_RATING |      1 |    210 |    210 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RATING"='NC-17')

Porażka!

PostgreSQL

Należy zauważyć, że baza danych Sakila w wersji PostgreSQL używa typu ENUM zamiast ograniczeń CHECK w kolumnie RATING . Zamiast tego zduplikowałem tabelę, używając ograniczenia CHECK . Predykat niemożliwy (ocena = „nie dotyczy”) Nie działa:

QUERY PLAN
------------------------------------------------------
Seq Scan on film2  (cost=0.00..67.50 rows=1 width=385)
  Filter: ((rating)::text = 'N/A'::text)

Odwrotny predykat (ocena = „NC-17”) również nie działa:

QUERY PLAN
------------------------------------------------------------------
Aggregate  (cost=70.53..70.54 rows=1 width=8)
  ->  Seq Scan on film2  (cost=0.00..70.00 rows=210 width=0)
        Filter: ((rating)::text  ALL ('{G,PG,PG-13,R}'::text[]))

Bardzo przepraszam! Uwaga: jak uprzejmie zauważył nas w komentarzach David Rowley , tę funkcję można włączyć, ustawiając parametr:

SET constraint_exclusion TO on;
SQL Server
Невозможный предикат (rating = 'N/A')
Да!
|--Constant Scan

Odwrotny predykat (ocena = „NC-17”) Tak też!

|--Compute Scalar
     |--Stream Aggregate
          |--Index Seek(OBJECT:([idx_film_rating]), SEEK:([rating]='NC-17'))

Streszczenie

9. Niepotrzebne połączenia odruchowe.

W miarę jak zapytania stają się coraz bardziej złożone, może zaistnieć potrzeba wykonania sprzężenia refleksyjnego w tabeli w oparciu o jej klucz podstawowy. Uwierz mi, jest to bardzo powszechna praktyka podczas budowania złożonych widoków i łączenia ich ze sobą, więc upewnienie się, że baza danych zwraca na to uwagę, jest kluczową częścią optymalizacji złożonego kodu SQL. Nie będę demonstrował skomplikowanego przykładu, wystarczy prosty, na przykład:

SELECT a1.first_name, a1.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

Można to postrzegać jako szczególny przypadek eliminacji JOIN , ponieważ tak naprawdę nie potrzebujemy łączenia z A2 , możemy zrobić wszystko, czego potrzebujemy, korzystając tylko z tabeli A1 . Następnie eliminacja INNER JOIN działa poprawnie tylko wtedy, gdy istnieje FOREIGN KEY , którego tutaj nie mamy. Ale dzięki kluczowi głównemu ACTOR_ID możemy udowodnić, że faktycznie A1 = A2 . W pewnym sensie jest to znowu domknięcie przechodnie . Możesz pójść jeszcze dalej i wykorzystać kolumny z obu tabel A1 i A2 :

SELECT a1.first_name, a2.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

W klasycznym przypadku eliminacji JOIN nie byłoby już możliwości jej wyeliminowania, ponieważ obie tabele są rzutowane. Ale skoro już udowodniliśmy, że A1 = A2 , to są one wymienne, więc możemy spodziewać się konwersji zapytania na:

SELECT first_name, last_name
FROM actor;

Jaki DBMS może to zrobić?

DB2

Projekcja tylko stołu A1 Tak:

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

Rzut tabel A1 i A2 ...też tak:

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

MySQL'a

Tylko projekcja stołu A1 Nr.

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id  Using index

Rzut tabel A1 i A2 ...także nr

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id

Kompletne rozczarowanie...

Wyrocznia

Projekcja tylko stołu A1 Tak

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

Projekcja tabel A1 i A2 Jeszcze raz tak

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

PostgreSQL

Tylko projekcja stołu A1 Nie:

QUERY PLAN
--------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=17)
  ->  Hash  (cost=4.00..4.00 rows=200 width=4)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=4)

Rzut tabel A1 i A2 I znowu nie:

QUERY PLAN
---------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=10)
  ->  Hash  (cost=4.00..4.00 rows=200 width=11)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=11)

Serwer SQL

Projekcja tylko stołu A1. Dziwne, że nie! (Pamiętaj jednak, że używam SQL Server 2014, w nowszych wersjach może to zostać naprawione. Zdecydowanie przydałaby mi się aktualizacja!)

|--Merge Join(Inner Join, MERGE:([a2].[actor_id])=([a1].[actor_id]))
     |--Index Scan(OBJECT:([a2]))
     |--Sort(ORDER BY:([a1].[actor_id] ASC))
          |--Table Scan(OBJECT:([a1]))

Projekcja tabel A1 i A2 Już nie, a plan nawet zmienił się na gorsze:

|--Hash Match(Inner Join, HASH:([a1].[actor_id])=([a2].[actor_id]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a1]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a2]))

Streszczenie

Szczerze mówiąc spodziewałem się, że ta optymalizacja zostanie przeprowadzona na wszystkich bazach danych, ale niestety bardzo się myliłem. Oprócz wyeliminowania JOIN jest to jedna z najważniejszych optymalizacji, umożliwiająca budowanie ogromnych zapytań SQL z części nadających się do ponownego wykorzystania, takich jak widoki i funkcje tabelowe. Niestety nie jest obsługiwany w 3 z 5 najpopularniejszych baz danych.