Optimieren von SQL-Abfragen

Die Datenverwaltung ist das Herzstück moderner Anwendungen, und SQL-Datenbanken bieten leistungsstarke Tools zur Optimierung von Datenabfragen und -bearbeitungen. Materialized Ansichten zeichnen sich durch ihre Fähigkeit aus, Abfragen in Szenarien, in denen sich die zugrunde liegenden Daten nur selten ändern, erheblich zu beschleunigen. Sie stellen damit ein Grundpfeiler beim Optimieren von SQL-Abfragen dar.

Beginnen wir mit den Tabellen

• Tabellen sind die Grundlage relationaler Datenbanken und speichern Daten in Zeilen und Spalten.
• Tabellen werden verwendet, um Daten beizubehalten. Sie können Vorgänge wie INSERT, UPDATE, DELETE und SELECT direkt für die Daten in einer Tabelle ausführen.
• Tabellen enthalten echte Daten und können aus Performance-Gründen indiziert werden. Sie können auch vollständige CRUD-Operationen (Create, Read, Update, Delete) unterstützen.
• Bei großen Tabellen mit vielen Zeilen können Abfragen ohne ordnungsgemäße Indizierung oder Optimierung langsam werden.

View

• Eine virtuelle Tabelle, die dynamisch Daten aus einer oder mehreren Tabellen basierend auf einer vordefinierten SQL-Abfrage abruft.
• Ansichten werden häufig verwendet, um komplexe Abfragen zu vereinfachen oder eine andere Datendarstellung bereitzustellen, ohne sie separat zu speichern.
• Ansichten machen komplexe Abfragen wiederverwendbar und besser lesbar. Sie belegen keinen Speicherplatz, da sie dynamisch generiert werden.
• Da die zugrunde liegende Abfrage jedes Mal ausgeführt wird, wenn auf die Ansicht zugegriffen wird, können Ansichten bei großen Datasets oder komplexen Abfragen langsam sein.

Materialized Ansicht

• In einer materialisierten Ansicht wird das Ergebnis einer Abfrage physisch gespeichert, was einen schnelleren Datenzugriff ermöglicht. Im Gegensatz zu einer normalen Ansicht enthält eine materialisierte Ansicht eine Kopie der Daten.
• Materialisierte Ansichten werden verwendet, um die Leistung für teure Abfragen zu optimieren, insbesondere wenn sich die Daten nicht häufig ändern.
• Das Abfragen von Daten aus einer materialisierten Ansicht ist viel schneller, da das Resultset vorab berechnet und gespeichert wird. Dies ist besonders effektiv für komplexe Abfragen, die Aggregationen oder Verknüpfungen über große Datasets umfassen.
• Da die Daten getrennt von den Basistabellen gespeichert werden, muss eine materialisierte Ansicht manuell oder geplant aktualisiert werden, um die Daten synchron zu halten. Darüber hinaus verbrauchen materialisierte Ansichten Speicherplatz.

Stellen Sie sich eine Tabelle employee_information mit 3,5 Millionen Datensätzen vor. Das Ausführen einer Abfrage, die Mitarbeiter nach Gehalt innerhalb der Abteilungen einstuft, kann 6-8 Sekunden dauern, was ein Engpass für die Leistung in einer Echtzeitanwendung sein kann.

Ein Punkt, den wir bemerken: Die Daten in dieser Tabelle ändern sich nicht häufig, was sie zu einem guten Kandidaten für eine materialisierte Ansicht macht.

Wir benötigen Details zu den 10 besten Mitarbeitern mit den höchsten Gehältern in jeder Abteilung und haben dafür die folgende Abfrage geschrieben.

WITH RankedEmployees AS (
    SELECT 
        departments, 
        salary,
        ROW_NUMBER() OVER (PARTITION BY departments ORDER BY salary DESC) AS row_number
    FROM 
        employee_information
)
SELECT 
    departments, 
    salary,
    row_number
FROM 
    RankedEmployees
WHERE 
    row_number <= 10;

Als wir diese Abfrage für 3,5 Millionen Datensätze ausführten, dauerte es etwa 6 bis 8 Sekunden, und die Anwendung musste in 1 Sekunde das Maximum erreichen.

Wie gezeigt, gibt die letzte Zeile beim Ausführen von EXPLAIN ANALYZE an, dass die Ausführung der Abfrage mehr als 6 Sekunden dauert.

WindowAgg  (cost=739609.64..809748.54 rows=3506945 width=23) (actual time=5170.487..6237.381 rows=50 loops=1)
  Run Condition: (row_number() OVER (?) <= 10)
  ->  Sort  (cost=739609.64..748377.00 rows=3506945 width=15) (actual time=5170.451..5984.252 rows=3506370 loops=1)
        Sort Key: employee_information.departments, employee_information.salary DESC
        Sort Method: external merge  Disk: 91920kB
        ->  Seq Scan on employee_information  (cost=0.00..238505.45 rows=3506945 width=15) (actual time=82.918..971.938 rows=3506370 loops=1)
Planning Time: 0.356 ms
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 2.742 ms, Inlining 11.976 ms, Optimization 44.458 ms, Emission 26.147 ms, Total 85.323 ms
Execution Time: 6244.394 ms

Wie wir bereits wissen, ändern sich Daten nicht häufig, daher ist die Anwendung einer materialisierten Ansicht der beste Fall.

Um die erforderlichen Daten schnell abzurufen, haben wir eine materialisierte Ansicht erstellt, wie Sie in den folgenden Code-Snippets sehen können.

CREATE MATERIALIZED VIEW employee_ranks AS
WITH RankedEmployees AS (
    SELECT 
        departments, 
        salary,
        ROW_NUMBER() OVER (PARTITION BY departments ORDER BY salary DESC) AS row_number
    FROM 
        employee_information
)
SELECT 
    departments, 
    salary,
    row_number
FROM 
    RankedEmployees
WHERE 
    row_number <= 10;

Refreshing

Die Daten in einer materialisierten Ansicht können veraltet sein, wenn sich die Basistabelle ändert. Sie können sie manuell aktualisieren oder eine geplante Aktualisierung einrichten, um die Richtigkeit der Daten zu gewährleisten:

REFRESH MATERIALIZED VIEW employee_ranks;

Dadurch wird die materialisierte Ansicht auf dem neuesten Stand gehalten und alle Änderungen in der Tabelle employee_information berücksichtigt.

Abrufen von Daten

Das Abfragen der materialisierten Ansicht ist so einfach wie das Abfragen einer Tabelle:

SELECT * FROM employee_ranks;

Durch die Abfrage dieses vorab berechneten Resultsets reduzieren Sie die Ausführungszeit der Abfrage von mehreren Sekunden auf den Bruchteil einer Sekunde, wie Sie in den folgenden Codeausschnitten sehen können.

Seq Scan on employee_ranks  (cost=0.00..1.50 rows=50 width=23) (actual time=0.836..0.839 rows=50 loops=1)
Planning Time: 2.659 ms
Execution Time: 0.871 ms

Wann sollten materialisierte Ansichten verwendet werden?

Materialisierte Ansichten sind perfekt zum Optimieren von SQL-Abfragen, wenn:

• Sie verfügen über große Datasets, die sich nicht häufig ändern.
• Die Leistung ist von entscheidender Bedeutung, und Sie benötigen einen schnellen Zugriff auf Daten.
• Es ist teuer, die zugrunde liegende Abfrage wiederholt auszuführen.

Sie sollten vermieden werden, wenn:

• Die Daten ändern sich häufig, da die ständige Aktualisierung die Leistungsvorteile zunichte machen würde.
• Speicherplatz ist ein Problem, da materialisierte Ansichten viel Speicherplatz beanspruchen können.

Fazit

Materialized Views bieten eine leistungsstarke Möglichkeit, die Leistung in SQL-Datenbanken zu optimieren, insbesondere beim Umgang mit großen Datasets und teuren Abfragen.

Weiterer interessanter Beitrag: SW-Architektur ist schwierig