인덱스의 동작 방식에 대해서 혼동되는 개념이 있어서 가볍게 정리해보았습니다.
자세한 내용은 다루지 않았고, 정확한 정보가 아닐 수 있습니다. 추측도 많습니다.
학교 수업에서 배웠던 자료와 인터넷에서 접하는 자료의 내용이 달랐습니다.
수업으로 배웠던 Secondary index의 동작은 간략히 이러합니다.
- 인덱스 트리의 리프 노드에는 버킷의 포인터가 있다.
- 버킷에는 실제 데이터 레코드에 접근할 수 있는 포인터가 있다.
- 레코드는 물리적으로 clustering index로 정렬되어있다.
- 따라서 Secondary index는 반드시 dense index다. (secondary index key로 정렬된 인덱스와 달리, 실제 레코드는 물리적으로 primary key로 정렬되어서 랜덤하게 저장되어있기 때문)
이를 통해 Secondary index가 primary index(clustering index)보다 느린 이유를 유추해보면,
- 물리적으로 데이터 레코드는 clustering index의 키 순서로 정렬되어있다.
- 따라서 범위 내의 레코드를 디스크에서 읽어오더라도, 메모리에 블록 단위로, 순차적으로 읽어오기 때문에 원하는 레코드를 빠르게 찾아낼 수 있다.
- 하지만 secondary index의 키 순서는 실제 데이터 레코드의 순서와는 무관하다.
- 따라서 secondary index로 범위 내의 데이터 레코드를 읽어올 때는 랜덤하게 디스크에 접근해야한다.
- 디스크에 접근하는 횟수도 많아질 것이고, 데이터를 찾는 시간도 더 오래 걸릴 것이다.(seek, 회전 지연 등)
- 이러한 비용은 비싸다.
즉, 인덱스 트리의 리프 노드는 데이터가 아니라 포인터를 가지고 있는 것이었습니다.
그런데 최근 커버링 인덱스를 공부하면서 관련 자료를 찾다보니, 제가 알던 인덱스 동작 방식과 달랐습니다.
secondary index는 clustering index의 키를 알고 있고, 이를 이용하여 두 번 인덱스를 타게 됩니다(index key외에 다른 데이터가 필요하다면).
또한, clustering index는 레코드 포인터가 아니라 데이터를 가지고 있습니다.
인덱스 트리의 리프 노드는 포인터가 아니라 데이터를 가지고 있던 것입니다.
처음에는 데이터베이스 벤더 사 또는 버전의 따른 차이인줄 알았습니다.
관련 자료를 찾아보니 스토리지 엔진 차이인 듯 합니다.
최근 알게된 인덱스의 동작 방식은 InnoDB의 방식이었습니다.
MyISAM에서는 기존에 알던 방식으로 동작된다고 합니다.
애초에 MyISAM은 primary index와 secondary index가 구분되지 않는다고 합니다.
어떤 인덱스 키에 의해서 정렬되는 게 아니라, 삽입된 순서로 저장되는 것입니다.
MyISAM처럼 인덱스가 데이터에 접근하는 포인터를 직접 가지고 있는 방식은, 레코드가 디스크에서 다른 위치로 이동될 때 인덱스를 다시 작성해줘야하는 비용이 있습니다.
이를 피하기 위해 InnoDB 방식을 사용한다고 합니다(다른 이유도 많겠지만).
일단 수업에서 사용됐던 교재는 Database System Concepts 7th Edition 였는데,
교재를 가지고 있던 것은 아니고 요약된 강의 자료로만 학습을 했어서 무엇을 기반으로 작성된지는 잘 모르겠습니다.
그냥 개념적인 설명이었나봅니다.
결국 정리해보면, 스토리지 엔진에 따라서 인덱스 동작 방식이 달랐던 것입니다.
* 참고자료
https://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/
https://docs.oracle.com/cd/E17275_01/html/programmer_reference/am_second.html
* 잘못된 정보가 있을 수 있습니다. 오류가 있으면 지적 부탁드립니다.