서론
위와 같은 테이블 구조에서, 경매 목록 조회 시 다음과 같은 요구사항이 주어졌습니다.
- 진행중인 경매 우선 정렬
- 진행중인 경매라면 현재 입찰가가 높은 순(bid.price)으로 정렬
- 현재 입찰가가 동일하다면 판매자의 신뢰도(users.reliability)가 높은 순으로 정렬
이를 조회하는 쿼리가 비효율적이라고 생각했기 때문에 이를 개선하고자 합니다.
이제부터 진행할 내용은 다음과 같은 더미 데이터 기준으로 진행했습니다.
- auction 총 100만 건, bid 총 50만 건, 회원 총 10건
- 입찰이 되지 않은 auction : 50만 건
- 입찰됐지만 삭제된 auction : 10만 건
- 입찰 상태의 auction : 40만 건
기존 쿼리
SELECT auction.id
FROM auction
LEFT JOIN bid ON bid.id = auction.last_bid_id
LEFT JOIN users on auction.seller_id = users.id
WHERE auction.is_deleted = 0
ORDER BY bid.price desc, users.reliability desc
LIMIT 200000, 11; 
시간은 6.06초가 걸렸습니다.
상당히 느린 것을 확인할 수 있습니다.
+----+-------------+---------+------------+--------+---------------------+---------+---------+--------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------------+---------+---------+--------------------------------+--------+----------+----------------------------------------------+
| 1 | SIMPLE | auction | NULL | ALL | NULL | NULL | NULL | NULL | 993558 | 50.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | bid | NULL | eq_ref | PRIMARY | PRIMARY | 8 | ddang_test.auction.last_bid_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | users | NULL | eq_ref | PRIMARY | PRIMARY | 8 | ddang_test.auction.seller_id | 1 | 100.00 | NULL |
+----+-------------+---------+------------+--------+---------------------+---------+---------+--------------------------------+--------+----------+----------------------------------------------+
auction - bid - users 순으로 조인이 발생했습니다.
드라이빙 테이블인 auction에 존재하지 않는 컬럼 price, reliability으로 정렬해야 하기 때문에 Using temporary, Using filesort이 발생했습니다.
드리븐 테이블 순서 변경
드라이빙 테이블인 auction의 경우 100만 건으로 가장 많지만, 이를 users나 bid로 바꿀 경우 마지막 입찰가를 찾기 위해 그룹화와 집계 함수 처리를 해야 하므로 오히려 더 느려졌습니다.
그래서 bid - users 순서의 드리븐 테이블을 users - bid 순서로 변경해봤습니다.
SELECT auction.id
FROM auction
LEFT JOIN users on auction.seller_id = users.id
LEFT JOIN bid ON bid.id = auction.last_bid_id
WHERE auction.is_deleted = 0
ORDER BY bid.price desc, users.reliability desc
LIMIT 200000, 11; 
시간은 5.97초가 걸렸습니다.
+----+-------------+---------+------------+--------+---------------------+---------+---------+--------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------------+---------+---------+--------------------------------+--------+----------+----------------------------------------------+
| 1 | SIMPLE | auction | NULL | ALL | NULL | NULL | NULL | NULL | 993558 | 50.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | users | NULL | eq_ref | PRIMARY | PRIMARY | 8 | ddang_test.auction.seller_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | bid | NULL | eq_ref | PRIMARY | PRIMARY | 8 | ddang_test.auction.last_bid_id | 1 | 100.00 | NULL |
+----+-------------+---------+------------+--------+---------------------+---------+---------+--------------------------------+--------+----------+----------------------------------------------+
드리븐 테이블 중 users가 먼저 호출된 것을 확인했습니다.
시간 상으로는 0.06, 코스트 상으로는 100정도 감소했지만 미미하기 때문에 다음 단계로 넘어갔습니다.
비정규화
지금 문제는 auction 테이블로 드라이빙을 해야 원하는 결과를 얻을 수 있는데, 다른 테이블에 위치한 컬럼을 기준으로 정렬을 한다는 점입니다.
이로 인해 Using temporary, Using filesort를 사용했으며 인덱스도 적용할 수 없었습니다.
이를 개선하기 위한 다음 방법으로 비정규화를 고려했습니다.
웹 서비스 특성상 쓰기 작업보다 읽기 작업이 많기 때문에 쓰기 작업 및 메모리를 희생해 읽기 작업 성능을 끌어올릴만 하다고 생각하기 때문입니다.
bid.price, users.reliability 컬럼이 실제 auction에 넣어도 의미가 혼동되지 않을지, 별도로 관리해도 문제가 없을지 체크하면 될 것 같습니다.
bid.price
auction에는 이미 last_bid_id라는 컬럼이 존재합니다.
가장 최근에 입찰한 정보를 찾기 위해, 집계 함수를 사용하지 않더라도 바로 가져오기 위한 값입니다.
이미 비정규화를 진행했고, 데이터의 중복이 발생한 상황에서 price를 추가하지 않을 이유가 없다고 판단했습니다.
users.reliability
판매자의 신뢰도가 경매와 같이 관리되는 것은 자연스럽기 때문에 의미가 혼동되지는 않을 것 같습니다.
이제 판매자의 신뢰도를 최신화하는 정책에 따라 갈리게 될 것입니다.
만약 reliability를 auction에 추가한 상태에서 정책이 신뢰도를 경매가 낙찰될 때 마다 최신화해준다면 users 테이블의 레코드 뿐만 아니라 auction 까지 계속 수정해야 하므로 전체적인 애플리케이션 성능이 떨어질 것입니다.
팀 프로젝트를 진행할 때 비즈니스 로직 메트릭을 측정해 가장 사용량이 적었던 매주 화요일 새벽 5시 30분에 업데이트 하기로 결정했었습니다.
그렇다면 일주일에 한 번만 변경하면 되기 때문에 충분히 처리가 가능하다고 판단했습니다.
테이블 구조 변경
요구사항의 정렬 조건이었던 price, reliability를 각각 last_bid_price, seller_reliability 컬럼으로 추가했습니다.
쿼리 실행
CREATE INDEX idx_auction_price_reliability ON auction(is_deleted, last_bid_price DESC, seller_reliability DESC);하나의 테이블에 모든 정렬 조건이 추가되었으므로, 우선적으로 인덱스를 추가했습니다.
SELECT id
FROM auction
WHERE is_deleted = 0
ORDER BY last_bid_price desc, seller_reliability desc
LIMIT 200000, 11; 
+----+-------------+---------+------------+------+-------------------------------+-------------------------------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------------+-------------------------------+---------+-------+--------+----------+-------------+
| 1 | SIMPLE | auction | NULL | ref | idx_auction_price_reliability | idx_auction_price_reliability | 2 | const | 449365 | 100.00 | Using index |
+----+-------------+---------+------------+------+-------------------------------+-------------------------------+---------+-------+--------+----------+-------------+
다음과 같이 성능을 개선했습니다.
- 시간 : 6.06초 -> 0.4초
- 코스트 비용 : 500000 -> 46000
결론
- 서로 다른 테이블 컬럼으로 정렬하는 쿼리의 경우 비정규화를 활용해 개선할 수 있습니다.
