Coole SQL-Optimierungen, die nicht vom Kostenmodell abhängen. Teil 4

Coole SQL-Optimierungen, die nicht vom Kostenmodell abhängen. Teil 1 Coole SQL-Optimierungen, die nicht vom Kostenmodell abhängen. Teil 2 Coole SQL-Optimierungen, die nicht vom Kostenmodell abhängen. Teil 3

8. PRÜFEN Sie die Einschränkungen

Oh, das ist cooles Zeug! Unsere Sakila- Datenbank verfügt über eine CHECK- Einschränkung für die Spalte FILM.RATING :

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

Im Ernst: Verwenden Sie CHECK- Einschränkungen , um die Datenintegrität sicherzustellen. Die Kosten für das Hinzufügen sind äußerst gering – viel geringer als bei anderen Einschränkungen, zum Beispiel PRIMARY , UNIQUE oder FOREIGN KEY , da sie keinen Index benötigen, um zu funktionieren, sodass Sie sie praktisch „kostenlos“ erhalten. Aber es gibt eine interessante Nuance im Zusammenhang mit der Optimierung! Betrachten Sie die folgenden Abfragen:

Unmögliche Prädikate

Wir sind bereits auf unmögliche Prädikate gestoßen, sogar auf NOT NULL- Einschränkungen (die eigentlich eine besondere Art von CHECK- Einschränkungen sind ), aber diese hier ist noch cooler:

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

Es gibt keinen solchen Film und kann es auch nicht geben, da die CHECK- Einschränkung das Einfügen (oder Aktualisieren) verhindert. Auch dies sollte zu einem Befehl führen, nichts zu tun. Was ist mit dieser Anfrage?

CREATE INDEX idx_film_rating ON film (rating);

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

Dank des obigen Index reicht es wahrscheinlich aus, einfach einen kurzen Blick auf den Index zu werfen und alle Filme mit der Bewertung = „NC-17“ zu zählen , da dies die einzige verbleibende Bewertung ist. Daher sollte die Abfrage wie folgt umgeschrieben werden:

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

Dies sollte unabhängig vom Index der Fall sein, da der Vergleich einer Spalte mit einem Wert schneller ist als der Vergleich mit 4. Welche Datenbanken können das also?

DB2

Unmögliches Prädikat (Bewertung = „N/A“) Cool!

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

Predicate Information
 2 - RESID (1 = 0)

Umgekehrtes Prädikat (Bewertung = „NC-17“) Nein ...

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

Obwohl der ID=3-Schritt einen Index verwendet und die Kardinalitäten korrekt sind, erfolgt ein vollständiger Scan, da der Plan kein Bereichsprädikat, sondern nur das Prädikat „SARG“ enthält. Weitere Informationen finden Sie in der Rezension von Marcus Wynand . Sie können dies auch demonstrieren, indem Sie das Prädikat manuell invertieren und Folgendes erhalten:

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

Jetzt haben wir das gewünschte Bereichsprädikat.

MySQL

MySQL unterstützt die CHECK- Einschränkungssyntax , erzwingt sie jedoch aus irgendeinem Grund nicht. Versuche dies:

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

und Sie erhalten:

A
-
0

Null Punkte für MySQL (wirklich, warum nicht einfach CHECK- Einschränkungen unterstützen ?)

Orakel

Unmögliches Prädikat (Bewertung = „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')

Wieder ein sehr seltsamer Filter NULL IS NOT NULL , der FULL TABLE SCAN abschneidet , der genauso gut ganz aus dem Plan entfernt werden könnte. Aber zumindest funktioniert es! Prädikat umkehren (Bewertung = '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'))

Das Prädikat kann nicht invertiert werden, die Kardinalitätsbewertung ist sehr lahm, außerdem erhalten wir INDEX FAST FULL SCAN anstelle von INDEX RANGE SCAN und das Filterprädikat anstelle des Zugriffsprädikats . Aber das sollten wir zum Beispiel bekommen, wenn wir das Prädikat manuell invertieren:

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

Schade!

PostgreSQL

Beachten Sie, dass die PostgreSQL- Version der Sakila- Datenbank den ENUM- Typ anstelle von CHECK- Einschränkungen für die RATING- Spalte verwendet . Ich habe die Tabelle stattdessen mit einer CHECK- Einschränkung dupliziert . Unmögliches Prädikat (Bewertung = „N/A“) Funktioniert nicht:

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

Das umgekehrte Prädikat (Bewertung = 'NC-17') funktioniert ebenfalls nicht:

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

Sehr schade! Hinweis: Wie David Rowley uns in den Kommentaren freundlicherweise darauf hingewiesen hat , kann diese Funktion durch Setzen des Parameters aktiviert werden:

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

Umgekehrtes Prädikat (Bewertung = „NC-17“) Ja auch!

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

Zusammenfassung

9. Unnötige reflexive Verbindungen.

Wenn Ihre Abfragen komplexer werden, kann es durchaus sein, dass Sie einen reflektiven Join für eine Tabelle basierend auf ihrem Primärschlüssel durchführen müssen. Glauben Sie mir, dies ist eine sehr gängige Praxis beim Erstellen komplexer Ansichten und deren Verbindung untereinander. Daher ist es ein entscheidender Teil der Optimierung von komplexem SQL-Code, sicherzustellen, dass die Datenbank dies berücksichtigt. Ich werde kein komplexes Beispiel zeigen, ein einfaches reicht aus, zum Beispiel:

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

Dies kann als Sonderfall der JOIN-Eliminierung angesehen werden , da wir eigentlich keinen Join mit A2 benötigen, sondern alles, was wir brauchen, nur mit Tabelle A1 erledigen können . Als nächstes funktioniert die Eliminierung von INNER JOIN nur dann ordnungsgemäß, wenn ein FOREIGN KEY vorhanden ist , den wir hier nicht haben. Aber dank des Primärschlüssels von ACTOR_ID können wir tatsächlich beweisen, dass A1 = A2 ist . In gewissem Sinne ist dies wiederum eine transitive Schließung . Sie können sogar noch weiter gehen und Spalten aus den beiden Tabellen A1 und A2 verwenden :

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

Im klassischen Fall der JOIN- Eliminierung wäre eine Eliminierung nicht mehr möglich, da beide Tabellen projiziert werden. Da wir jedoch bereits bewiesen haben, dass A1 = A2 , sind sie austauschbar, sodass wir davon ausgehen können, dass die Abfrage wie folgt konvertiert wird:

SELECT first_name, last_name
FROM actor;

Welches DBMS kann das?

DB2

Projektion nur von Tisch A1 Ja:

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

Projektion der Tabellen A1 und A2 ... auch ja:

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

MySQL

Projektion nur von Tisch A1 Nr.

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

Projektion der Tabellen A1 und A2 ... auch nein

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

Völlige Enttäuschung...

Orakel

Projektion nur von Tisch A1 Ja

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

Projektion der Tabellen A1 und A2 Nochmals Ja

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

PostgreSQL

Projektion nur von Tisch A1 Nein:

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)

Projektion der Tabellen A1 und A2 Und wieder nein:

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)

SQL Server

Nur Projektion von Tisch A1. Seltsamerweise nein! (Denken Sie aber daran, dass ich SQL Server 2014 verwende. In neueren Versionen ist dieses Problem möglicherweise behoben. Ich könnte auf jeden Fall ein Upgrade gebrauchen!)

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

Projektion der Tabellen A1 und A2 Nicht schon wieder, und der Plan änderte sich sogar zum Schlechten:

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

Zusammenfassung

Ehrlich gesagt hatte ich erwartet, dass diese Optimierung für alle Datenbanken durchgeführt würde, aber leider habe ich mich sehr geirrt. Neben der Eliminierung von JOIN ist dies eine der wichtigsten Optimierungen, die es Ihnen ermöglicht, umfangreiche SQL-Abfragen aus wiederverwendbaren Teilen wie Ansichten und Tabellenfunktionen zu erstellen. Leider wird es in drei der fünf häufigsten Datenbanken nicht unterstützt.