SQL튜닝 - Chapter03. 인덱스튜닝 -2-

인덱스 스캔 효율화

-- <조건절 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

 

6개 조건 중 어떤게 가장 빠를까

조건이 많은게 빨리 출력.

 

그렇다면 BETWEEN과 등호 중에선?

등호가 더 빠르다. 범위가 좁아지기 때문

 

여기서 팁은 어떤 조건이 인덱스의 수평탐색을 줄여주냐가 핵심

 

조건절 2> 는 C1 = 'B'이면서 C2 = 3 인 부분이 시작점이고 C2 = 4인 곳을 만나는 순간 스캔을 멈춘다.

따라서 수평적 탐색의 화살표가 가장 짧다.

 

반대로 <조건절 6>은 C1이 BETWEEN으로 첫번째 조건부터 범위가 매우 넓다. 그러므로 6개 조건 중에 가장 화살표가 길다.

 

인덱스 스캔 효율성

 

1번
WHERE C1 ='성'
AND C2 ='능'
AND C3 ='검'

2번
WHERE C1 ='성'
AND C2 = '능'
AND C4 ='선'

1번과 2번의 결과이다.

2번 같은 경우에는 사실상 조건이 2개인것과 다름이 없다

그래서 위 조건과 차이가 많이 나는걸 알 수 있다.

 

 

엑세스 조건과 필터조건

액세스 조건 : 인덱스 스캔 범위를 결정하는 조건절.  인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는 데 영향을 미치고, 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는 데 영향을 미친다

 

필터 조건 : 테이블로 액세스할지를 결정하는 조건절. 인덱스를 이용하든, 테이블을 Full Scan하든 테이블 액세스 단계에서 처리되는 조건절은 모두 필터 조건.

 

 

BETWEEN과 LIKE 스캔 범위 비교

 

-- LIKE
SELECT * FROM 월별고객별판매집계
WHERE 판매월 LIKE '2019%';

-- BETWEEN
SELECT * FROM 월별고객별판매집계
WHERE 판매월 BETWEEN '201901' AND '201912'

 

LIKE와 BETWEEN 중 

BETWEEN이 더 정확한 표현식인데도 LIKE를 더 선호하는 이유는 LIKE로 코딩하는 것이 더 편리하기 때문이라고 한다.

 

LIKE와 BETWEEN 둘 다 범위검색 조건으로서, 범위검색 조건을 사용할때의 비효율 원리가 똑같이 적용되지만,

데이터 분포와 조건절 값에 따라 인덱스 스캔량이 서로 다를 수 있다

 

결론적으로는 LIKE 보다 BETWEEN이 낫다.

그 이유는 범위를 LIKE보다 더 지정해놨기 때문에 LIKE보다 항상 빠르다고 할 수 있다.

 

 

 

<조건절1>
WHERE 판매월 BETWEEN '201901' AND '201912'
AND 판매구분 = 'B'

<조건절2>
WHERE 판매월 LIKE '2019%' 
AND 판매구분 = 'B'

 

앞서말한 수평검색하는 것이 LIKE보다 BETWEEN이 더 짧은걸 확인할 수 있다.

 

 

<조건절3>
WHERE 판매월 BETWEEN '201901' AND '201912'
AND 판매구분 = 'A'

<조건절4>
WHERE 판매월 LIKE '2019%'
AND 판매구분 = 'A'

 

LIKE는 BETWEEN과 달리 중간에 멈출 수 없다.

위와 비슷한 조건문이고 말하고자 하는 바 또한 같다. 

 

 

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

PL/SQL 함수의 성능적 특성

 

PL/SQL 사용자정의 함수는 일반적으로 생각하는 것보다 매우 느리다

한두 번 호출할 때는 함수를 사용하지 않았을 때와 비교해 성능 차이를 잘 느끼지 못한다

 

-- 한 건 조회하는 경우
SELECT 회원번호, 회원명, 생년, 생월일, encryption(전화번호)
FROM 회원
WHERE 회원번호 = :member_no

-- 수십 ~ 수백 만 건 조회
SELECT 회원번호, 회원명, 생년, 생월일, encryption(전화번호)
FROM 회원
WHERE 생월일 LIKE '01%'

한 건마다 함수인 encryption을 호출하기 때문에 느릴 수 밖에 없다.

 

PL/SQL 사용자 정의 함수가 느린 이유

- 가상머신(VM)상에서 실행되는 인터프리터 언어

-호출 시마다 컨텍스트 스위칭 발생

-내장 SQL에 대한 Recursive Call 발생

 

C++, Java와 같은 일반 프로그래밍 언어에서는 될 수 있으면 함수를 이용해 작은 단위로

모듈화 , 공용화 하는 것이 좋다. 그러나 PL/SQL 함수를 그런 식으로 활용하면 안되는 이유는 

실행시 매번 SQL 실행엔진과 PL/SQL 가상머신 사이에 컨텍스트 스위칭이 일어나기 때문이다.

 

PL/SQL 사용자 정의 함수의 성능을 떨어뜨리는 가장 결정적인 요소는 Recursive Call이다.

SQL 조건에 만족하는 건이 100만 건이면 encryption도 100만 번 실행되는데, 만약 함수에 SQL이

내장돼 있다면 그 SQL도 100만 번 실행이 된다.

 

 

효과적인 인덱스를 만들어서 사용하면 함수호출을 최소화 할 수 있다

SELECT /*+ FULL(A) */ 회원번호, 회원명, 생년, 생월일, 등록일자
FROM 회원 a
WHERE 암호화된_전화번호 = encryption( :phone_no )

이렇게 PL/SQL 함수를 사용했을 때, 회원 테이블을 Full Scan 방식으로 읽으면 

encryption 함수는 테이블 건수만큼 수행된다.

 

SELECT /*+ FULL(A) */ 회원번호, 회원명, 생년, 생월일, 등록일자
FROM 회원 a
WHERE 생년 = '1987'
AND 암호화된_전화번호 = encryption( :phone_no )

1번째 코드문과 달리 다른 조건절이 있으면, encryption 함수는 조건절(WHERE 생년 = '1987') 에 해당하는 건수만큼 수행.

 

CREATE INDEX 회원_X01 ON 회원(생년);
CREATE INDEX 회원_X02 ON 회원(생년, 생월일, 암호화된_전화번호);
CREATE INDEX 회원_X03 ON 회원(생년, 암호화된_전화번호);

- 인덱스를 만들어서 사용하면 범위가 줄어든다-

함수를 굳이 안쓰는게 좋지만, 써야한다면 인덱스를 추가해서 쓰는 것이 좋다

 

 

인덱스 설계

인덱스 설계가 어려운 이유 : SQL 각각에 최적화된 인덱스를 마음껏 생성할 수 있다면, SQL 튜닝과 인덱스 설계만큼 쉬운 일이 없을 것이다. -> 많이 만들면 시스템 부하를 증가하게 되고 성능이 느려지는 것뿐만이 아닌 다음과 같은 문제도 발생한다

 

- DML 성능 저하 ( TPS 저하)

[ DML : Delete , Insert , Update ] [ TPS : Transaction for second ( 성능) ]

 

- 데이터베이스 사이즈 증가 ( 디스크 공간 낭비 )

 

- 데이터베이스 관리 및 운영 비용 상승 

 

이처럼 인덱스가 6개 달려있으면, 신규데이터를 입력할 때마다 여섯 개 인덱스에도 데이터를 입력해야 한다.

 

집계형 테이블이면 상관이 없다. SELECT 문만 사용하기 때문.

 

 

가장 중요한 두가지 인덱스 선택 기준

1. 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정한다.

 

2. '=' 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다

 

 

스캔 효율성 이외의 판단 기준

위에 말한건 인덱스 스캔 효율성 판단 기준이었다. 그 외에 판단 기준은 다음과 같다

 

- 수행 빈도

 

- 업무상 중요도

 

- 클러스터링 팩터

 

- 데이터량 

 

-  DML 부하 ( =기존 인덱스 개수, 초당 DML 발생량, 자주 갱신하는 컬럼 포함 여부 등 ) 

 

- 저장 공간

 

- 인덱스 관리 비용 등 

 

이 중에서도 가장 중요한 하나를 꼽게 되면 수행빈도가 가장 중요하다

(자주 사용되는게 빨라야 한다.)

 

 

 

공식을 초월한 전략전 설계

 

X01 : 청약일자 + 취급부서 + 취급지점 + 취급자 + 입력자 + 대리점설계사 + 대리점지사

X02 : 보험개시일자 + 취급부서 + 취급지점 + 취급자 + 입력자 + 대리점설계사 + 대리점지사

X03 : 보험종료일자 + 취급부서 + 취급지점 + 취급자 + 입력자 + 대리점설계사 + 대리점지사

X04 : 데이터생성일시 + 취급부서 + 취급지점 + 취급자 + 입력자 + 대리점설계사 + 대리점지사

 

이렇게 설계한 핵심 포인트는 두 가지이다. 

- 일자 조회구간이 길지 않으면 인덱스 스캔 비효율이 성능에 미치는 영향이 크지 않다는 점이다.

- 인덱스 스캔 효율보다 테이블 액세스가 더 큰 부하요소라는 점

 

공식대로라면 24개 인덱스가 필요하지만, 업무 상황을 고려한 전략적 판단을 통해 수를 많이 줄였다.

 

 

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

인덱스는 항상 정렬 상태를 유지하므로 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 절에 없더라도 인덱스 구성에 포함할 수 있다.

'='이 아닌 조건절 컬럼들은 반드시 ORDER BY 컬럼보다 뒤쪽에 두어야 소트 연산을 생략할 수 있다.

 

I/O를 최소화하면서도 소트 연산을 생략하려면

 

'=' 연산자로 사용한 조건절 컬럼 선정

ORDER BY 절에 기술한 컬럼 추가

'=' 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정 

 

 

 

중복 인덱스 제거

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

위 세 인덱스는 중복이다. 

X03 인덱스 선두 컬럼이 X01 , X02 인덱스 전체를 완전히 포함하기 때문에 '완전중복'이라고 한다

다음과 같은 경우는 X03 인덱스를 남기고  X01 , X02 인덱스는 지워도 된다

 

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

위는 완전중복과 대비되는 불완전중복

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

 

중복이 아니어서 삭제를 할 수 없지만 몇 건 없기 때문에 합쳐도 상관이 없는 케이스이다

 

 

인덱스 설계도 작성