[DB] Materialized view

어느날 태블로 대시보드에 장애가 발생했습니다.

 

원인은 아래와 같이 ... DW에서 데이터 추출 간 걸리는 시간이 초과한 상태였습니다.

 

 

 

크게 추출 시간은 고려하지 않았고 (초기에 1시간쯤 걸리던 추출이 2시간을 넘기겠어?) 

일 단위 추출이라 시간은 크게 고려하지 않았습니다. 

 

물론 Timeout 을 늘리면 문제는 해결되겠지만, 근본적으로 문제를 해결하고자 사용했던 방법인 구체화된 뷰에 대해 적어볼까 합니다.

 

 

Materialized view(구체화된 뷰)란?

구체화된 뷰는 더 빠른 데이터 검색을 위해 여러 기존 테이블의 데이터를 결합하여 생성되는 중복 데이터 테이블입니다. 예를 들어 고객과 제품 데이터를 위한 두 개의 기본 테이블이 있는 소매 애플리케이션을 생각해 봅니다. 고객 테이블에는 고객 이름 및 연락처 세부 정보와 같은 정보가 포함되고 제품 테이블에는 제품 세부 정보 및 비용에 대한 정보가 포함됩니다. 고객 테이블에는 개별 고객이 구매하는 품목의 제품 ID만 저장됩니다. 특정 고객이 구매한 품목의 제품 세부 정보를 얻으려면 두 테이블을 상호 참조해야 합니다. 이렇게 만드는 대신에 고객 이름 및 관련 제품 세부 정보를 단일 임시 테이블에 저장하는 구체화된 뷰를 생성할 수 있습니다. 구체화된 뷰에 인덱스 구조를 구축하여 데이터 읽기 성능을 향상시킬 수 있습니다. - AWS Docs

 

 

쉽게 말해  MV(Materialized view) 란 뷰와 같이 요청 간 쿼리를 수행해 보여주는 것이 아닌

테이블과 유사하게 물리적 공간에 저정하는 뷰를 말합니다.

 

물리적 공간에 데이터를 저장해두니, 접근하여 데이터를 로딩할때 빠르게 접근 가능한 이점이 있습니다.

 

 

 

그럼 언제써?

다양한 케이스에 사용 가능하지만,  분산 환경에서 최신 동기화된 데이터를 참고할때 

 

예를 들어 아래와 같은 아키텍쳐에서도 활용 가능합니다. (출처 AWS summit 2023)

 

또 제가 사용한 케이스 처럼 분석가가 요구한 환경을 뷰로 구축했지만, 뷰에 표시되는 케이스는 많지 않고

연산 자체가 무거울땐, 연산 결과를 저장해두는 MV로 만들어두면 다음부턴 접근이 빠르다는 장점이 있습니다.

 

그 이외 활용 가능한 케이스는 문서 아래에 관련 Docs 첨부했습니다.,

 

 

 

어떻게 쓰는데?

만약 뷰 생성 쿼리가 있으면 create view 대신 materialize 로 변경하고 

옵션만 추가하면 됩니다.

 

생성 및 자동갱신

- MV 를 생성하고 Commit 시점에 업데이트 하는 방식입니다.

CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE 
REFRESH FAST ON COMMIT
AS
SELECT region, SUM(sales_amount) AS total_sales, COUNT(order_id) AS order_count
FROM sales
GROUP BY region;

 

 

단 REFRESH FAST 및 ON COMMIT 옵션은 제약조건이 많아, 서브쿼리나 집계쿼리 등 복잡한 쿼리에는 동작 안할 가능성이 있습니다.

관련 DOCS 또한 아래 첨부드립니다.

 

 

생성 및 수동갱신(스케줄 추가)

- 수동으로 데이터를 전체 갱신하지만, 생성 쿼리에 스케줄 옵션까지 적용한 쿼리입니다.

CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE 
REFRESH COMPLETE 
START WITH SYSDATE 
NEXT SYSDATE + 1/24 -- 매 1시간 간격
AS
SELECT region, SUM(sales_amount) AS total_sales, COUNT(order_id) AS order_count
FROM sales
GROUP BY region;

 

옵션은

 

BUILD IMMEDIATE : MV 생성 즉시 빌드하여 데이터 저장

REFRESH COMPLETE: 전체 데이터를 갱신하는 방식으로 새로고침

REFRESH FAST : 증분 업데이트

ON COMMIT : 커밋 시점에 뷰를 업데이트

NEXT SYSDATE + 1/24: 매 1시간마다 새로고침을 수행합니다. (24분의 1일이므로 하루의 1/24는 1시간)

 

 

등이 있습니다.

 

이렇게 미리 생성된 MV에 스케줄을 따로 추가도 가능합니다.

ALTER MATERIALIZED VIEW mv_sales_summary refresh NEXT sysdate + (1/24);

 

 

결론

무튼 MV 를 적절히 활용해서 최적화 하는 것도 좋은 방법이 될 수 있다고 생각이 듭니다 

전 2시간 이상 걸리던 추출이 46초로 줄었습니다~~ (물론 DB 에서 갱신할때 시간이 들지만, 그렇게 오래 걸리진 않습니다)

 

 

MV 활용 케이스

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dwhsg/basic-materialized-views.html#GUID-A7AE8E5D-68A5-4519-81EB-252EAAF0ADFF

Database Data Warehousing Guide

 

MV 정의

https://aws.amazon.com/ko/what-is/materialized-view/#:~:text=%EA%B5%AC%EC%B2%B4%ED%99%94%EB%90%9C%20%EB%B7%B0%EB%8A%94%20%ED%8A%B9%EC%A0%95,%EB%8D%B0%EC%9D%B4%ED%84%B0%20%EC%95%A1%EC%84%B8%EC%8A%A4%EB%A5%BC%20%EC%A0%9C%EA%B3%B5%ED%95%A9%EB%8B%88%EB%8B%A4.

구체화된 뷰란?- 구체화된 뷰 설명 - AWS

 

 

MV 제약조건

https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/refreshing-materialized-views.html#GUID-587D67C7-CD96-44CA-8B3B-597C39247F31

Data Warehousing Guide