DataBase/SQL

SQL BOOSTER - 복합인덱스

now0204 2024. 6. 12. 15:13

 

복합 인덱스 - 컬럼 순정과 순서 

 

A,B,C 칼럼 순서의 복합 인덱스와 C,B,A 컬럼 순서의 복합 인덱스는 완전히 다른 인덱스다 

 

복합 인덱스를 만들 때 가장 중요한 것은 인덱스를 구성하는 컬럼 순서다. 

어떤 순서로 컬럼을 정하는 지에 따라 강력한 성능을 발휘할 수도 있으며, 도움이 안 될 수도 있다. 

 

이때, 컬럼 순서를 정할 때 관심 있게 봐야하는 것은 WHERE절의 조건이다. 

  • 같다 조건이 사용된 컬럼을 복합 인덱스에 앞 부분에 두는 것이 기본 원칙이다. 
CREATE INDEX X_T_ROD_BIG_$ ON T_ORD_BIF(CUS_ID,ORD_YMD);

SELECT /*+GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_3) */
T1.ORD_ST, COUNT(*)
FROM T_ORD_BIG T1
WHERE T1.ORD_YMD LIKE '201703%'
AND T1.CUS_ID = 'CUS_0075'
GROUP BY T1.ORD_ST;
  • CUS_ID가 = 조건이므로, 복합 인덱스에 앞에 두었다.

SQL BOOSTER

 

INDEX RANGE SCAN의 Bufferes를 보자, 이전보다 약 7000블록 정도의 IO를 개선했다. 

 

SQL BOOSTER

 

그림 가운데 인덱스의 리프 블록 데이터를 보면, ORD_YMD 순서로 먼저 정렬하고, CUS_ID로 정렬한 것을 볼 수 있다. 

 

선두 컬럼 ORD_YMD는 LIKE 조건이 사용되었다. INDEX RANGE SCAN에서 가장 먼저 할 일은 루트에서 검색을 시작할 리프를 찾아가는 것이다. 근데, 복합 인덱스의 선두 컬럼이 범위 조건으로 되면, 인덱스의 두 번째 컬럼은 리프 검색 위치 알아내는데 관여하지 못한다. 

 

또한 찾고자하는 CUS_ID가 ORD_YMD별로 흩어져 있으므로, 스캔 과정에서 CUS_ID도 읽는 비효율이 발생한다. 

 

SQL BOOSTER

 

그림 가운데 리프 블록을 보면 CUS_ID 순서로 먼저 정렬되고, ORD_YMD순서로 정렬되어 있다. 

복합 인덱스의 선두 컬럼이 같다 조건으로 사용되어야만 두 번째 컬럼도 리프 블록 검색 시작 위치를 찾는데 관여할 수 있다. 

 

리프의 시장위치를 보면, CUS_0075이면서 ORD_YMD가 20170301이다. 

SQL BOOSTER

 

조인이 포함되면, 조인 조건까지 생각해서 인덱스 칼럼 순서를 결정해야한다. 

위 공식을 외우지말고, 인덱스에 따라 IO가 어떻게 변경되는지 추적해보면서 최적의 인덱스를 찾아야한다. 

 

컬럼 선정과 순서 (2)

 

이번에는 ORD_YMD가 같다 조건, CUS_ID에는 범위 조건이 사용된 SQL을 살펴보자면, 

하나의 SQL에는 효율적이고, 다른 SQL에는 비율적일 수 있다. 따라서 SQL에 따라 적절한 인덱스를 사용해야한다.

 

컬럼 선정과 순서(3)

 

SELECT T1.ORD_ST,COUNT(*)
FROM T_ORD_BIG T1
WHERE T1.ORD_YMD LIKE '2017%4'
AND T1.CUS_ID = 'CUS_0042'
AND T1.PAY_TP = 'BANK'
GROUP BY T1.ORD_ST;

3개 조건이 사용된 SQL에 복합인덱스의 컬럼 후보로 ORD_YMD,CUS_ID,PAY_TP가 있다.

같다 조건이 사용된 컬럼이 선두로 오면 좋기 때문에 다음 케이스를 고민할 것이다.

 

1. CUS_ID,PAY_TP,ORD_YMD

2. PAY_TP,CUS_ID,ORD_YMD

 

위 SQL 성능만 해결하고자 한다면, 1번과 2번 둘 중 어느 순서로 만들어도 상관없다.

다만, 이럴땐 다른 SQL도 신경써두자 

 

SELECT 'X'
FROM DUAL A
WHERE EXISTS(

	SELECT * 
    FROM T_ORD_BIG T1
    WHERE T1.CUS_ID = 'CUS_0042'
);

 

EXISTS 절에서 CUS_ID컬럼을 같다 조건으로 사용하고 있다. 시스템에 이와 같은 SQL도 있다면, 1번 순서가 더 아능 ㄴ선택이다. 

오라클에는 INDEX SKIP SCAN 기능이 있어, 복합 인덱스의 가운데 컬럼을 인덱스 검색에 어느정도 활용할 수 있다.

 

또한, 업무적으로 CUS_ID가 같다 조건으로 사용될 사능성이 매우 높다 그러므로, CUS_ID 컬럼을 복합 인덱스의 선두에 놓는 것이 좋다 

 

컬럼 선정과 순서 (4)

 

SELECT COUNT(*)
FROM T_ORD_BIG T1
WHERE T1.ORD_AMT = 2400
AND T1.PAY_TP = 'CARD'
AND T1.ORD_YMD = '20170406'
AND T1.ORD_ST = 'COMP'
AND T1.CUS_ID = 'CUS_0036';

-- 해당 SQ의 인덱스를 고르고 싶다면 조건별로 카운트 해보자 

SELECT 'ORD_AMT' COL, COUNT(*) FROM T_ORD_BIG T1 WHERE T1.ORD_AMT =2400
UNION ALL 
SELECT 'PAY_TP' COL, COUNT(*) FROM T_ORD_BIG T1 WHERE T1.PAY_TP ='CARD'
UNION ALL 
SELECT 'ORD_YMD' COL, COUNT(*) FROM T_ORD_BIG T1 WHERE T1.ORD_YMD ='20170406'
UNION ALL
SELECT 'ORD_ST' COL, COUNT(*) FROM T_ORD_BIG T1 WHERE T1.ORD_ST ='COMP'
UNION ALL
SELECT 'CUS_ID' COL, COUNT(*) FROM T_ORD_BIG T1 WHERE T1.CUS_ID ='CUS_0036'

 

위 SQL에는 다섯 개의 조건이 있다. 

 

SQL BOOSTER

 

어떤 조건이 성능에 도움이 되는지 알 수 있다. ORD_YMD 조건은 90,000건의 데이터를 찾아내고, CUS_ID 조건은 330,000건의 데이터를 찾아낸다. 아마도 ORD_YMD와 CUS_ID로 복합 인덱스를 구성하면 충분히 성능이 나올 것이다.

다른 조건들은 성능에 큰 도움이 안된다. 

 

이를 토대로 인덱스를 만들고, SQL을 실행하면 다음과 같은 실행계획이 나온다. 

 

SQL BOOSTER

 각 단계의 A-Rows를 살펴보면 INDEX RANGE SCAN단계와 TABLE ACCESS BY INDEX ROWID가 모두 10000이다.

인덱스에서 찾아낸 데이터 모두가 최종 결과에 포함되었으므로, 비효율이 전혀 없다. 

 

결론

 

  • 같다 조건이 사용된 컬럼이 복합 인덱스의 앞부분에 위치해야 한다.
  • 인덱스를 만들 때, 해당 테이블에 대한 SQL 전체를 검토하도록 한다.(모든 SQL을 검토할 수 없다면, 최대한 많은 SQL을 검토한다)
  • 조건에 사용된 모든 컬럼을 무조건 복합 인덱스에 추가해서는 안된다. (성능에 도움이 되는 조건 컬럼만 선별해서 복합 인덱스를 구현하도록 한다.)