실무를 하다보면 가장 많이 접하게 되는 용어 중, 하나인 INDEX
INDEX의 위키디피아의 정의는 아래와 같다.
"데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조"
실제 우리가 책을 읽을 때도 제일 처음 접하게 되는게 INDEX(목차) 입니다.
SQL에서도 동일하게 이 INDEX는 우리가 원하는 정보를 빠르게 찾도록 도와주는 역할을 합니다.
INDEX를 생성하고,삭제 등등의 기본적인 내용은 자세하게 정리된 글들이 많아서
저는 실무에서 많이 사용되는 힌트 중에서 INDEX 관련된 내용을 정리해볼까 합니다.
전문 DBA가 아닌 단순 개발자의 눈으로 바라봐서 내용이 다소 부실할 수 있습니다.
1. Index Range Scan
: 인덱스의 일부분만 범위 스캔해서 DATA를 엑세스 스캔 방법
: Index를 루트 블럭에서 리프 블럭까지 수직 탐색 후 필요한 범위까지만 탐색은 하는 방식입니다.
: 범위를 얼마만큼 줄일 수 있느냐 Table Access 횟수를 얼마만큼 줄이느냐가 성능의 차이를 낸다.
: WHERE절에 인덱스 구성 컬럼이 ‘<’ or ‘>’와 같이 범위 제한 연산자에 의해 이용되거나 BETWEEN or LIKE와 같은 조건 절에 이용될 때 INDEX RANGE SCAN을 하게 된다.
: 인덱스를 Range Scan 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야한다.
그러니까 쉬운말로.. 인덱스 A를 만들때 이름/나이/성별 순서로 만들었다고 했을 때 쿼리의 조건절에 나이/성별만 있을경우
INDEX RANGE SCAN 이 되지 않는다. 꼭, 이름을 포함시켜야한다.
2. Index Full Scan
: Index Full Scan은 첫번째 리프블록까지 수직적 탐색 후, 인덱스 전체를 탐색하는 방법입니다. 주로 테이블에서 Table Full Scan의 부담이 크거나 정렬작업을 생략하기 위해 테이블 전체를 탐색하는 것보다 Index를 사용하는 것이 유리합니다.
: count 함수를 사용할때도 Index Full Scan을 사용합니다
: 다음 Index Full Scan을 가장 많이 사용하는 부분이 max와 min함수를 사용할때 입니다. Index Full Scan은 인덱스 전체를 확인하는데 Min Max를 구할때는 인덱스의 시작 혹은 끝 한번 건만 확인하고 탐색은 끝내는 방법으로 1개의 블록을 읽어 결과를 얻을 수 있습니다.
Full Table Scan과 Index Full Scan의 차이점?
가장 큰 차이점은 Multi-Block Read가 가능 여부 입니다. Full Table Scan은 Multi-Block Read가 가능하지만 Index Full Scan은 리프블 록을 일일히 방문하기 때문에 Single Block Read밖에 할 수 없습니다. 그래서 때로는 Full Table Scan의 성능이 더 좋을 때가 있습니다.
3. Index Unique Scan
: 쿼리를 실행했을 때, 결과 값이 유일한 값이 나올 경우입니다.
예) 대한민국의 주민등록번호가 123456-1234556 인 사람을 찾는다. 하면
대한민국에서 123456-1234556 의 주민번호를 가진사람은 1명 뿐이기 때문에 Index Unique Scan 이 됩니다.
* 컬럼의 값이 유일한 Primary Key에 사용된 Index
4. Index Skip Scan
: Multi Column Index에서 후행 칼럼을 조건절에 사용할때 활용할 수 있습니다. 성별처럼 선두 칼럼의 고유 값의 개수가 적고 후행 칼럼의 고유 값이 많을 때 효과적입니다.
예를 들어 ( 나라, 나이 ) 로 이루어진 인덱스를 생성했다고 했을 때 나라는 한국,일본,중국이 있고 나이가 20대~30대인 사람들을 알고싶다고 하면 우리가 찾고싶은거는 나이가 20대~30대인 사람들을 찾는거기 때문에 처음 한국에서 20대~30대인 사람들을 찾고, 그 다음 동일한 조건으로 일본,중국에서 20대~30대인 사람들 찾는 방법을 반복하는걸 INDEX SKPI SCAN 방식으로 보면 됩니다.
: 결합 인덱스의 선행 칼럼이 조건식에 없어도 사용 가능
5. Index Fast Full Scan
: Index Fast Full Scan은 말 그대로 Index Full Scan보다 빠르다. 전부를 탐색하면서 더 빠르게 결과를 얻을 수 있는 이유는 Multi-Block Read가 가능하기 때문입니다. 하지만 이로 인해 정렬된 상태로 데이터를 받을 수 없다는 제한이 있습니다. Index Full Scan은 데이터의 논리적인 순서로 결과를 얻지만 Index Fast Full Scan은 데이터의 물리적 저장 구조의 순서로 받아오고 그래서 Multi-Block Read가 가능합니다.
오라클 저장구조 관련 참조 블로그
'IT Diary > # SQL' 카테고리의 다른 글
| [ SQL ] 004. INDEX가 동작을 제대로 안하는 경우 #2 (0) | 2023.01.11 |
|---|---|
| [ SQL ] 003. INDEX가 동작을 제대로 안하는 경우 #1 (0) | 2023.01.04 |
| [ SQL ] 003. 실행계획 보는법 (0) | 2022.04.18 |
| [ SQL ] 001. INDEX 의 정의 (0) | 2021.03.17 |
