필터링된 인덱스 만들기

아티클
08/11/2023

적용 대상:SQL Server Azure SQL DatabaseAzure SQL Managed Instance

이 문서에서는 SSMS(SQL Server Management Studio) 또는 Transact-SQL을 사용하여 필터링된 인덱스를 만드는 방법을 설명합니다. 필터링된 인덱스는 특히 데이터의 잘 정의된 하위 집합에서 선택하는 쿼리를 처리하는 데 적합한 최적화된 디스크 기반 rowstore 비클러스터형 인덱스입니다. 필터 조건자를 사용하여 테이블의 행 부분을 인덱싱합니다. 잘 디자인된 필터링된 인덱스는 전체 테이블 인덱스에 비해 쿼리 성능을 개선하고 인덱스 유지 관리 및 스토리지 비용을 줄일 수 있습니다.

필터링된 인덱스는 전체 테이블 인덱스에 비해 다음과 같은 이점이 있습니다.

향상된 쿼리 성능 및 계획 품질

잘 디자인된 필터링된 인덱스는 전체 테이블 비클러스터형 인덱스보다 작고 통계를 필터링했기 때문에 쿼리 성능 및 실행 계획 품질을 향상시킵니다. 필터링된 통계는 필터링된 인덱스의 행만 포함하므로 전체 테이블 통계보다 더 정확합니다.
인덱스 유지 관리 비용 절감

인덱스의 DML(데이터 조작 언어) 문이 데이터에 영향을 줄 때에만 인덱스가 유지 관리됩니다. 필터링된 인덱스는 크기가 더 작고 인덱스의 데이터가 변경될 때에만 유지 관리되기 때문에 전체 테이블 비클러스터형 인덱스에 비해 인덱스 유지 관리 비용이 줄어듭니다. 특히 자주 변경되지 않는 데이터가 포함된 경우 필터링된 인덱스가 많을 수 있습니다. 마찬가지로 필터링된 인덱스에 자주 수정되는 데이터만 포함된 경우 인덱스의 크기가 작을수록 통계 업데이트 비용이 줄어듭니다.
줄어든 인덱스 스토리지 비용

필터링된 인덱스를 만들면 전체 테이블 인덱스가 필요하지 않은 경우 비클러스터형 인덱스에 대한 디스크 스토리지를 줄일 수 있습니다. 스토리지 요구 사항을 크게 늘리지 않고 전체 테이블 비클러스터형 인덱스를 필터링된 여러 인덱스로 바꿀 수 있습니다.

디자인 고려 사항

열에 쿼리에 대한 몇 가지 관련 값만 있는 경우 값의 하위 집합에 필터링된 인덱스를 만들 수 있습니다. 결과 인덱스는 동일한 키 열에 정의된 전체 테이블 비클러스터형 인덱스보다 작고 유지 관리 비용이 적게 듭니다.

예를 들어 다음 데이터 시나리오에서 필터링된 인덱스를 고려합니다. 각 경우에 쿼리 절은 WHERE 필터링된 인덱스의 절의 WHERE 하위 집합이어야 필터링된 인덱스의 이점을 얻을 수 있습니다.

열의 값이 대부분 NULL이고 쿼리가 NULL이 아닌 값에서만 선택하는 경우 NULL이 아닌 데이터 행에 대해 필터링된 인덱스를 만들 수 있습니다.
테이블의 행이 되풀이 워크플로 또는 큐 프로세스에 의해 처리된 것으로 표시되는 경우 시간이 지남에 따라 테이블의 대부분의 행은 처리된 것으로 표시됩니다. 아직 처리되지 않은 행의 필터링된 인덱스는 아직 처리되지 않은 행을 찾는 되풀이 쿼리에 도움이 됩니다.
테이블에 다른 유형의 데이터 행이 있는 경우 하나 이상의 데이터 범주에 대한 필터링된 인덱스를 만들 수 있습니다. 이렇게 하면 쿼리의 포커스를 테이블의 특정 영역으로 좁혀 이러한 데이터 행에 대한 쿼리 성능을 향상시킬 수 있습니다. 결과 인덱스는 전체 테이블 비클러스터형 인덱스에 비해 크기가 더 작고 유지 관리하는 비용이 더 적게 듭니다.

제한 사항

보기에서 필터링된 인덱스 만들 수 없습니다. 그러나 쿼리 최적화 프로그램에는 뷰에서 참조되는 테이블에 정의되는 필터링된 인덱스에 성능상 이점이 있습니다. 쿼리 최적화 프로그램은 쿼리 결과가 올바른지 보기에서 선택하는 쿼리에 대해 필터링된 인덱스를 고려합니다.
필터 식에서 액세스하는 열이 CLR 데이터 형식인 경우 테이블에 필터링된 인덱스는 만들 수 없습니다.
필터링된 인덱스는 인덱싱된 뷰보다 다음과 같은 이점이 있습니다.
- 인덱스 유지 관리 비용 절감 예를 들어 쿼리 프로세서는 인덱싱된 뷰보다 더 적은 CPU 리소스를 사용하여 필터링된 인덱스를 업데이트합니다.
- 향상된 계획 품질. 예를 들어 쿼리 컴파일 중에 쿼리 최적화 프로그램은 동일한 인덱싱된 뷰보다 더 많은 상황에서 필터링된 인덱스를 사용하는 것을 고려합니다.
- 온라인 인덱스를 다시 작성합니다. 쿼리에 사용할 수 있는 동안 필터링된 인덱스를 다시 작성할 수 있습니다. 인덱싱된 뷰에는 온라인 인덱스 다시 작성이 지원되지 않습니다. 자세한 내용은 ALTER INDEX(Transact-SQL) 옵션을 참조 REBUILD 하세요.
- 고유하지 않은 인덱스입니다. 필터링된 인덱스는 고유하지 않아도 되지만 인덱싱된 뷰는 반드시 고유해야 합니다.
필터링된 인덱스는 한 테이블에 정의되며 간단한 비교 연산자만 지원합니다. 여러 테이블을 참조하거나 복잡한 논리를 사용하는 필터 식이 필요할 경우 뷰를 만들어야 합니다. 필터링된 인덱스는 연산자를 지원하지 LIKE 않습니다.
필터링된 인덱스 식이 쿼리 조건자와 동일하고 쿼리가 쿼리 결과와 함께 필터링된 인덱스 식의 열을 반환하지 않는 경우 필터링된 인덱스 식의 열은 필터링된 인덱스 정의에 키 또는 포함된 열이 될 필요가 없습니다.
쿼리 조건자가 필터링된 인덱스 식과 동일하지 않은 비교에서 열을 사용하는 경우 필터링된 인덱스 식의 열은 필터링된 인덱스 정의에 키 또는 포함된 열이어야 합니다.
필터링된 인덱스 식의 열은 열이 쿼리 결과 집합에 있는 경우 필터링된 인덱스 정의의 키 또는 포함된 열이어야 합니다.
테이블의 클러스터형 인덱스 키는 필터링된 인덱스 정의에 키 또는 포함된 열일 필요가 없습니다. 클러스터형 인덱스 키는 필터링된 인덱스를 포함하여 모든 비클러스터형 인덱스에 자동으로 포함됩니다. 인덱스 아키텍처 및 디자인 가이드에서 자세히 알아보세요.
필터링된 인덱스의 필터링된 인덱스 식에 지정된 비교 연산자가 암시적 또는 명시적 데이터 변환을 초래하는 경우 비교 연산자의 왼쪽에서 변환이 발생하면 오류가 발생합니다. 해결 방법은 비교 연산자의 오른쪽에 데이터 변환 연산자(CAST 또는 CONVERT)를 사용하여 필터링된 인덱스 식을 작성하는 것입니다.
CREATE INDEX(Transact-SQL) 구문에서 필터링된 인덱스 만들기에 필요한 SET 옵션을 검토합니다.
필터는 기본 키 또는 고유 제약 조건에 적용할 수 없지만 속성이 있는 인덱스에 UNIQUE 적용할 수 있습니다.
계산 열에는 필터링된 인덱스만 만들 수 없습니다.

사용 권한

테이블이나 뷰에 대한 ALTER 권한이 필요합니다. 사용자는 sysadmin 고정 서버 역할 또는 db_ddladmin 및 db_owner 고정 데이터베이스 역할의 멤버여야 합니다. 필터링된 인덱스 식을 수정하려면 CREATE INDEX WITH DROP_EXISTING을 사용합니다.

SSMS를 사용하여 필터링된 인덱스 만들기

개체 탐색기에서 더하기 기호를 선택하여 필터링된 인덱스를 만들 테이블이 포함된 데이터베이스를 확장합니다.
더하기 기호를 선택하여 테이블 폴더를 확장합니다.
더하기 기호를 선택하여 필터링된 인덱스를 만들 테이블을 확장합니다.
인덱스 폴더를 마우스 오른쪽 단추로 클릭하고 새 인덱스를 가리킨 다음 비클러스터형 인덱스를 선택합니다.
새 인덱스 대화 상자의 일반 페이지에서 인덱스 이름 상자에 새 인덱스의 이름을 입력합니다.
인덱스 키 열 아래에서 추가...를 선택합니다.
table_name에서 열 선택 대화 상자에서 인덱스에 추가할 테이블 열의 확인란을 선택합니다.
확인을 선택합니다.
필터 페이지의 필터 식에서 필터링된 인덱스 만들기에 사용할 SQL 식을 입력합니다.
확인을 선택합니다.

Transact-SQL을 사용하여 필터링된 인덱스 만들기

이 문서에는 AdventureWorks2022 Microsoft SQL Server 샘플 및 커뮤니티 프로젝트 홈페이지에서 다운로드할 수 있는 샘플 데이터베이스가 필요합니다.

개체 탐색기에서 데이터베이스 엔진인스턴스에 연결합니다.
표준 도구 모음에서 새 쿼리를 선택합니다.
다음 예제를 복사하여 쿼리 창에 붙여넣고 실행을 선택합니다.

USE AdventureWorks2022;
GO

DROP INDEX IF EXISTS FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

필터링된 인덱 FIBillOfMaterialsWithEndDate 스는 다음 쿼리에 유효합니다. 이 필터링된 인덱스가 쿼리 최적화 프로그램에서 사용되는지 확인하기 위해 쿼리 실행 계획을 표시할 수 있습니다.

USE AdventureWorks2022;
GO

SELECT ProductAssemblyID, ComponentID, StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
    AND ComponentID = 5
    AND StartDate > '01/01/2008' ;
GO

다음 단계

인덱스 및 관련 개념을 만드는 방법에 대한 자세한 내용은 다음 문서를 참조하세요.