[DB] 인덱스

인덱스 (index)

추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의

검색 속도를 향상

데이터 주소값을 저장해두어 빠르게 찾을수있도록 함

DB 테이블에 인덱스(index)가 필요한 이유

🧐 만약 table에 index를 걸지 않으면 어떻게 될까?

SELECT *FROM customerWHERE first_name = "smith";

원하는 데이터를 찾고 싶을 때 table 전체를 full scan 해야 한다.

즉, first_name에 index가 걸려있지 않다면 “smith"을 찾기 위해서는 모든 데이터를 하나씩 확인해야 한다.

full scan은 시간이 오래 걸리기 때문에 서비스에 좋지 않은 영향을 끼친다.

💡 index를 쓰는 이유

• 조건을 만족하는 튜플(들)을 하기 위해서!
  • 빠르게 조회
• 빠르게 정렬(order by)하거나 그룹핑(group by)하기 위해서!

[ 인덱스 설정 : MySQL ]

1. 이미 테이블과 데이터가 존재하는 경우

다음과 같은 테이블이 있다고 가정하자.

CREATE TABLE PLAYER (  id INT PRIMARY KEY,  name VARCHAR(255) NOT NULL,  team_id INT NOT NULL,  back_number INT NOT NULL);

그리고 다음과 같은 두 가지 쿼리문이 있다.

SELECT * FROM player WHERE name = "Sonny";
SELECT * FROM player WHERE team_id = 105 and back_number = 7;

이때 이렇게 index를 생성할 수 있다.

두 번째 쿼리문의 index는 player 테이블에 각 데이터를 유니크하게 식별할 수 있어 UNIQUE INDEX로 생성했다.

-- single column index
CREATE INDEX player_name_idx ON player (name);
-- multi column index
CREATE UNIQUE INDEX team_id_back_number_idx ON player (team_id, back_number);

2. 테이블 생성 시 index 생성

CREATE TABLE PLAYER (  
id INT PRIMARY KEY,  
name VARCHAR(255) NOT NULL,  
team_id INT NOT NULL,  
back_number INT NOT NULL,  
INDEX player_name_idx (name),  
UNIQUE INDEX team_id_back_number_idx (team_id, back_number));

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

[ Multi Column Index ]

1) 고려사항

위의 예시에서 multi column index를 생성했다.

-- multi column index
CREATE UNIQUE INDEX team_id_back_number_idx ON player (team_id, back_number);

어떤 경우에 multi column index 생성을 고려해야 할까

• WHERE절에서 AND 연산자에 의해 자주 사용되는 칼럼일때
  • WHERE team_id = 105 and back_number = 7
  • 만약 team_id에만 index가 걸려있다면 team_id가 105인 데이터 중에서 back_number를 찾는 것은 team_id=105은 데이터들 중에서 full scan이기 때문에 index 설정을 고려해봐야 한다.

어떻게 정렬될까

• index를 생성할 때 칼럼의 순서에 따라 정렬된다.
  • 현재 예시에서는 INDEX(team_id, back_number) 순으로 index가 걸려있다.
  • 만약 WHERE back_number=7 조건문이 들어오면 성능은 어떨까?
    • 현재 multi column index는 우선 team_id를 기준으로 정렬되어 있다. 그래서 성능이 full scan과 같거나 더 좋지 않을 수도 있다.
    • 그래서 이럴 경우에는 back_number만 single column index를 생성하여 조회하거나 인덱스를 타지 않는 방법이 있다.
    • 따라서, multi column index를 생성할 때 순서에 따라 성능이 달라질 수 있기 때문에 이 점을 고려하여 개발해야 한다.

2) 장점: Covering Index

위의 예시처럼 **INDEX(team_id, back_number)**이 걸려있다. 이때 다음과 같은 쿼리가 들어온다.

SELECT team_id, back_number FROM player WHERE team_id = 5;

모든 Attribute를 가져오는 것이 아니라 team_id, back_number 두 가지 정보만 가지고 온다.

그러면 인덱스에서 검색한 이후 물리적인 데이터 블록을 읽을 필요가 없다.

(인덱스 테이블만 읽으면 됨)

정리하면,

• 조회하는 attribute(s)를 index가 모두 cover할 때
• 조회 성능이 더 빠르다.

✔️ Index 구조

[ Single-Level Ordered Indexes ]

• 엔트리는 탐색키와 값에 대한 포인터로 구성되어 있음 
• 엔트리들은 탐색 키 값의 오름차순으로 정렬되어 있음

1) Primary index (기본 인덱스) | sparse index

• 탐색 키 값에 따라 정렬된 데이터 파일에 대해 정의
• 탐색 키 == 테이블의 기본 키(primary key)인 인덱스

Dense index: 모든 key value에 대해 index entry를 준다. 즉, 모든 레코드에 대해 색인을 만든다.

Sparse index: 몇몇 값에 대해서만 entry를 만든다. 대부분 기본적으로 sparse index를 사용한다. Primary index도 sparse index이다.

2) Clustering index (클러스터링 인덱스) | sparse index

• 탐색 키 값에 따라 정렬된 데이터 파일에 대해 정의
• 많은 레코드가 ordering field에 대한 공통된 값을 가질 경우 사용할 수 있다.
• ex) 숫자의 경우 (1,1,1,2,2,3,3,3,3,3,4,4,4,5,5,5,)

3) Secondary index (보조 인덱스) | dense index

• 다른 인덱스를 돕는 보조 인덱스이며 레코드가 어디 위치한지만 알려주는 역할
• 주키가 아니라 보조 키를 이용하여 추가적인 방법으로 원하는 값을 가져올 수 있다.

[ Multi-level Indexes ]

• 인덱스 자체가 큰 경우 인덱스를 탐색하는 시간도 오래 걸릴 수 있다.
• 인덱스 엔트리를 탐색하는 시간을 줄이기 위해서 Single-Level Ordered Indexes를 디스크 상의 하나의 순서 파일로 생각하고, 이것에 대해 다시 인덱스를 정의할 수 있다.
• 가장 상위 단계 인덱스를 마스터 인덱스(master index)
• 대부분은 B+트리를 사용한다.

✔️ Point 2 : 동작 방식 (자료구조)

그럼 index는 어떤 자료구조를 쓰기 때문에 조회가 빠를까. 그리고 항상 조회 성능이 빠를까? 이러한 궁금증을 해소하기 위해 자료구조에 대해서 살펴보자.

DB index에 자주 쓰이는 자료구조는 B-Tree, B+Tree, Hash Table이다.