| ПРИМЕЧАНИЕЕстественно, в реальных задачах, планы выполнения запросов могут быть гораздо более сложными. Многое зависит от селективности индексов, статистики, доступной памяти и других факторов, но рассмотрение этих высоких материй выходят за рамки данной статьи. |
В силу того, что стоимость дополнительной операции по извлечению полей, не входящих в индекс, может быть довольно высока, то иногда приходится от нее избавляться. В предыдущих версиях Microsoft SQL Server был, фактически, только один способ избавится от дорогого bookmark lookup. Для этого строился составной (композитный) индекс, первым полем или полями которого являлись поля, входящие в условие поиска, а затем шли поля, которые необходимо было извлечь. Поскольку в этом случае все нужные значения уже содержатся в ключе индекса, то потребность в дополнительных операциях отпадает. Но при подобном подходе вырастает размер ключей индекса, из-за этого увеличивается размер базы и снижается эффективность индексных операций. Вдобавок, максимальный размер ключа индекса не может превышать 900 байт, и вылезти за эти границы довольно просто.
Для решения этой проблемы в Yukon была добавлена новая функциональность. Теперь индексы могут содержать добавленные поля (include). Суть этого нововведения заключается в том, что в индекс могут быть добавлены не ключевые поля, при этом поиск по ним не осуществляется, но если они понадобятся в запросе, то дополнительных усилий по их извлечению предпринимать не приходится.
Синтаксис довольно прост, при создании или изменении индекса добавляется дополнительный параметр:
| INCLUDE(field[, field...]) |
Здесь field – список неключевых полей таблицы, которые должны быть добавлены в индекс.
При этом сам индекс состоит только из значений ключевых полей, но в листьевые узлы, и только в листьевые, добавляется копия полей, указанных в INCLUDE. Таким образом, поиск остается таким же эффективным, и отсутствуют все ограничения на размер полей, указанных в качестве включаемых, вплоть до того, что там могут находиться даже LOB. При этом индекс занимает меньше места по сравнению с обычным, составным, применявшимся ранее в подобных случаях. И хотя, на первый взгляд, экономия места за счет нелистьевых узлов кажется незначительной – это довольно серьезный плюс, в силу того, что одно из свойств деревьев заключается в прямой зависимости эффективности поиска от размера ключа. Чем меньше размер ключа, тем эффективнее поиск.
Если создать еще один индекс в таблице из предыдущего примера (по полю PostalCode), но добавить туда в качестве включаемого поля LastName, а потом опять сделать выборку LastName по определенному значению PostalCode:
| CREATE INDEX IXPostalCode_inc ON Employees(PostalCode) INCLUDE(LastName)GOSELECT LastName FROM Employees WHERE PostalCode = '98105' |
то план запроса снова станет простым и незатейливым, а стоимость – такой же низкой, как и при использовании составного индекса.
| |--Index Seek(OBJECT:([Employees].[IXPostalCode_inc]), SEEK:([Employees].[PostalCode]=@1) ORDERED FORWARD) |
При этом для поиска используется только что созданный индекс IXPostalCode_inc. Если сейчас попробовать извлечь другое поле той же записи по тому же критерию PostalCode, то будет использоваться обычный индекс, и снова потребуется bookmark lookup.
Посмотрев индексы, построенные по полям таблицы Employees с помощью хорошо известной хранимой процедуры sp_helpindex, можно заметить, что обычный индекс (по PostalCode), и только что построенный индекс (с включаемыми полями) ничем друг от друга не отличаются.
| index_name | index_description | index_keys |
| IXPostalCode_inc | nonclustered located on PRIMARY | PostalCode |
| PostalCode | nonclustered located on PRIMARY | PostalCode |
Более того, даже с помощью устаревшей системной таблицы sysindexes (что неудивительно), и новой sys.indexes (что странно) невозможно найти отличия. И лишь вызвав специальную функцию, дающую расширенную информацию об индексах – fn_indexinfo(...), можно заметить, что индекс IXPostalCode_inc занимает больше места.
| IndexName | IndexType | Rows | MinimumRecordSize | MaximumRecordSize | AverageRecordSize |
| PostalCode | Nonclustered Index | 9 | 22 | 26 | 23.777 |
| IXPostalCode_inc | Nonclustered Index | 9 | 36 | 46 | 40 |
Как уже говорилось, в качестве включаемых полей могут выступать поля любых типов данных, но, по очевидным причинам, в кластерный индекс добавлять не ключевые поля нельзя. Это просто не имеет смысла, так как если проводить аналогию, то в кластерном индексе вся запись является не ключевым полем. Очевидно также, что вставка записей при наличии такого индекса будет дороже, поскольку необходимо делать копию включаемого поля в листьевые узлы индекса. Однако полезных свойств у данного нововведения, безусловно больше, чем недостатков и, судя по всему, оно окажется весьма полезным.
Если в предыдущих версиях Microsoft SQL Server DDL-операции с индексами вызывали блокировку всей таблицы, то теперь индексы могут быть созданы, изменены и удалены без блокирования других операций с данными (online). Например, если один пользователь перестраивает кластерный индекс, то другие могут продолжать изменять и читать данные, по которым этот индекс перестраивается. Эта функциональность может оказаться очень полезной приложениям, которые работают по принципу 24x7, уменьшая время недоступности системы из-за административного обслуживания.
Опция ONLINE может быть установлена для следующих команд:
CREATE INDEX
ALTER INDEX
DROP INDEX
ALTER TABLE (при удалении или изменении UNIQUE или PRIMARY KEY ограничений (constraints))
Чтобы иметь возможность работать с данными во время выполнения DDL операций с индексами, построенными по этим данным, используются следующие временные структуры:
«исходная структура» (source) – это оригинальная таблица или кластерный индекс.
«исходный индекс» (preexisting indexes) – любой индекс, построенный по данным источника. Эта структура доступна параллельным процессам при выборке, вставке, изменении и удалении данных, в том числе для пакетных операций (bulk) и проверки ограничений контроля целостности (referential integrity constraints). Исходный индекс может быть выбран оптимизатором или даже явно указан в запросе.
«конечная структура» (target) – это новый индекс или набор индексов, который создается или перестраивается. Все запросы к исходной структуре, изменяющие данные, автоматически применяются сервером и к конечной структуре. Эта структура не используется для поиска значений до тех пор, пока операция изменения или создания нового индекса не будет зафиксирована, внутри сервера она помечается «только для записи» (write only).
«временный индекс» (temporary mapping index) – эта структура создается только при выполнении online-операций с кластерными индексами. Она используется для определения записей, которые надо удалить из нового индекса, когда во время работы с ним удаляются или изменяются данные в исходной таблице. Этот некластерный индекс создается на том же шаге, что и новый кластерный индекс, и все изменения исходных данных также применяются и к временному индексу.
Например, если происходит ONLINE-операция по перестроению кластерного индекса и четырех некластерных, ассоциированных с ним, то существует одна исходная структура (оригинальный кластерный индекс), пять предварительных индексов (четыре некластерных и один кластерный) и одна конечная структура (конечный кластерный индекс). При перестроении кластерного индекса, некластерные, ассоциированные с ним, не перестраиваются.
Во время выполнения индексных операций на исходной таблице удерживается блокировка IS (Intent Share), также, на некоторых стадиях, на короткое время накладываются блокировки S (Share) и Sch-M (Schema Modification).
Процесс выполнения не блокирующих операций с индексами может быть разделен на три фазы: подготовка, модификация и завершение.
| Фаза | Описание | Блокировки исходных данных |
| Подготовка | Подготовка метаданных для новой структуры. Параллельные операции записи данных блокируются на короткое время. Создается новая структура и помечается как write-only. | S (Shared) натаблицу. IS (Intent Shared) натаблицу. INDEX_BUILD_INTERNAL_RESOURCE. |
| МодификацияОсновная фаза | Данные сканируются, сортируются, перестраиваются и вставляются в новую структуру пакетными операциями (bulk insert). Параллельные операции вставки, изменения и удаления применятся и к исходным структурам, и к создающимся. Выборка происходит с использованием исходных структур. | IS (Intent Shared) натаблицу. INDEX_BUILD_INTERNAL_RESOURCE |
| Завершение | Прежде чем начнется эта фаза, все незафиксированные изменения исходных данных должны быть завершены. Все новые транзакции, работающие с исходной таблицей, блокируются на короткое время, до завершения этой фазы. В системных метаданных исходная структура заменяется только что созданной конечной. В случае необходимости исходная структура удаляется. | IS (Intent Shared) натаблицу. INDEX_BUILD_INTERNAL_RESOURCE. S (Shared) на таблицу после добавления некластерного индекса. SCH-M (Schema Modification) на таблицу после изменения любого индекса. |
Операции с индексом ожидают завершения всех незафиксированных транзакций, прежде чем наложить коллективную (S) блокировку или блокировку изменения метаданных (Sch-m).
Блокировка INDEX_BUILD_INTERNAL_RESOURCE предотвращает параллельные DDL-операции над исходной таблицей во время работы с индексами. Обычная пользовательская активность при этом не блокируется.
В случае перестроения кластерного и некластерного индексов за одну операцию (это может происходить при создании кластерного индекса по таблице, в которой уже существуют некластерные индексы), во время основной фазы на исходную таблицу могут накладываться кратковременные коллективные (S) блокировки. Это необходимо для корректного переключения работы с кластерного индекса на некластерные. После завершения переключения блокировка опять понижается до блокировки намерения (IS).