데이터베이스 인덱스를 설계할 때 가장 자주 등장하지만 막상 설명하기는 애매한 개념이 있다. 바로 기수성이다. 기수성은 컬럼이 얼마나 다양한 값을 가지는지를 나타내는 지표이며, 실행계획 수립 과정에서 매우 중요한 기준으로 사용된다.
이 글에서는 이론적인 설명을 넘어 동일한 구조의 테이블에 기수성만 다른 데이터를 삽입한 뒤 실제 MySQL 실행계획이 어떻게 달라지는지 직접 확인해본다.
기수성 데이터 준비와 실험 환경
실험을 위해 두 개의 테이블을 준비했다. 두 테이블 모두 컬럼 구조는 동일하며 city, country 두 개의 컬럼만 존재한다. 그리고 country 컬럼에 단일 인덱스를 생성했다.
각 테이블에는 10,000건의 데이터가 삽입되어 있으며 city 컬럼은 두 테이블 모두 동일하다. city1부터 city10000까지 순차적으로 생성된다. 차이는 country 컬럼의 값 분포에 있다.
첫 번째 테이블은 기수성이 높은 데이터다. country 컬럼 값이 1부터 10,000까지 모두 다르다. 즉, country 컬럼의 기수성은 1,000 수준으로 매우 높다.
두 번째 테이블은 기수성이 낮다. country 컬럼 값은 1부터 10까지만 반복된다. 이 경우 country 컬럼의 기수성은 10에 불과하다.
요약하면 다음과 같다. 동일한 row 수, 동일한 인덱스, 동일한 쿼리 조건에서 오직 기수성만 다르게 구성한 것이다. 이 상태에서 실행계획이 어떻게 달라지는지 살펴본다.
기수성 기반 MySQL 실행계획 비교
실행할 쿼리는 매우 단순하다. city와 country 두 조건을 동시에 만족하는 단일 레코드를 조회하는 쿼리다.
SELECT *
FROM tb_city_xxx
WHERE city = 'city9'
AND country = 'country9';
두 테이블 모두 해당 조건을 만족하는 레코드는 단 1건이다. 차이는 이 1건을 찾기 위해 얼마나 많은 불필요한 데이터를 읽느냐에 있다.
기수성이 높은 테이블의 실행계획을 보면 country 인덱스를 통해 약 10건의 레코드를 읽는다. 그 중 9건은 조건에 맞지 않아 버려진다.
반면 기수성이 낮은 테이블에서는 같은 인덱스를 사용하지만 무려 1,000건의 레코드를 읽는다. 그 중 999건은 불필요한 데이터다.
실행계획의 rows 컬럼은 조건을 만족하기 위해 읽어야 하는 예상 레코드 수를 의미한다. filtered는 읽은 데이터 중 조건을 만족하는 비율을 의미한다.
조건 결과는 동일하지만 기수성이 낮은 경우 옵티마이저는 훨씬 많은 데이터를 읽고 필터링한다. 이 차이가 누적되면 쿼리 성능에 직접적인 영향을 미친다.
인덱스 읽기 손익분기점 이해
인덱스를 사용하면 항상 빠를 것 같지만 현실은 그렇지 않다. 인덱스를 거쳐 테이블 레코드를 조회하는 작업은 테이블을 직접 순차 스캔하는 것보다 비용이 더 크다.
일반적으로 DBMS 옵티마이저는 인덱스를 통한 접근 비용을 테이블 직접 접근보다 약 4~5배 비싸다고 판단한다.
예를 들어 전체 레코드가 100만 건인 테이블에서 조건에 맞는 레코드가 50만 건이라면 인덱스를 통해 50만 건을 읽는 것보다 차라리 테이블 전체를 읽는 것이 더 효율적일 수 있다.
이 기준이 바로 읽기 손익분기점이다. 대략 전체 레코드의 20~25% 이상을 읽어야 하는 쿼리라면 옵티마이저는 인덱스를 사용하지 않는 방향을 선택한다.
기수성이 낮은 컬럼에 단일 인덱스를 생성했을 때 실행계획이 기대와 다르게 나오는 이유도 여기에 있다. 인덱스는 존재하지만 실질적인 선택도가 낮아 효율이 떨어지는 것이다.
결론적으로 인덱스 설계에서 기수성은 선택이 아니라 전제 조건이다. 컬럼의 데이터 분포를 이해하지 못한 인덱스는 존재만 할 뿐, 성능 개선에는 도움이 되지 않는다.