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

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

4. Penghapusan predikat "tidak bermakna".

Sama-sama tidak bermakna adalah predikat yang (hampir) sentiasa benar. Seperti yang anda boleh bayangkan, jika anda bertanya:

SELECT * FROM actor WHERE 1 = 1;

... maka pangkalan data tidak akan melaksanakannya, tetapi hanya akan mengabaikannya. Saya pernah menjawab soalan tentang ini di Stack Overflow dan itulah sebabnya saya memutuskan untuk menulis artikel ini. Saya akan membiarkan ujian ini sebagai latihan kepada pembaca, tetapi apa yang berlaku jika predikatnya kurang "tidak bermakna"? Sebagai contoh:

SELECT * FROM film WHERE release_year = release_year;

Adakah anda benar-benar perlu membandingkan nilai dengan dirinya sendiri untuk setiap baris? Tidak, tiada nilai yang mana predikat ini adalah FALSE , bukan? Tetapi kita masih perlu menyemaknya. Walaupun predikat tidak boleh sama dengan FALSE , ia mungkin sama dengan NULL di mana-mana sahaja , sekali lagi disebabkan oleh logik tiga nilai. Lajur RELEASE_YEAR boleh dibatalkan, dan jika mana-mana baris mempunyai RELEASE_YEAR IS NULL , maka NULL = NULL menghasilkan NULL dan baris mesti dihapuskan. Jadi permintaan itu menjadi seperti berikut:

SELECT * FROM film WHERE release_year IS NOT NULL;

Pangkalan data mana yang melakukan ini?

DB2

Ya!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49
Predicate Information
 2 - SARG Q1.RELEASE_YEAR IS NOT NULL

MySQL

Sungguh memalukan, tetapi MySQL, sekali lagi, tidak memetakan predikat ke dalam rancangan pelaksanaan, jadi memikirkan sama ada MySQL melaksanakan pengoptimuman khusus ini agak sukar. Anda boleh melakukan penilaian prestasi dan melihat sama ada sebarang perbandingan berskala besar sedang dibuat. Atau anda boleh menambah indeks:

CREATE INDEX i_release_year ON film (release_year);

Dan dapatkan rancangan untuk permintaan berikut sebagai balasan:

SELECT * FROM film WHERE release_year = release_year;
SELECT * FROM film WHERE release_year IS NOT NULL;

Jika pengoptimuman berfungsi, maka rancangan kedua-dua pertanyaan hendaklah lebih kurang sama. Tetapi dalam kes ini ini tidak berlaku:

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film             1000  10.00           Using where

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film   i_release_year  1000  100.00    Using where

Seperti yang anda lihat, dua pertanyaan kami berbeza dengan ketara dalam nilai lajur POSSIBLE_KEYS dan FILTERED . Oleh itu, saya akan cuba meneka yang munasabah bahawa MySQL tidak mengoptimumkan ini.

Oracle

Ya!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|*  1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RELEASE_YEAR" IS NOT NULL)

PostgreSQL

Malangnya tidak!

QUERY PLAN
--------------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: ((release_year)::integer = (release_year)::integer)

Pelan dan kos berbeza-beza. Iaitu, lihat penilaian kardinaliti, yang sama sekali tidak baik, sedangkan predikat ini:

SELECT * FROM film WHERE release_year IS NOT NULL;

memberikan hasil yang lebih baik:

QUERY PLAN
---------------------------------------------------------
Seq Scan on film  (cost=0.00..65.00 rows=1000 width=386)
  Filter: (release_year IS NOT NULL)

Bummer!

Pelayan SQL

Anehnya, SQL Server nampaknya tidak melakukan ini sama ada:

|--Table Scan(OBJECT:([film]), WHERE:([release_year]=[release_year]))

Walau bagaimanapun, berdasarkan penampilan pelan, penilaian kardinaliti adalah betul, begitu juga dengan kosnya. Tetapi dalam pengalaman saya dengan SQL Server, saya akan mengatakan bahawa dalam kes ini, tiada pengoptimuman berlaku, kerana SQL Server akan memaparkan predikat yang sebenarnya dilaksanakan dalam rancangan (untuk melihat sebabnya, lihat contoh kekangan SEMAK di bawah). Bagaimana pula dengan predikat "tidak bermakna" pada lajur NOT NULL ? Penukaran di atas hanya perlu kerana RELEASE_YEAR boleh tidak ditentukan. Apakah yang berlaku jika anda menjalankan pertanyaan tidak bermakna yang sama pada, contohnya, lajur FILM_ID ?

SELECT * FROM film WHERE film_id = film_id

Sekarang adakah ia tidak sepadan dengan predikat sama sekali? Atau sekurang-kurangnya begitulah sepatutnya. Tetapi adakah ia?

DB2

Ya!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49

Tiada predikat digunakan sama sekali dan kami memilih semua filem.

MySQL

Ya! (Sekali lagi, tekaan terpelajar)

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film                   1000  100.00

Perhatikan bahawa lajur EXTRA kini kosong, seolah-olah kita tidak mempunyai klausa WHERE sama sekali!

Oracle

Ya!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|   1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------

Sekali lagi, tiada predikat dikenakan.

PostgreSQL

Wah, tidak lagi!

QUERY PLAN
------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: (film_id = film_id)

Penapis digunakan dan skor kardinaliti masih 5. Bummer!

Pelayan SQL

Dan di sini sekali lagi tidak!

|--Table Scan(OBJECT:([film]), WHERE:([film_id]=[film_id]))

Ringkasan

Ia kelihatan seperti pengoptimuman yang mudah, tetapi ia tidak digunakan dalam semua DBMS; khususnya, anehnya, ia tidak digunakan dalam SQL Server!

5. Unjuran dalam subkueri EXISTS

Menariknya, saya selalu ditanya tentang mereka di kelas induk saya, di mana saya mempertahankan pandangan bahawa SELECT * biasanya tidak membawa kepada apa-apa kebaikan. Persoalannya ialah: adakah mungkin untuk menggunakan SELECT * dalam subquery EXISTS ? Sebagai contoh, jika kita perlu mencari pelakon yang bermain dalam filem...

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT * -- Is this OK?
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Dan jawapannya... ya. boleh. Asterisk tidak menjejaskan permintaan. Bagaimana anda boleh yakin tentang ini? Pertimbangkan pertanyaan berikut:

-- DB2
SELECT 1 / 0 FROM sysibm.dual
-- Oracle
SELECT 1 / 0 FROM dual
-- PostgreSQL, SQL Server
SELECT 1 / 0
-- MySQL
SELECT pow(-1, 0.5);

Semua pangkalan data ini melaporkan pembahagian dengan ralat sifar. Perhatikan fakta menarik: dalam MySQL, apabila kita membahagi dengan sifar, kita mendapat NULL dan bukannya ralat, jadi kita perlu melakukan satu lagi tindakan yang menyalahi undang-undang. Sekarang, apakah yang berlaku jika kita melaksanakan, bukannya di atas, pertanyaan berikut?

-- DB2
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM sysibm.dual
) THEN 1 ELSE 0 END
FROM sysibm.dual
-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
-- SQL Server
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0
) THEN 1 ELSE 0 END
-- MySQL
SELECT EXISTS (SELECT pow(-1, 0.5));

Kini tiada satu pun pangkalan data mengembalikan ralat. Mereka semua kembali BENAR atau 1 . Ini bermakna tiada pangkalan data kami benar-benar menilai unjuran (iaitu, klausa SELECT ) subquery EXISTS . SQL Server, sebagai contoh, menunjukkan pelan berikut:

|--Constant Scan(VALUES:((CASE WHEN (1) THEN (1) ELSE (0) END)))

Seperti yang anda lihat, ungkapan CASE telah ditukar kepada pemalar dan subquery telah dihapuskan. Pangkalan data lain menyimpan subquery dalam pelan dan tidak menyebut apa-apa tentang unjuran, jadi mari kita lihat sekali lagi rancangan pertanyaan asal dalam Oracle:

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT *
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Pelan pertanyaan di atas kelihatan seperti ini:

------------------------------------------------------------------
| Id  | Operation             | Name                    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |        |
|*  1 |  HASH JOIN SEMI       |                         |    200 |
|   2 |   TABLE ACCESS FULL   | ACTOR                   |    200 |
|   3 |   INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR |   5462 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) LAST_NAME, FIRST_NAME
   2 - (rowset=256) A.ACTOR_ID, FIRST_NAME, LAST_NAME
   3 - FA.ACTOR_ID

Kami memerhati maklumat tentang unjuran pada Id=3 . Malah, kami tidak mengakses jadual FILM_ACTOR pun kerana kami tidak perlu. Predikat EXISTS boleh dilakukan menggunakan indeks kunci asing pada satu lajur ACTOR_ID - semua yang diperlukan untuk pertanyaan ini - walaupun kami menulis SELECT * .

Ringkasan

Nasib baik, semua pangkalan data kami mengalih keluar unjuran daripada subkueri EXISTS : Nantikan Bahagian 3 , di mana kita akan membincangkan pengoptimuman SQL yang hebat yang lain.