Xərc modelindən asılı olmayan sərin SQL optimallaşdırmaları. 2-ci hissə

Xərc modelindən asılı olmayan sərin SQL optimallaşdırmaları. 1-ci hissə

4. “Mənasız” predikatların aradan qaldırılması

Eyni dərəcədə mənasız olanlar (demək olar ki) həmişə doğru olan predikatlardır. Təsəvvür edə bildiyiniz kimi, soruşsanız:

SELECT * FROM actor WHERE 1 = 1;

...onda verilənlər bazaları onu əslində icra etməyəcək, sadəcə olaraq ona məhəl qoymayacaqlar. Bir dəfə Stack Overflow-da bununla bağlı suala cavab verdim və buna görə də bu məqaləni yazmaq qərarına gəldim. Bunu sınamağı bir məşq kimi oxucunun ixtiyarına buraxacağam, lakin predikat bir az daha az "mənasız" olarsa nə olar? Misal üçün:

SELECT * FROM film WHERE release_year = release_year;

Həqiqətən, hər bir sıra üçün dəyəri özü ilə müqayisə etməlisiniz? Xeyr, bu predikatın FALSE olacağı heç bir dəyər yoxdur , elə deyilmi? Ancaq hələ də yoxlamaq lazımdır. Predikat FALSE -ə bərabər ola bilməsə də, yenə üç dəyərli məntiqə görə hər yerdə NULL -ə bərabər ola bilər . RELEASE_YEAR sütunu null edilə bilər və sətirlərdən hər hansı birində RELEASE_YEAR IS NULL varsa , NULL = NULL NULL ilə nəticələnir və sətir silinməlidir. Beləliklə, sorğu aşağıdakı kimi olur:

SELECT * FROM film WHERE release_year IS NOT NULL;

Bunu hansı verilənlər bazası edir?

DB2

Bəli!

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

Bu ayıbdır, lakin MySQL yenə də predikatları icra planlarına uyğunlaşdırmır, ona görə də MySQL-in bu xüsusi optimallaşdırmanı həyata keçirib-etdirmədiyini öyrənmək bir az çətin məsələdir. Siz performansın qiymətləndirilməsini həyata keçirə və hər hansı geniş miqyaslı müqayisələrin aparıldığını görə bilərsiniz. Və ya bir indeks əlavə edə bilərsiniz:

CREATE INDEX i_release_year ON film (release_year);

Və qarşılığında aşağıdakı sorğular üçün planlar əldə edin:

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

Optimallaşdırma işləyirsə, hər iki sorğunun planları təxminən eyni olmalıdır. Ancaq bu vəziyyətdə belə deyil:

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ördüyünüz kimi, bizim iki sorğumuz POSIBLE_KEYS və FILTERED sütunlarının dəyərlərində əhəmiyyətli dərəcədə fərqlənir . Beləliklə, mən ağlabatan bir təxmin edərdim ki, MySQL bunu optimallaşdırmır.

Oracle

Bəli!

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

Təəssüf ki, heç bir!

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

Planlar və xərclər dəyişir. Məhz, kardinallığın qiymətləndirilməsinə baxın, bu heç də yaxşı deyil, halbuki bu predikat:

SELECT * FROM film WHERE release_year IS NOT NULL;

daha yaxşı nəticələr verir:

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

Bummer!

SQL Server

Qəribədir ki, SQL Server bunu da etmir:

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

Bununla belə, planın görünüşünə əsaslanaraq, qiymət kimi kardinallıq qiymətləndirməsi düzgündür. Lakin SQL Server ilə təcrübəmdə deyərdim ki, bu halda heç bir optimallaşdırma baş vermir, çünki SQL Server planda həqiqətən icra edilmiş predikatı göstərəcək (niyə olduğunu görmək üçün aşağıdakı CHECK məhdudiyyət nümunələrinə nəzər salın ). DEYİL NULL sütunlarındakı "mənasız" predikatlar haqqında nə demək olar ? RELEASE_YEAR qeyri-müəyyən ola bildiyi üçün yuxarıdakı çevrilmə yalnız zəruri idi . Eyni mənasız sorğunu, məsələn, FILM_ID sütununda icra etsəniz, nə baş verir ?

SELECT * FROM film WHERE film_id = film_id

İndi heç bir predikata uyğun gəlmirmi? Və ya heç olmasa belə olmalıdır. Amma elədir?

DB2

Bəli!

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

Heç bir predikat tətbiq edilmir və biz bütün filmləri seçirik.

MySQL

Bəli! (Yenə savadlı təxmin)

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

Diqqət yetirin ki, EXTRA sütunu indi boşdur, sanki bizdə ümumiyyətlə HARADA bəndi yoxdur !

Oracle

Bəli!

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

Yenə də heç bir predikatlar tətbiq edilmir.

PostgreSQL

Vay, yenə yox!

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

Filtr tətbiq olundu və kardinallıq balı hələ də 5-dir. Bummer!

SQL Server

Və burada yenə yox!

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

Xülasə

Sadə bir optimallaşdırma kimi görünür, lakin o, bütün DBMS-lərdə istifadə edilmir; xüsusilə, qəribə də olsa, SQL Serverdə istifadə edilmir!

5. EXISTS alt sorğularındakı proqnozlar

Maraqlıdır ki, master-klassımda məndən həmişə onlar haqqında soruşurlar, burada SELECT *-in adətən heç bir yaxşılığa səbəb olmadığı fikrini müdafiə edirəm . Sual belədir: EXISTS alt sorğusunda SELECT * istifadə etmək mümkündürmü ? Məsələn, filmlərdə oynayan aktyorları tapmaq lazımdırsa...

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
)

Və cavab... bəli. Bacarmaq. Ulduz işarəsi sorğuya təsir etmir. Buna necə əmin ola bilərsiniz? Aşağıdakı sorğunu nəzərdən keçirin:

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

Bütün bu verilənlər bazası sıfır səhvə bölünmə barədə məlumat verir. Maraqlı bir fakta diqqət yetirin: MySQL-də biz sıfıra böldükdə xəta yerinə NULL alırıq , ona görə də başqa bir qanunsuz hərəkət etməliyik. İndi yuxarıdakıların yerinə aşağıdakı sorğuları yerinə yetirsək nə olar?

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

İndi verilənlər bazalarından heç biri xəta qaytarmır. Onların hamısı TRUE və ya 1 qaytarır . Bu o deməkdir ki, verilənlər bazalarımızın heç biri EXISTS alt sorğusunun proyeksiyasını (yəni SELECT bəndini ) həqiqətən qiymətləndirmir . SQL Server, məsələn, aşağıdakı planı göstərir:

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

Göründüyü kimi, CASE ifadəsi sabitə çevrildi və alt sorğu aradan qaldırıldı. Digər verilənlər bazaları alt sorğunu planda saxlayır və proyeksiya haqqında heç nə qeyd etmir, ona görə də Oracle-da orijinal sorğu planına bir daha nəzər salaq:

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

Yuxarıdakı sorğu planı belə görünü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

Proyeksiya haqqında məlumatı Id=3 -də müşahidə edirik . Əslində, biz FILM_ACTOR cədvəlinə belə daxil olmuruq, çünki ehtiyacımız yoxdur. EXISTS predikatı bir ACTOR_ID sütununda xarici açar indeksindən istifadə etməklə yerinə yetirilə bilər - bu sorğu üçün lazım olan hər şey - biz SELECT * yazmışıq .

Xülasə

Xoşbəxtlikdən, bütün verilənlər bazalarımız EXISTS alt sorğularından proyeksiyanı silir : Digər gözəl SQL optimallaşdırmalarını müzakirə edəcəyimiz 3-cü hissə üçün bizi izləyin .