필터링된 인덱스 디자인 지침

열에 적은 수의 쿼리 관련 값만 있는 경우 값의 하위 집합에 필터링된 인덱스를 만들 수 있습니다. 예를 들어 열에 있는 값이 대부분 NULL이고 쿼리는 NULL이 아닌 값에서만 선택하는 경우 NULL이 아닌 데이터 행에 대한 필터링된 인덱스를 만들 수 있습니다. 결과 인덱스는 같은 키 열에서 정의된 전체 테이블 비클러스터형 인덱스에 비해 크기가 더 작고 유지 관리하는 비용이 더 적게 듭니다.

예를 들어 AdventureWorks2008R2 데이터베이스에는 2,679개의 행이 있는 Production.BillOfMaterials 테이블이 있습니다. NULL이 아닌 값이 들어 있는 EndDate 열에는 199개의 행만 있고 나머지 2,480개의 행에는 NULL이 들어 있습니다. 다음 필터링된 인덱스는 이 인덱스에서 정의된 열을 반환하고 EndDate에 필요한 NULL이 아닌 값이 있는 행만 선택하는 쿼리를 처리합니다.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

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

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

필터링된 인덱스를 만드는 방법 및 필터링된 인덱스 조건자 식을 정의하는 방법은 CREATE INDEX(Transact-SQL)를 참조하십시오.

테이블에 서로 다른 데이터 행이 있는 경우 하나 이상의 데이터 범주에 대한 필터링된 인덱스를 만들 수 있습니다.

예를 들어 Production.Product 테이블에 나열된 제품은 각각 ProductSubcategoryID에 지정된 다음 제품 범주 Bikes, Components, Clothing 또는 Accessories 같은 제품 범주와 연결됩니다. Production.Product 테이블에 있는 해당 열 값이 서로 유사하지 않기 때문에 이러한 범주는 서로 다릅니다. 예를 들어 Color, ReorderPoint, ListPrice, Weight, Class 및 Style에는 각 제품 범주에 대한 고유한 특징이 있습니다. 27개에서 36개의 하위 범주가 있는 Accessories에 쿼리가 자주 수행된다고 가정해 봅니다. Accessories 하위 범주에 필터링된 인덱스를 만들어 Accessories에 대한 쿼리의 성능을 향상시킬 수 있습니다.

다음 예에서는 Production.Product 테이블의 Accessories 하위 범주에 있는 모든 제품에 필터링된 인덱스를 만듭니다.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIProductAccessories'
    AND object_id = OBJECT_ID ('Production.Product'))
DROP INDEX FIProductAccessories
    ON Production.Product;
GO
CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice) 
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

쿼리 결과는 인덱스에 포함되고 기본 테이블 조회는 쿼리 계획에 포함되지 않으므로

필터링된 인덱스 FIProductAccessories는 다음 쿼리에 사용할 수 있습니다. 예를 들어 쿼리 조건자 식 ProductSubcategoryID = 33은 필터링된 인덱스 조건자 ProductSubcategoryID >= 27 및 ProductSubcategoryID <= 36의 하위 집합이고 쿼리 조건자의 ProductSubcategoryID 및 ListPrice 열은 모두 인덱스의 키 열이며 이름은 인덱스의 리프 수준에 포괄 열로 저장됩니다.

SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;
GO

뷰는 쿼리의 정의를 저장하는 가상 테이블로 필터링된 인덱스보다 그 목적과 기능이 광범위합니다. 뷰에 대한 자세한 내용은 뷰 이해 및 뷰 사용 시나리오를 참조하십시오. 다음 테이블에서는 뷰에서 사용할 수 있는 일부 기능과 필터링된 인덱스의 해당 기능을 비교합니다.

*조건자에서의 간단한 비교 논리에 대해서는 CREATE INDEX의 WHERE 절 구문을 참조하십시오.

**조건자에서의 복잡한 논리에 대해서는 SELECT의 WHERE 절 구문을 참조하십시오.

뷰에서 필터링된 인덱스를 만들 수 없습니다. 그러나 쿼리 최적화 프로그램에는 뷰에서 참조되는 테이블에 정의되는 필터링된 인덱스에 성능상 이점이 있습니다. 쿼리 최적화 프로그램에서는 쿼리 결과가 수정될 경우 뷰에서 선택하는 쿼리에 대한 필터링된 인덱스를 검토합니다. 다음 예에서는 시작 날짜 2000년 4월 1일 이후인 뷰와 시작 날짜가 2000년 8월 1일 이후인 필터링된 인덱스를 만듭니다.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('ViewOnBillOfMaterials') IS NOT NULL
DROP VIEW ViewOnBillOfMaterials;
GO
CREATE VIEW ViewOnBillOfMaterials AS 
SELECT ComponentID, StartDate, EndDate, StartDate + 2 AS ShipDate
FROM Production.BillOfMaterials
WHERE StartDate > '20000401';
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsByStartDate'
    AND object_ID = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsByStartDate 
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsByStartDate
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE StartDate > '20000801';
GO

다음 예에서 쿼리는 필터링된 인덱스와 필터링된 뷰에 모두 포함되어 있는 2004년 9월 1일 이후의 시작 날짜를 선택합니다. 쿼리 최적화 프로그램에서는 필터링된 인덱스 FIBillOfMaterialsByStartDate에는 쿼리에 대한 올바른 결과가 들어 있기 때문에 이 필터링된 인덱스를 검토합니다.

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040901';
GO

다음 예에서 쿼리는 뷰에는 포함되지만 필터링된 인덱스에는 포함되지 않는 2004년 6월 1일 이후의 시작 일자를 선택합니다. 쿼리 최적화 프로그램에서는 필터링된 인덱스 FIBillOfMaterialsByStartDate를 검토하지 않습니다. 그 이유는 쿼리가 뷰에서 선택할 때의 올바른 결과와 비교하여 필터링된 인덱스를 사용하면 쿼리는 다른 결과를 반환할 수 있기 때문입니다.

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040601';
GO

필터링된 인덱스에는 인덱싱된 뷰에 비해 다음과 같은 이점이 있습니다.

• 줄어든 인덱스 유지 관리 비용. 예를 들어 쿼리 프로세서에서 필터링된 인덱스를 업데이트하는 데 인덱싱된 뷰보다 적은 CPU 리소스를 사용합니다.

• 향상된 계획 품질. 예를 들어 쿼리 컴파일 중에 쿼리 최적화 프로그램이 인덱싱된 뷰보다 더 많은 경우에 필터링된 인덱스의 사용을 고려합니다.

• 온라인 인덱스 다시 작성. 필터링된 인덱스를 쿼리에 사용할 수 있는 동시에 다시 작성할 수 있습니다. 인덱싱된 뷰에 대해서는 온라인 인덱스 다시 작성이 지원되지 않습니다. 자세한 내용은 ALTER INDEX(Transact-SQL)의 REBUILD 옵션을 참조하십시오.

• 고유하지 않은 인덱스. 필터링된 인덱스는 고유하지 않아도 되지만 인덱싱된 뷰는 반드시 고유해야 합니다.

위와 같은 이유 때문에 가능하면 인덱싱된 뷰 대신 필터링된 인덱스를 사용하는 것이 좋습니다. 인덱싱된 뷰 대신 필터링된 인덱스를 사용하려면 뷰가 한 개의 테이블만 참조하고, 쿼리는 계산 열을 반환하지 않으며, 뷰 조건자가 간단한 비교 논리를 사용하는 조건이 충족되어야 합니다. 예를 들어 다음 조건자 식에는 LIKE 연산자가 들어 있기 때문에 필터링된 인덱스가 아니라 뷰 정의에서 다음 조건자 식을 사용할 수 있습니다.

WHERE StartDate > '20040701' AND ModifiedDate LIKE 'E%'

적은 수의 키 또는 포괄 열을 필터링된 인덱스 정의에 포함하고 쿼리 최적화 프로그램에 필요한 열만 통합하여 쿼리 실행 계획에 대한 필터링된 인덱스를 선택하는 것이 가장 좋습니다. 쿼리 최적화 프로그램에서는 필터링된 인덱스의 쿼리 처리 여부에 상관없이 쿼리에 대한 필터링된 인덱스를 선택할 수 있습니다. 그러나 쿼리 최적화 프로그램에서는 필터링된 인덱스가 쿼리를 처리할 경우 필터링된 인덱스를 선택할 가능성이 커집니다. 쿼리 처리에 대한 자세한 내용은 포괄 열을 사용하여 인덱스 만들기를 참조하십시오.

경우에 따라 필터링된 인덱스는 필터링된 인덱스 식에 필터링된 인덱스 정의의 포괄 열 또는 키로 열을 포함하지 않고 쿼리를 처리합니다. 다음 지침은 필터링된 인덱스 식의 열이 필터링된 인덱스 정의의 포괄 열 또는 키여야 하는 경우에 대해 설명합니다. 이 예에서는 앞에서 만든 필터링된 인덱스 FIBillOfMaterialsWithEndDate를 참조합니다.

필터링된 인덱스 식이 쿼리 조건자와 같고 쿼리가 쿼리 결과로 필터링된 인덱스 식의 열을 반환하지 않는다면 필터링된 인덱스 식의 열이 필터링된 인덱스 정의의 포괄 열 또는 키여야 할 필요는 없습니다. 예를 들어 다음 쿼리 조건자가 필터 식과 같고 EndDate가 쿼리 결과로 반환되지 않기 때문에 FIBillOfMaterialsWithEndDate는 이 쿼리를 처리합니다. FIBillOfMaterialsWithEndDate는 필터링된 인덱스 정의의 포괄 열 또는 키로 EndDate가 필요하지 않습니다.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

쿼리 조건자가 필터링된 인덱스 식과 다른 비교에 필터링된 인덱스 식의 열을 사용하면 해당 열은 필터링된 인덱스 정의의 포괄 열 또는 키여야 합니다. 예를 들어 다음 쿼리는 필터링된 인덱스에서 행의 하위 집합을 선택하기 때문에 FIBillOfMaterialsWithEndDate는 이 쿼리에 적합합니다. 그러나 EndDate가 필터링된 인덱스 식과 다른 EndDate > '20040101' 비교에 사용되기 때문에 다음 쿼리를 처리하지는 못합니다. 쿼리 프로세서는 EndDate 값을 조회하지 않고 이 쿼리를 실행할 수 없습니다. 따라서 EndDate는 필터링된 인덱스 정의의 포괄 열 또는 키여야 합니다.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20040101';
GO

필터링된 인덱스 식의 열이 쿼리 결과 집합에 있으면 해당 열은 필터링된 인덱스 정의의 포괄 열 또는 키여야 합니다. 예를 들어 다음 쿼리가 쿼리 결과에 EndDate 열을 반환하기 때문에 FIBillOfMaterialsWithEndDate는 이 쿼리를 처리하지 못합니다. 따라서 EndDate는 필터링된 인덱스 정의의 포괄 열 또는 키여야 합니다.

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

테이블의 기본 키가 필터링된 인덱스 정의의 포괄 열 또는 키여야 할 필요는 없습니다. 기본 키는 필터링된 인덱스를 비롯하여 모든 비클러스터형 인덱스에 자동으로 포함됩니다.

필터링된 인덱스의 필터링된 인덱스 식에 지정된 비교 연산자로 인해 암시적 또는 명시적 데이터 변환이 발생할 경우 비교 연산자의 왼쪽에서 변환이 일어나면 오류가 발생합니다. 이에 대한 해결 방법은 비교 연산자의 오른쪽에 데이터 변환 연산자(CAST 또는 CONVERT)를 사용하여 필터링된 인덱스 식을 작성하는 것입니다.

다음 예에서는 여러 가지 데이터 형식이 있는 테이블을 만듭니다.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.TestTable') IS NOT NULL
DROP TABLE dbo.TestTable;
GO
CREATE TABLE TestTable (a int, b varbinary(4));
GO

다음 필터링된 인덱스 정의에서 상수 1과 비교하기 위해 b 열이 정수 데이터 형식으로 암시적으로 변환됩니다. 이로 인해 오류 메시지 10611이 생성되며 그 이유는 필터링된 조건자에 있는 연산자의 왼쪽에서 변환이 발생하기 때문입니다.

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name from sys.indexes 
    WHERE name = N'TestTabIndex'
    AND object_id = OBJECT_ID (N'dbo.TestTable'))
DROP INDEX TestTabIndex on dbo.TestTable
GO
CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO

해결 방법은 다음 예에서와 같이 b 열과 동일한 유형이 되도록 오른쪽에 있는 상수를 변환하는 것입니다.

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO

데이터 변환을 비교 연산자의 왼쪽에서 오른쪽으로 이동하면 변환 방법이 변경될 수 있습니다. 위의 예에서 CONVERT 연산자가 오른쪽에 추가될 때 정수 비교에서 varbinary 비교로 비교가 변경되었습니다.

sys.sql_expression_dependencies 카탈로그 뷰에서는 필터링된 인덱스 식의 각 열을 종속성 참조로 추적합니다. 필터링된 인덱스 식에 정의된 테이블 열의 정의에 대해 삭제, 이름 변경 또는 변경을 수행할 수 없습니다.

필터링된 인덱스는 쿼리가 SELECT 문에서 참조하는 데이터의 잘 정의된 하위 집합이 열에 포함되는 경우 유용합니다. 예는 다음과 같습니다.

• 몇 개의 NULL이 아닌 값만 포함하는 스파스 열

• 포함하는 데이터의 범주가 서로 다른 열

• 달러 금액, 시간 및 날짜와 같은 값의 범위를 포함하는 열

• 열 값에 대해 간단한 비교 논리로 정의되는 테이블 파티션

필터링된 인덱스에 대해 줄어든 유지 관리 비용은 인덱스의 행 수가 전체 테이블 인덱스에 비해 적을 때 가장 분명하게 드러납니다. 필터링된 인덱스에 테이블의 열이 대부분 포함되어 있을 경우 전체 테이블 인덱스보다 유지 관리 비용이 더 들 수 있습니다. 이런 경우 필터링된 인덱스 대신 전체 테이블 인덱스를 사용해야 합니다.

필터링된 인덱스는 하나의 테이블에서 정의되고 간단한 비교 논리만 지원합니다. 여러 테이블을 참조하거나 복잡한 논리를 사용하는 필터 식이 필요할 경우 뷰를 만들어야 합니다.

일반적으로 데이터베이스 엔진 및 도구에서는 필터링된 인덱스를 특별한 유형의 비클러스터형 인덱스로 간주하여 필터링된 인덱스에 대해 비클러스터형 전체 테이블 인덱스에 제공하는 것과 동일한 지원을 제공합니다. 다음 목록에서는 필터링된 인덱스에 대해 완전히 지원하거나 지원하지 않거나 제한된 지원을 하는 도구 및 기능에 대한 참고 사항을 설명합니다.

• ALTER INDEX는 필터링된 인덱스를 지원합니다. 필터링된 인덱스를 수정하려면 CREATE INDEX WITH DROP_EXISTING을 사용합니다.

• 누락된 인덱스 기능은 필터링된 인덱스를 제안하지 않습니다.

• 데이터베이스 엔진 튜닝 관리자는 인덱스 튜닝 조언을 권장할 때 필터링된 인덱스를 고려하며 is not null로 필터링된 인덱스를 권장할 수 있습니다.

• 온라인 인덱스 작업은 필터링된 인덱스를 지원합니다.

• 테이블 힌트는 필터링된 인덱스를 지원하지만 필터링되지 않은 인덱스에는 적용되지 않는 몇 가지 제한 사항이 있습니다. 이러한 제한 사항은 다음 섹션에서 설명합니다.

경우에 따라 매개 변수가 있는 쿼리에는 컴파일 시 쿼리 최적화 프로그램에서 필터링된 인덱스를 선택하기에 충분한 정보가 들어 있지 않습니다. 쿼리를 다시 작성하여 누락된 정보를 제공할 수도 있습니다. 다음 예에서 컴파일 시 @p 및 @q의 매개 변수 값이 알 수 없기 때문에 쿼리 최적화 프로그램이 SELECT 문에 대해 필터링된 인덱스 FIBillOfMaterialsWithComponentID를 검토하지 않습니다. 다음 쿼리 예에서는 SHOWPLAN_XML을 ON으로 설정하여 실행하므로 SHOWPLAN_XML 출력에서 매개 변수가 있는 쿼리에 대해 일치하지 않는 필터링된 인덱스를 볼 수 있습니다.

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE ComponentID IN (533, 324, 753);
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID from Production.BillOfMaterials 
WHERE ComponentID = @p OR ComponentID = @q;
GO
SET SHOWPLAN_XML OFF;
GO

SHOWPLAN_XML 출력에서 UnmatchedIndexes 요소 및 Parameterization 하위 요소는 필터링된 인덱스가 쿼리에 대해 일치하지 않았음을 나타냅니다. SHOWPLAN_XML 출력을 보는 방법은 XML 실행 계획을 참조하십시오.

해결 방법은 매개 변수가 있는 식이 필터 조건자의 하위 집합이 아닐 경우 쿼리 결과가 비어 있도록 쿼리를 수정하는 것입니다. 다음 쿼리에서는 이러한 수정 내용을 보여 줍니다. ComponentID in (533, 324, 753) 식을 WHERE 절에 추가하여 쿼리 결과가 반드시 필터링된 조건자 식의 하위 집합이 되게 합니다. 이렇게 수정하면 쿼리 최적화 프로그램에서는 다음 SELECT 문에 대해 필터링된 인덱스 FIBillOfMaterialsWithComponentID를 검토할 수 있습니다.

USE AdventureWorks2008R2;
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WHERE ComponentID in (533, 324, 753)
    AND (ComponentID = @p OR ComponentID = @q);
GO
SET SHOWPLAN_XML OFF;
GO

단순 매개 변수화

쿼리 최적화 프로그램은 필터링된 인덱스가 처리하지 않는 잔여 열을 검색하는 키 조회를 수행하여 쿼리를 처리하지 않더라도 필터링된 인덱스를 사용할 수 있습니다. 키 조회에 대한 자세한 내용은 Key Lookup 실행 계획 연산자를 참조하십시오. 예상되는 키 조회의 수가 적은 경우 쿼리 최적화 프로그램이 이 방법을 선택할 수도 있습니다. 다음 쿼리에서는 인덱스 힌트를 사용하여 쿼리 프로세서가 EndDate에 대해 책갈피 조회와 함께 FIBillOfMaterialsWithEndDate를 사용하도록 할 수 있습니다. 키 조회는 쿼리 조건자의 EndDate > @date 비교에 대해 발생합니다.

USE AdventureWorks2008R2;
GO
DECLARE @date AS DATE;
SET @date = '20040825'
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WITH ( INDEX (FIBillOfMaterialsWithEndDate) )
WHERE EndDate > @date;
GO

EndDate > @Date가 필터링된 인덱스 식 EndDate IS NOT NULL과 정확히 일치하지 않습니다. 매개 변수가 있는 쿼리가 필터링된 인덱스 식에서 정의된 행의 하위 집합을 반환하기 때문에 필터링된 인덱스가 이 쿼리에 대해 여전히 적합합니다.