데이터 블록 ACCESS 방식에는 RANDOM ACCESS와 SEQUENCIAL ACCESS 방식 두 가지가 있습니다. 데이터 블록 I/O 효율화를 위한 핵심 원리는 다음과 같습니다.
- 랜덤 엑세스 발생량을 줄인다.
- 시퀀셜 액세스에 의한 선택 비중을 높인다.
쿼리의 조건절과 인덱스의 구성상태에 따라 시퀀셜 엑세스의 선택비중이 낮아지고 랜덤 엑세스의 비중이 높아지는 비효율이 발생합니다.
인덱스 선행칼럼이 범위조건일때의 비효율
인덱스 구성 칼럼이 조건절에서 모두 등치조건(=)으로 비교되면 리프블록을 스캔하면서 읽은 레코드는 모두 테이블 엑세스로 이어집니다. 읽고나서 버리는 레코드가 업으므로 인덱스 스캔 효율은 100% 입니다. 인덱스 선행칼러믈 제외한 칼럼일부가 등차조건으로 사용되지 않거나 생략되더라도 비효율은 발생하지 않습니다.
반면 인덱스 선행 칼럼이 조건절에서 누락되거나 BETWEEN, 부등호, LIKE 같은 범위 검색 조건으로 사용되면 스캔한 인덱스 레코드가 테이블 엑세스로 이어지지 않는 비효율이 발생합니다.
-- 인덱스 구성: [인터넷매물 + 아파트시세코드 + 평형 + 평형타입]
SELECT 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
FROM 매물아파트매매
WHERE 아파트시세코드 =
AND 평형 =
AND 평형타입 =
AND 인터넷매물 BETWEEN '1' AND '2'
ORDER BY 입력일 DESC
위의 경우 실제 테이블 엑세스로 이어지는 레코드의 범위보다 인덱스 스캔 범위가 넓습니다. 인덱스 선두 칼럼에 범위조건연산자를 사용하면 등차비교 조건을 만족하는 레코드들이 선두탈럼 별로 흩어지기 때문입니다. 따라서 조건을 만족하지 않는 레코드까지 스캔하고 버리는 비효율이 발생합니다.
범위조건을 IN-LIST로 전환
BETWEEN을 IN-LIST로 바꾸면 성능개선효과가 있습니다. 단 수직적 탐색이 IN-LIST의 요소수만큼 발생하므로 인덱스 깊이가 깊은 인덱스의 경우나 요소의 수가 많은경우 오히려 효율이 악회될 수 있습니다.
범위조건을 2개 이상 사용할때의 비효율
범위검색 조건을 2개 이상 사용하면, 첫 번째 조건이 인데스 스캔 범위를 결정하고, 두 번째 부터는 필터조건 역할만을 하기 때문에 성능상 불리해질 수 있습니다.
인덱스 ROWID에 의한 테이블 랜덤 엑세스
쿼리로 참조하는 모든 칼럼이 인덱스 구성칼럼이면 테이블 랜덤 엑세스는 발생하지 않습니다. 하나라도 포함돼있지 않다면 테이블 랜덤 엑세스는 항상 발생합니다.
인데스 ROWID에 의한 테이블 엑세스 구조
인덱스에 저장돼있는 ROWID는 데이터의 물리적 주소정보입니다. ROWID는 오브젝트 번호, 데이터 파일번호, 블록 번호와 같은 물리적 요소로 구성됩니다. 하지만 ROWID로 직접 디스크에 접근하지는 않습니다. 데이터 블록을 읽을때 항상 버퍼 캐시를 경유하기 때문입니다. 버퍼탐색에 해시구조와 알고리즘을 사용하는데 해시 값으로 ROWID에 내포된 데이터 블록 주소를 사용합니다.
ROWID를 이용해 데이터 블록을 읽어오는 메커니즘은 아래와 같습니다.
- 인덱스에서 ROWID를 읽고 내부의 DBA(DataBlock Address) 를 해시함수에 적용해 해시 값을 구합니다.
- 해시 값을 이용해 해시 버킷을 찾습니다.
- 해시 버킷에 연결된 해시 체인을 스캔하여 블록 헤더를 찾습니다.
- 해시 체인에서 블록헤더를 찾으면 헤더에 저장된 포인터로 버퍼 블록을 읽습니다.
- 블록 헤더를 찾지 못하면 LRU 리스트내의 FREE 버퍼를 찾습니다. 디스크에서 읽어온 블록을 적재할 빈공간을 찾기 위함 입니다.
- FREE 버퍼를 얻지 못하면, DIRTY 버퍼를 디스크에 긹해 FREE 버퍼를 확보합니다.
- FREE 버퍼를 확보하면 디스크에서 블록을 읽어 캐시에 적재합니다.
ROWID에 의한 테이블 엑세스는 위 과정을 거치기 때문에 느립니다.
클러스터링 팩터
ORACLE은 클러스터링 팩터를 사용해 인덱스 ROWID에 의한 테이블 엑세스 비용을 평가합니다. 클러스터링 팩터는 특정 칼럼을 기준으로 같은 값인 데이터가 모여있는 정도를 의미합니다. 인덱스 레코드 정렬 순서와 테이블 레코드 정렬 순서가 전혀일치하지 않으면 클러스터링 팩터가 가장 좋지 않은 상태입니다.
인덱스 손익분기점
데이터 스캔양이 일정 지점을 넘어가면 인덱스 스캔이 테이블 전체 스캔보다 더 느려집니다. 이 지점을 인덱스 손익분기점이라고 합니다. 인덱스의 손익분기점은 일반적으로 5~20% 수준이지만 클러스터링 팩터 상태에 따라 달라집니다.
인덱스 스캔이 테이블 전체 스캔보다 느려지게 만드는 요인은 다음과 같습니다.
인덱스 ROWID에 의한 테이블 엑세스는 랜덤 엑세스인 반면, FULL TABLE SCAN은 시퀀셜 엑세스 방식으로 이뤄집니다. 디스크 I/O 시, 인덱스 ROWID에 의한 테이블 엑세스는 SINGLE BLOCK I/O 방식으로 , FULL TABLE SCAN은 MULTIBLOCK READ I/O 방식으로 진행됩니다.
손익분기점 극복하기
레코드 선택도가 높은 인덱스는 효용가치가 낮습니다. 그럼에도 테이블 전체 스캔의 부담이 높다면 DBMS가 제공하는 기능을 활용해 인덱스 손익분기점을 높일 수 있습니다.
SQL SERVER의 클러스터링 인덱스 또는 ORACLE IOT로 테이블을 인덱스 구조로 생성할 수 있습니다. 테이블 자체가 인덱스 구조와 동일하게 정렬된 상태를 유지합니다. 더불어 인덱스 리프 블록이 곧 데이터블록이므로 추가적인 테이블 엑세스가 불필요합니다.
SQL SERVER의 INCLUDE INDEX를 이용하면 인덱스 키 외에 미리 지정한 칼럼을 리프레벨과 함께 저장합니다. 테이블 액세스를 최소화하기 위함입니다.
ORACLE이 제공하는 클러스터 테이블을 이용하면 클러스터 키값이 같은 레코드를 동일 블록에 저장해 테이블 랜덤 액세스가 키별로 한번씩만 발생하도록 합니다. 클러스터에 도달해서는 시퀀셜 엑세스하기 때문에 넒은 범위를 읽더라도 비효율이 없습니다.
파티셔닝을 통해대량 범위검색 조건으로 자주 사용되는 칼럼 기준으로 테이블을 값에 따라 분리하면 일부 파티션만 스캔하고도 원하는 결과를 얻을 수 있습니다.
클러스터는 클러스터 키 값이 같은 레코드를 블록 단위로 모아 저장하지만, 파티셔닝은 기준 값이 같은 레코드를 세그먼트 단위로 모아 저장합니다.
테이블 엑세스 최소화 튜닝
인덱스 칼럼추가
기존에 존재하는 인데스에 칼럼추가를 통해 인덱스 스캔의 범위를 최소화하여 불필요한 테이블 엑세스를 방지할 수 있습니다.
COVERED INDEX
테이블 랜덤 엑세스의 양이 많더라도 테이블 필터 조건에 의해 버려지는 레코드가 없다면 비효율은 없습니다. 이때는 아예 테이블 엑세스가 발생하지 않도록 필쿼리에 필요한 모든 칼럼을 인덱스에 포함시키는 방법을 고려해볼 수 있스비다. 이런 쿼리를 COVERED 쿼리라고 합니다.
IOT, 클러스터형 인덱스, 클러스터 테이블 활용
해시 클러스터 테이블은 해시 함수의 반환값이 같은 레코드를 물리적으로 함께 저장하는 구조입니다. 해시 함수가 인덱스 역할을 대신하는데 해싱 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로 변환합니다. 별도의 인덱스 구조를 생성하지 않는 장점이 있지만 등치 조건(=) 만 검색가능하다는 제한점이 있습니다.
클러스터링 팩터 높이기
테이블은 무작위로 데이터가 입력되지만, 그것을 가리키는 인덱스는 항상 key 순으로 정렬되므로 대부분의 경우 클러스터링 팩터가 나쁩니다. 인덱스를 기준으로 테이블을 재생성하면 클러스터링 팩터를 인위적으로 좋게 만들수 있습니다.
주의할점은, 다수의 인덱스가 존재하는 테이블을 특정 인덱스를 기준으로 재정렬하면 다른 인덱스의 클러스터링팩터가 나빠질 수 있습니다. 따라서 데이터를 재조정할 때 가장 자주 사용되는 인데스를 기준으로 삼고 다른 인덱스에 영향을 주지 않는지 확인해야 합니다.
배치 I/O
배치 I/O는 읽는 테이블 블록마다 건건이 입출력 호출을 발생시키는 비효율을 줄이기 위해 고안한 기능입니다. 버퍼캐시에서 블록을 찾지 못하면 데이터 블록에 대한 디스크 입출력 호출을 미뤘다가 읽을 블록이 일정량 쌓이면 한번에 처리합니다. 단 배치 I/O 기능이 작동하면 데이터의 정렬순서가 매번 달라질 수 있습니다. 일반적으로 인덱스를 이용하면 소트 연산을 생략할 수 있지만 배치 I/O가 작동하면 정렬 순서를 보장할 수 없으므로 옵티마이저는 SORT ORDER BY 오퍼레이션을 실행합니다.
과거에는 ORDER BY를 생략한체 ROWNUM 조건과 INDEX 힌트를 적용하는 패턴을 많이 사용했지만 배치 I/O가 도입됨에 따라 ORDER BY를 명시하는 것이 바람직합니다.
-- 변경일시 순으로 상위 10개 레코드 출력
SELECT /*+INDEX(H 상태변경이력_pk) */ 장비번호, 변경일시, 상태코드
FROM 상태변경이력 h
WHERE 장비번호 = :eqp_no
AND ROWNUM <= 10
-- 변경일시 역순으로 상위 1개 레코드 조회
SELECT 장비번호, 장비명, 상태코드
,(
SELECT /*+INDEX_DESC(H 상태변경이력_pk) */ 변경일시
FROM 상태변경이력 H
WHERE 장비번호 = P.장비번호
AND ROWNUM <= 1
) 최종변경일시
FROM 장비 P
WHERE 장비구분코드 = 'A001';결합 인덱스 구성을 위한 기본공식
가장 일반적인 인덱스 스캔방식은 INDEX RANGE SCAN 입니다. 이를 위해서는 인덱스 선두 칼럼이 조건절에 반드시 사용돼야합니다. 따라서 결합 인덱스를 구성할 때 첫 번째 기준은 조건절에 항상 혹은 자주 사용되는 칼럼을 선두칼럼으로 선정하는 것입니다.
두 번째 기준은 선정된 칼럼 중 등치조건(=)으로 자주 조회되는 칼럼을 앞쪽에 두는것 입니다. 세 번째 기준은, 소트 오퍼레이션을 생략하기위해 칼럼을 추가하는 것입니다. 인덱스는 정렬상태를 유지하므로 ORDER BY, GROUP BY 를 위한 소트 연산을 생략할 수 있습니다. 따라서 조건 절에 사용하지 않는 칼럼이라도 소트연산 대체 목적으로 인덱스에 포함할 수 있습니다. 인덱스를 이용해 소트 연산을 대체하려면, 인덱스 칼럼과 같은 순서로 누락없이 ORDER BY 절이 기술되야 합니다. 단, 인덱스 구성 칼럼이 조건절에서 = 연산자로 비교되면 ORDER BY 절에서 누락되거나 순서가 달라도 소트연산 생략이 가능합니다.
마지막으로 인덱스 생성여부를 결정할 때는 선택도가 충분히 낮은지 고려해야합니다. 선택도가 높은 칼럼으로 구성된 인덱스의 스캔은 FULL TABLE SCAN 보다 느릴 수 있습니다. 따라서 선택도가 높은 인덱스는 효용가치가 없습니다. 결합 인덱스 간 칼럼 순서를를 정할때 어느 칼럼이 선두에 오는것이 유리한지는 상황에 따라 달라집니다. 개별 칼럼의 선택도 보다는 조건절에서 이떤 형태로 자주 사용되는지, 사용빈도는 어느 쪽이 높은지, 데이터를 빠르게 검색하는데 어느 쪽
효용성이 높은지가 중요한 판단기준이 됩니다.
추가적인 고려사항
- 쿼리 수행빈도
- 업무 상 중요도
- 클러스터링 팩터
- 데이터량
- DML 부하
- 저장공간
- 인덱스 관리비용