[MySQL] DISTINCT 사용에 따른 임시 테이블 및 성능 차이

 
 

개요

---

공연 요약 정보를 조회하는 경우 중복 데이터를 제거하기 위해 DISTINCT 명령어를 사용했었습니다.
 
하지만 테이블 구조가 잘못되었음을 깨닫고 테이블 구조를 리팩토링 하게 되었습니다.
 
테이블 구조 리팩토링을 진행하니 더 이상 DISTINCT 명령어가 필요하지 않게 되었습니다.
 
하지만 당시에 급하게 리팩토링 작업을 진행하면서 DISTINCT 명령어를 삭제하지 않고 방치하게 되었습니다.
 
DISTINCT에 대한 학습을 진행하면서 이 명령어가 조회 성능에 영향을 미칠 수 있다는 것을 깨닫게 되었고
 
실제로 이 명령어가 그동안 조회 성능에 영향을 미치고 있었는지 확인해 보고자 합니다.
 
 

+ DB는 MySQL 8.0을 사용하고 있습니다.

 
 

어디서 문제가 발생했을까?

---

멜론 티켓 콘서트 정보

위의 이미지는 멜론 티켓에서 콘서트를 조회한 페이지입니다.
 
멜론 티켓에서는 콘서트 목록을 조회하는 경우, 다음과 같은 정보들을 확인할 수 있습니다.

1. 공연 이미지
2. 공연 제목
3. 공연 날짜
4. 공연장 이름
5. 공연 상태 ex) 단독 판매, 판매 중

제 프로젝트에서는 이와 비슷한 구조로 만들고자 하였고, 다음 정보들을 반환하기로 결정했습니다.

1. 공연 이미지
2. 공연 제목
3. 공연장 이름

 

@Subselect("""
SELECT DISTINCT 
    c.concert_id,c.title, c.image_path, v.place
FROM concert c join venue v on v.venue_id = c.venue_id
""")

 
공연 요약 정보 조회 모델은 @Subselect를 사용하여 쿼리 결과를 엔티티에 매핑해서 사용하고 있습니다.
 
공연장 정보를 담고 있는 Venue 테이블과 공연 정보를 담고 있는 Concert 테이블을 Join 하고,
 
그 결과에서 식별자, 공연 제목, 공연 이미지, 공연장 이름 컬럼을 가져오도록 작성했습니다.
 
(추가로 페이징을 사용해서 한 번에 10개의 데이터만 받아올 수 있도록 했습니다.)
 
 
 
 
 
현재는 테이블을 수정하여 이 쿼리에 더 이상 DISTINCT 키워드가 필요하지 않았습니다.
 
 

공연 요약 정보 조회

---

select
    s.concert_id,
    s.image_path,
    s.place,
    s.title
from
    (
        select distinct
            c.concert_id,
            c.title,
            c.image_path,
            v.place
        from
            concert c
        join
            venue v on v.venue_id = c.venue_id
    ) s
limit 0, 10;

 
위의 쿼리는 공연 요약 정보가 조회되는 경우 실제로 발생되는 쿼리로 DISTINCT를 사용하고 있습니다.
 
이 쿼리는 다음과 같은 이유로 임시 테이블을 사용할 것으로 예상했습니다.

1. DISTINCT가 인덱스로 처리되지 못하는 경우
2. 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리

실제로 이 쿼리가 동작할 때 임시 테이블을 사용하고 있다면, 이는 쿼리 성능이 저하될 수 있어 반드시 개선해야 한다고 생각했습니다.
 
 

실행 계획을 확인해 보자

---

실제로 해당 쿼리를 사용했을 때 임시 테이블을 사용하는지 확인해 보았습니다.
 

DISTINCT 제거 전 실행 계획

위의 이미지는 EXPLAIN 명령어를 사용해서 기존 쿼리의 실행 계획을 확인한 결과입니다.
 
2행의 Extra에 존재하는 Using temporary를 통해 해당 쿼리가 임시 테이블을 사용하고 있다는 것을 확인할 수 있습니다.
 
그렇다면, DISTINCT를 삭제하면 Using temporary가 사라질까요?

DISTINCT 제거 후 실행 계획

네, Using temporary가 사라진 걸 확인할 수 있습니다.
 
 
 
 

임시 테이블과 성능 저하

---

임시 테이블을 사용하는 경우 성능에 영향을 미치는 이유는 다음 두 가지라고 생각합니다.
 
 

첫 번째, 메모리 임시 테이블을 사용하면서 임시 테이블을 위한 Create/Drop/Insert/Select 쿼리가 발생

임시 테이블 여부에 따른 벤치마킹 결과에 따르면 동시 접속 자가 많아질수록 임시 테이블 없이 사용하는 것이 성능이 좋았다고 합니다.

 

두 번째, 디스크 임시 테이블을 사용하는 경우 임시 테이블로 레코드를 복사하고 읽는 오버헤드 발생

Real MySQL 8.0에 따르면 "내부 임시 테이블은 기본적으로 메모리상에 만들어지지만 다음 조건을 만족하면 메모리에 임시 테이블을 사용할 수 없고, 디스크 기반의 임시 테이블을 사용한다."라고 합니다. 다음 조건을 확인해 보겠습니다.

• UNION이나 UNION ALL에서 SELECT 되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
• GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
• 메모리 임시 테이블의 크기가 (Memory 스토리지 엔진에서) tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 (TempTable 스토리지 엔진에서) temptable_max_ram 시스템 변수 값보다 큰 경우

메모리 임시 테이블을 사용하기만 해도 성능에 저하가 발생하지만, 디스크 임시 테이블을 사용하면 디스크 입출력 I/O에 따른 오버헤드로 그 영향이 더욱 커집니다.
 
그러므로, 기존 쿼리가 어떤 임시 테이블을 사용하고 있는지 확인해 보겠습니다.

DISTINCT 사용 쿼리 결과

실제로 DISTINCT를 사용한 쿼리를 확인해 보면 임시 테이블을 2개 사용하고 있습니다.
 
여기서 Created_tmp_disk_tables의 결과는 0으로, 해당 쿼리는 메모리 임시 테이블을 사용하고 있다고 판단할 수 있습니다.
 
(임시 테이블을 1개만 사용할 것이라고 생각했는데 2개를 사용하고 있었습니다. 이는 더 학습을 해보겠습니다.)
 

DISTINCT 제거 쿼리 결과

DISTINCT를 제거하면 예상했던 대로 임시 테이블을 사용하지 않습니다.
 
 
 
대규모 프로젝트 상황에서 반드시 임시 테이블을 사용해야 하는 경우에는 temptable_max_ram의 크기를 증가시켜 최대한 메모리 임시 테이블을 사용하는 방향으로 개선할 수 있다고 생각합니다.
 
하지만 제 프로젝트는 소규모이고 해당 쿼리는 임시 테이블 사용이 필수적이지 않다고 판단하여 시스템 변수를 수정하는 개선 방향은 고려하지 않았습니다.
 
 
 

수행 시간 측정 및 비교

---

profiling

MySQL의 profiling 기능으로 쿼리 수행 시간을 측정해 보았습니다.
 
(데이터는 Venue에 10개 Concert에 100개를 넣은 상황에서 측정했습니다.)
 
2행은 DISTINCT를 제거한 쿼리 수행 시간 측정 결과이고, 4행은 DISTINCT를 사용한 결과입니다.
 
저장된 데이터의 개수가 상당히 적음에도 불구하고 0.00161000 -> 0.00090275로 약 44%의 개선 효과를 얻을 수 있었습니다.
 
데이터가 훨씬 많았다면 이 쿼리 수행 시간의 격차는 더욱 커졌을 것이라고 생각합니다.
 

이 결과를 통해 DISTINCT 사용 시 임시 테이블이 생성되어 성능이 저하될 수 있음을 확인했습니다. 따라서 불필요한 임시 테이블 생성을 피하고자 DISTINCT를 제거하여 공연 요약 정보 조회 성능을 개선할 수 있었습니다.

 
 
 
 
 
 

참고

---

 
https://learn.microsoft.com/en-us/azure/mysql/flexible-server/how-to-troubleshoot-low-memory-issues
 
https://gywn.net/2012/06/mysql-temporary-table-effect/

Real MySQL 8.0 - 백은빈, 이성욱 지음