Применение оптимизатором запросов индексов на представлениях в планах исполнения.
Посмотрим на героя нашего повествования с иной точки зрения, а именно: насколько хорошо (или плохо) индексы созданные на представлениях «встраиваются» в планы исполнения. Если отбросить неоднозначную по возможным последствиям технологию «хинтов запроса» (однако кратко мы рассмотрим и ее тоже), провести такое «встраивание» может лишь одна сущность — оптимизатор запросов. Вот его поведение давайте проанализируем.
Вновь перестроим нашу тестовую базу данных, однако на этот раз наполним ее «условно-реальным» содержимым, для чего нам потребуется хорошо всем известная учебно-демонстрационная база данных AdventureWorks2008R2. Если по недоразумению на вашем тестовом сервере ее нет, срочно скачайте соответствующий инсталлятор по ссылке, которую вы без проблем найдете на странице Скачать данного блога. Итак — новая версия базы ~DB~:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | USE master GO CREATE DATABASE [~DB~] ON PRIMARY ( NAME = '~DB~_Data', FILENAME = 'c:\sqlCMD.ru\~DB~.mdf', SIZE = 20 MB ) LOG ON ( NAME = '~DB~_Log', FILENAME = 'c:\sqlCMD.ru\~DB~.ldf', SIZE = 20 MB ) GO USE [~DB~] GO SELECT [AddressID],[City],[StateProvinceID] INTO T1 FROM [AdventureWorks2008R2].[Person].[Address] GO CREATE UNIQUE CLUSTERED INDEX [IX_T1] ON T1 ([AddressID]) GO CREATE VIEW vT1 WITH SCHEMABINDING AS SELECT [AddressID],[City],[StateProvinceID] FROM dbo.T1 GO CREATE UNIQUE CLUSTERED INDEX [IX_vT1] ON vT1 ([AddressID]) |
Снова все несложно: представление vT1 ничем, по сути, не отличается от базовой таблицы T1, а индекс IX_vT1 — от IX_T1. Вопрос первый, совсем простой, «на троечку» — к какому из двух индексов прибегнет оптимизатор при построении плана для вот такого элементарного запроса:
1 2 3 | USE [~DB~] GO SELECT * FROM T1 WHERE AddressID < 100 |
Не сомневаюсь, что вы уже готовы ответить и ответ ваш — индекс IX_T1, то бишь «табличный». И это абсолютно верно:
И то сказать, в запросе не имеющим никакого отношения к представлению, и явно напрашивающегося на применение кластерного индекса того объекта что прямо заявлен в коде (то есть таблицы), иное и ожидать было бы трудно. Так что вопрос был откровенно «разминочный». А вот теперь — похитрее, вопрос примерно так «на четыре»:
1 2 3 | USE [~DB~] GO SELECT * FROM vT1 WHERE AddressID < 100 |
На этот раз какой индекс? По-моему, просто напрашивается индекс представления, IX_vT1. Так же полагают и большинство даже опытных DBA. И все они — ошибаются. Вот план для последнего запроса:
«Найдите десять отличий», как говорят в таких случаях. А вот теперь вопрос на «пять с плюсом» — пояснить наблюдаемое. Да, понятно, что оба разбираемых индекса эквивалентны и что оптимизатору можно «юзать» любой для достижения эквивалентного же результата, но почему не пользоваться «родным» для объекта упоминаемого в коде запроса? Хороший вопрос! А ответом на него будет список из таких трех пунктов:
- извлечение данных из базовой таблицы дешевле извлечения данных из индекса представления. Ну то есть как: допустим что кластерного индекса таблицы (IX_T1) нет вовсе. Тогда никаких дискуссий быть не может в принципе: поиск по IX_vT1 в сотни раз быстрее/дешевле чем скан исходной таблицы. Но вот когда есть две [почти] равнозначных альтернативы (как умышленно сделано в нашем тесте), оптимизатор всегда будет клониться в сторону таблицы, а не индексированного представления. Опять же — смотрите на вещи разумно. Все сказанное вовсе не означает, что индекс IX_T1 напоминает сверхзвуковой истребитель, а IX_vT1 — черепаху. Разница в скорости извлечении данных оттуда и оттуда составит доли миллисекунд, если не меньше. Но она, разница эта — есть! Причины ее кроются в совсем уж тонких нюансах программирования ядра сервера и от нас уж в любом случае не зависят. Ну а дальнейшее понятно, оптимизатор «видит», что «а вот тут мы 0.01ms экономим!», и экономит. Что тут сказать? Молодец, старается.
Так же стоит заметить, что сценарий при котором отсутствие кластерного индекса на таблице ведет к безусловному использованию его альтернативы на представлении описывает общий случай, а наш код представляет случай частный, где тот же сценарий разыгрывается намного занимательней, читайте далее; - оптимизатор может оценить таблицу из которой реально идет извлечение данных (T1 в нашем примере) как «небольшую» (small table). Определение большая/небольшая полностью на совести оптимизатора, никаких абсолютных индикаторов типа «если строк меньше 100 а колонок меньше пяти» — нет. Так вот, по одному ему ведомому причинам оптимизатор может оценить базовую таблицу как small table. Если такое решение принято дальнейшее общение идет только с нею, оптимизатор начисто игнорирует представление и все его «обвесы». На самом деле это просто «ответвление» первого пункта, говорящее о том, что бывают ситуации когда прямое извлечение данных из таблиц дешевле работы с самым наидеальнейшим индексом предлагаемым со стороны представления. На практике этот второй пункт нашего списка «в игру» вступает не часто, однако осознание его любым DBA вреда последнему не принесет, это уж как минимум;
- оптимизатор может оценить таблицу из которой реально идет извлечение данных как «нормальную» (то есть не small), однако при этом оценить сам запрос эти данные извлекающий как тривиальный (trivial). Результат такой оценки полностью эквивалентен предыдущему пункту — работа продолжается только с таблицей/табличными индексами, и ни в коем случае с представлением/индексами на нем.
Вот по одной из этих причин в нашем последнем примере выбирается индекс таблицы, а не представления. Что является большой неожиданностью для многих SQL-специалистов. Вас, конечно, интересует какой именно из трех пунктов стал в нашем случае причиной выбора табличного индекса? Это не сложно — виноват пункт третий, говорящий о trivial плане. Как автор об этом узнал? Очень просто. Он включил отображение графического варианта плана исполнения (Ctrl+M в студии), выполнил последний запрос, увидел план состоящий из двух операторов (как на последней иллюстрации) и выбрал мышкой левый, то есть щелкнул по оператору SELECT. После чего нажал F4 и в открывшемся в правой части студии окне свойств указанного оператора нашел одно из них — Optimization Level. Значение этого свойства расставило «все точки над ё»:
Потому-то в данном случае о индексе IX_vT1 и речи быть не может — он даже не рассматривается оптимизатором как кандидат.
Тривиальность нашего плана имеет пару-тройку любопытных следствий. Что бы с ними ознакомиться удалим табличный индекс IX_T1:
1 | DROP INDEX [IX_T1] ON T1 |
Теперь, само собой, два показанных до этого запроса не могут использовать этот индекс. Опираясь на полученные знания и поставив чтение статьи на «паузу», попробуйте предсказать новый план (планы) для каждого из двух запросов не запуская, разумеется, эти самые запросы в студии. Прикинули возможный план(-ы)? Тогда вас несомненно заинтересует тот примечательный факт, что все до единого запросы показанные вот в этом отрывке кода
1 2 3 4 | SELECT AddressID FROM T1 WHERE AddressID < 1000 SELECT AddressID FROM vT1 WHERE AddressID < 1000 SELECT AddressID FROM T1 WITH (FORCESEEK) WHERE AddressID < 1000 SELECT AddressID FROM vT1 WITH (INDEX(IX_vT1)) WHERE AddressID < 1000 |
будут использовать один и тот же план — «лобовое» сканирование таблицы T1! Особого упоминания заслуживают запросы из строк 3 и 4 (обратите внимание, что первый из этих двух пытается работать с таблицей, а второй — с представлением):
- запрос с подсказкой FORCESEEK вообще отказывается исполняться, вываливая нам ошибку о невозможности реализации указанного хинта. Ну вот не по чему поиск вести и все тут!
- запрос с хинтом и явным указанием целевого индекса IX_vT1 ведет себя еще забавнее — он выполняется без малейшего звука, ни на какие хинты не жалуется, а просто... игнорирует последний! И снова бодро принимается сканировать T1, как будто так и было заказано!
Все эти странности/забавности объясняются одной и той же причиной: оптимизатор оценивает запрос как тривиальный и на самых ранних стадиях его анализа начисто отметает любые упоминания представлений и их индексов. Начиная с этого момента для оптимизатора существует лишь T1 со своими индексами, а поскольку в завершающем примере у нас таковых нет, то жизнь оптимизатора становится совсем простой и веселой: table scan не глядя, для любого запроса обращающегося к T1 и любому представлению на нем базирующемся, и вся недолга.
Хорошо, ну а можно как-то в текущей нашей конфигурации заставить оптимизатор обратить свой благосклонный взор на индекс IX_vT1? Или последний бесполезен абсолютно и неприменим в принципе? Выясняется, что один (и ровно один!) способ все же есть — вот он:
1 | SELECT AddressID FROM vT1 WITH (NOEXPAND) WHERE AddressID < 1000 |
Вот только для такого кода план будет следующим:
Итого, для достижения желаемого («ткнуть» оптимизатор носом в «выгодный» индекс) нам пришлось соблюсти два условия:
- выбирать данные из представления, а не таблицы;
- указать специальный хинт NOEXPAND.
Оба пункта взаимосвязаны и выполнить надо непременно оба, иначе мы вновь откатываемся к неизбежному сканированию таблицы. А вот что бы разобраться почему надо выполнить именно эти пункты и почему непременно вместе, нам необходимо рассмотреть один крайне важный процесс — разворачивание индексированных представлений (views expand).
Разворот представления есть не более (но и не менее) чем подстановка на то место где в исходном запросе упоминается имя этого представления (то есть почти всегда в клаузуле FROM) кода (тела) того же представления. Например, если в последнем запросе убрать хинт NOEXPAND то, по сути, выполняется вот такой T-SQL код:
1 2 3 | SELECT AddressID FROM (SELECT [AddressID],[City],[StateProvinceID] FROM dbo.T1) AS vT1 WHERE AddressID > 1000 |
Иными словами происходит макроподстановка, сам объект (представление) заменяется его определением. И вот после такого разворота идет работа исключительно с объектами (и их индексами) упомянутых в развернутом запросе. То есть, в нашем случае: если мы допустили разворот представления vT1, шанс что оптимизатор рассмотрит вариант применения индекса на представлении IX_vT1 будет чуть менее чем 0%. Еще раз — с момента разворота нет никаких представлений (хоть индексированных, хоть нет). А есть базовые таблицы и ничего кроме.
Что, в свете сказанного, делает хинт NOEXPAND? А делает он одну простую вещь: говорит оптимизатору что не нужно заниматься разворачиванием/макроподстановкой того представления к коему данный хинт применен. А следует, как-раз таки, смотреть на это представление как если бы оно было таблицей. Со всеми, разумеется, его индексами — и кластерными, и нет. И вот только такая смена «точки зрения» приводит к тому, что оптимизатор замечает очень удобный для нашего запроса индекс IX_vT1 и немедленно им пользуется.
У пытливого читателя немедленно рождается вопрос: а может тот же самый хинт можно и к таблице применить? Ведь может же быть ситуация, что мы хотим черпать данные именно из T1 (а не из представления), но при этом вовсе не против (а даже настаиваем), что бы оптимизатор использовал индекс IX_vT1? И автор с этим пытливым читателем немедленно согласится — может такая ситуация сложиться! Ну и? Укажем NOEXPAND для T1? Указать-то мы укажем:
1 | SELECT AddressID FROM T1 WITH (NOEXPAND) WHERE AddressID < 1000 |
да вот толку с того ровно 0:
Hint 'noexpand' on object 'T1' is invalid.
Оптимизатор не хуже нас знает, что разворачивать таблицу не нужно, куда ж ее дальше-то развернешь? Но и это не все. Удалим теперь индекс с представления:
1 | DROP INDEX [IX_vT1] ON vT1 |
и вновь опробуем работу того же хинта на представлении:
1 | SELECT AddressID FROM vT1 WITH (NOEXPAND) WHERE AddressID < 1000 |
Результат:
Hint 'noexpand' on object 'vT1' is invalid.
А как вы сами представляете «не разворот» представления без индекса? Что еще полезного порекомендуете оптимизатору с таким делать? В общем — запоминаем:
А если хотя бы одно условие не соблюдено — error 8171, «получите-распишитесь». Понимаете теперь почему нам непременно требовалось соблюсти оба условия что бы IX_vT1 к делу «припахать»?
Ну а что с вопросом нашего пытливого читателя? Что если мы хотим обращаться именно к таблице, но при этом желаем воспользоваться таким привлекательным индексом на представлении? Да что тут скажешь — никто и не обещал, что SQL Server будет выполнять прям таки любое желание администратора/разработчика. Что многие — да, было обещано. Но что бы прямо 100%... Это желание как раз одно из тех, «немногих». Не реализуемо оно! Ссылайтесь на представление и указывайте хинт NOEXPAND или смиритесь со сканом таблицы. Или, в конце концов, создайте индекс на таблице, а не представлении. Вот он будет как раз «и в пир, и в мир», и без всяких намеков оптимизатору. Итоговая мораль наших последних изысканий:
Ну а контр-примеры у автора найдутся? Такие что бы показали полезность индексов на представлении, причем желательно увидеть пользу не только «прямую» (текст запроса прямо ссылается на представление), но и «косвенную» (текст запроса ссылается на базовую таблицу/таблицы, а индекс с представления все-равно используется в плане)? Как говорится — их есть у меня. Для этого, как ни парадоксально это прозвучит, следует усложнить жизнь оптимизатору, «подсунув» последнему не тривиальный запрос. Скажем, слияние (соединение) данных из двух таблиц на эту роль годится. В очередной раз перестроим нашу тестовую базу данных:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | USE master GO CREATE DATABASE [~DB~] ON PRIMARY ( NAME = '~DB~_Data', FILENAME = 'c:\sqlCMD.ru\~DB~.mdf', SIZE = 20 MB ) LOG ON ( NAME = '~DB~_Log', FILENAME = 'c:\sqlCMD.ru\~DB~.ldf', SIZE = 20 MB ) GO USE [~DB~] GO SELECT [AddressID],[City],[StateProvinceID] INTO T1 FROM [AdventureWorks2008R2].[Person].[Address] SELECT [StateProvinceID],[StateProvinceCode],[CountryRegionCode] INTO T2 FROM [AdventureWorks2008R2].[Person].[StateProvince] GO CREATE VIEW vT1T2 WITH SCHEMABINDING AS SELECT [AddressID],[City],[StateProvinceCode],[CountryRegionCode],T1.[StateProvinceID] FROM dbo.T1 INNER JOIN dbo.T2 ON dbo.T1.[StateProvinceID]=dbo.T2.[StateProvinceID] GO CREATE UNIQUE CLUSTERED INDEX [IX_vT1T2] ON vT1T2 ([AddressID]) |
Какие варианты будут у оптимизатора при реализации запроса вида
1 2 | SELECT [City],[CountryRegionCode] FROM T1 INNER JOIN T2 ON T1.[StateProvinceID]=T2.[StateProvinceID] |
Если подумать, потенциальных путей ровно два:
- оптимизатор «замечает» индекс IX_vT1T2 — тогда данные попросту черпаются из него и делу конец. Иными словами весь запрос реализуется ровно одним оператором — сканированием кластерного индекса;
- оптимизатор не «замечает» указанный индекс — тогда не остается ничего иного, как просканировать обе таблицы и слить соответствующие строки обеих. Иными словами операторов будет минимум три — два скана и одно слияние.
Смотрим, какой из двух путей выберет оптимизатор для обсуждаемого запроса. Актуальный план исполнения сомнений не оставляет:
«Заметил»! А почему оптимизатор не поступал так же в предыдущем эксперименте, когда запрос был тривиален? Да именно потому что запрос был именно таким — «слишком простым». А нынешний запрос у нас:
То есть на этот раз оптимизатор не «упирается рогом» в самый очевидный вариант плана, а перебирает несколько альтернатив. Что, конечно, дольше с точки зрения построения плана, но зато много короче с точки зрения его, плана построенного, исполнения. Можно сказать и так: в предыдущем случае оптимизатор мгновенно принимал решение о развороте представления (после чего возможный план и вправду оставался одним единственным), а в случае текущем он некоторое время обдумывает запрос и принимает решение не разворачивать представление. А «смотреть» на него как на таблицу. И, как следствие, пользоваться всеми благами, что та «как бы таблица» предоставляет, в том числе кластерным индексом IX_vT1T2.
Все сказанное в последнем абзаце полностью применимо и к такому запросу:
1 | SELECT [City],[CountryRegionCode] FROM vT1T2 |
Вновь оптимизатор не считает такой запрос элементарным и поэтому план его полностью совпадает с последней иллюстрацией, то есть происходит сканирование кластерного индекса.