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

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

4. Eliminierung „bedeutungsloser“ Prädikate

Ebenso bedeutungslos sind Prädikate, die (fast) immer wahr sind. Wie Sie sich vorstellen können, wenn Sie fragen:

SELECT * FROM actor WHERE 1 = 1;

... dann führen die Datenbanken es nicht wirklich aus, sondern ignorieren es einfach. Ich habe einmal eine Frage dazu auf Stack Overflow beantwortet und deshalb habe ich beschlossen, diesen Artikel zu schreiben. Ich überlasse es dem Leser, dies als Übung zu testen, aber was passiert, wenn das Prädikat etwas weniger „bedeutungslos“ ist? Zum Beispiel:

SELECT * FROM film WHERE release_year = release_year;

Müssen Sie den Wert wirklich für jede Zeile mit sich selbst vergleichen? Nein, es gibt keinen Wert, für den dieses Prädikat FALSE wäre , oder? Aber wir müssen es noch überprüfen. Obwohl das Prädikat nicht gleich FALSE sein kann, kann es aufgrund der dreiwertigen Logik durchaus überall gleich NULL sein. Die Spalte RELEASE_YEAR ist nullbar, und wenn in einer der Zeilen RELEASE_YEAR IS NULL vorhanden ist , führt NULL = NULL zu NULL und die Zeile muss entfernt werden. Die Anfrage sieht also wie folgt aus:

SELECT * FROM film WHERE release_year IS NOT NULL;

Welche Datenbanken machen das?

DB2

Ja!

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

Es ist schade, aber MySQL ordnet Prädikate wiederum nicht den Ausführungsplänen zu. Daher ist es etwas schwierig herauszufinden, ob MySQL diese spezielle Optimierung implementiert. Sie können eine Leistungsbewertung durchführen und sehen, ob umfangreiche Vergleiche durchgeführt werden. Oder Sie können einen Index hinzufügen:

CREATE INDEX i_release_year ON film (release_year);

Und erhalten Sie im Gegenzug Pläne für die folgenden Anfragen:

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

Wenn die Optimierung funktioniert, sollten die Pläne beider Abfragen ungefähr gleich sein. In diesem Fall ist dies jedoch nicht der Fall:

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

Wie Sie sehen, unterscheiden sich unsere beiden Abfragen erheblich in den Werten der Spalten POSSIBLE_KEYS und FILTERED . Daher gehe ich davon aus, dass MySQL dies nicht optimiert.

Orakel

Ja!

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

Leider nein!

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

Pläne und Kosten variieren. Schauen Sie sich nämlich die Bewertung der Kardinalität an, die absolut nicht gut ist, während dieses Prädikat:

SELECT * FROM film WHERE release_year IS NOT NULL;

liefert viel bessere Ergebnisse:

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

Schade!

SQL Server

Seltsamerweise scheint SQL Server dies auch nicht zu tun:

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

Aufgrund des Erscheinungsbilds des Plans ist die Kardinalitätsbewertung jedoch korrekt, ebenso wie die Kosten. Aufgrund meiner Erfahrung mit SQL Server würde ich jedoch sagen, dass in diesem Fall keine Optimierung stattfindet, da SQL Server das tatsächlich ausgeführte Prädikat im Plan anzeigen würde (um zu sehen, warum, werfen Sie einen Blick auf die CHECK- Einschränkungsbeispiele unten). Was ist mit „bedeutungslosen“ Prädikaten für NOT NULL- Spalten? Die obige Konvertierung war nur notwendig, weil RELEASE_YEAR undefiniert sein kann. Was passiert, wenn Sie dieselbe bedeutungslose Abfrage beispielsweise für die Spalte FILM_ID ausführen ?

SELECT * FROM film WHERE film_id = film_id

Entspricht es nun überhaupt keinem Prädikat? Zumindest sollte es so sein. Aber ist es?

DB2

Ja!

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

Es werden überhaupt keine Prädikate angewendet und wir wählen alle Filme aus.

MySQL

Ja! (Wieder eine fundierte Vermutung)

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

Beachten Sie, dass die EXTRA- Spalte jetzt leer ist, als ob wir überhaupt keine WHERE-Klausel hätten !

Orakel

Ja!

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

Auch hier gelten keine Prädikate.

PostgreSQL

Wow, nein schon wieder!

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

Der Filter wird angewendet und der Kardinalitätswert beträgt immer noch 5. Schade!

SQL Server

Und auch hier wieder nein!

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

Zusammenfassung

Es scheint eine einfache Optimierung zu sein, aber sie wird nicht in allen DBMS verwendet; insbesondere wird sie seltsamerweise nicht in SQL Server verwendet!

5. Projektionen in EXISTS-Unterabfragen

Interessanterweise werde ich in meiner Meisterklasse ständig danach gefragt, wo ich den Standpunkt vertrete, dass SELECT * normalerweise zu nichts Gutem führt. Die Frage ist: Ist es möglich, SELECT * in einer EXISTS- Unterabfrage zu verwenden ? Wenn wir zum Beispiel Schauspieler finden müssen, die in Filmen mitgespielt haben ...

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
)

Und die Antwort ist... ja. Dürfen. Das Sternchen hat keinen Einfluss auf die Anfrage. Wie können Sie sich dessen sicher sein? Betrachten Sie die folgende Abfrage:

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

Alle diese Datenbanken melden einen Division-durch-Null-Fehler. Beachten Sie eine interessante Tatsache: Wenn wir in MySQL durch Null dividieren, erhalten wir NULL anstelle eines Fehlers, also müssen wir etwas anderes tun, was nicht erlaubt ist. Was passiert nun, wenn wir anstelle der oben genannten die folgenden Abfragen ausführen?

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

Jetzt gibt keine der Datenbanken einen Fehler zurück. Sie alle geben TRUE oder 1 zurück . Das bedeutet, dass keine unserer Datenbanken tatsächlich die Projektion (d. h. die SELECT- Klausel ) der EXISTS- Unterabfrage auswertet . SQL Server zeigt beispielsweise den folgenden Plan:

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

Wie Sie sehen, wurde der CASE- Ausdruck in eine Konstante umgewandelt und die Unterabfrage wurde entfernt. Andere Datenbanken speichern die Unterabfrage im Plan und erwähnen nichts über die Projektion. Schauen wir uns also noch einmal den ursprünglichen Abfrageplan in Oracle an:

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

Der obige Abfrageplan sieht folgendermaßen aus:

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

Wir beobachten Informationen über die Projektion bei Id=3 . Tatsächlich greifen wir nicht einmal auf die Tabelle FILM_ACTOR zu , da dies nicht erforderlich ist. Das EXISTS- Prädikat kann mithilfe eines Fremdschlüsselindex für eine einzelne ACTOR_ID- Spalte ausgeführt werden – alles, was für diese Abfrage erforderlich ist – obwohl wir SELECT * geschrieben haben .

Zusammenfassung

Glücklicherweise entfernen alle unsere Datenbanken die Projektion aus EXISTS- Unterabfragen : Seien Sie gespannt auf Teil 3 , in dem wir weitere coole SQL-Optimierungen besprechen.