Кластерный и некластерный

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

У нас есть таблица, которая содержит "Приказы суда" для людей. Когда я создал таблицу, (Name: CourtOrder), я создал ее как:

CREATE TABLE dbo.CourtOrder
(
  CourtOrderID INT NOT NULL IDENTITY(1,1), (Primary Key)
  PersonId INT NOT NULL,
  + around 20 other fields of different types.
)

Затем я применил некластеризованный индекс к первичному ключу (для эффективности). Мои причины заключаются в том, что это уникальное поле (первичный ключ) и должно индексироваться, главным образом для целей выбора, поскольку мы часто Select from table where primary key = ...

Затем я применил индекс CLUSTERED для PersonId. Причина состояла в том, чтобы группировать заказы для определенного человека физически, поскольку подавляющее большинство работ получает заказы на человека. Итак, select from mytable where personId = ...

Я сейчас подтянулся. Мне сказали, что мы должны поместить кластеризованный индекс на первичный ключ и нормальный индекс на personId. Мне это очень странно. Во-первых, почему вы кладете кластерный индекс в уникальный столбец? что это кластеризация? Неужели это пустая трата кластеризованного индекса? Я бы поверила, что обычный индекс будет использоваться в уникальном столбце. Кроме того, кластеризация индекса будет означать, что мы не можем группировать другой столбец (по одному на таблицу, правда?).

Мне говорят, что я ошибся в том, что, полагая, что кластеризованный индекс в PersonId сделает медленные вставки. Для 5% -ного увеличения скорости выбора мы получим 95% -ное снижение скорости при вставках и обновлениях. Это верно и верно?

Они говорят, что, поскольку мы кластер personId, SQL Server должен переставлять данные, когда мы вставляем или вносим изменения в PersonId.

Итак, я спросил, почему SQL должен иметь концепцию CLUSTERED INDEX, если это так медленно? Это так медленно, как они говорят? Как мне настроить свои индексы для достижения оптимальной производительности? Я бы подумал, что SELECT используется больше, чем INSERT... но они говорят, что у нас есть проблемы с блокировкой на INSERTS...

Надеюсь, кто-то может мне помочь.

Ответ 1

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

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

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

Ответ 2

Я отнюдь не эксперт SQL... так что рассматривайте это как представление разработчика, а не представление DBA.

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

Я предполагаю, что ваш PersonId не будет меняться, поэтому обновления не вступают в игру здесь. Но рассмотрим снимок нескольких строк с PersonId из 1 2 3 3 4 5 6 7 8 8

Теперь вставьте 20 новых строк для PersonId 3. Сначала, поскольку это не уникальный ключ, сервер добавляет некоторые дополнительные байты в ваше значение (за кулисами), чтобы сделать его уникальным (что также добавляет дополнительное пространство), а затем место, где они будут проживать, должно быть изменено. Сравните это с тем, чтобы вставить автоинкрементный ПК, где вставки появляются в конце. Нетехническое объяснение, скорее всего, сведено к следующему: существует меньше работ "перетасовки листьев", если он, естественно, развивает более высокие значения в конце таблицы и перерабатывает местоположение существующих элементов в этом месте при вставке ваших элементов.

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

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

В любом случае, удачи!

Ответ 3

Некоторые авторы предлагают не "тратить" CI на столбец identity, если есть альтернатива, которая принесет пользу запросам диапазона.

Из MSDN Руководство по разработке кластерных указателей ключ следует выбирать в соответствии со следующими критериями

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

Ваш столбец CourtOrderID соответствует 2. Ваш PersonId соответствует 1 и 3. Поскольку в большинстве строк будет добавлен добавленный uniqueifier, вы можете просто объявить его уникальным и использовать PersonId,CourtOrderID, поскольку он будет иметь одинаковую ширину, но будет более полезен, поскольку кластерный индексный ключ будет добавлен ко всем NCI, поскольку локатор строк, и это позволит им покрывать больше запросов.

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

Ответ 4

Это объясняется в следующей ссылке: https://msdn.microsoft.com/en-us/ms190457.aspx

кластерного

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

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

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

Ответ 5

Некоторые db с некоторыми неприятными выборами, объединяются в хранимую процедуру - только diffrence - это индекс

ИНДЕКСЫ - кластерные и некластеризованные

  891 rows
  10 sec
  NONCLUSTERED 

  OR

  891 rows
  14 sec
  CLUSTERED