카디널리티 VS 선택도 (feat. NDV, Density)

NDV, 선택도, 카디널리티가 헷갈려 글을 정리하게 되었다. 본인이 이해하기 쉽게!

<회원>

회원번호	거주지역	...
1	서울	...
2	서울	...
3	구미	...
4	구미	...
5	구미	...
6	익산	...

이 회원 테이블을 기준으로 정리할 것이다. 참고로 이 테이블의 PK는 회원번호다.

1. NDV(Number of Distinct Value)

특정 컬럼에 Unique한 값이 얼마나 있는지를 얘기하는 것이다.

1) Primary Key이거나 Unique 속성을 지닌 컬럼인 경우

위의 테이블에서 회원번호와 같은 경우이다. 컬럼의 값으로 {1, 2, 3, 4, 5, 6}이 존재하므로 일단 NDV는 6이 된다.
또한, 이러한 컬럼은 중복되는 값이 존재하지 않기 때문에 테이블의 행 수와 개수가 일치하다.

2) 그렇지 않은 경우

위의 테이블에서 거주지역과 같은 경우이다. 컬럼의 값으로 {서울, 구미, 익산} 이렇게 세 종류가 있기 때문에 NDV는 3이 된다.

2. 밀도(Density)

선택도 = 1 / NDV

회원번호의 NDV는 6이고 거주지역의 NDV는 3이기 때문에 Density는 각각 1/6, 1/3이 된다.

3. 선택도(Selectivity)

선택도란, 전체 레코드 중에서 조건절에 의해 선택될 것으로 예상되는 레코드의 비율(%)이다.
선택도 = 카디널리티 / 총 레코드 수
선택도가 낮을수록 인덱스의 후보가 되기 좋다.

+ 히스토그램 없이 등치(=) 조건에 대한 선택도를 구하는 공식 선택도 = 1 / Distinct Value 개수 = 1 / num_distinct

4. 카디널리티

특정 데이터 집합의 Unique한 값의 개수이다.
ex) 성별은 {'남', '여'}로 카디널리티가 2가 된다.
선택도에 총 레코드 수를 곱해서 구한다.
카디널리티 = 선택도 * 총 레코드 수

+ 히스토그램 없이 등치(=) 조건에 대한 카디널리티를 구하는 공식 카디널리티 = 총 레코드 수 * 선택도 = num_rows / num_distinct

공식만 보고 단순히 카디널리티와 선택도는 비례한다고 생각했는데 둘의 관계는 상대적이라는 것을 알게 되었다.

회원번호의 경우 카디널리티가 6이며 총 레코드의 수도 6이기 때문에 선택도는 1이 된다.
반면, 거주지역은 카디널리티가 3이며 총 레코드의 수는 6이기 때문에 선택도는 1/2가 된다.
-> 카디널리티가 높을수록, 선택도가 낮을수록 중복도가 낮은 컬럼이다.

참고로, 중복도가 낮은 컬럼이 인덱스로 설정하기 좋은 컬럼이다. 중복도가 낮을수록 조건절에서(특히 =연산자 사용 시) 걸리는 행이 적기 때문에, 탐색할 범위가 줄어들기 때문이다.

책을 공부하다 보니 이런 부분이 있었다.

컬럼명	NDV
주소ID	736,000
건물동번호	175
건물호번호	3,052
관리번호	250,782
상태구분코드	3

상태구분코드는 NDV가 3이므로 선택도가 매우 높다. 상태구분코드로만 조회할 때는 N1(상태구분코드 + 관리번호) 인덱스가 사용되지 않는다는 뜻이다.

여기서 주소ID가 Surrogate Key정도로 추측되고 행의 개수라고 가정해보자. 736,000개의 주소 중 상태구분코드가 3이라면 중복도가 매우 높을 것이다. 따라서 선택도가 매우 높다고 표현한 것이다.
(아무리 고르게 분포되었다고 해도 where절에서 =연산자로 특정 상태구분코드에 해당하는 행을 찾는다고 해도 약 24만~25만개의 행을 탐색해야 한다는 뜻이다.)

이정도 양이면 인덱스 타고 탐색하느니 차라리 Table Full Scan 하는 것이 낫지 않을까, 그래서 N1 인덱스가 사용되지 않는다고 적어놓은게 아닐까 추측한다.

+ 파란색 글씨로 쓴 부분이 책에서 나온 정의인데 기존 글과 뭔가 다른 것 같아서 혼란스러우니 더 알아낸게 있으면 바로 수정할 예정이다...