[친절한 SQL 튜닝 스터디] 인덱스 기본

친절한 SQL 튜닝 스터디

https://velog.io/@wooncloud/series/%EC%B9%9C%EC%A0%88%ED%95%9C-SQL-%ED%8A%9C%EB%8B%9D-%EC%8A%A4%ED%84%B0%EB%94%94

시리즈 | 친절한 SQL 튜닝 스터디 - wooncloud.log

 

 

 

 

인덱스 구조 및 탐색

인덱스 튜닝

데이터베이스 테이블에서 데이터를 찾는 방법은 크게 2가지이다.

• 테이블 전체 스캔
• 인덱스를 이용한 스캔

인덱스 튜닝의 2가지 핵심 요소

인덱스는 큰 테이블에서 소량의 데이터를 검색할 때 사용함. 온라인 트랜잭션 처리 시스템(OLTP)에서는 소량 데이터를 주로 검색하므로 인덱스 튜닝이 중요함.

인덱스 효율화 튜닝

• 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것.
• 특정 컬럼 순으로 정렬했다면 찾고자 하는 내용 중심으로 소량만 스캔하면 됨
• 반면에 정렬이 안되어 있다면 많은 양을 스캔해야 함.

테이블 액세스 횟수 줄이기

• 인덱스 스캔 후 테이블 레코드를 액세스할 때, 랜덤 I/O 방식을 사용하므로 "랜덤 액세스 최소화 튜닝"이다.

랜덤 I/O와의 전쟁

• SQL 튜닝을 랜덤 I/O와의 전쟁이다.
• 데이터베이스 성능이 느린 이유는 디스크 I/O 때문 => 읽을 데이터량이 많고 디스크 I/O가 많이 발생할 경우임.
• IOT, 클러스터, 파티션, 테이블 Prefetch, Batch I/O 등등 모두 랜덤 I/O 줄이기 위함임.
• NL조인이 대량데이터 조인할때 느린 이유도 랜덤 I/O 때문 => 그래서 소트머지 조인과 해시 조인이 개발됨.

인덱스 구조

인덱스란 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용되는 오브젝트이다.

• 데이터베이스에서 인덱스없이 데이터 검색하려면, 테이블 전체를 스캔해야 함.
• 인덱스를 사용하면 일부만 읽으면 됨. (인덱스는 정렬되어 있어 Range Scan 이 가능)
• DBMS는 일반적으로 B*Tree 사용
• LMC : 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가리킴. LMC가 가리키는 주소로 찾아가면 첫번째 레코드보다 작거나 같은 레코드가 저장되어 있음.
• 리프 블록에 저장된 레코드는 키값 순으로 정렬되어 있음.
• 리프 블록은 랜덤 액세스 할수 있는 테이블 레코드 (ROWID)가 저장되어 있음.
• ROWID = 데이터 블록 주소 + 로우 번호
• 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
• 블록 번호 : 데이터파일 내에서 부여한 상대적 순번
• 로우 번호 : 블록 내 순번

B*Tree

B*Tree 인덱스의 B의 약자는 'Balanced'의 약자.
어떤 값으로 탐색해도 인덱스 루트에서 리프 블록에 도달하기까지 읽는 블록 수가 같음.
=> 루트로부터 모든 리프 블록까지의 높이는 항상 같음.

BSTree, B Tree, B*Tree, B+Tree

인덱스 탐색 과정

• 수직적 탐색 : 인덱스 스캔 시작지점을 찾는 과정
• 수평적 탐색 : 데이터를 찾는 과정

인덱스 수직적 탐색

• 정렬된 인덱스 레코드 중 조건을 만족하는 첫번째 레코드를 찾는 과정
• ** "인덱스 스캔 시작지점을 찾는 과정" **
• 탐색 과정에서 찾고자 하는 값보다 크거나 같은 값을 만나면 바로 직전 레코드가 가리키는 하위 블록으로 이동.
• '조건을 만족하는 레코드 찾기' X / '조건을 만족하는 첫번째 레코드 찾기' O

인덱스 수평적 탐색

• 수직적 탐색을 통해 스캔 시작점을 찾으면 데이터를 찾기위해 수평탐색 시작
• 본격 데이터 찾기 과정
• 인덱스 리프 블록끼리 서로 앞뒤 블록에 대한 주소값을 가지고 있음.
• 양방향 연결 리스트 구조
• 수평탐색 이유 : 조건을 만족하는 데이터를 모두 찾기 위함, ROWID 얻기 위함

결합 인덱스 구조와 탐색

두 개 이상의 컬럼을 결합해서 인덱스를 만들 수 있음. => 결합 인덱스

결합 인덱스 생성시 컬럼 배치

중복이 적은 고유값을 가진 컬럼부터 앞에 배치하는 것이 일반적으로 성능이 좋다고 생각할 수 있으나, B*Tree 인덱스는 평면 구조가 아니기 때문에, 컬럼순서가 어떻든 바로 찾아갈 수 있다.

즉, 컬럼 순서가 어떻든 성능에는 지장이 없다.

인덱스 기본 사용법

인덱스 컬럼은 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.

인덱스를 정상적으로 사용

• 리프 블록에서 스캔 시작점을 찾아 스캔을 시작해서 중간에 멈출 수 있음을 의미
• Index Range Scan을 의미

인덱스를 가공하면 중간에 스캔을 멈출 수 없어서 일부가 아닌 전체를 스캔하게 됨. (Index Full Scan)

인덱스 가공하면 Range Scan을 못 쓰는 이유

인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문이다.

일정 범위를 스캔하려면 ‘시작지점’과 ‘끝지점’이 있어야 한다.

인덱스 가공

-- 생년월일 인덱스 (Index Range Scan)
where 생년월일 between '20070101' and '20070131'

-- 인덱스를 가공 (Index Range Scan) 못 씀.
where substr(생년월일, 5, 2) = '05'

-- Index Range Scan 사용 불가
where nvl(주문수량, 0) < 100

위와 같이 인덱스를 가공하면 년도 순으로 정렬된 인덱스에 5월을 기준으로 찾아야 하니 띄엄띄엄 찾아야한다. 이것은 시작지점과 끝지점을 찾을수 없게된다.

그래서 Index Full Scan을 하게 된다.

Like 문을 사용

-- 업체명이 인덱스
where 업체명 like '%대한%'

LIKE로 검색해도 위와 같이 ‘대한’이라는 글자를 가진 인덱스를 중간 글씨에서 찾아야 하기 때문에, Range Scan이 불가능하다.

where 업체명 like '대한%' 은 Index Range Scan이 가능하다.

OR 조건

where (전화번호 = :tel_no OR 고객명 = :cust_nm) -- Range Scan 안됨

-- OR Expansion (Index Range Scan 사용 가능)
select * from 고객 where 고객명 = :cust_nm
union all
select * from 고객
where 전화번호 = :tel_no and (고객명 <> :cust_nm OR 고객명 is null)

OR 조건으로 검색할떄, 수직적 탐색을 통해 전화번호 ‘01012345678’이거나 고객명이 ‘홍길동’인 시작지점을 찾을 수 없다.

하지만 union all을 사용하면 각 쿼리당 다른 브랜치 블록을 타기 때문에, Index Range Scan 가능

IN 조건

where 전화번호 IN (:tel_no1, :tel_no2) -- Range Scan 안됨

-- Index Range Scan 사용 가능
select * from 고객 where 전화번호 = :tel_no1
union all
select * from 고객 where 전화번호 = :tel_no2

IN조건 또한 OR의 다른 표현이기 때문에, OR과 마찬가지로 Index Range Scan이 안된다.

하지만 union all을 사용하면 각 쿼리당 다른 브랜치 블록을 타기 때문에, Index Range Scan 가능

IN조건절에는 옵티마이저가 IN-List Iterator를 사용한다.
IN-List 갯수만큼 Index Range Scan을 반복한다.
그래서 UNION ALL으로 변환한 것과 같은 효과를 얻는다.

더 중요한 인덱스 사용 조건

만약 인덱스가 [소속팀 + 사원명 + 연령] 순으로 되어 있는데, 사원명으로 스캔하면 range scan이 될까?

답은 아니다. range scan을 하기 위해선 가장 첫번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다.

반대로 말하면 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 range scan이 무조건 가능하다.

인덱스를 이용한 소트 연산 생략

[장비번호 + 변경일자 + 변경순번] 등으로 구성한 인덱스에 아래와 같은 쿼리를 실행한다.

select *
from 상태 변경 이력
where 장비번호 = 'C'
    and 변경일자 = '20180316'
order by 변경순번

위의 쿼리는 인덱스 순으로 이미 정렬된 상태로 order by를 했다. 이것은 order by를 생략할 수 있다.

만약 이렇게 인덱스가 구성되어있지 않다면 SORT ORDER BY 연산이 실행계획에 추가된다.

select *
from 상태 변경 이력
where 장비번호 = 'C'
    and 변경일자 = '20180316'
order by 변경순번 desc

위의 쿼리처럼 desc를 사용하면 Index Range Scan Descending 을 실행한다.

Order by 절에서 컬럼 가공

조건절 뿐만 아니라 ORDER BY절이나 SELECT-LIST 에서 컬럼을 가공하여 인덱스를 정상적으로 못 사용하는 경우가 있다.

-- Index Range Scan 가능
select * 
from 상테변경이력 
where 장비번호 = 'C'
order by 변경일자, 변경순번

-- Index Range Scan 불가능 (ORDER BY 가공)
select * 
from 상테변경이력 
where 장비번호 = 'C'
order by 변경일자 || 변경순번

두번째 쿼리는 order by가 가공되었기 때문에 인덱스를 정상적으로 사용할 수 없다.

그리고, select 절에서 가공된 인덱스를 서브쿼리로 만들어 사용해도 인덱스를 정상적으로 사용할 수 없다.

SELECT-LIST 에서 컬럼 가공

select NVL(MAX(TO_NUMBER(변경순번)), 0)
from 상태변경이력
where 장비번호 = 'C'
    and 변경일자 = '20180316'

위와 같이 ‘변경순번’의 값을 숫자로 바꾸면 인덱스를 사용할 수 없다.

왜냐면 인덱스는 문자열 기준으로 정렬되어 있는데 숫자값으로 바꾸었기 때문이다.

select NVL(TO_NUMBER(MAX(변경순번)), 0)
from 상태변경이력
where 장비번호 = 'C'
    and 변경일자 = '20180316'

위와 같이 변경하면 인덱스를 사용하여 변경순번의 MAX값을 찾고 NUMBER로 변경하니 성능저하가 없다.

자동 형변환

인덱스 가공도 안했는데 가끔 옵티마이저가 테이블 전체 스캔하는 경우 있음.

그 이유는 자동 형변환 때문이다.

select * from 고객 where 생년월일 = 19920920

-- 이걸 옵티마이저가 이렇게 바꿈
select * from 고객 where TO_NUMBER(생년월일) = 19920920

조건을 찾기 위해 생년월일을 입력값과 같은 number로 형변환 했다.

DBMS마다 다른데 오라클은 자동 형변환 하는 방식을 채택한다. Postgresql은 에러를 내려줄거 같다.

형변환 우선순위

• 오라클에서 숫자형과 문자형이 만나면 숫자형이 이긴다.
• 날짜형과 문자형이 만나면 날짜형이 이긴다.
• 근데 LIKE가 출동하면 문자형이 이긴다. select * from 고객 where 고객번호 like '9410%'

인덱스 컬럼 기준으로 형변환 잘하자

형변환 당하지 않게 들어오는 값이 컬럼과 다른 값이 들어오지 않게 형변환을 잘 해야 인덱스를 정상적으로 사용할 수 있다.

인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 TO_DATE, TO_NUMBER, TO_CHAR 등을 사용하여 정확히 형변환하자.

인덱스 확장기능 사용법

Index Range Scan

Index Range Scan은 B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 엑세스 방식이다.

• 수직적 탐색 후 필요함 범위(Range)만 수평적 탐색한다.
• 인덱스 스캔 범위와 테이블 액세스 횟수를 줄이는 것이 성능 향상의 길이다.

Index Full Scan

Index Full Scan은 수직적 탐색 없이 오직 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.
최적의 인덱스가 없을때, 차선으로 선택된다.

Index Full Scan 효용성

• 선두 컬럼이 조건절에 없으면 옵티마이저가 Index Full Scan을 고려한다.
• 만약 테이블이 고용량이면 인덱스 활용을 고려하지 않을 수 있다.
• 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 아주 일부만 테이블을 액세스하는 상황이라면, 면적이 큰 테이블보다 인덱스를 스캔하는 쪽이 유리하다.

인덱스를 이용한 소트 연산 생략

Index Full Scan 역시 Range Scan과 마찬가지로 인덱스 컬럼순으로 정렬된다.
즉, Sort Order By 연산을 생략할 수 있다. 그러한 목적으로도 사용한다.

옵티마이저는 Table Full Scan이 오히려 더 유리한 상황이 와도, Order By가 있다면 이 소트 연산을 생략하기 위해 Index Full Scan을 선택할 경우도 있다.

Index Unique Scan

Index Unique Scan은 수직적 탐색으로만 데이터를 찾는 방식으로, Unique 인덱스를 = (equal) 조건으로 탐색하는 경우이다.

Unique 인덱스가 존재하는 컬럼은 중복값 없이 입력되지 않게 DBMS가 정합성 관리해준다. 그래서 데이터를 = 조건으로 찾은 후 더이상 탐색이 필요없다.

• Unique 인덱스가 존재해도 Between, Like 등 범위 조건으로 검색하면 Index Range Scan을 한다.
• Unique 결합 인덱스도 검색 할 때, Index Range Scan을 사용한다.

Index Skip Scan

오라클은 9i버전에서 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 Index Skip Scan을 사용한다.
*Index Skip Scan은 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 갯수가 많을때 유용하다.
*

예시

Index Skip Scan이 동작하는 방식을 예시로 설명한다.

• Distinct Value 개수가 가장 적은 컬럼은 '성별'이다.
• Distinct Value 개수가 가장 많은 컬럼은 '고객번호'다.

-- 스캔방식 유도를 위한 index_ss 힌트 사용.
select /*+ index_ss(사원 사원_IDX) */ * 
from 사원 
where 연봉 between 2000 and 4000

1. 성별 '남'보다 작은 값이 있을까봐 일단 첫번때 리프 블록 액세스
2. 연봉 >= 800인 2번째 리프 블록은 Skip
3. 연봉 >= 1500인 3번째 리프 블록 다음이 연봉 >= 5000이기 때문에 조건을 만족하는 값이 있을 가능성이 있어 3번째 리프 블록 액세스
4. 연봉 >= 5000인 4번째 리프 블록은 애초에 조건에 만족하지 않기 때문에 Skip
5. 5번째 리프 블록 또한 같은 이유로 Skip
6. 6번째 리프 블록은 연봉 >= 10000이라 skip 될것 같지만, 다음 블록에서 성별 조건이 바뀌어서 일단 액세스
7. 7번 블록은 성별 상관없이 연봉 >= 3000 이고, 다음 레코드는 연봉 >= 5000이기 때문에 액세스
8. 8번 ~ 9번 블록은 연봉 >= 5000, 연봉 >= 7000 이기 때문에 조건에 만족하지 않아 Skip
9. 10번 블록 또한 연봉 >= 10000으로 조건에 만족하지 않지만, 다음 성별이 있을수 있으니 일단 액세스

Index Skip Scan이 작동하기 위한 조건

인덱스 선두 컬럼이 없을때만 Index Skip Scan이 아니다.

인덱스에 컬럼이 3개일 경우 => 일별업종별거래_PK : 업종유형코드 + 업종코드 + 기준일자

• 선두 컬럼이 조건절에 있고, 중간 컬럼이 조건절에 없어도 Index Skip Scan을 사용할 수 있다.
• Distinct Value 개수가 적은 두 개의 선두컬럼이 다 조건절에 없어도 Index Skip Scan 사용할 수 있다.
• 선두 컬럼이 부등호, BETWEEN, LIKE 같은 범위 검색 조건일때도 Index Skip Scan 사용할 수 있다.

인덱스는 기본적으로 최적의 Index Range Scan을 목표로 설계해야 한다.
만약 수행 횟수가 적은 SQL을 위해 인덱스를 추가하는 것이 비효율적일 때, 이들 스캔 방식을 차선책으로 활용하는 전략이 바람직하다.

Index Fast Full Scan

Index Fast Full Scan은 Index Full Scan보다 빠르다.
그 이유는, 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하기 때문이다.

인덱스의 논리적 구조와 물리적 순서에 따라 재배치된 구조의 차이

인덱스의 논리적 구조

Index Full Scan은 인덱스의 논리적 구조를 따라 루트 → 브랜치1 → 1 → 2 → 3 → 4 → 5 → 6 → 7 → 8 → 9 → 10번 순으로 블록을 읽어들인다.

물리적 순서에 따라 재배치된 구조

Index Fast Full Scan은 물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록들을 읽어들인다.

Multiblock I/O 방식으로 왼쪽 익스텐트에서 1 → 2 → 10 → 3 → 9번 순으로 읽고,
그 다음 오른쪽 익스텐트에서 8 → 7 → 4 → 5 → 6번 순으로 읽는다.

루트와 두 개의 브랜치 블록도 읽지만 필요 없는 블록이므로 버린다.

Index Fast Full Scan 특징

• Multiblock I/O 방식을 사용한다.
• 디스크로부터 대량의 인덱스 블록을 읽어야 할 때 큰 효과 발휘한다.
• 인덱스 키 순서대로 정렬되지 않다. (연결 리스트 구조를 무시해서 결과집합이 인덱스 키 순서대로 정렬되지 않음)
• 쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을 때 사용 가능함.
• 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리가 가능
• 병렬 쿼리 시에는 Direct I/O방식 사용하기 때문에 I/O 속도가 더 빨라짐

Index Full Scan	Index Fast Full Scan
1. 인덱스 구조를 따라 스캔 2. 결과집합 순서 보장 3. Single Block I/O 4. (파티션 돼 있지 않다면) 병렬스캔 불가 5. 인덱스에 포함되지 않은 컬럼 조회 시에도 사용 가능	1. 세그먼트 전체를 스캔 2. 결과집합 순서 보장 안 됨 3. Multiblock I/O 4. 병렬스캔 가능 5. 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능

Index Range Scan Descending

Index Range Scan과 기본적으로 동일 스캔방식이나, 인덱스를 뒤에서 앞으로 스캔하기 때문에 내림차순으로 결과집합을 얻는다.

• Order By Desc 가 있으면 옵티마이저는 Index Range Scan Descending을 실행
• Order By Desc 없이 "옵티마이저 힌트"를 이용하여 Index Range Scan Descending 실행 가능
• MAX 함수를 사용하면 인덱스 범위 뒤에서부터 읽는것이 빠르기 때문에, Index Range Scan Descending 사용.