테이블 액세스 최소화
SQL 튜닝은 랜덤 I/O와의 전쟁이라는 말이 있다. 그만큼 중요하다.
SQL이 참조하는 컬럼을 인덱스가 모두 포함하는 경우가 아니면, 인덱스를 스캔한 후에 반드시 테이블을 액세스한다.
인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 거기서 테이블 레코드를 찾아가기 위한 주소값(ROWID)을 얻으려는 데 있다.
인덱스 ROWID는 논리적 주소에 가깝다. 물리적으로 직접 연결되지 않고 테이블 레코드를 찾아가기 위한 논리적 주소 정보를 담고 있기 떄문이다.
정확하게는 디스크 상에서 테이블 레코드를 찾아가기 위한 "위치 정보"를 담는다.
데이터베이스의 인덱스를 설명할 떄 항상 도서 색인에 비유한다. 색인에 기록된 페이지 번호가 ROWID에 해당한다.
메인 메모리 DB
메인 메모리 DB는 말 그대로 데이터를 모두 메모리에 로드해 놓고 메모리를 통해서만 I/O를 수행하는 DB이다.
잘 튜닝된 OLTP성 데이터베이스 시스템이라면 버퍼 캐시 히트율이 99% 이상이다. 디스크를 경유하지 않고 대부분 데이터를 메모리에서 읽는다는 뜻이다. 그런데도 메인 메모리 DB만큼 빠르지는 않다. 특히 대량 데이터를 인덱스로 액세스할 떄는 엄청난 차이가 난다.
어떤 메인 메모리 DB의 경우 인스턴스를 기동하면 디스크에 저장된 데이터를 버퍼캐시로 로딩하고 이어서 인덱스를 생성한다. 이떄 인덱스는 오라클처럼 디스크 상의 주소정보를 갖는게 아닌 메모리상의 주소정보, 즉 포인터를 갖는다. 따라서 인덱스를 경유해 테이블을 액세스하는 비용이 오라클과 비교할 수 없을 정도로 낮다.
FLOW
SQL 실행 → 메모리 확인 → 없으면 디스크 읽기 후 메모리 적재 → 결과 반환
오라클의 인덱스가 "디스크 주소 참조형"이라면 메모리 DB의 인덱스는 "메모리 포인터 참조형"이다.
I/O 매커니즘
DBA(=데이터 파일 번호 + 블록번호)는 디스크 상에서 블록을 찾기 위한 주소 정보이다.
그렇다고 매번 디스크에서 블록일 읽을 수는 없다. I/O 성능을 높이려면 버퍼캐시를 활용해야된다.
그래서 블록을 읽을 떄는 디스크로 가기 전에 버퍼캐시부터 찾아본다. 읽고자 하는 DBA를 해시함수에 입력해서 해시 체인을 찾고 거기서 버퍼 헤더를 찾는다.
캐시에 적재할 떄와 읽을 떄 같은 해시 함수를 사용하므로 버퍼 헤더는 항상 같은 해시 체인에 연결된다.
해싱 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 찾아가는 것이다.
모든 데이터가 캐싱돼 있더라도 테이블 레코드를 찾기 위해 매번 DBA 해싱과 래치 획득 과정을 반복해야 된다. 동시 액세스가 심할 때는 캐시버퍼 체인 래치와 버퍼 Lock에 대한 경합까지 발생한다. 이처럼 인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용 구조다.
인덱스 클러스터링 팩터
클러스터링 팩터는 군집성 계수이다.
특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다.
당연하게도 CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다.
거주지역 = '대한민국'인 데이터가 물리적으로 근접해 있으면 흩어져 있을 때보다 데이터를 찾는 속도가 빠르다.
인덱스 클러스터링 팩터가 가장 좋은 상태를 도식화 한 이미지이다.
CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 좋다고 했는데, 이는 테이블 액세스량에 비해 블록 I/O가 적게 발생함을 의미힌다.
인덱스 손익분기점
인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용 구조다. 따라서 읽어야 할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔하는 것보다 오히려 느려진다.
Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 흔히 인덱스 손익분기점이라고 부른다.
Table Full Scan은 성능이 일정하다. 몇 건을 조회하든 차이가 거의 없다.
인덱스를 이용해 테이블을 액세스할 떄는 전체 데이터 중 몇 건을 추출하느냐에 따라 성능이 크게 달라진다. 당연히 추출 건수가 많을수록 느려진다. 바로 테이블 랜덤 액세스 떄문이다.
인덱스를 이용한 테이블 액세스가 Table Full Scan보다 더 느려지게 만드는 가장 핵심적인 두 가지 요인은 다음과 같다.
- Table Full Scan은 시퀀셜 액세스인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식이다.
- Table Full Scan은 MultiBlock I/O인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 Single Block I/O방식이다.
이런 요인에 의해 인덱스 손익분기점은 보통 5~20%의 낮은 수준에서 결정된다. 인덱스 CF가 나쁘면 같은 테이블 블록을 여러번 반복 액세스하면서 논리적 I/O 횟수가 늘고, 물리적 I/O 횟수가 늘기 떄문이다. CF가 나쁘면 손익분기점은 5% 미만에서 결정되며, 심할 떄는 1% 미만으로 낮아진다. 반대로 CF가 좋으면 90% 수준까지 올라간다.
인덱스 손익분기점과 버퍼캐시 히트율
5~20% 수준의 손익분기점은 10만 건 이내, 많아봐야 100만 건 이내 테이블에 적용되는 수치이다. 더 많은 건을 가진 테이블에선 손익분기점이 더 낮아진다.
10만 건 기준으로 10%는 1만 건이다. 만 건 정도면 버퍼캐시에서 데이터를 찾을 가능성이 어느정도 있다. 게다가 이정도 크기의 테이블이면 인덱스 컬럼 기준으로 값이 같은 테이블 레코드가 근처에 모여 있을 가능성이 있다.
따라서 인덱스를 스캔하면서 테이블을 액세스하다 보면 어느 순간부터 대부분 테이블 블록을 캐시에서 찾게된다.
온라인 프로그램 튜닝 VS 배치 프로그램 튜닝
온라인 프로그램은 보통 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 무엇보다 중요하다. 조인도 대부분 NL 방식을 이용한다. NL조인은 인덱스를 이용하는 조인 방식이다.
반면, 대량 데이터를 읽고 갱신하는 Batch 프로그램은 항상 전체범위 처리 기준으로 튜닝해야된다. 즉, 처리대상 집합 중 일부를 빠르게 처리하는 것이 아니라 전체를 빠르게 처리하는 것을 목표로 삼아야 된다. 대량 데이터를 빠르게 처리하려면, 인덱스와 NL 조인보다 Full Scan과 해시 조인이 유리하다.
대량 Batch 프로그램에서는 인덱스보다 Full Scan이 효과적이다. 따라서 파티션 활용 전략이 매우 중요한 튜닝 요소이고, 병렬 처리까지 더할 수 있으면 좋다. 테이블을 특정 조건으로 파티셔닝 하면, 해당 파티션만 골라서 Full Scan하므로 부담을 크게 줄일 수 있다.
테이블을 파티셔닝 하는 이유는 결국 Full Scan을 빠르게 처리하기 위해서다.
인덱스 컬럼 추가
테이블 액세스 최소화를 위해 가장 일반적으로 사용하는 튜닝 기법은 인덱스에 컬럼을 추가하는 것이다.
DeptNo + JobTask 순으로 구성한 인덱스가 있다고 가정해보자.
SELECT /*index(emp emp_x01)*/
FROM EMP
WHERE DeptNo = '00137'
AND Sal >= 2000위 조건을 만족하는 사원이 단 한명인데, DeptNo = '00137'인 데이터가 6건이 있다면 테이블을 여섯번 액세스 해야된다.
인덱스 구성을 변경하면 좋겠지만, 실무에서 인덱스 구성을 변경하는 것은 절대 쉽지 않다.
인덱스 구성을 새로 만드는 건? 이런식으로 인덱스를 추가하다 보면 테이블마다 인덱스가 수십 개씩 달려 배보다 배꼽이 더 커지게 된다. 인덱스 관리 비용이 증가함은 물론 DML 부하에 따른 트랜잭션 성능 저하가 생길 수 있다.
이런 경우, 기존 emp_x01 인덱스에 Sal 컬럼을 추가하는 것만으로도 큰 효과를 얻을 수 있다. 인덱스 스캔량은 줄지 않지만, 테이블 랜덤 액세스 횟수를 줄여주기 때문이다.
