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

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 Coole SQL-Optimierungen, die nicht vom Kostenmodell abhängen. Teil 4

10. Prädikate schieben

Diese Optimierung ist hier nicht ganz angemessen, da nicht gesagt werden kann, dass sie überhaupt nicht auf dem Kostenmodell basiert. Da mir jedoch kein einziger Grund einfällt, warum der Optimierer Prädikate nicht in abgeleitete Tabellen verschieben sollte, werde ich ihn hier zusammen mit den übrigen nicht kostenintensiven Optimierungen auflisten. Betrachten Sie die Anfrage:

SELECT *
FROM (
  SELECT *
  FROM actor
) a
WHERE a.actor_id = 1;

Die abgeleitete Tabelle in dieser Abfrage ergibt keinen Sinn und sollte durch Reduzierung der Anzahl der Abfrageverschachtelungsebenen eliminiert werden. Aber ignorieren wir das zunächst einmal. Sie können davon ausgehen, dass die Datenbank anstelle der oben genannten die folgende Abfrage ausführt:

SELECT *
FROM (
  SELECT *
  FROM actor
  WHERE actor_id = 1
) a;

Und dann vielleicht noch einmal die externe Anforderung eliminieren. Ein komplexeres Beispiel erhält man mit UNION :

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
) people
WHERE people.last_name = 'DAVIS';

Das Ergebnis dieser Abfrage:

FIRST_NAME  LAST_NAME  TYPE
----------------------------
JENNIFER    DAVIS      actor
SUSAN       DAVIS      actor
SUSAN       DAVIS      actor
JENNIFER    DAVIS      customer

Nun wäre es großartig, wenn der Datenbankoptimierer stattdessen eine Abfrage wie diese ausführen würde:

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  WHERE last_name = 'DAVIS'
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
  WHERE last_name = 'DAVIS'
) people;

Das heißt, dass das Prädikat in die abgeleitete Tabelle und von dort in zwei UNION ALL- Unterabfragen verschoben wird , da wir schließlich einen Index sowohl für die Spalte ACTOR.LAST_NAME als auch für die Spalte CUSTOMER.LAST_NAME haben . Auch diese Konvertierung basiert wahrscheinlich auf Kostenschätzungen in den meisten Datenbanken, aber ich halte sie dennoch für eine Selbstverständlichkeit, da es bei jedem Algorithmus fast immer besser ist, die Anzahl der verarbeiteten Tupel so früh wie möglich zu reduzieren. Wenn Sie einen Fall kennen, in dem sich eine solche Transformation als schlechte Idee herausstellt, freue ich mich über Ihre Kommentare! Ich wäre sehr interessiert. Welche unserer Datenbanken kann das also? (Und bitte, es ist so einfach und so wichtig, lasst die Antwort lauten: alles)

DB2

Einfache abgeleitete Tabelle Ja

Explain Plan
--------------------------------------------------
ID | Operation         |               Rows | Cost
 1 | RETURN            |                    |    6
 2 |  FETCH ACTOR      |   1 of 1 (100.00%) |    6
 3 |   IXSCAN PK_ACTOR | 1 of 200 (   .50%) |    0
Predicate Information
 3 - START (Q1.ACTOR_ID = 1)
      STOP (Q1.ACTOR_ID = 1)

Abgeleitete Tabelle mit UNION Auch ja:

Explain Plan
-----------------------------------------------------------------
ID | Operation                        |               Rows | Cost
 1 | RETURN                           |                    |   20
 2 |  UNION                           |             2 of 1 |   20
 3 |   FETCH CUSTOMER                 |   1 of 1 (100.00%) |   13
 4 |    IXSCAN IDX_CUSTOMER_LAST_NAME | 1 of 599 (   .17%) |    6
 5 |   FETCH ACTOR                    |   1 of 1 (100.00%) |    6
 6 |    IXSCAN IDX_ACTOR_LAST_NAME    | 1 of 200 (   .50%) |    0
Predicate Information
 4 - START (Q1.LAST_NAME = 'DAVIS')
      STOP (Q1.LAST_NAME = 'DAVIS')
 6 - START (Q3.LAST_NAME = 'DAVIS')
      STOP (Q3.LAST_NAME = 'DAVIS')

Außerdem wurde in beiden Fällen die abgeleitete Tabelle (Ansicht) aus dem Plan ausgeschlossen, da sie eigentlich nicht benötigt wurde.

MySQL

Einfache abgeleitete Tabelle Ja

ID  TABLE  TYPE   KEY      REF    EXTRA
---------------------------------------
1   actor  const  PRIMARY  const

Es wird der übliche Primärschlüsselzugriff über einen konstanten Wert verwendet. Abgeleitete Tabelle mit UNION Ups, nein.

ID  SELECT_TYPE  TABLE       TYPE  KEY          REF    ROWS  EXTRA
------------------------------------------------------------------
1   PRIMARY        ref   	const  10
2   DERIVED      actor       ALL                       200
3   UNION        customer    ALL                       599

Die manuelle Konvertierung ergibt im Plan:

ID  SELECT_TYPE  TABLE       TYPE  KEY                  REF    ROWS  EXTRA
--------------------------------------------------------------------------
1   PRIMARY        ALL                               5
2   DERIVED      actor       ref   idx_actor_last_name  const  3
3   UNION        customer    ref   idx_last_name        const  1

Dies ist ein ernstes Problem bei der Verwendung komplexer verschachtelter Abfragen in MySQL!

Orakel

Einfache abgeleitete Tabelle Ja, es funktioniert.

---------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR    |      1 |      1 |      1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ACTOR |      1 |      1 |      1 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ACTOR"."ACTOR_ID"=1)

Und die Anzahl der Verschachtelungsebenen wurde reduziert. Abgeleitete Tabelle mit UNION funktioniert auch:

---------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | E-Rows |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |        |
|   1 |  VIEW                                 |                        |      4 |
|   2 |   UNION-ALL                           |                        |        |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR                  |      3 |
|*  4 |     INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME    |      3 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER               |      1 |
|*  6 |     INDEX RANGE SCAN                  | IDX_CUSTOMER_LAST_NAME |      1 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("LAST_NAME"='DAVIS')
   6 - access("LAST_NAME"='DAVIS')

Allerdings ohne die Anzahl der Schachtelungsebenen zu reduzieren. Id=1 „View“ zeigt an, dass die abgeleitete Tabelle noch vorhanden ist. In diesem Fall handelt es sich nicht um ein großes Problem, sondern vielleicht nur um einen kleinen Mehraufwand.

PostgreSQL

Einfache abgeleitete Tabelle Ja, es funktioniert:

QUERY PLAN
----------------------------------------------------
Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)
  Filter: (actor_id = 1)

Beachten Sie jedoch, dass PostgreSQL manchmal nicht einmal den Primärschlüssel verwendet, um eine einzelne Zeile nachzuschlagen, sondern stattdessen die gesamte Tabelle durchsucht. In diesem Fall passen 200 Zeilen × 25 Bytes pro Zeile („Breite“) in einen Block. Welchen Sinn hat es also, sich mit Index-Lesevorgängen zu beschäftigen, außer unnötige E/A-Operationen zu generieren, um auf eine so kleine Tabelle zuzugreifen? Abgeleitete Tabelle mit UNION Ja, es funktioniert auch:

QUERY PLAN
-----------------------------------------------------------------------------------
Append  (cost=0.00..12.83 rows=4 width=45)
  ->  Seq Scan on actor  (cost=0.00..4.50 rows=3 width=45)
        Filter: ((last_name)::text = 'DAVIS'::text)
  ->  Index Scan using idx_last_name on customer  (cost=0.28..8.29 rows=1 width=45)
        Index Cond: ((last_name)::text = 'DAVIS'::text)

Auch hier wird kein Index für die Spalte ACTOR.LAST_NAME verwendet, sondern ein Index für die Spalte CUSTOMER.LAST_NAME , da die Tabelle CUSTOMER viel größer ist.

SQL Server

Einfache abgeleitete Tabelle Ja, es funktioniert

|--Nested Loops(Inner Join)
     |--Index Seek(SEEK:([actor_id]=(1)))
     |--RID Lookup(OBJECT:([actor]))

Eine abgeleitete Tabelle mit UNION funktioniert auch.

|--Concatenation
     |--Compute Scalar(DEFINE:([Expr1003]='actor'))
     |    |--Nested Loops(Inner Join)
     |         |--Index Seek(SEEK:([actor].[last_name]='DAVIS'))
     |         |--RID Lookup(OBJECT:([actor]))
     |--Compute Scalar(DEFINE:([Expr1007]='customer'))
          |--Nested Loops(Inner Join)
               |--Index Seek(SEEK:([customer].[last_name]='DAVIS'))
               |--RID Lookup(OBJECT:([customer]))

Zusammenfassung

Meine Hoffnungen haben sich nicht erfüllt. MySQL 8.0.2 unterstützt diese einfache Optimierung noch nicht vollständig. Alle anderen unterstützen ihn jedoch.

Abschluss

Die hier präsentierte Liste ist bei weitem nicht vollständig. Es gibt viele andere einfache SQL-Transformationen, die für Datenbanken nicht schwierig zu implementieren sind (oder sein sollten), selbst bevor ein Kostenoptimierer eingeschaltet wird. Sie eliminieren unnötige, zusätzliche Arbeit [für die Datenbank] ( im Gegensatz zu unnötiger, erforderlicher Arbeit, über die ich bereits geschrieben habe ). Dies sind wichtige Werkzeuge, um:

1. Dumme [Entwickler-]Fehler hatten keinen Einfluss auf die Leistung. Fehler sind unvermeidlich, und wenn ein Projekt wächst und SQL-Abfragen komplexer werden, können sich diese Fehler anhäufen, hoffentlich ohne Auswirkungen.


2. Bieten Sie die Möglichkeit, komplexe Blöcke wie Ansichten und Tabellenfunktionen wiederzuverwenden, die in übergeordnete SQL-Abfragen eingebettet, transformiert oder teilweise gelöscht oder neu geschrieben werden können.

Diese Fähigkeiten sind für Punkt 2 von entscheidender Bedeutung. Ohne sie wäre es sehr schwierig, SQL-Abfragen mit 4000 Zeilen und normaler Leistung basierend auf einer Bibliothek wiederverwendbarer SQL-Komponenten zu erstellen. Sehr zur Enttäuschung der PostgreSQL- und MySQL-Benutzer haben diese beiden beliebten Open-Source-Datenbanken im Vergleich zu ihren kommerziellen Konkurrenten DB2, Oracle und SQL Server noch einen langen Weg vor sich, von denen DB2 am besten abgeschnitten hat, wobei Oracle und SQL Server ungefähr gleichauf liegen Hals. etwas zurück.