1. 인덱스 선택도와 카디널리티의 개념
- 데이터베이스 성능 최적화에서 인덱스는 핵심적인 역할을 합니다.
- 그러나 모든 컬럼에 인덱스를 추가하는 것은 오히려 성능을 저하시킬 수 있습니다.
- 효율적인 인덱스 설계를 위해 선택도(Selectivity)와 카디널리티(Cardinality)라는 두 가지 중요한 개념을 이해해야 합니다.
1.1 카디널리티(Cardinality)란?
- 카디널리티는 인덱스가 포함하는 컬럼에서 고유한 값의 수를 의미합니다.
- MySQL에서
SHOW INDEXES명령을 실행하면 출력 결과의cardinality컬럼에서 특정 인덱스 컬럼의 대략적인 고유 값 수를 확인할 수 있습니다. - 카디널리티 값은 실시간으로 정확하게 계산되지 않고 통계를 기반으로 한 추정치입니다.
정보
MySQL은 테이블의 레코드가 많을 경우 통계를 기반으로 카디널리티를 추정합니다. 이 통계는 ANALYZE TABLE 명령으로 갱신할 수 있습니다.
1.2 선택도(Selectivity) 란?
- 선택도는 컬럼 내 값의 고유성을 측정하는 지표입니다.
- 선택도 = 카디널리티 / 총 레코드 수
- 선택도 값이 1에 가까울수록(최대 1) 해당 컬럼의 값이 더 고유하다는 의미입니다.
- 선택도 값이 0에 가까울수록 중복된 값이 많다는 의미입니다.
1.2.1 선택도 계산 예시
-- 선택도 계산을 위한 SQL 쿼리
SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;
- 위 쿼리는
column_name컬럼의 선택도를 계산합니다. - 결과 값이 1에 가까울수록 해당 컬럼은 인덱스로 적합합니다.
2. 인덱스 선택도가 쿼리 성능에 미치는 영향
- 선택도는 MySQL 쿼리 옵티마이저가 인덱스 사용 여부를 결정하는 중요한 기준입니다.
- 선택도가 높은 인덱스는 더 효율적으로 데이터를 필터링하여 쿼리 성능을 향상시킵니다.
- MySQL은 일반적으로 인덱스를 사용하는 것이 전체 테이블 스캔보다 효율적인지 판단하기 위해 선택도를 고려합니다.
2.1 인덱스의 낮은 선택도 문제
- 낮은 선택도를 가진 컬럼에 인덱스를 생성하면 기대한 성능 향상을 얻지 못할 수 있습니다.
- 대표적인 예로, 성별(gender)과 같이 값의 종류가 매우 적은 컬럼을 들 수 있습니다.
- 성별 컬럼에는 주로 '남성', '여성'과 같은 두 가지 값만 존재하므로 선택도가 매우 낮습니다.
경고
선택도가 낮은 컬럼(예: 0.1 이하)에 인덱스를 생성하면 MySQL 옵티마이저는 해당 인덱스를 무시하고 전체 테이블 스캔을 선택할 수 있습니다. 이는 인덱스 생성과 유지에 리소스를 소모했음에도 실제 쿼리 성능이 개선되지 않는 결과를 초래합니다.
2.2 높은 선택도의 중요성
- 기본 키(Primary Key)와 고유 키(Unique Key)는 선택도가 1로, 가장 높은 선택도를 가집니다.
- 이메일 주소, 주민등록번호, UUID와 같이 고유성이 보장되는 컬럼은 인덱스로 매우 적합합니다.
- 선택도가 높은 인덱스는 소량의 데이터만 검색하므로 쿼리 성능을 크게 향상시킵니다.
3. 실제 사례로 알아보는 인덱스 선택도
3.1 사용자 테이블 예시
사용자 테이블을 예로 들어 다양한 컬럼의 선택도를 비교해보겠습니다:
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE,
status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
gender ENUM('male', 'female', 'other') NOT NULL,
country_code CHAR(2),
registration_date DATE
);
각 컬럼의 선택도 특성:
id: 기본 키로, 선택도는 1(최대)username,email: 고유 값을 갖는 컬럼으로, 선택도는 1status: 세 가지 값만 가능, 선택도가 매우 낮음gender: 세 가지 값만 가능, 선택도가 매우 낮음country_code: 국가 코드의 수에 따라 중간 정도의 선택도(약 200여 개 국가)registration_date: 날짜의 분포에 따라 선택도가 달라짐
3.2 쿼리 최적화 실제 사례
3.2.1 낮은 선택도 컬럼의 문제점
특정 성별의 사용자를 찾는 쿼리:
-- gender 컬럼의 선택도가 낮기 때문에 인덱스가 있어도
-- MySQL은 전체 테이블 스캔을 선택할 가능성이 높습니다
SELECT * FROM users WHERE gender = 'female';
위 쿼리에서 gender 컬럼에 인덱스를 추가해도, 해당 값이 전체 레코드의 약 50%를 차지한다면 MySQL은 인덱스보다 전체 테이블 스캔이 더 효율적이라고 판단할 수 있습니다.
3.2.2 복합 인덱스로 선택도 높이기
낮은 선택도 컬럼의 문제는 다른 컬럼과 함께 복합 인덱스를 생성하여 해결할 수 있습니다:
-- gender와 country_code를 결합한 복합 인덱스
CREATE INDEX idx_gender_country ON users(gender, country_code);
-- 이제 이 쿼리는 인덱스를 효율적으로 사용할 수 있습니다
SELECT * FROM users WHERE gender = 'female' AND country_code = 'KR';
두 컬럼을 결합함으로써 전체 선택도가 높아져 쿼리 성능이 향상됩니다.
4. 인덱스 선택도 확인 및 분석 방법
4.1 카디널리티 확인하기
-- 테이블의 모든 인덱스와 카디널리티 정보 확인
SHOW INDEXES FROM users;
실행 결과 예시:
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users | 0 | PRIMARY | 1 | id | A | 10000 | NULL | NULL | | BTREE | | | YES |
| users | 0 | username | 1 | username | A | 10000 | NULL | NULL | YES | BTREE | | | YES |
| users | 0 | email | 1 | email | A | 10000 | NULL | NULL | YES | BTREE | | | YES |
| users | 1 | status | 1 | status | A | 3 | NULL | NULL | YES | BTREE | | | YES |
| users | 1 | gender | 1 | gender | A | 3 | NULL | NULL | | BTREE | | | YES |
| users | 1 | country_code | 1 | country | A | 200 | NULL | NULL | YES | BTREE | | | YES |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
이 결과에서 Cardinality 컬럼은 각 인덱스의 고유 값 수를 나타냅니다. id, username, email은 높은 카디널리티를 보이는 반면, status와 gender는 매우 낮은 카디널리티를 보입니다.
4.2 테이블 통계 갱신하기
MySQL은 테이블 통계를 기반으로 카디널리티를 추정하므로, 정확한 판단을 위해 통계를 주기적으로 갱신해야 합니다:
-- 테이블 통계 갱신
ANALYZE TABLE users;
4.3 실행 계획으로 인덱스 사용 확인하기
-- 실행 계획 확인
EXPLAIN SELECT * FROM users WHERE gender = 'female';
실행 결과 예시:
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | gender | NULL | NULL | NULL | 9852 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
- 위 결과에서
type이ALL이고key가NULL이면 MySQL이 전체 테이블 스캔을 수행한다는 의미입니다. - 이는
gender인덱스의 선택도가 낮아 옵티마이저가 인덱스 사용을 선택하지 않았음을 나타냅니다.
5. 효율적인 인덱스 설계 전략
5.1 높은 선택도 컬럼 우선 인덱싱
- 선택도가 높은 컬럼(고유값이 많은 컬럼)에 우선적으로 인덱스를 생성합니다.
- 기본 키와 고유 키는 자동으로 높은 선택도를 갖습니다.
- 일반적으로 선택도가 0.1 이상인 컬럼은 인덱스 후보로 고려할 수 있습니다.
5.2 복합 인덱스 활용하기
- 개별 컬럼의 선택도가 낮더라도, 여러 컬럼을 결합한 복합 인덱스는 선택도가 높아질 수 있습니다.
- 복합 인덱스 설계 시 선택도가 높은 컬럼을 먼저 배치하는 것이 일반적으로 효율적입니다.
팁
복합 인덱스 설계 시 = 조건으로 자주 검색되는 컬럼을 먼저 배치하고, 범위 검색(>, < 등)을 사용하는 컬럼은 뒤에 배치하면 인덱스 효율성이 높아집니다.
5.3 쿼리 패턴 분석
- 실제 애플리케이션에서 자주 실행되는 쿼리 패턴을 분석하여 인덱스 설계에 반영합니다.
- 특정 컬럼의 선택도가 낮더라도, 해당 컬럼을 자주 검색한다면 인덱스가 필요할 수 있습니다.
- 이 경우 다른 컬럼과의 복합 인덱스를 고려해봐야 합니다.
5.4 인덱스 유지 비용 고려하기
- 인덱스는 읽기 성능을 향상시키지만, 쓰기 작업(INSERT, UPDATE, DELETE)에는 추가 부하를 발생시킵니다.
- 쓰기가 많은 테이블에는 꼭 필요한 인덱스만 유지하는 것이 좋습니다.
- 선택도가 낮고 사용 빈도도 낮은 인덱스는 제거를 고려해야 합니다.
6. 결론
- 인덱스 선택도와 카디널리티는 MySQL 데이터베이스 성능 최적화의 핵심 개념입니다.
- 선택도가 높은 컬럼에 인덱스를 생성함으로써 쿼리 성능을 크게 향상시킬 수 있습니다.
- 선택도가 낮은 컬럼은 단독보다는 다른 컬럼과 함께 복합 인덱스로 구성하는 것이 효율적입니다.
- 실행 계획을 통해 MySQL이 인덱스를 어떻게 활용하는지 분석하고, 이를 바탕으로 인덱스 전략을 지속적으로 개선해야 합니다.
- 데이터베이스 성능 최적화는 단순히 인덱스를 많이 추가하는 것이 아니라, 적절한 선택도를 가진 컬럼에 효율적인 인덱스를 설계하는 것이 핵심입니다.