Лучшие практики и анти-шаблоны при создании индексов в SQL Server?

Мушкетон - антипаттерн индексации, в котором я был виноват в прошлом. Размещение индекса или вариантов одного и того же индекса в столбцах таблицы без просмотра плана объяснения или реального понимания того, как работает оптимизатор.

Индекс - это, по сути, «шпаргалка». Это позволяет СУБД находить определенное значение (или диапазон значений) на диске без необходимости сканировать всю таблицу. Как правило, вы платите небольшой штраф за INSERT / UPDATE / DELETE, имея индекс, но редко так сильно, что это само по себе является узким местом. Хорошая СУБД будет использовать индексы только тогда, когда они улучшают производительность запросов, поэтому здесь не так много крайне негативных антипаттернов; Обычно вам не очень больно, если у вас есть дополнительные индексы (если вы не говорите об очень транзакционных таблицах). Тем не менее, тщательное индексирование по всем направлениям поможет вам убедиться в наличии действительно важных, а лучший способ обнаружить это - профилировать ваше приложение.

Ключ к пониманию того, когда и когда не следует использовать индексы, - это понять, что они на самом деле делают под прикрытием. Короче говоря, они нужны вам, когда селективность индекса высока (т. Е. Количество различных возможных значений велико по сравнению с размером отношения). Так, например, если у вас есть таблица с 10 000 строками, и у вас есть столбец с названием «цвет» в этой таблице, который является либо «красным», либо «синим», наличие индекса не очень помогает, потому что СУБД вероятно, все равно придется загружать большую часть страниц в память (при случайном распределении). И наоборот, индекс по идентификатору первичного ключа таблицы (который почти всегда добавляется автоматически) ускорит поиск в этой таблице - в порядке log (n) - потому что очень небольшое количество узлов в дереве должно быть быть исследованным, чтобы найти страницу на диске, где находится запись.

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

Вот еще пара антипаттернов индексирования, которые я видел или в которых был виноват:

Покрывало - размещение индексов в таблицах с небольшим или нулевым ростом и (очень) низким количеством строк. Это контрпродуктивно, поскольку поиск по индексу может занять больше времени, чем сканирование таблицы.

Индексирование промышленной прочности - Размещение индекса в столбце первичного ключа. Меня попросили сделать это, чтобы «ускорить» запрос.

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

Я вижу ряд людей, которые просто используют помощник по настройке ядра СУБД и думают, что он достаточно умен, чтобы предлагать наиболее оптимальные индексы и статистику. Этого шаблона следует избегать.

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

Возьмите книгу Ицика Бен-Гана по запросам T-SQL (MS Press) в следующий раз, когда вы будете в книжном магазине (она будет у них). Прочтите первые 3 главы, и они расскажут, как процесс запросов работает внутри SQL Server - что касается вашей работы с этой конкретной технологией, они могут оказаться самыми важными 3 главами, которые вы когда-либо читали.

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

База данных обычно игнорирует любой индекс в логическом поле. Он будет игнорировать его как часть составного индекса. (Однако см. «Отфильтрованный индекс» в SQL Server 2008.)

Для составных индексов, в которых будут указаны все значения, перечислите их в обратном порядке по мощности (или по арности, или по количеству различных значений в данных).

Ничего не предполагайте. Все протестировать.

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

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

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

Одна вещь, которую я обнаружил, что люди забывают делать при индексировании, - это индексировать внешний ключ. Индексы первичных ключей строятся автоматически (я говорю о SQL Server, другие базы данных могут отличаться), а внешние ключи - нет. Но многие люди предполагают, что это так (предположительно, те же люди, которые предполагают, что триггеры будут действовать только для одной записи за раз). Поскольку они почти всегда участвуют в объединениях (зачем еще они нужны?), Большую часть времени их нужно индексировать (исключение может быть очень маленькой таблицей).

Я бы определил свой любимый антипаттерн индексирования как: Почему мои запросы такие медленные - состояние, которое возникает, когда люди, не работающие с базами данных, создают большие базы данных и даже не знают достаточно, чтобы помещать в них какие-либо индексы. Типичный симптом можно найти на доске сообщений, когда человек спрашивает, почему требуется 40 минут, чтобы выполнить простой запрос к его таблице с 50 миллионами записей. Вероятно, этот антипаттерн будет встречаться с множеством других антипаттернов проектирования баз данных, поскольку кто-то, даже не знакомый с индексированием, вряд ли сможет разработать эффективную или действенную структуру базы данных.

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

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