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

Yalnız metadata (məhdudiyyətlər) və sorğunun özü əsasında həyata keçirilə bilən beş sadə optimallaşdırma Biz sizə Lukas Ederin məqaləsinin verilənlər bazası və SQL haqqında ümumi anlayışı, həmçinin DBMS ilə bağlı bəzi praktiki təcrübəsi olanlar üçün nəzərdə tutulmuş uyğunlaşmasını təklif edirik. . Xərclərin optimallaşdırılması əslində müasir verilənlər bazalarında SQL sorğularını optimallaşdırmağın standart yoludur. Buna görə 3GL-də (üçüncü nəsil proqramlaşdırma dilləri) mürəkkəb alqoritmi əl ilə yazmaq çox çətindir, onun performansı müasir optimallaşdırıcı tərəfindən yaradılan dinamik hesablanmış icra planını üstələyir. Bu gün biz xərclərin optimallaşdırılmasını, yəni verilənlər bazasının xərc modelinə əsaslanan optimallaşdırmanı müzakirə etməyəcəyik. Daha sadə optimallaşdırmalara baxacağıq. Yalnız metadata (yəni məhdudiyyətlər) və sorğunun özü əsasında həyata keçirilə bilənlər. Adətən verilənlər bazası üçün onların həyata keçirilməsi Nyuton binomialı deyil, çünki bu halda hər hansı optimallaşdırma indekslərin, məlumatların həcminin və məlumatların paylanmasının əyriliyindən asılı olmayaraq daha yaxşı icra planına səbəb olacaqdır. "Nyuton binomialı deyil" optimallaşdırmanı həyata keçirməyin nə qədər asan olması mənasında deyil, onun həyata keçirilməli olub-olmaması mənasındadır. Bu optimallaşdırmalar [verilənlər bazası üçün] lazımsız, əlavə işləri aradan qaldırır ( haqqında artıq yazdığım lazımsız, tələb olunan işdən fərqli olaraq ).

Bu optimallaşdırmalar nə üçün istifadə olunur?

Onların əksəriyyəti aşağıdakılar üçün istifadə olunur:

• sorğularda səhvlərin düzəldilməsi;
• verilənlər bazası həqiqətən görünüş məntiqini icra etmədən baxışların təkrar istifadəsinə imkan verir.

Birinci halda, biri deyə bilər: "Yaxşı, davam et və bu axmaq SQL sorğusunu düzəldin." Amma heç vaxt səhv etməyən əvvəlcə mənə daş atsın. İkinci hal xüsusilə maraqlıdır: o, bizə bir neçə təbəqədə təkrar istifadə oluna bilən mürəkkəb görünüş kitabxanaları və cədvəl funksiyaları yaratmaq imkanı verir.

İstifadə olunan verilənlər bazaları

Bu yazıda biz ən çox istifadə olunan beş DBMS-də 10 SQL optimallaşdırmasını müqayisə edəcəyik ( verilənlər bazası sıralamalarına görə ):

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

Başqa bir reytinq demək olar ki, əks-səda verir. Həmişə olduğu kimi, bu yazıda Sakila verilənlər bazasını sorğulayacağam . Bu on optimallaşdırma növünün siyahısı:

1. keçid bağlanması;
2. qeyri-mümkün predikatlar və lazımsız masa çağırışları;
3. JOIN-in aradan qaldırılması;
4. "mənasız" predikatların aradan qaldırılması;
5. EXISTS alt sorğularında proqnozlar;
6. predikatların birləşməsi;
7. yəqin ki, boş dəstlər;
8. məhdudiyyətləri CHECK;
9. lazımsız refleksiv əlaqələr;
10. Pushdown predikatları

Bu gün s.-ni müzakirə edəcəyik. 1-3, ikinci hissədə - 4 və 5, 3-cü hissədə - 6-10.

1. Keçidli bağlanma

Daha sadə bir şeylə başlayaq: keçid bağlanması . Bu, bərabərlik operatoru kimi bir çox riyazi əməliyyatlara aid olan mənasız bir anlayışdır. Bu halda onu aşağıdakı kimi formalaşdırmaq olar: əgər A = B və B = C olarsa, onda A = C.

Çətin deyil, hə? Ancaq bunun SQL optimallaşdırıcıları üçün bəzi maraqlı təsirləri var. Bir nümunəyə baxaq. ACTOR_ID = 1 olan bütün filmləri çıxaraq:

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;

Nəticə aşağıdakı kimidir:

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

İndi Oracle DBMS vəziyyətində bu sorğunun yerinə yetirilməsi planına nəzər salaq:

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

Burada predikatlar bölməsi xüsusilə maraqlıdır. ACTOR_ID = 1 predikatı keçid qapanmasına görə həm ACTOR cədvəlinə, həm də FILM_ACTOR cədvəlinə aiddir. Əgər:

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

Daha mürəkkəb sorğular üçün bu, çox gözəl nəticələr verir. Xüsusilə, kardinallıq qiymətləndirmələrinin dəqiqliyi əhəmiyyətli dərəcədə artır, çünki aşağıdakı sorğuda olduğu kimi, məsələn, aktyorlar tərəfindən çəkilmiş filmlərin orta sayına deyil, predikatın xüsusi sabit dəyərinə əsaslanaraq təxminləri seçmək mümkün olur (qaytararaq eyni nəticə):

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'

Onun planı:

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

Gördüyünüz kimi, FILM_ACTOR cədvəlində sətirlərin sayı həddən artıq qiymətləndirilir, NESTED LOOP isə aşağı qiymətləndirilir. Burada bir neçə maraqlı dəyər var:

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

Nəticə:

19
27.315

Təxminlər buradan gəlir. Əgər verilənlər bazası ACTOR_ID = 1-dən danışdığımızı bilirsə, o zaman bu aktyor üçün filmlərin sayı haqqında statistik məlumat toplaya bilər . Əgər belə deyilsə (standart statistika toplama mexanizmi FIRST_NAME/LAST_NAME ilə ACTOR_ID arasında əlaqə yaratmadığı üçün), biz bütün aktyorlar üçün orta film sayını əldə edəcəyik . Bu konkret halda sadə, əhəmiyyətsiz xətadır, lakin mürəkkəb sorğuda o, daha da yayıla, toplana və sorğuya (planda daha yüksək) doğru olmayan QOŞULUŞ seçiminə səbəb ola bilər. Buna görə də, mümkün olan hər zaman keçid bağlama imkanından faydalanmaq üçün birləşmələrinizi və sadə predikatlarınızı dizayn edin. Bu funksiyanı başqa hansı verilənlər bazası dəstəkləyir?

DB2

Bəli!

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)

Yeri gəlmişkən, bu kimi gözəl icra planlarını sevirsinizsə, Markus Winandın ssenarisinə baxın .

MySQL

Təəssüf ki, MySQL icra planları bu tip analiz üçün uyğun deyil. Çıxış məlumatında predikatın özü yoxdur:

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

Lakin REF sütununda const-un iki dəfə göstərilməsi hər iki cədvəlin sabit qiymət axtardığını göstərir. Eyni zamanda, FIRST_NAME/LAST_NAME ilə sorğu planı belə görünür:

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

Gördüyünüz kimi, REF indi JOIN predikatından sütuna istinad edir. Kardinallıq balı demək olar ki, Oracle ilə eynidir. Beləliklə, bəli, MySQL keçid bağlanmalarını da dəstəkləyir.

PostgreSQL

Bəli!

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

Bəli!

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

Xülasə

Bütün verilənlər bazalarımız keçid bağlanmasını dəstəkləyir. Bununla belə, məqalənin növbəti hissəsində #6-nı gözləyin. Bütün verilənlər bazalarının idarə edə bilmədiyi mürəkkəb keçid halları var.

2. Mümkün olmayan predikatlar və lazımsız masa çağırışları

Bu tamamilə axmaq optimallaşdırmadır, amma niyə olmasın? Əgər istifadəçilər qeyri-mümkün predikatlar yazırsa, o zaman onları yerinə yetirməyə nə ehtiyac var? Budur bəzi nümunələr:

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

İlk sorğu heç vaxt heç bir nəticə verməyəcək, lakin eyni ifadə ikinci üçün də doğrudur. Axı, NULL IS NULL həmişə DOĞRU olsa da, NULL = NULL hesablamasının nəticəsi NULL-dur ki, bu da üç qiymətli məntiqə görə FALSE-ə ekvivalentdir. Bu, olduqca izahlıdır, ona görə də gəlin birbaşa hansı verilənlər bazalarının bu optimallaşdırmanı yerinə yetirdiyini öyrənək.

DB2

Bəli!

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

Göründüyü kimi, AKTOR cədvəlinə giriş plandan tamamilə çıxarılıb. O, yalnız sıfır sıra yaradan GENROW əməliyyatını ehtiva edir. Mükəmməl.

MySQL

Bəli!

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

Bu dəfə MySQL bizə qeyri-mümkün WHERE bəndi haqqında məlumat verəcək qədər mehriban idi. Çox sağ ol! Bu, xüsusilə digər verilənlər bazaları ilə müqayisədə təhlili çox asanlaşdırır.

Oracle

Bəli!

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

Biz görürük ki, plan hələ də AKTOR cədvəlinə girişdən bəhs edir və gözlənilən sıra sayı hələ də 200-dür, lakin eyni zamanda Id=1 olan filtrləmə əməliyyatı (FILTER) var, burada heç vaxt TRUE olmayacaq. Oracle standart SQL Boolean məlumat növünü bəyənmədiyinə görə , Oracle planda FALSE əvəzinə NULL IS NOT NULL göstərir. Oh, yaxşı... Amma ciddi şəkildə bu predikata baxın. Mən 1000 sətirli alt ağac və son dərəcə yüksək qiymət dəyərləri ilə icra planlarında debug etmək imkanım oldu, yalnız bütün alt ağacın NULL IS NULL filtri tərəfindən "kəsilməsi" faktından sonra aşkar etdim. Bir az ruhdan salır, sizə deyirəm.

PostgreSQL

Bəli!

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

Artıq daha yaxşı. Heç bir zəhlətökən AKTOR masası zəngləri və səliqəli kiçik FALSE predikatı.

SQL Server?

Bəli!

|--Constant Scan

SQL Server bunu heç bir şeyin baş vermədiyi bir skan olan " daimi tarama" adlandırır - DB2-yə bənzər. Bütün verilənlər bazalarımız qeyri-mümkün predikatları istisna edə bilər:

3. QOŞULMALARI aradan qaldırın

Əvvəlki bölmədə biz tək cədvəl sorğularında lazımsız cədvələ girişləri müşahidə etdik. Bəs JOIN bir neçə cədvələ girişdən birini tələb etmirsə nə baş verir? QOŞULUŞ-un ləğvi haqqında bloqumdan əvvəlki yazıda artıq yazmışdım . SQL mühərriki sorğunun növünə və əsas və xarici açarların mövcudluğuna əsaslanaraq müəyyən bir sorğuda konkret JOIN-ə ehtiyac olub-olmadığını və ya onun aradan qaldırılması sorğunun semantikasına təsir etməyəcəyini müəyyən edə bilir. Növbəti üç nümunənin hamısında JOIN tələb olunmur. Daxili ...-to-bir birləşmə NOT NULL xarici açarla aradan qaldırıla bilər. Bunun əvəzinə:

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

Verilənlər bazası aşağıdakıları edə bilər:

SELECT first_name, last_name
FROM customer c

Nüfuz edilə bilən xarici açar varsa, "...-to-one" tipli INNER JOIN əvəz edilə bilər. Xarici açar NOT NULL məhdudiyyətinə tabedirsə, yuxarıdakı sorğu işləyir. Əgər yoxsa, məsələn, bu sorğuda olduğu kimi:

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

onda JOIN hələ də aradan qaldırıla bilər, lakin siz bu kimi NOT NULL predikatını əlavə etməli olacaqsınız:

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

Unikal açar varsa, "...-to-bir" tipli OUTER JOIN silinə bilər. Bunun əvəzinə:

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

Verilənlər bazası yenə də aşağıdakıları edə bilər:

SELECT first_name, last_name
FROM customer c

... CUSTOMER.ADDRESS_ID üçün xarici açar olmasa belə. "...-to-çox" tipli unikal xarici əlaqə (DISTINCT OUTER JOIN) çıxarıla bilər. Bunun əvəzinə:

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

Verilənlər bazası aşağıdakıları edə bilər:

SELECT DISTINCT first_name, last_name
FROM actor a

Bütün bu nümunələr əvvəlki məqalədə ətraflı öyrənildi, buna görə də özümü təkrarlamayacağam, ancaq müxtəlif verilənlər bazalarının aradan qaldıra biləcəyi hər şeyi ümumiləşdirəcəyəm: Təəssüf ki, bütün verilənlər bazaları bütün növ əlaqələri həll edə bilmir. DB2 və SQL Server burada şəksiz liderdir! Ardı var