인덱스 구조 및 탐색
인덱스 탐색 과정
- 수직적 탐색
- 수평적 탐색
인덱스 튜닝
- 인덱스 스캔 과정에서 비효율 줄이기
- 테이블 엑세스 횟수 줄이기
- 인덱스 스캔후 레코드 엑세스시 랜덤 IO 방식 사용
- 즉 랜덤 엑세스 최소화 튜닝
- 인덱스 스캔후 레코드 엑세스시 랜덤 IO 방식 사용
EX) 학생 명부에서 시력이 1.0~1.5인 홍길동 학생을 찾는 경우, 시력이 1.0~1.5인 학생은 50명, 이름이 ‘
홍길동’인 학생은 5명일시 시력이 1.0~1.5인 홍길동 학생은 2명일때, 이름과 시력순으로 정렬한 학생명부가 있으면 가장 좋지만, 이름만으로 정렬한 학생명부와 시력만으로 정렬한 학생명부가 따로 있다면 이름순으로 정렬한 학생명부가 좋음
시력일시 학생명부에 없는 나머지 정보 즉 교실을 찾아가기 부담
SQL 튜닝은 랜덤 I/O와 전쟁
- 데이터 베이스 성능이 느린 이유는 디스크 I/O 때문
- 인덱스를 많이 사용하는 OLTP 시스템이라면 랜덤 I/O가 중요
조인 메서드 중 가장 일반적으로 사용하는 NL 조인이 대량 데이터 조인할때 느린 이유도 랜덤 I/O 때문
- 순차 I/O(Sequential I/O): 파일이나 테이블의 데이터를 연속된 블록으로 차례대로 읽거나 씁니다. 물리적 디스크에서 헤드 이동이 적어 상대적으로 빠르며, 대용량 데이터를 한 번에 처리할 때 유리합니다.
- 랜덤 I/O(Random I/O): 필요한 데이터가 서로 떨어진 위치에 있어 점프(Seek)를 반복해야 하므로, 물리적 디스크(HDD)에서는 추가적인 헤드 이동 때문에 속도가 떨어지는 편입니다.
데이터베이스 관점에서 살펴보면, 인덱스를 사용해 특정 레코드를 바로 찾는 과정(랜덤 액세스)이 많아질수록 스토리지 단에서 랜덤 I/O가 많이 발생한다고 볼 수 있습니다. 따라서 인덱스 스캔이 잦은 OLTP 환경에서는 랜덤 I/O 성능이 중요해집니다.
그래서 소트머지 조인과 해시 조인이 개발 → 즉 느린 랜덤 I/O를 극복하기 위해 개발
인덱스 구조
- 인덱스를 사용시 → 범위 스캔 이 가능
- 인덱스가 정렬되어 있기 때문
- DBMS는 일반적으로 B*TREE 인덱스 사용
인덱스 수직적 탐색
-
정렬된 인덱스 레코드중 조건을 만족하는 첫번째 레코드를 찾는 과정
- 즉 인덱스 스캔 시작 지점 찾는 과정
-
인덱스 수직적 탐색은 루트 블록에서 부터 시작
-
루트를 포함해 브랜치 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소값을 갖음
-
수직적 탐색 과정에 찾고자 하는 값보다 크거나 같은 값을 만나면, 바로 직전 레코드가 가리키는 하위 블록으로 이동
- 즉 수직적 탐색은 조건을 만족하는 레코드를 찾는 과정이 아닌 조건을 만족하는 첫번째 레코드를 찾는 과정
인덱스 수평적 탐색
- 수직적 탐색을 통해 스캔 시작점을 찾았으면, 찾고자 하는 데이터가 더이상 안나타날 때까지 인덱스 리프 블록을 수평적으로 스캔
- 즉 본격적으로 데이터를 찾는 과정
- 인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 갖음
- 양방향 연결 리스트 구조
인덱스를 [고객명 + 성별] 로 구성하든 [성별 + 고객명]으로 구성하든 읽는 인덱스 블록 개수는 똑같다.
Indax Range Scan
- 리프 블록 일부만 스캔하는 것
Indax Full Scan
- 리프 전채를 스캔하는 것
인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없음
인덱스 rangeScan 조건
- 선두 컬럼이 조건절에 있어야 한다. 가공하지 않은 상태로
ex)
CREATE INDEX idx_customer
ON customer (name, age); > 인덱스 지정일시SELECT *
FROM customer
WHERE name = '홍길동'
AND age > 20;
인덱스를 이용한 소트 연산
- 테이블과 달리 인덱스는 정렬돼 있음
- 그래서 RangeScan이 가능
- 옵티마이저는 이런 속성을 활용해 sql 에 ORDER BY 가 있어도 정렬 연산 따로 수행 않함
- 소트연산 생략
ORDER BY 절 컬럼 가공
- 조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공함으로 인덱스를 정상적으로 사용할 수 없는 경우가 있음
- 가공 값으로 정렬 요청시
SELECT * FROM ( SELECT TO_CHAR(A,주민번호,'르0000') AS 주문번호, A.업체번호, A.주문금액
FROM 주문 A
WHERE A.주문일자 = :DT
AND A.주문번호 > NVL(;next_ord_no, 0)
ORDER BY 주문번호) WHERE ROWNUM <= 30다음 쿼리에서 실행 계획에 SORT ORDER BY 연산이 나타나는 이유
ORDER BY 절에 기술한 주문번호는 순수한 주문번호가 아닌 TO_CHAR 함수로 가공한 주문번호를 가리키기 때문
그래서 다음같이 변경 (ORDER BY A.주문번호)
SELECT * FROM ( SELECT TO_CHAR(A,주민번호,'르0000') AS 주문번호, A.업체번호, A.주문금액
FROM 주문 A
WHERE A.주문일자 = :DT
AND A.주문번호 > NVL(;next_ord_no, 0)
ORDER BY A.주문번호) WHERE ROWNUM <= 30자동 형변환
SELECT * FROM 고객 WHERE 생년월일 = 19821225다음은 FULLTABLESCAN을 하였다.
그 이유는
SELECT * FROM 고객 WHERE TO_NUMBER(생년월일) = 19821225다음과 같이 형변환 되었기 때문에 RangeScan 할수 없는 것
- 고객 테이블 생년월일이 문자형인데 숫자형으로 표현 했기 때문에
좌변 칼럼 기준으로 우변을 변환하면 인덱스 사용에는 전혀 문제 없음
- 문자 < 숫자
- 문자 < 날짜
SELECT * FROM 고객 WHERE 가입일자 = '01-JAN-2018';다음은 좌변 날짜고 우변 문자기 때문 문자가 날짜로 변경
SELECT * FROM 고객 WHERE 가입일자 = TO_DATA('01-JAN-2018','DO-MON'YYYY');허나 NLS_DATE-FORMAT 파라미터가 다르게 설정된 환경에서 수행하면 컴파일 오류가 나거나 결과 집합이 틀려질수 있다.
- 즉 정확히 포멧하는 습관이 필요하다.
LIKE
- 숫자형과 문자형이 만나면 숫자형이 이기지만 LIKE 일 시 다름
- LIKE 자체가 문자열 비교 연산자
- 문자형 기준으로 숫자형 컬럼이 변환
EX) 조회시 계좌번호는 사용자가 입력 할 수도 있고 안할 수도 있는 옵션 조건 이는
SELECT * FROM 거래 WHERE 계좌번호 = :acnt_no
AND 거래일자 between :trd_dt1 and :trd_dt2
SELECT * FROM 거래
WHERE 거래일자 BETWEEN :trd_dt1 and trd_dt2만약 이를 하나로
SELECT * FROM 거래
WHERE 계좌번호 LIKE :acnt_no || '%'
AND 거래일자 between :trd_dt1 abd :trd_dt2LIKE BETWEEN 조건을 같이 사용했으므로 인덱스 스캔 효율이 안좋아짐
- 계좌번호가 형변환되면 [거래번호 + 거래일자]순으로 구성된 인덱스를 Range Scan 할 수 없다
- [거래일자 + 거래번호]순으로 구성된 인덱스는 인덱스 RangeScan 할수 있지만 인덱스 스캔 효율은 매우 안좋아짐
Index Range Scan
- B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식
- 인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후 필요범위만 스캔
- 선두컬럼 가공하지않은 상태로 조건절에 사용
- 성능은 인덱스 스캔범위, 테이블 엑세스 횟수를 얼마나 줄일수있느냐로 결정
Index Full Scan
- 수직 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색
- 데이터 검색을 위한 최적의 인덱스가 없을때 차선으로 선택
- 마땅한 인덱스가 없을땐 조건절에서 필터후 데이터량이 소량이면 인덱스 풀 스캔 후 필터된 데이터 대상으로 테이블 엑세스 하는것이 효율적
Index Unique Scan
- 수직적 탐색만으로 데이터 찾는 경우
- 등치 = 조건으로 탐색하는 경우 작동
- UNIQUE INDEX라 해도 범위 조건 (BETWEEN, <>, LIKE) 검색시 INDEX RANGE SCAN 으로 처리
INDEX SKIP SCAN
• 9i버전부터 선두컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔방식을 선보임 > skip scan
- 인덱스 선두컬럼의 DISTINCT VALUE개수가 적고 후행 컬럼의 DISTINCT VALUE갯수가 많을때 유용
Index Fast Full Scan
- INDEX FULL SCAN 보다 빠름
- 논리적 인덱스 트리 구조 무시하고 인덱스 세그먼트 전체를 MULTIBLOCK I/O 방식으로 스캔
- 관련 힌트 index_ffs 와 no_index_ffx
- Multiblock I/O방식을 사용하여 디스크로 부터 대량의 인덱스 블록을 읽을때 큰 효과 발휘
- 허나 인덱스 리프 노드가 갖는 연결 리스트 구조를 무시한 채 데이터를 읽기때문에 결과 집합이 인덱스 키 순서대로 정렬되지 않음
- 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리가 간틍
- 병렬 쿼리시 Direct Path I/O 방식 사용
INDEX RANGE SCAN DESCENDING
- Index Range Scan 과 기본적 동일
- 인덱스를 뒤부터 앞쪽으로 스캔하기 때문 내림차순 정렬
- 인덱스를 거꿀로 읽지 않음 index_desc 힌트를 이용해 유도
