MySQL Index 정리 및 팁

인덱스(Index)란 무엇인가?

MySQL에서 인덱스는 데이터를 효율적으로 검색하기 위해 사용되는 자료구조입니다. 기본적으로 MySQL은 B-Tree 인덱스를 사용하며, 이는 데이터를 정렬된 상태로 저장하여 빠른 검색을 가능하게 합니다.

---

B-Tree 인덱스 구조

• 브랜치 노드: 각 노드는 여러 키와 자식 노드를 가지며, 데이터는 정렬된 형태로 저장됩니다.
• 정렬된 데이터: 인덱스는 앞의 키를 기준으로 뒤의 키가 정렬되어 있습니다.
• 빠른 검색: 로그 시간 복잡도로 데이터를 탐색할 수 있습니다.

---

카디널리티(Cardinality)란?

카디널리티는 데이터의 중복도를 나타내는 수치입니다.

• 높은 카디널리티 (High Cardinality): 중복도가 낮은 데이터 (예: 주민등록번호, 핸드폰 번호).
• 낮은 카디널리티 (Low Cardinality): 중복도가 높은 데이터 (예: 성별, 국가 코드).

카디널리티가 높을수록 인덱스의 효율성이 높아집니다.

---

인덱스 동작 방식

테이블에 인덱스를 설정할 때 고려해야 할 세 가지 경우가 있습니다.

1. 한 컬럼에 인덱스를 설정하는 경우

• 데이터 중복도가 낮은 컬럼을 선택하여 인덱스를 설정합니다.
• 높은 카디널리티를 가진 컬럼에 인덱스를 걸면 검색 성능이 향상됩니다.

---

2. 다중 컬럼에 각각 인덱스를 설정하는 경우

• 여러 컬럼에 단일 컬럼 인덱스를 각각 설정합니다.
• 쿼리 실행 시 MySQL은 Index Merge 최적화를 통해 인덱스 결과를 병합합니다.
• 참고: MySQL Index Merge Optimization

---

3. 다중 컬럼 인덱스(Multiple-column Index)를 설정하는 경우

• 여러 컬럼을 하나의 인덱스로 설정합니다.
• 인덱스 설정 시 카디널리티가 높은 순서로 컬럼을 나열하는 것이 좋습니다.

카디널리티에 따른 인덱스 성능 비교

카디널리티 내림차순 (높은 → 낮은)	카디널리티 오름차순 (낮은 → 높은)
더 나은 성능 제공	상대적으로 낮은 성능 제공

---

3.1 다중 컬럼 인덱스 사용 조건

• WHERE 절에서 =, >, <=, BETWEEN, IN 등의 연산자를 사용하는 경우 B-Tree 인덱스가 효율적으로 작동합니다.

  *"B-Tree 자료 구조는 WHERE 절에서 =, >, <=, BETWEEN, IN 등의 연산자에 대응하여 특정 값, 값의 집합 또는 값의 범위를 빠르게 찾을 수 있게 해줍니다."*

• 조인 시 인덱스가 걸린 컬럼을 사용하면 성능이 향상됩니다.

  *"조인을 수행할 때 다른 테이블에서 행을 가져오기 위해 인덱스를 사용합니다. MySQL은 동일한 타입과 크기로 선언된 컬럼의 인덱스를 더 효율적으로 사용할 수 있습니다."*

• 인덱스된 컬럼의 데이터 타입과 크기가 동일하면 더욱 효율적입니다.

  • 예를 들어, VARCHAR(10)과 CHAR(10)은 동일한 크기로 간주되지만, VARCHAR(10)과 CHAR(15)는 그렇지 않습니다.

---

인덱스된 컬럼의 크기와 성능

인덱스된 컬럼의 크기가 작을수록 성능이 향상됩니다. 아래는 데이터 타입별 크기와 성능을 비교한 예시입니다.

데이터 타입	크기	성능
BIGINT	8바이트	보통
UUID	16바이트	느림
VARCHAR(32)	최대 32바이트	빠름

---

풀 테이블 스캔을 피하는 방법

풀 테이블 스캔은 대량의 데이터를 처리할 때 성능 저하를 유발합니다. 이를 피하기 위해 다음을 고려해야 합니다.

1. 적절한 인덱스 설정: 자주 조회되는 컬럼에 인덱스를 설정합니다.
2. 쿼리 최적화: 불필요한 전체 테이블 검색을 피하도록 쿼리를 작성합니다.
3. 통계 정보 최신화: ANALYZE TABLE 명령어를 사용하여 통계 정보를 업데이트합니다.

참고 자료:

• MySQL 8.0 Reference Manual - Avoiding Full Table Scans