인덱스란?
인덱스는 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료 구조로, 책의 '찾아보기'와 유사한 역할을 합니다.
•
역할: 검색, 정렬, 병합 작업을 빠르게 수행.
•
구조: 주로 B-Tree를 사용해 데이터를 정렬된 형태로 저장.
•
비유:
◦
인덱스 없이: 책의 첫 페이지부터 끝까지 확인 (시간 오래 걸림).
◦
인덱스 사용: '찾아보기'로 원하는 단어의 페이지를 바로 이동.
예시: 원본 테이블과 인덱스
원본 테이블 (product)
id | name | price | stock | created_date | sales |
1 | Keyboard | 30,000 | 35 | 2024-02-01 | 4,000 |
2 | Monitor | 150,000 | 22 | 2024-01-15 | 5,500 |
3 | Mouse | 20,000 | 40 | 2024-03-10 | 3,800 |
4 | Laptop | 1,000,000 | 27 | 2024-01-10 | 6,200 |
5 | USB Drive | 10,000 | 18 | 2024-02-15 | 4,700 |
인덱스 생성 (price 기준 B-Tree)
CREATE INDEX idx_price ON product(price);
SQL
복사
인덱스 구조 (B-Tree)
순서 | price | 참조 |
1 | 10,000 | id:5 (USB Drive) |
2 | 20,000 | id:3 (Mouse) |
3 | 30,000 | id:1 (Keyboard) |
4 | 150,000 | id:2 (Monitor) |
5 | 1,000,000 | id:4 (Laptop) |
쿼리 실행 예시
SELECT * FROM product WHERE price = 20,000;
SQL
복사
•
B-Tree에서 price = 20,000인 행(id:3)을 빠르게 찾아 반환.
인덱스의 장단점
장점
1.
검색 속도 향상:
•
WHERE 절, JOIN 조건에서 특정 값 빠르게 조회.
2.
정렬 성능 개선:
•
ORDER BY 작업에서 인덱스 활용 시 정렬 비용 감소.
SELECT * FROM product ORDER BY price ASC;
SQL
복사
3.
병합 성능 개선:
•
GROUP BY 작업에서 데이터 빠르게 그룹화.
단점
1.
추가 저장 공간: 인덱스는 별도 저장 공간 차지.
2.
쓰기 성능 저하: INSERT, UPDATE, DELETE 시 인덱스 갱신으로 오버헤드 발생.
3.
관리 비용 증가: 인덱스 많아질수록 유지보수 부담 증가.
인덱스 효율적으로 선언하는 방법
1.
카디널리티 높은 열 선택
•
카디널리티: 열에 존재하는 고유 값의 개수.
•
예: email (고유 값 많음, 카디널리티 높음) → 인덱스 효과적.
•
예: gender (남/여 2개, 카디널리티 낮음) → 인덱스 효과 미미.
•
팁: 카디널리티가 높을수록 쿼리 결과 행이 적어 인덱스 효율 증가.
2.
복합 인덱스 활용
•
자주 함께 검색되는 열은 복합 인덱스로 생성.
CREATE INDEX idx_name_price ON product(name, price);
SQL
복사
•
주의: 자주 사용되는 열을 앞에 배치.
•
커버링 인덱스: 쿼리 결과에 필요한 모든 열이 인덱스에 포함되면 테이블 접근 없이 데이터 반환.
3.
ORDER BY와 함께 사용
•
정렬 기준 열에 인덱스 생성 시 ORDER BY 성능 개선.
4.
LIKE 검색 최적화
•
접두사 검색(LIKE '김%')은 인덱스 사용.
•
중간/접미사 검색(LIKE '%철수%')은 인덱스 활용 불가.
SELECT * FROM product WHERE name LIKE 'Mouse%'; -- 인덱스 사용
SQL
복사
카디널리티와 선택도
카디널리티 (Cardinality)
•
정의: 열에 존재하는 고유한 값의 개수.
•
예시: email 열에 1,000개 행, 고유 값 900개 → 카디널리티 = 900.
•
확인 방법:
SHOW INDEX FROM product;
SQL
복사
선택도 (Selectivity)
•
정의: 카디널리티를 전체 행 수로 나눈 값.
•
수식: 선택도 = 카디널리티 / 전체 행 수.
•
예시:
◦
email 열 (카디널리티 900, 행 1,000) → 선택도 = 0.9 (90%).
◦
gender 열 (카디널리티 2, 행 1,000) → 선택도 = 0.002 (0.2%).
•
중요성: 선택도가 높을수록 인덱스 사용 시 쿼리 성능 향상.
MySQL 옵티마이저의 판단
•
MySQL 쿼리 옵티마이저는 카디널리티와 선택도를 분석해 인덱스 사용 여부 결정.
•
카디널리티 높음 → 인덱스 사용 유리.
•
카디널리티 낮음 → Full Table Scan이 더 효율적일 수 있음.
B-Tree 인덱스 알고리즘
•
특징: MySQL은 주로 B-Tree 구조 사용, 시간 복잡도 O(log N).
•
왜 B-Tree인가?
◦
균형 유지: 데이터 많아져도 자동으로 균형 맞춰 성능 보장.
◦
디스크 I/O 최소화: 한 노드에 여러 키 저장해 디스크 접근 횟수 감소.
◦
범위 검색 지원: BETWEEN, <, > 같은 쿼리에 효율적.
◦
정렬 최적화: ORDER BY 작업 가속화.
B-Tree vs 다른 구조
•
Hash 인덱스: 등치 검색(=)에 빠름, 범위 검색/정렬 부적합.
•
AVL/Red-Black Tree: 이진 트리 기반, O(log N)이지만 디스크 I/O 많아 부적합.
•
Bitmap 인덱스: 카디널리티 낮은 열에 유용, 범용성 떨어짐.
인덱스 사용 시 주의점
1.
과도한 인덱스 생성 금지: 쓰기 성능 저하, 저장 공간 낭비.
2.
카디널리티 낮은 열 피하기: 중복 값 많은 열(예: gender)은 인덱스 효과 미미.
3.
복합 인덱스 순서 고려: 자주 검색되는 열을 앞에 배치.
4.
쿼리 최적화 확인:
EXPLAIN SELECT * FROM product WHERE name = 'Mouse';
SQL
복사
다양한 인덱스 유형
•
B-Tree 인덱스: 범위 검색, 정렬에 적합 (가장 일반적).
•
Hash 인덱스: 등치 검색에 최적화, 범위 검색 불가.
•
복합 인덱스: 여러 열 조합.
•
클러스터드 인덱스: 데이터가 인덱스 순서대로 물리적 저장 (InnoDB 기본 키).
•
커버링 인덱스: 쿼리 결과에 필요한 모든 데이터를 인덱스에서 제공.
결론
•
인덱스는 MySQL 성능 최적화의 핵심 도구.
•
카디널리티와 선택도를 고려해 적절한 열에 인덱스 생성.
•
복합 인덱스와 커버링 인덱스를 활용해 검색, 정렬, 병합 작업 효율성 극대화.
•
과도한 인덱스는 쓰기 성능 저하와 저장 공간 낭비 초래 → 쿼리 패턴과 데이터 특성 분석해 균형 있게 설계.