Search

세 가지 가능성을 열어주는 인덱스 전략

아래의 [SQL-1]을 위한 인덱스 전략은 정말 다양하다. 다양한 방법 중에, 세 가지 가능성을 열어주는 인덱스 전략에 대해 살펴보려고 한다.
[SQL-1] SELECT * FROM startdbpg.ms_mbr_big t1 INNER JOIN startdbpg.tr_ord_big t2 ON (t1.mbr_id = t2.mbr_id) WHERE t1.nick_nm LIKE 'Air%' AND t2.ord_ymd = '20241221';
SQL
복사
위 SQL은 두 개의 테이블이 조인되고 있다. 두 테이블을 조인하는 방법은, Nest Loop, Merge 또는 Hash 셋 중에 하나다. 여기서는, ms_mbr_big을 드라이빙 또는 빌드 집합으로만 처리한다. 그러므로 tr_ord_big은 드리븐 또는 프로브 집합이 되며, 그에 맞게 인덱스를 만들려고 한다.
tr_ord_big에는 다음의 두 가지 인덱스 전략을 고려해볼 수 있다.
•
tx01: 조인 조건을 처리하는 mbr_id 컬럼을 인덱스 앞쪽에 위치
•
tx02: 조인 조건을 처리하는 mbr_id 컬럼을 인덱스 뒤쪽에 위치
tx01은 조인 조건을 처리하는 mbr_id 컬럼이 앞쪽에, tx02는 해당 컬럼이 뒤쪽에 위치해 있다. (이 외에도 단독 컬럼 인덱스도 고려할 수 있다. 여기서는 단독 컬럼 인덱스는 제외한다.) 아래 스크립트로 인덱스를 생성한다.
CREATE INDEX tr_ord_big_tx01 ON startdbpg.tr_ord_big(mbr_id, ord_ymd); CREATE INDEX tr_ord_big_tx02 ON startdbpg.tr_ord_big(ord_ymd, mbr_id);
SQL
복사
위의 인덱스 중에 tx01을 사용하면, 해당 인덱스는 [SQL-1]에서 Nest Loop 조인에만 적합하다. 다른 조인에는 적절하지 않은 인덱스가 된다. 힌트를 변경해가면서 [SQL-1]을 실행해보면 아래와 같은 실행계획을 얻을 수 있다.
EXPLAIN (ANALYZE,COSTS OFF) /*+ IndexScan(t2 tr_ord_big_tx01) NestLoop(t1 t2) */ SELECT * FROM startdbpg.ms_mbr_big t1 INNER JOIN startdbpg.tr_ord_big t2 ON (t1.mbr_id = t2.mbr_id) WHERE t1.nick_nm LIKE 'Air%' AND t2.ord_ymd = '20241221'; /*+ IndexScan(t2 tr_ord_big_tx01) NestLoop(t1 t2) */ Nested Loop (actual time=0.083..12.321 rows=455 loops=1) -> Seq Scan on ms_mbr_big t1 (actual time=0.015..5.020 rows=2000 loops=1) Filter: ((nick_nm)::text ~~ 'Air%'::text) Rows Removed by Filter: 97990 -> Index Scan using tr_ord_big_tx01 on tr_ord_big t2 (actual time=0.003..0.003 rows=0 loops=2000) Index Cond: (((mbr_id)::text = (t1.mbr_id)::text) AND ((ord_ymd)::text = '20241221'::text)) Planning Time: 0.231 ms Execution Time: 12.363 ms /*+ IndexScan(t2 tr_ord_big_tx01) HashJoin(t1 t2) */ Hash Join (actual time=163.716..798.932 rows=455 loops=1) Hash Cond: ((t2.mbr_id)::text = (t1.mbr_id)::text) -> Index Scan using tr_ord_big_tx01 on tr_ord_big t2 (actual time=0.094..628.891 rows=25560 loops=1) Index Cond: ((ord_ymd)::text = '20241221'::text) -> Hash (actual time=163.587..163.589 rows=2000 loops=1) Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 217kB -> Seq Scan on ms_mbr_big t1 (actual time=108.786..163.255 rows=2000 loops=1) Filter: ((nick_nm)::text ~~ 'Air%'::text) Rows Removed by Filter: 97990 Planning Time: 0.148 ms Execution Time: 799.576 ms /*+ IndexScan(t2 tr_ord_big_tx01) MergeJoin(t1 t2) */ Merge Join (actual time=92.605..683.203 rows=455 loops=1) Merge Cond: ((t1.mbr_id)::text = (t2.mbr_id)::text) -> Index Scan using ms_mbr_big_pk on ms_mbr_big t1 (actual time=92.530..102.095 rows=2000 loops=1) Filter: ((nick_nm)::text ~~ 'Air%'::text) Rows Removed by Filter: 97990 -> Index Scan using tr_ord_big_tx01 on tr_ord_big t2 (actual time=0.059..576.619 rows=25474 loops=1) Index Cond: ((ord_ymd)::text = '20241221'::text) Planning Time: 0.283 ms Execution Time: 683.857 ms
SQL
복사
이번에는, tx02 인덱스를 사용하도록 힌트를 적용해보자. Nest Loop 뿐만 아니라, Merge, Hash 조인에 모두 효율적으로 동작한다.
/*+ IndexScan(t2 tr_ord_big_tx02) NestLoop(t1 t2) */ Nested Loop (actual time=0.077..12.779 rows=455 loops=1) -> Seq Scan on ms_mbr_big t1 (actual time=0.015..6.112 rows=2000 loops=1) Filter: ((nick_nm)::text ~~ 'Air%'::text) Rows Removed by Filter: 97990 -> Index Scan using tr_ord_big_tx02 on tr_ord_big t2 (actual time=0.003..0.003 rows=0 loops=2000) Index Cond: (((ord_ymd)::text = '20241221'::text) AND ((mbr_id)::text = (t1.mbr_id)::text)) Planning Time: 0.253 ms Execution Time: 12.929 ms /*+ IndexScan(t2 tr_ord_big_tx02) HashJoin(t1 t2) */ Hash Join (actual time=6.907..15.992 rows=455 loops=1) Hash Cond: ((t2.mbr_id)::text = (t1.mbr_id)::text) -> Index Scan using tr_ord_big_tx02 on tr_ord_big t2 (actual time=0.030..6.667 rows=25560 loops=1) Index Cond: ((ord_ymd)::text = '20241221'::text) -> Hash (actual time=6.865..6.867 rows=2000 loops=1) Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 217kB -> Seq Scan on ms_mbr_big t1 (actual time=0.008..6.662 rows=2000 loops=1) Filter: ((nick_nm)::text ~~ 'Air%'::text) Rows Removed by Filter: 97990 Planning Time: 0.258 ms Execution Time: 16.040 ms /*+ IndexScan(t2 tr_ord_big_tx02) MergeJoin(t1 t2) */ Merge Join (actual time=5.271..18.340 rows=455 loops=1) Merge Cond: ((t1.mbr_id)::text = (t2.mbr_id)::text) -> Sort (actual time=5.244..5.385 rows=2000 loops=1) Sort Key: t1.mbr_id Sort Method: quicksort Memory: 236kB -> Seq Scan on ms_mbr_big t1 (actual time=0.005..4.996 rows=2000 loops=1) Filter: ((nick_nm)::text ~~ 'Air%'::text) Rows Removed by Filter: 97990 -> Index Scan using tr_ord_big_tx02 on tr_ord_big t2 (actual time=0.020..8.853 rows=25474 loops=1) Index Cond: ((ord_ymd)::text = '20241221'::text) Planning Time: 0.188 ms Execution Time: 18.388 ms
SQL
복사
인덱스 별로, 조인 방식에 따른 실행시간을 정리하면 아래와 같다. tx01을 사용하면 Hash와 Merge 조인에서는 성능상으로 이득이 없음을 알 수 있다.
tx01
tx02
NestLoop
12.363 ms
12.929 ms
Hash Join
799.576 ms
16.040.ms
Merge Join
683.857 ms
18.388 ms
인덱스를 생성할 때는, WHERE 절의 조건을 걸러내는 것만이 전부가 아니다. 조인 방식도 고려해야 한다. tx02와 같은 유연한(?) 방식으로 인덱스를 설계하면, 조인 방식의 선택은 옵티마이져에 맡길 수 있다. 물론, tx01과 같이 인덱스를 구성해 무조건 Nest Loop로 풀리게 해야 할 때도 있다.
왜 tx02는 세 가지 전략이 모두 가능한지는, 인덱스의 원리와 조인 방식의 원리를 추가로 공부해야 한다. 여기서 간단히 설명할만한 부분은 아니다. 중요한 것은 절대로, ‘조인 조건 컬럼을 인덱스 뒤쪽에 두면 된다.’라고 외우지 않는 것이다. [SQL-1]은 이에 효과가 있도록 필자가 SQL을 구성한 것이다. 실무에서 만나는 SQL은 더욱 복잡하고 다양하다. 결론만 외워서 실무에서 써먹을 수 있는 것은 별로 없다. 더 많은 공부를 통해 원리를 이해하기 바란다.
끝으로, 힌트없이 [SQL-1]을 실행하면, Nest Loop 조인으로 tx02 인덱스를 사용한다.
준비한 내용은 여기까지입니다.
데이터 활용이 필요한 모든 분을 위한 SQL 실무서를 소개합니다.
•
SQL 입문자를 위한 실습환경 구성과 SELECT 기초부터
•
SQL 중급자를 위한 GROUP BY와 JOIN, 그리고 분석함수까지!
•
그림과 친절한 설명, 손끝으로 익히는 실습 SQL과 실무형 퀴즈를 더한 올인원 SQL 실무서