안녕하세요. SQLER의 코난 김대우입니다.
이번 강좌에서는, 12-2. 인덱스 생성과 관리 - 인덱스 종류를 진행 하겠습니다.
SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
이번에 진행할 강좌는 인덱스 생성과 관리 - 인덱스 종류입니다.
TL;DR
인덱스 생성과 관리 방법을 소개합니다. 인덱스 적용 패턴과 활용 시 고려해야 할 사항을 강좌로 소개하고, 데이터 조회 속도 향상과 생성 및 유지보수 비용을 이해하며, 적절한 인덱스 사용법 및 주의사항을 진행합니다.
SQL Server의 인덱스를 조금 더 깊이 있게 살펴봅니다. 만약, 인덱스의 탐색 패턴이나 구조가 어렵다면, 지금은 SQL 쿼리 구문만 실행하셔도 괜찮습니다. 인덱스는 DBMS의 성능을 보장하는 가장 효율적이고 가성비 좋은 기능입니다. 개발일을 하면서 데이터베이스는 피할 수 없을 정도로 중요하고, 인덱스 역시 마찬가지입니다. 서두르지 말고, 시간을 두면서 천천히 공부하세요. 궁금한 내용은 언제든지 SQLER 질문답변 게시판에 문의하시면 답변드리겠습니다.
SQL Server 인덱스 종류
SQL서버는 저장소 형식(Storage format)에 따라 디스크 기반 인덱스, Columnstore 인덱스, 메모리 최적화 인덱스로 분류합니다. 대부분의 시간을 디스크 기반 rowstore(행 저장) 인덱스 중 클러스터형과 비클러스터형 인덱스를 다루게 되며, 나머지 인덱스들은 특수한 조건에서 사용됩니다.
|
저장소 방식
|
인덱스 종류 |
| 디스크 기반 | 클러스터형(Clustered) 인덱스 |
| (Disk-based) rowstore | 비클러스터형(Non-clustered) 인덱스 |
| 고유한 (Unique) 인덱스 | |
| Filtered 인덱스 | |
| Columnstore | 클러스터형(Clustered) columnstore |
| 비클러스터형(Nonclustered) columnstore | |
| 메모리 최적화 | Hash |
| (Memory-optimized) | 메모리 최적화 비클러스터형 |
| (Memory-Optimized nonclustered) |
주로 클러스터형과 비클러스터형 인덱스를 이 강좌에서 진행합니다.
인덱스를 만들기 전 고려사항
이전 강좌에서도 잠시 말씀드렸습니다. 인덱스는 “만들어야”합니다. 바꿔 말하면,
- 인덱스는 만드는 비용이 있다.
인덱스는 만들어지고 참조되니 공간이 필요합니다. 바꿔 말하면,
- 인덱스는 저장소가 필요하며 저장소 비용이 있다.
인덱스를 저장소에 만들면 끝일까요? 아닙니다. 만들었으면?
- 인덱스는 저장소에 생성하고 사용하면서 지속적으로 비용을 들여 유지보수해야 한다.
인덱스를 만들면 당연히 데이터를 조회 속도를 높일 수 있지만, 지속적으로 테이블에 데이터가 삽입 / 수정 / 삭제된다고 생각해 보세요. 앞에서 본 인덱스의 B트리 구조를 변경된 테이블 데이터에 맞춰 지속적으로 인덱스를 보완하면서 유지보수 해야, 빠른 조회 속도를 유지합니다. 바꿔 말하면,
- 데이터가 변경되면, 인덱스 보완 비용이 추가된다.
인덱스를 만들기만 하면 빠른가요? 이전 강좌에서 말씀드렸지만, 인덱스를 만든다고 항상 빨라지지는 않습니다. 잘못 인덱스를 생성하거나, 데이터 변경으로 파편화가 발생해 인덱스 통계 정보가 업데이트되지 않으면 잘못된 인덱스를 타게 되어 검색이 더 느릴 수도 있습니다. 바꿔 말하면,
- 인덱스는 항상 빠르지 않다.
인덱스를 적용하면 좋은 케이스
그렇다면, 어떤 데이터에 인덱스를 사용하면 일반적으로 유용한가요? 아래와 같은 패턴에 사용하면 좋은 효과가 있습니다
- WHERE 절에서 참조되는 컬럼
- 참조키가 설정되어 있는 컬럼
- 참조키는 아니지만 JOIN에 사용되는 컬럼
- 범위 검색(일정한 선택도 이하)이 일어나는 컬럼
- ORDER BY로 정렬되는 컬럼
- GROUP BY로 그룹핑되는 컬럼
- OFFSET - FETCH(TOP 구문과 유사)에서 사용되는 컬럼
등에서 사용하면 좋습니다. 인덱스를 생성할 경우 고려사항은 위의 일반적인 쿼리 상황 외에 다양합니다. 공식 가이드 내용도 참고하세요.
SQL Server 및 Azure SQL 인덱스 아키텍처와 디자인 가이드
인덱스를 적용하면 좋지 않은 케이스
그렇다면 어떤 경우에 인덱스를 주의해 사용하거나, 사용하지 말아야 할까요? 위의 인덱스를 적용하면 좋은 케이스와 비교하면서 확인하세요.
- WHERE절에서 참조되지 않는 컬럼에는 효과 없음
- WHERE절에서 변환(함수등이 사용되는)되는 컬럼과 비교 시
- 선택도(찾을 데이터 / 전체 데이터)가 클 경우 효과 적음
선택도는 예를 들어, 성별 컬럼과 같은 남 / 여 비율적으로 대략 50 : 50의 구성비가 있는 컬럼이라면 인덱스의 효과가 떨어집니다. 선택도는 잠시 후 상세히 논의합니다.
인덱스 생성 구문
인덱스 생성 구문을 확인하고 SQL 구문으로 살펴보겠습니다.
| CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON <object> ( column [ ASC | DESC ] [ ,...n ] ) [ INCLUDE ( column_name [ ,...n ] ) ] [ WHERE <filter_predicate> ] [ WITH ( <relational_index_option> [ ,...n ] ) ] [ ON { partition_scheme_name ( column_name ) | filegroup_name | default } ] [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ] [ ; ] |
주요한 인덱스 생성 구문을 말씀드리면,
- UNIQUE를 지정해 동일한 키 값을 가질 수 없는 고유 인덱스를 생성 가능합니다.
- CLUSTERED | NONCLUSTERED는 중요한 색인 종류로 이어지는 강좌에서 상세하게 진행합니다.
- 색인은 테이블과 뷰에 생성할 수 있습니다.
- 인덱스 컬럼 정렬을 오름차순(ASC) 또는 내림차순(DESC)으로 정렬해 생성 가능합니다.
- PAD_INDEX는 FILLFACTOR와 주로 함께 사용되며 인덱스 B트리의 중간 레벨을 비워 데이터 삽입 등에 대비합니다. FILLFACTOR는 리프 레벨을 적절히 비워 역시 삽입 등에 대비하는 채우기 비율입니다.
- DROP_EXISTING 은 이미 존재하는 인덱스가 있으면 제거하고 재생성합니다.
- STATISTICS_NORECOMPUTE는 인덱스를 사용할지 안 할지 쿼리 최적화기가 결정하는데, 이 근거는 통계 데이터라는 정보로 판단하게 됩니다. 이 통계 데이터는 기본적으로 자동 업데이트 되는데 이 통계데이터를 해당 인덱스에서 자동 업데이트 할지 여부를 설정하는 옵션입니다.
- SORT_IN_TEMPDB는 tempdb에 임시 정렬 결과를 저장 여부를 설정합니다. 예를 들어, 데이터와 인덱스가 같은 물리적인 디스크에 있고 데이터가 한 1,000만 건 정도 된다면 인덱스 생성에 대단히 많은 시간이 소요될 수 있습니다. 이때 tempdb에서 인덱스 생성 시 필요한 정렬작업을 수행하고 사용자 데이터베이스의 물리적인 디스크와 tempdb의 물리적인 디스크가 다르다면 인덱스 생성 시 IO 부하를 줄일 순 있지만, tempdb에 불필요 공간이 한시적으로 생기니 주의하셔야 합니다.(테이블 데이터가 크면 인덱스 생성 시 임시 공간도 많이 사용해 tempdb가 늘어나면서 disk full 오류가 발생할 수도 있습니다)
- ON filegroup은 데이터베이스 특정 파일그룹에 인덱스를 생성하고 유지하는 설정입니다. 기억하시죠? 인덱스 역시 데이터라고 말씀드렸습니다. SQLER의 데이터베이스 강좌에서 filegroup을 물리적인 디스크에 분산시켜 생성하면 속도를 높일 수 있다고 말씀드린 것처럼 인덱스 역시 적절한 filegroup에 위치시켜 최적의 속도를 낼 수 있게 할 수 있지요.
다음 강좌에서 인덱스를 생성하겠습니다.
SQL 강좌 책 구매
강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다.
책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022
