View
뷰(view)는 데이터베이스에 존재하는 일종의 가상 테이블이다. 실제 테이블처럼 행과 열을 가지고 있지만, 실제로 데이터를 저장하고 있지는 않습니다. 그러므로 디스크 공간을 차지할 우려가 없다.
MySQL에서 뷰(view)는 다른 테이블이나 다른 뷰에 저장되어 있는 데이터를 보여주는 역할만을 수행한다. 즉, 뷰를 사용하면 여러 테이블이나 뷰를 하나의 테이블처럼 볼 수 있다.
예제 : 모든 직원이 고객테이블에 접근해 고객의 정보를 확인할 수 있어야 하지만, email과 같은 개인정보는 개인정보보호정책에 따라 숨겨져야 한다.
이 경우 직원들이 customer 테이블에 직접 액세스하게 하는 대신 customer_vw 라는 뷰를 정의하고, 여기에 액세스하도록 하면 된다.
CREATE VIEW를 이용해 뷰를 만들어보자.
CREATE VIEW customer_vw (
customer_id
, first_name
, last_name
, email
)
AS
SELECT
customer_id
, first_name
, last_name
, CONCAT(SUBSTR(email,1,2), '*****', SUBSTR(email, -4))
FROM customer ;두번째 SELECT 문에서 뷰의 각 열에 대해 정의해줘야 한다. 이 경우 이메일 주소의 일부를 가려야 하므로 CONCAT과 SUBSTR를 사용했다.
테이블과 마찬가지로 SELECT문으로 뷰를 확인할 수 있다.
테이블과 마찬가지로 DESC문으로 뷰의 열을 확인할 수 있다.
한마디로,
쿼리를 쓰는 사용자 입장에서는 뷰는 그냥 테이블하고 똑같다. (저장되어 있는 데이터가 없을 뿐)
GROUP BY, HAVING, ORDER BY 등을 자유롭게 사용할 수 있고, INNER JOIN 등을 통해 다른 테이블이나 다른 뷰랑 조인할수도 있다.
뷰를 사용하는 이유
뷰의 장점
- 사용자에게 높은 편의성을 제공한다. (한번 저장해두면 계속 재활용할 수 있음)
- 특정 사용자에게 테이블 전체가 아닌 필요한 필드만을 보여줄 수 있다.
- 복잡한 쿼리를 단순화해서 사용할 수 있다.
- 쿼리를 재사용할 수 있다.
- 데이터 수요에 알맞은 다양한 구조의 데이터 분석 기반을 구축할 수 있다. (기존의 테이블 구조를 건드리지 않음)
- 데이터 보안을 제공한다. (WHERE절로 특정 row들만 보여주는 뷰를 보여주는 등 특정 정보들만 보여준다. DBMS의 사용자별 권한 관리 기능을 통해 데이터 분석가가 테이블에 직접적인 접근을 하지 못하도록 막는다.)
뷰의 단점
- 한번 정의된 뷰는 변경할 수 없다.
- 삽입, 삭제, 갱신 작업에 많은 제한 사항을 가진다.
- 자신만의 인덱스를 가질 수 없다.
❶ 데이터 보안
보유한 데이터를 모든 DB사용자에게 노출하게되면, 개인정보 보호 정책이나 법을 위반하게 될 수도 있다. (예를 들면, 신용 카드 번호와 같은 민감한 데이터가 포함된 경우)
예를 들어, 비활성고객(active = 0)을 제외하는 다음 뷰 정의를 보자.
CREATE VIEW active_customer_vw (
customer_id
, first_name
, last_name
, email
)
AS
SELECT
customer_id
, first_name
, last_name
, CONCAT(SUBSTR(email,1,2), '*****', SUBSTR(email, -4))
FROM customer
WHERE active = 1 ;만약 이 뷰를 마케팅부서에 제공하면, WHERE절의 조건 때문에 항상 활성고객만 뷰에 포함되게 되고, 비활성고객에게 어떤 정보를 보내지 않도록 할 수 있다.
❷ 데이터 집계
보고(report)를 위해서는 보통 집계된 데이터가 필요하다.
예를 들어, DVD 대여점 관리자가 새롭게 추가할 영화DVD를 결정하기 위해, 영화 카테고리별 총 판매량을 보여주는 보고서를 매월 생성한다고 가정해보자.
CREATE VIEW sales_by_film_category
AS
SELECT
c.name AS category
, SUM(p.amount) AS total_sales
FROM payment AS p
INNER JOIN rental r ON p.rental_id = r.rental_id
INNER JOIN inventory i ON r.inventory_id = i.inventory_id
INNER JOIN film f ON i.film_id = f.film_id
INNER JOIN film_category fc ON f.film_id = fc.film_id
INNER JOIN category c ON fc.category_id = c.category_id
GROUP BY c.name
ORDER BY total_sales DESC ;위와 같은 뷰를 생성하면 매번 번거롭게 여러 테이블을 조인하지 않고도, sales_by_film_category 테이블만으로 보고서를 쉽게 생성할 수 있다.
❸ 복잡성 숨기기
뷰를 통해 복잡한 쿼리를 단순화해서 사용할 수 있다.
예를 들어, 모든 영화에 대한 정보를 보여주는 보고서를 매달 생성해야 한다고 가정해보자.
CREATE VIEW film_stats
AS
SELECT
f.film_id
, f.title
, f.description
, f.rating
, (SELECT c.name
FROM category c
INNER JOIN film_category fc ON c.category_id = fc.category_id
WHERE fc.film_id = f.film_id
) category_name
, (SELECT count(*)
FROM film_actor fa
WHERE fa.film_id = f.film_id
) num_actors
, (SELECT count(*)
FROM inventory i
WHERE i.film_id = f.film_id
) inventory_cnt
, (SELECT count(*)
FROM inventory i
INNER JOIN rental r ON i.inventory_id = r.inventory_id
WHERE i.film_id = f.film_id
) num_rentals
FROM film f ; 이렇게 뷰를 생성하면 필요한 데이터를 수집하기 위해 매번 6개의 테이블을 탐색하는 대신 film_stats 테이블만을 이용하면 된다.
❹ 분할 데이터의 조인
어떤 데이터베이스는 성능을 높이기 위해 큰 테이블을 여러 조각으로 나누어 설계할 수도 있다.
예를 들어, 고객과 거래횟수가 점점 늘어나면서 payment 테이블이 커지면 이 테이블을 최근 6개월치 거래내역인 payment_current 테이블과, 6개월 이전의 모든 기록을 보유하는 payment_historic 테이블로 나눌 수 있다.
- payment
- payment_current : 최근 6개월 거래내역
- payment_historic : 6개월 이전의 전체 거래내역
이렇게 두개의 테이블로 나눠진 상태에서, 어떤 특정 고객에 대한 모든 거래내역을 보고 싶으면 2개 테이블을 모두 조회해야 한다. 이때 두 테이블을 모두 쿼리하고 결과를 결합하는 뷰를 만들면 모든 거래내역이 마치 단일 테이블에 저장된 것처럼 보일 수 있다.
CREATE VIEW payment_all (
payment_id
, customer_id
, staff_id
, rental_id
, amount
, payment_date
, last_update
)
AS
SELECT
payment_id, customer_id, staff_id, rental_id, amount,
payment_date, last_update
FROM payment_historic
UNION ALL
SELECT
payment_id, customer_id, staff_id, rental_id, amount,
payment_date, last_update
FROM payment_current ;뷰 업데이트
기본적으로 뷰는 읽기 전용으로 많이 사용되지만 어떤 뷰들은 업데이트가 가능하다. 뷰는 아래의 것 중에 하나를 가지고 있으면 업데이트가 되지 않는다.
뷰의 업데이트 제한사항
- 집계함수 (
SUM(),MIN(),MAX(),COUNT(), 등등) DISTINCTGROUP BY또는HAVINGUNION또는UNION ALL- 선택한 리스트에 있는 서브 쿼리
- 조인(Join)
FROM구문에 있는 업데이트할 수 없는(non-updatable) 뷰 : 즉,FROM절에는 최소한 하나 이상의 테이블, 또는 업데이트할 수 있는(updatable) 뷰가 포함되어 있어야 한다.FROM구문에 있는 테이블을 참조하는WHERE구문에 있는 서브쿼리
단순한 뷰 업데이트
앞에서 만들었던 뷰를 다시 보자.
CREATE VIEW customer_vw (
customer_id
, first_name
, last_name
, email
)
AS
SELECT
customer_id
, first_name
, last_name
, CONCAT(SUBSTR(email,1,2), '*****', SUBSTR(email, -4))
FROM customer ;customer_vw 뷰는 단일 테이블을 쿼리하고, 4개 열 중 하나(email)만 표현식을 통해 파생된다. 이 뷰는 위의 제한사항들을 위반하지 않으므로 이 뷰를 사용해 customer 테이블의 데이터를 수정할 수 있다.
UPDATE customer_vw
SET last_name = 'SMITH-ALLEN'
WHERE customer_id = 1 ; 결과적으로 customer_id가 1인 고객의 성이 SMITH에서 SMITH-ALLEN으로 바뀌게 된다.
하지만 email의 경우에는 표현식에서 파생되므로 수정할 수 없다.
만약 email열의 데이터를 업데이트하려 한다면, 아래와 같이 에러가 발생할 것이다.
UPDATE customer_vw
SET email = 'MARY.SMITH-ALLEN@sakilacustomer.org'
WHERE customer_id = 1 ;
-----------------
ERROR 1348(HY000): Column 'email' is not updatable표현식에서 파생된 파생 열은 뷰를 이용해 데이터 값을 업데이트 할 수 없다.
파생 열이 포함된 뷰는 데이터 삽입이 불가능하다.
복잡한 뷰 업데이트
기존의 테이블 구조를 건드리지 않으면서 복잡한 쿼리를 단순화해서 사용하는 장점을 가진 만큼, 대부분의 뷰는 FROM 절에 여러 테이블을 포함한다.
예를 들면 다음 뷰는 customer, address, city, country 테이블을 조인하여 모든 고객 데이터를 편리하게 쿼리할 수 있다.
CREATE VIEW customer_details
AS
SELECT
c.customer_id
, c.store_id
, c.first_name
, c.last_name
, c.address_id
, c.active
, c.create_date
, a.address
, ct.city
, cn.country
, a.postal_code
FROM customer c
INNER JOIN address a ON c.address_id = a.address_id
INNER JOIN city ct ON a.city_id = ct.city_id
INNER JOIN country cn ON ct.country_id = cn.country_id ; 2개의 테이블의 열을 동시에 수정(Update) → 불가능
하나의 쿼리로 customer 테이블의 last_name, active와 address 테이블의 address 값을 수정하려고 하면 에러가 발생한다.
2개의 테이블에 새로운 값을 추가(Insert) → 어떤 열의 데이터냐에 따라 다름
하나의 쿼리로 신규 고객(customer_id = 9998)의 데이터를 2개의 테이블에 동시에 삽입하면 정상작동한다.
이 쿼리가 정상작동한 이유는 customer 테이블에 address_id가 FK로 있기 때문이다. 만약 서로 다른 두 테이블 중 한 테이블에 없는 열을 포함해(address)동시에 추가하려고 하면 에러가 발생한다.
따라서 복잡한 뷰에 데이터를 삽입하려면 각 열의 출처를 명확히 알아야 한다.
참고 포스트
