인덱스

인덱스란

---

• 책의 맨 처음 맨 뒤에 있는 "색인"이라고 한다.
• 데이터 == 책의 내용 , 데이터가 저장된 레코드의 주소 == 페이지 번호(인덱스 목록)
• DBMS도 테이블의 모든 데이터를 full scan 하면 시간이 오래 걸린다.
• 그러므로 컬럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만들어 두는 것이다.
• DBMS의 인덱스는 항상 정렬된 상태를 유지한다.
  • 장점 : 값을 탐색하는데 빠르다.
  • 단점 : 추가적 메모리 공간 필요,삽입/삭제 시 느리다.
    • I,U,D시 기존의 DB 정보 뿐만 아니라, 인덱스 정보도 갱신해주어야 하기 때문에 성능이 떨어질 수 있다.
    • 즉 검색 성능과 삽입 삭제 성능의 트레이드 오프의 관계이다(Trade-Off)
• DBMS의 인덱스는 데이터의 저장 성능을 희생하고 ,읽기 속도를 높이는 기능.
• Select 쿼리 문의 where 조건절에 사용되는 칼럼이라고 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 커져 오히려 역효과만 불러올 수 있다.

 

 

 

인덱스 자료구조

---

• B+ /B- Tree 인덱스 알고리즘
  • 인덱스 칼럼의 값을 변형하지 않고 (사실 값의 앞부분만 잘라서 관리).
  • 원래의 값을 이용해 인덱싱하는 알고리즘
• Hash 인덱스 알고리즘
  • 컬럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원.
  • 하지만 값을 변형해서 인덱싱하므로, 특정 문자로 시작하는 값으로 검색을 하는 전방 일치와 같이 값의 일부분만으로 검색하고자 할 때는 해시 인덱스 사용이 불가하다.
  • 주로 메모리 기반의 데이터베이스에서 많이 사용한다.
• 왜 index는 B-Tree를 사용하는것일까?
  • == 일떄 hash 는 O(1)로 더 효과적이라고 생각한다.
  • 하지만 <,> 부등호 연산도 존재하므로 문제가 발생한다.
  • == 동등연산에서는 특화되어있지만 부등호의 연산의 문제가 발생하여 적합하지 못하다.
  •  
   

B-Tree

• 브랜치 블록은 분기를 위한 목적으로 활용.
• 리프 블록은 인덱스를 구성하는 컬럼의 데이터와 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자로 구성되어 있다.
• 인덱스는 인덱스를 구성하는 컬럼의 값으로 정렬되어 있다.
• 리프블록은 양방향 링크를 가지고 있어, 오름,내림차순으로 검색이 가능하다.
• < , = 연산에 적합한 구조이다.

인덱스 동작 원리

• 1.  브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽 포인터로 이동.
• 2.  찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동.
• 3.  오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동.

- 하나의 값 탐색 :37을 찾고자 한다면 루트블록에서  50보다 작으므로 왼쪽 포인터로 이동한다.

37는 왼쪽 브랜치 블록의 11과 40 사이의 값이므로 가운데 포인터로 이동한다.

이동한 결과 해당 블록이 리프블록이므로 37이 블록 내에서 존재하는지 검색한다. 

 

- 범위 탐색 : 또 예를 들어 만약 37~50의 값을 찾고 싶다면??

앞에서와 같이 37을 찾은다음에 정렬되어 있는 링크를 따라 50까지 검색해주면 된다. 완전 좋다!

Primary Index vs Secondary Index

---

Cluster란?

• 여러 개를 하나로 묶는다는 의미이다.
• 인덱스에서 클러스 터드는 비슷한 것들을 묶어서 저장하는 형태이다.
• 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에서 착안된 것이다.
• 여기서 비슷한 값들은 물리적으로 인접한 장소에 저장되어 있는 데이터들을 말한다.

 

클러스터드 인덱스는 테이블의 PK 키에 대해서만 적용되는 내용이다.

즉 PK값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터드 인덱스라고 표현한다.

클러스터드 인덱스에서는 PK 값에 의해 레코드의 저장 위치가 결정되며 PK 값이 변경되면 그 레코드의 물리적인 저장 위치 또한 변경되어야 한다. 그렇기 때문에 PK키를 신중하게 결정하고 클러스터드 인덱스를 사용해야 한다.

인덱스의 종류

클러스터 인덱스

1. • 테이블 당 1개씩만 허용.
   • 물리적으로 행을 재배열
   • PK 설정 시 PK칼럼은 자동으로 클러스터드 인덱스 생성.
   • 인덱스 자체의 리프 페이지가 곧 데이터.
     • 즉 테이블 자체가 인덱스이다(따로 인덱스 페이지 만들지 않아도 된다)
   • Insert,Update,Delete 시 항상 정렬 상태를 유지한다.
   • 검색속도가 빠르다. 하지만 I,U,D는 느리다.
   • 30% 이내에서 사용해야 좋은 선택도를 갖는다.

 

넌 클러스터형 인덱스(Nonclustered Index)

• 테이블 당 240개 인덱스를 만들 수 있다.
• 인덱스 페이지는 로그파일에 저장
• 레코드 원본은 정렬되지 않고, 인덱스 페이지만 정렬.
• 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 포인터이기 때문에 클러스터형 보다 검색이 느리지만, I,U,D는 빠르다.
• 인덱스를 생성할 때 데이터 페이지는 그냥 둔 상태에서 별도의 인덱스 페이지를 따로 만들기 때문에 용량을 더 차지한다.
• 3%이내에서 사용해야 좋은 선택도를 갖는다.

결론적으로 클러스터 인덱스는 데이터의 위치를 바로 알 수 있기 때문에 해당 데이터에 빠르게 접근할 수 있다. 넌 클러스터 인덱스는 페이지를 한 번 거쳐서 데이터에 접근하는 방식이므로 탐색이 느리다.

 

RID == Row ID로 ( 해당하는 행의 파일 그룹 번호-데이터 페이지 번호- 행의 순번(데이터 페이지 오프셋) ) 으로 구성되는 포인팅 정보이다.

 

복합 인덱스

---

• 인덱스로 설정하는 필드의 속성이 매우 중요.
• 만약 title,author 순서로 인덱스를 설정하면 title로 탐색경우 index 고려한 효과를 볼 수 있지만, author만으로 탐색을 한다면 index 생성의 효과를 보지 못한다.
• 따라서 sql문을 어떻게 할 것인가가 인덱스를 어떻게 생성할 것인가에 대해 많은 영향을 끼치게 된다.

 

 

인덱스의 성능과 고려사항

---

• select 쿼리의 성능을 월등히 향상시키는데 index가 가장 좋은 것인가?
  • 쿼리문의 성능을 향사키는데, 모든 컬럼에 index 생성하는게 좋지 않을까?
• 결론적으로 그렇지 못하다.
  • index 생성 시 I,D,U 쿼리문을 실행할 때 별도의 과정이 추가된다.
    • Insert 경우 index에 대한 데이터도 추가해야하므로 성능에 손실이 따른다.
    • Delete 경우 index에 존재하는 값은 삭제하고 사용안하는 표시로 남게 된다.
      • 즉 row의 수는 그대로이다.
      • 실제 데이터는 10만건인데, 100만건의 결과를 발생 시킨다.
      • 이렇게 되면 인덱스가 제 역할을 못한다.
    • Update 경우는 insert경우,Delete경우 문제점을 동시에 발생시킨다.
      • 이전 데이터가 삭제되고 그 자리에 새로운 데이터가 들어오는 개념이다.
      • 즉 변경 전 데이터는 삭제되지 않고 insert로 인한 split도 발생하게 된다.
  • 더 중용한 것은 컬럼을 이루고 있는 데이터의 형식에 따라 인덱스 성능이 악영향을 미칠 수 있다.
  • 즉 데이터 형식에 따라 인덱스를 만들면 효율적이고, 비효휼적은 데이터의 형식이 존재하는 경우가 있다는 것이다.
    • ex ) 이름, 나이, 성별 세가지 필드의 테이블.
      • 이름은 온갖 경우의 수가 존재하며, 나이는 INT,성별은 남,녀 두가지 경우이다.
      • 이 경우 어떤 컬럼에 대해서 인덱스를 생성하는 것이 효율적인가?
      • 결론적으로 이름에 대해서만 인덱스를 생성하면 효율적이다.
      • 만개의 레코드를 가진 테이블에 대해 2000단위로 성별에 인덱스를 생성했다고 가정하면,
        • 값의 range가 적은 성별은 인덱스를 읽고 다시 디스크 I/O가 발생하기 때문에 더 비효율적 이다.

 

그럼 어떤 컬럼에 인덱스를 설정하는게 좋을까?

인덱스는 한 테이블당 보통 3~5개 정도가 적당합니다.
물론 테이블의 목적 등에 따라 개수는 달라질 수 있습니다.

인덱스는 컬럼을 정해서 설정하는 것이므로 후보 컬럼의 특징을 잘 파악해야 합니다.
아래 4가지 기준을 사용하면 효율적으로 인덱스를 설정할 수 있습니다.

• 카디널리티 (Cardinality)
• 선택도 (Selectivity)
• 활용도
• 중복도

카디널리티 (Cardinality)

✔️ 카디널리티가 높을 수록 인덱스 설정에 좋은 컬럼입니다.
= 한 컬럼이 갖고 있는 값의 중복 정도가 낮을 수록 좋습니다.

 

※ 중복 정도가 낮으므로 카디널리티가 낮습니다.

 

 

선택도 (Selectivity)

✔️ 선택도가 낮을 수록 인덱스 설정에 좋은 컬럼입니다.
5~10% 정도가 적당합니다.

 

 

활용도

✔️ 활용도가 높을 수록 인덱스 설정에 좋은 컬럼입니다.

해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값입니다.
수동 쿼리 조회, 로직과 서비스에서 쿼리를 날릴 때 WHERE 절에 자주 활용되는지를 판단하면 됩니다.

 

 

중복도

✔️ 중복도가 없을 수록 인덱스 설정에 좋은 컬럼입니다.

중복 인덱스 여부에 대한 값입니다.

 

 

인덱스 설정 기준 - 정리!

                              기준                                                                   정도

카디널리티 (Cardinality)	높을 수록 적합
선택도 (Selectivity)	낮을 수록 적합 (5~10% 적정)
활용도	높을 수록 적합
중복도	없을 수록 적합

 

 

 

ref. yurimkoo.github.io/db/2020/03/14/db-index.html