2. 인덱스 기본

2.1 인덱스 구조 및 탐색

• 인덱스 구조와 탐색 원리를 설명
• 인덱스의 기본 구조와 탐색 원리조차 모르는 개발자가 많다.
• 강조하고 싶은 내용 : 인덱스 참색 과정이 수직적 탐색과 수평적 탐색 단 2개로 이뤄진다.
• 이것을 이해하면 막연한 인덱스 구조에 대해 그림이 명확해진다.

 

2.1.1 미리 보는 인덱스 튜닝

데이터를 찾는 2 가지 방법

1. full scan
2. index scan
   1. 정렬 data
   2. 위치를 알려줌

• 데이터베이스 테이블에서 데이터를 찾는 방법
  1. 테이블 전체를 스캔한다.
  2. 인덱스를 이용한다.
• 인덱스와 관련해서 튜닝 요소가 매우 많고 기법도 다양하다.

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

• 2가지 이지만 실제로는 컬럼이 여러개 일 경우 index가 된 column 순서가 중요 (성능이 달라짐)

1. 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것
   - 컬럼이 여러개 일 때 순서를 잘 정해서 시작부터 범위를 줄인다.
   - 조건절은 순서가 없지만 index는 순서가 존재한다.
   
   
2. 테이블 액세스 횟수를 줄이기. 랜덤 액세스 최소화 튜닝

 

• 인덱스 튜닝 핵심 요소를 간단히 살펴본 이유 : 정작 튜닝의 핵심을 잘 이해 못 하는 분을 여럿 만났기 때문

 

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

• 데이터베이스 성능이 느린 이유는 디스크 I/O 때문

 

2.1.2 인덱스 구조

• 인덱스 없이 데이터를 검색하려면, 테이블을 처음부터 끝까지 모두 읽어야 한다.
• 인덱스를 이용하면 일부만 읽고 멈출 수 있다.
• 범위 스캔이 가능하다. -> 가능한 이유 인덱스가 정렬 돼 있기 때문
• DBMS: 일반적으로 B*Tree index를 사용한다.
  • 루트와 블랜치 블록에 있는 각 record는 하의 블록에 대한 주소값을 갖는다. - 자기보다 큰 값을 가진다.
  • LMC: leftmost child라는 키값을 갖지않는 record가 없는 그냥 공간인데 값이 없지만 공간만 있는 것이라고 생각하면 된다.
    - lmc가 가리키는 주소는 자기보다 작거나 같은 레코드가 저장되어있다.
• 레코드(row), 키값(column 값)
• 리프 블록에 저장된 각 레코드 : 키값 순으로 정렬, rowId를 갖는다.

sql 블록 에서 record와 key 값 차이점
Record (레코드):
레코드는 데이터베이스 테이블 내에서 하나의 행(row)을 나타내는 단위입니다. 테이블은 여러 개의 레코드로 구성되며, 각 레코드는 테이블의 컬럼(column)에 해당하는 값을 포함하고 있습니다. 예를 들어, "Customers" 테이블의 레코드는 고객 한 명에 대한 정보를 포함할 수 있습니다. 레코드는 데이터베이스 내의 실제 데이터를 나타내며, 각 레코드는 고유한 식별자(primary key)에 의해 식별됩니다.

Key (키):
키는 레코드를 식별하거나 검색하는 데 사용되는 값의 집합입니다. 데이터베이스에서 키는 레코드 간의 관계를 정의하거나 레코드를 빠르게 검색하기 위한 목적으로 사용됩니다. 주요 유형의 키에는 다음과 같은 것들이 있습니다.

Primary Key (기본 키): 각 레코드를 고유하게 식별하는 주요 식별자입니다. 중복된 값을 가질 수 없으며, NULL 값도 허용되지 않습니다. 주로 하나의 컬럼이나 여러 개의 컬럼을 결합하여 기본 키를 구성합니다.

Foreign Key (외래 키): 다른 테이블의 기본 키를 참조하는 컬럼입니다. 외래 키는 관계형 데이터베이스에서 테이블 간의 관계를 구축하는 데 사용됩니다. 이를 통해 데이터의 무결성을 유지하고 관련된 데이터를 조작하거나 검색할 수 있습니다.

Unique Key (고유키): 중복된 값을 허용하지 않는 키로, 한 테이블 내에서 유일한 값을 가집니다. 기본 키와 유사하지만, NULL 값은 하나만 허용됩니다.

Secondary Key (보조 키): 레코드를 빠르게 검색하기 위한 목적으로 사용되는 키입니다. 기본 키나 고유키가 아닌 컬럼에 대한 인덱스를 생성하여 보조 키를 만들 수 있습니다.

요약하자면, "레코드"는 데이터베이스 테이블의 개별 행을 나타내는 단위이며, "키"는 레코드를 식별하거나 관계를 구성하기 위해 사용되는 값의 집합을 의미합니다.


리프 블로게 저장된 레코드가 키값 순으로 정렬되고 RowId를 가진다고 한다
그저 table의 행일 뿐인데 어떻게 RowID를 가지지?

"리프 블록에 저장된 레코드가 키값 순으로 정렬되고 RowID를 가진다"라는 말은 보통 B-트리나 B+ 트리와 같은 인덱스 구조를 의미하는 것으로 추측됩니다. 이러한 트리 구조는 데이터베이스에서 데이터를 빠르게 검색하기 위해 사용되며, 키값에 따라 정렬되어 있는 레코드를 관리합니다.

여기서 "RowID"는 주로 해당 레코드가 물리적으로 어디에 저장되어 있는지를 가리키는 식별자입니다. RowID는 데이터베이스 내부에서 사용되는 값으로, 사용자가 직접 조작하거나 신경쓸 필요가 없는 값입니다. 보통은 데이터베이스 시스템이 자동으로 할당하며, 이를 사용하여 레코드가 어디에 저장되었는지 효율적으로 찾을 수 있습니다.

일반적으로 B-트리나 B+ 트리는 데이터베이스 내의 레코드를 효율적으로 저장하고 관리하기 위해 사용되는 자료구조입니다. 이러한 트리 구조에서 각 노드는 키값 범위를 가지며, 이 키값 범위에 따라 레코드가 정렬되어 저장됩니다. 리프 노드에 저장된 레코드는 키값에 따라 정렬되어 있으며, 각 레코드에는 해당 레코드의 RowID가 포함되어 있습니다. 이 RowID를 사용하여 실제 데이터가 어디에 저장되었는지 빠르게 찾아갈 수 있습니다.

즉, 사용자는 RowID를 직접 다룰 필요 없이 데이터베이스 시스템이 효율적으로 데이터를 관리하고 레코드를 검색하게 됩니다. RowID는 데이터베이스 내부적인 구조를 지원하기 위한 용도로 사용되며, 사용자에게는 추상화된 인터페이스를 제공하기 위해 사용됩니다.

 

2.1.3 인덱스 수직적 탐색

• B* tree에서부터 인덱스를 어떻게 찾는지, rowId가 있기 때문
• 정렬된 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정
• 인덱스 스캔 시작지점을 찾는 과정
• 인덱스 수직적 탐색 : 루트 블록에서부터 시작한다. 루트를 포함해 브랜치 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소 값을 갖는다

2.1.4 인덱스 수평적 탐색

• 본격적으로 데이터를 찾는 과정
• 수평적으로 탐색하는 이유
  1. 조건절을 만족하는 데이터를 모두 찾기 위해서
  2. RowId를 얻기 위해서 - 일반적으로 인덱스를 스캔하고서 테이블도 액세스하기 때문에 알아야한다.

 

2.1.5 결합 인덱스 구조와 탐색

• 2개 이상 컬럼을 결합해서 인덱스를 만들 수도 있다.
• 단일 인덱스가 여러개 일 경우도 순서가 중요
• 결합 인덱스의 경우 순서가 바뀌어도 인덱스 블록 개수가 똑같지만 성능이 달라지므로 순서가 중요하다.

 

• 규칙
  • 결합 index인 경우 동시에 수행 - 해당 인덱스가 가지고 있는 column의 조건절에 한해서
  • 단일  index 들일 경우 조건절 순서대로
  • index가 없는 컬럼은 조건절에서 순서가 밀림
• B tree의 B는 balanced를 의미한다.

2.2 인덱스 기본 사용법

• 인덱스 기본 사용법은 인덱스를 Range Scan하는 방법을 의미

2.2.1 인덱스를 사용한다는 것

• 인덱스를 정상적으로 사용한다
  == 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미
  == 리프 블록 일부만 스캔하는 Index Range Scan 의미

 

2.2.2 인덱스를 Range Scan 할 수 없는 이유

• 인덱스를 어떨 경우에 사용 못하는지를 알고 해당 상황이 아닌 경우를 제외하고 인덱스를 사용 할 것
  1. column의 변화 
  2. like
  3. or
  4. in - in은 옵티마이저가 union all로 변경해줘서 index 사용할 수 있는 경우도 있다.
     optimizer - INLIST Iterator 방식 사용

2.2.3 더 중요한 인덱스 사용 조건

• 결합 인덱스 [ a + b + c ] 의 내부 구성 순서 생각 해보기
  1. 조건절에서 a, b, c column을 가지고 조건을 수행할 경우 동시에 작업이 진행되는 것은 맞지만 index b tree 가 생성될 때, 결합 인덱스 a,b,c의 순서도 성능에 영향을 준다
  2. 인덱스 Range Scan 하기 위한 가장 1st 조건은 인덱스 선두 컬럼이 조건절(where)에 있어야 한다는 사실이다. 물론 가공되면 안된다.
• 인덱스를 Range scan하려면 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있어야 한다.
  == 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan은 무조건 가능
• 하지만 위의 조건이 항상 성능이 좋은 건 아니다. = 인덱스를 탄다 = 인덱스를 Range Scan한다와 같은 의미
  
  
• 풀어서 설명
  • 인덱스를 타서 속도가 빠르겠다고 생각할 수 있지만 인덱스는 가공되지 않는 선두 컬럼만 조건절에 존재시 무조건 탈수 있는 것이기 때문에 이것만으로 성능이 좋다고 함부로 판단할 수 없다.

 

2.2.4 인덱스를 이용한 소트 연산 생략

• 인덱스를 Range Scan 할 수 있는 이유는 데이ㅓ가 정렬돼 있기 때문이다.

2.2.5 ORDER BY 절에서 컬럼 가공

• 조건절이 아닌 Order by 또는 select-list에서 컬럼을 가공함으로 인해 인덱스를 정상적으로 사용할 수 없는 경우도 종종 있다.
• ex) order by 변경일자 || 변경순번

2.2.6 SELECT-LIST에서 컬럼 가공

• 정렬 연산을 따로 수행하지 않는다. 수직적 탐색을 통해 조건을 만족하는 가장 왼쪽 혹은 오른쪽 지점의 레코드가 최소, 최대값이기 때문
• root부터 타고 내려가서 leaf block에서 rowID 받아낼 수 있다. leaf block까지 타고 내려가는 과정이 수직적 탐색이다.

2.2.7 자동 형변환 (그냥 쓰지말 것)

• 자동형변환을 하려고하니깐 column이 가공되게 된다. 따라서 인덱스 Range Scan을 할 수 없게 된다.
• 그래서 수동 포맷팅을 하는 습관을 잘 들여야 한다.
• sql 성능 원리를 잘 모르는 개발자는 형변환 함수를 의도적으로 생략하곤 한다. 연산횟수가 줄어 성능이 더 좋지 않을까라고 생각하기 때문이다.
• 성능은 블록 I/O를 줄일 수 있느야 없느냐에 따라 결정이 된다.

2.3 인덱스 확장기능 사용법

• Index Range Scan, Index full Scan, index Unique Scan, Index Skip Scan, Index Fast Full Scan

2.3.1 Index Range Scan

• 가장 일반적이고 정상적인 형태의 액세스 방식
• 수직 탐색으로 시작점을 찾고 수평 탐색으로 끝날 때 까지 읽어간다.
• 조건 : 선두 컬럼을 가공하지 않은 상태로 조건절에 사용
• 주의 : 성능은 인덱스 스캔 범위, 테이블 액세스 횟수 줄이는 것이 관건

2.3.2 Index Full Scan

• 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
• 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.
• 예시
  • 해당 경우는 sal >9000 조건에 부합한 사원이 전체 중 극히 일부라면 table full scan보다는 index full scan이 났지만 이것 또한 결국은 index range scan을 못하여 사용하는 차선책일 뿐이다.
  • 가능하면 [ sal + ename ] 인덱스를 생성하는 것이 좋다.
  •  sal > 9000 조건 부합 사원이 많으면 인덱스고 나발이고 tabl full scan이 더 효과적
    -> 힌트로 /*+ first_rows */ 사용하면 무조건 index full scan 때려버림 -> sort 연

create index emp_ename_sal_idx on emp (ename, sal); # 결합 인덱스

select *
from emp
where sal > 9000 # index  존재 - index full scan 돌아감
order by ename; # 선두 index지만 조건절에 넣기에는 부합하지 않아 넣지 않음 - index range scan 불가

# 선두 index가 아닌 sal로 값을 받아옴. 받아올 때 range scan과 마찬가지로 결과집합이 인덱스 컬럼 순으로 정렬
# 그래서 sort order by 연산 생략이 가능

• full 보다 range를 권장하는 이유 : 결국에는 full은 index를 다 읽어야 하고 range는 필요한 index만 읽기 때문
• 하지만 결국에 결과 집합이 10~15% 이상이 되면 table full scan을 돌리는 것이 났다.
• 물론 부분범위 처리를 수행할 것이면 index range scan, index full scan이 극적인 성능 개선 효과를 가져다 준다.

2.3.3 Index Unique Scan

• 수직적 탐색만으로 데이터를 찾는 스캔 방식으로 Unique 인덱스( PK )를 '=' 조건으로 탐색하는 경우에 작동한다.
• Unique 인덱스가 존재하는 컬럼은 중복 값이 입력되지 않게 DBMS가 데이터 정합성을 관리 해 준다.
• 단, 범위 검색 조건으로 검색 할 때 Index Range Scan으로 처리가 된다.
• 또한, Unique 결합 인덱스에 대해 일부 컬럼만으로 검색할 때도 Index Range Scan이 나타난다.

2.3.4 Index Skip Scan

• 목적 : 수평 탐색이 너무 길고 좁힐 수 없을 때 사용 ex) 동일 data가 계속 나올 때 사용
• 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용
• 얘는 조금 더 공부 후, 정리가 필요하다.

2.3.5 Index Fast Full Scan

• 정렬 안하고 막 읽기
  • 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Mulitiblock I/O 방식으로 스캔하기 때문
  • fast full scan : 시퀀싱이 가능, 정렬 없이 걍 읽기 때문
    full scan : 정렬하면서 읽기 때문에 시퀀싱이 불가
  • multiblock I/O 방식을 사용하므로 디스크로부터 대량의 인덱스블록을 읽어야 할 때 큰 효과를 발휘한다.

 

2.3.6 Index Range Scan Descending

• index Range Scan과 기본적으로 동일한 스캔 방식
• 뒤에서부터 앞쪽으로 스캔하기 때문에 정렬 순서만 다를 뿐이다.