Database | 인덱스(Index)를 생성 시 주의할점 및 생성전략

인덱스(Index)란?

데이터베이스에서 검색 속도를 향상시키기 위해 사용되는 데이터 구조입니다. 

인덱스는 특정 열(컬럼)에 대한 정렬된 데이터를 가지며, 이를통해 빠른 검색, 정렬, 그룹화, 조인 등의 작업을 가능하게 합니다. 

 

인덱스는 주로 SELECT 쿼리의 성능을 향상시키는데 사용되지만, 

삽입, 수정, 삭제와 같은 쓰기 작업의 속도는 저하될 수 있습니다. 

 

인덱스를 사용하게되면, 전체 데이터를 스캔하는 대신 인덱스를 검색하여 원하는 데이터를 빠르게 찾을 수 있습니다. 

 

인덱스(Index)의 특징?

1. 고유성(Uniqueness)
   
   • 유일한 값을 가지도록 정의할 수 있습니다. 
   • 주로 기본키(primary Key)와 관련되어 있습니다. 
2. 클러스터드(Clustered) vs 비클러스터드(Non-clustered)
   • 클러스터드 인덱스는 테이블의 실제 데이터 순서에 맞게 정렬되어있습니다. 
   • 비 클러스터드 인덱스는 별도의 데이터 구조로 정렬됩니다. 
3. 복합 인덱스(Composite Index)
   • 여러 열에 대한 인덱스를 생성할 수 있습니다. 
   • 여러 열을 동시에 고려해야하는 검색 작업에서 성능을 향상시킬 수 있습니다. 
4. 함수 기반 인덱스(Function-Based Index) 
   • 특정 함수를 적용한 결과를 인덱스로 사용할 수 있습니다. 
   • 데이터를 가공한 후에도 인덱스를 활용할 수 있습니다. 
5. 인덱스의 선택과 성능 고려 
   • 어떤 열에 인덱스를 생성할지 고려할 때, 검색 빈도가 높은 열이나 조인 조건에 사용되는 열을 우선적으로 고려해야 합니다. 
   • 그러나 인덱스를 지나치게 많이 생성하면, 쓰기 작업의 성능이 저하될 수 있으므로 적절한 균형을 유지해야 합니다. 

인덱스를 타지 않는 조건 

인덱스가 생성된 컬럼이어도, 인덱스를 타지 않는 경우가 존재합니다. 

 

 

1. 함수나 연산을 사용하는 경우

• 데이터베이스에서는 함수나 연산이 인덱스를 사용하는 것을 어렵게 만들 수 있습니다. 
• 함수를 사용하면 데이터베이스 엔진은 해당 함수를 인덱스된 데이터에 적용하기 어려워져 인덱스의 효과가 감소합니다. 

-- 함수를 사용하는 경우
SELECT * FROM 테이블 WHERE UPPER(인덱스_컬럼) = '대문자';

 

2. OR 연산자 사용 

• OR 연산자는 여러 개의 조건 중 하나만 만족해도 결과를 반환합니다. 
• 이런 경우 데이터베이스 엔진은 각 조건에 대해 인덱스를 타기 어려울 수 있습니다. 

-- OR 연산자를 사용하는 경우
SELECT * FROM 테이블 WHERE 인덱스_컬럼 = '조건' OR 다른_컬럼 = '다른조건';

 

3. NOT 연산자 사용 

• NOT을 사용하면 데이터베이스 엔진은 해당 조건을 반대로 처리해야하기 때문에, 인덱스를 타기 어렵습니다. 

-- NOT 연산자를 사용하는 경우
SELECT * FROM 테이블 WHERE NOT 인덱스_컬럼 = '조건';

 

4. 패턴 매칭을 위한 LIKE 연산자 사용 

• 와일드카드를 사용한 패턴 매칭은 인덱스 효과를 감소시킬 수 있습니다. 

-- LIKE 연산자를 사용하는 경우
SELECT * FROM 테이블 WHERE 인덱스_컬럼 LIKE '패턴%';

 

인덱스 사용 시 주의할점

1. 너무 많은 인덱스를 생성하게되면 쓰기 작업의 성능이 저하되므로, 자주 사용되는 검색조건이나 조인에 필요한 컬럼을 중점적으로 선택해야 합니다. 
2. 테이블에 데이터가 추가, 수정, 삭제될 때마다 인덱스도 업데이트 되어야 합니다. 따라서 쓰기 작업이 빈번한 테이블에서는 인덱스의 수를 최소화하고, 필요한 경우에만 생성하는 것이 좋습니다. 
3. 대용량의 테이블에서는 인덱스의 크기가 상당히 커질 수 있는데, 이는 디스크 공간을 차지 및 캐시 메모리에 올리는 데도 영향을 주기 때문에, 큰 테이블에서는 인덱스를 효율적으로 사용하도록 최적화해야 합니다. 
4. 데이터베이스는 일반적으로 오름차순(ASC) 정렬된 인덱스를 효과적으로 사용합니다. 따라서, 인덱스 생성 시 정렬 순서를 고려해 최적화할 수 있습니다.
5. 테이블의 데이터가 변경될 때마다 인덱스도 유지보수가 필요합니다. 주기적으로 인덱스를 재구축하거나 조정해 최적을 성능을 유지하는 것이 중요합니다. 
6. 데이터베이스는 통계 정보를 사용하여 쿼리 실행 계획을 수립합니다. 따라서 인덱스의 통계 정보가 정확해야 최적의 실행 계획을 수립할 수 있습니다. 주기적으로 통계 정보를 갱신하는 것이 좋습니다. 
7. 검색 시, 인덱스가 무조건 유용한 것은 아닙니다. 인덱스는 테이블 전체 데이터 중 10~15% 이하의 데이터를 처리하는 경우에만 효율적이고, 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 낫습니다. 
8. 인덱스를 관리하기 위해 데이터베이스의 약 10%의 저장공간이 추가로 필요하기때문에, 상황을 고려하여 index를 만들어야합니다. 

인덱스 생성 전략

생성된 인덱스를 가장 효율적으로 사용하는 방법은 데이터의 분포도는 최대한,

그리고 조건절의 호출 빈도는 자주 사용되는 컬럼을 인덱스로 생성하는 것이 좋습니다. 

인덱스는 특정 컬럼을 기준으로 생성하고 기준이 된 컬럼으로 정렬된 인덱스 테이블이 생성됩니다. 

이 기준 컬럼은 최대한 중복이 되지 않는 값이 좋은데, 가장 최선의 방법은 PK값을 인덱스로 거는 것입니다. 

중복된 값이 없는 인덱스 테이블이 가장 최적의 효율을 발생시킵니다.

 

인덱스 생성 팁 5가지 

1. 조건절에 자주 등장하는 컬럼
2. 항상 = 으로 비교되는 컬럼
3. 중복되는 데이터가 최소한인 컬럼(분포도가 좋은 컬럼)
4. ORDER BY 절에서 자주 사용되는 컬럼
5. JOIN 조건으로 자주 사용되는 컬럼