카디널리티와 선택도의 관계와 차이.. 대체 뭐가 다른 걸까요

이전에 리뷰했던 "업무에 바로 쓰는 SQL 튜닝"이라는 책을 읽으며 카디널리티와 선택도 개념을 처음 접했었다.

 

 

개발 도서 리뷰 (10) 업무에 바로 쓰는 SQL 튜닝 4.0/5.0

 

원래도 기억이라는 건 시간이 지나면 흐려지는 것이기도 하고, 해당 책이 이론에 대해 깊게 설명하는 책은 아니였기 때문에 슬슬 헷갈리기 시작해서 다시 한 번 정리해보기로 했다.

 

 

 

1. 카디널리티

 

카디널리티(cardinality)는 집합의 크기라는 뜻으로 해당 column에 유일값이 얼마나 많은지를 나타내는 척도다.

우선 바로 뇌에 꽂히는 설명부터 보고 가자.

 

https://stackoverflow.com/questions/2566211/what-is-cardinality-in-mysql

 

카디널리티 최대치는 "column의 모든 row가 유니크하다."

카디널리티 최소치는 "column의 모든 row가 같은 값을 지닌다."

 

즉, Unique Index 혹은 Primary Key로 지정된 Column은 Max-Cardinality를 가지게 된다.

 

대표적인 카디널리티 설명의 예시는 성별과 주민등록번호이다.

주민등록번호는 그 어떤 사람도 같은 값을 가질 수 없으므로 해당 column의 모든 row가 유일값을 가질 것임을 확신할 수 있다.

반면, 성별의 경우 오직 "남성"과 "여성" 둘로만 분류되기 때문에 값이 중복될 확률이 굉장히 높아진다.

이런 경우 카디널리티가 낮다고 할 수 있다.

 

그래서 왜 카디널리티를 알아야 하는데?

카디널리티는 데이터를 정렬하고 검색하는 성능에 아주 큰 영향을 미친다.

데이터베이스의 옵티마이저가 인덱스의 카디널리티를 기준으로 데이터 조회를 최적화하기 때문이다.

가령, 주민등록번호 기준으로 특정 데이터를 조회한다고 가정해보자.

 

우리는 1999년에서 2001년 사이에 태어난 모든 회원을 조회하기 위해서 이런 쿼리문을 작성할 것이다. (물론 해당 column은 인덱스로 지정되어 있어야 한다.)

 

SELECT m.member_id, m.member_name, m.member_email

FROM Member AS m

WHERE year(m.birth_date) BETWEEN 1999 AND 2001

 

우리의 데이터베이스가 B+Tree 구조로 인덱스를 저장했다고 가정했을 때, 위와 같이 Leaf Node에서 해당하는 자료를 찾고 만약 "GGG"가 1999년이 위치한 인덱스라면 이후 "HHH" -> "III" -> "JJJ"의 값들을 Linked List를 순회하며 읽어들이며 빠르게 필요한 범위의 값들을 불러올 수 있다.

해당 column은 인덱스로 지정했을 때 성능 향상이라는 유의미한 이점을 가질 수 있다는 것이다.

(단, 중요한 것은 카디널리티가 높다는 가정하에서다. 당연하게도 해당 멤버 테이블의 모든 회원이 1999년부터 2001년생이라면 아무 의미가 없다.)

 

그러나 성별을 인덱스로 지정한 상황이라면 어떨까?

10000명의 검색 인원을 5000명으로 줄이는 것은 풀스캔을 하는 것에 비해 큰 이점을 가지지 못한다고 볼 수 있다.

게다가 상황에 따라서 테이블 풀스캔보다 오히려 더 느릴수도 있다고 한다. (검증 필요)

글을 읽어보았으나 타인에게 설명할 정도로 이해하지 못했기 때문에 링크만 걸도록 하겠다.

 

Can an index be slower than a full table scan?

 

참고로 위에서 언급한 "업무에 바로 쓰는 SQL 튜닝"책 273p에서 직원의 성(Last name)과 성별(Sex) 인덱스 순서를 변경한 것만으로 쿼리 성능이 200배 향상되는 예시를 확인할 수 있다.

 

 

 

 

 

2. 선택도

 

사실 내가 이 글을 작성하기로 마음 먹은 계기는 선택도 때문이다.

선택도는 그냥 겉으로 봤을 때는 카디널리티와 너무나도 유사해보이며, 실제로 선택도를 설명하는 글과 카디널리티를 설명하는 글을 동시에 켜놓고 비교해봐도 차이를 파악하기 힘들다.

 

심지어 선택도를 구하는 공식부터 여기서 다르고 저기서 다르다.

 

책에서 제시하는 인덱스의 선택도(Selectivity)를 구하는 공식은 아래와 같다.

선택도 = 선택한 데이터 건수 / 전체 데이터 건수

위 공식에 따르면 선택도가 낮은 것이 좋은 것이고, 높은 것이 좋지 않은 것이 된다. 즉, 반비례 관계이다.

 

아래는 구글에 검색하면 주로 나오는 선택도 계산법이다.

Selectivity = Cardinality / Record (Number of records)

참고 : (https://www.programmerinterview.com/database-sql/selectivity-in-sql-databases/)

 

위 글에 따르면 카디널리티와 선택도는 비례 관계에 속한다.

 

대체 어쩌라고.. ^^~~

Oracle이 DBA가 아래의 공식대로 선택도를 계산하는 글을 발견했기 때문에, 난 일단 아래 공식으로 밀고 가겠다.

참고 : (https://expertoracle.com/2017/11/15/db-tuning-basics-1-selectivity-and-cardinality/)

 

선택도랑 카디널리티의 차이는 그러면 대체 뭘까요

 

 

Selectivity Vs Cardinality

 

 

위 테이블에서 하나의 column을 인덱스로 지정했을 때는 중복되는 데이터가 많아서 (즉, 카디널리티가 낮아서) 선택도 또한 0.22였다.

그러나 둘의 인덱스를 묶어서 카디널리티를 높이자 그에 비례해서 선택도 또한 0.92로 거의 1에 가깝게 상승한 것을 확인할 수 있다.

 

카디널리티는 단순히 데이터의 중복도를 나타내는 지표이며, 선택도는 해당 레코드의 모든 값들 중에 하나의 값을 빠르게 골라낼 수 있는 정도를 나타낸다.

비슷하면서도 미묘하게 다른 개념이다..

 

나는 처음에 카디널리티가 최대일 때 선택도가 최대인 경우만 생각해서 둘이 왜 따로 존재해야 하는지 난해하게 느껴졌는데, 전체 로우 수 > 카디널리티보다인 경우를 고려했을 때 선택도는 해당 컬럼의 인덱스 효율을 보여주는 지표 역할을 할 수 있는 것으로 이해했다.

 

아쉽게도 100% 이해하지는 못했지만 오늘은 여기까지.