SQL-Ex blog

Что такое фильтрованный индекс?

Это просто индекс с предложением WHERE. Он представляет собой оптимизированный некластеризованный индекс, который может быть сужен для того, чтобы лучше соответствовать подмножеству данных. Например, диапазонам дат, лет, не-NULL значениям или конкретным типам продукции.

Преимущества

Использование фильтрованных индексов может улучшить производительность запросов и качество плана по сравнению с полными табличными индексами. Статистика является более точной, поскольку она относится только к строкам фильтрованного индекса, что приводит к лучшим планам выполнения. Также сокращаются накладные расходы на обслуживание индекса благодаря его уменьшенному размеру, и вы обслуживаете только те данные в индексе, которые изменились, а не изменение всей таблицы данных. Наконец, поскольку размер такого индекса меньше, он занимает меньше места на диске. Зачем иметь индекс со всеми строками, которые никогда не используются?

Давайте перейдем к демонстрации

Сначала создадим индекс с помощью GUI.

В дереве объектов вашей базы данных перейдите к индексам. Щелкните правой кнопкой мыши на индексах и выберите New Index (Новый индекс), затем Non-Clustered Index (некластеризованный индекс).



Как и для обычного индекса выберите столбцы индекса и включенные столбцы, если они необходимы.



Затем выберите FILTER (фильтр) и напечатайте выражение. Здесь мы говорим индексу отфильтровать все NULL-значения. Обратите внимание, что я не включаю слово WHERE, которое будет добавлено автоматически.

USE [AdventureWorks2014]

GO

CREATE NONCLUSTERED INDEX [FIIDX_ComponentID_StartDate] 

ON [Production].[BillOfMaterials]

(       [ComponentID] ASC,       [StartDate] ASC)

WHERE EndDate IS NOT NULL

GO

Давайте теперь рассмотрим пару планов выполнения, чтобы оценить результат. Это обычный запрос, когда вы хотите получить пункты, имеющие конкретную дату завершения. Вместо чтения все строк, включающих NULL (активные продукты, представленные столбцом EndDate), ваш индекс уже исключил неактивные продукты. Если у вас есть индекс только на EndDate, NULL также будут включены, и это может иметь существенное значение, если таких продуктов много в вашей таблице.

USE AdventureWorks2014; 

GO 

SELECT ProductAssemblyID, ComponentID, StartDate  

FROM Production.BillOfMaterials 

WHERE EndDate IS NOT NULL      

AND ComponentID = 5      

AND StartDate > '01/01/2008' ; 

GO

Если вы обнаружите, что по каким-то причинам оптимизатор не использует ваш фильтрованный индекс, вы можете заставить его это сделать с помощью хинта в запросе. Следует предупредить, что использование хинтов запроса не всегда лучший образ действий. Это просто пример того, что вы можете сделать.

USE AdventureWorks2014; 

GO 

SELECT ComponentID, StartDate 

FROM Production.BillOfMaterials    

 WITH ( INDEX ( FIIDX_ComponentID_StartDate ) )  

WHERE EndDate IN ('20000825', '20000908', '20000918');  

GO

Заключение

Для администратора баз данных важно учесть все варианты при создании индексов. Мы привыкли мыслить в терминах предикатов и включенных столбцов, но не вникаем глубоко в фактическое использование индексов и то, как лучшим образом исключить нежелательные результаты в рамках индекса, а не в предложении WHERE запроса. Если вы обнаружите, что ваш индекс необходим только для данных текущего года, то фильтруйте эти данные на уровне индекса. Не читайте/записывайте данные всех лет, если они вам не потребуются.