인덱스 튜닝의 핵심 요소는 다음의 2가지이다.
-
인덱스 스캔 효율화 튜닝 : 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것
-
랜덤 액세스 최소화 튜닝 : 테이블 액세스 횟수, 즉, 랜덤 I/O를 줄이는 것
중요도를 따지면 2번이 1번보다 훨씬 더 중요하다.
0. 인덱스 구조 - B Tree 인덱스
(1) 루트, 브랜치, 리프 블록으로 구성
(2) 루트와 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 가진다.
- ex. 루트 블록 레코드가 ‘a’라면 하위블록은 ‘a’보다 크거나 같은 레코드가 저장되어 있다.
(3) 루트와 브랜치 블록엔 키 값을 갖지 않고 자식 노드 중 가장 왼쪽 끝에 위치한 블록인 LMC 레코드가 존재한다.
(4) 리프 블록에 저장된 레코드는 키값 순으로 정렬되어 있으며, 테이블 레코드를 가리키는 ROWID를 갖는다.
(5) 인덱스 키 값이 같으면 ROWID 순으로 정렬되어 있다.
(6) 인덱스 스캔의 이유는 검색 조건을 만족하는 소량의 데이터에서 ROWID를 얻고, ROWID로부터 DBA와 로우번호를 통해 테이블 레코드를 찾아가기 위함이다.
1. 인덱스 수직적/수평적 탐색
인덱스 수직적 탐색 : 인덱스 스캔 시작지점을 찾는 것
찾고자 하는 값보다 크거나 같은 값을 만나면, 바로 직전 레코드가 가리키는 하위 블록으로 이동한다.
만약 크거나 같은 값이 없으면 해당 블록의 맨 마지막 레코드가 가리키는 하위블록으로 이동하고, 루트-브랜치 블록을 거쳐 리프블록에서 찾고자 하는 값을 찾으면 종료된다.
단, 중도에 브랜치 블록에서 찾고자 하는 값을 찾았다고 해서 그 레코드가 가리키는 하위블록으로 이동하면 안된다.
그 이유는, 수직적 탐색은 조건을 만족하는 첫 번째 레코드를 찾는 과정이기에 브랜치 블록에서 찾고자 하는 값을 찾았어도 바로 직전 레코드가 가리키는 하위 블록으로 이동해 첫 번째 레코드를 찾아야 한다.
인덱스 수평적 탐색 : 만족하는 데이터를 모두 찾는 과정
찾고자 하는 데이터가 더 안 나타날 때까지 인덱스 리프 블록을 수평적으로 스캔하는 과정이다.
인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 가지는 양방향 연결리스트 구조이므로, 좌우로 수평적 탐색이 가능하다.
수평적 탐색의 목적은 조건절을 만족하는 데이터를 모두 찾고, ROWID를 얻기 위함이다.
일반적으로 인덱스를 스캔하고 테이블도 스캔하는데, 테이블 스캔 시 ROWID가 필요하기 때문에 수평적 탐색의 목적은 2가지이다.
2. 인덱스 기본
인덱스를 정상적으로 사용한다는 의미는 리프 블록 일부만 스캔하는 Index Range Scan을 진행한다는 의미이며,
Index Range Scan을 진행하기 위해선 인덱스 컬럼을 가공하지 않아야 한다. 인덱스 컬럼을 가공하면 인덱스 스캔 시작점을 찾을 수 없기에 Index Full Scan이 되어버린다.
그렇기에 Index Range Scan을 위한 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 가공하지 않은 상태로 있어야 한다는 것이다.
인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있기만 하면 그 뒤의 조건절에서 인덱스 컬럼을 가공해도 Index Range Scan이 가능하다.
하지만, Index Range Scan이 항상 성능을 보장하는 것은 아니다. range scan 하는 것이 잘 이루어지는지를 보려면 인덱스 리프 블록에서 스캔하는 양을 확인해야 한다.
소트 연산 생략
데이터가 정렬되어 있기 때문에 Index Range Scan이 가능하므로 sql에 order by가 없어도 pk 인덱스를 사용하면 정렬 연산을 따로 수행하지 않는 효과가 있다.
내림차순 정렬에도 조건을 만족하는 가장 큰 값을 찾아 우측 수직 탐색 후 좌측으로 수평 탐색하면 되기에 여기에도 인덱스를 활용할 수 있다.
실행계획에 index range descending으로 표시된다.
3. 자동 형변환
조건절에서 양쪽 값의 데이터 타입이 서로 다르면 값을 비교할 수 없기에 자동으로 형변환을 처리해주는 DBMS가 있고, 타입체크를 엄격히 진행해 컴파일 시점에 에러를 내는 DBMS가 있다.
오라클은 전자에 속하고, 오라클에선 기본적으로 숫자형 컬럼 기준으로 문자형 컬럼을 변환한다.
단, 연산자가 like일 경우엔 like 자체가 문자열 비교 연산자이므로 문자형 기준으로 숫자형 컬럼이 변환된다.
References
- 친절한 SQL 튜닝
Leave a comment