커버링 인덱스, 클러스터드 인덱스, 넌클러스터드 인덱스

커버링 인덱스, 클러스터드 인덱스, 넌클러스터드 인덱스

조건문에 PK 인덱스를 사용한 쿼리

해당 쿼리는 커버링 인덱스가 아니다

SELECT *
FROM Test t 
WHERE t.id =1;

Extra가 빈값으로 나왔으니 인덱스 에서 한번에 찾은 정보가 아니라 한번 더 데이터 블록에 접근해서 찾은 정보이다.

해당 쿼리는 커버링 인덱스 이다

SELECT id
FROM Test t 
WHERE t.id =1;

Extra가 Using index로 나왔으니 인덱스 에서 한번에 찾은 정보이다.

그러면 여기서 질문!

아래의 쿼리는 커버링 인덱스 일까?

SELECT data
FROM Test t 
WHERE t.id =1;

아쉽게도 아니다.

Extra가 빈값으로 나왔으니 인덱스 에서 한번에 찾은 정보가 아니라 한번 더 데이터 블록에 접근해서 찾은 정보이다.

그렇다면 커버링 인덱스란 무엇일까?

커버링 인덱스는 실제 데이터의 접근 행위 없이 인덱스 탐색만으로 쿼리의 데이터들을 모두 완성 또는 얻을 수 있는 것을 의미한다.

​ [그림출처: https://jojoldu.tistory.com/476]

MYSQL에서는 Non Clustered Key에 Clustered Key가 항상 포함되어 있다고 한다.
이유는 Non Clustered Key에는 데이터 블록의 위치가 없기 때문에 실제 데이터를 접근하는 과정은 아래와 같다.
1. 인덱스 탐색
2. 클러스터드 키로 실제 데이터 접근

위 내용기반으로 PK 를 사용해서 데이터 조회시 1. 인덱스 탐색 과정을 생략하기 때문에 실제 성능향상의 효과가 있다.

위 내용을 이해하기 수월하도록 클러스터드/넌클러스터드 인덱스 개념을 먼저 이해하고 가자

클러스터드 인덱스 내부 노드 저장 구조

Root Node
|__ [id=1, Data Page]
|__ [id=2, Data Page]
|__ [id=3, Data Page]

Leaf Node = Data Pages
|__ Data for id=1
|__ Data for id=2
|__ Data for id=3

넌클러스터드 인덱스 내부 노드 저장 구조

Root Node
|__ [id=1]
|__ [id=2]
|__ [id=3]

Middle Node
|__ [id=1, Leaf Nodes pointer]
|__ [id=2, Leaf Nodes pointer]
|__ [id=3, Leaf Nodes pointer]

Leaf Nodes
|__[ Index Key(id=1) , Clustered Index Key (Primary Key) ]
|__ [ Index Key(id=2) , Clustered Index Key (Primary Key) ]
|__ [ Index Key(id=3) , Clustered Index Key (Primary Key) ]

클러스터드 인덱스

Root 노드에 인덱스 키와 Leaf 노드의 주소값을

Leaf 노드에는 키에 해당하는 raw 데이터가 같이 존재한다.

Root Node
|__ [id=1, Data Page]
|__ [id=2, Data Page]
|__ [id=3, Data Page]

Leaf Node = Data Pages
|__ Data for id=1
|__ Data for id=2
|__ Data for id=3

데이터 정렬:

• 클러스터드 인덱스는 데이터 행이 인덱스 키 순서에 따라 정렬되어 저장되므로, 범위 검색 및 정렬이 효율적입니다.

리프 노드가 데이터 행:

• 리프 노드가 실제 데이터 행을 포함하므로, 클러스터드 인덱스를 사용하면 별도의 데이터 행 접근 없이 인덱스에서 직접 데이터를 읽을 수 있습니다.

테이블 당 하나의 클러스터드 인덱스:

• 테이블 당 하나의 클러스터드 인덱스만 가질 수 있습니다. 이는 데이터 행이 물리적으로 정렬된 상태로 저장되어야 하기 때문입니다.(mysql 은 pk가 클러스터드 인덱스이다.)

빠른 데이터 접근:

• 클러스터드 인덱스를 사용하면 특정 키 값이나 키 값의 범위를 검색할 때 매우 빠르게 데이터를 접근할 수 있습니다.

넌클러스터드 인덱스

리프 노드는 인덱스의 가장 하단에 위치하며, 인덱스 키와 해당 키의 실제 데이터 위치(포인터)를 저장한다.

클러스터드 인덱스와는 달리, 리프 노드는 테이블의 실제 데이터가 아닌, 데이터가 저장된 위치를 가리킨다

때문에 실제 데이터를 찾기위해서는 포인터를 통해서 실제 데이터를 찾는 또는 접근하는 과정이 필요하다.

Root Node
|__ [id=1]
|__ [id=2]
|__ [id=3]

Middle Node
|__ [id=1, Leaf Nodes pointer]
|__ [id=2, Leaf Nodes pointer]
|__ [id=3, Leaf Nodes pointer]


Leaf Nodes
|__ [id=1, pointer to Data Page]
|__ [id=2, pointer to Data Page]
|__ [id=3, pointer to Data Page]

데이터와 분리된 저장:

• 넌클러스터드 인덱스는 테이블 데이터와 별도로 저장된다.
• 리프 노드는 인덱스 키와 해당 키의 데이터 위치를 가리키는 포인터를 포함한다.

다중 인덱스 생성 가능:

• 하나의 테이블에 여러 개의 넌클러스터드 인덱스를 생성할 수 있다.
• 다양한 열에 대해 인덱스를 만들어 여러 검색 조건에 최적화할 수 있다.

간접적인 데이터 접근:

• 인덱스를 통해 검색한 후, 포인터를 사용해 실제 데이터를 접근해야 한다.
• 검색 과정에서 두 번의 접근이 필요합니다: 인덱스 접근 후 데이터 접근.

업데이트 비용:

• 데이터 변경 시 인덱스도 함께 갱신되어야 하므로 삽입, 삭제, 업데이트 작업 시 추가 비용이 발생한다.
• 인덱스를 유지하는 데 추가 저장 공간이 필요한다.

그런데 여기서 생각해야 될 부분은 mysql의 넌클러스터드 탐색 원리를 이해해야 된다.

MYSQL 같은 경우는 Leaf 노드에 인덱스키와 클러스터드 키를 포함하고 있다.

때문에 실제 데이터를 찾으려면 결국 클러스터드 인덱스로 찾는 과정을 진행하게 된다.

Root Node
|__ [id=1]
|__ [id=2]
|__ [id=3]

Middle Node
|__ [id=1, Leaf Nodes pointer]
|__ [id=2, Leaf Nodes pointer]
|__ [id=3, Leaf Nodes pointer]

Leaf Nodes
|__[ Index Key(id=1) , Clustered Index Key (Primary Key) ]
|__ [ Index Key(id=2) , Clustered Index Key (Primary Key) ]
|__ [ Index Key(id=3) , Clustered Index Key (Primary Key) ]

그러면 우리는 다시 커버링 인덱스 로 돌아가서

쿼리를 충족시키는 인덱스가 커버링 인덱스이다.

실제 데이터의 접근 행위 없이 인덱스 탐색만으로 쿼리의 데이터들을 모두 완성 또는 얻을 수 있는 것 이라고 명시해뒀는데

클러스터드 인덱스의 경우 그 구조 내부에 이미 데이터가 존재하기 때문에 커버링 인덱스가 될 수 있다고 말할 수 있으며

넌클러스터드의 경우도 마찬가지이다.

그러면 여기서 의문점이 생긴다.

아래는 클러스터드 인덱스인 pk 를 사용한 쿼리인데 위는 커버링 인덱스가 아니며

아래는 커버링 인덱스이다. 왜 이런차이가 발생할까?

=========== 커버링 인덱스 x ===========
SELECT *
FROM Test t 
WHERE t.id =1;
=========== 커버링 인덱스 x ===========
SELECT data
FROM Test t 
WHERE t.id =1;
=========== 커버링 인덱스 o ===========
SELECT id
FROM Test t 
WHERE t.id =1;

이유는

첫번째 쿼리는 클러스터드 인덱스가 실제 데이터 행을 포함하고 있음에도 불구하고, SQL 엔진은 SELECT * 쿼리를 처리할 때 인덱스를 통해 실제 데이터 행을 검색해야 하므로, 이를 커버링 인덱스로 간주하지 않는다고 한다.

두번째는 클러스터드 인덱스의 경우 특정 필드의 데이터를 찾을때는 한번 데이터를 탐색해야 한다. 그냥 인덱스 노드 안에 전체 행에 대한 데이터만 존재할뿐

키값인 id만 조회 했으면 커버링 인덱스가 됐을거다.

세번째는 id 인 pk조회기 때문에 인덱스 노드안에서 바로 찾았기 때문에 커버링 인덱스이다.