[MySQL] 09. 인덱스

인덱스의 개념

데이터를 좀 더 빠르게 찾을 수 있도록 해주는 도구

인덱스 장점

• 검색 속도가 빨라질 수 있다(아닌 경우도 존재)
• 쿼리의 부하가 줄어 시스템 전체의 성능이 향상될 수 있다.

인덱스 단점

• 대략 데이터베이스의 10% 정도의 추가 공간이 필요하다.
• 처음 인덱스를 생성하는데 많은 시간이 소요될 수 있다.
• Insert, Update, Delete가 자주 일어나는 경우, 성능이 나빠질 수 있다.

 

 

인덱스의 종류와 자동 생성

인덱스의 종류

• 클러스터형 인덱스
  • 영어사전과 같은 책
  • Primary Key 인덱스
  • 테이블당 한 개만 생성할 수 있다.
  • 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬한다.
• 보조 인덱스
  • 테이블당 여러 개를 생성할 수 있다. 
  • Unique 인덱스 (Not Null이 붙어있다면 클러스터형 인덱스이지만, Primary key 인덱스가 있다면 primary key 설정 열이 우선 클러스터형 인덱스가 된다.)
  • 현재 중복되는 값이 없다고 Unique를 바로 설정하지 말고, 업무 절차상 절대로 중복되지 않을 경우 사용한다.
  • 책 뒤의 <찾아보기>같은 역할

자동으로 생성되는 인덱스

• 테이블 생성 시에 제약 조건 Primary Key 또는 Unique를 사용하면 자동으로 인덱스가 생성된다.
• PRIMARY KEY로 지정한 열로 데이터가 오름차순 정렬된다.

 

 

인덱스의 내부 작동

B-Tree(Balanced Tree)

페이지 분할

• Insert 작업 시 페이지 분할이 발생하기에 MySQL이 느려질 수 있다.
• 페이지가 가득찬 곳에 새로운 데이터를 넣으려 할 때 페이지 분할이 일어난다. (위 그림 첫 번째 페이지에 새로운 데이터를 추가할 때)

클러스터형 인덱스와 보조 인덱스의 구조

• 클러스터형 인덱스
  • 생성 시 데이터 페이지 전체가 다시 정렬된다. (이미 대용량 데이터가 입력된 상태라면 시스템에 부하를 줄 수 있다.)
  • 인덱스 자체에 데이터가 포함되어있다.
  • 보조 인덱스보다 select 속도가 빠르다. (입력/수정/삭제는 느리다)
  • 어느 열에 클러스터형 인덱스를 생성하는지에 따라 성능이 달라질 수 있다.

클러스터형 인덱스 구성 후

• 보조인덱스
  • 데이터 페이지를 건드리지 않고 별도의 장소에 인덱스 페이지를 생성한다.
  • 보조 인덱스를 사용하는 것이 클러스터형 인덱스를 사용하는 것보다 성능 부하가 더 적다.
  • 리프 페이지가 데이터가 아니라, 데이터가 위치하는 주소 값이다
  • 여러 개를 생성할 수 있지만, 남용 시 성능을 떨어뜨린다.

보조 인덱스 구성 후

클러스터형 인덱스와 보조 인덱스가 혼합되어 있을 경우

혼합된 인덱스으이 내부 구성

 

 

인덱스의 생성/변경/삭제

인덱스 생성

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part, ...)
    [index_option]
    [algorithm_option | lock_option] ...

인덱스 제거

DROP INDEX [인덱스 이름] ON [테이블 이름]

• 인덱스를 모두 제거할 때는 되도록 보조 인덱스부터 삭제한다
• 인덱스를 많이 생성해두었는데 활용도가 떨어진다면 과감하게 삭제해야 한다.

 

 

인덱스를 생성해야 하는 경우와 그렇지 않은 경우

• WHERE절에서 사용되는 열에 인덱스를 만들어야 한다.
• WHERE절에서 사용되더라도 자주 사용해야 가치가 있다.
• 데이터 중복도가 높은 열은 인덱스를 만들어도 별 효과가 없다.
• 외래 키를 지정한 열에는 자동으로 외래 키 인덱스가 생성된다.
• JOIN에 자주 사용되는 열에는 인덱스를 생성해주는 것이 좋다
• INSERT/UPDATE/DELETE 작업이 많이 일어난다면 인덱스 생성은 성능에 나쁜 영향을 끼친다.
  • 인덱스를 만들어 SELECT 성능을 높일지, 만들지 않고 INSERT/UPDATE/DELETE 시 영향을 최소화할지 결정해야 한다.
• 클러스터형 인덱스가 테이블에 없는 것이 좋은 경우도 있다.
  • ex) 대용량 데이터가 계속 입력되는 시스템의 경우 (입력 시마다 정렬이 계속 수행되고 페이지 분할이 끊임없이 일어날 수 있다.)
  • Primary key 대신 Unique로 지정하는 편이 좋다.
• 사용하지 않는 인덱스는 제거해야 한다.
  • 주기적인 OPTIMIZE TABLE 구문이나 ANALYZE TABLE 구문으로 조각화를 최소화해야 성능을 최상으로 유지할 수 있다.