SQL Server версии 2008 привнес в жизнь разработчиков и администраторов этой платформы довольно изрядное количество новшеств. Разрыв в наборе новых «фич» между версиями 2005-2008 не столь умопомрачителен, как между 2000-2005 но и здесь более свежей версии есть чем блеснуть на фоне младших своих товарищей. Одна из таких «козырных карт» 2008-го — фильтрующие индексы, они же filtered index. Это, безусловно, одно из моих любимейших нововведений в SQL Server, и даже претендент на первую строчку в персональном списке «что вам понравилось в 2008-м сервере». Почему я ценю их столь высоко? Как они могут помочь в разработке более быстрых запросов? Смогут ли они предложить какие-либо выгоды администраторам сервера с точки зрения экономии ресурсов последнего? Каковы типичные сценарии их применения? Есть ли подводные камни у этой технологии? Обо всем этом и о ряде вопросов сопутствующих поведет речь данная статья.
Общая идея и формальный синтаксис. Начинаем работу с filtered index.
Итак мы собираемся рассмотреть относительно свежую концепцию официально называемую filtered index, а проще и понятнее — индекс с условием. В любом случае начиная с этого места и до конца статьи я буду ссылаться на изучаемую технологию как FI, дабы сэкономить немного бумаги тем, кто будет изучать материал в распечатанном виде. Нас, конечно, ждет ряд «запускабельных» примеров демонстрирующих практику создания и использования FI, причем все наши эксперименты будут производится на тестовой базе данных AdventureWorks2008R2. Если у вас ее еще нет то рекомендую скачать установочный пакет с набором баз-примеров отсюда и установить или все базы или, как минимум, указанную. Как вы уже правильно поняли, автор выполнял все скрипты и анализировал их результаты на самой последней версии сервера — 2008R2. Однако несомненно, что в версии предыдущей, 2008-й, результаты выполнения тех же скриптов будут аналогичны.
Если перейти к генеральной идее приведшей к появлению на свет FI то она будет настолько проста, что остается только удивляться — как это столь очевидная вещь не появилась в сервере лет этак 5-7 назад? Допустим возьмем таблицу Sales.SalesOrderHeader нашей тестовой базы. Ее колонка PurchaseOrderNumber имеет тип nvarchar(25) и допускает NULL значения. Никаких индексов на этой колонке нет. Мы знаем, что 90% запросов к таблице имеют вид:
1 2 | SELECT <column_list> FROM Sales.SalesOrderHeader WHERE PurchaseOrderNumber='X' OR PurchaseOrderNumber='Y' |
где 'X'/'Y' некоторые значения из разбираемой колонки, но точно не NULL. Разумеется правильный порыв тут — создать обычный индекс по колонке из условия WHERE:
1 | CREATE NONCLUSTERED INDEX [IX_SalesOrderHeader_PurchaseOrderNumber] ON [Sales].[SalesOrderHeader] (PurchaseOrderNumber ASC) |
Однако давайте прежде оценим количество ячеек данной колонки содержащих реальное (не NULL) значение и значение NULL:
1 2 3 4 5 6 | USE AdventureWorks2008R2 GO SELECT COUNT(*) as TotalRows, COUNT(PurchaseOrderNumber) as Rows_Where_PurchaseOrderNumber_Is_Not_NULL, COUNT(*)-COUNT(PurchaseOrderNumber) as Rows_Where_PurchaseOrderNumber_Is_NULL FROM Sales.SalesOrderHeader |
Результат:
31465 3806 27659
Что мы видим? Да то, что под 28 тыс. ячеек этой колонки (а это, без малого, 90% от их общего числа) имеет значение NULL. Иными словами, по условиям задачи лишь 10% записей могут потенциально представлять интерес для нашего «популярного» запроса приведенного выше. Но не менее очевидно, что при создании индекса не фильтрованного (т.е. «классического») будут проиндексированы абсолютно все ячейки вне зависимости от их содержимого. Это плохо сразу по двум причинам:
- 90% места под новый индекс будет потрачено абсолютно впустую и в обозримом будущем (а может и вообще никогда) не будет задействовано в поиске целевых строк
- избирательность получившегося индекса низка. Как мы увидим в дальнейшем идеальная селективность (selectivity) записей при таком количестве строк таблицы Sales.SalesOrderHeader составляет 1/31465, т.е. ≈3.1E-05. Если бы все значения колонки PurchaseOrderNumber были уникальны именно такую селективность мы бы получили, и это было бы очень хорошо. Однако реальное значение этого параметра для «классического» индекса при текущем наборе значений составит 1/3807, или ≈2.6E-04, что хуже почти на порядок! Если предыдущие вычисления вам не совсем понятны или вы не уверены что лучше — высокое значение селективности или низкое, то рекомендую ознакомиться с вот этой базовой статьей разбирающей все эти концепции крайне подробно.
И вот тут самое время вывести «героя» нашего повествования на сцену, встречаем:
1 2 3 4 | CREATE [UNIQUE] NONCLUSTERED INDEX index_name ON table_name(column_name [ ASC | DESC ] [ ,...n ]) [INCLUDE(column_name [ ,...n ])] WHERE filter_predicate |
Именно так записывается упрощенно-формальный синтаксис команды создающей FI. Безусловно центральной частью этого синтаксиса является клаузула WHERE, столь привычная в SELECT-запросах и выглядящая несколько чудаковато в команде CREATE INDEX. Однако — привыкайте, судя по всему она «прикрепилась» к последней инструкции очень надолго. Кстати аргумент ее, filter_predicate, крайне близок по функционалу к своему собрату из инструкции SELECT — он тоже указывает выражение отфильтровывающее часть строк входного набора. Есть ряд нюансов не позволяющих сказать что оба аргумента эквивалентны на 100% по синтаксису и функционалу (и мы эти нюансы кратко разберем в конце статьи), но то что их генеральное предназначение идентично — несомненно. Каковы же будут последствия применения такой необычной клаузулы? Последствия, не побоюсь этого слова, будут самыми драматическими, особенно при использовании ее в нужном месте и в нужных обстоятельствах. Потому что без нее в новый индекс попадают все ячейки целевой колонки/колонок (если речь идет о составном индексе). А с нею в индекс входят только ячейки удовлетворяющие условию filter_predicate! Давайте оценим эти последствия в количественном выражении для нашей тестовой таблицы Sales.SalesOrderHeader. Создайте «классический» индекс IX_SalesOrderHeader_PurchaseOrderNumber по колонке PurchaseOrderNumber скриптом приведенным чуть выше. А затем создайте альтернативный индекс по имени IX_Filtered отличающийся от предыдущего всего одной строчкой:
1 2 | CREATE NONCLUSTERED INDEX [IX_Filtered] ON [Sales].[SalesOrderHeader] (PurchaseOrderNumber) WHERE PurchaseOrderNumber IS NOT NULL |
Сразу выясняем по поводу занятого на диске места:
1 2 3 4 5 6 7 8 9 | use AdventureWorks2008R2 go SELECT sys_i.name,sys_ips.page_count,sys_ips.record_count FROM sys.indexes AS sys_i JOIN sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'Sales.SalesOrderHeader'), null, null, N'Detailed') AS sys_ips ON sys_i.OBJECT_ID = sys_ips.OBJECT_ID AND sys_i.index_id = sys_ips.index_id WHERE sys_i.name IN ('IX_SalesOrderHeader_PurchaseOrderNumber', 'IX_Filtered') AND sys_ips.index_level=0 |
Видим, что индекс «классический» комфортно раскинул все свои 31465 записи по 53-м индексным страницам, большинство из которых для наших условий представляет собой ненужный балласт. Индекс же фильтрованный намного скромнее в своих аппетитах: 3806 записи на 19 страницах. Еще раз смотрим на значение колонки Rows_Where_PurchaseOrderNumber_Is_Not_NULL из приведенного ранее резалт-сета. Что же, с точностью «до копейки», ровно 3806 строк имеют не-NULL значение в целевой колонке. Так же читателю должно быть уже ясно, что при вставке/удалении/обновлении строки со значением NULL в той же колонке новый индекс не будет «камнем на шее» соответствующей команды INSERT/DELETE/UPDATE, т.к. попросту не требует обслуживания при таких обстоятельствах (т.е. тогда, когда модифицируемая строка не подпадает под условие filter_predicate индекса). Не менее ясным должен так же представляться тот факт что FI определенно будет иметь глубину не более чем их «классические» собратья, а в ряде случаев (зависит от конкретного выражения в аргументе filter_predicate) будет иметь глубину меньшую или значительно меньшую. Под глубиной индекса (index depth) понимают общее количество уровней от самого верхнего, корневого (root level), до самого нижнего, листового (leaf level). Разумеется более «мелкий» индекс всегда предпочтительнее индекса «глубокого» — выше его КПД и ниже стоимость обслуживания. Кроме того, не нужно забывать что сам по себе индекс без прилагаемой к нему статистики бесполезен, а потому движок сервера создает и поддерживает статистику по всем вашим индексным и ключевым колонкам. Понятно, что часть ресурсов сервера тратится на создание статистики и ее поддержку в актуальном состоянии. Но это — неизбежная плата за качественную работу оптимизатора запросов, который просто не сможет функционировать без этих метаданных. Тем приятнее будет вам узнать что к FI «прилагается» фильтрованная же статистика, полностью устраивающая оптимизатор, но значительно менее ресурсоемкая в плане поддержки своей актуальности. Уже только перечисленное выше полностью оправдывает существование FI и те достаточно скромные усилия что требуется приложить для освоения этой технологии. Но давайте продолжим наши изыскания. Как у нас дела с плотностью обоих индексов?
1 2 3 4 | use AdventureWorks2008R2 go DBCC SHOW_STATISTICS ('Sales.SalesOrderHeader', IX_SalesOrderHeader_PurchaseOrderNumber) WITH DENSITY_VECTOR DBCC SHOW_STATISTICS ('Sales.SalesOrderHeader', IX_Filtered) WITH DENSITY_VECTOR |
Инструкция DBCC SHOW_STATISTICS с опцией DENSITY_VECTOR дает нам отчет по плотности распределения данных для целевого индекса указываемого как второй параметр инструкции. Для каждого запрошенного индекса выводятся 2 строки резалт-сета: первая для самого этого индекса (не кластерного), вторая — для кластерного индекса на который «опирается» первый. В нашем случае кластерный индекс есть индекс первичного ключа, что означает гарантированную уникальность каждого из его значения, что в свою очередь означает лучшее из возможных значений колонки All Density для данного количества строк. Т.е. мы можем использовать значение в этой колонке для кластерного индекса как эталонное. Чем ближе значение в той же колонке для исследуемых нами индексов к эталонному — тем выше КПД его применения оптимизатором запроса. На самом деле я уже привел часть значений этого запроса в начале статьи при описании проблем связанных с «классическим» индексом. Давайте сделаем «выжимку» из двух полученных резалт-сетов:
IX_SalesOrderHeader_PurchaseOrderNumber 3.1E-05 2.6E-04 Посредственно
IX_Filtered 2.6E-04 2.6E-04 Идеально
Думаю вы и сами все понимаете...