안녕하세요. mika입니다. 이번 시간엔 DB의 인덱스(Index) 에 대해서 알아보고자 합니다!
데이터베이스 관리 시스템(DBMS)으로 데이터베이스(DB) 테이블에서 데이터를 찾는 방식은
- 테이블 전체를 스캔
- 인덱스(Index)를 이용
이렇게 2가지가 있습니다.
테이블 전체를 스캔하는 방식은 테이블 전체에서 데이터를 찾는 경우이고,
인덱스를 이용하는 방식은 고유한 데이터. 즉, 기본키(PK)를 이용하는 경우입니다.
테이블 전체를 스캔하는 방식은 튜닝 요소는 많지 않지만, 인덱스와 관련된 튜닝 요소는 많으며 기법도 다양합니다.
앞서, 인덱스를 스캔하는 이유는 검색 조건을 만족하는 소량의 데이터를 빨리 찾고 그에 따른 ROWID를 얻기 위해서 입니다.
- ROWID = 데이터 블록 주소 + 로우 번호
- 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
- 로우 번호 = 블록 내 순번
- 블록 번호 = 데이터파일 내에서 부여한 상대적 순번
인덱스 탐색 과정은 수직적 탐색과 수평적 탐색으로 나뉘어지는데
- 수직적 탐색 = 인덱스 스캔 시작지점을 찾는 과정
- 수평적 탐색 = 데이터를 찾는 과정
으로 2가지가 있습니다.
수직적 탐색은 조건을 만족하는 첫 번째 레코드를 찾는 과정이고, 수평적 탐색은 조건을 만족하는 데이터 모두와 ROWID를 찾기 위한 과정입니다.
이러한 인덱스 탐색을 통해서 인덱스 Range Scan을 이해하면 '인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용(Range Scan) 할 수 없다.' 라는 이유를 바로 알 수 있습니다.
인덱스 컬럼을 가공하게 되면, 인덱스의 스캔 시작지점을 찾을 수 없습니다.
Range Scan에서 Range는 범위를 의미하는데, 인덱스 일정 범위를 스캔한다는 뜻이므로 시작지점과 끝지점이 있어야 합니다.
예로, 가공하지 않는 '주문량을 인덱스로 만들었는데, 값이 NULL이면 0으로 치환하는 값' 기준으로 10보다 작은 레코드를 찾으라는 쿼리를 작성하면 인덱스 스캔 시작지점을 찾을 수 없습니다. 즉, Range Scan할 수 없습니다.
where NVL(주문량, 0) < 100
인덱스를 정상적으로 사용한다 라는 표현은 리프 블록에서 스캔 시작지점을 찾아 거기서부터 스캔을 하다 중간에 멈추는 것을 의미합니다.
즉, 인덱스를 Range Scan하기 위한 가장 첫 번째 조건은 가공하지 않은 상태로 인덱스 선두 컬럼이 조건절에 있어야 합니다. 하지만, 인덱스를 Range Scan 한다고 해서 항상 성능이 좋은 건 아닙니다.
흔히 우리가 말하는 '인덱스를 탄다'라는 표현은 '인덱스를 Range Scan 한다'와 같은 의미인데,
1. SELECT *
FROM 창고
WHERE 주문일자 = :ord_dt
AND 물품번호 LIKE '%PNUM%';
2. SELECT *
FROM 창고
WHERE 주문일자 = :ord_dt
AND SUBSTR(물품번호, 1, 4) = 'PNUM';
위 SQL에서 물품번호는 스캔 범위를 줄이는데 전혀 도움이 안됩니다. 1번의 SQL은 중간값 검색이고, 2번의 SQL은 컬럼을 가공했기 때문입니다. 따라서 위 조건절을 처리할 때 인덱스에서 스캔하는 데이터의 량은 주문일자를 만족하는 100만 건이므로, 인덱스를 잘 탄다고 볼 수 없습니다.
성능은 인덱스 스캔 범위, 테이블 액세스 횟수를 얼마나 줄일 수 있느냐로 결정되므로, SQL 튜닝을 할 때 항시 고려해야합니다.
인덱스 튜닝을 하면, 아무리 데이터가 많아도 인덱스를 사용하므로 데이터가 금방 조회됩니다. 하지만, 대량의 데이터를 조회할 때 인덱스를 사용하므로 테이블 전체를 스캔할 때보다 훨씬 느립니다.
그 이유는 인덱스 ROWID의 특징을 이해하면 바로 알 수 있습니다.
그 내용 설명이 다소 길어서 다음 게시물에서 발행하도록 하겠습니다.
감사합니다.
댓글