index의 개념

DB에서 검색속도를 향상하기 위해 인덱스를 사용한다.

 

인덱스란 무엇인가?

데이터베이스에서 데이터 검색속도를 향상시키기 위해 사용하는 "자료구조"이다.

인덱스는 테이블의 특정 컬럼에 대한 정렬된 값의 목록과 그에 해당하는 레코드의 위치 정보를 저장한다.
이를 통해 전체 테이블을 검색하지 않고도 원하는 데이터를 빠르게 찾을 수 있음. 책의 목차와 비슷한 역할임.

 

인덱스를 사용하지 않은 Full Tabel Scan은 전체 테이블을 조회하고, 인덱스를 사용한 Index Scan은 인덱스를 먼저 조회 후 필요한 값만 찾아갈 수 있다.

 

 

인덱스의 생성

1. 사용자가 인덱스를 생성 명령어로 요청함

2. DB는 해당 컬럼의 데이터를 읽음

3. 자료구조(B-Tree 또는 Hash 등)에 삽입하며 정렬하거나 해시 값으로 매핑

4. 이 구조는 별도의 파일 또는 테이블 공간에 저장됨

5. 이후부터는 쿼리 실행 시 옵티마이저가 인덱스를 사용해서 빠르게 탐색

 

 

 

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

member_id	name	email	age
1	Alice	alice@domain.com	25
2	Bob	bob@domain.com	30
3	Charlie	charlie@domain.com	28
4	Dave	dave@domain.com	32
5	Evan	evan@domian.com	22

 

이 테이블에 대해다음 쿼리를 날린다.

SELECT * FROM member WHERE name = 'Evan';

 

인덱스가 없는 경우, db는 member 테이블의 모든 row를 처음부터 끝까지

조건에 맞는 name = 'Evan'을 찾을 때까지 fullScan 한다.

Step 1: Alice → 비교 → 불일치  
Step 2: Bob → 비교 → 불일치  
Step 3: Charlie → 비교 → 불일치  
Step 4: Dave → 비교 → 불일치  
Step 5: Evan → 비교 → 일치! → 결과 반환

 

 

인덱스를 사용할 경우를 보자.

우선 다음 명령어로 name컬럼기준의 B-tree 인덱스를 생성한다.

+ 보통 인덱스를 사용하기 위해서는 이처럼 create index를 통해 생성해야 되며,

primary key, unique key 제약조건을 걸어주면 자동으로 해당 컬럼에 대해 자동으로 인덱스를 생성한다.

← 이건 DB가 무결성을 보장하기 위해 인덱스를 필요로 해서 만드는 거

CREATE INDEX idx_name ON member(name);

 

다음과 같은 구조의 인덱스가 생성된다.

          [Charlie]
         /         \
    [Alice]        [Evan]
      \             /
     [Bob]       [Dave]

 

 

DB는 인덱스를 먼저 타고 내려가서 'Evan'이 있는 리프 노드를 빠르게 찾고,

해당 노드에 저장된 RowID 또는 primary key 값을 사용해서 테이블에서 실제 데이터를 조회한다.

 

테이블의 컬럼이 많을수록 인덱스를 사용하면 조회속도의 차이가 클 것이다.

Step 1: Charlie와 비교 → Evan은 오른쪽  
Step 2: Evan → 일치!  
Step 3: Evan의 row 위치를 가져와서 실제 테이블에서 조회

 

 

인덱스를 사용하면 장점만 있는지?

그렇지 않다.

인덱스를 사용하면 조회속도가 빨라진다는 장점이 있지만,

 

추가 저장공간이 필요하다는 단점.

[

인덱스는 테이블과 별도로 자신만의 구조(B-Tree, Hash 등)를 저장하기 때문에 디스크 공간을 추가로 차지함.

컬럼 수가 많거나 복합 인덱스를 많이 만들면, 인덱스 크기가 테이블 크기를 넘어가는 경우도 있음.

특히 읽기보다 쓰기가 많은 시스템에서는 부적절할 수 있음.

예를 들어, 대량의 데이터를 저장하는 로그 테이블에 인덱스를 너무 많이 만들면 디스크가 빨리 차고, 관리가 어려워짐.

],

쓰기 작업 성능 저하 (INSERT, UPDATE, DELETE)

[

인덱스는 데이터가 변경될 때마다 자동으로 업데이트돼야 함.

그래서 인덱스가 많을수록 삽입/수정/삭제 시점마다 추가적인 비용이 발생.

특히 B-Tree는 삽입/삭제 시 균형 유지, 노드 분할/병합 등이 발생할 수 있어서 부하가 큼.

],

인덱스 파편화, 잘못된 인덱스 설계로 인한 성능저하, 과도한 인덱스로 옵티마이저에 부하

등의 단점이 있음.

 

 

인덱스는 항상 사용되는가?

DB에 인덱스를 만들어도, 해당 인덱스가 실제로 쿼리 수행 시 활용될지는 옵티마이저가 판단한다.
옵티마이저는 최적의 실행 계획을 자동으로 선택하며, 인덱스를 쓰는 것이 성능에 더 좋다고 판단되면 사용함.

 

++

여기서 옵티마이저란,

SQL 쿼리 실행을 위한, 데이터를 어떻게 찾을지 최적의 방법(실행 계획)을 선택하는 DBMS에 내장된 컴포넌트.

옵티마이저는 db마다 다르게 구현되어 있는데 아래와 같이 동작한다.

옵티마이저가 쿼리를 받으면

1. SQL 파싱 : 쿼리의 구조 select, from, where 분석함

2. 실행 계획 후보 생성 : 인덱스 사용, 조인 순서 등 실행계획을 시뮬레이션함

3. 비용 계산 : 각각의 실행 방법에 대해 디스크 I/O, 메모리 사용량, CPU 사용량 등 점수를 매김

4. 가장 비용이 낮은 후보를 선택 : 위 점수를 토대로 가장 효율적이라 판단한 실행 계획을 선택

이 과정의 동작을 쿼리 최적화 라고 한다.

 

옵티마이저는 인덱스를 어떻게 선택하는지?

DBMS 옵티마이저는 쿼리를 실행하기 전에 다음과 같은 정보들을 참고해서 인덱스를 사용할지 말지를 결정한다.

 

1. 데이터의 선택도(Selectivity)

선택도란?
→ 조건이 얼마나 좁은 범위의 데이터를 걸러내는지를 의미.
→ 선택도가 높을수록(=결과 행이 적을수록) 인덱스를 사용하려는 경향이 강함.

 

2. 조건절이 인덱스와 맞는 형태인지

WHERE, JOIN, ORDER BY, GROUP BY 등에 사용된 컬럼이 정확히 인덱스 컬럼과 일치하거나 맨 앞 컬럼부터 순서대로 사용되는 경우에만 인덱스가 잘 사용됨.

 

3. 정렬이나 그룹핑이 인덱스 정렬 순서와 맞는지

정렬 순서가 인덱스 순서와 일치할 경우 정렬 없이 결과를 낼 수 있어 인덱스를 우선 사용하려 함.

 

4. 테이블의 크기

데이터가 매우 적은 경우 (예: 수십 ~ 수백 건)
→ 인덱스를 쓰는 것보다 그냥 전체 테이블을 스캔하는 것이 빠르다고 판단할 수 있음.

 

5. 통계정보

DBMS는 테이블과 인덱스에 대한 **통계 정보 (데이터 분포, 행 수 등)**를 바탕으로 판단.

통계 정보가 오래되었거나 정확하지 않으면 잘못된 실행계획을 세워 인덱스를 안 쓸 수도 있음.