[MySQL] View Processing Alogrighms (MERGE vs. TEMPTABLE)

view 테이블을 활용한 업무 중 문득 아래와 같은 의문이 들었다.

 

가정. view 쿼리 결과가 1000만 건일 경우

1) view 쿼리가 실행될 때 where 절도 함께 실행되어 단일 쿼리처럼 동작할까?

2) 1000만 건을 모두 조회한 후 where 절이 추가로 실행될까?

 

1번처럼 동작한다면 문제가 없겠지만, 2번처럼 동작한다면 조회 시 바라생하는 비용이 클 것이고, view를 사용하기 위한 명확한 이유가 필요할 것이다. 그럼 아래에서 이 문제에 대해 "view는 어떤 식으로 동작하는지?"와 "view를 사용하는 것이 정말 효율적인지?"에 대한 답을 찾아보며, "나는 view를 올바르게 사용하고 있을까?"에 대해서 생각해보는 시간을 가져보자.

 

 

---

우선 MySQL에서 뷰를 생성할 때 사용할 수 있는 `MERGE, TEMPTABLE, UNDEFINED` 세 가지 주요 알고리즘이 있다.

 

1. MERGE

MERGE 알고리즘은 view의 쿼리를 외부 쿼리와 병합하여 실행한다.

 

특징

• 가장 효율적인 방식
• 뷰의 정의가 외부 쿼리에 직접 통합됨
• 옵티마이저가 전체 쿼리를 최적화할 수 있음

제한사항

• GROUP BY, HAVING, DISTINCT, UNION 등이 포함된 복잡한 뷰에는 사용할 수 없음
• 집계 함수가 사용된 경우 사용 불가

Query

-- 1) MERGE 알고리즘을 사용한 view 'v_merge' 생성
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;

-- 2) view 'v_merge'에서 where 조건 추가
SELECT * FROM v_merge WHERE vc1 < 100;

-- 위 쿼리의 실행 결과는 다음과 같이 where 절이 병합된 쿼리로 실행되게 된다.
SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);

 

 

 

2. TEMPTABLE

임시 테이블을 생성하여 뷰의 결과를 저장한 후 외부 쿼리를 실행한다.

 

특징

• 복잡한 뷰를 처리할 수 있음
• 임시 테이블 생성으로 인한 오버헤드 발생
• 메모리 사용량 증가

사용 케이스

• GROUP BY, HAVING, DISTINCT, UNION 등이 포함된 복잡한 뷰
• 집계 함수를 사용하는 뷰

 

 

 

3. UNDEFINED

MySQL이 자동으로 MERGE 또는 TEMPTABLE 중 하나를 선택한다.

 

특징

• 기본 알고리즘
• MySQL 옵티마이저가 상황에 따라 최적의 알고리즘 선택
• 대부분의 경우 MERGE를 우선 시도

사용 시기

• 특별한 요구사항이 없는 경우
• MySQL의 자동 최적화를 신뢰할 수 있는 경우

 

 

---

Q1. TEMPTABLE 알고리즘을 명시적으로 사용해야 할 때가 있을까??

 

TEMPTABLE view는 임시 테이블을 생성하는 데 드는 시간과 메모리 비용을 생각하면 일반적으로 좋은 방법은 아닌 듯 하다.

이러한 단점에도 불구하고, TEMPTABLE 알고리즘을 명시적으로 사용해야 할 때가 있을까??

 

---

 

[사용해야 한다.]

공식 문서를 더 찾아본 바로, 명시적으로 TEMPTABLE을 명시적으로 지정하는 이유에 대해 설명하는 글을 발견했다.

https://dev.mysql.com/doc/refman/8.4/en/view-algorithms.html

 

TEMPTABLE 알고리즘은 임시 테이블이 생성된 후에는 원본 테이블에 대한 락(lock)이 해제되고 나머지 쿼리가 처리되어, view를 사용하는 다른 클라이언트가 더 빨리 테이블을 사용할 수 있게 된다. 반면에, MERGE 알고리즘을 사용할 경우에는, 뷰에 관련된 모든 처리가 끝날 때까지 원본 테이블에 대한 락이 유지되기 때문에, 그 동안 다른 클라이언트가 해당 테이블을 사용할 수 없게 된다.

 

즉, "높은 동시성이 필요한 환경에서 TEMPTABLE 알고리즘을 사용해야 된다."는 것이다.

 

 

위 내용에 따르면 TEMPTABLE 방식의 view는 다음과 같이 동작한다.

1. 임시 테이블을 생성

2. 기본 테이블에서 데이터를 읽어 임시 테이블에 복사

3. *중요. 이후 기본 테이블의 잠금을 즉시 해제

4. 쿼리의 나머지 처리를 임시 테이블에서 동작

 

---

 

[사용하지 않아야 한다.]

 

MySQL의 공식문서에서는 '높은 동시성이 필요한 환경에서 사용해야 된다'라고 소개했지만,

TEMPTABLE 방식의 단점을 안고서라도, 이를 활용해야만 하는 비즈니스 로직이 있을지 아직 잘 모르겠다..

(게임의 경매장 같은 높은 동시성이 필요한 시스템을 구현한다고 해도, A가 물건을 구매했을 때, 해당 물건에 대한 MERGE 방식의 Update 로직이 0.01초라도 Lock이 걸릴 수 있다. 그렇다고 TempTable 방식의 Update는 다른 유저 B가 구매 완료 처리중인 물건에 대해 접근할 수도 있는 것이다.)

 

우선 Select View일 경우,

MySQL-InnoDB의 경우에는 MVCC를 지원해 높은 수준의 트랜잭션 격리가 아니라면 읽기 작업에서는 락을 걸지 않는다.

따라서 "View 쿼리로 인한 Lock이 발생하고, 이를 빠르게 해제하기 위해서 TEMPTABLE 알고리즘을 사용한다??"는 잘 와닿지가 않는다.

(트랜잭션 격리 수준이 SERIALIZABLE로 설정하여, SELECT 쿼리에 공유 락을 걸 경우에는 얘기가 달라질 수 있다..)

 

Update/Delete View일 경우,

MySQL-InnoDB의 경우에는 Update/Delete 작업을 수행할 때, 기본적으로 row-level lock이 걸린다.

만약 동일한 레코드에 대해, 여러 클라이언트의 동시성을 지원해야 한다면 TEMPTABLE 알고리즘이 필요할 수도 있을 것 같다.

하지만 이는 언제까지나, MERGE 알고리즘으로 작업을 수행 시 처리 시간으로 인한 lock이 오래 걸렸을 때의 이야기이다.

(Update/Delete 해야 하는 row가 매우 많으며 계산이 복잡해 처리 시간이 길고, 동시성을 지원해야 하는 비즈니스 로직에 대한 쿼리.. 무엇이 있을까..??)

 

 

---

Q2. 복잡한 View는 사용하는 것이 좋을까?

앞에서 살펴보았듯이 GROUP BY, HAVING, DISTINCT, UNION 등이 포함된 복잡한 뷰와 집계 함수를 사용하는 뷰는 TEMPTABLE 알고리즘으로 동작하게 된다. 그러면 TEMPTABLE 알고리즘을 사용하기보다는 view를 사용하지 않고 조금 번거롭더라도 프레임워크 내에서의 직접 쿼리(QueryDSL/JPQL)를 사용하는 것이 좋지 않을까?

 

이러할 경우에는 각 프로젝트에서 View를 사용해서 얻는 장점과 단점을 잘 비교해야 한다.

 

장점

• View 재사용성
  • 자주 사용되는 복잡한 쿼리를 1개의 View로 정의하여, 여러 쿼리에서 재사용 할 수 있다.
• 복잡한 비즈니스 로직이나 계산 로직을 DB 레벨에서 캡슐화
  • 응용 프로그램에서는 이러한 로직을 다룰 필요 없이, 그 결과만을 쉽게 가져올 수 있다.

 

단점은 임시 테이블을 생성하는 데 드는 시간과 메모리 비용이다.

---

 

정리하면 성능 측면에서는 프레임워크 내에서의 직접 쿼리(QueryDSL/JPQL)를 사용하는 것이, 유지보수 및 재사용성 측면에서는 View를 사용하는 것이 적절할 것으로 보인다.

 

하지만 정리한 내용은 "이론적"인 내용이고, 실제로 쿼리에 영향을 받는 row 수가 적을 때는 View TEMPTABLE 알고리즘으로 동작해도 큰 차이 없이 동작한다. 따라서 실무에 적용시킬 때는 반드시 View 쿼리와 일반 SQL 쿼리와 쿼리 실행계획과 실행시간을 체크해보고, 큰 차이가 없다면 재사용성과 복잡성을 줄여주는 View를 사용해도 될 듯 하다.

(해당 포스팅을 작성하게 된 이유였던, 회사 프로젝트 또한 view를 사용해서 했습니다.. ㅎㅎ)

 

 

---

참고문헌

1) View Processing Algorithms

https://dev.mysql.com/doc/refman/8.4/en/view-algorithms.html

 

2) Optimizing Derived Tables with Merging or Materialization

https://dev.mysql.com/doc/refman/8.4/en/derived-table-optimization.html

 

3) Beware the Performance Dangers of MySQL Views

https://dev.to/jamiemcmanus/beware-the-performance-dangers-of-mysql-views-352e