Dezelfde twintigregelige rapportquery is gekopieerd naar vijf dashboards, en elke kopie is een beetje afgedreven. De een telt geannuleerde bestellingen mee, de ander niet. SQL-views lossen dit op door die logica één keer in de database te definiëren, onder een duidelijke naam.

We hebben elk hieronder voorbeeld uitgevoerd in SQLite 3.50, en de getoonde resultaten komen uit die run. Views zijn standaard SQL, maar details zoals het bijwerken via een view, gematerialiseerde views en de afhandeling van afhankelijkheid variëren per database, en daar wijzen we op.

Wat is een uitzicht?

Een weergave is een SELECT-instructie die met een naam in de database is opgeslagen. Wanneer u een query uitvoert op de weergave, voert de database die opgeslagen query uit. Het fungeert als een leestabel, maar een normale weergave bevat geen eigen kopie van de gegevens.

Denk aan een opgeslagen filter in een spreadsheet. Het filter is geen tweede kopie van het blad. Het is een manier om naar dezelfde gegevens te kijken, die altijd de nieuwste waarden weerspiegelen.

De voorbeeldtabellen

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

Maak een weergave

In deze weergave worden de bestellingen van elke klant samengevat. Het verbergt een join, groepering en een CASE-expressie achter één eenvoudige naam:

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;

Nu kan iedereen het gebruiken, zonder te weten hoe het werkt:

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 verschijnt met nul, omdat de weergave een LEFT JOIN en COALESCE gebruikt. Dit soort beslissingen, die eenmaal in het zicht zijn genomen, zijn precies wat de rapporten consistent houdt. Zie SQL NULL-waarden om uw begrip van de NULL-afhandeling op te frissen.

Uitzichten zijn altijd actueel

Omdat een normale weergave elke keer een zoekopdracht uitvoert, verschijnen er onmiddellijk nieuwe gegevens. We hebben een betaalde bestelling voor Ben toegevoegd en de weergave opnieuw opgevraagd:

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

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

-- Ben | 900

Er was geen vernieuwingsstap en geen kopie om gesynchroniseerd te houden. Dit is het belangrijkste verschil met het opslaan van queryresultaten in een aparte tabel.

Kolommen verbergen

Weergaven worden vaak gebruikt om alleen de kolommen weer te geven die een persoon of toepassing nodig heeft. Hier worden e-mailadressen en kredietlimieten weggelaten:

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

-- columns: id, name, city

In databases met machtigingen, zoals PostgreSQL, SQL Server en MySQL, kunt u een rapporterende gebruiker toegang verlenen tot de weergave, maar niet tot de onderliggende tabel. SQLite heeft geen gebruikersaccounts, dus we konden alleen de kolombeperking zelf weergeven. Beschouw een weergave als één beschermingslaag. Gevoelige gegevens hebben nog steeds een goede toegangscontrole nodig, en sommige databases bieden beveiliging op rijniveau voor het filteren van rijen op gebruiker.

Prestaties: weergaven zijn geen snelheidsboost

Een veel voorkomende mythe is dat weergaven zoekopdrachten sneller maken. Een normale weergave wordt vervangen door de definitie ervan wanneer u er een query op uitvoert, zodat de database hetzelfde werk doet alsof u de volledige query zelf had geschreven. Ons uitvoeringsplan liet zien dat de join en groepering van de weergave werden uitgevoerd als onderdeel van de query:

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

Een weergave van een langzame query is dus nog steeds een langzame query. Snelheid is te danken aan indexen en een goed queryontwerp, zoals beschreven in SQL-queryoptimalisatie en SQL-indexen uitgelegd. Let ook op weergaven die op andere weergaven zijn gebaseerd. Dankzij de nestinglagen kun je gemakkelijk meerdere keren aan dezelfde grote tafel schuiven zonder dat je het merkt.

Gematerialiseerde opvattingen

Een gematerialiseerde weergave slaat de resultaten op, net als een tabel in de cache. Het lezen gaat snel, maar de gegevens zijn slechts zo actueel als de laatste vernieuwing.

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

Ondersteuning varieert. PostgreSQL en Oracle hebben gematerialiseerde weergaven, en SQL Server biedt geïndexeerde weergaven met hun eigen regels. SQLite en MySQL missen deze, dus gebruiken mensen een reguliere overzichtstabel die volgens een schema wordt vernieuwd. We hebben het PostgreSQL-voorbeeld voor dit artikel niet uitgevoerd.

Kunt u een weergave bijwerken?

Het hangt af van de database en de weergave. Een eenvoudige weergave van een enkele tabel, zonder groepering of DISTINCT, kan vaak worden bijgewerkt in PostgreSQL, MySQL en SQL Server, en de wijziging wordt doorgevoerd in de tabel. Een weergave met joins of aggregaties kan dat doorgaans niet, omdat de database niet kan bepalen welke rij moet worden gewijzigd.

SQLite staat helemaal geen directe schrijfbewerkingen naar views toe. Onze poging mislukte:

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

-- cannot modify customer_sales because it is a view

Sommige databases ondersteunen INSTEAD OF triggers om een weergave beschrijfbaar te maken, en vele bieden WITH CHECK OPTION, waardoor schrijfbewerkingen worden gestopt die niet zichtbaar zouden zijn via de weergave. In de praktijk houden de meeste teams de weergaven alleen-lezen en schrijven ze naar de tabellen.

Afhankelijkheden en breuk

Een weergave is afhankelijk van de tabellen waaruit deze leest. We hebben een weergave op een tafel gemaakt en vervolgens de tabel laten vallen:

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 stond de verwijdering toe en faalde pas later, toen de weergave werd gebruikt. Andere databases, zoals PostgreSQL, weigeren een tabel te verwijderen waarvan een weergave afhankelijk is, tenzij u CASCADE toevoegt, waardoor de weergave ook wordt verwijderd. Hoe het ook zij, het hernoemen of verwijderen van een kolom kan de weergaven verbreken. Controleer deze dus in uw regressietests na schemawijzigingen.

Er is ook een subtiele valkuil met SELECT * in beeld. We hebben CREATE VIEW star AS SELECT * FROM customers gemaakt en vervolgens een kolom aan de tabel toegevoegd. In SQLite pakte de weergave de nieuwe kolom op. Andere databases, waaronder PostgreSQL, breiden de kolommenlijst uit wanneer de weergave wordt gemaakt, zodat er geen nieuwe kolommen verschijnen. Vermeld de kolommen dus expliciet. Het documenteert het uitzicht en gedraagt ​​zich overal hetzelfde.

Weergaven beheren

Als u een weergave verwijdert, wordt alleen de opgeslagen zoekopdracht verwijderd. In onze test had tabel customers nog steeds alle drie de rijen nadat public_customers was verwijderd. Bewaar weergavedefinities in versiebeheer samen met uw migraties, zodat wijzigingen worden beoordeeld zoals elke andere code.

Bekijk versus CTE versus tabel

Veel voorkomende fouten

• Er wordt een snelheidsboost verwacht. Een normale weergave voert elke keer een zoekopdracht uit.
• SELECT * gebruiken in weergaven. Het gedrag verschilt tussen databases wanneer tabellen veranderen.
• Diep genest. Weergaven die op weergaven zijn gebouwd, verbergen dure joins.
• ORDER BY gebruiken in een weergave. De volgorde is niet gegarandeerd wanneer u er een query op uitvoert, dus sorteer in de buitenste query.
• Onduidelijke namen. Een voorvoegsel zoals v_, of een duidelijk zelfstandig naamwoord zoals customer_sales, helpt.
• Vergeet weergaven tijdens migraties. Test ze na elke schemawijziging.

Tot slot

Views zijn een eenvoudige manier om belangrijke logica een plek in de database te geven. Gebruik ze om rapporten consistent te houden en onnodige kolommen te verbergen, niet als prestatietruc. Zoek om te beginnen een query die op verschillende plaatsen in uw code voorkomt, verander deze in een weergave met expliciete kolommen en wijs elke beller ernaar.

Veelgestelde vragen

Blijf leren