99클럽 TIL

99클럽 코테 스터디 12일차 TIL DB Covering Index

차가리 2024. 5. 31. 20:19
728x90
  • DB
    • Index
      • Index 작동 원리
      • Index 종류
        • B-Tree 인덱스
        • Hash Indexes
        • Bitmap Index
        • GIST(Generalized Search Tree)
        • R-Tree
        • Full Text Index
        • Spatial Index
        • Trie(Prefix Tree) Index
        • Covering Index
    • 영속성
    • 트랜잭션
    • ORM
    • ACID
    • N+1 문제
    • DB 정규화
    • Data Replication
    • sharding 전략
    • CAP 이론

란?

  • 특정 쿼리의 모든 필요한 데이터를 인덱스에서 직접 얻을 수 있게 하는 인덱스
  • 인덱스를 사용하면 디스크 I/O 를 줄이고 쿼리 성능을 크게 향상 시킬 수 있음.
  • 쿼리가 테이블의 데이터 페이지를 직접 접근하는 것을 방지하여 성능을 높여줌.

장단점

장점

  1. 성능 향상 : 디스크 I/O 를 최소화 하여 쿼리 성능을 향상 시킴.
  2. 데이터 페이지 접근 최소화 : 데이터 페이지에 대한 접근을 줄여 데이터베이스의 전반적인 부하를 줄임.
  3. 효율적인 캐시 사용 : 인덱스는 일반적으로 메모리에 캐시 되므로 메모리 내에서 빠르게 조회할 수 있음.

단점

  1. 인덱스 크기 증가
    • 저장 공간
      • Covering Index 는 많은 컬럼을 포함하므로 인덱스의 크기가 커짐. 이는 더 많은 저장 공간을 필요로 하며, 특히 대용량 테이블에서 문제를 일으킬 수 있음.
    • 메모리 사용
      • 큰 인덱스는 메모리에 더 많이 적재되므로 메모리 사용량이 증가함.
  2. 데이터 수정 시 성능 저하
    • INSERT, UPDATE, DELETE
      • 테이블에서 데이터를 삽입, 수정, 삭제할 때마다 인덱스도 갱신되어야 함. Covering Index 는 포함된 컬럼이 많기 때문에 이 작업이 더 복잡하고 시간이 많이 걸릴 수 있음
    • 잠금(Lock) 및 병합(Merge)
      • 인덱스 갱신 시 더 많은 잠금이 필요하며, 이로 인해 동시성 문제가 발생할 수 있음.
  3. 관리 복잡성 증가
    • 인덱스 설계
      • Covering Index 는 설계 시 어떤 컬럼을 포함해야 할지 신중하게 결정해야함. 잘못된 인덱스 설계는 성능 개선보다 더 많은 문제를 일으킬 수 있음.
    • 유지보수
      • 데이터베이스 스키마나 쿼리가 변경될 때 인덱스를 재설계하고 관리해야함. -> 추가적인 유지보수 작업을 필요로함.
  4. 쿼리 패턴의 변화
    • 유연성 부족
      • 특정 쿼리 패턴에 맞추어 설계된 Covering Index 는 쿼리 패턴이 변하면 더 이상 효율적이지 않을 수 있음. 새로운 쿼리에 맞추기 위해 인덱스를 다시 만들어야 할 수 있음.
    • 다양한 쿼리 지원 한계
      • 모든 쿼리에 대해 효율적인 Covering Index 를 설계하는 것은 불가능할 수 있으며, 일부 쿼리는 여전히 성능상의 문제를 겪을 수 있음.
  5. 복잡한 인덱스 구조
    • 높은 인덱스 깊이
      • 인덱스의 키가 복잡해질수록 인덱스의 깊이가 증가할 수 있으며, 이는 인덱스 탐색 성능을 저하시킬 수 있음.
    • 균형 유지
      • 인덱스 트리의 균형을 유지하는 것이 더 어려워질 수 있으며, 이는 전체적인 인덱스 성능에 영향을 미칠 수 있음.

예제

CREATE TABLE orders(
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
)

위와 같은 쿼리가 있다고 가정할 때

SELECT order_id, total_amount FROM orders WHERE customer_id = 123;

위 쿼리를 최적화 하기 위해 covering index 를 생성할 수 있음.

CREATE INDEX ind_customer_order ON orders (customer_id, order_id, total_amount);

이 인덱스는 customer_id 로 먼저 정렬하고, 그 후에 order_id 와 total_amount 를 포함함.
이 인덱스는 customer_id 를 조건으로 하고, order_id 와 total_amount를 반환하는 쿼리를 완전히 커버함.

쿼리 실행 시 테이블의 실제 데이터 페이지에 접근 할 필요 없이 인덱스만으로 결과를 반환할 수 있음.

Index Included Columns(인덱스 포함 컬럼)

일부 DB 는 인덱스의 INCLUDE 기능을 제공함.

이러한 Include 는 인덱스 트리의 리프 페이지에만 저장되며, 인덱스의 키의 일부로 간주되지 않음.

예를 들어서 위의 예제에서 order_date 는 쿼리 조건에는 사용되지 않지만 반환될 수 있는 컬럼임.

CREATE INDEX idx_customer_order_include ON orders (customer_id) INCLUDE (order_id, total_amount, order_date);

이러한 인덱스를 사용하면 쿼리는 covering index 효과를 보면서 인덱스 키의 크기를 줄일 수 있음.

728x90