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

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

8. SEMAK sekatan

Oh, ini adalah perkara yang menarik! Pangkalan data Sakila kami mempunyai kekangan SEMAK pada lajur 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 kekangan CHECK untuk memastikan integriti data. Kos untuk menambahnya adalah sangat rendah - lebih kurang daripada sekatan lain, contohnya, PRIMARY , UNIK atau FOREIGN KEY , kerana mereka tidak memerlukan indeks untuk berfungsi, jadi anda boleh mendapatkannya secara praktikal "secara percuma". Tetapi ada nuansa menarik yang berkaitan dengan pengoptimuman! Pertimbangkan pertanyaan berikut:

Predikat yang mustahil

Kami telah pun menemui mustahil predikat , malah NOT NULL kekangan (yang sebenarnya merupakan jenis khas kekangan CHECK ), tetapi yang ini lebih keren:

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

Tiada filem sedemikian, dan tidak boleh ada, kerana kekangan CHECK menghalang penyisipannya (atau kemas kini). Sekali lagi, ini sepatutnya diterjemahkan kepada arahan untuk tidak melakukan apa-apa. 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');

Terima kasih kepada indeks di atas, mungkin cukup dengan hanya melakukan imbasan pantas indeks dan mengira semua filem dengan rating = 'NC-17' , memandangkan itu sahaja rating yang tinggal. Jadi pertanyaan harus ditulis semula seperti ini:

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

Ini sepatutnya berlaku tanpa mengira indeks, kerana membandingkan lajur dengan satu nilai adalah lebih pantas daripada membandingkan dengan 4. Jadi, pangkalan data apa yang boleh melakukan ini?

DB2

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

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

Predicate Information
 2 - RESID (1 = 0)

Predikat songsang (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'))

Walaupun langkah ID=3 menggunakan indeks, dan walaupun kardinaliti adalah betul, imbasan penuh berlaku kerana pelan itu tidak mempunyai predikat julat, hanya predikat "SARG". Lihat ulasan Marcus Wynd untuk butiran . Anda juga boleh menunjukkan ini dengan menyongsangkan 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 mempunyai predikat julat yang dikehendaki.

MySQL

MySQL menyokong sintaks kekangan CHECK , tetapi atas sebab tertentu tidak menguatkuasakannya. Cuba ini:

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

dan anda akan mendapat:

A
-
0

Mata sifar untuk MySQL (benar-benar, mengapa tidak menyokong kekangan CHECK sahaja ?)

Oracle

Predikat mustahil (penilaian = '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, penapis yang sangat pelik NULL IS NOT NULL , memotong FULL TABLE SCAN , yang boleh dengan mudah dialih keluar daripada pelan sama sekali. Tetapi sekurang-kurangnya ia berfungsi! Predikat songsang (rating = 'NC-17') Oops:

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

Predikat tidak boleh disongsangkan, penilaian kardinaliti sangat pincang, di samping itu kita mendapat INDEX FAST FULL SCAN bukannya INDEX RANGE SCAN , dan predikat penapis bukannya predikat akses . Tetapi inilah yang harus kita dapatkan, sebagai contoh, dengan menyongsangkan 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')

Bummer!

PostgreSQL

Ambil perhatian bahawa versi PostgreSQL pangkalan data Sakila menggunakan jenis ENUM dan bukannya kekangan CHECK pada lajur RATING . Saya menduplikasi jadual menggunakan kekangan CHECK sebaliknya . Predikat mustahil (penilaian = '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 terbalik (rating = 'NC-17') tidak berfungsi sama ada:

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

maaf sangat! Nota: Seperti yang ditunjukkan oleh David Rowley kepada kami dalam ulasan , ciri ini boleh didayakan dengan menetapkan parameter:

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

Predikat songsang (rating = 'NC-17') Ya juga!

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

Ringkasan

9. Sambungan refleksif yang tidak perlu.

Apabila pertanyaan anda menjadi lebih kompleks, anda mungkin akhirnya perlu melakukan gabungan reflektif pada jadual berdasarkan kunci utamanya. Percayalah, ini adalah amalan yang sangat biasa apabila membina pandangan yang kompleks dan menghubungkannya antara satu sama lain, jadi memastikan pangkalan data memberi perhatian kepada perkara ini adalah bahagian penting dalam mengoptimumkan kod SQL yang kompleks. Saya tidak akan menunjukkan contoh yang kompleks, yang mudah akan mencukupi, contohnya:

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

Ini boleh dilihat sebagai satu kes khas penyingkiran JOIN , kerana kita sebenarnya tidak memerlukan gabungan ke A2 , kita boleh melakukan semua yang kita perlukan hanya dengan jadual A1 . Seterusnya, penyingkiran INNER JOIN hanya berfungsi dengan baik jika terdapat KUNCI ASING , yang kami tiada di sini. Tetapi terima kasih kepada kunci utama oleh ACTOR_ID , kita boleh membuktikan bahawa sebenarnya A1 = A2 . Dari satu segi, ini sekali lagi penutupan transitif . Anda boleh pergi lebih jauh dan menggunakan lajur dari kedua-dua jadual A1 dan A2 :

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

Dalam kes klasik penyingkiran JOIN , ia tidak lagi boleh dihapuskan kerana kedua-dua jadual diunjurkan. Tetapi kerana kita telah membuktikan bahawa A1 = A2 , maka ia boleh ditukar ganti, jadi kita boleh mengharapkan pertanyaan ditukar kepada:

SELECT first_name, last_name
FROM actor;

Apakah DBMS boleh melakukan ini?

DB2

Unjuran jadual A1 sahaja Ya:

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

Unjuran jadual A1 dan A2 ... juga ya:

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

MySQL

Unjuran jadual A1 sahaja No.

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

Unjuran jadual A1 dan A2 ... juga tidak

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

kekecewaan sepenuhnya...

Oracle

Unjuran jadual A1 sahaja Ya

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

Unjuran jadual A1 dan A2 Ya sekali lagi

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

PostgreSQL

Unjuran jadual A1 sahaja 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)

Unjuran jadual A1 dan A2 Dan sekali 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)

Pelayan SQL

Unjuran jadual A1 sahaja Anehnya, tidak! (Tetapi perlu diingat bahawa saya menggunakan SQL Server 2014, versi yang lebih baharu mungkin membetulkannya. Saya pasti boleh menggunakan peningkatan!)

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

Unjuran jadual A1 dan A2 Tidak lagi, malah rancangannya berubah menjadi lebih teruk:

|--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 katakan, saya menjangkakan bahawa pengoptimuman ini akan dilakukan pada semua pangkalan data, tetapi saya sangat tersilap, sedihnya. Bersama-sama dengan menghapuskan JOIN , ini adalah salah satu pengoptimuman yang paling penting, membolehkan anda membina pertanyaan SQL yang besar daripada bahagian yang boleh digunakan semula seperti paparan dan fungsi jadual. Malangnya, ia tidak disokong dalam 3 daripada 5 pangkalan data yang paling biasa.