카디널리티(Cardinality)와 선택도(Selectivity)

카디널리티(Cardinality, 기수성)는 특정 데이터 집합의 유니크(Unique)한 값의 개수이다.

예를 들어 데이터베이스의 '성별' 컬럼의 경우 남자와 여자라는 값만 가지기 때문에 카디널리티는 2다. 반면에 주민등록번호는 모든 레코드가 고유한 값을 가지기 때문에 전체 레코드 개수와 카디널리티는 일치한다.

​

선택도는 카디널리티로부터 다음과 같이 계산할 수 있다.

선택도 = 카디널리티 / 전체 레코드 수

따라서 선택도는 0~1사이의 값이며, 1일 때 모든 값이 유니크하다는 뜻이다.

​

​

인덱스의 카디널리티(Cardinality)도 모든 인덱스 키값 가운데 유니크한 값의 수를 의미한다.

전체 인덱스 키값은 100개인데, 그중에서 유니크한 값의 수는 10개라면 카디널리티는 10이다. 인덱스 키값 가운데 중복된 값이 많아지면 많아질수록 카디널리티는 낮아지고 동시에 선택도 또한 떨어진다. 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.

​

※ 선택도가 좋지 않다고 하더라도 정렬이나 그룹핑과 같은 작업을 위해 인덱스를 만드는 것이 나은 경우도 많다. 인덱스가 항상 검색에만 사용되는 것은 아니므로 여러 가지 용도를 고려해 적절히 인덱스를 설계할 필요가 있다.

​

카디널리티의 중요성을 보여주는 예를 한번 살펴 보자.

1만건의 데이터를 가지는 tb_city라는 테이블이 있다. country, city 두 컬럼을 조합하면 유니크한 레코드를 식별할 수 있다고 가정해 보자.

CREATE TABLE tb_city ( country VARCHAR(10), city VARCHAR(10), INDEX ix_country (country) ); -- 1만건의 데이터 INSERT INSERT INTO tb_city VALUES (), ()....; SELECT * FROM tb_city WHERE country='KOREA' AND city='SEOUL';

SELECT를 한 결과..

(1) country 컬럼의 유니크값이 10개일 때

전체 레코드 1만건 중 country='KOREA' 가 성립하는 레코드는 대략 1000건으로 예상할 수 있다. city='SEOUL'이라는 조건까지 부합하는 레코드는 1건이다. 따라서 선택도가 낮으면 999건의 불필요한 데이터를 읽게 된다.

​

(2) country 컬럼의 유니크값이 1000개 일 때

전체 레코드 1만건 중 검색해야 할 레코드는 대략 10건 정도이다. 따라서 9건만 불필요하게 읽은 것이다. 현실적으로는 모든 조건을 만족하도록 인덱스를 생성한다는 것을 불가능하기 때문에 이 정도의 낭비는 무시할 수 있다.

​

이와 같이 인덱스에서 유니크한 값의 개수는 인덱스나 쿼리의 효율성에 큰 영향을 미치게 된다.

MySQL 옵티마이저는 인덱스의 통계 정보(유니크한 값의 개수, 전체 레코드 개수)를 관리한다. 이 통계를 활용해서 쿼리의 실행 계획을 수립한다. 다음의 쿼리로 특정 테이블의 카디널리티를 확인할 수 있다.

SHOW INDEXES FROM `테이블명`;

유니크한 값의 개수를 고려하고 SHOW INDEXS와 실행계획 (EXPLAIN SELECT ..)을 활용하여 튜닝을 해나가면서 인덱스를 설계해야 한다.

​

※ 멀티 컬럼 인덱스의 경우 선택도가 높은 컬럼이 먼저 오는 것이 좋다. 물론 어떤 컬럼이 먼저 와야 하는지 기획적 요구사항이 있다면 선택도가 낮음에도 불구하고 먼저 와야 한다. 예를 들어 게임의 길드를 구성하는 길드 멤버 테이블이 있다고 가정해보자. 그러면 guild_id와 user_id로 PK를 생성할 수 있을 것이다. guild_id를 사용해 검색하는 경우가 빈번하기 때문에 guild_id 컬럼이 선택도가 낮음에도 불구하고 첫번째 인덱스 컬럼이 되어야 한다. 그러나 이러한 기획적 요구사항이 없고 어떤 컬럼이 먼저 와도 상관없는 경우 선택도가 높은 컬럼이 먼저 오는 게 좋다.