InnoDB에서 Covering Index를 사용해도 테이블 접근이 발생하는 이유

1. 배경

 

Covering Index는 쿼리에서 필요로 하는 모든 컬럼을 Index가 포함하고 있는 경우를 의미한다

 

일반적으로 Covering Index를 사용하면 실제 테이블의 Row를 다시 읽지 않고,

Index만으로 조회를 완료할 수 있기 때문에 성능을 개선할 수 있다고 설명한다

 

하지만 InnoDB의 MVCC 동작을 살펴보던 중 필요한 컬럼이 모두 Secondary Index에 포함돼 있더라도

Clustered Index를 다시 조회할 수 있다는 내용을 확인했다

 

Covering Index는 실제 Row에 대한 접근을 생략하기 위한 방법인데,

왜 다시 Clustered Index를 읽어야 할까?

 

이를 이해하기 위해서 먼저 InnoDB가 Clustered Index와 Secondary Index를 어떻게 구성하는지 알아야 한다

 

2. Covering Index의 비용 절감

 

InnoDB의 모든 테이블에는 Row 데이터를 저장하는 Clustered Index가 존재한다

 

테이블에 Primary Key가 정의돼 있다면 해당 Primary Key가 Clustered Index로 사용된다

Clustered Index의 Leaf Node에는 실제 Row 데이터가 저장된다

 

반면 Secondary Index의 Leaf Node에는 Index로 지정한 컬럼과 해당 Row의 Primary Key가 저장된다

쿼리에 필요한 컬럼이 Secondary Index에 없다면, 조회된 Primary Key를 이용해 Clustered Index에서 Row를 다시 조회한다

 

위 그림은 설명을 위해 InnoDB의 Index Record와 Page 배치를 단순화해 표현한 것이다

 

Secondary Index에서 조건에 맞는 Record를 찾으면 해당 Record에 저장된 Primary Key를 얻을 수 있다

 

InnoDB는 이 Primary Key를 이용해 Clustered Index를 탐색하고, Leaf Node에 저장된 실제 Row에서 필요한 컬럼을 조회한다

 

Clustered Index는 Primary Key를 기준으로 논리적인 정렬 순서를 유지한다

하지만 논리적으로 인접한 Leaf Page가 Tablespace의 물리적인 공간에서도 반드시 연속적으로 배치되는 것은 아니다

 

따라서 여러 Row를 조회한다면 서로 다른 Clustered Index Page에 접근할 수 있다

 

그렇다면 쿼리에 필요한 모든 컬럼이 이미 Secondary Index에 포함돼 있다면 어떨까?

 

이 경우에는 Primary Key를 이용해 Clustered Index를 다시 탐색하지 않고,Secondary Index만으로 필요한 값을 반환할 수 있다

 

이러한 Index를 Covering Index라고 한다

 

그렇다면 쿼리에 필요한 모든 컬럼이 Index에 존재하기만 하면 언제나 Secondary Index에서 조회가 끝날까?

 

3. Secondary Index만으로는 판단할 수 없는 정보 ━ MVCC

 

InnoDB는 하나의 Row에 대해 과거 버전의 정보를 유지하는 Multi-Version Storage Engine이다

 

이를 위해 Clustered Index의 Row에는 내부적으로 다음과 같은 시스템 컬럼이 추가된다

•  DB_TRX_ID : 해당 Row를 마지막으로 삽입하거나 변경한 트랜잭션의 ID
•  DB_ROLL_PTR : Undo Log의 Record를 가리키는 포인터

InnoDB는 이 정보를 이용해 현재 트랜잭션에서 해당 Row를 볼 수 있는지 판단하고,

필요하다면 Undo Log를 따라가 과거 버전을 복원한다

 

하지만 Secondary Index에는 이러한 시스템 컬럼이 존재하지 않는다

---

InnoDB multiversion concurrency control (MVCC) treats secondary indexes differently than clustered indexes. Records in a clustered index are updated in-place, and their hidden system columns point undo log entries from which earlier versions of records can be reconstructed. Unlike clustered index records, secondary index records do not contain hidden system columns nor are they updated in-place. (출처)

---

 

공식 문서에 따르면 Clustered Index Record는 제자리에서 갱신되고,

숨겨진 시스템 컬럼을 통해 이전 버전의 Undo Log를 참조한다

 

반면 Secondary Index Record에는 숨겨진 시스템 컬럼이 없으며,

제자리에서 갱신되지도 않는다고 언급한다

 

이 차이가 Covering Index를 사용하고도 Clustered Index를 다시 읽도록 하는 이유가 된다

 

4. Secondary Index의 값이 변경되는 경우

 

Secondary Index가 users라는 테이블 안에 있고, email 주소에 따라 정렬된다고 가정해보자

이때 email을 변경한다면 어떤 일이 일어날까?

 

UPDATE users
SET email = 'new-user@example.com'
WHERE id = 1;

 

 

Clustered Index Record는 새로운 값으로 갱신되고, 시스템 컬럼이 Undo Log를 통해 이전 버전을 참조하게 된다

 

하지만 Secondary Index는 이와 다르게 처리된다

 

기존 Secondary Index Record는 삭제 대상으로 표시되고,

변경된 값을 가진 새로운 Record가 삽입된다

 

여기서 delete-mark는 해당 Record가 물리적으로 삭제되었다는 것이 아닌, 삭제 대상을 의미한다

삭제 대상으로 표시된 기존 Record는 Purge 과정에서 제거된다

 

5. Covering Index를 사용해도 Clustered Index를 조회하는 경우

 

InnoDB는 MVCC를 통해 하나의 Row에 대한 여러 버전을 관리한다

하지만 여러 버전이 존재한다고 해서 모든 트랜잭션이 같은 버전을 조회하는 것은 아니다

 

일반적인  SELECT 와 같은 Consistent Read에서는 현재 트랜잭션이 어떤 Row Version을 볼 수 있는지 판단해야 한다

이러한 가시성 판단을 위해 Snapshot을 사용하게 되고, 가시성 판단 기준을 Read View라고 한다

 

Read View는 특정 시점의 Row 데이터를 복사해 저장한 구조가 아니다

대신 현재 조회에서 어떤 트랜잭션의 변경 결과를 볼 수 있고, 어떤 변경 결과는 아직 볼 수 없는지를 판단하기 위한 정보를 가진다

 

InnoDB는 Clustered Index Record의  DB_TRX_ID 와 Read View를 비교해 현재 Row Version의 가시성을 판단한다

현재 버전을 볼 수 없다면  DB_ROLL_PTR 을 따라 Undo Log를 탐색하며,

해당 Read View에서 볼 수 있는 이전 버전을 복원한다

 

문제는 Secondary Index Record에는 이러한 가시성 판단에 필요한 시스템 컬럼이 존재하지 않는다는 점이다

 

---

When a secondary index record is delete-marked or the secondary index page is updated by a newer transaction, InnoDB looks up the database record in the clustered index. (출처)

---

 

MySQL 공식 문서에서는 이와 같이 설명한다

 

정리하면

• Secondary Index Record가 delete-marked 상태일 때
• Secondary Index Page가 현재 Read View보다 새로운 트랜잭션으로 인해 업데이트 됐을 때

이와 같은 경우에 Clustered Index Record를 조회한다

 

Secondary Index에는  DB_TRX_ID 가 존재하지 않기 때문에,

해당 Record가 현재 Read View에서 볼 수 있는 버전인지 Secondary Index만으론 확정할 수 없기 때문이다

 

이에 따라 InnoDB는 Secondary Index에 저장된 Primary Key를 이용해 Clustered Index를 다시 조회한다

 

Clustered Index Record의  DB_TRX_ID 를 확인하고,

현재 버전을 볼 수 없다면  DB_ROLL_PTR 이 가리키는 Undo Log를 따라가 적절한 이전 버전을 복원한다

 

즉 Covering Index는 필요한 컬럼을 얻기 위한 Clustered Index 접근을 생략할 수 있지만,

MVCC 가시성 확인을 위한 접근까지 항상 제거하는 것은 아니다

 

6. Delete-mark 된 Record는 언제 제거될까?

 

Secondary Index 컬럼이 변경되면 기존 Record는 즉시 물리적으로 제거되지 않고 delete-mark된다

이 Record는 과거의 Read View에서 여전히 필요할 수 있기 때문이다

 

InnoDB는 Purge 과정에서 Undo Record를 이용해 더이상 필요하지 않은 delete-mark된 Row와 Index Record를 제거한다

 

Commit된 트랜잭션의 Update Undo Log는 History List에 연결되고,

Purge는 오래된 Read View가 필요로 할 수 있는 범위를 고려해 오래된 이력부터 처리한다

 

History List에 대해 설명하기 전에 구조를 잠시 살펴보자

 

• Undo Tablespace
  Rollback Segment를 담고 있는 보관소
  
  
• Rollback Segment
  여러 트랜잭션의 Undo Log 할당과 History 할당을 분산하기 위해 여러 Rollback Segment로 분산 관리
  
  
• History List
  Commit되었지만 Purge되지 않은 Update Undo Log의 Header를 연결해 관리하는 Linked List
  
  
• Undo Log
  여러 Undo Record를 보관하는 로그
  
  
• Binary Min-Heap
  여러 Rollback Segment로 흩뿌려진 Undo Log 사이에서 빠른 속도로 Purge 대상을 찾아내기 위한 Heap

Purge System은 Binary Min-Heap을 이용해 여러 Rollback Segment의 History에서 다음으로 처리할 오래된 대상을 선택한다

 

Binary Min-Heap에는 모든 Undo Record가 들어가는 것이 아니라

각 Rollback Segment에서 비교할 오래된 History 후보가 등록된다

 

이후 Purge할 수 있는 Undo Log Page를 하나의 Batch에서 파싱하고 처리한다

하나의 Batch에서 처리하는 Undo Log Page 수는  innodb_purge_batch_size 로 설정할 수 있다

수집된 Page는 여러 Purge Task로 나뉘어  innodb_purge_threads 의 수에 따라 병렬 처리될 수 있다

 

다만 Purge Thread 수를 늘린다고 처리량이 항상 같은 비율로 증가하는 것은 아니다

 

하나의 테이블에는 하나의 Purge Thread만 접근할 수 있어 병렬 처리 효과가 제한될 수 있고,

긴 트랜잭션이 Read View를 유지하고 있다면 Thread 수와 관계없이 Purge 범위 자체가 전진하지 못한다

 

7. 긴 트랜잭션과 Purge Lag

 

Purge 대상이 되려면 Undo Log와 Row Version이 더 이상 어떤 활성 Read View에서도 필요하지 않아야 한다

 

하지만 오래 실행되는 트랜잭션이 과거 시점의 Read View를 유지하고 있다면

InnoDB는 해당 트랜잭션 이후의 Consistent Read에서도 동일 Snapshot으로 Row Version을 조회할 가능성을 고려해야 한다

 

TX 129가 Select를 통해 Read View가 생성되고,

긴 트랜잭션으로 살아 있는 상태이며TX 234와 TX 346이 Update를 통해 Undo Log가 생성된 상태라고 가정해보자

(트랜잭션 격리 수준은 모두 InnoDB 기본 격리 수준인  REPEATABLE_READ 로 가정한다)

 

Purge는 가장 오래 된 Read View가 필요로 할 수 있는 범위를 기준으로,

더 이상 어떤 활성 Read View도 필요하지 않음이 보장된 History까지만 처리할 수 있다

 

즉 TX 234와 TX 346은 모두 Commit됐지만,

TX129의 Read View에서 과거 Read Version을 복원하는 데 필요할 가능성이 있어 관련 History는 안전하게 Purge할 수 없다

 

이처럼 새로운 History가 생성되는 속도를 Purge가 따라가지 못해

처리되지 않은 History가 누적되는 상태를 Purge Lag이라고 한다

 

긴 트랜잭션이 유지하는 오래된 Read View는 Purge Lag를 유발하는 대표적인 원인 중 하나다

 

InnoDB에서 상태 로그를 조회했을 때 History List Length를 확인해 History 누적을 유추해볼 수 있다

 

8. Purge Lag이 Covering Index에 미치는 영향

 

Purge Lag은 Undo History가 누적되는 문제로 끝나지 않는다

 

Purge가 지연되는 동안 오래된 Read View로 인해 Purge 경계가 전진하지 못하므로,

delete-mark된 Record가 Secondary Index에 장시간 남아있을 수 있다

 

이러한 상태가 누적되면 유효한 Record 수에 비해 Secondary Index가 더 많은 Page와 공간을 차지할 수 있다

이처럼 Index의 물리적 밀도가 낮아지고, 실제 유효 데이터에 비해 불필요한 Index 공간이 증가한 상태를 Index Bloat라 한다

 

Index Bloat가 발생하면 Covering Index를 통해 Clustered Index 접근을 생략하더라도,

Secondary Index 자체를 탐색하는 비용이 증가할 수 있다

 

또한 앞서 살펴본 것처럼 Secondary Index Record가 delete-mark 되어 있거나,

Secondary Index Page가 더 새로운 트랜잭션에 의해 변경된 경우에는 Clustered Index를 다시 조회할 수 있다

 

Purge Lag로 delete-mark된 Record가 장시간 남아 있다면,

이러한 Record를 조회 과정에서 만날 가능성도 높아질 수 있다

 

따라서 Purge Lag은 Secondary Index 자체의 탐색 비용을 증가시킬 뿐 아니라,

일부 조회에서 MVCC 확인을 위한 Clustered Index 접근이 발생할 가능성에도 영향을 줄 수 있다