Kurs
SQL Materialized View: Verbesserung der Abfrageleistung
Materialisierte Ansichten sind eine leistungsstarke Funktion in SQL-Datenbanken, die dazu beiträgt, die Abfrageleistung zu optimieren, indem sie die Ergebnisse einer Abfrage physisch auf der Festplatte speichert und so eine schnellere Abfrageleistung durch weniger Neuberechnungen ermöglicht. Das macht sie besonders nützlich für die Bearbeitung komplexer, ressourcenintensiver Abfragen, die Joins, Aggregationen und große Datensätze beinhalten.
Für den Anfang empfehle ich den DataCamp-Kurs "Einführung in SQL" und den Lernpfad "SQL-Grundlagen", um die Grundlagen von SQL zu lernen und zu erfahren, wie man mit Abfragen Daten extrahiert. Das Spickzettel für SQL-Grundlagen ist ein hilfreicher Leitfaden für gängige SQL-Funktionen zum Filtern und Aggregieren von Daten.
Was sind SQL Materialized Views?
Materialisierte Ansichten sind eine besondere Art von Datenbankobjekten, die die Ergebnisse einer Abfrage physisch speichern, anstatt sie wie normale Ansichten im laufenden Betrieb zu berechnen. Während eine reguläre SQL-Ansicht eine gespeicherte SQL-Abfrage ist, die ihre Ergebnisse bei jedem Zugriff dynamisch generiert, werden die Daten bei einer materialisierten Ansicht vorberechnet und in einer tabellenähnlichen Struktur gespeichert.
Durch die Speicherung der Ergebnismenge auf der Festplatte können materialisierte Ansichten die Belastung einer Datenbank erheblich reduzieren, die Abfrageleistung verbessern und die Verarbeitung von rechenintensiven Operationen rationalisieren.
Erstellen einer materialisierten Ansicht in SQL
Um eine materialisierte Ansicht zu erstellen, musst du die CREATE MATERIALIZED VIEW
Syntax verwenden, die sich in den verschiedenen SQL-Datenbanken leicht unterscheidet. Die folgenden Methoden zeigen, wie man materialisierte Ansichten in SQL Server, PostgreSQL und Oracle erstellt.
Materialisierte Ansicht in PostgreSQL
In PostgreSQL kannst du eine materialisierte Ansicht mit der folgenden Syntax erstellen. In diesem Beispiel wird eine materialisierte Ansicht namens sales_summary
erstellt, die die Gesamtmenge und den Umsatz jedes Produkts zusammenfasst.
-- Create a materialized view to summarize sales data
CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;
Materialisierte Ansicht in SQL Server
In SQL Server werden materialisierte Ansichten als "indizierte Ansichten" bezeichnet. Die zugrundeliegenden Tabellen müssen bestimmte Anforderungen erfüllen, um eine indizierte Ansicht zu erstellen, wie z.B. die Aktivierung der Option WITH SCHEMABINDING
.
Die Option WITH SCHEMABINDING
stellt sicher, dass sich das Schema nicht ändern kann, solange die indizierte Ansicht existiert. Damit die Ansicht materialisiert werden kann, muss ein eindeutiger geclusterter Index erstellt werden.
-- Create an indexed view with schema binding to summarize sales data
CREATE VIEW sales_summary
WITH SCHEMABINDING
AS
SELECT product_id,
COUNT_BIG(*) AS record_count,
SUM(ISNULL(quantity, 0)) AS total_quantity,
SUM(ISNULL(price, 0) * ISNULL(quantity, 0)) AS total_revenue
FROM sales
GROUP BY product_id;
GO
-- Create a unique clustered index to materialize the view
CREATE UNIQUE CLUSTERED INDEX IX_sales_summary
ON sales_summary (product_id);
GO
Wenn du mehr über SQL Server erfahren möchtest, empfehle ich dir unseren Lernpfad SQL Server Fundamentals, um dich mit den verschiedenen SQL-Fähigkeiten für die Datenanalyse vertraut zu machen.
Materialisierte Ansicht in Oracle
Die Syntax für die Erstellung von materialisierten Ansichten in Oracle ist ähnlich wie die der PostgreSQL-Datenbank. Wir können auch Aktualisierungsoptionen angeben, wie ON DEMAND
oder ON COMMIT
.
-- Create a materialized view to summarize sales data
CREATE MATERIALIZED VIEW sales_summary
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT product_id, SUM(quantity) AS total_quantity, SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;
Materialisierte Ansicht in verteilten Datenbanken
Materialisierte Ansichten können auch Verteilungsmethoden für eine bessere Leistung für Datenbanken wie Azure Synapse oder Amazon Redshift angeben, die verteilte Data Warehouses unterstützen.
Hash-Verteilung für Amazon Redshift
Die folgende Abfrage erstellt eine materialisierte Ansicht namens sales_summary
, die die Gesamtmenge und den Umsatz nach product_id
aggregiert. Die Optionen DISTSTYLE KEY
und DISTKEY(product_id)
stellen sicher, dass die Daten auf der Grundlage von product_id
auf die Knoten verteilt werden, um die Leistung für Abfragen zu verbessern, die auf dieser Spalte verknüpft werden.
-- Create a materialized view with key-based distribution for efficient joins
CREATE MATERIALIZED VIEW sales_summary
DISTSTYLE KEY
DISTKEY(product_id)
AS
SELECT product_id,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;
Round-Robin-Verteilung für Azure Synapse
Diese Abfrage erstellt eine materialisierte Ansicht namens sales_summary
, die die Gesamtmenge und den Umsatz nach product_id
aggregiert. Die ROUND_ROBIN
Verteilung verteilt die Daten gleichmäßig über die Knotenpunkte, was für Szenarien nützlich ist, die nicht stark auf Joins angewiesen sind.
-- Create a materialized view with round-robin distribution for balanced data storage
CREATE MATERIALIZED VIEW sales_summary
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT product_id,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;
Auffrischen einer materialisierten Ansicht in SQL
Die Daten in materialisierten Ansichten können aktualisiert werden, um mit den zugrunde liegenden Tabellen auf dem neuesten Stand zu bleiben. Die Wahl der Aktualisierungsmethode hängt von den geschäftlichen Anforderungen und Leistungsüberlegungen der jeweiligen Datenbank ab. Schauen wir uns die folgenden Methoden zur Datenaktualisierung von materialisierten Ansichten an.
Manuelle Aktualisierung
Bei der manuellen Aktualisierung wird die materialisierte Ansicht nur auf ausdrücklichen Wunsch des Benutzers aktualisiert. Dieser Ansatz bietet die größte Kontrolle darüber, wann die Daten aktualisiert werden, und eignet sich daher für Szenarien, in denen sich die Daten nur selten ändern oder die Aktualisierungen außerhalb der Hauptverkehrszeiten durchgeführt werden.
Die folgende Abfrage zeigt die manuelle Aktualisierungsmethode in PostgreSQL.
REFRESH MATERIALIZED VIEW sales_summary;
Regelmäßige Aktualisierung
Die materialisierte Ansicht wird während des Aktualisierungszeitraums automatisch in bestimmten Intervallen aktualisiert, sodass die Daten ohne Benutzereingriff aktuell sind. Diese Methode ist nützlich für zeitkritische Anwendungen, bei denen die Daten relativ aktuell sein müssen.
Das folgende Beispiel zeigt, wie du regelmäßige Aktualisierungen in Oracle einbinden kannst, indem du Aktualisierungszeitpläne direkt in der Anweisung CREATE MATERIALIZED VIEW
definierst. Die Aktualisierungszeit wird in stündlichen Abständen eingestellt.
-- Create a materialized view to aggregate sales data
-- Set to refresh completely every hour
CREATE MATERIALIZED VIEW sales_summary
REFRESH COMPLETE START WITH (SYSDATE) NEXT (SYSDATE + 1/24)
AS
SELECT product_id,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;
Auffrischung auf Abruf
Die On-Demand-Aktualisierung erfolgt immer dann, wenn sich die zugrunde liegenden Daten ändern, in der Regel durch einen Trigger-Mechanismus. Dadurch wird sichergestellt, dass die materialisierte Ansicht immer die aktualisierten Daten enthält. In PostgreSQL können zum Beispiel Trigger eingerichtet werden, die die Ansicht aktualisieren, wenn sich die zugrunde liegende Tabelle ändert.
Vollständige vs. inkrementelle Aktualisierungen
Du kannst die Daten in den materialisierten Ansichten vollständig oder inkrementell aktualisieren. Die folgende Tabelle fasst die beiden Methoden und die Anwendungsfälle zusammen.
Typ auffrischen | Beschreibung | Vorteile | Beeinträchtigungen |
---|---|---|---|
Vollständige Auffrischung | Lädt den gesamten Datensatz neu und ersetzt dabei alle vorhandenen Daten in der Ansicht | - Einfach zu implementieren - Baut die gesamte Ansicht neu auf |
- Ressourcenintensiv für große Datenmengen - Längere Aktualisierungszeiten |
Inkrementelle Auffrischung | Aktualisiert nur die geänderten Teile der Ansicht | - Effizienter, da nur geänderte Daten verarbeitet werden - Geeignet für große Datensätze mit häufigen Änderungen |
- Erfordert zusätzliche Einstellungen (z. B. Protokolle, um Änderungen zu verfolgen) - Wird nicht immer für alle Abfragen unterstützt |
Best Practices für Materialized Views
Bei der Verwendung von materialisierten Ansichten ist es wichtig, die folgenden Praktiken für eine optimale Nutzung zu beachten.
- Wähle die richtigen Abfragen für die Materialisierung: Materialisiere komplexe, ressourcenintensive Abfragen wie Joins, Aggregationen und Unterabfragen. Solche Abfragen würden von vorberechneten Ergebnissen profitieren, die die Datenbank entlasten.
- Ausgleich zwischen Datenfrische und Leistung: Je nach Anwendungsfall wählst du die passende Aktualisierungsstrategie, wie z.B. manuelle, periodische oder bedarfsgesteuerte Aktualisierungsmethoden. Nutze die inkrementelle Aktualisierung, um die Rechenlast zu verringern, und überwache die Häufigkeit von Datenänderungen, um die Aktualisierungsintervalle festzulegen.
- Materialisierte Ansichten zur Optimierung von abfrageintensiven Workloads verwenden: Nutze materialisierte Ansichten für BI-Berichte und Dashboards, bei denen es auf schnelle Reaktionszeiten ankommt. Du kannst auch die in materialisierten Ansichten verwendeten Spalten indizieren, um schneller filtern und sortieren zu können.
Materialisierte Ansichten in verschiedenen Datenbanksystemen
Wie du gesehen hast, bieten verschiedene Datenbanksysteme unterschiedliche Unterstützung für materialisierte Ansichten. Die folgende Tabelle fasst die einzigartigen Merkmale und Einschränkungen der materialisierten Ansichten in diesen Datenbanken zusammen.
Datenbank-System | Methoden auffrischen | Inkrementelle Auffrischung | Automatische Auffrischung | Besondere Merkmale/Einschränkungen |
---|---|---|---|---|
PostgreSQL | Handbuch (REFRESH MATERIALISIERTE ANSICHT) | Nein | Nein | Es fehlt eine native inkrementelle Aktualisierung. Eine manuelle Planung ist erforderlich. |
SQL Server | Automatisch (indizierte Ansichten) | Ja (automatische Synchronisierung) | Ja |
Erfordert |
Oracle | Manuell, Bei Commit, Geplant | Ja (Schnelles Auffrischen) | Ja | Unterstützt schnelle Aktualisierung, Partitionierung und Parallelität. Erfordert die Anzeige von Protokollen. |
Amazon Redshift | Manuell, planmäßig | Ja | Ja | Unterstützt verteilte Daten mit Hash- oder Round-Robin-Verteilung. |
MySQL | Von Haus aus nicht unterstützt | Nein | Nein | Erforderliche Workarounds (z. B. temporäre Tabellen, Tools von Drittanbietern). |
Azure Synapse | Manuell, planmäßig | Ja | Ja | Ermöglicht verschiedene Verteilungsstrategien zur Optimierung. |
Wenn du SQL Server als bevorzugte Datenbank verwendest, empfehle ich dir den Kurs Einführung in SQL Server von DataCamp, um die Grundlagen von Microsoft SQL Server für die Datenanalyse zu erlernen. Schau dir auch unseren Lernpfad für SQL Server-Entwickler/innen an, um zu verstehen, wie man Abfragen optimiert und Probleme in SQL Server behebt.
Zusätzliche Dinge zu beachten
SQL Materialized Views sind zwar nützlich für die Optimierung von Abfragen, aber sie bringen auch einige Herausforderungen und Einschränkungen mit sich. Im Folgenden findest du die häufigsten Probleme mit materialisierten Ansichten und wie du sie lösen kannst.
- Lagerung über Kopf: Materialisierte Ansichten speichern Abfrageergebnisse physisch auf der Festplatte, was den Speicherbedarf erhöht. Um zu vermeiden, dass unnötiger Speicherplatz verbraucht wird, materialisiere Ansichten nur für ressourcenintensive Abfragen und partitioniere die materialisierten Ansichten für große Datensätze.
- Kosten aktualisieren und Gemeinkosten auffrischen: Die Synchronisierung von materialisierten Ansichten mit den zugrunde liegenden Tabellen kann ressourcenintensiv sein, insbesondere bei Ansichten, die häufig aktualisiert werden müssen oder komplexe Berechnungen beinhalten. Um den Auffrischungsaufwand zu vermeiden, verwende die inkrementelle Auffrischung, wo sie unterstützt wird, oder stelle die entsprechenden Auffrischungsintervalle ein, wenn die Datenbankauslastung geringer ist.
- Datenkonsistenz und Synchronisierung: Materialisierte Ansichten können veraltet sein, wenn sich die zugrunde liegenden Daten häufig ändern, was zu Problemen mit veralteten Daten führt. Um dieses Problem zu vermeiden, wähle eine geeignete Aktualisierungsstrategie und überwache die Datenänderungen, um die Aktualisierungsstrategie bei Bedarf anzupassen.
- Wartung Overhead: Materialisierte Ansichten erfordern eine kontinuierliche Wartung, z. B. die Festlegung geeigneter Aktualisierungszeitpläne, die Überwachung der Speichernutzung und die Verfolgung von Abhängigkeiten von den zugrunde liegenden Tabellen. Um diese Herausforderung zu meistern, solltest du immer automatische Aktualisierungszeitpläne verwenden, die Systemleistung überwachen und Warnungen für fehlgeschlagene Aktualisierungen einrichten.
Fazit
Materialisierte Ansichten sind in SQL-Datenbanken nützlich, um die Abfrageleistung zu optimieren. Sie speichern die Ergebnisse der Abfrage physisch auf der Festplatte und bieten so eine schnellere Abfrageleistung, da weniger Neuberechnungen erforderlich sind. Diese Funktion macht die materialisierten Ansichten nützlich für die Bearbeitung komplexer, ressourcenintensiver Abfragen, die Joins, Aggregationen und große Datensätze beinhalten. Wenn du verstehst, wie man materialisierte Ansichten in verschiedenen Datenbanken implementiert, kannst du deine Fähigkeiten zur Abfrage- und Datenbankoptimierung verbessern.
Wenn du deine SQL-Kenntnisse erweitern möchtest, empfehle ich dir den Lernpfad Associate Data Analyst in SQL von DataCamp, um ein kompetenter Datenanalyst zu werden. Der Kurs Reporting in SQL hilft dir außerdem, komplexe Berichte und Dashboards für eine effektive Datenpräsentation zu erstellen. Schließlich solltest du die SQL Associate-Zertifizierung erwerben, um zu zeigen, dass du SQL zur Lösung von Geschäftsproblemen beherrschst und dich von anderen Fachleuten abhebst.
Werde SQL-zertifiziert
FAQs
Was ist eine materialisierte Ansicht in SQL?
Eine materialisierte Ansicht ist ein Datenbankobjekt, das das Ergebnis einer Abfrage physisch speichert und die Leistung optimiert, indem es Neuberechnungen vermeidet.
Wie unterscheidet sich eine materialisierte Ansicht von einer normalen Ansicht?
Im Gegensatz zu regulären Sichten, die Daten bei jedem Zugriff dynamisch abrufen, speichern materialisierte Sichten Daten als physische Tabellen und ermöglichen so eine schnellere Ausführung von Abfragen.
Was ist der Unterschied zwischen einer vollständigen und einer inkrementellen Aktualisierung?
Bei einer vollständigen Aktualisierung wird die gesamte Ansicht neu berechnet, während bei einer inkrementellen Aktualisierung nur die geänderten Daten aktualisiert werden, was die Effizienz erhöht.
Welche Datenbanken unterstützen materialisierte Ansichten?
PostgreSQL, SQL Server (indexierte Ansichten), Oracle, Amazon Redshift und Azure Synapse Analytics unterstützen materialisierte Ansichten, jeweils mit unterschiedlichen Funktionen und Einschränkungen, während MySQL keine native Unterstützung bietet.
Verbrauchen materialisierte Ansichten zusätzlichen Speicherplatz?
Ja, da sie Daten physisch speichern, erhöhen materialisierte Ansichten den Speicherbedarf.
SQL lernen mit DataCamp
Kurs
Einführung in relationale Datenbanken in SQL
Kurs