Шығын үлгісіне тәуелді емес керемет SQL оңтайландырулары. 2-бөлім

Шығын үлгісіне тәуелді емес керемет SQL оңтайландырулары. 1 бөлім

4. «Мағынасыз» предикаттарды жою

Бірдей мағынасыз предикаттар (дерлік) әрқашан шындық. Сіз ойлағандай, егер сіз сұрасаңыз:

SELECT * FROM actor WHERE 1 = 1;

...онда дерекқорлар оны іс жүзінде орындамайды, бірақ жай ғана оны елемейді. Мен бір рет Stack Overflow-те бұл туралы сұраққа жауап бердім , сондықтан мен осы мақаланы жазуды шештім. Мен мұны жаттығу ретінде тексеруді оқырманға қалдырамын, бірақ предикат сәл «мағынасыз» болса не болады? Мысалы:

SELECT * FROM film WHERE release_year = release_year;

Әрбір жол үшін мәнді өзімен салыстыру керек пе? Жоқ, бұл предикат FALSE болатын мән жоқ , солай ма? Бірақ біз оны әлі де тексеруіміз керек. Предикат 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 мұны оңтайландырмайды деп ақылға қонымды болжам жасаймын.

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)

Жаман!

SQL serverі

Бір қызығы, SQL Server мұны да жасамайды:

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

Ешқандай предикаттар қолданылмайды және біз барлық фильмдерді таңдаймыз.

MySQL

Иә! (Тағы да білімді болжам)

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

Назар аударыңыз, ҚОСЫМША бағаны енді бос, бізде WHERE сөйлемі мүлдем жоқ сияқты !

Oracle

Иә!

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

Қайтадан, ешқандай предикаттар қолданылмайды.

PostgreSQL

Уау, тағы жоқ!

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

Сүзгі қолданылды және негізгі көрсеткіш әлі де 5. Баммер!

SQL serverі

Және тағы да жоқ!

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

Түйіндеме

Бұл қарапайым оңтайландыру сияқты көрінеді, бірақ ол барлық ДҚБЖ-да қолданылмайды; атап айтқанда, біртүрлі, ол SQL serverінде қолданылмайды!

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

Енді дерекқорлардың ешқайсысы қатені қайтармайды. Олардың барлығы TRUE немесе 1 қайтарады . Бұл біздің дерекқорларымыздың ешқайсысы EXISTS ішкі сұрауының проекциясын (яғни, SELECT сөйлемі ) нақты бағаламайтынын білдіреді . Мысалы, 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 ішкі сұрауларынан проекцияны жояды : Басқа тамаша SQL оңтайландыруларын талқылайтын 3-ші бөлімді күтіңіз .