배달의 민족, 주문 총금액 어떻게 구현하지?

제목 : 배달의 민족, 주문 총금액 어떻게 구현하지?

원본 위치 : DB 전문가 네트워크 디비안 (https://cafe.naver.com/dbian)

작성자 : SweetBoss

작성일 : 2019.11.05

 

1. 목적 및 환경

이 글은 누구라도 데이터를 활용 할 수 있게 하는데 목적이 있습니다. 기본적으로 SELECT, UPDATE등의 SQL은 사용할 줄 아는 사람들을 대상으로 합니다. 이 글을 통해 SQL 실력도 향상할 수 있습니다. 이번 글은 특히 MySQL의 성능을 다양하게 측정해보는 과정에 목적이 있습니다.

 

사용 DBMS : MySQL 5.7 Windows

사용 Tool : MySQL Workbench, MySQL Command Line Client

활용 주제 : MySQL의 성능, 나의 총 주문금액

선행 주제 : 차례대로입니다.

1.     https://cafe.naver.com/dbian/2331

2.     https://cafe.naver.com/dbian/2335

3.     https://cafe.naver.com/dbian/2337

4.     https://cafe.naver.com/dbian/2338

5.     https://cafe.naver.com/dbian/2341

6.     https://cafe.naver.com/dbian/2391

 

 

2. ‘배달의 민족’ 나의 총 주문금액

오늘은 데이터 활용과는 약간 어긋난 주제가 될지도 모르겠습니다. 최근에 이슈가 된 ‘배달의 민족’의 ‘나의 총 주문금액’을 구하는 과정을 구현해보려고 합니다. 어쩌면 뻔한 이야기가 될 수도 있습니다. 아직은 DB를 잘 모르거나, MySQL을 많이 접해보지 않은 분들을 대상으로 합니다. 디비에 능통하신 ‘디비안’ 회원님들은 스킵 부탁드립니다. 먼저, 저는 ‘배달의 민족’과 아무 관련이 없습니다.!

먼저, SQL 테스트를 위해 여기서는 사용자 테이블과 주문 테이블, 일자(C_BaseDate) 테이블을 신규로 생성합니다. 아래와 같습니다.

주문 테이블은 기존에 과정에서 만들었던 M_Shop을 참조합니다. 다만, FK를 설정하지는 않을 예정입니다.

 

(1) 일자 테이블 생성 및 데이터 생성

아래 SQL로 일자 테이블을 만들고 데이터를 생성합니다. 기존의 ‘데이터활용6’ 과정에서 이미 만들었던 테이블입니다. 해당 과정을 진행하신 분은 추가로 수행하실 필요 없습니다.

# 일자 테이블 생성 및 데이터 생성
# C_BaseDate
CREATE TABLE C_BaseDate
(
BaseDT DATE NOT NULL
,BaseDTSeq INT NOT NULL
, PRIMARY KEY(BaseDT)
, UNIQUE KEY(BaseDTSeq)
) ENGINE = InnoDB;
    
INSERT INTO C_BaseDate (BaseDT, BaseDTSeq)
SELECT '2015-01-01', 1 FROM DUAL;

-- 결과 건수가 0건이 될때까지 반복 실행한다.(2015-01-01부터 2030-12-31까지 데이터 생성)
INSERT INTO C_BaseDate (BaseDT, BaseDTSeq)
SELECT 	DATE_ADD(T1.BaseDT, interval T2.MAX_Seq day) BaseDT
	,T1.BaseDTSeq + T2.MAX_Seq
FROM 	C_BaseDate T1
	CROSS JOIN (SELECT MAX(BaseDT) MAX_DT, MAX(BaseDTSeq) MAX_Seq FROM C_BaseDate) T2
WHERE	DATE_ADD(T1.BaseDT, interval T2.MAX_Seq day) <= '2030-12-31';

 

(2) 사용자 테이블 생성 및 데이터 생성

M_User 테이블을 만들고 데이터를 생성합니다. 총 10,000명의 사용자를 생성합니다. M_Shop 테이블에서 10000건의 데이터를 가져와서 사용자 데이터를 생성합니다. 만 건의 데이터가 필요해서 M_Shop을 임의로 사용했습니다.

# M_User 테이블과 데이터 생성
CREATE TABLE M_User
(   UserNo INT NOT NULL
    ,UserName VARCHAR(100) CHARACTER SET UTF8MB4 NULL
,PRIMARY KEY(UserNo)
) ENGINE = InnoDB;

INSERT INTO M_User(UserNo, UserName)
SELECT @RNO := @RNO + 1 UserNo
,CONCAT('U_',@RNO) UserNamr
FROM  (SELECT T1.ShopNo FROM M_Shop T1 ORDER BY ShopNo LIMIT 10000 ) A
	 CROSS JOIN (SELECT @RNO := 0 FROM DUAL) B
    ;

SQL의 8번과 11번 라인의 @RNO를 이용해서 순번을 부여하는 패턴은 유용하니 익혀두도록 합니다. 하지만 해당 패턴은 절대 남발하지 않습니다. 임시성이나 일회성 SQL에만 적절한 패턴입니다. 실제 서비스되는 SQL에서는 사용하지 않기를 가이드합니다. (이유는 성능상, 불리할 수 있기 때문입니다.)

 

 

(3) 상장매핑 임시 테이블 생성

M_Shop의 ShopNo는 1부터 시작하지 않습니다. 주문 데이터를 생성할 때, 실제 M_Shop의 ShopNo를 물리기 위해서, ShopNo에 1부터 시퀀스한 숫자를 매핑해서 2,000개 데이터를 만듭니다. 실전에서 ID를 완전히 새로 부여할 때 자주 사용하는 패턴입니다.

# 상장매핑 임시 테이블 생성
CREATE TEMPORARY TABLE TMP_MAPSHOP AS
SELECT @RNO := @RNO + 1 MapNo 
,A.ShopNo 
FROM (SELECT T1.ShopNo FROM M_Shop T1 ORDER BY ShopNo LIMIT 2000 ) A
	CROSS JOIN (SELECT @RNO := 0 FROM DUAL) B
    ;
    
ALTER TABLE TMP_MAPSHOP ADD INDEX X_TMP_MAPSHOP(MapNo,ShopNo);

성능에 문제 없도록 인덱스도 잡아줍니다.

 

 

(4) 주문 테이블 생성 및 데이터 생성

아래 SQL로 T_Order를 생성하고, 가상의 주문 데이터를 만들어 냅니다.

# 주문 테이블 생성 및 데이터 생성
# ROOT 권한으로 다음 SQL을 실행해 버퍼를 늘려 놓는 것이 좋습니다.
SET  GLOBAL innodb_buffer_pool_size = 402653184;

CREATE TABLE T_Order
(
	OrderNo BIGINT AUTO_INCREMENT NOT NULL
	,OrderDTM DATETIME NOT NULL
	,OrderAMT DECIMAL(20,5) DEFAULT 0 NOT NULL
	,DeliveryAMT DECIMAL(20,5) DEFAULT 0 NOT NULL
	,PayAMT DECIMAL(20,5) DEFAULT 0 NOT NULL
	,UserNo INT NOT NULL
	,ShopNo INT NOT NULL
	,PRIMARY KEY(OrderNO)
) ENGINE = InnoDB;

INSERT INTO T_Order (OrderDTM,OrderAMT,DeliveryAMT,PayAMT,UserNo,ShopNo)
SELECT 	T1.OrderDTM
,10000 OrderAMT
        ,1000 DeliveryAMT
        ,11000 PayAMT
        ,T1.UserNo
        ,(SELECT A.ShopNo FROM TMP_MAPSHOP A WHERE A.MapNo = T1.MapNo) ShopNo
FROM	(
          #일반 사용자
	SELECT DATE_ADD(T1.BaseDT, interval MOD(T2.UserNo, 240) minute) OrderDTM
	       ,T2.UserNo
                 ,MOD(T1.BaseDTSeq + T2.UserNo ,2000) + 1 MapNo
	FROM   C_BaseDate T1
	       CROSS JOIN ( SELECT * FROM M_User A WHERE UserNo >= 30) T2
WHERE  T1.BaseDT <= STR_TO_DATE('2019-10-31','%Y-%m-%d')
	AND    WEEKDAY(T1.BaseDT) = 6 #일요일만 배달.
	UNION ALL
	#우수(?) 사용자 매일 배달.
	SELECT DATE_ADD(T1.BaseDT, interval MOD(T2.UserNo, 240) minute) OrderDTM
	       ,T2.UserNo
                 ,MOD(T1.BaseDTSeq + T2.UserNo ,2000) + 1 MapNo
	FROM   C_BaseDate T1
	       CROSS JOIN ( SELECT * FROM M_User A WHERE UserNo < 30) T2
WHERE T1.BaseDT <= STR_TO_DATE('2019-10-31','%Y-%m-%d') 
        ) T1
ORDER BY T1.OrderDTM, T1.UserNo
;

 

주문을 만들 때, UserNo가 30 미만인 경우는 매일 주문을 한 우수(?) 사용자처럼 주문을 만들어 줍니다. 30 이상은 일주일에 한번 주문한 것으로 만들어줍니다. 총 2,563,877 건의 주문 데이터가 만들어 집니다.

 

 

3. 총 주문 금액 조회하기 – 테이블 전체 스캔

총 주문 금액을 조회해보도록 합니다.

SHOW STATUS를 실행하면 innodb에서 읽어들인 row 건수를 확인할 수 있습니다. 아래와 같이 100번 사용자의 총 주문금액을 조회합니다.

# 100번 사용자 주문금액 확인
SHOW STATUS LIKE 'innodb_rows_read';
SELECT SUM(T1.OrderAMT) , COUNT(*) FROM T_Order T1 WHERE T1.UserNo = 100;
SHOW STATUS LIKE 'innodb_rows_read';

제 환경에서 ‘Innodb rows’는 SQL 실행 전에 251 실행 후에 2,564,128이 나옵니다.
총 2,563,877(=2,564,128-251) 건을 읽은 것을 알 수 있습니다. 2,563,877은 테이블의 전체 건수입니다. 100번 사용자의 주문총금액을 구하기 위해 테이블의 전체 데이터를 읽은 것입니다.

 

이번에는 주문이 많은 10번 사용자의 총 주문금액을 조회합니다.

# 10번 사용자 주문금액 확인
EXPLAIN
SELECT SUM(T1.OrderAMT) FROM T_Order T1 WHERE T1.UserNo = 10;

 

아래와 같은 실행계획이 나옵니다. Type이 ALL이고 key값이 비어 있으면 테이블 전체를 읽은 것입니다. 당연히 T_Order에는 OrderNo로 구성된 PK 밖에 없으니, 원하는 데이터를 찾으려면 테이블 전체를 뒤질 수 밖에 없습니다.

 

4. 총 주문 금액 조회하기 – UserNo 인덱스 사용

UserNo 컬럼에 인덱스를 만들어 3번에서 수행한 테스트를 동일하게 해보도록 하겠습니다. 인덱스에 대해서는 추가적인 공부가 필요합니다. 친절한SQL튜닝책을 추천드립니다.

아래와 같이 UserNo 로 구성된 인덱스를 만듭니다.

# UserNo 인덱스 생성
CREATE INDEX X_T_Order_1 ON T_Order(UserNo);

 

 

인덱스가 잘 만들어졌는지는 아래 SQL로 확인합니다.

# 인덱스 확인
show index from T_Order;

 

인덱스가 만들어 졌다면 다시 innodb_rows_read를 확인하면서, 100번 사용자와 10번 사용자의 총 주문금액을 조회해봅니다.

100번 사용자의 총 주문금액을 조회합니다.

 

# 100번 사용자 주문금액 확인
SHOW STATUS LIKE 'innodb_rows_read';
SELECT SUM(T1.OrderAMT) , COUNT(*) FROM T_Order T1 WHERE T1.UserNo = 100;
SHOW STATUS LIKE 'innodb_rows_read';

 

100번 사용자 SQL 실행전과 후과 rows_read를 비교해 보면 251건의 데이터만 읽은 것을 알 수 있습니다.

이번에는 10번 사용자를 테스트해봅니다.

# 10번 사용자 주문금액 확인
SHOW STATUS LIKE 'innodb_rows_read';
SELECT SUM(T1.OrderAMT) , COUNT(*) FROM T_Order T1 WHERE T1.UserNo = 10;
SHOW STATUS LIKE 'innodb_rows_read';

10번 사용자는 1765건의 rows_read가 발생했습니다. 실제 해당 사용자가 주문한 건수만큼만 접근했습니다. UserNo에 인덱스를 만들어서 성능을 향상했습니다.

 

 

5. 총 주문 금액 조회하기 – UserNo + OrderAMT 인덱스 사용

이번에는 UserNo와 OrderAMT에 인덱스를 만들어서 테스트해보도록 합니다.

인덱스를 만들기 전에, EXPLAIN FORMAT=JSON을 사용해, 10번 사용자의 실행계획을 JSON형태로 조회해봅니다. EXPLAIN보다 자세한 정보들이 있습니다.

# 10번 사용자 주문금액 확인 - UserNo인덱스 – 실행계획 얻기
EXPLAIN FORMAT = JSON
SELECT SUM(T1.OrderAMT) , COUNT(*) FROM T_Order T1 WHERE T1.UserNo = 10;

 

 

아래와 같은 JSON형태의 실행계획을 얻을 수 있습니다.

# 10번 사용자 주문금액 확인 - UserNo인덱스 – 실행계획 결과
{
   "query_block": {
     "select_id": 1,
     "cost_info": {
       "query_cost": "2118.00"
     },
     "table": {
       "table_name": "T1",
       "access_type": "ref",
       "possible_keys": [
         "X_T_Order_1"
       ],
       "key": "X_T_Order_1",
       "used_key_parts": [
         "UserNo"
       ],
       "key_length": "4",
       "ref": [
         "const"
       ],
       "rows_examined_per_scan": 1765,
       "rows_produced_per_join": 1765,
       "filtered": "100.00",
       "cost_info": {
         "read_cost": "1765.00",
         "eval_cost": "353.00",
         "prefix_cost": "2118.00",
         "data_read_per_join": "96K"
       },
       "used_columns": [
         "OrderAMT",
         "UserNo"
       ]
     }
   }
 }

아래와 같이 UserNo+OrderAMT 복합 인덱스를 만든 후에, 동일한 10번 사용자의 총 주문 금액의 실행계획을 확인해 봅니다.

# 10번 사용자 주문금액 확인 – UserNo+OrderAMT인덱스 – 실행계획 얻기
CREATE INDEX X_T_Order_2 ON T_Order(UserNo, OrderAmt);

EXPLAIN FORMAT = JSON
SELECT SUM(T1.OrderAMT) , COUNT(*) FROM T_Order T1 WHERE T1.UserNo = 10;

{
   "query_block": {
     "select_id": 1,
     "cost_info": {
       "query_cost": "358.73"
     },
     "table": {
       "table_name": "T1",
       "access_type": "ref",
       "possible_keys": [
         "X_T_Order_1",
         "X_T_Order_2"
       ],
       "key": "X_T_Order_2",
       "used_key_parts": [
         "UserNo"
       ],
       "key_length": "4",
       "ref": [
         "const"
       ],
       "rows_examined_per_scan": 1765,
       "rows_produced_per_join": 1765,
       "filtered": "100.00",
       "using_index": true,
       "cost_info": {
         "read_cost": "5.73",
         "eval_cost": "353.00",
         "prefix_cost": "358.73",
         "data_read_per_join": "96K"
       },
       "used_columns": [
         "OrderAMT",
         "UserNo"
       ]
     }
   }
 }

이전 SQL과 비교해보면, cost가 현저히 낮아졌습니다. 인덱스에 OrderAMT가 추가되어서 인덱스만으로 모든 문제를 해결할 수 있기 때문이다. 사실,,, 이러한 부분이 실행계획에 명확히 표시되면 좋은데 MySQL은 그렇지가 않습니다. 그러므로 오라클을 사용하던 분들이 MySQL을 쓰면 답답해서 속이 터집니다. 그래도 현실에 적응해야 합니다. 그냥 무료로 사용할 수 있음에 감사해야겠습니다.

 

실제, 실행시간도 측정해보도록 합니다. 아래 SQL들을 실행해봅니다.

# 2번 사용자, 1번 사용자 주문금액 확인(각각 다른 인덱스 사용)
SET PROFILING = ON;

SELECT SQL_NO_CACHE SUM(T1.OrderAMT),COUNT(*) FROM T_Order T1 FORCE INDEX(X_T_Order_2)
WHERE T1.UserNo = 2;

SELECT SQL_NO_CACHE SUM(T1.OrderAMT),COUNT(*) FROM T_Order T1 FORCE INDEX(X_T_Order_1)
WHERE T1.UserNo = 1;

일부러, 기존의 버퍼캐시의 데이터를 읽지 않도록 다른 UserNo를 조회합니다. 첫 번쨰 SQL은 UserNo+OrderAMT 인덱스를 사용하게 했고, 두 번째 SQL은 UserNo 인덱스를 사용하도록 힌트를 주었습니다.

SHOW PROFILES로 실행 성능을 확인해봅니다.

SHOW PROFILES;

저의 환경에서는 UserNo+OrderAMT 인덱스를 사용한 경우에는 0.008초로 측정되었고, UserNo 인덱스를 사용한 경우에는 0.2초로 측정되었습니다. 실제 시간으로도 UserNo+OrderAMT가 월등한걸 알 수 있습니다. (너무 당연한걸 길게 설명하고 있습니다.ㅜ.ㅜ)

추가로.. 개인적으로 OrderAMT와 같은 금액, 단가와 같은 컬럼이 인덱스에 들어가는 것을 별로 추천하지는 않습니다. 꼭 어쩔 수 없을 때만 사용합니다.

 

 

 

6. 총 주문 금액 – 집계 테이블 전략

UserNo+OrderAMT 인덱스를 만들었어도, 10번 사용자의 총 주문 금액을 조회하려면 1,765건의 데이터를 읽어야 하는 것은 피할 수 없습니다. (물론, 인덱스의 리프 블록만 읽으므로 매우 빠릅니다.) 한 명이 사용하는 거라면 큰 이슈가 없겠지만, 배달의 민족처럼 매우 많은 사용자가 한 순간에 조회를 요청하면 이 또한 심한 부하가 발생될 수 있습니다. 이를 해결하기 위해 집계(배치) 테이블 전략을 시도해보겠습니다. 사실 집계 테이블을 하면 성능이 월등히 좋을거야란 추측으로 시도했으나 현실은 그렇지 않네요!!!!!. 어쨌든, SQL에 대한 공부로 생각해주시면 감사하겠습니다.

아래와 같은 집계 테이블을 설계합니다.

 

테이블을 보면, 누적주문금액 컬럼을 가지고 있습니다. 사용자의 주문년월까지의 누적주문금액을 저장합니다. 예를 들어 2018년1월이라면 최초부터 2018년1월까지의 주문금액 합계가, 2019년2월이라면 최초부터 2019년2월까지의 주문금액을 누적해서 저장합니다. 마지막 월의 해당 값만 읽어와서 총주문금액을 처리하는 전략입니다.

아래 스크립트로 테이블을 생성합니다.

# 사용자월별주문 집계 테이블 생성
CREATE TABLE S_UserOrderYM
(
	OrderYM VARCHAR(6) CHARACTER SET UTF8MB4 NOT NULL
	,UserNo INT NOT NULL
	,OrderAMT DECIMAL(20,5) DEFAULT 0 NOT NULL
	,DeliveryAMT DECIMAL(20,5) DEFAULT 0 NOT NULL
	,PayAMT DECIMAL(20,5) DEFAULT 0 NOT NULL
	,SUMOrderAMT DECIMAL(20,5) DEFAULT 0 NOT NULL
    ,PRIMARY KEY(OrderYM,UserNo)
) ENGINE = InnoDB;

 

아래 SQL로 SUMOrderAMT를 제외한 2019년9월까지의 주문을 S_UserOrderAMT에 입력합니다.

# 사용자월별주문 초기 데이터 생성
INSERT INTO S_UserOrderYM
(OrderYM,UserNo,OrderAMT,DeliveryAMT,PayAMT)
SELECT DATE_FORMAT(OrderDTM,'%Y%m') 
,UserNo
        ,SUM(T1.OrderAMT)
        ,SUM(T1.DeliveryAMT)
        ,SUM(T1.PayAMT)
FROM T_Order T1
WHERE OrderDTM < '2019-10-01'
GROUP BY DATE_FORMAT(OrderDTM,'%Y%m'), UserNo;

 

아직, 10월달 데이터와 SUMOrderAMT는 입력하지 않았습니다. 입력된 9월까지의 SUMOrderAMT는 아래 업데이트 SQL로 처리합니다. MySQL 5.7은 분석함수 기능이 없습니다. 분석함수 기능이 있다면, 분석 함수 한 줄로 깔끔하게 총 주문금액을 처리할 수 있는데 안타깝습니다. 어쩔 수 없이 아래와 같이 업데이트 합니다. 업데이트 전에 루트 계정으로 버퍼풀 사이즈도 늘려주고 추가로 인덱스도 만들어줍니다. 업데이트 처리시에 성능 때문입니다.

 

# 사용자월별주문 누적주문금액 업데이트
SET  GLOBAL innodb_buffer_pool_size = 402653184;

CREATE INDEX X_S_UserOrderYM ON S_UserOrderYM(UserNo,OrderYM,OrderAMT);

UPDATE  S_UserOrderYM T1
		right outer join (
	  SELECT A.UserNo, A.OrderYM , SUM(B.OrderAMT) SUMOrderAMT
            FROM	S_UserOrderYM A
		INNER JOIN S_UserOrderYM B
                     ON (A.UserNO = B.UserNo AND A.OrderYM >= B.OrderYM)
			GROUP BY A.UserNo, A.OrderYM
                     ) T2
                     ON (T1.UserNo = T2.UserNo AND T1.OrderYM = T2.OrderYM)
SET T1.SUMOrderAMT = T2.SUMOrderAMT;

업데이트를 보시면 S_UserOrderYM과 인라인-뷰(7~12)의 결과를 아우터-조인(6번 라인) 하고 있습니다. 업데이트 처리가, 인라인-뷰=>S_UserOrderYM 순으로 해야 성능이 좋을거 같습니다. 그냥 INNER JOIN을 하면 이처럼 안되기 때문에 일부러 RIGHT OUTER JOIN을 사용했습니다. 아주,… 힌트가 안되니 어렵습니다. (오라클이 그립습니다.)

 

이제 주문총금액을 조회해봅니다. 그런데, 집계 테이블에는 9월 주문까지만 있습니다. 그러므로 10월 주문금액 정보는 T_Order에서 읽어야 합니다. T_Order의 효율적인 접근을 위해 아래와 같이 인덱스를 추가합니다.(인덱스가 계속 늘어납니다. 좋지 않습니다. 나중에 불필요한 인덱스는 제거해야 합니다.)

# 집계 테이블을 이용한 사용자 총주문금액 조회 – T_Order 인덱스 추가
CREATE INDEX X_T_Order_3 ON T_Order(UserNo,OrderDTM);

아래는 실제 조회 SQL입니다.

집계 테이블을 이용한 사용자 총주문금액 조회
SHOW STATUS LIKE 'innodb_rows_read';

SELECT	MAX(T1.SUMOrderAMT) + IFNULL(SUM(T2.OrderAMT),0)
FROM	(
	SELECT	T1.SUMOrderAMT
		,DATE_ADD(STR_TO_DATE(CONCAT(T1.OrderYM,'01'),'%Y%m%d'),interval 1 month) FromDate
	FROM	S_UserOrderYM T1
	WHERE	T1.UserNo = 10
	ORDER BY T1.OrderYM DESC
	LIMIT 1
        ) T1
        LEFT OUTER JOIN T_Order T2
	ON (T2.UserNo = 10 AND T2.OrderDTM >= T1.FromDate);
                
SHOW STATUS LIKE 'innodb_rows_read';

위 SQL에서 주의 깊게 볼 만한 부분은 T1과 T2를 조인 처리하는 부분입니다. 보시고 무릎을 탁 치신다면 이미 SQL은 저만큼 하시는 겁니다.

Rows_read를 확인해보면, 이전에는 1,765건을 읽었지만 이번에는 33건만 읽어서 결과를 처리했습니다. 집계 테이블이라는 불편한 작업이 있었지만, 확실한 성능 개선 효과가 있었다고 단정할 수 있습니다. JSON 실행계획도 확인해 보시기 바랍니다. 전체 실행계획은 훨씬 길고 복잡해졌지만 query cost가 44로 확실하게 좋아졌습니다.

이제 실제 실행시간도 측정해 봅니다. 실제 실행시간을 측정하기 전에는 당연히 집계 전략이 유리할거라 생각했습니다.!!!!! 그러나…

실제 실행시간을 측정해 봅니다. 각각 다른 사용자를 사용합니다.

# 집계 테이블을 이용한 사용자 총주문금액 조회 – 시간 측정
SET PROFILING = ON;

SELECT	SQL_NO_CACHE
	MAX(T1.SUMOrderAMT) + IFNULL(SUM(T2.OrderAMT),0)
FROM	(
	SELECT	T1.SUMOrderAMT
		,DATE_ADD(STR_TO_DATE(CONCAT(T1.OrderYM,'01'),'%Y%m%d'),interval 1 month) FromDate
	FROM	S_UserOrderYM T1
	WHERE	T1.UserNo = 23
	ORDER BY T1.OrderYM DESC
	LIMIT 1
        ) T1
        LEFT OUTER JOIN T_Order T2
			ON (T2.UserNo = 23
				AND T2.OrderDTM >= T1.FromDate);
                
SELECT SQL_NO_CACHE SUM(T1.OrderAMT),COUNT(*) FROM T_Order T1 FORCE INDEX(X_T_Order_2)
WHERE T1.UserNo = 29;

SHOW PROFILES;

 

실제 실행시간을 확인해 보면, 사용자의 T_Order를 그냥 모두 읽은 것이 더 좋습니다. SHOW PROFILES에서 얻은 각 쿼리의 ID를 이용해 실행타임을 자세하게 분석해 봅니다.

# 쿼리ID를 이용해 자세한 단계별 실행시간 확인
#집계 테이블 사용+실적 테이블 사용
SHOW PROFILE FOR QUERY 135;

#실적 테이블만 사용
SHOW PROFILE FOR QUERY 136;

 

비교 결과는 아래와 같습니다.

비교해보면, ‘집계 테이블과 조인’은 두 번의 executing과 sending data가 발생합니다. 아마도 조인을 위해 스토리지 엔진을 나누어서 다녀오기 때문인 듯 합니다.

지금의 상황으로는 집계 테이블이 유리한 상황은 아닌 거 같습니다. (하지만 데이터가 더 많고, 많은 사용자가 몰린다면 집계 전략이 훨씬 유리할거라 여전히 추측은 됩니다.) 추가로 해보고 싶은 전략도 많고, 데이터 분포 변경에 따른 테스트도 해보고 싶지만 여기까지 쓰고 줄이도록 하겠습니다.

감사합니다.

 

이처럼, 데이터를 분석하는 과정을 공부해보고 싶으신 분은 아래의 '평생 필요한 데이터 분석'의 교육 과정을 추천합니다. 교육을 통해 SQL을 배운다면, 위 내용을 좀 더 보강할 수도 있고, 자신만의 스타일로 분석을 할 수 있습니다. SQL을 완전히 자신의 것으로 만들 수 있는 교육이니 관심 가져보시기 바랍니다. 감사합니다.~!

 

https://cafe.naver.com/dbian/5259

「평생 필요한 데이터 분석(MySQL 과정)」 수강자 모집