[친절한SQL튜닝] 2. 인덱스 기본

도서 [친절한SQL튜닝] 정리

[친절한SQL튜닝] 1. SQL 처리 과정과 I/O

인덱스 구조 및 탐색

---

미리보는 인덱스 튜닝

데이터를 찾는 방법

1. 테이블 전체를 스캔
2. 인덱스 이용

 

OLTP (Online Transaction Processing, 온라인 트랜잭션 처리)

온라인 뱅킹, 쇼핑, 주문 입력 또는 텍스트 메시지 전송 등 동시에 발생하는 다수의 트랜잭션을 실행하는 데이터 처리 유형이다.

 

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

• 인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용한다.
• OLTP 시스템에선 소량 데이터를 주로 검색하므로, 인덱스 튜닝이 중요하다.
• 둘 중 더 중요한 것은 랜덤 액세스 최소화 튜닝으로, 성능에 미치는 영향이 더 크다.

1. 인덱스 스캔 효율화 튜닝

• 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 방법이다.
• p.71

2. 랜덤 액세스 최소화 튜닝

• 테이블 액세스 횟수를 줄이는 방법이다.
• 인덱스 스캔 후 테이블 레코드를 액세스할 때 랜덤 I/O 방식을 사용해 붙인 이름이다.
• p.72

 

SQL 튜닝은 랜덤 I/O와의 전쟁이다.

• 성능을 위해 DBMS가 제공하는 많은 기능이, 느린 랜덤 I/O를 극복하기 위해 개발됐다.
• IOT, 클러스터, 파티션 등

 

인덱스 구조

• 인덱스는 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용하는 오브젝트다.
• 인덱스를 이용하면 전체 스캔이 아닌, 일부만 읽고 멈추는 범위 스캔(Range Scan)이 가능하다.
• 범위스캔이 가능한건 인덱스가 정렬돼있기 때문이다.
  
  
• DBMS는 일반적으로 B*Tree 인덱스를 사용한다. (B*Tree 개념)

p.75

• Root, Branch, Leaf
• 루트와 브랜치 블록의 각 레코드는, 하위 블록에 대한 주소값을 갖는다.
• 키 값은 하위 블록에 저장된 키 값의 범위를 나타낸다. (위 그림에서 부등호)
• 루트와 브랜치 블록에는 키 값을 갖지 않는 레코드로, 가장 왼쪽 끝에 위치한 블록인 LMC(Leftmost Child)가 있다.
• LMC가 가리키는 주소로 찾아간 블록엔 키 값을 가진 첫번째 레코드보다 작거나 같은 레코드가 저장돼있다.
  
  
• 리프 블록에 저장된 레코드는 키값 순으로 정렬됐고, 테이블 레코드를 가리키는 주소값인 ROWID를 갖는다.
• 인덱스 키값이 같으면, ROWID 순으로 정렬된다.
• ROWID는 데이터 블록 주소(DBA, Data Block Address)와 로우번호로 구성되어있어, 이 값을 알면 테이블 레코드를 찾아갈 수 있다.
  
  
• ROWID : 테이블 블록 주소 + 로우 번호
• 데이터 블록 주소 : 데이터 파일 번호 + 블록 번호
• 블록 번호 : 데이터파일 내에서 부여한 상대적 순번
• 로우 번호 : 블록 내 순번
  
  

 

인덱스 수직적 탐색

• 인덱스 스캔 시작지점을 찾는 과정
• 조건을 만족하는 첫 번째 레코드를 찾는 과정이다.
  != 조건을 만족하는 레코드
• 루트를 포함해 브랜치 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소값을 갖는다.
  

  

• 루트에서 시작해서 리프 블록까지 수직적 탐색이 가능한 이유다.
• p.77

 

인덱스 수평적 탐색

• 인덱스에서 본격적으로 데이터를 찾는 과정이다.
• 스캔 시작 지점을 찾았으면, 찾고자 하는 데이터가 더 안 나타날 때까지 인덱스 리프 블록을 수평적으로 스캔한다.
• 인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 갖는다.
  즉, 양방향 연결 리스트 구조다. -> 좌에서 우, 우에서 좌로 수평적 탐색이 가능한 이유다.
  

  

 

결합 인덱스 구조와 탐색

• 두 개 이상의 컬럼을 결합해 인덱스를 만들수도 있다.
• p.80
• 결합 인덱스에서 인덱스 스캔 시작점을 찾을 때, 각 인덱스 컬럼을 모두 비교해서 찾는다는 걸 명심해야한다.
  ex) '여자이면서 김징징'을 찾는거지, '여자들 중에 김징징'을 찾는게 아니다. 
• 인덱스 선두 컬럼을 모두 "=" 조건으로 검색할 때는 어느 컬럼을 인덱스 앞쪽에 두든 읽는 블록I/O 개수가 같으므로 성능도 똑같다.

 

Balanced 의미

• B*Tree에서 B는 Balanced 다.
• delete 작업으로 인덱스가 불균형 상태에 놓일 수 있다고 설명한 자료들이 있는데, B*Tree에서 이런 현상은 절대 발생하지 않는다.