쿼리 튜닝의 기본이다.

인덱스란?

• 많은 곳에서 책의 ‘색인’으로 설명된다.
  • 책의 내용: 데이터 파일
  • 책의 ‘색인’으로 알아낼 수 있는 페이지 번호: 데이터 파일에 저장된 레코드의 주소
• 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸리므로, 인덱스를 만들어 사용한다.
  • 칼럼(또는 칼럼들)의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍(key-value)으로 삼아 인덱스를 만든다.

인덱스의 특징

정렬

• DBMS의 인덱스는 칼럼의 값을 주어진 순서로 미리 정렬해서 보관한다.
  • 최대한 빠르게 찾아갈 수 있게 정렬한다.
  • 책의 색인이 가나다 순으로 정렬되는 것과 같다.

자료 구조

• DBMS의 인덱스는 저장되는 칼럼의 값을 이용해 항상 정렬된 상태를 유지한다.
  • 프로그래밍 언어의 SortedList와 같은 자료 구조
• 데이터 파일은 저장된 순서대로 별도의 정렬 없이 그대로 저장해 둔다.
  • 프로그래밍 언어의 ArrayList와 같은 자료 구조

인덱스의 장단점

SortedList 자료 구조의 장단점을 통해 인덱스의 장단점을 살펴볼 수 있다.

• 데이터가 저장될 때마다 항상 값을 정렬해야 하므로 저장하는 과정이 복잡하고 느리다.
  • 인덱스가 많은 테이블은 INSERT, UPDATE, DELETE 문장의 처리가 느려진다.
• 값을 조회 시, 이미 정렬되어 있어서 아주 빨리 원하는 값을 찾아올 수 있다.
  • 인덱스를 가지고 있으면 SELECT 문장은 매우 빠르게 처리할 수 있다.
• 결론적으로 DBMS에서 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다.

인덱스 추가 시 주의점

• 테이블의 인덱스를 하나 더 추가할지 말지는 다음에 따라 결정해야 한다.
  • 데이터의 저장 속도를 어디까지 희생할 수 있는지
  • 읽기 속도를 얼마나 더 빠르게 만들어야 하느냐
• 인덱스가 많아지면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 오히려 역효과만 불러올 수 있다.
  • SELECT 쿼리 문장의 WHERE 조건절에 사용되는 칼럼을 모드 인덱스로 생성하는 행위는 하지 말자.

인덱스의 분류

참고 서적의 저자가 분류한 기준에 따라 나누었다.
여기서 키(Key)와 인덱스(Index)는 같은 의미로 사용한다.

역할 별 분류

• 프라이머리 키 (Primary key)
  • 그 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스
  • 테이블에서 해당 레코드를 식별할 수 있는 기준값이다. (=식별자)
  • NULL 값과 중복을 허용하지 않는다.
• 보조 키 (세컨더리 인덱스, Secondary key)
  • 프라이머리 키를 제외한 나머지 모든 인덱스
  • 유니크 인덱스는 별도로 분류하기도 하고 세컨더리 인덱스로 분류하기도 한다.

기능 별 분류

자세한 것은 뒤에서 살펴본다.

• 전문 검색용 인덱스
• 공간 검색용 인덱스
• 그 외의 수많은 인덱스가 있겠지만, MySQL을 사용할 때는 위 두 가지만으로 충분하다.

데이터를 관리하는 방식 (알고리즘)

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

중복 값의 허용 여부

• 유니크 인덱스 (Unique)
  • 같은 값이 1개만 존재
• 유니크하지 않은 인덱스 (Non-Unique)
  • 같은 값이 1개 이상 존재

유니크 인덱스에 대해 동등 조건(Equal, =)로 검색한다는 것은 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 낸다.

유니크 인덱스로 인한 MySQL의 처리 방식의 변화나 차이점이 상당히 많다.

Reference

참고 서적

📔 Real MySQL 8.0