동일한 20줄의 보고서 쿼리가 5개의 대시보드에 복사되었으며 각 복사본은 조금씩 표류되었습니다. 하나는 취소된 주문을 계산하지만 다른 하나는 취소된 주문을 계산하지 않습니다. 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를 사용하기 때문에 Ben은 0으로 표시됩니다. 이와 같은 결정은 뷰에서 한 번만 이루어지면 보고서의 일관성이 유지됩니다. NULL 처리에 대한 이해를 다시 높이려면 SQL NULL 값을 참조하세요.

뷰는 항상 최신 상태입니다.

일반 보기는 매번 쿼리를 실행하므로 새 데이터가 즉시 나타납니다. Ben에 대한 유료 주문을 추가하고 뷰를 다시 쿼리했습니다.

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을 포함한 다른 데이터베이스는 뷰가 생성될 때 열 목록을 확장하므로 새 열이 표시되지 않습니다. 따라서 열을 명시적으로 나열하십시오. 뷰를 문서화하고 어디에서나 동일하게 작동합니다.

보기 관리

뷰를 삭제하면 저장된 쿼리만 제거됩니다. 테스트에서는 public_customers를 삭제한 후에도 customers 테이블에 여전히 세 개의 행이 모두 남아 있었습니다. 마이그레이션과 함께 버전 제어에 뷰 정의를 유지하여 변경 사항이 다른 코드와 마찬가지로 검토되도록 합니다.

보기 vs CTE vs 테이블

일반적인 실수

• 속도 향상이 예상됩니다. 일반 보기는 매번 쿼리를 실행합니다.
• 뷰에서 SELECT *를 사용합니다. 테이블이 변경되면 데이터베이스마다 동작이 다릅니다.
• 깊은 중첩. 뷰를 기반으로 구축된 뷰는 비용이 많이 드는 조인을 숨깁니다.
• 뷰 내에서 ORDER BY 사용. 쿼리할 때 순서가 보장되지 않으므로 외부 쿼리에서 정렬하세요.
• 불명확한 이름. v_와 같은 접두사 또는 customer_sales과 같은 명확한 명사가 도움이 됩니다.
• 마이그레이션 중에 뷰를 잊어버립니다. 스키마가 변경될 때마다 뷰를 테스트하세요.

마무리

뷰는 중요한 논리를 데이터베이스에 하나의 홈으로 제공하는 간단한 방법입니다. 이를 사용하여 성능 트릭이 아닌 보고서의 일관성을 유지하고 불필요한 열을 숨길 수 있습니다. 시작하려면 코드의 여러 위치에 나타나는 쿼리를 찾아서 명시적인 열이 있는 보기로 전환하고 각 호출자가 이를 가리키도록 합니다.

자주 묻는 질문

계속 학습하세요