[CS] 데이터베이스_인덱스

1️⃣  인덱스의 개념과 설정 기준 이해

인덱스를 쓰는 이유?

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

PLAYER 테이블

id	name	team_id	backnumber
...	...	...	...

 

테이블 생성 후 인덱스 걸기

CREATE INDEX player_name_idx ON player (name);

name 어트리뷰트에 인덱스 걸기

 

CREATE UNIQUE INDEX team_id_backnumber_idx On player(team_id, backnumber);

 

테이블 생성 시 인덱스 걸기

CREATE TABLE player (

id INT PRIMARY KEY,

name VARCHAR(20) NOT NULL,

team_id INT,

backnumber INT,

INDEX player_name_idx (name),

UNIQUE INDEX team_id_backnumber_idx (team_id, backnumber)

);

• 2개 이상으로 구성된 인덱스 = multicolumn index 또는 composite index, 2개 중 왼쪽에 있는 애트리뷰트가 우선순위가 된다. 
• primary key에는 index가 자동 생성된다.

player 테이블에 설정 된 인덱스 확인하기 

SHOW INDEX FROM player;

 

2️⃣ B-Tree와 B+Tree 구조의 차이 (보충 必)

 B-Tree

균형 트리로, 모든 노드가 특정한 규칙을 따라 정렬 되면서 유지되는 자료구조

• 각 노드는 여러 개의 키와 자식 노드를 가질 수 있다.
• 모든 키가 트리의 여러 노드에 저장될 수 있다.
• 이진 탐색 트리는 한쪽으로 편향될 수 있지만, B-Tree는 노드가 가득 차면 자동으로 균형을 맞추도록 설계되어 있다.

B+Tree

B-Tree의 확장 버전으로 검색과 범위 조회 성능을 개선한 자료구조

• 내부 노드는 키만 저장하고, 실제 데이터는 리프 노드에만 저장
  • 리프노드는 트리 구조에서 자식 노드가 없는 가장 아래층의 노드
• 리프 노드끼리 연결 리스트로 연결돼 있다.
• 검색 성능이 일정하고 빠르다.

 

3️⃣ 테이블 인덱스의 장단점

장점

• 검색 속도 향상: WHERE, JOIN, ORDER BY, GROUP BY 성능 최적화
• 랜덤 액세스 최적화: 특정 데이터 탐색 속도 증가
• 트랜잭션 처리 성능 향상: 적절한 인덱스는 동시 처리 성능 개선

단점

• 쓰기(INSERT, UPDATE, DELETE) 성능 저하: 인덱스 갱신이 필요
• 디스크 공간 증가: 인덱스 저장을 위한 추가 공간 필요
• 과도한 인덱스는 오히려 성능 저하: 잘못된 인덱스 설정은 쿼리 실행 계획을 비효율적으로 만듦

 

4️⃣ 다중 컬럼 인덱스와 커버링 인덱스 활용

다중 컬럼 인덱스

• 2개 이상으로 구성된 인덱스를 뜻한다.
• 만약 쿼리문 조건식이 WHERE a AND b 라면 a와 b를 다중 컬럼 인덱스로 걸어주는 것이 효과적이다.
• 하지만 a와 b를 다중 컬럼 인덱스 걸어준 상태에서 조건식이 WHERE a OR b 라면 b 를 찾을 때 풀 스캔이 된다. 따라서 이때는 b를 따로 인덱스 걸어주는 것이 좋다.
• 그러므로 사용되는 query에 맞춰 적절하게 index를 걸어줘야 quert가 빠르게 처리 될 수 있다.

커버링 인덱스

• 조회하는 애트리뷰트를 인덱스가 모두 커버할 때 조회 성능이 더 빠르다.
• 의도적으로 커버링 인덱스를 사용하기도 한다.

INDEX(team_id, backnumber)

team_id	backnumber	ptr
...	..	...

 

SELECT team_id, backnumber FROM player WHERE team_id = 5;

 

굳이 테이블까지 안 가도 쿼리 결과를 낼 수 있다.

 

5️⃣ 실행 계획(EXPLAIN)과 쿼리 힌트 활용법

EXPLAIN 키워드를 사용하여 해당 쿼리문이 어떤 인덱스를 사용할지 확인할 수 있다.

EXPLAIN SELECT * FROM player WHERE backnumber = 7;

=>dbms에 존재하는 optimizer가 알아서 적절하게 index를 선택 해준다.

 

직접 index를 고르고싶다면? USE 또는 FORCE 키워드를 사용한다.

SELECT * FROM player USE INDEX (backnumber_idx) WHERE backnumber = 7; 

SELECT * FROM player FORCE INDEX (backnumber_idx) WHERE backnumber = 7; (use 보다 좀 더 강함)

 

optimizer가 판단하기에 적절하지 않은 인덱스면 full scan을 함

 

6️⃣ 인덱스 최적화와 주의사항

불필요한 index를 만들지 않는다.

• table에 write할 때마다 index도 변경이 발생한다.
• 추가적인 저장 공간을 차지하게 된다.

Full scan이 더 좋은 경우도 있다.

• table에 데이터가 조금 있을 때 (몇십 ~ 몇백건)
• 조회하려는 데이터가 테이블의 상당 부분을 차지할 때 

order by나 group by에도 index가 사용될 수 있다.

foreign key에는 index가 자동으로 생성되지 않을 수 있다. (join 관련)

이미 데이터가 몇 백만 건 이상 있는 테이블에 인덱스를 생성하는 경우 DB 성능에 안 좋은 영향을 줄 수 있다.

 

---

참고자료

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