[친절한SQL튜닝] 4. 인덱스 튜닝 - 인덱스 스캔 효율화

H.J.SHIN·2024년 12월 17일

sql 데이터베이스 인덱스 친절한SQL튜닝

친절한 SQL 튜닝

목록 보기

4/8

인덱스 스캔 효율화

인덱스 스캔 효율성

인덱스 선행 컬럼이 조건절에 없거나 '=' 조건이 아니면 인덱스 스캔 과정에 비효율이 발생한다.

①인덱스 액세스 조건
- 인덱스 스캔 범위를 결정하는 조건절
- 인덱스 수직적 탐사를 통해 스캔 시작점을 결정하고, 인덱스 리프노드를 스캔을 어디서 멈출지 결정하는 조건절
②인덱스 필터 조건
- 테이블로 액세스 여부를 결정하는 조건절
- 인덱스든 테이블 Full Scan이든 테이블 액세스 단계에서 처리되는 조건절은 모두 필터 조건이다.
- 쿼리의 SELECT 대상 컬럼이 인덱스에 모두 포함되어 있는지 확인하여 테이블 액세스 여부를 결정한다.
③테이블 필터 조건
- 쿼리 수행 다음 단계로 전달하거나 최종 결과 집합에 포함할지 여부를 결정

인덱스 스캔 효율성 측정

SQL 트레이스를 통해 인덱스 스캔의 효율성을 알 수 있다.
ex)
- 인덱스를 스캔하고 얻은 레코드가 10개인데, 그 과정에서 7463개 블록(cr=7463)을 읽었다는 사실을 알 수 있다.
- 한 블록에 평균 500개 레코드가 담긴다고 가정하면 7463*500개 레코드를 읽고 10개의 데이터를 얻은 것이니 상당히 비효율적이다.

인덱스 선행 컬럼이 등치(=)조건이 아닐 때 생기는 비효율

처음으로 나타나는 범위검색 조건(부등호, between, like 등)까지만 만족하는 인덱스 레코드가 연속해서 모여있고, 그 이후 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.
인덱스 레코드가 군집해 있을수록 읽어야하는 레코드의 수가 줄어들기 때문에 효율적이다.
인덱스 컬럼 중 일부가 조건절에 없거나, 등치 조건이 아니더라도, 그것이 뒤쪽 컬럼일 경우에는 비효율이 없다.
인덱스 선행 컬럼이 조건절에 없거나, 범위검색 조건(부등호, between, like 등)이면 비효율이 생긴다.
ex)
- 1)
  조건절 2의 C1, C2, C3, C4 조건으로 스캔 범위를 스캔범위는 (5-10)으로 줄이는데 도움을 준다. (C1, C2, C3, C4 모두 인덱스 액세스 조건)
- 2)
  반면 조건절3에서 C1, C2, C3 조건으로 스캔 범위를 (1-12)로 줄일 수 있지만, C4 조건까지 만족하는 레코드는 (2,3,5,6,7,11) 흩어지게 된다. (C1, C2, C3는 인덱스 액세스 조건, C4는 인덱스 필터 조건)

인덱스 선행 컬럼이 등치(=)조건이 아닐 때 생기는 비효율 해결

범위검색 컬럼이 맨 뒤로 가도록 인덱스 구성을 변경하면 좋겠지만, 운영 시스템에서 인덱스 구성을 바꾸기는 쉽지 않다.
이럴 때 BETWEEN 조건을 IN-List로 바꿔주면 큰 효과를 얻을 수 있다.
ex)
```
where 인터넷매물 between '1' and '3'
and 아파트시세코드='A01011350900056'
and 평형 = '59'
and 평형타입 = 'A'
```
[인터넷매물+아파트시세코드+평형+평형타입]으로 인덱스가 구성되어 있을 때, 위 SQL을 수행하면 아래 그림처럼 인덱스 스캔 범위가 넓다.(비효율적이다)

하지만 BETWEEN 조건을 IN-List로 바꿔주면
```
where 인터넷매물 in('1','2','3')
and 아파트시세코드='A01011350900056'
and 평형 = '59'
and 평형타입 = 'A'
```
In-List 개수만큼 UNION ALL 브랜치가 생성되고, 모든 컬럼을 '='조건으로 검색하기 때문에 선두컬럼에 BETWEEN을 사용할 때와 같은 비효율이 사라진다.

BETWEEN조건을 IN-List로 전환할 때 주의 사항

BETWEEN조건을 In-List 조건으로 전환할 때 In-List 개수가 많지 않도록 주의해야 한다.
In-List 개수가 많으면 수직적 탐색이 많이 발생하고, 이는 BETWEEN조건 때문에 리프 블록을 많이 스캔하는 것보다 더 비효율적일 수 있다.
또한 선택되는 레코드들이 서로 멀리 떨어져 있을 때 유용하다. 오히려 수직적 탐색 때문에 더 많은 블록 I/O가 발생할 수도 있다.

Index Skip Scan 활용

Index Skip Scan을 활용하여 BETWEEN조건을 IN-List 조건으로 변환한 것과 같은 효과를 낼 수 있다.
마찬가지로 검색 조건을 만족하는 데이터들이 서로 멀리 떨어져 있을 때 유용하다.

다양한 옵션 조건 처리 방식의 장단점 비교

OR 조건

인덱스 선두 컬럼에 대한 옵션 조건에 OR 조건을 사용해서는 안된다.

옵티마이저에 의한 OR Expansion 쿼리 변환이 기본적으로 작동하지 않으므로, 옵션 조건 컬럼을 선두에 두고 인덱스를 구성해도 이를 사용할 수 없다.
인덱스 액세스 조건으로 사용 불가, 인덱스 필터 조건으로도 사용 불가, 테이블 필터 조건으로만 사용 가능
단, 인덱스 구성 컬럼 중 하나 이상이 Not Null 컬럼이면, 인덱스 필터 조건으로 사용 가능
유일한 장점은 옵션 조건 컬럼이 Null 허용 컬럼이더라도 결과집합을 보장한다.
참고로, OR 조건이 선두에 있지 않다면 OR Expansion을 통해 인덱스 사용이 가능하다.

LIKE/BETWEEN 조건 활용

필수 조건 컬럼을 인덱스 선두에 두고 액세스 조건으로 사용하면 LIKE/BETWEEN이 인덱스 필터 조건이어도 충분히 좋은 성능을 낼 수 있다.
하지만 변별력이 좋지 않은 필수 조건(만족하는 레코드가 다수)일 때는 Table Full Scan이 더 유리할 수도 있다.

따라서 LIKE/BETWEEN 패턴을 사용할 때는 아래 4가지 경우에 속하는지 반드시 점검해야 한다(BETWEEN은 1,2에 해당하는지만 점검)
1. 인덱스 선두 컬럼에 대한 옵션 조건에 사용 금지
2. NULL 허용 컬럼에 대한 옵션 조건에 사용 금지
3. 숫자형 컬럼이면서 인덱스 액세스 조건으로도 사용 가능한 컬럼에 대한 옵션 조건에 LIKE 사용 금지 (자동 형변환 때문에 인덱스 사용 불가)
4. LIKE를 옵션 조건에 사용할 때는 컬럼 값 길이가 고정적이어야 한다.

UNION ALL 활용

UNION ALL을 이용하여 변수에 값을 입력했는지에 따라 SQL을 실행할 수 있다.
위 SQL에서 cust_id 변수에 값을 입력하지 않으면 위쪽 브랜치에서 거래일자가 선두인 인덱스 사용
cust_id 변수에 값을 입력하면 아래쪽 브랜치에서 인덱스 사용
UNION ALL을 통해 변수에 값을 입력하든 안하든 인덱스를 가장 최적으로 사용할 수 있다.
UNION ALL은 옵션 조건 컬럼도 인덱스 액세스 조건으로 사용한다.

함수호출부하 해소를 위한 인덱스 구성

PL/SQL 사용자 정의 함수는 상당히 느리다.
PL/SQL 사용자 함수가 느린 이유는 3가지가 있다.
- 가상머신 상에서 실행되는 인터프리터 언어
- 호출 시마다 컨텍스트 스위칭 발생
- 내장 SQL에 대한 Recursive Call 발생
여기서 Recursive Call 부하가 가장 크다.
따라서 PL/SQL 함수를 쓰지 않고 조인문으로 처리하는 것이 가장 좋음

효과적인 인덱스 구정을 통한 함수 호출 최소화

인덱스를 효과적으로 구성하면 함수호출을 최소화할 수 있다.
함수를 사용한 조건을 포함하여, 조건절에 있는 모두 인덱스 액세스 조건으로 사용되도록 인덱스를 구성하면 함수 호출을 최소화할 수 있다.
ex) [생년 + 암호화된_전화번호] 인덱스
- 생년과 암호화된_전화번호가 인덱스 액세스 조건으로 사용
- 따라서 encryption 함수는 1회 수행