Optimizations SQL Cool sing ora gumantung ing model biaya. Bagean 4

Optimizations SQL Cool sing ora gumantung ing model biaya. Part 1 Optimizations SQL Cool sing ora gumantung ing model biaya. Part 2 Optimizations SQL Cool sing ora gumantung ing model biaya. Bagean 3

8. Mriksa watesan

Oh, iki apik tenan! Database Sakila kita duwe kendala CHECK ing kolom FILM.RATING :

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

Serius, gunakake watesan CHECK kanggo njamin integritas data. Biaya nambahake iku arang banget - kurang saka watesan liyane, contone, PRIMARY , UNIK utawa FOREIGN KEY , amarga padha ora mbutuhake indeks kanggo bisa, supaya sampeyan njaluk wong praktis "gratis". Nanging ana nuansa menarik sing ana hubungane karo optimasi! Coba pitakon ing ngisor iki:

Predikat mokal

Kita wis nemoni predikat sing ora mungkin , sanajan kendala NOT NULL (sing sejatine minangka kendala CHECK khusus ), nanging iki luwih adhem:

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

Ora ana film kasebut, lan ora ana, amarga watesan CHECK nyegah sisipan (utawa nganyari). Maneh, iki kudu nerjemahake prentah supaya ora nindakake apa-apa. Kepiye babagan panjaluk iki?

CREATE INDEX idx_film_rating ON film (rating);

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

Thanks kanggo indeks ing ndhuwur, bisa uga cukup kanggo nindakake pindai cepet indeks lan ngetung kabeh film kanthi rating = 'NC-17' , amarga mung siji-sijine rating sing isih ana. Dadi pitakon kudu ditulis maneh kaya mangkene:

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

Iki mesthine ora preduli saka indeks, amarga mbandhingake kolom kanthi nilai siji luwih cepet tinimbang mbandhingake karo 4. Dadi, database apa sing bisa nindakake iki?

DB2

Predikat mokal (rating = 'N/A') Keren!

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

Predicate Information
 2 - RESID (1 = 0)

Predikat mbalikke (rating = 'NC-17') Ora...

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

Senajan ID = 3 langkah nggunakake indeks, lan sanajan kardinalitas bener, ana scan lengkap amarga rencana ora duwe sawetara predikat, mung predikat "SARG". Waca review Marcus Wynand kanggo rincian . Sampeyan uga bisa nduduhake iki kanthi ngowahi predikat kanthi manual lan entuk:

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

Saiki kita duwe predikat rentang sing dikarepake.

MySQL

MySQL ndhukung sintaks CHECK constraint , nanging sakperangan alesan ora dileksanakake. Coba iki:

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

lan sampeyan bakal entuk:

A
-
0

Nol poin kanggo MySQL (pancen, kenapa ora mung ndhukung kendala CHECK ?)

Oracle

Predikat ora mungkin (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')

Maneh, filter sing aneh banget NULL IS NOT NULL , ngethok FULL TABLE SCAN , sing bisa uga gampang dibusak saka rencana kasebut. Nanging paling ora bisa! Predikat mbalikke (rating = 'NC-17') Ups:

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

Predikat ora bisa dibalik, penilaian kardinalitas banget lumpuh, saliyane kita entuk INDEX FAST FULL SCAN tinimbang INDEX RANGE SCAN , lan predikat filter tinimbang predikat akses . Nanging iki sing kudu ditindakake, umpamane, kanthi ngowahi predikat kanthi manual:

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

Elinga yen versi PostgreSQL saka database Sakila nggunakake jinis ENUM tinimbang CHECK alangan ing kolom RATING . Aku duplikat tabel nggunakake CHECK alangan tinimbang . Predikat mokal (rating = 'N/A') Ora bisa:

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

Predikat mbalikke (rating = 'NC-17') uga ora bisa:

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

Nuwun sewu! Cathetan: Minangka David Rowley nudingake kita ing komentar , fitur iki bisa diaktifake kanthi nyetel parameter:

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

Predikat mbalikke (rating = 'NC-17') Ya uga!

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

Ringkesan

9. Sambungan refleksif sing ora perlu.

Nalika pitakon sampeyan dadi luwih rumit, sampeyan bisa uga kudu nindakake gabungan reflektif ing tabel adhedhasar kunci utama. Pracayaa, iki minangka praktik sing umum banget nalika mbangun tampilan kompleks lan nyambungake siji-sijine, supaya manawa database menehi perhatian marang iki minangka bagean kritis kanggo ngoptimalake kode SQL sing kompleks. Aku ora bakal nduduhake conto sing rumit, sing prasaja bakal cukup, contone:

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

Iki bisa katon minangka kasus khusus saka JOIN eliminasi , awit kita ora bener kudu nggabungake kanggo A2 , kita bisa nindakake kabeh sing perlu karo mung Tabel A1 . Sabanjure, penghapusan INNER JOIN mung bisa digunakake yen ana KUNCI ASING , sing ora ana ing kene. Nanging thanks kanggo kunci utami dening ACTOR_ID , kita bisa mbuktekaken sing nyatane A1 = A2 . Ing pangertèn, iki maneh penutupan transitif . Sampeyan bisa luwih maju lan nggunakake kolom saka loro tabel A1 lan A2 :

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

Ing kasus klasik eliminasi JOIN , ora bisa diilangi maneh amarga loro tabel digambarake. Nanging amarga kita wis mbuktekake yen A1 = A2 , mula bisa diganti, mula kita bisa ngarepake pitakon kasebut bakal diowahi dadi:

SELECT first_name, last_name
FROM actor;

Apa DBMS bisa nindakake iki?

DB2

Proyeksi tabel A1 mung Ya:

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

Proyeksi tabel A1 lan A2 ... uga ya:

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

MySQL

Proyeksi tabel A1 mung No.

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

Proyeksi tabel A1 lan A2 ... uga ora

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

Kuciwane komplit...

Oracle

Proyeksi tabel A1 mung Ya

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

Proyeksi tabel A1 lan A2 Ya maneh

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

PostgreSQL

Proyeksi tabel A1 mung No:

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)

Proyeksi tabel A1 lan A2 Lan maneh ora:

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

Proyeksi tabel A1 mung Aneh, ora! (Nanging elinga yen aku nggunakake SQL Server 2014, versi sing luwih anyar bisa uga tetep. Aku mesthi bisa nggunakake 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]))

Proyeksi tabel A1 lan A2 Ora maneh, lan rencana kasebut malah saya ganti:

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

Ringkesan

Terus terang, aku ngarepake manawa optimasi iki bakal ditindakake ing kabeh database, nanging aku salah banget, sedhih. Bebarengan karo ngilangi JOIN , iki minangka salah sawijining optimasi sing paling penting, ngidini sampeyan mbangun pitakon SQL sing gedhe saka bagean sing bisa digunakake maneh kaya tampilan lan fungsi tabel. Sayange, iku ora didhukung ing 3 saka 5 database paling umum.