RDB 인덱스 개념 정리

인덱스 (Index)

데이터베이스에서 검색 성능을 향상시키기 위해 사용하는 데이터 구조입니다.
다양한 쿼리 성능을 최적화하지만, 사용 시 장단점을 고려해야 합니다.

인덱스의 장점

1. 검색 성능 향상
   •  Full Table Scan을 피할 수 있어 검색 속도가 빨라집니다.
   •  대규모 데이터베이스에서 WHERE 조건이 포함된 쿼리의 효율을 높입니다.
2. 정렬 성능 향상
   •  인덱스가 있는 열에 대해 ORDER BY, GROUP BY 사용 시 추가 작업 없이 정렬된 결과를 가져옵니다.
   •  인덱스 열은 이미 정렬된 상태로 데이터를 그룹화하여 추가 연산을 줄입니다.
3. 중복 방지
   •  UNIQUE 인덱스를 통해 특정 열의 데이터 중복을 방지할 수 있습니다.
4. 조인 성능 향상
   •  인덱스가 있는 열을 기준으로 테이블 간 JOIN 쿼리를 빠르게 처리합니다.
     
     

인덱스의 단점

1. 변경이 잦은 데이터에 부적합
   •  데이터 변경 시 인덱스를 재구성해야 하므로 관리 비용이 높습니다.
2. 쓰기 성능 저하
   •  INSERT, UPDATE, DELETE 작업 시 인덱스 테이블을 수정해야 하므로 작업이 두 번 이루어져 성능이 저하될 수 있습니다.
3. 추가 저장 공간 필요
   •  인덱스를 유지하기 위한 별도의 데이터 구조가 필요하며, 테이블 크기가 클수록 인덱스 크기도 증가합니다.
4. 인덱스 남용으로 인한 성능 저하
   •  너무 많은 인덱스를 생성하면 데이터베이스가 어떤 인덱스를 사용할지 결정하는 데 시간이 오래 걸릴 수 있습니다.

 

복합 인덱스 (Composite Index)

두 개 이상의 열(column)을 기반으로 생성된 인덱스입니다.
단일 인덱스가 하나의 열에 대해서만 속도를 높이는 반면, 복합 인덱스는 여러 열의 조합을 활용하여 검색 성능을 최적화합니다.

 

 

복합 인덱스의 특징

1. 인덱스 순서 중요성
   •  복합 인덱스는 정의된 열의 순서에 따라 작동합니다.
     예를 들어, (column1, column2)로 생성된 복합 인덱스는 column1을 우선 정렬하고, 그다음 column2를 정렬합니다.
2. 데이터 정렬 지원
   •  복합 인덱스는 정의된 열 순서에 따라 데이터를 효율적으로 정렬합니다.
     예: (A ASC, B DESC)는 A를 오름차순, B를 내림차순으로 정렬합니다.
3. 부분적 활용
   •  첫 번째 열이 조건에 포함된 경우, 부분적으로 인덱스를 활용할 수 있습니다.
   •  하지만 첫 번째 열을 건너뛰고 두 번째 열만 사용하는 경우 인덱스를 활용할 수 없습니다.

 

 

복합 인덱스 생성

 

CREATE INDEX index_name ON table_name (column1, column2, column3);

 

복합 인덱스 활용 예시

-- 완전 활용
SELECT * FROM table_name WHERE column1 = 'value1';

-- 부분 활용
SELECT * FROM table_name WHERE column1 = 'value1' AND column2 = 'value2';

-- 완벽 활용
SELECT * FROM table_name WHERE column1 = 'value1' AND column2 = 'value2' AND column3 = 'value3';

 

복합 인덱스의 활용 여부

1. WHERE column1 = 'value1'
   → 복합 인덱스를 사용할 수 있습니다.
2. WHERE column2 = 'value2'
   → 복합 인덱스를 사용할 수 없습니다.
3. WHERE column1 = 'value1' AND column2 = 'value2'
   → 복합 인덱스를 완벽히 활용할 수 있습니다.
4. WHERE column1 = 'value1' AND column2 = 'value2' AND column3 = 'value3'
   → 복합 인덱스를 최적화하여 모든 열을 효율적으로 활용합니다.

 

사용 시 고려 사항

•  복합 인덱스는 자주 사용하는 열 순서에 맞게 설계해야 최적의 성능을 발휘합니다.
•  너무 많은 인덱스를 생성하면 쓰기 성능이 저하되고 관리 비용이 증가할 수 있습니다.
  따라서 쿼리 패턴을 분석하여 꼭 필요한 경우에만 생성하는 것이 중요합니다.

 

인덱스 테스트 시나리오

 

1. 데이터 조건:
   •  약 40만 건의 데이터를 가진 테이블(test_index_performance)을 사용.
   •  단순한 조건(department_id = 5 AND hire_date > '2010-01-01')으로 조회 쿼리 실행.
2. 테스트 과정:
   

   

   실제 인덱스 테스트 사진

   
   
   
   
   •  인덱스 생성 후 조회:
     
     CREATE INDEX idx_department_id ON test_index_performance(department_id);
     SELECT * FROM test_index_performance WHERE department_id = 5 AND hire_date > '2010-01-01' LIMIT 400000;
     
     실행 결과 : 0.219초.
      
      
      
   •  인덱스 삭제 후 조회:
      
     DROP INDEX idx_department_id ON test_index_performance; SELECT * FROM test_index_performance WHERE department_id = 5 AND hire_date > '2010-01-01' LIMIT 400000;
     
     실행 결과: 0.313초.

 

결과 분석

•  인덱스 생성 후:
  •  조건에 해당하는 데이터만 효율적으로 검색하기 위해 인덱스를 사용. 
  •  Full Table Scan(전체 테이블 스캔)을 피하면서, 쿼리 실행 시간이 0.219초로 단축됨.
•  인덱스 삭제 후:
  •  테이블 전체를 스캔(Full Table Scan)하면서 조건을 필터링하므로 실행 시간이 0.313초로 증가.
•  성능 차이:
  •  단순 데이터와 단순 쿼리에서는 약 0.1초 정도의 성능 차이가 발생.
  •  하지만 데이터가 훨씬 더 방대하거나 조건이 복잡한 경우, 인덱스 유무에 따른 성능 차이는 훨씬 커질 가능성이 큼.

결론

이번 테스트를 통해 인덱스를 활용하면 Full Table Scan을 줄여 성능을 향상시킬 수 있음을 확인했습니다.
특히, 방대한 데이터나 복잡한 쿼리에서는 효율적인 인덱스 설계가 성능 개선에 큰 영향을 미칠 수 있습니다.
따라서, 데이터를 효율적으로 검색하려면 자주 사용되는 열 또는 조건에 적절한 인덱스를 생성하는 것이 필수적입니다.