Кластерный против некластеризованного

Question

Кластерный против некластеризованного

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

У нас есть таблица, которая содержит "судебные приказы" для людей. Когда я создал таблицу (имя: 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 = ...

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

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

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

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

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

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

692 5

performance sql indexing sql-server-2008

5 ответов:

Comments

Ничего не найдено.

Adam Robinson · Accepted Answer · 2014-03-17 15:56:23

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

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

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

Darian Miller · Accepted Answer · 2011-09-30 08:04:46

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

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

Я предполагаю, что ваш PersonId не будет меняться, поэтому обновления не вступают в игру здесь. А рассмотреть снимок в несколько рядов с PersonId из Один Два Три Три Четыре Пять Шесть Семь Восемь 8

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

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

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

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

Martin Smith · Accepted Answer · 2011-09-30 11:21:56

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

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

может использоваться для часто используемых запросов.

обеспечивают высокую степень уникальности.

может использоваться в запросах диапазона.

ваш

user2191454 · Accepted Answer · 2015-06-08 15:23:18

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

Clustered

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

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

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

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

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

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

toLucky · Accepted Answer · 2013-11-26 10:20:16

некоторые БД с некоторыми неприятными выбирает, присоединяется в хранимой процедуре-только diffrence является индексом

индексы-кластеризованные против некластеризованных
  891 rows
  10 sec
  NONCLUSTERED 

  OR

  891 rows
  14 sec
  CLUSTERED