최근 주문 목록 SQL 성능 개선하기

 

StartUP Tuning(MySQL): 최근 주문 목록 SQL 성능 개선하기

 
DBMS: MySQL 8.0.35
 
스타벅스 앱을 키면 Quick Order에는 최근 주문 내역을 보여줍니다.
사용자에게 앱을 키자마자 최근주문 내역을 보여줘 조금 더 편리하게 앱을 사용할 수 있도록 한 것입니다.
다른 많은 시스템이나 플랫폼도 비슷합니다. 접속을 하면, 마지막 접속기록이나 최근 구매 내역을 보여줍니다.
이와 같은 기능을 처리하는 SQL의 성능 개선 과정을 간단히 살펴보겠습니다.
 
실습을 위해 Ord(주문), OrdDet(주문상세), 상품(Item) 테이블을 사용합니다.
해당 테이블은 StartUP SQL의 무료 스크립트를 통해 자신의 환경에도 생성할 수 있습니다.
참고: https://sweetquant.tistory.com/503

2-8~2-10. 데이터베이스 구성하기

 
Ord와 OrdDet, Item 테이블의 ERD를 살펴보면 다음과 같습니다.

 
위 테이블들을 이용해 특정 회원의 최근 주문 상품 세 개를 가져오려고 합니다. 아래와 같이 SQL을 구현할 수 있습니다.

SELECT  T2.ItemId
        ,T3.ItemNm
        ,T1.OrdDtm
        ,T1.OrdNo
FROM    startdb.Ord T1
        INNER JOIN startdb.OrdDet T2 ON (T2.OrdNo = T1.OrdNo)
        INNER JOIN startdb.Item T3 ON (T3.ItemId = T2.ItemId)
WHERE   T1.MemberId = 'M0001'
ORDER BY T1.OrdDtm DESC
LIMIT 3;

 - Time sec: 0.018636
   * Rows read: 1263
   * Buffer pages: 3797

 
SQL의 마지막에 수행 시간과 IO(Buffer pages) 수치를 첨부했습니다. 0.018초로 그렇게 느리다고 할 수는 없는 SQL입니다. 하지만 데이터 3건만 보여주는 상황에서 3,797이란 IO 수치는 약간 높은 편입니다. 수십만 명의 회원이 동시에 사용한다고 생각해보면, 약간씩 더 느려질 소지가 있습니다.
위 SQL이 현재 시스템에서 매우 많이 사용되는 핵심 SQL이라면 당연히 추가 성능 개선이 필요합니다. SQL의 성능 개선을 위해 가장 먼저 할일은 실행계획을 확인하는 것입니다. 위 SQL의 실제 실행계획은 아래와 같습니다.

1. -> Limit: 3 row(s)  (actual time=162..162 rows=3 loops=1)
2.     -> Nested loop inner join  (actual time=162..162 rows=3 loops=1)
3.         -> Nested loop inner join (actual time=162..162 rows=3 loops=1)
4.             -> Sort: startdb.t1.OrdDtm DESC   (actual time=162..162 rows=3 loops=1)
5.                 -> Index lookup on T1 using Ord_FK01 (MemberId='M0001')  (actual time=18.5..161 rows=1257 loops=1)
6.             -> Filter: (startdb.t2.ItemId is not null)  (actual time=0.027..0.0288 rows=1 loops=3)
7.                 -> Index lookup on T2 using PRIMARY (OrdNo=startdb.t1.OrdNo)  (actual time=0.0261..0.0277 rows=1 loops=3)
7.         -> Single-row index lookup on T3 using PRIMARY (ItemId=startdb.t2.ItemId)  (actual time=0.0104..0.0105 rows=1 loops=3)

 
실행계획을 확인해 보면 세 테이블이 모두 NL(Nested Loop) 방식으로 조인되고 있습니다. 성능 개선을 위해서는 이러한 조인 방식을 이해하는 것이 매우 중요합니다. 이 부분을 여기서 다루자면 길어지므로 나중에 기회가 되면 별도로 이야기하도록 하겠습니다.
실행계획이 익숙하지 않은 분들은, 실행계획에서 문제가 되는 부분을 찾기가 쉽지 않을 것입니다.
실행계획의 마지막 단계(1번 라인)의 actual time을 보면 162..162(시작시간ms..종료시간ms)입니다. 162ms에 이 SQL이 완료되었다는 것을 알 수 있습니다.
이제, 마지막 단계를 제외하고 각 단계별로 actual time을 살펴봅니다. 가장 많은 시간을 차지하는 단계를 한 번 찾아보는거죠. 5번 단계가 다른 단계들에 비해 많은 시간을 사용하고 있는 것을 알 수 있습니다. 5번 단계의 작업 내용은 아래와 같습니다.
-> Index lookup on T1 using Ord_FK01 (MemberId='M0001')  (actual time=18.5..161 rows=1257 loops=1)
 
T1(Ord) 테이블을 Ord_FK01 인덱스를 사용해 Lookup 처리하고 있습니다. Ord_FK01은 MemberId로 구성된 인덱스입니다. 실행계획에서 actual 항목의 rows를 보면 1257입니다. Ord_FK01을 이용해 추출한 데이터가 1257건이라는 뜻입니다.
우리가 필요한건 최근 조회 세 건인데, 1257건의 주문 데이터에 접근한 것입니다. 바로 이 부분에 비효율이 있다라고 할 수 있습니다.
 
우리는 성능을 개선하기 위해 아래와 같은 인덱스를 만들어 볼 수 있습니다.
CREATE INDEX Ord_X01 ON startdb.Ord(MemberId, OrdDtm);
 
인덱스를 만든 후에 다시 같은 SQL을 실행해보고 실제 실행계획을 추출해보면 아래와 같습니다.

1. -> Limit: 3 row(s)  (actual time=0.227..0.271 rows=3 loops=1)
2.    -> Nested loop inner join   (actual time=0.226..0.269 rows=3 loops=1)
3.        -> Nested loop inner join  (actual time=0.208..0.242 rows=3 loops=1)
4.            -> Covering index lookup on T1 using Ord_X01 (MemberId='M0001') (reverse)  (actual time=0.178..0.182 rows=3 loops=1)
5.            -> Filter: (startdb.t2.ItemId is not null)  (actual time=0.0155..0.0186 rows=1 loops=3)
6.                -> Index lookup on T2 using PRIMARY (OrdNo=startdb.t1.OrdNo)  (actual time=0.0149..0.0178 rows=1 loops=3)
7.        -> Single-row index lookup on T3 using PRIMARY (ItemId=startdb.t2.ItemId)  (actual time=0.00827..0.00837 rows=1 loops=3)
        
 - Time sec: 0.005036
   * Rows read: 9
   * Buffer pages: 17
   * Disk reads: 0

 
총 실행시간이 0.005초로 인덱스를 만들기 전인 0.018초에 비해 1/3 이상으로 감소했습니다. IO 수치인 Buffer pages도 3,797에서 17로 획기적으로 감소한 것을 알 수 있습니다.
실행계획의 4번 단계를 살펴보면 다음과 같습니다.
-> Covering index lookup on T1 using Ord_X01 (MemberId='M0001') (reverse)  (actual time=0.178..0.182 rows=3 loops=1)
 
새로 만든 인덱스를 Covering Index lookp으로 처리하고 있습니다. 또한 (reverse)라고 표시되어 있는데, 이는 인덱스 리프 데이터를 역순으로 접근하는 방식을 뜻합니다. 인덱스의 리프 데이터는 인덱스 키 값으로 정렬되어 있으므로 이처럼 역순으로 접근해 세 건만 읽어들이면, 최근 주문 세건을 추출해낼 수 있습니다. 4번 단계의 rows를 보면 3으로 줄어든 것을 알 수 있습니다.
 
최근 데이터 조회에 적절한 인덱스를 만들때는 WHERE 절의 조건을 인덱스의 앞쪽 컬럼으로 배치하고 ORDER BY에 있는 컬럼을 인덱스의 뒤쪽 컬럼에 배치합니다. 그러므로 위 SQL에서 WHERE 조건인 MemberId, ORDER BY 컬럼인 OrdDtm을 조합해 Ord_X01(MemberId, OrdDtm)으로 인덱스를 구성한 것입니다. (SQL에 따라 이와 같은 인덱스 구성이 불가능하고나 복잡한 경우도 많습니다.)
 
이처럼 인덱스를 만들어주는 것만으로 최근 주문을 조회하는 SQL 성능이 획기적으로 개선되었습니다.
그런데 중요한건, "인덱스를 만들자"가 아닙니다. 실행계획을 먼저 살펴보고, 성능에 문제가 되는 지점을 찾아내는 것입니다. 그리고 그에 맞게 개선안을 적용해야 합니다. 또한 지금 살펴본 SQL보다 훨씬 복잡해서 단순 인덱스 전략으로 성능 개선을 만들어 내지 못하는 경우도 많습니다.
그러므로 우리는 성능 개선을 위해 단순히 인덱스를 만들자가 아닌, 조인의 작동원리, 인덱스의 구조와 작동원리를 잘 알고 있어야 합니다.
 
오늘 준비한 내용은 여기까지입니다. 감사합니다.

---

 
[StartUP Tuning]

• SQL 입문을 넘어선 MySQL을 활용한 SQL 튜닝 입문 교육
• 2024년 5월 주말 오프라인 교육 과정 모집중: https://cafe.naver.com/dbian/7717

StartUP Tuning - MySQL/MariaDB를 활용한 SQL 튜닝 입문

 
[StartUP SQL]

• 데이터 분석가, 개발자, 기획자를 위한 SQL 입문 교육
• 교육용 슬라이드 전체 무료 오픈: https://sweetquant.tistory.com/497

StartUP SQL 소개 및 목차