To samo dwudziestoliniowe zapytanie dotyczące raportu zostało skopiowane do pięciu pulpitów nawigacyjnych, a każda kopia uległa pewnemu przesunięciu. Jeden liczy anulowane zamówienia, a drugi nie. Widoki SQL rozwiązują ten problem, jednorazowo definiując tę ​​logikę w bazie danych pod jasną nazwą.

Każdy poniższy przykład uruchomiliśmy w SQLite 3.50 i pokazane wyniki pochodzą z tego uruchomienia. Widoki to standardowy język SQL, ale szczegóły, takie jak aktualizacja poprzez widok, widoki zmaterializowane i obsługa zależności różnią się w zależności od bazy danych i na to zwracamy uwagę.

Co to jest widok?

Widok to SELECT instrukcja zapisana w bazie danych pod nazwą. Kiedy wysyłasz zapytanie do widoku, baza danych uruchamia zapisane zapytanie. Działa jak tabela do odczytu, ale normalny widok nie przechowuje własnej kopii danych.

Pomyśl o filtrze zapisanym w arkuszu kalkulacyjnym. Filtr nie jest drugą kopią arkusza. Jest to sposób patrzenia na te same dane, który zawsze odzwierciedla najnowsze wartości.

Przykładowe tabele

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

Utwórz widok

Ten widok podsumowuje zamówienia każdego klienta. Ukrywa łączenie, grupowanie i wyrażenie CASE za jedną prostą nazwą:

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;

Teraz każdy może z niego korzystać, nie wiedząc, jak to działa:

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 pojawia się z zerem, ponieważ w widoku używane są LEFT JOIN i COALESCE. Takie decyzje, podejmowane raz w widoku, zapewniają spójność raportów. Aby odświeżyć wiedzę na temat obsługi wartości NULL, zobacz Wartości SQL NULL.

Wyświetlenia są zawsze aktualne

Ponieważ widok normalny uruchamia zapytanie za każdym razem, nowe dane pojawiają się natychmiast. Dodaliśmy płatne zamówienie dla Bena i ponownie zapytaliśmy o widok:

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

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

-- Ben | 900

Nie było etapu odświeżania ani kopiowania, które umożliwiałyby synchronizację. Jest to kluczowa różnica w stosunku do zapisywania wyników zapytania w osobnej tabeli.

Ukrywanie kolumn

Widoki są często używane do udostępniania tylko tych kolumn, których potrzebuje dana osoba lub aplikacja. Tutaj pominięto adresy e-mail i limity kredytowe:

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

-- columns: id, name, city

W bazach danych z uprawnieniami, takich jak PostgreSQL, SQL Server i MySQL, użytkownikowi raportującemu można przyznać dostęp do widoku, ale nie do tabeli źródłowej. SQLite nie ma kont użytkowników, więc mogliśmy pokazać tylko samo ograniczenie kolumny. Traktuj widok jako jedną warstwę ochrony. Wrażliwe dane nadal wymagają odpowiedniej kontroli dostępu, a niektóre bazy danych oferują zabezpieczenia na poziomie wierszy w celu filtrowania wierszy według użytkownika.

Wydajność: widoki nie zwiększają prędkości

Powszechnym mitem jest twierdzenie, że widoki przyspieszają wykonywanie zapytań. Kiedy wykonujesz zapytanie, widok normalny jest zastępowany jego definicją, więc baza danych działa tak samo, jakbyś sam napisał pełne zapytanie. Nasz plan wykonania pokazał, że w ramach zapytania uruchamiane jest łączenie i grupowanie widoku:

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

Zatem widok wolnego zapytania jest nadal wolnym zapytaniem. Szybkość wynika z indeksów i dobrego projektu zapytań, jak opisano w Optymalizacja zapytań SQL i Wyjaśnienie indeksów SQL. Uważaj także na widoki zbudowane na innych widokach. Warstwy zagnieżdżeń ułatwiają wielokrotne łączenie tego samego dużego stołu bez zauważania.

Zmaterializowane poglądy

Zmaterializowany widok zapisuje swoje wyniki, podobnie jak tabela w pamięci podręcznej. Odczyty są szybkie, ale dane są tak świeże, jak ostatnie odświeżenie.

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

Wsparcie jest zróżnicowane. PostgreSQL i Oracle mają widoki zmaterializowane, a SQL Server oferuje widoki indeksowane z własnymi regułami. Brakuje ich w SQLite i MySQL, dlatego ludzie korzystają z regularnej tabeli podsumowań odświeżanej zgodnie z harmonogramem. W tym artykule nie uruchomiliśmy przykładu PostgreSQL.

Czy możesz zaktualizować widok?

To zależy od bazy danych i widoku. Prosty widok pojedynczej tabeli, bez grupowania lub DISTINCT, często można zaktualizować w PostgreSQL, MySQL i SQL Server, a zmiana zostaje przeniesiona na tabelę. Widok ze złączeniami lub agregacjami zazwyczaj nie jest możliwy, ponieważ baza danych nie jest w stanie określić, który wiersz należy zmienić.

SQLite w ogóle nie pozwala na bezpośredni zapis do widoków. Nasza próba nie powiodła się:

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

-- cannot modify customer_sales because it is a view

Niektóre bazy danych obsługują INSTEAD OF wyzwalacze umożliwiające zapis widoku, a wiele oferuje WITH CHECK OPTION, które zatrzymują zapisy, które nie byłyby widoczne w widoku. W praktyce większość zespołów utrzymuje widoki tylko do odczytu i zapisuje je w tabelach.

Zależności i złamania

Widok zależy od tabel, z których czyta. Stworzyliśmy widok na stole, a następnie upuściliśmy tabelę:

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 pozwolił na upuszczenie i nie powiódł się dopiero później, gdy użyto widoku. Inne bazy danych, takie jak PostgreSQL, nie usuwają tabeli, od której zależy widok, chyba że dodasz CASCADE, co również usunie widok. Tak czy inaczej, zmiana nazwy lub usunięcie kolumny może spowodować uszkodzenie wyświetleń, więc sprawdź to w testach regresyjnych po zmianach schematu.

Istnieje również subtelna pułapka z SELECT * w widoku. Stworzyliśmy CREATE VIEW star AS SELECT * FROM customers, a następnie dodaliśmy kolumnę do tabeli. W SQLite widok pochwycił nową kolumnę. Inne bazy danych, w tym PostgreSQL, rozwijają listę kolumn podczas tworzenia widoku, więc nowe kolumny nie pojawiają się. Więc wymień kolumny jawnie. Dokumentuje widok i wszędzie zachowuje się tak samo.

Zarządzanie widokami

Upuszczenie widoku powoduje usunięcie tylko zapisanego zapytania. W naszym teście tabela customers po upuszczeniu public_customers nadal zawierała wszystkie trzy wiersze. Zachowaj definicje widoków w kontroli wersji wraz z migracjami, aby zmiany były sprawdzane jak każdy inny kod.

Widok vs CTE vs tabela

Typowe błędy

• Oczekuję zwiększenia prędkości. Widok normalny uruchamia zapytanie za każdym razem.
• Używanie SELECT * w widokach. Zachowanie różni się w przypadku różnych baz danych w przypadku zmiany tabel.
• Głębokie zagnieżdżanie. Widoki zbudowane na widokach ukrywają kosztowne połączenia.
• Korzystanie z funkcji ORDER BY w widoku. Porządek nie jest gwarantowany, gdy wysyłasz zapytanie, więc sortuj w zapytaniu zewnętrznym.
• Niejasne nazwy. Pomocny jest przedrostek taki jak v_ lub wyraźny rzeczownik taki jak customer_sales.
• Zapominanie widoków podczas migracji. Testuj je po każdej zmianie schematu.

Podsumowanie

Widoki to prosty sposób na umieszczenie ważnej logiki w bazie danych. Używaj ich, aby zachować spójność raportów i ukryć niepotrzebne kolumny, a nie jako trik zwiększający wydajność. Na początek znajdź zapytanie, które pojawia się w kilku miejscach Twojego kodu, zamień je w widok z wyraźnymi kolumnami i skieruj na nie każdego wywołującego.

Najczęstsze pytania

Ucz się dalej