Оптимизатсияи сард 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

Тавре ки шумо мебинед, ду дархости мо дар арзишҳои сутунҳои POSSIBLE_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 дар зер нигаред ). Дар бораи предикатҳои "бемаъно" дар сутунҳои NO 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

Аҳамият диҳед, ки сутуни EXTRA ҳоло холӣ аст, гӯё мо умуман банди 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]))

Хулоса

Ин як оптимизатсияи оддӣ ба назар мерасад, аммо он дар ҳама DBMS истифода намешавад; алалхусус, аҷиб он аст, ки он дар SQL Server истифода намешавад!

5. Пешгӯиҳо дар зерпурсишҳои EXISTS

Ҷолиб он аст, ки ман ҳамеша дар бораи онҳо дар мастер-класси худ мепурсам, ки ман дар он нуқтаи назарро ҳимоя мекунам, ки SELECT * одатан ба ягон фоида оварда намерасонад. Савол ин аст: оё SELECT * -ро дар зерпурсиш EXISTS истифода бурдан мумкин аст ? Масалан, агар актёронеро пайдо кунем, ки дар филмҳо бозӣ кардаанд...

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-ро бармегардонанд . Ин маънои онро дорад, ки ҳеҷ яке аз пойгоҳи додаҳои мо воқеан проексияи (яъне банди SELECT ) зерпурсиш EXISTS -ро арзёбӣ намекунад . Масалан, 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-ро муҳокима хоҳем кард.