1만 시간의 법칙이라고 들어보셨나요? 1만 시간을 투자한다면, 전문가가 될 수 있다는 개념입니다.
2024년을 맞이해, 새로운 기술로 1만 시간을 채워볼수 있을까란 생각이 들어 계산을 좀 해봤습니다.
SQL로요! (여기서는 MySQL을 사용했습니다.) 먼저 아래와 같이 날짜 테이블을 생성합니다.
(C_BAS_DT는 StartUP Tuning 강의에서 튜닝을 위해 소개되는 테이블입니다.)
CREATE TABLE C_BAS_DT
(
BAS_DT DATE NOT NULL
,BAS_YMD VARCHAR(8) NOT NULL
,BAS_YM VARCHAR(6) NOT NULL
,BAS_YW VARCHAR(6) NOT NULL
,BAS_DT_SEQ INT NOT NULL
,PRIMARY KEY(BAS_DT)
);
그다음에, MySQL의 RECURSIVE문을 이용해 기본 데이터를 저장합니다. 아래 SQL을 실행하면 2050년 1월 1일까지의 날짜 데이터가 C_BAS_DT에 저장됩니다.
SET @@cte_max_recursion_depth = 30000;
INSERT INTO C_BAS_DT (BAS_DT, BAS_YMD, BAS_YM, BAS_YW, BAS_DT_SEQ)
WITH RECURSIVE WR01 AS (
SELECT STR_TO_DATE('20000101','%Y%m%d') AS BAS_DT
UNION ALL
SELECT DATE_ADD(BAS_DT, INTERVAL 1 day) BAS_DT
FROM WR01
WHERE BAS_DT < STR_TO_DATE('20500101','%Y%m%d')
)
SELECT BAS_DT BAS_DT
,DATE_FORMAT(BAS_DT, '%Y%m%d') AS BAS_YMD
,DATE_FORMAT(BAS_DT, '%Y%m') AS BAS_YM
,CONCAT(YEAR(BAS_DT), LPAD(WEEK(BAS_DT), 2, '0')) AS BAS_YW
,ROW_NUMBER() OVER (ORDER BY BAS_DT) AS BAS_DT_SEQ
FROM WR01
ORDER BY BAS_DT ASC;
1만 시간의 법칙을 계산하기 위해서는 더 많은 날짜 데이터가 필요합니다. 리커시브를 계속 사용하기 보다는 셀프조인을 사용해 추가 날짜 데이터를 생성합니다.
아래의 SQL을 여러번 실행해 2300년 1월 1일까지의 데이터를 생성합니다. 세 번 정도 실행하면 됩니다.(앞에서 생성된 C_BAS_DT를 다시 사용(셀프조인)해 더 많은 연속된 날짜 데이터를 추가로 생성하는 방법입니다.)
-- INSERT 건수가 0건 될때까지 계속 실행(세번 실행하면 됨)
INSERT INTO C_BAS_DT (BAS_DT, BAS_YMD, BAS_YM, BAS_YW, BAS_DT_SEQ)
SELECT T3.BAS_DT BAS_DT
,DATE_FORMAT(T3.BAS_DT, '%Y%m%d') AS BAS_YMD
,DATE_FORMAT(T3.BAS_DT, '%Y%m') AS BAS_YM
,CONCAT(YEAR(T3.BAS_DT), LPAD(WEEK(BAS_DT), 2, '0')) AS BAS_YW
,ROW_NUMBER() OVER (ORDER BY T3.BAS_DT) + T3.MAX_DT_SEQ AS BAS_DT_SEQ
FROM (
SELECT DATE_ADD(T2.MAX_DT, INTERVAL T1.BAS_DT_SEQ day) BAS_DT
,T2.MAX_DT_SEQ
FROM C_BAS_DT T1
CROSS JOIN (SELECT MAX(X.BAS_DT) MAX_DT , MAX(X.BAS_DT_SEQ) MAX_DT_SEQ FROM C_BAS_DT X) T2
WHERE DATE_ADD(T2.MAX_DT, INTERVAL T1.BAS_DT_SEQ day) <= '23000101'
) T3
;
이제 위에서 만들어진 C_BAS_DT 테이블을 사용해, 평일에만 하루에 30분씩 투자해서 1만 시간을 채우려면 얼마나 걸릴지를, SQL로 구해봅니다. 아래와 같이 SQL을 구현해 볼 수 있습니다.
-- 오늘부터 하루에 30분씩 투자해서 평일(월,화,수,목,금)만 했을때 만시간을 채우려면 몇일이 끝일까?
-- SQL로 구해보자.
SELECT MAX(T2.BAS_DT) 만시간완료일자
,SUM(CASE WHEN T2.HOUR_SUM <= 10000 THEN 1 END) 일수
FROM (
SELECT T1.*
,DAYNAME(T1.BAS_DT) DNM
,0.5 HOUR
,SUM(0.5) OVER(ORDER BY T1.BAS_DT ASC) HOUR_SUM
FROM C_BAS_DT T1
WHERE T1.BAS_DT >= DATE_FORMAT(NOW(),'%Y%m%d')
AND DAYNAME(T1.BAS_DT) IN ('Monday','Tuesday','Wednesday','Thursday','Friday')
ORDER BY T1.BAS_DT ASC
) T2
WHERE T2.HOUR_SUM <= 10000;
만시간완료일자 |일수
---------------+-----+
2100-08-27 |20000|
제가 죽기전에는 하루에 30분씩으로는 1만 시간을 채울수가 없네요!
그렇다면, 지금 자신이 하고 있는 직업으로 1만 시간을 채우려면 얼마나걸리까요.
하루에 보통 8시간 일을 하니, 위 SQL에서 0.5 HOUR만 8 로 변경해주면 됩니다.
SELECT MAX(T2.BAS_DT) 만시간완료일자
,SUM(CASE WHEN T2.HOUR_SUM <= 10000 THEN 1 END) 일수
FROM (
SELECT T1.*
,DAYNAME(T1.BAS_DT) DNM
,8 HOUR
,SUM(8) OVER(ORDER BY T1.BAS_DT ASC) HOUR_SUM
FROM C_BAS_DT T1
WHERE T1.BAS_DT >= DATE_FORMAT(NOW(),'%Y%m%d')
AND DAYNAME(T1.BAS_DT) IN ('Monday','Tuesday','Wednesday','Thursday','Friday')
ORDER BY T1.BAS_DT ASC
) T2
WHERE T2.HOUR_SUM <= 10000;
만시간완료일자 |일수 |
----------+----+
2028-10-13|1250|
자신의 직업을 꾸준히 1250일 정도 한다면 만시간을 채울 수 있네요. 우리가 하고있는 일을 오늘부터 열심히 2028년 10월까지, 하루에 8시간씩만 쏟는다면 1만 시간을 채울 수 있습니다.
이처럼, 1만 시간을 채우기란 매우 어려운 일입니다. 실제 1만 시간을 채우지 않고도 1만 시간을 채운 효과를 보기 위해서는 다른 사람들의 도움을 받고, 다른 사람과 유연하게 협동을 해야 하지 않나란 생각이 듭니다.
2024년에서는 다양한 교육 과정과 협동을 통해 1만 시간을 더욱 단축해보실 수 있기 바랍니다.!
SQL(Structured Query Language)은 선언적 언어입니다. 그러나 SQL 튜닝을 잘하고 싶다면, SQL이 DBMS 내부적으로는 절차형 언어로 처리된다는 생각의 전환이 필요합니다. (SQL을 절차형 언어처럼 작성하라는 뜻이 절대 아닙니다.!!)
선언적 언어는 "어떻게(How)" 가 아닌 "무엇을(What)" 할 것인지, 또는 "무엇이" 필요한지를 정의하는 언어입니다. 다시말해, SQL을 사용할때는 필요한 데이터 집합이 무엇인지 정의만 해주면 됩니다. SQL과 같은 선언적 언어는 데이터를 어떠한 순서, 또는 어떠한 절차로 가져올 것인지를 정의할 필요가 없습니다.
자바, 파이썬, C와 같은 프로그래밍 언어는 절차형 언어입니다. 절차형 언어는 "무엇(What)"과 함께 "어떻게(How)"에 모두 집중해야 합니다. 절차형 언어는 필요한 결과(무엇)를 얻기 위한 절차(어떻게)까지 모두 구현해야 하기 때문입니다. 이와 같은 차이로, 절차형 언어만 다루던 분들이 초반에 SQL을 다루기 어려워하기도 합니다. 하지만, 절차형 언어를 제대로 잘하는 개발자라면, 분명히 "SQL 튜너"의 소질이 있을거라 생각합니다.
SQL은 표면적으로는 선언적 언어입니다. 하지만, DBMS 내부적으로 데이터가 처리되는 과정은 절차적입니다. 예를 들어, 뉴욕에 사는 고객의 최근 한달 주문 건수가 필요하다면, 아래와 같이 SQL로 필요한 데이터 집합을 정의해주면 됩니다.
SELECT COUNT(*) 주문건수
FROM 고객 A INNER JOIN 주문 B ON (A.고객ID = B.고객ID)
WHERE A.도시 = 'NY'
AND B.주문 >= ADD_MONTHS(NOW(),-1M);
위와 같은 SQL을 실행하면, DBMS는 내부적인 절차를 만들어 SQL에 맞는 데이터 집합을 만들어 줍니다. 이때, DBMS는 사용자가 요청한 SQL의 결과를 만들기 위해, 다양한 내부적인 처리 절차를 고려할 수 있습니다. 주문 테이블에서 최근 한달치를 읽은 후에, 고객에 접근해 결합(JOIN)할 수도 있으며, 반대로 도시가 NY인 고객을 먼저 찾은 후에 주문 테이블과 결합 할 수도 있습니다. SQL은 선언적인 언어이기 때문에, 내부적인 절차는 DBMS가 마음대로 정하고 처리하게 됩니다. 어떠한 절차로 처리하든 사용자가 요구한 데이터만 정확히 만들어주면 됩니다. 그러나, DBMS가 마음대로 처리 절차를 정한다기 보다는, SQL의 최종 결과를 얻는데 가장 비용이 적은(성능이 좋은) 방법을 고려해 절차를 정합니다. 그리고 이러한 처리 절차를 만드는 모듈이 그 유명한 옵티마이져(Optimizer)입니다.
이 글에서 SQL 성능 관련해 우리가 주목해야 할 것은, 선언적 언어인 SQL이 DBMS 내부에서는 절차적으로 처리된다는 것입니다. 그리고 이 절차에 따라 SQL의 성능 차이가 천차만별이 될 수 있습니다. SQL의 처리 절차는 실행계획(EXPLAIN)을 통해 확인할 수 있습니다. 그러므로, SQL의 성능에 이상이 있다면 가장 먼저 실행계획을 확인해야 합니다. 좀더 덧붙여 말하자면, 실행계획을 통해 SQL의 내부적인 처리 절차를 확인해야 하는 것입니다. 실행계획을 통해, 비효율적인 절차(처리 순서나 처리 방법)를 발견해 제거할 수 있다면, SQL의 성능은 저절로 좋아지게 될 것입니다.
SQL 튜닝은, 이러한 내부적인 절차를 관찰하고 비효율적인 절차를 찾아 제거하거나 변경하는 것입니다. 다시말해, SQL은 표면적으로는 선언적 언어이지만, SQL 튜닝 관점에서는 절차형 사고 방식으로 내부적인 처리 절차를 확인해야 합니다. 이러한 이해를 바탕으로 SQL 튜닝에 접근하면, SQL 튜닝을 좀 더 잘 할 수 있습니다.
끝으로, SQL을 절차적으로 작성하라는 뜻이 절대 아님을 다시 한 번 강조드립니다. 우리가 집합 개념으로 작성한 SQL이, DBMS 내부에서 어떤 절차로 처리되는지를 알 필요가 있다는 뜻입니다.
StartUP Tuning(PostgreSQL, MySQL을 활용한 SQL 튜닝 입문) 강의에서는,
Booster QUIZ를 통해, SQL 튜닝을 각자 직접 실습해볼 수 있도록 하고 있습니다.
이와 같은 실습과 고민의 시간은 SQL 튜닝 능력 향상에 많은 도움이 됩니다.
오늘은 Booster Quiz 중에 NL 조인 관련 연습문제를 하나 풀어보도록 하겠습니다.
Booster Quiz: 아래 SQL을 NL 조인으로 처리한다고 했을때, 어느 방향으로 NL 처리하는 것이 좋은지 파악하기 위해 아래 질문에 차례대로 답해보세요.
SELECT t1.itm_id
,COUNT(*) CNT
FROM m_itm t1
INNER JOIN t_itm_evl_big t2 ON (t2.itm_id = t1.itm_id)
WHERE t1.itm_tp = 'CLOTHES'
AND t1.unt_prc <= 1000
AND t2.evl_pt = 5
GROUP BY t1.itm_id;
SQL을 보면 m_itm(아이템/품목)과 t_itm_evl_big(아이템평가), 두 개의 테이블이 사용되고 있습니다.
위 SQL에 대한 실행계획과 인덱스를 확인하지 않고 아래 질문에 차례대로 답해봅니다.
1. t1 > t2 방향으로 NL 조인한다면, t2에 방문(loop) 횟수는?
아래와 같이 원래 SQL에서 t1 부분만 추출해 카운트하면 알 수 있습니다.(답: 10번)
SELECT COUNT(*)
FROM m_itm t1
WHERE t1.itm_tp = 'CLOTHES'
AND t1.unt_prc <= 1000;
count|
-----+
10|
2. t2 > t1 방향으로 NL 조인한다면, t1에 방문(loop) 회수는?
아래와 같이 원래 SQL에서 t2 부분만 추출해 카운트하면 알 수 있습니다.(답:25,000번)
SELECT COUNT(*)
FROM t_itm_evl_big t2
WHERE t2.evl_pt = 5;
count|
-----+
25000|
1번과 2번 질문을 통해, 2번보다는 1번 방법이 더 유리할 수 있겠구나 판단할 수 있습니다.
t1>t2 방향으로 NL 조인하면 t2에는 10번만 접근하면 되고, t2>t1 방향으로 NL 조인하면 t1에는 무려 25,000번 반복 접근해야 합니다. 반복적인 접근 횟수를 줄이는 것은 SQL 성능에 도움이 됩니다. (물론 적절한 인덱스 구성이 선행되어야 합니다.)
이어서 다음 질문에도 답해봅니다.
3. 힌트를 사용해 t1 > t2 방향으로 NL 조인되도록 처리하고, 실제 실행계획을 확인
아래와 같이 힌트를 적용해 실행계획을 확인해보면, t2의 loops가 10인 것을 알 수 있습니다.
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
/*+ Leading((t1 t2)) NestLoop(t1 t2) */
SELECT t1.itm_id
,COUNT(*) CNT
FROM m_itm t1
INNER JOIN t_itm_evl_big t2 ON (t2.itm_id = t1.itm_id)
WHERE t1.itm_tp = 'CLOTHES'
AND t1.unt_prc <= 1000
AND t2.evl_pt = 5
GROUP BY t1.itm_id;
GroupAggregate (actual time=9.148..10.760 rows=2 loops=1)
Group Key: t1.itm_id
Buffers: shared hit=296
-> Nested Loop (actual time=0.035..10.411 rows=3500 loops=1)
Buffers: shared hit=296
-> Index Scan using m_itm_pk on m_itm t1 (actual time=0.012..0.035 rows=10 loops=1)
Filter: ((unt_prc <= '1000'::numeric) AND ((itm_tp)::text = 'CLOTHES'::text))
Rows Removed by Filter: 90
Buffers: shared hit=2
-> Index Scan using t_itm_evl_big_pk on t_itm_evl_big t2 (actual time=0.807..1.004 rows=350 loops=10)
Index Cond: ((itm_id)::text = (t1.itm_id)::text)
Filter: (evl_pt = '5'::numeric)
Rows Removed by Filter: 1550
Buffers: shared hit=294
Planning Time: 0.179 ms
Execution Time: 10.792 ms
4. 힌트를 사용해 t2 > t1 방향으로 NL 조인되도록 처리하고, 실제 실행계획을 확인
아래와 같이 힌트를 적용해 실행계획을 확인해보면, t1의 실제 loops는 16번만 발생했습니다. 하지만, NL 조인의 후행 집합쪽을 보면, t1의 데이터를 Memoize 처리했고, Memoiz에 25,000 번의 반복 접근이 발생했습니다.
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
/*+ Leading((t2 t1)) NestLoop(t1 t2) */
SELECT t1.itm_id
,COUNT(*) CNT
FROM m_itm t1
INNER JOIN t_itm_evl_big t2 ON (t2.itm_id = t1.itm_id)
WHERE t1.itm_tp = 'CLOTHES'
AND t1.unt_prc <= 1000
AND t2.evl_pt = 5
GROUP BY t1.itm_id;
HashAggregate (actual time=30.695..30.697 rows=2 loops=1)
Group Key: t1.itm_id
Batches: 1 Memory Usage: 24kB
Buffers: shared hit=1694
-> Nested Loop (actual time=0.028..30.145 rows=3500 loops=1)
Buffers: shared hit=1694
-> Seq Scan on t_itm_evl_big t2 (actual time=0.010..22.777 rows=25000 loops=1)
Filter: (evl_pt = '5'::numeric)
Rows Removed by Filter: 147500
Buffers: shared hit=1662
-> Memoize (actual time=0.000..0.000 rows=0 loops=25000)
Cache Key: t2.itm_id
Cache Mode: logical
Hits: 24984 Misses: 16 Evictions: 0 Overflows: 0 Memory Usage: 2kB
Buffers: shared hit=32
-> Index Scan using m_itm_pk on m_itm t1 (actual time=0.004..0.004 rows=0 loops=16)
Index Cond: ((itm_id)::text = (t2.itm_id)::text)
Filter: ((unt_prc <= '1000'::numeric) AND ((itm_tp)::text = 'CLOTHES'::text))
Rows Removed by Filter: 1
Buffers: shared hit=32
Planning Time: 0.186 ms
Execution Time: 30.736 ms
지금까지의 내용을 종합해, NL 조인 처리 방향에 따른 성능 차이를 아래와 같이 정리할 수 있습니다. 정리를 통해 현재 SQL에서는 어떤 방향으로 NL 조인이 적합한가를 판단할 수 있습니다.
t1 > t2
후행(t2)에 접근횟수: 10번
t1 > t2 IO(Buffers) : 296
t1 > t2 Execution time : 10.792 ms
t2 > t1
후행(t1)에 접근횟수: 실제 테이블에는 16번 접근, 하지만 memoize(cache) 처리된 데이터를 25,000번 접근.
IO(Buffers): 1694
Execution time : 30.736 ms
물론, 지금까지 살펴본 내용에 인덱스 구성을 추가하거나 변경한다면 완전히 다른 결과가 나올 수 있습니다.
여기서 중요한건, 이와 같은 순차적인 질문과 답으로 NL 조인의 방향에 따라 어떤 부분에서 성능 변화가 발생할 수 있는지 확인해보는 과정입니다.
안녕하세요. 오늘은 어떻게 보면 당연한 이야기이지만, SQL 튜닝 입문자라면 한번쯤은 생각해볼 이야기입니다.
SQL 튜닝(성능 개선)을 위해서는 SQL이 내부적으로 처리하는 데이터의 양을 정확히 파악할 필요가 있습니다. SQL의 성능은 SQL을 실행하면 나오는 최종 결과 건수 보다는 내부적으로 처리하는 데이터 양에 따라 좌우될 가능성이 높기 때문입니다.
아래의 SQL 을 살펴보도록 하겠습니다.
SELECT t1.ord_st ,COUNT(*) cnt
FROM t_ord_big t1
WHERE t1.ord_dt >= TO_DATE('20170301','YYYYMMDD')
AND t1.ord_dt < TO_DATE('20170401','YYYYMMDD')
GROUP BY t1.ord_st;
ord_st|cnt |
------+------+
WAIT | 36000|
COMP |334000|
위 SQL은 t_ord_big의 2017년 3월 데이터를 ord_st 별로 건수를 집계하고 있습니다. 실행해보면 표면적으로는 단 두 건의 데이터가 조회됩니다. 하지만, 내부적으로는 2017년 3월 전체 데이터를 접근하게 됩니다. 2017년 3월에 해당하는 데이터는 370,000 건이 있습니다. 이 수치는 위 SQL에서 GROUP BY를 제거하고 실행해보면 알 수 있습니다. (또는 위 SQL의 ord_st별 건수를 더해서도 알 수 있습니다.)
위 SQL의 튜닝을 위해서는 이 370,000건의 데이터를 어떻게 효율적으로 처리할 수 있을지를 고민해야 합니다. 370,000 건 모두를 인덱스 리프 블록에서 처리하게 할 수도 있으며, 테이블의 데이터 블록에서 처리하게 할 수도 있습니다. 이처럼 절대적으로 읽어야 할 데이터를 어떻게 효율적으로 처리할지 모색하는 것이 바로 SQL 튜닝이라 할 수 있습니다.
내부적으로 접근해야 하는 데이터 건수를 파악하는 것! 이것이 바로 SQL 튜닝의 시작점입니다. 어떤 방법으로 튜닝할까를 고민하기 전에, 절대적으로 읽어야 하는 건수가 몇 건인지 파악하는 노력을 해보기 바랍니다. 이러한 접근은 SQL 튜닝을 더 빠르고 효과적으로 수행하는 데 크게 기여할 것입니다
P.S. 위에서 실제 접근한 데이터 건수는 SQL의 실제 실행계획을 통해서도 알 수 있습니다. 아래는 PostgreSQL의 실행계획입니다. t_ord_big에 대한 인덱스를 이용해 370,000(actual 부분의 rows)건에 접근한 것을 알 수 있습니다.
HashAggregate (actual time=121.000..121.002 rows=2 loops=1)
Group Key: ord_st
Batches: 1 Memory Usage: 24kB
-> Index Scan using t_ord_big_x01 on t_ord_big t1 (actual time=0.017..57.820 rows=370000 loops=1)
Index Cond: ((ord_dt >= to_date('20170301'::text, 'YYYYMMDD'::text)) AND (ord_dt < to_date('20170401'::text, 'YYYYMMDD'::text)))
Planning Time: 0.121 ms
Execution Time: 121.035 ms
다양한 종류의 조인 연산을 보여주고 있으며, 각각의 조인 유형마다 결과 테이블이 어떻게 나타나는지 예를 들어 설명하고 있습니다.
또한, 각 조인 유형에 대한 ANSI SQL과 ORACLE SQL 구문도 제시되어 있습니다.
이미지의 내용을 기반으로 검토해보면:
Inner Join:
두 테이블(TAB1과 TAB2)에 모두 존재하는 키 값을 기준으로 조인되어 있습니다.
결과는 두 테이블에 모두 존재하는 행들만 포함합니다.
Left Outer Join:
TAB1의 모든 행과 TAB2의 일치하는 행들이 조인됩니다.
TAB1에는 있지만 TAB2에는 없는 행의 경우, TAB2 관련 컬럼은 NULL로 표시됩니다.
Right Outer Join:
TAB2의 모든 행과 TAB1의 일치하는 행들이 조인됩니다.
TAB2에는 있지만 TAB1에는 없는 행의 경우, TAB1 관련 컬럼은 NULL로 표시됩니다.
이러한 조인 방식들은 데이터베이스에서 데이터를 합치고, 관계를 분석할 때 중요한 연산입니다.
이 이미지는 SQL을 배우는 사람들에게 매우 유용한 자료로 사용될 수 있을 것 같습니다.
이미지에 표현된 내용은 정확하게 보이며, 조인 연산의 기본적인 개념을 잘 설명하고 있습니다.
이론적으로 정보는 정확해 보이지만, 실제 SQL 구문의 정확성이나 구문에 대한 실습은 해당 SQL 환경에서 직접 실행해보는 것이 가장 좋습니다. SQL의 세부적인 구현은 사용하는 데이터베이스 관리 시스템(DBMS)에 따라 다를 수 있기 때문에, 이론적인 검토 외에 실제 데이터를 사용한 검증도 중요합니다.
본 문서의 내용은 PostgreSQL 버젼에 따라 다를 수 있습니다. 본 문서에서 사용한 버젼은 아래와 같습니다.
AWS RDS PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
PostgreSQL의 Bitmap Index Scan
PostgreSQL의 실행계획에는 Index (Range) Scan이 아닌 Bitmap Index Scan이 종종 나타납니다.
설명에 앞서, 오라클의 Bitmap Index와 PostgreSQL의 Bitmap Index Scan를 구분할 필요가 있습니다. 이 두개는 완전히 다른 것입니다.
오라클의 Bitmap Index는 인덱스 자체를 Bitmap 형태로 구성한 것입니다.
반면에 PostgreSQL의 Bitmap Index Scan은 일반적인 B Tree 인덱스를 사용하는 처리 방식 중에 하나입니다. 다시 말해, B Tree 인덱스를 사용해 일반적인 Index Scan을 할 수도 있으며 Bitmap Index Scan을 할 수도 있습니다. Bitmap Index Scan은 인덱스를 통한 테이블 블록의 접근 횟수를 줄이기 위해 Bitmap 처리를 추가 적용한 것을 뜻합니다.
아래 SQL을 실행해 봅니다. 실행계획을 보면 x02(rno) 인덱스에 대해 Bitmap Index Scan이 작동했습니다.
총 실행 시간은 3.482 ms가 걸렸고, 3053의 IO(Buffers: shared ht)가 발생하고 있습니다.
EXPLAIN (ANALYZE,BUFFERS)
SELECT TO_CHAR(t1.ord_dt,'YYYYMM') ym,COUNT(*) cnt
FROM t_ord_big t1
WHERE t1.cus_id = 'CUS_0064'
AND t1.pay_tp = 'BANK'
AND t1.rno = 2
GROUP BY TO_CHAR(t1.ord_dt,'YYYYMM');
GroupAggregate (cost=10374.03..10374.25 rows=11 width=40) (actual time=3.446..3.448 rows=2 loops=1)
Group Key: (to_char(ord_dt, 'YYYYMM'::text))
Buffers: shared hit=3053
-> Sort (cost=10374.03..10374.06 rows=11 width=32) (actual time=3.440..3.442 rows=2 loops=1)
Sort Key: (to_char(ord_dt, 'YYYYMM'::text))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=3053
-> Bitmap Heap Scan on t_ord_big t1 (cost=35.24..10373.84 rows=11 width=32) (actual time=0.712..3.432 rows=2 loops=1)
Recheck Cond: (rno = 2)
Filter: (((cus_id)::text = 'CUS_0064'::text) AND ((pay_tp)::text = 'BANK'::text))
Rows Removed by Filter: 3045
Heap Blocks: exact=3047
Buffers: shared hit=3053
-> Bitmap Index Scan on t_ord_big_x02 (cost=0.00..35.23 rows=3040 width=0) (actual time=0.282..0.283 rows=3047 loops=1)
Index Cond: (rno = 2)
Buffers: shared hit=6
Planning Time: 0.092 ms
Execution Time: 3.482 ms
Bitmap Index Scan 역시 Index Scan과 마찬가지로 수직적탐색과 수평적탐색, 테이블접근으로 작업이 진행됩니다.
단, 테이블 블록 접근 단계에서, 접근 횟수를 줄일 수 있도록 아래와 같은 알고리즘이 추가되어 있습니다.
인덱스 수평탐색을 하면서 얻은 ctid(실제 레코드 포인터) 정보를 모아서 비트맵으로 변환
ctid 별로 건건이 테이블 블록에 한 번씩 접근하는 것이 아니라,
ctid 를 모아서 비트맵으로 변환한 후에 같은 블록의 데이터는 한 번에 가져오는 방법
이를 통해, 불필요한 중복 접근을 줄일 수 있습니다.
마치 오라클의 TABLE ACCESS BY INDEX ROWID BATCHED 와 유사하다 할 수 있습니다.
Bitmap Index Scan에는 Bitmap Heap Scan 단계가 따라옵니다. 이는 ctid 비트맵을 사용해 Heap 테이블에 접근하는 단계입니다.
이와 같은 Bitmap Index Scan은 인덱스를 사용해 많은 데이터 블록에 접근할 가능성이 높거나, 실제 테이블의 데이터 정렬 순서와 인덱스 리프의 정렬 순서 차이가 클때 유용한 것으로 알려져 있습니다.
PostgreSQL의 pg_stats를 통해 컬럼과 테이블간의 정렬 순서의 상관 관계 통계값을 알 수 있습니다. rno의 상관 계수는 0.012로 낮은 편입니다. 결과적으로 x02 인덱스(rno)의 정렬 순서와 실제 데이터의 정렬 순서 차이가 커서 Bitmap Index Scan을 사용한 것으로 추정할 수 있습니다.
그러나! 100% 완벽한 통계는 없습니다. 아니요. 통계는 100% 완벽할 수 있을거 같습니다. 하지만, 그에 따라 선택한 옵티마이져의 결정이 항상 최선은 아닐 수 있다고 말하는게 맞을거 같습니다.
아래와 같이 힌트를 사용해 일반적인 IndexScan으로 SQL을 실행해봅니다. IO가 3,053으로 기존의 Bitmap Index Scan을 했을때와 동일합니다. 이처럼 IO가 같다면 Bitmap 연산을 추가로 수행하는 Bitmap Inedx Scan이 좀 더 느릴 수 있습니다. 현재 결과에서도 IndexScan의 실행시간이 2.416ms로 Bitmap Index Scan보다 아주 조금 더 빠릅니다.
EXPLAIN (ANALYZE,BUFFERS)
/*+ IndexScan(t1) */
SELECT TO_CHAR(t1.ord_dt,'YYYYMM') ym,COUNT(*) cnt
FROM t_ord_big t1
WHERE t1.cus_id = 'CUS_0064'
AND t1.pay_tp = 'BANK'
AND t1.rno = 2
GROUP BY TO_CHAR(t1.ord_dt,'YYYYMM');
GroupAggregate (cost=12031.20..12031.42 rows=11 width=40) (actual time=2.387..2.389 rows=2 loops=1)
Group Key: (to_char(ord_dt, 'YYYYMM'::text))
Buffers: shared hit=3053
-> Sort (cost=12031.20..12031.23 rows=11 width=32) (actual time=2.381..2.382 rows=2 loops=1)
Sort Key: (to_char(ord_dt, 'YYYYMM'::text))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=3053
-> Index Scan using t_ord_big_x02 on t_ord_big t1 (cost=0.43..12031.01 rows=11 width=32) (actual time=0.043..2.371 rows=2 loops=1)
Index Cond: (rno = 2)
Filter: (((cus_id)::text = 'CUS_0064'::text) AND ((pay_tp)::text = 'BANK'::text))
Rows Removed by Filter: 3045
Buffers: shared hit=3053
Planning Time: 0.123 ms
Execution Time: 2.416 ms
3.482 ms나 2.416 ms나 인간이 거의 느낄 수 없는 시간 차이이기 때문에 의미가 있다고 할 수는 없습니다.
어쨋든, 옵티마이져가 선택한 방법이 최선이 아닐 수도 있다라고 한 번쯤 의심해볼 필요는 있습니다.
(절대, Bitmap Index Scan보다 Index Scan이 좋다는 일반론적 이야기가 아닙니다. 상황에 따라서는 Bitmap Index Scan이 더 유리할 수 있습니다. 또한 옵티마이져가 제대로 제 할일을 못한다라는 뜻의 이야기도 아닙니다.! 기본적으로 통계가 잘 구성되어 있다면 옵티마이져를 믿는 것이 우선입니다.)
이번에는 SQL을 살짝 바뿨봅니다. rno를 범위 조건으로 조금 더 많은 데이터를 조회해봅니다. 아래 결과를 살펴보면 Bitma Index Scan이 Index Scan보다 훨씬 적은 IO(Bitmap:3,192, IndexScan: 12,202)가 발생하고 실행시간도 근소하지만 더 빠릅니다.
EXPLAIN (ANALYZE,BUFFERS)
SELECT TO_CHAR(t1.ord_dt,'YYYYMM') ym,COUNT(*) cnt
FROM t_ord_big t1
WHERE t1.rno BETWEEN 2 AND 5
GROUP BY TO_CHAR(t1.ord_dt,'YYYYMM');
HashAggregate (cost=33470.11..33474.47 rows=349 width=40) (actual time=9.486..9.489 rows=12 loops=1)
Group Key: to_char(ord_dt, 'YYYYMM'::text)
Batches: 1 Memory Usage: 37kB
Buffers: shared hit=3192
-> Bitmap Heap Scan on t_ord_big t1 (cost=168.78..33409.45 rows=12131 width=32) (actual time=1.140..7.639 rows=12188 loops=1)
Recheck Cond: ((rno >= 2) AND (rno <= 5))
Heap Blocks: exact=3178
Buffers: shared hit=3192
-> Bitmap Index Scan on t_ord_big_x02 (cost=0.00..165.74 rows=12131 width=0) (actual time=0.741..0.741 rows=12188 loops=1)
Index Cond: ((rno >= 2) AND (rno <= 5))
Buffers: shared hit=14
Planning:
Buffers: shared hit=8
Planning Time: 0.104 ms
Execution Time: 9.546 ms
EXPLAIN (ANALYZE,BUFFERS)
/*+ IndexScan(t1) */
SELECT TO_CHAR(t1.ord_dt,'YYYYMM') ym,COUNT(*) cnt
FROM t_ord_big t1
WHERE t1.rno BETWEEN 2 AND 5
GROUP BY TO_CHAR(t1.ord_dt,'YYYYMM');
HashAggregate (cost=45715.02..45719.38 rows=349 width=40) (actual time=10.388..10.392 rows=12 loops=1)
Group Key: to_char(ord_dt, 'YYYYMM'::text)
Batches: 1 Memory Usage: 37kB
Buffers: shared hit=12202
-> Index Scan using t_ord_big_x02 on t_ord_big t1 (cost=0.43..45654.36 rows=12131 width=32) (actual time=0.015..8.493 rows=12188 loops=1)
Index Cond: ((rno >= 2) AND (rno <= 5))
Buffers: shared hit=12202
Planning:
Buffers: shared hit=8
Planning Time: 0.142 ms
Execution Time: 10.420 ms
이처럼 대량의 데이터에 접근해야 한다면, 바꿔말해 인덱스를 사용해 테이블의 데이터 블록에 많은 접근이 발생할 수 있다면, Bitmap 처리를 통해 불필요한 IO를 줄일 수 있습니다. 이것이 바로 Bitmap Index Scan입니다.
본 문서의 내용은 PostgreSQL 버젼에 따라 다를 수 있습니다. 본 문서에서 사용한 버젼은 아래와 같습니다.
AWS RDS PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
PostgreSQL의 EXPLAIN OPTION
PostgreSQL은 MySQL에 비해 다양하고 많은 정보를 실행계획에 표현해줍니다. PostgreSQL의 EXPLAIN 명령어 옵션들에 대해 간단히 살펴보겠습니다.
ANALYZE: 실제 쿼리를 수행하고 사용한 실행계획을 보여준다.
ANALYZE는 실제 SQL이 실행되므로, INESRT, UPDATE, DELETE에서는 사용해서는 안된다.
VERBOSE: 쿼리 계획에 추가적인 정보를 포함, 출력 컬럼과 각 노드의 OID
OID: Object Identifier, PG에서 객체를 구분하는 ID
SELECT * FROM pg_class WHERE oid = '테이블_OID';
BUFFERS: 쿼리 실행 중에 사용된 공유 버퍼, 로컬 버퍼, 그리고 쓰기 버퍼에 대한 정보
ANALYZE와 함께 사용해야 의미가 있다.
COSTS: 각 계획 단계의 추정 비용. 기본적으로 활성화되어 있음
COSTS OFF 로 예상 비용 표시 제외 가능
TIMING: 각 계획 단계의 시간 표시
ANALYZE 사용시 자동 활성화
FORMAT: 결과를 다른 형식으로 반환.
TEXT, XML, JSON, YAML입니다.
SQL 튜닝을 위해 가장 활발히 사용할 부분은 ANALYZE와 BUFFERS입니다. COSTS의 경우는 OFF도 가능합니다. 저는 교육 자료를 만들때 주로 아래와 같은 옵션으로 SQL의 실행계획을 추출합니다. (실제 튜닝 중이라면, 통계 구성을 예상할 수 있는 COSTS도 같이 보는게 좋겠죠.)
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT /*+ NestLoop(t1 t2) */
t1.* ,t2.ord_amt
,(SELECT x.rgn_nm FROM m_rgn x WHERE x.rgn_id = t1.rgn_id) rgn_nm
FROM m_cus t1 LEFT OUTER JOIN (
SELECT a.cus_id, sum(a.ord_amt) ord_amt
FROM t_ord a
GROUP BY a.cus_id) t2 on (t2.cus_id = t1.cus_id)
WHERE t1.rgn_id = 'A';
위와 같이 EXPLAIN에 ANALYZE와 BUFFERS 그리고 COSTS OFF를 하면 아래와 같은 실행계획을 확인할 수 있습니다.
Nested Loop Left Join (actual time=1.167..1.440 rows=30 loops=1)
Join Filter: ((t2.cus_id)::text = (t1.cus_id)::text)
Rows Removed by Join Filter: 1354
Buffers: shared hit=122
-> Seq Scan on m_cus t1 (actual time=0.012..0.024 rows=30 loops=1)
Filter: ((rgn_id)::text = 'A'::text)
Rows Removed by Filter: 60
Buffers: shared hit=2
-> Materialize (actual time=0.037..0.041 rows=46 loops=30)
Buffers: shared hit=60
-> Subquery Scan on t2 (actual time=1.106..1.136 rows=89 loops=1)
Buffers: shared hit=60
-> HashAggregate (actual time=1.106..1.127 rows=89 loops=1)
Group Key: a.cus_id
Batches: 1 Memory Usage: 80kB
Buffers: shared hit=60
-> Seq Scan on t_ord a (actual time=0.002..0.229 rows=3047 loops=1)
Buffers: shared hit=60
SubPlan 1
-> Index Scan using m_rgn_pk on m_rgn x (actual time=0.001..0.001 rows=1 loops=30)
Index Cond: ((rgn_id)::text = (t1.rgn_id)::text)
Buffers: shared hit=60
Planning Time: 0.193 ms
Execution Time: 1.483 ms
위 실행계획에서 Buffers 부분이 IO에 대한 항목입니다. shared hit는 모두 메모리에서 처리된 Logical IO입니다. 디스크에서 처리된 Physical IO가 발생하면, Buffers 항목에 read로 별도 표시를 해줍니다.
이와 같은 트리형태의 실행계획과 SQL이 실제 실행된 실행계획, 그리고 각 단계별 rows, loops의 수치와 IO 수치 제공은 SQL 튜닝을 더욱 효율적으로 할 수 있도록 해줍니다. PostgreSQL의 SQL 성능때문에 고민중이라면 이와 같은 실행계획에 익숙해주시기를 권장합니다.
위와 같은 ZB급 데이터의 대다수는 동영상, 게임, 오디오, 신문 기사와 같은 비정형적인 데이터입니다.
하지만, 이처럼 데이터가 늘어난다는 것은 관계형 데이터베이스로 관리하는 정형적인 형태의 데이터도 그만큼 늘어나고 있다고 볼 수 있습니다. 비정형 데이터를 효율적으로 분석하기 위해서는 정형 데이터로 가공이 필요하기 때문입니다.
뿐만아니라, 최근에 활발하게 추진한 마이데이터 관련 사업은 비정형 데이터뿐만 아니라 정형 데이터도 폭발적으로 증가시키고 있습니다. 2020년대 들어서는 이러한 데이터 증가 현상과 함께 클라우드 환경으로의 서버 이전 현상도 활발히 일어나고 있습니다.
‘(정형)데이터 증가’와 ‘클라우드 환경’ 이 두 가지 키워드의 공통 분모에는 여러 가지가 있지만, 그 중의 하나가 바로 비용(Money)입니다.
클라우드 서비스 업체는 다양한 방법으로 비용을 청구합니다. 기본적인 데이터베이스의 디스크 용량과 함께, 디스크에 발생한 IO에 따라서도 비용이 발생합니다. 그러므로 클라우드 환경에서 데이터베이스 비용을 줄이기 위해서는 디스크를 아껴 쓰고 디스크 사용량도 줄여야 합니다.
실제로 클라우드 환경으로 넘어갔던 기업들이 디스크 비용의 부담으로 데이터 조회를 “아껴서” 하라는 난애한 지침을 내리는 경우도 있습니다. 일반적인 개발자나 데이터 분석가 수준에서 비용까지 생각하면 데이터를 핸들링하기는 매우 어려운 일입니다.
상황과 지시가 어떻든 간에, 클라우드 환경의 데이터베이스 비용을 줄이기 위해 우리가 접근할 수 있는 전략 중 하나가 바로 파티셔닝입니다. 어떤 데이터를 조회하든 비용이 발생하지만, 대부분은 빅 테이블에 비용이 집중될 가능성이 높습니다. 그러므로 우리는 빅 테이블의 파티션을 통해 비용 절감의 기회를 만들어 볼 수 있습니다.
1,000 G(Giga)의 테이블이 있다고 가정해보죠. 분석을 위해 최근 데이터 한달만 읽고 싶지만, 해당 테이블에는 일자에 대한 인덱스가 없는 상황이라면 1,000 G 전체를 스캔해야 합니다. 다시 말해, 클라우드 환경에 1,000 G에 대한 IO 비용을 지불해야 합니다. 마찬가지로 일자에 대한 인덱스가 없는 상황에서 매월 과거 한달 데이터를 삭제한다고 가정해보죠. 일자에 대한 인덱스가 없으므로 한 달치데이터 삭제를 위해서도 1,000 G의 데이터에 접근이 발생합니다.
그렇다면 일자에 대한 인덱스를 만들어 보면 어떨까요? 이 경우는 비용(Money)에 도움이 될 수도 있고 안될 수도 있습니다. 읽어야 할 한달 치의 데이터가 매우 많다면 DBMS의 옵티마이져는 인덱스가 아닌 FULL SCAN을 선택할 가능성이 있습니다. DBMS의 옵티마이져는 SQL의 성능과 관련한 비용(Cost)에만 집중을 합니다. 클라우드에 지불할 비용(Money)은 전혀 신경 쓰지 않으니까요. 또한 데이터 분포에 따라서는 인덱스로 한달 데이터를 읽은 경우가 더 많은 비용이 발생할 수도 있습니다. 삭제 역시 마찬가지입니다. 인덱스를 사용할 수도 있지만 FULL SCAN을 택할 수도 있습니다. 추가로 인덱스가 있는 상태에서 테이블 삭제가 발생한다면, 인덱스 삭제에 대한 IO 비용도 같이 발생하지 않을까 생각이 듭니다. 정리하면, 많은 데이터를 처리하기 위한 인덱스는 클라우드 비용(Money) 절약에 직접적으로 도움이 될지는 상황에 따라서 달라질 수 있습니다.
1,000 G 테이블을 월별로 파티션하면 어떨까요? 최근 한달의 데이터를 조회한다면 조회 월에 해당하는 파티션 하나만 전체로 읽으면 됩니다. 파티션 하나가 10 G라면, 10 G에 대한 IO 비용(Money)만 지불하면 됩니다. 삭제는 어떨까요? 파티션은 파티션별로 TRUNCATE나 DROP이 가능합니다. 그러므로 삭제에 대한 IO 비용 없이 데이터 삭제가 가능 할 수도 있습니다. (DBMS나 클라우드 환경에 따라 다를 수는 있습니다.)
이처럼 빅 테이블에 파티션을 지정하는 것만으로 우리는 비용을 아낄 수 있는 기회를 창출할 수 있습니다. 필요하다면, 일반 테이블의 파티션 전환을 설득하기 위해 지난 글에서 설명한 것처럼 용량 산정 작업을 진행해 볼 수 있습니다.
파티션은 클라우드 환경에서의 비용뿐만 아니라 SQL의 성능에도 도움을 줄 수 있으며, 백업 및 복구 시간의 단축에도 이득을 줍니다. 파티션은 빅 테이블의 데이터 이관에도 도움이 됩니다.
하지만 파티션이 이와 같이 장점만 있는 것은 아닙니다. 무엇보다 일반 테이블을 파티션으로 전환하는 것은 매우 어려운 일입니다. “일필휘지(一筆揮之)”하듯, 한 번에 휙 만들어 적용할 수 있는 작업이 절대 아닙니다.
파티션을 적용하면, 이미 서비스되고 있는 파티션 테이블을 사용하는 모든 SQL들의 성능에 영향을 주게 됩니다. 성능이 좋아질 수도 있지만, 반대로 성능이 나빠질 수도 있습니다. 하나의 핵심 SQL이 성능이 나빠지면, 전체 시스템을 위험에 빠뜨릴 수도 있습니다. 파티션 적용은 이러한 위험 상황을 만들 가능성도 가지고 있습니다.
제가 경험한 사이트 역시 파티션을 적용한 후 SQL을 그에 맞게 변경하지 않아 30분만에 끝나던 배치 작업이 하루 종일 걸려도 끝나지 않은 경우가 실제 있었습니다. 또한 파티션 테이블이 추가되면 그만큼 관리할 포인트가 늘어납니다. DBMS를 관리하는 입장에서는 반가운 작업이 아닐 수 있습니다. 그러므로 이미 운영 중이며 데이터가 많이 쌓인 상황에서 파티션은 정말 어려운 작업이며, 섣불리 진행하면 안 되는 작업입니다.
파티션 키와 파티션 방법을 결정하기 위해서는 비즈니스를 이해하고 기존에 개발된 SQL들도 검토해야 합니다. 또한 파티션 구성 방법의 특징을 이해하고 있어야 합니다. 다시 말해, 파티션을 정하려면 비즈니스 담당자, 개발 담당자, 데이터베이스 담당자 모두가 모여 머리를 맞대고 고민해야 합니다.
또한, 앞서 말한 것처럼 위험성에 대해서 서로가 충분히 인지하고 파티션 후의 상황에 대응할 수 있도록 서로 노력해야 합니다.
클라우드 환경이면서 테이블의 데이터가 지속적으로 늘어날 가능성이 높다면 서둘러 파티션을 고민해보기 바랍니다.
하지만, 이미 데이터가 많이 쌓여 있고 대부분의 서비스가 성숙한 상황이라면 파티션에 대해서는 좀 더 보수적으로 접근하기 바랍니다. 비용을 줄이기 위한 노력이 서비스 장애라는 더 큰 어려움을 가져올 수도 있습니다.
본 문서에서 준비한 내용은 여기까지입니다. 이 문서에서는 파티션 키를 정하는 법이나 파티션의 종류에 대해서는 설명하지 않습니다. 또한 이에 따른 SQL의 성능도 설명하지 않습니다. 간단히 설명할 수 있는 부분이 아니기 때문입니다. 파티션과 성능 관련해서는 조시형님의 “친절한 SQL 튜닝”과 “오라클 성능 고도화”를 읽어 보시는 것을 추천합니다.
기업의 데이터는 계속 쌓여가고 늘어납니다. 그것도 쉴 새 없이요. 그리고 최근에는 더 폭발적으로 늘어나고 있습니다. 데이터가 문제없이 쌓이도록 하기 위해서는 필요한 디스크 공간을 미리 산정해야 합니다. 디스크 공간 확보를 위해서는 비용(Money)이 필요하기 때문입니다. 오늘은 빅 테이블의 용량을 산정하는 법에 대해 살펴보겠습니다. 어렵지 않으며, 모두가 알만한 이야기입니다. 리마인드 개념으로 한 번 읽어 보시기 바랍니다.
1,000G(Giga)의 판매 테이블이 있다고 가정하도록 하겠습니다. 해당테이블에는 2021년 1월부터 2023년 12월까지 2년 간의 데이터가 존재합니다. 테이블 용량이 너무 커서 해당 테이블이 내년에는 얼만큼 늘어날지 걱정이 된다고 합니다.
이 상황에서 가장 간단한 용량 산정 방법은, 2년 간의 데이터가 1,000G의 용량을 차지하므로 2024년에는 1,000G의 절반인 500G가 필요하다고 산정하는 것입니다.
여기서는 이처럼 간단한 방법이 아닌, 월별로 데이터를 살펴보고 그에 따른 증가양을 분석해 2024년의 테이블 용량을 산정하려 합니다. 현대의 데이터는 날이 갈수록 쌓이는 양이 늘어나고 있습니다. 데이터의 특징(업무의 특징)에 따라 다르겠지만, 많은 생활들이 디지털화되면서 그에 맞추어 디지털로 입력되는 데이터의 양 역시 늘어나고 있습니다.
그러므로 1000G의 절반을 2024년의 디스크 용량으로 산정하기 보다는 월별로 변경되는 데이터의 양을 조사해 디스크 크기를 산정하는 것이 보다 합리적입니다.
우선 판매 테이블의 년월별 데이터 건수를 조사합니다. 이를 통해 아래와 같은 표를 만들어 낼 수 있습니다.
테이블의 전체 용량은 1,000G이고 테이블의 총 건수는 60.38 억 건입니다. 1월달의 판매 데이터가 1.32 억 건이므로 아래와 같은 계산을 통해 1월달이 차지하는 디스크 용량을 추정해 볼 수 있습니다.
ROUND(1.32 / 60.38 * 1000,2) = 21.86 G
위 식을 각 월별로 모두 적용해 월별로 용량을 추정해볼 수 있습니다. 위의 표에서 노란색 부분이 월별로 용량을 추정한 부분입니다. 각 월별 용량을 모두 합해 보면 1,000G가 되는 것을 알 수 있습니다.
이제, 위 데이터를 근거로 2024년 12월까지의 디스크 용량을 산정하면 됩니다. 이때 다양한 방법을 고려할 수 있습니다.
여기서는 “최근 6개월 간의 용량 증감의 평균 값”을 사용해 용량을 산정할 계획입니다.
(이 기준은 각자가 생각하는 적정한 수준으로 정하면 되겠지요.)
“최근 6개월 간의 용량 증감의 평균 값”을 구하기 위해서는, 먼저 각 월별로 전월 대비 증감한 용량을 구합니다.
그리고 최근 6개월의 증가에 대해서만 평균을 구하면 됩니다. 아래 표의 녹색 부분에 해당합니다.
최근 6개월 간의 용량 증감 평균이 1.02G가 나옵니다.
이제 이 1.02G란 값을 적용해 2024년의 디스크 공간을 산정하면 됩니다. 2024년 1월 용량은 2023년 12월 용량인 56.64에 1.02를 더한, 57.66이 됩니다. 이 계산을 2024년 각 월에 적용해 2024년 디스크 공간을 산정할 수 있습니다.
판매 테이블의 디스크 용량 산정에 대한 최종 결과는 아래 차트와 함께 “최근 6개 월 간의 판매 테이블의 데이터 증가양을 고려했을 때, 2024년 판매 데이터를 위해서는 최소 759G의 디스크 확보가 필요합니다.”라고 마무리할 수 있습니다.
MySQL에는 Index For GroupBy라는 강력한 기능이 있습니다. 다른 DBMS에는 없는 훌륭한 기능입니다. 테스트를 통해 다른 DBMS에 비해 얼마나 좋은 성능을 내는지 살펴보겠습니다.
1. GROUP BY COUNT - ORALE 19C SE
먼저 DBMS의 가장 큰 형님 오라클부터 살펴보겠습니다. 아래와 같이 단순히 GROUP BY와 COUNT를 하는 SQL이 있습니다. 이에 맞게 인덱스도 생성합니다. SQL을 실행해 성능을 측정해보면 17,044의 IO가 발생했습니다.
-- CREATE INDEX T_ORD_BIG_TEST_X ON T_ORD_BIG(ORD_YMD);
SELECT T1.ORD_YMD, COUNT(*)
FROM T_ORD_BIG T1
WHERE T1.ORD_YMD > ' ' -- 인덱스를 강제로 사용하게 하기 위해 조건 사용
GROUP BY T1.ORD_YMD
ORDER BY 1 DESC;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 |00:00:03.81 | 17044 |
| 1 | SORT GROUP BY | | 1 | 100 |00:00:03.81 | 17044 |
|* 2 | INDEX FAST FULL SCAN| T_ORD_BIG_TEST_X | 1 | 6094K|00:00:01.54 | 17044 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."ORD_YMD">' ')
2. GROUP BY COUNT - PostgreSQL 16.1
이번에는 PostgreSQL에서 작업을 해봅니다. 아래와 같습니다. 오라클과 마찬가지로 인덱스 리프를 모두 읽어 GROUP BY를 처리합니다. 그 결과 IO(Buffers) 가 52,401이나 나옵니다.
-- CREATE INDEX t_ord_big_test_x on t_ord_big(ord_ymd);
SELECT t1.ord_ymd, COUNT(*)
FROM t_ord_Big t1
GROUP BY t1.ord_ymd
ORDER BY 1 DESC;
GroupAggregate (actual time=30.175..10685.530 rows=365 loops=1)
Group Key: ord_ymd
Buffers: shared hit=52401
-> Index Only Scan Backward using t_ord_big_test_x on t_ord_big t1
(actual time=0.017..4792.832 rows=61194000 loops=1)
Heap Fetches: 0
Buffers: shared hit=52401
Planning Time: 0.064 ms
Execution Time: 10685.672 ms
3. GROUP BY COUNT - MySQL 8.0.22
이번에는 MySQL입니다. MySQL 역시 GROUP BY와 카운트가 사용된 SQL은 리프 페이지를 모두 스캔해야 하므로 제법 시간이 걸립니다. 77,979 페이지의 IO가 발생했습니다.
SELECT T1.ORD_YMD, COUNT(*)
FROM T_ORD_BIG T1
GROUP BY T1.ORD_YMD
ORDER BY 1 DESC;
-> Group aggregate: count(0) (actual time=28.596..10960.686 rows=349 loops=1)
-> Index scan on T1 using T_ORD_BIG_X05(reverse)
(actual time=1.328..7564.492 rows=6094000 loops=1)
id table ptype key key_len rows Extra
-- ----- ------ ------------- ------- ------- --------------------------------
1 T1 Nindex T_ORD_BIG_X05 35 6145479 Backward index scan; Using index
- Time sec: 7.538511
* Rows read: 6094000
* Buffer pages: 77979
4. ONLY GROUP BY - ORALE 19C SE
다시, 오라클로 돌와옵니다. 이번에는 GROUP BY만 SQL에 존재합니다. COUNT 처리는 하지 않습니다. 이전과 같은 17,044의 IO가 발생했습니다.
SELECT T1.ORD_YMD
FROM T_ORD_BIG T1
WHERE T1.ORD_YMD > ' ' -- 인덱스를 강제로 사용하게 하기 위해 조건 사용
GROUP BY T1.ORD_YMD
ORDER BY 1 DESC;
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts |A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 |00:00:03.67 | 17044 |
| 1 | SORT GROUP BY | | 1 | 100 |00:00:03.67 | 17044 |
|* 2 | INDEX FAST FULL SCAN| T_ORD_BIG_TEST_X | 1 | 6094K|00:00:01.54 | 17044 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."ORD_YMD">' ')
5. GROUP BY COUNT - PostgreSQL 16.1
PostgreSQL에서 GROUP BY만 있는 SQL을 실행해봅니다. 2번과 마찬가지로 52,401의 IO가 발생합니다.
SELECT t1.ord_ymd
FROM t_ord_Big t1
GROUP BY t1.ord_ymd
ORDER BY 1 DESC;
Group (actual time=0.019..9361.799 rows=365 loops=1)
Group Key: ord_ymd
Buffers: shared hit=52401
-> Index Only Scan Backward using t_ord_big_test_x on t_ord_big t1
(actual time=0.018..4604.412 rows=61194000 loops=1)
Heap Fetches: 0
Buffers: shared hit=52401
Planning Time: 0.060 ms
Execution Time: 9361.961 ms
6. GROUP BY COUNT - MySQL 8.0.22
마지막으로 MySQL에서 COUNT 없이 GROUP BY만 사용해봅니다. 두둥!!! IO가 1,056으로 획기적으로 줄었습니다.
이는 MySQL에만 존재하는 내부적인 알고리즘때문인듯 합니다. 클래식 실행계획의 Extra를 보면 Using index for group-by가 표시되어 있습니다. 또한 Tree 실행계획에는 using index_for_group_by라고 표시되어 있습니다.
SELECT T1.ORD_YMD
FROM T_ORD_BIG T1
GROUP BY T1.ORD_YMD
ORDER BY 1 DESC;
id table type key key_len rows Extra
-- ----- ----- ------------- ------- ---- -----------------------------
1 T1 range T_ORD_BIG_X05 35 351 Using index for group-by; ...
-> Group (computed in earlier step, no aggregates) (actual time=7.000..7.138 rows=349 loops=1)
-> Sort: t1.ORD_YMD DESC (actual time=6.998..7.066 rows=349 loops=1)
-> Table scan on <temporary> (actual time=0.002..0.051 rows=349 loops=1)
-> Temporary table with deduplication (actual time=5.657..5.775 rows=349 loops=1)
-> Index range scan on T1 using index_for_group_by(T_ORD_BIG_X05)
(actual time=0.149..5.239 rows=349 loops=1)
- Time sec: 0.011888
* Rows read: 350
* Buffer pages: 1056
결론
SQL에서 집계함수 없이 GROUP BY만 사용되고 있고, GROUP BY 컬럼이 인덱스로 커버가 되는 상황이라면, MySQL의 성능이 압도적으로 좋습니다. 정확히는 MySQL의 실행계획에 index_for_group_by가 출현해야 합니다. 그러나, 이와 같은 패턴, 이와 같은 상황은 실제 SQL에서 잘 나오지 않습니다. 특히나 SQL이 복잡해질수록 거의 나오기 힘든 패턴입니다.
그러므로 이런 기능 하나로 MySQL이 다른 DBMS보다 좋다라고 할 수는 없습니다. 각 DBMS마다 가지고 있는 장점과 단점이 있습니다. 우리는 이러한 부분을 잘 파악하고 그에 맞게 사용해야 합니다.
어쨋든, MySQL을 운영하고 있다면, 이와 같은 기능이 있음을 잘 기억할 필요가 있습니다. 가끔 업무 요건에 따라, 튜닝을 위해 써먹을 수 있는 그런 기술이니까요.
위와 같이 SQL을 사용한다면 전혀 문제 없습니다. 그런데 실수로(?) 또는 습관적으로 GROUP BY 를 추가하는 경우가 있습니다. 아래와 같이 말이죠. 아래 SQL은 위의 SQL과 완전히 같은 결과를 보여줍니다. 하지만, 성능적으로는 차이가 있습니다.
IO를 보면 45로 기존 대비 10배 이상 증가했습니다. 물론, 따져보면 실제 실행시간은 이전이나 지금이나 거의 차이는 없습니다. 인덱스만 이용해 SQL이 처리되기 때문입니다. 실행계획을 살펴보면, GROUP BY 를 위해 Index range scan이 나온것을 알 수 있습니다.
SELECT MAX(T1.ORD_DT) LAST_ORD_DT
FROM T_ORD_BIG T1
WHERE T1.CUS_ID = 'CUS_0001'
GROUP BY T1.CUS_ID; -- > GROUP BY 를 추가!!!!
Execution Plan:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-- ----------- ----- ---------- ----- --------------------------- ------------- ------- ---- ---- -------- -------------------------------------
1 SIMPLE T1 None range T_ORD_BIG_X03,T_ORD_BIG_X04 T_ORD_BIG_X04 163 None 228 100.0 Using where; Using index for group-by
-> Group aggregate (computed in earlier step): max(t1.ORD_DT) (actual time=0.105..0.126 rows=1 loops=1)
-> Filter: (t1.CUS_ID = 'CUS_0001') (cost=159.60 rows=228) (actual time=0.102..0.122 rows=1 loops=1)
-> Index range scan on T1 using index_for_group_by(T_ORD_BIG_X04) (cost=159.60 rows=228) (actual time=0.099..0.119 rows=1 loops=1)
- Time sec: 0.000999
* Rows read: 4
* Buffer pages: 45
PostgreSQL 16.1
PostgreSQL도 다르지 않습니다. 먼저 GROUP BY 없이 마지막 일자를 가져오는 SQL입니다. IO(Buffers) 수치를 보면 4입니다.
SELECT MAX(T1.ORD_DT) LAST_ORD_DT
FROM T_ORD_BIG T1
WHERE T1.CUS_ID = 'CUS_0001';
Result (actual time=0.057..0.059 rows=1 loops=1)
Buffers: shared hit=4
InitPlan 1 (returns $0)
-> Limit (actual time=0.052..0.053 rows=1 loops=1)
Buffers: shared hit=4
-> Index Only Scan Backward using t_ord_big_x04 on t_ord_big t1 (actual time=0.049..0.050 rows=1 loops=1)
Index Cond: ((cus_id = 'CUS_0001'::text) AND (ord_dt IS NOT NULL))
Heap Fetches: 0
Buffers: shared hit=4
Planning Time: 0.298 ms
Execution Time: 0.100 ms
이번에는 같은 결과이지만, GROUP BY를 추가한 SQL입니다. IO(Buffers) 수치가 64로 급등한 것을 알 수 있습니다. 같은 결과이지만, GROUP BY 가 추가되었으므로 내부적으로 GROUP BY 를 처리하기 때문입니다.
SELECT MAX(T1.ORD_DT) LAST_ORD_DT
FROM T_ORD_BIG T1
WHERE T1.CUS_ID = 'CUS_0001'
GROUP BY T1.CUS_ID;
GroupAggregate (actual time=20.337..20.339 rows=1 loops=1)
Buffers: shared hit=64
-> Index Only Scan using t_ord_big_x04 on t_ord_big t1 (actual time=0.037..11.810 rows=66000 loops=1)
Index Cond: (cus_id = 'CUS_0001'::text)
Heap Fetches: 0
Buffers: shared hit=64
Planning Time: 0.132 ms
Execution Time: 20.385 ms
ORACLE 19C SE
내친김에 오라클도 살펴보도록 하겠습니다. 이 부분은 오라클도 별반 다르지 않습니다. 아래는 GROUP BY가 없는 경우로 3블록의 IO가 발생했습니다.
아래는 GROUP BY가 추가된 경우입니다. IO가 260으로 매우 높습니다. 다른 DBMS보다 나쁘지만 이는 의미가 없습니다. 데이터가 실제 저장되어 군집된 상태가 다를 수 있기 때문입니다.
SELECT MAX(T1.ORD_DT) LAST_ORD_DT
FROM T_ORD_BIG T1
WHERE T1.CUS_ID = 'CUS_0001'
GROUP BY T1.CUS_ID;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 260 |
| 1 | SORT GROUP BY NOSORT| | 1 | 1 | 1 |00:00:00.03 | 260 |
|* 2 | INDEX RANGE SCAN | T_ORD_BIG_X04 | 1 | 1 | 66000 |00:00:00.12 | 260 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."CUS_ID"='CUS_0001')
결론
사소한 습관성 GROUP BY를 사용하지 않도록 주의합시다. 실제 시간상으로 성능 차이가 크지는 않지만 DB 내부적인 IO에서는 제법 차이가 있으며, 해당 SQL이 정말 자주 실행된다면 불필요한 연산으로 DB 자원 사용량 증가에도 영향을 줄 수있습니다. 무엇보다 중요한 것은, 결론만 외우는 것이 아니라 실행계획을 통해 이와 같은 차이점을 확인하고 이해하는 것입니다.
위와 같은 인덱스 구성에서 다음과 같은 SQL을 실행합니다. SQL에서는 ORD_DT 조건만 사용하고 있습니다.
아래 SQL의 성능을 위해서는 ORD_DT가 선두 컬럼인 인덱스를 추가해야 하지만, 여기서는 인덱스 추가를 고려하지 않습니다. 현재 인덱스 구성에서 선두 컬럼이 ORD_DT인 인덱스는 존재하지 않습니다. 그러므로 일반적인 Index Scan이나 Index Lookup은 할 수 없습니다. 하지만 MySQL 8에서는 Index Skip Scan을 활용할 수 있으므로, ORD_DT가 두 번째 컬럼으로 위치한 T_ORD_JOIN_X02 인덱스를 사용할 수 있습니다. 아래 SQL의 TREE 형태의 실행계획을 보면 Index for skip scan이 있는 것을 알 수 있습니다. IO를 측정(Buffer pages)해보면 4,224 페이지의 IO 호출이 있었습니다. 실행시간 기준으로 0.94초 정도가 걸렸고요.
-- 1. INDEX 컬럼만 조회, SKIP SCAN 발생, Buffers = 4224
SELECT T1.CUS_ID ,T1.ORD_DT
FROM T_ORD_JOIN T1
WHERE T1.ORD_DT >= STR_TO_DATE('20170301','%Y%m%d')
AND T1.ORD_DT < STR_TO_DATE('20170305','%Y%m%d');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-- ----------- ----- ---------- ----- -------------- -------------- ------- ---- ------ -------- --------------------------------------
1 SIMPLE T1 None range T_ORD_JOIN_X02 T_ORD_JOIN_X02 168 None 356310 100.0 Using where; Using index for skip scan
-> Filter: ((t1.ORD_DT >= <cache>(str_to_date('20170301','%Y%m%d'))) and (t1.ORD_DT < <cache>(str_to_date('20170305','%Y%m%d')))) (cost=96850.94 rows=356310)
-> Index range scan on T1 using index_for_skip_scan(T_ORD_JOIN_X02) (cost=96850.94 rows=356310)
* Rows read: 32180
* Buffer pages: 4224
2. SELECT 절에 인덱스 외의 컬럼을 조회 - TABLE FULL SCAN 발생
이제 SQL을 변경해봅니다. 앞에서 실행한 SQL은 T_ORD_JOIN_X02 인덱스에 있는 컬럼만 사용하고 있습니다.
다음과 같이 T1.*로 SQL을 변경해 실행해봅니다. 실행계획을 보면 Table scn(TABLE FULL SCAN)으로 변경이 되었습니다 IO 수치 역시 49,677로 이전 Index Skip Scan보다 열배 이상 늘어났습니다. 실행시간도 3.33초로 증가했습니다. 이처럼 SELECT 절에 인덱스 외의 컬럼이 추가되자 Index Skip Scan이 작동하지 않습니다. 인덱스를 통해 다시 클러스터드 인덱스에 접근하는 부하가 크다고 옵티마이져가 판단했기 때문에 FULL SCAN을 선택했을 것이라 생각이 듭니다.
(현재까지 테스트로는 인덱스에 없는 컬럼이 하나라도 추가되면 FULL SCAN으로 처리됩니다. 다른 테이블(두 번째 컬럼의 카디널리티가 큰 테이블)을 사용해도 마찬가지입니다. 이는 두 번째 컬럼의 통계를 적절히 계산 못하는 상황이 있는거 아닌가란 생각이 듭니다.)
-- 2. INDEX 이외의 컬럼을 사용, TABLE FULL SACN으로 변경됨, Buffers = 49677
SELECT T1.*
FROM T_ORD_JOIN T1
WHERE T1.ORD_DT >= STR_TO_DATE('20170301','%Y%m%d')
AND T1.ORD_DT < STR_TO_DATE('20170305','%Y%m%d')
Execution Plan:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-- ----------- ----- ---------- ---- ------------- ---- ------- ---- ------- -------- -----------
1 SIMPLE T1 None ALL None None None None 3207440 11.11 Using where
-> Filter: ((t1.ORD_DT >= <cache>(str_to_date('20170301','%Y%m%d'))) and (t1.ORD_DT < <cache>(str_to_date('20170305','%Y%m%d')))) (cost=341086.14 rows=356311)
-> Table scan on T1 (cost=341086.14 rows=3207440)
* Rows read: 3224000
* Buffer pages: 49677
3. SELECT 절에 PK 컬럼만 조회 - INDEX SKIP SCAN 발생
이번에는 인덱스 컬럼인 CUS_ID, ORD_DT가 아닌 ORD_SEQ를 SELECT 절에 사용해 봅니다. 그러면 다시 Index Skip Scan이 작동합니다. MySQL의 InnoDB는 테이블마다 PK로 클러스터드 인덱스를 만들고, 넌클러스터드 인덱스의 리프 노드에는 클러스터드 인덱스의 키(PK) 값을 저장하기 때문에, 이처럼 PK 컬럼을 SELECT하는 경우는 기존의 X02 인덱스만 사용해서 해결이 가능합니다.
이러한 포인트를 잘 이해했다면, 이 부분을 응용해 T1.* 와 같은 결과를 만들어내면서 Index Skip Scan을 유지할 수 있습니다. (이 부분은 별도 글로 올리도록 하겠습니다.)
-- 3. PK 컬럼(ORD_SEQ)만 SELECT, INDEX SKIP SCAN, Buffers = 4224
SELECT T1.ORD_SEQ
FROM T_ORD_JOIN T1
WHERE T1.ORD_DT >= STR_TO_DATE('20170301','%Y%m%d')
AND T1.ORD_DT < STR_TO_DATE('20170305','%Y%m%d');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-- ----------- ----- ---------- ----- -------------- -------------- ------- ---- ------ -------- --------------------------------------
1 SIMPLE T1 None range T_ORD_JOIN_X02 T_ORD_JOIN_X02 168 None 356310 100.0 Using where; Using index for skip scan
-> Filter: ((t1.ORD_DT >= <cache>(str_to_date('20170301','%Y%m%d'))) and (t1.ORD_DT < <cache>(str_to_date('20170305','%Y%m%d')))) (cost=96849.94 rows=356310)
-> Index range scan on T1 using index_for_skip_scan(T_ORD_JOIN_X02) (cost=96849.94 rows=356310)
* Rows read: 32180
* Buffer pages: 4224
준비한 내용은 여기까지입니다. MySQL도 이제 Index Skip Scan이 있다 정도를 알고 가시면 될거 같습니다.^^
MySQL 또는 PostgreSQL의 SQL 튜닝 입문 강의가 필요하신 분은 아래 링크를 참고해주세요.!
Leading(table table[ table...]) - Forces join order as specified. - 지정된 대로 조인 순서를 강제한다 Leading(<join pair>) - Forces join order and directions as specified. A join pair is a pair of tables and/or other join pairs enclosed by parentheses, which can make a nested structure. - 지정된 대로 조인 순서와 방향을 강제한다. 조인 쌍은 괄호로 둘러싸인 테이블 또는 다른 조인 쌍의 쌍이며, 이를 통해 중첩 구조를 만들 수 있다
이처럼 두 가지 사용법이 적혀 있습니다. 하지만 이 설명만 봐서는 잘 이해할 수 없습니다.
Leading 힌트의 용법을 이해하기 위해서는 조인 순서와 조인 방향에 대해 정의할 필요가 있습니다.
조인 순서: 조인을 처리하는 순서
조인 순서가 A, B, C: A와 B를 조인해, (A, B)를 만든 후에 (A, B)와 C를 조인하는 순서로 처리
이처럼 조인 순서만 정의하면, 두 데이터 집합을 조인할 때는 어떤 방향으로 조인할지는 알 수 없다.
예를 들어, A와 B를 조인할 때 어느 테이블을 먼저 처리할지는 알 수 없다.
(A, B)와 C를 조인할 때도, 어느 쪽을 먼저 접근해 처리할지는 알 수 없다.
조인 방향: 두 데이터 집합이 결합(조인)할 때 정해지는 두 데이터 집합 간의 접근 순서
A, B, C의 조인 순서이면서, A에서 B 방향으로 조인, C에서 (A, B) 방향으로 조인
조인 순서와 함께 조인 방향이 정의되었다.
A에서 B 방향으로 조인 처리해서 (A, B) 생성, C에서 (A, B) 데이터 집합 방향으로 조인 처리
A, B, C의 조인 순서이면서, B에서 A 방향으로 조인, (B, A)에서 C 방향으로 조인
B에서 A 방향으로 조인 처리해서 (B, A) 생성, (B, A) 집합에서 C 방향으로 조인 처리
아마 이해가 될 듯하면서도 이해가 안될 수도 있습니다. 조인 순서나 조인 방향이나 얼핏 보기에 비슷하니까요.
아래와 같이 Leading(t1 t2) 로 힌트를 지정합니다. 이처럼 괄호를 하나만 사용하면 조인 순서만 지정됩니다. 이 경우 NL 조인을 한다고 가정하면 t1을 먼저 접근해 t2 방향으로 조인을 처리할 지, t2를 먼저 접근해 t1 방향으로 조인을 처리할지 알 수 없습니다. 이러한 조인 방향은 옵티마이져가 선택합니다. 사실 두 개 테이블만 있는 상황에서는 이 같은 조인은 불필요합니다.
/*+ Leading(t1 t2) */
SELECT t1.cus_id ,SUM(t2.ord_qty) ord_qty
FROM m_cus t1
INNER JOIN t_ord_join t2
ON (t2.cus_id = t1.cus_id)
WHERE t1.cus_gd = 'B'
AND t2.ord_dt >= TO_DATE('20170201','YYYYMMDD')
AND t2.ord_dt < TO_DATE('20170301','YYYYMMDD')
GROUP BY T1.cus_id;
이번에는 Leading 힌트에 괄호를 중첩해서 사용합니다. 이와 같이 정의하면 조인 방향이 정해집니다. 단순한 NL 조인이라면, 앞에 있는 t1이 선행 집합이 되어 t2 방향으로 접근해 조인이 처리됩니다. 그런데, SQL이 HASH 조인으로 풀린다면 주의가 필요합니다. Leading 힌트의 뒤쪽에 있는 t2가 Build가 되고, t1이 Probe가 됩니다.
다시 말해, HASH JOIN에서는 Build가 일반적으로 먼저 만들어진다는 개념과, Leading의 서술에 따른 순서가 반대되므로 이 부분은 외워두기 바랍니다.
/*+ Leading((t1 t2)) */
SELECT t1.cus_id ,SUM(t2.ord_qty) ord_qty
FROM m_cus t1
INNER JOIN t_ord_join t2
ON (t2.cus_id = t1.cus_id)
WHERE t1.cus_gd = 'B'
AND t2.ord_dt >= TO_DATE('20170201','YYYYMMDD')
AND t2.ord_dt < TO_DATE('20170301','YYYYMMDD')
GROUP BY T1.cus_id;
이번에는 세 개 테이블을 조인해봅니다. 아래와 같이 Leading에서 순서만 지정해봅니다. t1, t2, t3, 순서의 조인만 유지합니다. 두 데이터 집합이 조인되는 순간에는 어느쪽을 선행을 할지, HASH 조인이라면 어느쪽을 Build로 할지는 옵티마이져가 알아서 선택합니다.
/*+ Leading(t1 t2 t3) */
SELECT t1.itm_id ,t1.itm_nm ,t2.ord_st ,count(*) ord_qty
FROM m_itm t1
INNER JOIN t_ord_join t2 on (t2.itm_id = t1.itm_id)
INNER JOIN m_cus t3 ON (t3.cus_id = t2.cus_id)
WHERE t1.itm_tp = 'ELEC'
AND t2.ord_dt >= TO_DATE('20170201','YYYYMMDD')
AND t2.ord_dt < TO_DATE('20170301','YYYYMMDD')
AND t3.cus_gd = 'B'
GROUP BY t1.itm_id ,t1.itm_nm ,t2.ord_st;
조인순서와 함께 조인 방향을 모두 지정하고 싶다면 아래와 같이 괄호를 조인이 발생하는 두 데이터 집합간에 추가 중첩합니다. 아래 SQL의 조인되는 순서와 방향을 정리하면 다음과 같습니다.
Leading(((t1 t2) t3)) 괄호가 세 번 중첩, t1과 t2를 묶고, (t1 t2)와 t3를 한 번 더 묶었습니다.
이러한 정의가 틀리지는 않지만, 이 부분에만 포커스를 맞추어서 접근하다 보면 튜닝이 더 어려울 수 있습니다.
SQL 성능 개선의 초심자라면, “SQL을 빠르게 만들자” 보다는, “SQL의 비효율을 찾아 제거해보자”라는 개념으로 접근하는 것이 좀 더 쉽습니다.
그러므로 저는 SQL 튜닝에 대해 다음과 같이 정의합니다. “SQL 튜닝이란 SQL의 비효율을 제거하는 작업이다. 여기에 SQL의 중요도에 따라 추가 최적화를 이끌어 내야 하는 작업이다.” 여기서 SQL의 중요도는 사용 빈도나 업무의 중요성으로 판단할 수 있습니다.
SQL 튜닝 관련해 제가 경험한 몇 가지 사례를 살펴보겠습니다. 안타깝게도 MySQL이나 PostgreSQL 보다는 오라클에서 겪었던 경험이 주를 이룹니다.
제가 경험한 사이트 중에 12시간이 지나도 끝나지 않는 SQL이 있었습니다. 해당 SQL에는 3억 건 이상의 데이터 집합 두 개가 조인하고 있었고, 이 외에도 천 만 건 이상의 여러 테이블이 조인이 되는 한 문장의 배치 SQL이었습니다. 물론 오라클의 병렬 처리도 작동하고 있는 SQL이었습니다. 이러한 배치 SQL은 튜닝이 쉽지 않습니다. 튜닝의 실제 효과를 알기 위해서는 실행을 해야 하는데, 한 번 실행하면 최소 몇 시간은 걸리기 때문입니다. 어려운 상황 속에서도 SQL을 부분별로 나누어서 살펴보다 보니, 조인 전에 매우 많은 IN 조건이 처리되면서 오래 걸리는 것을 확인해습니다. IN 보다 조인이 먼저 처리되도록 SQL을 변경하자 4시간 이내로 SQL이 완료되었니다. 이는 조인을 먼저 하면 결과 건수가 줄어들어 IN 처리할 대상이 그만큼 줄어들어 개선 효과가 있었던 사례입니다. 어느 한 사이트는 사용자들이 비교적 자주 실행하는 SQL인데도 43초가 걸리고 있었습니다. SQL을 살펴보니, 특정 사용자가 자신의 데이터만 조회하는 SQL임에도 불구하고 사용자ID에 대한 조건을 LIKE로 처리하고 있었습니다. 이로 인해 사용자ID에 대한 인덱스를 사용하지 않아 오래 걸리는 케이스였습니다. 이 경우는 힌트로 해결할 수도 있지만, 가장 좋은 것은 프로그램을 변경해 사용자ID에 대한 조건을 같다(=) 조건으로 처리하는 것입니다. 이와 같이 처리하면 DBMS의 옵티마이져가 알아서 사용자ID에 대한 인덱스를 사용하게 됩니다. 이를 통해 43초 걸리던 SQL은 0.02초로 개선이 되었습니다.
또 다른 사이트는 차세대 사이트로서, 고객의 주문을 집계하는 배치에서 성능 저하가 있었습니다. AS-IS에서는 30분이 안 걸리던 집계 처리가 차세대에서는 6시간 이상이 걸리는 상황이 발생한 것입니다. 원인을 살펴보니 차세대로 넘어가면서 관련 테이블을 파티션으로 변경했고, SQL이 파티션의 키 값을 활용하지 못하도록 되어 있어 성능 저하가 발생한 것입니다. SQL을 변경해 파티션 키를 사용할 수 있도록 변경을 해주었고, 원래의 성능으로 돌아갈 수 있었습니다. 지금까지 소개한 사례들은 주로 오라클 환경에서의 경험입니다. MySQL 환경에서의 사례도 있습니다. MySQL 환경에서 업데이트 SQL이 한 시간 이상 걸려 문의가 들어온 적이 있었습니다. SQL은 업데이트 대상을 상관 서브쿼리 방식으로 선별하고 있었고, 실행계획을 확인해 보니, 상관 서브쿼리의 테이블을 FULL SCAN으로 처리하고 있었습니다. SQL을 중지하고 업데이트 대상 선별 방식을 직접적인 조인으로 변경하자 10초만에 업데이트가 완료되었습니다.
지금까지 살펴본 튜닝 사례에서 저는 “SQL을 빠르게 하자”가 아닌, “이 SQL의 비효율이 뭐지?”라는 방향으로 접근을 했습니다. 이와 같은 방향으로 접근해 비효율을 제거하다 보면, SQL은 저절로 빨라지게 됩니다.
사람마다 다를 수 있지만, 저에게 SQL 튜닝은 그 과정 자체가 미지의 세계를 탐험하는 것처럼 매우 흥미롭고 흥분되는 일입니다. 마치 셜록 홈즈가 사람의 겉모습만을 보고 그 사람의 직업과 과거를 맞추는 것처럼, SQL과 실행계획을 통해 문제점을 직관적으로 찾아내는 것은 행복한 흥분을 선사합니다.
StartUP Tuning 강의를 통해 SQL 튜닝의 기본을 습득하고, 그 신나는 세계에 한 발짝 들어와 보기 바란다.
MySQL에서 테이블을 생성할 때, 캐릭터셋(CHARSET)과 콜레이션(COLLATION)을 설정할 수 있습니다.
캐릭터셋과 콜레이션은 테이블뿐만 아니라 컬럼 단위로도 설정이 가능합니다.
이러한 설정을 잘못하면 조인 SQL 자체가 실행이 안될 수도 있으며, 이는 성능 저하로 연결될 수도 있습니다.
캐릭터셋과 콜레이션은 데이터베이스에서 문자 데이터를 저장하고 비교하는 방식을 뜻합니다.
캐릭터셋(Character Set)
데이터베이스에 저장할 수 있는 문자들의 집합
각 문자셋은 다양한 문자를 인코딩하는 코드를 정의합니다. UTF-8, ASCII, LATIN1등이 있습니다.
UTF-8은 국제적으로 널리 사용되는 캐릭터셋입니다. 거의 모든 언어와 이모지까지 지원합니다.
캐릭터셋에 따라 같은 텍스트도 다른 방식으로 저장될 수 있습니다.
콜레이션(Collation)
문자 데이터를 비교하고 정렬할때 사용하는 규칙의 집합입니다.
대소문자 구분, 악센트 구분, 문자의 정렬 순서 등을 정의합니다.
콜레이션에 따라 언어와 문자의 우선 순위가 달라질 수 있습니다.
이러한 캐릭터셋과 콜레이션은 MySQL이 설치되면서 서버 레벨(DBMS)에서 정의가 됩니다.
my.cnf나 my.ini에 기본으로 값이 설정되어 있으며, 변경이 가능합니다.
현재 운영중인 시스템에서 서버레벨에서 이 설정을 변경하는 순간 매우 큰 혼란이 만들어지게 됩니다.
서버 레벨에서 정의한 캐릭터셋과 콜레이션은 데이터베이스를 생성할 때 상속됩니다.
그리고 데이터베이스에 만들어진 캐릭터셋과 콜레이션은 다시 테이블에 상속됩니다.
캐릭터셋과 콜레이션이 이미 상속되어 테이블이 만들어진 상태에서 이와 같은 설정을 변경한다면 어떤 여파가 있을지 상상하기도 힘듭니다. 그러므로 캐릭터셋과 콜레이션은 서비스를 시작하기 전에 정의되어야 합니다. 아니, 개발을 시작하기 전 설계 단계에서부터 정의되어야 맞습니다.
MySQL 관련된 블로그나 자료를 찾다 보면, 테이블을 생성할 때, 캐릭터셋이나 콜레이션을 지정해서 생성하는 스크립트를 볼 수 있습니다. 이러한 스크립트를 카피해서 사용하다 보면, 예상하지 못한 문제에 마주칠 수 있습니다.
먼저, 자신이 접속한 데이터베이스의 캐릭터셋과 콜레이션을 살펴봅니다.
# 사용할 기본 데이터베이스 변경하기
USE MYTUNDB;
# 현재 계정이 사용하는 기본 데이터베이스 확인하기
SHOW VARIABLES LIKE 'character_set%';
-- character_set_database utf8mb4
-- character_set_connection utf8mb4
SHOW VARIABLES LIKE 'collation%';
-- collation_database utf8mb4_0900_ai_ci
-- collation_connection utf8mb4_0900_ai_ci
위와 같이 기본 캐릭터셋과콜레이션이 있음에도 불구하고, 아래와 같이 COLLATE를 기본과 다른 설정으로 생성을 해봅니다. 인터넷을 검색하다 알게된걸 그냥 카피해서 사용한 것이죠. 데이터베이스의 기본 콜레이트는 utf8mb4_0900_ai_ci이지만, 아래 테이블은 utf8mb_unicode_ci 를 사용했습니다.
CREATE TABLE MYTUNDB.T_ORD_TEST
(
ORD_SEQ BIGINT UNSIGNED NOT NULL COMMENT'주문번호',
CUS_ID VARCHAR(40) NOT NULL COMMENT '고객ID',
ORD_DT DATETIME NULL COMMENT '주문일시',
ORD_ST VARCHAR(40) NULL COMMENT '주문상태',
PAY_DT DATETIME NULL COMMENT '결제일시',
PAY_TP VARCHAR(40) NULL COMMENT '결제유형',
ORD_AMT DECIMAL(18,3) NULL COMMENT '주문금액',
PAY_AMT DECIMAL(18,3) NULL COMMENT '결제금액',
PRIMARY KEY(ORD_SEQ)
)
CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci
COMMENT '주문테스트';
-- 테스트를 위한 데이터 입력 및 인덱스 생성
INSERT INTO MYTUNDB.T_ORD_TEST SELECT* FROM T_ORD;
CREATE INDEX T_ORD_TEST_X01 ON T_ORD_TEST(CUS_ID);
이제 기존에 원래 있던 테이블과 조인 SQL을 만들어 실행해 봅니다.
SELECT *
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_TEST T2 ON (T2.CUS_ID = T1.CUS_ID)
WHERE T1.CUS_ID = 'CUS_0001'
위 SQL을 실행해보면 다음과 같은 에러를 만납니다.
Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
두 테이블(M_CUS와 T_ORD_TEST)간에 조인 조건으로 사용한 CUS_ID가 테이블별로 다른 콜레이트를 사용하기 때문에 발생한 에러입니다. 두 테이블의 CUS_ID에 대한 콜레이트는 아래 SQL로 확인할 수 있습니다.
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ('M_CUS','T_ORD_TEST') AND COLUMN_NAME = ('CUS_ID')
AND TABLE_SCHEMA = 'MYTUNDB';
TABLE_NAME|COLUMN_NAME|CHARACTER_SET_NAME|COLLATION_NAME |
----------+-----------+------------------+------------------+
m_cus |CUS_ID |utf8mb4 |utf8mb4_0900_ai_ci|
t_ord_test|CUS_ID |utf8mb4 |utf8mb4_unicode_ci|
이와 같이 콜레이트가 다른 상황에서 SQL을 실행하기 위한 임시 방편은 아래와 같이 조인시 콜레이트를 재정의하는 것입니다. 아래는 T1(M_CUS)의 콜레이트를 T2(T_ORD_TEST)와 동일하게 변경한 경우입니다.
EXPLAIN ANALYZE
SELECT *
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_TEST T2 ON (T2.CUS_ID = T1.CUS_ID COLLATE utf8mb4_unicode_ci)
WHERE T1.CUS_ID = 'CUS_0001'
;
-> Index lookup on T2 using T_ORD_TEST_X01 (CUS_ID=('CUS_0001' collate utf8mb4_unicode_ci)) (cost=11.55 rows=33) (actual time=0.168..0.183 rows=33 loops=1)
실행계획을 보면, T_ORD_TEST_X01이라는 인덱스를 사용해 결과를 처리하고 있습니다.
인덱스를 사용한다고 성능이 무조건 좋은건 아니지만, 원하는 방향으로 처리가 되고 있습니다.
만약에 콜레이트를 T1쪽이 아닌 T2쪽의 콜레이트를 변경하면 어떻게 될까요? 아래와 같이 T2(T_ORD_TEST)를 FULL SCAN하는 것을 알 수 있습니다.
현재(2023년 1월) 기준으로 위의 레퍼런스를 살펴보면, 해시 조인과 관련된 힌트로, HASH_JOIN과 NO_HASH_JOIN이 있는 것을 알 수 있습니다. 하지만, Description을 보면 해당 힌트는 MySQL 8.0.18 에서만 사용할 수 있는 힌트입니다.
저는 현재 8.0.22 를 사용하고 있습니다. 이처럼 8.0.18 이후 버젼에서는 해시 조인을 컨트를하려면 BNL이나 NO_BNL이란 힌트를 사용해야 합니다.
위 레퍼런스에는, BNL 힌트의 설명으로 아래와 같이 정리되어 있습니다.
- Prior to MySQL 8.0.20: affects Block Nested-Loop join processing;
- MySQL 8.0.18 and later: also affects hash join optimization;
- MySQL 8.0.20 and later: affects hash join optimization only
BNL은 원래 Block Nested-Loop 를 뜻합니다. NL(Nested-Loops) 조인을 처리할 때, 조인 버퍼를 사용해 NL 조인 성능을 향상시키는 기능입니다. MySQL 8 이전 버젼은 해시 조인을 지원하지 않았기 때문에, BNL 기능으로 해시 조인을 어느 정도 대체했던거 같습니다.
어찌되었든, 8.0.2 부터는 BNL 힌트가 해시 조인 최적화에 영향을 준다니 혼란스럽습니다. 아마도 8.0.2부터는 본연의 BNL(Block Nested-Loops) 기능 자체는 없어진거 같습니다. 그냥 BNL 힌트를 제거하고 HASH_JOIN 힌트를 살려줬으면 혼란이 없지 않았을까 생각이 듭니다만, 제가 모르는 다양한 이유가 있겠죠.
그러면, BNL 힌트를 사용해 해시 조인을 한번 테스트해볼까 합니다. 생각보다? 아니, 생각만큼 힌트가 잘 안먹습니다.
먼저, 저희가 테스트할 테이블인 M_CUS와 T_ORD_BIG에는 어떤 인덱스가 있는지 먼저 살펴봅니다.
인덱스는 조인의 내부적인 처리 방법(NL, HASH, Sort Merge)에 영향을 끼치는 중요 요소입니다.
MySQL에서 인덱스는 'show index from 테이블명' 명령어를 통해 쉽게 확인할 수 있습니다.
위와 같이 인덱스가 구성된 상태에서 아래와 같이 SQL을 실행해 봅니다. 실행 계획을 보면, M_CUS와 T_ORD_BIG을 NL 방식으로 조인하고 있습니다. 조인 조건으로 사용된 CUS_ID 컬럼의 인덱스는 M_CUS에만 있으므로, M_CUS를 후행 집합으로 NL 처리하고 있습니다.
EXPLAIN ANALYZE
SELECT T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID
-> Table scan on <temporary> (actual time=0.003..0.015 rows=72 loops=1)
-> Aggregate using temporary table (actual time=187.347..187.376 rows=72 loops=1)
-> Nested loop inner join (cost=31569.86 rows=39462) (actual time=1.043..126.440 rows=19800 loops=1)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462) (actual time=0.973..91.609 rows=19800 loops=1)
-> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID) (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=19800)
이제, NL이 아닌, 해시 조인으로 위 SQL이 처리되도록 하기 위해 아래와 같이 BNL 힌트를 적용합니다.
실행계획을 확인해보면, 힌트가 무시되고 NL 조인이 여전히 사용중인 것을 알 수 있습니다.
힌트를 잘 못 준건 아닐까란 생각에, /*+ BNL(T1) */ 이나, /*+ BNL(T1, T2) */, /*+ BNL(T2, T1) */ 등 다양하게 시도해봤으나 해시 조인이 발생하지는 않습니다.
아마도 MySQL의 옵티마이져가 생각했을때, 현재 SQL과 테이블, 인덱스 구성에서는 해시 조인이 비효율적이라고 판단한 것이겠죠.
EXPLAIN ANALYZE
SELECT /*+ BNL(T2) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary> (actual time=0.002..0.014 rows=72 loops=1)
-> Aggregate using temporary table (actual time=120.311..120.340 rows=72 loops=1)
-> Nested loop inner join (cost=31569.86 rows=39462) (actual time=0.240..80.108 rows=19800 loops=1)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462) (actual time=0.223..58.354 rows=19800 loops=1)
-> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID) (cost=0.25 rows=1) (actual time=0.000..0.001 rows=1 loops=19800)
이 경우, T2(T_ORD_BIG)쪽에는 CUS_ID에 대한 인덱스가 없으므로 해시 조인이 발생할 가능성이 높습니다. (FULL SCAN 방식으로 NL을 처리할 수는 없으니까요.)
BNL 힌트 없이, JOIN_ORDER 만으로 조인 순서를 변경하자 해시 조인이 사용된 것을 알 수 있습니다.
EXPLAIN ANALYZE
SELECT /*+ JOIN_ORDER(T1, T2) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary> (actual time=0.002..0.014 rows=72 loops=1)
-> Aggregate using temporary table (actual time=106.843..106.871 rows=72 loops=1)
-> Inner hash join (t2.CUS_ID = t1.CUS_ID) (cost=1598243.65 rows=355158) (actual time=0.386..67.943 rows=19800 loops=1)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=13816.34 rows=39462) (actual time=0.250..55.218 rows=19800 loops=1)
-> Hash
-> Table scan on T1 (cost=9.25 rows=90) (actual time=0.058..0.081 rows=90 loops=1)
이번에는 조인 순서를 T2->T1으로 변경하고 BNL 힌트까지 추가해봅니다. 혹시나 JOIN_ORDER와 BNL을 같이 써야만 해시 조인이 사용되는지 의심되서 입니다.
하지만, 다시 NL 조인이 사용되는 것을 볼 수 있습니다. 아무래도 M_CUS의 PRIMARY 인덱스 사용의 우선 순위가 높은거 아닌가 싶습니다.
EXPLAIN ANALYZE
SELECT /*+ JOIN_ORDER(T2, T1) BNL(T2, T1) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary> (actual time=0.002..0.011 rows=72 loops=1)
-> Aggregate using temporary table (actual time=93.163..93.184 rows=72 loops=1)
-> Nested loop inner join (cost=31569.86 rows=39462) (actual time=0.236..62.395 rows=19800 loops=1)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462) (actual time=0.219..44.639 rows=19800 loops=1)
-> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID) (cost=0.25 rows=1) (actual time=0.000..0.000 rows=1 loops=19800)
끝으로, 실제 성능과 상관없이, 죽었다 깨어나도 T2->T1 순서로 조인 순서를 유지하면서, 해시 조인을 만들어 보고 싶다면, 조인 조건의 강제 변화를 고려할 수 있습니다.
아래와 같이 T1.CUS_ID를 TRIM 처리해서 인덱스를 사용못하도록 합니다. 그리고 JOIN_ORDER 힌트만 사용해서 T2 먼저 접근하도록 합니다. BNL 힌트를 주지 않았지만, 해시 조인이 발생한 것을 알 수 있습니다.
(아래와 같이 조인 조건절을 일부러 변형해 해시 조인을 유도하는 것은 절대 좋은 방법이 아니죠.)
EXPLAIN ANALYZE
SELECT /*+ JOIN_ORDER(T2, T1) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (TRIM(T1.CUS_ID) = T2.CUS_ID) -- > T1.CUS_ID를 일부러 TRIM 처리
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary> (actual time=0.002..0.024 rows=72 loops=1)
-> Aggregate using temporary table (actual time=280.777..280.815 rows=72 loops=1)
-> Inner hash join (trim(t1.CUS_ID) = t2.CUS_ID) (cost=372923.03 rows=3551580) (actual time=141.649..239.391 rows=19800 loops=1)
-> Table scan on T1 (cost=0.00 rows=90) (actual time=0.078..0.119 rows=90 loops=1)
-> Hash
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462) (actual time=0.243..48.248 rows=19800 loops=1)
지금까지 살펴본 바로는, 해시 조인을 사용하는 힌트인 BNL이 그렇게 잘 작동하는거 같지는 않습니다. 조인을 처리할 수 있는 막강한 PRIMARY 인덱스가 있다면, 아마도 해시 조인보다 NL 조인을 선호하는 것은 아닌가 생각이 듭니다.
BNL 힌트는 잘 작동하지 않지만, NO_BNL 힌트는 또 잘 먹는거 같드라고요.
아래와 같이 해시 조인이 자동으로 작동했던, JOIN_ORDER(T1, T2)를 적용한 후에 NO_BNL까지 힌트를 걸어주면 NL 방식으로 조인이 처리되어 매우 느린 것을 알 수 있습니다.
EXPLAIN ANALYZE
SELECT /*+ JOIN_ORDER(T1, T2) NO_BNL(T2) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Group aggregate: max(t1.CUS_NM), sum(t2.ORD_AMT), count(0) (actual time=187.159..6557.740 rows=72 loops=1)
-> Nested loop inner join (cost=1598243.65 rows=355158) (actual time=16.554..6530.982 rows=19800 loops=1)
-> Index scan on T1 using PRIMARY (cost=9.25 rows=90) (actual time=0.060..0.328 rows=90 loops=1)
-> Filter: (t2.CUS_ID = t1.CUS_ID) (cost=13816.34 rows=3946) (actual time=24.775..72.503 rows=220 loops=90)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=13816.34 rows=39462) (actual time=0.290..61.939 rows=19800 loops=90)
본 글에서는 BNL을 이용해 해시 조인이 발생한 케이스는 나오지 않았으나, 인덱스의 구성, 쿼리 조건의 변경에 따라 BNL 힌트가 작동이 될 수도 있다고 생각합니다. 어쨋든, 8.0.2 부터는 BNL 힌트를 사용해 해시 조인을 유도할 수 있다는 점과, 힌트를 준다고 해도, 무조건 해시 조인이 발생하지 않는다는 점을 알고 있으면 될거 같습니다.
EXPLAIN
SELECT T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
결과를 보면 아래와 같습니다. T2(T_ORD_BIG)가 위에 있습니다. MySQL의 경우, 아래와 같은 결과에서는 위쪽에 있는 단계가 먼저 처리됩니다. 다시 말해 T2를 먼저 접근해 T1과 조인이 처리되고 있습니다.
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
---- ------------- ------- ------------ -------- --------------- -------------- --------- ------------------- ------- ---------- ----------------------------------------
1 SIMPLE T2 None range T_ORD_BIG_X1 T_ORD_BIG_X1 6 None 39462 100.0 Using index condition; Using temporary
1 SIMPLE T1 None eq_ref PRIMARY PRIMARY 162 mytundb.T2.CUS_ID 1 100.0 None
위 SQL의 실행계획을 EXPLAIN FORMAT = TREE를 사용해 TREE 형태로 살펴보면 아래와 같습니다. T2를 T_ORD_BIG_X1 인덱스로 접근한 후, T1을 NL 조인 방식으로 처리하고 있는 것을 알 수 있습니다.
EXPLAIN FORMAT = TREE
SELECT T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Nested loop inner join (cost=31569.86 rows=39462)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462)
-> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID) (cost=0.25 rows=1)
위 SQL의 조인 순서를 변경해봅니다. T1을 먼저 접근해 T2쪽으로 조인을 하도록 처리해보는 거죠.
아래와 같이 JOIN_ORDER라는 옵티마이져 힌트를 사용할 수 있습니다.
EXPLAIN
SELECT /*+ JOIN_ORDER(T1, T2) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
아래와 같이 실행계획을 보면, T1부터 접근해서 조인이 처리되는 것을 알 수 있습니다. 힌트가 작동한거죠.
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------- ---------- -------------------------------------------------------------------
1 SIMPLE T1 None ALL PRIMARY None None None 90 100.0 Using temporary
1 SIMPLE T2 None range T_ORD_BIG_X1 T_ORD_BIG_X1 6 None 39462 10.0 Using index condition; Using where; Using join buffer (hash join)
TREE 형태로 실행계획을 살펴보면 아래와 같습니다.
실행계획을 잘 살펴보면, T1을 먼저 처리하는 방식으로 조인 순서가 바뀌면서, Inner hash join이 발생한 것을 알 수 있습니다. 바뀐 조인 순서에 따라 옵티마이져가HASH 조인으로 변경을 한 것입니다.
EXPLAIN FORMAT = TREE
SELECT /*+ JOIN_ORDER(T1, T2) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Inner hash join (t2.CUS_ID = t1.CUS_ID) (cost=1598243.65 rows=355158)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=13816.34 rows=39462)
-> Hash
-> Table scan on T1 (cost=9.25 rows=90)
끝으로 주의할 점이 있습니다. 옵티마이져 힌트에서, JOIN_ORDER의 테이블을 정의할 때 반드시 콤마(,)로 구분을 해야 합니다. 아래와 같이 스페이스로만 두 테이블을 분리하면 힌트가 작동하지 않습니다.
EXPLAIN FORMAT = TREE
SELECT /*+ JOIN_ORDER(T1 T2) */ # 잘못된 힌트, T2과 T2를 콤마(,)로 분리해야함!
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Nested loop inner join (cost=31569.86 rows=39462)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462)
-> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID) (cost=0.25 rows=1)
넌클러스터드 인덱스와 클러스터드 인데스의 차이점을 다시 한 번 짚어보면 아래 그림과 같습니다.
넌클러스터드 인덱스와 클러스터드 인덱스 간의 가장 큰 차이점은, 리프 노드의 구성입니다.
넌클러스터드 인덱스: 리프 노드에는 인덱스 컬럼의 값과, 해다 테이블의 클러스터드 인덱스의 컬럼 값이 존재
클러스터드 인덱스: 리프 노드에는 실제 데이터가 존재
이 부분을 정확히 알고 있어야 합니다.
자 그러면, 아래 SQL을 살펴봅니다. WHERE 절에서는 ORD_DT에 같다(=) 조건이 사용되었고, SELECT 절에서는 ORD_DT 컬럼만 사용하고 있습니다.
SELECT T1.ORD_DT
FROM MYTUNDB.T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170104','%Y%m%d');
EXPLAIN
------------------------------------------------------------------------------------------------------------
-> Index lookup on T1 using T_ORD_BIG_X1 (ORD_DT=str_to_date('20170104','%Y%m%d')) (cost=92.52 rows=900) (actual time=0.087..0.554 rows=900 loops=1)
위 SQL의 실행계획을 보면, T_ORD_BIG_X1 인덱스를 사용해 처리가 된 것을 알 수 있습니다. actual time은 0.087, cost는 92.52라고 측정되어 있습니다. 위 과정을 그림으로 그려보면, 아래와 같습니다. 앞에서도 계속 설명했던 내용입니다.
아래 그림에서 1번 과정은 수직적 탐색, 2번 과정은 수평적 탐색입니다.
1번, 수직적 탐색은 WHERE 절 조건에 해당하는 값이 어디서부터 시작인지, 위치를 찾기 위한 과정입니다.
2번, 수평적 탐색은 WHERE 절 조건을 만족하지 않을때까지 리프 노드를 차례대로 검색하는 과정입니다.
이번에는 아래 SQL을 살펴보도록 합니다. 방금 살펴본 위의 SQL과 거의 같습니다. 다른 점은 SELECT 절에 ORD_DT 외에도 ORD_SEQ 컬럼이 있다는 점입니다. cost는 위의 SQL과 같고, actual time이 아주 약간 늘어난 것을 확인 할 수 있지만, 거의 차이가 없다고 보셔도 됩니다.
아래 SQL의 실행 계획 역시 위의 그림과 동일합니다. T_ORD_BIG_X1 인덱스만 사용해 원하는 결과를 처리합니다. 넌클러스터드 인덱스의 리프 노드에는 클러스터드 인덱스의 컬럼인 ORD_SEQ의 값도 존재하므로, ORD_SEQ 값을 가져오기 위한 추가적인 인덱스 접근이나 액션이 필요하지 않습니다.
SELECT T1.ORD_DT ,T1.ORD_SEQ
FROM MYTUNDB.T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170104','%Y%m%d');
EXPLAIN
------------------------------------------------------------------------------------------------------------
-> Index lookup on T1 using T_ORD_BIG_X1 (ORD_DT=str_to_date('20170104','%Y%m%d')) (cost=92.52 rows=900) (actual time=0.133..1.150 rows=900 loops=1)
이번에는 아래 SQL과 같이 SELECT 절에 T1.*를 사용해 모든 컬럼을 조회하도록 합니다. 실행계획을 살펴보면, 이전 SQL들과 마찬가지로 T_ORD_BIG_X1 인덱스를 사용하고 있습니다. 그런데 뒤쪽의 cost를 보면, 987.60으로 10배 가까이 증가한 것을 알 수 있습니다. actual time 역시 0.475로 증가했습니다.
SELECT T1.*
FROM MYTUNDB.T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170104','%Y%m%d');
EXPLAIN
-------------------------------------------------------------------------------------------------------------
-> Index lookup on T1 using T_ORD_BIG_X1 (ORD_DT=str_to_date('20170104','%Y%m%d')) (cost=987.60 rows=900) (actual time=0.475..3.152 rows=900 loops=1)
위 SQL의 경우, 실행계획에는 T_ORD_BIG_X1만 사용한 것으로 표시되어 있습니다. 하지만 실제로는, T_ORD_BIG 테이블의 클러스터드 인덱스도 사용하고 있습니다.
위 SQL의 결과를 만들기 위해서는 ORD_DT가 2017년 1월 4일인 로우들의 모든 값이 필요합니다.
이 값들은 클러스터드 인덱스에 저장되어 있습니다. 그러므로 내부적으로 클러스터드 인덱스를 경유해야만 위 SQL의 결과를 처리할 수 있습니다. 안타깝게도, MySQL의 실행계획에서는 해당 작업을 별도로 표시해 주지는 않습니다.
위 SQL을 처리한 과정을 그려보면 아래와 같습니다.
1번, 2번은 넌 클러스터드 인덱스로 WHERE 조건에 해당하는 값의 클러스터드 인덱스의 키 값(ORD_SEQ)을 찾는 과정입니다.
3번은 2번에서 찾은 ORD_SEQ를 사용해, 클러스터드 인덱스를 검색하러 가는 과정입니다.
4번, 5번은 3번의 ORD_SEQ를 사용해 실제 데이터를 찾아오는 과정입니다.
논리적으로 3번, 4번, 5번 작업은 2번에서 찾은 건수만큼 반복 실행됩니다. 내부 매커니즘을 통해 반복 3, 4, 5번의 반복 횟수를 줄일 것이라 예상되지만, 논리적으로는 반복 실행된다고 생각하면 됩니다.
오늘 살펴볼 내용은 여기까지입니다.
자 그러면, 우리는 SELECT 절에 무턱대고 '*'를 사용해서는 안되겠죠. '*'를 처리하기 위해서는 실제 데이터의 접근이 발생할 수 밖에 없으니까요.
또한 테이블의 접근(클러스터드 인덱스 접근)을 피하기 위해 무턱대고 넌클러스터드 인덱스의 컬럼을 늘릴 수는 없겠죠. 이는 CUD 작업의 성능 저하로 이어지니까요. 그래서 인데스 설계가 어렵습니다.
그런데, MySQL은 좀 더 어렵습니다. 이와 같은 넌클러스터드와 클러스터드 구조를 반드시 머리에 넣고 성능을 고려한 인덱스를 설계를 해야 하니까요.
아래와 같이 ORD_DT에 범위 조건을 사용해 2017년 1월 4일 하루치의 데이터만 조회하는 SQL을 작성하고 EXPLAIN 처리합니다. 얻은 실행 계획을 보면, type 부분을 보면 range 라고 되어 있는 것을 알 수 있습니다.
type의 range는 범위 조건 처리를 위해 인덱스가 사용되어진 것을 뜻합니다. 어떤 인덱스를 사용했는지는 key 부분에 명시되어 있습니다. 또한 가장 오른쪽 Extra 항목을 보면, Using index라고 추가적으로 명시되어 있습니다.
EXPLAIN
SELECT COUNT(*)
FROM MYTUNDB.T_ORD_BIG T1
WHERE T1.ORD_DT >= STR_TO_DATE('20170104','%Y%m%d')
AND T1.ORD_DT < STR_TO_DATE('20170105','%Y%m%d');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- --------------------------
1 SIMPLE T1 None range T_ORD_BIG_X1 T_ORD_BIG_X1 6 None 900 100.0 Using where; Using index
이번에는 ORD_DT에 같다(=) 조건을 사용해 2017년 1월 4일 하루치 데이터를 조회하도록 합니다. 실행계획을 살펴보면 아래와 같습니다. 이번에는 type이 ref로 표시되어 있습니다. 이처럼 같다(=) 조건에 인덱스가 사용되면 ref라고 표시가 됩니다. 위에서 살펴본 것과 마찬가지로 key에는 어떤 인덱스를 사용했는지가 표시되어 있고, Extra에도 Using index라고 표시 되어 있습니다.
EXPLAIN
SELECT COUNT(*)
FROM MYTUNDB.T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170104','%Y%m%d');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
---- ------------- ------- ------------ ------ --------------- -------------- --------- ------- ------ ---------- -------------
1 SIMPLE T1 None ref T_ORD_BIG_X1 T_ORD_BIG_X1 6 const 900 100.0 Using index
마지막으로, ORD_DT 컬럼을 변형해 인덱스를 효율적으로 사용할 수 없도록 쿼리를 작성해 실행해봅니다. 실행계획을 확인해 보면, 아래와 같이 type 부분에 index라고 표시되어 있습니다. 이를 보고, '인덱스를 잘 타고 있구나'라고 착각하면 안됩니다. 인덱스를 사용한 것은 맞지만, 효율적으로 사용한 케이스는 아닙니다.
Key에는 어떤 인덱스를 사용했는지, Extra에 using index 라고 표시되어 있지만, 인덱스 리프 노드를 모두 스캔하는 Index full scan을 하고 있는 동작입니다.
EXPLAIN
SELECT COUNT(*)
FROM MYTUNDB.T_ORD_BIG T1
WHERE DATE_FORMAT(T1.ORD_DT,'%Y%m%d') = '20170104'
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ -------- ---------- --------------------------
1 SIMPLE T1 None index None T_ORD_BIG_X1 6 None 301158 100.0 Using where; Using index
고전적인 실행 계획으로 살펴봤을 때, 인덱스를 잘 사용하고 있는지는 type 항목을 보셔야 합니다. type 항목 값에 따라 아래와 같이 정리할 수 있습니다.
이번에는 실제 MySQL에서 인덱스 관련된 실행 게획을 어떻게 표현하는지 살펴보도록 하겠습니다.
1. MySQL의 인덱스 실행 계획: EXPLAIN FORMAT = TREE
EXPLAIN FORMAT = TREE로 지정했을 때 인덱스 관련 동작이 어떻게 표현되는지 알아보겠습니다.
이번 글에서는 EXPLAIN FORMAT = TREE 기준을 설명하고, 다음 글에서는 고전적인 실행게획에서 인덱스 관련 동작이 표시되는 방법을 설명할 예정입니다.
아래와 같이 SQL을 실행합니다. 아래 SQL은 ORD_DT에는 범위 조건을 사용했습니다.(범위 조건을 사용해 2017년 1월 4일 하루만 조회합니다.)
앞에서 T_ORD_BIG 테이블의 ORD_DT 컬럼에 대해서는 T_ORD_BIG_X1 인덱스를 생성했습니다. 그러므로 아래 SQL은 해당 인덱스를 사용해 처리가 될 확률이 높습니다. 실행계획을 살펴보면, 가장 아래에 'Index range scan'이 표시되어 있습니다. 그리고 'using T_ORD_BIG_X1'이라고 어떤 인덱스를 사용했는지 친절하게 표시되어 있습니다. MySQL이 버젼이 올라갈수록, 실행계획을 해석하기 좋게 보여주고 있습니다.
EXPLAIN FORMAT = TREE
SELECT COUNT(*)
FROM MYTUNDB.T_ORD_BIG T1
WHERE T1.ORD_DT >= STR_TO_DATE('20170104','%Y%m%d')
AND T1.ORD_DT < STR_TO_DATE('20170105','%Y%m%d');
-> Aggregate: count(0)
-> Filter: ((mytundb.t1.ORD_DT >= <cache>(str_to_date('20170104','%Y%m%d'))) and (mytundb.t1.ORD_DT < <cache>(str_to_date('20170105','%Y%m%d')))) (cost=182.54 rows=900)
-> Index range scan on T1 using T_ORD_BIG_X1 (cost=182.54 rows=900)
이번에는 ORD_DT에 범위 조건이 아닌, 같다(=) 조건을 사용한 SQL을 실행해봅니다. (위 SQL과 똑같이 2017년 1월 4일 하루만 조회합니다.) 아래와 같슽니다. 실행계획을 살펴보면 'Index range scan'이 사라지고, 'Index lookup'이 표시되었습니다.
EXPLAIN FORMAT = TREE
SELECT COUNT(*)
FROM MYTUNDB.T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170104','%Y%m%d');
-> Aggregate: count(0)
-> Index lookup on T1 using T_ORD_BIG_X1 (ORD_DT=str_to_date('20170104','%Y%m%d')) (cost=92.53 rows=900)
'Index lookup' 역시 'Index range scan' 처럼 수직점 탐색과 수평적 탐색이 발생합니다. 그러므로 결국 실제 작동하는 내용은 Index range scan과 같다고 보시면 됩니다.
MySQL은 인덱스를 사용하는 상황에서, 사용된 조건이 범위(>=, <=, BETWEEN) 조건이면 Index range scan을, 같다(=) 조건이면 Index lookup으로 실행계획을 표시해줍니다.
이번에는 아래와 같이 SQL을 실행합니다. WHERE 절에서 ORD_DT 컬럼을 DATE_FORMAT 처리했기 때문에 인덱스를 '효율적'으로 사용 못하는 경우입니다.(인덱스를 사용은 하지만 비효율적인 방법으로 사용하게 됩니다.) 실행계획을 보면 'Index scan'이라고 표시되어 있습니다. 이 부분만 보면, '인덱스를 탔네'라고 생각할 수 있습니다. 하지만, 아래 경우는 인덱스의 리프 노드를 모두 읽어서 조건을 처리한 경우입니다.
EXPLAIN FORMAT = TREE
SELECT COUNT(*)
FROM MYTUNDB.T_ORD_BIG T1
WHERE DATE_FORMAT(T1.ORD_DT,'%Y%m%d') = '20170104'
-> Aggregate: count(0)
-> Filter: (date_format(mytundb.t1.ORD_DT,'%Y%m%d') = '20170104') (cost=31559.80 rows=301158)
-> Index scan on T1 using T_ORD_BIG_X1 (cost=31559.80 rows=301158)
방금 살펴본, SQL 세 개를 EXPLAIN ANALYZE를 사용해 각각 실행해봅니다. 아래와 같은 실제 실행 계획을 확인할 수 있습니다.
1. 범위 조건 ANALYZE
-> Aggregate: count(0) (actual time=0.462..0.462 rows=1 loops=1)
-> Filter: ((mytundb.t1.ORD_DT >= <cache>(str_to_date('20170104','%Y%m%d'))) and (mytundb.t1.ORD_DT < <cache>(str_to_date('20170105','%Y%m%d')))) (cost=180.64 rows=900) (actual time=0.034..0.407 rows=900 loops=1)
-> Index range scan on T1 using T_ORD_BIG_X1 (cost=180.64 rows=900) (actual time=0.032..0.241 rows=900 loops=1)
2. 같다(=) 조건 ANALYZE
-> Aggregate: count(0) (actual time=0.383..0.383 rows=1 loops=1)
-> Index lookup on T1 using T_ORD_BIG_X1 (ORD_DT=str_to_date('20170104','%Y%m%d')) (cost=90.63 rows=900) (actual time=0.042..0.316 rows=900 loops=1)
3. 컬럼변경(DATE_FORMAT) ANALYZE
-> Aggregate: count(0) (actual time=200.378..200.378 rows=1 loops=1)
-> Filter: (date_format(mytundb.t1.ORD_DT,'%Y%m%d') = '20170104') (cost=31559.80 rows=301158) (actual time=1.075..200.319 rows=900 loops=1)
-> Index scan on T1 using T_ORD_BIG_X1 (cost=31559.80 rows=301158) (actual time=0.108..85.289 rows=304700 loops=1)
위 내용을 보면 '1. 범위 조건' 보다 '2. 같다(=) 조건'이 아주 아주 약간 더 빠릅니다.(각 실행계획 가장 위의 actual time) 거의 사람이 차이를 느낄 수 없는 속도일듯합니다. 실제 실행해 보면 1번과 2번 모두 0.000초 정도입니다. 다만, '1. 범위 조건'의 경우는 Filter 라는 단계가 실행계획 중간에 끼어 들어옵니다. '3. 컬럼 변경'의 경우만 0.2초 정도의 시간이 걸립니다.
정리하면 아래와 같습니다.
범위 조건을 인덱스로 처리하면, 실행 계획에 Index range scan 으로 표시된다.
같다 조건을 인덱스로 처리하면, 실행 계획에 Index lookup 으로 표시된다.
Index range scan과 Index lookup은 수직적 탐색과 수평적 탐색이 발생하는 같은 작업이라고 볼 수 있습니다.
Index scan은 인덱스 리프 노드를 순차적으로 모두 읽는 작업이다.
당연히, Index scan은 Index range scan이나 Index lookup 보다 성능이 좋지 못할 수 있다.
앞에서 클러스터드 인덱스와 넌클러스터드 인덱스의 차이점에 대해서 설명했습니다. 잠시, 클러스터드와 넌클러스터드의 차이는 잠시 접어두고 인덱스를 이용해 데이터 찾는 과정을 살펴보겠습니다.
1. 인덱스 탐색
B Tree(B+Tree) 인덱스에서 조건에 맞는 값을 찾기 위해서는 두 단계의 과정으로 인덱스를 탐색합니다.
1 단계: 루트 노드에서 리프 노드로 내려가기(수직적 탐색)
2 단계: 리프 노드를 순차적으로 읽어 가기(수평적 탐색)
위의 과정을 그림으로 살펴보면 아래와 같습니다. 그림의 빨간색 화살표가 1 단계인 수직적 탐색이고, 파란색 화살표가 2 단계인 수평적 탐색입니다.
경우에 따라 2 단계는 생략될 수도 있습니다. 찾고자 하는 값이 한 건이거나, 극히 일부라면 2 단계 과정인 수평적 탐색은 생략될 수도 있습니다. (1 단계인 수직적 탐색으로 찾은 곳에 원하는 데이터가 모두 있다면, 수평적 탐색은 필요가 없는 경우죠.)
위 그림을 더 간략하게 그려보면 아래와 같습니다. 인덱스 동작 관련해서 자주 보게될 그림입니다.
방금 살펴본 설명과 그림이 바로, 우리가 흔이 말하는 '인덱스를 탔네'라고 말하는 것의 동작 방식입니다. 오라클의 실행 계획에서는 이와 같은 동작을 'INDEX RANGE SCAN'이라고 표현합니다. 반면에 MySQL에서는 실행 계획을 보는 방식과 상황에 따라 다양하게 표현이 됩니다. 관련해서는 다음 글에서 설명을 드릴 예정입니다.
위와 같은 인덱스 탐색 외에도, 인덱스 리프 노드를 차례대로 모두 읽어야 하는 경우가 있습니다. 해당 경우는 아래 그림과 같이 표시합니다. 오라클에서는 INDEX FULL SCAN이라고 동작하는 경우입니다.
이번에는 클러스터드 인덱스의 구조를 살펴볼 차례입니다. 넌클러스터드와 클러스터 인덱스의 다음 차이점을 반드시 기억하시고 읽어주시기 바랍니다.
넌클러스터드 인덱스: 리프 노드에 실제 데이터를 찾아 갈 수 있는 '주소 값'이나 '키 값'이 저장된 인덱스
클러스터드 인덱스: 리프 노드에 실제 '데이터'가 저장된 인덱스
2. Clustered Index
T_ORD_BIG의 클러스터드 인덱스는 Primary 인덱스입니다. 앞에 글에서도 'SHOW INDEX FROM T_ORD_BIG' 을 실행했을 때, 'Key_name'이 'Primary'로 되어 있는 인덱스가 바로 클러스터드 인덱스라고 설명했습니다.
여기서 잠깐, MySQL에서는 인덱스라는 용어를 Key라는 용어와 혼용해서 사용합니다.
그러므로 'SHOW INDEX FROM'을 실행하면 인덱스의 이름 항목이 'Index_name'이 아닌 'Key_name'으로 나옵니다.
T_ORD_BIG의 클러스터드(Primary) 인덱스는 PK(Primary Key) 컬럼인 ORD_SEQ로 구성되어 있습니다.
MySQL의 InnoDB 스토리지 엔진에서, 테이블의 PK 컬럼은 무조건 클러스터드 인덱스로 만들어집니다. 다시 말해, '테이블 생성 = 클러스터드 인덱스 생성'이 됩니다. 우리는 테이블을 만들지만, 테이블의 데이터는 클러스터드 인덱스 구조로 관리가 되는겁니다. 아무리, 클러스터드 구조로 테이블을 만들기 싫어도 방법이 없습니다.
클러스터드 인덱스를 피하기 위해 PK를 설정하지 않아도, 내부적으로 6 byte의 Hidden 컬럼을 만들어 PK를 구성한다고 합니다.
(구글링을 해보면, 이와 같은 이야기가 나오는데, MySQL 공식 레퍼런스에서는 이와 같은 부분을 찾을수가 없네요. 혹시 MySQL 레퍼런스에서 보신분 있으면 알려주세요!^^)
T_ORD_BIG의 클러스터드 인덱스를 그려보면 아래 그림과 같습니다. 처음에 이야기했듯이, 인덱스의 리프 노드에는 실제 데이터가 저장되어 있는 것을 알 수 있습니다.
클러스터드 인덱스의 리프 노드에는 실제 데이터가 저장되어 있으므로, 테이블 별로 하나만 만들 수 있습니다. 넌 클러스터드는 하나의 테이블에 여러개 만들 수 있지만, 클러스터드는 무조건 하나만 만들 수 있으므로 클러스터할 컬럼 선택에 신중한 선택이 필요합니다. 하지만 MySQL은 무조건 PK 컬럼에 클러스터드를 설정하므로 선택의 폭이 제한적입니다.
클러스터드 인덱스와 같은 개념으로 오라클에는 IOT가 있습니다. 오라클의 IOT는 선택이라면, MySQL의 클러스터드 인덱스는 강제적입니다.
MS-SQL Server에도 클러스터드 인데스가 있습니다. MS-SQL Server에서 클러스터드 인덱스는 PK 컬럼이 아닌 곳에도 설정할 수 있습니다. 반면에 MySQL에서는 무조건 PK 컬럼에만 클러스터드 인덱스가 설정이 됩니다.
넌클러스터드(Non-Clustered) 인덱스와 클러스터드(Clustered) 인덱스에 대해 알아볼 차례입니다. MySQL의 인덱스와 관련된 성능에서 가장 중요한 부분이 아닐까 싶습니다.
앞에서 B Tree 인덱스에 대해 설명드렸는데, B Tree는 인덱스를 만들때 사용하는 기본적인 자료구조입니다. MySQL에서는(정확히는 MySQL의 InnoDB 스토리지 엔진) B Tree 인덱스를 넌클러스터드와 클러스터드로 세분화해서 사용할 수 있습니다. 넌클러스터드와 클러스터드는 리프 노드의 구성에 따라 구분됩니다. 아래와 같습니다.
넌클러스터드 인덱스: 리프 노드에 실제 데이터를 찾아 갈 수 있는 '주소 값'이나 '키 값'이 저장된 인덱스
클러스터드 인덱스: 리프 노드에 실제 '데이터'가 저장된 인덱스
1. Non Clustered Index
우선은 넌클러스터드인덱스를 설명하고, 다음 글에서 클러스터드 인덱스에 대해 살펴볼 예정입니다. 앞의 글에서 T_ORD_BIG의 ORD_DT 컬럼에 대해 인덱스를 생성했습니다. 이 인덱스는 넌클러스터드 인덱스입니다.
아래와 같이 SHOW INDEX FROM 을 사용해 현재 테이블에 생성된 인덱스를 확인할 수 있습니다.
SHOW INDEX FROM MYTUNDB.T_ORD_BIG;
위 SQL을 실행해 결과를 확인해 보면, T_ORD_BIG_X1 인덱스가 넌클러스터드인지, 클러스터드인지 구분되어 있지는 않습니다. 위 SQL 결과 중, Key_name 항목의 결과가 Primary면 클러스터드 인덱스이고 Primary가 아니면 모두 넌 클러스터드 인덱스로 생각하시면 됩니다.
MySQL에서는 인덱스를 생성할 때 클러스터드와 넌클러스터드를 지정할 수 없습니다.
테이블을 생성할 때 지정한 Primary Key 외에 추가로 생성하는 인덱스는 모두 넌클러스터드 인덱스 입니다.
T_ORD_BIG의 ORD_DT에 대한 인덱스를 그림으로 표현해보면 아래와 같습니다.
위 그림에서, 가장 아래 리프 노드를 보면, 인덱스 컬럼인 ORD_DT외에 ORD_SEQ 정보도 존재합니다. ORD_SEQ는 T_ORD_BIG의 PK(Primary Key) 컬럼으로서 클러스터드 인덱스로 구성된 컬럼입니다. 이처럼 MySQL의 넌클러스터드 인덱스의 리프 노드에는 해당 인덱스의 컬럼과 함께 PK의 컬럼 값이 존재합니다. (PK 컬럼 값은 실제 데이터를 찾기 위한 '키 값'으로 사용됩니다.) 반면에 오라클의 넌클러스터드 인덱스의 리프 노드에는 실제 데이터를 바로 찾아 갈 수 있는 주소 값(ROWID)이 저장되어 있습니다.
MySQL은 아래와 같은 구조의 인덱스를 제공합니다. 정확히는 MySQL의 스토리지 엔진중 하나인 InnoDB에서 사용할 수 있는 인덱스입니다.MySQL에는 InnoDB 외에도 다양한 스토리지 엔진도 사용할 수 있지만, 대부분 InnoDB를 사용합니다.
B Tree 인덱스: 원하는 데이터를 빠르게 검색하기 위한 인덱스
R Tree 인덱스(Spatial Index): 위치나 거리등의 검색에 효율적인 인덱스
Full text search 인덱스: 문자열 검색에 효율적인 인덱스
RDBMS에서 흔이 말하는 '인덱스' 대부분은 B Tree 구조의 인덱스입니다. (이 글에서도 특별히 언급하지 않으면 B Tree 인덱스를 뜻합니다.) 마찬가지로, 인덱스를 만들때 특별히 옵션을 주지 않으면 B Tree 구조의 인덱스가 만들어집니다. 앞에 글(인덱스 왜 빨라)에서 예를 든 구조(거래처 서류와 서류함) 역시 B Tree 인덱스입니다.
실제 RDBMS에서 사용하는 인덱스 구조는 B Tree를 좀 더 발전시킨 B* Tree 또는 B+ Tree 입니다. B Tree, B* Tree, B+ Tree에 대한 차이는 아래 잘 정리된 블로그 글로 대신하도록 하겠습니다. 한 번쯤 읽어보시고 각자 정리해보시기 바랍니다. (사실 아래 글만으로 정리가 안될 수도 있습니다. 대략적인 차이를 이해하는 정도에서 넘어가시는게 좋습니다.)
아래의 MySQL 레퍼런스에 의하면, MySQL은 B Tree 구조를 사용한다고 설명되어 있습니다. B+나 B*를 직접적으로 언급하는 부분은 없는거 같습니다. 다만, 많은 책들과 자료에서 MySQL의 인덱스는 B+ Tree라고 설명되어 있습니다.(저 역시 B+ Tree가 맞다고 생각합니다.)
아래 그림을 통해 B Tree(B+ Tree)를 구성하는 요소별 명칭을 정리해봅니다. 아래 그림은 앞의 글에서 거래처와 서류함을 B Tree 인덱스로 표현한 것으로, '거래처명'으로 B Tree 인덱스를 생성한 경우입니다.
Tree의 가장 위에는 트리 탐색을 시작하는 루트(Root, 뿌리) 노드가 위치합니다. 루트 노드는 하나의 인덱스에 오직 하나만 존재합니다. 루트 아래에는 브런치(Branch, 가지, 또는 미들(Middle)) 노드가 위치합니다. 브런치 노드는 루트에서 가장 아래에 있는 리프(Leaf) 노드를 찾아가는 중간 노드로서, 여러 층이 존재할 수 있습니다.(그림에서는 편의상 한 층만 표현했습니다.) 마지막으로 Tree의 가장 아래에는 리프(Leaf, 잎사귀) 노드가 존재합니다. 리프에는 실제 데이터가 존재하거나 실제 데이터가 위치한 주소 값을 저장합니다. 그리고 리프 노드를 보면 왼쪽부터 알파벳 순서대로 데이터가 입력되어 있으며 근접한 리프 노드끼리는 서로 연결되어 있습니다.
그림을 전체적으로 보면, 나무 뿌리가 가장 위에 있고, 나무 잎사귀가 가장 아래에 있습니다. 나무를 뒤집어서 그린 모양이라고 이해하시면 됩니다.
B Tree 인덱스(B+ Tree) 관련해서 중요한 내용을 정리해보면 아래와 같습니다.
'루트(뿌리), 브런치(가지, 미들), 리프(잎)' 세 개의 요소로 나누어진다.
리프에는 실제 데이터 또는 실제 데이터가 존재하는 주소 값을 가지고 있다.
리프 노드의 데이터는 정렬되어 있다.
근접한 리프 노드는 서로 연결되어 있다.
오늘 살펴볼 내용은 여기까지입니다. B Tree와 같은 자료 구조 이야기가 나와 조금 어려울 수 있지만, 한 번쯤은 깊이 공부해볼 필요가 있는 내용입니다.
'인덱스를 만들면 조회가 빨라지더라~'라는 것에 대해서는 이미 앞에서 살짝 테스트를 해봤습니다. 인덱스를 마스터하려면 인덱스의 종류, 구조등 다양한 내용을 공부해야 합니다. 이와 같은 복잡한 내용은 천천히 기회가 될 때 살펴보도록 하고, 우선은 '왜 .. 인덱스를 만들면 빨라지는거야?'에 대해서 개념을 살짝 잡아볼까 합니다.
1. 인덱스 왜 빨라?
회사에 거래처가 아래 그림 좌측과 같이 12개가 있다고 가정해 보겠습니다. 거래처별 거래 서류를 정리하기 위해, 아래 그림 오른쪽과 같은 네 칸으로 구분된 거래처 서류함을 사용하려고 합니다. '나신입'님께 정리를 부탁했더니, 거래처 서류를 아무렇게나 세 개씩 나누어서 서랍에 넣습니다. 다시 말해, Airbnb 서류가 1번 칸에 있는지, 2번 칸에 있는지 알 수가 없습니다. 필요한 거래처 서류가 어디있는지는 서랍을 열어야만 알수 있는 상황이 된거죠. 필요한 거래처 서류를 운이 좋으면 한 번에 찾을 수 있지만, 운이 나쁘면 서랍 네개를 모두 열어야만 찾을 수 있습니다.
필요한 서류 하나를 찾을 때마다 서랍을 몇 번씩 열고 닫아야 합니다. 매우 비효율적이죠. 거기에 서랍을 열고 닫는 소리도 시끄럽고요.
도저히 참을 수 없어 '나경력'님께 재정리를 부탁해 봅니다. '나경력'님은 거래처 서류를 모두 꺼내서 알파벳 순서대로 정렬 한 후에, 세 개씩 나누어서 다시 서류함 서랍에 넣습니다. 그리고 서류함 서랍마다 어떤 알파벳으로 시작하는 거래처 서류가 있는지 라벨링합니다.아래 그림과 같이 말이죠
위 그림과 같이 정리한 결과, 필요한 거래처 서류를 찾기 위해서는 서랍을 한 번만 열면 됩니다. Airbnb 거래처 서류는 당연히 1번 서랍에 있고, Tesla 서류는 당연히 4번 서랍에 있다는 것을 누구나 알 수 있습니다.(알파벳 순서만 알고 있다면요) 다시 정리해보면, 거래처 서류를 알파벳 순서로 정렬해 차례대로 서랍에 넣는다. 그리고 서랍에는 라벨링을 해 놓는다. 이것이 바로 인덱스의 원리입니다. "정렬된 구조로 만들어서 필요한 데이터를 빠르게 찾을 수 있게 만든다." 이 정도면, 인덱스 구조가 왜 빠른가에 대한 이해가 되지 않을까 싶습니다.
마지막으로 아래 그림을 살펴볼까요. 정렬된 서류와 서류함을 옆으로 돌리면 삼각형 구조의 모양이 되는 것을 알 수 있습니다. 이처럼 성능 관련해서 인덱스를 설명할 때는 삼각형 도형을 많이 사용한다는 것도 알아놓으시면 좋을거 같습니다.
MySQL에서 테이블에 어떤 인덱스가 있고, 인덱스를 생성하거나 제거할 때 어떤 문법으로 처리하는지 살펴보겠습니다.
1. 인덱스 확인하기
인덱스는 테이블에 종속적입니다. 아래와 같은 명령어로 해당 테이블에 어떤 인덱스가 있는지 확인할 수 있습니다.
SHOW INDEX FROM MYTUNDB.T_ORD_BIG;
2. 인덱스 생성하기
MySQL에서는 인덱스를 생성하는 두 가지 문법이 있습니다. 하나는 CREATE INDEX이고, 또다른 하나는 ALTER TABLE을 통해 테이블에 인덱스를 추가하는 방법입니다. 아래와 같습니다. (아마도 이미 인덱스가 있다면 생성이 안되고 에러가 나겠죠.)
# CREATE INDEX
CREATE INDEX T_ORD_BIG_X1 ON MYTUNDB.T_ORD_BIG(ORD_DT);
# ALTER TABLE로 인덱스 추가
ALTER TABLE MYTUNDB.T_ORD_BIG ADD INDEX T_ORD_BIG_X1(ORD_DT);
3. 인덱스 제거하기
인덱스를 제거하는 문법 역시 두 가지입니다. 아래와 같습니다.
# DROP INDEX
DROP INDEX T_ORD_BIG_X1 ON MYTUNDB.T_ORD_BIG;
# ALTER TABLE로 인덱스 제거
ALTER TABLE MYTUNDB.T_ORD_BIG DROP INDEX T_ORD_BIG_X1;
