인덱스 튜닝

데이터베이스 테이블에서 데이터를 찾는 방법은 크게 2가지이다.

• 테이블 전체 스캔
• 인덱스를 이용한 스캔

인덱스 튜닝의 2가지 핵심 요소

인덱스는 큰 테이블에서 소량의 데이터를 검색할 때 사용함. 온라인 트랜잭션 처리 시스템(OLTP)에서는 소량 데이터를 주로 검색하므로 인덱스 튜닝이 중요함.

인덱스 효율화 튜닝

• 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것.
• 특정 컬럼 순으로 정렬했다면 찾고자 하는 내용 중심으로 소량만 스캔하면 됨
• 반면에 정렬이 안되어 있다면 많은 양을 스캔해야 함.

테이블 액세스 횟수 줄이기

• 인덱스 스캔 후 테이블 레코드를 액세스할 때, 랜덤 I/O 방식을 사용하므로 "랜덤 액세스 최소화 튜닝"이다.

랜덤 I/O와의 전쟁

• SQL 튜닝을 랜덤 I/O와의 전쟁이다.
• 데이터베이스 성능이 느린 이유는 디스크 I/O 때문 => 읽을 데이터량이 많고 디스크 I/O가 많이 발생할 경우임.
• IOT, 클러스터, 파티션, 테이블 Prefetch, Batch I/O 등등 모두 랜덤 I/O 줄이기 위함임.
• NL조인이 대량데이터 조인할때 느린 이유도 랜덤 I/O 때문 => 그래서 소트머지 조인과 해시 조인이 개발됨.

인덱스 구조

인덱스란 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용되는 오브젝트이다.

• 데이터베이스에서 인덱스없이 데이터 검색하려면, 테이블 전체를 스캔해야 함.
• 인덱스를 사용하면 일부만 읽으면 됨. (인덱스는 정렬되어 있어 Range Scan 이 가능)
• DBMS는 일반적으로 B*Tree 사용
• LMC : 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가리킴. LMC가 가리키는 주소로 찾아가면 첫번째 레코드보다 작거나 같은 레코드가 저장되어 있음.
• 리프 블록에 저장된 레코드는 키값 순으로 정렬되어 있음.
• 리프 블록은 랜덤 액세스 할수 있는 테이블 레코드 (ROWID)가 저장되어 있음.

• ROWID = 데이터 블록 주소 + 로우 번호
• 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
• 블록 번호 : 데이터파일 내에서 부여한 상대적 순번
• 로우 번호 : 블록 내 순번

B*Tree

B*Tree 인덱스의 B의 약자는 'Balanced'의 약자.
어떤 값으로 탐색해도 인덱스 루트에서 리프 블록에 도달하기까지 읽는 블록 수가 같음.
=> 루트로부터 모든 리프 블록까지의 높이는 항상 같음.

BSTree, B Tree, B*Tree, B+Tree

인덱스 탐색 과정

• 수직적 탐색 : 인덱스 스캔 시작지점을 찾는 과정
• 수평적 탐색 : 데이터를 찾는 과정

인덱스 수직적 탐색

• 정렬된 인덱스 레코드 중 조건을 만족하는 첫번째 레코드를 찾는 과정
• "인덱스 스캔 시작지점을 찾는 과정"
• 탐색 과정에서 찾고자 하는 값보다 크거나 같은 값을 만나면 바로 직전 레코드가 가리키는 하위 블록으로 이동.
• '조건을 만족하는 레코드 찾기' X / '조건을 만족하는 첫번째 레코드 찾기' O

인덱스 수평적 탐색

• 수직적 탐색을 통해 스캔 시작점을 찾으면 데이터를 찾기위해 수평탐색 시작
• 본격 데이터 찾기 과정
• 인덱스 리프 블록끼리 서로 앞뒤 블록에 대한 주소값을 가지고 있음.
• 양방향 연결 리스트 구조
• 수평탐색 이유 : 조건을 만족하는 데이터를 모두 찾기 위함, ROWID 얻기 위함

결합 인덱스 구조와 탐색

두 개 이상의 컬럼을 결합해서 인덱스를 만들 수 있음. => 결합 인덱스

결합 인덱스 생성시 컬럼 배치

중복이 적은 고유값을 가진 컬럼부터 앞에 배치하는 것이 일반적으로 성능이 좋다고 생각할 수 있으나, B*Tree 인덱스는 평면 구조가 아니기 때문에, 컬럼순서가 어떻든 바로 찾아갈 수 있다.

즉, 컬럼 순서가 어떻든 성능에는 지장이 없다.