Выбор кластерного индекса - ПК или FK?
У меня есть таблица SQL Server 2014 , которая выглядит следующим образом:
OrderId int not null IDENTITY --this is the primary key column
OrderDate datetime2 not null
CustomerId int not null
Description nvarchar(255) null
Некоторые люди в моей команде предположили, что кластерный индекс должен быть на OrderId, но я думаю, что CustomerId + OrderId будет лучшим выбором по следующим причинам:
- Почти все запросы будут выглядеть
WHERE CustomerId = @param, notOrderId -
CustomerId- это внешний ключ дляCustomerтаблицу, поэтому кластеризованный индекс с помощьюCustomerIdдолжен ускорить объединение - Пока
CustomerIdне является уникальным, с дополнительнымOrderIdуказанный в индексе, гарантирует уникальность (мы можем использовать ключевое словоUNIQUEпри создании кластерного индекса для этих двух столбцов, чтобы избежать накладных расходов не имея уникальности) - После ввода данных код
CustomerIdиOrderIdникогда не меняются, поэтому после первоначальной записи эти строки не будут перемещаться. - Доступ к данным происходит через ORM, который запрашивает все столбцы по умолчанию, поэтому, когда приходит запрос на основе
CustomerId, кластеризованный индекс сможет предоставить все столбцы без дополнительной работы.
Используется ли CustomerId и OrderId как кажется, лучший вариант, приведенный выше? Или, OrderId по своему усмотрению, так как это единственный столбец, который гарантирует уникальность сам по себе?
В настоящее время таблица имеет кластерный индекс на OrderId и некластеризованный индекс на CustomerId, но это не распространяется, поэтому, поскольку мы используем ORM, и все столбцы запрашиваются, это дополнительная работа по их восстановлению. Поэтому с этой записью я пытаюсь улучшить производительность с лучшим CI.
Активность в нашей БД составляет около 85%, а 15% - записи.
2 ответа
Я думаю, что составной кластерный ключ ключа с CustomerID в качестве первого столбца будет лучше, поскольку это в WHERE почти всех запросов.
Может быть больше разрывов по сравнению с инкрементным ключом (или, скорее, субоптимальная плотность страницы за какое-то время, если вы управлять и поддерживать коэффициент заполнения , чтобы избежать« плохих »расколов). Тем не менее, общее улучшение производительности для запросов клиентов является существенным, потому что ключевой поиск избегается.
OrderID или OrderDate может быть лучше для второго столбца в зависимости от ваших наиболее важных запросов.
Например, если клиенты видят хронологический список последних заказов после входа на веб-сайт, OrderDate должен быть следующим, чтобы оптимизировать ORDER BY OrderDate DESC.
Если вы выбрали OrderID как кластерный индекс, с некластеризованным индексом на CustomerID , вы все равно получите расколы и фрагментацию, как раз в некластерном индекс.
Если эта таблица сильно интенсивно записывается (например, появляются гораздо больше операторов INSERT, а не SELECT), я не соглашусь с ответом на wiki .
Выбор CustomerID в качестве первого столбца составного кластерного ключа будет генерировать много раскладки на средней странице . Надеемся, у вас много существующих клиентов, а также много новых клиентов. Поскольку клиенты (надеюсь) размещают несколько заказов по мере того, как ваш бизнес продолжает расти, этот подход будет демонстрировать значительное количество разделов средней страницы, которые будут убивать производительность не только при записи, но также и в том случае, если ваши индексы будут сильно раздроблены и, вероятно, содержат большее количество пробелов (что означает потерянное хранилище и память).
Если вы считаете, что CustomerID должен быть ведущим столбцом составного кластерного индекса, вы можете уменьшить влияние разделов средней страницы, настроив FILLFACTOR on все индексы для этой таблицы. Это уменьшит количество разделов средней страницы, увеличив размер таблицы /индекса. Если вы хотите пройти этот маршрут, я бы предложил провести тестирование со значением 80 и уменьшить, если показывает, что расщепления в середине страницы по-прежнему убивают производительность.
Мое предложение - использовать OrderId. OrderID, естественно, должен быть последовательным и генерировать больше разделов конечной страницы, которые хороши и ожидаются с ростом таблицы. Кроме того, этот подход будет лучше работать с Таблица Разделение , если вы решите использовать столбец OrderDate в качестве ключа раздела. Что касается запросов, которые постоянно используют поле CustomerID, создайте некластеризованный индекс для обработки этих запросов. Этот индекс нужно будет определить с помощью правильного FILLFACTOR, поскольку он будет страдать от разделов средней страницы, о которых я упоминал выше, t будет столь же плохим в целом, в отличие от того, были ли расщепления связаны с кластеризованным индексом.
Активность в нашей БД составляет около 85%, а 15% - записи.
CustomerID + OrderID (и указав заполняющий фактор, чтобы разрешить рост без расщеплений), вероятно, лучше, если эта оценка верна. Просто сделайте уверенным , что оценка является точной. Тестовый тест.