기본 키 색인과 일반 색인의 작동 방식

InnoDB에서는 테이블이 기본 키 순서에 따라 인덱스 형태로 저장됩니다. 이렇게 저장된 테이블을 인덱스 구성 테이블이라고 합니다. InnoDB는 B+ 트리 인덱스 모델을 사용하므로 데이터는 B+ 트리에 저장됩니다.

각 인덱스는 InnoDB의 B+ 트리에 해당합니다.

기본 키 열이 ID인 테이블이 있고 테이블에 k 필드가 있고 k에 대한 인덱스가 있다고 가정합니다.

이 테이블의 테이블 생성문은 다음과 같습니다.

테이블의 R1~R5의 (ID,k) 값은 (100,1), (200) 입니다. ,2), (300) 각각 ,3), (500,5) 및 (600,6), 두 트리의 예시 개략도는 다음과 같습니다

에서 보는 것은 어렵지 않습니다. 리프 노드의 내용에 따라 인덱스 유형은 기본 키 인덱스와 비기본 키 인덱스로 구분됩니다.

기본 키 인덱스의 리프 노드는 전체 데이터 행을 저장합니다. InnoDB에서는 기본 키 인덱스를 클러스터형 인덱스라고도 합니다.

기본 키가 아닌 인덱스의 리프 노드 내용은 기본 키 값입니다. InnoDB에서는 기본 키가 아닌 인덱스를 보조 인덱스 또는 일반 인덱스라고도 합니다.

위의 인덱스 구조 설명에 따라 다음 질문에 대해 논의해 보겠습니다. 기본 키 인덱스 기반 쿼리와 일반 인덱스 기반 쿼리의 차이점은 무엇입니까?

즉, 기본 키가 아닌 인덱스를 기반으로 하는 쿼리는 인덱스 트리를 하나 더 스캔해야 합니다. 이것이 바로 우리가 가능한 한 기본 키 쿼리를 사용하려고 노력해야 하는 이유입니다.

인덱스의 순서를 유지하기 위해 B+ 트리는 새로운 값을 삽입할 때 필요한 유지 관리를 수행해야 합니다. 위 그림을 예로 들면, ID 값이 700인 새 행을 삽입하는 경우 R5 레코드 뒤에 새 레코드를 삽입하기만 하면 됩니다. 새로 삽입된 ID 값이 400이면 상대적으로 번거로울 수 있으므로 후속 데이터를 논리적으로 이동하여 공간을 확보해야 합니다.

더 나쁜 점은 B+ 트리 알고리즘에 따라 R5가 위치한 데이터 페이지가 가득 찬 경우 새 데이터 페이지를 신청한 후 일부 데이터를 그곳으로 옮겨야 한다는 것입니다. 이 프로세스를 페이지 분할이라고 합니다. 이 경우 성능은 당연히 저하됩니다.

성능 외에도 페이지 분할 작업은 데이터 페이지 활용도에도 영향을 미칩니다. 원래 한 페이지에 있던 데이터가 이제 두 페이지로 나누어져 전체 공간 활용도가 약 50% 정도 줄어듭니다.

물론 분열이 있으면 합병도 있겠죠. 인접한 두 페이지가 삭제된 데이터로 인해 활용도가 낮은 경우 데이터 페이지가 병합됩니다. 병합 과정은 분할 과정의 역과정으로 볼 수 있다.

인덱스 유지 관리 프로세스에 대한 위의 설명을 바탕으로 사례를 논의해 보겠습니다.

일부 테이블 생성 사양에서 유사한 설명을 본 적이 있을 수 있습니다. 자동 증가 기본 키가 있습니다. 물론 절대적인 것은 없습니다. 자동 증가 기본 키를 사용해야 하는 시나리오와 사용하지 말아야 하는 시나리오를 분석해 보겠습니다.

자동 증가 기본 키는 자동 증가 열에 정의된 기본 키를 참조하며 일반적으로 테이블 생성 문에서 다음과 같이 정의됩니다.

그럴 필요는 없습니다. 새 레코드를 삽입할 때 ID 값을 지정합니다. 시스템은 현재 최대 ID 값에 1을 더한 값을 다음 레코드의 ID 값으로 가져옵니다.

즉, 자동 증가 기본 키의 데이터 삽입 모드는 앞서 언급한 증분 삽입 시나리오와 정확히 일치합니다. 새 레코드가 삽입될 때마다 이는 추가 작업이며 다른 레코드 이동을 포함하지 않으며 리프 노드 분할을 트리거하지도 않습니다.

비즈니스 로직이 있는 필드를 기본 키로 사용하는 경우 순서대로 삽입하기 어려운 경우가 많으므로 데이터 작성 비용이 상대적으로 높습니다.

성능을 고려하는 것 외에 저장공간 측면에서도 살펴볼 수 있다. 문자열 유형의 ID 번호와 같은 테이블에 실제로 고유한 필드가 있다고 가정할 때 ID 번호를 기본 키로 사용해야 할까요, 아니면 자동 증가 필드를 기본 키로 사용해야 할까요?

기본 키가 아닌 각 인덱스의 리프 노드에는 기본 키 값이 포함되어 있기 때문입니다. ID 번호를 기본 키로 사용하면 보조 인덱스의 각 리프 노드가 약 20바이트를 차지합니다. 정수를 기본 키로 사용하면 긴 정수(bigint)인 경우 4바이트만 차지합니다. 8바이트가 필요합니다.

당연히 기본 키 길이가 작을수록 일반 인덱스의 리프 노드도 작아지고 일반 인덱스가 차지하는 공간도 작아집니다.

따라서 성능 및 저장 공간 측면에서 기본 키를 자동 증가시키는 것이 더 합리적인 선택인 경우가 많습니다.

비즈니스 필드를 기본 키로 직접 사용하는 것이 적합한 시나리오가 있습니까? 아직 일부가 있습니다. 예를 들어 일부 비즈니스 시나리오 요구 사항은 다음과 같습니다.

이것이 일반적인 KV 시나리오라는 점을 눈치채셨을 것입니다.

다른 인덱스가 없기 때문에 다른 인덱스의 리프 노드 크기를 고려할 필요가 없습니다.

이때, "기본 키 쿼리를 사용하도록 시도"하는 원칙이 우선되어야 합니다. 이 인덱스를 기본 키로 직접 설정하면 각 쿼리에 대해 두 개의 트리를 검색할 필요가 없습니다.

——괴짜 시간에서 배우기