[DB] Index(인덱스)

뚜비·2023년 8월 2일

DB

목록 보기

5/6

Index란?

데이터베이스에서 테이블의 검색 속도를 향상시켜줄 수 있는 객체, 자료구조이다.
Index에는 데이터의 키와 해당 데이터 레코드 위치를 가리키는 포인터(물리적 주소)가 있음
인덱스는 테이블 내의 1개의 컬럼, 혹은 여러 개의 컬럼을 이용하여 생성될 수 있다.
- 위의 그림의 경우 Employee 테이블에서 EMPNO 컬럼에 EmpnoIndex를 걸었음.

🤔 책의 마지막 장에 있는 찾아보기
'페이지 번호'를 통해 책의 본문 안에 내가 찾고자 하는 '키워드'을 빠르게 찾을 수 있음.
↔ DBMS에서는 컴퓨터가 '인덱스'를 보고 '데이터 레코드'를 빠르게 찾을 수 있음.

인덱스의 필요성 및 특징

장점
: select 검색 속도를 크게 향상
: 인덱스는 실제 테이블의 데이터 크기에 비해 작아 메모리에 적재하기 쉽다.

🤔생각해보자
메모리(RAM) 최대 용량 1GB이고 하드디스크는 100GB의 데이터베이스가 존재하고 원하는 데이터가 데이터베이스의 맨 끝에 위치한다 가정
-> 100GB의 데이터를 모두 탐색해야 원하는 데이터를 얻을 수 있음
-> 메모리는 1GB라 100GB 데이터를 100개로 나눈 후 100번 꺼내와야 함 (탐색 성능 저하)
-> 인덱스만 메모리에 적재하고 원하는 데이터 물리적 주소(하드디스크)에 접근 (탐색 성능 향상)
단점
: 데이터 조회에는 플러스지만, 데이터 변경이 자주 일어나면 오히려 성능 감소된다. insert, update, delete같은 데이터 변경 쿼리가 잦은 경우 paging이 빈번해져 성능이 악화될 수 있다.
: 인덱스 생성 시 DB 크기의 약 10% 정도되는 추가 공간이 필요하다. 잘못하면 불필요한 저장 공간 낭비

B-Tree 알고리즘

인덱스는 B-Tree 라는 자료구조로 이루어짐
B-Tree는 이진 탐색 트리의 일반화된 트리로, 자식 노드가 2개 이상인 트리이다.
루트 노드(최상위 노드), 리프 노드(최하위 노드), 브랜치 노드(루프 노드와 리프 노드 사이에 있는 중간 노드)로 나뉨

키 57에 해당하는 데이터를 검색해보자.
-> 트리 탐색은 맨 위 루트 노드부터 탐색이 일어나며 브랜치 노드를 거쳐 리프 노드까지 내려옴
-> '57보다 같거나 클 때까지 <='를 기반으로 처음 루트 노드에서는 39, 83 이후 아래 노드로 내려와 46, 53, 57 등 정렬된 값을 기반으로 탐색하는 것을 확인할 수 있음
-> 루트 노드 부터 시작하여 마지막 리프 노드에 도달해서 57이 가리키는 데이터 포인터를 통해 결괏값을 반환

인덱스가 효율적인 이유와 대수확장성

효율적인 이유
: 효율적인 단계를 거쳐 모든 요소에 접근할 수 있는 균형 잡힌 트리 구조와 트리 깊이의 대수확장성 때문

🤔 대수확장성이란

트리 깊이가 리프 노드 수에 비해 매우 느리게 성장하는 것을 의미

기본적으로 인덱스가 한 깊이씩 증가할 때마다 최대 인덱스 항목의 수는 4배씩 증가 (트리 깊이 1 : 4 -> 트리깊이 2 : 16)

트리 깊이 열 개짜리로 100만 개의 레코드를 검색할 수 있다는 의미,
실제 인덱스는 이것보다 훨씬 더 효율적!

인덱스 만드는 방법

인덱스 만드는 방법은 데이터베이스마다 다름

MySQL

클러스터형(Primary) 인덱스와 세컨더리(보조) 인덱스가 있음
클러스터형(Primary) 인덱스
; 특정 나열된 데이터들을 일정 기준으로 정렬해주는 인덱스
: 테이블당 하나씩 설정할 수 있음
: 리프 노드 자체가 데이터임
: primary key 옵션으로 기본키로 만들면 해당 기본키를 클러스터형 인덱스로 만든다.
: 기본키를 만들지 않고 unique not null 옵션을 붙이면 해당 컬럼을 클러스터형 인덱스로 만든다.

위의 그림의 경우

userID를 Primary Key로 지정하여 클러스터형 인덱스로 구성
인덱싱을 하면 각 데이터 페이지의 첫 번째 데이터만 매핑시킨 루트 페이지 형성 이후 Leaf 페이지에 연결
클러스터 인덱스(userID)가 만들어지면서 기존 페이지들이 데이터의 알파벳 순으로 오름차순 정렬

세컨더리(보조) 인덱스
: 개념적으로 후보키에만 부여할 수 있는 인덱스
: create index... 명령어를 기반으로 생성
: 하나의 인덱스만 생성할 것이라면 클러스터형 인덱스를 만드는 것이 세컨더리 인덱스를 만드는 것보다 성능이 좋음
: 보조 인덱스로 여러 개의 필드 값을 기반으로 쿼리를 많이 보낼 때 생성해야 하는 인덱스
EX) age라는 하나의 필드만으로 쿼리를 보낸다면 클러스터형 인덱스만 필요.. 하지만 age, name, email 등 다양한 필드를 기반으로 쿼리를 보낼 때는 세컨더리 인덱스를 사용해야 함
: 기존의 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성

위의 그림의 경우
1. 루트 페이지를 생성 그러나 데이터 페이지에 바로 연결시키지 않고 따로 리프 페이지를 만들어 매핑 후 정렬
2. 보조 인덱스는 데이터 페이지는 변화하지 않고 그대로 존재

MongoDB

document를 만들면 자동으로 ObjectID가 형성
해당 키가 기본키로 설정
세컨더리키도 부가적으로 설정해서 기본키와 세컨더리키를 같이 쓰는 복합 인덱스를 설정할 수 있음

인덱스 최적화 기법

데이터베이스마다 조금씩 다르지만 기본적인 골조는 똑같기 때문에 특정 데이터베이스를 기준으로 설명해도 무방
MongoDB를 기반으로 인덱스 최적화 기법 설명 이를 기반으로 다른 데이터베이스에 적용 가능

1. 인덱스는 비용이다

인덱스는 두 번 탐색하도록 강요
: 인덱스 리스트, 그 다음 컬렉션 순으로 탐색하기 때문이며, 관련 읽기 비용이 들게 됨.
컬렉션이 수정되었을 때 인덱스도 수정되어야 함
: 책의 본문이 수정되었을 때 목차나 찾아보기도 수정해야 함
: 이때 B-트리의 높이를 균형 있게 조절하는 비용, 데이터를 효율적으로 조회할 수 있도록 분산시키는 비용이 듬
쿼리에 있는 필드에 인덱스를 무작정 다 설정하는 것은 답이 아님
컬렉션에서 가져와야 하는 양이 많을수록 인덱스를 사용하는 것은 비효율적

2. 항상 테스팅하라

인덱스 최적화 기법은 사용하는 서비스의 객체의 깊이, 테이블 양 등에 따라 달라진다. 그렇기에 항상 테스팅하는 것이 중요하다. explain() 함수를 이용해서 인덱스를 만들고 쿼리를 보낸 이후 테스팅하면서 걸리는 시간을 줄여야한다.

EXPLAIN
SELECT * FROM t1
JOIN t2 ON t1.c1=t2.c1

3. 복합 인덱스는 같음, 정렬, 다중 값, 카디널리티 순이다

여러 필드를 기반으로 조회할 때 복합 인덱스를 생성한다. 복합 인덱스를 생성할 때는 같음, 정렬, 다중 값, 카디널리티 순으로 생성해야 한다. 생성 순서에 따라서 인덱스 성능이 달라지기 때문이다.

같음: "==" 혹은 "equal" 쿼리가 있으면 제일 먼저 인덱스로 생성
정렬: 정렬에 사용되는 필드면 2번째 인덱스로 설정
다중 값: 쿼리 자체가 ">" 이거나 "<" 등 많은 값을 출력하는 다중 값 출력 필드는 3번째로 설정한다.
카디널리티
: 특정 데이터 집합의 유니크(Unique)한 값의 개수, 특정 컬럼의 중복 수치를 나타내는 지표
: 카디널리티가 높은 순서를 기반으로 인덱스를 설정해야 한다.
Ex) 성별의 카디널리티는 남, 여로 2이다. 그에 비해 나이는 여러 카디널리티가 존재하여 나이에 대한 인덱스를 먼저 생성해야 한다.
EX) age와 email이 있을 때 email의 카디널리티가 더 높다 email 필드에 대해 인덱스를 먼저 생성!