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

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

8. PERIKSA batasan

Oh, ini barang keren! Database Sakila kami memiliki batasan CHECK pada kolom FILM.RATING :

CREATE TABLE film (
  ..
  RATING varchar(10) DEFAULT 'G',
  ..
  CONSTRAINT check_special_rating
    CHECK (rating IN ('G','PG','PG-13','R','NC-17')),
  ..
);

Serius, gunakan batasan CHECK untuk memastikan integritas data. Biaya untuk menambahkannya sangat rendah - jauh lebih murah dibandingkan batasan lainnya, misalnya PRIMARY , UNIQUE atau FOREIGN KEY , karena tidak memerlukan indeks untuk berfungsi, sehingga Anda mendapatkannya secara praktis "gratis". Namun ada nuansa menarik terkait optimasi! Pertimbangkan pertanyaan berikut:

Predikat yang mustahil

Kita telah menemukan batasan yang tidak mungkin , bahkan batasan NOT NULL (yang sebenarnya merupakan jenis batasan CHECK yang khusus ), namun yang ini lebih keren lagi:

SELECT *
FROM film
WHERE rating = 'N/A';

Tidak ada film seperti itu, dan tidak mungkin ada, karena batasan CHECK mencegah penyisipan (atau pembaruan). Sekali lagi, ini seharusnya diterjemahkan menjadi perintah untuk tidak melakukan apa pun. Bagaimana dengan permintaan ini?

CREATE INDEX idx_film_rating ON film (rating);

SELECT count(*)
FROM film
WHERE rating NOT IN ('G','PG','PG-13','R');

Berkat indeks di atas, mungkin cukup dengan melakukan pemindaian cepat terhadap indeks dan menghitung semua film dengan rating = 'NC-17' , karena hanya itulah peringkat yang tersisa. Jadi kuerinya harus ditulis ulang seperti ini:

SELECT count(*)
FROM film
WHERE rating = 'NC-17';

Hal ini harus terjadi terlepas dari indeksnya, karena membandingkan kolom dengan satu nilai lebih cepat daripada membandingkan dengan 4. Jadi, database apa yang bisa melakukan ini?

DB2

Predikat mustahil (rating = 'N/A') Keren!

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

Predicate Information
 2 - RESID (1 = 0)

Predikat terbalik (rating = 'NC-17') Tidak...

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |   34
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |   34
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |   34

Predicate Information
 3 - SARG  NOT(Q1.RATING IN ('G', 'PG', 'PG-13', 'R'))

Meskipun langkah ID=3 menggunakan indeks, dan meskipun kardinalitasnya benar, pemindaian penuh terjadi karena rencana tersebut tidak memiliki predikat rentang, hanya predikat "SARG". Lihat ulasan Marcus Wynand untuk detailnya . Anda juga dapat mendemonstrasikannya dengan membalik predikat secara manual dan mendapatkan:

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |    7
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |    7
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |    7

Predicate Information
 3 - START (Q1.RATING = 'NC-17')
      STOP (Q1.RATING = 'NC-17')

Sekarang kita memiliki predikat rentang yang diinginkan.

MySQL

MySQL mendukung sintaks batasan CHECK , tetapi karena alasan tertentu tidak menerapkannya. Coba ini:

CREATE TABLE x (a INT CHECK (a != 0));
INSERT INTO x VALUES (0);
SELECT * FROM x;

dan Anda akan mendapatkan:

A
-
0

Poin nol untuk MySQL (sungguh, mengapa tidak mendukung batasan CHECK saja ?)

Peramal

Predikat mustahil (rating = 'N/A')

--------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |
|*  1 |  FILTER            |      |      1 |        |      0 |
|*  2 |   TABLE ACCESS FULL| FILM |      0 |     89 |      0 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)
   2 - filter("RATING"='N/A')

Sekali lagi, filter yang sangat aneh NULL IS NOT NULL , memotong FULL TABLE SCAN , yang dapat dengan mudah dihapus dari rencana sama sekali. Tapi setidaknya itu berhasil! Predikat terbalik (rating = 'NC-17') Ups:

----------------------------------------------------------------------------
| Id  | Operation             | Name            | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE       |                 |      1 |      1 |      1 |
|*  2 |   INDEX FAST FULL SCAN| IDX_FILM_RATING |      1 |    415 |    210 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((RATING'PG-13' AND RATING'R' AND RATING'PG' AND RATING'G'))

Predikatnya tidak bisa dibalik, penilaian kardinalitasnya sangat timpang, selain itu kita mendapatkan INDEX FAST FULL SCAN alih-alih INDEX RANGE SCAN , dan predikat filter alih-alih predikat akses . Namun inilah yang seharusnya kita dapatkan, misalnya dengan membalik predikat secara manual:

------------------------------------------------------------------------
| Id  | Operation         | Name            | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE   |                 |      1 |      1 |      1 |
|*  2 |   INDEX RANGE SCAN| IDX_FILM_RATING |      1 |    210 |    210 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RATING"='NC-17')

Kekecewaan!

PostgreSQL

Perhatikan bahwa database Sakila versi PostgreSQL menggunakan tipe ENUM alih - alih batasan CHECK pada kolom RATING . Saya menduplikasi tabel menggunakan batasan CHECK sebagai gantinya . Predikat mustahil (rating = 'N/A') Tidak berfungsi:

QUERY PLAN
------------------------------------------------------
Seq Scan on film2  (cost=0.00..67.50 rows=1 width=385)
  Filter: ((rating)::text = 'N/A'::text)

Predikat sebaliknya (rating = 'NC-17') juga tidak berfungsi:

QUERY PLAN
------------------------------------------------------------------
Aggregate  (cost=70.53..70.54 rows=1 width=8)
  ->  Seq Scan on film2  (cost=0.00..70.00 rows=210 width=0)
        Filter: ((rating)::text  ALL ('{G,PG,PG-13,R}'::text[]))

Sangat menyesal! Catatan: Seperti yang ditunjukkan oleh David Rowley kepada kami di komentar , fitur ini dapat diaktifkan dengan mengatur parameter:

SET constraint_exclusion TO on;
SQL Server
Невозможный предикат (rating = 'N/A')
Да!
|--Constant Scan

Predikat terbalik (rating = 'NC-17') Iya juga!

|--Compute Scalar
     |--Stream Aggregate
          |--Index Seek(OBJECT:([idx_film_rating]), SEEK:([rating]='NC-17'))

Ringkasan

9. Koneksi refleksif yang tidak perlu.

Saat kueri Anda menjadi lebih kompleks, Anda mungkin perlu melakukan penggabungan reflektif pada tabel berdasarkan kunci utamanya. Percayalah, ini adalah praktik yang sangat umum ketika membangun tampilan kompleks dan menghubungkannya satu sama lain, jadi memastikan database memperhatikan hal ini adalah bagian penting dalam mengoptimalkan kode SQL yang kompleks. Saya tidak akan menunjukkan contoh yang rumit, yang sederhana saja sudah cukup, misalnya:

SELECT a1.first_name, a1.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

Hal ini dapat dilihat sebagai kasus khusus dari eliminasi JOIN , karena kita sebenarnya tidak memerlukan join ke A2 , kita dapat melakukan semua yang kita perlukan hanya dengan tabel A1 . Selanjutnya, eliminasi INNER JOIN hanya berfungsi dengan baik jika ada FOREIGN KEY yang tidak kita miliki di sini. Namun berkat kunci utama ACTOR_ID , kami dapat membuktikan bahwa sebenarnya A1 = A2 . Dalam arti tertentu, ini lagi-lagi merupakan penutupan transitif . Anda dapat melangkah lebih jauh lagi dan menggunakan kolom dari tabel A1 dan A2 :

SELECT a1.first_name, a2.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

Dalam kasus klasik eliminasi JOIN , tidak mungkin lagi menghilangkannya karena kedua tabel diproyeksikan. Namun karena kita telah membuktikan bahwa A1 = A2 , maka keduanya dapat dipertukarkan, sehingga kita dapat mengharapkan kueri dikonversi menjadi:

SELECT first_name, last_name
FROM actor;

DBMS apa yang bisa melakukan ini?

DB2

Proyeksi tabel A1 saja Ya:

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

Proyeksi tabel A1 dan A2 ...juga ya:

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

MySQL

Proyeksi tabel A1 saja No.

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id  Using index

Proyeksi tabel A1 dan A2 ...juga tidak

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id

Kekecewaan total...

Peramal

Proyeksi tabel A1 saja Ya

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

Proyeksi tabel A1 dan A2 Ya lagi

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

PostgreSQL

Proyeksi tabel A1 saja No:

QUERY PLAN
--------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=17)
  ->  Hash  (cost=4.00..4.00 rows=200 width=4)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=4)

Proyeksi tabel A1 dan A2 Dan lagi tidak:

QUERY PLAN
---------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=10)
  ->  Hash  (cost=4.00..4.00 rows=200 width=11)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=11)

SQLServer

Proyeksi tabel A1 saja Anehnya, tidak! (Namun perlu diingat bahwa saya menggunakan SQL Server 2014, versi yang lebih baru mungkin telah memperbaikinya. Saya pasti dapat menggunakan pemutakhiran!)

|--Merge Join(Inner Join, MERGE:([a2].[actor_id])=([a1].[actor_id]))
     |--Index Scan(OBJECT:([a2]))
     |--Sort(ORDER BY:([a1].[actor_id] ASC))
          |--Table Scan(OBJECT:([a1]))

Proyeksi tabel A1 dan A2 Tidak lagi, dan rencananya malah berubah menjadi lebih buruk:

|--Hash Match(Inner Join, HASH:([a1].[actor_id])=([a2].[actor_id]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a1]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a2]))

Ringkasan

Terus terang saya berharap optimasi ini bisa dilakukan di semua database, tapi sayangnya saya salah besar. Selain menghilangkan JOIN , ini adalah salah satu pengoptimalan terpenting, yang memungkinkan Anda membuat kueri SQL berukuran besar dari bagian yang dapat digunakan kembali seperti tampilan dan fungsi tabel. Sayangnya, ini tidak didukung di 3 dari 5 database paling umum.