Mga cool na SQL optimization na hindi nakadepende sa modelo ng gastos. Bahagi 4

Mga cool na SQL optimization na hindi nakadepende sa modelo ng gastos. Bahagi 1 Mga cool na pag-optimize ng SQL na hindi nakadepende sa modelo ng gastos. Bahagi 2 Mga cool na pag-optimize ng SQL na hindi nakadepende sa modelo ng gastos. Bahagi 3

8. SURIIN ang mga paghihigpit

Oh, ito ay cool na bagay! Ang aming Sakila database ay may CHECK constraint sa FILM.RATING column :

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

Seryoso, gumamit ng CHECK constraints para matiyak ang integridad ng data. Ang halaga ng pagdaragdag sa mga ito ay napakababa - mas mababa kaysa sa iba pang mga paghihigpit, halimbawa, PRIMARY , NATATANGI o FOREIGN KEY , dahil hindi sila nangangailangan ng isang index upang gumana, kaya maaari mong makuha ang mga ito nang "libre". Ngunit mayroong isang kawili-wiling nuance na nauugnay sa pag-optimize! Isaalang-alang ang mga sumusunod na query:

Imposibleng panaguri

Nakatagpo na kami ng mga imposibleng predicates , kahit na NOT NULL constraints (na talagang isang espesyal na uri ng CHECK constraint ), ngunit ang isang ito ay mas cool pa:

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

Walang ganoong pelikula, at hindi maaaring magkaroon, dahil pinipigilan ng CHECK constraint ang pagpasok nito (o pag-update). Muli, dapat itong isalin sa isang utos na walang gagawin. Paano ang kahilingang ito?

CREATE INDEX idx_film_rating ON film (rating);

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

Salamat sa index sa itaas, marahil ay sapat na na gawin lamang ang isang mabilis na pag-scan ng index at bilangin ang lahat ng mga pelikulang may rating = 'NC-17' , dahil iyon na lang ang natitirang rating. Kaya ang query ay dapat na muling isulat tulad nito:

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

Ito dapat ang kaso anuman ang index, dahil ang paghahambing ng column na may isang value ay mas mabilis kaysa sa paghahambing sa 4. Kaya, anong mga database ang makakagawa nito?

DB2

Imposibleng panaguri (rating = 'N/A') Astig!

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

Predicate Information
 2 - RESID (1 = 0)

Baliktad na panaguri (rating = 'NC-17') Hindi...

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'))

Bagama't ang ID=3 na hakbang ay gumagamit ng isang index, at bagama't ang mga kardinalidad ay tama, ang isang buong pag-scan ay nangyayari dahil ang plano ay walang saklaw na predicate, tanging ang "SARG" na predicate. Tingnan ang pagsusuri ni Marcus Wynand para sa mga detalye . Maaari mo ring ipakita ito sa pamamagitan ng manu-manong pagbaligtad sa panaguri at pagkuha ng:

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')

Ngayon ay mayroon na tayong gustong hanay ng predicate.

MySQL

Sinusuportahan ng MySQL ang CHECK constraint syntax , ngunit sa ilang kadahilanan ay hindi ito ipinapatupad. Subukan mo ito:

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

at makakakuha ka ng:

A
-
0

Zero points para sa MySQL (talaga, bakit hindi na lang suportahan ang CHECK constraints ?)

Oracle

Imposibleng panaguri (rating = 'N/A')

--------------------------------------------------------------
| 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')

Muli, isang kakaibang filter na NULL IS NOT NULL , pinutol ang FULL TABLE SCAN , na maaaring kasing madaling maalis sa plano nang buo. Ngunit hindi bababa sa ito ay gumagana! Baliktad na panaguri (rating = 'NC-17') Oops:

----------------------------------------------------------------------------
| 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'))

Ang predicate ay hindi maaaring baligtarin, ang cardinality assessment ay napaka pilay, bilang karagdagan nakakakuha kami ng INDEX FAST FULL SCAN sa halip na INDEX RANGE SCAN , at ang filter na predicate sa halip na ang access predicate . Ngunit ito ang dapat nating makuha, halimbawa, sa pamamagitan ng manu-manong pagbaligtad ng panaguri:

------------------------------------------------------------------------
| 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')

Bummer!

PostgreSQL

Tandaan na ang PostgreSQL na bersyon ng Sakila database ay gumagamit ng ENUM na uri sa halip na CHECK na mga hadlang sa RATING column . Nadoble ko ang talahanayan gamit ang isang CHECK constraint sa halip . Ang imposibleng panaguri (rating = 'N/A') ay hindi gumagana:

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

Ang reverse predicate (rating = 'NC-17') ay hindi rin gumagana:

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[]))

Sorry talaga! Tandaan: Tulad ng mabait na itinuro sa amin ni David Rowley sa mga komento , maaaring paganahin ang feature na ito sa pamamagitan ng pagtatakda ng parameter:

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

Baliktarin ang panaguri (rating = 'NC-17') Oo din!

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

Buod

9. Mga hindi kinakailangang reflexive na koneksyon.

Habang nagiging mas kumplikado ang iyong mga query, maaaring kailanganin mong magsagawa ng reflective join sa isang talahanayan batay sa pangunahing key nito. Maniwala ka sa akin, ito ay isang napaka-karaniwang kasanayan kapag bumubuo ng mga kumplikadong view at ikinonekta ang mga ito sa isa't isa, kaya ang pagtiyak na binibigyang pansin ito ng database ay isang kritikal na bahagi ng pag-optimize ng kumplikadong SQL code. Hindi ako magpapakita ng isang kumplikadong halimbawa, ang isang simple ay sapat na, halimbawa:

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

Ito ay makikita bilang isang espesyal na kaso ng JOIN elimination , dahil hindi naman talaga namin kailangan ng join sa A2 , magagawa namin ang lahat ng kailangan namin gamit lang ang table A1 . Susunod, gagana lang nang maayos ang INNER JOIN elimination kung mayroong FOREIGN KEY , na wala tayo rito. Ngunit salamat sa pangunahing susi ng ACTOR_ID , mapapatunayan natin na sa katunayan A1 = A2 . Sa isang kahulugan, isa na naman itong transitive closure . Maaari kang pumunta nang higit pa at gumamit ng mga haligi mula sa parehong mga talahanayan A1 at A2 :

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

Sa klasikong kaso ng JOIN elimination , hindi na ito posibleng alisin dahil ang parehong mga talahanayan ay inaasahang. Ngunit dahil napatunayan na natin na A1 = A2 , kung gayon ang mga ito ay mapagpapalit, kaya maaari nating asahan na ang query ay mako-convert sa:

SELECT first_name, last_name
FROM actor;

Anong DBMS ang makakagawa nito?

DB2

Projection ng talahanayan A1 lamang Oo:

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

Projection ng mga talahanayan A1 at A2 ... oo din:

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

MySQL

Projection ng talahanayan A1 lamang No.

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

Projection ng mga talahanayan A1 at A2 ... hindi rin

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

Ganap na pagkabigo...

Oracle

Projection ng talahanayan A1 lamang Oo

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

Projection ng mga talahanayan A1 at A2 Oo muli

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

PostgreSQL

Projection ng talahanayan A1 lamang Hindi:

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)

Projection ng mga talahanayan A1 at A2 At muli hindi:

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)

SQL Server

Projection ng talahanayan A1 lamang Kakatwa, hindi! (Ngunit tandaan na gumagamit ako ng SQL Server 2014, maaaring naayos na ito ng mga mas bagong bersyon. Talagang makakagamit ako ng pag-upgrade!)

|--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]))

Projection ng mga talahanayan A1 at A2 Hindi muli, at ang plano ay nagbago pa para sa mas masahol pa:

|--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]))

Buod

Sa totoo lang, inaasahan ko na ang pag-optimize na ito ay isasagawa sa lahat ng mga database, ngunit ako ay lubos na nagkamali, nakalulungkot. Kasabay ng pag-aalis ng JOIN , isa ito sa pinakamahalagang pag-optimize, na nagbibigay-daan sa iyong bumuo ng malalaking query sa SQL mula sa mga bahaging magagamit muli tulad ng mga view at function ng talahanayan. Sa kasamaang palad, hindi ito sinusuportahan sa 3 sa 5 pinakakaraniwang database.