당신의 인덱스는 안녕하신가요?(커버링 인덱스)

인덱스를 적용하는 이유

인덱스는 데이터베이스 내의 데이터를 빠르게 찾을 수 있도록 도와주는 데이터 구조이다. 인덱스를 사용하지 않은 상태에서 데이터를 검색하면, DBMS는 요청된 데이터를 찾기 위해 테이블의 모든 행을 순차적으로 검색해야 한다. 이러한 과정을 풀 테이블 스캔(Full Table Scan)이라고 하며, 대량의 데이터가 저장된 테이블에 풀 스캔을 할 경우에 많은 시간이 소요될 수 있다.
인덱스를 적용하면, DBMS는 인덱스를 통해 데이터의 위치를 빠르게 찾아 접근할 수 있게 되어 검색 속도가 향상된다. 대부분 인덱스를 설명할 때 책의 목차에 비유하는 이유도 이 때문이다.
인덱스는 검색 시간과 쿼리 실행 시간을 단축시킴과 동시에 테이블 전체가 아닌 인덱스 내에서 스캔하기 때문에 DB에 부하도 최소화 할 수 있는 장점이 있다.
하지만 인덱스는 데이터베이스 내에서 인덱스를 저장할 공간을 필요로 하며, 데이터의 추가, 삭제, 수정 작업 시 인덱스도 같이 업데이트가 되어야 하기 때문에 DB의 공간적인 특성과 추가나 수정이 빈번한 테이블인지 테이블의 특성에 따라서 알맞게 설계되어야 한다.

커버링 인덱스란?

일반적으로 인덱스를 설계한다고 하면 여러가지가 복합적으로 검토되어야 한다. 예를 들어 어떤 컬럼이 SELECT, WHERE절, JOIN절, ORDER BY 절 등에서 자주 사용하는 지, 어떤 컬럼이 선택도가 높고 중복된 값이 적어 검색 결과가 전체 데이터의 작은 부분만을 차지하는 지 등
여기서 커버링 인덱스는 쿼리를 충족시키는 데 필요한 모든 데이터를 갖고 있는 인덱스를 커버링 인덱스(Covering Index)라고 부른다.
위에 인덱스 조건을 만족하는 테이블을 찾아 인덱스를 걸었다고 할 때, 이 커버링 인덱스 조건을 만족하는지 확인하려면 EXPLAIN 쿼리를 사용하면 된다.
EXPLAIN 쿼리의 결과 값 중에서 Extra 컬럼을 확인해보면 다음과 같은 표기로 나뉜다.

• Using Where: DB의 엔진으로 필터링 될 때 Using where로 풀림 즉, 스토리지 엔진에서 불필요하게 많은 데이터로 엑세스 한다는 의미이므로 인덱스 설계가 제대로 되지 않았을 가능성이 높음
• Using Index: 커버링 인덱스 상태
• Using index condition: 인덱스 컨디션 푸시다운으로 WHERE 조건을 스토리지 엔진에 전달하여 스토리지 엔진에서 필터링된 데이터를 전달함. 역시나 스토리지 엔진에서 데이터를 엑세스하므로 인덱스 설계가 제대로 되지 않았을 가능성이 높음
• 빈값 - 일반적인 쿼리

위와 같이 Using Index로 나온다면 커버링 인덱스가 잘 적용되었다는 뜻이다. 그렇다면 커버링 인덱스를 만족하기 위해서(Using Index)어떻게 해야할까?

커버링 인덱스를 만족하는 인덱스 만들기

• 카디널리티가 높은 순에서 낮은 순으로 구성
  • 카디널리티는 해당 컬럼의 중복된 수치를 나타낸다.
  • 예를 들어 성별은 남과 여밖에 없으므로 카디널리티가 낮고 주민등록번호는 고유하므로 카디널리티가 높다
  • 인덱스의 최대의 효율을 뽑아내기 위해서는 해당 인덱스로 많은 부분을 걸러내야하기 때문에 카디널리티가 높은 것을 잡아야 한다.
  • 만약 복합 인덱스를 건다면 카디널리티가 높은 순에서 낮은 순으로 구성하자
  • SELECT count(distinct col1) FROM table 쿼리를 통해서 카디널리티를 구해서 비교해보기
• SELECT와 WHERE 조건에 걸리는 것으로 INDEX를 구성
  • SELECT절에 무심코 *를 썼다면 이제부터는 index를 건 컬럼을 명시해주자
  • WHERE절도 마찬가지이다
• 비교 연산이나 서브쿼리는 지양
  • like절 특히 문자열 시작부분이 아닌 중간이나 끝 부분을 검색하는 패턴 ex)%검색어
  •  부정 연산자: NOT EQUAL(<>), NOT IN, NOT LIKE 등의 부정 연산자
  • 계산이 포함된 조건 ex)column + 1 = 10

무작정 인덱스는 검색성능 향상에 좋을 것이라는 착각을 가지고 인덱스를 생성했다면 다시한번 위와 같은 사항을 점검해서 자신이 올바르게 인덱스를 사용해서 쿼리하고 있는지 확인해보고 인덱스를 활용해야겠다.