[친절한 SQL 튜닝] 3장. 인덱스 튜닝

2021. 6. 13. 16:49

3.1 테이블 액세스 최소화

테이블 랜덤 액세스 절차
1. 쿼리문에 사용하는 컬럼을 인덱스가 모두 포함하는 경우가 아니라면, 인덱스를 스캔한 후에 ROWID 획득.
2. ROWID가 가리키는 테이블 블록을 버퍼 캐시에서 먼저 찾아봄.
3. 못 찾을 때만 ROWID를 가지고 디스크에서 찾아 블록을 읽는다.
1장에서 설명했지만, 디스크에서 읽는 방법은 엄청 느리지만, 버퍼 캐시를 탐색하는 것도 생각보다 고비용 구조이므로 테이블 랜덤 액세스 수 자체를 줄여야 함.

클러스터링 팩터(CF) : 같은 값을 갖는 데이터끼리 물리적으로 모여있는 정도
- 높을수록 데이터 찾는 속도가 빠르다
어차피 인덱스로 스캔한 수만큼 테이블을 액세스 할 텐데 CF가 좋다고 빠른 이유는 무엇일까?
- ROWID로 테이블을 액세스 할 때, 아주 어렵게 얻은 테이블 블록에 대한 포인터를 바로 해제하지 않고 가지고 있음. => 버퍼 Pinning이라고 함.
- 다음 데이터를 테이블 액세스 하려고 ROWID를 확인해보니, 이전 스캔한 ROWID와 같은 테이블 블록임.
- 그럼 테이블 블록 포인터를 얻기 위한 노력을 다시 할 필요가 없이, 이전 포인터를 그대로 사용하면 되기 때문에 CF가 좋으면 블록 I/O가 적게 발생하여 찾는 속도가 빠름.

인덱스 손익분기점 : Index Range Scan이 Table Full Scan 보다 느려지는 조회 건수 지점
- Table Full Scan으로 스캔할 경우, 어떤 쿼리든 동일한 성능을 가짐.
- Index Range Scan은 조회되는 건수가 많아질 경우 점점 느려짐.
- 이 값은 CF가 나쁘면 손익분기점은 최악의 경우 5% 정도가 되고 아주 좋으면 90% 수준으로 올라간다.
- 테이블의 로우수가 많을수록 인덱스 손익분기점은 더욱 낮아진다.
- 스캔하려는 건수가 1만 건만 돼도 Table Full Scan이 빠를 수 있다.

인덱스 컬럼 추가 : 해당하는 인덱스가 없다고 새로 만드는 것보다, 기존 인덱스에 추가하는 것도 효과적일 수 있음
- 부서번호 + 업무 인덱스에서 부서번호 + 연봉으로 검색하게 되면 부서번호에 해당하는 데이터가 많으면 랜덤 액세스가 많음
- 부서번호 + 업무 + 연봉 인덱스에서 부서번호 + 연봉으로 검색하게 되면 스캔량은 동일하여도 랜덤 액세스는 적음

조건절에 해당하는 컬럼이 모두 인덱스에 존재하지만 다른 컬럼이 필요해 테이블 액세스를 해야 한다면, 해당 컬럼을 인덱스에 추가하는 방법도 있다.

-- 인덱스 : 부서번호 일 경우, 부서번호로 테이블 스캔을 하지만, 수량을 위해서 테이블 액세스 필요
-- 인덱스 : 부서번호 + 수량일 경우, 수량이 인덱스 자체에 있으므로, 테이블 액세스 불필요
SELECT 부서번호, SUM(수량)
FROM 판매집계
WHERE 부서번호 LIKE '12%'
GROUP BY 부서번호;

인덱스 구조 테이블 : 랜덤 액세스가 절대 발생하지 않도록 인덱스 자체에 테이블 데이터를 갖는 방식
- Index-Organized Table(IOT)라고 하며 인덱스를 만들 때 organization index 키워드를 주면 됨
- IOT는 데이터를 정렬 상태를 유지하기 때문에 클러스터링 팩터가 좋음
- 따라서 BETWEEN이나 부등호 조건으로 넓은 범위를 읽을 때 유리
ex) 사원이 100명인 회사에 날마다 실적을 쌓는 테이블이 있다고 가정. PK인덱스는 사번 + 일자
- 날마다 사원 100명의 실적을 Insert 하기 때문에 한 사원에 대한 실적 데이터는 서로 멀리 떨어지게 됨. 즉 CF가 좋지 않음.
- SELECT * FROM 실적 WHERE 사번 = 'S1234' AND 일자 BETWEEN '20200101' AND '20201231'
- 위 쿼리를 수행할 경우 여러 개의 블록 I/O를 조회해야 하므로 효율적이지 않음.
- 인덱스 구조 테이블로 만들면 사번, 일자 순으로 정렬 상태를 유지하기 때문에 클러스터링 팩터가 좋음. 즉, 같은 데이터끼리 같은 블록에 있으므로 블록 I/O가 기존보다 적게 발생하여 효율적임.

인덱스 클러스터 테이블 : 클러스터 키(ex : 부서번호) 값이 같은 레코드를 한 블록에 모아서 저장하는 방식
- 인덱스를 스캔하면서 값을 찾을 때, 랜덤 액세스가 값 하나당 한 번씩 밖에 발생하지 않음
- 따라서 같은 클러스터 키 값의 데이터가 많을 경우 효율적임
```
-- 인덱스 클러스터 생성
create cluster c_dept# (deptno number(2) ) index; 

-- 클러스터 인덱스 생성
create index c_dept#_idx on cluster c_dept#; 

-- 클러스터 테이블 생성
create table dept (deptno number(2) not null , ... ) cluster c_dept#( deptno );  
```

해시 클러스터 테이블 : 인덱스를 사용하지 않고 특정 키를 가지고 해시 알고리즘을 사용해 클러스터를 찾음
- 인덱스 클러스터와 마찬가지로 값을 찾을 때, 랜덤 액세스가 해시 값 하나당 한 번씩 밖에 발생하지 않음
```
-- 해시 클러스터 생성
create cluster c_dept# (deptno number(2) ) hashkeys 4; 

-- 클러스터 테이블 생성
create table dept (deptno number(2) not null , ... ) cluster c_dept#( deptno );  
```

3.2 부분 범위 처리 활용

부분 범위 처리 : 클라이언트에게 데이터를 전송할 때 쉼 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것
인덱스로 액세스 할 대상 레코드가 아무리 많아도 아주 빠른 응답속도를 낼 수 있음.
자바에서는 Fetch Size를 조절할 수 있으며, 적절하게 사용해서 불필요한 레코드를 조회하지 않게 하는 것이 좋다. 그렇다고 너무 적게 잡으면 Fetch Call이 많아질 수 있다.
ORDER BY 대상이 INDEX 컬럼으로 잘 정의되어 있으면 부분 범위 처리가 가능
- 인덱스를 이용한다는 것은 이미 정렬되어 있기 때문에 SORT 연산이 필요하지 않음.
- 따라서 액세스 할 데이터가 많아도 SORT 연산을 하지 않고 바로 일부 데이터만 Fetch 할 수 있음

3.3 인덱스 스캔 효율화

인덱스 액세스 : 인덱스 스캔 범위를 결정하는 조건절
인덱스 필터 : 테이블로 액세스 할지를 결정하는 조건절
테이블 필터 : 쿼리 수행 다음 단계로 전달하거나 최종 결과 집합에 포함할지를 결정

ex) 인덱스 C1, C2, C3, C4 순으로 존재할 때

-- C1 ~ C4 인덱스 엑세스 조건
SELECT * FROM A WHERE C1='' AND C2='' AND C3='' AND C4=''

-- C1 ~ C3 인덱스 엑세스 조건, C4 인덱스 필터 조건
SELECT * FROM A WHERE C1='' AND C2='' AND C3 BETWEEN '' AND '' AND C4=''

-- C1 인덱스 엑세스 조건, C2 ~ C4 인덱스 필터 조건
SELECT * FROM A WHERE C1 BETWEEN '' AND '' AND C2='' AND C3='' AND C4=''

오라클은 보통 위와 같은 케이스의 실행계획을 보면 모두 인덱스 액세스에 표현함. 구별할 줄 알아야 함.
인덱스 뒤쪽 컬럼이 조건절에 없거나 등치 조건이 아니더라도, 비효율이 없다.
반면, 선행 컬럼이 조건절에 없거나 부등호, BETWEEN 같은 범위 검색 조건이면 비효율이 발생한다.

BETWEEN을 IN-List로 전환

만약 선행 컬럼을 BETWEEN을 해야 하고, 인덱스를 바꿀 수 없다면, BETWEEN을 IN으로 바꾸면 옵티마이저가 INLIST ITERATOR(UNION ALL)를 사용하여 큰 효과를 얻을 수 있다.

-- 인덱스 : 부서번호 + 연봉

-- 선행 컬럼인 부서번호가 BETWWEN으로 사용되어 효율적이지 않음
SELECT 부서번호, 연봉
FROM 사원
WHERE 부서번호 BETWEEN '1' AND '2'
AND 연봉 = '4000'


-- IN을 사용하면 옵티마이저는 IN-List로 변환하여 최적화
SELECT 부서번호, 연봉
FROM 사원
WHERE 부서번호 IN('1', '2')
AND 연봉 = '4000'

-- 위 쿼리는 마치 아래 쿼리처럼 동작한다.
SELECT 부서번호, 연봉
FROM 사원
WHERE 부서번호 = '1'
AND 연봉 = '4000'
UNION ALL
SELECT 부서번호, 연봉
FROM 사원
WHERE 부서번호 = '2'
AND 연봉 = '4000'

IN의 조건 개수가 많아지거나 계속 추가되면 관리하기가 힘들다. 따라서 NL 조인으로 IN 조건의 값을 다른 테이블에서 조인하여 가지고 오면 동일한 효과를 볼 수 있음.

-- 인덱스 : 부서번호 + 연봉

-- IN의 조건이 추가될 경우, 불편함.
SELECT 
	부서번호, 연봉
FROM
	사원
WHERE
부서번호 IN('1', '2', '3', '4', '5')
AND	연봉 = '4000'

-- 부서번호를 코드값으로 가지고 있는 테이블과 조인하여 사용하면 동일하게 In-List 방식으로 최적화 됨.
SELECT /*+ ordered use_nl(B) */
	B.부서번호, B.연봉
FROM 
	코드 A, 사원 B
WHERE
A.코드값 = '사원번호'
AND A.코드 BETWEEN '1' AND '5'
AND B.부서번호 = A.코드
AND B.연봉 = '4000'

In-List는 값 개수만큼 인덱스 스캔을 위한 수직적 탐색을 하므로 오히려 비효율이 생길 수 있으므로 잘 고려해서 사용해야 함.
In-List는 선택되는 레코드끼리 서로 멀리 떨어질 때 효율이 더 좋음. 바로 근처에 레코드가 있을 경우 오히려 IN-List로 조회하면 수직적 탐색 비용이 더 들 수 있음.
BETWEEN을 IN으로 수정하지 않고, INDEX_SS 힌트를 주어 Index Skip Scan을 사용하여도 비슷한 효율을 얻을 수 있다.

IN은 '='이 아니다. (이해가 잘 되지 않음)

IN이 '='로 처리되려면 IN-List 방식으로 풀려야 한다. 그렇지 않으면 필터 조건으로 사용됨.

만약 해당 필드를 IN-List로 풀고 싶거나 하는 방법은 아래와 같은 힌트를 사용

-- 인덱스가 고객번호 + 상품ID 일때

-- num 값을 1을 주었으므로 고객번호까지만 인덱스를 사용. 
-- 즉 IN-List로 풀지 않고 상품ID는 필터 조건으로 사용됨
select /*+ num_index_keys(TABLE 인덱스명 1) */ 
FROM TABLE
WHERE 고객번호 = :cust_no
AND 상품ID in ('1','2','3')

-- num 값을 2를 주었으므로 상품ID까지 인덱스를 사용하여 In-List로 풀게 됨
select /*+ num_index_keys(TABLE 인덱스명 2) */ 
FROM TABLE
WHERE 고객번호 = :cust_no
AND 상품ID in ('1','2','3')

LIKE 검색보다는 BETWEEN이 낫다.
- BETWEEN은 정확한 시작점과 끝을 알 수 있지만, LIKE는 혹시 모를 값을 위해 앞 뒤로 한 번씩 더 스캔함.

코딩을 쉽게 하려고 LIKE나 BETWEEN를 이용한 범위 검색 조건을 너무 남용하지 말자.
```
-- 인덱스 : 사원번호 + 이름 + 주소
SELECT 
	*
FROM
	사원
WHERE
사원번호 = :no
AND 이름 LIKE :name || '%'
AND 주소 LIKE :addr || '%'
```
- 이름과 주소는 각각 옵션이기 때문에 위와 같이 쿼리를 작성함.
- 이름을 LIKE로 검색하였기 때문에 이름을 입력하여도 INDEX 스캔 비효율이 발생함.
- 데이터가 많은 테이블에서는 귀찮지만 각각의 옵션 여부에 따라 쿼리를 여러 개 작성하여 '='로 하는 것을 고려해야 함.

선두 컬럼에 OR 조건이 있으면 인덱스를 타지 않는다.

-- 인덱스 : 사원ID + 이름
-- 사원ID가 선두컬럼이어도 OR를 사용하여서 인덱스를 사용하지 않음
SELECT *
FROM 사원
WHERE (:id is null or 사원ID = :id)
AND 이름 between :name1 and :name2

후행 컬럼의 LIKE/BETWEEN : 필수 조건이 변별력이 좋을 경우 (해당 조건에 해당하는 데이터가 적을 경우) 후행 컬럼이 LIKE/BETWEEN을 사용해도 괜찮다.

-- 인덱스 : 상품명 + 상품분류코드
-- 상품명의 변별력이 좋기 때문에 충분히 좋은 성능 발휘
SELECT * FROM 상품
WHERE 상품명 = :nm
AND 상품분류코드 LIKE :cd || '%'


-- 인덱스 : 상품대분류코드 + 상품코드
-- 상품대분류코드는 변별력이 좋지 않아 만약 상품코드를 입력하지 않으면 엑세스할 데이터가 많아 성능이 좋지 않음
SELECT * FROM 상품
WHERE 상품대분류코드 = :big_cd
AND 상품코드 LIKE :cd || '%'

LIKE/BETWEEN 튜닝 위치
- 인덱스 선두 컬럼
- NULL 허용 컬럼 : NULL 값이 실제 조건에 입력되면, 그 데이터는 결과 집합에 누락됨
- 숫자형 컬럼 : LIKE 검색을 하게 되면 문자열로 형 변환이 되어 인덱스 타지 않음
- 가변 길이 컬럼

3.4 인덱스 설계

인덱스가 많으면
- DML 성능 저하
- DB 사이즈 증가
- DB 관리 및 운영 비용 상승

인덱스 설계 기준 가장 기본
- 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정한다.
- = 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.

이외 조건
- 수행 빈도
- 업무상 중요도
- 클러스터링 팩터
- 데이터량
- DML 부하
- 저장 공간
- 인덱스 관리 비용 등

수행 빈도가 많은 것은 인덱스를 타야 한다.
- NL 조인(4장에서 설명. 다중 포문이라고 생각하면 된다.)은 Inner 쪽의 쿼리가 여러 번 수행되기 때문에 Inner은 '=' 조건 컬럼을 선두에 두어 인덱스를 최대한 타도록 해야 한다.

인덱스를 공식에 맞춰 만들면 좋지만 현실적으로 불가능하기 때문에 전략적으로 작성해야 한다.
- 조건 컬럼이 = 로 조회하는 것이 있고, BETWEEN으로 일자를 검색하는 쿼리가 있다고 가정하면 일자를 선두 컬럼으로 두고, 나머지 조건 컬럼을 뒤에 후행 컬럼으루 만드는게 좋다.
- 가장 많이 사용되는 '=' 조건 컬럼이 있다면 그 조건컬럼은 선두 컬럼으로 두고, 일자를 그 뒤로 두는 인덱스가 하나 정도 있는것도 좋음.

소트 연산을 생략하기 위한 인덱스 설계 공식
1. '=' 연산자로 사용한 조건절 컬럼을 가장 앞에 선정
2. ORDER BY 절에 기술한 컬럼 추가
3. '=' 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정

결합 인덱스 선택도 : 인덱스 컬럼을 모두 '=' 조회했을 때 전체 레코드 대비 선택되는 비율
- 선택도가 높으면 인덱스를 만들어봤자 테이블 액세스가 많이 발생하므로 비효율적이다.

중복 인덱스 제거
- 완전 중복 : 인덱스가 앞에서부터 동일하게 포함된 게 있다면 중복이므로 작은 인덱스를 제거해야 함.
- 불완전 중복 : 선두 컬럼은 같지만, 두 번째 컬럼이 모두 다른데, 선두 컬럼이 만약 카디널 리티가 5 이하정도 라면 그냥 다 합치는 게 나음.

'SQL > [책] 친절한 SQL 튜닝' 카테고리의 다른 글

[친절한 SQL 튜닝] 6장. DML 튜닝 (0)	2021.06.26
[친절한 SQL 튜닝] 5장. 소트 튜닝 (0)	2021.06.21
[친절한 SQL 튜닝] 4장. 조인 튜닝 (0)	2021.06.15
[친절한 SQL 튜닝] 2장. 인덱스 기본 (0)	2021.06.12
[친절한 SQL 튜닝] 1장. SQL 처리 과정과 I/O (0)	2021.06.12

개발 공부