인덱스 란?

추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조로 마치 책의 목차처럼 테이블 칼럼을 색인화 하여 검색 속도를 향상시키는 기술이다.

인덱스 종류

• 클러스터형 인덱스
  • 인덱스 생성시 데이터 전체가 다시 정렬된다.
  • 인덱스 자체의 리프페이지가 곧 데이터 페이지이다. (인덱스 자체에 데이터 포함)
  • 보조 인덱스보다 검색 속도는 빠르나 입력/수정/삭제 작업에서는 더 느리다.
  • 성능이 좋으나 테이블에 단 하나만 생성할 수 있다.
  • Primary key로 지정하거나 unique not null 지정시 클러스터형 인덱스가 생성된다. 우선순위는 Primary key가 더 우선시 된다.
• 보조 인덱스 (논 클러스터형 인덱스)
  • 데이터 페이지와 별개로 별도의 페이지에 인덱스를 구성한다.
  • 리프페이지에 데이터가 아니라 데이터가 위치하는 주소값을 가지고 있다.
  • 보조 인덱스를 테이블 당 여러가지 생성할 수 있으나, 남용 경우 시스템 성능을 떨어뜨리는 결과를 초래한다.
  • not null이 아닌 unique 경우 보조 인덱스가 생성된다.
• 둘 혼합 경우
  • 보조 인덱스 리프 페이지에 데이터 주소값이 아닌 클러스터형 인덱스의 키 값을 가지게 된다.
  • 보조 인덱스 조회 후 다시 클러스터형 인덱스 조회
  • 왜 보조 인덱스에 주소값을 넣지 않을까? 주소값으로 리프페이지에 저장되어 있으면, 데이터에 삽입 삭제 등으로 페이지 분할 등 변화가 생길 때, 클러스터형 인덱스의 하나 변화로 클러스터형 데이터페이지가 일단 페이지 변화, 오프셋 등이 대폭 변경이 된다. 이에 따라 보조 인덱스의 주소값 까지 대폭 변경되는 상황이 벌어져 큰 부하가 일어난다. 그래서 둘 혼합 경우 보조 인덱스의 리프페이지는 클러스터형 인덱스의 키 값만을 가지게 된 것이다.

인덱스 적용 방법

• ✅ 클러스터형 인덱스
• ✅ 보조 인덱스

인덱스 성능 비교

조건) 정렬 안된 동일한 데이터가진 테이블 3개를 가지고 비교 (110만건 기준)

아래 명령어를 통해 검색시 몇개의 페이지를 읽었는 지 검색 이전과 이후의 값 차이로 알수있다.

show global status like 'Innodb_pages_read';

• 아무 인덱스도 없을때
• 클러스터형 인덱스가 있을때