앞서 2장에서 인덱스 종류들에 대해 알아보았지만, 이는 인덱스 사용법을 익힌 것에 불과하다.
SQL 튜닝에서 가장 중요한 것은 I/O 성능 향상과 직결되어 있기에 그간 개발되었던 조인 메소드, 조건절 등의 발전이 랜덤 I/O 최소화를 초점으로 이루어지고 있다.
3.1 ✅ 테이블 액세스 최소화
인덱스를 활용하면 대량의 데이터를 조회해도 금방 데이터를 조회할 수도 있고, 오히려 테이블 전체를 스캔할 때보다 느릴 때도 있다. 이러한 이유에 대해서 알아보자.
SQL이 참조하는 컬럼을 인덱스가 모두 포함하는 경우가 아니면, 인덱스를 스캔한 이후 테이블을 스캔하도록 반드시 설정이 되어있다.
1. 인덱스 ROWID는 물리적 주소? 논리적 주소?-> 결과부터 이야기하면 인덱스 ROWID는 논리적 주소에 가깝다. 물리적(데이터파일 번호, 오브젝트 번호, 블록 번호 와 같은 물리적 요소)으로 직접 연결되지 않고 디스크 상에서 테이블 레코드를 찾기 위한 위치 정보만을 담는다.
우리가 표현하기 쉽도록 포인터라고 지칭하지 실제로 인덱스 ROWID는 포인터에 해당되지 않는다. 잘못된 표현이다. 또한, 인덱스 ROWID는 물리적으로 테이블과 직접 연결되어 있지도 않기에 무조건 논리적 주소라고 지칭하는 것이 맞다.
※ 포인터 : 메모리 주소값을 담는 변수
메인 메모리 DB : 데이터를 모두 메모리에 로드해놓고 메모리를 통해서만 I/O를 수행하는 DB
잘 튜닝된 OLTP라면, 디스크를 경유하지 않고 대부분 데이터를 메모리에서만 읽기 때문에 버퍼캐시 히트율이 99% 이상이다.
A라는 메인 메모리 db의 경우 인스턴스 기동 시 디스크에 저장된 데이터를 버퍼캐시로 로딩하고 이어서 인덱스를 생성한다. 이때 인덱스는 메모리상의 주소 정보, 즉 포인터를 갖기에 테이블을 액세스하는 비용이 매우 낮다.
그렇기에 메인 메모리 DB에 비해 일반 DBMS에서 인덱스 ROWID를 이용한 테이블 액세스가 생각만큼 빠르지가 않다.결과적으로 I/O 성능을 높이려면 버퍼캐시를 활용해야 한다.
정리하면, 해싱 알고리즘으로 버퍼 헤더를 찾고, 그때 얻은 포인터로 버퍼 블록을 찾아간다.
디스크 DB가 사용하는 ROWID는 우편주소에, 메인 메모리 DB가 사용하는 포인터를 전화번호에 비유할 수 있다.
이 말의 뜻은, 디스크 DB가 메인 메모리 DB에 비해 현저히 느리다는 것을 알 수 있다.
3.1.2 🧨 인덱스 클러스터링 팩터
CF (Clustering Factor)는 군집성 계수라고 번역할 수 있고, 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 모여있는 정도를 의미한다.
특정 컬럼을 기준으로 데이터가 물리적으로 근접해있으면 흩어져 있을 때보다 데이터를 찾는 속도가 빠르다. (검색 효율이 좋다)이는, 테이블 액세스량에 비해 블록 I/O가 적게 발생함을 의미한다.
인덱스 손익분기점 : Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점
Table Full Scan을 성능이 항상 일정한 반면, Index Range Scan의 경우 조회해야 할 데이터가 일정량을 넘어가는 순간 더 느려지는 지점이 존재한다.
- Table Full Scan은 시퀀셜 액세스인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식이다.
- Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 Single Block I/O 방식이다.
알아두기...
만 건만 넘어가도 그냥 Table Full Scan 방식으로 읽는 게 더 빠를 수 있다.
대량 데이터를 빠르게 처리하기 위해선 인덱스와 NL조인보다 Full Scan과 해시 조인이 더 유리하다.3.1.4 🧨 인덱스 컬럼 추가
테이블 액세스 최소화를 위해 가장 일반적으로 사용하는 튜닝 기법은 인덱스에 컬럼을 추가하는 것이다.
인덱스에 조건절에 사용된 컬럼을 추가하여 컬럼을 만든다면, 불필요한 테이블 액세스를 크게 줄일 수 있다.3.1.6 🧨 인덱스 구조 테이블
랜덤 액세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성하는 방식.
Oracle : IOT(Index-Organized Table)
MSSQL : 클러스터형 인덱스 (Clustered)테이블을 찾기 위해 ROWID를 갖는 일반 인덱스와 달리 IOT는 그 자리에 테이블 데이터를 갖는다. 즉, 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장하고 있다.
IOT는 인위적으로 CF를 좋게 만드는 방법 중 하나이다. 같은 값을 가지는 레코드들이 100% 정렬된 상태로 모여 있기에 랜덤 액세스가 아닌 시퀀셜 방식으로 데이터를 액세스하기 때문이다.이 때문에 BETWEEN이나 부등호 조건으로 넓은 범위를 읽을 때 유리하다.
오라클의 클러스터 테이블은 인덱스 클러스터와 해시 클러스터로 나뉜다.
인덱스 클러스터 테이블은 클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조이다.
한 블록에 모두 담을 수 없다면 새로운 블록에 할당하여 클러스터 체인으로 이를 연결한다.여러 테이블을 같은 블록에 저장할 때는 다중 테이블 클러스터 라고 부른다.
명칭 때문에 MSSQL 서버에서 말하는 클러스터형 인덱스와 같다 생각할 수도 있겠지만, 클러스터형 인덱스는 오히려 IOT와 가깝다.
클러스터 테이블은 정렬까지 수행하진 않기 때문이다.해시 클러스터 테이블의 경우는 인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다는 점만 다르다.
인덱스 선행컬럼이 반드시 조건 절에 있어야 한다.
EX) 인덱스 조건이 C1_C2_C3_C4 컬럼일 때,
아래와 같이 SQL을 작성했다고 하자.SELECT * FROM TABLE WHERE C1 = 'A' AND C2 = 'B' AND C4 <= 3이럴 경우 선행 컬럼인 C3가 조건절에 없기 때문에 더 많은 레코드를 조회하게 되는 불상사가 일어날 수 있다.
