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

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

Когда вы используете первичный ключ NONCLUSTERED, а не первичный ключ CLUSTERED?

Заранее спасибо

139 голосов | спросил Stuart Blackler 11 52011vEurope/Moscow11bEurope/MoscowFri, 11 Nov 2011 01:31:59 +0400 2011, 01:31:59

5 ответов


163

Вопрос не в том, должен ли ПК быть NC, но вместо этого вы должны спросить «что такое правильный ключ для кластерного индекса»?

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

Еще одна часть головоломки: как использовать индекс ? Существует три типичных шаблона:

  • , когда одно значение ключа запрашивается в индексе
  • сканирует диапазон, когда извлекается диапазон значений ключа
  • порядок по требованию, когда индекс может удовлетворять порядку без требования сортировки стоп-орда

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

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

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

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

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

Чтобы привести пример, рассмотрите таблицу фактов продаж. Каждая запись имеет идентификатор, который является первичным ключом. Но подавляющее большинство запросов запрашивают данные между датой и другой датой, поэтому лучшим кластеризованным ключевым ключом будет продажа date , а не ID . Другим примером наличия кластерного индекса из первичного ключа является очень низкий ключ селективности, такой как «категория» или «состояние», ключ с очень небольшим количеством отдельных значений. Наличие кластерного индексного ключа с этим ключом с низкой селективностью в качестве самой левой клавиши, например. (state, id), часто имеет смысл из-за сканирования диапазонов, которые ищут все записи в определенном «состоянии».

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

ответил Remus Rusanu 11 52011vEurope/Moscow11bEurope/MoscowFri, 11 Nov 2011 02:39:02 +0400 2011, 02:39:02
23

Основная причина использования кластеризованных индексов указана в Wikipedia :

  

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

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

Таким образом, я всегда могу выбрать SELECT WHERE или ORDER в столбце Country; кластерный индекс в Первичном ключе не приносит мне никакой пользы, я не получаю доступа к этим данным ПК, я обращаюсь к нему с помощью этого другого столбца. Поскольку у меня может быть только один кластеризованный индекс в таблице, объявление моего PK как Clustered помешает мне использовать Clustered Index on Country.

Кроме того, вот хорошая статья о Clustered vs Nonclustered Indexes , получается кластеризованные индексы вызванных проблемами производительности в SQL Server 6.5 (что, по крайней мере, мы надеемся, не относится к большинству из нас).

  

Если вы поместите кластерный индекс в столбец IDENTITY, то все ваши вставки появятся на последней странице таблицы - и эта страница заблокирована на время каждой ИДЕНТИФИКАЦИИ. Нет большой сделки ... если у вас нет 5000 человек, которые хотят последней страницы. Тогда у вас много споров для этой страницы

Обратите внимание, что это не относится к более поздним версиям.

ответил Ben Brocka 11 52011vEurope/Moscow11bEurope/MoscowFri, 11 Nov 2011 02:26:13 +0400 2011, 02:26:13
9

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

ответил Bryan Johns 29 42012vEurope/Moscow11bEurope/MoscowThu, 29 Nov 2012 08:07:29 +0400 2012, 08:07:29
5

Очень распространенный пример:

  • Клиент с CustomerID как CLUSTERED PRIMARY KEY
  • Таблица заказов с OrderID (PK), CustomerID, OrderDate и некоторыми другими столбцами
  • OrderPositions с помощью OrderPositionID (PK), OrderId, ProductID, Amount, Price ...
  • вам нужно индексировать таблицы заказов

Конечно, «это зависит» - это почти всегда - правильный ответ, но большинство приложений (а не BI-Reports) будут работать на основе клиента (например, вы входите в систему как клиент 278 на веб-сайт и нажимаете на «Мой заказы "или клерк перечисляет все заказы для клиента 4569 или ваш счет-фактура суммирует все заказы для клиента 137).

В этом случае было бы не очень удобно класть таблицу с помощью OrderID. Да, у вас будут запросы в виде SELECT ... WHERE OrderId =? , чтобы перечислить детали заказа, но это, как правило, будет коротким и дешевым (3 раза) индексом.

С другой стороны, если вы скопируете таблицу Order с помощью CustomerID, ему не нужно будет выполнять несколько ключевых запросов каждый раз, когда вы запрашиваете таблицу для < code> CustomerId =?.

CLUSTERED INDEX должен всегда быть UNIQUE, иначе SQL Server добавит невидимый (= непригодный) столбец INT UNIQUIFIER, чтобы обеспечить уникальность - и было бы гораздо разумнее добавлять реальные (пригодные для использования) данные, а затем некоторые случайные (в зависимости от порядка вставки).

Поскольку клиент (надеюсь) разместит несколько заказов, нам нужно будет добавить либо OrderID, либо (если вы обычно сортируете для этого) OrderDate ( если это дата-время, иначе клиент будет ограничен одним порядком в день) в CLUSTERED INDEX и в итоге:

CREATE UNIQUE CLUSTERED INDEX IX_Orders_UQ по заказам (CustomerID, OrderID)

Те же правила применяются к таблице OrderPositions. Обычно большинство запросов будут перечислять все позиции по определенному заказу, поэтому вы должны создать PK с помощью OrderPositionID как NONCLUSTERED и UNIQUE CLUSTERED INDEX на OrderId, OrderPositionID.

BTW: правильно, что таблица Customer кластеризована по его PK (CustomerID), потому что это «таблица верхнего уровня» и будет включена типичное приложение - в основном запрашивается его CustomerID.

Чистые таблицы поиска, например. Genders или InvoiceTypes или PaymentType - это еще один пример таблиц, которые должны быть сгруппированы по его PK (потому что вы обычно присоединяетесь к ним по GenderId, InvoiceTypeId или PaymentTypeId).

ответил Thomas Franz 20 AMpWed, 20 Apr 2016 10:10:51 +030010Wednesday 2016, 10:10:51
1

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

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

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

ответил crokusek 17 AMpWed, 17 Apr 2013 02:44:04 +040044Wednesday 2013, 02:44:04

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132