MySQL의 기본 -6

B-Tree 인덱스의 구조와 실제 저장 방식

B-Tree 인덱스는 데이터베이스에서 가장 널리 사용되는 인덱스 구조로, 인덱스 키 값이 항상 정렬된 상태로 관리된다는 특징이 있습니다. 즉, 인덱스가 적용된 컬럼의 값은 B-Tree 구조체 내에서 자동으로 오름차순(혹은 내림차순)으로 정렬되어 저장되고, 이 덕분에 빠르고 효율적인 탐색이 가능해집니다.

하지만 여기서 한 가지 오해하기 쉬운 점은, 인덱스의 키 값이 정렬되어 있다고 해서 데이터 파일 자체의 레코드도 항상 같은 순서로 저장된다는 것은 아니라는 점입니다.

많은 분들이 데이터가 INSERT된 순서대로 물리적으로 저장된다고 생각하지만, 실제로는 삭제와 재삽입, 빈 공간 재사용 등의 과정 때문에 데이터 파일 내 레코드는 임의의 순서로 저장될 수 있습니다. 만약 테이블에 데이터가 한 번도 삭제되지 않고, 오직 INSERT만 일어난다면 INSERT 순서와 저장 순서가 일치할 수 있지만, 일상적인 운영환경에서는 삭제된 레코드의 공간을 InnoDB 엔진이 효율적으로 재활용하도록 설계되어 있기 때문에 언제나 INSERT 순서와 저장 순서가 일치한다고 볼 수는 없습니다.

Secondary Key에 관해서 책에서 좀 더 읽어봐야 될 것 같아.

다만, InnoDB의 경우에는 프라이머리 키(Primary Key)에 의한 클러스터링 인덱스 방식을 사용합니다. 즉, InnoDB 테이블의 실제 데이터 레코드는 디스크 상에서 프라이머리 키 순서대로 클러스터되어 정렬 저장됩니다. 따라서 InnoDB 테이블은 별도의 옵션 없이도 디폴트로 클러스터링 구조를 갖게 되고, 이 구조 덕분에 프라이머리 키 기반의 레인지 쿼리나 순차 탐색에서 매우 뛰어난 성능을 보입니다.

반면, 보조 인덱스(Secondary Index)는 해당 키 컬럼만을 B-Tree 구조로 관리하고, 실제 데이터 레코드의 모든 컬럼 값을 포함하지는 않습니다. 이 때문에 인덱스의 리프 노드에는 해당 인덱스 키와 함께, 실제 데이터 레코드의 위치를 찾아갈 수 있는 주소 정보(일반적으로는 프라이머리 키 값 또는 Row ID)가 저장됩니다.

따라서 보조 인덱스를 통해 검색할 경우에는 먼저 인덱스 B-Tree에서 키 값을 찾고, 그 다음 해당 주소를 따라가 실제 테이블 데이터에서 나머지 컬럼을 읽어오는 방식이 적용됩니다. 이러한 구조는 빠른 탐색과 효율적인 공간 활용이라는 B-Tree의 장점을 극대화하는 한편, 인덱스를 통한 SELECT는 빠르지만, 인덱스 유지 비용(INSERT, DELETE, UPDATE 시의 추가 연산)과 불필요한 인덱스 과다 생성이 오히려 성능 저하 요인이 될 수 있음을 시사합니다.

결국 인덱스 설계 시에는 프라이머리 키와 보조 인덱스의 특징, 실제 데이터의 저장 방식, 그리고 서비스 특성을 모두 고려해 최적의 인덱스 전략을 세워야 합니다.