View와 Materialized View

갑자기 궁금한 게 생겼다. 바로 "View에도 인덱스를 걸 수 있나?" 였다.

 

그리고 궁금점에 대한 답을 찾아보던 중 몰랐던 개념을 알아냈다. 바로 MATERIALIZED VIEW였다.

 

얼추 어떤 것인지는 알아냈지만, "알았다!" 하고 아무것도 안하면 분명 까먹을 것이 분명하기에 적어놓으려고 한다.

 

 

VIEW

우선 VIEW는 데이터베이스의 테이블에서 가져온 결과 세트에 대한 저장된 쿼리이다. 실제 데이터를 저장하지 않고, 특정 쿼리에 대한 결과를 제공하는 "가상 테이블"로 생각하면 된다.

 

예를 들어서 다음과 같은 DB가 있다고 가정하자.

우선 위 세 개의 테이블에 대한 CREATE와 테스트용으로 사용할 더미 데이터를 입력한다.

 

참고로 MariaDB는 Materialized View를 지원하지 않아 PostgreSQL을 사용했다.

*위에서는 user인데 아래 실제 테이블은 users로 선언한 이유가 PSql이라서.

 

-- 귀찮아서 외래키 선언은 제외

DROP TABLE IF EXISTS post;
DROP TABLE IF EXISTS comment;
DROP TABLE IF EXISTS users;

CREATE TABLE post (
	post_id		INT		NOT NULL 	PRIMARY KEY,
	title		VARCHAR(50)	NOT NULL,
	content		TEXT		NOT NULL,
	write_date	TIMESTAMP	NOT NULL,
	user_id		VARCHAR(20)	NOT NULL
);

CREATE TABLE comment (
	content_id	INT		NOT NULL 	PRIMARY KEY,
	content		TEXT		NOT NULL,
	post_id		INT		NOT NULL,
	user_id		VARCHAR(20)	NOT NULL
);

CREATE TABLE users (
	user_id		VARCHAR(20)	NOT NULL 	PRIMARY KEY,
	name		VARCHAR(20)	NOT NULL
);

INSERT INTO users VALUES 
('A', '김씨'),
('B', '이씨'),
('C', '박씨'),
('D', '최씨');

INSERT INTO post VALUES
(1, '제목1', '대충이런저런내용', NOW(), 'A'),
(2, '제목2', '이런저런내용대충', NOW(), 'B');

INSERT INTO comment VALUES
(1, '좋은글이네요', 1, 'C'),
(2, '잘 보고 갑니다', 1, 'D');

 

이제 사용자에게 보여줄 리스트를 작성하기 위해 아래의 정보를 출력할 예정이다.

• 게시글의 id
• 게시글의 제목
• 게시글의 작성일
• 게시글 작성자의 닉네임
• 게시글에 대한 댓글 수

만약 위 정보를 출력하기 위해서는 아래의 쿼리가 필요할 것이다.

SELECT 
	p.post_id,
	p.title, 
	p.write_date, 
	u.name, 
	COUNT(c.content_id) as comment_count
FROM 
	post p
	JOIN users u ON p.user_id = u.user_id
	LEFT JOIN comment c ON p.post_id = c.post_id
GROUP BY 
	p.post_id, u.name;

 

하지만 굳이 데이터를 가져올 때마다 join 해서 가져오기는 귀찮다.

이럴 때 사용하는 것이 view이다.

 

view를 이용해 사실은 테이블은 존재하지 않지만, 마치 테이블이 존재하는 것처럼 사용하는 기술이다.

CREATE VIEW post_list_view AS
SELECT 
	p.post_id,
	p.title, 
	p.write_date, 
	u.name, 
	COUNT(c.content_id) as comment_count
FROM 
	post p
	JOIN users u ON p.user_id = u.user_id
	LEFT JOIN comment c ON p.post_id = c.post_id
GROUP BY 
	p.post_id, u.name;

 

다시 한 번 언급하지만 view는 실제 테이블이 아닌 가상 테이블이다. 고로 새로운 데이터가 INSERT된다면 원본 테이블의 변화에 따라 굳이 별다른 수정이 없어도 자동으로 갱신이 된다.

INSERT 후 곧바로 반영되는 VIEW

 

그런데 view에는 한 가지 단점이 존재한다. 바로 실제 테이블이 아니라 가상 테이블이기에 인덱스를 걸 수가 없다는 점이다.

인덱스 생성 시 에러 발생

 

지금이야 post가 몇 개 없다지만 상용에서 수십만, 수백만개의 post가 쌓였는데 index가 없다면 당연히 sql이 느려질 수밖에 없다.

 

 

Materialized View

그래서 이에 대한 단점을 해결하고자 Materialized View를 사용할 수 있다.

*참고로 위에서 언급했듯 mariaDB에는 존재하지 않는 기능이다.

 

View가 논리적으로 존재했다면, Materialized View는 물리적으로 존재하는 테이블이다.

물리적으로 존재한다는 말은 SELECT 시 그냥 안에 적재된 데이터만 가져만 오면 되기 때문에 조회도 빠르고, INDEX도 걸 수 있다.

 

한 번 생성을 해보자. 간단하게 CREATE 과 VIEW 사이에 MATERIALIZED 키워드만 넣어주면 된다.

CREATE MATERIALIZED VIEW post_list_m_view AS
SELECT 
	p.post_id,
	p.title, 
	p.write_date, 
	u.name, 
	COUNT(c.content_id) as comment_count
FROM 
	post p
	JOIN users u ON p.user_id = u.user_id
	LEFT JOIN comment c ON p.post_id = c.post_id
GROUP BY 
	p.post_id, u.name;

 

앞서 언급했듯 물리 테이블이기 때문에 이제 INDEX도 걸 수 있게 된다.

VIEW에 INDEX 걸기 성공

 

하지만 단점이 하나 존재한다.

VIEW의 경우는 논리 테이블이었다. 실재하는 테이블이 없었고, 그저 쿼리를 저장해놓는 방식 정도이다. 그렇기에 원본 테이블에 변화가 일어날 경우, VIEW에도 당연히 테이블도 변화되었다.

 

하지만 물리적인 Materialized View는 어떨까? 당연히 물리적이기 때문에 원본 테이블에 어떤 변화가 일어나도 View 안의 내용이 변경되지 않는다.

3번 게시글을 삭제했음에도 남아있다.

 

Materialized View는 스냅샷의 개념이다. 즉, 특정 시점의 데이터를 저장할 뿐 갱신은 수동으로 해주어야 한다.

 

그렇다면 선언을 할 때처럼 select로 다시 데이터를 넣어줘야 하는가? 그럴 필요까지는 없다. 결국에는 View이기 때문에 쿼리를 저장하고 있고, 이에 View의 상태를 갱신하는 명령어가 존재한다.

REFRESH MATERIALIZED VIEW post_list_m_view;

 

위 명령어 입력 후 SELECT 시 원본 테이블의 변화가 View에도 반영되었음을 확인할 수 있다.

아까 3번 게시물을 삭제한 게 반영되었음

 

즉 Materialized View 이용 시 빠른 조회와 인덱스 설정이 가능하다.

다만 물리 테이블이기 때문에 저장 공간을 추가로 차지해야 하고, 갱신 작업이 필요하다는 점을 유의한다.