45798 (665142), страница 2
Текст из файла (страница 2)
MAXDOP
MAXDOP (max degree of parallelism) – максимальное количество процессоров, используемых при построении плана выполнения запроса. В предыдущих версиях задать этот параметр напрямую при работе с индексами было нельзя – использовались настройки для всей системы, задаваемые через системную хранимую процедуру sp_configure. Теперь же этот параметр можно указать отдельно для каждого индекса. Здесь имеется в виду количество процессоров, которое будет использоваться непосредственно при создании или изменении индекса, а не при последующей работе с ним. Что называется, «пустячок, а приятно» ;)
Index include
В команде создания индекса появился параметр “INCLUDE”. Он позволяет задействовать новую, достаточно полезную функциональность, но для того, чтобы показать, что это такое, лучше начать немного издалека.
В Microsoft SQL Server индекс представляет собой B+tree, узлы которого состоят из ключевых полей, а в листьях (узлах самого последнего уровня) содержатся ссылки на записи таблицы.
Индекс может быть двух типов, кластерный (clustered) и не кластерный.
Кластерный индекс отличается от некластерного тем, что в листьях этого индекса содержатся не ссылки на записи в таблице, а сами записи. Таким образом, при наличии кластерного индекса записи в таблице выстраиваются в порядке ключей такого индекса (строго говоря это не совсем так, но в первом приближении верно). По очевидным причинам кластерный индекс может быть только один на таблицу. Идентификация конкретной записи в таблице также находится в прямой зависимости от наличия кластерного индекса. Если кластерного индекса нет, то запись находится по уникальному идентификатору записи – RID, который однозначно определяет ее положение в файле данных. Если же кластерный индекс в таблице присутствует, то физическое место этой записи не постоянно, а, следовательно, использовать RID не очень практично, поскольку его пришлось бы обновлять во всех индексах при каждом изменении. Поэтому запись идентифицируется по ключу кластерного индекса.
Допустим, необходимо выбрать поле одной из записей в таблице по какому-нибудь условию. В этом случае, в зависимости от наличия индексов, сервер может поступить несколькими способами.
Поле, которое надо выбрать, совпадает с полем, по которому нужно осуществить поиск, и по этому полю построен индекс.
| SELECT LastName FROM employees WHERE LastName = 'Callahan' |
План такого запроса прост и незатейлив:
| |--Index Seek(OBJECT:([Employees].[LastName]), SEEK:([Employees].[LastName]=Convert([@1])) ORDERED FORWARD) |
Нужное значение просто находится по индексу.
Поле, которое необходимо просмотреть, не совпадает с полем, по которому нужно искать, но при этом по полю поиска построен кластерный индекс.
| SELECT LastName FROM Employees WHERE EmployeeID = 8 |
План такого запроса также не отличается излишней сложностью:
| |--Clustered Index Seek(OBJECT:([Employees].[PK_Employees]), SEEK:([Employees].[EmployeeID]=Convert([@1])) ORDERED FORWARD) |
Все закономерно – идет поиск по кластерному индексу, а затем извлекается нужное поле из этого индекса.
Поле, которое надо достать из таблицы, не совпадает с полем, по которому производится поиск, и по полю поиска построен самый обычный индекс.
| SELECT LastName FROM Employees WHERE PostalCode = '98105' |
Вот здесь уже серверу приходится совершать дополнительные телодвижения, и план запроса немного усложняется:
| |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Employees])) |--Index Seek(OBJECT:([Employees].[PostalCode]), SEEK:([Employees].[PostalCode]=Convert([@1])) ORDERED FORWARD) |
Сначала по индексу находится нужная запись, а точнее, не запись, а ссылка на запись. При этом значения поля, которое на самом деле надо достать из таблицы, по-прежнему нет, так как поле поиска не является нужным полем, как это было в первом случае. На данный момент у сервера, как уже говорилось, есть только ссылка, и чтобы извлечь нужное поле, надо выполнить еще одну операцию – bookmark lookup. Стоимость этой операции в некоторых случаях может быть очень высока, например, в этом запросе она составляет половину всей его стоимости (49%). В случае с кластерным индексом такого не происходит, потому что в листьях кластерного индекса содержится вся запись, а значит, ничего искать уже не надо.
| ПРИМЕЧАНИЕ Естественно, в реальных задачах, планы выполнения запросов могут быть гораздо более сложными. Многое зависит от селективности индексов, статистики, доступной памяти и других факторов, но рассмотрение этих высоких материй выходят за рамки данной статьи. |
В силу того, что стоимость дополнительной операции по извлечению полей, не входящих в индекс, может быть довольно высока, то иногда приходится от нее избавляться. В предыдущих версиях 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) GO SELECT 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 |
Как уже говорилось, в качестве включаемых полей могут выступать поля любых типов данных, но, по очевидным причинам, в кластерный индекс добавлять не ключевые поля нельзя. Это просто не имеет смысла, так как если проводить аналогию, то в кластерном индексе вся запись является не ключевым полем. Очевидно также, что вставка записей при наличии такого индекса будет дороже, поскольку необходимо делать копию включаемого поля в листьевые узлы индекса. Однако полезных свойств у данного нововведения, безусловно больше, чем недостатков и, судя по всему, оно окажется весьма полезным.
ONLINE
Если в предыдущих версиях 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).
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
