Pengoptimalan SQL keren yang tidak bergantung pada model biaya. Bagian 2

Pengoptimalan SQL keren yang tidak bergantung pada model biaya. Bagian 1

4. Penghapusan predikat “tidak bermakna”.

Yang juga tidak bermakna adalah predikat yang (hampir) selalu benar. Seperti yang dapat Anda bayangkan, jika Anda bertanya:

SELECT * FROM actor WHERE 1 = 1;

...maka database tidak akan benar-benar mengeksekusinya, tapi akan mengabaikannya begitu saja. Saya pernah menjawab pertanyaan tentang ini di Stack Overflow dan itulah mengapa saya memutuskan untuk menulis artikel ini. Saya akan menyerahkan pengujian ini sebagai latihan kepada pembaca, tetapi apa yang terjadi jika predikatnya kurang "tidak berarti"? Misalnya:

SELECT * FROM film WHERE release_year = release_year;

Apakah Anda benar-benar perlu membandingkan nilainya dengan nilai itu sendiri untuk setiap baris? Tidak, tidak ada nilai yang predikatnya FALSE , bukan? Tapi kami masih perlu memeriksanya. Meskipun predikatnya tidak bisa sama dengan FALSE , predikatnya mungkin sama dengan NULL di mana pun , sekali lagi karena logika tiga nilai. Kolom RELEASE_YEAR dapat dibatalkan, dan jika salah satu baris memiliki RELEASE_YEAR IS NULL , maka NULL = NULL menghasilkan NULL dan baris tersebut harus dihilangkan. Jadi permintaannya menjadi sebagai berikut:

SELECT * FROM film WHERE release_year IS NOT NULL;

Basis 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

Sayang sekali, tapi MySQL, sekali lagi, tidak memetakan predikat ke dalam rencana eksekusi, jadi mencari tahu apakah MySQL mengimplementasikan pengoptimalan khusus ini agak rumit. Anda dapat melakukan penilaian kinerja dan melihat apakah ada perbandingan skala besar yang dilakukan. Atau Anda dapat menambahkan indeks:

CREATE INDEX i_release_year ON film (release_year);

Dan dapatkan rencana untuk permintaan berikut sebagai imbalannya:

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

Jika pengoptimalan berhasil, maka rencana kedua kueri harus kurang lebih sama. Namun dalam kasus ini tidak demikian:

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, kedua kueri kami berbeda secara signifikan dalam nilai kolom POSSIBLE_KEYS dan FILTERED . Jadi saya berani menebak bahwa MySQL tidak mengoptimalkan ini.

Peramal

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

Sayangnya tidak ada!

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

Paket dan biaya bervariasi. Yakni melihat penilaian kardinalitas yang sama sekali tidak baik, sedangkan predikatnya:

SELECT * FROM film WHERE release_year IS NOT NULL;

memberikan hasil yang jauh lebih baik:

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

Kekecewaan!

SQLServer

Anehnya, SQL Server sepertinya juga tidak melakukan ini:

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

Namun, berdasarkan tampilan rencana, penilaian kardinalitasnya sudah tepat, begitu pula biayanya. Namun dalam pengalaman saya dengan SQL Server, saya akan mengatakan bahwa dalam kasus ini, tidak ada optimasi yang terjadi, karena SQL Server akan menampilkan predikat yang sebenarnya dieksekusi dalam rencana (untuk mengetahui alasannya, lihat contoh batasan CHECK di bawah). Bagaimana dengan predikat "tidak berarti" pada kolom NOT NULL ? Konversi di atas hanya diperlukan karena RELEASE_YEAR tidak dapat ditentukan. Apa yang terjadi jika Anda menjalankan kueri tidak berarti yang sama, misalnya, kolom FILM_ID ?

SELECT * FROM film WHERE film_id = film_id

Apakah sekarang tidak ada predikat sama sekali? Atau setidaknya begitulah seharusnya. Tapi benarkah?

DB2

Ya!

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

Tidak ada predikat yang diterapkan sama sekali dan kami memilih semua film.

MySQL

Ya! (Sekali lagi, tebakan cerdas)

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

Perhatikan bahwa kolom EXTRA sekarang kosong, seolah-olah kita tidak mempunyai klausa WHERE sama sekali!

Peramal

Ya!

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

Sekali lagi, tidak ada predikat yang berlaku.

PostgreSQL

Wah, tidak lagi!

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

Filter diterapkan dan skor kardinalitasnya masih 5. Sayang sekali!

SQLServer

Dan di sini sekali lagi tidak!

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

Ringkasan

Sepertinya optimasi sederhana, tetapi tidak digunakan di semua DBMS; khususnya, anehnya, ini tidak digunakan di SQL Server!

5. Proyeksi dalam subkueri EXISTS

Menariknya, saya selalu ditanya tentangnya di kelas master saya, di mana saya mempertahankan sudut pandang bahwa SELECT * biasanya tidak membawa kebaikan. Pertanyaannya adalah: apakah mungkin menggunakan SELECT * dalam subkueri EXISTS ? Misalnya, jika kita perlu mencari aktor yang bermain di film...

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 jawabannya adalah... ya. Bisa. Tanda bintang tidak mempengaruhi permintaan. Bagaimana Anda bisa yakin akan hal 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 database ini melaporkan kesalahan pembagian dengan nol. Perhatikan fakta menarik: di MySQL, ketika kita membaginya dengan nol, kita mendapatkan NULL alih-alih kesalahan, jadi kita harus melakukan hal lain yang tidak diperbolehkan. Sekarang, apa yang terjadi jika kita menjalankan, bukan pertanyaan 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));

Sekarang tidak ada database yang mengembalikan kesalahan. Semuanya mengembalikan TRUE atau 1 . Ini berarti bahwa tidak ada database kami yang benar-benar mengevaluasi proyeksi (yaitu, klausa SELECT ) dari subkueri EXISTS . SQL Server, misalnya, memperlihatkan rencana berikut:

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

Seperti yang Anda lihat, ekspresi CASE telah diubah menjadi konstanta dan subquery telah dihilangkan. Basis data lain menyimpan subkueri dalam rencana dan tidak menyebutkan apa pun tentang proyeksinya, jadi mari kita lihat lagi rencana kueri asli di Oracle:

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

Rencana kueri di atas terlihat 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 mengamati informasi tentang proyeksi di Id=3 . Faktanya, kita bahkan tidak mengakses tabel FILM_ACTOR karena tidak perlu. Predikat EXISTS dapat dilakukan dengan menggunakan indeks kunci asing pada satu kolom ACTOR_ID - semua yang diperlukan untuk kueri ini - meskipun kami menulis SELECT * .

Ringkasan

Untungnya, semua database kami menghapus proyeksi dari subkueri EXISTS : Nantikan Bagian 3 , di mana kita akan membahas optimasi SQL keren lainnya.