테이블 랜덤 액세스
💡 인덱스로 검색해도 빠른데, 왜 굳이 파티셔닝?
인덱스로 검색하는데 왜 느림?
대량의 데이터를 조회하면 테이블 전체 스캔하는것보다 인덱스 사용하는것이 더 느리다.
인덱스 ROWID는 물리적 주소? 논리적 주소?
인덱스를 스캔하는 이유는, 검색조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾아 테이블레코드를 찾아가기 위한 ROWID를 얻는것이다.
ROWID는 테이블 레코드를 찾아가기 위한 논리적 주소 정보를 담고있기 때문에 논리적인 주소에 가깝다.
ROWID를 포인터라고 이해하면 안된다.
인덱스 ROWID는 논리적 주소이다. 디스크상에서 테이블 레코드를 찾아가기 위한 정보를 담는다.
메인 메모리 DB와 비교
메인 메모리 DB (MMDB)는 데이터를 모두 메모리에 로드해놓고 메모리에서 I/O를 수행하는 DB이다.
그럼 버퍼캐시 히트율 99% DB와 MMDB를 비교하면 MMDB가 빠르다.
버퍼캐시 히트율 99%도 거의 메모리에서 I/O가 일어난다는데 왜?
- MMDB는 디스크 저장 데이터를 버퍼캐시에 이동후 인덱스를 생성해서, 인덱스에 디스크상 주소정보가 아니라 메모리 주소정보(Pointer)를 저장해서 더 빠르다.
- 오라클은 인덱스에 포인터를 직접 연결할 수 없는 구조다. 그래서 디스크 주소정보를 이용해 해시알고리즘으로 버퍼 블록을 찾아가서 훨씬 느리다.
즉, 일반 DBMS에서 인덱스 ROWID를 이용한 테이블 액세스가 생각보다 빠르지 않다.
I/O 매커니즘 복습
- DBA는 데이터파일번호 + 블록번호로서 디스크 상에서 블록을 찾아가기 위한 주소다.
- I/O 성능을 올리려면 버퍼캐시를 활용해야 한다.
- 읽고자 하는 DBA를 해시함수에 입력해서 해시체인을 찾고 거기서 버퍼헤더를 찾음
- 인덱스로 테이블 블록을 액세스 할때 리프블록에서 읽은 ROWID를 분해해 DBA정보를 얻음.
- 테이블 FULL SCAN 할 때는 익스텐트 맵을 통해서 읽을 블록들의 DBA 정보를 얻는다.
- 모든 데이터가 캐싱되어 있더라도 테이블 레코드를 찾기 위해, DBA 해싱과 래치 획득 과정을 반복한다. 동시 액세스가 심하면 캐시버퍼 체인래치와 버퍼 LOCK에 대한 경합까지 발생.
- 결론으로 ROWID를 이용한 테이블 액세스는 고비용 구조이다.
인덱스 클러스터링 팩터
클러스터링 팩터 (CF) : 군집성 계수
특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미.
CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 좋다. 그 의미는 테이블 액세스량에 비해 블록 I/O가 적게 발생한다는 뜻이다. (뭉쳐 있으니까 다 들리기 수월함.)
인덱스 손익분기점
Index Range Scan에 의한 테이블 액세스가 Table Full Scan 보다 느려지는 지점을 말한다.
인덱스를 이용한 테이블 액세스가 Table Full Scan 보다 느린 이유는 아래와 같다.
- Table full scan은 시퀀셜 액세스인 반면, ROWID를 이용한 것은 랜덤액세스 방식
- Table Full Scan은 Multiblock I/O이지만, 인덱스 ROWID를 이용한 액세스는 Single Block I/O이다.
CF의 정도에 따라 다른 손익분기점
- CF가 나쁘면 손익분기점은 5%(심하면 1%)미만에서 결정됨
- CF가 아주좋으면 90%수준까지 올라감
인덱스 손익 분기점을 보고 좋은 인덱스를 만드려고 노력해야한다.
그리고 테이블스캔이 항상 나쁜건 아니고 인덱스 스캔이 항상 좋은건 아니다.
온라인 프로그램 튜닝 vs 배치 프로그램 튜닝
온라인 프로그램
- 소량의 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 중요.
- 조인도 NL 조인을 적극 활용 (인덱스를 사용한 조인)
- 인덱스를 이용한 소트연산 생략을 사용하면 빠른 속도를 낸다.
배치 프로그램
- 배치 프로그램에서는 인덱스보다 Full Scan이 효과적
- 하지만 초대용량은 시스템 부담이 크기 때문에, 파티션 활용 전략이 중요한 튜닝 요소이다.
- 병렬 처리까지 더하면 최고닷
- 테이블을 파티셔닝 하는 이유는 Full Scan을 빠르게 하기 위함.
인덱스 컬럼 추가
테이블 액세스 최소화를 위해 가장 일반적인 튜닝방법은 인덱스에 컬럼을 추가하는 것이다.
테이블 액세스 단계 필터 조건에 의해 버려지는 레코드가 많을 때, 인덱스에 컬럼을 추가하면 테이블 랜덤 액세스 횟수를 줄일 수 있다.
인덱스만 읽고 처리
테이블 액세스 단계 필터 조건에 의해 버려지는 레코드가 많을땐, 인덱스에 컬럼 추가해서 성능 향상하지만, 만약 버려지는 레코드가 없다면 결국 테이블 랜덤 액세스가 많이 발생해 느릴수 밖에 없다.
이때, 쿼리에 사용된 컬럼을 모두 인덱스에 추가하여 테이블 액세스가 아예 발생하지 않게 할 수 있다. 이것을 Covered 쿼리라고 부르고, 그 쿼리에 사용한 인덱스를 'Covered 인덱스'라고 부른다.
하지만 실제로 추가해야 할 컬럼이 많아서 적용하기 곤란함.
Include 인덱스
Oracle엔 없지만 SQL Server2005에는 있는 기능이다.
인덱스 키 외에 미리 지정한 컬럼을 리프레벨에 함께 저장하는 기능이다.
Include 인덱스는 순전히 테이블 랜덤 액세스를 줄이는 용도로 개발됨.
인덱스 구조 테이블
인덱스를 이용한 테이블 액세스가 고비용 구조임. 그럼 랜덤 액세스가 아에 발생하지 않도록 테이블을 인덱스 구조로 생성한다면?
오라클은 이걸 IOT (Index Organized Table)라고 함. MS-SQL은 '클러스터형 인덱스'라고 함.
IOT (Index Organized Table)
- 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장하고 있다. 인덱스 리프 블록이 곧 데이터 블록이다.
- 힙 구조 테이블에 데이터를 입력할 때는 랜덤방식을 사용, 반면 IOT는 인덱스 구조 테이블이므로 정렬 상태를 유지하며 데이터를 입력.
- IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나.
- 랜덤 액세스X 시퀀셜 액세스에 유리
- BETWEEN이나 부등호 조건으로 넓은 범위 읽을 때 유리함.
클러스터 테이블
인덱스 클러스터 테이블
- 클러스터 키값이 같은 레코드를 한 블록에 모아서 저장하는 구조.
- 한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당하여 클러스터 체인으로 연결한다.
다중 테이블 클러스터
여러 테이블 레코드를 같은 블록에 저장한다. 이는 SQL Server의 클러서터형 인덱스와는 다르며 IOT에 가깝다.
클러스터 인덱스도 일반 B*Tree를 인덱스 구조를 사용하지만, 테이블 레코드를 일일이 가리키지 않고 해당 키값을 저장하는 첫 번째 데이터 블록을 가리킨다는 점이 다르다.
즉, 일반 테이블의 인덱스 레코드는 테이블 레코드와 1:1 대응 관계를 갖지만, 클러스터 인덱스는 테이블 레코드와 1:M관계를 가진다.
클러스터 인덱스의 키값은 항상 Unique하다.(중복값이 없다)
해시 클러스터 테이블
인덱스를 활용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다.
