Ну а можно ли «пощупать» выгоду от применения FI в реальных запросах? Легко. Возьмем вот такой вполне типичный пример — нам нужны значения колонок PurchaseOrderNumber и SalesOrderID тестовой таблицы, но только для тех строк в которых PurchaseOrderNumber не начинается с «PO5307»:
1 2 3 4 5 6 7 8 9 | USE AdventureWorks2008R2 GO SELECT PurchaseOrderNumber, SalesOrderID FROM Sales.SalesOrderHeader WITH (INDEX(IX_SalesOrderHeader_PurchaseOrderNumber)) WHERE PurchaseOrderNumber NOT LIKE 'PO5307%' GO SELECT PurchaseOrderNumber, SalesOrderID FROM Sales.SalesOrderHeader WITH (INDEX(IX_Filtered)) WHERE PurchaseOrderNumber NOT LIKE 'PO5307%' |
Нетрудно заметить, что оба запроса в последнем скрипте выполняют идентичную работу но разными способами: первый используя индекс «классический», а второй — FI. Дабы быть в этом уверенными мы применяем подсказки в запросе (query hints), что, как известно, в целом хорошей идеей никак не назовешь. В подавляющем большинстве случаев «хинты» должны использоваться только в учебно-демонстрационных целях, а не как ежедневная практика в создании промышленного кода. По счастью наша цель именно учебная поэтому продолжаем. Ясно что результат обоих запросов будет одинаков, а вот будет ли равна их стоимость (query cost)? Для выяснения этого вопроса сформируйте для последнего скрипта план выполнения (будет он estimated или actual — без разницы) и посмотрите в «шапку» каждого (рисунок справа, «кликабельно»). Вновь комментарии не нужны, все скажут факты. Можно еще оценить количество дисковой активности генерируемой каждым запросом:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | USE AdventureWorks2008R2 GO SET STATISTICS IO ON; DBCC DropCleanBuffers; SELECT PurchaseOrderNumber, SalesOrderID FROM Sales.SalesOrderHeader WITH (INDEX(IX_SalesOrderHeader_PurchaseOrderNumber)) WHERE PurchaseOrderNumber NOT LIKE 'PO5307%' DBCC DropCleanBuffers; SELECT PurchaseOrderNumber, SalesOrderID FROM Sales.SalesOrderHeader WITH (INDEX(IX_Filtered)) WHERE PurchaseOrderNumber NOT LIKE 'PO5307%' SET STATISTICS IO OFF; |
Выдержка из полученных результатов:
Scan count 1, logical reads 21, physical reads 2, read-ahead reads 19
Даже не запуская последний скрипт вы без труда сообразите что первая строчка относится к запросу с обычным индексом, а вторая — к запросу с FI. И заметьте — все эти более чем убедительные результаты получены на очень скромной (по меркам реальных баз) таблице с очень умеренными 30-тю тысячами строк. Что может нас ожидать после применения индекса с очень избирательным filter_predicate на таблице с числом строк миллионов этак в 40? Правильно, преимущество FI перед «классикой» станет не просто очень заметным, а колоссальным. Резонным возражением может быть фраза типа «но обычные индексы более универсальны — они могут пригодиться для гораздо более широкого спектра инструкций SELECT». И — да, это верно. Можно даже сказать (с некоторой долей утрирования, но совсем небольшой) «1 WHERE индекс — для 1 инструкции SELECT с подобным WHERE фильтром». Причем обе клаузулы — и в индексе, и в инструкции — должны быть весьма близки по своему логическому функционалу. Скажем в нашем примере чуть измененный запрос:
1 2 3 | SELECT COUNT(*) from Sales.SalesOrderHeader WITH (INDEX(IX_Filtered)) GROUP BY PurchaseOrderNumber |
уже не работает:
defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
Группировка может идти и по значениям не входящим в наш FI IX_Filtered, а значит его применение невозможно. Хорошая новость заключается в том что не менее 80% SELECT-ов имеют вполне ясно выраженный фильтр, что и понятно: никому не нужны все 10 тыс. наименований электроники из нашего интернет-магазина, а нужны только стиральные машины по акции «на четверть дешевле и в кредит», ну или типа того. Вот для таких запросов всегда можно «подогнать» FI соответствующий ситуации. Причем, что приятно, «тонкость нарезки» таких FI мы определяем сами. Один фильтрованный индекс на все стиральные машины? Или один на все кроме участвующих в акции, а второй только для последних? Или еще дополнительно первый разделить на «ценою до...» и «ценою после...»? Решение полностью за нами — чем больше мы «мельчим» тем эффективнее работа каждого отдельного индекса/запроса, но и сложнее управление этим «табунчиком» из десятков (сотен?) крошечных индексов. Полагаю агитацию за «новую жизнь» можно считать завершенной. Совершенно ясно что FI пришли к нам всерьез и надолго, и что поле для их деятельности (как минимум — деятельности потенциальной) просто неисчерпаемо. Посмотрим на некоторые тонкие моменты в работе FI.
Filtered Index vs. Indexed Views
Возможно у пытливого читателя к данному моменту родился такой вопрос — ну хорошо, мы создали FI:
1 2 | CREATE NONCLUSTERED INDEX [IX_Filtered] ON [Sales].[SalesOrderHeader] (PurchaseOrderNumber) WHERE PurchaseOrderNumber IS NOT NULL |
и с успехом применили его в запросе вида
1 2 3 | SELECT PurchaseOrderNumber, SalesOrderID from Sales.SalesOrderHeader WITH (INDEX(IX_Filtered)) WHERE PurchaseOrderNumber NOT LIKE 'PO5307%' |
А что если вот так:
1 2 3 4 5 6 7 8 9 10 | USE AdventureWorks2008R2 go CREATE VIEW vFilteredAlternative WITH SCHEMABINDING AS SELECT PurchaseOrderNumber, SalesOrderID FROM Sales.SalesOrderHeader WHERE PurchaseOrderNumber IS NOT NULL and PurchaseOrderNumber NOT LIKE 'PO5307%' GO CREATE UNIQUE CLUSTERED INDEX IDX_VFiltered ON vFilteredAlternative (PurchaseOrderNumber) |
Не тоже ли самое безо всяких новомодных «наворотов»? Может идет изобретение уже существующего велосипеда? Поэтому не удивительно, что вопрос вынесенный в заголовок текущего раздела не единожды повторялся (и продолжает) на форумах/блогах. Что же — надо признать что в плане консервации ресурсов индексированные представления практически не уступают FI, все крайне аккуратно и расчетливо. И с Disk I/O у них так же все в порядке. Можно сказать что тут, в двух главных чертах, паритет. А что по вторичным характеристикам этих подходов? Тогда при создании идентичных индексов, как в нашем примере, расклад будет таков:
Сопоставление основных характеристик фильтрованных индексов и индексированных представлений |
||
| Характеристика | Filtered Index | Indexed Views |
| Принадлежность индексируемых колонок | Все колонки входящие в индекс должны принадлежать одной таблице | Могут объединять колонки нескольких таблиц |
| Вариативность создаваемого индекса | Только не кластерный, как уникальный, так и нет | Первый индекс кластерный и уникальный; последующие — любые не кластерные |
| Затраты на сопровождение | С т.з. ресурсов поддержка индекса в актуальном состоянии стоит чуть дешевле | С т.з. ресурсов поддержка индекса в актуальном состоянии стоит чуть дороже |
| Возможность перестроения индекса online | Да | Да |
| Возможность включения в индекс вычисляемых колонок | Нет | Да |
| Выражение в предикате WHERE | Только простое* | Простое и сложное* |
| Выбор оптимизатором запросов, если индекс представляется подходящим для целевого запроса | Оптимизатор несколько «недолюбливает» индексированные представления и с гораздо большей вероятностью предпочтет аналогичный по набору строк фильтрованный индекс при прочих равных условиях. | |
| Редакции сервера | Создаются и используются в любой редакции без каких либо ограничений. Оптимизатор всегда рассмотрит вариант применения FI. | Создаются в любой редакции, однако только в редакциях Enterprise / Developer оптимизатор автоматически (ключевое слово) рассматривает возможность применения индекса представления в подходящих условиях. |
| Взаимная совместимость | Никакой, FI применимы только к таблицам. Иными словами FI неприменимы даже к обычным представлениям, а уж к индексированным и подавно. Однако если представление имеет в своем составе колонку для которой в исходной таблице создан FI, то при запросе данных из такого представления указанный FI учитывается оптимизатором как возможный кандидат на включение в план исполнения. | |
*точное определение «простых» и «сложных» выражений для предиката фильтрации последует далее
Полагаю что практические выводы из приведенной таблицы каждый читатель может сделать самостоятельно. Для себя я сделал такие: если имеется хотя бы теоретическая возможность использовать FI — надо так и поступать. Они проще, легковеснее, лучше интегрируются оптимизатором в финальный план выполнения. Если требуется «фича» в них отсутствующая (обычно это ограничение по «простоте» фильтрующего предиката) – то да, придется вспомнить об индексированных представлениях. В качестве гипотезы выскажу предположение что FI это «начало конца» индексируемых представлений. Если в будущих версиях FI избавятся от ограничений (часть из которых представлена в таблице выше, а часть еще только будет обсуждаться) — представления можно сделать строго не индексируемыми. Т.е. индексация их все-равно будет возможна, но только «косвенная», через соответствующий FI. На текущий же момент, если у вас есть индексируемое представление которое:
- возвращает колонки одной физической таблицы;
- не использует вычисляемые колонки;
- не использует сложное выражение в своем предикате WHERE,
то вероятность вашего желания заменить подобный индекс на представлении индексом фильтрованным должна быть близка к 100%. Проиграть от такой замены вы практически не способны, а вот приобрести потенциальные выгоды — вполне. Еще раз повторю, по мнению и рекомендациям многих SQL экспертов, да и самой команды разработчиков SQL Server, всегда старайтесь в первую очередь применить именно FI, а не indexed views.
Управление, ограничения и прочие детали.
В принципе для «бытового» применения FI информации изложенной выше вполне достаточно. Если мы четко представляем их генеральное предназначение и способны вычленить запросы «напрашивающиеся» на создание под них FI — дело в шляпе, берем и пользуемся. Однако будет не лишним посвятить известную толику времени и печатно-экранного пространства вещам может и не столь важным, но не безынтересным, а так же помогающим глубже понять FI, идеи за ними стоящие, ну и текущую реализацию этих идей заодно. Итак — первый из таких вторичных вопросов: как мы можем знать, индекс по имени My_Index является классическим или фильтрованным? А если он именно FI — какие данные «пройдут» через него, а какие нет (т.е. как выглядит его filter_predicate)? На оба вопроса можно ответить без T-SQL кодирования, а можно и с ним.
Путь 1, через интерфейс студии.
Определяем какие индексы на таблице FI, а какие обычные:
На тех, что фильтрованные определяем условие фильтрации:
и далее
Если индексов у нас немного, и нам не требуются их характеристики «списком» (т.е. в виде резалт-сета), такой подход, пожалуй, самый удобный.