Index 설정을 어떻게 해야 가장 효율적으로 쿼리를 구축할 수 있을까?

Index 관련 기본개념

1. WHERE 절에서 필터링할 때:
   • 인덱스 컬럼을 사용하여 데이터를 필터링하면 전체 테이블 스캔 대신 인덱스를 통해 빠르게 원하는 레코드를 찾을 수 있습니다.
   • 특히 높은 선택도(selectivity)를 가진 컬럼, 즉 고유한 값이 많은 컬럼에서 더 효과적입니다.
2. JOIN 연산 시:
   • 두 테이블을 조인할 때 조인 키에 인덱스가 있으면 조인 성능이 크게 향상됩니다.
   • 외래 키 관계가 있는 컬럼에는 인덱스를 생성하는 것이 일반적입니다.
3. ORDER BY 절에서 정렬할 때:
   • 인덱스는 이미 정렬된 상태로 데이터를 저장하므로, 정렬 연산의 비용을 줄일 수 있습니다.
   • 정렬 방향(ASC/DESC)과 인덱스 방향이 일치할 때 더 효과적입니다.
4. GROUP BY 절에서 그룹화할 때:
   • 그룹화 작업에서도 인덱스를 활용하면 성능이 향상됩니다.
5. 복합 인덱스의 왼쪽 접두사 규칙(Left-most Prefix Rule)을 활용할 때:
   • 복합 인덱스의 경우, 인덱스의 왼쪽부터 순서대로 사용될 때 효과적입니다.
   • 예: (A, B, C) 인덱스는 A, (A,B), (A,B,C) 조건에서 사용될 수 있지만, (B,C)나 B만 사용하는 경우에는 효율적이지 않습니다.
6. 작은 결과 집합을 검색할 때:
   • 전체 테이블의 일부분만 검색할 때(약 20-30% 이하) 인덱스가 효율적입니다.
   • 대량의 데이터(테이블의 대부분)를 검색하는 경우에는 오히려 전체 테이블 스캔이 더 효율적일 수 있습니다.
7. MAX/MIN 같은 집계 함수를 사용할 때:
   • 인덱스가 있는 컬럼에서 최댓값이나 최솟값을 찾는 작업은 인덱스를 통해 빠르게 수행할 수 있습니다.
8. 범위 조건에서 인덱스를 사용할 때:
   • 날짜나 숫자 범위를 검색할 때(예: WHERE price BETWEEN 100 AND 200)도 인덱스가 효과적입니다.

인덱스가 효율적이지 않은 경우:

1. 인덱스된 컬럼에 함수를 적용할 때:
   • WHERE YEAR(date_column) = 2023와 같이 함수를 사용하면 인덱스가 사용되지 않습니다.
   • 대신 WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31'처럼 작성하는 것이 좋습니다.
2. NOT, !=, <> 연산자를 사용할 때:
   • 이러한 부정 연산자는 인덱스 사용 효율이 떨어집니다.
3. LIKE '%text' (후행 와일드카드):
   • 선행 와일드카드(예: LIKE '%text')는 인덱스를 사용할 수 없습니다.
   • 반면, 후행 와일드카드(예: LIKE 'text%')는 인덱스를 사용할 수 있습니다.
4. OR 연산자:
   • 둘 중 하나의 조건만 인덱스가 있는 경우, 옵티마이저는 인덱스 사용을 포기할 수 있습니다.
   • 둘 다 인덱스가 있으면 인덱스 병합(index merge)을 사용할 수 있습니다.
5. NULL 값 검색:
   • WHERE column IS NULL은 인덱스 효율이 떨어질 수 있습니다.
6. 작은 테이블에서의 인덱스:
   • 몇 백 행 정도의 작은 테이블에서는 인덱스 오버헤드가 이점보다 클 수 있습니다.
7. 자주 갱신되는 컬럼:
   • 데이터가 자주 변경되는 컬럼에 인덱스를 걸면 인덱스 유지 비용이 높아집니다.

인덱스 최적화는 이런 요소들을 종합적으로 고려하여 데이터베이스 성능을 향상시키는 중요한 작업입니다.