[오늘의 문제] Secondary Index (보조 인덱스)

오늘의 문제 - DB

Table - exam_result

column: id, user_id, school_id, score

index: id(PK), user_id(Secondary Index), school_id(Secondary Index)

---------------------------------------------------------------------

SELECT sum(score) from exam_result where school_id = 10;

위 SQL을 개선하고자 할 때 어떤 방법이 있을까요? 그리고 그렇게 생각한 이유는 무엇인가요?

InnoDB 기준

1. Secondary Index는 어떤 데이터 구조로 이루어져 있나요?
2. Secondary Index를 통해 데이터를 조회하는 것은 내부적으로 어떻게 처리되는건가요?
3. 커버링 인덱스는 무엇인가요?

개념

PK가 아닌 다른 컬럼을 기준으로 생성하는 인덱스, PK와 달리 물리적 정렬 되지 않음

구조

데이터 테이블과 별개로 정렬된 인덱스 페이지를 생성하고 관리
• 보조 인덱스는 순서를 가지지 않는다 (정렬되지 않아도 상관없다). 인덱스 블록의 인덱스 키만 정렬되어야 한다.

보조 인덱스 조회 과정

1. 보조 인덱스(B+Tree 등)에서 조건 컬럼으로 검색
   • 예: SELECT * FROM users WHERE email = 'abc@example.com'
   • email 컬럼에 보조 인덱스가 있다면,
   • DB는 먼저 보조 인덱스를 검색하여 해당 email에 해당하는 primary key(예: user_id)를 찾습니다.
2. 인덱스 리프 노드에서 primary key(PK) 값을 획득
   • 보조 인덱스는 일반적으로 (email, user_id) 형태로 구성됩니다.
   • 이때 email에 해당하는 user_id를 하나 또는 여러 개 얻습니다.
3. 클러스터 인덱스(또는 테이블 본문)로 이동하여 실제 데이터 조회
   • 앞에서 얻은 user_id를 기반으로, 클러스터 인덱스(또는 Heap Table의 경우 테이블 자체)에서 실제 레코드를 찾습니다.
   • 이 과정을 인덱스 루킹(Index Lookup) 혹은 인덱스 스캔 + 테이블 접근이라고도 합니다.

커버링 인덱스 - 개념

https://jojoldu.tistory.com/476

인덱스를 설계한다고하면 WHERE절에 대한 인덱스 설계를 이야기하지만 사실 WHERE뿐만 아니라 쿼리 전체에 대해 인덱스 설계가 필요합니다.

인덱스는 데이터를 효율적으로 찾는 방법이지만, MySQL의 경우 인덱스안에 포함된 데이터를 사용할 수 있으므로 이를 잘 활용한다면 실제 데이터까지 접근할 필요가 전혀 없습니다.

이처럼 쿼리를 충족시키는 데 필요한 모든 데이터를 갖고 있는 인덱스를 커버링 인덱스 (Covering Index 혹은 Covered Index) 라고합니다.

쿼리를 만드는 SELECT / WHERE / GROUP BY / ORDER BY 등에 활용되는 모든 컬럼이 인덱스여야 한다는 것이다. 즉, 데이터 블록을 보지않고도 필요 컬럼을 인덱스 레벨에서 검색할 수 있기 때문에 쿼리 성능 개선에 도움이 된다.

커버링 인덱스 - 사전 지식

InnoDB 기준, 보조 인덱스는 해당 인덱스+pk값을 저장한다.

보조 인덱스를 통해 데이터를 조회하는건 먼저 해당 인덱스 트리에서 조건에 맞는 인덱스 키로 탐색 → 해당키에 연결된 pk로 실제 테이블에 접근해서 가져오는 구조 (인덱스 루킹 후 테이블 루킹 과정 or 인덱스 루킹(Index Lookup) 혹은 인덱스 스캔 + 테이블 접근)

💡
정리

보조 인덱스로 조회를 하면  
보조 인덱스 구조상 "인덱스 키 컬럼의 실제 값"과 "해당 행의 PK"가 저장로 이루어져있고  
여기 보조 인덱스에 없는 데이터는 PK를 통해 테이블에 접근하는 과정이 존재한다.

다시말하면, 쿼리에 필요한 칼럼이 모두 인덱스에 포함되어 있으면(커버링 인덱스) pk를 통해 테이블에 접근하는 과정이 일어나지 않아 성능이 향상된다.

따라서 보조인덱스인 school\_id와 score를 복합인덱스를 걸어서 해당 school\_id에 해당하는 score를 바로 가져올 수 있게 끔 한다.