Bahanyň modeline bagly bolmadyk sowuk SQL optimizasiýalary. 2-nji bölüm

Bahanyň modeline bagly bolmadyk sowuk SQL optimizasiýalary. 1-nji bölüm

4. "manysyz" predikatlary ýok etmek

Deň manysyz, hemişe diýen ýaly dogry sözlerdir. Göz öňüne getirişiňiz ýaly, soraýan bolsaňyz:

SELECT * FROM actor WHERE 1 = 1;

... şonda maglumat bazalary aslynda ýerine ýetirmez, diňe äsgermezlik eder. Bir gezek “Stack Overflow” -da bu hakda bir soraga jogap berdim we şonuň üçin bu makalany ýazmagy makul bildim. Muny synagy okyja maşk hökmünde goýaryn, ýöne predikat biraz az "manysyz" bolsa näme bolar? Mysal üçin:

SELECT * FROM film WHERE release_year = release_year;

Hakykatdanam, her hatar üçin bahany özi bilen deňeşdirmek zerurmy? , Ok, bu çaklamanyň ALALAN boljak gymmaty ýok , şeýlemi? Stillöne henizem barlamaly. Kesgitleýiş ALALSEYŞ bilen deň bolup bilmese-de, üç bahaly logika sebäpli hemme ýerde NULL bilen deň bolup biler . RELEASE_YEAR sütüni ulanyp bolmaýar, eger hatarlaryň haýsydyr birinde RELEASE_YEAR IS NULL bolsa , NULL = NULL NULL - da netijeler bolýar we hatar ýok edilmeli. Şonuň üçin haýyş aşakdakylara öwrülýär:

SELECT * FROM film WHERE release_year IS NOT NULL;

Haýsy maglumat bazalary muny edýär?

DB2

Hawa!

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

Utandyryjy zat, ýöne MySQL ýene-de ýerine ýetiriş meýilnamalarynda kartalary düzmeýär, şonuň üçin MySQL-yň bu optimizasiýany durmuşa geçirýändigini ýa-da ýokdugyny anyklamak birneme kyn. Netijelilige baha berip, uly göwrümli deňeşdirmeleriň edilýändigini ýa-da ýokdugyny görüp bilersiňiz. Ora-da indeks goşup bilersiňiz:

CREATE INDEX i_release_year ON film (release_year);

Munuň ýerine aşakdaky haýyşlar üçin meýilnamalary alyň:

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

Optimizasiýa işleýän bolsa, iki soragyň meýilnamalary takmynan birmeňzeş bolmaly. Emma bu ýagdaýda beýle däl:

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

Görşüňiz ýaly, iki soragymyz POSSIBLE_KEYS we FILTERED sütünleriniň bahalarynda ep-esli tapawutlanýar . Şonuň üçin MySQL muny optimizirlemeýär diýip çaklaýaryn.

Oracle

Hawa!

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

Gynansagam ýok!

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

Meýilnamalar we çykdajylar dürli-dürli bolýar. .Agny, kardinallyga baha bermäge serediň, bu düýbünden gowy däl, ýöne bu çaklaýar:

SELECT * FROM film WHERE release_year IS NOT NULL;

has gowy netijeleri berýär:

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

Bummer!

SQL serweri

Geň ýeri, SQL Serweri hem muny eden ýaly däl:

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

Şeýle-de bolsa, meýilnamanyň daşky görnüşine esaslanyp, kardinallyga baha bermek, çykdajy ýaly dogry. Söne SQL Serweri bilen toplan tejribämde, bu ýagdaýda hiç hili optimizasiýa bolmaýandygyny aýdýaryn, sebäbi SQL Serweri meýilnamada ýerine ýetirilen predikaty görkezer (munuň sebäbini görmek üçin aşakdaky CHECK çäklendiriji mysallaryna göz aýlaň ). NULL sütünlerindäki "manysyz" çaklamalar hakda näme aýdyp bilersiňiz ? Aboveokardaky öwrülişik diňe RELEASE_YEAR kesgitlenmedik bolany üçin zerur boldy. Şol bir manysyz soragy, meselem, FILM_ID sütüninde işledseňiz näme bolar ?

SELECT * FROM film WHERE film_id = film_id

Indi hiç hili predikete gabat gelmeýärmi? Ora-da bolmasa, şeýle bolmaly. Emma şeýlemi?

DB2

Hawa!

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

Hiç hili kynçylyk ulanylmaýar we ähli filmleri saýlaýarys.

MySQL

Hawa! (Againene-de bilimli çaklama)

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

EXTRA sütüniniň indi boşdugyna üns beriň , aslynda WHERE maddasy ýok ýaly !

Oracle

Hawa!

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

Againene-de hiç hili predikat ulanylmaýar.

PostgreSQL

Wah, ýok!

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

Süzgüç ulanylýar we kardinallyk baly henizem 5. Bummer!

SQL serweri

Ynha, ýok!

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

Gysgaça mazmun

Simpleönekeý optimizasiýa ýaly bolup görünýär, ýöne ähli DBMS-lerde ulanylmaýar; esasanam geň zat, SQL Serwerinde ulanylmaýar!

5. EXISTS kiçi soragnamalarynda çaklamalar

Gyzykly tarapy, men olar hakda hemişe master klasymda soraýaryn, bu ýerde SELECT * adatça hiç hili gowulyk getirmeýär diýen pikiri goraýaryn. Sorag: SELECT * -ni EXISTS subquery -da ulanmak mümkinmi ? Mysal üçin, filmlerde oýnan aktýorlary tapmaly bolsa ...

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
)

Jogap ... hawa. Bolup biler. Teryldyzjyk haýyşa täsir etmeýär. Muňa nädip ynanyp bilersiňiz? Aşakdaky soraga serediň:

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

Bu maglumat bazalarynyň hemmesi nol ýalňyşlyk bilen bölünişigi habar berýär. Gyzykly bir hakykata üns beriň: MySQL-da nula bölsek, ýalňyşlygyň ýerine NULL alarys , şonuň üçin rugsat berilmeýän başga bir zat etmeli bolarys. Indi, ýokardaky soraglaryň ýerine, aşakdaky soraglary ýerine ýetirsek näme bolar?

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

Indi maglumat bazalarynyň hiç birinde-de ýalňyşlyk ýok. Olaryň hemmesi HAKYKAT ýa-da 1-ni gaýtaryp berýärler . Diýmek, maglumatlar bazalarymyzyň hiç biri-de EXISTS subquery-nyň proýeksiýasyna (ýagny SELECT maddasyna ) baha bermeýär . Mysal üçin SQL Serweri aşakdaky meýilnamany görkezýär:

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

Görşüňiz ýaly, CASE aňlatmasy hemişeliklige öwrüldi we subquery ýok edildi. Beýleki maglumat bazalary subquery meýilnamada saklaýar we proýeksiýa barada hiç zat aýtmaýar, geliň, Oracle-daky asyl gözleg meýilnamasyna başga bir göz aýlalyň:

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

Aboveokardaky talap meýilnamasy şuňa meňzeýär:

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

Proýeksiýa barada maglumatlary Id = 3 -de synlaýarys . Aslynda, FILM_ACTOR tablisasyna -da girip bilmeris, sebäbi zerur däl. EXISTS çaklamasy, bir ACTOR_ID sütüninde daşary ýurt açar görkezijisini ulanyp ýerine ýetirilip bilner - SELECT * ýazsak-da, bu talap üçin zerur zat .

Gysgaça mazmun

Bagtymyza, maglumatlar bazalarymyzyň hemmesi EXISTS soragnamalaryndan proýeksiýany aýyrýar : 3-nji bölümi diňläň , bu ýerde beýleki ajaýyp SQL optimizasiýalaryny ara alyp maslahatlaşarys.