Наркы моделине көз каранды эмес Cool SQL оптималдаштыруу. 2 бөлүк

Наркы моделине көз каранды эмес Cool SQL оптималдаштыруу. 1-бөлүк

4. «Мааниси жок» предикаттарды жок кылуу

Ушундай эле маанисиз предикаттар (дээрлик) дайыма чындык. Сиз элестете тургандай, эгер сиз сурап жатсаңыз:

SELECT * FROM actor WHERE 1 = 1;

...анда маалымат базалары аны иш жүзүндө аткарbyte, бирок жөн гана көңүл бурбай калат. Мен бир жолу Stack Overflow боюнча бул суроого жооп бергем , ошондуктан бул макаланы жазууну чечтим. Мен муну сынап көрүүнү окурманга көнүгүү катары калтырам, бирок предикат бир аз "маанисиз" болсо эмне болот? Мисалы:

SELECT * FROM film WHERE release_year = release_year;

Сиз чындап эле ар бир сап үчүн маанини салыштырышыңыз керекпи? Жок, бул предикат ЖАЛГАН боло турган эч кандай маани жок , туурабы? Бирок биз дагы эле текшеришибиз керек. Предикат FALSE га барабар боло албаса да, үч маанилүү логикага байланыштуу, ал бардык жерде NULLге барабар болушу мүмкүн . RELEASE_YEAR тилкеси нөл болот жана саптардын биринде RELEASE_YEAR IS NULL болсо , NULL = NULL натыйжасы NULL болот жана сап жок кылынышы керек. Ошентип, өтүнүч төмөнкүдөй болот:

SELECT * FROM film WHERE release_year IS NOT NULL;

Кайсы маалымат базалары муну жасайт?

DB2

Ооба!

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

Бул уят, бирок MySQL, дагы бир жолу, предикаттарды аткаруу пландарына киргизбейт, андыктан MySQL бул оптималдаштырууну ишке ашырарын аныктоо бир аз татаал. Сиз өндүрүмдүүлүктү баалоону жүргүзүп, масштабдуу салыштыруулар жасалып жатканын көрө аласыз. Же индексти кошо аласыз:

CREATE INDEX i_release_year ON film (release_year);

Анын ордуна төмөнкү суроо-талаптар боюнча пландарды алыңыз:

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

Эгерде оптималдаштыруу иштесе, анда эки суроонун планы болжол менен бирдей болушу керек. Бирок бул учурда андай эмес:

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

Көрүнүп тургандай, биздин эки сурообуз POSIBLE_KEYS жана FILTERED мамычаларынын маанилери боюнча олуттуу айырмаланат . Ошентип, мен MySQL муну оптималдаштырbyte деп акылга сыярлык божомолдомокмун.

Oracle

Ооба!

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

Тилекке каршы жок!

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

Пландар жана чыгымдар ар кандай. Тактап айтканда, кардиналдуулуктун баасын караңыз, бул такыр жакшы эмес, ал эми бул предикат:

SELECT * FROM film WHERE release_year IS NOT NULL;

алда канча жакшы натыйжаларды берет:

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

Bummer!

SQL Server

Кызык жери, SQL Server да муну кылbyte окшойт:

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

Бирок, пландын сырткы көрүнүшү боюнча кардиналдуу баа туура, ошондой эле наркы. Бирок SQL Server менен болгон тажрыйбамда, бул учурда оптималдаштыруу болбойт деп айтаар элем, анткени SQL Server планда иш жүзүндө аткарылган предикатты көрсөтөт (эмне үчүн экенин билүү үчүн, төмөндөгү CHECK чектөө мисалдарын карап көрүңүз ). NOT NULL мамычаларындагы "маанисиз" предикаттар жөнүндө эмне айтууга болот ? RELEASE_YEAR аныкталбагандыктан , жогорудагы өзгөртүү гана керек болчу . Эгер сиз бир эле маанисиз суроону, мисалы, FILM_ID тилкесинде иштетсеңиз эмне болот ?

SELECT * FROM film WHERE film_id = film_id

Эми ал эч кандай предикатка туура келбейби? Же жок дегенде ушундай болушу керек. Бирок ошондойбу?

DB2

Ооба!

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

Эч кандай предикаттар колдонулbyte жана биз бардык тасмаларды тандайбыз.

MySQL

Ооба! (Кайрадан, бorмдүү божомол)

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

КОШУМЧА тилке азыр бош экенине көңүл буруңуз , бизде КАЙДА деген пункт такыр жок дегендей !

Oracle

Ооба!

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

Дагы, эч кандай предикаттар колдонулbyte.

PostgreSQL

Ваа, кайра жок!

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

чыпка колдонулат жана кардиналдуулук упай дагы эле 5. Bummer!

SQL Server

Жана бул жерде дагы жок!

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

Жыйынтык

Бул жөнөкөй оптималдаштыруу сыяктуу көрүнөт, бирок ал бардык DBMSларда колдонулbyte; атап айтканда, таң калыштуусу, ал SQL serverинде колдонулbyte!

5. EXISTS подсуроолорундагы проекциялар

Кызыгы, мен алар жөнүндө мастер-классымда дайыма сурашат, мында мен SELECT * адатта эч кандай жакшылыкка алып келбейт деген көз карашымды коргойм. Суроо: EXISTS ички сурамында SELECT * колдонууга болобу ? Мисалы, тасмаларда ойногон актёрлорду табыш керек болсо...

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
)

Жана Ооба. мүмкүн. Жылдызча суроо-талапка таасирин тийгизбейт. Буга кантип ишенүүгө болот? Төмөнкү суроону карап көрүңүз:

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

Бул маалымат базаларынын баары нөл катага бөлүү жөнүндө кабарлайт. Кызыктуу фактыга көңүл буруңуз: MySQLде биз нөлгө бөлгөнүбүздө катанын ордуна NULL алабыз , ошондуктан уруксат берилбеген башка нерсени кылышыбыз керек. Эми, эгерде биз жогоруда айтылгандардын ордуна төмөнкү суроолорду аткарсак эмне болот?

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

Азыр маалымат базаларынын бири да ката кайтарbyte. Алардын баары TRUE же 1 деп кайтарышат . Бул биздин маалымат базаларыбыздын эч бири EXISTS кичи сурамынын проекциясын (б.а. SELECT пунктун ) иш жүзүндө баалай алbyte дегенди билдирет . SQL Server, мисалы, төмөнкү планды көрсөтөт:

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

Көрүнүп тургандай, CASE туюнтмасы туруктууга айландырылды жана подсуроо жок кылынды. Башка маалымат базалары подсуроону планда сактайт жана проекция жөнүндө эч нерсе айтпайт, андыктан Oracle'дагы баштапкы суроо планын дагы бир жолу карап көрөлү:

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

Жогорудагы суроо планы мындай көрүнөт:

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

Биз Id=3 проекциясы тууралуу маалыматты байкайбыз . Чынында, биз FILM_ACTOR жадыбалына кире албайбыз , анткени кереги жок. EXISTS предикатын бир ACTOR_ID тилкесинде чет өлкөлүк ачкыч индексин колдонуу менен аткарууга болот - бул сурам үчүн зарыл болгон нерселердин бардыгы - биз SELECT * деп жазганыбызга карабастан .

Жыйынтык

Бактыга жараша, биздин бардык маалымат базаларыбыз EXISTS подсуроолорунан проекцияны алып салышат : 3-бөлүгүн күтө туруңуз , анда биз башка сонун SQL оптималдаштырууларын талкуулайбыз.