Bahanyň modeline bagly bolmadyk sowuk SQL optimizasiýalary. 1-nji bölüm

Diňe metadata (ýagny çäklendirmeler) we soragyň özi esasynda amala aşyrylyp bilinjek bäş sany ýönekeý optimizasiýa, maglumatlar bazasyna we SQL-ä umumy düşünýänler üçin döredilen Lukas Ederiň makalasyny, şeýle hem DBMS bilen käbir amaly tejribäni hödürleýäris. . Çykdajylary optimizasiýa, häzirki zaman maglumat bazalarynda SQL talaplaryny optimizirlemegiň adaty usulydyr. Şonuň üçin 3GL-de (üçünji nesil programmirleme dillerinde) çylşyrymly algoritmi el bilen ýazmak gaty kyn, öndürijiligi häzirki zaman optimizatorynyň döreden dinamiki hasaplanan ýerine ýetiriş meýilnamasyndan ýokary bolar. Bu gün çykdajylaryň optimizasiýasyny, ýagny maglumatlar bazasynyň çykdajy modeline esaslanýan optimizasiýany ara alyp maslahatlaşmarys. Has ýönekeý optimizasiýalara serederis. Diňe metadata (ýagny çäklendirmeler) we haýyşyň özi esasynda amala aşyrylyp bilinjekler. Adatça maglumatlar binýady üçin durmuşa geçirilmegi Nýuton binomial däl, sebäbi bu ýagdaýda islendik optimizasiýa indeksleriň, maglumatlaryň göwrümine we maglumat paýlanyşynyň gödekligine garamazdan has gowy ýerine ýetiriş meýilnamasyna getirer. "Nýuton binomial däl" optimizasiýany durmuşa geçirmegiň aňsatlygy däl-de, edilmelidigi manysynda däl. Bu optimizasiýalar zerur däl, goşmaça işleri [maglumatlar bazasy üçin] ýok edýär ( öňem ýazan gereksiz, zerur işlerden tapawutlylykda ).

Bu optimizasiýalar näme üçin ulanylýar?

Olaryň köpüsi aşakdakylar üçin ulanylýar:

• talaplarda näsazlyklary düzetmek;
• görüniş logikasyny ýerine ýetirýän maglumatlar bazasyz görnüşleriň gaýtadan ulanylmagyna mümkinçilik berýär.

Birinji ýagdaýda: “Onda näme, bu samsyk SQL talapyny düzediň” diýip biler. Neveröne hiç haçan ýalňyşlyk goýbermedik adam ilki maňa daş atsyn. Ikinji ýagdaý aýratyn gyzykly: bize dürli gatlaklarda gaýtadan ulanyp boljak görnüşleriň we tablisanyň funksiýalarynyň çylşyrymly kitaphanalaryny döretmäge mümkinçilik berýär.

Ulanylan maglumat bazalary

Bu makalada iň köp ulanylýan bäş DBMS-de ( maglumatlar bazasynyň reýtingine görä ) 10 SQL optimizasiýasyny deňeşdireris :

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

Başga bir reýting hem diýen ýaly gaýtalanýar. Hemişe bolşy ýaly, bu makalada Sakila maglumatlar bazasyny soraryn . Ine, optimizasiýanyň bu on görnüşiniň sanawy:

1. geçiş ýapylmagy;
2. mümkin bolmadyk çaklamalar we gereksiz stol jaňlary;
3. JOIN-i ýok etmek;
4. "manysyz" predikatlary ýok etmek;
5. EXISTS kiçi soragnamalarynda çaklamalar;
6. predikatlaryň birleşmegi;
7. ähtimal boş toplumlar;
8. çäklendirmeler;
9. gereksiz refleksli baglanyşyklar;
10. Pushdown çaklaýar

Bu gün s. 1-3, ikinji bölümde - 4 we 5, 3-nji bölümde - 6-10.

1. Geçiş ýapylyşy

Has ýönekeý bir zat bilen başlalyň: geçiş ýapylyşy . Bu deňlik operatory ýaly köp matematiki amallara degişli ähmiýetsiz düşünje. Bu ýagdaýda aşakdaky ýaly düzülip bilner: eger A = B we B = C bolsa, A = C.

Kyn däl, şeýlemi? Emma munuň SQL optimizatorlary üçin käbir gyzykly netijeleri bar. Bir mysala seredeliň. ACTOR_ID = 1 bilen ähli filmleri çykaralyň:

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;

Netije aşakdaky ýaly:

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

Indi Oracle DBMS ýagdaýynda bu soragy ýerine ýetirmek meýilnamasyna göz aýlalyň:

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

Kesgitler bölümi bu ýerde aýratyn gyzykly. Kesgitli ACTOR_ID = 1, geçiş ýapylmagy sebäpli ACTOR tablisasyna we FILM_ACTOR tablisasyna degişlidir. Eger:

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

Has çylşyrymly soraglar üçin bu gaty gowy netijeleri berýär. Hususan-da, kardinallyk çaklamalarynyň takyklygy ep-esli ýokarlanýar, sebäbi kesgitlemäniň belli bir hemişelik bahasyna esaslanyp bahalandyrmalary saýlamak mümkin bolýar, mysal üçin, aşakdaky talapdaky ýaly aktýorlar tarapyndan düşürilen filmleriň ortaça sanyny däl. şol bir netije):

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'

Onuň meýilnamasy:

----------------------------------------------------------------------------
| 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örşüňiz ýaly, FILM_ACTOR tablisasyndaky hatarlaryň sanyna aşa baha berilýär, NESTED LOOP bolsa kembaha garalýar. Ine birnäçe gyzykly gymmatlyk:

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

Netije:

19
27.315

Çaklamalar şu ýerden gelýär. Maglumatlar bazasy ACTOR_ID = 1 hakda gürleşýändigimizi bilýän bolsa, bu aktýor üçin filmleriň sany barada statistika ýygnap biler . Eger ýok bolsa (adaty statistika ýygnamak mehanizmi FIRST_NAME / LAST_NAME bilen ACTOR_ID bilen baglanyşygy ýoklugy sebäpli), ähli aktýorlar üçin ortaça film sanyny alarys . Bu aýratyn ýagdaýda ýönekeý, ähmiýetsiz ýalňyşlyk, ýöne çylşyrymly talapda ol hasam köpelip, toplanyp we talaplara (meýilnamada has ýokary) nädogry JOIN saýlamasyna alyp biler. Şonuň üçin mümkin boldugyça geçiş ýapylyşyndan peýdalanmak üçin bogunlaryňyzy we ýönekeý çaklamalaryňyzy düzüň. Bu aýratynlygy başga haýsy maglumat bazalary goldaýar?

DB2

Hawa!

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)

Theeri gelende aýtsak, şunuň ýaly ajaýyp ýerine ýetiriş meýilnamalaryny halaýan bolsaňyz, Markus Winandyň ssenarisini gözden geçiriň .

MySQL

Gynansagam, MySQL ýerine ýetiriş meýilnamalary bu derňew görnüşi üçin oňaýly däl. Kesgitleýjiniň özi çykyş maglumatlaryndan ýitýär:

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

Emma const REF sütüninde iki gezek görkezilendigi, iki tablisanyň hem hemişelik baha gözleýändigini görkezýär. Şol bir wagtyň özünde, FIRST_NAME / LAST_NAME bilen gözleg meýilnamasy şuňa meňzeýär:

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

Görşüňiz ýaly, REF indi JOIN predikat sütünine salgylanýar. Kardinallyk baly Oracle-daky ýalydyr. Hawa, MySQL geçiş ýapylmagyny hem goldaýar.

PostgreSQL

Hawa!

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 serweri

Hawa!

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

Gysgaça mazmun

Maglumatlar bazalarymyzyň hemmesi geçişiň ýapylmagyny goldaýar. Şeýle-de bolsa, makalanyň indiki bölüminde # 6 garaşyň. Transhli maglumat bazalarynyň dolandyryp bilmeýän geçiş ýapylmagynyň çylşyrymly ýagdaýlary bar.

2. Mümkin däl çaklamalar we gereksiz stol jaňlary

Bu düýbünden samsyk optimizasiýa, ýöne näme üçin beýle däl? Ulanyjylar mümkin bolmadyk çaklamalary ýazýan bolsa, näme üçin olary ýerine ýetirmekden biynjalyk bolýarsyňyz? Ine käbir mysallar:

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

Birinji talap hiç haçan hiç hili netije bermez, ýöne ikinjisi üçin şol bir jümle dogry. Galyberse-de, NULL IS NULL elmydama HAKYKAT bolsa-da, hasaplamagyň netijesi NULL = NULL, üç bahaly logika görä , ALALSEYŞlyga deňdir. Bu gaty öz-özünden düşnükli, şonuň üçin haýsy maglumat bazalarynyň bu optimizasiýany ýerine ýetirýändigini anyklamaga geçeliň.

DB2

Hawa!

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

Görşüňiz ýaly, ACTOR tablisasyna girmek meýilnamadan düýbünden aýrylýar. Onda diňe nol hatar döredýän GENROW amaly bar. Kämil.

MySQL

Hawa!

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

Bu gezek MySQL mümkin däl WHERE maddasy barada bize habar berdi. Sagbol! Bu, esasanam beýleki maglumat bazalary bilen deňeşdirilende derňewi has aňsatlaşdyrýar.

Oracle

Hawa!

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

Meýilnamada henizem ACTOR tablisasyna girmegiň agzalýandygyny we garaşylýan hatarlaryň sanynyň 200-e ýetendigini görýäris, ýöne Id = 1 bilen süzgüç amaly (FILTER) hem bar, bu ýerde hiç haçan HAKYKAT bolmaz. Oracle-yň adaty SQL Boolean maglumat görnüşini halamaýandygy sebäpli , Oracle diňe ALALSEYŞ däl-de, meýilnamada NULL NULL däldigini görkezýär. Aý, gowy ... seriouslyöne çynlakaý serediň. 1000 setirli aşaky agaçlar we gaty ýokary çykdajylar bilen ýerine ýetiriş meýilnamalaryny düzetmäge pursat tapdym, diňe NULL IS NULL süzgüji bilen ähli subtree "kesilendigini" anyklandan soň. Biraz lapykeç, saňa aýdýaryn.

PostgreSQL

Hawa!

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

Eýýäm has gowy. Hiç hili bizar ediji ACTOR stol jaňlary we birneme sähelçe ýalňyş.

SQL serweri?

Hawa!

|--Constant Scan

SQL Serweri muny " hemişelik skaner" diýip atlandyrýar, bu hiç zat bolmadyk ýerde skaner - DB2-e meňzeýär. Datahli maglumat bazalarymyz mümkin bolmadyk çaklamalary aýyryp biler:

3. JOIN-leri aýyryň

Öňki bölümde bir tablisaly talaplarda gereksiz tablisa girişlerine syn etdik. Jöne JOIN birnäçe tablisa girişini talap etmese näme bolýar? JOIN-i ýok etmek hakda blogymdan öňki ýazgyda ýazypdym . SQL hereketlendirijisi, gözlegiň görnüşine we esasy we daşary ýurt düwmeleriniň barlygyna esaslanyp, berlen talapda belli bir JOIN-iň zerurdygyny ýa-da ýok edilmeginiň soragyň semantikasyna täsir etmejekdigini kesgitläp bilýär. Indiki üç mysalyň hemmesinde JOIN gerek däl. Içerki ... - birek-birege goşulmak, DÜZGÜN daşary ýurt açary bilen ýok edilip bilner. Munuň ýerine:

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

Maglumat bazasy aşakdakylary edip biler:

SELECT first_name, last_name
FROM customer c

Daşary ýurt açary bar bolsa, "... - to-one" görnüşli INNER JOIN çalyşyp biler. Foreignokardaky talap, daşary ýurt açary DÜZGÜN çäklendirmä sezewar bolsa işleýär. Notok bolsa, meselem, şu haýyşdaky ýaly:

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

şonda JOIN henizem ýok edilip bilner, ýöne munuň ýaly NULL predikat goşmaly bolarsyňyz:

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

Üýtgeşik açar bar bolsa, "...- to-one" görnüşindäki daşarky birleşme aýrylyp bilner. Munuň ýerine:

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

Maglumat bazasy ýene-de aşakdakylary edip biler:

SELECT first_name, last_name
FROM customer c

... CUSTOMER.ADDRESS_ID üçin daşary ýurt açary bolmasa-da. "...- to-many" görnüşiniň özboluşly daşarky baglanyşygy (DISTINCT OUTER JOIN) aýrylyp bilner. Munuň ýerine:

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

Maglumat bazasy aşakdakylary edip biler:

SELECT DISTINCT first_name, last_name
FROM actor a

Bu mysallaryň hemmesi öňki makalada jikme-jik öwrenilipdi, şonuň üçin özümi gaýtalamaryn, ýöne dürli maglumatlar bazalarynyň ýok edip biljek ähli zatlaryny jemlärin: Gynansagam, maglumatlar bazalarynyň hemmesi baglanyşyklaryň ähli görnüşini çözüp bilmeýär. DB2 we SQL Serweri bu ýerde jedelsiz liderler! Dowam etmek