Pengoptimuman SQL hebat yang tidak bergantung pada model kos. Bahagian 1

Lima pengoptimuman mudah yang boleh dilaksanakan hanya berdasarkan metadata (iaitu kekangan) dan pertanyaan itu sendiri Kami menawarkan anda penyesuaian artikel Lukas Eder, direka untuk mereka yang mempunyai pemahaman umum tentang pangkalan data dan SQL, serta beberapa pengalaman praktikal dengan DBMS . Pengoptimuman kos sebenarnya adalah cara standard untuk mengoptimumkan pertanyaan SQL dalam pangkalan data moden. Inilah sebabnya mengapa sukar untuk menulis algoritma kompleks secara manual dalam 3GL (bahasa pengaturcaraan generasi ketiga) yang prestasinya akan melebihi pelan pelaksanaan yang dikira secara dinamik yang dijana oleh pengoptimum moden. Hari ini kita tidak akan membincangkan pengoptimuman kos, iaitu, pengoptimuman berdasarkan model kos pangkalan data. Kami akan melihat pengoptimuman yang lebih mudah. Yang boleh dilaksanakan hanya berdasarkan metadata (iaitu sekatan) dan permintaan itu sendiri. Biasanya pelaksanaannya untuk pangkalan data bukanlah binomial Newton, kerana, dalam kes ini, sebarang pengoptimuman akan membawa kepada pelan pelaksanaan yang lebih baik, tanpa mengira kehadiran indeks, volum data dan kecondongan pengedaran data. "Bukan binomial Newton" bukanlah dalam erti kata betapa mudahnya untuk melaksanakan pengoptimuman, tetapi sama ada ia perlu dilakukan. Pengoptimuman ini menghapuskan kerja tambahan yang tidak perlu [untuk pangkalan data] ( berbanding dengan kerja yang tidak perlu dan diperlukan, yang telah saya tulis tentang ).

Untuk apa pengoptimuman ini digunakan?

Kebanyakannya digunakan untuk:

• pembetulan pepijat dalam pertanyaan;
• membenarkan pandangan untuk digunakan semula tanpa pangkalan data benar-benar melaksanakan logik paparan.

Dalam kes pertama, seseorang boleh berkata: "Jadi apa, teruskan dan betulkan pertanyaan SQL yang bodoh ini." Tapi biarlah yang tak pernah buat salah lempar batu dulu. Kes kedua amat menarik: ia memberi kita keupayaan untuk mencipta perpustakaan kompleks pandangan dan fungsi jadual yang boleh digunakan semula merentas berbilang lapisan.

Pangkalan data yang digunakan

Dalam artikel ini kami akan membandingkan 10 pengoptimuman SQL dalam lima DBMS yang paling banyak digunakan ( mengikut kedudukan pangkalan data ):

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

Penilaian lain hampir bergema. Seperti biasa, dalam artikel ini saya akan menanyakan pangkalan data Sakila . Berikut ialah senarai sepuluh jenis pengoptimuman ini:

1. penutupan transitif;
2. predikat mustahil dan panggilan jadual yang tidak perlu;
3. menghapuskan JOIN;
4. penghapusan predikat "tidak bermakna";
5. unjuran dalam subkueri EXISTS;
6. penggabungan predikat;
7. set kosong yang terbukti;
8. kekangan SEMAK;
9. sambungan refleksif yang tidak perlu;
10. Predikat tekan bawah

Hari ini kita akan membincangkan ms. 1-3, dalam bahagian kedua - 4 dan 5, dan dalam bahagian 3 - 6-10.

1. Penutupan transitif

Mari kita mulakan dengan sesuatu yang lebih mudah: penutupan transitif . Ini adalah konsep remeh yang digunakan untuk banyak operasi matematik, seperti pengendali kesamaan. Ia boleh dirumuskan dalam kes ini seperti berikut: jika A = B dan B = C, maka A = C.

Tak susah kan? Tetapi ini mempunyai beberapa implikasi menarik untuk pengoptimum SQL. Mari kita lihat contoh. Mari ekstrak semua filem dengan 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;

Hasilnya adalah seperti berikut:

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

Sekarang mari kita lihat rancangan untuk melaksanakan pertanyaan ini dalam kes 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)

Bahagian predikat amat menarik di sini. Predikat ACTOR_ID = 1, disebabkan oleh penutupan transitif, digunakan pada kedua-dua jadual ACTOR dan jadual FILM_ACTOR. Jika:

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

Untuk pertanyaan yang lebih kompleks, ini menghasilkan beberapa hasil yang sangat bagus. Khususnya, ketepatan anggaran kardinaliti meningkat dengan ketara, kerana ia menjadi mungkin untuk memilih anggaran berdasarkan nilai malar tertentu bagi predikat, dan bukan, sebagai contoh, purata bilangan filem oleh pelakon, seperti dalam pertanyaan berikut (mengembalikan keputusan yang sama):

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'

Rancangannya:

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

Seperti yang anda lihat, bilangan baris dalam jadual FILM_ACTOR adalah terlalu tinggi, manakala NESTED LOOP dipandang rendah. Berikut adalah beberapa nilai yang menarik:

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

Keputusan:

19
27.315

Dari sinilah anggaran datang. Jika pangkalan data mengetahui bahawa kita bercakap tentang ACTOR_ID = 1, maka ia boleh mengumpul statistik tentang bilangan filem untuk pelakon tertentu ini . Jika tidak (memandangkan mekanisme pengumpulan statistik standard tidak mengaitkan FIRST_NAME/LAST_NAME dengan ACTOR_ID), maka kami akan mendapat purata bilangan filem untuk semua pelakon . Ralat yang mudah dan tidak penting dalam kes tertentu ini, tetapi dalam pertanyaan yang kompleks ia boleh menyebar lebih jauh, terkumpul dan membawa lebih jauh ke dalam pertanyaan (lebih tinggi dalam pelan) kepada pilihan JOIN yang salah. Jadi pada bila-bila masa yang anda boleh, reka bentuk gabungan dan predikat mudah anda untuk memanfaatkan penutupan transitif. Apakah pangkalan data lain yang menyokong ciri ini?

DB2

Ya!

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)

Ngomong-ngomong, jika anda suka rancangan pelaksanaan yang hebat seperti ini, lihat skrip Markus Winand .

MySQL

Malangnya, rancangan pelaksanaan MySQL tidak sesuai untuk jenis analisis ini. Predikat itu sendiri hilang daripada maklumat output:

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

Tetapi fakta bahawa const dinyatakan dua kali dalam lajur REF menunjukkan bahawa kedua-dua jadual sedang mencari nilai malar. Pada masa yang sama, pelan pertanyaan dengan FIRST_NAME/LAST_NAME kelihatan seperti ini:

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

Dan seperti yang anda lihat, REF kini merujuk lajur daripada predikat JOIN. Skor kardinaliti hampir sama dengan Oracle. Jadi ya, MySQL juga menyokong penutupan transitif.

PostgreSQL

Ya!

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)

Pelayan SQL

Ya!

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

Ringkasan

Semua pangkalan data kami menyokong penutupan transitif. Walau bagaimanapun, tunggu # 6 di bahagian seterusnya artikel. Terdapat kes kompleks penutupan transitif yang tidak semua pangkalan data boleh mengendalikan.

2. Predikat yang mustahil dan panggilan jadual yang tidak diperlukan

Ini adalah pengoptimuman yang bodoh, tetapi mengapa tidak? Jika pengguna menulis predikat yang mustahil, maka mengapa perlu bersusah payah melaksanakannya? Berikut adalah beberapa contoh:

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

Pertanyaan pertama jelas sekali tidak akan mengembalikan sebarang hasil, tetapi pernyataan yang sama adalah benar untuk yang kedua. Lagipun, walaupun NULL IS NULL sentiasa BENAR, hasil pengiraan NULL = NULL ialah NULL, yang, menurut logik tiga nilai , adalah bersamaan dengan FALSE. Ini cukup jelas, jadi mari kita terus mencari pangkalan data yang melakukan pengoptimuman ini.

DB2

Ya!

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

Seperti yang anda lihat, akses kepada jadual ACTOR dikecualikan sepenuhnya daripada pelan. Ia mengandungi hanya operasi GENROW, yang menjana sifar baris. Sempurna.

MySQL

Ya!

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

Kali ini, MySQL dengan baik hati memberitahu kami tentang klausa WHERE yang mustahil. Terima kasih! Ini menjadikan analisis lebih mudah, terutamanya berbanding pangkalan data lain.

Oracle

Ya!

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

Kami melihat bahawa rancangan itu masih menyebut akses kepada jadual ACTOR, dan bilangan baris yang dijangkakan masih 200, tetapi terdapat juga operasi penapisan (FILTER) dengan Id=1, di mana tidak akan ada BENAR. Disebabkan Oracle tidak menyukai jenis data SQL Boolean standard , Oracle memaparkan NULL IS NOT NULL dalam pelan, bukannya hanya FALSE. Oh well... Tetapi serius, perhatikan predikat itu. Saya mempunyai kesempatan untuk menyahpepijat pelan pelaksanaan dengan subpokok 1000 baris dan nilai kos yang sangat tinggi, hanya untuk mengetahui selepas fakta bahawa keseluruhan subpokok telah "dipotong" oleh penapis NULL IS NOT NULL. Sedikit mengecewakan, saya beritahu anda.

PostgreSQL

Ya!

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

Sudah lebih baik. Tiada panggilan meja ACTOR yang menjengkelkan dan predikat PALSU kecil yang kemas.

Pelayan SQL?

Ya!

|--Constant Scan

SQL Server memanggil ini sebagai " imbasan berterusan ", iaitu imbasan di mana tiada apa yang berlaku - serupa dengan DB2. Semua pangkalan data kami boleh mengecualikan predikat yang mustahil:

3. Hapuskan JOIN

Dalam bahagian sebelumnya, kami memerhatikan akses jadual yang tidak diperlukan dalam pertanyaan jadual tunggal. Tetapi apa yang berlaku jika JOIN tidak memerlukan satu daripada beberapa akses jadual? Saya sudah menulis tentang menghapuskan JOIN dalam catatan sebelumnya dari blog saya . Enjin SQL dapat menentukan, berdasarkan jenis pertanyaan dan kehadiran kunci utama dan asing, sama ada JOIN tertentu sebenarnya diperlukan dalam pertanyaan tertentu, atau sama ada menghapuskannya tidak akan menjejaskan semantik pertanyaan. Dalam kesemua tiga contoh seterusnya, JOIN tidak diperlukan. Gabungan ...-ke-satu dalaman boleh dihapuskan dengan mempunyai kunci asing NOT NULL. Sebaliknya:

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

Pangkalan data boleh melakukan perkara berikut:

SELECT first_name, last_name
FROM customer c

JOIN DALAM jenis "...-to-one" boleh digantikan jika terdapat kunci asing yang boleh batal. Pertanyaan di atas berfungsi jika kunci asing tertakluk kepada kekangan NOT NULL. Jika tidak, sebagai contoh, seperti dalam permintaan ini:

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

maka JOIN masih boleh dihapuskan, tetapi anda perlu menambah predikat NOT NULL, seperti ini:

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

JOIN LUAR jenis "...-to-one" boleh dialih keluar jika terdapat kunci unik. Daripada ini:

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

Pangkalan data, sekali lagi, boleh melakukan perkara berikut:

SELECT first_name, last_name
FROM customer c

... walaupun tiada kunci asing untuk CUSTOMER.ADDRESS_ID. Sambungan luar yang unik (DISTINCT OUTER JOIN) daripada jenis "...-to-many" boleh dialih keluar. Daripada ini:

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

Pangkalan data boleh melakukan perkara berikut:

SELECT DISTINCT first_name, last_name
FROM actor a

Semua contoh ini telah dikaji secara terperinci dalam artikel sebelumnya, jadi saya tidak akan mengulangi diri saya sendiri, tetapi hanya akan meringkaskan segala-galanya yang boleh dihapuskan oleh pelbagai pangkalan data: Malangnya, tidak semua pangkalan data boleh menyelesaikan semua jenis sambungan. DB2 dan SQL Server adalah pemimpin yang tidak dapat dipertikaikan di sini! Akan bersambung