Индексы - это первое, что необходимо хорошо понимать в работе SQL Server , но странным образом базовые вопросы не слишком часто задаются на форумах и получают не так уж много ответов.
Роб Шелдон отвечает на эти, вызывающие смущение в профессиональных кругах, вопросы об индексах в SQL Server : одни из них мы просто стесняемся задать, а прежде чем задать другие сначала подумаем дважды.

Используемая терминология:

index	индекс
heap	куча
table	таблица
view	представление
B-tree	сбалансированное дерево
clustered index	кластеризованный индекс
nonclustered index	некластеризованный индекс
composite index	составной индекс
covering index	покрывающий индекс
primary key constraint	ограничение на первичный ключ
unique constraint	ограничение на уникальность значений
query	запрос
query engine	подсистема запросов
database	база данных
database engine	подсистема хранения данных
fill factor	коэффициент заполнения индекса
surrogate primary key	суррогатный первичный ключ
query optimizer	оптимизатор запросов
index selectivity	избирательность индекса
filtered index	фильтруемый индекс
execution plan	план выполнения

Основы индексов в SQL Server.

Одним из важнейших путей достижения высокой производительности SQL Server является использование индексов. Индекс ускоряет процесс запроса, предоставляя быстрый доступ к строкам данных в таблице, аналогично тому, как указатель в книге помогает вам быстро найти необходимую информацию. В этой статье я приведу краткий обзор индексов в SQL Server и объясню как они организованы в базе данных и как они помогают ускорению выполнения запросов к базе данных.

Индексы создаются для столбцов таблиц и представлений. Индексы предоставляют путь для быстрого поиска данных на основе значений в этих столбцах. Например, если вы создадите индекс по первичному ключу, а затем будете искать строку с данными, используя значения первичного ключа, то SQL Server сначала найдет значение индекса, а затем использует индекс для быстрого нахождения всей строки с данными. Без индекса будет выполнен полный просмотр (сканирование) всех строк таблицы, что может оказать значительное влияние на производительность.
Вы можете создать индекс на большинстве столбцов таблицы или представления. Исключением, преимущественно, являются столбцы с типами данных для хранения больших объектов (LOB ), таких какimage , text или varchar(max) . Вы также можете создать индексы на столбцах, предназначенных для хранения данных в формате XML , но эти индексы устроены немного иначе, чем стандартные и их рассмотрение выходит за рамки данной статьи. Также в статье не рассматриваются columnstore индексы. Вместо этого я фокусируюсь на тех индексах, которые наиболее часто применяются в базах данных SQL Server .
Индекс состоит из набора страниц, узлов индекса, которые организованы в виде древовидной структуры - сбалансированного дерева . Эта структура является иерархической по своей природе и начинается с корневого узла на вершине иерархии и конечных узлов, листьев, в нижней части, как показано на рисунке:

Когда вы формируете запрос на индексированный столбец, подсистема запросов начинает идти сверху от корневого узла и постепенно двигается вниз через промежуточные узлы, при этом каждый слой промежуточного уровня содержит более детальную информацию о данных. Подсистема запросов продолжает двигаться по узлам индекса до тех пор, пока не достигнет нижнего уровня с листьями индекса. К примеру, если вы ищете значение 123 в индексированном столбе, то подсистема запросов сначала на корневом уровне определит страницу на первом промежуточном (intermediate) уровне. В данном случае первой страница указывает на значение от 1 до 100, а вторая от 101 до 200, таким образом подсистема запросов обратится ко второй странице этого промежуточного уровня. Далее будет выяснено, что следует обратиться к третьей странице следующего промежуточного уровня. Отсюда подсистема запросов прочитает на нижнем уровне значение самого индекса. Листья индекса могут содержать как сами данные таблицы, так и просто указатель на строки с данными в таблице, в зависимости от типа индекса: кластеризованный индекс или некластеризованный.

Кластеризованный индекс

Кластеризованный индекс хранит реальные строки данных в листьях индекса. Возвращаясь к предыдущему примеру, это означает что строка данных, связанная со значение ключа, равного 123 будет храниться в самом индексе. Важной характеристикой кластеризованного индекса является то, что все значения отсортированы в определенном порядке либо возрастания, либо убывания. Таким образом, таблица или представление может иметь только один кластеризованный индекс. В дополнение следует отметить, что данные в таблице хранятся в отсортированном виде только в случае если создан кластеризованный индекс у этой таблицы.
Таблица не имеющая кластеризованного индекса называется кучей.

Некластеризованный индекс

В отличие от кластеризованного индекса, листья некластеризованного индекса содержат только те столбцы (ключевые ), по которым определен данный индекс, а также содержит указатель на строки с реальными данными в таблице. Это означает, что системе подзапросов необходима дополнительная операция для обнаружения и получения требуемых данных. Содержание указателя на данные зависит от способа хранения данных: кластеризованная таблица или куча. Если указатель ссылается на кластеризованную таблицу, то он ведет к кластеризованному индексу, используя который можно найти реальные данные. Если указатель ссылается на кучу, то он ведет к конкретному идентификатору строки с данными. Некластеризованные индексы не могут быть отсортированы в отличие от кластеризованных, однако вы можете создать более одного некластеризованного индекса на таблице или представлении, вплоть до 999. Это не означает, что вы должны создавать как можно больше индексов. Индексы могут как улучшить, так и ухудшить производительность системы. В дополнение к возможности создать несколько некластеризованных индексов, вы можете также включить дополнительные столбцы (included column ) в свой индекс: на листьях индекса будет храниться не только значение самих индексированных столбцов, но и значения этих не индексированных дополнительных столбцов. Этот подход позволит вам обойти некоторые ограничения, наложенные на индекс. К примеру, вы можете включить неидексируемый столбец или обойти ограничение на длину индекса (900 байт в большинстве случаев).

Типы индексов

В дополнение к тому, что индекс может быть либо кластеризованным, либо некластеризованным, возможно его дополнительно сконфигурировать как составной индекс, уникальный индекс или покрывающий индекс.

Составной индекс

Такой индекс может содержать более одного столбца. Вы можете включить до 16 столбцов в индекс, но их общая длина ограничена 900 байтами. Как кластеризованный, так и некластеризованный индексы могут быть составными.

Уникальный индекс

Такой индекс обеспечивает уникальность каждого значения в индексируемом столбце. Если индекс составной, то уникальность распространяется на все столбцы индекса, но не на каждый отдельный столбец. К примеру, если вы создадите уникальных индекс на столбцах ИМЯ и ФАМИЛИЯ , то полное имя должно быть уникально, но отдельно возможны дубли в имени или фамилии.
Уникальный индекс автоматически создается когда вы определяете ограничения столбца: первичный ключ или ограничение на уникальность значений:

• Первичный ключ
  Когда вы определяете ограничение первичного ключа на один или несколько столбцов, тогда SQL Server автоматически создаёт уникальный кластеризованный индекс, если кластеризованный индекс не был создан ранее (в этом случае создается уникальный некластеризованный индекс по первичному ключу)
• Уникальность значений
  Когда вы определяете ограничение на уникальность значений, тогда SQL Server автоматически создает уникальный некластеризованный индекс. Вы можете указать, чтобы был создан уникальный кластеризованный индекс, если кластеризованного индекса до сих пор не было создано на таблице

Покрывающий индекс

Такой индекс позволяет конкретному запросу сразу получить все необходимые данные с листьев индекса без дополнительных обращений к записям самой таблицы.

Проектирование индексов

Насколько полезны индексы могут быть, настолько аккуратно они должны быть спроектированы. Поскольку индексы могут занимать значительное дисковое пространство, вы не захотите создавать индексов больше, чем необходимо. В дополнение, индексы автоматически обновляются когда сама строка с данными обновляется, что может привести к дополнительным накладным расходам ресурсов и падению производительности. При проектирование индексов должно приниматься во внимание несколько соображений относительно базы данных и запросов к ней.

База данных

Как было отмечено ранее индексы могут улучить производительность системы, т.к. они обеспечивают подсистему запросов быстрым путем для нахождения данных. Однако, вы должны также принять во внимание то, как часто вы собираетесь вставлять, обновлять или удалять данные. Когда вы изменяете данные, то индексы должны также быть изменены, чтобы отразить соответствующие действия над данными, что может значительно снизить производительность системы. Рассмотрим следующие рекомендации при планировании стратегии индексирования:

• Для таблиц которые часто обновляются используйте как можно меньше индексов.
• Если таблица содержит большое количество данных, но их изменения незначительны, тогда используйте столько индексов, сколько необходимо для улучшение производительности ваших запросов. Однако хорошо подумайте перед использованием индексов на небольших таблицах, т.к. возможно использование поиска по индексу может занять больше времени, нежели простое сканирование всех строк.
• Для кластеризованных индексов старайтесь использовать настолько короткие поля насколько это возможно. Наилучшим образом будет применение кластеризованного индекса на столбцах с уникальными значениями и не позволяющими использовать NULL. Вот почему первичный ключ часто используется как кластеризованный индекс.
• Уникальность значений в столбце влияет на производительность индекса. В общем случае, чем больше у вас дубликатов в столбце, тем хуже работает индекс. С другой стороны, чем больше уникальных значения, тем выше работоспособность индекса. Когда возможно используйте уникальный индекс.
• Для составного индекса возьмите во внимание порядок столбцов в индексе. Столбцы, которые используются в выражениях WHERE (к примеру, WHERE FirstName = ‘Charlie’ ) должны быть в индексе первыми. Последующие столбцы должны быть перечислены с учетом уникальности их значений (столбцы с самым высоким количеством уникальных значений идут первыми).
• Также можно указать индекс на вычисляемых столбцах, если они соответствуют некоторым требованиям. К примеру, выражение которые используются для получения значения столбца, должны быть детерминистическими (всегда возвращать один и тот же результат для заданного набора входных параметров).

Запросы к базе данных

Другое соображение которое следует учитывать при проектировании индексов это какие запросы выполняются к базе данных. Как было указано ранее, вы должны учитывать как часто изменяются данные. Дополнительно следует использовать следующие принципы:

• Старайтесь вставлять или модифицировать в одном запросе как можно больше строк, а не делать это в несколько одиночных запросов.
• Создайте некластеризованный индекс на столбцах которые часто используются в ваших запросах в качестве условий поиска в WHERE и соединения в JOIN .
• Рассмотрите возможность индексирования столбцов, использующихся в запросах поиска строк на точное соответствие значений.

Почему таблица не может иметь два кластеризованных индекса?

Хотите короткий ответ? Кластеризованный индекс – это и есть таблица. Когда вы создаете кластеризованный индекс у таблицы, подсистема хранения данных сортирует все строки в таблице в порядке возрастания или убывания, согласно определению индекса. Кластеризованный индекс это не отдельная сущность как другие индексы, а механизм сортировки данных в таблице и облегчения быстрого доступа к строкам с данными.
Представим, что у вас есть таблица, содержащая историю операций по продажам. Таблица Sales включает в себя такую информация как идентификатор заказа, позицию товара в заказе, номер товара, количество товара, номер и дату заказа и т.д. Вы создаёте кластеризованный индекс по столбцам OrderID и LineID , с сортировкой в порядке возрастания, как показано в следующем T-SQL коде:

CREATE UNIQUE CLUSTERED INDEX ix_oriderid_lineid ON dbo.Sales(OrderID, LineID);

Когда вы запустите этот скрипт все строки в таблице будут физически отсортированы сначала по столбцу OrderID, а затем по LineID, но сами данные останутся в единственном логическом блоке, в таблице. По этой причине вы не можете создать два кластеризованных индекса. Может быть только одна таблица с одними данными и эта таблица может быть отсортирована только один раз в определенном порядке.

Если кластеризованная таблица даёт множество преимуществ, то зачем использовать кучу?

Вы правы. Кластеризованые таблицы отличны и большинство ваших запросов будут лучше выполнятся к таблицам, имеющим кластеризованный индекс. Но в некоторых случаях вы возможно захотите оставить таблицы в их естественном первозданном состоянии, т.е. в виде кучи, и создать лишь некластеризованные индексы для поддержания работоспособности ваших запросов.
Куча, как вы помните, хранит данные в случайном порядке. Обычно подсистема хранения данных добавляет в таблицу данные в той последовательности в которой они вставляются, однако подсистема также любит перемещать строки с целью более эффективного хранения. В результате у вас нет ни единого шанса предсказать в каком порядке будут храниться данные.
Если подсистема запросов должна найти данные без преимуществ некластеризованного индекса, то она сделает полное сканирование таблицы для нахождения нужных ей строк. На очень маленьких таблицах это обычно не проблема, но как только куча растет в своих размерах производительность быстро падает. Конечно, некластеризованный индекс может помочь, используя указатель на файл, страницу и строку где хранятся необходимые данные – обычно это намного лучшая альтернатива сканированию таблицы. Но даже в этом случае трудно сравнивать с преимуществами кластеризованного индекса при рассмотрении производительности запросов.
Однако куча может помочь улучшить производительность в определенных ситуациях. Рассмотрим таблицу с большим количеством вставок, но редкими обновлениями или удалением данных. К примеру, таблица, хранящая лог, преимущественно используется для вставки значений до тех пор пока не будет архивирована. В куче вы не увидите разбиением страниц и фрагментацию данных, как это случается с кластеризованным индексом, потому что строки просто добавляются в конец кучи. Слишком большое разделение страниц может иметь значительное влияние на производительность и в не самом хорошем смысле. В общем, куча позволяет производить вставку данных относительно безболезненно и вам не надо будет бороться с накладными расходами на хранение и обслуживание, как это бывает в случае кластеризованного индекса.
Но отсутствие обновления и удаления данных не должны рассматриваться как единственная причина. Способ выборки данных также является важным фактором. К примеру, вы не должны использовать кучу, если часто выполняете запросы диапазонов данных или запрашиваемые данные часто должны быть сортированы или сгруппированы.
Всё это означает, что вы должны рассматривать возможность использования кучи только когда работаете с особо-маленькими таблицами или всё ваше взаимодействие с таблицей ограничено вставкой данных и ваши запросы чрезвычайно просты (и вы все-равно используете некластеризованные индексы). В противном случае держитесь хорошо спроектированного кластеризованного индекса, к примеру определенного на простом возрастающем ключевом поле, как широко применяемый столбец с IDENTITY .

Как изменить установленное по умолчанию значение коэффициента заполнения индекса?

Изменение установленного по умолчанию коэффициента заполнения индекса это одно дело. Понимание того как установленный по умолчанию коэффициент работает это другое. Но сначала пару шагов назад. Коэффициент заполнения индекса определяет количество пространства на странице для хранения индекса на нижнем уровне (уровень листьев) перед тем как начать заполнять новую страницу. К примеру, если коэффициент выставлен в значение 90, то при росте индекс займет на странице 90%, а затем перейдет на следующую страницу.
По умолчанию, значение коэффициента заполнения индекса в SQL Server равно 0, что равнозначно значению 100. В результате все новые индексы автоматически наследуют эту настройки, если вы специально в коде не укажете отличное от стандартного для системы значения или измените поведение по умолчанию. Вы можете воспользоваться SQL Server Management Studio для корректировки установленного по умолчанию значения или запустить системную сохраненную процедуру sp_configure . К примеру, следующий набор T-SQL команд устанавливает значение коэффициента равное 90 (предварительно необходимо переключится в режим продвинутых настроек):

EXEC sp_configure "show advanced options", 1; GO RECONFIGURE; GO EXEC sp_configure " fill factor", 90; GO RECONFIGURE; GO

После изменения значения коэффициента заполнения индекса необходимо перезагрузить сервис SQL Server . Теперь вы можете проверить установленное значение, запустив процедуру sp_configure без указанного второго аргумента:

EXEC sp_configure "fill factor" GO

Данная команда должна вернуть значение равное 90. В результате все вновь создаваемые индексы будут использовать это значение. Вы можете проверить это, создав индекс и запросить значение коэффициента заполнения:

USE AdventureWorks2012; -- ваша база данных GO CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName); GO SELECT fill_factor FROM sys .indexes WHERE object_id = object_id("Person.Person" ) AND name ="ix_people_lastname" ;

В данном примере мы создали некластеризованный индекс в таблице Person в базе данныхAdventureWorks2012 . После создания индекса мы можем получить значение коэффициента заполнения из системной таблиц sys.indexes. Запрос должен вернуть 90.
Однако, представим, что мы удалили индекс и снова создали его, но теперь указали конкретное значение коэффициента заполнения:

CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName) WITH (fillfactor=80 ); GO SELECT fill_factor FROM sys .indexes WHERE object_id = object_id("Person.Person" ) AND name ="ix_people_lastname" ;

В этот раз мы добавили инструкцию WITH и опцию fillfactor для нашей операции создания индексаCREATE INDEX и указали значение 80. Оператор SELECT теперь возвращает соответствующее значение.
До сих пор всё было довольно-таки прямолинейно. Где вы реально можете погореть во всём этом процессе, так это когда вы создаёте индекс, использующий значение коэффициента по умолчанию, подразумевая, что вы знаете это значение. К примеру, кто-то неумело ковыряется в настройках сервера и он настолько упорот, что ставит значение коэффициента заполнения индекса равное 20. Тем временем вы продолжаете создавать индексы, предполагая значение по умолчанию равное 0. К сожалению, у вас нет способа узнать значение коэффициента до тех пор как вы не создадите индекс, а затем проверите значение, как мы делали в наших примерах. В противном случае, вам придётся ждать момента когда производительность запросов настолько упадёт, что вы начнёте что-то подозревать.
Другая проблема о которой вам стоит помнить это перестроение индексов. Как и при создании индекса вы можете конкретизировать значение коэффициента заполнения индекса, когда его перестраиваете. Однако, в отличие от команды создания индекса, перестройка не использует серверные настройки по умолчанию, несмотря на то что так может показаться. Даже больше, если вы конкретно не укажете значение коэффициента заполнения индекса, то SQL Server будет использовать то значение коэффициента, с которым этот индекс существовал до его перестройки. К примеру, следующая операцияALTER INDEX перестраивает только что созданный нами индекс:

ALTER INDEX ix_people_lastname ON Person.Person REBUILD ; GO SELECT fill_factor FROM sys .indexes WHERE object_id = object_id("Person.Person" ) AND name ="ix_people_lastname" ;

Когда мы проверим значение коэффициента заполнения мы получим значение равное 80, потому что именно его мы указали при последнем создании индекса. Значение по умолчанию не учитывается.
Как вы видите изменить значение коэффициента заполнения индекса не такое уж сложно дело. Намного сложнее знать текущее значение и понимать когда оно применяется. Если вы всегда конкретно указывается коэффициент при создании и перестройки индексов, то вы всегда знаете конкретный результат. Разве что вам приходится заботиться о том, чтобы кто-то другой снова не напортачил в настройках сервера, вызвав перестройку всех индексов со смехотворно низким значением коэффициента заполнения индекса.

Можно ли создать кластеризованный индекс на столбце, содержащем дубликаты?

И да, и нет. Да вы можете создать кластеризованный индекс на ключевом столбце, содержащем дубликаты значений. Нет, значение ключевого столбца не смогут остаться в состоянии не уникальности. Позвольте объяснить. Если вы создаёте неуникальный кластерный индекс (non-unique clustered index) на столбце, то подсистема хранения данных добавляет к дублирующему значению целочисленное значение (uniquifier), чтобы удостовериться в уникальности и, соответственно, обеспечить возможность идентифицировать каждую строку в кластеризованной таблице.
К примеру, вы можете решить создать в таблице с данными о клиентах кластеризованный индекс по столбцу LastName , хранящим фамилию. Столбец содержит такие значения как Franklin, Hancock, Washington и Smith. Затем вы вставляете значения Adams, Hancock, Smith и снова Smith. Но значение ключевого столбца обязательно должны быть уникальны, поэтому подсистема хранения данных изменит значение дубликатов таким образом, что они будут выглядеть примерно так: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 и Smith5678.
На первый взгляд такой подход кажется нормальным, но целочисленное значение увеличивает размер ключа, что может стать проблемой при большом количестве дубликатов, а эти значения станут основой некластеризованного индекса или ссылкой внешнего ключа. По этим причинам вы всегда должны стараться создавать уникальный кластеризованный (unique clustered indexes) при любой возможности. Если это невозможно, то по крайней мере постарайтесь использовать столбцы с очень высоким содержание уникальных значений.

Как хранится таблица, если не был создан кластеризованный индекс?

SQL Server поддерживает два типа таблиц: кластеризованные таблицы, имеющие кластеризованный индекс и таблицы-кучи или просто кучи. В отличие от кластеризованных таблиц данные в куче не сортированы никоим образом. По сути это и есть нагромождение (куча) данных. Если вы добавите строку к такой таблице, то подсистема хранения данных просто добавит её к концу страницы. Когда страница заполнится данными, то они будут добавлены на новую страницу. В большинстве случаев, вы захотите создать кластеризованный индекс на таблице, чтобы получить преимущества от возможности сортировки и ускорения запросов (попробуйте представить себе найти телефонный номер в адресной книге, не отсортированной по какому-либо принципу). Однако, если вы решите не создавать кластеризованный индекс, то вы по-прежнему можете создать у кучи некластеризованный индекс. В этом случае каждая строка индекса будет иметь указатель на строку кучи. Указатель включает в себя идентификатор файла, номер страницы и номер строки с данными.

Какая взаимосвязь между ограничениями на уникальность значения и первичным ключом с индексами таблицы?

Первичный ключ и и ограничение уникальности обеспечивают, что значения в столбце будут уникальны. Вы можете создать только один первичный ключ у таблицы и он не может содержать значения NULL . Вы можете создать у таблицы несколько ограничений на уникальность значения и каждый из них может иметь единственную запись с NULL .
Когда вы создаете первичный ключ, подсистема хранения данных так же создает уникальный кластеризованный индекс, в случае если уже кластеризованный индекс не был создан. Однако, вы можете переопределить установленное по умолчанию поведение и тогда будет создан некластеризованный индекс. Если кластеризованный индекс существует когда вы создаёте первичный ключ, то будет создан уникальный некластеризованный индекс.
Когда вы создаете ограничение на уникальность, подсистема хранения данных создает уникальный некластеризованный индекс. Но вы можете указать создание уникального кластеризованного индекса, если он не был создан ранее.
В общем случае, ограничение на уникальность значение и уникальный индекс это одно и то же.

Почему в SQL Server кластеризованные и некластеризованные индексы называются сбалансированным деревом?

Базовые индексы в SQL Server, кластеризованные или некластеризованные, распространяются по наборам страниц – узлам индекса. Эти страницы организованы в виде определенной иерархии с древовидной структурой, называемой сбалансированным деревом. На верхнем уровне находится корневой узел, на нижнем, конечные узлы листьев, с промежуточными узлами между верхним и нижним уровнями, как показано на рисунке:

Корневой узел предоставляет главную точку входа для запросов, пытающихся получить данные через индекс. Начиная с этого узла, подсистема запросов инициирует переход по иерархической структуре вниз к подходящему конечному узлу, содержащему данные.
К примеру, представим, что поступил запрос на выборку строк, содержащих значение ключа равное 82. Подсистема запросов начинает работу с корневого узла, который отсылает к подходящему промежуточному узлу, в нашем случае 1-100. От промежуточного узла 1-100 происходит переход к узлу 51-100, а оттуда к конечному узлу 76-100. Если это кластеризованный индекс, то на листе узла содержится данные строки, ассоциированной с ключом равным 82. Если же это некластеризованный индекс, то лист индекса содержит указатель на кластеризованную таблицу или конкретную строку в куче.

Как вообще индекс может улучшить производительность запросов, если приходится переходить по всем этим индексным узлам?

Во-первых, индексы не всегда улучшают производительность. Слишком много неверно созданных индексов превращают систему в болото и понижают производительность запросов. Правильнее сказать, что если индексы были аккуратно применены, то они могут обеспечить значительный прирост в производительности.
Подумайте об огромной книге, посвященной настройке производительности SQL Server (бумажной, не об электронном варианте). Представьте, что вы хотите найти информацию о конфигурировании Регулятора ресурсов . Вы можете водить пальцем постранично через всю книгу или открыть содержание и узнать точный номер страницы с искомой информацией (при условии, что книга правильно проиндексирована и в содержании верные указатели). Безусловно, это сэкономит вам значительное время, не смотря на то, что вам надо сначала обратиться к совершенно другой структуре (индексу), чтобы получить необходимую вам информацию из первичной структуры (книги).
Как и книжный указатель, указатель в SQL Server позволяет вам выполнять точные запросы к нужным данным вместо полного сканирования всех данных, содержащихся в таблице. Для маленьких таблиц полное сканирование обычно не проблема, но большие таблицы занимают много страниц с данными, что в результате может привезти с значительному времени выполнения запроса, если не существует индекса, позволяющего подсистеме запросов сразу получить правильное месторасположение данных. Представьте, что вы заблудились на многоуровневой дорожной развязке перед крупным мегаполисом без карты и вы поймёте идею.

Если индексы настолько замечательны, то почему бы просто не создать их на каждый столбец?

Ни одно доброе дело не должно оставаться безнаказанным. По крайней мере, именно так и обстоит дело с индексами. Разумеется, индексы отлично себя показывают, пока вы выполняете запросы на выборку данных оператором SELECT , но как только начинается частый вызов операторов INSERT , UPDATE иDELETE , так пейзаж очень быстро меняется.
Когда вы инициируется запрос данных оператором SELECT , подсистема запросов находит индекс, продвигается по его древовидной структуре и обнаруживает искомые данные. Что может быть проще? Но все меняется, если вы инициируете оператор изменения, такой как UPDATE . Да, для первой части оператора подсистема запросов может снова использовать индекс для обнаружения модифицируемой строки – это хорошие новости. И если происходит простое изменение данных в строке, не затрагивающее изменение ключевых столбцов, то процесс изменения пройдет вполне безболезненно. Но что, если изменение приведет к разделению страниц, содержащих данные, или будет изменено значение ключевого столбца, приводящее к переносу его в другой индексный узел – это приведёт к тому, что индексу может потребоваться реорганизация, затрагивающая все связанные индексы и операции, в результате будет повсеместное падение производительности.
Аналогичные процессы происходят при вызове оператора DELETE . Индекс может помочь найти месторасположение удаляемых данных, но само по себе удаление данных может привести к перестановке страниц. Касаемо оператора INSERT , главного врага всех индексов: вы начинаете добавлять большое количество данных, что приводит к изменению индексов и их реорганизации и все страдают.
Так что учитывайте виды запросов к вашей базе данных при размышлениях какой тип индексов и в каком количестве стоит создавать. Больше не значит лучше. Перед тем как добавить новый индекс на таблицу просчитайте стоимость не только базовых запросов, но и объем занимаемого дискового пространства, стоимость поддержания работоспособности и индексов, что может привести к эффекту домино для других операций. Ваша стратегия проектирования индексов один из важнейших аспектов внедрения и должна включать в рассмотрение множество соображений: от размера индекса, количества уникальных значений, до типа поддерживаемых индексом запросов.

Обязательно ли создавать кластеризованный индекс на столбце с первичным ключом?

Вы можете создать кластеризованный индекс на любой столбце, соответствующем необходимым условиям. Это верно, что кластеризованный индекс и ограничение первичного ключа созданы друг для друга и их брак заключен на небесах, так что усвойте факт, что когда вы создаете первичный ключ, тогда же будет автоматически создан кластеризованный индекс, если он не был создан ранее. Тем не менее, вы можете решить, что кластеризованный индекс будет лучше работать в другом месте, и часто ваше решение будет вполне оправданным.
Главная цель кластеризованного индекса это сортировка всех строк к вашей таблице на основе ключевого столбца, указанного при определении индекса. Это обеспечивает быстрый поиск и легкий доступ к данным таблицы.
Первичный ключ таблицы может быть хорошим выбором, потому что он однозначно идентифицирует каждую строку в таблицы без необходимости добавлять дополнительные данные. В некоторых случаях лучшим выбором будет суррогатный первичный ключ, обладающий не только признаком уникальности, но и малым размером, а значения которого увеличиваются последовательно, что делает некластеризованные индексы, основанные на этом значении более эффективными. Оптимизатор запросов также любит такое сочетание кластеризованого индекса и первичного ключа, потому что соединение таблиц происходит быстрее, чем при соединении другим способом, не использующим первичный ключ и ассоциированный с ним кластеризованный индекс. Как я и говорил это брак, заключенный на небесах.
В конце стоит, однако, отметить, что при создании кластеризованного индекса необходимо принять во внимание несколько аспектов: как много некластеризованных индексов будет основываться на нём, как часто будут изменяться значение ключевого столбца индекса и на сколько ни большие. Когда значение в столбцах кластеризованого индекса изменятся или индекс не будет обеспечивать должной производительности, тогда все другие индексы таблицы могут быть задеты. Кластеризованный индекс должен быть основан на наиболее устойчивом столбце, значения которого увеличиваются в определенном порядке, но не изменяются в случайном. Индекс должен поддерживать запросы к наиболее часто используемым данным таблицы, таким образом запросы получают все преимущества того, что данные сортированы и доступны на корневых узлах, листьях индекса. Если первичный ключ соответствует этому сценарию, то используйте его. Если же нет, то выберите другой набор столбцов.

А что если проиндексировать представление, то это по-прежнему будет представление?

Представление – это виртуальная таблица, формирующая данные из одной или нескольких таблиц. По сути, это именованный запрос, который получает данные из нижележащих таблиц, когда вы вызываете запрос к этому представлению. Вы можете улучшить производительность запросов, создав кластеризованных индекс и некластеризованные индексы у этого представления, аналогично как вы создаете индексы у таблицы, но основной нюанс состоит в том, что первоначально создается кластеризованный индекс, а затем вы можете создать некластеризованный.
Когда создается индексированное представление (материализованное представление), тогда само определение представления остается отдельной сущностью. Это, в конце концов, всего лишь жестко прописанный оператор SELECT , хранящийся в базе данных. А вот индекс совсем другая история. Когда вы создаете кластеризованный или некластеризованный индекс у предастваления, то данные физически сохраняются на диск, аналогично обычному индексу. В дополнение, когда в нижележащих таблицах изменяются данные, то индекс представления автоматически изменяется (это означает, что вы можете захотеть избежать индексирования представлений тех таблиц, в которых происходят частые изменения). В любом случае, представление остается представлением - взглядом на таблицы, но именно выполненном в данный момент, с индексами ему соответствующими.
Перед тем как вы сможете создать индекс у представления, оно должно соответствовать нескольким ограничениям. К примеру, представление может ссылаться только на базовые таблицы, но не другие представления и эти таблицы должны находиться в той же самой базе данных. На самом деле там множество других ограничений, так что не забудьте обратиться к документации по SQL Server за всеми грязными подробностями.

Зачем использовать покрывающий индекс взамен составного индекса?

Во-первых, давайте убедимся, что мы понимаем различие между ними. Составной индекс это просто обычный индекс, в который включено больше одного столбца. Несколько ключевых столбцов может использоваться для обеспечения уникальности каждой строки таблицы, также возможен вариант, когда первичный ключ состоит из нескольких столбцов, обеспечивающих его уникальность, или вы пытаетесь оптимизировать выполнение часто вызываемых запросов к нескольким столбцам. В общем, однако, чем больше ключевых столбцов содержит индекс, тем менее эффективна работа этого индекса, а значит составные индексы стоит использовать разумно.
Как было сказано, запрос может извлечь огромную выгоду, если все необходимые данные сразу расположены на листьях индекса, как и сам индекс. Это не проблема для кластеризованного индекса, т.к. все данные уже там (вот почему так важно хорошенько подумать когда вы создаете кластеризованный индекс). Но некластеризованный индекс на листьях содержит только ключевые столбцы. Для доступа ко всем остальным данным оптимизатору запросов необходимы дополнительные шаги, что может вызвать значительные дополнительные накладные расходы для выполнения ваших запросов.
Вот где покрывающий индекс спешит на помощь. Когда вы определяете некластеризованный индекс, то можете указать дополнительные столбцы к вашим ключевым. К примеру, представим, что ваше приложение часто запрашивает данные столбцов OrderID и OrderDate в таблице Sales :

SELECT OrderID, OrderDate FROM Sales WHERE OrderID = 12345 ;

Вы можете создать составной некластеризованный индекс на обоих столбцах, но столбец OrderDate только добавит накладных расходов на обслуживание индекса, но так и не сможет служить особо полезным ключевым столбцом. Лучшее решение будет это создание покрывающего индекса с ключевым столбцом OrderID и дополнительно включенным столбцом OrderDate :

CREATE NONCLUSTERED INDEX ix_orderid ON dbo.Sales(OrderID) INCLUDE (OrderDate);

При этом вы избегаете недостатков, возникающих при индексации излишних столбцов, в то же время сохраняете преимущества хранения данных на листьях при выполнении запросов. Включенный столбец не является частью ключа, но данные хранятся именно на конечном узле, листе индекса. Это может улучшить производительность выполнения запроса без каких либо дополнительных расходов. К тому же, на столбцы, включенные в покрывающий индекс, накладывается меньше ограничений, нежели на ключевые столбцы индекса.

Имеет ли значение количество дубликатов в ключевом столбце?

Когда вы создаете индекс, вы обязаны постараться уменьшить количество дубликатов в ваших ключевых столбцах. Или более точно: стараться держать коэффициент повторяющихся значений настолько низким, насколько это возможно.
Если вы работаете с составным индексом, то дублирование относится ко всем ключевым столбцам в целом. Отдельный столбец может содержать множество повторяющихся значений, но повторения среди всех столбцов индекса должно быть минимальным. К примеру, вы создаете составной некластеризованный индекс на столбцах FirstName и LastName , вы можете иметь множество значений равных John и множество Doe, но вы хотите иметь как можно меньше значений John Doe, или лучше только одно значение John Doe.
Коэффициент уникальности значений ключевого столбца называется избирательностью индекса. Чем больше уникальных значений, тем выше избирательность: уникальный индекс обладает наибольшей возможной избирательностью. Подсистема запросов очень любит столбцы с высоким значением избирательности, особенно если эти столбцы участвуют в условиях выборки WHERE ваших наиболее часто выполняемых запросов. Чем выше избирательность индекса, тем быстрее подсистема запросов может уменьшить размер результирующего набора данных. Обратной стороной, разумеется, является то, что столбцы с относительно небольшим количеством уникальных значений редко будут хорошими кандидатами на индексирование.

Можно ли создать некластеризованный индекс только для определенного подмножества данных ключевого столбца?

По умолчанию, некластеризованный индекс содержит по одной строке для каждой строки таблицы. Конечно, вы можете сказать то же самое относительно кластеризованного индекса, принимая в расчет, что такой индекс это и есть таблица. Но что касается некластеризованного индекса, то отношение «один к одному» важный концепт, потому что, начиная с версии SQL Server 2008 , у вас есть возможность создать фильтруемый индекс, который ограничивает включенные в него строки. Фильтруемый индекс может улучшить производительность выполнения запросов, т.к. он меньше по размеру и содержит отфильтрованную, более аккуратную, статистику, чем вся табличная - это приводит к созданию улучшенных планов выполнения. Фильтруемый индекс также требует меньше места для хранения и меньших затрат на обслуживание. Индекс обновляется только когда изменяются подходящие под фильтр данные.
В дополнение, фильтруемый индекс легко создать. В операторе CREATE INDEX просто необходимо указать в WHERE условие фильтрации. К примеру, вы можете отфильтровать из индекса все строки, содержащие NULL, как показано в коде:

CREATE NONCLUSTERED INDEX ix_trackingnumber ON Sales.SalesOrderDetail(CarrierTrackingNumber) WHERE CarrierTrackingNumber IS NOT NULL ;

Мы можем, фактически, отфильтровать любые данные, которые не важны в критических запросах. Но будьте внимательны, т.к. SQL Server накладывает несколько ограничений на фильтруемые индексы, такие, как невозможность создать фильтруемый индекс у представления, так что внимательно читайте документацию.
Также, может случиться, что вы можно достичь подобных результатов созданием индексированного представления. Однако, фильтруемый индекс имеет несколько преимуществ, таких как возможность уменьшить стоимость обслуживания и улучшить качество ваших планов выполнения. Фильтруемые индексы также допускают перестройку в онлайн-режиме. Попробуйте это сделать с индексируемым представлением.

4. Обслуживание баз данных

Файлы операционной системы, используемые sql Server 2000:

Структура базы данных

5. УстановкаMicrosoftSqlServer2000

Ограничения инсталляции sql-сервера

ТемаIii. Работа с базой данных создание и использова­ние индексов и ключей

1. Базы данных и их свойства

Описание ролей, используемых для доступа к базе данных и серверу баз данных

Дополнительные опции настройки базы данных

Настройка параметров доступа к базе данных

2. Таблицы базы данных.

3. Типы данных, используемые в sql-сервере

Текстовые типы данных

Типы данных даты и времени

Типы данных для хранения больших объемов инфор­мации

Типы данных специального назначения:

4. Основные операции с базами данных

5. Восстановление бд (администрированиеSqlServer2000)

6. Создание и использование индексов и ключей в сис­теме sql Server.

7. Использование ограничений

8. Использование диаграмм для разработки структуры базы данных

9. Создание представлений (видов)

ТемаIv. Правила и создание значения

1. Инструменты контроля целостности данных

2. Создание правил и стандартных значений

ТемаV. Триггеры в системеSql–сервер

1. Понятие триггера. Типы триггеров

2. Создание триггеров

3. Использование триггеров. Виды триггеров

ТемаVi. Оптимизация запросов и основы sql

1. Оптимизация запросов

2. Основные операторы sql в sql Server 2000

3. Объединение таблиц в операторе select

From titles

4. Использование директив group by иHaving

5. Оператор insert

6. ОператорUpdate

7. ОператорDelete

8. ОператорCreatetable

ТемаVii. Создание и работа с представлениями

1. Основные сведения о представлениях

2. Создание представлений. Отображение представле­ний

3. Редактирование представлений. Отображение зави­симостей представлений. Создание представлений и пред­ставлений

4. Переименование столбцов представлений. Переиме­нование представлений. Удаление представлений

5. Изменение данных посредством представлений. Об­новление данных с помощьюSqlServerEnterpriseManager

ТемаViii. Создание и использование курсоров

1. Понятие курсора

2. Выборка данных из курсора

3. Операторы и глобальные переменные для работы с курсорами

4. Примеры использования курсоров

ТемаIx. Использование хранимых процедур

1. Достоинства и недостатки хранимых процедур

2.Создание хранимой процедуры

3. Операторы языка управления программой. Опера­тор declare

4. Операторы goto, begin…end и if…else

5. Операторы waitfor, return, while, break и continue

6. Операторы print и raiserror

7. Использование параметров в хранимых процедурах

8. Глобальные переменные. Отладка хранимых процедур

Тема X. Транзакции и блокировки

1. Определение транзакции. Ограничения для транзакций. Уровни изоляции транзакций

2. Базовая информация о блокировках. Типы блокировок

3. Создание транзакций и работа с ними. Точки сохранения

4.Отображение информации о блокировках. Явное задание блокировки

Описание параметров для явного задания блокировок

ТемаXi. Система безопасностиSqlServer2000

1. Типы безопасности. Создание и управление бюджетами пользователей

Стандартные роли сервера

Стандартные роли базы данных

2. Добавление новых пользователей. Удаление идентификаторов и пользователей

3. Создание ролей. Удаление ролей

4. Права доступа. Управление правами доступа

ТемаXii. Использование распределенных объектов управления.

1.Sql-dmo. Назначение, возможности

2. Экспорт данных с помощью команды вср

3. Использование объектовSql-dmOв хранимых процедурах

ТемаXiii. Основные сведения о хранилищах данных

1. Хранилища данных. Системы поддержки принятия решений (dss). Интерактивная аналитическая обработка (olap)

Сравнение субд и хранилища данных

2. Компоненты хранилища данных. Хранилища дан­ных и магазины данных

3. Преобразование данных. Метаданные

4. Разработка плана хранилища данных

Microsoft Repository

ТемаXiv. Использование служб преобразования данных

1. Службы преобразования данных (dts).DtSи хра­нилища данных

2. СредствоDtsDataPump

3. МастераDts

4. ИспользованиеDtsDesigner

Тема XV. Службы olap Microsoft sql Server

1.OlaPи многомерные данные

2. Хранение данных в бдolap

3. Оптимизация базы данныхOlap

4. Доступ к многомерным данным

6. Создание и использование индексов и ключей в сис­теме sql Server.

Индексом называется отдельная физическая структура БД, созданная на основе таблиц и предназначенная для ускорения выборки данных, поиск которых осуществляется по значению из проиндексированного столбца. Кроме того, в SQL Server индексы используются для обеспечения уникальности строк и столбцов таблицы, упорядочения информации и распределения данных таблицы в отдельном файле или группе файлов с целью повыше­ния скорости доступа.

В SQL Server данные и индексы таблиц хранятся в виде страниц следующего формата:

В SQL Server дисковая память для таблиц и индексов раз­деляется блоками по 8 страниц, которые называются экстен­тами . После заполнения одного экстента объекту выделяется следующий (еще 8 страниц).

Для представления индексов в SQL Server используется схема двоичного дерева:

Уровень 1

Уровень 0

Каждый из прямоугольников на схеме отображает стра­ницу индекса. С увеличением числа уровней производительность обработки индекса уменьшается. В SQL Server поддерживается два типа индексов: кластерные и некластерные.

Кластерный индекс – это двоичное дерево, в котором на нулевом уровне (уровне листов) содержатся страницы актуаль­ных данных таблицы и физически информация хранится в логи­ческом порядке данного индекса. Создание кластерного индекса требует наличия в БД свободного дискового пространства  в 1,2 раза большего, чем существующий объем данных таблицы. Для каждой таблицы может существовать только один кластерный индекс.

В случае некластерных индексов страницы листового уровня содержат не актуальные данные таблицы, а указатель на строку данных, включающий номер страницы данных и порядко­вый номер записи на странице. Некластерный индекс не требует физического переупорядочения строк данных таблицы и соответ­ственно, не требует наличия в БД большого свободного диско­вого пространства.

Некластерные индексы всегда имеют на один уровень больше кластерных, поэтому после достижения уровня листов дополнительно требуется выполнить чтение страницы данных. Если таблица имеет кластерный индекс, указатели строк некла­стерных индексов будут ссылаться на уровень листов кластер­ного индекса. Если таблица не имеет кластерного индекса указа­тель строк включает идентификатор файла, номер страницы дан­ных и номер записи на странице.

Для одной таблицы может быть создано до 249 некла­стерных индексов. Строка индекса не может иметь длину больше 900 Байт и не должна включать более 16 столбцов значений.

Для любой таблицы достаточно иметь один кластерный и 2 - 6 некластерных индексов (за исключением создания хранилищ данных).

Индексы не могут быть созданы для столбцов со следую­щими типами данных: BIT, TEXT, IMAGE. Индексы не могут создаваться для видов.

Для создания индексов определенной таблицы базы дан­ных SQL-сервера можно воспользоваться одним из следующих способов:

Создать индекс с помощью SQL-команды CREATE IN­DEX;

Воспользоваться возможностями утилиты SQL Server Enterprise Manager.

Рассмотрим второй способ создания индексов. Haчальным этапом создания индекса является выбор необходи­мой базы данных и таблицы, для которой он будет определяться.

Выполнение команды. Все задачи/ Manage Indexes меню Действие отобразит на экране диалоговое окно управления ин­дексами базы данных. Следует обратить внимание на выпадаю­щие списки данного диалогового окна Database и Table , которые позволяют перемещаться между базами данных и их таблицами. При том в списке Existing indexes отображаются имеющиеся ин­дексы для выбранных таблиц баз данных.

В нижней части данного диалогового окна расположены управляющие кнопки, выполняющие следующие действия:

New - создание нового индекса для выбранной таблицы БД;

Edit - редактирование параметров существующего ин­декса;

Delete - удаление предварительно выбранного индекса;

Close - закрытие диалогового окна;

Help - получение справочной информации по данному во­просу.

Для создания нового индекса следует воспользо­ваться кнопкой New данного диалогового окна. Это действие приведет к открытию другого диалогового окна Create New In ­ dex , с помощью которого и устанавливаются параметры индекса. В поле Index name данного диалогового окна необходимо ввести имя создаваемого индекса, после чего определить пере­чень полей участвующих в индексе. Для до­бавления определенного поля в индекс следует установить фла­жок слева от его имени. Здесь также можно просмотреть сле­дующую информацию о поле: Column - имя поля, Data type - тип данных, Length - размер, Nullable - возможность использования Null-значений, Precision - точность и Scale - порядок вводимых значений. Можно менять порядок расположения полей в представленном списке.

Группа опций Index options позволяет настроить дополни­тельные параметры создаваемого индекса:

Unique values – при необходимости ввода в определён­ное поле только уникальных значений, следует установить дан­ную опцию. Это позволит осуществлять автоматическую про­верку уникальности при каждом добавлении новой записи. Если будет предпринята попытка ввода уже имеющегося значения в записи данного поля, будет выдано сообщение об ошибке. При этом следует обратить внимание на запрет присут­ствия NULL-значений в этом поле. При использовании NULL-значений и установке данной опции могут возникнуть ошибки. Поэтому рекомендуется установить обязательный ввод значений в поле, для которого планируется создание уникального индекса;

Clustered index - в системе SQL-сервер имеется возмож­ность физического индексирования данных. Другими словами, использование индексов приводит к созданию отдельной струк­туры, которая связывается с физическим расположением данных в таблице. Использование этой опции позволяет произвести так называемое кластерное индексирование, в результате чего будут отсортированы данные в самой таблице согласно порядку этого индекса, и вся добавляемая информация будет приводить к изме­нению физического порядка данных. При этом нужно учитывать, что в таблице может быть определён только один кластерный ин­декс;

Ignore duplicate values - выбор данной опции приводит к игнорированию ввода повторяющихся значений в проиндексиро­ванных полях;

Do not recompute statistics - установка этой опции опре­деляет функцию автоматического обновления стати­стики для таблицы;

File group - с помощью данной опции можно осуществить выбор файловой группы, в которой будет находиться создавае­мый индекс;

Fill factor - данная возможность используется крайне редко. С помощью этой опции осуществляется настройка разбие­ния индекса на страницы. Если планируется частое изменение, удаление и добавление информации в таблице базы данных, то коэффициент FILLFACTOR следует установить как можно меньше, например, 20. Установка коэффициенту значения 100 рекомендуется при использовании больших таблиц, обращение к которым обычно происходит только для чтения;

Pad index - опция определяет заполнение внутреннего пространства индекса и используется совместно с опцией Fill fac ­ tor ;

Drop existing - при использовании кластерного индекса, выбор данной опции определяет его повторное создание, что по­зволяет предотвратить нежелательное обновление кластерных индексов.

Использование кнопки Edit SQL данного диалогового окна предоставляет пользователю сгенерированную SQL-ко­манду, с помощью которой и будут выполняться произве­денные настройки. В окне имеются управляющие кнопки Parse и Execute , с помощью которых можно проанализи­ровать корректность установленных настроек (Parse), а также произвести запуск полученной SQL-команды (Execute).

Впоследствии созданные индексы могут использо­ваться в SQL-операторах SELECT следующим образом:

SELECT ...

FROM <имя таблицы> (INDEX = <имя_индекса>)

Одним из основных понятий баз данных, используемых при контроле целостности информации, является ключ . Разде­ляют первичные и внешние ключи. Первичный ключ - это уни­кальное поле (или несколько полей), однозначно определяющее записи таблицы базы данных. Внешние ключи - это поля таб­лицы, которые, как правило, соответствуют первичным ключам из других таблиц.

Рассмотрим основные различия между индексами и ключами:

SQL-сервер разрешает определить только один пер­вичный ключ для таблицы, тогда как уникальных индексов можно создавать несколько;

при использовании первичного ключа запрещается воз­можность ввода NULL-значений, тогда как при работе с уникальными индексами этот запрет не является обяза­тель­ным, однако придерживаться его желательно.

Рассмотрим процесс создания первичных ключей с помо­щью утилиты SQL Server Enterprise Manager. Первым этапом решения данной задачи будет выбор таблицы в списке объ­ектов базы данных. Выполнение команды Design Table меню Действие приведет к загрузке дизайнера таблиц, в окне которого следует выбрать необходимые поля, убрать флажок из колонки Allow Nulls для этих полей. Ус­тановка первичного ключа осуществляется с использованием кнопки Set primary key .

Если данная операция была выполнена корректно, то слева от имени поля/полей должен появиться значок ключа. Уда­ление первичного ключа производится аналогично его установке.

Для создания индекса используется команда Transact-SQL.

CREATE INDEX, общий синтаксис которой следующий:

CREATE INDEX <имя индекса> ON <имя таблицы>(имя столбца[, имя столбца]…)

[,] FILLFACTOR=x][[,]

IGNORE_DUP_KEY][[,] DROP_EXISTING}[[,]

STATISTICS_NORECOMPUTE]]

Рассмотрим параметры этой команды:

PAD_INDEX - это размер пространства, оставляемого от­крытым на каждой внутренней странице. По умолчанию число элементов на внутренней странице ≥2. Этот параметр использу­ется совместно с FILLFACTOR и использует процентное значе­ние этого параметра.

IGNORE_DUP_KEY - позволяет продолжить работу даже при попытке поместить в таблицу строки с дублирующимся зна­чением уникального ключевого поля – на экран выводится сооб­щение, а строка игнорируется.

DROP_EXISTING - при использовании этого параметра существующий кластерный индекс удаляется и создаётся заново, существующие некластерные индексы перестраиваются только после создания нового кластерного индекса

STATISTICS_NORECOMPUTE - блокирует автоматиче­ское обновление статистических сведений по индексам.

Рассмотрим другие операции над индексами.

1) Просмотр индексов:

а) в окне SQL Server Enterprise Manager выбрать БД (пикто­грамма в папке Databases );

б) перейти во вкладку Table and indexes – здесь отобра­жаются имена всех таблиц и имена связанных с ними индексов для выбранной БД.

2) Переименование, удаление индексов.

Для удаления индекса используется команда Transact-SQL:

DROP INDEX [владелец.] <имя_ таблицы>. <имя_индекса> [,[владелец.] <имя_таблицы>, <имя_индекса>]

Переименование индекса осуществляется командой:

sp _ rename <имя_объекта>, <новое имя> [,COLUMN | INDEX ]

Можно также использовать окно SQL Server Enterprise Manager: открыть таблицу в дизайнере, из контекстного меню для таблицы выбрать Properties , в диалоговом окне Table Properties выбрать вкладку Indexes / Key - здесь можно переименовать ин­декс и создать новый индекс.

Стратегия использования индексов:

1) Следует индексировать:

столбцы, используемые для объединения таблиц;

столбцы, используемые для ограничения диапазона дан­ных, которые анализируются при выполнении запросов;

столбцы, используемые в директивах ORD ER BY и

GROUP BY ;

столбцы, используемые для суммирования и подведения итогов.

2) Не следует индексировать:

таблицы с небольшим количеством строк;

столбцы, имеющие широкий диапазон значений;

столбцы, значения в которых очень длинные (>25 байт);

столбцы, не используемые при построении запросов.

3) Целесообразно использовать кластерные индексы для столбцов:

если столбцы используются во многих запросах;

если столбцы используются в ORDER BY или

если столбцы используются для объединения таб­лиц.

Использование функции автоматического выбора типа ин­декса:

открыть Query Analyzer;

в списке DB выбрать имя БД;

ввести текст SQL-команды;

Выбрать команду Query/Perform Index Analysis .

SQL Server проанализирует запрос для определения, можно ли создать индекс, который будет способствовать ускоре­нию выполнения запроса. Если индекс удается обнаружить, то будет выведено окноQuery Analyzer . Для создания предлагае­мого индекса, щёлкнуть кнопку Accept .

И ндексы, это специальные таблицы поиска , которую поисковая система базы данных можно использовать для ускорения поиска данных. Проще говоря, индекс представляет собой указатель на данные в таблице. Индекс в базе данных очень похож на индекс в конце книги.

Например, если вы хотите ссылки на все страницы в книге, посвященной определенной теме, сначала обратитесь к индексу, в котором перечислены все темы в алфавитном порядке, а затем передается одному или нескольким конкретным номерам страниц.

Индекс помогает ускорить для запросов и предложения , но это замедляет ввод данных, с заявлениями UPDATE и INSERT . Индексы могут быть созданы или удалены без влияния на данные.

Создание индекса предполагает заявление CREATE INDEX , которое позволяет назвать индекс, чтобы указать таблицу и какой столбец или столбцы индексировать и указать, является ли индекс в порядке возрастания или убывания.

Индексы также могут быть уникальными, с ограничением UNIQUE , для того, чтобы индекс предотвращал дублирование записей в столбце или комбинации столбцов, на которых есть индекс.

Команда CREATE INDEX

Основной синтаксис CREATE INDEX выглядит следующим образом:

CREATE INDEX index_name ON table_name;

Одноколоночные индексы

Индекс для одного столбца создается на основе только одного столбца таблицы. Базовый синтаксис выглядит следующим образом.

CREATE INDEX index_name ON table_name (column_name);

Уникальные индексы

Уникальные индексы используются не только для работы, но и для обеспечения целостности данных. Уникальный индекс не позволяет какие-либо повторяющиеся значения, которые будут вставлены в таблицу. Базовый синтаксис выглядит следующим образом.

CREATE UNIQUE INDEX index_name on table_name (column_name);

Составные индексы

Составной индекс является индексом для двух или более столбцов таблицы. Его основной синтаксис выглядит следующим образом.

CREATE INDEX index_name on table_name (column1, column2);

Независимо от того, какой создать индекс, для одного столбца или составной индекс, примите во внимание столбец(ы), которые вы можете использовать очень часто в запросе WHERE в качестве условия фильтра.

Если есть только один используемый столбец, индекс должен быть выбран для одного столбца. Если существуют два или несколько столбцов, которые часто используются в предложении WHERE в качестве фильтров, композитный индекс будет лучшим выбором.

Неявные индексы

Неявные индексы – это индексы, которые автоматически создаются на сервере базы данных при создании объекта. Индексы автоматически создаются для первичного ключа и ограничения уникальности.

Команда DROP INDEX

Индекс может быть удален с помощью SQL команды DROP . Следует соблюдать осторожность при удалении индекса, поскольку производительность может либо замедлиться или улучшиться.

Базовый синтаксис выглядит следующим образом:

DROP INDEX index_name;

Вы можете посмотреть пример ограничения INDEX , чтобы увидеть некоторые реальные примеры по индексам.

Когда следует избегать индексов?

Хотя индексы предназначены для повышения производительности работы с базой данных, есть моменты, когда их следует избегать.

Следующие инструкции показывают, когда использование индекса должно быть пересмотрено.

• Индексы не должны использоваться на небольших таблицах.
• Таблицы, которые имеют частые большие операции обновления или вставки.
• Индексы не должны использоваться на колонках, содержащих большое количество нулевых значений.
• Столбцы, которыми часто манипулируют не должны быть проиндексированы.

1) Понятие индекса
Индекс – это средство, обеспечивающее быстрый доступ к строкам таблицы на основе значений одного или нескольких столбцов.

Много разнообразия в этом операторе, ибо он не стандартизуется, поскольку стандарты не касаются вопросов производительности.

2) Создание индексов
CREATE INDEX
ON ()

3) Изменение и удаление индексов
Для управления активностью индекса используется оператор:
ALTER INDEX
Для удаления индекса используется оператор:
DROP INDEX

a) Правила выбора таблиц
1. Целесообразно индексировать таблицы, в которых выбирается не более 5% строк.
2. Следует индексировать таблицы, не имеющие дублей в разделе WHERE оператора SELECT.
3. Нецелесообразно индексировать часто обновляемые таблицы.
4. Нецелесообразно индексировать таблицы, занимающие не более 2-х страниц (для Oracle это менее 300 строк), поскольку её полный просмотр не дольше.

b) Правила выбора столбцов
1. Первичные и внешние ключи – часто используются для объединения таблиц, выборки данных и поиска. Это всегда уникальные индексы с максимальной полезностью
2. При использовании опций ссылочной целостности всегда нужен индекс на FK.
3. Столбцы, по которым часто производится сортировка и/или группирование данных.
4. Столбцы, по которым часто производится поиск в разделе WHERE оператора SELECT.
5. Не следует создавать индексов для длинных описательных столбцов.

c) Принципы создания составных индексов
1. Составные индексы хороши, если столбцы по отдельности имеют мало уникальных значений, а составной индекс обеспечивает большую уникальность.
2. Если все значения, выбираемые оператором SELECT, принадлежат составному индексу, то значения выбираются из индекса.
3. Следует создавать составной индекс, если в разделе WHERE используется два или более значений объединенных оператором AND.

d) Не рекомендуется создавать
Не рекомендуется создавать индексы по столбцам, включая составные, которые:
1. Редко используются для поиска, объединения и сортировки результатов запросов.
2. Содержат часто меняющиеся значения, что требует частого обновления индекса замедляющего производительность БД.
3. Содержат небольшое количество уникальных значений (менее 10% м/ж) или преобладающее число строк с одним-двумя значениями (город проживания поставщика Москва).
4. К ним в разделе WHERE применяют функции или выражение, и индекс не работает.

e) Следует не забывать
Следует стремиться к уменьшению количества индексов, поскольку при большом их числе снижается скорость обновления данных. Так MS SQL Server рекомендует создавать не более 16 индексов на таблицу.
Как правило, индексы создаются для запросов и поддержки ссылочной целостности.
Если индекс не используется для запросов, то его следует удалять, а ссылочную целостность обеспечивать с использованием триггеров.

В рамках данной статьи для начинающих, я рассмотрю как определить необходимые индексы для увеличения скорости выполнения sql-запросов.

На самом деле с индексами связано очень много тонкостей, которые могут существенно влиять на производительность как в одну сторону, так и в обратную. Об этом можно найти немало статей в интернете. Громоздкие статьи, поясняющие разницу в адресации, хранении памяти и многих других вещей.

Это, конечно, действительно полезные вещи, однако в них нередко упускается один маленький нюанс - объемы данных, при которых все эти особенности действительно заметно сказываются. А цифра эта обычно измеряется в сотнях тысяч записей. Простыми словами, если у вас в таблицах находится в районе 1-30 тысяч записей и речь идет о веб-сайте (или подобном ресурсе), а не каком-то промежуточном хранилище данных для нагруженных систем, то чаще всего важнее просто построить корректные индексы. Тут важно отметить, что вам совсем не обязательно шибко сильно разбираться во всей технической части. Многие полезные индексы можно построить и при помощи простой логики.

Примечание : При этом подразумевается, что сами запросы построены более или менее оптимально, например, нет лишних полей в select , и прочее.

Индекс для целочисленных полей идентификаторов.

Если у вас есть поле с целочисленным идентификатором (не важно, идентификатор ли это самой таблицы или же идентификатор, указывающий на строку другой таблицы), то стройте для него отдельный индекс.

Суть заключается в следующем. Если поле является идентификатором записей самой таблицы, то речь идет о первичном ключе (он так же является индексом). Пользы от такого индекса много, ведь сайты чаще всего оперируют именно идентификаторами. Если же это идентификатор строчки из таблицы справочника, то индекс также нужен. Так как если вам нужны отфильтрованные данные, то без индексов от этих справочников толку не очень много (ну только разве что, размер базы данных).

Если с первым случаем все достаточно просто и понятно, то для второго случая (со справочником) приведу простой пример.

Допустим, есть две таблицы: статьи (article - id, name, text) и комментарии (comment - id, article_id, text). В первой таблице содержится 200 записей (статей), во второй таблице содержится 2000 записей (по 10 комментариев для каждой статьи). Соответственно, когда каждый пользователь открывает любую статью, выполняется следующий запрос:

Если же sql-запрос выполняется без индекса для поля article_id, то каждый раз будет полностью просматриваться вся таблица с комментариями (все 2000 записей). Если же индекс будет добавлен для поля article_id, то базе данных нужно будет посмотреть не более 20 записей (если быть точным, то порядка 18 в худшем случае). Расчет тут прост. Поиск по индексу в худшем случае происходит примерно со скоростью - двоичный логарифм от числа записей + количество записей с одинаковым значением поля индекса. В данном случае, 10 записей есть у каждой статьи (Их значения повторяются) + log2 от 200 (так как статей всего 200 = 2000 / 10) = 10 + 8 (округляют в большую сторону) = 18.

Конечно, каждый такой индекс, в добавок к занимаемому месту на диске, представляет собой еще и дополнительные издержки в базе данных при insert, update и delete. Ведь, кроме изменения данных самой таблицы, так же возникает необходимость перестраивать ее индексы. Но, как я уже говорил, для объемов обычных веб-сайтов - это не страшно. И даже если у вас будет создан индекс в таблице, которым вы не пользуетесь в своих sql-запросах, то каких-то заметных проблем от этого не возникнет. Кроме того, всегда возможен вариант, что поставив дополнительный модуль или же собственноручно добавив запросы, этот индекс может оказаться очень кстати.

Примечание : Тем не менее, помните, что это касается именно целочисленных индексов, а не варианта "сделаю-ка я индексы для всех возможных полей".

Простые и составные индексы для наиболее частых запросов.

Многие базы данных имеют кэш результатов для запросов. Попробуйте выполнить один и тот же запрос два раза подряд - в первом случае запрос выполнится долго, второй раз быстро. Первый раз данные будут вычисляться, во второй раз данные будут предоставлены из кэша. Однако, это не очень помогает в тех случаях, когда для запросов не строится кэш (например, когда в фильтре вычисляемые условия с использованием встроенных функций базы данных), тех случаях, когда запросы хоть и однотипные, но используются с разными параметрами, и тех случаев, когда запросов много и поэтому данные в кэше хранятся весьма недолгий промежуток времени.

Поэтому периодически может иметь смысл дополнительно строить обычные и составные индексы для часто выполняемых запросов. Рассмотрим два примера.

Простой индекс.

Допустим, у вас есть таблица - товары (product - id, code, name, text). И так сложилось, что пользователи сайта часто ищут товары по их цифробуквенным кодам (артикулам - поле code). Соответственно, запрос выглядит примерно так:

В такой ситуации, имеет смысл создать отдельный индекс для поля "code", так как с ним базе данных не придется полностью сканировать все записи таблицы. Однако, учтите, что у баз данных могут быть ограничения для типов и размеров полей. Поэтому предварительно стоит проверить, можно ли создавать индекс для таких полей.

Составной индекс.

Прежде, чем приводить пример с составным индексом, хотел бы немного пояснить один существенный момент - порядок полей в индексе имеет важное значение. Так как поиск вначале осуществляется по первому полю, а затем уже по последующему (и так далее). Поэтому если же вам известно конкретное значение только последнего поля, то такой индекс не подойдет, так как не зная конкретного значения первого поля, определить какой набор записей необходимо проверять невозможно, из-за чего базе данных придется сканировать полностью все записи таблицы. Простыми словами индекс (column_1, column_2) не равен индексу (column_2, column_1).

Теперь, допустим следующую ситуацию. Есть три таблицы: пользователь (user - id, name), категория (cat - id, name) и статья (article - id, cat_id, user_id, name, text). И вы сделали на сайте такую штуку - внизу статьи выводится полный список статей того же пользователя из данной категории. При этом пользователи оказались настолько плодовитыми, что пишут очень много статей, хоть и в разные категории (например, мелкие истории, небольшие заметки и так далее). В этом случае, запрос будет выглядеть следующим образом:

Если вы сделали индексы для полей идентификаторов, то это вам хоть и поможет, но не сильно. Во-первых, тут есть два равновероятных индекса. Один для категорий, а второй для пользователей. Какой будет лучше - в общем случае неизвестно. Кроме того, это может не сильно помочь, так как у пользователей может быть 1000 статей и в категориях может быть по 1000 статей. Во-вторых, даже сократив записи по конкретному пользователю (или категории), все равно их придется просматривать по второму полю, то есть полное сканирование (хоть и для меньшего объема записей). Например, если у пользователей 1000 записей, то придется для всех 1000 записей проверить - входят они в категорию или нет.

Для большого количества записей и частого вызова - это весьма накладной sql-запрос. Поэтому в таком случае стоит сделать составной индекс, например, (user_id, cat_id), В таком случае, после поиска по пользователю, последующий поиск по категории будет происходить быстрее, так как для полученных записей будет так же иметься индекс. Соответственно, вместо проверки 1000 записей, будет проверено существенно меньше (расчет проверок так же осуществляется, как и со случаем обычного индекса - логарифм + число записей).

Как в таких ситуациях определить порядок полей? Тут все достаточно просто и аналогично тому, что я описывал в статье про фильтрацию (см. ссылку вначале). Напомню, суть в том, чтобы с каждым примененным фильтром записей становилось как можно меньше. Поэтому имеет смысл проверить, среднее количество записей приходящееся на каждое значение поля в таблице. И то поле, у которого это число меньше должно идти первым. Например, для данного sql-запроса, стоит проверить следующее:

Вычисляем среднее число записей для пользователей select -- Среднее число записей avg(data.count) as avg from -- Группируем все записи по идентификатору (select count(*) as `count` from article -- Группируем по пользователям group by user_id) as data ; -- Вычисляем среднее число записей для категорий select -- Среднее число записей avg(data.count) as avg from -- Группируем все записи по идентификатору (select count(*) as `count` from article -- Группируем по категориям group by cat_id) as data ;

Соответственно, если среднее число по пользователям меньше, то это поле должно идти первым, так как, после первого поиска, записей для проверки будет немного. В противном случае, идентификатор категории должен идти первым.

Однако, стоит понимать, что в такой ситуации так же стоит проверить, что записи распределены более или менее равномерно. Ведь может оказаться, что 1 пользователь написал 2000 статей, а остальные всего 100. В такой ситуации, фильтр по категории может быть предпочтительнее, ведь большинство читателей будут просматривать статьи именно этого пользователя. Поэтому иногда стоит вычислить только группировку по идентификаторам (без вычисления avg) и бегло просмотреть полученные результаты.

Если же требуется составить индекс для трех и более полей, то стоит проделать то же самое, только с увеличением количества полей, для которых осуществляется группировка по идентификатору. Простыми словами, сначала проверить первое поле и определить наиболее меньшее число, затем вместо "group by column_1" указать различные варианты с оставшимися полями в виде "group by column_1, column_2", затем "group by column_1, column_3" и так далее. При этом каждый выбирать те комбинации, при которых среднее число записей становится меньше и меньше.