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

Faqat metama'lumotlar (ya'ni cheklovlar) va so'rovning o'zi asosida amalga oshirilishi mumkin bo'lgan beshta oddiy optimallashtirish Biz sizga Lukas Ederning ma'lumotlar bazalari va SQL haqida umumiy tushunchaga ega bo'lganlar uchun mo'ljallangan maqolasining moslashuvini, shuningdek, ma'lumotlar bazasi bo'yicha ba'zi amaliy tajribani taklif qilamiz. . Xarajatlarni optimallashtirish aslida zamonaviy ma'lumotlar bazalarida SQL so'rovlarini optimallashtirishning standart usuli hisoblanadi. Shuning uchun 3GLda (uchinchi avlod dasturlash tillari) murakkab algoritmni qo'lda yozish juda qiyin , uning ishlashi zamonaviy optimallashtiruvchi tomonidan yaratilgan dinamik hisoblangan bajarish rejasidan oshib ketadi. Bugun biz xarajatlarni optimallashtirish, ya'ni ma'lumotlar bazasining xarajatlar modeliga asoslangan optimallashtirishni muhokama qilmaymiz. Biz ancha sodda optimallashtirishlarni ko'rib chiqamiz. Faqat metadata (ya'ni cheklovlar) va so'rovning o'zi asosida amalga oshirilishi mumkin bo'lganlar. Odatda ma'lumotlar bazasi uchun ularni amalga oshirish Nyuton binomi emas, chunki bu holda har qanday optimallashtirish indekslar, ma'lumotlar hajmi va ma'lumotlarni taqsimlashning egriligidan qat'i nazar, yaxshiroq bajarish rejasiga olib keladi. "Nyuton binomial emas" optimallashtirishni amalga oshirish qanchalik oson ekanligi ma'nosida emas, balki uni amalga oshirish kerakmi yoki yo'qmi. Ushbu optimallashtirishlar [ma'lumotlar bazasi uchun] keraksiz, qo'shimcha ishlarni yo'q qiladi ( men allaqachon yozgan keraksiz, talab qilinadigan ishlardan farqli o'laroq ).

Ushbu optimallashtirishlar nima uchun ishlatiladi?

Ularning aksariyati quyidagilar uchun ishlatiladi:

• so'rovlardagi xatolarni tuzatish;
• ko'rinishlardan ma'lumotlar bazasi ko'rinish mantig'ini amalda bajarmasdan qayta foydalanishga ruxsat berish.

Birinchi holda, shunday deyish mumkin: "Nima bo'lsa, davom eting va bu ahmoqona SQL so'rovini tuzating." Lekin hech qachon xato qilmagan odam menga tosh otsin. Ikkinchi holat ayniqsa qiziq: u bizga bir nechta qatlamlarda qayta ishlatilishi mumkin bo'lgan ko'rinishlar va jadval funktsiyalarining murakkab kutubxonalarini yaratish qobiliyatini beradi.

Ishlatilgan ma'lumotlar bazalari

Ushbu maqolada biz eng ko'p ishlatiladigan beshta ma'lumotlar bazasida ( ma'lumotlar bazasi reytingiga ko'ra ) 10 ta SQL optimallashtirishni solishtiramiz :

• Oracle 12.2;
• MySQL 8.0.2;
• SQL Server 2014;
• PostgreSQL 9.6;
• DB2 LUW 10.5.

Yana bir reyting buni deyarli aks ettiradi. Odatdagidek, ushbu maqolada men Sakila ma'lumotlar bazasini so'rayman . Mana shu o'n turdagi optimallashtirish ro'yxati:

1. tranzitiv yopilish;
2. imkonsiz predikatlar va keraksiz jadval chaqiruvlari;
3. JOINni yo'q qilish;
4. "ma'nosiz" predikatlarni yo'q qilish;
5. EXISTS pastki so'rovlaridagi proektsiyalar;
6. predikatlarni birlashtirish;
7. shubhasiz bo'sh to'plamlar;
8. cheklovlar CHECK;
9. keraksiz refleksli ulanishlar;
10. Pushdown predikatlar

Bugun biz pp.ni muhokama qilamiz. 1-3, ikkinchi qismda - 4 va 5, 3-qismda - 6-10.

1. Transitiv yopilish

Keling, oddiyroq narsadan boshlaylik: o'tish davri yopilishi . Bu tenglik operatori kabi ko'plab matematik operatsiyalarga taalluqli ahamiyatsiz tushunchadir. Bu holda uni quyidagicha shakllantirish mumkin: agar A = B va B = C bo'lsa, u holda A = C.

Qiyin emas, to'g'rimi? Ammo bu SQL optimizatorlari uchun qiziqarli ta'sirga ega. Keling, bir misolni ko'rib chiqaylik. ACTOR_ID = 1 bo'lgan barcha filmlarni chiqaramiz:

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE a.actor_id = 1;

Natija quyidagicha:

FIRST_NAME      LAST_NAME  FILM_ID
PENELOPE        GUINESS    1
PENELOPE        GUINESS    23
PENELOPE        GUINESS    25
PENELOPE        GUINESS    106
PENELOPE        GUINESS    140
PENELOPE        GUINESS    166
...

Keling, Oracle DBMS misolida ushbu so'rovni bajarish rejasini ko'rib chiqamiz:

--------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |       |
|   1 |  NESTED LOOPS                |               |    19 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACTOR         |     1 |
|*  3 |    INDEX UNIQUE SCAN         | PK_ACTOR      |     1 |
|*  4 |   INDEX RANGE SCAN           | PK_FILM_ACTOR |    19 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."ACTOR_ID"=1)
   4 - access("FA"."ACTOR_ID"=1)

Bu erda predikatlar bo'limi ayniqsa qiziqarli. ACTOR_ID = 1 predikati tranzitiv yopilish tufayli ham ACTOR jadvali, ham FILM_ACTOR jadvali uchun amal qiladi. Agar:

• A.ACTOR_ID = 1 (из предиката WHERE) и…
• A.ACTOR_ID = FA.ACTOR_ID (из предиката ON)
  То:
• FA.ACTOR_ID = 1

Keyinchalik murakkab so'rovlar uchun bu juda yaxshi natijalar beradi. Xususan, kardinallikni baholashning aniqligi sezilarli darajada oshadi, chunki quyidagi so'rovda bo'lgani kabi, masalan, aktyorlar tomonidan suratga olingan filmlarning o'rtacha soniga emas, balki predikatning o'ziga xos doimiy qiymatiga qarab taxminlarni tanlash mumkin bo'ladi. bir xil natija):

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE first_name = 'PENELOPE'
AND last_name = 'GUINESS'

Uning rejasi:

----------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |       |
|   1 |  NESTED LOOPS                        |                     |     2 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR               |     1 |
|*  3 |    INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME |     3 |
|*  4 |   INDEX RANGE SCAN                   | PK_FILM_ACTOR       |    27 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."FIRST_NAME"='PENELOPE')
   3 - access("A"."LAST_NAME"='GUINESS')
   4 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")

Ko'rib turganingizdek, FILM_ACTOR jadvalidagi qatorlar soni ortiqcha baholangan, NESTED LOOP esa kam baholangan. Mana bir nechta qiziqarli qadriyatlar:

SELECT count(*) FROM film_actor WHERE actor_id = 1;
SELECT avg(c) FROM (
  SELECT count(*) c FROM film_actor GROUP BY actor_id
);

Natija:

19
27.315

Hisob-kitoblar shu erdan kelib chiqadi. Agar ma'lumotlar bazasi ACTOR_ID = 1 haqida gapirayotganimizni bilsa, u ushbu aktyor uchun filmlar soni bo'yicha statistik ma'lumotlarni to'plashi mumkin . Agar shunday boʻlmasa (chunki standart statistik maʼlumotlarni yigʻish mexanizmi FIRST_NAME/LAST_NAME bilan ACTOR_ID bogʻliq boʻlmasa), biz barcha aktyorlar uchun oʻrtacha filmlar sonini olamiz . Bu alohida holatda oddiy, ahamiyatsiz xato, lekin murakkab so'rovda u yanada ko'payishi, to'planishi va so'rovga (rejada yuqoriroq) noto'g'ri JOIN tanloviga olib kelishi mumkin. Iloji bo'lsa, tranzitiv yopilishdan foydalanish uchun birlashmalaringizni va oddiy predikatlaringizni loyihalashtiring. Bu xususiyatni boshqa qaysi ma'lumotlar bazalari qo'llab-quvvatlaydi?

DB2

Ha!

Explain Plan
-----------------------------------------------------------
ID | Operation              |                 Rows | Cost
 1 | RETURN                 |                      |   13
 2 |  NLJOIN                |              27 of 1 |   13
 3 |   FETCH ACTOR          |     1 of 1 (100.00%) |    6
 4 |    IXSCAN PK_ACTOR     |   1 of 200 (   .50%) |    0
 5 |   IXSCAN PK_FILM_ACTOR | 27 of 5462 (   .49%) |    6
Predicate Information
 4 - START (Q2.ACTOR_ID = 1)
      STOP (Q2.ACTOR_ID = 1)
 5 - START (1 = Q1.ACTOR_ID)
      STOP (1 = Q1.ACTOR_ID)

Aytgancha, agar siz bu kabi ajoyib ijro rejalarini yoqtirsangiz, Markus Winandning skriptini tekshiring .

MySQL

Afsuski, MySQL ijro rejalari ushbu turdagi tahlil uchun mos emas. Chiqish ma'lumotlarida predikatning o'zi yo'q:

ID  SELECT TYPE  TABLE  TYPE   REF    ROWS
------------------------------------------
1   SIMPLE       a      const  const  1
1   SIMPLE       fa     ref    const  19

Lekin const ning REF ustunida ikki marta ko'rsatilganligi ikkala jadval ham doimiy qiymatni qidirayotganligini ko'rsatadi. Shu bilan birga, FIRST_NAME/LAST_NAME bilan so‘rov rejasi quyidagicha ko‘rinadi:

ID  SELECT TYPE  TABLE  TYPE   REF         ROWS
-----------------------------------------------
1   SIMPLE       a      ref    const       3
1   SIMPLE       fa     ref    a.actor_id  27

Ko'rib turganingizdek, REF endi JOIN predikatidan ustunga murojaat qiladi. Kardinallik reytingi Oracle bilan deyarli bir xil. Ha, MySQL o'tish davrini yopishni ham qo'llab-quvvatlaydi.

PostgreSQL

Ha!

QUERY PLAN
------------------------------------------------------------------------------------
Nested Loop  (cost=4.49..40.24 rows=27 width=15)
  ->  Seq Scan on actor a  (cost=0.00..4.50 rows=1 width=17)
        Filter: (actor_id = 1)
  ->  Bitmap Heap Scan on film_actor fa  (cost=4.49..35.47 rows=27 width=4)
        Recheck Cond: (actor_id = 1)
        ->  Bitmap Index Scan on film_actor_pkey  (cost=0.00..4.48 rows=27 width=0)
              Index Cond: (actor_id = 1)

SQL Server

Ha!

|--Nested Loops(Inner Join)
     |--Nested Loops(Inner Join)
     |    |--Index Seek (SEEK:([a].[actor_id]=(1)))
     |    |--RID Lookup
     |--Index Seek (SEEK:([fa].[actor_id]=(1)))

Xulosa

Bizning barcha ma'lumotlar bazalarimiz tranzitiv yopilishni qo'llab-quvvatlaydi. Biroq, maqolaning keyingi qismida # 6 ni kuting. Barcha ma'lumotlar bazalari bajara olmaydigan o'tish davri yopilishining murakkab holatlari mavjud.

2. Mumkin bo'lmagan predikatlar va keraksiz jadval chaqiruvlari

Bu butunlay ahmoqona optimallashtirish, lekin nima uchun emas? Agar foydalanuvchilar imkonsiz predikatlarni yozsalar, ularni bajarishning nima keragi bor? Mana bir nechta misollar:

-- "Очевидный"
SELECT * FROM actor WHERE 1 = 0
-- "Хитрый"
SELECT * FROM actor WHERE NULL = NULL

Birinchi so'rov, shubhasiz, hech qanday natijani qaytarmaydi, lekin xuddi shu bayonot ikkinchisiga to'g'ri keladi. Axir, NULL IS NULL har doim TRUE bo'lsa-da, NULL = NULL hisoblash natijasi NULL bo'lib, uch qiymatli mantiqqa ko'ra , FALSE ga ekvivalentdir. Bu o'z-o'zidan tushunarli, shuning uchun keling, qaysi ma'lumotlar bazalari ushbu optimallashtirishni amalga oshirishini aniqlaylik.

DB2

Ha!

Explain Plan
-----------------------------------
ID | Operation      |   Rows | Cost
 1 | RETURN         |        |    0
 2 |  TBSCAN GENROW | 0 of 0 |    0

Ko'rib turganingizdek, ACTOR jadvaliga kirish rejadan butunlay chiqarib tashlangan. U faqat nol qatorlarni yaratuvchi GENROW operatsiyasini o'z ichiga oladi. Mukammal.

MySQL

Ha!

ID  SELECT TYPE  TABLE   EXTRAS
-----------------------------------------
1   SIMPLE         Impossible WHERE

Bu safar MySQL bizga imkonsiz WHERE bandi haqida ma'lumot berdi. Rahmat! Bu, ayniqsa, boshqa ma'lumotlar bazalariga nisbatan tahlilni ancha osonlashtiradi.

Oracle

Ha!

---------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      0 |
|*  1 |  FILTER            |       |      1 |        |      0 |
|   2 |   TABLE ACCESS FULL| ACTOR |      0 |    200 |      0 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)

Ko'ramizki, rejada ACTOR jadvaliga kirish hali ham eslatib o'tiladi va kutilayotgan qatorlar soni hali ham 200 ta, lekin Id=1 bo'lgan filtrlash operatsiyasi (FILTER) ham mavjud, bu erda hech qachon TRUE bo'lmaydi. Oracle standart SQL mantiqiy ma'lumotlar turini yoqtirmasligi sababli , Oracle rejada FALSE o'rniga NULL IS NOT NULL qiymatini ko'rsatadi. Oh, yaxshi ... Lekin jiddiy, bu predikatga qarang. Men 1000 qatorli pastki daraxt va juda yuqori xarajat qiymatlari bilan ijro rejalarini disk raskadrovka qilish imkoniyatiga ega bo'ldim, faqat NULL IS NOLL NOLL filtri tomonidan butun pastki daraxt "kesilgan"ligini aniqladim. Bir oz tushkunlikka tushaman, sizga aytaman.

PostgreSQL

Ha!

QUERY PLAN
-------------------------------------------
Result  (cost=0.00..0.00 rows=0 width=228)
  One-Time Filter: false

Allaqachon yaxshiroq. Hech qanday zerikarli AKTOR jadvali qo'ng'iroqlari va toza kichik FALSE predikati.

SQL Server?

Ha!

|--Constant Scan

SQL Server buni " doimiy skanerlash" deb ataydi, bu DB2 ga o'xshash hech narsa sodir bo'lmaydigan skanerlashdir. Bizning barcha ma'lumotlar bazalarimiz imkonsiz predikatlarni istisno qilishi mumkin:

3. JOINlarni yo'q qiling

Oldingi bo'limda biz bitta jadvalli so'rovlarda jadvalga keraksiz kirishlarni kuzatdik. Ammo JOIN bir nechta jadvalga kirishni talab qilmasa nima bo'ladi? Blogimning oldingi postida JOINni yo'q qilish haqida allaqachon yozgan edim . SQL dvigateli so'rov turiga, asosiy va tashqi kalitlarning mavjudligiga qarab, ma'lum bir JOIN haqiqatda berilgan so'rovda zarurligini yoki uni yo'q qilish so'rov semantikasiga ta'sir qilmasligini aniqlashga qodir. Keyingi uchta misolning barchasida JOIN kerak emas. Ichki ...-dan birga qo'shilish NOT NULL tashqi kalitiga ega bo'lgan holda o'chirilishi mumkin. Buning o'rniga:

SELECT first_name, last_name
FROM customer c
JOIN address a ON c.address_id = a.address_id

Ma'lumotlar bazasi quyidagilarni amalga oshirishi mumkin:

SELECT first_name, last_name
FROM customer c

"...-to-one" turidagi INNER JOIN o'rnini bosish mumkin bo'lgan tashqi kalit mavjud bo'lsa, almashtirish mumkin. Yuqoridagi so'rov chet el kaliti NOT NULL chekloviga bo'ysunsa ishlaydi. Agar yo'q bo'lsa, masalan, ushbu so'rovda bo'lgani kabi:

SELECT title
FROM film f
JOIN language l ON f.original_language_id = l.language_id

keyin JOIN hali ham o'chirilishi mumkin, lekin siz NOT NULL predikatini qo'shishingiz kerak bo'ladi, masalan:

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

Agar noyob kalit mavjud bo'lsa, "...-to-bir" turidagi OUTER JOIN o'chirilishi mumkin. Buning o'rniga:

SELECT first_name, last_name
FROM customer c
LEFT JOIN address a ON c.address_id = a.address_id

Ma'lumotlar bazasi yana quyidagilarni amalga oshirishi mumkin:

SELECT first_name, last_name
FROM customer c

... CUSTOMER.ADDRESS_ID uchun chet el kaliti bo'lmasa ham. "...-to-ko'p" turidagi noyob tashqi ulanish (DISTINCT OUTER JOIN) olib tashlanishi mumkin. Buning o'rniga:

SELECT DISTINCT first_name, last_name
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id

Ma'lumotlar bazasi quyidagilarni amalga oshirishi mumkin:

SELECT DISTINCT first_name, last_name
FROM actor a

Ushbu misollarning barchasi oldingi maqolada batafsil o'rganilgan, shuning uchun men o'zimni takrorlamayman, balki turli xil ma'lumotlar bazalari yo'q qilishi mumkin bo'lgan hamma narsani umumlashtiraman: Afsuski, barcha ma'lumotlar bazalari barcha turdagi ulanishlarni hal qila olmaydi. DB2 va SQL Server bu erda shubhasiz etakchilardir! Davomi bor