Один и тот же запрос отчета из двадцати строк был скопирован на пять информационных панелей, и каждая копия немного изменилась. Один считает отмененные заказы, а другой нет. Представления SQL решают эту проблему, определяя эту логику один раз в базе данных под понятным именем.

Мы выполнили каждый приведенный ниже пример в SQLite 3.50, и показанные результаты получены в результате этого запуска. Представления представляют собой стандартный SQL, но такие детали, как обновление через представление, материализованные представления и обработка зависимостей, различаются в зависимости от базы данных, и мы указываем на это.

Что такое вид?

Представление — это оператор SELECT, сохраненный в базе данных с именем. Когда вы запрашиваете представление, база данных выполняет этот сохраненный запрос. Он действует как таблица для чтения, но обычное представление не содержит собственной копии данных.

Представьте себе сохраненный фильтр в электронной таблице. Фильтр не является второй копией листа. Это способ просмотра одних и тех же данных, который всегда отражает последние значения.

Примеры таблиц

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

Создать представление

В этом представлении суммируются заказы каждого клиента. Он скрывает объединение, группировку и выражение CASE за одним простым именем:

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;

Теперь любой может использовать его, даже не зная, как он работает:

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

Бен отображается с нулем, поскольку в представлении используются LEFT JOIN и COALESCE. Подобные решения, принятые однажды в представлении, — это именно то, что обеспечивает согласованность отчетов. Чтобы освежить понимание обработки NULL, см. Значения SQL NULL.

Просмотры всегда актуальны

Поскольку обычное представление выполняет запрос каждый раз, новые данные появляются немедленно. Мы добавили оплаченный заказ для Бена и снова запросили представление:

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

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

-- Ben | 900

Не было ни шага обновления, ни копии для синхронизации. В этом ключевое отличие от сохранения результатов запроса в отдельную таблицу.

Скрытие столбцов

Представления часто используются для предоставления только тех столбцов, которые нужны пользователю или приложению. Здесь адреса электронной почты и кредитные лимиты не учитываются:

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

-- columns: id, name, city

В базах данных с разрешениями, таких как PostgreSQL, SQL Server и MySQL, вы можете предоставить пользователю отчетов доступ к представлению, но не к базовой таблице. В SQLite нет учетных записей пользователей, поэтому мы могли показать только само ограничение столбца. Рассматривайте представление как один уровень защиты. Конфиденциальные данные по-прежнему требуют надлежащего контроля доступа, а некоторые базы данных обеспечивают безопасность на уровне строк для фильтрации строк по пользователю.

Производительность: просмотры не повышают скорость

Распространенным мифом является то, что представления делают запросы быстрее. Обычное представление заменяется его определением при запросе, поэтому база данных выполняет ту же работу, как если бы вы написали полный запрос самостоятельно. Наш план выполнения показал, что объединение и группировка представлений выполняются как часть запроса:

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

Таким образом, просмотр медленного запроса по-прежнему остается медленным запросом. Скорость зависит от индексов и хорошего дизайна запросов, как описано в Оптимизация SQL-запросов и Объяснение индексов SQL. Также следите за представлениями, построенными на основе других представлений. Слои вложенности позволяют легко и незаметно присоединиться к одной и той же большой таблице несколько раз.

Материализованные представления

Материализованное представление сохраняет свои результаты, как кэшированная таблица. Чтение происходит быстро, но данные остаются свежими только после последнего обновления.

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

Поддержка варьируется. PostgreSQL и Oracle имеют материализованные представления, а SQL Server предлагает индексированные представления со своими собственными правилами. В SQLite и MySQL их нет, поэтому люди используют обычную сводную таблицу, обновляемую по расписанию. Для этой статьи мы не запускали пример PostgreSQL.

Можете ли вы обновить представление?

Это зависит от базы данных и представления. Простое представление одной таблицы без группировки или DISTINCT часто можно обновить в PostgreSQL, MySQL и SQL Server, и изменения передаются в таблицу. Представление с объединениями или агрегатами обычно не может этого сделать, поскольку база данных не может определить, какую строку следует изменить.

SQLite вообще не допускает прямую запись в представления. Наша попытка не удалась:

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

-- cannot modify customer_sales because it is a view

Некоторые базы данных поддерживают триггеры INSTEAD OF, позволяющие сделать представление доступным для записи, а многие предлагают WITH CHECK OPTION, который останавливает запись, которая не была бы видна через представление. На практике большинство команд сохраняют представления только для чтения и записывают данные в таблицы.

Зависимости и поломки

Представление зависит от таблиц, из которых оно считывается. Мы создали представление таблицы, а затем удалили ее:

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 разрешил удаление и потерпел неудачу только позже, когда использовалось представление. Другие базы данных, такие как PostgreSQL, отказываются удалять таблицу, от которой зависит представление, если вы не добавите CASCADE, что также удалит представление. В любом случае переименование или удаление столбца может привести к поломке представлений, поэтому проверьте их при регрессионном тестировании после изменения схемы.

Существует также тонкая ловушка с SELECT * в представлении. Мы создали CREATE VIEW star AS SELECT * FROM customers, затем добавили столбец в таблицу. В SQLite представление подобрало новый столбец. Другие базы данных, включая PostgreSQL, расширяют список столбцов при создании представления, поэтому новые столбцы не появляются. Поэтому перечислите столбцы явно. Он документирует представление и везде ведет себя одинаково.

Управление представлениями

При удалении представления удаляется только сохраненный запрос. В нашем тесте таблица customers все еще содержала все три строки после удаления public_customers. Сохраняйте определения представлений в системе контроля версий вместе с миграциями, чтобы изменения проверялись так же, как и любой другой код.

Просмотр, CTE и таблица

Распространенные ошибки

• Ожидается увеличение скорости. Обычное представление выполняет запрос каждый раз.
• Использование SELECT * в представлениях. Поведение разных баз данных при изменении таблиц различается.
• Глубокая вложенность. Представления, построенные на основе представлений, скрывают дорогостоящие соединения.
• Использование ORDER BY внутри представления. Порядок не гарантируется при запросе, поэтому сортируйте внешний запрос.
• Непонятные имена. Поможет префикс, например v_, или ясное существительное, например customer_sales.
• Забываем представления во время миграции. Проверяйте их после каждого изменения схемы.

Итоги

Представления — это простой способ разместить важную логику в базе данных. Используйте их для обеспечения единообразия отчетов и скрытия ненужных столбцов, а не для снижения производительности. Для начала найдите запрос, который появляется в нескольких местах вашего кода, превратите его в представление с явными столбцами и укажите на него каждому вызывающему объекту.

Часто задаваемые вопросы

Продолжайте учиться