CASE WHEN 쿼리 개선

appti·2024년 2월 21일

개선

목록 보기

2/4

서론

팀 프로젝트에서 다음과 같은 요구사항이 주어졌습니다.

경매가 마감 임박인 순으로 경매 목록 정렬
경매 마감 일자가 동일하다만 경매 생성 시작가 최신인 경매가 먼저 정렬
이미 마감된 경매는 진행중인 경매보다 후순위 정렬

오늘이 5월 1일이라고 가정했을 때, 다음과 같이 정렬되어야 했습니다.

id	경매 생성 일자	경매 종료 일자
5	4월 20일	2일 후 마감
4	4월 12일	3일 후 마감
3	4월 1일	7일 후 마감
1	3월 3일	12일 전 마감
2	3월 4일	5일 전 마감

배경

+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| auctioneer_count   | int          | NO   |     | NULL    |                |
| bid_unit           | int          | YES  |     | NULL    |                |
| is_deleted         | bit(1)       | YES  | MUL | NULL    | 삭제 여부        |
| start_price        | int          | YES  |     | NULL    |                |
| closing_time       | datetime(6)  | YES  |     | NULL    | 경매 마감 일자    |
| created_time       | datetime(6)  | NO   |     | NULL    |                |
| id                 | bigint       | NO   | PRI | NULL    | auto_increment |
| last_bid_id        | bigint       | YES  | UNI | NULL    |                |
| last_modified_time | datetime(6)  | NO   |     | NULL    |                |
| seller_id          | bigint       | YES  | MUL | NULL    |                |
| sub_category_id    | bigint       | YES  | MUL | NULL    |                |
| title              | varchar(30)  | YES  |     | NULL    |                |
| description        | text         | YES  |     | NULL    |                |
| store_name         | varchar(255) | YES  |     | NULL    |                |
| upload_name        | varchar(255) | YES  |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+

다음과 같은 구조입니다.
해당 글에서는 id(PK), is_deleted(삭제 여부), closing_time(경매 마감 일자) 만을 고려하면 됩니다.

성능을 측정하기 위해 다음과 같이 분류된 100만 개의 더미 데이터를 준비했습니다.

25만건 : 삭제되지 않은 진행중인 경매
25만건 : 삭제되지 않은 마감된 경매
50만건 : 삭제된 경매

구현

CASE WHEN

일단 Auction 테이블은 다른 테이블의 데이터도 필요하기 때문에 조인을 사용합니다.
이 때, 처음부터 모든 값을 조회하지 않고 id만을 조회한 뒤 IN 절로 처리했으므로 id를 구하는 쿼리를 고려했습니다.

SELECT auction.id  
FROM auction auction  
WHERE auction.is_deleted = 0  
ORDER BY
CASE
    WHEN (auction.closing_time > now()) then 1  
    ELSE 2
END ASC,  
auction.closing_time ASC,  
auction.id DESC  
limit 0, 11;

하나의 컬럼 closing_time과 쿼리 실행 시간(now())을 비교해 다른 방식의 정렬을 적용해야 했기 때문에, CASE WHEN을 사용했습니다.

이후 다음과 같은 인덱스를 추가했습니다.

CREATE INDEX idx_auction_is_deleted_closing_time_id_desc ON auction(is_deleted, closing_time, id DESC);

실행 계획은 다음과 같습니다.

+----+-------------+---------+------------+------+---------------------------------------------+---------------------------------------------+---------+-------+--------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys                               | key                                         | key_len | ref   | rows   | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------------------------------------+---------------------------------------------+---------+-------+--------+----------+-----------------------------+
|  1 | SIMPLE      | auction | NULL       | ref  | idx_auction_is_deleted_closing_time_id_desc | idx_auction_is_deleted_closing_time_id_desc | 2       | const | 498383 |   100.00 | Using index; Using filesort |
+----+-------------+---------+------------+------+---------------------------------------------+---------------------------------------------+---------+-------+--------+----------+-----------------------------+

범위 검색이 아니기 때문에 type은 ref이며 Extra의 경우 CASE WHEN으로 인해 Using filesort가 적용된 것을 확인할 수 있습니다.

데이터 100만개 기준으로 다음과 같은 쿼리를 실행했습니다.
1. LIMIT 0, 11 -> 진행 중인 경매 1 페이지 조회
2. LIMIT 15000, 11 -> 임의의 진행 중인 경매 페이지 조회
3. LIMIT 249995, 11 -> 5개의 진행 중인 경매와 6개의 마감된 경매 조회

-- 쿼리 실행시간 2.35s
SELECT auction.id  
FROMauction auction  
WHERE auction.is_deleted = 0  
ORDER BY
CASE
    WHEN (auction.closing_time > '2024-02-21T17:02:00') THEN 1  
    ELSE 2
END ASC,  
auction.closing_time ASC,  
auction.id DESC  
LIMIT 0, 11;

-- 쿼리 실행시간 2.77s
SELECT auction.id  
FROMauction auction  
WHERE auction.is_deleted = 0  
ORDER BY
CASE
    WHEN (auction.closing_time > '2024-02-21T17:02:00') THEN 1  
    ELSE 2
END ASC,  
auction.closing_time ASC,  
auction.id DESC  
LIMIT 15000, 11;

-- 쿼리 실행시간 3.42s
SELECT auction.id  
FROMauction auction  
WHERE auction.is_deleted = 0  
ORDER BY
CASE
    WHEN (auction.closing_time > '2024-02-21T17:02:00') THEN 1  
    ELSE 2
END ASC,  
auction.closing_time ASC,  
auction.id DESC  
LIMIT 249995, 11;

전반적으로 시간이 오래 걸리는 것을 확인할 수 있었습니다.

UNION ALL

CASE WHEN을 사용한 이유는 closing_time 컬럼에 대한 정렬 방식이 다르기 때문이었습니다.

그렇다면 쿼리를 나눈 뒤 결과를 합치면 되는 것이 아닌가 싶어 UNION ALL로 개선을 시도했습니다.

CASE WHEN으로 실행한 쿼리는 다음과 같이 분리할 수 있습니다.

진행중인 경매의 경우 closing_time 오름차순 정렬
종료된 경매의 경우 closing_time 내림차순 정렬

현재 인덱스는 closing_time 오름차순 정렬 밖에 없기 때문에, 다음과 같은 인덱스를 추가했습니다.

CREATE INDEX idx_auction_is_deleted_closing_time_desc_id_desc ON auction(is_deleted, closing_time DESC, id DESC);

이후 다음과 같은 쿼리를 작성했습니다.

(SELECT auction.id 
FROM auction auction 
WHERE auction.is_deleted = 0 and auction.closing_time > '2024-02-21T17:02:00' 
ORDER by auction.closing_time ASC, auction.id DESC)

UNION ALL

(SELECT auction.id 
FROM auction auction 
WHERE auction.is_deleted = 0 and auction.closing_time <= '2024-02-21T17:02:00' 
ORDER BY auction.closing_time DESC, auction.id DESC)

LIMIT 0, 11;

실행 계획은 다음과 같습니다.

+----+-------------+---------+------------+-------+----------------------------------------------------------------------------------------------+--------------------------------------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys                                                                                | key                                              | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+---------+------------+-------+----------------------------------------------------------------------------------------------+--------------------------------------------------+---------+------+--------+----------+--------------------------+
|  1 | PRIMARY     | auction | NULL       | range | idx_auction_is_deleted_closing_time_id_desc,idx_auction_is_deleted_closing_time_desc_id_desc | idx_auction_is_deleted_closing_time_id_desc      | 11      | NULL | 488576 |   100.00 | Using where; Using index |
|  2 | UNION       | auction | NULL       | range | idx_auction_is_deleted_closing_time_id_desc,idx_auction_is_deleted_closing_time_desc_id_desc | idx_auction_is_deleted_closing_time_desc_id_desc | 11      | NULL | 488504 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+----------------------------------------------------------------------------------------------+--------------------------------------------------+---------+------+--------+----------+--------------------------+

인덱스에 지정된 컬럼으로 범위 검색을 진행했으므로 type이 range로 표기되었고, Extra의 경우 CASE WHEN으로 인한 Using filesort가 사라진 것을 확인할 수 있습니다.

이전과 동일하게 쿼리 실행 시간을 확인해보도록 하겠습니다.

LIMIT 0, 11 -> 진행 중인 경매 1 페이지 조회
LIMIT 15000, 11 -> 임의의 진행 중인 경매 페이지 조회
LIMIT 249995, 11 -> 5개의 진행 중인 경매와 6개의 마감된 경매 조회

-- 쿼리 실행시간 0.01s
(SELECT auction.id 
FROM auction auction 
WHERE auction.is_deleted = 0 and auction.closing_time > '2024-02-21T17:02:00' 
ORDER by auction.closing_time ASC, auction.id DESC)

UNION ALL

(SELECT auction.id 
FROM auction auction 
WHERE auction.is_deleted = 0 and auction.closing_time <= '2024-02-21T17:02:00' 
ORDER BY auction.closing_time DESC, auction.id DESC)

LIMIT 0, 11;

-- 쿼리 실행시간 0.08s
(SELECT auction.id 
FROM auction auction 
WHERE auction.is_deleted = 0 and auction.closing_time > '2024-02-21T17:02:00' 
ORDER by auction.closing_time ASC, auction.id DESC)

UNION ALL

(SELECT auction.id 
FROM auction auction 
WHERE auction.is_deleted = 0 and auction.closing_time <= '2024-02-21T17:02:00' 
ORDER BY auction.closing_time DESC, auction.id DESC)

LIMIT 15000, 11;

-- 쿼리 실행시간 0.95s
(SELECT auction.id 
FROM auction auction 
WHERE auction.is_deleted = 0 and auction.closing_time > '2024-02-21T17:02:00' 
ORDER by auction.closing_time ASC, auction.id DESC)

UNION ALL

(SELECT auction.id 
FROM auction auction 
WHERE auction.is_deleted = 0 and auction.closing_time <= '2024-02-21T17:02:00' 
ORDER BY auction.closing_time DESC, auction.id DESC)

LIMIT 249995, 11;

CASE WHEN보다 속도가 월등히 빠른 것을 확인할 수 있습니다.
하나 아쉬운 점은 진행중인 경매와 마감된 경매가 섞여서 나오는 경우 시간이 독보적으로 오래 걸린다는 점이었습니다.

첫 번째 쿼리와 두 번째 쿼리를 모두 수행한 뒤 이를 합치고 LIMIT로 페이징 처리가 필요하므로 자연스러운 결과이지만, 그래도 아쉬운 점이라는 사실은 분명했습니다.

비즈니스 로직 분리

실제 서비스에 100만 건의 데이터가 있다고 가정하고, 사용자가 경매 목록을 검색중이라고 가정하겠습니다.

이 때, 사용자가 최근 낙찰가를 확인하고 싶어 종료된 경매를 확인하고자 한다면 지금 쿼리로는 진행중인 경매를 모두 지나친 다음에서야 확인할 수 있을 것입니다.

그렇지 않고 사용자가 진행중인 경매를 확인하고자 한다면, 이 사용자에게는 종료된 경매가 큰 의미가 없을 것입니다.

즉, 사용자의 의도에 따라서 진행중인 경매 혹은 종료된 경매 한 쪽은 아예 볼 필요가 없다는 의미입니다.

그래서 이 쿼리를 한 번에 처리하지 않고 두 번으로 나눠서 실행하도록 변경했습니다.

UNION ALL에서 이미 실행 계획을 간접적으로 확인했기 때문에, 간단하게 LIMIT를 활용한 페이징 방식에서 가장 시간이 오래 걸리는 마지막 페이지를 조회하는 쿼리로 테스트 했습니다.

-- 쿼리 실행시간 0.5s
SELECT auction.id 
FROM auction auction 
WHERE auction.is_deleted = 0 and auction.closing_time > '2024-02-21T17:02:00' 
ORDER by auction.closing_time ASC, auction.id DESC 
LIMIT 249995, 11;

+----+-------------+---------+------------+-------+----------------------------------------------------------------------------------------------+---------------------------------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys                                                                                | key                                         | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+---------+------------+-------+----------------------------------------------------------------------------------------------+---------------------------------------------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | auction | NULL       | range | idx_auction_is_deleted_closing_time_id_desc,idx_auction_is_deleted_closing_time_desc_id_desc | idx_auction_is_deleted_closing_time_id_desc | 11      | NULL | 488576 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+----------------------------------------------------------------------------------------------+---------------------------------------------+---------+------+--------+----------+--------------------------+

-- 쿼리 실행시간 0.55s
SELECT auction.id 
FROM auction auction 
WHERE auction.is_deleted = 0 and auction.closing_time <= '2024-02-21T17:02:00' 
ORDER BY auction.closing_time DESC, auction.id DESC 
LIMIT 249995, 11;

+----+-------------+---------+------------+-------+----------------------------------------------------------------------------------------------+--------------------------------------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys                                                                                | key                                              | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+---------+------------+-------+----------------------------------------------------------------------------------------------+--------------------------------------------------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | auction | NULL       | range | idx_auction_is_deleted_closing_time_id_desc,idx_auction_is_deleted_closing_time_desc_id_desc | idx_auction_is_deleted_closing_time_desc_id_desc | 11      | NULL | 488504 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+----------------------------------------------------------------------------------------------+--------------------------------------------------+---------+------+--------+----------+--------------------------+

쿼리 실행 시간의 경우 0.98s -> 0.55s로 단축할 수 있었습니다.

간단하게 생각해봐도 UNION ALL을 쓸 때는 최대 50만건을 조회해야 했었지만 이렇게 쿼리를 분리하면 각각 최대 25만건만 조회하면 되므로 시간이 단축될 수 있다고 이해했습니다.

다만 문제점은 현재 데이터가 많지 않아 둘 중 하나만을 조회하면 아예 경매가 없는 것처럼 보일 수 있어, 사용자 유치에 좋지 못한 영향을 끼칠 수 있다고 생각했습니다.

그래서 Service Layer까지는 쿼리를 분리했고, Controller Layer는 진행 중인 경매의 수가 페이지 크기보다 부족할 경우 종료된 경매 쿼리를 조회하도록 개선했습니다.

추후 데이터가 늘어나면 별도의 API로 분리할 예정입니다.

결론

코드 리펙토링

CASE WHEN을 사용하고 있던 쿼리를 두 개의 쿼리로 분리
서비스의 현재 상황을 고려해 API에서 결과에 따라 첫 번째 쿼리 결과가 페이지 크기보다 적은 경우 두 번째 쿼리 호출 후 결과를 합쳐 반환

학습 내용

CASE WHEN으로 정렬을 동적으로 제어할 경우 Using filesort가 발생하므로 비효율적
- 동적으로 쿼리를 제어할 필요가 있을 경우 쿼리를 한 번에 날려야 하는지 고려
- 한 번에 날려야 한다면 UNION ALL 등으로 각 쿼리에 인덱스를 적용하는 방식으로 최적화

appti

안녕하세요

이전 포스트

집계함수 쿼리 개선

다음 포스트