아래와 같은 테이블을 생성 후 테스트를 위해 600만개 정도의 데이터를 입력했다
- id: int, Not Null, Auto Increment, PK
- memberId: int, Not Null, MUL(Index, 인덱스)
- contents: varchar(100)
- createdDate: date
- createdAt: datetime
- likeCount: int
- version: int
커버링 인덱스 적용 전
- 아래 쿼리는 0.063s의 실행 시간을 가진다 (createdDate를 기준으로 memberId, contents, createdDate를 선택한다)
SELECT memberId, contents, createdDate FROM POST WHERE createdDate = '2000-12-30';
일반적인 쿼리 작동 순서
-
풀 테이블 스캔(Full Table Scan): createdDate에 대한 인덱스가 없다면, 데이터베이스 엔진은 POST 테이블의 모든 레코드를 처음부터 끝까지 스캔하여, createdDate가 '2000-12-30'인 레코드를 찾는다. 이는 매우 비효율적일 수 있으며, 테이블의 크기가 큰 경우 많은 시간이 소요될 수 있다.
-
필터링(Filtering): 테이블의 각 레코드에 대해 createdDate가 '2000-12-30'과 일치하는지 확인한다. 일치하는 레코드만 추출하여 다음 단계로 이동.
-
데이터 검색(Data Retrieval): 필터링된 레코드에서 memberId, contents, createdDate 컬럼 값을 가져온다. 이 정보는 실제 테이블에서 읽어야 하므로, 해당 레코드의 저장된 위치로 직접 접근해야 한다.
-
결과 반환(Returning Results): 위의 컬럼들을 포함하는 결과를 사용자에게 반환한다.
커버링 인덱스 적용 후
-
쿼리에 의해 요청된 모든 컬럼을 포함하는 인덱스를 생성한다.
CREATE INDEX idx_post_covering ON POST(createdDate, memberId, contents); -
위의 쿼리는 실제 테이블에 접근할 필요 없이 idx_post_covering 인덱스를 사용하여 모든 필요한 데이터를 가져올 수 있다. createdDate 컬럼으로 인덱싱하고, memberId와 contents를 포함하므로, 이 인덱스는 커버링 인덱스가 된다. 인덱스 적용 후 실행 시간은 0.020s 이다.
- 실제 쿼리에 explain을 붙여 실행하면 커버링 인덱스가 적용 된 것을 확인할 수 있다.
장점
-
성능 향상: 인덱스에서 직접 데이터를 가져올 수 있기 때문에, 쿼리 실행 시간이 단축된다.
-
리소스 절약: 인덱스는 일반적으로 데이터의 요약 버전이므로, 전체 테이블 데이터에 접근하는 것보다 메모리 사용량이 적다.
-
락 경합 감소: 테이블 대신 인덱스만 접근하므로, 데이터베이스의 락 경합이 줄어들어 동시성이 향상될 수 있다.
결론
커버링 인덱스는 특정 쿼리 패턴에 맞춰 적절히 설계되어야 하며, 모든 상황에 적합한 것은 아니다. 인덱스는 데이터의 삽입, 수정, 삭제 작업에 추가적인 비용을 발생시키므로, 인덱스를 추가할 때는 해당 쿼리의 빈도와 성능 개선 효과를 충분히 고려해야 한다.
