[SQL Server] 필터링된 인덱스 (Filtered Indexes)

 

SQL Server 2008에서 새로 제공되는 기능인 '필터링된 인덱스 (Filtered Indexes)' 란

인덱스를 생성하는 해당 열의 전체 데이터에 대해서 인덱스를 생성하는 것이 아니라,

특정 조건에 맞는 데이터로만 인덱스를 생성하는 것이다.

이렇게 필터링된 인덱스를 이용하면 인덱스의 크기가 줄어들어 인덱스 관리의 부담을 덜 수 있다.

필터링된 인덱스를 생성하는 구문은 기존의 'CREATE INDEX...' 문장에 'WHERE 조건'만 붙여주면 된다.

 

 

필터링된 인덱스를 사용하면 좋은 사례

 

1. NULL 값이 대부분인 열에 인덱스를 생성할 때

    - NULL 값이 대부분인 열에 인덱스를 생성하게 되면, NULL 값까지 인덱스가 생성된다.

그런데, 인덱스의 실제로 NULL 값을 조회할 일은 별로 없지만, 필요 없는 NULL 값으로 인해서

인덱스의 크기는 쓸데 없이 커지게 된다. 이때, NULL 값을 제외한 인덱스를 생성하면

인덱스도 작아지고 검색 속도도 더욱 빨라진다.

 

2. 데이터가 있더라도 특정 범위로만 검색할 경우

    - 예로 전 국민의 데이터가 저장되어 있다고 가정해 보자. 이때, 노인 복지와 관련된 업무를

주로 하게 된다면 주로 노인의 나이 범위(60세 이상)로 검색하게 될 것이므로 해당하는 범위로만

인덱스를 생성하는 것이 좋다.

 

3. 경우에 따라서는 '인덱싱된 뷰' 대신에 사용하는 것이 관리 비용이 절감된다.

 

4. '포괄 열이 있는 인덱스'에도 필터링될 범위를 지정하는 것이 관리에 효율적이다.

 

 

<실습>

 

실습을 원할히 진행하기 위해서 테이블에 생성된 인덱스의 정보를 파악해 주는 저장 프로시저 생성

USE master  GO    CREATE DATABASE WorkTemp  GO    USE WorkTemp  GO    CREATE PROC sp_IndexInfo     @tablename sysname  AS  SELECT @tablename AS '테이블 이름'       , I.name AS '인덱스 이름'       , I.type_desc AS '인덱스 타입'       , A.data_pages AS '페이지 개수'    -- 사용된 데이터 페이지 수       , A.data_pages * 8 AS '크기(KB)'  -- 페이지를 KB(1page = 8KB)로 계산       , P.rows AS '행개수'  FROM sys.indexes I  INNER JOIN sys.partitions P  ON P.object_id = I.object_id  AND OBJECT_ID(@tablename) = I.object_id  AND I.index_id = P.index_id  INNER JOIN sys.allocation_units A  ON A.container_id = P.hobt_id  GO

 

 

데이터 양이 많은 동일한 두 테이블 생성 (19614건)

데이터를 조회해 보면 AddressLine2 열에 NULL 값이 많이 들어 있음을 확인 가능

USE WorkTemp  GO    SELECT AddressID, AddressLine1, AddressLine2  INTO normalIndexTbl  FROM AdventureWorks.Person.Address  GO    SELECT AddressID, AddressLine1, AddressLine2  INTO filterIndexTbl  FROM AdventureWorks.Person.Address  GO    SELECT * FROM normalIndexTbl  GO

 

 

AddressLine2 열에 대해서, 일반 인덱스와 NULL 값을 제외하는 필터링된 인덱스 생성

CREATE NONCLUSTERED INDEX idx_normal  ON normalIndexTbl(AddressLine2)  GO    CREATE NONCLUSTERED INDEX idx_filter  ON filterIndexTbl(AddressLine2)  WHERE AddressLine2 IS NOT NULL  GO

 

 

생성된 인덱스의 크기를 비교

EXEC sp_IndexInfo normalIndexTbl  EXEC sp_IndexInfo filterIndexTbl

 

idx_normal 인덱스는 전체 행(19,614건)이 모두 인덱스로 생성되었다.

그리고 페이지는 35 페이지를 차지

하지만, 필터링된 인덱스인 idx_filter 인덱스는 NULL 값이 아닌 데이터(362건)에 대해서만

인덱스를 생성해서 겨우 2페이지만 차지한다.

 

필터링된 인덱스는 대용량의 테이블에 인덱스를 생성할 때 인덱스의 크기를 줄여줌으로써,

인덱스 관리 비용의 절감 효과와 성능 향상이라는 효과를 낸다.

 

 

참고 : 뇌를 자극하는 SQL Server 2008