[DB] 3장. 인덱스 튜닝(2)

3. 인덱스 스캔 효율화

IOT, 클러스터, 파티션은 테이블 랜덤 액세스를 최소화하는데 효과적인 저장구조이지만, 운영환경에서는 성능 검증을 위해 많은 테스트를 진행해야 하므로 어려움이 있다. 그러므로 운영환경에서 가능한 일반적인 튜닝 기법은 인덱스 컬럼추가를 사용한다. 

🔎 인덱스 탐색

-- 조건절 1 
where C1 = 'B'

-- 조건절 2 (스캔량 줄임)
where C1 = 'B' 
and C2 = 3

-- 조건절 3 (스캔량 줄임)
where C1 = 'B' 
and C2 >= 3

-- 조건절 4 (스캔량 줄임)
where C1 = 'B' 
and C2 <= 3

-- 조건절 5 (스캔량 줄임)
where C1 = 'B' 
and C2 between 2 and 3

-- 조건절 6 (스캔량 줄이는데 역할 못함)
where C1 = between 'A' and 'C' 
and C2 between 2 and 3

 

※ 인덱스 스캔 효율이 좋은지 나쁜지는 알 수 있는 방법은? > SQL트레이스 사용

🔎 액세스 조건과 필터 조건 

 

• 인덱스 스캔하는 단계에 처리하는 조건절은 액세스 조건과 필터조건으로 나눈다.
• 인덱스 액세스 조건 : 인덱스 스캔 범위를 결정하는 조건절
  • 인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는 데 영향을 미치고, 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는 데 영향을 미치는 조건절
• 인덱스 필터조건 : 테이블로 액세스할지를 결정하는 조건절
  • 인덱스를 이용하든, 테이블을 Full Scan 하든, 테이블 액세스 단계에서 처리되는 조건절은 모두 필터 조건이다.
• 테이블 필터 조건 : 쿼리 수행 다음단계로 전달하거나 최종 결과집합에 포함할지 결정 

🔎 비교 연산자 종류와 컬럼 순서에 따른 군집성

• 인덱스에는 '같은 값'을 갖는 레코드들이 서로 모여있다.
• '같은 값'을 찾을 때에는 '=' 연산자를 사용하므로 인덱스 컬럼을 앞쪽부터 누락없이 '연산자'로 조회하면 조건절을 만족하는 레코드는 모두 모여있다.
• 하나가 누락하거나 '='조건이 아닌 연산자로 조회하면 조건절을 만족하는 레코드가 서로 흩어진 상태가 된다.
• 선행컬럼이 모두 '조건'인 상태에서 첫 번째가 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.

--인덱스 구성 컬럼을 모두 '='조건으로 비교할때는 레코드들이 연속해서 모여있음
where C1 = 1 
and C1 = 'A' 
and C3 = '나' 
and C4 = 'a'

--선행컬럼은 모두 '='이고, 맨 마지막 컬럼은 범위검색(Between, like)일때도
레코드들이 연속해서 모여있음
where C1 = 1 
and C1 = 'A' 
and C3 = '나' 
and C4 >= 'a'

--중간 컬럼이 범위검색 조건일경우 C1-C3은 모여 있지만, C4조건까지 만족하는 레코드는 흩어짐
where C1 = 1 
and C1 = 'A' 
and C3 between '나' and '다' 
and C4 = 'a'

--C1-C2까지 두 조건을 만족하는 인덱스 레코드는 서로 모여있지만, C3-C4조건까지 만족하는
레코드는 흩어짐
where C1 = 1 
and C1 <= 'A' 
and C3 = '나' 
and C4 between 'a' and 'b'

--선두 C1컬럼이 범위검색 조건이면 C1조건을 만족하는 레코드는 모여있고 나머지는 흩어짐
where C1 between '1' and '2' 
and C1 = 'A' 
and C3 = '나' 
and C4 = 'a'

🔎 인덱스 선행컬럼이 '='조건이 아닐때 생기는 비효율 

• 인덱스 스캔 효율성은 인덱스 컬럼을 조건절에 모두 '=' 조건으로 사용할때가 가장 좋다.
• 리프블록을 스캔하면서 읽은 레코드는 하나도 걸러지지 않고 모두 테이블 액세스로 이어지므로 인덱스 스캔 단계에서의 비효율은 전혀 없다.
• 인덱스 컬럼 중 일부 조건절에 없거나 '=' 조건이 아니더라도, 뒤쪽 컬럼일 때에는 비효율이 없다.

where 아파트시세코드 = :a
where 아파트시세코드 = :a and 평형 = :b
where 아파트시세코드 = :a and 평형 = :b and 평형타입 = :c
where 아파트시세코드 = :a and 평형 = :b and 평형타입 between :c and :d

• 반면, 인덱스 선행컬럼이 조건절에 없거나 부등호, Between, Like 같은 범위검색 조건이면, 인덱스를 스캔하는 단계에서 비효율이 생긴다.

SELECT 해당층, 평단가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
FROM 매물아파트매매
WHERE 아파트시세코드 = 'A01011350900056'
AND 평형 ='59'
AND 평형타입 ='A'
AND 인터넷매물 BETWEEN '1' AND '3'
ORDER BY 입력일

• 인덱스 선행 컬럼이 모두 '='조건일 때 필요한 범위만 스캔하고, 멈출 수 있는 것은 조건을 만족하는 레코드가 모두 한데 모여 있기 때문이다.

🔎 Between을 IN-List로 전환

• 운영시스템에서 인덱스 구성을 바꾸기는 쉽지 않기에, Between조건을 IN-List로 바꿔주면 큰 효과를 얻을 수 있다.
• IN-List 방식을 사용하기 곤란하다면, 조인문이나 서브쿼리로 구현하면 된다.

select 매물 where 인터넷 매물 between '1' and '3'

--IN-List 사용 
select 매물 where 인터넷 매물 in ('1','2','3')

--union all 사용했을 경우 > IN-List 개수만큼 union all 브랜치가 생성되고, 
각 브랜치마다 모든 컬럼을 '='조건으로 검색하므로, between을 사용했을 때와 같은 비효율이 사라짐
select 매물 where 인터넷 매물 = '1'
union all
select 매물 where 인터넷 매물 = '2'
union all
select 매물 where 인터넷 매물 = '3'

※ Between조건을 IN-List로 전환시 주의 사항
- IN-List 개수가 많지 않아야 한다.
- 인덱스 스캔과정에 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다.
- BETWEEN 조건 때문에 인덱스를 비효율적으로 스캔하더라도 블록 I/O 측면에서는 대개 소량에 그치는 경우가 있다.

🔎 Index Skip Scan 활용

• Index Skip Scan을 활용하면 Between조건을 IN-List조건으로 변환하면 도움이 되는 상황에서 조건절을 바꾸지 않고도 같은 효과를 낼 수 있다.
• 선두컬럼이 Between이고, 나머지 검색조건을 만족하는 데이터들이 서로 멀리 떨어져 있을때, Index Skip Scan 효과가 발휘된다.

⚙️ IN 조건은 '='인가

- IN 조건은 '='이 아니다. IN 조건은 필터 조건이다.

- IN조건이 '='가 되려면 IN-List Iterator 방식으로 풀려야 한다.

- 소트연산을 생략하려면 IN조건절이 IN-List Iterator 방식으로 풀려선 안된다.

즉, IN조건절을 인덱스 액세스 조건으로 사용하면 안되며, 필터 조건으로 사용해야 한다.

 

⚙️ NUM_INDEX_KEYS힌트 활용

- IN-List를 액세스 조건 또는 필터 조건으로 유도하는 방법

/*+ num_index_keys( 인덱스명 )*/

🔎 Between과 Like 스캔범위 비교 

• Between과 Like는 둘다 범위검색 조건이먀, 비효율 원리가 동일하게 적용된다.
• Like보다 Between을 사용하는 게 낫다.

🔎 범위검색조건을 남용할 때 생기는 비효율

• 인덱스 스캔 비효율이 발생한다.
• 대량테이블의 넓은 범위로 검색할때 그영향이 매우 클 수 있다.

---

🔎 OR조건 활용

• 인덱스 선두 컬럼에 대한 옵션조건에 OR조건을 사용해선 안된다.
• 인덱스에 포함하지 않는 컬럼에 대한 옵션 조건은 테이블에서 필터링할 수 밖에 없으므로 OR조건을 사용해도 된다.

• OR조건을 활용한 옵션 조건 처리 
  • 인덱스 액세스 조건으로 사용불가
  • 인덱스 필터조건으로 사용불가
  • 테이블 필터조건으로만 사용가능
  • 단, 인덱스 구성컬럼 중 하나 이상의 Not Null 컬럼이면, 18c부터 인덱스 필터 조건으로 사용가능
• 해당 방식의 장점 : 옵션 조건 컬럼이 NULL 허용 컬럼이라도 결과집합을 보장한다는 것
• 해당 방식의 단점 : UNION ALL을 제외하면 모두 NULL 허용 컬럼에 사용할 수 없다 - 결과집합을 보장하지 않기때문

🔎 LIKE/BETWEEN 조건 활용 

• LIKE/BETWEEN 조건을 사용시 좋은 성능을 낼 수 있을 때 
  • 필수 조건 컬럼을 인덱스 선두에 두고 액세스 조건으로 사용한다.
  • 필수조건이 '='이면 옵션 조건인 인덱스 액세스 조건으로 사용한다.
• 필수 조건의 변별력이 좋지 않을 때는 사용하지 않는게 좋다.

💫 LIKE/BETWEEN 사용시 아래의 경우에 속하는지 점검
1. 인덱스 선두 컬럼
2. NULL 허용 컬럼 (BETWEEN조건은 1번과 2번 조건에 해당하는지만 점검하면 됨)
3. 숫자형 컬럼
4. 가변 길이 컬럼

 

1. 인덱스 선두 컬럼에 대한 옵션 조건을 LIKE/BETWEEN 연산자로 처리하는 것은 금물이다 → 모든 데이터를 스캔 후 필터링하는 일이 생김

SELECT * FROM 거래
WHERE 고객ID LIKE '%'
AND 거래일자 BETWEEN :DT1 AND :DT2

2. NULL 허용 컬럼에 대한 옵션 조건을 LIKE/BETWEEN 연산자로 처리하는 것은 금물이다 →  결과집합에 오류가 생김

SELECT * FROM 거래
WHERE 고객ID LIKE NULL LIKE '&';
 - 선택된 레코드가 없습니다.

3. 숫자형이면서 인덱스 액세스 조건으로도 사용 가능한 컬럼에 대한 옵션 조건 처리는 LIKE 방식을 사용해선 안된다

SELECT * FROM 거래
WHERE 거래일자 =:DT
AND 고객ID LIKE :USER_ID

--고객ID가 숫자형 컬럼이면, 자동 형변환이 일어나므로 고객ID가 필터조건으로 사용됨
SELECT * FROM 거래
WHERE 거래일자 =:DT
AND TO_CHAR(고객ID) LIKE :USER_ID

4. LIKE를 옵션 조건에 사용할 때는 컬럼 값 길이가 고정적이어야 한다.

--김훈, 김훈남
SELECT * FROM 거래
WHERE 고객명 LIKE '감훈%'

컬럼 값 길이가 가변적일때는 변수 값 길이가 같은 레코드만 조회되도록 조건절을 추가 
WHERE 고객명 LIKE :CUST_NM || '%'
AND LENGTH(고객명) = LENGTH(NVL(:CUST_NM,고객명))

🔎 UNION ALL 활용

• UNION ALL 방식은 옵션 조건 컬럼도 인덱스 액세스 조건으로 사용한다는 사실이 중요하다.
• NULL 허용 컬럼이더라도 사용하는데 문제 없다.
• 단점은 코딩량이 길어진다.

🔎 NVL/DECODE 함수 활용

• 옵션 조건 컬럼을 익데스 액세스 조건으로 사용할 수 있다.
• NVL/DECODE 함수를 여러개 사용하면 그중 변별력이 가장 좋은 컬럼 기준으로 한 번만 OR Expansion이 일어난다.
• OR Expansion 기준으로 선택되지 않으면 인덱스 구성 컬럼이어도 모두 필터 조건으로 처리된다.

4. 인덱스 설계 

🔎 인덱스 설계가 어려운 이유

⚙️ 인덱스가 많으면 아래와 같은 문제가 생긴다.
- DML 성능저하(→ TPS 저하)
- 데이터베이스 사이즈 증가 (→ 디스크 공간낭비)
- 데이터베이스 관리 및 운영 비용 상승

 

 

 

⚙️ 인덱스 스캔 효율성 판단 기준 

1. 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정하는 것
2. '='조건으로 자주 조회하는 컬럼을 앞쪽에 두어야 한다는 것

⚙️ 인덱스 스캔 효율성 이외의 판단 기준

• 수행빈도 (가장 중요✨)
• 업무상 중요도
• 클러스터링 팩터
• 데이터량
• DML 부하 (= 기존 인덱스 개수, 초당 DML 발생량, 자주 갱신하는 컬럼 포함 여부 등)
• 저장공간
• 인덱스 관리 비용 등

🔎 소트연산을 생략하기 위한 컬럼 추가

• 인덱스는 항상 정렬 상태를 유지하므로 Order by, Group By를 위한 소트 연산을 생략 할 수 있게 해준다.
• 조건절에 사용하지 않는 컬럼이라도 소트 연산을 생략할 목적으로 인덱스 구성에 포함시켜 성능개선이 가능하다.

SELECT 계약ID, 청약일자, 입력자ID, 계약상태코드, 보험시작일자, 보험종료일자
  FROM 계약
 WHERE 취급지점ID = :trt_brch_id
   AND 청약일자 BETWEEN :sbcp_dt1 AND :sbcp_dt2
   AND 입력일자 >= TRUNC(SYSDATE - 3)
   AND 계약상태코드 IN (:ctr_stat_cd1, :ctr_stat_cd2, :ctr_stat_cd3)
 ORDER BY 청약일자, 입력자ID

• '=' 조건절 컬럼은 Order by절에 없더라도 인덱스 구성에 포함할 수 있다.
• 취급지점ID가 '=' 조건이므로, 소트연산을 생략할 수 있다.
• '='이 아닌 조건절 컬럼들은 반드시 Order by 컬럼보다 뒤쪽에 두어야 소트 연산을 생략 할 수 있다. 

⚙️ I/O를 최소화하면서도 소트 연산을 생략하려면, 아래 공식에 따라 인덱스를 구성하면 된다.
1. '=' 연산자로 사용한 조건절 컬럼 선정
2. Order by절에 기술한 컬럼 추가
3. '='연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정

🔎 결합 인덱스 선택도

• 인덱스 생성 여부를 결정할 때는 선택도가 충분히 낮은지가 중요한 판단기준이다.
• 선택도(Selectivity) : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율
  • 선택도에 총 레코드 수를 곱해서 카디널리티를 구한다.
• 인덱스 선택도 : 인덱스 컬럼을 모두 '=' 로 조회할때 평균적으로 선택되는 비율 
• 선택도가 높은(카디널리티가 높은)인덱스는 생성해봐야 테이블 액세스가 많이 발생하기 때문에 가치가 별로 없다.
• 인덱스를 생성할때는 반드시 선택도/카디널리티를 확인해야한다.

--계약ID, 취급지점ID 컬럼에 대한 카디널리티를 조회하는 쿼리
select count(*) as NDV, max(cnt) as MX_CARD, min(cnt) as MX_CARD, avg(cnt) as AVG_CARD 
from (
    select 계약ID, 취급지점ID, count(*) as cnt
    from 계약조직
    where (계약ID is not null or 취급지점ID is not null)
    group by 계약ID, 취급지점ID
)

🔎 컬럼 순서 결정시, 선택도 이슈 

• 결합 인덱스 컬럼 간 순서를 정할때도 선택도가 중요한가? 
  • 각 컬럼의 선택도보다 필수 조건 여부, 연산자 형태가 더 중요한 판단 기준이며, 어느 컬럼을 앞에 두는 것이 유리한지 상황에 따라 판단할 일이다. 

🔎 중복 인덱스 제거 

• 아래 인덱스들은 중복이다. 포함 여부를 보면 X03 > X02 > X01 이기 때문에, X03을 남기고 나머지는 지워도 된다.

X01 : 계약ID + 청약일자
X02 : 계약ID + 청약일자 + 보험개시일자
X03 : 계약ID + 청약일자 + 보험개시일자 + 보험종료일자

• 아래의 인덱스들은 중복이 아니나, 계약 ID의 평균 카디널리티가 매우 낮다면 사실상 중복이다.( 불완전 중복 ) 

X01 : 계약ID + 청약일자
X02 : 계약ID + 보험개시일자
X03 : 계약ID + 보험종료일자
X04 : 계약ID + 데이터생성일시

• 그래서 아래와 같이 하나만 만들면 충분하다.

X01 : 계약ID + 청약일자 + 보험개시일자 + 보험종료일자 + 데이터생성일시

🔎 인덱스 설계도 작성

• 인덱스 설계 시 시스템 전체 효율을 고려해야 한다. 
• 건축물을 짓기 위해 설계도가 필수인 것처럼 인덱스 설계 시에도 전체를 조망할 수 있는 설계도면이 필요하다.
• 실제 발생하는 액세스 유형을 모두 조사하는 과정 필요합니다.
• 인덱스 설계 전에 파티션 설계를 먼저 진행하거나 최소한 병행해야 제대로 된 인덱스 전략 수립 가능합니다.