[데이터베이스] 인덱스(index)

Cs/DataBase

[데이터베이스] 인덱스(index)

ukkkk7 2024. 2. 23. 10:10

728x90

인덱스(index)란?

데이터베이스 인덱스(index)는 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다.

데이터 = 책의 내용, 인덱스 = 책의 목차, 물리적 주소 = 페이지 번호 라고 생각하면 된다.

index를 사용하는 이유?

만일 위와같은 테이블에서 AIRPORT 컬럼에서 값이 LCY인 것을 조회한다고 가정해보자.

그렇다면 select문은 모든 테이블에 있는 데이터를 조회 후 최종적으로 LCY가 포함된 모든 값들을 반환하는 동작을 수행한다.

데이터가 수십만 개가 들어있는 테이블에서 위와같은 작업이 자주 실행된다면 Full Scan을 하기 때문에 트래픽에 따라 성능이 저하될 수 밖에 없다.

따라서! index를 활용해 자주 조회되는 Coulmn에 대한 Index Table을 따로 만들어 Index테이블에 있는 값들로 결과 값을 조회해 온다.

이처럼 대량의 데이터를 다루며 검색, 조회시 Index를 활용하면 데이터 검색 속도를 크게 향상시킬 수 있다.

💡대부분의 RDBMS는 primary key에 자동으로 index가 생성된다.

Multi Column Index를 생성하는 경우

CREATE UNIQUE INDEX team_id_back_number_indx ON player (team_id, back_number);

📌 where 절에서 and 연산자에 의해 자주 같이 질의되는 컬럼일 경우

만일 team_id = 100이면서 back_number = 7 인 값을 조회하려고 할 때 team_id에 대해서만 index가 생성되어 있다면 최초로는 인덱싱 된 team_id가 100인 데이터를 찾을 것이다. back_number에 대한 full scan을 통해 조거문에 해당하는 번호를 찾는다.

team_id 에대한 데이터가 back_number에 대한 데이터의 수보다 많다면 위와 같은 인덱싱 순서는 효과적이다. 하지만 그 반대라면 오히려 검색 속도를 저하시킬 수 있다.

❗index는 컬럼의 순서에 따라 정렬된다.

위의 index는 team_id -> back_number순으로 index가 걸려있다.

이 상황에서 where back_number = 7 이라는 back_number 한 컬럼에 대한 조건문이 실행된다면 team_id를 기준으로 우선 정렬되어 있기 때문에

성능상 full scan과 같거나 더 안 좋을 수 있다.

💡따라서 multi column index를 생성할 때는

자주 조회되는 컬럼이 있다면 multi column index생성을 고려해봐라
index를 생성할 때 컬럼의 순서를 고려해라
하나의 컬럼에 대한 조건문만 수행되는 경우라면 각각 개별적인 컬럼에 대한 index를 생성하거나 index를 생성하지 않고 조회하는 방법을 선택해라

Index의 자료구조

인덱스의 자료구조는 대표적으로 Hash Table, B-tree, B+tree 자료구조가 있다.

Hash Table

컬럼의 값과 물리적 주소를 (key, value)의 한 쌍으로 저장하는 자료구조

O(1)의 시간복잡도를 갖고 있어 상당히 빠른 검색을 할 수 있다.

But 등호(=) 연산에만 특화되어 있기 때문에 부등호 연산(>,<) 이 자주 사용되는 범위 비교시 데이터베이스 검색을 위해서는 적합하지 않다.

B-tree

자식 노드가 2개 이상인 트리로 균형트리(Balanced Tree)라고 불린다.

최상위 루트 노드에서 리프 노드까지 거리가 모두 동일하다.

B+tree

보편적으로 사용되는 자료구조로 B-tree를 확장 및 개선한 자료구조

leaf노드만 인덱스와 데이터를 함께 가지고 있고 나머지 노드는 데이터를 위한 인덱스(key)만을 갖는다.

인덱스 컬럼은 부등호를 이용한 순차 검색 연산이 자주 발생될 수 있는데 B+tree의 리프노드들은 LinkedList로 연결하여 순차 검색을 용이하게 한다.

💡Index로 B tree 계열의 자료구조를 사용하는 이유

Secondary Storage (SSD or HDD)

데이터베이스는 secondary storage에 저장되는데 secondary storage는 아래와 같은 특징을 갖고있다.

데이터를 처리하는 속도가 가장 느림
디스크 I/O(특히 랜덤 I/O)이 많이 발생하면 느림
데이터를 저장하는 용량이 가장 큼
block 단위로 데이터를 읽고 쓴다.

데이터베이스에서 데이터를 조회할 때 secondary storage에 접근하게 되는데 이 영역에 최대한 적게 접근하는 것이 성능 면에서 좋다. 또한 block 단위로 읽고 쓰기 때문에 연관된 데이터를 모아서 저장하면 더 효율적으로 읽고 쓸 수 있다.

위의 그림과 같이 B-tree 계열의 index와 Binary Search Tree 계열의 index가 있을 때

B-tree 계열이 조회하고자 하는 데이터에 접근하기 위해 secondary storage를 덜 접근하는 것을 눈으로 봐도 알 수 있다. 또한 한 block안에 더 많은 데이터를 담고있기 때문에 불필요한 공간 낭비가 사라진다.

Index의 장단점

장점

데이터가 정렬되어 있기 때문에 테이블에서 검색과 정렬 속도를 향상시킨다.
- 조검검색 where절을 사용할 때 특정 조건에 맞는 데이터를 처음부터 비교해야 하는데 인덱스를 통해 정렬되어 있다면 빠르게 찾아낼 수 있다.
- order by 에 의한 정렬과정을 피할 수 있다. order by는 부하가 많이 걸리는 과정이기 때문에 미리 정렬을 해논다면 부하를 줄일 수 있다.
- MIN, MAX값을 효율적으로 찾을 수 있다. 정렬이 되어있기 때문에 처음부터 비교하는 과정이 필요없다.
중복 데이터를 방지하거나 특정 컬럼의 유일성(Unique)을 보장할 수 있다.

단점

정렬된 상태를 계속 유지시켜야 한다.
- 즉 Insert, Update, Delete작업이 일어난다면 이후 데이터를 다시 재정렬해주는 작업을 반복하기 때문에 추가적인 연산이 발생한다. 또한 추가적인 저장공간을 차지한다.

Index 설정기준

카디널리티(cardinality)

카디널리티가 높을수록( ↑ ) 인덱스 설정에 좋다(인덱스를 통해 불필요한 데이터를 걸러낼 수 있음)
- 카디널리티가 높다는 것은 한 컬림이 갖는 중복 정도가 낮다는 뜻

선택도(selectivity)

선택도가 낮으면( ↓ ) 인덱스 설정에 좋다(일반적으로 5~10%가 적당)
- 선택도가 낮다 = 한 컬럼이 갖고 있는 값 하나로 적은 row가 찾아진다,

선택도 계산법(전체 레코드 중에서 조건절에 의해 선택되는 레코드의 비율)
- ( 컬럼의 특정 값의 row수 / 테이블의 총 row 수 )* 100
- ex) 10개의 데이터 - 고유한 번호 컬럼(number), 2개씩 같은 이름 컬럼(name), 5개씩 같은 나이 컬럼(age)
  - 번호(number)컬럼 선택도: 1/10 = 10%
  - 이름(name)컬럼 선택도: 2/10 = 20%
  - 나이(age)컬럼 선택도: 5/10 = 50%

조회 활용도

조회 활용도가 높으면( ↑ ) 인덱스 설정에 좋다
- 해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값(where의 대상 컬럼으로 많이 활용되는지 판단)

수정빈도

수정 빈도가 낮으면 ( ↓ ) 인덱스 설정에 좋다.
- 인덱스도 테이블이기 때문에, 인덱스로 지정된 컬럼의 값이 바뀌게 되면 인덱스 테이블도 새롭게 갱신되어야 하기 때문이다.

references

https://github.com/devSquad-study/2023-CS-Study/blob/main/DB/db_index.md

https://www.youtube.com/watch?v=liPSnc6Wzfk

728x90