데이터베이스 인덱스 최적화 전략 및 핵심 개념 정리

데이터베이스 인덱스 최적화 전략 및 핵심 개념 정리

데이터베이스 **옵티마이저(Optimizer)**가 효율적인 쿼리 실행 계획을 수립하는 데 필수적인 인덱스(Index)의 작동 원리, 선택 기준, 그리고 올바른 사용법을 정리합니다.

---

1. 인덱스 미사용 조건 (Index Skip)

옵티마이저가 인덱스를 생성했음에도 불구하고 사용하지 않고 **풀 테이블 스캔(Full Table Scan)**을 선택하는 주요 경우입니다.

조건	설명
높은 데이터 조회율	조회하려는 데이터가 원본 데이터의 20~25% 이상일 경우 (정확한 수치는 DBMS나 설정에 따라 다름).
이유 (I/O 비용)	인덱스를 통해 원본 데이터에 접근하는 과정에서 발생하는 랜덤 I/O의 비용이, 테이블 전체를 순차적으로 읽는 순차 I/O 비용보다 크다고 판단하기 때문입니다. 순차 I/O는 디스크 헤더의 이동이 적어 대용량 읽기에 효율적입니다.

 

---

2. 커버링 인덱스 (Covering Index)

커버링 인덱스는 쿼리가 필요로 하는 모든 컬럼이 인덱스 자체에 포함되어 있어, 데이터 파일(원본 테이블)에 접근할 필요가 없도록 설계된 인덱스입니다.

• 원리: 쿼리 실행 시 필요한 정보를 인덱스 블록 내에서 모두 해결하여, 인덱스를 참조해 원본 데이터 레코드를 찾는 과정에서 발생하는 랜덤 I/O를 완전히 제거합니다.
• 장점:
  • 극적인 조회 속도 향상: 랜덤 I/O가 제거되어 매우 빠릅니다.
• 단점:
  • 저장 공간 증가: 인덱스 자체가 더 많은 컬럼의 데이터를 포함하므로 저장 공간을 더 많이 차지합니다.
  • 쓰기 성능 저하: 데이터 변경(INSERT, UPDATE, DELETE) 시 인덱스도 함께 갱신해야 하는 비용이 커지므로 쓰기 성능이 저하됩니다.
• 고려 사항: 조회가 빈번하고 쓰기가 상대적으로 적은 테이블 및 쿼리에 적합합니다.

---

3. 복합 인덱스 (Composite Index) 활용 대원칙

두 개 이상의 컬럼을 조합하여 만든 인덱스를 효율적으로 사용하기 위한 규칙입니다.

3-1. 인덱스 순서 사용 (왼쪽 접두어 규칙, Leftmost Prefix Rule)

• 원칙: 복합 인덱스는 구성된 컬럼 순서대로 사용해야 효율적입니다.
• 예시: (col1, col2, col3) 순서의 인덱스에서 col1만 사용하거나, col1과 col2만 사용하는 경우 인덱스를 활용할 수 있지만, col2나 col3만 단독으로 사용하는 경우 인덱스를 탈 수 없습니다.

3-2. 조건 처리 순서 (등호 vs. 범위)

• 원칙: **등호 조건(=)**을 먼저 사용하고, **범위 조건(<,>,BETWEEN,LIKE '%'$)**을 뒤에 배치해야 합니다.
• 범위 조건의 영향: 복합 인덱스에서 범위 조건이 먼저 사용되면, 해당 컬럼 이후에 오는 컬럼들은 인덱스 순서를 활용할 수 없게 되어 인덱스가 깨지는(Index Break) 현상이 발생합니다.
• 대체 전략: 광범위한 범위 조건 대신 특정 값들을 나열하는 IN 절을 사용하면 옵티마이저가 여러 개의 등호 조건처럼 처리하여 효율성을 높일 수 있습니다.

3-3. 정렬(ORDER BY)과의 연관성

• 원칙: ORDER BY 절의 정렬 순서가 인덱스의 순서와 일치하면, 별도의 정렬 작업(Sort Operation) 없이 인덱스의 정렬된 상태를 그대로 활용할 수 있어 성능이 향상됩니다.

---

4. 카디널리티 (Cardinality)와 인덱스 효율

카디널리티는 특정 컬럼에 존재하는 중복되지 않는 값의 개수를 의미하며, 인덱스 선택의 핵심 기준이 됩니다.

• 높은 카디널리티: 해당 컬럼에 중복된 값이 거의 없다 (예: 주민등록번호, Primary Key).
  • 효율성: 특정 데이터를 찾을 때 검색 범위를 확 줄여주기 때문에 인덱스 효율이 매우 높습니다.
• 낮은 카디널리티: 해당 컬럼에 몇 종류 안되는 중복된 값이 많다 (예: 성별(남/여), 예/아니오).
  • 효율성: 데이터의 대부분(예: 80%)이 특정 값에 해당한다면, 옵티마이저는 인덱스 사용보다 풀 스캔이 낫다고 판단할 가능성이 높습니다.
• 핵심: 인덱스는 검색 범위를 줄이는 것이 목표입니다. 카디널리티가 높을수록 목표 달성에 유리합니다.

---

5. 대용량 데이터 조회 최적화 (LIMIT/ROWNUM 활용)

대용량 테이블에서 최신 상품 10개와 같이 특정 개수(N)의 데이터만 가져오는 경우, LIMIT (MySQL, PostgreSQL 등) 또는 ROWNUM (Oracle)을 사용하는 것이 효과적입니다.

• 예시: ORDER BY registered_date DESC LIMIT 10 (등록일 내림차순 정렬 후 10개만 가져오기)
• 효과: registered_date 컬럼에 인덱스가 걸려 있고 정렬 방향이 일치한다면, 데이터베이스는 전체 데이터를 정렬하지 않고 인덱스를 따라 이동하며 필요한 N개의 데이터만 빠르게 찾습니다. 즉, 정렬 시간이 크게 줄어들어 성능이 향상됩니다.

---

6. 인덱스의 단점 (Trade-off)

인덱스는 조회 성능을 높여주지만, 다음과 같은 **비용(Cost)**을 발생시키므로 항상 **트레이드 오프(Trade-off)**를 고려해야 합니다.

1. 용량 차지: 인덱스 자체가 별도의 저장 공간을 차지합니다.
2. 쓰기 성능 저하: 데이터가 변경될 때마다(INSERT, UPDATE, DELETE), 해당 테이블의 모든 인덱스를 동시에 갱신해야 하므로 쓰기 작업의 오버헤드가 증가합니다.
3. 관리 비용: 인덱스가 많아질수록 옵티마이저가 최적의 실행 계획을 수립하기 위해 고려해야 할 경우의 수가 늘어나고, 인덱스의 지속적인 관리 및 재구성(Rebuild) 비용이 발생할 수 있습니다.

 

인덱스 및 스캔 방식 최종 요약   * range ,ffs, fs 만 비교

방식	주 사용 목적	읽는 대상	I/O 방식	인덱스 활용
Index Range Scan (인덱스 범위 스캔)	특정 범위 탐색 (가장 이상적인 인덱스 사용법)	인덱스의 일부	랜덤 I/O (테이블 접근 시)	조건에 맞는 데이터를 콕 집어 찾음.
Fast Full Scan (FFS)	전체 데이터 읽기 (Full Scan 대안)	해당 인덱스 파일 전체	순차 I/O	인덱스를 탐색 용도가 아닌, 더 작은 통로로 활용.
Full Table Scan (FS)	전체 데이터 읽기 (가장 확실한 방법)	원본 테이블 파일 전체	순차 I/O	인덱스를 사용하지 않음.

Sheets로 내보내기

---

핵심 작동 원리 재확인

1. Index Range Scan:
   • WHERE 조건이 인덱스를 따라 탐색 범위를 좁히는 데 성공했을 때 발생합니다.
   • 조회 건수가 적을수록 랜덤 I/O 비용이 낮아져 가장 효율적입니다.
2. Fast Full Scan (FFS):
   • 랜덤 I/O (Range Scan + Table Access)의 총비용이 너무 높을 때, 이를 피하기 위한 대안으로 등장합니다.
   • 인덱스 파일의 크기가 테이블보다 작다는 점을 활용하여, 순차 I/O로 전체를 읽는 것이 더 빠르다고 판단될 때 선택됩니다.
3. Full Table Scan (FS):
   • 인덱스를 사용할 수 없을 때 (예: 데이터 타입 불일치, 컬럼에 함수 적용) 발생하거나,
   • MAX() 같은 집계 함수로 인해 FFS의 안정성이 의심될 때 선택되는 가장 안정적이고 확실한 전체 읽기 방법입니다.