Xarajat modeliga bog'liq bo'lmagan ajoyib SQL optimallashtirish. 2-qism

Xarajat modeliga bog'liq bo'lmagan ajoyib SQL optimallashtirish. 1-qism

4. "Ma'nosiz" predikatlarni yo'q qilish

Xuddi shunday ma'nosiz predikatlar (deyarli) har doim to'g'ri. Tasavvur qilganingizdek, agar siz so'rasangiz:

SELECT * FROM actor WHERE 1 = 1;

... keyin ma'lumotlar bazalari uni amalda bajarmaydi, balki uni e'tiborsiz qoldiradi. Men bir marta Stack Overflow-da bu haqda savolga javob berdim va shuning uchun men ushbu maqolani yozishga qaror qildim. Men buni sinab ko'rishni o'quvchiga mashq sifatida qoldiraman, lekin agar predikat biroz kamroq "ma'nosiz" bo'lsa nima bo'ladi? Masalan:

SELECT * FROM film WHERE release_year = release_year;

Haqiqatan ham har bir satr uchun qiymatni o'zi bilan solishtirish kerakmi? Yo'q, bu predikat FALSE bo'ladigan qiymat yo'q , to'g'rimi? Lekin biz hali ham tekshirishimiz kerak. Predikat FALSE ga teng bo'lmasa ham, u hamma joyda NULL ga teng bo'lishi mumkin , bu yana uch qiymatli mantiq tufayli. RELEASE_YEAR ustuni null bo'ladi va agar satrlardan birida RELEASE_YEAR IS NULL bo'lsa , NULL = NULL NULLga olib keladi va satr o'chirilishi kerak. Shunday qilib, so'rov quyidagicha bo'ladi:

SELECT * FROM film WHERE release_year IS NOT NULL;

Qaysi ma'lumotlar bazalari buni amalga oshiradi?

DB2

Ha!

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 uyat, lekin MySQL, yana, predikatlarni ijro rejalariga kiritmaydi, shuning uchun MySQL ushbu optimallashtirishni amalga oshiradimi yoki yo'qligini aniqlash biroz qiyin. Siz samaradorlikni baholashni amalga oshirishingiz va har qanday keng ko'lamli taqqoslashlar amalga oshirilayotganligini ko'rishingiz mumkin. Yoki indeks qo'shishingiz mumkin:

CREATE INDEX i_release_year ON film (release_year);

Buning evaziga quyidagi so'rovlar uchun rejalarni oling:

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

Agar optimallashtirish ishlayotgan bo'lsa, ikkala so'rovning rejalari taxminan bir xil bo'lishi kerak. Ammo bu holda bunday emas:

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

Ko'rib turganingizdek, bizning ikkita so'rovimiz POSIBLE_KEYS va FILTERED ustunlari qiymatlarida sezilarli darajada farq qiladi . Shunday qilib, MySQL buni optimallashtirmaydi, deb o'rtacha taxmin qilardim.

Oracle

Ha!

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

Afsuski yo `q!

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

Rejalar va xarajatlar har xil. Ya'ni, kardinallikni baholashga qarang, bu mutlaqo yaxshi emas, shu bilan birga bu predikat:

SELECT * FROM film WHERE release_year IS NOT NULL;

ancha yaxshi natijalar beradi:

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

Bummer!

SQL Server

Ajabo, SQL Server ham buni qilmaydi:

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

Biroq, rejaning ko'rinishiga asoslanib, xarajat kabi kardinallikni baholash to'g'ri. Ammo SQL Server bilan ishlash tajribamda shuni aytmoqchimanki, bu holda optimallashtirish sodir bo'lmaydi, chunki SQL Server rejada haqiqatda bajarilgan predikatni ko'rsatadi (nima uchun ekanligini bilish uchun quyidagi CHECK cheklash misollarini ko'rib chiqing ). NOT NULL ustunlaridagi "ma'nosiz" predikatlar haqida nima deyish mumkin ? Yuqoridagi konvertatsiya faqat zarur edi, chunki RELEASE_YEARni aniqlab bo‘lmaydi. Agar siz bir xil ma'nosiz so'rovni, masalan, FILM_ID ustunida bajarsangiz nima bo'ladi ?

SELECT * FROM film WHERE film_id = film_id

Endi u umuman predikatga mos kelmaydimi? Yoki hech bo'lmaganda shunday bo'lishi kerak. Lekin shundaymi?

DB2

Ha!

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

Predikatlar umuman qo'llanilmaydi va biz barcha filmlarni tanlaymiz.

MySQL

Ha! (Yana, bilimli taxmin)

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

E'tibor bering, EXTRA ustuni endi bo'sh, go'yo bizda WHERE bandi umuman yo'q !

Oracle

Ha!

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

Shunga qaramay, hech qanday predikatlar qo'llanilmaydi.

PostgreSQL

Voy, yana yo'q!

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

Filtr qo'llangan va kardinallik reytingi hali ham 5. Bummer!

SQL Server

Va yana yo'q!

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

Xulosa

Bu oddiy optimallashtirish kabi ko'rinadi, lekin u barcha DBMSlarda qo'llanilmaydi; ayniqsa, g'alati, u SQL Serverda ishlatilmaydi!

5. EXISTS pastki so'rovlaridagi proyeksiyalar

Qizig'i shundaki, mendan master-klassda doim ular haqida so'rashadi, u erda men SELECT * odatda hech qanday yaxshilikka olib kelmaydi degan nuqtai nazarni himoya qilaman. Savol: EXISTS quyi so'rovida SELECT * dan foydalanish mumkinmi ? Masalan, filmlarda o'ynagan aktyorlarni topish kerak bo'lsa...

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
)

Va javob ... ha. mumkin. Yulduzcha so'rovga ta'sir qilmaydi. Bunga qanday ishonch hosil qilish mumkin? Quyidagi so'rovni ko'rib chiqing:

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

Ushbu ma'lumotlar bazalarining barchasi nolga bo'lingan xato haqida xabar beradi. Qiziqarli faktga e'tibor bering: MySQLda biz nolga bo'linganimizda xato o'rniga NULLni olamiz , shuning uchun biz yana bir noqonuniy harakat qilishimiz kerak. Endi yuqoridagi so'rovlar o'rniga quyidagi so'rovlarni bajarsak nima bo'ladi?

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

Endi ma'lumotlar bazalarining hech biri xato qaytarmaydi. Ularning barchasi TRUE yoki 1 ni qaytaradi . Bu shuni anglatadiki, bizning ma'lumotlar bazalarimizdan hech biri EXISTS quyi so'rovining proyeksiyasini (ya'ni SELECT bandi ) haqiqatda baholamaydi . Masalan, SQL Server quyidagi rejani ko'rsatadi:

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

Ko'rib turganingizdek, CASE ifodasi doimiyga aylantirildi va pastki so'rov o'chirildi. Boshqa ma'lumotlar bazalari pastki so'rovni rejada saqlaydi va proyeksiya haqida hech narsa aytmaydi, shuning uchun Oracle'dagi asl so'rovlar rejasini yana bir bor ko'rib chiqamiz:

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

Yuqoridagi so'rov rejasi quyidagicha ko'rinadi:

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

Biz proyeksiya haqidagi ma'lumotni Id=3 da kuzatamiz . Aslida, biz FILM_ACTOR jadvaliga ham kira olmaymiz, chunki bizga kerak emas. EXISTS predikati bitta ACTOR_ID ustunidagi xorijiy kalit indeksi yordamida bajarilishi mumkin - bu so'rov uchun zarur bo'lgan barcha narsa - biz SELECT * deb yozgan bo'lsak ham .

Xulosa

Yaxshiyamki, bizning barcha ma'lumotlar bazalarimiz EXISTS pastki so'rovlaridan proyeksiyani olib tashlaydi : 3-qismni kuzatib boring , u yerda biz boshqa ajoyib SQL optimallashtirishlarini muhokama qilamiz.