인덱스를 타고 있는데도 쿼리가 너무 느린 경우 (인덱스 조각화)

• 생성된지 60일이 지난 항목을 삭제하는 배치 작업이 있다. (일 1회 수행)
• 삭제하기 전 삭제 대상 count를 가져와야 하는데, 쿼리가 생각보다 너무 느렸다. (5분)
• 파티셔닝은 되어있지 않다.

문제의 쿼리

SELECT COUNT(*) 
FROM mm_notf_atcl_rslt 
WHERE reg_ymdt < TRUNC(SYSDATE - 60)

-- 결과 300개
-- 5분 걸림

인덱스를 안타고 있나?

• 쿼리 플랜도 보고, 인덱스 힌트를 명시적으로 넣어서 돌려보았는데 인덱스는 제대로 타고 있는게 맞았다.
• reg_ymdt 단일 컬럼으로 이루어진 인덱스가 있고 쿼리 플랜 상 INDEX RANGE SCAN 타고 있음.

테이블 row 수가 너무 많은게 원인인가?

• 테이블의 전체 row 수를 세어보니 80,000,000개. 파티셔닝이 안되어 있어 테이블 전체 사이즈가 크긴 하다.
• 비클러스터링 인덱스 일거라 index leaf node 수도 80,000,000개 일 듯 한데… leaf node를 너무 많이 봐야 해서 느린가?
  • 하지만 INDEX RANGE SCAN 하는거라 B+트리 level을 따라 쭉 내려와서 읽는건데, 그러면 이 300개가 모두 다른 leaf node에 저장되어 있다고 하더라도 결국 읽어야 하는 leaf node 수는 300개 일 것 같다. (인덱스가 효율적으로 구성되어 있는 경우를 가정)

B+트리 레벨이 너무 깊은가?

• 권한이 없어서 확인 할 수 없음… 하지만 트리 레벨이 10은 안넘을 것 같다. (보통 데이터가 웬만큼 많아도 4 level 정도면 커버가 된다.)
• 대상 row 수가 300개 밖에 안되므로, 트리 level이 10이라고 쳐도 최악의 경우 3,000번만 인덱스 블록을 가져오면 결과를 알 수 있지 않나?

인덱스 조각화 (index fragmentation) ← 원인

• 매일 생성된지 60일이 지난 항목들을 매일 삭제하고 있다. 지속적인 삭제로 인해 인덱스 B+트리에 Fragmentation이 발생/누적되고, 그로 인해 인덱스 구조가 비효율적이 되어 느린게 아닐지?
• B+트리는 기본적으로 Balanced Tree이기 때문에 root node에서 leaf node까지의 거리는 어떤 leaf node이든 모두 동일하다.
• 하지만 Fragmentation으로 인한 Index Skew 또는 Sparse 현상은 생길 수 있는데…
  • Index Skew는 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상을 말한다.
  • Index Sparse는 인덱스 블록 전반에 걸쳐 밀도(density)가 떨어지는 현상을 말한다.

[!info] Oracle의 경우, 텅 빈 인덱스 블록은 커밋하는 순간 freelist로 반환되지만 인덱스 구조 상에는 그대로 남는다. 상위 브랜치에서 해당 리프 블록을 가리키는 엔트리가 그대로 남아 있어 인덱스 정렬 순서상 그 곳에 입력될 새로운 값이 들어오면 언제든 재사용될 수 있다.
레코드가 모두 삭제된 블록은 이처럼 언제든 재사용 가능하지만, 문제는 다시 채워질 때까지 인덱스 스캔 효율이 낮다는 데에 있다.
SQL Server에선 Index Skew 현상이 발생하지 않는다. 주기적으로 B*Tree 인덱스를 체크함으로써 지워진 레코드와 페이지를 정리해 주는 메커니즘을 갖는다.

• 매일 reg_ymdt 인덱스의 왼쪽 끝을 삭제하고 있었으니, Index Skew가 발생한 것이 원인이었다.
• 실제로 문제 쿼리와 동일한 결과를 반환하는 아래 쿼리를 돌려보면, 소요 시간이 10% 로 감소한다.
• 즉, 65일 이전의 데이터(이미 삭제되어 없는)를 가리키는 인덱스 블록들이 아직 존재하고, 이를 탐색하는데 시간이 낭비되고 있었던 것.

SELECT COUNT(*) 
FROM mm_notf_atcl_rslt 
WHERE TRUNC(SYSDATE - 65) < reg_ymdt AND reg_ymdt < TRUNC(SYSDATE - 61)

-- 결과 300개
-- 20초 걸림

해결 방안

• 파티셔닝 하지 않는다면, 성능 개선을 위한 테이블 reorg 작업 및 인덱스 rebuild 작업을 주기적으로 진행.
  • 진행 시 순간적으로 lock을 잡기 때문에 배치 중단 및 해당 테이블 access가 적은 시간에 작업해야 함
  • 주기적으로 인덱스 rebuild 한다고 해도, 쿼리는 반구간이 아닌 구간으로 수정하는 것이 안전해보임.
• 파티셔닝 한다면, (일or월) 파티션 단위로 관리하고, 불필요한 파티션은 drop
  • 주기적으로 파티션을 drop 한다고 해도, 파티션키를 구간으로 넣어서 호출하는게 안전해보임.

즉, 어느 쪽이든 반구간(a < x)이 아니라 구간(a < x < b)으로 쿼리를 수정해야 하므로, 서비스에서는 사실 파티셔닝을 하든지 안하든지 개발 관점에서 큰 차이는 없다.
데이터 효율 관점에서는 파티셔닝 해서 관리하는 것이 더 나아보인다.