[MySQL] 인덱스

디스크 읽기 방식

• 데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건
  • 데이터베이스는 디스크를 사용해 데이터를 저장한다. 이때 대용량의 요청에도 어떻게 고성능을 낼 수 있게 할까?에 대한 의문이다.
  • 즉, 결론부터 말하자면 필요한 데이터만 읽도록 쿼리를 개선해야 한다.

 

랜덤 I/O와 순차 I/O

• 공통점
  • 하드 디스크 드라이브의 플래터(원판)을 돌려 데이터를 읽거나 써야 할 위치로 디스크 헤더를 이동시켜야 함
• 차이점
  • 순차 I/O : 위치를 찾기 위해 디스크의 헤더 1번 움짐임
  • 랜덤 I/O : 위치를 찾기 위해 디스크의 헤더 3번 움직임
• 디스크에 데이터를 쓰고 읽는 데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정됨
• 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정됨
• 일반적으로 쿼리를 튜닝하는 것은 랜덤 I/O 자체를 줄여주는 것이 목적임
  • 쿼리를 튜닝해 랜덤 I/O를 순차 I/O로 바꿔 실행할 방법은 그다지 많지 않음
  • 랜덤 I/O를 줄이는 것은 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미함
• 참고)
  • 데이터베이스 대부분의 작업은 이러한 작은 데이터를 빈번히 읽고 쓰는 것이기 때문에 MySQL 서버에서는 그룹 커밋, 바이너리 로그 버퍼, InnoDB 로그 버퍼 등의 기능이 내장되어 있음

 

인덱스란?

• DBMS의 인덱스는 SortedList, 데이터 파일은 ArrayList와 동일한 자료 구조를 사용한다고 할 수 있음
  • 인덱스는 SortedList와 마찬가지로 저장되는 컬럼의 값을 이용해 항상 정렬된 상태 유지
  • 데이터 파일은 ArrayList와 같이 저장된 순서대로 별도의 정렬 없이 그대로 저장
• DBMS에서 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 대신 데이터의 읽기 속도를 높이는 기능임
  • 데이터가 저장될 때마다 항상 값을 정렬해야 하기 때문

 

인덱스 분류

1. 역할 기준

• 프라이머리 키(프라이머리 인덱스)
  • 레코드를 대표하는 컬럼 값(식별자)으로 만들어진 인덱스
  • NULL 값 허용 X, 중복 허용 X
• 보조 키(세컨더리 인덱스)
  • 프라이머리 키를 제외한 나머지 모든 인덱스

 

2. 데이터 저장 방식(알고리즘) 기준 (대표적 방식인 아래 2가지로 구분)

• B-Tree 인덱스
  • 가장 일반적으로 사용되는 인덱스 알고리즘
  • 컬럼 값을 변형하지 않고 원래 값을 이용해 인덱싱하는 알고리즘
• Hash 인덱스
  • 컬럼의 값으로 해시값을 계산해 인덱싱하는 알고리즘, 매우 빠른 검색 지원
  • 값을 변형해서 인덱싱하므로 전방(Prefix) 일치와 같이 값의 일부만 검색하거나 범위를 검색할 때는 해시 인덱스를 사용할 수 없음
  • 주로 메모리 기반 데이터베이스에서 많이 사용

 

기타

• 데이터 중복 허용 여부 기준
  • 유니크 인덱스, 유니크하지 않은 인덱스
• 인덱스의 기능 기준
  • 전문 검색용 인덱스, 공간 검색용 인덱스