Index

index가 중요한 이유

성능 향상

SELECT *
FROM customer
WHERE first_name = 'Minsoo';

위 쿼리를 실행할 때 first_name 에

• index가 걸려있지 않다면? full scan으로 찾아야 함 → O(n)
• index가 걸려있다면? full scan보다 더 빨리 찾을 수 있음 → O(logN) (B-tree based index)

Index를 쓰는 이유

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

index 개념

• index : 데이터를 빠르게 찾을 수 있도록 도와주는 도구
• 클러스터형 인덱스(Clustered Index)
  • 기본 키로 지정한 열만 클러스터형 인덱스로 지정됨
  • 테이블이 클러스터형 인덱스를 기준으로 자동으로 정렬됨
  • 따로 index를 생성하지 않음 → 추가 디스크 공간이 필요 없음
• 보조 인덱스(Secondary Index)
  • 고유 키(UNIQUE)로 지정하거나 직접 index를 만들면 생성되는 index
  • 추가 디스크 공간이 필요함
  • 테이블이 보조 인덱스를 기준으로 자동으로 정렬되지 않음
• 특정 테이블에 어떤 index가 걸려있는지 확인할 때

index 장단점

장점

• SELECT 문으로 검색하는 속도가 매우 빨라짐
• 빠른 응답속도를 얻을 수 있음 → 전체 시스템의 성능 향상

단점

• 인덱스도 공간을 차지하기 때문에 데이터베이스에 추가적인 공간이 필요함
• 처음에 인덱스를 만드는데 시간이 오래 걸릴 수 도 있음
• 데이터 변경 작업(추가, 수정, 삭제)이 자주 일어나면 오히려 성능이 나빠질 수도 있음

index 동작 방식

B-tree (Balanced-tree)기반의 index가 동작하는 방식

기본 구조

• MySQL에서 노드(node) = 페이지
• 모든 데이터는 리프 노드에 저장됨
• 루트 노드에는 각 리프 노드의 대표되는 값들을 저장

데이터 조회(검색) 시

무조건 루프 페이지부터 검색한 리프 페이지로 이동

데이터 추가 시 - 페이지 분할

만약 위와 같이 2번째 리프 페이지가 모두 차있는 상태에서 ‘GGG’라는 값을 넣게 된다면FFF 다음에 GGG가 삽입되어야 하므로 새로운 페이지가 필요함 → 페이지 분할
페이지 분할을 수행한 후에 새로운 페이지를 루트에 등록해야 함

❗ 데이터가 많아질 수록 데이터를 추가할 때, 수정할 때(순서가 바뀜), 삭제할 때 수행해야 할 처리가 많아짐

클러스터형 인덱스 작동 방식

• 클러스터형 인덱스는 따로 index를 생성하지 않고 테이블 자체가 인덱스로 사용됨

보조 인덱스 작동 방식

• 별도의 공간에 index를 생성하고 그 index에서 데이터 페이지로 접근해야하므로 클러스터형 인덱스보다 시간이 오래걸림

index 거는 법

• 아래와 같은 상황일 때 index를 어떻게 만들 수 있는가

  • name에 index 걸어주기 → name은 중복이 허용됨

    CREATE INDEX player_name_idx ON player (name);

  • team_id와 back_number에 함께 index를 걸어주어야 함
    -> team_id와 backnumber를 이용하면 선수들을 unique하게 식별할 수 있음

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

    - 여러 attributes에 대한 index가 있다면 생성할 때 왼쪽에 있던 index 순으로 먼저 정렬됨
    - 위와 같은 경우라면 team_id를 기준으로 먼저 정렬되고 그 다음으로 backnumber 기준으로 정렬된 index를 생성함

• 아래 테이블을 생성할 때 index를 생성하려면

  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)
  );

  • 여러 개의 attributes에 적용하는 index ⇒ multicolumn index, composite index
  • primary key에 자동으로 index가 생성됨

• 특정 테이블에 어떤 index가 걸려있는지 확인할 때

  SHOW INDEX FROM player 

• 쿼리문이 어떤 index를 사용하는지 확인하기 위해서 아래 코드를 실행하면 됨

  EXPLAIN
  SELECT * FROM player WHERE backnumber = 7;

• optimizer가 이상한 index를 사용해서 성능이 안나와서 직접 사용할 index를 정하고 싶다면

  // 가급적 지명한 index 사용해줘
  SELECT * FROM player USE INDEX (backnumber_idx)
  WHERE backnumber = 7;

  // 꼭 지명한 index 사용해줘
  SELECT * FROM player FORCE INDEX (backnumber_idx)
  WHERE backnumber = 7;

  // 지명한 index 사용하지 말아줘
  SELECT * FROM player IGNORE INDEX (backnumber_idx)
  WHERE backnumber = 7;

index 사용 시 참고 사항

• 사용되는 query에 맞춰서 적절하게 index를 걸어주어야 query가 빠르게 처리될 수 있음
• index 막 만들어도 괜찮은가
  • table에 write(추가, 수정, 삭제)할 때마다 index에도 변경 발생 → 성능 저하
  • 추가적인 저장 공간 차지 → 불필요한 index는 만들면 안 됨
• Full scan이 더 좋은 경우
  • table에 데이터가 조금 있을 때 (몇 십, 몇 백건 정도)
  • 조회하려는 데이터가 테이블의 상당 부분을 차지할 때

Reference

• DB 인덱스(DB index) !! 핵심만 모아서 설명합니다 !! - 쉬운 코드 - <index 거는 법>의 이미지 출처
• 혼자 공부하는 SQL - 우재남 저 - <index 동작 방식>의 이미지 출처
• DB 클러스터 인덱스와 넌클러스터 인덱스 개념 총정리 - 코딩 시그널
• 클러스터형 인덱스와 비클러스터형 인덱스의 차이점
• MySQL - 6.2 인덱스 - 인덱스의 내부 작동 - lee9843 - <index 동작 방식>의 이미지 출처