Dieselbe zwanzigzeilige Berichtsabfrage wurde in fünf Dashboards kopiert, und jede Kopie hat sich ein wenig verschoben. Einer zählt stornierte Bestellungen, ein anderer nicht. SQL-Ansichten lösen dieses Problem, indem sie diese Logik einmal in der Datenbank unter einem klaren Namen definieren.

Wir haben jedes Beispiel unten in SQLite 3.50 ausgeführt und die angezeigten Ergebnisse stammen aus diesem Lauf. Ansichten sind Standard-SQL, aber Details wie die Aktualisierung durch eine Ansicht, materialisierte Ansichten und die Handhabung von Abhängigkeiten variieren zwischen den Datenbanken, und wir weisen darauf hin.

Was ist eine Ansicht?

Eine Ansicht ist eine SELECT-Anweisung, die unter einem Namen in der Datenbank gespeichert wird. Wenn Sie die Ansicht abfragen, führt die Datenbank diese gespeicherte Abfrage aus. Es verhält sich wie eine Tabelle zum Lesen, aber eine normale Ansicht enthält keine eigene Kopie der Daten.

Stellen Sie sich einen gespeicherten Filter in einer Tabelle vor. Der Filter ist keine zweite Kopie des Blattes. Es handelt sich um eine Betrachtungsweise der gleichen Daten, die immer die aktuellsten Werte widerspiegeln.

Die Beispieltabellen

CREATE TABLE customers (
  id INT PRIMARY KEY, name TEXT, city TEXT, email TEXT, credit_limit INT
);
CREATE TABLE orders (
  id INT PRIMARY KEY, customer_id INT, total INT, status TEXT
);

INSERT INTO customers VALUES
 (1,'Asha','Pune','asha@example.com',50000),
 (2,'Ben','Delhi','ben@example.com',20000),
 (3,'Chitra','Pune','chitra@example.com',80000);

INSERT INTO orders VALUES
 (101,1,1200,'paid'), (102,1,800,'paid'),
 (103,2,300,'pending'), (104,3,2500,'paid');

Erstellen Sie eine Ansicht

In dieser Ansicht werden die Bestellungen jedes Kunden zusammengefasst. Es verbirgt einen Join, eine Gruppierung und einen CASE-Ausdruck hinter einem einfachen Namen:

CREATE VIEW customer_sales AS
SELECT c.id, c.name, c.city,
       COUNT(o.id) AS orders,
       COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.total END), 0) AS paid_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.city;

Jetzt kann es jeder nutzen, ohne zu wissen, wie es funktioniert:

SELECT * FROM customer_sales ORDER BY paid_total DESC;

-- 3 | Chitra | Pune  | 1 | 2500
-- 1 | Asha   | Pune  | 2 | 2000
-- 2 | Ben    | Delhi | 1 |    0

SELECT name, paid_total FROM customer_sales WHERE city = 'Pune';

-- Asha   | 2000
-- Chitra | 2500

Ben erscheint mit Null, da die Ansicht LEFT JOIN und COALESCE verwendet. Entscheidungen wie diese, die einmal in der Ansicht getroffen werden, sind genau das, was die Konsistenz der Berichte gewährleistet. Um Ihr Verständnis der NULL-Behandlung aufzufrischen, lesen Sie SQL-NULL-Werte.

Ansichten sind immer aktuell

Da eine normale Ansicht ihre Abfrage jedes Mal ausführt, werden neue Daten sofort angezeigt. Wir haben eine kostenpflichtige Bestellung für Ben hinzugefügt und die Ansicht erneut abgefragt:

INSERT INTO orders VALUES (105, 2, 900, 'paid');

SELECT name, paid_total FROM customer_sales WHERE name = 'Ben';

-- Ben | 900

Es gab keinen Aktualisierungsschritt und keine Kopie, die synchron gehalten werden musste. Dies ist der wesentliche Unterschied zum Speichern von Abfrageergebnissen in einer separaten Tabelle.

Spalten ausblenden

Ansichten werden häufig verwendet, um nur die Spalten anzuzeigen, die eine Person oder Anwendung benötigt. Hier werden E-Mail-Adressen und Kreditlimits weggelassen:

CREATE VIEW public_customers AS
SELECT id, name, city FROM customers;

-- columns: id, name, city

In Datenbanken mit Berechtigungen wie PostgreSQL, SQL Server und MySQL können Sie einem berichtenden Benutzer Zugriff auf die Ansicht gewähren, nicht jedoch auf die zugrunde liegende Tabelle. Da SQLite keine Benutzerkonten hat, konnten wir nur die Spalteneinschränkung selbst anzeigen. Behandeln Sie eine Ansicht als eine Schutzebene. Sensible Daten benötigen weiterhin eine ordnungsgemäße Zugriffskontrolle, und einige Datenbanken bieten Sicherheit auf Zeilenebene zum Filtern von Zeilen nach Benutzer.

Leistung: Aufrufe sind kein Geschwindigkeitsschub

Ein verbreiteter Mythos besagt, dass Ansichten Abfragen schneller machen. Eine normale Ansicht wird bei der Abfrage durch ihre Definition ersetzt, sodass die Datenbank die gleiche Arbeit ausführt, als ob Sie die vollständige Abfrage selbst geschrieben hätten. Unser Ausführungsplan zeigte, dass die Verknüpfung und Gruppierung der Ansicht als Teil der Abfrage ausgeführt wurde:

EXPLAIN QUERY PLAN SELECT * FROM customer_sales WHERE city = 'Pune';

-- CO-ROUTINE customer_sales
-- SCAN c ...
-- SEARCH o USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN
-- USE TEMP B-TREE FOR GROUP BY
-- SCAN customer_sales

Ein Blick auf eine langsame Abfrage ist also immer noch eine langsame Abfrage. Geschwindigkeit kommt von Indizes und einem guten Abfragedesign, wie in SQL-Abfrageoptimierung und Erklärung von SQL-Indizes behandelt. Achten Sie auch auf Ansichten, die auf anderen Ansichten basieren. Durch die Verschachtelungsebenen ist es einfach, den gleichen großen Tisch mehrmals zusammenzustellen, ohne es zu merken.

Materialisierte Ansichten

Eine materialisierte Ansicht speichert ihre Ergebnisse wie eine zwischengespeicherte Tabelle. Die Lesevorgänge erfolgen schnell, die Daten sind jedoch nur so aktuell wie bei der letzten Aktualisierung.

-- PostgreSQL
CREATE MATERIALIZED VIEW daily_sales AS
SELECT order_date, SUM(total) AS revenue FROM orders GROUP BY order_date;

REFRESH MATERIALIZED VIEW daily_sales;

Die Unterstützung variiert. PostgreSQL und Oracle verfügen über materialisierte Ansichten und SQL Server bietet indizierte Ansichten mit eigenen Regeln. Da sie in SQLite und MySQL fehlen, verwenden die Leute eine regelmäßige Übersichtstabelle, die nach einem Zeitplan aktualisiert wird. Wir haben das PostgreSQL-Beispiel für diesen Artikel nicht ausgeführt.

Können Sie eine Ansicht aktualisieren?

Es hängt von der Datenbank und der Ansicht ab. Eine einfache Ansicht einer einzelnen Tabelle ohne Gruppierung oder DISTINCT kann häufig in PostgreSQL, MySQL und SQL Server aktualisiert werden, und die Änderung wird auf die Tabelle übertragen. Eine Ansicht mit Joins oder Aggregaten kann dies im Allgemeinen nicht, da die Datenbank nicht erkennen kann, welche Zeile geändert werden soll.

SQLite erlaubt überhaupt keine direkten Schreibvorgänge in Ansichten. Unser Versuch ist gescheitert:

INSERT INTO customer_sales (name) VALUES ('X');

-- cannot modify customer_sales because it is a view

Einige Datenbanken unterstützen INSTEAD OF-Trigger, um eine Ansicht beschreibbar zu machen, und viele bieten WITH CHECK OPTION, das Schreibvorgänge stoppt, die in der Ansicht nicht sichtbar wären. In der Praxis halten die meisten Teams die Ansichten schreibgeschützt und schreiben in die Tabellen.

Abhängigkeiten und Bruch

Eine Ansicht hängt von den Tabellen ab, aus denen sie liest. Wir haben eine Ansicht für eine Tabelle erstellt und dann die Tabelle gelöscht:

CREATE TABLE ghost AS SELECT 1 AS x;
CREATE VIEW v_ghost AS SELECT x FROM ghost;
DROP TABLE ghost;

SELECT * FROM v_ghost;
-- no such table: main.ghost

SQLite erlaubte das Löschen und schlug erst später fehl, als die Ansicht verwendet wurde. Andere Datenbanken wie PostgreSQL weigern sich, eine Tabelle zu löschen, von der eine Ansicht abhängt, es sei denn, Sie fügen CASCADE hinzu, wodurch auch die Ansicht entfernt wird. In jedem Fall kann das Umbenennen oder Löschen einer Spalte dazu führen, dass Ansichten beschädigt werden. Überprüfen Sie sie daher in Ihren Regressionstests nach Schemaänderungen.

Es gibt auch eine subtile Falle mit SELECT * in einer Ansicht. Wir haben CREATE VIEW star AS SELECT * FROM customers erstellt und dann der Tabelle eine Spalte hinzugefügt. In SQLite hat die Ansicht die neue Spalte übernommen. Andere Datenbanken, einschließlich PostgreSQL, erweitern die Spaltenliste, wenn die Ansicht erstellt wird, sodass keine neuen Spalten angezeigt werden. Listen Sie die Spalten also explizit auf. Es dokumentiert die Ansicht und verhält sich überall gleich.

Ansichten verwalten

Durch das Löschen einer Ansicht wird nur die gespeicherte Abfrage entfernt. In unserem Test hatte die Tabelle customers nach dem Weglassen von public_customers immer noch alle drei Zeilen. Behalten Sie bei Ihren Migrationen die Versionskontrolle der Ansichtsdefinitionen bei, sodass Änderungen wie jeder andere Code überprüft werden.

Ansicht vs. CTE vs. Tabelle

Häufige Fehler

• Erwartet einen Geschwindigkeitsschub. Eine normale Ansicht führt ihre Abfrage jedes Mal aus.
• Verwendung von SELECT * in Ansichten. Das Verhalten unterscheidet sich zwischen den Datenbanken, wenn sich Tabellen ändern.
• Tiefe Verschachtelung. Ansichten, die auf Ansichten basieren, verbergen teure Verknüpfungen.
• Verwendung von ORDER BY innerhalb einer Ansicht. Die Reihenfolge ist bei der Abfrage nicht garantiert, also sortieren Sie in der äußeren Abfrage.
• Unklare Namen. Ein Präfix wie v_ oder ein klares Substantiv wie customer_sales hilft.
• Ansichten bei Migrationen vergessen. Testen Sie sie nach jeder Schemaänderung.

Fazit

Ansichten sind eine einfache Möglichkeit, wichtiger Logik einen Platz in der Datenbank zu geben. Verwenden Sie sie, um Berichte konsistent zu halten und nicht benötigte Spalten auszublenden, und nicht als Leistungstrick. Suchen Sie zunächst nach einer Abfrage, die an mehreren Stellen in Ihrem Code vorkommt, wandeln Sie sie in eine Ansicht mit expliziten Spalten um und verweisen Sie jeden Aufrufer darauf.

Häufige Fragen

Lerne weiter