SQL 첫걸음 30강. 뷰 작성과 삭제

뷰 작성과 삭제

• FROM 구에 서브쿼리를 사용할 수 있다는 걸 서브쿼리 부분에서 알 수 있었습니다.
• 이때 서브쿼리에 이름을 붙여 데이터베이스 객체화하여 쓰기 쉽게 한 것을 뷰(View)라고 합니다.

-- 뷰 생성
> CREATE VIEW 뷰_테이블명 AS SELECT * FROM 테이블명; 

-- 뷰 생성 (열 지정)
> CREATE VIEW 뷰_테이블명(컬럼명, 컬럼명, 컬럼명) AS SELECT 컬럼명, 컬럼명, 컬럼명 FROM 테이블명;

-- 뷰 삭제
> DROP VIEW 뷰_테이블명;

---

뷰

• 본래 객체로 사용할 수 없는 SELECT 명령을 객체로서 이름을 붙여 관리할 수 있도록 한 것이 바로 뷰입니다.

• 따라서 뷰를 참조하면 정의된 SELECT 명령의 실행결과를 테이블처럼 사용할 수 있습니다.

• 예를 들어 아래와 같이 FROM 구에 서브쿼리가 들어간 명령문이 존재한다고 가정해봅시다.

SELECT * FROM (SELECT * FROM sample54) AS sq;

• 이때 FROM 구에 작성된 SELECT * FROM sampl54 서브쿼리 부분을 sample_view_67과 같이 이름을 붙여 뷰로 만들 수 있습니다.
• 그러면 아래와 같이 기존 SELECT 명령을 더 단순하게 사용할 수 있습니다.

SELECT * FROM sample_view_67;

이처럼 뷰를 사용하면 복잡한 SELECT 명령을 더 단순하게 사용할 수 있게 됩니다.

가상 테이블

뷰는 테이블처럼 취급되지만 결국 실체가 존재하지 않기 때문에 가상 테이블(Virtual Table)이라 합니다.
따라서 뷰는 다른 테이블처럼 쓰거나 지울 수 있는 저장공간을 가지고 있지 않습니다.

• 물론 INSERT, UPDATE, DELETE 명령도 조건만 맞으면 사용할 수 있지만 SELECT 명령만 사용할 것을 권장하고 있습니다.

---

뷰 작성과 삭제

뷰의 작성

뷰를 작성할 때는 CREATE VIEW 명령을 사용합니다.

• SELECT * FORM sample54 명령문을 대체하는 sample_view_67 뷰를 만드는 방법은 아래와 같습니다.
• 이때 생성된 뷰를 확인하는 명령은 SHOW CREATE VIEW 뷰이름 명려운과 같은 형태입니다.

mysql > CREATE VIEW sample_view_67 AS SELECT * FROM sample54;

Query OK, 0 rows affected (0.00 sec)

mysql > SHOW CREATE VIEW sample_view_67;

+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View           | Create View                                                                                                                                                              | character_set_client | collation_connection |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| sample_view_67 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sample_view_67` AS select `sample54`.`no` AS `no`,`sample54`.`a` AS `a` from `sample54` | utf8mb4              | utf8mb4_0900_ai_ci   |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

생성한 뷰를 실제로 사용해보면 아래와 같이 정상적으로 원하는 결괏값을 반환한 걸 확인할 수 있습니다.

mysql > SELECT * FROM sample_view_67;

+------+------+
| no   | a    |
+------+------+
|    1 |  900 |
|    2 |  900 |
+------+------+
2 rows in set (0.00 sec)

mysql > SELECT * FROM (SELECT * FROM sample54) AS sq;

+------+------+
| no   | a    |
+------+------+
|    1 |  900 |
|    2 |  900 |
+------+------+
2 rows in set (0.00 sec)

• 뷰를 만들 때 열 지정을 생략하면 SELECT 명령의 SELECT 구에서 지정하는 열 정보가 수집되어 자동적으로 뷰의 열로 지정됩니다. 반대로 열을 지정한 경우에는 SELECT 명령의 SELECT 구에 지정한 열보다 우선됩니다. 이때 유의할 점은 열만 지정할 수 있을 뿐 자료형이나 제약을 지정할 수 없습니다.

• 열을 지정하여 뷰를 작성하는 방법은 아래와 같습니다.

mysql > CREATE VIEW sample_view_672(n, v1, v2) AS SELECT no, a, a*2 FROM sample54;

Query OK, 0 rows affected (0.01 sec)

mysql > SELECT * FROM sample_view_672;

+------+------+------+
| n    | v1   | v2   |
+------+------+------+
|    1 |  900 | 1800 |
|    2 |  900 | 1800 |
+------+------+------+
2 rows in set (0.00 sec)

SELECT 명령의 SELECT 구와 같은 수의 열을 일일이 지정해야 하기 때문에 SELECT 명령의 모든 열을 사용할 경우에는 열을 지정하지 않는 편이 낫습니다.

---

뷰 삭제

뷰를 삭제할 때는 DROP VIEW 명령을 사용합니다. 방법은 아래와 같습니다.

mysql> DROP VIEW sample_view_672;

Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE VIEW sample_view_672;

ERROR 1146 (42S02): Table 'sample.sample_view_672' doesn't exist

---

뷰의 약점

• SELECT 명령은 행을 검색하여 클라이언트로 반환하는 명령입니다.

• 따라서 단순한 검색 뿐만 아니라 ORDER BY 명령을 통한 정렬은 물론 GROUP BY 명령을 통해 집계하여 결괏값을 반환할 수 있습니다.

• 이러한 모든 처리는 계산능력을 필요로 하기 때문에 컴퓨터의 CPU를 사용합니다.

• 뷰의 경우 저장되는 것이 SELECT 명령뿐이기 때문에 다른 테이블과 달리 대용량의 저장공간을 필요로 하지 않습니다.

• 그러나 앞서 설명한 것처럼 계산능력을 위해 CPU 자원을 사용합니다.

머티리얼라이즈드 뷰(Materialized View)

• 이처럼 계산능력을 사용하는 뷰이기 때문에 만약 근원이 되는 테이블에 보관되는 데이터양이 많을 경우 처리속도가 떨어집니다.
• 뷰를 중첩해서 사용해도 마찬가지입니다.

이러한 상황을 회피하기 위해 사용할 수 있는 것이 머티리얼라이즈드 뷰(Materialized View)입니다.

• 일반적인 뷰는 데이터를 일시적으로 저장했다가 쿼리가 실행 종료될 때 함께 삭제됩니다.

반면에 머티리얼라이즈 뷰의 경우 처음 참조되었을 때 데이터를 저장해둡니다.

• 이후 다시 참조할 때 이전에 저장한 데이터를 그대로 사용하기 때문에 매번 SELECT 명령을 실행할 필요가 없게 됩니다.

• 마치 테이블처럼 저장장치에 저장해두고 사용하는 것입니다.

• 만약에 뷰에 지정된 테이블의 데이터가 변경되면 SELECT 명령을 재실행하여 데이터를 다시 저장합니다. 이는 RDBMS가 자동으로 실행합니다.

• 뷰에 지정된 테이블의 데이터가 자주 변경되지 않는 경우라면 머티리얼라이즈드 뷰를 사용하여 뷰의 약점을 보완할 수 있습니다.

• 그러나 이를 지원하지 않는 데이터베이스 제품도 있다는 점에 주의해야 합니다.

머티리얼라이즈드 뷰와 같은 방식을 스냅샷(Snapshot)이라 한다.

함수 테이블

• 뷰를 구성하는 SELECT 명령의 경우 단독으로 수행 가능해야 합니다.

• 따라서 상관 서브쿼리처럼 서브쿼리가 부모 쿼리와 연관되어 있는 경우 해당 서브쿼리를 뷰로 사용할 수 없습니다.

• 이러한 뷰의 약점을 보완하기 위해 함수 테이블(Function Table)을 사용할 수 있습니다.

• 함수 테이블은 테이블을 결괏값으로 반환해주는 사용자정의 함수입니다.

• 이때 함수에는 인수를 지정할 수 있기 때문에 인수의 값에 따라 WHERE구의 조건을 붙여 결괏값을 바꿀 수 있습니다.

• 이러한 방식으로 함수 테이블은 서브쿼리처럼 동작합니다.

• MySQL 기준으로 함수는 CREATE FUNCTION 명령을 통해 만들 수 있습니다.

• 데이터베이스에 함수를 만드는 방법에 관해서는 추후에 더 자세히 살펴보겠습니다.