Что такое индекс в sql

1.2.7. Индексы в SQL Server

Для ограничений UNIQUE и PRIMARY KEY автоматически создается индекс, который упрощает поиск необходимых данных. Что такое индекс? Если говорить простыми словами, то это способ отсортировать данные по определенной колонке. Когда список отсортирован, намного проще производить поиск необходимых данных.

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

• Каждая страница данных содержит 8 килобайт информации. Группа и 8-и рядом стоящих страниц называется пространством.
• Строки данных не хранятся в каком-либо определенном порядке, и нет определенного порядка для последовательности страниц.
• Страницы данных не связаны в связанные списки.
• Когда строка вставляется в страницу и страница переполнена, страница разделяется.

Сервер SQL получает доступ к данным одним из следующих способов:

• Сканирует все страницы таблицы – сканирование таблицы. Когда SQL Server выполняет сканирование таблицы он:
  • Начинает с начала таблицы;
  • Сканирует от страницы к странице через все строки таблицы;
  • Выделяет строку, которая соответствует запросу.
  • Пересекает структуру дерева индексов для поиска строк, соответствующих запросу;
  • Выделяет только необходимые строки, соответствующие критериям запроса.

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

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

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

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

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

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

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

  Индексы бывают кластерными (CLUSTERED) и не кластерными (NONCLUSTERED). В кластерном индексе строки физически сортируются на диске в соответствии с индексируемым полем. Я думаю, что не надо объяснять, почему кластерный индекс может быть только один на таблицу? Нельзя же одновременно физически отсортировать данные по двум ключам.

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

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

  Все первичные ключи, начиная с SQL Server 2000, по умолчанию создаются кластерными. Ограничения UNIQUE по умолчанию создаются не кластерными.

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

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

  В не кластерном индексе в листовом блоке находится ссылка на строку с данными, где можно найти имя Анатолий, а в кластерном индексе данные находятся непосредственно в листовом блоке.

  Кластерный индекс иногда работает быстрее, а иногда удобнее. У меня был случай, когда я разрабатывал программу на Delphi + MS SQL Server, которая состояла из двух частей. Серверная часть программы сохраняла в базе данных строки, полученные с производственного оборудования, а клиентская их читала. Когда сервер сохранял данные в базе, программа направляла программе клиенту по сети сообщение. Получив это сообщение, клиент должен был прочитать сохраненную строку и вывести пользователю на экран.

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

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

  Следующий пример, показывает, как можно создать не кластерный индекс:

  CREATE TABLE Names ( idName int IDENTITY(1,1), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY NONCLUSTERED (idName), )

  Если нужно, чтобы первичный ключ был кластерным, то для MS SQL Server 2000 это по умолчанию и явное указание оператора CLUSTERED нужно только для MS SQL Server 7. Учитывайте эту разницу при обработке первичного ключа разными версиями MS SQL Server во время разработке собственных приложений. А лучше ни на кого не надеяться, а всегда явно указывать тип необходимого ключа.

  В таблице может быть создано 249 не кластерных индексов и только один индекс может быть кластерным. Такое ограничение количества кластерных индексов связано с тем, что физически можно упорядочить только по одному полю, а не из-за прихоти разработчиков MS SQL Server.

  Как мы уже знаем, кластерным может быть и ограничение уникальности. Для ограничения уникальности создается индекс, а свойство кластерный/не кластерный относится как раз к индексу. Например:

  CREATE TABLE Names ( idName int , vcName varchar(50), vcLastName varchar(50), vcSurName varchar(50), dBirthDay datetime, CONSTRAINT cn_unique UNIQUE CLUSTERED(vcName, vcLastName, vcSurName, dBirthDay) )

  В данном примере создается только индекс для полей, с ограничением уникальности. При этом главного ключа нет. А что если его создать:

  CREATE TABLE Names ( idName int , vcName varchar(50), vcLastName varchar(50), vcSurName varchar(50), dBirthDay datetime, CONSTRAINT pk_idName PRIMARY KEY (idName), CONSTRAINT cn_unique UNIQUE CLUSTERED(vcName, vcLastName, vcSurName, dBirthDay) )

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

  А если явно указать, что мы хотим первичный ключ и ограничение уникальности сделать кластерными? В этом случае произойдет ошибка: «Cannot add more than one clustered index for constraints on table ‘Names’ » (не могу добавлять более чем один кластерный индекс для ограничения на таблицу Names). Я же говорил, что кластерным может быть только один индекс.

  Чтобы вам было удобно видеть, что делает сервер после создания таблицы, лучше всего выполнить команду:

  sp_help Names

  Команда sp_help (точнее это процедура SQL сервера) отображает подробную информацию о указанной таблице, в данном случае это таблица Names. Ее команду лучше всего выполнять в программе Query Analyzer, которая поставляется вместе с MS SQL Server, потому что ее результат состоит из нескольких таблиц, а Query Analyzer умеет их отображать в удобочитаемом виде.

  Выполните команду и посмотрите на результат выполнения sp_help для таблицы Names. Обратите внимание на последние две строки. Здесь отображается список ограничений и их имена. Чуть выше показаны две строки индексов для этих ограничений. Первая строка соответствует ограничению cn_unique и во второй колонке видно, что этот индекс кластерный (clustered). Вторая строка – это индекс pk_idNames и во второй колонке указано, что индекс не кластерный (nonclustered).

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

  Создавайте индексы на следующие поля:

  • первичный ключ, такой индекс создается автоматически;
  • внешний ключ или поле, которое часто используется для связи таблиц. На внешние ключи индексы автоматически не создаются, но если в связанных таблицах находится много строк, то индекс реально может повысить производительность. Если в основной таблице много строк, а в связанной не более 100, можно обойтись и без индекса;
  • поле, используемое для поиска ряда значений;
  • поле, по которому сортируются данные;
  • поля, которые группируются во время агрегации (оператор GROUP BY);
  • поле, которое часто используется в запросах SELECT.
  • редко используемые в запросе;
  • содержащие несколько уникальных значений, например колонки, содержащие только значения мужской или женский пол. Такой индекс будет только тормозить систему;
  • объявленные как text, ntext или image типы данных. Колонки с этими типами данных не могут быть проиндексированы.

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

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

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

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

  Рекомендуется делать кластерный индекс минимальным по размеру. Для предотвращения больших кластерных индексов:

  • ограничьте количество колонок в кластерном индексе;
  • уменьшите среднее значение символов с помощью использования типа данных varchar вместо char, а лучше использовать числовые типы данных или уникальный идентификатор guid;
  • старайтесь использовать максимально маленький тип данных.

  Когда вы определяете плотность ваших данных, помните, что плотность связана с определенными элементами данных. Плотность может изменяться. Что значит плотность? Рассмотрим ее на примере таблицы работников, которая содержит даты рождения. Допустим, что у вас на фирме работает 100 человек в возрасте от 23 до 30 и 10 человек в возрасте старше 30. В диапазоне от 23 до 30 получается высокая плотность, потому что здесь находиться очень много записей.

  Так как данные распределяются не равномерно, оптимизатор запросов может использовать или не использовать индексы. Оптимизатор может:

  • Выполнить сканирование таблицы для поля с большой плотностью или для поля, значение которого может вызвать возврат большого количества строк.
  • Если в проиндексированном поле с именами много имен «Вася», то по этому имени может быть использовано сканирование. При этом, для редкого значения, например, имени «Аврора», будет использоваться индекс.

  Разброс данных связан с плотностью. Когда вы определяете плотность данных, вы должны также рассматривать и разброс.

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

  Сервер сам по себе не может знать о разбросе данных. Для этого ему необходимо собрать определенную статистику о полях и содержащихся в таблице значениях, чтобы можно было принять эффективное решение. Имея статистику, сервер сможет принять более эффективное решение о том, надо ли использовать индекс, или сканирование таблицы будет выполняться быстрее. О статистике мы еще не раз будем говорить в главе 4.

  Создание индексов в SQL Server

  Теперь посмотрим, как создавать индексы вручную. До этого момента мы использовали индексы, которые сервер создавал автоматически для первичного ключа и уникального поля. Сервер SQL автоматически создает индекс, когда создается ограничение PRIMARY KEY или UNIQUE, но бывает необходимость создать индекс на поле без этих ограничений.

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

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

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

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

  CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON < table | view >( column [ ASC | DESC ] [ . n ] ) [ WITH < index_option >[ . n] ] [ ON filegroup ]

  Чтобы удобнее было понять команду, я разбиваю ее на строчки. В первой строке указывается ключевые слова CREATE и INDEX, между которыми можно указать UNIQUE, чтобы индекс был уникальным и CLUSTERED или NONCLUSTERED, чтобы сделать индекс кластерным или не кластерным соответственно. После INDEX указывается имя индекса.

  Имя должно быть понятным, должно отображать, что это индекс и желательно, чтобы отражалось имя поля. Я рекомендую использовать для этого формат: «I_CL_Имя». Первая буква I, указывает на то, что это индекс. Затем я ставлю CL или UCL, что будет показывать кластерный или не кластерный индекс. И в самом конце перечисляются имена полей, которые индексируются. В данном случае только одно поле «vcName».

  Во вторую строку я пишу ключевое слово ON, за которым идет имя таблицы и в скобках имена индексируемых полей.

  Следующий пример создает кластерный индекс на колонку vcName:

  CREATE CLUSTERED INDEX I_CL_vcName ON TestTable(vcName)

  После имени колонки нужно указать направление сортировки индекса. Направление задается ключевыми словами ASC (возрастание) или DESC (убывание). Следующий пример создает не кластерный индекс по убыванию:

  CREATE NONCLUSTERED INDEX I_CL_vcName ON TestTable(vcName DESC)

  Теперь поговорим о удалении индексов. Можно удалять только созданные вами индексы. Для этого используется оператор DROP INDEX. Вы не можете использовать этот оператор для удаления индекса, который был автоматически создан на ограничения PRIMARY KEY или UNIQUE. Вы должны удалить ограничение, прежде чем удалять индекс. Нельзя удалять индексы системных таблиц.

  Если удалить кластерный индекс, то все не кластерные индексы будут автоматически перестроены.

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

  DROP INDEX 'table.index | view.index' [ . n ]

  В следующем примере удаляется созданный нами ранее индекс:

  DROP INDEX TestTable.I_CL_vcName

  Ранее мы уже создавали индекс уникальности, но делали мы это только на этапе создания таблицы. Если она уже существует, то индекс уникальности можно добавить с помощью оператора CREATE UNIQUE INDEX.

  Уникальный индекс гарантирует, что все данные в колонке с таким индексом – уникальны, и не содержат повторяющихся значений. Сервер SQL автоматически создает индекс, когда создается ограничение PRIMARY KEY или UNIQUE.

  Сервер SQL проверяет дубликаты каждый раз, когда вы выполняете операторы INSERT или UPDATE. Если дубликат существует, то сервер отклоняет ваши операторы и возвращает сообщение об ошибке.

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

  SELECT индексная колонка, COUNT(индексная колонка) FROM имя таблицы GROUP BY индексная колонка HAVING COUNT (индексная колонка)>1 ORDER BY индексная колонка

  Снова мы забегаем вперед, потому что запросы SELECT это тема следующей главы. Если вы не работали с SQL, то этот запрос еще не понятен для вас, но вернитесь к нему после прочтения второй главы, и все встанет на свои места.

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

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

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

  Вы можете объединять до 16 колонок в составной индекс. Сумма длины всех колонок составного индекса должна быть менее 900 байт. При этом, все поля должны быть из одной таблицы.

  Объявляйте сначала уникальные колонки. Первые колонки, описанные в операторе CREATE INDEX, имеют высший приоритет при сортировке. При поиске данных в таблице, ваш запрос должен будет обязательно ссылаться на первую колонку индекса, иначе индекс точно использоваться не будет.

  Индекс на поля «Фамилия» и «Имя» это не то же самое, что индекс на поля «Фамилия» и «Имя». Эти индексы имеют разный порядок полей. Например, для первого случая сортировка будет следующей:

  Фамилия Имя ----------------------------------------- Иванов Андрей Иванов Сергей Петров Андрей Петров Василий

  Те же самые поля, но с индексом «Имя» и «Фамилия» будут отсортированы следующим образом:

  Фамилия Имя ----------------------------------------- Иванов Андрей Петров Андрей Петров Василий Иванов Сергей

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

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

  Следующий пример создает не кластерный составной индекс для таблицы телефонного справочника. Обратите внимание, что поле «Фамилия» описывается первой, потому что она чаще всего является основой при выборке данных из таблицы:

  CREATE UNIQUE NONCLUSTERED INDEX I_NCL_Фамилия_Имя ON [Телефонный справочник] (Фамилия, Имя)

  Так как индекс уникальный, в таблицу нельзя будет записать двух людей с фамилией и именем Иванов Андрей.

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

  Руководство по SQL. Индексы.

  Индексы – это специальные таблицы, которые могут быть использованы поисковым двигателем базы данных (далее – БД), для ускорения получения данных. Необходимо просто добавить указатель индекса в таблицу. Индекс в БД крайне схож с индексом в конце книги.

  Допустим, мы хотим иметь ссылку на все страницы книги, которые касаются определённой темы (например, Наследование в книге по программированию на языке Java). Для этого, мы в первую очередь ссылаемся на индекс, который указан в конце книге и переходим на любую из страниц, которая относится к необходимой теме.

  Индекс помогает ускорить запросы на получение данных (SELECT [WHERE]), но замедляет процесс добавления и изменения записей (INSERT, UPDATE). Индексы могут быть добавлены или удалены без влияния на сами данные.

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

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

  Команда добавления индекса имеет следующий вид:

   CREATE INDEX имя_индекса ON имя_таблицы; 

  Индекс может относиться, как к одной колонке:

   CREATE INDEX имя_индекса ON имя_таблицы (имя_колонки); 

  Так и к нескольким:

   CREATE INDEX имя_индекса ON имя_таблицы (колонка1, колонка2); 

  Выбор типа индекса (одноколоночный или многоколоночный) зависит от того, что именно мы чаще всего будем использовать в нашем условном операторе WHERE.

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

   DROP INDEX имя_индекса; 

  Дополнительно вы можете ознакомиться с индексом, прочитав статью, посвящённую констрейнту INDEX.

  Индексы используются для увеличения производительности БД, но есть случаи, когда нам стоит избегать их использования:

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

  На этом мы заканчиваем изучение индексов.

  В следующей статье мы рассмотрим способ изменения структуры таблицы.

  Индексы MySQL: что это и зачем нужны
  

  MySQL — это система управления реляционными базами данных с открытым исходным кодом с моделью клиент-сервер. Говоря совсем простым языком, база данных — набор структурированных данных. Чем их больше, тем труднее найти нужные. Для облегчения поиска информации и используются индексы MySQL.

  Что такое индексы?

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

  Важность индексов увеличивается по мере роста объема данных. Если у вас какая-то небольшая база данных, она может работать без индексов, но производительность ваших запросов может сильно упасть, как только она начнет расти.

  Для чего используются индексы?

  Индексы помогают:

  быстро находить строки, соответствующие выражению WHERE

  Индекс – это специальная структура данных, обычно это B-Tree дерево, которое позволяет повышать скорость извлечения данных за счет дополнительных операций записи и хранения. Здесь стоит отметить, что индексы хранятся отдельно от данных.

  

  Схематично B-Tree можно изобразить так: дерево состоит из корня (верхняя вершинка), дальше у нас идут ветви, ветви заканчиваются листьями, на листьях находится нужная информация.

  Мощность Индекса

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

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

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

  14 вопросов об индексах в SQL Server, которые вы стеснялись задать

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

  От переводчика

  • SQL Server Index Basics от 25 ноября 2008 года (заметка даёт понимание основных терминов)
  • 14 SQL Server Indexing Questions You Were Too Shy To Ask от 25 марта 2014 года (собственно, ради неё всё и затевалось)

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

  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.
  • Рассмотрите возможность индексирования столбцов, использующихся в запросах поиска строк на точное соответствие значений.

  А теперь, собственно:

  14 вопросов об индексах в SQL Server, которые вы стеснялись задать

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

  Хотите короткий ответ? Кластеризованный индекс – это и есть таблица. Когда вы создаете кластеризованный индекс у таблицы, подсистема хранения данных сортирует все строки в таблице в порядке возрастания или убывания, согласно определению индекса. Кластеризованный индекс это не отдельная сущность как другие индексы, а механизм сортировки данных в таблице и облегчения быстрого доступа к строкам с данными.
  Представим, что у вас есть таблица, содержащая историю операций по продажам. Таблица 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 накладывает несколько ограничений на фильтруемые индексы, такие, как невозможность создать фильтруемый индекс у представления, так что внимательно читайте документацию.
  Также, может случиться, что вы можно достичь подобных результатов созданием индексированного представления. Однако, фильтруемый индекс имеет несколько преимуществ, таких как возможность уменьшить стоимость обслуживания и улучшить качество ваших планов выполнения. Фильтруемые индексы также допускают перестройку в онлайн-режиме. Попробуйте это сделать с индексируемым представлением.

  И снова немного от переводчика

  Целью появления данного перевода на страницах Хабрахабра было рассказать или напомнить вам о блоге SimpleTalk от RedGate.
  В нём публикуется множество занимательных и интересных записей.
  Я не связан ни с продуктами фирмы RedGate, ни с их продажей.

  Как и обещал, книги для тех кто хочет знать больше
  Порекомендую от себя три очень хорошие книги (ссылки ведут на kindle версии в магазине Amazon):

  	Microsoft SQL Server 2012 T-SQL Fundamentals (Developer Reference) Author Itzik Ben-Gan Publication Date: July 15, 2012 Автор, мастер своего дела, даёт базовые знания о работе с базами данных. Если вы всё забыли или никогда не знали, то определенно стоит её прочитать
  	SQL Server Execution Plans Author Grant Fritchey Publication Date: May 21, 2013 Автор буквально на пальцах объясняет как работают запросы к базе данных. GraDea напомнил, что бесплатно можно скачать pdf на сайте RedGate
  	SQL Server Query Performance Tuning Author Grant Fritchey Publication Date: September 3, 2014 Этот же автор в более глобальной и всеобъемлющей книге объясняет как улучшить производительности базы данных и запросов к ней.

  Да, на самом деле очень много хороших и качественных книг.
  В принципе, можно открыть просто список самых желаемых по SQL Server и покупать любую.
  Многие из них на русский язык не переведены. И, наверное, никогда не будут!
  Тема бесплатного поиска книг не раскрыта и оставлена на ваше личное усмотрение (upd: таки, теперь немного раскрыта).

  • Веб-разработка
  • Программирование
  • SQL
  • Microsoft SQL Server
  Похожие публикации:

  1. Chromedriver что это
  2. Mcsa что это
  3. Как перепрошить яндекс станцию
  4. Как убрать подсказки в input