Optimizations SQL Cool sing ora gumantung ing model biaya. Bagean 1

Limang optimasi prasaja sing bisa dileksanakake mung adhedhasar metadata (yaiku kendala) lan pitakon dhewe Kita menehi sampeyan adaptasi saka artikel Lukas Eder, sing dirancang kanggo wong sing duwe pangerten umum babagan database lan SQL, uga sawetara pengalaman praktis karo DBMS . Optimasi biaya sejatine minangka cara standar kanggo ngoptimalake pitakon SQL ing basis data modern. Pramila angel banget nulis algoritma kompleks kanthi manual ing 3GL (basa pamrograman generasi katelu) sing kinerja bakal ngluwihi rencana eksekusi sing diwilang kanthi dinamis sing digawe dening pangoptimal modern. Dina iki kita ora bakal ngrembug babagan optimasi biaya, yaiku, optimasi adhedhasar model biaya database. Kita bakal ndeleng optimasi sing luwih gampang. Sing bisa dileksanakake mung adhedhasar metadata (yaiku watesan) lan panjaluk kasebut dhewe. Biasane implementasine kanggo database dudu binomial Newton, amarga, ing kasus iki, optimasi apa wae bakal mimpin kanggo rencana eksekusi sing luwih apik, preduli saka anané indeks, volume data lan skewness distribusi data. "Ora binomial Newton" ora ing pangertèn saka carane gampang iku kanggo ngleksanakake Optimization, nanging apa iku kudu rampung. Optimizations iki ngilangi rasah, ekstra karya [kanggo database] ( minangka gantos kanggo rasah, karya dibutuhake, kang wis aku nulis bab ).

Apa optimasi iki digunakake kanggo?

Paling akeh digunakake kanggo:

• ndandani bug ing pitakon;
• ngidini tampilan bisa digunakake maneh tanpa database bener nglakokake logika tampilan.

Ing kasus sing sepisanan, siji bisa ngomong: "Dadi apa, terusake lan ndandani pitakon SQL sing bodho iki." Nanging sapa sing ora tau luput, ayo mbenturi watu dhisik. Kasus kapindho iku utamané menarik: menehi kita kemampuan kanggo nggawe perpustakaan Komplek tampilan lan fungsi tabel sing bisa digunakake maneh liwat sawetara lapisan.

Database digunakake

Ing artikel iki kita bakal mbandhingake 10 optimasi SQL ing limang DBMS sing paling akeh digunakake ( miturut peringkat basis data ):

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

Rating liyane meh nyuarakke. Kaya biasane, ing artikel iki aku bakal takon database Sakila . Punika dhaptar sepuluh jinis optimasi iki:

1. penutupan transitif;
2. predikat mokal lan panggilan meja sing ora perlu;
3. ngilangi JOIN;
4. ngilangi predikat "ora ana guna";
5. proyeksi ing EXISTS subqueries;
6. panggabungan predikat;
7. mbuktekaken set kosong;
8. watesan Mriksa;
9. sambungan refleksif sing ora perlu;
10. Predikat pushdown

Dina iki kita bakal ngrembug pp. 1-3, ing bagean kapindho - 4 lan 5, lan ing bagean 3 - 6-10.

1. Panutup transitif

Ayo dadi miwiti karo sing luwih prasaja: penutupan transitif . Iki minangka konsep sepele sing ditrapake kanggo akeh operasi matematika, kayata operator kesetaraan. Bisa dirumusake ing kasus iki: yen A = B lan B = C, banjur A = C.

Ora angel, ta? Nanging iki duwe sawetara implikasi sing menarik kanggo pangoptimal SQL. Ayo padha ndeleng conto. Ayo extract kabeh film nganggo ACTOR_ID = 1:

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;

Hasile kaya ing ngisor iki:

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

Ayo saiki ndeleng rencana kanggo nglakokake pitakon iki ing kasus Oracle DBMS:

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

Bagean babagan predikat utamane menarik ing kene. Predikat ACTOR_ID = 1, amarga penutupan transitif, ditrapake kanggo tabel ACTOR lan tabel FILM_ACTOR. Yen:

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

Kanggo pitakon sing luwih rumit, iki ngasilake asil sing apik banget. Utamane, akurasi perkiraan kardinalitas mundhak sacara signifikan, amarga bisa milih perkiraan adhedhasar nilai konstanta tartamtu saka predikat, lan ora, contone, jumlah rata-rata film dening aktor, kaya ing pitakonan ing ngisor iki (mbalikake asil sing padha):

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'

Rencanane:

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

Nalika sampeyan bisa ndeleng, jumlah larik ing tabel FILM_ACTOR overestimated, nalika LOOP NESTED disepelekake. Ing ngisor iki sawetara nilai sing menarik:

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

asil:

19
27.315

Iki ngendi perkiraan teka saka. Yen database ngerti yen kita ngomong babagan ACTOR_ID = 1, mula bisa ngumpulake statistik babagan jumlah film kanggo aktor tartamtu iki . Yen ora (amarga mekanisme koleksi statistik standar ora ana hubungane FIRST_NAME/LAST_NAME karo ACTOR_ID), mula kita bakal entuk rata-rata jumlah film kanggo kabeh aktor . Kesalahan sing prasaja lan ora penting ing kasus tartamtu iki, nanging ing pitakonan rumit bisa nyebar luwih akeh, nglumpukake lan mimpin menyang pitakon (luwih dhuwur ing rencana) menyang pilihan JOIN sing salah. Dadi kapan sampeyan bisa, desain gabungan lan predikat prasaja kanggo njupuk kauntungan saka penutupan transitif. Apa basis data liyane sing ndhukung fitur iki?

DB2

ya wis!

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)

Miturut cara, yen sampeyan seneng rencana eksekusi sing keren kaya iki, priksa naskah Markus Winand .

MySQL

Sayange, rencana eksekusi MySQL ora cocog kanggo analisis kaya iki. Predikat dhewe ilang saka informasi output:

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

Nanging kasunyatan sing const kasebut kaping pindho ing kolom REF nuduhake yen loro tabel nggoleki nilai pancet. Ing wektu sing padha, rencana pitakon karo FIRST_NAME/LAST_NAME katon kaya iki:

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

Lan kaya sing sampeyan deleng, REF saiki ngrujuk kolom saka predikat JOIN. Skor kardinalitas meh padha karo Oracle. Dadi ya, MySQL uga ndhukung penutupan transitif.

PostgreSQL

ya wis!

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

ya wis!

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

Ringkesan

Kabeh database kita ndhukung penutupan transitif. Nanging, ngenteni # 6 ing bagean sabanjure artikel. Ana kasus kompleks penutupan transitif sing ora kabeh database bisa ditangani.

2. Predikat mokal lan telpon meja rasah

Iki minangka optimasi sing bodho, nanging kenapa ora? Yen pangguna nulis predikat sing ora mungkin, mula kenapa keganggu ngeksekusi? Ing ngisor iki sawetara conto:

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

Pitakonan pisanan mesthi ora bakal ngasilake asil apa wae, nanging pernyataan sing padha bener kanggo sing kapindho. Sawise kabeh, sanajan NULL IS NULL tansah TRUE, asil pitungan NULL = NULL punika NULL, kang, miturut telung nilai logika , padha karo FALSE. Iki cukup jelas, mula ayo langsung golek database sing nindakake optimasi iki.

DB2

ya wis!

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

Nalika sampeyan bisa ndeleng, akses menyang meja ACTOR rampung dipun tilar saka rencana. Isine mung operasi GENROW, sing ngasilake nol larik. Sempurno.

MySQL

ya wis!

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

Wektu iki, MySQL cukup apik kanggo ngandhani babagan klausa WHERE sing ora mungkin. Matur nuwun! Iki nggawe analisis luwih gampang, utamane dibandhingake karo database liyane.

Oracle

ya wis!

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

Kita waca sing rencana isih nyebataken akses kanggo Tabel ACTOR, lan nomer samesthine larik isih 200, nanging ana uga operasi nyaring (FILTER) karo Id = 1, ngendi ora bakal TRUE. Amarga Oracle ora seneng karo jinis data SQL Boolean standar , Oracle nampilake NULL IS NOT NULL ing rencana kasebut, tinimbang mung PALSU. Wah... Nanging serius, nonton predikat kasebut. Aku duwe kesempatan kanggo debug rencana eksekusi kanthi subtree 1000-line lan nilai biaya sing dhuwur banget, mung kanggo nemokake sawise kasunyatan manawa kabeh subtree "dipotong" dening filter NULL IS NOT NULL. A little discoursing, Aku pitutur marang kowe.

PostgreSQL

ya wis!

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

Wis luwih apik. Ora ana telpon meja ACTOR sing ngganggu lan predikat PALSU cilik sing apik.

SQL Server?

ya wis!

|--Constant Scan

SQL Server nyebat iki minangka " scan konstan ", yaiku scan sing ora ana sing kedadeyan - padha karo DB2. Kabeh database kita bisa ngilangi predikat sing ora mungkin:

3. Ngilangi JOIN

Ing bagean sadurunge, kita mirsani akses tabel sing ora perlu ing pitakon tabel siji. Nanging apa sing kedadeyan yen JOIN ora mbutuhake salah siji saka sawetara akses meja? Aku wis nulis babagan mbusak JOIN ing kirim sadurunge saka blogku . Mesin SQL bisa nemtokake, adhedhasar jinis pitakon lan anane kunci utama lan manca, apa JOIN tartamtu pancen dibutuhake ing pitakon tartamtu, utawa ngilangi ora bakal mengaruhi semantik pitakon kasebut. Ing kabeh telung conto sabanjure, JOIN ora dibutuhake. Gabungan ...-kanggo-siji bisa diilangi kanthi nduwe kunci asing NOT NULL.

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

Database bisa nindakake ing ngisor iki:

SELECT first_name, last_name
FROM customer c

GABUNGAN INNER saka jinis "...-to-one" bisa diganti yen ana tombol manca nullable. Pitakonan ing ndhuwur bisa digunakake yen kunci manca tundhuk watesan NOT NULL. Yen ora, contone, kaya ing panyuwunan iki:

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

banjur JOIN isih bisa diilangi, nanging sampeyan kudu nambah predikat NOT NULL, kaya iki:

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

JOIN OUTER saka jinis "...-to-one" bisa dibusak yen ana tombol unik. Tinimbang iki:

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

Database, maneh, bisa nindakake ing ngisor iki:

SELECT first_name, last_name
FROM customer c

... sanajan ora ana kunci asing kanggo CUSTOMER.ADDRESS_ID. Sambungan njaba unik (DISTINCT OUTER JOIN) saka jinis "...-to-many" bisa dibusak. Tinimbang iki:

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

Database bisa nindakake ing ngisor iki:

SELECT DISTINCT first_name, last_name
FROM actor a

Kabeh conto kasebut diteliti kanthi rinci ing artikel sadurunge, mula aku ora bakal mbaleni maneh, nanging mung bakal ngringkes kabeh sing bisa diilangi dening macem-macem database: Sayange, ora kabeh database bisa ngatasi kabeh jinis sambungan. DB2 lan SQL Server minangka pimpinan sing ora bisa dibantah ing kene! Diterusake