SQLP/SQL 전문가 가이드

DB 튜닝 - 인덱스

개발자 키우기 2023. 12. 29. 16:15

1. 적절한 인덱스 스캔 활용

----------------------------------------------------------------
Index Full Scan

대용량 테이블이라 Table Full Scan에 비용이 많이 들며
조건절로 필터링되어 일부에 대해서만 테이블 액세스가 발생될 경우
sort 연산이나 min/max 연산 생략 가능
----------------------------------------------------------------
Index Range Scan

조건절로 인덱스 후위컬럼만 필요할 경우 선두컬럼의 중복값이 적다면 In-List를 사용
sort 연산이나 min/max 연산 생략 가능
----------------------------------------------------------------
Index Skip Scan

조건절에 빠진 인덱스 선두 컬럼의 중복값 개수가 적고 후행 컬럼의 중복값 개수가 많을 경우
Index Range Scan으로 변경 고려 대상
----------------------------------------------------------------
Index Fast Full Scan

인덱스로 구성된 컬럼 값만 필요하며 정렬이 필요 없을 경우
----------------------------------------------------------------
Index Range Scan Descending

sort 연산이나 min/max 연산 생략 가능
----------------------------------------------------------------

 

2. 인덱스 재생성

  • 인덱스 부하에 의한 경합이 현저히 높을 때
  • 자주 사용되는 인덱스 스캔 효율을 높이고자 할 때(NL 조인에서 반복 액세스되는 인덱스 높이가 증가했을 때)
  • 대량의 delete 작업을 수행한 이후 다시 레코드가 입력되기까지 오랜 시간이 소요될 때
  • 총 레코드 수가 일정한데 인덱스가 계속 커질 때

3. 비트맵 인덱스 사용

  • OLAP 환경에서 인덱스로 정할 칼럼이 중복값 개수가 적을 때 사용
  • B*Tree 인덱스보다 훨씬 적은 용량 사용

4. 함수기반 인덱스 사용

  • 대소문자를 구분해 입력 받은 데이터를 대소문자 구분 없이 조회할 때
  • 조걸 절에 항상 함수를 사용해서 인덱스 칼럼을 사용해야 할 때

5. 리버스 키 인덱스 사용

  • Right Growing을 방지하고 = 조건으로만 검색할 때

6. 클러스터 인덱스 사용

  • 인덱스 키 순서가 쿼리의 접근 패턴과 일치할 때
  • 매번 조인해서 사용할 때
  • = 조건이 아니라 범위 조건으로 조회할 때
  • 데이터 변경이 적을 때

7. IOT(오라클) / 클러스터형 인덱스 사용(SQL Server)

  • 넓은 범위를 주로 검색하는 테이블
  • 크기가 작고 NL 조인으로 반복 룩업 하는 테이블
  • 칼럼 수가 적고 로우 수가 많은 테이블
  • 데이터 입력이 적고 주로 조회를 하는 테이블(데이터 입력과 조회 패턴이 서로 다른 테이블)

8. 인덱스 튜닝

  • 인덱스 선투 칼럼을 조건절에서 가공하거나 부정형 비교, is not null을 사용하면 Index Full Scan을 수행함으로 주의하자. ( SQL Server에서만 is null 검색 시에도 인덱스를 사용할 수 있다 )
  • 인덱스 칼럼을 가공하면 Index Range Scan이 불가능해짐으로 주의하자.
  • 묵시적 형변환으로도 Index Range Scan이 불가능해짐으로 주의하자.
  • 클러스터링 팩터를 높이기 위해 인덱스 기준으로 테이블을 재성성 할 수도 있다. 자주 사용되는 인덱스를 기준으로 삼아야 다른 인덱스의 클러스터링 팩터가 나빠지는걸 최대한 방지할 수 있으며 주기적으로 수행하면 데이터베이스 관리비용이나 가용성에 영향이 끼치기 때문에 확실할 때만 사용해야 한다.
  • SQL Server는 Include Index를 사용할 수 있는데 인덱스 키 외에 미리 지정한 칼럼을 리프 레벨에 함께 저장하는 기능이다. 인덱스 + 추가 인덱스의 결과만 필요할 때 사용하면 테이블 랜덤 액세스 횟수를 줄이는데 효과적이다.
  •  대량 범위 검색 조건으로 자주 사용되는 칼럼 기준으로 테이블을 파티셔닝 하면 Full Table Scan을 하더라도 일분 파티션만 읽기 때문에 효율적이다.
  • 부분범위처리를 사용하면 인덱스 효용성을 100%까지 올릴 수 있다.
  • 인덱스 칼럼 추가.
  • 테이블 랜덤 액세스가 발생하지 않도록 필요한 모든 칼럼을 인덱스에 포함시키는 것을 Covered Index라고 하며 I/O를 줄일 수 있기 때문에 효율적이지만 많은 칼럼을 추가했을 경우 인덱스 크기도 같이 커지기 때문에 주의해야 한다.
  • 해시 클러스터 테이블은 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조로 해시 함수가 인덱스 역할을 대신하며 해싱 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로 변환해 준다. 별도의 인덱스 구조를 생성하지 않는 것이 장점이며 해시 함수를 사용하기 때문에 항상 = 조건으로 검색해야 한다.
  • 배치 I/O를 사용해서 읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄일 수 있는데 인덱스를 이용해서 출력하는 데이터 정렬 순서가 매번 다르기 때문에 소트연산을 추가적으로 하는 게 효율적인지 배치 I/O를 사용하지 않고 소트연산을 하지 않은지 판단해야 한다.
  • 인덱스 선행 칼럼은 = 조건이고 후위 칼럼이 = 또는 범위 조건이면 버려지는 레코드가 없기 때문에 효율적이다.

 

참고 서적 - SQL 전문가 가이드