index-mysql.md

인덱스 (mySql 기준)

장점

• 검색 쿼리의 속도 효율이 높아진다. (항상 그런 것은 아님)
• 그 결과로 쿼리의 부하가 줄어들어 결국 전체 시스템의 성능이 향상될 수 있다.

단점

• 보통 인덱스가 db 저장 공간의 10~20 % 정도 (최소 5%)를 차지한다.
• 처음 인덱스를 생성하는데 시간이 소요된다.
• 데이터의 변경 작업(insert/update/delete)이 자주 발생하는 경우에는 오히려 성능이 나빠질 수도 있다.

페이지 분할

• 삽입과 수정이 발생할 때, 경우에 따라 인덱스 자료구조의 변경(페이지 분할)이 유도되면서 작업의 효율이 떨어지게 된다.

인덱스 자동 생성 기준

클러스터드 인덱스

• PRIMARY KEY를 지정하면 해당 컬럼으로 클러스터형 인덱스를 생성한다.
• 테이블당 한 개만 생성할 수 있다.
  • 그러므로 어느 열을 이용해 인덱스를 생성하는지에 따라서 시스템의 성능이 달라질 수 있다.
• 행 데이터를 인덱스로 지정한 컬럼(열)에 맞춰서 정렬된 상태로 저장한다.
  • 클러스터드 인덱스의 리프 페이지는 곧 데이터다. 그러므로, 인덱스 자체에 데이터가 포함되어 있다.
• 클러스터드 인덱스는 보조 인덱스보다 검색 속도는 빠르지만, 데이터의 변경 작업에서는 더 느리다.
• 클러스터드 인덱스가 생성되면, 데이터 페이지 전체가 인덱스에 따라서 재정렬되는 작업이 수행된다.
  • 그러므로 이미 대용량의 데이터를 가진 테이블은 업무 시간 외에 클러스터드 인덱스를 생성하도록 한다.

보조 인덱스 (Secondary Index, 논클러스터드 인덱스)

• 테이블당 여러 개 생성할 수 있다.
  • 그러나, 보조 인덱스를 남용하는 경우 역시 시스템의 성능을 악화시킬 수 있으므로, 꼭 필요한 열에서만 생성하는 것이 좋다.
• Unique 속성을 지정하면 해당 컬럼을 이용해 보조 인덱스를 생성한다.
• 보조 인덱스는 데이터 페이지를 활용하지 않고, 별도의 페이지에 인덱스를 구성한다.
  • 그러므로, 보조 인덱스의 리프 페이지는 데이터가 아닌 데이터가 위치하는 주소값(RID)을 갖는다.
• 클러스터드 인덱스와 비교해 검색 속도는 더 느리지만, 데이터의 변경작업에서는 덜 느리다.

B-Tree 구조

인덱스 구조 예시

인덱스가 없는 경우

클러스터드 인덱스(userId)가 생성된 경우

• 데이터 페이지도 인덱스 구조에 포함된다.

보조 인덱스(userId)를 생성하는 경우

• 데이터 영역을 정렬하지 않는다.
• 리프 페이지를 별도로 생성한다.
• 조회시 클러스터드 인덱스에 비해 한 레벨을 더 거쳐서 실제 데이터에 접근하게 된다. (I/O 추가 발생)
• 범위 검색에서도 마찬가지로 클러스터드 인덱스에 비해 추가적으로 I/O가 더 발생하게 된다.

데이터 삽입 예

• 보조 인덱스의 경우 데이터 페이지를 정렬할 필요가 없으므로, 새로운 데이터는 바로 기존 데이터 페이지의 가장 마지막 다음 빈 부분에 삽입이 된다.
  • 즉, 데이터 구조가 크게 변경될 여지가 적어 성능에 주는 부하도 적다.

OLTP/OLAP 시스템에서의 인덱스

클러스터드 인덱스와 보조 인덱스를 동시에 사용하는 경우

• 실무에서 사용하는 대부분의 db 테이블이 여기 해당함

참고 강의

• [2020개정판]이것이 MySQL이다 : https://www.youtube.com/watch?v=THZjyppzu90&list=PLVsNizTWUw7Hox7NMhenT-bulldCp9HP9&index=37