다중 컬럼 인덱스

다중 컬럼 인덱스에서 선정 기준

B-Tree 인덱스의 다중 컬럼 인덱스는 컬럼 순서가 매우 중요하다. 순서에 따라 해당 인덱스를 활용하지 못 할 수도 있기 때문이다.

반드시 왼쪽에 있는 컬럼이 사용되어야 그 오른쪽의 컬럼도 사용될 수 있다.

그럼 어떤 기준으로 순서를 정하는게 좋을까? (단일 컬럼 인덱스에도 적용되는 해당되는 이야기)

Cardinality(기수성)

카디널리티는 유니크한 값의 개수를 말한다.

• cardinality(기수성)가 높은 컬럼이 앞에와야 검색 효율이 좋다.
• 기수성이 높은 컬럼이 앞에오고 컬럼이 범위를 크게 줄여주고, 낮은 컬럼이 뒤에오면 필터링 조건으로 사용된다.

남자이고 이름이 A로 시작하는 사람을 가져와보자.
예를 들어 100명 중 A로 시작하는 사람은 10명, 전체 남자는 70명이다.
그리고 A로 시작하는 남자는 5명이다.

select NAME, GENDER from EMPLOYEES where NAME like 'A%' and GENDER='male'

(NAME, GENDER) 인덱스
A로 시작하는 사람 10명 목록을 가져온다. 그리고 그 중 남자만 필터링 하면 된다.
(GENDER, NAME) 인덱스
성별이 남자인 사람을 70명 가져온다. 그 중 A로 시작하는 사람을 찾는다.
성별로 가져온 70명 대부분이 버려진다.
=> 이름이 더 유니크한 값으로 이름이 기수성이 높은 칼럼이다.

Selectivity (선택도)

선택도란 얼마나 값을 잘 가져오냐를 말하는데, 1이면 유니크하다는 의미다.
20~25% 를 넘어서면 그 인덱스를 사용하지 않는게 좋다고 한다.

• Cardinality의 (NAME, GENDER) 인덱스는 100명중 10명만 읽고 5명을 찾아온다. 즉 전체의 10%만 읽었다. 그 중 50% 만 필터링되었다.
• (GENDER, NAME) 인덱스 예제를 보면 전체 100명중 무려 70%를 읽었다. 그 중 5명만 찾은거니 70명중에서도 35% 만 필터링에서 살아남았다. 비효율적이다.

활용도

• 활용도가 높을 수록 인덱스 설정에 좋은 컬럼이다.
  해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값이다. 수동 쿼리 조회, 로직과 서비스에서 쿼리를 날릴 때 WHERE 절에 자주 활용되는지를 판단하면 된다.

중복도

• 중복도가 없을수록 인덱스 설정에 좋은 컬럼이다.
  중복 인덱스 여부에 대한 값이다. 인덱스 성능에 대한 고려 없이 마구잡이로 설정하거나,
  다른 부서 다른 작업자의 분리된 요청으로 같은 컬럼에 대해 인덱스가 중복으로 생성된 경우를 볼 수 있다. 인덱스도 속성을 가진다. 인덱스는 테이블 형태로 생성되므로 속성을 컬럼으로 관리한다.